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 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")