怎么使用 SQLAlchemy 正确地将外键约束添加到 SQLite DB
本教程将介绍如何使用 SQLAlchemy 正确地将外键约束添加到 SQLite DB的处理方法，这篇教程是从别的地方看到的，然后加了一些国外程序员的疑问与解答，希望能对你有所帮助，好了，下面开始学习吧。
问题描述
我对 SQLAlchemy 很陌生，我正在努力弄明白.
请记住以下测试设置:
class Nine(Base):
__tablename__ = 'nine'
__table_args__ = (sqlalchemy.sql.schema.UniqueConstraint('nine_b', name='uq_nine_b'), )
nine_a = sqlalchemy.Column(sqlalchemy.dialects.sqlite.INTEGER(), primary_key=True, autoincrement=False, nullable=False)
nine_b = sqlalchemy.Column(sqlalchemy.String(20), nullable=False)
class Seven(Base):
__tablename__ = 'seven'
__table_args__ = (sqlalchemy.sql.schema.PrimaryKeyConstraint('seven_a', 'seven_b'),
sqlalchemy.sql.schema.Index('fk_seven_c_nine_a_idx', 'seven_c'),)
seven_a = sqlalchemy.Column(sqlalchemy.dialects.sqlite.INTEGER(), nullable=False)
seven_b = sqlalchemy.Column(sqlalchemy.dialects.sqlite.INTEGER(), nullable=False)
seven_c = sqlalchemy.Column(sqlalchemy.dialects.sqlite.INTEGER(), sqlalchemy.ForeignKey('nine.nine_a'), nullable=False)
seven_d = sqlalchemy.Column(sqlalchemy.dialects.sqlite.INTEGER(), nullable=False)
nine = sqlalchemy.orm.relationship(Nine, backref=sqlalchemy.orm.backref('seven'), uselist=False)
class Three(Base):
__tablename__ = 'three'
__table_args__ = (sqlalchemy.sql.schema.UniqueConstraint('three_b', 'three_c', name='uq_three_b_c'),
sqlalchemy.sql.schema.Index('fk_three_c_seven_a_idx', 'three_c'), )
three_a = sqlalchemy.Column(sqlalchemy.dialects.sqlite.INTEGER(), primary_key=True, autoincrement=True, nullable=False)
three_b = sqlalchemy.Column(sqlalchemy.dialects.sqlite.INTEGER(), nullable=False)
three_c = sqlalchemy.Column(sqlalchemy.dialects.sqlite.INTEGER(), sqlalchemy.ForeignKey('seven.seven_a'), nullable=False)
seven = sqlalchemy.orm.relationship(Seven, backref=sqlalchemy.orm.backref('three'), uselist=False)
转换为以下 DDL:
CREATE TABLE nine (
nine_a INTEGER NOT NULL,
nine_b VARCHAR(20) NOT NULL,
PRIMARY KEY (nine_a),
CONSTRAINT uq_nine_b UNIQUE (nine_b)
);
CREATE TABLE seven (
seven_a INTEGER NOT NULL,
seven_b INTEGER NOT NULL,
seven_c INTEGER NOT NULL,
seven_d INTEGER NOT NULL,
PRIMARY KEY (seven_a, seven_b),
FOREIGN KEY(seven_c) REFERENCES nine (nine_a)
);
CREATE INDEX fk_seven_c_nine_a_idx ON seven (seven_c);
CREATE TABLE three (
three_a INTEGER NOT NULL,
three_b INTEGER NOT NULL,
three_c INTEGER NOT NULL,
PRIMARY KEY (three_a),
CONSTRAINT uq_three_b_c UNIQUE (three_b, three_c),
FOREIGN KEY(three_c) REFERENCES seven (seven_a)
);
CREATE INDEX fk_three_c_seven_a_idx ON three (three_c);
所有表格都是空的.然后，如下代码语句:
session.add(Nine(nine_a=1, nine_b='something'))
session.add(Nine(nine_a=2, nine_b='something else'))
session.commit()
session.add(Seven(seven_a=7, seven_b=7, seven_c=7, seven_d=7))
session.commit()
session.add(Three(three_a=3, three_b=3, three_c=3))
sessionDB.commit()
谁能解释一下为什么上面的代码片段执行没有错误?FK 约束不应该停止在 seven 或 three 中插入新行吗?我认为在类本身中描述 FK 的方式有问题，但我不知道问题出在哪里(以及怎么解决它).
为所有类添加 __table_args__(忘记包含它们).
添加 DDL 以供进一步参考.
推荐答案
SQLite 默认不强制 ForeignKey 约束(见这里 http://www.sqlite.org/pragma.html#pragma_foreign_keys)
要启用，请在此处遵循以下文档:http://docs.sqlalchemy.org/en/latest/dialects/sqlite.html#foreign-key-support
这是官方文档的复制粘贴:
SQLite 在为表发出 CREATE 语句时支持 FOREIGN KEY 语法，但是默认情况下，这些约束对表的操作没有影响.
对 SQLite 的约束检查具有三个先决条件:
必须使用至少 3.6.19 版的 SQLite
必须在未启用 SQLITE_OMIT_FOREIGN_KEY 或 SQLITE_OMIT_TRIGGER 符号的情况下编译 SQLite 库.
PRAGMA foreign_keys = ON 语句必须在使用前在所有连接上发出.SQLAlchemy 允许通过使用事件为新连接自动发出 PRAGMA 语句:
At least version 3.6.19 of SQLite must be in use
The SQLite library 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:
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()
好了关于怎么使用 SQLAlchemy 正确地将外键约束添加到 SQLite DB的教程就到这里就结束了，希望趣模板源码网找到的这篇技术文章能帮助到大家，更多技术教程可以在站内搜索。
