级联删除未被使用的SQLAlchemy

huangapple go评论69阅读模式
英文:

cascade delete not being used sqlalchemy

问题

当我尝试从parent表中删除所有条目时,我遇到了以下错误:

sqlalchemy.exc.IntegrityError: (psycopg2.errors.ForeignKeyViolation) 表"parent"上的更新或删除违反了表"child"上的外键约束"child_p_id_fkey"
详细信息:键(id)=(1)仍然被表"child"引用。

这是我的代码,我已经尝试根据https://stackoverflow.com/questions/5033547/sqlalchemy-cascade-delete 在relationship的两端都添加了cascade,在这种情况下,我得到了以下错误:

sqlalchemy.exc.ArgumentError: 对于多对一关系Child.parent,delete-orphan级联通常仅在一对多关系的“一”端配置,而不在多对一或多对多关系的“多”端配置。要强制此关系允许通过Child.parent关系仅被单个Parent对象引用一次,从而允许在此方向上进行delete-orphan级联,设置single_parent=True标志。 (有关此错误的更多信息请访问:https://sqlalche.me/e/14/bbf0)

from sqlalchemy import create_engine, Column, ForeignKey, Integer, delete
from sqlalchemy.orm import declarative_base, relationship, Session

Base = declarative_base()

class Parent(Base):
    __tablename__ = "parent"
    id = Column(Integer, primary_key=True)
    children = relationship(
        "Child",
        back_populates="parent",
        cascade="save-update, merge, delete, delete-orphan",
    )

class Child(Base):
    __tablename__ = "child"
    id = Column(Integer, primary_key=True)
    p_id = Column(Integer, ForeignKey(Parent.id))
    parent = relationship(Parent, back_populates="children")


engine = create_engine(
    "postgresql://user:pass@localhost:5432/postgres", future=True
)

Base.metadata.create_all(engine)

with Session(engine) as session, session.begin():
    foo = Parent()
    session.add(foo)
    session.add(Child(parent=foo))

with Session(engine) as session:
    session.execute(delete(Parent).where(Parent.id == 1))
英文:

I am getting the following error when I try to delete all entries from parent table

> sqlalchemy.exc.IntegrityError: (psycopg2.errors.ForeignKeyViolation) update or delete on table "parent" violates foreign key constraint "child_p_id_fkey" on table "child"
DETAIL: Key (id)=(1) is still referenced from table "child".

This is my code, and I have already tried adding cascadein either ends of relationship as per https://stackoverflow.com/questions/5033547/sqlalchemy-cascade-delete, in which case I get this instead

> sqlalchemy.exc.ArgumentError: For many-to-one relationship Child.parent, delete-orphan cascade is normally configured only on the "one" side of a one-to-many relationship, and not on the "many" side of a many-to-one or many-to-many relationship. To force this relationship to allow a particular "Parent" object to be referred towards by only a single "Child" object at a time via the Child.parent relationship, which would allow delete-orphan cascade to take place in this direction, set the single_parent=True flag. (Background on this error at: https://sqlalche.me/e/14/bbf0)

from sqlalchemy import create_engine, Column, ForeignKey, Integer, delete
from sqlalchemy.orm import declarative_base, relationship, Session

Base = declarative_base()

class Parent(Base):
    __tablename__ = "parent"
    id = Column(Integer, primary_key=True)
    children = relationship(
        "Child",
        back_populates="parent",
        cascade="save-update, merge, delete, delete-orphan",
    )

class Child(Base):
    __tablename__ = "child"
    id = Column(Integer, primary_key=True)
    p_id = Column(Integer, ForeignKey(Parent.id))
    parent = relationship(Parent, back_populates="children")


engine = create_engine(
    "postgresql://user:pass@localhost:5432/postgres", future=True
)

Base.metadata.create_all(engine)

with Session(engine) as session, session.begin():
    foo = Parent()
    session.add(foo)
    session.add(Child(parent=foo))

with Session(engine) as session:
    session.execute(delete(Parent).where(Parent.id == 1))

答案1

得分: 1

根据我理解,当你直接使用类似于 delete 的操作时,你将跳出 ORM(对象关系映射)。要执行关系级联操作,你需要使用 session.delete(foo)

ORM 删除

因此,在你的最后一个代码块中,你需要像这样执行以触发在 Parent 上的关系级联操作:

with Session(engine) as session:
    # 从数据库中选择父对象
    foo = session.get(Parent, 1)
    # 现在使用 ORM 删除它
    session.delete(foo)

批量删除 / 核心删除

要在使用 session.execute(delete(Parent).where(Parent.id == 1)) 时进行级联操作,你需要在外键上设置 ondelete='CASCADE',如下所示:

class Child(Base):
    __tablename__ = "child"
    id = Column(Integer, primary_key=True)
    p_id = Column(Integer, ForeignKey(Parent.id, ondelete='CASCADE'))
    parent = relationship(Parent, back_populates="children")

结合使用 relationship(..., cascade="...")ondelete=... 的方法在这里有详细说明:

使用外键的删除级联与 ORM 关系

英文:

As I understand it when you use something like delete directly you step outside the orm. For the relationship cascades to execute you need to use session.delete(foo).

ORM DELETE

So in your last block you'd need to do something like this to trigger the cascades in your relationship on Parent:

with Session(engine) as session:
    # Select the parent back from the db
    foo = session.get(Parent, 1)
    # Now delete it with the ORM
    session.delete(foo)

BULK DELETE / CORE DELETE

To cascade when using session.execute(delete(Parent).where(Parent.id == 1)) you need to set ondelete='CASCADE' on the foreign key like this:

class Child(Base):
    __tablename__ = "child"
    id = Column(Integer, primary_key=True)
    p_id = Column(Integer, ForeignKey(Parent.id, ondelete='CASCADE'))
    parent = relationship(Parent, back_populates="children")

Combining relationship(..., cascade="...") and ondelete=... together is explained here:

using-foreign-key-on-delete-cascade-with-orm-relationships

huangapple
  • 本文由 发表于 2023年1月9日 11:45:48
  • 转载请务必保留本文链接:https://go.coder-hub.com/75052993.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定