How to use PRAGMA with sqlite3's new autocommit attribute in Python?

Question

Python's sqlite3 module recently introduced the autocommit attribute, which the docs recommend setting to False. I'd like to use this recommended setting for a new project using Python 3.12. However, I'd also like to enforce foreign key constraints in my database. I'm aware that this required setting connection.execute("PRAGMA foreign_keys = ON;") for every connection. This works when using the default option for autocommit, which is LEGACY_TRANSACTION_CONTROL. Using autocommit=False (as recommended), the foreign key constraints are no longer enforced. If I understand correctly, this is because the PRAGMA only works if no transaction is active, but autocommit=False always implicitly opens a transaction, as explained in the docs.

I found that the following seems to work:

con = sqlite3.connect(path, autocommit=True)
con.execute("PRAGMA foreign_keys = ON;")
con.autocommit = False

Now my question is: Is this intended behavior from sqlite3 or a bug? If it's intended, is the above workaround the best way to solve this and are there any hidden consequences in doing so, that result in different behavior compared to setting autocommit=False immediately in the connect() call? I'm surprised none of this seems to be documented anywhere, I'd assume foreign key checks are quite commonly desired...

Here's a full minimum working example of all of this:

import sqlite3

# con = sqlite3.connect(":memory:", autocommit=sqlite3.LEGACY_TRANSACTION_CONTROL)  # PRAGMA works
# con = sqlite3.connect(":memory:", autocommit=False)  # PRAGMA doesn't work
con = sqlite3.connect(":memory:", autocommit=True)  # PRAGMA works, but need to set autocommit=False afterwards
con.execute("PRAGMA foreign_keys = ON;")
con.autocommit = False

# Super basic two-table example...
con.execute("CREATE TABLE foo(id INTEGER PRIMARY KEY, baz TEXT UNIQUE)")
con.execute("CREATE TABLE bar(id INTEGER PRIMARY KEY, bazid INTEGER UNIQUE,"
            "FOREIGN KEY (bazid) REFERENCES foo (id))")

# Insert some values so a foreign key exists
with con:
    con.execute("INSERT INTO foo(baz) VALUES(?)", ("spam",))
    con.execute("INSERT INTO foo(baz) VALUES(?)", ("eggs",))

# This should pass
with con:
    con.execute("INSERT INTO bar(bazid) VALUES(?)", ("1",))

# This should fail
with con:
    con.execute("INSERT INTO bar(bazid) VALUES(?)", ("42",))

con.close()

Answer 1

I stumbled upon the same problem and found several workarounds.

Changing autocommit attribute

I found that modifying the autocommit attribute is documented behavior. The CPython documentation indirectly indicates this by explaining the behavior of Connection when the attribute is changed.

Changing autocommit to False will open a new transaction, and changing it to True will commit any pending transaction. https://docs.python.org/3/library/sqlite3.html#sqlite3.Connection.autocommit

So, you can set autocommit to True, execute PRAGMA statements, and then set it to False just like the following script:

from contextlib import closing
import sqlite3

with closing(sqlite3.connect("database.sqlite3", autocommit=True)) as conn:
    conn.executescript("PRAGMA journal_mode=wal")
    conn.executescript("PRAGMA foreign_keys=on")
    conn.autocommit = False

    with closing(conn.cursor()) as cur:
        ...

It's a bit disappointing that the official documentation doesn't provide explicit examples of how to manage PRAGMA statements in autocommit=False connections.

More workarounds

There are several additional workarounds you can use, although they are quite hacky.

First, you can execute PRAGMA statements on a temporary connection, close the connection, and then open a new connection with autocommit=False.

from contextlib import closing
import sqlite3

with closing(sqlite3.connect("database.sqlite3")) as temp:
    temp.execute("PRAGMA journal_mode=wal")

with closing(sqlite3.connect("database.sqlite3", autocommit=False)) as conn:
    ...

However, this trick doesn't work for transient in-memory (:memory:) databases.

Moreover, not every PRAGMA statement is saved to the database. Some PRAGMA statements, like PRAGMA foreign_keys, need to be set per connection.

For those statements, you can use .executescript() to close a pending transaction, execute PRAGMA statements, and reopen a transaction.

from contextlib import closing
import sqlite3

with closing(sqlite3.connect("database.sqlite3", autocommit=False)) as conn:
    conn.executescript("COMMIT; PRAGMA foreign_keys=on; BEGIN;")

It works but feels hacky. Usually, you would execute statements with .execute() and control transactions with Connection.commit() or .rollback(), but this workaround requires using .executescript() and the COMMIT statement.

Maybe we could add a new method Connection.execute_pragma(), for instance, where PRAGMA statements can be executed reliably regardless of the state of the connection. But such a method does not exist.