Hi,
I have a many-many relationship implemented as an Association Object.
When I add an association between a parent and a child, the list of
associations within the parent contains the child element twice. A
flush() does not help, but commit() resolves the issue. The
generated SQL only creates one INSERT, though (as expected).
The attached script can be used to reproduce the issue. The objects are
directly from the bi-directional "Association Object" example in the
0.9 docs (parent and child have an extra attribute "name", though). The
rest of the code just adds instances of these objects to the session.
On my system (Debian Jessie, SA 0.9.4), this script produces the
following output:
0 some data <__main__.Child object at 0x7ffb63d81fd0>
1 some data <__main__.Child object at 0x7ffb63d81fd0>
--FLUSH--
0 some data <__main__.Child object at 0x7ffb63d81fd0>
1 some data <__main__.Child object at 0x7ffb63d81fd0>
--COMMIT--
0 some data <__main__.Child object at 0x7ffb63d81fd0>
Side question: what's the "proper" way to remove a many-many association
between two entities? As in: both entities continue to exist, it's just
the association that is removed (eg: entity "software", entity "os",
assoc "runs_on"). What I currently do is iterate over the associations
of the parent and remove the matched child, but that seems inefficient.
Christian
(re-submitted because apparently being subscribed to the ML is required
to post to i)
--
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.
#!/usr/bin/python
from sqlalchemy import Column, ForeignKey, Integer, String, create_engine
from sqlalchemy.orm import relationship, sessionmaker
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class Association(Base):
__tablename__ = 'association'
left_id = Column(Integer, ForeignKey('left.id'), primary_key=True)
right_id = Column(Integer, ForeignKey('right.id'), primary_key=True)
extra_data = Column(String(50))
child = relationship("Child", backref="parent_assocs")
class Parent(Base):
__tablename__ = 'left'
id = Column(Integer, primary_key=True)
name = Column(String, nullable=False, unique=True)
children = relationship("Association", backref="parent")
class Child(Base):
__tablename__ = 'right'
id = Column(Integer, primary_key=True)
name = Column(String, nullable=False, unique=True)
# PREPARE: Create tables, establish session, add 1 parent, child, commit
engine = create_engine('sqlite:///:memory:')
Session = sessionmaker(bind=engine)
Base.metadata.create_all(engine)
session = Session()
session.add(Parent(name='parent_name'))
session.add(Child(name='child_name'))
session.commit()
# Associate the parent with the child
# Query for parent, child because that's how the code will eventually be used
# (from within a method). I just flattened it for this example.
a = Association(extra_data="some data")
par = session.query(Parent).filter_by(name='parent_name').one()
chi = session.query(Child).filter_by(name='child_name').one()
a.child = chi
# SIDE QUESTION: Why is the following line required for association objects?
# With an association TABLE (following the doc's many-many example), omitting
# this works; with an assoc OBJECT the next line (append) raises IntegrityError
a.parent = par
par.children.append(a)
# QUESTION: Why are the Associations in p.children duplicated?
# Observe the changed behavior after flush / commit below!
p = session.query(Parent).filter_by(name='parent_name').one()
for idx, assoc in enumerate(p.children):
print idx, assoc.extra_data, assoc.child
print '--FLUSH--'
session.flush()
# Flush has no effect...
for idx, assoc in enumerate(p.children):
print idx, assoc.extra_data, assoc.child
# Commit resolves the issue!
print '--COMMIT--'
session.commit()
for idx, assoc in enumerate(p.children):
print idx, assoc.extra_data, assoc.child
