使用SQLAlchemy高效地删除嵌套记录

Question

我正试图从SQLAlchemy中删除一大组嵌套记录。为此，我需要查询数据，然后使用会话删除它。我的问题是查询的内存不足。

具体地，我有类CA、CB、CC和CD。CA使用name作为主键，CB具有自动递增的CBId列作为其主键。CA的实例包含约1000个CB的实例，而CCD_10又包含1000个CCD11的实例和约800万个CD的实例。

我想向数据库发出的基本信息是：

DELETE FROM CCTable WHERE CBId IN (SELECT CBId FROM CBTable WHERE Name = ?)
DELETE FROM CDTable WHERE CBId IN (SELECT CBId FROM CBTable WHERE Name = ?)
DELETE FROM CBTable WHERE Name = ?
DELETE FROM CATable WHERE Name = ?

我目前正在尝试的是：

data = self._context.query(CA) 
.filter_by(name = name_to_delete) 
.options(
joinedload('CB').options(joinedload('CC'), joinedload('CD')) 
).first() 
if data:
context.delete(data)
self._context.commit()

这失败了，这并不意外，因为内存不足。

如何在SQLAlchemy中实现我的目标？

Answer 1

注释中提到的ON DELETE CASCADE选项可能是数据库级别的解决方案

否则，您的查询基本上将从CA开始的整个对象网络加载到内存中，这很可能是问题的原因。此外，即使不是，通过执行context.delete(data)，您也只是删除CA的实例，因为data将是CA实例，并且除非您的关系正确定义了cascade，否则相关行将不会在数据库中删除。

如果你已经正确地配置了这些，那么需要将所有内容加载到内存中以完全删除它的问题就不是应该采取的方法。

解决方案：直接在数据库上删除(无需加载数据(

示例模型定义如下：

class CA(Base):
__tablename__ = "ca_table"
name = Column(String, primary_key=True)
cbs = relationship("CB", back_populates="ca")

class CB(Base):
__tablename__ = "cb_table"
id = Column(Integer, primary_key=True)
name = Column(String, index=True)
name = Column(ForeignKey(CA.name))
ca = relationship(CA, back_populates="cbs")
ccs = relationship("CC", back_populates="cb")
cds = relationship("CD", back_populates="cb")

class CC(Base):
__tablename__ = "cc_table"
id = Column(Integer, primary_key=True)
name = Column(String, index=True)
cb_id = Column(ForeignKey(CB.id))
cb = relationship(CB, back_populates="ccs")

class CD(Base):
__tablename__ = "cd_table"
id = Column(Integer, primary_key=True)
name = Column(String, index=True)
cb_id = Column(ForeignKey(CB.id))
cb = relationship(CB, back_populates="cds")

我将根据以下内容执行删除：

def delete_a(session, name_to_delete):
q = session.query(CD).filter(CD.cb.has(CB.name == name_to_delete))
q.delete(synchronize_session=False)
q = session.query(CC).filter(CC.cb.has(CB.name == name_to_delete))
q.delete(synchronize_session=False)
q = session.query(CB).filter(CB.name == name_to_delete)
q.delete(synchronize_session=False)
q = session.query(CA).filter(CA.name == name_to_delete)
q.delete(synchronize_session=False)
session.commit()

delete_a(session, "some_a_name")