SQLAlchemy: 级联删除

Question

我可能在SQLAlchemy的级联选项上漏了一些微不足道的东西，因为我无法使一个简单的级联删除操作正确运行 - 如果删除一个父元素，则子元素会持久化，但外键变为null。

我在这里放了一个简明的测试案例：

from sqlalchemy import Column, Integer, ForeignKey
from sqlalchemy.orm import relationship

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class Parent(Base):
    __tablename__ = "parent"
    id = Column(Integer, primary_key = True)

class Child(Base):
    __tablename__ = "child"
    id = Column(Integer, primary_key = True)
    parentid = Column(Integer, ForeignKey(Parent.id))
    parent = relationship(Parent, cascade = "all,delete", backref = "children")

engine = create_engine("sqlite:///:memory:")
Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)

session = Session()

parent = Parent()
parent.children.append(Child())
parent.children.append(Child())
parent.children.append(Child())

session.add(parent)
session.commit()

print "Before delete, children = {0}".format(session.query(Child).count())
print "Before delete, parent = {0}".format(session.query(Parent).count())

session.delete(parent)
session.commit()

print "After delete, children = {0}".format(session.query(Child).count())
print "After delete parent = {0}".format(session.query(Parent).count())

session.close()

输出：

Before delete, children = 3
Before delete, parent = 1
After delete, children = 3
After delete parent = 0

Parent和Child之间存在一个简单的一对多关系。该脚本创建了一个父对象，并添加了三个子对象，然后提交了事务。接下来，它删除了父对象，但是这些子对象仍然存在。为什么会出现这种情况？如何使孩子对象进行级联删除？

- carl

---

这篇文档中（至少是现在，在原始帖子发布三年后），关于此问题似乎相当有帮助：http://docs.sqlalchemy.org/en/rel_0_9/orm/session.html#cascades - Soferio

我认为在 session.delete(parent) 后面加上 session.commit() 是不必要的。 - NMO

Answer 1

问题在于SQLAlchemy将Child视为父类，因为您在那里定义了关系（当然，它并不关心您将其称为“Child”）。

如果您在Parent类上定义这个关系，它将会生效：

children = relationship("Child", cascade="all,delete", backref="parent")

声明式风格允许使用字符串"Child"，以便引用尚未定义的类。注意不要更改原来的意思。

您可能还想添加delete-orphan，delete会在删除父级时删除其子级，delete-orphan还会删除任何已从父级中“移除”的子级，即使未删除父级也是如此。

编辑：刚刚发现：如果您真的想在Child类上定义关系，则可以这样做，但必须在backref上定义级联操作（显式创建backref），例如：

parent = relationship(Parent, backref=backref("children", cascade="all,delete"))

(意味着 from sqlalchemy.orm import backref)

- Steven

---

啊哈，就是这样。我希望文档能更明确地说明这一点！ - carl

好的，非常有帮助。我一直对SQLAlchemy的文档有问题。 - ayaz

这在当前文档http://docs.sqlalchemy.org/en/rel_0_9/orm/cascades.html中有很好的解释。 - Epoc

@Lyman Zerga：在 OP 的例子中，如果从 parent.children 中删除一个 Child 对象，那么该对象应该从数据库中删除，还是只应该删除它与父对象的引用（即将 parentid 列设置为 null，而不是删除行）？ - Steven

等等，relationship并不决定父子关系的设置。在表上使用ForeignKey才是将其设置为子表。无论relationship在父表还是子表上都没有关系。 - d512

Answer 2

这是一个相当旧的帖子，但我刚刚花了一两个小时在这上面，所以我想分享一下我的发现，特别是因为其他评论中列出的一些并不完全正确。

简而言之：

给子表添加一个外键或修改现有的外键，并添加ondelete='CASCADE'。

parent_id = db.Column(db.Integer, db.ForeignKey('parent.id', ondelete='CASCADE'))

并且以下关系之一：

a）这是父表上的关系：

children = db.relationship('Child', backref='parent', passive_deletes=True)

b) 或者在子表上面这么做：

parent = db.relationship('Parent', backref=backref('children', passive_deletes=True))

---

细节

首先，不管接受的答案如何说，父/子关系的建立并不是通过使用relationship来实现的，而是通过使用ForeignKey来实现的。你可以将relationship放在父表或子表上，都可以正常工作。不过，显然在子表上，除了关键字参数外，还必须使用backref函数。

选项1（首选）

其次，SqlAlchemy支持两种不同类型的级联操作。第一种，也是我推荐的一种，是内置在数据库中的，通常以外键声明的约束形式存在。在PostgreSQL中，它看起来像这样：

CONSTRAINT child_parent_id_fkey FOREIGN KEY (parent_id)
REFERENCES parent_table(id) MATCH SIMPLE
ON DELETE CASCADE

这意味着当你从"parent_table"中删除一条记录时，数据库会自动删除所有对应的"child_table"中的行。这种方式快速可靠，可能是你最好的选择。在SqlAlchemy中，你可以通过"ForeignKey"来设置这个功能，就像这样（在子表定义的一部分）：

parent_id = db.Column(db.Integer, db.ForeignKey('parent.id', ondelete='CASCADE'))
parent = db.relationship('Parent', backref=backref('children', passive_deletes=True))

ondelete='CASCADE'是在表中创建ON DELETE CASCADE的部分。

注意！

这里有一个重要的警告。注意我使用了relationship和passive_deletes=True吗？如果没有这个设置，整个功能将无法正常工作。这是因为默认情况下，当你删除父记录时，SqlAlchemy会执行一些非常奇怪的操作。它将所有子行的外键设置为NULL。所以如果你从parent_table中删除id为5的行，实际上它会执行以下操作：

UPDATE child_table SET parent_id = NULL WHERE parent_id = 5

为什么你会想要这样做我不知道。我会很惊讶如果许多数据库引擎甚至允许你将有效的外键设置为NULL，从而创建一个孤立的行。这似乎是个坏主意，但也许有一些使用案例。无论如何，如果你让SqlAlchemy这样做，你将阻止数据库能够使用你设置的ON DELETE CASCADE来清理子行。这是因为它依赖于这些外键来知道要删除哪些子行。一旦SqlAlchemy将它们全部设置为NULL，数据库就无法删除它们了。通过设置passive_deletes=True，可以防止SqlAlchemy将外键设置为NULL。

你可以在SqlAlchemy文档中了解更多关于被动删除的信息。

第二种方法是让SqlAlchemy为你完成。这是通过relationship的cascade参数进行设置的。如果你在父表上定义了这个关系，它看起来像这样：

children = relationship('Child', cascade='all,delete', backref='parent')

如果关系是在子元素上，你可以这样做：

parent = relationship('Parent', backref=backref('children', cascade='all,delete'))

再次强调，这是子级，所以您必须调用一个名为backref的方法，并将级联数据放在其中。

有了这个设置，当您删除父行时，SqlAlchemy实际上会运行删除语句来清理子行。这可能不如让数据库自己处理高效，所以我不建议使用这种方法。

这里是SqlAlchemy文档中关于级联特性的介绍。

- d512

---

为什么在子表中声明Column为ForeignKey('parent.id', ondelete='cascade', onupdate='cascade')也不起作用呢？我本来期望当父表行被删除时，其子表也会被删除。但是，SQLA要么将子表设置为parent.id=NULL，要么保持原样，但没有删除操作。这是在最初在父表中定义relationship为children = relationship('Parent', backref='parent')或relationship('Parent', backref=backref('parent', passive_deletes=True))之后的情况；DB在DDL中显示了级联规则（基于SQLite3的概念验证）。你有什么想法吗？ - code_dredd

另外，我应该指出当我使用 backref=backref('parent', passive_deletes=True) 时，我会收到以下警告：SAWarning: On Parent.children, 'passive_deletes' is normally configured on one-to-many, one-to-one, many-to-many relationships only. "relationships only." % self，这表明它不喜欢在这个（显然的）一对多的父子关系中使用 passive_deletes=True。 - code_dredd

根据SQLAlchemy的文档，all是以下所有内容的同义词：save-update, merge, refresh-expire, expunge, delete。因此，在cascade='all,delete'中，@zaggi delete是多余的。 - pmsoltani

@pmsoltani 我认为它不是冗余的，因为它在文档中被广泛使用: https://docs.sqlalchemy.org/en/13/orm/cascades.html#using-delete-cascade-with-many-to-many-relationships. - Jérôme

感谢“Gotcha”部分的提醒，正是我被卡住的地方。这个答案很可能值得采纳。 - Jérôme

Answer 3

@Steven的答案对于通过session.delete()删除时很好，但在我的情况下从未发生。我注意到大多数时候我是通过 session.query().filter().delete() 删除的（它不会将元素放入内存并直接从数据库中删除）。使用此方法SQLAlchemy的cascade='all, delete'无效。然而，有一个解决方案：通过db使用ON DELETE CASCADE（注意：并非所有数据库都支持它）。

class Child(Base):
    __tablename__ = "children"

    id = Column(Integer, primary_key=True)
    parent_id = Column(Integer, ForeignKey("parents.id", ondelete='CASCADE'))

class Parent(Base):
    __tablename__ = "parents"

    id = Column(Integer, primary_key=True)
    child = relationship(Child, backref="parent", passive_deletes=True)

- Alex Okrushko

---

谢谢您解释这个差异 - 我一直在尝试使用session.query().filter().delete()，但一直找不到问题所在。 - nighthawk454

为了使父对象被删除时数据库能够级联删除其子对象，我需要设置passive_deletes='all'。当passive_deletes=True时，在父对象被删除之前，子对象会被取消关联（即将其父属性设置为NULL），因此数据库级联删除不会起作用。 - DhhJmm

我可以确认在这种情况下，“passive_deletes=True”确实有效。 - d512

因为我在进行删除操作时已经三次遇到了这个问题，所以我想强调一下：对于SQLite数据库来说，启用foreign_keys是至关重要的。简而言之，使用cursor.execute('PRAGMA foreign_keys=ON')命令即可。具体实现方法可以参考https://docs.sqlalchemy.org/en/13/dialects/sqlite.html#sqlite-foreign-keys（另外还有一个答案也提供了解释：https://dev59.com/Wm445IYBdhLWcg3wGmk6#62327279）。 - Sebastian Höffner

希望我能给这个答案点赞超过一次——我花了太长时间才弄清楚x = session.query(T).all(); [session.delete(y) for y in x]和session.query(T).delete()不是同一个东西。特别是在使用flask_sqlalchemy时，它会给你T.query.delete()，看起来真的应该考虑到某些原因的关系。 - Tom

Answer 4

Alex Okrushko的答案对我来说基本上是最好的。使用ondelete ='CASCADE'和passive_deletes = True组合使用。但我不得不做一些额外的工作才能使它在sqlite上正常工作。

Base = declarative_base()
ROOM_TABLE = "roomdata"
FURNITURE_TABLE = "furnituredata"

class DBFurniture(Base):
    __tablename__ = FURNITURE_TABLE
    id = Column(Integer, primary_key=True)
    room_id = Column(Integer, ForeignKey('roomdata.id', ondelete='CASCADE'))


class DBRoom(Base):
    __tablename__ = ROOM_TABLE
    id = Column(Integer, primary_key=True)
    furniture = relationship("DBFurniture", backref="room", passive_deletes=True)

请确保添加此代码以确保它适用于sqlite。

from sqlalchemy import event
from sqlalchemy.engine import Engine
from sqlite3 import Connection as SQLite3Connection

@event.listens_for(Engine, "connect")
def _set_sqlite_pragma(dbapi_connection, connection_record):
    if isinstance(dbapi_connection, SQLite3Connection):
        cursor = dbapi_connection.cursor()
        cursor.execute("PRAGMA foreign_keys=ON;")
        cursor.close()

摘自这里：SQLAlchemy表达式语言和SQLite的on delete cascade

- stupidstudent

Answer 5

Steven是正确的，你需要明确创建backref，这会导致级联应用在parent上（而不是像测试场景中应用在child上）。

然而，在Child上定义关系并不意味着sqlalchemy认为Child是parent。无论关系在哪里定义（child或parent），连接两个表的外键决定哪个是parent，哪个是child。

有一个约定最好遵守，根据Steven的回答，我正在将所有子关系定义在parent上。

- Larry Weya

Answer 6

Steven 的回答很好。我想指出一个额外的含义。

通过使用relationship，您使应用程序层（Flask）负责引用完整性。这意味着通过 Flask 以外访问数据库的其他进程，例如数据库实用程序或直接连接到数据库的人，将无法体验到这些约束，并且可能以破坏您努力设计的逻辑数据模型的方式更改您的数据。

只要有可能，就请使用 d512 和 Alex 描述的 ForeignKey 方法。DB 引擎非常擅长真正强制执行约束（以不可避免的方式），因此这是维护数据完整性的最佳策略。只有当数据库无法处理它们时（例如不支持外键的 SQLite 版本），您才需要依赖应用程序来处理数据完整性。

如果您需要创建实体之间的进一步链接以启用应用程序行为，例如导航父子对象关系，请与ForeignKey 结合使用 backref。

- Chris Johnson

Answer 7

我也曾为文档而苦恼，但发现函数注释本身通常比手册更容易理解。例如，如果你从 sqlalchemy.orm 中导入 relationship 并执行 help(relationship)，它将给出你可以指定级联选项的全部选项。其中 delete-orphan 的说明如下：

如果检测到一个没有父级的子级项目，则将其标记为删除。
请注意，此选项会防止未存在父级的子类待定项被持久化。

我意识到您的问题更多地涉及定义父子关系的文档。但似乎您也可能遇到了级联选项的问题，因为 "all" 包括 "delete"。只有 "delete-orphan" 不包含在 "all" 中。

- Profane

---

在sqlalchemy对象上使用help(..)非常有帮助！谢谢 :-)))！PyCharm在上下文窗口中没有显示任何内容，而且显然忘记了检查help。非常感谢！ - dmitry_romanov

Answer 8

虽然这个问题非常老，但在谷歌搜索中它排名第一，所以我会发表我的解决方案来补充其他人说的（即使我已经阅读了所有答案，在此之后花费了几个小时）。

如d512所解释的那样，这完全与外键有关。令我感到惊讶的是，并非所有数据库/引擎都支持外键。我正在运行一个MySQL数据库。经过长时间的调查，我注意到当我创建新表时，它默认为一个不支持外键的引擎（MyISAM）。我所要做的就是通过在定义表时添加mysql_engine='InnoDB'来将其设置为InnoDB。在我的项目中，我使用了命令映射，它看起来像这样:

db.Table('child',
    Column('id', Integer, primary_key=True),
    # other columns
    Column('parent_id',
           ForeignKey('parent.id', ondelete="CASCADE")),
    mysql_engine='InnoDB')

- Amongalen

Answer 9

"最初的回答"翻译成中文为“Original Answer”。

Stevan的答案很完美。但如果您仍然遇到错误，可以尝试其他可能性 -

http://vincentaudebert.github.io/python/sql/2015/10/09/cascade-delete-sqlalchemy/

从链接中复制 -

如果您在模型中指定了级联删除，但仍存在外键依赖关系问题，则可以使用SQLAlchemy，在父表上指定cascade='all, delete'。好的，但是当您执行以下操作时：

session.query(models.yourmodule.YourParentTable).filter(conditions).delete()

这实际上会触发一个有关于子表中使用了外键的错误。

我使用的解决方法是先查询对象，然后再将其删除：

Original Answer翻译成：最初的回答

session = models.DBSession()
your_db_object = session.query(models.yourmodule.YourParentTable).filter(conditions).first()
if your_db_object is not None:
    session.delete(your_db_object)

这将会删除你的父记录以及所有相关的子记录。最初的回答中的内容应该这样表述：

这个操作会同时删除父记录和所有相关的子记录。

- Prashant Momale

---

调用.first()是必需的吗？哪些过滤条件会返回对象列表，并且所有内容都必须被删除？难道调用.first()只能获取第一个对象吗？@Prashant - Kavin Raju S

我遇到了这个问题。 我想在pytest fixture中清除整个表，但是session.query(Parent).delete()似乎无法使用外键。 查询对象，然后单独迭代每个对象似乎不是正确的解决方案，至少不应该是... - Kkulikovskis

Answer 10

TLDR: 如果以上解决方案无效，请尝试在列中添加nullable=False。

我想在这里补充一点，对于一些可能无法使用现有解决方案（这些解决方案非常好）的人来说。我的工作与示例的主要区别是我使用了automap。我不知道它如何干扰级联的设置，但我想指出我使用了它。我还在使用SQLite数据库。

我尝试了这里描述的每个解决方案，但当删除父行时，子表中的行仍将其外键设置为null。我已经尝试了所有的解决方案，但都无济于事。然而，一旦我将具有外键的子列设置为nullable = False，级联就起作用了。

在子表上，我添加了：

Column('parent_id', Integer(), ForeignKey('parent.id', ondelete="CASCADE"), nullable=False)
Child.parent = relationship("parent", backref=backref("children", passive_deletes=True)

使用这个设置，级联功能按预期运行。

- Spencer Weston

---

我也使用 automap，只需设置 nullable=False 就能删除子行。 - Weber Huang

请勿写“上述解决方案”，因为答案出现的顺序可能会有所不同。在您之上的所有答案都比较新，因此我相信您是指下面的答案。 - bfontaine