Source code for examples.association.proxied_association
"""Same example as basic_association, adding inusage of :mod:`sqlalchemy.ext.associationproxy` to make explicit referencesto ``OrderItem`` optional."""fromdatetimeimportdatetimefromsqlalchemyimportColumnfromsqlalchemyimportcreate_enginefromsqlalchemyimportDateTimefromsqlalchemyimportFloatfromsqlalchemyimportForeignKeyfromsqlalchemyimportIntegerfromsqlalchemyimportStringfromsqlalchemy.ext.associationproxyimportassociation_proxyfromsqlalchemy.ext.declarativeimportdeclarative_basefromsqlalchemy.ormimportrelationshipfromsqlalchemy.ormimportSessionBase=declarative_base()classOrder(Base):__tablename__="order"order_id=Column(Integer,primary_key=True)customer_name=Column(String(30),nullable=False)order_date=Column(DateTime,nullable=False,default=datetime.now())order_items=relationship("OrderItem",cascade="all, delete-orphan",backref="order")items=association_proxy("order_items","item")def__init__(self,customer_name):self.customer_name=customer_nameclassItem(Base):__tablename__="item"item_id=Column(Integer,primary_key=True)description=Column(String(30),nullable=False)price=Column(Float,nullable=False)def__init__(self,description,price):self.description=descriptionself.price=pricedef__repr__(self):return"Item(%r, %r)"%(self.description,self.price)classOrderItem(Base):__tablename__="orderitem"order_id=Column(Integer,ForeignKey("order.order_id"),primary_key=True)item_id=Column(Integer,ForeignKey("item.item_id"),primary_key=True)price=Column(Float,nullable=False)def__init__(self,item,price=None):self.item=itemself.price=priceoritem.priceitem=relationship(Item,lazy="joined")if__name__=="__main__":engine=create_engine("sqlite://")Base.metadata.create_all(engine)session=Session(engine)# create catalogtshirt,mug,hat,crowbar=(Item("SA T-Shirt",10.99),Item("SA Mug",6.50),Item("SA Hat",8.99),Item("MySQL Crowbar",16.99),)session.add_all([tshirt,mug,hat,crowbar])session.commit()# create an orderorder=Order("john smith")# add items via the association proxy.# the OrderItem is created automatically.order.items.append(mug)order.items.append(hat)# add an OrderItem explicitly.order.order_items.append(OrderItem(crowbar,10.99))session.add(order)session.commit()# query the order, print itemsorder=session.query(Order).filter_by(customer_name="john smith").one()# print items based on the OrderItem collection directlyprint([(assoc.item.description,assoc.price,assoc.item.price)forassocinorder.order_items])# print items based on the "proxied" items collectionprint([(item.description,item.price)foriteminorder.items])# print customers who bought 'MySQL Crowbar' on saleorders=(session.query(Order).join("order_items","item").filter(Item.description=="MySQL Crowbar").filter(Item.price>OrderItem.price))print([o.customer_nameforoinorders])