CODEWITHSUNDEEP
pythonsqlitetransactions
Georgi Angelov
Georgi Angelov

Reputation: 4388

Python SQLite - How to manually BEGIN and END transactions?

Context

So I am trying to figure out how to properly override the auto-transaction when using SQLite in Python. When I try and run

cursor.execute("BEGIN;")
.....an assortment of insert statements...
cursor.execute("END;")

I get the following error:

OperationalError: cannot commit - no transaction is active

Which I understand is because SQLite in Python automatically opens a transaction on each modifying statement, which in this case is an INSERT.

Question:

I am trying to speed my insertion by doing one transaction per several thousand records. How can I overcome the automatic opening of transactions?

Upvotes: 4

Views: 8884

Answers (2)

pancakes
pancakes

Reputation: 712

As @CL. said you have to set isolation level to None. Code example:

s = sqlite3.connect("./data.db")
s.isolation_level = None

try:
    c = s.cursor()
    c.execute("begin")
    ...
    c.execute("commit")
except:
    c.execute("rollback")

Upvotes: 13

CL.
CL.

Reputation: 180010

The documentaton says:

You can control which kind of BEGIN statements sqlite3 implicitly executes (or none at all) via the isolation_level parameter to the connect() call, or via the isolation_level property of connections.

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

Upvotes: 3

Related Questions