On 12/01/2015 07:24 PM, Thorsten von Stein wrote: > The following issue, which puzzled for several days, exists in several > versions. I verified that it applies to version 1.0.9 > > In my understanding, the post_update flag is required on a relationship > between to classes A and B if there is another relationship between B > and A in order to break a circular dependency that can result from > adding new instances in both classes that are mutually related. > post_update=True means that the foreign key for the relationship is set > in a separate UPDATE query after completion of the INSERT queries. For > reasons that are not clear to me, a separate UPDATE query for the > foreign is also issued for normal updates of the containing instance.
this sounds like issue https://bitbucket.org/zzzeek/sqlalchemy/issues/1063/self-referential-flush-should-not-require which is a long-term TODO. > > However, there is a particular case in which the UPDATE of the foreign > is left out: When the relationship property is set to an instance > without having been accessed in the session and is then set to Null, > then required UPDATE query for the foreign key is not issued. that is a bug, and is: https://bitbucket.org/zzzeek/sqlalchemy/issues/3599/post_update-does-not-honor-many-to-one this bug is fixed in master and rel_1_0 and will be in release 1.0.10. An immediate workaround for this issue short of the new release is to apply active history to the relationship in question so that setting it to None does load the prior version: topElement = relationship("Element", post_update=True, active_history=True, primaryjoin='Container.topElementID==remote(Element.id)') > > Please consider the test case below with a Container class and an > Element class. There are relationships Element.container to Container > and Container.topElement to Element. The latter is defined with the > post_update flag. A container with two elements is created. Then the > following steps are taken, to rule out any interference in separate > sessions: > > 1. Set the container's topElement to one of the elements. This works > normally. > 2. Set the container's topElement to None without prior read access to > the topElement property. This does not work. > 3. Repeat the second step, but this time after accessing the topElement > property. Then the update works. > > Any thoughts on this issue? I wonder whether the post_update behavior > should be limited to cases where actually new instances are inserted > because only those can lead to circular dependencies. > > Thanks, > > Thorsten > > > > from sqlalchemy import Column, ForeignKey, Integer, String, create_engine > from sqlalchemy.orm import Session, relationship, backref > from sqlalchemy.ext.declarative import declarative_base, declared_attr > from contextlib import contextmanager > > > > Base = declarative_base() > > > class Container(Base): > __tablename__ = 'container' > id = Column(Integer, primary_key=True) > topElementID = Column(Integer, ForeignKey('element.id', > name='fk_topelement', use_alter=True)) > name = Column(String(50), nullable=False) > > topElement = relationship("Element", > post_update=True, > primaryjoin='Container.topElementID==remote(Element.id)') > > def __init__(self, name): > self.name = name > > def __repr__(self): > return "Container(name=%r, id=%r, topElementID=%r)" % ( > self.name, > self.id, > self.topElement > ) > > def dump(self): > return repr(self) + '\n' + '\n'.join(repr(e) for e in self.elements) > > > class Element(Base): > __tablename__ = 'element' > id = Column(Integer, primary_key=True) > containerID = Column(Integer, ForeignKey('container.id')) > name = Column(String(50), nullable=False) > > container = relationship("Container", > primaryjoin='Element.containerID==remote(Container.id)', > backref=backref("elements", > primaryjoin='remote(Element.containerID)==Container.id'), > ) > > def __init__(self, name, container=None): > self.name = name > self.container = container > > def __repr__(self): > return "Element(name=%r, id=%r, containerID=%r)" % ( > self.name, > self.id, > self.containerID > ) > > @contextmanager > def new_session(engine): > session = Session(engine) > yield session > session.commit() > session.close() > > def msg(msg, *args): > msg = msg % args > print("\n\n\n" + "-" * len(msg.split("\n")[0])) > print(msg) > print("-" * len(msg.split("\n")[0])) > > def getContainer(session, cid): > return session.query(Container).filter(Container.id==cid).one() > > > if __name__ == '__main__': > # engine = > create_engine('mysql://testdb:testpass@localhost/testdb?charset=utf8&use_unicode=0', > echo=True) > engine = create_engine('sqlite://', echo=True) > > Base.metadata.create_all(engine) > session = Session(engine) > > # Creating a container with two elements; obtaining container ID for > subsequent retrieval > with new_session(engine) as session: > container = Container('My container') > Element('a', container=container) > Element('b', container=container) > session.add(container) > session.flush() > cID = container.id > > # Setting container's topElement to the element with name b > with new_session(engine) as session: > container = getContainer(session, cID) > container.topElement = next(e for e in container.elements if > e.name == 'b') > > with new_session(engine) as session: > msg("After setting topElement=b:\n %s", getContainer(session, > cID).dump()) > > > # Setting container's topElement to None > with new_session(engine) as session: > container = getContainer(session, cID) > container.topElement = None > > with new_session(engine) as session: > msg("After first attempt to set topElement=None:\n %s", > getContainer(session, cID).dump()) > > > # Trying again after making sure the session has accessed the prior > top element > with new_session(engine) as session: > container = getContainer(session, cID) > container.topElement > container.topElement = None > > with new_session(engine) as session: > msg("After second attempt to set topElement=None:\n %s", > getContainer(session, cID).dump()) > > > > > This is the output: > > --------------------------- > After setting topElement=b: > Container(name=u'My container', id=1, topElementID=Element(name=u'b', > id=2, containerID=1)) > Element(name=u'a', id=1, containerID=1) > Element(name=u'b', id=2, containerID=1) > --------------------------- > 2015-12-01 15:56:48,700 INFO sqlalchemy.engine.base.Engine COMMIT > 2015-12-01 15:56:48,700 INFO sqlalchemy.engine.base.Engine BEGIN (implicit) > 2015-12-01 15:56:48,700 INFO sqlalchemy.engine.base.Engine SELECT > container.id AS container_id, container."topElementID" AS > "container_topElementID", container.name AS container_name > FROM container > WHERE container.id = ? > 2015-12-01 15:56:48,700 INFO sqlalchemy.engine.base.Engine (1,) > 2015-12-01 15:56:48,701 INFO sqlalchemy.engine.base.Engine COMMIT > 2015-12-01 15:56:48,701 INFO sqlalchemy.engine.base.Engine BEGIN (implicit) > 2015-12-01 15:56:48,702 INFO sqlalchemy.engine.base.Engine SELECT > container.id AS container_id, container."topElementID" AS > "container_topElementID", container.name AS container_name > FROM container > WHERE container.id = ? > 2015-12-01 15:56:48,702 INFO sqlalchemy.engine.base.Engine (1,) > 2015-12-01 15:56:48,703 INFO sqlalchemy.engine.base.Engine SELECT > element.id AS element_id, element."containerID" AS > "element_containerID", element.name AS element_name > FROM element > WHERE element.id = ? > 2015-12-01 15:56:48,703 INFO sqlalchemy.engine.base.Engine (2,) > 2015-12-01 15:56:48,704 INFO sqlalchemy.engine.base.Engine SELECT > element.id AS element_id, element."containerID" AS > "element_containerID", element.name AS element_name > FROM element > WHERE element."containerID" = ? > 2015-12-01 15:56:48,704 INFO sqlalchemy.engine.base.Engine (1,) > > > > ------------------------------------------- > After first attempt to set topElement=None: > Container(name=u'My container', id=1, topElementID=Element(name=u'b', > id=2, containerID=1)) > Element(name=u'a', id=1, containerID=1) > Element(name=u'b', id=2, containerID=1) > ------------------------------------------- > 2015-12-01 15:56:48,704 INFO sqlalchemy.engine.base.Engine COMMIT > 2015-12-01 15:56:48,705 INFO sqlalchemy.engine.base.Engine BEGIN (implicit) > 2015-12-01 15:56:48,705 INFO sqlalchemy.engine.base.Engine SELECT > container.id AS container_id, container."topElementID" AS > "container_topElementID", container.name AS container_name > FROM container > WHERE container.id = ? > 2015-12-01 15:56:48,705 INFO sqlalchemy.engine.base.Engine (1,) > 2015-12-01 15:56:48,706 INFO sqlalchemy.engine.base.Engine SELECT > element.id AS element_id, element."containerID" AS > "element_containerID", element.name AS element_name > FROM element > WHERE element.id = ? > 2015-12-01 15:56:48,706 INFO sqlalchemy.engine.base.Engine (2,) > 2015-12-01 15:56:48,707 INFO sqlalchemy.engine.base.Engine UPDATE > container SET "topElementID"=? WHERE container.id = ? > 2015-12-01 15:56:48,707 INFO sqlalchemy.engine.base.Engine (None, 1) > 2015-12-01 15:56:48,707 INFO sqlalchemy.engine.base.Engine COMMIT > 2015-12-01 15:56:48,708 INFO sqlalchemy.engine.base.Engine BEGIN (implicit) > 2015-12-01 15:56:48,708 INFO sqlalchemy.engine.base.Engine SELECT > container.id AS container_id, container."topElementID" AS > "container_topElementID", container.name AS container_name > FROM container > WHERE container.id = ? > 2015-12-01 15:56:48,708 INFO sqlalchemy.engine.base.Engine (1,) > 2015-12-01 15:56:48,709 INFO sqlalchemy.engine.base.Engine SELECT > element.id AS element_id, element."containerID" AS > "element_containerID", element.name AS element_name > FROM element > WHERE element."containerID" = ? > 2015-12-01 15:56:48,709 INFO sqlalchemy.engine.base.Engine (1,) > > > > -------------------------------------------- > After second attempt to set topElement=None: > Container(name=u'My container', id=1, topElementID=None) > Element(name=u'a', id=1, containerID=1) > Element(name=u'b', id=2, containerID=1) > -------------------------------------------- > > -- > 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+unsubscr...@googlegroups.com > <mailto:sqlalchemy+unsubscr...@googlegroups.com>. > To post to this group, send email to sqlalchemy@googlegroups.com > <mailto:sqlalchemy@googlegroups.com>. > 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 sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
