[Tutor] sqlite3 COMMIT directive

[Tim Golden]

On 01/10/2013 09:03, Alan Gauld wrote:
> You don't normally need to use COMMIT when programming SQLite from
> Python, the database starts and ends transactions automatically from
> within the execute() function. You only need to manually commit if you
> manually create the transaction using BEGIN...

Not so, I'm afraid. If you want autocommit, you need to send an
isolation_level of None to the .connect function. If you don't, the
database will rollback any uncommitted transactions on exit.

The code below will only output (1,) as that value was committed. The
later row is lost when the cursor/db closes.

<code>
import os, sys
import sqlite3

dbname = "no-commit.db"
if os.path.exists(dbname):
    os.unlink(dbname)

db = sqlite3.connect(dbname)
q = db.cursor()
q.execute("CREATE TABLE test(a INT)")
q.execute("INSERT INTO test(a) VALUES (1)")
db.commit()
q.execute("INSERT INTO test(a) VALUES (2)")
q.close()
db.close()

db = sqlite3.connect(dbname)
q = db.cursor()
for row in q.execute("SELECT * FROM test").fetchall():
    print(row)
q.close()
db.close()

</code>