如何使用SQLAlchemy将外键约束正确添加到SQLite DB [英] How to correctly add Foreign Key constraints to SQLite DB using SQLAlchemy

问题描述

我对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:

That translates into the following DDLs:

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:

• 至少必须使用SQLite 3.6.19版本
• 必须在未启用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的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！