Python SQLite - How to manually BEGIN and END transactions?

Question

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?

score 12 · Accepted Answer · answered Jun 23 '14 at 20:06

12

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

answered Jun 23 '14 at 20:06

pancakes

682
5
8

3

I've a question about this statement: "If you want autocommit mode, then set isolation_level to None.", does it mean, after we set the isolation_level as None, any kind of DML operation will be autocommit? So the performance couldn't be improved. Is my understanding is right? I jump here from this URL: http://stackoverflow.com/questions/17867605/performance-of-insert-with-python-and-sqlite3 – Clock ZHONG Mar 16 '17 at 06:50
1

I have the same concern as @ClockZHONG. Based on some debugging I have done, I think that the documentation is misleading, and what it really should say is `If you want to manually handle transactions, then set isolation_level to None`. – Alvaro Gutierrez Perez Nov 12 '17 at 17:38
2

I found [this on the official SQLite documentation](https://www.sqlite.org/c3ref/get_autocommit.html) that clarifies what the python documentation was referring to by "autocommit". It seems that if no BEGIN statement is used, all statements execute in "autocommit" mode. So, as far as we enclose our statements with BEGIN/COMMIT, there is no such autocommit when isolation_level is set to None. Hope that helps. – Alvaro Gutierrez Perez Nov 12 '17 at 17:53

score 3 · Answer 2 · answered Jun 23 '14 at 19:59

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.

Python SQLite - How to manually BEGIN and END transactions?

2 Answers2

Linked

Related