sqlite3 saving changes without commit command in p

Question

i read somewhere to save data to a sqlite3 database in python you got to call the commit() function on the connection object. i never do this but my database still gets the data saved to it... why?

Answer 1

With the sqlite3 module for Python, autocommit is off by default (as required by PEP 249):

By default, the sqlite3 module opens transactions implicitly before a Data Modification Language (DML) statement (i.e. INSERT/UPDATE/DELETE/REPLACE), and commits transactions implicitly before a non-DML, non-query statement (i. e. anything other than SELECT or the aforementioned).

If you want autocommit mode, then set isolation_level to None.

Otherwise leave it at its default, which will result in a plain “BEGIN” statement, or set it to one of SQLite’s supported isolation levels: “DEFERRED”, “IMMEDIATE” or “EXCLUSIVE”.

You can check that:

import sqlite3

# non-autocommit mode (default)
connection = sqlite3.connect("test.db")
cursor = connection.cursor()
cursor.execute("CREATE TABLE IF NOT EXISTS t (i INT)")
cursor.execute("INSERT INTO t VALUES (?)", (5,))
cursor.close()
connection.close()
connection = sqlite3.connect("test.db")
cursor = connection.cursor()
cursor.execute("SELECT * FROM t")
assert cursor.fetchall() == []
cursor.close()
connection.close()

# autocommit mode
connection = sqlite3.connect("test.db", isolation_level=None)
cursor = connection.cursor()
cursor.execute("CREATE TABLE IF NOT EXISTS t (i INT)")
cursor.execute("INSERT INTO t VALUES (?)", (5,))
cursor.close()
connection.close()
connection = sqlite3.connect("test.db", isolation_level=None)
cursor = connection.cursor()
cursor.execute("SELECT * FROM t")
assert cursor.fetchall() == [(5,)]
cursor.close()
connection.close()

Note. — This test will fail using an in-memory database (with the ":memory:" argument passed to the function sqlite3.connect) instead of an on-disk database, since an in-memory database is freed when the connection is closed.

Answer 2

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",))

Answer 3

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

Answer 4

Add isolation_level=None to connect (Ref)

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

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