Delete children after parent is deleted in SQLAlchemy

Question

My problem is the following:

I have the two models Entry and Tag linked by a many-to-many relationship in SQLAlchemy. Now I want to delete every Tag that doesn't have any corresponding Entry after an Entry is deleted.

Example to illustrate what I want:

• Entry 1 with tags python, java

• Entry 2 with tags python, c++

With these two entries the database contains the tags python, java, and c++. If I now delete Entry 2 I want SQLAlchemy to automatically delete the c++ tag from the database. Is it possible to define this behavior in the Entry model itself or is there an even more elegant way?

Thanks.

Answer 1

this question was asked awhile back here: Setting delete-orphan on SQLAlchemy relationship causes AssertionError: This AttributeImpl is not configured to track parents

This is the "many-to-many orphan" problem. jadkik94 is close in that you should use events to catch this, but I try to recommend against using the Session inside of mapper events, though it works in this case.

Below, I take the answer verbatim from the other SO question, and replace the word "Role" with "Entry":

from sqlalchemy.ext.associationproxy import association_proxy
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import event
from sqlalchemy.orm import attributes

Base= declarative_base()

tagging = Table('tagging',Base.metadata,
    Column('tag_id', Integer, ForeignKey('tag.id', ondelete='cascade'), primary_key=True),
    Column('entry_id', Integer, ForeignKey('entry.id', ondelete='cascade'), primary_key=True)
)

class Tag(Base):

    __tablename__ = 'tag'
    id = Column(Integer, primary_key=True)
    name = Column(String(100), unique=True, nullable=False)

    def __init__(self, name=None):
        self.name = name

class Entry(Base):
    __tablename__ = 'entry'

    id = Column(Integer, primary_key=True)
    tag_names = association_proxy('tags', 'name')

    tags = relationship('Tag',
                        secondary=tagging,
                        backref='entries')

@event.listens_for(Session, 'after_flush')
def delete_tag_orphans(session, ctx):
    # optional: look through Session state to see if we want
    # to emit a DELETE for orphan Tags
    flag = False

    for instance in session.dirty:
        if isinstance(instance, Entry) and \
            attributes.get_history(instance, 'tags').deleted:
            flag = True
            break
    for instance in session.deleted:
        if isinstance(instance, Entry):
            flag = True
            break

    # emit a DELETE for all orphan Tags.   This is safe to emit
    # regardless of "flag", if a less verbose approach is
    # desired.
    if flag:
        session.query(Tag).\
            filter(~Tag.entries.any()).\
            delete(synchronize_session=False)


e = create_engine("sqlite://", echo=True)

Base.metadata.create_all(e)

s = Session(e)

r1 = Entry()
r2 = Entry()
r3 = Entry()
t1, t2, t3, t4 = Tag("t1"), Tag("t2"), Tag("t3"), Tag("t4")

r1.tags.extend([t1, t2])
r2.tags.extend([t2, t3])
r3.tags.extend([t4])
s.add_all([r1, r2, r3])

assert s.query(Tag).count() == 4

r2.tags.remove(t2)

assert s.query(Tag).count() == 4

r1.tags.remove(t2)

assert s.query(Tag).count() == 3

r1.tags.remove(t1)

assert s.query(Tag).count() == 2

two almost identical SO questions qualifies this as something to have on hand so I've added it to the wiki at http://www.sqlalchemy.org/trac/wiki/UsageRecipes/ManyToManyOrphan.

Answer 2

I will let code speak for me:

from sqlalchemy import create_engine, exc, event
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import func, Table, Column, Integer, String, Float, Boolean, MetaData, ForeignKey
from sqlalchemy.orm import relationship, backref

# Connection
engine = create_engine('sqlite:///', echo=True)
Base = declarative_base(bind=engine)
Session = sessionmaker(bind=engine)

# Models
entry_tag_link = Table('entry_tag', Base.metadata,
    Column('entry_id', Integer, ForeignKey('entries.id')),
    Column('tag_id', Integer, ForeignKey('tags.id'))
)

class Entry(Base):
    __tablename__ = 'entries'
    id = Column(Integer, primary_key=True)
    name = Column(String(255), nullable=False, default='')

    tags = relationship("Tag", secondary=entry_tag_link, backref="entries")

    def __repr__(self):
        return '<Entry %s>' % (self.name,)

class Tag(Base):
    __tablename__ = 'tags'
    id = Column(Integer, primary_key=True)
    name = Column(String(255), nullable=False)

    def __repr__(self):
        return '<Tag %s>' % (self.name,)

# Delete listener
def delete_listener(mapper, connection, target):
    print "---- DELETING %s ----" % (target,)
    print '-' * 20
    for t in target.tags:
        if len(t.entries) == 0:
            print ' ' * 5, t, 'is to be deleted'
            session.delete(t)
    print '-' * 20

event.listen(Entry, 'before_delete', delete_listener)

# Utility functions
def dump(session):
    entries = session.query(Entry).all()
    tags = session.query(Tag).all()

    print '*' * 20
    print 'Entries', entries
    print 'Tags', tags
    print '*' * 20


Base.metadata.create_all()

session = Session()

t1, t2, t3 = Tag(name='python'), Tag(name='java'), Tag(name='c++')

e1, e2 = Entry(name='Entry 1', tags=[t1, t2]), Entry(name='Entry 2', tags=[t1, t3])

session.add_all([e1,e2])
session.commit()

dump(session)

raw_input("---- Press return to delete the second entry and see the result ----")

session.delete(e2)
session.commit()

dump(session)

This code above uses the after_delete event of the SQLAlchemy ORM events. This line does the magic:

event.listen(Entry, 'before_delete', delete_listener)

This says to listen to all deletes to an Entry item, and call our listener which will do what we want. However, the docs do not recommend changing the session inside the events (see the warning in the link I added). But as far as I can see, it works, so it's up to you to see if this works for you.