How to cascade delete a Table object?

[John Anderson]

I have 2 tables that are referenced via a relationship() flag, I need to figure out how to cascade delete them or delete them when their parent is deleted.

My DB Structure:

learn_tags = Table('learn_tags', Entity.metadata,

    Column('profile_pk', Integer, ForeignKey('user_profile.pk')),

    Column('skill_tag_pk', Integer, ForeignKey('skill_tag.pk'))

)

teach_tags = Table('teach_tags', Entity.metadata,

    Column('profile_pk', Integer, ForeignKey('user_profile.pk')),

    Column('skill_tag_pk', Integer, ForeignKey('skill_tag.pk'))

)

class SkillTag(Entity):

    name = Column(UnicodeText, nullable=False, unique=True)

class UserProfile(Entity):

    learn = relationship("SkillTag", secondary=learn_tags)

    teach = relationship("SkillTag", secondary=teach_tags)

I want to be able to delete a SkillTag and delete all of the entries in the learn_tags and teach_tags.  I'm not sure where to place the cascade for this to work.

I'm currently doing:

    del1 = learn_tags.delete().where(

            learn_tags.c.skill_tag_pk == pk

    )

    del2 = teach_tags.delete().where(

            teach_tags.c.skill_tag_pk == pk

    )

    DBSession.execute(del1)

    DBSession.execute(del2)

    skill = DBSession.query(SkillTag).get(pk)

which works but is kind of dirty.

[Michael Bayer]

When you use "secondary" with relationship, SQLAlchemy maintains the rows in this table for you automatically, that is, when you remove a SkillTag from UserProfile.learn:

UserProfile.learn.remove(some_skill_tag)

the row in learn_tags is deleted automatically.

if you want to delete some_skill_tag directly:

session.delete(some_skill_tag)

one way to solve it is to add a backref to each relationship():

learn = relationship("SkillTag", secondary=learn_tags, backref="learn_user_profs")

the UOW will load in the learn_user_profs rows and delete them, not the most efficient system but keeps the state of objects in the Session in sync.

To avoid loading in the rows, another way is to do what you're doing but just use an event, see a similar recipe here:

http://stackoverflow.com/questions/9234082/setting-delete-orphan-on-sqlalchemy-relationship-causes-assertionerror-this-att/9264556#comment12407940_9264556

The most efficient way of all is to set ON DELETE CASCADE on the foreign keys of learn_tags and teach_tags, see:

http://docs.sqlalchemy.org/en/latest/orm/collections.html#using-passive-deletes

that is:

learn_tags = Table('learn_tags', Entity.metadata,

    Column('profile_pk', Integer, ForeignKey('user_profile.pk', ondelete="CASCADE")),

    Column('skill_tag_pk', Integer, ForeignKey('skill_tag.pk', ondelete="CASCADE"))

)

then make sure the actual table in the database is generated with this schema, or otherwise alter the FK constraints in the DB directly.

In the CASCADE case, you won't see the collection deletion represented within a particular UserProfile.learn or UserProfile.teach collection until the collection is expired, which occurs normally after you say Session.commit().

--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/6c2mefWpTdMJ.
To post to this group, send email to sqlal...@googlegroups.com.
To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.

[Michael Bayer]

On Mar 26, 2012, at 5:14 PM, Michael Bayer wrote:

To avoid loading in the rows, another way is to do what you're doing but just use an event, see a similar recipe here:

http://stackoverflow.com/questions/9234082/setting-delete-orphan-on-sqlalchemy-relationship-causes-assertionerror-this-att/9264556#comment12407940_9264556

correction, that question isn't quite the same thing - you can use a before_delete event on this to run similar queries to what you have, see:

http://docs.sqlalchemy.org/en/latest/orm/events.html#sqlalchemy.orm.events.MapperEvents.before_delete

so:

@event.listens_for(SkillTag, "before_delete")

def del_assoc(mapper, connection, target):

    connection.execute(learn_tags.delete().where(learn_tags.c.skill_tag_.pk==target.pk))