search

coleifer / pysqlite3

SQLite3 DB-API 2.0 driver from Python 3, packaged separately, with improvements
zlib License
183 stars 51 forks source link

Cannot change `journal_mode`: cannot commit transaction - SQL statements in progress #41

Closed puddly closed 2 years ago

puddly commented 2 years ago

Before I begin with the issue, I want to thank you for this awesome package. Being able to use a current SQLite version on common platforms is very useful!

Here is a simple example to illustrate the problem:

#import sqlite3
import pysqlite3 as sqlite3

with sqlite3.connect(":memory:") as connection:
    cursor = connection.cursor()
    cursor.execute("PRAGMA journal_mode = TRUNCATE")

When run with the built-in sqlite3 module, the script executes normally. When run with pysqlite3-binary==0.4.6, it fails:

      4 with sqlite3.connect(":memory:") as connection:
      5     cursor = connection.cursor()
----> 6     cursor.execute("PRAGMA journal_mode = TRUNCATE")

OperationalError: cannot commit transaction - SQL statements in progress

I've tried executing COMMIT and BEGIN + COMMIT a few times beforehand but it doesn't make a difference.

Is there some default connection option that I am forgetting that would cause this behavior?

coleifer commented 2 years ago

This has to do, I believe, with an open statement in the sqlite statement cache. The better thing to do for setting the journal mode in this case is to write the following:

In [1]: from pysqlite3 import dbapi2 as sqlite3

In [2]: with sqlite3.connect('/tmp/foo.db') as conn:
   ...:     conn.execute('pragma journal_mode=truncate;')
   ...:
coleifer commented 2 years ago

Alternatively you can explicitly close your cursor when you're done with it:

In [2]: from pysqlite3 import dbapi2 as sqlite3

In [3]: with sqlite3.connect('/tmp/foo.db') as conn:
   ...:     curs = conn.cursor()
   ...:     curs.execute('pragma journal_mode=truncate;')
   ...:     curs.close()
   ...:

The above works just fine.

puddly commented 2 years ago

Thanks for the help. I was able to work around this by temporarily setting conn.isolation_level = None:

import pysqlite3.dbapi2 as sqlite3

with sqlite3.connect(":memory:") as connection:
    connection.isolation_level = None

    cursor = connection.cursor()
    cursor.execute("PRAGMA journal_mode = TRUNCATE")

    connection.isolation_level = "DEFERRED"
coleifer commented 2 years ago

Out of curiosity, what python version are you running where the stdlib sqlite3 does not exhibit the problem?

puddly commented 2 years ago

The original code I posted but with the stdlib sqlite3 module works on every platform I have access to: