When does sqlite3 roll back transactions in python, and when doesn't it?

Question

I am creating a little workshop to teach how to use python and SQL and came across this oddity. I wanted to show how to use the with statement to create a transaction with sqlite:

import sqlite3

filename = 'data/transaction.db'

print("_________________________")
print("Create Table")
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)',
        'SELECT * FROM test']
    for sql in sqls:
        cursor.execute(sql)
        print(cursor.fetchall())

print("_________________________")
print("Create Error with 'with'")        
try:
    with sqlite3.connect(filename) as conn:
        cursor = conn.cursor()
        sqls = [
            'update test set i = 1',
            'SELECT * FROM test',
            'fnord',   # <-- trigger error
            'update test set i = 0',]
        for sql in sqls:
            cursor.execute(sql)
            print(cursor.fetchall())
except sqlite3.OperationalError as err:
    print(err)
    # near "fnord": syntax error

print("_________________________")
print("Show Table")       
with sqlite3.connect(filename) as conn:
    cursor = conn.cursor()
    cursor.execute('SELECT * FROM test')
    for row in cursor:
        print(row)
        # (99,)

This works exactly as expected. However to prove that without the with block the executions would be done halfway I tried the following:

print("_________________________")
print("Create Error without 'with'")        
conn = sqlite3.connect(filename) 
cursor.execute(  'SELECT * FROM test')
print(cursor.fetchall())    
cursor.execute(  'UPDATE test SET i = 1 WHERE i = 99')
print(cursor.fetchall())    
cursor.execute(  'SELECT * FROM test')
print(cursor.fetchall())    
cursor.execute(  'update test set i = 0')
print(cursor.fetchall())    
cursor.execute(  'SELECT * FROM test')
print(cursor.fetchall())    
conn.close()

print("_________________________")
print("Show Table")       
with sqlite3.connect(filename) as conn:
    cursor = conn.cursor()
    cursor.execute('SELECT * FROM test')
    for row in cursor:
        print(row)
        # (99,)`

The whole output is:

_________________________
Create Table
[]
[]
[]
[(99,)]
_________________________
Create Error with 'with'
[]
[(1,)]
near "fnord": syntax error
_________________________
Show Table
(99,)
_________________________
Create Error without 'with'
[(99,)]
[]
[(1,)]
[]
[(0,)]
_________________________
Show Table
(99,)                            # Why is this not (0,)???

I am very confused as to why the last Block shows a 99 again. Eventually the plan is to add a try,except block with an exception, such that the SQL code mimics the first block - however I am confused without this already :).

Thanks for clarifying

score 0 · Answer 1 · answered Nov 18 '19 at 23:06

From the python sqlite3 API doc:

The underlying sqlite3 library operates in autocommit mode by default, but the Python sqlite3 module by default does not.

autocommit mode means that statements that modify the database take effect immediately. A BEGIN or SAVEPOINT statement disables autocommit mode, and a COMMIT, a ROLLBACK, or a RELEASE that ends the outermost transaction, turns autocommit mode back on.

The Python sqlite3 module by default issues a BEGIN statement implicitly before a Data Modification Language (DML) statement (i.e. INSERT/UPDATE/DELETE/REPLACE).

Python will rollback transactions if the connection is closed without a commit (or an explicit ROLLBACK is issued). No transactions are committed in this program.

FYI a new connection is created in the "Create error without 'with'" block, but no new cursor is instantiated.

C Perkins · Answer 2 · 2019-11-18T23:15:27.543

The Python with statement works with context managers. Whereas some context managers will release resources and possibly close an object, it seems that at least with the sqlite3.connection object, it merely commits or rolls back transactions but does not close the connection. This can be confirmed for the DB-API 2.0 interface:

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:

...
# Successful, con.commit() is called automatically afterwards
...
# con.rollback() is called after the with block finishes with an exception, the
# exception is still raised and must be caught
...
# Connection object used as context manager only commits or rollbacks transactions,
# so the connection object should be closed manually

For the non-with statement block, you are never committing the transactions. When the connection is closed, all changes are automatically rolled back.

You need to call

conn.commit();

See Why the need to commit explicitly when doing an UPDATE? for more details.

As a side note, the section of your code titled "Create Error without 'with'" does not actually cause an error/exception.

When does sqlite3 roll back transactions in python, and when doesn't it?

2 Answers2