python Sqlite/SQLAlchemy：如何强制使用外键？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2614984/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Sqlite / SQLAlchemy: how to enforce Foreign Keys?
提问by Nick Perkins
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 具有强制外键约束的能力，但为了向后兼容，您必须为每个数据库连接单独打开它！
sqlite> PRAGMA foreign_keys = ON;
I am using SQLAlchemy -- how can I make sure this always gets turned on? What I have tried is this:
我正在使用 SQLAlchemy——我怎样才能确保它总是被打开？我试过的是这样的：
engine = sqlalchemy.create_engine('sqlite:///:memory:', echo=True)
engine.execute('pragma foreign_keys=on')
...but it is not working!...What am I missing?
...但它不起作用！...我错过了什么？
EDIT:I think my real problem is that I have more than one version of SQLite installed, and Python is not using the latest one!
编辑：我认为我真正的问题是我安装了不止一个版本的 SQLite，而 Python 没有使用最新版本！
>>> import sqlite3
>>> print sqlite3.sqlite_version
3.3.4
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?
但是我刚下载了3.6.23，把exe放在我的项目目录下！我怎样才能弄清楚它正在使用哪个 .exe 并更改它？
采纳答案by CarlS
I now have this working:
我现在有这个工作：
Download the latest sqlite and pysqlite2 builds as described above: make sure correct versions are being used at runtime by python.
如上所述下载最新的 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
Next add a PoolListener:
接下来添加一个 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()])
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=Falseto some ForeignKey()statements helped me here.
然后小心你如何测试外键是否有效：我在这里有些困惑。当对add()事物使用 sqlalchemy ORM 时，我的导入代码隐式处理关系连接，因此永远不会失败。添加nullable=False一些ForeignKey()陈述对我有帮助。
The way I test sqlalchemy sqlite foreign key support is enabled is to do a manual insert from a declarative ORM class:
我测试启用 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)
Here wall_idand type_idare 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.
这里wall_id和type_id都是ForeignKey()的，如果试图联播无效fkeys sqlite的正确，现在抛出异常。所以它有效！如果您删除侦听器，那么 sqlalchemy 将很乐意添加无效条目。
I believe the main problem may be multiple sqlite3.dll's (or .so) lying around.
我相信主要问题可能是多个 sqlite3.dll（或 .so）存在。
回答by conny
For recent versions (SQLAlchemy ~0.7) the SQLAlchemy homepagesays:
对于最新版本（SQLAlchemy ~0.7），SQLAlchemy 主页说：
PoolListener is deprecated. Please refer to PoolEvents.
不推荐使用 PoolListener。请参阅PoolEvents。
Then the example by CarlS becomes:
然后 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)
回答by Kiran Jonnalagadda
Building on the answers from conny and shadowmatter, here's code that will check if you are using SQLite3 before emitting the PRAGMA statement:
基于 conny 和 shadowmatter 的答案，这里的代码将在发出 PRAGMA 语句之前检查您是否使用 SQLite3：
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()
回答by shadowmatter
From the SQLite dialect page:
SQLite supports FOREIGN KEY syntax when emitting CREATE statements for tables, however by default these constraints have no effect on the operation of the table.
Constraint checking on SQLite has three prerequisites:
- At least version 3.6.19 of SQLite must be in use
- The SQLite libary must be compiled without the SQLITE_OMIT_FOREIGN_KEY or SQLITE_OMIT_TRIGGER symbols enabled.
- The PRAGMA foreign_keys = ON statement must be emitted on all connections before use.
SQLAlchemy allows for the PRAGMA statement to be emitted automatically for new connections through the usage of events:
SQLite 在为表发出 CREATE 语句时支持 FOREIGN KEY 语法，但是默认情况下这些约束对表的操作没有影响。
SQLite 的约束检查有三个先决条件：
- 必须至少使用 SQLite 3.6.19 版本
- 必须在未启用 SQLITE_OMIT_FOREIGN_KEY 或 SQLITE_OMIT_TRIGGER 符号的情况下编译 SQLite 库。
- PRAGMA foreign_keys = ON 语句必须在使用前在所有连接上发出。
SQLAlchemy 允许通过使用事件为新连接自动发出 PRAGMA 语句：
from sqlalchemy.engine import Engine
from sqlalchemy import event
@event.listens_for(Engine, "connect")
def set_sqlite_pragma(dbapi_connection, connection_record):
cursor = dbapi_connection.cursor()
cursor.execute("PRAGMA foreign_keys=ON")
cursor.close()
回答by ncoghlan
As a simpler approach if your session creation is centralised behind a Python helper function (rather than exposing the SQLA engine directly), you can just issue session.execute('pragma foreign_keys=on')before returning the freshly created session.
如果您的会话创建集中在 Python 辅助函数后面（而不是直接公开 SQLA 引擎），作为一种更简单的方法，您可以session.execute('pragma foreign_keys=on')在返回新创建的会话之前发出。
You only need the pool listener approach if arbitrary parts of your application may create SQLA sessions against the database.
如果您的应用程序的任意部分可能针对数据库创建 SQLA 会话，您只需要池侦听器方法。
回答by serge_gubenko
I had the same problem before (scripts with foreign keys constraints were going through but actuall constraints were not enforced by the sqlite engine); got it solved by:
我之前也遇到过同样的问题（带有外键约束的脚本正在通过，但实际上 SQL 引擎没有强制执行约束）；通过以下方式解决：
downloading, building and installing the latest version of sqlite from here: sqlite-sqlite-amalgamation; before this I had sqlite 3.6.16 on my ubuntu machine; which didn't support foreign keys yet; it should be 3.6.19 or higher to have them working.
installing the latest version of pysqlite from here: pysqlite-2.6.0
从这里下载、构建和安装最新版本的 sqlite：sqlite-sqlite-amlgamation；在此之前，我的 ubuntu 机器上有 sqlite 3.6.16；尚不支持外键；让它们工作应该是 3.6.19 或更高版本。
从这里安装最新版本的 pysqlite：pysqlite-2.6.0
after that I started getting exceptions whenever foreign key constraint failed
之后，每当外键约束失败时，我就开始收到异常
hope this helps, regards
希望这有帮助，问候
回答by Ants Aasma
If you need to execute something for setup on every connection, use a PoolListener.
如果您需要在每个连接上执行某些设置，请使用PoolListener。
