1
.. _associationproxy_toplevel:
6
.. module:: sqlalchemy.ext.associationproxy
8
``associationproxy`` is used to create a read/write view of a
9
target attribute across a relationship. It essentially conceals
10
the usage of a "middle" attribute between two endpoints, and
11
can be used to cherry-pick fields from a collection of
12
related objects or to reduce the verbosity of using the association
13
object pattern. Applied creatively, the association proxy allows
14
the construction of sophisticated collections and dictionary
15
views of virtually any geometry, persisted to the database using
16
standard, transparently configured relational patterns.
19
Simplifying Scalar Collections
20
------------------------------
22
Consider a many-to-many mapping between two classes, ``User`` and ``Keyword``.
23
Each ``User`` can have any number of ``Keyword`` objects, and vice-versa
24
(the many-to-many pattern is described at :ref:`relationships_many_to_many`)::
26
from sqlalchemy import Column, Integer, String, ForeignKey, Table
27
from sqlalchemy.orm import relationship
28
from sqlalchemy.ext.declarative import declarative_base
30
Base = declarative_base()
33
__tablename__ = 'user'
34
id = Column(Integer, primary_key=True)
35
name = Column(String(64))
36
kw = relationship("Keyword", secondary=lambda: userkeywords_table)
38
def __init__(self, name):
42
__tablename__ = 'keyword'
43
id = Column(Integer, primary_key=True)
44
keyword = Column('keyword', String(64))
46
def __init__(self, keyword):
47
self.keyword = keyword
49
userkeywords_table = Table('userkeywords', Base.metadata,
50
Column('user_id', Integer, ForeignKey("user.id"),
52
Column('keyword_id', Integer, ForeignKey("keyword.id"),
56
Reading and manipulating the collection of "keyword" strings associated
57
with ``User`` requires traversal from each collection element to the ``.keyword``
58
attribute, which can be awkward::
60
>>> user = User('jek')
61
>>> user.kw.append(Keyword('cheese inspector'))
63
[<__main__.Keyword object at 0x12bf830>]
64
>>> print(user.kw[0].keyword)
66
>>> print([keyword.keyword for keyword in user.kw])
69
The ``association_proxy`` is applied to the ``User`` class to produce
70
a "view" of the ``kw`` relationship, which only exposes the string
71
value of ``.keyword`` associated with each ``Keyword`` object::
73
from sqlalchemy.ext.associationproxy import association_proxy
76
__tablename__ = 'user'
77
id = Column(Integer, primary_key=True)
78
name = Column(String(64))
79
kw = relationship("Keyword", secondary=lambda: userkeywords_table)
81
def __init__(self, name):
84
# proxy the 'keyword' attribute from the 'kw' relationship
85
keywords = association_proxy('kw', 'keyword')
87
We can now reference the ``.keywords`` collection as a listing of strings,
88
which is both readable and writable. New ``Keyword`` objects are created
89
for us transparently::
91
>>> user = User('jek')
92
>>> user.keywords.append('cheese inspector')
95
>>> user.keywords.append('snack ninja')
97
[<__main__.Keyword object at 0x12cdd30>, <__main__.Keyword object at 0x12cde30>]
99
The :class:`.AssociationProxy` object produced by the :func:`.association_proxy` function
100
is an instance of a `Python descriptor <http://docs.python.org/howto/descriptor.html>`_.
101
It is always declared with the user-defined class being mapped, regardless of
102
whether Declarative or classical mappings via the :func:`.mapper` function are used.
104
The proxy functions by operating upon the underlying mapped attribute
105
or collection in response to operations, and changes made via the proxy are immediately
106
apparent in the mapped attribute, as well as vice versa. The underlying
107
attribute remains fully accessible.
109
When first accessed, the association proxy performs introspection
110
operations on the target collection so that its behavior corresponds correctly.
111
Details such as if the locally proxied attribute is a collection (as is typical)
112
or a scalar reference, as well as if the collection acts like a set, list,
113
or dictionary is taken into account, so that the proxy should act just like
114
the underlying collection or attribute does.
116
Creation of New Values
117
-----------------------
119
When a list append() event (or set add(), dictionary __setitem__(), or scalar
120
assignment event) is intercepted by the association proxy, it instantiates a
121
new instance of the "intermediary" object using its constructor, passing as a
122
single argument the given value. In our example above, an operation like::
124
user.keywords.append('cheese inspector')
126
Is translated by the association proxy into the operation::
128
user.kw.append(Keyword('cheese inspector'))
130
The example works here because we have designed the constructor for ``Keyword``
131
to accept a single positional argument, ``keyword``. For those cases where a
132
single-argument constructor isn't feasible, the association proxy's creational
133
behavior can be customized using the ``creator`` argument, which references a
134
callable (i.e. Python function) that will produce a new object instance given the
135
singular argument. Below we illustrate this using a lambda as is typical::
140
# use Keyword(keyword=kw) on append() events
141
keywords = association_proxy('kw', 'keyword',
142
creator=lambda kw: Keyword(keyword=kw))
144
The ``creator`` function accepts a single argument in the case of a list-
145
or set- based collection, or a scalar attribute. In the case of a dictionary-based
146
collection, it accepts two arguments, "key" and "value". An example
147
of this is below in :ref:`proxying_dictionaries`.
149
Simplifying Association Objects
150
-------------------------------
152
The "association object" pattern is an extended form of a many-to-many
153
relationship, and is described at :ref:`association_pattern`. Association
154
proxies are useful for keeping "association objects" out the way during
157
Suppose our ``userkeywords`` table above had additional columns
158
which we'd like to map explicitly, but in most cases we don't
159
require direct access to these attributes. Below, we illustrate
160
a new mapping which introduces the ``UserKeyword`` class, which
161
is mapped to the ``userkeywords`` table illustrated earlier.
162
This class adds an additional column ``special_key``, a value which
163
we occasionally want to access, but not in the usual case. We
164
create an association proxy on the ``User`` class called
165
``keywords``, which will bridge the gap from the ``user_keywords``
166
collection of ``User`` to the ``.keyword`` attribute present on each
169
from sqlalchemy import Column, Integer, String, ForeignKey
170
from sqlalchemy.orm import relationship, backref
172
from sqlalchemy.ext.associationproxy import association_proxy
173
from sqlalchemy.ext.declarative import declarative_base
175
Base = declarative_base()
178
__tablename__ = 'user'
179
id = Column(Integer, primary_key=True)
180
name = Column(String(64))
182
# association proxy of "user_keywords" collection
183
# to "keyword" attribute
184
keywords = association_proxy('user_keywords', 'keyword')
186
def __init__(self, name):
189
class UserKeyword(Base):
190
__tablename__ = 'user_keyword'
191
user_id = Column(Integer, ForeignKey('user.id'), primary_key=True)
192
keyword_id = Column(Integer, ForeignKey('keyword.id'), primary_key=True)
193
special_key = Column(String(50))
195
# bidirectional attribute/collection of "user"/"user_keywords"
196
user = relationship(User,
197
backref=backref("user_keywords",
198
cascade="all, delete-orphan")
201
# reference to the "Keyword" object
202
keyword = relationship("Keyword")
204
def __init__(self, keyword=None, user=None, special_key=None):
206
self.keyword = keyword
207
self.special_key = special_key
210
__tablename__ = 'keyword'
211
id = Column(Integer, primary_key=True)
212
keyword = Column('keyword', String(64))
214
def __init__(self, keyword):
215
self.keyword = keyword
218
return 'Keyword(%s)' % repr(self.keyword)
220
With the above configuration, we can operate upon the ``.keywords``
221
collection of each ``User`` object, and the usage of ``UserKeyword``
224
>>> user = User('log')
225
>>> for kw in (Keyword('new_from_blammo'), Keyword('its_big')):
226
... user.keywords.append(kw)
228
>>> print(user.keywords)
229
[Keyword('new_from_blammo'), Keyword('its_big')]
231
Where above, each ``.keywords.append()`` operation is equivalent to::
233
>>> user.user_keywords.append(UserKeyword(Keyword('its_heavy')))
235
The ``UserKeyword`` association object has two attributes here which are populated;
236
the ``.keyword`` attribute is populated directly as a result of passing
237
the ``Keyword`` object as the first argument. The ``.user`` argument is then
238
assigned as the ``UserKeyword`` object is appended to the ``User.user_keywords``
239
collection, where the bidirectional relationship configured between ``User.user_keywords``
240
and ``UserKeyword.user`` results in a population of the ``UserKeyword.user`` attribute.
241
The ``special_key`` argument above is left at its default value of ``None``.
243
For those cases where we do want ``special_key`` to have a value, we
244
create the ``UserKeyword`` object explicitly. Below we assign all three
245
attributes, where the assignment of ``.user`` has the effect of the ``UserKeyword``
246
being appended to the ``User.user_keywords`` collection::
248
>>> UserKeyword(Keyword('its_wood'), user, special_key='my special key')
250
The association proxy returns to us a collection of ``Keyword`` objects represented
251
by all these operations::
254
[Keyword('new_from_blammo'), Keyword('its_big'), Keyword('its_heavy'), Keyword('its_wood')]
256
.. _proxying_dictionaries:
258
Proxying to Dictionary Based Collections
259
-----------------------------------------
261
The association proxy can proxy to dictionary based collections as well. SQLAlchemy
262
mappings usually use the :func:`.attribute_mapped_collection` collection type to
263
create dictionary collections, as well as the extended techniques described in
264
:ref:`dictionary_collections`.
266
The association proxy adjusts its behavior when it detects the usage of a
267
dictionary-based collection. When new values are added to the dictionary, the
268
association proxy instantiates the intermediary object by passing two
269
arguments to the creation function instead of one, the key and the value. As
270
always, this creation function defaults to the constructor of the intermediary
271
class, and can be customized using the ``creator`` argument.
273
Below, we modify our ``UserKeyword`` example such that the ``User.user_keywords``
274
collection will now be mapped using a dictionary, where the ``UserKeyword.special_key``
275
argument will be used as the key for the dictionary. We then apply a ``creator``
276
argument to the ``User.keywords`` proxy so that these values are assigned appropriately
277
when new elements are added to the dictionary::
279
from sqlalchemy import Column, Integer, String, ForeignKey
280
from sqlalchemy.orm import relationship, backref
281
from sqlalchemy.ext.associationproxy import association_proxy
282
from sqlalchemy.ext.declarative import declarative_base
283
from sqlalchemy.orm.collections import attribute_mapped_collection
285
Base = declarative_base()
288
__tablename__ = 'user'
289
id = Column(Integer, primary_key=True)
290
name = Column(String(64))
292
# proxy to 'user_keywords', instantiating UserKeyword
293
# assigning the new key to 'special_key', values to
295
keywords = association_proxy('user_keywords', 'keyword',
297
UserKeyword(special_key=k, keyword=v)
300
def __init__(self, name):
303
class UserKeyword(Base):
304
__tablename__ = 'user_keyword'
305
user_id = Column(Integer, ForeignKey('user.id'), primary_key=True)
306
keyword_id = Column(Integer, ForeignKey('keyword.id'), primary_key=True)
307
special_key = Column(String)
309
# bidirectional user/user_keywords relationships, mapping
310
# user_keywords with a dictionary against "special_key" as key.
311
user = relationship(User, backref=backref(
313
collection_class=attribute_mapped_collection("special_key"),
314
cascade="all, delete-orphan"
317
keyword = relationship("Keyword")
320
__tablename__ = 'keyword'
321
id = Column(Integer, primary_key=True)
322
keyword = Column('keyword', String(64))
324
def __init__(self, keyword):
325
self.keyword = keyword
328
return 'Keyword(%s)' % repr(self.keyword)
330
We illustrate the ``.keywords`` collection as a dictionary, mapping the
331
``UserKeyword.string_key`` value to ``Keyword`` objects::
333
>>> user = User('log')
335
>>> user.keywords['sk1'] = Keyword('kw1')
336
>>> user.keywords['sk2'] = Keyword('kw2')
338
>>> print(user.keywords)
339
{'sk1': Keyword('kw1'), 'sk2': Keyword('kw2')}
341
.. _composite_association_proxy:
343
Composite Association Proxies
344
-----------------------------
346
Given our previous examples of proxying from relationship to scalar
347
attribute, proxying across an association object, and proxying dictionaries,
348
we can combine all three techniques together to give ``User``
349
a ``keywords`` dictionary that deals strictly with the string value
350
of ``special_key`` mapped to the string ``keyword``. Both the ``UserKeyword``
351
and ``Keyword`` classes are entirely concealed. This is achieved by building
352
an association proxy on ``User`` that refers to an association proxy
353
present on ``UserKeyword``::
355
from sqlalchemy import Column, Integer, String, ForeignKey
356
from sqlalchemy.orm import relationship, backref
358
from sqlalchemy.ext.associationproxy import association_proxy
359
from sqlalchemy.ext.declarative import declarative_base
360
from sqlalchemy.orm.collections import attribute_mapped_collection
362
Base = declarative_base()
365
__tablename__ = 'user'
366
id = Column(Integer, primary_key=True)
367
name = Column(String(64))
369
# the same 'user_keywords'->'keyword' proxy as in
370
# the basic dictionary example
371
keywords = association_proxy(
375
UserKeyword(special_key=k, keyword=v)
378
def __init__(self, name):
381
class UserKeyword(Base):
382
__tablename__ = 'user_keyword'
383
user_id = Column(Integer, ForeignKey('user.id'), primary_key=True)
384
keyword_id = Column(Integer, ForeignKey('keyword.id'),
386
special_key = Column(String)
387
user = relationship(User, backref=backref(
389
collection_class=attribute_mapped_collection("special_key"),
390
cascade="all, delete-orphan"
394
# the relationship to Keyword is now called
396
kw = relationship("Keyword")
398
# 'keyword' is changed to be a proxy to the
399
# 'keyword' attribute of 'Keyword'
400
keyword = association_proxy('kw', 'keyword')
403
__tablename__ = 'keyword'
404
id = Column(Integer, primary_key=True)
405
keyword = Column('keyword', String(64))
407
def __init__(self, keyword):
408
self.keyword = keyword
411
``User.keywords`` is now a dictionary of string to string, where
412
``UserKeyword`` and ``Keyword`` objects are created and removed for us
413
transparently using the association proxy. In the example below, we illustrate
414
usage of the assignment operator, also appropriately handled by the
415
association proxy, to apply a dictionary value to the collection at once::
417
>>> user = User('log')
418
>>> user.keywords = {
422
>>> print(user.keywords)
423
{'sk1': 'kw1', 'sk2': 'kw2'}
425
>>> user.keywords['sk3'] = 'kw3'
426
>>> del user.keywords['sk2']
427
>>> print(user.keywords)
428
{'sk1': 'kw1', 'sk3': 'kw3'}
430
>>> # illustrate un-proxied usage
431
... print(user.user_keywords['sk3'].kw)
432
<__main__.Keyword object at 0x12ceb90>
434
One caveat with our example above is that because ``Keyword`` objects are created
435
for each dictionary set operation, the example fails to maintain uniqueness for
436
the ``Keyword`` objects on their string name, which is a typical requirement for
437
a tagging scenario such as this one. For this use case the recipe
438
`UniqueObject <http://www.sqlalchemy.org/trac/wiki/UsageRecipes/UniqueObject>`_, or
439
a comparable creational strategy, is
440
recommended, which will apply a "lookup first, then create" strategy to the constructor
441
of the ``Keyword`` class, so that an already existing ``Keyword`` is returned if the
442
given name is already present.
444
Querying with Association Proxies
445
---------------------------------
447
The :class:`.AssociationProxy` features simple SQL construction capabilities
448
which relate down to the underlying :func:`.relationship` in use as well
449
as the target attribute. For example, the :meth:`.RelationshipProperty.Comparator.any`
450
and :meth:`.RelationshipProperty.Comparator.has` operations are available, and will produce
451
a "nested" EXISTS clause, such as in our basic association object example::
453
>>> print(session.query(User).filter(User.keywords.any(keyword='jek')))
454
SELECT user.id AS user_id, user.name AS user_name
456
WHERE EXISTS (SELECT 1
458
WHERE user.id = user_keyword.user_id AND (EXISTS (SELECT 1
460
WHERE keyword.id = user_keyword.keyword_id AND keyword.keyword = :keyword_1)))
462
For a proxy to a scalar attribute, ``__eq__()`` is supported::
464
>>> print(session.query(UserKeyword).filter(UserKeyword.keyword == 'jek'))
465
SELECT user_keyword.*
467
WHERE EXISTS (SELECT 1
469
WHERE keyword.id = user_keyword.keyword_id AND keyword.keyword = :keyword_1)
471
and ``.contains()`` is available for a proxy to a scalar collection::
473
>>> print(session.query(User).filter(User.keywords.contains('jek')))
476
WHERE EXISTS (SELECT 1
477
FROM userkeywords, keyword
478
WHERE user.id = userkeywords.user_id
479
AND keyword.id = userkeywords.keyword_id
480
AND keyword.keyword = :keyword_1)
482
:class:`.AssociationProxy` can be used with :meth:`.Query.join` somewhat manually
483
using the :attr:`~.AssociationProxy.attr` attribute in a star-args context::
485
q = session.query(User).join(*User.keywords.attr)
487
.. versionadded:: 0.7.3
488
:attr:`~.AssociationProxy.attr` attribute in a star-args context.
490
:attr:`~.AssociationProxy.attr` is composed of :attr:`.AssociationProxy.local_attr` and :attr:`.AssociationProxy.remote_attr`,
491
which are just synonyms for the actual proxied attributes, and can also
492
be used for querying::
494
uka = aliased(UserKeyword)
495
ka = aliased(Keyword)
496
q = session.query(User).\
497
join(uka, User.keywords.local_attr).\
498
join(ka, User.keywords.remote_attr)
500
.. versionadded:: 0.7.3
501
:attr:`.AssociationProxy.local_attr` and :attr:`.AssociationProxy.remote_attr`,
502
synonyms for the actual proxied attributes, and usable for querying.
507
.. autofunction:: association_proxy
509
.. autoclass:: AssociationProxy
513
.. autodata:: ASSOCIATION_PROXY
b'\\ No newline at end of file'
added
removed
doc/_sources/orm/extensions/associationproxy.txt
