Cannot prevent auto-commit in SQLite3 for Python-CodePudding

I am stumped in the behavior of python's builtin module sqlite3. The following code prints out the data inserted regardless of if I comment out/in the line containing commit statement. How can that be?

My understanding from reading the python doc is that while sqlite3's underlying C library has autocommit enabled by default, the python binding does not. Instead, I have to add isolation_level=None to connect() call in order to enable auto-commit. I am asking this because I want to turn off auto-commit and could not find a way to do so.

My Python version is 3.9.2, and since sqlite3 is a builtin module, the module version is also 3.9.2 I think.

import sqlite3
import os

if os.path.exists("dummy.db"):
    os.system('rm dummy.db')
assert not os.path.exists("dummy.db") #ensure dummy.db is a new db every run

# Write
c = sqlite3.connect("dummy.db")
ddl = '''--sql
CREATE TABLE foo_table (foo INTEGER, bar INTEGER, foobar INTEGER);
'''
c.execute(ddl)

ddl = '''--sql
INSERT INTO foo_table VALUES(1,2,3); 
'''
c.execute(ddl)

#c.commit()
c.close()

# Read
c = sqlite3.connect("dummy.db")
ddl = 'SELECT * FROM foo_table'
for row in c.execute(ddl):
    print(row)

Output

>>(1, 2, 3)

CodePudding user response：

Commands with "--sql" line in SQLite are run in a script mode which appears to be auto-commit by default as opposed to executing single SQL commands which uses manual commit mode by default for insert, update, delete, etc. If change the command from --sql to a standard SQL command then the insert is deferred until an expicit commit is executed.

con = sqlite3.connect("dummy.db")
cursor = con.cursor()
# auto-commit mode for CREATE/ALTER/DROP
cursor.execute('CREATE TABLE foo_table (foo INTEGER, bar INTEGER, foobar INTEGER)')
# Manual commit mode (the default) for INSERT, UPDATE, DELETE
cursor.execute('INSERT INTO foo_table VALUES(1,2,3)')
# uncomment commit below and the row will be inserted
#con.commit()
con.close()

# Read
con = sqlite3.connect("dummy.db")
cursor = con.cursor()
cursor.execute('SELECT * FROM foo_table')
result = cursor.fetchall()
print("rows=", len(result), sep='')
for row in result:
    print(row)

Output:

rows=0

If you want autocommit mode enabled for SQL commands, then set isolation_level to None.

con = sqlite3.connect("dummy.db", isolation_level=None)

Output:

rows=1
(1, 2, 3)

For more fine-grain control, you can use transactions, with BEGIN, COMMIT, and ROLLBACK commands. If call rollback or simply close the connection then insert is not committed.

cursor.execute("begin")
cursor.execute("INSERT...")
cursor.execute("rollback")