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.
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.
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.
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.
