Python中sqlite3新autocommit属性下如何正确使用PRAGMA开启外键约束？

问题背景 #

Python 3.12的sqlite3模块新增了autocommit属性，官方文档推荐将其设置为False。但在项目中使用该推荐配置时，发现执行connection.execute("PRAGMA foreign_keys = ON;")无法正常开启外键约束。原因在于autocommit=False会隐式开启事务，而SQLite的PRAGMA语句需要在无事务状态下执行才能生效，这和默认的LEGACY_TRANSACTION_CONTROL行为存在差异。

用户找到的可行临时解决方案：

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

现针对以下问题解答：

• 此为sqlite3的预期行为还是bug？
• 若为预期行为，该临时方案是否为最优解？
• 与直接在connect()中设autocommit=False相比，是否存在隐藏差异？

解答 #

1. 这是预期行为，并非bug #

autocommit=False遵循PEP-249数据库API规范，连接建立后会立即启动一个隐式事务。而SQLite的PRAGMA foreign_keys属于会话级配置，仅在无事务状态下执行时，设置才会应用到整个连接会话；如果在事务内执行，该设置仅对当前事务有效，事务提交/回滚后就会失效。

而旧的LEGACY_TRANSACTION_CONTROL模式不会自动开启事务，所以PRAGMA语句能直接生效，这是新旧事务控制逻辑的正常差异，并非bug。

2. 该临时方案是合理方案，也属于最优解之一 #

除了用户的方法，还有两种常用替代方案：

• 方案一：提交隐式事务
  直接用autocommit=False连接，执行PRAGMA后手动提交事务，让设置生效：

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

• 方案二：URI参数直接配置
  开启URI模式，通过连接字符串直接指定外键约束：

  con = sqlite3.connect("file::memory:?foreign_keys=on", uri=True, autocommit=False)
  

用户的临时方案优势在于直观易懂，不需要修改连接字符串格式，兼容性好；URI方案则更简洁，适合一次性配置的场景，可根据自己的习惯选择。

3. 与直接设autocommit=False的隐藏差异 #

• 初始事务状态：用户的方法先以autocommit=True启动，此时无隐式事务，执行PRAGMA后再关闭autocommit才会开启第一个隐式事务；直接设autocommit=False的连接，建立后立即启动隐式事务。
• PRAGMA生效范围：用户的方法中PRAGMA在无事务状态执行，设置会持续生效到连接关闭；直接设autocommit=False时，PRAGMA在事务内执行，仅对当前事务有效，事务结束后外键约束会恢复默认状态（通常为OFF）。
• 事务启动时机：用户的方法中，第一个事务的启动节点是设置autocommit=False之后；直接配置的连接则从建立起就处于事务中，后续所有未提交的操作都在这个初始事务内。

完整验证示例代码 #

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

内容的提问来源于stack exchange，提问作者VY_CMa