在使用sqlalchemy与sqlite时，即使在模型中正确配置了`on delete cascade`外键约束，通过`session.query(...).delete()`执行批量删除操作时，关联数据可能不会被级联删除。这是因为sqlite默认禁用外键约束，需要为每个数据库连接显式开启`pragma foreign_keys=on`。本文将详细介绍此问题的成因及通过sqlalchemy事件监听机制的解决方案。
理解SQLAlchemy与SQLite中的级联删除问题
在使用关系型数据库时，级联删除（ON DELETE CASCADE）是一种强大的机制，它允许在删除父记录时自动删除所有相关的子记录。在SQLAlchemy中，我们可以通过在ForeignKey定义中设置ondelete="CASCADE"，并在relationship中设置cascade="all, delete"和passive_deletes=True来配置这一行为。
考虑以下两个模型：UserOrm 和 ComputerOrm，它们之间存在一对多的关系，一个用户可以拥有多台电脑。
from sqlalchemy import create_engine, Column, Integer, ForeignKey
from sqlalchemy.orm import relationship, sessionmaker, declarative_base, Mapped, mapped_column
from sqlalchemy.engine import Engine
from sqlalchemy import event
# 数据库连接设置
engine = create_engine(
"sqlite:///./database.db",
echo=True, # 开启echo可以查看SQL语句
)
Session = sessionmaker(bind=engine)
Base = declarative_base()
# 用户模型
class UserOrm(Base):
__tablename__ = "users"
id: Mapped[int] = mapped_column(primary_key=True, autoincrement=True)
name: Mapped[str] = mapped_column(nullable=True, default="")
age: Mapped[int] = mapped_column(nullable=True, default=0)
# 定义与ComputerOrm的关系，并设置级联删除
computers = relationship(
"ComputerOrm",
back_populates="host",
cascade="all, delete", # ORM层面的级联操作
passive_deletes=True, # 告知ORM数据库会处理删除，减少ORM的工作量
)
# 电脑模型
class ComputerOrm(Base):
__tablename__ = "computers"
id: Mapped[int] = mapped_column(primary_key=True, autoincrement=True)
name: Mapped[str]
# 定义外键，并设置ON DELETE CASCADE
host_id = Column(Integer, ForeignKey("users.id", ondelete="CASCADE"), index=True, nullable=False)
host = relationship(UserOrm, back_populates="computers")
# 创建所有表
Base.metadata.create_all(engine)
# 示例数据插入（此处省略，假设数据库中已有用户及其电脑数据）
# with Session() as session:
# user1 = UserOrm(name="Alice", age=30)
# computer1 = ComputerOrm(name="LaptopX", host=user1)
# computer2 = ComputerOrm(name="DesktopY", host=user1)
# session.add_all([user1, computer1, computer2])
# session.commit()当尝试使用session.query(UserOrm).filter_by(id=some_id).delete()执行批量删除操作时，用户记录会被删除，但其关联的电脑记录却不会被级联删除。通过SQLAlchemy的echo=True输出可以看到，只执行了一条针对users表的DELETE语句，并没有触发对computers表的级联删除。
DELETE FROM users WHERE users.id = ?
然而，如果在SQLite客户端（如DB Browser for SQLite）中直接执行相同的SQL语句，级联删除却能正常工作：
DELETE FROM users WHERE users.id = some_id;
这表明问题并非出在表结构定义上，因为CREATE TABLE语句中已经明确包含了FOREIGN KEY(host_id) REFERENCES users (id) ON DELETE CASCADE。
问题根源：SQLite的外键约束默认行为
问题的核心在于SQLite数据库的默认行为。为了保持向后兼容性和提供更大的灵活性，SQLite在每个新的数据库连接中默认禁用外键约束。这意味着，即使在表定义中指定了ON DELETE CASCADE，除非显式地为当前连接启用外键，否则这些约束将不会被执行。
要启用SQLite的外键约束，需要在每个数据库连接建立后执行一个特殊的PRAGMA命令：
PRAGMA foreign_keys=ON;
解决方案：通过SQLAlchemy事件监听启用外键
为了确保每次SQLAlchemy建立新的SQLite数据库连接时都能启用外键约束，我们可以利用SQLAlchemy的事件系统。SQLAlchemy允许我们监听引擎的connect事件，并在每次建立新连接时执行自定义操作。
将以下代码添加到你的SQLAlchemy引擎创建之后、会话使用之前：
# 监听Engine的"connect"事件
@event.listens_for(Engine, "connect")
def set_sqlite_pragma(dbapi_connection, connection_record):
"""
为每个新的SQLite连接启用外键约束。
dbapi_connection 是底层DBAPI连接对象。
"""
cursor = dbapi_connection.cursor()
cursor.execute("PRAGMA foreign_keys=ON")
cursor.close()结合前面的模型定义，完整的解决方案代码如下：
from sqlalchemy import create_engine, Column, Integer, ForeignKey
from sqlalchemy.orm import relationship, sessionmaker, declarative_base, Mapped, mapped_column
from sqlalchemy.engine import Engine
from sqlalchemy import event
# 数据库连接设置
engine = create_engine(
"sqlite:///./database.db",
echo=True, # 开启echo可以查看SQL语句
)
Session = sessionmaker(bind=engine)
Base = declarative_base()
# 监听Engine的"connect"事件，为每个新的SQLite连接启用外键约束
@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()
# 用户模型
class UserOrm(Base):
__tablename__ = "users"
id: Mapped[int] = mapped_column(primary_key=True, autoincrement=True)
name: Mapped[str] = mapped_column(nullable=True, default="")
age: Mapped[int] = mapped_column(nullable=True, default=0)
computers = relationship(
"ComputerOrm",
back_populates="host",
cascade="all, delete",
passive_deletes=True,
)
# 电脑模型
class ComputerOrm(Base):
__tablename__ = "computers"
id: Mapped[int] = mapped_column(primary_key=True, autoincrement=True)
name: Mapped[str]
host_id = Column(Integer, ForeignKey("users.id", ondelete="CASCADE"), index=True, nullable=False)
host = relationship(UserOrm, back_populates="computers")
# 创建所有表
Base.metadata.create_all(engine)
# 示例：填充数据并执行删除操作
with Session() as session:
# 清理旧数据，确保测试环境干净
session.query(ComputerOrm).delete()
session.query(UserOrm).delete()
session.commit()
# 插入新数据
user1 = UserOrm(name="Alice", age=30)
user2 = UserOrm(name="Bob", age=25)
session.add_all([user1, user2])
session.commit() # 提交以获取user1和user2的id
computer1 = ComputerOrm(name="Alice's Laptop", host=user1)
computer2 = ComputerOrm(name="Alice's Desktop", host=user1)
computer3 = ComputerOrm(name="Bob's Workstation", host=user2)
session.add_all([computer1, computer2, computer3])
session.commit()
print(f"Initial users count: {session.query(UserOrm).count()}")
print(f"Initial computers count: {session.query(ComputerOrm).count()}")
# 删除用户Alice，预期其关联的电脑也会被删除
alice_id = user1.id
session.query(UserOrm).filter_by(id=alice_id).delete()
session.commit()
print(f"After deleting Alice, users count: {session.query(UserOrm).count()}")
print(f"After deleting Alice, computers count: {session.query(ComputerOrm).count()}")
# 验证是否只剩Bob的电脑
remaining_computers = session.query(ComputerOrm).all()
for comp in remaining_computers:
print(f"Remaining computer: {comp.name} (Host ID: {comp.host_id})")
执行上述代码后，你会发现当删除 UserOrm 实例时，其关联的 ComputerOrm 实例也会被正确级联删除。SQLAlchemy的echo输出现在会显示在DELETE FROM users语句执行后，SQLite内部自动处理了DELETE FROM computers的操作，而无需SQLAlchemy额外发出SQL语句。
注意事项与总结
-
ORM级联与数据库级联：
- relationship中的cascade="all, delete"和passive_deletes=True主要控制SQLAlchemy ORM层面的级联行为。当使用session.delete(some_orm_instance)删除单个对象时，ORM会负责加载关联对象并逐个删除，或者在passive_deletes=True时，告知ORM数据库会处理级联，从而避免加载关联对象。
- ForeignKey中的ondelete="CASCADE"则是在数据库层面定义级联行为。session.query(...).delete()执行的是批量删除，它会直接生成SQL语句并发送给数据库，绕过ORM的Python对象操作，因此它依赖于数据库自身的外键级联功能。
- 对于SQLite，为了使数据库层面的ON DELETE CASCADE生效，必须通过PRAGMA foreign_keys=ON显式启用外键约束。
-
适用范围：
- @event.listens_for(Engine, "connect")的解决方案专门针对SQLite，因为其他数据库（如PostgreSQL、MySQL）默认启用外键约束，不需要此额外配置。
- 此方法确保了所有通过该Engine创建的连接都将启用外键，从而保证了数据库层面的级联删除功能。
通过上述方法，您可以确保在使用SQLAlchemy与SQLite进行开发时，ON DELETE CASCADE外键约束能够按预期工作，从而维护数据完整性并简化删除操作的逻辑。
