[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