On 10/28/15 10:42 AM, Chris Wilson wrote:
> 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:
>
>
...
> 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, [])
OK, sorry, you've misunderstood the documentation. When it says
"delete", it refers to the *parent* object that refers to the *child*,
that is, the parent is the one that the foreign key refers *to*, and the
child is the one that has a foreign key reference to the parent. This
is the opposite case. You're deleting one of the objects that
references the parent, that does not inherently affect the parent until
it is freshly re-loaded from the database, such as after a commit.
This is why it's better to deal with manipulation of the collection,
instead of using session.delete(). See the section
http://docs.sqlalchemy.org/en/rel_1_0/orm/session_basics.html#deleting-from-collections.
I haven't gone further in your examples past that one, hopefully this is
the source of confusion for all of them.
>
> 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]
> <mailto:[email protected]>.
> To post to this group, send email to [email protected]
> <mailto:[email protected]>.
> Visit this group at http://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.
--
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.
