sqlite3通过SQLAlchemy“在删除限制”中未被授予，但在sqlite3中工作良好
在sqlite3和SQLAlchemy中，我遇到了一个问题，在这里，我的外键是用带有ondelete=“限制”的ForeignKey创建的，每个会话都执行"PRAGMA foreign_keys = on"，但是SQLAlchemy仍然允许我使一个孩子成为孤儿。试图删除sqlite3本身中的同一行将正确引发外键错误。
这是我的SQLAlchemy模型：
class User(Base):
__tablename__ = 'users'
username = Column(String, nullable=False, unique=True, primary_key=True)
account_type = Column(String, ForeignKey("account_types.name", ondelete="RESTRICT"), nullable=False)
class AccountType(Base):
__tablename__ = 'account_types'
name = Column(String, nullable=False, unique=True, primary_key=True)
description = Column(String)现在，我打开到数据库的连接并执行以下操作：
engine = create_engine("sqlite3:///mydatabase")
Session = sessionmaker(bind=engine)
session = Session()
session.execute("PRAGMA foreign_keys = on")
# This gives me the expected result:
# I can't add a user which references an account type I haven't
# created yet
new_user = User(username="abc123",
account_type="admin") # "admin" doesn't exist yet
session.add(new_user)
with pytest.raises(sqlalchemy.exc.IntegrityError):
session.commit()
session.rollback()
new_account_type = AccountType(name="admin",
description="blah blah blah")
session.add(new_account_type)
session.commit()
# Now add our new user (Horray, this now works!)
session.add(new_user)
session.commit()
# Now let's try to delete the account type
# This should raise an integrity error, but it doesn't!
# SELECT * FROM account_types and SELECT * FROM users
# show that the user still exists, but the account type doesn't
session.query(AccountType).filter_by(name="admin").delete()
session.commit()现在，将SQLAlchemy从等式中删除，清除表，然后手动插入/删除该数据，sqlite3将遵守“”：
sqlite> PRAGMA foreign_keys = on;
sqlite> DELETE FROM users;
sqlite> DELETE FROM account_types;
sqlite> .schema users
CREATE TABLE users (
username VARCHAR NOT NULL,
account_type VARCHAR NOT NULL,
PRIMARY KEY (username),
UNIQUE (username),
FOREIGN KEY(account_type) REFERENCES account_types (name) ON DELETE RESTRICT
);
sqlite> .schema account_types
CREATE TABLE account_types (
name VARCHAR NOT NULL,
description VARCHAR,
PRIMARY KEY (name),
UNIQUE (name)
);
sqlite> INSERT INTO account_types VALUES ("admin", "blah blah blah");
sqlite> INSERT INTO users VALUES ("abc123", "admin");
sqlite> DELETE FROM account_types WHERE name = "admin";
Error: FOREIGN KEY constraint failed=====================================================
为什么SQLAlchemy (1.0.12)不尊重sqlite3?中的"ON删除限制“约束
如果重要的话，表(和约束)都是通过SQLAlchemy生成的：
engine = create_engine("sqlite3:///mydatabase")
Base.metadata.create_all(engine)回答 1
Stack Overflow用户
发布于 2016-05-04 18:48:57
我在发帖前花了大约两个小时的时间，然后在发帖大约一个小时后，我想出了办法。这是任何其他孤独的SQLAlchemy新手现在或几年后到达这个页面的答案。"session.rollback()“是”取消“我以前执行的"PRAGMA foreign_keys = on”。
我从以前的一些内部源代码中提取了设置会话的方法，所以我假设它是可以的。手册详细列出了如何在http://docs.sqlalchemy.org/en/latest/dialects/sqlite.html#foreign-key-support中处理这些问题。
简言之，我补充说：
from sqlalchemy import event
from sqlalchemy.engine import Engine
@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()我的模块，其中包含了我的所有模型，它只是“工作”。不需要以任何方式调用此方法。现在我的外键约束一直受到尊重，甚至是回滚。
https://stackoverflow.com/questions/37034264
复制相似问题
腾讯云开发者
