Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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?

like image 261
Georgi Angelov Avatar asked Dec 12 '22 05:12

Georgi Angelov


1 Answers

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")
like image 183
pancakes Avatar answered Jan 05 '23 01:01

pancakes