Why doesn’t SQLite3 require a commit() call to save data?
Solution 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
orSAVEPOINT
) and end-transaction (COMMIT
,ROLLBACK
orRELEASE
) 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
orSAVEPOINT
) and end-transaction (COMMIT
,ROLLBACK
orRELEASE
) 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 aBEGIN
statement. Autocommit mode is re-enabled by aCOMMIT
orROLLBACK
.If certain kinds of errors occur on a statement within a multi-statement transaction (errors including
SQLITE_FULL
,SQLITE_IOERR
,SQLITE_NOMEM
,SQLITE_BUSY
, andSQLITE_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 Pythonsqlite3
module by default does not.
autocommit
mode means that statements that modify the database take effect immediately. ABEGIN
orSAVEPOINT
statement disablesautocommit
mode, and aCOMMIT
, aROLLBACK
, or aRELEASE
that ends the outermost transaction, turnsautocommit
mode back on.The Python
sqlite3
module by default issues aBEGIN
statement implicitly before a Data Modification Language (DML) statement (i.e.INSERT
/UPDATE
/DELETE
/REPLACE
).You can control which kind of
BEGIN
statementssqlite3
implicitly executes via theisolation_level
parameter to theconnect()
call, or via theisolation_level
property of connections. If you specify noisolation_level
, a plainBEGIN
is used, which is equivalent to specifyingDEFERRED
. Other possible values areIMMEDIATE
andEXCLUSIVE
.You can disable the
sqlite3
module’s implicit transaction management by settingisolation_level
toNone
. This will leave the underlyingsqlite3
library operating inautocommit
mode. You can then completely control the transaction state by explicitly issuingBEGIN
,ROLLBACK
,SAVEPOINT
, andRELEASE
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.
Solution 2
Probably autocommit is on, it is by default http://www.sqlite.org/c3ref/get_autocommit.html
Solution 3
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",))
Solution 4
Add isolation_level=None
to connect (Ref)
db = sqlite.connect(":memory:", isolation_level=None)
james
Updated on July 07, 2022Comments
-
james almost 2 years
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? -
james over 13 yearsive 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
-
james over 13 yearswould 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?
-
AntonyFalegk over 13 years@james, see docs.python.org/library/…
-
Jesse Droidka over 13 years@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
-
kxr about 7 yearsWhen "
sqlite.
" is an alias or equivalent for thesqlite3
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 reportsconn.isolation_level
as''
by default, but being also not non-zero makes the effect. -
kxr about 7 yearsIt 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." :-)
-
Maggyero almost 5 yearsNot exactly. By default, auto-commit is on in the SQLite C library, but it is off in the SQLite 3 Python library.
-
Maggyero almost 5 years@kxr Actually auto-commit is off by default in the SQLite 3 Python library.
-
iperov over 2 years@Maggyero , no, autocommit is on by default
-
Maggyero over 2 years@iperov 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.’