Essential SQLAlchemy, 2nd Edition (2010)
Chapter 11. Other SQLAlchemy Extensions
SQLAlchemy provides an extremely powerful method of defining schemas, performing queries, and manipulating data, both at the ORM level and at the SQL level. SQLAlchemy also provides several extensions to this core behavior. We have already seen one of these extensions, SqlSoup, discussed in Chapter 10. One of the nice things about the SQLAlchemy extensions package is that it provides a “proving ground” for functionality that may eventually make it into the core SQLAlchemy packages. When this occurs (the functionality of an extension is absorbed into the core feature set of SQLAlchemy), the extension is deprecated and eventually removed.
This chapter discusses the two remaining nondeprecated extensions available in SQLAlchemy 0.4, sqlalchemy.ext.associationproxy and sqlalchemy.ext.orderinglist. We will also describe the deprecated extensions, focusing on how to achieve the same functionality using “core” SQLAlchemy.
Association Proxy
The association proxy extension allows our mapped classes to have attributes that are proxied from related objects. One place where this is useful is when we have two tables related via an association table that contains extra information in addition to linking the two tables. For instance, suppose we have a database containing the following schema:
user_table = Table(
'user', metadata,
Column('id', Integer, primary_key=True),
Column('user_name', String(255), unique=True),
Column('password', String(255)))
brand_table = Table(
'brand', metadata,
Column('id', Integer, primary_key=True),
Column('name', String(255)))
sales_rep_table = Table(
'sales_rep', metadata,
Column('brand_id', None, ForeignKey('brand.id'), primary_key=True),
Column('user_id', None, ForeignKey('user.id'), primary_key=True),
Column('commission_pct', Integer, default=0))
In this case, we might want to create User, Brand, and SalesRep classes to represent our domain objects. The basic mapper setup would then be the following:
class User(object): pass
class Brand(object): pass
class SalesRep(object): pass
mapper(User, user_table, properties=dict(
sales_rep=relation(SalesRep, backref='user', uselist=False)))
mapper(Brand, brand_table, properties=dict(
sales_reps=relation(SalesRep, backref='brand')))
mapper(SalesRep, sales_rep_table)
In such a case, we have completely mapped the data in our schema to the object model. But what if we want to have a property on the Brand object that lists all of the Users who are SalesReps for that Brand? One way we could do this in “base” SQLAlchemy is by using a property in theBrand class:
class Brand(object):
@property
def users(self):
return [ sr.user for sr in self.sales_reps ]
This is not very convenient, however. It doesn’t allow us to append to or remove from the list of users, for instance. The association proxy provides a convenient solution to this problem. Using the association_proxy() function, we can add the users property much more simply:
from sqlalchemy.ext.associationproxy import association_proxy
class Brand(object):
users=association_proxy('sales_reps', 'user')
If we want to keep our domain object definition code ignorant of SQLAlchemy, we can even move the association_proxy() call outside our class into the mapper configuration:
mapper(Brand, brand_table, properties=dict(
sales_reps=relation(SalesRep, backref='brand')))
Brand.users=association_proxy('sales_reps', 'user')
We can even append onto the users attribute to add new SalesReps. To enable this functionality, however, we need to create some sensible constructors for our mapped objects:
class User(object):
def __init__(self, user_name=None, password=None):
self.user_name=user_name
self.password=password
class Brand(object):
def __init__(self, name=None):
self.name = name
class SalesRep(object):
def __init__(self, user=None, brand=None, commission_pct=0):
self.user = user
self.brand = brand
self.commission_pct=commission_pct
Now, we can populate the database and add a user as a sales rep to a brand:
>>> Session = sessionmaker(bind=engine)
>>> engine.echo = True
>>> session = Session()
>>>
>>> b = Brand('Cool Clothing')
>>> session.save(b)
>>>
>>> u = User('rick', 'foo')
>>> session.save(u)
>>>
>>> metadata.bind.echo = True
>>> session.flush()
2007-11-23 12:48:28,304 INFO sqlalchemy.engine.base.Engine.0x..90
... BEGIN
2007-11-23 12:48:28,305 INFO sqlalchemy.engine.base.Engine.0x..90
... INSERT INTO user (user_name, password) VALUES (?, ?)
2007-11-23 12:48:28,306 INFO sqlalchemy.engine.base.Engine.0x..90
... ['rick', 'foo']
2007-11-23 12:48:28,308 INFO sqlalchemy.engine.base.Engine.0x..90
... INSERT INTO brand (name) VALUES (?)
2007-11-23 12:48:28,308 INFO sqlalchemy.engine.base.Engine.0x..90
... ['Cool Clothing']
>>> b.users
2007-11-23 12:48:31,317 INFO sqlalchemy.engine.base.Engine.0x..90
... SELECT sales_rep.brand_id AS sales_rep_brand_id,
... sales_rep.user_id AS sales_rep_user_id, sales_rep.commission_pct
... AS sales_rep_commission_pct
FROM sales_rep
WHERE ? = sales_rep.brand_id ORDER BY sales_rep.oid
2007-11-23 12:48:31,318 INFO sqlalchemy.engine.base.Engine.0x..90
... [1]
[]
>>> b.users.append(u)
2007-11-23 12:48:33,668 INFO sqlalchemy.engine.base.Engine.0x..90
... SELECT sales_rep.brand_id AS sales_rep_brand_id,
... sales_rep.user_id AS sales_rep_user_id, sales_rep.commission_pct
... AS sales_rep_commission_pct
FROM sales_rep
WHERE ? = sales_rep.user_id ORDER BY sales_rep.oid
2007-11-23 12:48:33,669 INFO sqlalchemy.engine.base.Engine.0x..90
... [1]
>>> b.users
[<__main__.User object at 0xbdc710>]
>>> b.sales_reps
[<__main__.SalesRep object at 0xbe4610>]
>>> b.sales_reps[0].commission_pct
0
>>> session.flush()
2008-01-27 21:12:35,991 INFO sqlalchemy.engine.base.Engine.0x..50
... INSERT INTO sales_rep (brand_id, user_id, commission_pct) VALUES
... (?, ?, ?)
2008-01-27 21:12:35,994 INFO sqlalchemy.engine.base.Engine.0x..50
... [1, 1, 0]
This works because the association proxy extension will automatically create the intermediary SalesRep object by calling its constructor with a single positional argument, the User. To override this creation behavior, you can supply a creation function in the creator parameter. For instance, if we wanted to give sales reps added in this manner a commission percentage of 10%, we could define the proxy as follows:
Brand.users=association_proxy(
'sales_reps', 'user',
creator=lambda u:SalesRep(user=u, commission_pct=10))
Although accessing the underlying user attribute of the sales_reps property is useful, what if we prefer dictionary-style access? associationproxy supports this as well. For instance, suppose we want a property on Brand that is a dictionary keyed by User containing thecommission_pct values. We can implement this as follows. (Note that dictionary-style association proxy creation functions take two positional parameters: the key and value being set.)
from sqlalchemy.orm.collections import attribute_mapped_collection
reps_by_user_class=attribute_mapped_collection('user')
mapper(Brand, brand_table, properties=dict(
sales_reps_by_user=relation(
SalesRep, backref='brand',
collection_class=reps_by_user_class)))
Brand.commissions=association_proxy(
'sales_reps_by_user', 'commission_pct',
creator=lambda key,value: SalesRep(user=key, commission_pct=value))
Now, we can conveniently access the commission values by user:
>>> session.clear()
>>> session.bind.echo = False
>>>
>>> b = session.get(Brand, 1)
>>> u = session.get(User, 1)
>>> b.commissions[u] = 20
>>> session.flush()
>>> session.clear()
>>>
>>> b = session.get(Brand, 1)
>>> u = session.get(User, 1)
>>> print u.user_name
rick
>>> print b.commissions[u]
20
Note that the proxy and the original relation are automatically kept synchronized by SQLAlchemy:
>>> print b.sales_reps_by_user[u]
<__main__.SalesRep object at 0xbf2750>
>>> print b.sales_reps_by_user[u].commission_pct
20
Ordering List
A common pattern in many applications is the use of ordered collections. For instance, consider a simple to-do list application with multiple lists, each containing an (ordered) set of items. We might start with the following schema:
todo_list_table = Table(
'todo_list', metadata,
Column('name', Unicode(255), primary_key=True))
todo_item_table = Table(
'todo_item', metadata,
Column('id', Integer, primary_key=True),
Column('list_name', None, ForeignKey('todo_list.name')),
Column('list_position', Integer),
Column('value', Unicode))
SQLAlchemy provides nice support for mapping the list items to a property and sorting them via the order_by parameter:
class TodoList(object):
def __init__(self, name):
self.name = name
def __repr__(self):
return '<TodoList %s>' % self.name
class TodoItem(object):
def __init__(self, value, position=None):
self.value = value
self.list_position = position
def __repr__(self):
return '<%s: %s>' % (self.list_position, self.value)
mapper(TodoList, todo_list, properties=dict(
items=relation(TodoItem,
backref='list',
order_by=[todo_item_table.c.list_position])))
mapper(TodoItem, todo_item)
We can now create a list with some items:
>>> lst = TodoList('list1')
>>> session.save(lst)
>>> lst.items = [ TodoItem('Buy groceries', 0),
... TodoItem('Do laundry', 1) ]
>>> session.flush()
>>> session.clear()
>>>
>>> lst = session.get(TodoList, 'list1')
>>> print lst.items
[<0: Buy groceries>, <1: Do laundry>]
This approach is certainly workable, but it requires you to manually keep track of the positions of all the list items. For instance, suppose we wanted to mow the lawn between buying groceries and doing laundry. To do this using base SQLAlchemy, we would need to do something like the following:
>>> lst.items.insert(1, TodoItem('Mow lawn'))
>>> for pos, it in enumerate(lst.items):
... it.list_position = pos
Rather than “fixing up” the list after each insert or remove operation, we can instead use orderinglist to keep track of the list_position attribute automatically:
>>> from sqlalchemy.ext.orderinglist import ordering_list
>>>
>>> mapper(TodoList, todo_list_table, properties=dict(
... items=relation(TodoItem,
... backref='list',
... order_by=[todo_item_table.c.list_position],
... collection_class
... =ordering_list('list_position'))))
<sqlalchemy.orm.mapper.Mapper object at 0xbcb850>
>>> mapper(TodoItem, todo_item_table)
<sqlalchemy.orm.mapper.Mapper object at 0xbcb710>
>>>
>>> session.clear()
>>> lst = session.get(TodoList, 'list1')
>>> print lst.items
[<0: Buy groceries>, <1: Mow lawn>, <2: Do laundry>]
>>> del lst.items[1]
>>> print lst.items
[<0: Buy groceries>, <1: Do laundry>]
>>> session.flush()
We can also customize the ordering_list() call either by providing a count_from argument (to use nonzero-based lists) or by providing an ordering_func argument that maps a position in a list to a value to store in the ordering attribute.
In some cases, you may also want to rearrange the entire list (rather than applying individual insertions and deletions). For such situations, ordering_list() provides the _reorder() method, which will generate new position values for every element in the list.
Deprecated Extensions
As mentioned previously, SQLAlchemy extensions function as a sort of “proving ground” for new functionality that may someday “graduate” into SQLAlchemy proper. There are several such extensions that have graduated in the transition from the SQLAlchemy 0.3.x release series to the 0.4.x release series. These extensions are briefly described here:
sqlalchemy.ext.selectresults
The sqlalchemy.ext.selectresults extension provided generative query support for ORM queries. Since version 0.3.6, this support has been built in to the native Query class. sqlalchemy.ext.selectresults also provides a MapperExtension that adds generative query behavior on a per-mapper basis.
sqlalchemy.ext.sessioncontext
The sqlalchemy.ext.sessioncontext extension provided contextual session support. This has been deprecated in favor of the scoped_session() support in core SQLAlchemy.
sqlalchemy.ext.assignmapper
The sqlalchemy.ext.assignmapper extension provided the ability to automatically save mapped objects and additional instrumentation on mapped classes above what the mapper() function normally does. This has been deprecated in favor of the Session.mapper() function available with contextual sessions created by scoped_session() in core SQLAlchemy.
sqlalchemy.ext.activemapper
The sqlalchemy.ext.activemapper extension provided a declarative layer implementing the active record pattern on SQLAlchemy. This has been deprecated in favor of the external package Elixir (Chapter 9), a more comprehensive declarative layer.