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

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

我对 SQLAlchemy 很陌生，我正在努力弄明白.

I'm very new to SQLAlchemy and I'm trying to figure it out.

请记住以下测试设置:

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);

所有表格都是空的.然后，如下代码语句:

All tables are empty. Then, the following code statements:

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 的方式有问题，但我不知道问题出在哪里(以及如何解决它).

Can somebody please explain why is the above code snippet executing without errors? Should't the FK constraints stop from inserting a new row into seven or three? I assume there is something wrong with how the FKs are described in the classes themselves, but I don't know where the problem is (and how to fix it).

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

Adding __table_args__ for all classes (forgot to include them).

添加 DDL 以供进一步参考.

Adding DDLs for further reference.

推荐答案

SQLite 默认不强制 ForeignKey 约束(见这里 http://www.sqlite.org/pragma.html#pragma_foreign_keys)

SQLite by default does not enforce ForeignKey constraints (see here http://www.sqlite.org/pragma.html#pragma_foreign_keys )

要启用，请在此处遵循以下文档:http://docs.sqlalchemy.org/en/latest/dialects/sqlite.html#foreign-key-support

To enable, follow these docs here: http://docs.sqlalchemy.org/en/latest/dialects/sqlite.html#foreign-key-support

这是官方文档的复制粘贴:

Here's a copy paste of the official documentation:

SQLite 在为表发出 CREATE 语句时支持 FOREIGN KEY 语法，但是默认情况下，这些约束对表的操作没有影响.

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.

对 SQLite 的约束检查具有三个先决条件:

Constraint checking on SQLite has three prerequisites:

  • 必须使用至少 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()

相关文章