1

I want to execute several complex statements in a transaction with a select statement at the end which I use for further processing.

Doing so once works fine but as soon as I execute the same statement again it causes the error below.

Test code:

import apsw
connection = apsw.Connection("temp.db")

cursor = connection.cursor()
cursor.execute("""
BEGIN TRANSACTION;
CREATE TABLE Foo(x);
COMMIT;
""")

cursor = connection.cursor()
print(cursor.execute("""
BEGIN TRANSACTION;
INSERT INTO Foo (x) VALUES (1);
INSERT INTO Foo (x) VALUES (2);
SELECT x FROM Foo LIMIT 1;
COMMIT;
""").fetchone())


cursor = connection.cursor()
print(cursor.execute("""
BEGIN TRANSACTION;
INSERT INTO Foo (x) VALUES (3);
INSERT INTO Foo (x) VALUES (4);
SELECT x FROM Foo LIMIT 1;
COMMIT;
""").fetchone())

Output:

$ python test.py 
(1,)
Traceback (most recent call last):
  File "test.py", line 28, in <module>
    """).fetchone())
  File "src/cursor.c", line 236, in resetcursor
apsw.SQLError: SQLError: cannot start a transaction within a transaction

Edit: It seems to be connected to the "fetchone()" method, if I instead use "fetchall()" it works. In my specific case I do expect on one result, so I have edited the code to reflect this.

VLAZ
  • 26,331
  • 9
  • 49
  • 67
Fabian Barkhau
  • 1,349
  • 2
  • 12
  • 31
  • Possible duplicate of [python sqlite "BEGIN TRANSACTION" and "COMMIT" commands](http://stackoverflow.com/questions/26770719/python-sqlite-begin-transaction-and-commit-commands) – AndrewK Jul 14 '16 at 18:43

1 Answers1

0

SQLite computes results on the fly. When you call fetchone(), the execution runs only as far as needed to return one row.

To ensure that all statements are executed, call fetchall(), or iterate over the results, even if you know that there is only one.

It might be a better idea to not squeeze as many statements as possible into a single SQL string.

CL.
  • 173,858
  • 17
  • 217
  • 259
  • Thank you for the helpful answer, I guess I will have to use `fetchall()` as I need to squeeze all statements into one transaction to ensure the correct state. – Fabian Barkhau Jul 14 '16 at 22:43
  • You don't need to use a single `execute` for a single transaction. – CL. Jul 15 '16 at 07:18