Transactions with Python sqlite3

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 sqlite3sql = sqlite3.connect("/tmp/test.db")sql.isolation_level = Nonec = 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")

Per the docs,

Connection objects can be used as context managers that automatically commit or rollback transactions. In the event of an exception, the transaction is rolled back; otherwise, the transaction is committed:

Therefore, if you let Python exit the with-statement when an exception occurs, the transaction will be rolled back.

import sqlite3filename = '/tmp/test.db'with sqlite3.connect(filename) as conn:    cursor = conn.cursor()    sqls = [        'DROP TABLE IF EXISTS test',        'CREATE TABLE test (i integer)',        'INSERT INTO "test" VALUES(99)',]    for sql in sqls:        cursor.execute(sql)try:    with sqlite3.connect(filename) as conn:        cursor = conn.cursor()        sqls = [            'update test set i = 1',            'fnord',   # <-- trigger error            'update test set i = 0',]        for sql in sqls:            cursor.execute(sql)except sqlite3.OperationalError as err:    print(err)    # near "fnord": syntax errorwith sqlite3.connect(filename) as conn:    cursor = conn.cursor()    cursor.execute('SELECT * FROM test')    for row in cursor:        print(row)        # (99,)

yields

(99,)

as expected.

Python's DB API tries to be smart, and begins and commits transactions automatically.

I would recommend to use a DB driver that does not use the Python DB API, like apsw.