[SOLVED] Automatic delete a sub-object when it is not longer referenced by any parent in SQLAlchemy

Automatic delete a sub-object when it is not longer referenced by any parent in SQLAlchemy

There is a many-to-many-relationship between A and B.

a_b_relation= sa.Table('a_b_relation', _Base.metadata,
    sa.Column('a_oid', sa.Integer, sa.ForeignKey('A.oid')),
    sa.Column('b_oid', sa.Integer, sa.ForeignKey('B.oid'))
)


class A(_Base):
    __tablename__ = 'A'

    _oid = sa.Column('oid', sa.Integer, primary_key=True)
    _bbb = sao.relationship('B', secondary=a_b_relation)


class B(_Base):
    __tablename__ = 'B'

    _oid = sa.Column('oid', sa.Integer, primary_key=True)

An instance of B can be referenced by many A instances. But when there there is no reference anymore B should be deleted automaticly. The point is that the A instances are not deleted! They just modify there relationship to another instance of B.

Is there a SQLAlchemy way to do that? Or do I have to check that by myself?

Solution

SQLAlchemy has a delete-orphan cascade that you can read about here. However, it does not work for many to many relationships since it requires that objects have "single parents":

delete-orphan cascade implies that each child object can only have one parent at a time, so is configured in the vast majority of cases on a one-to-many relationship. Setting it on a many-to-one or many-to-many relationship is more awkward; for this use case, SQLAlchemy requires that the relationship() be configured with the single_parent argument, establishes Python-side validation that ensures the object is associated with only one parent at a time.

This question discusses a similar situation as yours.