"""this is a modified version of the basic association example, which illustrates the usage of the associationproxy extension.""" from datetime import datetime from sqlalchemy import (create_engine, MetaData, Table, Column, Integer, String, DateTime, Float, ForeignKey, and_) from sqlalchemy.orm import mapper, relationship, Session from sqlalchemy.ext.associationproxy import AssociationProxy engine = create_engine('sqlite://') #engine = create_engine('sqlite://', echo=True) metadata = MetaData(engine) orders = Table('orders', metadata, Column('order_id', Integer, primary_key=True), Column('customer_name', String(30), nullable=False), Column('order_date', DateTime, nullable=False, default=datetime.now)) items = Table('items', metadata, Column('item_id', Integer, primary_key=True), Column('description', String(30), nullable=False), Column('price', Float, nullable=False)) orderitems = Table('orderitems', metadata, Column('order_id', Integer, ForeignKey('orders.order_id'), primary_key=True), Column('item_id', Integer, ForeignKey('items.item_id'), primary_key=True), Column('price', Float, nullable=False)) metadata.create_all() class OrderItem(object): def __init__(self, item, price=None): self.item = item self.price = price is None and item.price or price class Order(object): def __init__(self, customer_name): self.customer_name = customer_name items = AssociationProxy('itemassociations', 'item', creator=OrderItem) class Item(object): def __init__(self, description, price): self.description = description self.price = price mapper(Order, orders, properties={ 'itemassociations':relationship(OrderItem, cascade="all, delete-orphan", lazy='joined') }) mapper(Item, items) mapper(OrderItem, orderitems, properties={ 'item':relationship(Item, lazy='joined') }) session = Session() # create our catalog session.add_all([Item('SA T-Shirt', 10.99), Item('SA Mug', 6.50), Item('SA Hat', 8.99), Item('MySQL Crowbar', 16.99)]) session.commit() # function to return items def item(name): return session.query(Item).filter_by(description=name).one() # create an order order = Order('john smith') # append an OrderItem association via the "itemassociations" # collection with a custom price. order.itemassociations.append(OrderItem(item('MySQL Crowbar'), 10.99)) # append two more Items via the transparent "items" proxy, which # will create OrderItems automatically using the default price. order.items.append(item('SA Mug')) order.items.append(item('SA Hat')) session.add(order) session.commit() # query the order, print items order = session.query(Order).filter_by(customer_name='john smith').one() print "Order #%s:\n%s\n%s\n%s items.\n" % ( order.order_id, order.customer_name, order.order_date, len(order.items)) # print items based on the OrderItem collection directly print [(assoc.item.description, assoc.price, assoc.item.price) for assoc in order.itemassociations] # print items based on the "proxied" items collection print [(item.description, item.price) for item in order.items] # print customers who bought 'MySQL Crowbar' on sale orders = session.query(Order).join('itemassociations', 'item').filter( and_(Item.description=='MySQL Crowbar', Item.price > OrderItem.price)) print [order.customer_name for order in orders]