Sqlite / SQLAlchemy：如何执行外键？ [英] Sqlite / SQLAlchemy: how to enforce Foreign Keys?

问题描述

新版本的SQLite有强制外键约束的能力，但是为了向后兼容，你必须分别为每个数据库连接打开它！

 源码> PRAGMA foreign_keys = ON; 
  

我正在使用SQLAlchemy - 如何确保始终打开？
我试过的是：
$ b $ pre $引擎= sqlalchemy.create_engine（'sqlite：///：memory： '，echo = True）
engine.execute（'pragma foreign_keys = on'）

...但它不工作！...我缺少什么？

编辑：
我认为我真正的问题是我已经安装了多个版本的SQLite，并且Python没有使用最新版本！

 >>> import sqlite3 
>>>打印sqlite3.sqlite_version 
 3.3.4 
  

但是我只是下载了3.6.23， exe文件在我的项目目录！
我怎样才能找出它正在使用哪个.exe，并改变它？

解决方案

我现在有这个工作：

按照上面的描述下载最新的sqlite和pysqlite2版本：确保正确的版本在运行时被python使用。

  import sqlite3 
 import pysqlite2 
 print sqlite3.sqlite_version＃应该是3.6.23.1 
 print pysqlite2 .__ path__＃eg C：\\ Python26 \\lib\\site-packages\\pysqlite2 
  

接下来添加一个PoolListener：

  from sqlalchemy.interfaces import PoolListener 
类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（）]）
  

然后小心如何测试forei键正在工作：我在这里有一些困惑。当使用sqlalchemy ORM添加（）事物时，我的导入代码隐式地处理关系连接，因此永远不会失败。在一些ForeignKey（）语句中添加'nullable = False'帮助我在这里。

我测试sqlalchemy sqlite外键支持的方式是从一个手动插入声明式的ORM类：

$ p $ $ $ $ $ $ $ $
description ='Wrong'，
area = 42.0，
wall_id = 99，＃invalid fkey id
type_id = 99）＃无效fkey_id
session.execute ）

这里'wall_id'和'type_id'都是ForeignKey（）和sqlite引发的异常正确地现在如果试图连接无效的fkeys。所以它的作品！如果你删除了侦听器，那么sqlalchemy会高兴地添加无效的条目。

我相信主要的问题可能是多个sqlite3.dll（或.so）。 >

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;

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?

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!

>>> 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?

解决方案

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.

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:

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=False' to some ForeignKey() statements helped me here.

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) 

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.

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

这篇关于Sqlite / SQLAlchemy：如何执行外键？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！