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

Question

I read somewhere that to save data to a SQLite3 database in Python, the method commit of the connection object should be called. Yet I have never needed to do this. Why?

Answer 1

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

Commit modes

A database transaction is a unit of recovery. In transactional database engines, all SQL statements are executed in a database transaction.

• When an SQL statement is not enclosed in a pair of start-transaction (BEGIN or SAVEPOINT) and end-transaction (COMMIT, ROLLBACK or RELEASE) SQL statements, it is executed in the database transaction implicitly delimited by the boundaries of the SQL statement. The SQL statement is said to be in auto-commit mode, since its database transaction is automatically delimited.

• When an SQL statement is enclosed in a pair of start-transaction (BEGIN or SAVEPOINT) and end-transaction (COMMIT, ROLLBACK or RELEASE) SQL statements, it is executed in the database transaction explicitly delimited by the pair of SQL statements. The SQL statement is said to be in manual commit mode, since its database transaction is manually delimited.

In other words, at the database engine level, the auto-commit mode is the default.

The best practice is to always use the manual commit mode, because by grouping SQL statements into database transactions explicitly, data corruption is avoided since units of recovery are delimited as intended.

Database drivers are above database engines and therefore can transform the SQL statements that they send to underlying database engines. A database driver often enforces the manual commit mode by implicitly sending to the database engine a start-transaction (BEGIN) SQL statement after any connection and end-transaction (COMMIT or ROLLBACK) SQL statement (that is to say before any SQL statement that is not already in a database transaction). That way, users who prefer to use the auto-commit mode have to tell the database driver explicitly.

In other words, at the database driver level, the manual commit mode is often the default.

SQLite database engine

The SQLite database engine operates by default in auto-commit mode:

Test For Auto-Commit Mode

int sqlite3_get_autocommit(sqlite3*);

The sqlite3_get_autocommit() interface returns non-zero or zero if the given database connection is or is not in autocommit mode, respectively. Autocommit mode is on by default. Autocommit mode is disabled by a BEGIN statement. Autocommit mode is re-enabled by a COMMIT or ROLLBACK.

If certain kinds of errors occur on a statement within a multi-statement transaction (errors including SQLITE_FULL, SQLITE_IOERR, SQLITE_NOMEM, SQLITE_BUSY, and SQLITE_INTERRUPT) then the transaction might be rolled back automatically. The only way to find out whether SQLite automatically rolled back the transaction after an error is to use this function.

If another thread changes the autocommit status of the database connection while this routine is running, then the return value is undefined.

See also lists of Objects, Constants, and Functions.

SQLite3 database driver

PEP 249 requires that Python database drivers operate by default in manual commit mode:

.commit()

Commit any pending transaction to the database.

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.

Database modules that do not support transactions should implement this method with void functionality.

Consequently, the SQLite3 database driver operates by default in manual commit mode:

Controlling Transactions

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

You can control which kind of BEGIN statements sqlite3 implicitly executes via the isolation_level parameter to the connect() call, or via the isolation_level property of connections. If you specify no isolation_level, a plain BEGIN is used, which is equivalent to specifying DEFERRED. Other possible values are IMMEDIATE and EXCLUSIVE.

You can disable the sqlite3 module’s implicit transaction management by setting isolation_level to None. This will leave the underlying sqlite3 library operating in autocommit mode. You can then completely control the transaction state by explicitly issuing BEGIN, ROLLBACK, SAVEPOINT, and RELEASE statements in your code.

Changed in version 3.6: sqlite3 used to implicitly commit an open transaction before DDL statements. This is no longer the case.

Note. — For backwards compatibility reasons, the SQLite3 database driver initiates the manual commit mode only before data modification (INSERT, UPDATE, DELETE or REPLACE) SQL statements, not before data definition (CREATE, DROP) nor data query (SELECT) SQL statements, which is not PEP 249 compliant and will hopefully be addressed soon.

Example

The following Python program illustrates the implications of using the manual commit mode versus the auto-commit mode with the SQLite3 database driver:

import sqlite3

# Manual commit mode (the default).

connection = sqlite3.connect("test.sqlite")
cursor = connection.cursor()
cursor.execute("CREATE TABLE IF NOT EXISTS t (i INT)")  # sent as is (DDL)
cursor.execute("INSERT INTO t VALUES (?)", (5,))  # sent as BEGIN; … (DML)
cursor.close()
connection.close()  # connection closed without COMMIT statement (common error)

connection = sqlite3.connect("test.sqlite")
cursor = connection.cursor()
cursor.execute("SELECT * FROM t")  # table persisted (full transaction)
assert cursor.fetchall() == []  # data did not persist (partial transaction)
cursor.close()
connection.close()

# Auto-commit mode.

connection = sqlite3.connect("test.sqlite", isolation_level=None)
cursor = connection.cursor()
cursor.execute("CREATE TABLE IF NOT EXISTS t (i INT)")  # sent as is
cursor.execute("INSERT INTO t VALUES (?)", (5,))  # sent as is
cursor.close()
connection.close()

connection = sqlite3.connect("test.sqlite", isolation_level=None)
cursor = connection.cursor()
cursor.execute("SELECT * FROM t")  # table persisted
assert cursor.fetchall() == [(5,)]  # data persisted
cursor.close()
connection.close()

Note. — The second assertion would fail if an in-memory database was used instead of an on-disk database (by passing the argument ":memory:" instead of "test.sqlite" to the function sqlite3.connect), since in-memory databases are dropped when the connection is closed.