6
.. module:: sqlalchemy.ext.associationproxy
8
``associationproxy`` is used to create a simplified, read/write view of a
9
relationship. It can be used to cherry-pick fields from a collection of
10
related objects or to greatly simplify access to associated objects in an
11
association relationship.
13
Simplifying Relationships
14
-------------------------
16
Consider this "association object" mapping::
18
users_table = Table('users', metadata,
19
Column('id', Integer, primary_key=True),
20
Column('name', String(64)),
23
keywords_table = Table('keywords', metadata,
24
Column('id', Integer, primary_key=True),
25
Column('keyword', String(64))
28
userkeywords_table = Table('userkeywords', metadata,
29
Column('user_id', Integer, ForeignKey("users.id"),
31
Column('keyword_id', Integer, ForeignKey("keywords.id"),
36
def __init__(self, name):
39
class Keyword(object):
40
def __init__(self, keyword):
41
self.keyword = keyword
43
mapper(User, users_table, properties={
44
'kw': relationship(Keyword, secondary=userkeywords_table)
46
mapper(Keyword, keywords_table)
48
Above are three simple tables, modeling users, keywords and a many-to-many
49
relationship between the two. These ``Keyword`` objects are little more
50
than a container for a name, and accessing them via the relationship is
54
user.kw.append(Keyword('cheese inspector'))
56
# [<__main__.Keyword object at 0xb791ea0c>]
57
print user.kw[0].keyword
59
print [keyword.keyword for keyword in user.kw]
60
# ['cheese inspector']
62
With ``association_proxy`` you have a "view" of the relationship that contains
63
just the ``.keyword`` of the related objects. The proxy is a Python
64
property, and unlike the mapper relationship, is defined in your class::
66
from sqlalchemy.ext.associationproxy import association_proxy
69
def __init__(self, name):
72
# proxy the 'keyword' attribute from the 'kw' relationship
73
keywords = association_proxy('kw', 'keyword')
77
[<__main__.Keyword object at 0xb791ea0c>]
80
>>> user.keywords.append('snack ninja')
82
['cheese inspector', 'snack ninja']
84
[<__main__.Keyword object at 0x9272a4c>, <__main__.Keyword object at 0xb7b396ec>]
86
The proxy is read/write. New associated objects are created on demand when
87
values are added to the proxy, and modifying or removing an entry through
88
the proxy also affects the underlying collection.
90
- The association proxy property is backed by a mapper-defined relationship,
91
either a collection or scalar.
93
- You can access and modify both the proxy and the backing
94
relationship. Changes in one are immediate in the other.
96
- The proxy acts like the type of the underlying collection. A list gets a
97
list-like proxy, a dict a dict-like proxy, and so on.
99
- Multiple proxies for the same relationship are fine.
101
- Proxies are lazy, and won't trigger a load of the backing relationship until
104
- The relationship is inspected to determine the type of the related objects.
106
- To construct new instances, the type is called with the value being
107
assigned, or key and value for dicts.
109
- A ````creator```` function can be used to create instances instead.
111
Above, the ``Keyword.__init__`` takes a single argument ``keyword``, which
112
maps conveniently to the value being set through the proxy. A ``creator``
113
function could have been used instead if more flexibility was required.
115
Because the proxies are backed by a regular relationship collection, all of the
116
usual hooks and patterns for using collections are still in effect. The
117
most convenient behavior is the automatic setting of "parent"-type
118
relationships on assignment. In the example above, nothing special had to
119
be done to associate the Keyword to the User. Simply adding it to the
120
collection is sufficient.
122
Simplifying Association Object Relationships
123
--------------------------------------------
125
Association proxies are also useful for keeping ``association objects`` out
126
the way during regular use. For example, the ``userkeywords`` table
127
might have a bunch of auditing columns that need to get updated when changes
128
are made- columns that are updated but seldom, if ever, accessed in your
129
application. A proxy can provide a very natural access pattern for the
132
.. sourcecode:: python
134
from sqlalchemy.ext.associationproxy import association_proxy
136
# users_table and keywords_table tables as above, then:
138
def get_current_uid():
139
"""Return the uid of the current user."""
140
return 1 # hardcoded for this example
142
userkeywords_table = Table('userkeywords', metadata,
143
Column('user_id', Integer, ForeignKey("users.id"), primary_key=True),
144
Column('keyword_id', Integer, ForeignKey("keywords.id"), primary_key=True),
145
# add some auditing columns
146
Column('updated_at', DateTime, default=datetime.now),
147
Column('updated_by', Integer, default=get_current_uid, onupdate=get_current_uid),
150
def _create_uk_by_keyword(keyword):
151
"""A creator function."""
152
return UserKeyword(keyword=keyword)
155
def __init__(self, name):
157
keywords = association_proxy('user_keywords', 'keyword', creator=_create_uk_by_keyword)
159
class Keyword(object):
160
def __init__(self, keyword):
161
self.keyword = keyword
163
return 'Keyword(%s)' % repr(self.keyword)
165
class UserKeyword(object):
166
def __init__(self, user=None, keyword=None):
168
self.keyword = keyword
170
mapper(User, users_table)
171
mapper(Keyword, keywords_table)
172
mapper(UserKeyword, userkeywords_table, properties={
173
'user': relationship(User, backref='user_keywords'),
174
'keyword': relationship(Keyword),
178
kw1 = Keyword('new_from_blammo')
180
# Creating a UserKeyword association object will add a Keyword.
181
# the "user" reference assignment in the UserKeyword() constructor
182
# populates "user_keywords" via backref.
183
UserKeyword(user, kw1)
185
# Accessing Keywords requires traversing UserKeywords
186
print user.user_keywords[0]
187
# <__main__.UserKeyword object at 0xb79bbbec>
189
print user.user_keywords[0].keyword
190
# Keyword('new_from_blammo')
194
# It's much easier to go through the association proxy!
195
for kw in (Keyword('its_big'), Keyword('its_heavy'), Keyword('its_wood')):
196
user.keywords.append(kw)
199
# [Keyword('new_from_blammo'), Keyword('its_big'), Keyword('its_heavy'), Keyword('its_wood')]
202
Building Complex Views
203
----------------------
205
.. sourcecode:: python
207
stocks_table = Table("stocks", meta,
208
Column('symbol', String(10), primary_key=True),
209
Column('last_price', Numeric)
212
brokers_table = Table("brokers", meta,
213
Column('id', Integer,primary_key=True),
214
Column('name', String(100), nullable=False)
217
holdings_table = Table("holdings", meta,
218
Column('broker_id', Integer, ForeignKey('brokers.id'), primary_key=True),
219
Column('symbol', String(10), ForeignKey('stocks.symbol'), primary_key=True),
220
Column('shares', Integer)
223
Above are three tables, modeling stocks, their brokers and the number of
224
shares of a stock held by each broker. This situation is quite different
225
from the association example above. ``shares`` is a *property of the
226
relationship*, an important one that we need to use all the time.
228
For this example, it would be very convenient if ``Broker`` objects had a
229
dictionary collection that mapped ``Stock`` instances to the shares held for
232
from sqlalchemy.ext.associationproxy import association_proxy
233
from sqlalchemy.orm.collections import attribute_mapped_collection
235
def _create_holding(stock, shares):
236
"""A creator function, constructs Holdings from Stock and share quantity."""
237
return Holding(stock=stock, shares=shares)
239
class Broker(object):
240
def __init__(self, name):
243
holdings = association_proxy('by_stock', 'shares', creator=_create_holding)
246
def __init__(self, symbol):
250
class Holding(object):
251
def __init__(self, broker=None, stock=None, shares=0):
256
mapper(Stock, stocks_table)
257
mapper(Broker, brokers_table, properties={
258
'by_stock': relationship(Holding,
259
collection_class=attribute_mapped_collection('stock'))
261
mapper(Holding, holdings_table, properties={
262
'stock': relationship(Stock),
263
'broker': relationship(Broker)
266
Above, we've set up the ``by_stock`` relationship collection to act as a
267
dictionary, using the ``.stock`` property of each Holding as a key.
269
Populating and accessing that dictionary manually is slightly inconvenient
270
because of the complexity of the Holdings association object::
273
broker = Broker('paj')
275
broker.by_stock[stock] = Holding(broker, stock, 10)
276
print broker.by_stock[stock].shares
279
The ``holdings`` proxy we've added to the ``Broker`` class hides the details
280
of the ``Holding`` while also giving access to ``.shares``::
282
for stock in (Stock('JEK'), Stock('STPZ')):
283
broker.holdings[stock] = 123
285
for stock, shares in broker.holdings.items():
291
# lets take a peek at that holdings_table after committing changes to the db
292
print list(holdings_table.select().execute())
293
# [(1, 'ZZK', 10), (1, 'JEK', 123), (1, 'STEPZ', 123)]
295
Further examples can be found in the ``examples/`` directory in the
296
SQLAlchemy distribution.
301
.. autofunction:: association_proxy
303
.. autoclass:: AssociationProxy
b'\\ No newline at end of file'
added
removed
doc/build/orm/extensions/associationproxy.rst
