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().
On Mar 26, 2012, at 4:54 PM, John Anderson wrote:
> 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.
>
>
>
> --
> 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 [email protected].
> To unsubscribe from this group, send email to
> [email protected].
> For more options, visit this group at
> http://groups.google.com/group/sqlalchemy?hl=en.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
