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}]
Interestingly, if I change the "dog" relation on the "Friendship"
mapper to not have a cascade rule, the delete works correctly -
however that means dog records won't get deleted if they get orphaned.
Just for reference, I run this script as follows:
# to build the database
% ./db.py --build
# to find out the id's I want to be watching using the db cli
% ./db.py
% ./db.py --del
Cheers
Dave
---
#!/usr/bin/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__":
from optparse import OptionParser
parser = OptionParser()
parser.add_option("--build", dest="build", action="store_true",
default=False)
parser.add_option("--del", dest="delete", action="store_true",
default=False)
(options, args) = parser.parse_args()
engine = sa.create_engine(
"postgres://dave:[EMAIL PROTECTED]:5432/satest",
#"mysql://dave:[EMAIL PROTECTED]:3306/satest",
#"sqlite:///:memory:",
strategy="threadlocal",
echo=True
)
metadata.bind = engine
session.configure(bind=engine)
if options.build:
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()
print "HERE HERE HERE ================================="
owner = sa.orm.Query(Owner).first()
for f in owner.friendships:
print "FRIENDSHIP: %s || DOG: %s" % (f.id, f.dog.id)
if options.delete:
print "HERE HERE HERE ================================="
session.delete(owner)
session.flush()
session.commit()
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
