Re: [sqlalchemy] post_update issue
Great! Thanks a lot. Thorsten -- 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.
Re: [sqlalchemy] post_update issue
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__ == '__
[sqlalchemy] post_update issue
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(sessio
