KeyError when adding objects to SQLAlchemy association object

Question

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.

I think your __tablename__ = "tablet_correspondent" is wrong. Shouldn't it be tablet_correspondents (with an s at the end)? — user849425
– user849425, Commented Jun 18, 2012 at 23:31

Ilja Everilä · Accepted Answer · 2018-11-13 21:47:05Z

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

dev_hero · Accepted Answer · 2013-10-09 21:08:11Z

2

Like van said, the problem stay in the __init__ method of the Association Object.

In fact, if Tablet or Correspondent classes don't define an __init__ method or don't pass any parameter, the solution doesn't work (no argument expected).

I found an alternative solution. It's easy to detect which class has to be proxied, so it can be assigned to the right field (and still work on adding more associations):

class Tablet_Correspondent(db.Model):

    # ...

    def __init__(self, proxied=None):
        if type(proxied) is Tablet:
            self.tablet = proxied
        elif type(proxied) is Correspondent:
            self.correspondent = proxied

answered Oct 9, 2013 at 21:08

dev_hero

3502 silver badges9 bronze badges

Collectives™ on Stack Overflow

KeyError when adding objects to SQLAlchemy association object

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related