Sqlite / SQLAlchemy：如何强制外键？

Question

SQLite的新版本可以强制执行外键约束，但为了兼容旧版本，你需要为每个数据库连接单独开启这个功能！

sqlite> PRAGMA foreign_keys = ON;

我在使用SQLAlchemy——我该怎么做才能确保这个功能总是开启的呢？我尝试过这个：

engine = sqlalchemy.create_engine('sqlite:///:memory:', echo=True)
engine.execute('pragma foreign_keys=on')

……但它没有效果！我漏掉了什么吗？

编辑：我觉得我真正的问题是我安装了多个版本的SQLite，而Python没有使用最新的那个！

>>> import sqlite3
>>> print sqlite3.sqlite_version
3.3.4

但是我刚下载了3.6.23，并把exe放在我的项目目录里！我怎么才能知道它正在使用哪个.exe，并且怎么更改它呢？

Answer 1

基于conny和shadowmatter的回答，这段代码会检查你是否在使用SQLite3，然后再发出PRAGMA语句：

from sqlalchemy import event
from sqlalchemy.engine import Engine
from sqlite3 import Connection as SQLite3Connection

@event.listens_for(Engine, "connect")
def _set_sqlite_pragma(dbapi_connection, connection_record):
    if isinstance(dbapi_connection, SQLite3Connection):
        cursor = dbapi_connection.cursor()
        cursor.execute("PRAGMA foreign_keys=ON;")
        cursor.close()

Answer 2

对于最近的版本（SQLAlchemy ~0.7），SQLAlchemy官网上说：

PoolListener已经不再推荐使用了。请参考PoolEvents。

然后CarlS的示例变成了：

engine = create_engine(database_url)

def _fk_pragma_on_connect(dbapi_con, con_record):
    dbapi_con.execute('pragma foreign_keys=ON')

from sqlalchemy import event
event.listen(engine, 'connect', _fk_pragma_on_connect)

Answer 3

我现在已经搞定这个了：

首先，下载最新的sqlite和pysqlite2版本，确保在运行时Python使用的是正确的版本。

import sqlite3   
import pysqlite2 
print sqlite3.sqlite_version   # should be 3.6.23.1
print pysqlite2.__path__       # eg C:\\Python26\\lib\\site-packages\\pysqlite2

接下来，添加一个PoolListener：

from sqlalchemy.interfaces import PoolListener
class ForeignKeysListener(PoolListener):
    def connect(self, dbapi_con, con_record):
        db_cursor = dbapi_con.execute('pragma foreign_keys=ON')

engine = create_engine(database_url, listeners=[ForeignKeysListener()])

然后要小心测试外键是否正常工作：我在这方面有点困惑。当我使用sqlalchemy的ORM来add()东西时，我的导入代码会自动处理关系的连接，所以永远不会出错。在一些ForeignKey()语句中添加nullable=False对我帮助很大。

我测试sqlalchemy sqlite外键支持是否开启的方法是，从一个声明式的ORM类手动插入数据：

# example
ins = Coverage.__table__.insert().values(id = 99,
                                    description = 'Wrong',
                                    area = 42.0,
                                    wall_id = 99,  # invalid fkey id
                                    type_id = 99)  # invalid fkey_id
session.execute(ins) 

在这里，wall_id和type_id都是ForeignKey()，如果尝试连接无效的外键，sqlite现在会正确抛出异常。所以它是有效的！如果你移除监听器，sqlalchemy会很乐意地添加无效的条目。

我认为主要问题可能是有多个sqlite3.dll（或.so）文件在周围。