在sqlalchemy中删除所有子项后，删除父对象 [英] Delete parent object when all children have been deleted in sqlalchemy

问题描述

假设我有以下关系:

Reference(slide_id, group_id)，其中引用是幻灯片和组的关联.

Reference(slide_id, group_id) where reference is the association of a slide and a group.

一张幻灯片可用于许多参考文献(具有不同的组):主键为slide_id + group_id，必须唯一.

One slide can be used in many references (with a different group): primary key is slide_id + group_id, which must be unique.

当要删除指向的组或幻灯片时，我想删除所有引用. 我通过在关系的backref中添加级联来做到这一点:

I want to have all the references deleted when either of the pointed group or slide are deleted. Which I do by adding a cascade in the backref of the relationship:

# definition of Reference model

slide = db.relationship(
    Slide,
    backref=db.backref('references', cascade='all, delete, delete-orphan')
)

group = db.relationship(
    Group,
    backref=db.backref('references', cascade='all, delete, delete-orphan')
)

每当我删除引用并且没有其他引用使用该组和/或幻灯片时，我也希望删除引用的组或幻灯片.

I also want to have the referenced group or slide deleted, whenever I delete a reference and there are no other references that use said group and/or slide.

我曾考虑过在需要时使用@listens_for(Reference, 'after_delete')手动删除父母.我也尝试过关系中的属性组合，但是我不确定如何解决此问题.

I have thought about using @listens_for(Reference, 'after_delete') to manually delete parents when needed. I also tried attribute combos in the relationship, but I am not really sure how to solve this problem.

推荐答案

我认为 before_delete .下面是一个可以帮助您的示例.

I think that idea with @listens_for is not bad. You need just listen for Reference before_delete. Below is an example that can help.

class Slide(db.Model):
    __tablename__ = 'slide'

    id = db.Column(db.Integer, primary_key=True)
    children = db.relationship('Reference')
    references = db.relationship('Reference', back_populates='slide')


class Group(db.Model):
    __tablename__ = 'group'

    id = db.Column(db.Integer, primary_key=True)
    references = db.relationship('Reference', back_populates='group')


class Reference(db.Model):
    __tablename__ = 'reference'

    id = db.Column(db.Integer, primary_key=True)
    slide_id = db.Column(db.Integer, db.ForeignKey('slide.id'))
    group_id = db.Column(db.Integer, db.ForeignKey('group.id'))
    group = db.relationship('Group', back_populates='references')
    slide = db.relationship('Slide', back_populates='references')


@event.listens_for(Reference, 'before_delete')
def delete_reference(mapper, connection, target):
    # after_flush used for consistent results
    @event.listens_for(Session, 'after_flush', once=True)
    def receive_after_flush(session, context):
        # just do here everything what you need...
        # if our preference slide is the last one
        if target.slide and not target.slide.references:
            print('add slide with id = %s to delete' % target.slide.id)
            session.delete(target.slide)
        # if our preference group is the last one
        if target.group and not target.group.references:
            session.delete(target.group)
            print('add group with id = %s to delete' % target.group.id)

让我们检查一下:

# clear tables
Reference.query.delete()
Group.query.delete()
Slide.query.delete()
# create one reference with only 1 group and only 1 slide
reference = Reference(group=Group(), slide=Slide())
db.session.add(reference)
db.session.commit()

print('reference with id %s was created. slide_id = %s, group_id = %s' % (
    reference.id, reference.slide_id, reference.group_id
))
reference = Reference.query.filter_by(id=reference.id).first()
print('add reference with id = %s to delete' % reference.id)
db.session.delete(reference)
db.session.commit()
print('amount references after delete: %s' % Reference.query.all())


# create 2 references but with the same group and slide
group = Group()
slide = Slide()
reference = Reference(group=group, slide=slide)
reference2 = Reference(group=group, slide=slide)
db.session.add(reference)
db.session.add(reference2)
db.session.commit()
print('reference with id %s was created. slide_id = %s, group_id = %s' % (
    reference.id, reference.slide_id, reference.group_id
))
print('reference2 with id %s was created. slide_id = %s, group_id = %s' % (
    reference2.id, reference2.slide_id, reference2.group_id
))

reference = Reference.query.filter_by(id=reference.id).first()
print('add reference with id = %s to delete' % reference.id)
db.session.delete(reference)
print('amount references after delete: %s' % Reference.query.all())
db.session.commit()

您将看到在第一种情况下，删除了Preference，Group和Slide.因为其他Preference与Group和Slide无关.但是在第二种情况下，您会看到仅删除了Preference:

You will see that in first case Preference, Group and Slide were deleted. Because other Preference’s didn't related to Group and Slide. But in the second case you will see that only Preference was removed:

reference with id 76 was created. slide_id = 65, group_id = 65
add reference with id = 76 to delete
add slide with id = 65 to delete
add group with id = 65 to delete
amount references after delete: []
reference with id 77 was created. slide_id = 66, group_id = 66
reference2 with id 78 was created. slide_id = 66, group_id = 66
add reference with id = 77 to delete
# with slide_id = 66, group_id = 66
amount references after delete: [<Reference 78>]

希望这会有所帮助.

这篇关于在sqlalchemy中删除所有子项后，删除父对象的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！