python - SQLAlchemy 删除相关元素取决于标志

Question

我有一个关于 SQLAlchemy 的新问题，我在试图找到一个好的解决方案时伤透了脑筋。所以我有一些表:

import sqlalchemy.orm.session

# other import statments . . .

Session = sqlalchemy.orm.session.Session

class Tempable(Base):
    id = Column(Integer, primary_key=True)
    name = Column(String, nullable=False, unique=True)
    temporary = Column(Boolean, nullable=False)

class Generic(Base):
    id = Column(Integer, primary_key=True)
    name = Column(String, nullable=False, unique=True)
    tempable_id = Column(Integer, ForeignKey(Tempable.id))

Tempable 表有一个名为 temporary 的字段。当此字段为 True 时，只有一个 Generic 可以与此 Tempable 表行关联，当相关 Generic 行被删除时，Tempable 也必须删除。否则许多 Generic 可以与 Tempable 连接并且删除其中一个不会影响 Tempable。经过一些研究后，我发现必须使用事件才能做到这一点。代码展开如下:

class Generic(Base):
    # . . .
    def before_delete(self, session):
        """:type session: Session"""
        condition = and_(Tempable.id == self.tempable_id, Tempable.temporary == 1)
        # I've tried use bulk session deletion:
        # session.query(Tempable).filter(condition).delete()
        # but if Tempable tables has relationships then related objects not deleted, 
        # I don't understand such behaviour.
        # But this works fine:
        for obj in session.query(Tempable).filter(condition):
            session.delete(obj)

@event.listens_for(Session, 'before_flush')
def _database_flush(session, flush_context, instances):
    for p_object in session.deleted:
        if hasattr(p_object, "before_delete"):
            p_object.before_delete(session)
    for p_object in session.dirty:
        if hasattr(p_object, "before_update"):
            p_object.before_update(session)
    for p_object in session.new:
        if hasattr(p_object, "before_insert"):
            p_object.before_insert(session)

但是出现了一些麻烦。当通用对象被删除时，相应的 GUI 也必须更新。为此，可以使用 Session 对象的 deleted 属性。但对我来说也有一个问题:删除的 Tempable 行没有出现在这个属性列表中。

class Database(object):

    # . . .

    def remove(name):
        # before commit I need to obtain list of all objects that will be deleted
        # that required to update GUI views

        try:
            this = self.__session.query(orm.Generic).filter(orm.Generic.name == name).one()
        except orm.NoResultFound:
            pass
        else:
            logging.info("Remove object: %s" % this)
            self.__session.delete(this)

        deleted = [obj for obj in self.__session.deleted]

        # At this point, list of deleted objects of course is not contain any Tempable objects
        print(deleted)

        self.__session.commit()

        # And here list is empty
        print([obj for obj in self.__session.deleted])

        return deleted

那么问题是获取已删除对象的正确方法是什么，或者整个方法可能是完全错误的？

Answer 1

批量删除系统不会处理您的关系，因为它会为所有行发出单个 DELETE 语句，而不会尝试加载和协调这些行所引用的内容。这是 the documentation for query.delete() 中列出的第一个“警告” :

The method does not offer in-Python cascading of relationships - it is assumed that ON DELETE CASCADE/SET NULL/etc. is configured for any foreign key references which require it, otherwise the database may emit an integrity violation if foreign key references are being enforced.

就“session.deleted”而言，该列表仅在刷新发生之前相关。 commit() 意味着 flush() 并且在刷新之后，所有 session.new、session.dirty、session.deleted 都被清除。您需要在将 session.deleted 清除到另一个列表之前复制它，或者可能更可靠的是在 before_flush() 中收集 session.deleted 就像您正在做的那样，并将您关心的那些对象复制到另一个列表，也许在 session.info 中；例如session.info['deleted_things'] = my_list_of_objects。