Python/SQLite3: cannot commit - no transaction is active
Solution 1
Well, as it often happens I found the solution just a minutes after posing the question.
The solution was found here and consists of the only idea:
Never use BEGIN/COMMIT in non-autocommit mode in Python application - use db.commit() and db.rollback() only!
It sounds odd, but it works.
Solution 2
This is a pretty late response, but perhaps take a look at APSW if you want finer-grain control over transactions. I ran a few tests on deferred transactions involving reads on pysqlite, and it just doesn't seem to perform correctly.
Solution 3
cursor=connection.cursor()
cursor.executemany("insert into person(firstname, lastname) values (?, ?)", persons)
connection.commit()
Comments
-
jno almost 3 years
I'm trying to code a book indexer using Python (traditional, 2.7) and SQLite (3).
The code boils down to this sequence of SQL statements:
'select count(*) from tag_dict' () /* [(30,)] */ 'select count(*) from file_meta' () /* [(63613,)] */ 'begin transaction' () 'select id from archive where name=?' ('158326-158457.zip',) /* [(20,)] */ 'select id from file where name=? and archive=?' ('158328.fb2', 20) /* [(122707,)] */ 'delete from file_meta where file=?' (122707,) 'commit transaction' () # error: cannot commit - no transaction is active
The isolation level is 'DEFERRED' ('EXCLUSIVE' is no better).
I've attempted to use connection.commit() instead of cursor.execute('commit') - nothing useful happened.
- Sure, I've searched stackoverflow and the Net, but the answers found are irrelevant.
- Autocommit mode is unacceptable for performance reason.
- I use the only database file at a time.
- My code runs in single thread.
- All the SQL execution is being done via single function that ensures that I have no more than only one cursor open at a time.
So, what's wrong with transaction here?
If I use connection.commit() (note: there is no connection.begin method!), then I merely lose my data.
Sure, I've double/triple/d checked file permissions on the database file and its directory.
-
jno over 12 yearsWell, it's anwered now. I'll be able to accept it tomorrow.
-
Top-Master over 5 yearsgot something like that in
Qt
and had to useQSqlDatabase::transaction()
instead raw-query