[sqlalchemy] Re: Deleting records in a MapperExtension on after_delete
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/4530
dd3f5585/eb4638599b02577d?lnk=gstq=Postgres+cascade+error#eb463
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
[sqlalchemy] Re: Deleting records in a MapperExtension on after_delete
You've changed the session object though, this is for a web app so the
scoped session is what I need. That then immediately breaks all the
session.save calls.
2008/11/27 [EMAIL PROTECTED]:
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
dd3f5585/eb4638599b02577d?lnk=gstq=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
[sqlalchemy] Re: Deleting records in a MapperExtension on after_delete
Which was the error I posted to ask about in the first place ;-)
Writing a session extension for this problem seems like using a very
very very large hammer, since I only need to trigger my 'manual
cascade' in a particular circumstance.
I'm hoping Mike or one of the devs might have advice on how this
situation is meant to be handled ?
2008/11/27 [EMAIL PROTECTED]:
i've modified all relations to be all,delete-orphan and now it does
add them all - as your scoped session does - and then complains:
Deleting owner
BEGIN
DELETE FROM friendship WHERE friendship.id = %(id)s
[{'id': 1L}, {'id': 2L}]
DELETE FROM owner WHERE owner.id = %(id)s
{'id': 1L}
DELETE FROM dog WHERE dog.id = %(id)s
[{'id': 1}, {'id': 2}]
ROLLBACK
Traceback (most recent call last):
File f.py, line 138, in module
session.flush()
...
raise exc.DBAPIError.instance(statement, parameters, e,
connection_invalidated=is_disconnect)
sqlalchemy.exc.IntegrityError: (IntegrityError) update or delete on
table dog violates foreign key constraint friendship_dog_id_fkey
on table friendship
DETAIL: Key (id)=(1) is still referenced from table friendship.
'DELETE FROM dog WHERE dog.id = %(id)s' [{'id': 1}, {'id': 2}]
well... u have other friendships referencing that same dog/s.
so i tried this and that and if the friendship.dog has no cascades at
all (i.e. just default), then all seems ok - owner and friendships
deleted, dogs not.
i guess u want when 2nd owner gets deleted to delete the orphan dogs?
it's not in the src...
maybe a better testcase would be of help - asserting whats in db and
what should not be, before and after.
back on the initial question, mapperExt come to play too late in
session. u may try sessionExt hooks... or other/earlier mapperExt
hook... eventualy.
svil
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
dd3f5585/eb4638599b02577d?lnk=gstq=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.
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
[sqlalchemy] Deleting records in a MapperExtension on after_delete
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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: Deleting records in a MapperExtension on after_delete
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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: Deleting records in a MapperExtension on after_delete
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/4530dd3f5585/eb4638599b02577d?lnk=gstq=Postgres+cascade+error#eb4638599b02577d
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
[sqlalchemy] Re: Postgres cascade error ?
On 17/04/2008, Michael Bayer [EMAIL PROTECTED] wrote:
On Apr 15, 2008, at 10:32 PM, Dave Harrison wrote:
Hey all,
The below code establishes 3 tables (house, dog, owner) and a
mapper table to associate owners and dogs (friendships).
When I use either MySQL (5.0.51) or SQLite (3.4.2) as the backend,
this code works correctly. However when I use Postgres (either 8.2.7
or 8.3.1) I get the following integrity error:
sqlalchemy.exceptions.IntegrityError: (IntegrityError) update or
delete on table dog violates foreign key constraint
friendship_dog_id_fkey on table friendship DETAIL: Key (id)=(1)
is still referenced from table friendship. 'DELETE FROM dog WHERE
dog.id = %(id)s' [{'id': 1}, {'id': 2}]
always use delete cascade in conjunction with delete-orphan. It
doesnt make much sense to have delete-orphan only and not delete
cascade. If that doesn't solve your problem here, let me know and Ill
try running the example script.
If I use delete, delete-orphan I get the same errors
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Postgres cascade error ?
On 17/04/2008, Michael Bayer [EMAIL PROTECTED] wrote: On Apr 16, 2008, at 9:31 PM, Eric Ongerth wrote: On Apr 16, 7:24 am, Michael Bayer [EMAIL PROTECTED] wrote: always use delete cascade in conjunction with delete-orphan. It doesnt make much sense to have delete-orphan only and not delete cascade. Oh wow. That clears up a few things for me. I don't remember ever seeing this (or at least I don't remember taking this sense of things away after reading) in the documentation. Maybe I developed a blind spot back around 3.something and never got past it? I have simply been avoiding delete-orphan although I looked forward to figuring out how to use it without errors some day. I think this was the key fact that I missed, even though as you pointed out it's kind of the only way that makes sense. it *could* make sense as this thing that will scan a whole set of referenced entities for a link, but thats just not what we have implemented right now (and also im less certain about what the real use case there is). For my part, my-use case is that I want to be able to delete an entry from my mapper table (the friendshipTable in my example code), and have it collect any of the mapped entries (dogTable) that are no longer mapped. Not sure if it's a common use-case. --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
