如何使用SQLAlchemy正确地将外键约束添加到SQLite DB

概述我对SQLAlchemy很新,我想弄明白. 请记住以下测试设置： class Nine(Base): __tablename__ = 'nine' __table_args__ = (sqlalchemy.sql.schema.UniqueConstraint('nine_b', name='uq_nine_b'), ) nine_a = sqlalchemy.Colum 我对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(),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约束不应该停止将新行插入七或三个？我假设在类本身中如何描述FK有什么问题,但我不知道问题出在哪里(以及如何修复它).

[编辑1]

为所有类添加__table_args__(忘记包含它们).

[编辑2]

添加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语句：

from sqlalchemy.engine import Enginefrom 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全部内容，希望文章能够帮你解决如何使用SQLAlchemy正确地将外键约束添加到SQLite DB所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。