Delete parent if the child is deleted

Question

I want to delete the parent row if the associated rows in child tables have been removed.

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)

If I remove the child

child_obj = session.query(Child).first()
session.delete(child_obj)
session.commit()

It does delete the child obj but parent remains as it is. I want to remove the parent as well using cascading.

Answer 1

You can read this thread: Linking Relationships with Backref

class User(Base):
    __tablename__ = 'user'
    id = Column(Integer, primary_key=True)
    name = Column(String)

    addresses = relationship("Address", backref="user")

class Address(Base):
    __tablename__ = 'address'
    id = Column(Integer, primary_key=True)
    email = Column(String)
    user_id = Column(Integer, ForeignKey('user.id'))

And you can define it in your child class:

parent = relationship(Parent, backref=backref("children", cascade="all,delete"))

Answer 2

You could do this for simple^* cases by creating a listener that intercepts deletions of Child instances and deletes the parent if there are no other children.

import sqlalchemy as sa

@sa.event.listens_for(sessionmaker, 'persistent_to_deleted')
def intercept_persistent_to_deleted(session, object_):
    # We are only interested in instances of Child.
    if not isinstance(object_, Child):
        return
    p = object_.parent
    # Handle null parents.
    if p is None:
        return
    cs = session.query(Child).filter(Child.parent == p).count()
    if cs == 0:
        session.delete(p)

^* I would recommend thorough testing if your code is doing things like deleting children and then creating new children with the deleted children's parents in the same session. The listener works in this case:

c = session.query(Child).first()
p = c.parent
session.delete(c)
c1 = Child(parent=p)
session.add(c1)
session.commit()

but hasn't been tested on anything more complicated.