From 695f65db853a7b74a1ce2da75d8e3c55bbafae81 Mon Sep 17 00:00:00 2001 From: Mike Bayer Date: Fri, 3 Nov 2006 01:17:28 +0000 Subject: - added an assertion within the "cascade" step of ORM relationships to check that the class of object attached to a parent object is appropriate (i.e. if A.items stores B objects, raise an error if a C is appended to A.items) - new extension sqlalchemy.ext.associationproxy, provides transparent "association object" mappings. new example examples/association/proxied_association.py illustrates. - some example cleanup --- examples/association/basic_association.py | 104 +++++++++++++++++++++++++++ examples/association/proxied_association.py | 108 ++++++++++++++++++++++++++++ examples/polymorph/polymorph.py | 3 +- examples/vertical/vertical.py | 9 ++- 4 files changed, 217 insertions(+), 7 deletions(-) create mode 100644 examples/association/basic_association.py create mode 100644 examples/association/proxied_association.py (limited to 'examples') diff --git a/examples/association/basic_association.py b/examples/association/basic_association.py new file mode 100644 index 000000000..5ce643671 --- /dev/null +++ b/examples/association/basic_association.py @@ -0,0 +1,104 @@ +"""basic example of using the association object pattern, which is +a richer form of a many-to-many relationship.""" + + +# the model will be an ecommerce example. We will have an +# Order, which represents a set of Items purchased by a user. +# each Item has a price. however, the Order must store its own price for +# each Item, representing the price paid by the user for that particular order, which +# is independent of the price on each Item (since those can change). + +from sqlalchemy import * +from sqlalchemy.ext.selectresults import SelectResults +from datetime import datetime + +import logging +logging.basicConfig(format='%(message)s') +logging.getLogger('sqlalchemy.engine').setLevel(logging.INFO) + +engine = create_engine('sqlite://') +metadata = BoundMetaData(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 Order(object): + def __init__(self, customer_name): + self.customer_name = customer_name + +class Item(object): + def __init__(self, description, price): + self.description = description + self.price = price + +class OrderItem(object): + def __init__(self, item, price=None): + self.item = item + self.price = price or item.price + +mapper(Order, orders, properties={ + 'items':relation(OrderItem, cascade="all, delete-orphan", lazy=False) +}) +mapper(Item, items) +mapper(OrderItem, orderitems, properties={ + 'item':relation(Item, lazy=False) +}) + +session = create_session() + +# create our catalog +session.save(Item('SA T-Shirt', 10.99)) +session.save(Item('SA Mug', 6.50)) +session.save(Item('SA Hat', 8.99)) +session.save(Item('MySQL Crowbar', 16.99)) +session.flush() + +# function to return items from the DB +def item(name): + return session.query(Item).get_by(description=name) + +# create an order +order = Order('john smith') + +# add three OrderItem associations to the Order and save +order.items.append(OrderItem(item('SA Mug'))) +order.items.append(OrderItem(item('MySQL Crowbar'), 10.99)) +order.items.append(OrderItem(item('SA Hat'))) +session.save(order) +session.flush() + +session.clear() + +# query the order, print items +order = session.query(Order).get_by(customer_name='john smith') +print [(item.item.description, item.price) for item in order.items] + +# print customers who bought 'MySQL Crowbar' on sale +result = SelectResults(session.query(Order)).join_to('item').select(and_(items.c.description=='MySQL Crowbar', items.c.price>orderitems.c.price)) +print [order.customer_name for order in result] + + + + + + + + + + diff --git a/examples/association/proxied_association.py b/examples/association/proxied_association.py new file mode 100644 index 000000000..3e80ffa16 --- /dev/null +++ b/examples/association/proxied_association.py @@ -0,0 +1,108 @@ +"""this is a modified version of the basic association example, which illustrates +the usage of the associationproxy extension.""" + +from sqlalchemy import * +from sqlalchemy.ext.selectresults import SelectResults +from sqlalchemy.ext.associationproxy import AssociationProxy +from datetime import datetime + +import logging +logging.basicConfig(format='%(message)s') +#logging.getLogger('sqlalchemy.engine').setLevel(logging.INFO) + +engine = create_engine('sqlite://') +metadata = BoundMetaData(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 Order(object): + def __init__(self, customer_name): + self.customer_name = customer_name + items = AssociationProxy('itemassociations', 'item', creator=lambda x:OrderItem(x)) + +class Item(object): + def __init__(self, description, price): + self.description = description + self.price = price + +class OrderItem(object): + def __init__(self, item, price=None): + self.item = item + self.price = price or item.price + +mapper(Order, orders, properties={ + 'itemassociations':relation(OrderItem, cascade="all, delete-orphan", lazy=False) +}) +mapper(Item, items) +mapper(OrderItem, orderitems, properties={ + 'item':relation(Item, lazy=False) +}) + +session = create_session() + +# create our catalog +session.save(Item('SA T-Shirt', 10.99)) +session.save(Item('SA Mug', 6.50)) +session.save(Item('SA Hat', 8.99)) +session.save(Item('MySQL Crowbar', 16.99)) +session.flush() + +# function to return items +def item(name): + return session.query(Item).get_by(description=name) + +# create an order +order = Order('john smith') + +# append an OrderItem association via the "itemassociations" collection +order.itemassociations.append(OrderItem(item('MySQL Crowbar'), 10.99)) + +# append two more Items via the transparent "items" proxy, which +# will create OrderItems automatically +order.items.append(item('SA Mug')) +order.items.append(item('SA Hat')) + +session.save(order) +session.flush() + +session.clear() + +# query the order, print items +order = session.query(Order).get_by(customer_name='john smith') + +# print items based on the OrderItem collection directly +print [(item.item.description, item.price) for item 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 +result = SelectResults(session.query(Order)).join_to('item').select(and_(items.c.description=='MySQL Crowbar', items.c.price>orderitems.c.price)) +print [order.customer_name for order in result] + + + + + + + + + + diff --git a/examples/polymorph/polymorph.py b/examples/polymorph/polymorph.py index 92554e087..6c4f0aae6 100644 --- a/examples/polymorph/polymorph.py +++ b/examples/polymorph/polymorph.py @@ -1,5 +1,5 @@ from sqlalchemy import * -import sys, sets +import sets # this example illustrates a polymorphic load of two classes, where each class has a very # different set of properties @@ -83,7 +83,6 @@ session.save(c) print session.new session.flush() -#sys.exit() session.clear() c = session.query(Company).get(1) diff --git a/examples/vertical/vertical.py b/examples/vertical/vertical.py index 4f0f5a15c..a6ee17428 100644 --- a/examples/vertical/vertical.py +++ b/examples/vertical/vertical.py @@ -1,11 +1,10 @@ -from sqlalchemy import * -import datetime -import sys - """this example illustrates a "vertical table". an object is stored with individual attributes represented in distinct database rows. This allows objects to be created with dynamically changing fields that are all persisted in a normalized fashion.""" - + +from sqlalchemy import * +import datetime + e = BoundMetaData('sqlite://', echo=True) # this table represents Entity objects. each Entity gets a row in this table, -- cgit v1.2.1