[sqlalchemy] Re: Deleting records in a MapperExtension on after_delete
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 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
[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 a
[sqlalchemy] Re: Deleting records in a MapperExtension on after_delete
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 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
[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
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] 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] Re: Deleting records in a MapperExtension on after_delete
On Thursday 27 November 2008 11:42:28 David Harrison wrote:
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 dont know, to me sessExt is a much softer hammer than mapperExt as
it has overall view at whats to be processed, before it is processed.
there is some info about which hook is called when, and what info is
available at what time, and what can and cannot be done then - see
SessionExt's and MapperExt's methods docs.
But the exact order and assumptions/dependencies thereof are still a
bit misty to me... 6 months after i asked for first time.
sorry i couldnot help.
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/threa
d/4 530
dd3f5585/eb4638599b02577d?lnk=gstq=Postgres+cascade+erro
r#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] Re: set_shard problems
Just tested with SQLAlchemy 0.4.8 and that one works fine (see log
below). So something relevant must have changed between 0.4.8 and
0.5.0rc1.
Regards,
Ids
2008-11-27 14:06:51,941 INFO sqlalchemy.orm.mapper.Mapper: (Person|
persons) _compile_property(id, Column)
2008-11-27 14:06:51,942 INFO sqlalchemy.orm.mapper.Mapper: (Person|
persons) _compile_property(name, Column)
2008-11-27 14:06:51,947 INFO sqlalchemy.orm.mapper.Mapper: (Person|
persons) Identified primary key columns: ColumnSet([Column('id',
Integer(), table=persons, primary_key=True, nullable=False)])
2008-11-27 14:06:51,947 INFO sqlalchemy.orm.mapper.Mapper: (Person|
persons) constructed
2008-11-27 14:06:52,047 INFO sqlalchemy.orm.mapper.Mapper: (Person|
persons) __initialize_properties() started
2008-11-27 14:06:52,051 INFO sqlalchemy.orm.mapper.Mapper: (Person|
persons) initialize prop id
2008-11-27 14:06:52,052 INFO sqlalchemy.orm.strategies.ColumnLoader:
register managed attribute id on class Person
2008-11-27 14:06:52,052 INFO sqlalchemy.orm.mapper.Mapper: (Person|
persons) initialize prop name
2008-11-27 14:06:52,049 INFO sqlalchemy.orm.strategies.ColumnLoader:
register managed attribute name on class Person
2008-11-27 14:06:52,049 INFO sqlalchemy.orm.mapper.Mapper: (Person|
persons) __initialize_properties() complete
2008-11-27 14:06:52,050 DEBUG root: QUERY 1: SELECT persons.id AS
persons_id, persons.name AS persons_name
FROM persons ORDER BY persons.id
LIMIT 1
2008-11-27 14:06:52,078 INFO sqlalchemy.pool.QueuePool.0x..74: Created
new connection _mysql.connection open to 'localhost' at 829a20c
2008-11-27 14:06:52,078 INFO sqlalchemy.pool.QueuePool.0x..74:
Connection _mysql.connection open to 'localhost' at 829a20c checked
out from pool
2008-11-27 14:06:52,083 INFO sqlalchemy.engine.base.Engine.0x..94:
BEGIN
2008-11-27 14:06:52,084 INFO sqlalchemy.engine.base.Engine.0x..94:
SELECT persons.id AS persons_id, persons.name AS persons_name
FROM persons ORDER BY persons.id
LIMIT 1
2008-11-27 14:06:52,081 INFO sqlalchemy.engine.base.Engine.0x..94: []
2008-11-27 14:06:52,086 DEBUG sqlalchemy.engine.base.Engine.0x..94:
Col ('persons_id', 'persons_name')
2008-11-27 14:06:52,087 DEBUG sqlalchemy.engine.base.Engine.0x..94:
Row (1L, 'bob')
2008-11-27 14:06:52,091 DEBUG sqlalchemy.orm.mapper.Mapper: (Person|
persons) _instance(): identity key (class '__main__.Person', (1L,),
None) not in session
2008-11-27 14:06:52,092 DEBUG sqlalchemy.orm.mapper.Mapper: (Person|
persons) _instance(): created new instance [EMAIL PROTECTED] identity
(class '__main__.Person', (1L,), None)
2008-11-27 14:06:52,092 DEBUG sqlalchemy.orm.strategies.ColumnLoader:
Returning active column fetcher for Mapper|Person|persons id
2008-11-27 14:06:52,089 DEBUG sqlalchemy.orm.strategies.ColumnLoader:
Returning active column fetcher for Mapper|Person|persons name
2008-11-27 14:06:52,089 DEBUG sqlalchemy.orm.strategies.ColumnLoader:
populating [EMAIL PROTECTED] with RowProxy/id
2008-11-27 14:06:52,090 DEBUG sqlalchemy.orm.strategies.ColumnLoader:
populating [EMAIL PROTECTED] with RowProxy/name
2008-11-27 14:06:52,095 DEBUG root: QUERY 1 RESULT: [__main__.Person
object at 0xb78aa60c]
2008-11-27 14:06:52,095 DEBUG root: QUERY 2: SELECT persons.id AS
persons_id, persons.name AS persons_name
FROM persons
LIMIT 1
2008-11-27 14:06:52,096 INFO sqlalchemy.engine.base.Engine.0x..94:
SELECT persons.id AS persons_id, persons.name AS persons_name
FROM persons
LIMIT 1
2008-11-27 14:06:52,094 INFO sqlalchemy.engine.base.Engine.0x..94: {}
2008-11-27 14:06:52,099 DEBUG sqlalchemy.engine.base.Engine.0x..94:
Col ('persons_id', 'persons_name')
2008-11-27 14:06:52,100 DEBUG sqlalchemy.engine.base.Engine.0x..94:
Row (1L, 'bob')
2008-11-27 14:06:52,100 DEBUG root: QUERY 2: RESULT: [(1L, 'bob')]
--~--~-~--~~~---~--~~
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: set_shard problems
r5335 will most likely resolve this issue.
On Nov 26, 2008, at 5:21 AM, Ids wrote:
Hello,
I think I have found a bug, but I may be doing something wrong. It
looks like session.query(class).set_shard(shard_id) does not work
and session.connection(shard_id=shard_id).execute does. The first
does not return any result, the second one does (even when executing
the same query).
I've tested it with MySQL 3.23.54 and 5.0.45 and sqlalchemy 0.5.0rc1,
rc2 and rc4.
Here is the test database setup:
CREATE TABLE persons (
id INTEGER NOT NULL AUTO_INCREMENT,
name VARCHAR(20) NOT NULL,
PRIMARY KEY (id),
UNIQUE (name)
);
insert into persons (name) values('bob');
insert into persons (name) values('alice');
Here is the test code:
#!/opt/python-2.4/bin/python
import sys
import logging
import sqlalchemy as sa
from sqlalchemy.orm.shard import ShardedSession
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
logging.basicConfig(stream=sys.stdout, format='%(asctime)s %
(levelname)
s %(name)s: %(message)s')
logging.getLogger('sqlalchemy').setLevel(logging.DEBUG)
logging.getLogger().setLevel(logging.DEBUG)
Session = sessionmaker(class_=ShardedSession)
Base = declarative_base()
class Person(Base):
__tablename__ = 'persons'
id = sa.Column(sa.Integer, primary_key=True)
name = sa.Column(sa.String(20), unique=True, nullable=False)
def __str__(self):
return 'Person(%s, %s)' % (self.id, self.name)
def shard_chooser(mapper, instance, clause=None):
raise NotImplementedError
def id_chooser(query, ident):
raise NotImplementedError
def query_chooser(query):
raise NotImplementedError
Session.configure(shard_chooser=shard_chooser,
id_chooser=id_chooser,
query_chooser=query_chooser)
session = Session()
shard_id='test'
engine = sa.create_engine('mysql://[EMAIL PROTECTED]/%s' % shard_id)
session.bind_shard(shard_id, engine)
q = session.query(Person).set_shard(shard_id).limit(1)
logging.debug(QUERY 1: %s, q)
rows = list(q.all())
logging.debug(QUERY 1 RESULT: %s % rows)
#
# now to it manually:
#
q = '''SELECT persons.id AS persons_id, persons.name AS persons_name
FROM persons
LIMIT 1
'''
logging.debug(QUERY 2: %s, q)
rows = session.connection(shard_id=shard_id).execute(q)
rows = list(rows)
logging.debug(QUERY 2: RESULT: %s % rows)
And here is the code output:
2008-11-26 10:52:26,043 INFO sqlalchemy.orm.strategies.ColumnLoader:
Person.id register managed attribute
2008-11-26 10:52:26,044 INFO sqlalchemy.orm.strategies.ColumnLoader:
Person.name register managed attribute
2008-11-26 10:52:26,045 DEBUG root: QUERY 1: SELECT persons.id AS
persons_id, persons.name AS persons_name
FROM persons
LIMIT 1
2008-11-26 10:52:26,061 INFO sqlalchemy.pool.QueuePool.0x...8bf4:
Created new connection _mysql.connection open to 'localhost' at
82b02ec
2008-11-26 10:52:26,062 INFO sqlalchemy.pool.QueuePool.0x...8bf4:
Connection _mysql.connection open to 'localhost' at 82b02ec checked
out from pool
2008-11-26 10:52:26,062 INFO sqlalchemy.engine.base.Engine.0x...8a14:
BEGIN
2008-11-26 10:52:26,060 INFO sqlalchemy.engine.base.Engine.0x...8a14:
SELECT persons.id AS persons_id, persons.name AS persons_name
FROM persons
LIMIT 1
2008-11-26 10:52:26,064 INFO sqlalchemy.engine.base.Engine.0x...8a14:
[]
2008-11-26 10:52:26,066 DEBUG sqlalchemy.engine.base.Engine.0x...8a14:
Col ('persons_id', 'persons_name')
2008-11-26 10:52:26,070 DEBUG root: QUERY 1 RESULT: []
2008-11-26 10:52:26,070 DEBUG root: QUERY 2: SELECT persons.id AS
persons_id, persons.name AS persons_name
FROM persons
LIMIT 1
2008-11-26 10:52:26,071 INFO sqlalchemy.engine.base.Engine.0x...8a14:
SELECT persons.id AS persons_id, persons.name AS persons_name
FROM persons
LIMIT 1
2008-11-26 10:52:26,071 INFO sqlalchemy.engine.base.Engine.0x...8a14:
{}
2008-11-26 10:52:26,073 DEBUG sqlalchemy.engine.base.Engine.0x...8a14:
Col ('persons_id', 'persons_name')
2008-11-26 10:52:26,073 DEBUG sqlalchemy.engine.base.Engine.0x...8a14:
Row (1L, 'bob')
2008-11-26 10:52:26,074 DEBUG root: QUERY 2: RESULT: [(1L, 'bob')]
There are two things I notice in the sqlalchemy.Engine logs; the
second SELECT statement seems to have an additional newline and the
next log (which seem to be the parameters for the select statement)
contain a {} instead of a [].
Am I doing something wrong here or is this supposed to work?
Regards,
Ids
--~--~-~--~~~---~--~~
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
On Nov 27, 2008, at 1:15 AM, 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 this error is because you've deleted some rows that the mapper is expecting to delete itself due to a delete cascade. here is the full spectrum of approaches: 1. remove all delete cascades that reach A objects. I'm guessing this is not an option. 2. move your function into a SessionExtension.after_flush() that issues your DELETEs after the unit of work has completed its tasks. When you work with SessionExtensions within after_flush(), peek into the new, dirty, and deleted lists, which haven't been reset at that point, to get the information you need. This might be the easiest way to go. 3. it would probably work if you were to peek into the UOWContext itself to see if the A in question is already marked for deletion. This would also be pretty easy though I've never recommended this approach before so would have to be tested. But unfortunately the UOWContext isn't passed to the mapper extension methods.It is passed to the SessionExtension methods though, so you could grab it through one of those, something like: import threading from sqlalchemy.orm.attributes import instance_state current_flush_context = threading.local() class MySessionExt(SessionExtension): def before_flush(self, session, flush_context, instances): current_flush_context.context = flush_context def after_flush(self, session, flush_context, instances): del current_flush_context.context class MyMapperExt(MapperExtension): def after_delete(self, mapper, connection, instance): ... get your A object to be deleted ... if not current_flush_context.context.is_deleted(instance_state(the_A_object)): ... delete the A --~--~-~--~~~---~--~~ 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] Invalid Request Error at session level.
Hello, I am using SQLA 0.5rc2 on Py 2.5.2 with Elixir 0.6. I have a multithreaded application, and each thread gets a separate session object. All of them use the same disk based database. I get the below exception : Exception in thread RSIHostDBSync: Traceback (most recent call last): File c:\python25\lib\threading.py, line 486, in __bootstrap_inner self.run() File O:\RecogSys\src\python\RSITerm\RSIHostDBSync.py, line 978, in run InteractionsSent, InteractionsRemaining = self.SendInteractions() File O:\RecogSys\src\python\RSITerm\RSIHostDBSync.py, line 1366, in SendInte ractions iaKeyList = list(self.rsiDB.InteractionDB().keys()) File O:\RecogSys\src\python\RSITerm\SQLConvert\InteractionDBDict.py, line 24 , in keys return self.dbo.InteractionDBDict_Keys() File O:\RecogSys\src\python\RSITerm\SQLConvert\SqlDB.py, line 1045, in Inter actionDBDict_Keys filter_by(SentFlag = 0).all(): File c:\python25\lib\site-packages\sqlalchemy-0.5.0rc2-py2.5.egg\sqlalchemy\o rm\query.py, line 990, in all return list(self) File c:\python25\lib\site-packages\sqlalchemy-0.5.0rc2-py2.5.egg\sqlalchemy\o rm\query.py, line 1078, in __iter__ return self._execute_and_instances(context) File c:\python25\lib\site-packages\sqlalchemy-0.5.0rc2-py2.5.egg\sqlalchemy\o rm\query.py, line 1081, in _execute_and_instances result = self.session.execute(querycontext.statement, params=self._params, m apper=self._mapper_zero_or_none(), _state=self._refresh_state) File c:\python25\lib\site-packages\sqlalchemy-0.5.0rc2-py2.5.egg\sqlalchemy\o rm\session.py, line 749, in execute return self.__connection(engine, close_with_result=True).execute( File c:\python25\lib\site-packages\sqlalchemy-0.5.0rc2-py2.5.egg\sqlalchemy\o rm\session.py, line 716, in __connection return self.transaction._connection_for_bind(engine) File c:\python25\lib\site-packages\sqlalchemy-0.5.0rc2-py2.5.egg\sqlalchemy\o rm\session.py, line 309, in _connection_for_bind self._assert_is_active() File c:\python25\lib\site-packages\sqlalchemy-0.5.0rc2-py2.5.egg\sqlalchemy\o rm\session.py, line 244, in _assert_is_active The transaction is inactive due to a rollback in a InvalidRequestError: The transaction is inactive due to a rollback in a subtrans action. Issue rollback() to cancel the transaction. I am guarding my commits with a mutex, but the queries are unguarded. Before I get the above exception, I dont see any other messages regarding the transaction rollback that is mentioned here before this exception occurs. Could this be a problem with the way I have implemented sessions? If one of the session is committing on the database and the other one is trying to query, would this problem arise? I am not very sure on how connection objects are created during this scenario. Appreciate your help. Regards, Harish --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
