KeyError when adding objects to SQLAlchemy association object

python, sqlalchemy

Solution

The problem with your code is in the `.__init__` method. If you are to `debug-watch/print()` the parameters, you will notice that the parameter `tablet` is actually an instance of `Correspondent`:

class Tablet_Correspondent(db.Model):
    def __init__(self, tablet=None, correspondent=None):
        print "in __init__: ", tablet, correspondent
        self.tablet = tablet
        self.correspondent = correspondent

The reason for this is the way SA creates new values. From documentation Creation of New Values:

When a list `append()` event (or set `add()`, dictionary `__setitem__()`, or scalar assignment event) is intercepted by the association proxy, it instantiates a new instance of the “intermediary” object using its constructor, passing as a single argument the given value.

In your case when you call `tab.correspondents.append(cor)`, the `Tablet_Correspondent.__init__` is called with single argument `cor`.

Solution? If you will only be adding `Correspondents` to the `Tablet`, then just switch the parameters in the `__init__`. In fact, remove the second parameter completely. If, however, you will also be using `cor.tablets.append(tab)`, then you need to explicitely use the `creator` argument to the `association_proxy` as explained in the documentation linked to above:

class Tablet(db.Model, GlyphMixin):
    # ...
    correspondents = association_proxy('tablet_correspondents', 'correspondent', creator=lambda cor: Tablet_Correspondent(correspondent=cor))

class Correspondent(db.Model, GlyphMixin):
    # ...
    tablets = association_proxy('correspondent_tablets', 'tablet', creator=lambda tab: Tablet_Correspondent(tablet=tab))

Problem

I have two tables, `tablet` and `correspondent`: ``` class Correspondent(db.Model, GlyphMixin): # PK column and tablename etc. come from the mixin name = db.Column(db.String(100), nullable=False, unique=True) # association proxy tablets = association_proxy('correspondent_tablets', 'tablet') def __init__(self, name, tablets=None): self.name = name if tablets: self.tablets = tablets class Tablet(db.Model, GlyphMixin): # PK column and tablename etc. come from the mixin area = db.Column(db.String(100), nullable=False, unique=True) # association proxy correspondents = association_proxy('tablet_correspondents', 'correspondent') def __init__(self, area, correspondents=None): self.area = area if correspondents: self.correspondents = correspondents class Tablet_Correspondent(db.Model): __tablename__ = "tablet_correspondent" tablet_id = db.Column("tablet_id", db.Integer(), db.ForeignKey("tablet.id"), primary_key=True) correspondent_id = db.Column("correspondent_id", db.Integer(), db.ForeignKey("correspondent.id"), primary_key=True) # relations tablet = db.relationship( "Tablet", backref="tablet_correspondents", cascade="all, delete-orphan", single_parent=True) correspondent = db.relationship( "Correspondent", backref="correspondent_tablets", cascade="all, delete-orphan", single_parent=True) def __init__(self, tablet=None, correspondent=None): self.tablet = tablet self.correspondent = correspondent ``` I can add records to `tablet` and `correspondent`, and doing e.g. `Tablet.query.first().correspondents` simply returns an empty list, as you would expect. If I manually insert a row into my `tablet_correspondent` table using existing tablet and correspondent IDs, the list is populated, again as you would expect. However, if I try to do ``` cor = Correspondent.query.first() tab = Tablet.query.first() tab.correspondents.append(cor) ``` I get: ``` KeyError: 'tablet_correspondents' ``` I'm pretty sure I'm leaving out something fairly elementary here.

Original source