Cannot prevent auto-commit in SQLite3 for Python

Question

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)

CodeMonkey · Accepted Answer · 2022-02-26 16:29:33Z

2

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

edited Feb 26, 2022 at 16:29

answered Feb 26, 2022 at 15:33

CodeMonkey

23.9k4 gold badges37 silver badges79 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Shuhei Yamada Over a year ago

Thank you! Deleting the --sql worked like a charm. I am really surprised because the --sql was there for VS Code extension that highlights SQL syntax within .py file. I thought it had no effect on the code itself because -- acted as a comment for SQL, letting it ignore whatever was behind it until line end. Why does the (non)existence of --sql converts between ddl and sql command?

CodeMonkey Over a year ago

@ShuheiYamada - If call cursor.executescript('INSERT...') vs cursor.execute('INSERT..') then auto-commit is enabled so --sql in the SQL calling execute() must enable script mode which is auto-commit enabled.

Shuhei Yamada Over a year ago

Thank you so much, now I can put hours of head-scratching to rest. Python doc for sqlite3 mentions nothing of the -- comment, it probably was not meant to be used.

Collectives™ on Stack Overflow

Cannot prevent auto-commit in SQLite3 for Python

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related