Delete parent if the child is deleted

user_2695448 0
Viewed 1201

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.

cascading-deletes one-to-many sqlalchemy python
3 Answers

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"))
user_32943960

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.

user_53209060

The behavior you're referring to is kinda simple. But once the application gets larger, you'd need more sophisticated methods. I, personally, implement a static method remove for every class. It takes the id and any other parameter necessary to know the delete scheme.

The answer is already given... But that's - not efficient - approach just to give you an example.

class Child(Base):
    __tablename__ = "children"
    id = Column(Integer, primary_key=True)
    parent_id = Column(Integer, ForeignKey("parents.id", ondelete='CASCADE'))
    
    @staticmethod
    def remove(_id_):
        child = Child.query().get(_id_)
        Parent.remove(child.parent_id)
        session.delete(child)

class Parent(Base):
    __tablename__ = "parents"
    id = Column(Integer, primary_key=True)
    child = relationship(Child, backref="parent", passive_deletes=True)

    @staticmethod
    def remove(_id_):
        parent = parent.query().get(_id_)
        session.delete(parent)

If you, for example, added a Column named is_old - in the Child class - that's a binary integer 0 or 1 and wanted to delete the parent of the Child that has is_old == 1, It'll be very easy task.

user_113518030
Related