Hi all,
I'm having problems with relationships getting out of sync with the
underlying foreign key properties, and hoping that someone can help.
It seems from the manual (if I've understood correctly), that deleting one
side of a relationship should set foreign keys on the other side to
NULL/None:
if our User.addresses relationship does *not* have delete cascade,
SQLAlchemy’s default behavior is to instead de-associate address1 and
address2 from user1 by setting their foreign key reference to NULL.
However this does not appear to extend to relationship properties, which
remain set to the old values even after a flush.
Here is some example code:
from sqlalchemy import Column, FetchedValue, ForeignKey, Integer, MetaData,
String, Table, create_engine, event
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import backref, relationship, sessionmaker
Base = declarative_base()
class House(Base):
__tablename__ = "house"
id = Column('id', Integer, primary_key=True)
name = Column(String)
dogs = relationship('Dog', backref=backref("house", uselist=False))
def __repr__(self):
return "House({})".format(self.name)
class Cat(Base):
''' Cat has a many-to-many relationship via a secondary table, but
constrained to be unique and single-valued
on both sides.
'''
__tablename__ = "cat"
id = Column('id', Integer, primary_key=True)
name = Column(String)
house_cat = Table("house_cat", Base.metadata,
Column("id_house", Integer, ForeignKey(House.id),
primary_key=True),
Column("id_cat", Integer, ForeignKey(id),
unique=True))
house = relationship(House, secondary=house_cat, uselist=False,
backref=backref("cat", uselist=False))
def __repr__(self):
return "Cat({})".format(self.name)
class Dog(Base):
''' Dog has a foreign key and a many-to-one to House, with a
one-to-many backref. '''
__tablename__ = "dog"
id = Column('id', Integer, primary_key=True)
name = Column(String)
id_house = Column(Integer, ForeignKey(House.id), unique=True)
# house = relationship(House, uselist=False, backref=backref("dogs"))
def __repr__(self):
return "Dog({})".format(self.name)
engine = create_engine('sqlite://')
Session = sessionmaker(bind=engine)
Base.metadata.create_all(engine)
session = Session()
# session.bind.echo = True
def debug(desc, boolean, left, right):
print "{:<40} {:<10} {:<20} {:<20}".format(desc, str(boolean), left,
right)
bedsit = House(name='bedsit')
snoopy = Dog(name='snoopy', house=bedsit)
session.add(bedsit)
session.flush()
debug("bedsit.dogs == [snoopy]", bedsit.dogs == [snoopy], bedsit.dogs,
[snoopy])
debug("snoopy.id_house == bedsit.id", snoopy.id_house == bedsit.id,
snoopy.id_house, bedsit.id)
debug("snoopy.house is bedsit", snoopy.house is bedsit, snoopy.house,
bedsit)
session.delete(snoopy)
debug("bedsit.dogs == []", bedsit.dogs == [], bedsit.dogs, [])
flat = House(name='flat')
muffin = Dog(name='muffin', house=flat)
session.add(flat)
session.flush()
debug("flat.dogs == [muffin]", flat.dogs == [muffin], flat.dogs, [muffin])
debug("muffin.id_house == flat.id", muffin.id_house == flat.id,
muffin.id_house, flat.id)
debug("muffin.house == flat", muffin.house == flat, muffin.house, flat)
session.delete(flat)
session.flush()
debug("muffin.id_house is None", muffin.id_house is None, muffin.id_house,
None)
debug("muffin.house is None", muffin.house is None, muffin.house, None)
mansion = House(name='mansion')
koshka = Cat(name='koshka', house=mansion)
session.add(mansion)
session.flush()
debug("mansion.cat is koshka", mansion.cat is koshka, mansion.cat, koshka)
debug("koshka.house == mansion", koshka.house == mansion, koshka.house,
mansion)
session.delete(koshka)
debug("mansion.cat is None", mansion.cat is None, mansion.cat, None)
session.rollback()
This prints (I hope this preformatted table comes out OK for everyone):
Assertion Passes Actual value Expected value
========= ====== ============ ==============
bedsit.dogs == [snoopy] True [Dog(snoopy)] [Dog(snoopy)]
snoopy.id_house == bedsit.id True 1 1
snoopy.house is bedsit True House(bedsit) House(bedsit)
bedsit.dogs == [] False [Dog(snoopy)] []
flat.dogs == [muffin] True [Dog(muffin)] [Dog(muffin)]
muffin.id_house == flat.id True 2 2
muffin.house == flat True House(flat) House(flat)
muffin.id_house is None True None None
muffin.house is None False House(flat) None
mansion.cat is koshka True Cat(koshka) Cat(koshka)
koshka.house == mansion True House(mansion) House(mansion)
mansion.cat is None False Cat(koshka) None
Ideally (for me :)) the second column would be True in all cases,
indicating that the relationship has been synchronised with the underlying
FK property. This is causing a problem for me because I delete a related
object inside a transaction; later on I check whether the primary object
has a related object and delete it if so; and if I end up processing the
same object again, I get an error because the primary object refers to a
deleted object, and deleting it again tries to delete rows (from the
secondary table) that are already deleted:
self.transaction.__exit__(a_type, value, traceback)
File
"R:\sw\external\20151014-0\python27\lib\site-packages\sqlalchemy-1.0.8-py2.7-win32.egg\sqlalchemy\orm\session.py",
line 490, in __exit__
self.rollback()
File
"R:\sw\external\20151014-0\python27\lib\site-packages\sqlalchemy-1.0.8-py2.7-win32.egg\sqlalchemy\util\langhelpers.py",
line 60, in __exit__
compat.reraise(exc_type, exc_value, exc_tb)
File
"R:\sw\external\20151014-0\python27\lib\site-packages\sqlalchemy-1.0.8-py2.7-win32.egg\sqlalchemy\orm\session.py",
line 487, in __exit__
self.commit()
File
"R:\sw\external\20151014-0\python27\lib\site-packages\sqlalchemy-1.0.8-py2.7-win32.egg\sqlalchemy\orm\session.py",
line 392, in commit
self._prepare_impl()
File
"R:\sw\external\20151014-0\python27\lib\site-packages\sqlalchemy-1.0.8-py2.7-win32.egg\sqlalchemy\orm\session.py",
line 372, in _prepare_impl
self.session.flush()
File
"R:\sw\external\20151014-0\python27\lib\site-packages\sqlalchemy-1.0.8-py2.7-win32.egg\sqlalchemy\orm\session.py",
line 2004, in flush
self._flush(objects)
File
"R:\sw\external\20151014-0\python27\lib\site-packages\sqlalchemy-1.0.8-py2.7-win32.egg\sqlalchemy\orm\session.py",
line 2122, in _flush
transaction.rollback(_capture_exception=True)
File
"R:\sw\external\20151014-0\python27\lib\site-packages\sqlalchemy-1.0.8-py2.7-win32.egg\sqlalchemy\util\langhelpers.py",
line 60, in __exit__
compat.reraise(exc_type, exc_value, exc_tb)
File
"R:\sw\external\20151014-0\python27\lib\site-packages\sqlalchemy-1.0.8-py2.7-win32.egg\sqlalchemy\orm\session.py",
line 2086, in _flush
flush_context.execute()
File
"R:\sw\external\20151014-0\python27\lib\site-packages\sqlalchemy-1.0.8-py2.7-win32.egg\sqlalchemy\orm\unitofwork.py",
line 373, in execute
rec.execute(self)
File
"R:\sw\external\20151014-0\python27\lib\site-packages\sqlalchemy-1.0.8-py2.7-win32.egg\sqlalchemy\orm\unitofwork.py",
line 485, in execute
self.dependency_processor.process_deletes(uow, states)
File
"R:\sw\external\20151014-0\python27\lib\site-packages\sqlalchemy-1.0.8-py2.7-win32.egg\sqlalchemy\orm\dependency.py",
line 1023, in process_deletes
secondary_update, secondary_delete)
File
"R:\sw\external\20151014-0\python27\lib\site-packages\sqlalchemy-1.0.8-py2.7-win32.egg\sqlalchemy\orm\dependency.py",
line 1111, in _run_crud
result.rowcount)
StaleDataError: DELETE statement on table 'blah' expected to delete 2
row(s); Only 0 were matched.
I can just assign None to the relationship attribute after deleting the
related object, but it seems to me that this will trip people up quite
often and unexpectedly, and that SQLAlchemy would be easier to use if it
was automatic.
Even better would be if deleting the related object immediately cleared the
relationship (even before a flush), and assigning to an FK column
immediately updated the relationship property and vice versa, but these may
be outside of the design goals of SQLAlchemy. In some cases I could
implement this manually, using an event listener on the FK column to update
the relationship immediately, and vice versa, but again it seems that it
would make SQLAlchemy easier to use if it was automatic.
I can see that there are synchronized properties which null out the FK
column on the other side of a relationship when an object is deleted (and
this is flushed to the database), but no synchronised properties for the
relationship property. I looked at adding one of these for synchronising
relationships, but it's quite complex with all the edge cases.
Can anyone see a better way of doing this, or a reason why it should not be
done at all? Thanks in advance for your help!
Chris.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
