1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
|
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>
associationproxy
— SQLAlchemy 0.6.3 Documentation</title>
<link rel="stylesheet" href="../../_static/pygments.css" type="text/css" />
<link rel="stylesheet" href="../../_static/docs.css" type="text/css" />
<script type="text/javascript">
var DOCUMENTATION_OPTIONS = {
URL_ROOT: '../../',
VERSION: '0.6.3',
COLLAPSE_MODINDEX: false,
FILE_SUFFIX: '.html'
};
</script>
<script type="text/javascript" src="../../_static/jquery.js"></script>
<script type="text/javascript" src="../../_static/underscore.js"></script>
<script type="text/javascript" src="../../_static/doctools.js"></script>
<script type="text/javascript" src="../../_static/init.js"></script>
<link rel="index" title="Index" href="../../genindex.html" />
<link rel="search" title="Search" href="../../search.html" />
<link rel="top" title="SQLAlchemy 0.6.3 Documentation" href="../../index.html" />
<link rel="up" title="sqlalchemy.ext" href="index.html" />
<link rel="next" title="orderinglist" href="orderinglist.html" />
<link rel="prev" title="declarative" href="declarative.html" />
</head>
<body>
<h1>SQLAlchemy 0.6.3 Documentation</h1>
<div id="search">
Search:
<form class="search" action="../../search.html" method="get">
<input type="text" name="q" size="18" /> <input type="submit" value="Search" />
<input type="hidden" name="check_keywords" value="yes" />
<input type="hidden" name="area" value="default" />
</form>
</div>
<div class="versionheader">
Version: <span class="versionnum">0.6.3</span> Last Updated: 07/15/2010 12:35:47
</div>
<div class="clearboth"></div>
<div class="topnav">
<div id="pagecontrol">
<a href="../index.html">API Reference</a>
|
<a href="../../genindex.html">Index</a>
<div class="sourcelink">(<a href="../../_sources/reference/ext/associationproxy.txt">view source)</div>
</div>
<div class="navbanner">
<a class="totoc" href="../../index.html">Table of Contents</a>
» <a href="../index.html" title="API Reference">API Reference</a>
» <a href="index.html" title="sqlalchemy.ext">sqlalchemy.ext</a>
»
associationproxy
<div class="prevnext">
Previous:
<a href="declarative.html" title="previous chapter">declarative</a>
Next:
<a href="orderinglist.html" title="next chapter">orderinglist</a>
</div>
<h2>
associationproxy
</h2>
</div>
<ul>
<li><a class="reference internal" href="#">associationproxy</a><ul>
<li><a class="reference internal" href="#simplifying-relationships">Simplifying Relationships</a></li>
<li><a class="reference internal" href="#simplifying-association-object-relationships">Simplifying Association Object Relationships</a></li>
<li><a class="reference internal" href="#building-complex-views">Building Complex Views</a></li>
<li><a class="reference internal" href="#api">API</a></li>
</ul>
</li>
</ul>
<div class="clearboth"></div>
</div>
<div class="document">
<div class="body">
<div class="section" id="associationproxy">
<span id="id1"></span><h1>associationproxy<a class="headerlink" href="#associationproxy" title="Permalink to this headline">¶</a></h1>
<span class="target" id="module-sqlalchemy.ext.associationproxy"></span><p><tt class="docutils literal"><span class="pre">associationproxy</span></tt> is used to create a simplified, read/write view of a
relationship. It can be used to cherry-pick fields from a collection of
related objects or to greatly simplify access to associated objects in an
association relationship.</p>
<div class="section" id="simplifying-relationships">
<h2>Simplifying Relationships<a class="headerlink" href="#simplifying-relationships" title="Permalink to this headline">¶</a></h2>
<p>Consider this “association object” mapping:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">users_table</span> <span class="o">=</span> <span class="n">Table</span><span class="p">(</span><span class="s">'users'</span><span class="p">,</span> <span class="n">metadata</span><span class="p">,</span>
<span class="n">Column</span><span class="p">(</span><span class="s">'id'</span><span class="p">,</span> <span class="n">Integer</span><span class="p">,</span> <span class="n">primary_key</span><span class="o">=</span><span class="bp">True</span><span class="p">),</span>
<span class="n">Column</span><span class="p">(</span><span class="s">'name'</span><span class="p">,</span> <span class="n">String</span><span class="p">(</span><span class="mi">64</span><span class="p">)),</span>
<span class="p">)</span>
<span class="n">keywords_table</span> <span class="o">=</span> <span class="n">Table</span><span class="p">(</span><span class="s">'keywords'</span><span class="p">,</span> <span class="n">metadata</span><span class="p">,</span>
<span class="n">Column</span><span class="p">(</span><span class="s">'id'</span><span class="p">,</span> <span class="n">Integer</span><span class="p">,</span> <span class="n">primary_key</span><span class="o">=</span><span class="bp">True</span><span class="p">),</span>
<span class="n">Column</span><span class="p">(</span><span class="s">'keyword'</span><span class="p">,</span> <span class="n">String</span><span class="p">(</span><span class="mi">64</span><span class="p">))</span>
<span class="p">)</span>
<span class="n">userkeywords_table</span> <span class="o">=</span> <span class="n">Table</span><span class="p">(</span><span class="s">'userkeywords'</span><span class="p">,</span> <span class="n">metadata</span><span class="p">,</span>
<span class="n">Column</span><span class="p">(</span><span class="s">'user_id'</span><span class="p">,</span> <span class="n">Integer</span><span class="p">,</span> <span class="n">ForeignKey</span><span class="p">(</span><span class="s">"users.id"</span><span class="p">),</span>
<span class="n">primary_key</span><span class="o">=</span><span class="bp">True</span><span class="p">),</span>
<span class="n">Column</span><span class="p">(</span><span class="s">'keyword_id'</span><span class="p">,</span> <span class="n">Integer</span><span class="p">,</span> <span class="n">ForeignKey</span><span class="p">(</span><span class="s">"keywords.id"</span><span class="p">),</span>
<span class="n">primary_key</span><span class="o">=</span><span class="bp">True</span><span class="p">)</span>
<span class="p">)</span>
<span class="k">class</span> <span class="nc">User</span><span class="p">(</span><span class="nb">object</span><span class="p">):</span>
<span class="k">def</span> <span class="nf">__init__</span><span class="p">(</span><span class="bp">self</span><span class="p">,</span> <span class="n">name</span><span class="p">):</span>
<span class="bp">self</span><span class="o">.</span><span class="n">name</span> <span class="o">=</span> <span class="n">name</span>
<span class="k">class</span> <span class="nc">Keyword</span><span class="p">(</span><span class="nb">object</span><span class="p">):</span>
<span class="k">def</span> <span class="nf">__init__</span><span class="p">(</span><span class="bp">self</span><span class="p">,</span> <span class="n">keyword</span><span class="p">):</span>
<span class="bp">self</span><span class="o">.</span><span class="n">keyword</span> <span class="o">=</span> <span class="n">keyword</span>
<span class="n">mapper</span><span class="p">(</span><span class="n">User</span><span class="p">,</span> <span class="n">users_table</span><span class="p">,</span> <span class="n">properties</span><span class="o">=</span><span class="p">{</span>
<span class="s">'kw'</span><span class="p">:</span> <span class="n">relationship</span><span class="p">(</span><span class="n">Keyword</span><span class="p">,</span> <span class="n">secondary</span><span class="o">=</span><span class="n">userkeywords_table</span><span class="p">)</span>
<span class="p">})</span>
<span class="n">mapper</span><span class="p">(</span><span class="n">Keyword</span><span class="p">,</span> <span class="n">keywords_table</span><span class="p">)</span></pre></div>
</div>
<p>Above are three simple tables, modeling users, keywords and a many-to-many
relationship between the two. These <tt class="docutils literal"><span class="pre">Keyword</span></tt> objects are little more
than a container for a name, and accessing them via the relationship is
awkward:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">user</span> <span class="o">=</span> <span class="n">User</span><span class="p">(</span><span class="s">'jek'</span><span class="p">)</span>
<span class="n">user</span><span class="o">.</span><span class="n">kw</span><span class="o">.</span><span class="n">append</span><span class="p">(</span><span class="n">Keyword</span><span class="p">(</span><span class="s">'cheese inspector'</span><span class="p">))</span>
<span class="k">print</span> <span class="n">user</span><span class="o">.</span><span class="n">kw</span>
<span class="c"># [<__main__.Keyword object at 0xb791ea0c>]</span>
<span class="k">print</span> <span class="n">user</span><span class="o">.</span><span class="n">kw</span><span class="p">[</span><span class="mi">0</span><span class="p">]</span><span class="o">.</span><span class="n">keyword</span>
<span class="c"># 'cheese inspector'</span>
<span class="k">print</span> <span class="p">[</span><span class="n">keyword</span><span class="o">.</span><span class="n">keyword</span> <span class="k">for</span> <span class="n">keyword</span> <span class="ow">in</span> <span class="n">user</span><span class="o">.</span><span class="n">kw</span><span class="p">]</span>
<span class="c"># ['cheese inspector']</span></pre></div>
</div>
<p>With <tt class="docutils literal"><span class="pre">association_proxy</span></tt> you have a “view” of the relationship that contains
just the <tt class="docutils literal"><span class="pre">.keyword</span></tt> of the related objects. The proxy is a Python
property, and unlike the mapper relationship, is defined in your class:</p>
<div class="highlight-python"><pre>from sqlalchemy.ext.associationproxy import association_proxy
class User(object):
def __init__(self, name):
self.name = name
# proxy the 'keyword' attribute from the 'kw' relationship
keywords = association_proxy('kw', 'keyword')
# ...
>>> user.kw
[<__main__.Keyword object at 0xb791ea0c>]
>>> user.keywords
['cheese inspector']
>>> user.keywords.append('snack ninja')
>>> user.keywords
['cheese inspector', 'snack ninja']
>>> user.kw
[<__main__.Keyword object at 0x9272a4c>, <__main__.Keyword object at 0xb7b396ec>]</pre>
</div>
<p>The proxy is read/write. New associated objects are created on demand when
values are added to the proxy, and modifying or removing an entry through
the proxy also affects the underlying collection.</p>
<blockquote>
<ul class="simple">
<li>The association proxy property is backed by a mapper-defined relationship,
either a collection or scalar.</li>
<li>You can access and modify both the proxy and the backing
relationship. Changes in one are immediate in the other.</li>
<li>The proxy acts like the type of the underlying collection. A list gets a
list-like proxy, a dict a dict-like proxy, and so on.</li>
<li>Multiple proxies for the same relationship are fine.</li>
<li>Proxies are lazy, and won’t trigger a load of the backing relationship until
they are accessed.</li>
<li>The relationship is inspected to determine the type of the related objects.</li>
<li>To construct new instances, the type is called with the value being
assigned, or key and value for dicts.</li>
<li>A <tt class="docutils literal"><span class="pre">``creator``</span></tt> function can be used to create instances instead.</li>
</ul>
</blockquote>
<p>Above, the <tt class="docutils literal"><span class="pre">Keyword.__init__</span></tt> takes a single argument <tt class="docutils literal"><span class="pre">keyword</span></tt>, which
maps conveniently to the value being set through the proxy. A <tt class="docutils literal"><span class="pre">creator</span></tt>
function could have been used instead if more flexibility was required.</p>
<p>Because the proxies are backed by a regular relationship collection, all of the
usual hooks and patterns for using collections are still in effect. The
most convenient behavior is the automatic setting of “parent”-type
relationships on assignment. In the example above, nothing special had to
be done to associate the Keyword to the User. Simply adding it to the
collection is sufficient.</p>
</div>
<div class="section" id="simplifying-association-object-relationships">
<h2>Simplifying Association Object Relationships<a class="headerlink" href="#simplifying-association-object-relationships" title="Permalink to this headline">¶</a></h2>
<p>Association proxies are also useful for keeping <tt class="docutils literal"><span class="pre">association</span> <span class="pre">objects</span></tt> out
the way during regular use. For example, the <tt class="docutils literal"><span class="pre">userkeywords</span></tt> table
might have a bunch of auditing columns that need to get updated when changes
are made- columns that are updated but seldom, if ever, accessed in your
application. A proxy can provide a very natural access pattern for the
relationship.</p>
<div class="highlight-python"><div class="highlight"><pre><span class="kn">from</span> <span class="nn">sqlalchemy.ext.associationproxy</span> <span class="kn">import</span> <span class="n">association_proxy</span>
<span class="c"># users_table and keywords_table tables as above, then:</span>
<span class="k">def</span> <span class="nf">get_current_uid</span><span class="p">():</span>
<span class="sd">"""Return the uid of the current user."""</span>
<span class="k">return</span> <span class="mi">1</span> <span class="c"># hardcoded for this example</span>
<span class="n">userkeywords_table</span> <span class="o">=</span> <span class="n">Table</span><span class="p">(</span><span class="s">'userkeywords'</span><span class="p">,</span> <span class="n">metadata</span><span class="p">,</span>
<span class="n">Column</span><span class="p">(</span><span class="s">'user_id'</span><span class="p">,</span> <span class="n">Integer</span><span class="p">,</span> <span class="n">ForeignKey</span><span class="p">(</span><span class="s">"users.id"</span><span class="p">),</span> <span class="n">primary_key</span><span class="o">=</span><span class="bp">True</span><span class="p">),</span>
<span class="n">Column</span><span class="p">(</span><span class="s">'keyword_id'</span><span class="p">,</span> <span class="n">Integer</span><span class="p">,</span> <span class="n">ForeignKey</span><span class="p">(</span><span class="s">"keywords.id"</span><span class="p">),</span> <span class="n">primary_key</span><span class="o">=</span><span class="bp">True</span><span class="p">),</span>
<span class="c"># add some auditing columns</span>
<span class="n">Column</span><span class="p">(</span><span class="s">'updated_at'</span><span class="p">,</span> <span class="n">DateTime</span><span class="p">,</span> <span class="n">default</span><span class="o">=</span><span class="n">datetime</span><span class="o">.</span><span class="n">now</span><span class="p">),</span>
<span class="n">Column</span><span class="p">(</span><span class="s">'updated_by'</span><span class="p">,</span> <span class="n">Integer</span><span class="p">,</span> <span class="n">default</span><span class="o">=</span><span class="n">get_current_uid</span><span class="p">,</span> <span class="n">onupdate</span><span class="o">=</span><span class="n">get_current_uid</span><span class="p">),</span>
<span class="p">)</span>
<span class="k">def</span> <span class="nf">_create_uk_by_keyword</span><span class="p">(</span><span class="n">keyword</span><span class="p">):</span>
<span class="sd">"""A creator function."""</span>
<span class="k">return</span> <span class="n">UserKeyword</span><span class="p">(</span><span class="n">keyword</span><span class="o">=</span><span class="n">keyword</span><span class="p">)</span>
<span class="k">class</span> <span class="nc">User</span><span class="p">(</span><span class="nb">object</span><span class="p">):</span>
<span class="k">def</span> <span class="nf">__init__</span><span class="p">(</span><span class="bp">self</span><span class="p">,</span> <span class="n">name</span><span class="p">):</span>
<span class="bp">self</span><span class="o">.</span><span class="n">name</span> <span class="o">=</span> <span class="n">name</span>
<span class="n">keywords</span> <span class="o">=</span> <span class="n">association_proxy</span><span class="p">(</span><span class="s">'user_keywords'</span><span class="p">,</span> <span class="s">'keyword'</span><span class="p">,</span> <span class="n">creator</span><span class="o">=</span><span class="n">_create_uk_by_keyword</span><span class="p">)</span>
<span class="k">class</span> <span class="nc">Keyword</span><span class="p">(</span><span class="nb">object</span><span class="p">):</span>
<span class="k">def</span> <span class="nf">__init__</span><span class="p">(</span><span class="bp">self</span><span class="p">,</span> <span class="n">keyword</span><span class="p">):</span>
<span class="bp">self</span><span class="o">.</span><span class="n">keyword</span> <span class="o">=</span> <span class="n">keyword</span>
<span class="k">def</span> <span class="nf">__repr__</span><span class="p">(</span><span class="bp">self</span><span class="p">):</span>
<span class="k">return</span> <span class="s">'Keyword(</span><span class="si">%s</span><span class="s">)'</span> <span class="o">%</span> <span class="nb">repr</span><span class="p">(</span><span class="bp">self</span><span class="o">.</span><span class="n">keyword</span><span class="p">)</span>
<span class="k">class</span> <span class="nc">UserKeyword</span><span class="p">(</span><span class="nb">object</span><span class="p">):</span>
<span class="k">def</span> <span class="nf">__init__</span><span class="p">(</span><span class="bp">self</span><span class="p">,</span> <span class="n">user</span><span class="o">=</span><span class="bp">None</span><span class="p">,</span> <span class="n">keyword</span><span class="o">=</span><span class="bp">None</span><span class="p">):</span>
<span class="bp">self</span><span class="o">.</span><span class="n">user</span> <span class="o">=</span> <span class="n">user</span>
<span class="bp">self</span><span class="o">.</span><span class="n">keyword</span> <span class="o">=</span> <span class="n">keyword</span>
<span class="n">mapper</span><span class="p">(</span><span class="n">User</span><span class="p">,</span> <span class="n">users_table</span><span class="p">)</span>
<span class="n">mapper</span><span class="p">(</span><span class="n">Keyword</span><span class="p">,</span> <span class="n">keywords_table</span><span class="p">)</span>
<span class="n">mapper</span><span class="p">(</span><span class="n">UserKeyword</span><span class="p">,</span> <span class="n">userkeywords_table</span><span class="p">,</span> <span class="n">properties</span><span class="o">=</span><span class="p">{</span>
<span class="s">'user'</span><span class="p">:</span> <span class="n">relationship</span><span class="p">(</span><span class="n">User</span><span class="p">,</span> <span class="n">backref</span><span class="o">=</span><span class="s">'user_keywords'</span><span class="p">),</span>
<span class="s">'keyword'</span><span class="p">:</span> <span class="n">relationship</span><span class="p">(</span><span class="n">Keyword</span><span class="p">),</span>
<span class="p">})</span>
<span class="n">user</span> <span class="o">=</span> <span class="n">User</span><span class="p">(</span><span class="s">'log'</span><span class="p">)</span>
<span class="n">kw1</span> <span class="o">=</span> <span class="n">Keyword</span><span class="p">(</span><span class="s">'new_from_blammo'</span><span class="p">)</span>
<span class="c"># Creating a UserKeyword association object will add a Keyword.</span>
<span class="c"># the "user" reference assignment in the UserKeyword() constructor</span>
<span class="c"># populates "user_keywords" via backref.</span>
<span class="n">UserKeyword</span><span class="p">(</span><span class="n">user</span><span class="p">,</span> <span class="n">kw1</span><span class="p">)</span>
<span class="c"># Accessing Keywords requires traversing UserKeywords</span>
<span class="k">print</span> <span class="n">user</span><span class="o">.</span><span class="n">user_keywords</span><span class="p">[</span><span class="mi">0</span><span class="p">]</span>
<span class="c"># <__main__.UserKeyword object at 0xb79bbbec></span>
<span class="k">print</span> <span class="n">user</span><span class="o">.</span><span class="n">user_keywords</span><span class="p">[</span><span class="mi">0</span><span class="p">]</span><span class="o">.</span><span class="n">keyword</span>
<span class="c"># Keyword('new_from_blammo')</span>
<span class="c"># Lots of work.</span>
<span class="c"># It's much easier to go through the association proxy!</span>
<span class="k">for</span> <span class="n">kw</span> <span class="ow">in</span> <span class="p">(</span><span class="n">Keyword</span><span class="p">(</span><span class="s">'its_big'</span><span class="p">),</span> <span class="n">Keyword</span><span class="p">(</span><span class="s">'its_heavy'</span><span class="p">),</span> <span class="n">Keyword</span><span class="p">(</span><span class="s">'its_wood'</span><span class="p">)):</span>
<span class="n">user</span><span class="o">.</span><span class="n">keywords</span><span class="o">.</span><span class="n">append</span><span class="p">(</span><span class="n">kw</span><span class="p">)</span>
<span class="k">print</span> <span class="n">user</span><span class="o">.</span><span class="n">keywords</span>
<span class="c"># [Keyword('new_from_blammo'), Keyword('its_big'), Keyword('its_heavy'), Keyword('its_wood')]</span></pre></div>
</div>
</div>
<div class="section" id="building-complex-views">
<h2>Building Complex Views<a class="headerlink" href="#building-complex-views" title="Permalink to this headline">¶</a></h2>
<div class="highlight-python"><div class="highlight"><pre><span class="n">stocks_table</span> <span class="o">=</span> <span class="n">Table</span><span class="p">(</span><span class="s">"stocks"</span><span class="p">,</span> <span class="n">meta</span><span class="p">,</span>
<span class="n">Column</span><span class="p">(</span><span class="s">'symbol'</span><span class="p">,</span> <span class="n">String</span><span class="p">(</span><span class="mi">10</span><span class="p">),</span> <span class="n">primary_key</span><span class="o">=</span><span class="bp">True</span><span class="p">),</span>
<span class="n">Column</span><span class="p">(</span><span class="s">'last_price'</span><span class="p">,</span> <span class="n">Numeric</span><span class="p">)</span>
<span class="p">)</span>
<span class="n">brokers_table</span> <span class="o">=</span> <span class="n">Table</span><span class="p">(</span><span class="s">"brokers"</span><span class="p">,</span> <span class="n">meta</span><span class="p">,</span>
<span class="n">Column</span><span class="p">(</span><span class="s">'id'</span><span class="p">,</span> <span class="n">Integer</span><span class="p">,</span><span class="n">primary_key</span><span class="o">=</span><span class="bp">True</span><span class="p">),</span>
<span class="n">Column</span><span class="p">(</span><span class="s">'name'</span><span class="p">,</span> <span class="n">String</span><span class="p">(</span><span class="mi">100</span><span class="p">),</span> <span class="n">nullable</span><span class="o">=</span><span class="bp">False</span><span class="p">)</span>
<span class="p">)</span>
<span class="n">holdings_table</span> <span class="o">=</span> <span class="n">Table</span><span class="p">(</span><span class="s">"holdings"</span><span class="p">,</span> <span class="n">meta</span><span class="p">,</span>
<span class="n">Column</span><span class="p">(</span><span class="s">'broker_id'</span><span class="p">,</span> <span class="n">Integer</span><span class="p">,</span> <span class="n">ForeignKey</span><span class="p">(</span><span class="s">'brokers.id'</span><span class="p">),</span> <span class="n">primary_key</span><span class="o">=</span><span class="bp">True</span><span class="p">),</span>
<span class="n">Column</span><span class="p">(</span><span class="s">'symbol'</span><span class="p">,</span> <span class="n">String</span><span class="p">(</span><span class="mi">10</span><span class="p">),</span> <span class="n">ForeignKey</span><span class="p">(</span><span class="s">'stocks.symbol'</span><span class="p">),</span> <span class="n">primary_key</span><span class="o">=</span><span class="bp">True</span><span class="p">),</span>
<span class="n">Column</span><span class="p">(</span><span class="s">'shares'</span><span class="p">,</span> <span class="n">Integer</span><span class="p">)</span>
<span class="p">)</span></pre></div>
</div>
<p>Above are three tables, modeling stocks, their brokers and the number of
shares of a stock held by each broker. This situation is quite different
from the association example above. <tt class="docutils literal"><span class="pre">shares</span></tt> is a <em>property of the
relationship</em>, an important one that we need to use all the time.</p>
<p>For this example, it would be very convenient if <tt class="docutils literal"><span class="pre">Broker</span></tt> objects had a
dictionary collection that mapped <tt class="docutils literal"><span class="pre">Stock</span></tt> instances to the shares held for
each. That’s easy:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="kn">from</span> <span class="nn">sqlalchemy.ext.associationproxy</span> <span class="kn">import</span> <span class="n">association_proxy</span>
<span class="kn">from</span> <span class="nn">sqlalchemy.orm.collections</span> <span class="kn">import</span> <span class="n">attribute_mapped_collection</span>
<span class="k">def</span> <span class="nf">_create_holding</span><span class="p">(</span><span class="n">stock</span><span class="p">,</span> <span class="n">shares</span><span class="p">):</span>
<span class="sd">"""A creator function, constructs Holdings from Stock and share quantity."""</span>
<span class="k">return</span> <span class="n">Holding</span><span class="p">(</span><span class="n">stock</span><span class="o">=</span><span class="n">stock</span><span class="p">,</span> <span class="n">shares</span><span class="o">=</span><span class="n">shares</span><span class="p">)</span>
<span class="k">class</span> <span class="nc">Broker</span><span class="p">(</span><span class="nb">object</span><span class="p">):</span>
<span class="k">def</span> <span class="nf">__init__</span><span class="p">(</span><span class="bp">self</span><span class="p">,</span> <span class="n">name</span><span class="p">):</span>
<span class="bp">self</span><span class="o">.</span><span class="n">name</span> <span class="o">=</span> <span class="n">name</span>
<span class="n">holdings</span> <span class="o">=</span> <span class="n">association_proxy</span><span class="p">(</span><span class="s">'by_stock'</span><span class="p">,</span> <span class="s">'shares'</span><span class="p">,</span> <span class="n">creator</span><span class="o">=</span><span class="n">_create_holding</span><span class="p">)</span>
<span class="k">class</span> <span class="nc">Stock</span><span class="p">(</span><span class="nb">object</span><span class="p">):</span>
<span class="k">def</span> <span class="nf">__init__</span><span class="p">(</span><span class="bp">self</span><span class="p">,</span> <span class="n">symbol</span><span class="p">):</span>
<span class="bp">self</span><span class="o">.</span><span class="n">symbol</span> <span class="o">=</span> <span class="n">symbol</span>
<span class="bp">self</span><span class="o">.</span><span class="n">last_price</span> <span class="o">=</span> <span class="mi">0</span>
<span class="k">class</span> <span class="nc">Holding</span><span class="p">(</span><span class="nb">object</span><span class="p">):</span>
<span class="k">def</span> <span class="nf">__init__</span><span class="p">(</span><span class="bp">self</span><span class="p">,</span> <span class="n">broker</span><span class="o">=</span><span class="bp">None</span><span class="p">,</span> <span class="n">stock</span><span class="o">=</span><span class="bp">None</span><span class="p">,</span> <span class="n">shares</span><span class="o">=</span><span class="mi">0</span><span class="p">):</span>
<span class="bp">self</span><span class="o">.</span><span class="n">broker</span> <span class="o">=</span> <span class="n">broker</span>
<span class="bp">self</span><span class="o">.</span><span class="n">stock</span> <span class="o">=</span> <span class="n">stock</span>
<span class="bp">self</span><span class="o">.</span><span class="n">shares</span> <span class="o">=</span> <span class="n">shares</span>
<span class="n">mapper</span><span class="p">(</span><span class="n">Stock</span><span class="p">,</span> <span class="n">stocks_table</span><span class="p">)</span>
<span class="n">mapper</span><span class="p">(</span><span class="n">Broker</span><span class="p">,</span> <span class="n">brokers_table</span><span class="p">,</span> <span class="n">properties</span><span class="o">=</span><span class="p">{</span>
<span class="s">'by_stock'</span><span class="p">:</span> <span class="n">relationship</span><span class="p">(</span><span class="n">Holding</span><span class="p">,</span>
<span class="n">collection_class</span><span class="o">=</span><span class="n">attribute_mapped_collection</span><span class="p">(</span><span class="s">'stock'</span><span class="p">))</span>
<span class="p">})</span>
<span class="n">mapper</span><span class="p">(</span><span class="n">Holding</span><span class="p">,</span> <span class="n">holdings_table</span><span class="p">,</span> <span class="n">properties</span><span class="o">=</span><span class="p">{</span>
<span class="s">'stock'</span><span class="p">:</span> <span class="n">relationship</span><span class="p">(</span><span class="n">Stock</span><span class="p">),</span>
<span class="s">'broker'</span><span class="p">:</span> <span class="n">relationship</span><span class="p">(</span><span class="n">Broker</span><span class="p">)</span>
<span class="p">})</span></pre></div>
</div>
<p>Above, we’ve set up the <tt class="docutils literal"><span class="pre">by_stock</span></tt> relationship collection to act as a
dictionary, using the <tt class="docutils literal"><span class="pre">.stock</span></tt> property of each Holding as a key.</p>
<p>Populating and accessing that dictionary manually is slightly inconvenient
because of the complexity of the Holdings association object:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">stock</span> <span class="o">=</span> <span class="n">Stock</span><span class="p">(</span><span class="s">'ZZK'</span><span class="p">)</span>
<span class="n">broker</span> <span class="o">=</span> <span class="n">Broker</span><span class="p">(</span><span class="s">'paj'</span><span class="p">)</span>
<span class="n">broker</span><span class="o">.</span><span class="n">by_stock</span><span class="p">[</span><span class="n">stock</span><span class="p">]</span> <span class="o">=</span> <span class="n">Holding</span><span class="p">(</span><span class="n">broker</span><span class="p">,</span> <span class="n">stock</span><span class="p">,</span> <span class="mi">10</span><span class="p">)</span>
<span class="k">print</span> <span class="n">broker</span><span class="o">.</span><span class="n">by_stock</span><span class="p">[</span><span class="n">stock</span><span class="p">]</span><span class="o">.</span><span class="n">shares</span>
<span class="c"># 10</span></pre></div>
</div>
<p>The <tt class="docutils literal"><span class="pre">holdings</span></tt> proxy we’ve added to the <tt class="docutils literal"><span class="pre">Broker</span></tt> class hides the details
of the <tt class="docutils literal"><span class="pre">Holding</span></tt> while also giving access to <tt class="docutils literal"><span class="pre">.shares</span></tt>:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="k">for</span> <span class="n">stock</span> <span class="ow">in</span> <span class="p">(</span><span class="n">Stock</span><span class="p">(</span><span class="s">'JEK'</span><span class="p">),</span> <span class="n">Stock</span><span class="p">(</span><span class="s">'STPZ'</span><span class="p">)):</span>
<span class="n">broker</span><span class="o">.</span><span class="n">holdings</span><span class="p">[</span><span class="n">stock</span><span class="p">]</span> <span class="o">=</span> <span class="mi">123</span>
<span class="k">for</span> <span class="n">stock</span><span class="p">,</span> <span class="n">shares</span> <span class="ow">in</span> <span class="n">broker</span><span class="o">.</span><span class="n">holdings</span><span class="o">.</span><span class="n">items</span><span class="p">():</span>
<span class="k">print</span> <span class="n">stock</span><span class="p">,</span> <span class="n">shares</span>
<span class="n">session</span><span class="o">.</span><span class="n">add</span><span class="p">(</span><span class="n">broker</span><span class="p">)</span>
<span class="n">session</span><span class="o">.</span><span class="n">commit</span><span class="p">()</span>
<span class="c"># lets take a peek at that holdings_table after committing changes to the db</span>
<span class="k">print</span> <span class="nb">list</span><span class="p">(</span><span class="n">holdings_table</span><span class="o">.</span><span class="n">select</span><span class="p">()</span><span class="o">.</span><span class="n">execute</span><span class="p">())</span>
<span class="c"># [(1, 'ZZK', 10), (1, 'JEK', 123), (1, 'STEPZ', 123)]</span></pre></div>
</div>
<p>Further examples can be found in the <tt class="docutils literal"><span class="pre">examples/</span></tt> directory in the
SQLAlchemy distribution.</p>
</div>
<div class="section" id="api">
<h2>API<a class="headerlink" href="#api" title="Permalink to this headline">¶</a></h2>
<dl class="function">
<dt id="sqlalchemy.ext.associationproxy.association_proxy">
<tt class="descclassname">sqlalchemy.ext.associationproxy.</tt><tt class="descname">association_proxy</tt><big>(</big><em>target_collection</em>, <em>attr</em>, <em>**kw</em><big>)</big><a class="headerlink" href="#sqlalchemy.ext.associationproxy.association_proxy" title="Permalink to this definition">¶</a></dt>
<dd><p>Return a Python property implementing a view of <em>attr</em> over a collection.</p>
<p>Implements a read/write view over an instance’s <em>target_collection</em>,
extracting <em>attr</em> from each member of the collection. The property acts
somewhat like this list comprehension:</p>
<div class="highlight-python"><pre>[getattr(member, *attr*)
for member in getattr(instance, *target_collection*)]</pre>
</div>
<p>Unlike the list comprehension, the collection returned by the property is
always in sync with <em>target_collection</em>, and mutations made to either
collection will be reflected in both.</p>
<p>Implements a Python property representing a relationship as a collection of
simpler values. The proxied property will mimic the collection type of
the target (list, dict or set), or, in the case of a one to one relationship,
a simple scalar value.</p>
<table class="docutils field-list" frame="void" rules="none">
<col class="field-name" />
<col class="field-body" />
<tbody valign="top">
<tr class="field"><th class="field-name">Parameters:</th><td class="field-body"><ul class="first last simple">
<li><strong>target_collection</strong> – Name of the relationship attribute we’ll proxy to,
usually created with <a class="reference internal" href="../orm/mapping.html#sqlalchemy.orm.relationship" title="sqlalchemy.orm.relationship"><tt class="xref py py-func docutils literal"><span class="pre">relationship()</span></tt></a>.</li>
<li><strong>attr</strong> – <p>Attribute on the associated instances we’ll proxy for.</p>
<p>For example, given a target collection of [obj1, obj2], a list created
by this proxy property would look like [getattr(obj1, <em>attr</em>),
getattr(obj2, <em>attr</em>)]</p>
<p>If the relationship is one-to-one or otherwise uselist=False, then simply:
getattr(obj, <em>attr</em>)</p>
</li>
<li><strong>creator</strong> – <p>optional.</p>
<p>When new items are added to this proxied collection, new instances of
the class collected by the target collection will be created. For list
and set collections, the target class constructor will be called with
the ‘value’ for the new instance. For dict types, two arguments are
passed: key and value.</p>
<p>If you want to construct instances differently, supply a <em>creator</em>
function that takes arguments as above and returns instances.</p>
<p>For scalar relationships, creator() will be called if the target is None.
If the target is present, set operations are proxied to setattr() on the
associated object.</p>
<p>If you have an associated object with multiple attributes, you may set
up multiple association proxies mapping to different attributes. See
the unit tests for examples, and for examples of how creator() functions
can be used to construct the scalar relationship on-demand in this
situation.</p>
</li>
<li><strong>**kw</strong> – Passes along any other keyword arguments to
<a class="reference internal" href="#sqlalchemy.ext.associationproxy.AssociationProxy" title="sqlalchemy.ext.associationproxy.AssociationProxy"><tt class="xref py py-class docutils literal"><span class="pre">AssociationProxy</span></tt></a>.</li>
</ul>
</td>
</tr>
</tbody>
</table>
</dd></dl>
<dl class="class">
<dt id="sqlalchemy.ext.associationproxy.AssociationProxy">
<em class="property">class </em><tt class="descclassname">sqlalchemy.ext.associationproxy.</tt><tt class="descname">AssociationProxy</tt><big>(</big><em>target_collection</em>, <em>attr</em>, <em>creator=None</em>, <em>getset_factory=None</em>, <em>proxy_factory=None</em>, <em>proxy_bulk_set=None</em><big>)</big><a class="headerlink" href="#sqlalchemy.ext.associationproxy.AssociationProxy" title="Permalink to this definition">¶</a></dt>
<dd><p>A descriptor that presents a read/write view of an object attribute.</p>
<dl class="method">
<dt id="sqlalchemy.ext.associationproxy.AssociationProxy.__init__">
<tt class="descname">__init__</tt><big>(</big><em>target_collection</em>, <em>attr</em>, <em>creator=None</em>, <em>getset_factory=None</em>, <em>proxy_factory=None</em>, <em>proxy_bulk_set=None</em><big>)</big><a class="headerlink" href="#sqlalchemy.ext.associationproxy.AssociationProxy.__init__" title="Permalink to this definition">¶</a></dt>
<dd><p>Arguments are:</p>
<dl class="docutils">
<dt>target_collection</dt>
<dd>Name of the collection we’ll proxy to, usually created with
‘relationship()’ in a mapper setup.</dd>
<dt>attr</dt>
<dd>Attribute on the collected instances we’ll proxy for. For example,
given a target collection of [obj1, obj2], a list created by this
proxy property would look like [getattr(obj1, attr), getattr(obj2,
attr)]</dd>
<dt>creator</dt>
<dd><p class="first">Optional. When new items are added to this proxied collection, new
instances of the class collected by the target collection will be
created. For list and set collections, the target class constructor
will be called with the ‘value’ for the new instance. For dict
types, two arguments are passed: key and value.</p>
<p class="last">If you want to construct instances differently, supply a ‘creator’
function that takes arguments as above and returns instances.</p>
</dd>
<dt>getset_factory</dt>
<dd><p class="first">Optional. Proxied attribute access is automatically handled by
routines that get and set values based on the <cite>attr</cite> argument for
this proxy.</p>
<p class="last">If you would like to customize this behavior, you may supply a
<cite>getset_factory</cite> callable that produces a tuple of <cite>getter</cite> and
<cite>setter</cite> functions. The factory is called with two arguments, the
abstract type of the underlying collection and this proxy instance.</p>
</dd>
<dt>proxy_factory</dt>
<dd>Optional. The type of collection to emulate is determined by
sniffing the target collection. If your collection type can’t be
determined by duck typing or you’d like to use a different
collection implementation, you may supply a factory function to
produce those collections. Only applicable to non-scalar relationships.</dd>
<dt>proxy_bulk_set</dt>
<dd>Optional, use with proxy_factory. See the _set() method for
details.</dd>
</dl>
</dd></dl>
<dl class="method">
<dt id="sqlalchemy.ext.associationproxy.AssociationProxy.any">
<tt class="descname">any</tt><big>(</big><em>criterion=None</em>, <em>**kwargs</em><big>)</big><a class="headerlink" href="#sqlalchemy.ext.associationproxy.AssociationProxy.any" title="Permalink to this definition">¶</a></dt>
<dd></dd></dl>
<dl class="method">
<dt id="sqlalchemy.ext.associationproxy.AssociationProxy.contains">
<tt class="descname">contains</tt><big>(</big><em>obj</em><big>)</big><a class="headerlink" href="#sqlalchemy.ext.associationproxy.AssociationProxy.contains" title="Permalink to this definition">¶</a></dt>
<dd></dd></dl>
<dl class="method">
<dt id="sqlalchemy.ext.associationproxy.AssociationProxy.has">
<tt class="descname">has</tt><big>(</big><em>criterion=None</em>, <em>**kwargs</em><big>)</big><a class="headerlink" href="#sqlalchemy.ext.associationproxy.AssociationProxy.has" title="Permalink to this definition">¶</a></dt>
<dd></dd></dl>
<dl class="attribute">
<dt id="sqlalchemy.ext.associationproxy.AssociationProxy.target_class">
<tt class="descname">target_class</tt><a class="headerlink" href="#sqlalchemy.ext.associationproxy.AssociationProxy.target_class" title="Permalink to this definition">¶</a></dt>
<dd><p>The class the proxy is attached to.</p>
</dd></dl>
</dd></dl>
</div>
</div>
</div>
</div>
<div class="bottomnav">
<div class="prevnext">
Previous:
<a href="declarative.html" title="previous chapter">declarative</a>
Next:
<a href="orderinglist.html" title="next chapter">orderinglist</a>
</div>
<div class="doc_copyright">
© Copyright 2007, 2008, 2009, 2010, the SQLAlchemy authors and contributors.
Created using <a href="http://sphinx.pocoo.org/">Sphinx</a> 1.0b2+.
</div>
</div>
</body>
</html>
|
