pythonsqlitetransactionsapsw

How to create multiple complex sqlite transactions with an expected out using apsw?

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.

Solution

  • 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.