Source code for examples.association.basic_association
"""Illustrate a many-to-many relationship between an"Order" and a collection of "Item" objects, associating a purchase pricewith each via an association object called "OrderItem"The association object pattern is a form of many-to-many whichassociates additional data with each association between parent/child.The example illustrates an "order", referencing a collectionof "items", with a particular price paid associated with each "item"."""fromdatetimeimportdatetimefromsqlalchemyimportand_fromsqlalchemyimportColumnfromsqlalchemyimportcreate_enginefromsqlalchemyimportDateTimefromsqlalchemyimportFloatfromsqlalchemyimportForeignKeyfromsqlalchemyimportIntegerfromsqlalchemyimportStringfromsqlalchemy.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")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 three OrderItem associations to the Order and saveorder.order_items.append(OrderItem(mug))order.order_items.append(OrderItem(crowbar,10.99))order.order_items.append(OrderItem(hat))session.add(order)session.commit()# query the order, print itemsorder=session.query(Order).filter_by(customer_name="john smith").one()print([(order_item.item.description,order_item.price)fororder_iteminorder.order_items])# print customers who bought 'MySQL Crowbar' on saleq=session.query(Order).join("order_items","item")q=q.filter(and_(Item.description=="MySQL Crowbar",Item.price>OrderItem.price))print([order.customer_namefororderinq])