[SQLAlchemy] Struggling with association_proxy

Robert Latest boblatest at yahoo.com
Thu Mar 18 09:52:08 EDT 2021


I'm trying to implement a many-to-many relationship that associates Baskets
with Items via an association object called Link which holds the quantity of
each item. I've done that in SQLAlchemy in a very pedestrian way, such as when
I want to have six eggs in a basket:

1. Find ID of Item with name 'egg'
2. See if there is an association object with the egg ID and the basket ID
3a. if yes, set its quantity to 6
3b if no, create it with quantity 6 and add it to the items colletion in basket

The association_proxy documentation suggests that this could be done elegantly
in such a way that I could simply write

basket.contents['egg'] = 6

and be done with it. I've tried to follow the documentation at 
https://docs.sqlalchemy.org/en/14/orm/extensions/associationproxy.html
but I don't understand it: It keeps creating new keyword instances rather
re-using existing ones, thus defeating the many-to-many idea. Here's what I've
come up so far, but it predictably fails because I don't want it to create new
Items on its own:

from sqlalchemy import create_engine, Column, Integer,\
    String, ForeignKey
from sqlalchemy.ext.associationproxy import association_proxy
from sqlalchemy.orm import relationship, sessionmaker, backref
from sqlalchemy.orm.collections import attribute_mapped_collection
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class Basket(Base):
    __tablename__ = 'basket'
    id            = Column(Integer, primary_key=True)
    contents = association_proxy('basket_contents', 'id')

class Link(Base):
    __tablename__ = 'link'
    item_id       = Column(ForeignKey('item.id'), primary_key=True)
    basket_id     = Column(ForeignKey('basket.id'), primary_key=True)
    quantity      = Column(Integer)
    basket = relationship(Basket, backref=backref('basket_contents',
        collection_class=attribute_mapped_collection('quantity')))
    item = relationship('Item')
    name = association_proxy('item', 'name')

    def __init__(self, name, quantity):
        # this doesn't work b/c it calls Item.__init__() rather than
        # looking for an existing Item
        self.name = name
        self.quantity = quantity

class Item(Base):
    __tablename__ = 'item'
    id            = Column(Integer, primary_key=True)
    name          = Column(String(10), unique=True)
    weight        = Column(Integer)
    color         = String(10)

engine = create_engine('sqlite://')
Base.metadata.create_all(engine)
Session = sessionmaker(engine)

db = Session()

egg = Item(name='egg', weight=50, color='white')

b = Basket()

# fails because in Link.__init__(), SQLAlchemy wants to create a new Item
# rather than using the existing one.
b.contents['egg'] = 6

db.add(b)

db.commit()



More information about the Python-list mailing list