Why doesn’t SQLite require a commit() call to save data?

Question

I read somewhere that to save data to an SQLite database in Python, a commit() call is required. Yet I have never needed to do this. Why?

Epicycle

Answer 1

It means that your SQLite3 driver is in auto-commit mode.

Understanding commit modes

In transactional database management systems, a transaction is a sequence of data access operations that is

• recoverable (atomicity property) to hide the effects of an abortion of the transaction, meaning that the database state after an aborted transaction is the same as if no operations of the transaction were performed;
• serialisable (isolation property) to hide the effects of other concurrent transactions, meaning that the database state after concurrently executing multiple transactions is the same as if they were sequentially executed.

According to the ISO/IEC 9075:2011 SQL standard, if no transaction is currently active, a transaction is explicitly initiated by a start transaction statement, or implicitly initiated before

• all SQL-schema statements;
• some SQL-transaction statements (savepoint statement, commit statement, rollback statement);
• some SQL-data statements (open statement, close statement, fetch statement, select statement, insert statement, delete statement, update statement, merge statement, truncate table statement, allocate extended dynamic cursor statement, allocate received cursor statement, dynamic open statement, dynamic close statement, dynamic fetch statement, direct select statement, dynamic single row select statement, dynamic delete statement, preparable dynamic delete statement, dynamic update statement, preparable dynamic update statement, free locator statement, hold locator statement);
• some SQL-dynamic statements (describe input statement, describe output statement, allocate descriptor statement, deallocate descriptor statement, get descriptor statement, set descriptor statement, deallocate prepared statement).

And a transaction is explicitly terminated by a commit statement or rollback statement, or implicitly terminated by an abortion of the transaction (cf. ISO/IEC 9075-2:2011).

So almost all SQL statements are executed in a transaction, and the transaction must be explicitly committed to take effect. A database interface that is in this commit mode is in manual commit mode. Manual commit mode is a best practice which is fine for programs (non-interactive sessions) but can be cumbersome for users (interactive sessions). For that reason, most database interfaces also provide an auto-commit mode for interactive sessions. In auto-commit mode, a transaction implicitly initiated before a transaction-initiating statement other than the start transaction statement is implicitly committed after the statement, whereas a transaction explicitly initiated by a start transaction statement must be explicitly committed to take effect.

A database interface is specific to a database engine so more generic interfaces are commonly used to interact with a database engine (e.g. Open Database Connectivity, Java Database Connectivity, Python Database API). The adaptation of a generic database interface to a specific database interface is provided by a database driver. The SQLite engine has a specific database interface in C. The SQLite3 driver adapts the Python Database API to the SQLite API.

SQL statements are interpreted by a database engine. So when a database engine and database driver are in the same commit mode (manual commit mode or auto-commit mode), the database driver can pass unmodified SQL statements to the database engine. However when they are in different commit modes, the database driver has to either configure the database engine to match the commit mode of the database driver, or transform SQL statements before passing them to the database engine to emulate the commit mode of the database driver:

• The database driver emulates manual commit mode by implicitly issuing a start transaction statement after each connection opening, commit statement, and rollback statement to prevent the transaction from being implicitly committed by the database engine in auto-commit mode, and by implicitly issuing a rollback statement before each connection closing to force the last transaction to be rolled back by the database engine (e.g. A; B; START TRANSACTION; C; D; COMMIT; E; F; is transformed into START TRANSACTION; A; B; START TRANSACTION; C; D; COMMIT; START TRANSACTION; E; F; ROLLBACK;).
• The database driver emulates auto-commit mode by implicitly issuing a commit statement after each transaction-initiating statements other than start transaction statements and statements within a start transaction statement and commit statement or rollback statement to make the transaction be explicitly committed by the database engine in manual commit mode (e.g. A; B; START TRANSACTION; C; D; COMMIT; E; F; is transformed into A; COMMIT; B; COMMIT; START TRANSACTION; C; D; COMMIT; E; COMMIT; F; COMMIT;).

The SQLite engine is always in auto-commit mode (cf. the SQLite documentation). The SQLite3 driver is by default in manual commit mode so has to emulate it (cf. the SQLite3 documentation). The SQLite3 driver currently does not emulate manual commit mode properly like described in the previous paragraph, but by implicitly issuing a start transaction statement before each transaction-initiating SQL-data statements other than select statements, so SQL-schema statements and select statements are not always in an explicitly initiated transaction (cf. the SQLite3 implementation). That is not Python Database API-conformant so the legacy manual commit mode and auto-commit mode configured with the isolation_level attribute of the Connection class will be deprecated in Python 3.12 with the introduction of a new autocommit attribute in the Connection class for configuring the Python Database API-conformant manual commit mode and auto-commit mode (cf. CPython issue #83638 and CPython pull request #93823).

Example 1. — This Python 3.11 program uses the SQLite3 driver in legacy manual commit mode.

import sqlite3

connection = sqlite3.connect(':memory:', isolation_level='DEFERRED')
# No transaction is explicitly initiated here by a start transaction statement.
assert connection.in_transaction is False
statements = []
connection.set_trace_callback(statements.append)
cursor = connection.cursor()
# Transaction 1 is implicitly initiated here.
cursor.execute('CREATE TABLE t (i INT)')
# Transaction 1 is implicitly committed here.
# Transaction 2 is explicitly initiated here by a start transaction statement.
cursor.execute('INSERT INTO t VALUES (?)', (1,))
cursor.execute('CREATE TABLE u (j INT)')
cursor.execute('INSERT INTO u VALUES (?)', (2,))
cursor.close()
connection.close()
# Transaction 2 is implicitly rolled back here.
assert statements == [
    'CREATE TABLE t (i INT)',
    'BEGIN DEFERRED',
    'INSERT INTO t VALUES (1)',
    'CREATE TABLE u (j INT)',
    'INSERT INTO u VALUES (2)',
]

Example 2. — This Python 3.12 program uses the SQLite3 driver in manual commit mode.

import sqlite3

connection = sqlite3.connect(':memory:', autocommit=False)
# Transaction 1 is explicitly initiated here by a start transaction statement.
assert connection.in_transaction is True
statements = []
connection.set_trace_callback(statements.append)
cursor = connection.cursor()
cursor.execute('CREATE TABLE t (i INT)')
cursor.execute('INSERT INTO t VALUES (?)', (1,))
cursor.execute('CREATE TABLE u (j INT)')
cursor.execute('INSERT INTO u VALUES (?)', (2,))
cursor.close()
connection.close()
# Transaction 1 is explicitly rolled back here by a rollback statement.
assert statements == [
    'CREATE TABLE t (i INT)',
    'INSERT INTO t VALUES (1)',
    'CREATE TABLE u (j INT)',
    'INSERT INTO u VALUES (2)',
    'ROLLBACK',
]

Example 3. — This Python 3.11 program uses the SQLite3 driver in legacy auto-commit mode.

import sqlite3

connection = sqlite3.connect(':memory:', isolation_level=None)
# No transaction is explicitly initiated here by a start transaction statement.
assert connection.in_transaction is False
statements = []
connection.set_trace_callback(statements.append)
cursor = connection.cursor()
# Transaction 1 is implicitly initiated here.
cursor.execute('CREATE TABLE t (i INT)')
# Transaction 1 is implicitly committed here.
# Transaction 2 is implicitly initiated here.
cursor.execute('INSERT INTO t VALUES (?)', (1,))
# Transaction 2 is implicitly committed here.
# Transaction 3 is implicitly initiated here.
cursor.execute('CREATE TABLE u (j INT)')
# Transaction 3 is implicitly committed here.
# Transaction 4 is implicitly initiated here.
cursor.execute('INSERT INTO u VALUES (?)', (2,))
# Transaction 4 is implicitly committed here.
cursor.close()
connection.close()
assert statements == [
    'CREATE TABLE t (i INT)',
    'INSERT INTO t VALUES (1)',
    'CREATE TABLE u (j INT)',
    'INSERT INTO u VALUES (2)',
]

Example 4. — This Python 3.12 program uses the SQLite3 driver in auto-commit mode.

import sqlite3

connection = sqlite3.connect(':memory:', autocommit=True)
# No transaction is explicitly initiated here by a start transaction statement.
assert connection.in_transaction is False
statements = []
connection.set_trace_callback(statements.append)
cursor = connection.cursor()
# Transaction 1 is implicitly initiated here.
cursor.execute('CREATE TABLE t (i INT)')
# Transaction 1 is implicitly committed here.
# Transaction 2 is implicitly initiated here.
cursor.execute('INSERT INTO t VALUES (?)', (1,))
# Transaction 2 is implicitly committed here.
# Transaction 3 is implicitly initiated here.
cursor.execute('CREATE TABLE u (j INT)')
# Transaction 3 is implicitly committed here.
# Transaction 4 is implicitly initiated here.
cursor.execute('INSERT INTO u VALUES (?)', (2,))
# Transaction 4 is implicitly committed here.
cursor.close()
connection.close()
assert statements == [
    'CREATE TABLE t (i INT)',
    'INSERT INTO t VALUES (1)',
    'CREATE TABLE u (j INT)',
    'INSERT INTO u VALUES (2)',
]

Stockade

Answer 2

Probably autocommit is on, it is by default http://www.sqlite.org/c3ref/get_autocommit.html

Janinejanis

ive been playing around with python sqlite3 and i have no idea how to make the code on that link work so i can check if auto commit is on – Epicycle

@james, see docs.python.org/library/… – Atheistic

It seems strange that the auto-commit is on by default in this Python standard lib module, while the Python DB API spec python.org/dev/peps/pep-0249 commands: "Note that if the database supports an auto-commit feature, this must be initially off. An interface method may be provided to turn it back on." :-) – Implausible

Not exactly. By default, auto-commit is on in the SQLite C library, but it is off in the SQLite 3 Python library. – Omegaomelet

Answer 3

Add isolation_level=None to connect (Ref)

db = sqlite.connect(":memory:", isolation_level=None)

Tardy

When "sqlite." is an alias or equivalent for the sqlite3 module of current Python standard lib, this just tells sqlite3 explicitely that the auto-commit mode shall be on, which is the default anyway. It reports conn.isolation_level as '' by default, but being also not non-zero makes the effect. – Implausible

@Implausible Actually auto-commit is off by default in the SQLite 3 Python library. – Omegaomelet

@Maggyero , no, autocommit is on by default – Precinct

@Precinct Not according to the official documentation and my tests (see the program given in my answer): ‘The underlying sqlite3 library operates in autocommit mode by default, but the Python sqlite3 module by default does not.’ – Omegaomelet

Answer 4

also connection objects can be used as context managers that automatically commit or rollback transactions. 11.13.7.3. on docs.python

# Successful, con.commit() is called automatically afterwards
with con:
    con.execute("insert into person(firstname) values (?)", ("Joe",))

Signalment

would the only way around this be to make a dictionary or list representation of this table and make changes to the list. then when im done screwing aound with some database and wanna save the changes go through the list and commit it to the table? – Epicycle

@james, don't use context manager like {with con:} in this example and do commit by yourself. if you don't use with statement and still have autocommit, refer to Navi answer and Ismail comment – Signalment

Answer 5

Python sqlite3 issues a BEGIN statement automatically before "INSERT" or "UPDATE". After that it automatically commits on any other command or db.close()

Snowman