Auto commit definition
When auto-commit is enabled, this means that SQL transactions:
- It is implicitly launched by the SQL library (using the
BEGIN statement) before data change INSERT ( INSERT , UPDATE , DELETE or REPLACE ); - Explicitly ends by the user (using the
COMMIT or ROLLBACK statement).
When auto-commit is disabled, this means that SQL transactions are performed either:
- implicitly launched by the SQL library (with the
BEGIN operator) before data modification INSERT ( INSERT , UPDATE , DELETE or REPLACE ); - implicitly terminated by the SQL library (using the
COMMIT or ROLLBACK statement) after data change INSERT ( INSERT , UPDATE , DELETE or REPLACE );
or:
- explicitly started by the user (using the
BEGIN or SAVEPOINT statement); - Explicitly ends by the user (using the
COMMIT , ROLLBACK or RELEASE statement or the ROLLBACK TO + RELEASE statements).
Library implementation
The SQLite C core library works by default with auto-commit to :
Test for auto-lock mode
int sqlite3_get_autocommit(sqlite3*);
The sqlite3_get_autocommit() interface returns a non-zero or zero value if this database connection is or is not in automatic commit mode, respectively. Auto-lock is enabled by default. Auto-lock is disabled by the BEGIN operator. Auto-lock mode is enabled using COMMIT or ROLLBACK .
If a certain type of error occurs in a statement with several statements (errors, including SQLITE_FULL , SQLITE_IOERR , SQLITE_NOMEM , SQLITE_BUSY and SQLITE_INTERRUPT ), the transaction may be rolled back automatically. The only way to find out if SQLite automatically rolls back a transaction after an error is to use this function.
If another thread changes the state of automatic confirmation of a database connection during the execution of this routine, the return value is undefined.
See also lists of objects, constants, and functions.
However, PEP 249 requires Python database libraries to work by default with auto-commit disabled :
.commit()
Pass any pending transaction to the database.
Note that if the database supports auto-commit, this should be turned off initially. An interface method can be provided to turn it back on.
Database modules that do not support transactions must implement this method with empty functionality.
Therefore, the SQLite3 Python library works by default with auto-commit disabled :
Transaction management
The sqlite3 library sqlite3 to auto-commit mode, and the sqlite3 Python module sqlite3 to not.
The autocommit mode means that statements that modify the database take effect immediately. The BEGIN or SAVEPOINT statement disables autocommit mode, and COMMIT , ROLLBACK or RELEASE , which completes the external transaction, autocommit automatic commit mode.
The Python sqlite3 module sqlite3 to issuing a BEGIN statement implicitly before a data change language (DML) statement (i.e., INSERT / UPDATE / DELETE / REPLACE ).
You can control what type of BEGIN sqlite3 implicitly executed using the isolation_level parameter to call connect() , or through the isolation_level property of the connections. If you specify no isolation_level, a regular BEGIN , which is equivalent to specifying DEFERRED . Other possible values are IMMEDIATE and EXCLUSIVE .
You can turn off implicit transaction management for sqlite3 modules by setting isolation_level to None . As a result, the sqlite3 core library will work in autocommit mode. Then you can fully control the status of the transaction by explicitly executing the 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.
You can test the behavior of the Python SQLite3 library using this Python program:
import sqlite3 # Auto-commit is off (default). connection = sqlite3.connect("test.sqlite") cursor = connection.cursor() cursor.execute("CREATE TABLE IF NOT EXISTS t (i INTEGER)") cursor.execute("INSERT INTO t VALUES (?)", (5,)) cursor.close() connection.close() connection = sqlite3.connect("test.sqlite") cursor = connection.cursor() cursor.execute("SELECT * FROM t") assert cursor.fetchall() == [] # data had not been committed cursor.close() connection.close() # Auto-commit is on. connection = sqlite3.connect("test.sqlite", isolation_level=None) cursor = connection.cursor() cursor.execute("CREATE TABLE IF NOT EXISTS t (i INTEGER)") cursor.execute("INSERT INTO t VALUES (?)", (5,)) cursor.close() connection.close() connection = sqlite3.connect("test.sqlite", isolation_level=None) cursor = connection.cursor() cursor.execute("SELECT * FROM t") assert cursor.fetchall() == [(5,)] # data had been committed cursor.close() connection.close()
Note. - The second statement will fail if the in-memory database is used instead of the database on the disk (by passing the argument ":memory:" instead of the "test.sqlite" function to the sqlite3.connect function), since the in-memory databases are deleted when the connection is closed .
We hope this information helps you answer your question.