Max Ischenko wrote:
> I have three related tables in MySQL and a corresponding mappings in
> SQLAlchemy.
>
> It all works fine until I tried to delete objects. I tinkered with
> different options but couldn't get it to work.
>
> Here is the relevant mappings:
>
> mapper(PlanetEntry, community_planet_tbl, properties={
> }, order_by=[community_planet_tbl.c.updated.desc()])
> mapper(CommunityFeed, community_feeds_tbl, properties={
> 'user':relation(User, backref='feeds'),
> 'entries':relation(PlanetEntry, backref='feed',
> cascade="all, delete, delete-orphan"),
> })
> mapper(CommunityMember, community_members_tbl)
> mapper(Community, communities_tbl, properties={
> 'members':relation(CommunityMember, backref='community',
> cascade="all, delete, delete-orphan"),
> 'feeds':relation(CommunityFeed, backref='community',
> cascade="all, delete, delete-orphan"),
> })
>
> I am attempting to delete a row from communities table (instance of
> Communy entity) and
> I get the following error:
>
> File '/home/max/projects/site-baseline/py/lib/python2.5/site-packages/
> SQLAlchemy-0.5.6-py2.5.egg/sqlalchemy/orm/unitofwork.py', line 762 in
> delete_objects
> task.mapper._delete_obj(task.polymorphic_todelete_objects, trans)
> File '/home/max/projects/site-baseline/py/lib/python2.5/site-packages/
> SQLAlchemy-0.5.6-py2.5.egg/sqlalchemy/orm/mapper.py', line 1527 in
> _delete_obj
> "number of objects deleted %d" % (c.rowcount, len(del_objects)))
> ConcurrentModificationError: Deleted rowcount 0 does not match number
> of objects deleted 1
>
> Introspection shows that it tries to delete CommunityMember objects
> with "DELETE FROM community_members WHERE
> community_members.community_id = %s AND community_members.member_id =
> %s"
>
> I suspect these are already gone since there is a CASCADE rule in
> community_members table in MySQL: FOREIGN KEY (`community_id`)
> REFERENCES `communities` (`id`) ON DELETE CASCADE ON UPDATE CASCADE.
>
> Why SQLAlchemy does not grok this?
>
> If I change mapper to the following (remove cascade spec):
>
> mapper(Community, communities_tbl, properties={
> 'members':relation(CommunityMember, backref='community'),
>
> I get this:
>
> File '/home/max/projects/site-baseline/py/lib/python2.5/site-packages/
> SQLAlchemy-0.5.6-py2.5.egg/sqlalchemy/orm/sync.py', line 28 in clear
> raise AssertionError("Dependency rule tried to blank-out primary key
> column '%s' on instance '%s'" % (r, mapperutil.state_str(dest)))
> AssertionError: Dependency rule tried to blank-out primary key column
> 'community_members.community_id' on instance '<CommunityMember at
> 0x31a0450>'
>
> Looks correct to me. Why the original cascade clause does not work
> then?
>
> Insight is much appreciated.
>
You can tell SQLAlchemy that the database will cascade deletes on its
own (add passive_deletes=True to the relation arguments, see
http://www.sqlalchemy.org/docs/05/mappers.html#using-passive-deletes for
more info). However, I was under the impression that even with
passive_deletes disabled, SQLAlchemy would know to delete child objects
before parent objects, so it should have worked anyway.
-Conor
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
