so my version does work on postgres too (did u try it?).. at least finishes with no errors. or should there be other checks? like what's left in each table?
On Thursday 27 November 2008 10:30:04 David Harrison wrote: > Postgres is the intended deployment platform so it really does need > to work on Postgres, that said last time I dug into this I found > that SQLite is less strict on enforcing key constraints where > Postgres isn't, so technically Postgres is right to complain. > > 2008/11/27 <[EMAIL PROTECTED]>: > > and what that shoud do? attached is a changed version... do see > > if that's what u want (it's sqlite, with plain session). > > the only real change is cascade=all,delete-orphan on > > house.owners... but i just unintentionaly guessed it. > > > > On Thursday 27 November 2008 09:51:38 David Harrison wrote: > >> So this is actually a follow on from a question I posed quite a > >> while back now: > >> > >> http://groups.google.com/group/sqlalchemy/browse_thread/thread/4 > >>530 > >> dffffd3f5585/eb4638599b02577d?lnk=gst&q=Postgres+cascade+error#e > >>b463 8599b02577d > >> > >> So my approach to solving this problem was to use a > >> MapperExtension, but it's giving me the error that I originally > >> posted in this thread. > >> > >> I'm re-posting my previous code here for easy reference and > >> testing by others (with one tiny mod to get rid of the > >> optionparser code I had): > >> > >> --- > >> > >> #!/usr/bin/env python > >> > >> import sys > >> import sqlalchemy as sa > >> import sqlalchemy.orm > >> > >> > >> session = sa.orm.scoped_session( > >> sa.orm.sessionmaker(autoflush=False, transactional=True) > >> ) > >> mapper = session.mapper > >> metadata = sa.MetaData() > >> > >> > >> houseTable = sa.Table( > >> 'house', > >> metadata, > >> sa.Column('id', sa.Integer, primary_key=True), > >> ) > >> > >> ownerTable = sa.Table( > >> 'owner', > >> metadata, > >> sa.Column('id', sa.Integer, primary_key=True), > >> sa.Column('house_id', sa.Integer, > >> sa.ForeignKey('house.id')), ) > >> > >> dogTable = sa.Table( > >> 'dog', > >> metadata, > >> sa.Column('id', sa.Integer, primary_key=True), > >> sa.Column('house_id', sa.Integer, > >> sa.ForeignKey('house.id')), ) > >> > >> friendshipTable = sa.Table( > >> 'friendship', > >> metadata, > >> sa.Column('id', sa.Integer, primary_key=True), > >> sa.Column('owner_id', sa.Integer, > >> sa.ForeignKey('owner.id')), sa.Column('dog_id', sa.Integer, > >> sa.ForeignKey('dog.id')), ) > >> > >> > >> class House(object): pass > >> class Owner(object): pass > >> class Dog(object): pass > >> class Friendship(object): pass > >> > >> > >> mapper( > >> House, > >> houseTable, > >> properties = { > >> "owners" : sa.orm.relation( > >> Owner, cascade="delete-orphan" > >> ), > >> "dogs" : sa.orm.relation( > >> Dog, cascade="delete-orphan" > >> ), > >> }, > >> ) > >> mapper( > >> Owner, > >> ownerTable, > >> properties = { > >> "friendships" : sa.orm.relation( > >> Friendship, cascade="delete" > >> ), > >> }, > >> ) > >> > >> mapper( > >> Friendship, > >> friendshipTable, > >> properties = { > >> "dog" : sa.orm.relation( > >> Dog, uselist=False, cascade="all, delete-orphan" > >> ), > >> }, > >> ) > >> > >> mapper(Dog, dogTable) > >> > >> > >> if __name__ == "__main__": > >> > >> engine = sa.create_engine( > >> "postgres://test:[EMAIL PROTECTED]/test", > >> strategy="threadlocal", > >> echo=True > >> ) > >> metadata.bind = engine > >> session.configure(bind=engine) > >> > >> print "Creating tables" > >> metadata.create_all() > >> > >> print "Seeding database" > >> for i in range(10): House() > >> session.flush() > >> > >> for house in sa.orm.Query(House).all(): > >> for i in range(2): > >> owner = Owner() > >> house.owners.append(owner) > >> session.flush() > >> > >> for house in sa.orm.Query(House).all(): > >> for i in range(2): > >> dog = Dog() > >> house.dogs.append(dog) > >> session.flush() > >> > >> for owner in sa.orm.Query(Owner).all(): > >> for dog in sa.orm.Query(Dog).filter_by(house_id = > >> owner.house_id).all(): friendship = Friendship() > >> friendship.dog = dog > >> owner.friendships.append(friendship) > >> session.commit() > >> > >> owner = sa.orm.Query(Owner).first() > >> for f in owner.friendships: > >> print "FRIENDSHIP: %s || DOG: %s" % (f.id, f.dog.id) > >> > >> print "Deleting owner" > >> session.delete(owner) > >> session.flush() > >> session.commit() > >> > >> 2008/11/27 David Harrison <[EMAIL PROTECTED]>: > >> > Sorry, I should probably have mentioned that C isn't the only > >> > object that maps A, so a cascade doesn't work. > >> > > >> > 2008/11/27 <[EMAIL PROTECTED]>: > >> >> i'm not expert on these, but i think u need something like > >> >> cascade='all' on your relation, _instead_ of the mapperExt. > >> >> check the docs about possible settings. the mapperExt fires > >> >> too late and the session flush-plan gets surprised. > >> >> > >> >> On Thursday 27 November 2008 08:15:04 David Harrison wrote: > >> >>> Hey all, > >> >>> > >> >>> I've got a situation where I have 2 object A and B, and a > >> >>> third object C that has a foreign key reference to both A > >> >>> and B. I can have many C's that map to the same A. > >> >>> > >> >>> Now I've implemented a MapperExtension for C that has an > >> >>> after_delete function, and that function checks to see if > >> >>> the A that the deleted C was mapped to has any other > >> >>> mappings, and if there are no other mappings left, deletes > >> >>> the A. > >> >>> > >> >>> Now this works fine if I'm just deleting C's directly, > >> >>> however as soon as this happens during a cascade delete from > >> >>> some other object D that happens to have a mapping to C I > >> >>> get the below error - I'm assuming this is because > >> >>> sqlalchemy has a test condition that doesn't see my mapper > >> >>> coming, and freaks out when extra rows get nuked. > >> >>> > >> >>> "ConcurrentModificationError: Deleted rowcount 0 does not > >> >>> match number of objects deleted 4" > >> >>> > >> >>> Help ? > >> >>> > >> >>> Cheers > >> >>> Dave > > --~--~---------~--~----~------------~-------~--~----~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~----------~----~----~----~------~----~------~--~---
