Relationships not being set to None when related object is deleted

[Chris Wilson]

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.

[Mike Bayer]

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.

> --
> 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 sqlalchemy+...@googlegroups.com
> <mailto:sqlalchemy+...@googlegroups.com>.
> To post to this group, send email to sqlal...@googlegroups.com
> <mailto:sqlal...@googlegroups.com>.
> Visit this group at http://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.

[Chris Wilson]

Hi Michael,

Thanks for replying so quickly!

On Wednesday, 28 October 2015 15:04:56 UTC, Michael Bayer wrote:

 

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.

I think some of my examples do do that (or try to). For example:

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)

In this case, muffin has a foreign key to flat, which we delete. muffin.id_house is set to None (correctly) but muffin.house is not (I was hoping it would be).

Also, in the case of a many-to-many relationship (Cat and House), which is the parent object and which is the child? 

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.

OK, I hadn't thought of that, it might work but in fact I want to delete a Cat when it has no Houses left (House -> Cat in this case, with uselist=True instead of uselist=False as in the example above) and it's not clear that delete-orphan can reliably do this to the "one" side of the relationship, can it? The documentation seems to imply not:

delete-orphan cascade implies that each child object can only have one parent at a time,

So there isn't an existing mechanism other than session.delete() that I can see, am I missing something?

Thanks, Chris.

[Mike Bayer]

On 10/28/15 1:26 PM, Chris Wilson wrote:
> OK, I hadn't thought of that, it might work but in fact I want to delete
> a Cat when it has no Houses left (House -> Cat in this case, with
> uselist=True instead of uselist=False as in the example above) and it's
> not clear that delete-orphan can reliably do this to the "one" side of
> the relationship, can it? The documentation seems to imply not:

if you want to delete an object when one of its collections are empty,
use events for that. I have an in-depth answer to the "remove an object
when all of its many-to-many children are gone" question here:

http://stackoverflow.com/questions/9234082/setting-delete-orphan-on-sqlalchemy-relationship-causes-assertionerror-this-att/9264556#9264556

>
> |delete-orphan| cascade implies that each child object can only have
> one parent at a time,
>
>
> So there isn't an existing mechanism other than session.delete() that I
> can see, am I missing something?
>
> Thanks, Chris.
>