Myujth

SQLAlchemy nicely documents how to use Association Objects with back_populates.

However, when copy-and-pasting the example from that documentation, adding children to a parent throws a KeyError as following code shows. The model classes are copied 100% from the documentation:

from sqlalchemy import Column, ForeignKey, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship
from sqlalchemy.schema import MetaData

Base = declarative_base(metadata=MetaData())

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", back_populates="parents")
 parent = relationship("Parent", back_populates="children")

class Parent(Base):
 __tablename__ = 'left'
 id = Column(Integer, primary_key=True)
 children = relationship("Association", back_populates="parent")

class Child(Base):
 __tablename__ = 'right'
 id = Column(Integer, primary_key=True)
 parents = relationship("Association", back_populates="child")

parent = Parent(children=[Child()])

Running that code with SQLAlchemy version 1.2.11 throws this exception:

lars$ venv/bin/python test.py
Traceback (most recent call last):
 File "test.py", line 26, in <module>
 parent = Parent(children=[Child()])
 File "<string>", line 4, in __init__
 File "/Users/lars/coding/sqlalchemy_association_object_test/venv/lib/python3.7/site-packages/sqlalchemy/orm/state.py", line 417, in _initialize_instance
 manager.dispatch.init_failure(self, args, kwargs)
 File "/Users/lars/coding/sqlalchemy_association_object_test/venv/lib/python3.7/site-packages/sqlalchemy/util/langhelpers.py", line 66, in __exit__
 compat.reraise(exc_type, exc_value, exc_tb)
 File "/Users/lars/coding/sqlalchemy_association_object_test/venv/lib/python3.7/site-packages/sqlalchemy/util/compat.py", line 249, in reraise
 raise value
 File "/Users/lars/coding/sqlalchemy_association_object_test/venv/lib/python3.7/site-packages/sqlalchemy/orm/state.py", line 414, in _initialize_instance
 return manager.original_init(*mixed[1:], **kwargs)
 File "/Users/lars/coding/sqlalchemy_association_object_test/venv/lib/python3.7/site-packages/sqlalchemy/ext/declarative/base.py", line 737, in _declarative_constructor
 setattr(self, k, kwargs[k])
 File "/Users/lars/coding/sqlalchemy_association_object_test/venv/lib/python3.7/site-packages/sqlalchemy/orm/attributes.py", line 229, in __set__
 instance_dict(instance), value, None)
 File "/Users/lars/coding/sqlalchemy_association_object_test/venv/lib/python3.7/site-packages/sqlalchemy/orm/attributes.py", line 1077, in set
 initiator=evt)
 File "/Users/lars/coding/sqlalchemy_association_object_test/venv/lib/python3.7/site-packages/sqlalchemy/orm/collections.py", line 762, in bulk_replace
 appender(member, _sa_initiator=initiator)
 File "/Users/lars/coding/sqlalchemy_association_object_test/venv/lib/python3.7/site-packages/sqlalchemy/orm/collections.py", line 1044, in append
 item = __set(self, item, _sa_initiator)
 File "/Users/lars/coding/sqlalchemy_association_object_test/venv/lib/python3.7/site-packages/sqlalchemy/orm/collections.py", line 1016, in __set
 item = executor.fire_append_event(item, _sa_initiator)
 File "/Users/lars/coding/sqlalchemy_association_object_test/venv/lib/python3.7/site-packages/sqlalchemy/orm/collections.py", line 680, in fire_append_event
 item, initiator)
 File "/Users/lars/coding/sqlalchemy_association_object_test/venv/lib/python3.7/site-packages/sqlalchemy/orm/attributes.py", line 943, in fire_append_event
 state, value, initiator or self._append_token)
 File "/Users/lars/coding/sqlalchemy_association_object_test/venv/lib/python3.7/site-packages/sqlalchemy/orm/attributes.py", line 1210, in emit_backref_from_collection_append_event
 child_impl = child_state.manager[key].impl
KeyError: 'parent'

I've filed this as a bug in SQLAlchemy's issue tracker. Maybe somebody can point me to a working solution or workaround in the meanwhile?

tldr; We have to use Association Proxy extensions and create a custom constructor for the association object which takes the child object as the first (!) parameter. See solution based on the example from the question below.

SQLAlchemy's documentation actually states in the next paragraph that we aren't done yet if we want to directly add Child models to Parent models while skipping the intermediary Association models:

Working with the association pattern in its direct form requires that
child objects are associated with an association instance before being
appended to the parent; similarly, access from parent to child goes
through the association object.

# create parent, append a child via association
p = Parent()
a = Association(extra_data="some data")
a.child = Child()
p.children.append(a)

To write convient code such as requested in the question, i.e. p.children = [Child()], we have to make use of the Association Proxy extension.

Here is the solution using an Association Proxy extension which allows to add children to a parent "directly" without explicitly creating an association between both of them:

from sqlalchemy import Column, ForeignKey, Integer, String
from sqlalchemy.ext.associationproxy import association_proxy
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import backref, relationship
from sqlalchemy.schema import MetaData

Base = declarative_base(metadata=MetaData())

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", back_populates="parents")
 parent = relationship("Parent", backref=backref("parent_children"))

 def __init__(self, child=None, parent=None):
 self.parent = parent
 self.child = child

class Parent(Base):
 __tablename__ = 'left'
 id = Column(Integer, primary_key=True)
 children = association_proxy("parent_children", "child")

class Child(Base):
 __tablename__ = 'right'
 id = Column(Integer, primary_key=True)
 parents = relationship("Association", back_populates="child")

p = Parent(children=[Child()])

Unfortunately I only figured out how to use backref instead of back_populates which isn't the "modern" approach.

Pay special attention to create a custom __init__ method which takes the child as the first argument.