我肯定遗漏了SQLAlchemy的级联选项中的一些微不足道的东西,因为我不能通过简单的级联删除来正确操作--如果父元素是deleted,那么子元素将使用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之间存在一种简单的一对多关系。该脚本创建一个父对象,添加3个子对象,然后提交。接下来,它删除父级,但子级仍然存在。为什么?如何使子节点级联删除?
发布于 2012-10-09 21:52:12
当你通过session.delete()
删除时,@Steven的asnwer很好用,这在我的例子中从来没有发生过。我注意到大多数时候我是通过session.query().filter().delete()
来删除的(它不会将元素放入内存,而是直接从db中删除)。使用此方法时,sqlalchemy的cascade='all, delete'
不起作用。但是有一个解决方案:ON DELETE CASCADE
到db (注意:并不是所有的数据库都支持它)。
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)
发布于 2013-04-21 21:57:55
Steven是正确的,因为您需要显式地创建backref,这将导致级联应用于父对象(而不是像测试场景中那样应用于子对象)。
但是,在子级上定义关系并不会使sqlalchemy将子级视为父级。关系在哪里定义(子关系或父关系)并不重要,连接两个表的外键决定了哪个是父关系,哪个是子关系。
不过,坚持一种约定是有意义的,根据Steven的回应,我在父级上定义了所有的子关系。
发布于 2020-06-11 22:57:12
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 expression language and SQLite's on delete cascade
https://stackoverflow.com/questions/5033547
复制相似问题