Transactions with Python sqlite3

Question

I'm trying to port some code to Python that uses sqlite databases, and I'm trying to get transactions to work, and I'm getting really confused. I'm really confused by this; I've used sqlite a lot in other languages, because it's great, but I simply cannot work out what's wrong here.

Here is the schema for my test database (to be fed into the sqlite3 command line tool).

BEGIN TRANSACTION;
CREATE TABLE test (i integer);
INSERT INTO "test" VALUES(99);
COMMIT;

Here is a test program.

import sqlite3

sql = sqlite3.connect("test.db")
with sql:
    c = sql.cursor()
    c.executescript("""
        update test set i = 1;
        fnord;
        update test set i = 0;
        """)

You may notice the deliberate mistake in it. This causes the SQL script to fail on the second line, after the update has been executed.

According to the docs, the with sql statement is supposed to set up an implicit transaction around the contents, which is only committed if the block succeeds. However, when I run it, I get the expected SQL error... but the value of i is set from 99 to 1. I'm expecting it to remain at 99, because that first update should be rolled back.

Here is another test program, which explicitly calls commit() and rollback().

import sqlite3

sql = sqlite3.connect("test.db")
try:
    c = sql.cursor()
    c.executescript("""
        update test set i = 1;
        fnord;
        update test set i = 0;
    """)
    sql.commit()
except sql.Error:
    print("failed!")
    sql.rollback()

This behaves in precisely the same way --- i gets changed from 99 to 1.

Now I'm calling BEGIN and COMMIT explicitly:

import sqlite3

sql = sqlite3.connect("test.db")
try:
    c = sql.cursor()
    c.execute("begin")
    c.executescript("""
            update test set i = 1;
            fnord;
            update test set i = 0;
    """)
    c.execute("commit")
except sql.Error:
    print("failed!")
    c.execute("rollback")

This fails too, but in a different way. I get this:

sqlite3.OperationalError: cannot rollback - no transaction is active

However, if I replace the calls to c.execute() to c.executescript(), then it works (i remains at 99)!

(I should also add that if I put the begin and commit inside the inner call to executescript then it behaves correctly in all cases, but unfortunately I can't use that approach in my application. In addition, changing sql.isolation_level appears to make no difference to the behaviour.)

Can someone explain to me what's happening here? I need to understand this; if I can't trust the transactions in the database, I can't make my application work...

Python 2.7, python-sqlite3 2.6.0, sqlite3 3.7.13, Debian.

Answer 1

For anyone who'd like to work with the sqlite3 lib regardless of its shortcomings, I found that you can keep some control of transactions if you do these two things:

1. set Connection.isolation_level = None (as per the docs, this means autocommit mode)
2. avoid using executescript at all, because according to the docs it "issues a COMMIT statement first" - ie, trouble. Indeed I found it interferes with any manually set transactions

So then, the following adaptation of your test works for me:

import sqlite3

sql = sqlite3.connect("/tmp/test.db")
sql.isolation_level = None
c = sql.cursor()
c.execute("begin")
try:
    c.execute("update test set i = 1")
    c.execute("fnord")
    c.execute("update test set i = 0")
    c.execute("commit")
except sql.Error:
    print("failed!")
    c.execute("rollback")