Sqlite/SQLAlchemy:如何强制执行外键?

2022-01-20 00:00:00 python sqlalchemy foreign-keys sqlite

新版本的SQLite可以强制执行外键约束，但是为了向后兼容，你必须为每个数据库连接单独开启它！

The new version of SQLite has the ability to enforce Foreign Key constraints, but for the sake of backwards-compatibility, you have to turn it on for each database connection separately!

sqlite> PRAGMA foreign_keys = ON;

我正在使用 SQLAlchemy——如何确保它始终处于开启状态?我试过的是这样的:

I am using SQLAlchemy -- how can I make sure this always gets turned on? What I have tried is this:

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

...但它不起作用！...我错过了什么?

...but it is not working!...What am I missing?

我认为我真正的问题是我安装了多个版本的 SQLite，而 Python 没有使用最新的版本！

I think my real problem is that I have more than one version of SQLite installed, and Python is not using the latest one! 

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

但是我刚下载了3.6.23，把exe放到我的项目目录下！如何确定它正在使用哪个 .exe 并进行更改?

But I just downloaded 3.6.23 and put the exe in my project directory! How can I figure out which .exe it's using, and change it?

推荐答案

我现在有这个工作:

如上所述下载最新的 sqlite 和 pysqlite2 构建:确保 python 在运行时使用正确的版本.

Download the latest sqlite and pysqlite2 builds as described above: make sure correct versions are being used at runtime by 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:

Next add a 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 对我有帮助.

Then be careful how you test if foreign keys are working: I had some confusion here. When using sqlalchemy ORM to add() things my import code was implicitly handling the relation hookups so could never fail. Adding nullable=False to some ForeignKey() statements helped me here.

我测试启用了 sqlalchemy sqlite 外键支持的方法是从声明性 ORM 类中手动插入:

The way I test sqlalchemy sqlite foreign key support is enabled is to do a manual insert from a declarative ORM class:

# 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() 的，如果尝试连接无效的 fkey，sqlite 现在会正确抛出异常.所以它有效！如果你删除了监听器，那么 sqlalchemy 会很乐意添加无效条目.

Here wall_id and type_id are both ForeignKey()'s and sqlite throws an exception correctly now if trying to hookup invalid fkeys. So it works! If you remove the listener then sqlalchemy will happily add invalid entries.

我认为主要问题可能是多个 sqlite3.dll(或 .so)存在.

I believe the main problem may be multiple sqlite3.dll's (or .so) lying around.

相关文章