英文:
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 cascade
in 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=...
的方法在这里有详细说明:
英文:
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:
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论