SQLAlchemy：级联删除 [英] SQLAlchemy: cascade delete

问题描述

我必须缺少SQLAlchemy的级联选项的琐碎内容，因为我无法获得简单的级联删除才能正确操作-如果删除了父元素，则子级将继续存在，并且 null 外键。

I must be missing something trivial with SQLAlchemy's cascade options because I cannot get a simple cascade delete to operate correctly -- if a parent element is a deleted, the children persist, with null foreign keys.

我在这里放置了一个简洁的测试用例：

I've put a concise test case here:

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

父母与子女之间存在简单的一对多关系。该脚本创建一个父级，添加3个子级，然后提交。接下来，它删除父级，但子级仍然存在。为什么？我该如何使子级联删除？

There is a simple, one-to-many relationship between Parent and Child. The script creates a parent, adds 3 children, then commits. Next, it deletes the parent, but the children persist. Why? How do I make the children cascade delete?

推荐答案

问题是sqlalchemy认为 Child 作为父对象，因为这是您定义关系的地方（当然，您在乎它是否称为孩子）。

The problem is that sqlalchemy considers Child as the parent, because that is where you defined your relationship (it doesn't care that you called it "Child" of course).

如果而是在 Parent 类上定义关系，它将起作用：

If you define the relationship on the Parent class instead, it will work:

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

（请注意 Child 作为字符串：使用声明性样式时允许使用此字符串，这样您就可以引用尚未定义的类）

(note "Child" as a string: this is allowed when using the declarative style, so that you are able to refer to a class that is not yet defined)

您可能还希望添加 delete-orphan （ delete 导致在删除父级时删除子级， delete-orphan 也会删除从父级删除的所有子级，即使未删除父级也是如此）

You might want to add delete-orphan as well (delete causes children to be deleted when the parent gets deleted, delete-orphan also deletes any children that were "removed" from the parent, even if the parent is not deleted)

编辑：刚刚发现：如果您 rea lly 想要在 Child 类上定义关系，可以这样做，但是您必须在backref上定义级联 （通过显式创建backref），如下所示：

just found out: if you really want to define the relationship on the Child class, you can do so, but you will have to define the cascade on the backref (by creating the backref explicitly), like this:

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

（暗指来自sqlalchemy.orm的导入backref ）

(implying from sqlalchemy.orm import backref)

这篇关于SQLAlchemy：级联删除的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！