Sqlalchemy many-to-many relationships: delete the child only if there are no more references?

Question

I'm writing a simple bookmark manager program that uses SQLAlchemy for data storage. I have database objects for Bookmarks and Tags, and there is a many-to-many relationship between them: a bookmark can use any tags in the database, and each tag can be assigned to any (or even all) bookmarks in the database. Tags are automatically created and removed by the program – if the number of bookmarks referencing a tag drops to zero, the tag should be deleted.

Here's my model code, with unnecessary methods such as __str__() removed:

mark_tag_assoc = Table('mark_tag_assoc', Base.metadata,
        Column('mark_id', Integer, ForeignKey('bookmarks.id')),
        Column('tag_id', Integer, ForeignKey('tags.id')),
        PrimaryKeyConstraint('mark_id', 'tag_id'))

class Bookmark(Base):
    __tablename__ = 'bookmarks'

    id = Column(Integer, primary_key=True)
    name = Column(String)
    url = Column(String)
    description = Column(String)
    tags_rel = relationship("Tag", secondary=mark_tag_assoc,
                            backref="bookmarks", cascade="all, delete")

class Tag(Base):
    __tablename__ = 'tags'

    id = Column(Integer, primary_key=True)
    text = Column(String)

I thought that if I set up a cascade (cascade="all, delete") it would take care of removing tags with no more references for me. What actually happens is that when any bookmark is deleted, all tags referenced by it are automatically removed from all other bookmarks and deleted, which is obviously not the intended behavior.

Is there a simple option to do what I want, or if not, what would be the cleanest way to implement it myself? Although I have a little bit of experience with simple SQL, this is my first time using SQLAlchemy, so details would be appreciated.

Answer 1

I'd still be interested to know if there happens to be a built-in function for this, but after further research it seems more likely to me that there is not, as there don't generally seem to be too many helpful functions for doing complicated stuff with many-to-many relationships. Here's how I solved it:

1. Remove cascade="all, delete" from the relationship so that no cascades are performed. Even with no cascades configured, SQLAlchemy will still remove rows from the association table when bookmarks are deleted.
2. Call a function after each delete of a Bookmark to check if the tag still has any relationships, and delete the tag if not:

---

def maybeExpungeTag(self, tag):
    """
    Delete /tag/ from the tags table if it is no longer referenced by
    any bookmarks.

    Return:
        True if the tag was deleted.
        False if the tag is still referenced and was not deleted.
    """
    if not len(tag.bookmarks):
        self.session.delete(tag)
        return True
    else:
        return False

# and for the actual delete...
mark = # ...get the bookmark being deleted
tags = mark.tags_rel
self.session.delete(mark)
for tag in tags:
    self.maybeExpungeTag(tag)
self.session.commit()