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
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
1001
1002
1003
1004
1005
1006
1007
1008
1009
1010
1011
1012
1013
1014
1015
1016
1017
1018
1019
1020
1021
1022
1023
1024
1025
1026
1027
1028
1029
1030
1031
1032
1033
1034
1035
1036
1037
1038
1039
1040
1041
1042
1043
1044
1045
1046
1047
1048
1049
1050
1051
1052
1053
1054
1055
1056
1057
1058
1059
1060
1061
1062
1063
1064
1065
1066
1067
1068
1069
1070
1071
1072
1073
1074
1075
1076
1077
1078
1079
1080
1081
1082
1083
1084
1085
1086
1087
1088
1089
1090
1091
1092
1093
1094
1095
1096
1097
1098
1099
1100
1101
1102
1103
1104
1105
1106
1107
1108
1109
1110
1111
1112
1113
1114
1115
1116
1117
1118
1119
1120
1121
1122
1123
1124
1125
1126
1127
1128
1129
1130
1131
1132
1133
1134
1135
1136
1137
1138
1139
1140
1141
1142
1143
1144
1145
1146
1147
1148
1149
1150
1151
1152
1153
1154
1155
1156
1157
1158
1159
1160
1161
1162
1163
1164
1165
1166
1167
1168
1169
1170
1171
1172
1173
1174
1175
1176
1177
1178
1179
1180
1181
1182
1183
1184
1185
1186
1187
1188
1189
1190
1191
1192
1193
1194
1195
1196
1197
1198
1199
1200
1201
1202
1203
1204
1205
1206
1207
1208
1209
1210
1211
1212
1213
1214
1215
1216
1217
1218
1219
1220
1221
1222
1223
1224
1225
1226
1227
1228
1229
1230
1231
1232
1233
1234
1235
1236
1237
1238
1239
1240
1241
1242
1243
1244
1245
1246
1247
1248
1249
1250
1251
1252
1253
1254
1255
1256
1257
1258
1259
1260
1261
1262
1263
1264
1265
1266
1267
1268
1269
1270
1271
1272
1273
1274
1275
1276
1277
1278
1279
1280
1281
1282
1283
1284
1285
1286
1287
1288
1289
1290
1291
1292
1293
1294
1295
1296
1297
1298
1299
1300
1301
1302
1303
1304
1305
1306
1307
1308
1309
1310
1311
1312
1313
1314
1315
1316
1317
1318
1319
1320
1321
1322
1323
1324
1325
1326
1327
1328
1329
1330
1331
1332
1333
1334
1335
1336
1337
1338
1339
1340
1341
1342
1343
1344
1345
1346
1347
1348
1349
1350
1351
1352
1353
1354
1355
1356
1357
1358
1359
1360
1361
1362
1363
1364
1365
1366
1367
1368
1369
1370
1371
1372
1373
1374
1375
1376
1377
1378
1379
1380
1381
1382
1383
1384
1385
1386
1387
1388
1389
1390
1391
1392
1393
1394
1395
1396
1397
1398
1399
1400
1401
1402
1403
1404
1405
1406
1407
1408
1409
1410
1411
1412
1413
1414
1415
1416
1417
1418
1419
1420
1421
1422
1423
1424
1425
1426
1427
1428
1429
1430
1431
1432
1433
1434
1435
1436
1437
1438
1439
1440
1441
1442
1443
1444
1445
1446
1447
1448
1449
1450
1451
1452
1453
1454
1455
1456
1457
1458
1459
1460
1461
1462
1463
1464
1465
1466
1467
1468
1469
1470
1471
1472
1473
1474
1475
1476
1477
1478
1479
1480
1481
1482
1483
1484
1485
1486
1487
1488
1489
1490
1491
1492
1493
1494
1495
1496
1497
1498
1499
1500
1501
1502
1503
1504
1505
1506
1507
1508
1509
1510
1511
1512
1513
1514
1515
1516
1517
1518
1519
1520
1521
1522
1523
1524
1525
1526
1527
1528
1529
1530
1531
1532
1533
1534
1535
1536
1537
1538
1539
1540
1541
1542
1543
1544
1545
1546
1547
1548
1549
1550
1551
1552
1553
1554
1555
1556
1557
1558
1559
1560
1561
1562
1563
1564
1565
1566
1567
1568
1569
1570
1571
1572
1573
1574
1575
1576
1577
1578
1579
1580
1581
1582
1583
1584
1585
1586
1587
1588
1589
1590
1591
1592
1593
1594
1595
1596
1597
1598
1599
1600
1601
1602
1603
1604
1605
1606
1607
1608
1609
1610
1611
1612
1613
1614
1615
1616
1617
1618
1619
1620
1621
1622
1623
1624
1625
1626
1627
1628
1629
1630
1631
1632
1633
1634
1635
1636
1637
1638
1639
1640
1641
1642
1643
1644
1645
1646
1647
1648
1649
1650
1651
1652
1653
1654
1655
1656
1657
1658
1659
1660
1661
1662
1663
1664
1665
1666
1667
1668
1669
1670
1671
1672
1673
1674
1675
1676
1677
1678
1679
1680
1681
1682
1683
1684
1685
1686
1687
1688
1689
1690
1691
1692
1693
1694
1695
1696
1697
1698
1699
1700
|
<!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>
Mapper Configuration
— 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="next" title="Using the Session" href="session.html" />
<link rel="prev" title="SQL Expression Language Tutorial" href="sqlexpression.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="reference/index.html">API Reference</a>
|
<a href="genindex.html">Index</a>
<div class="sourcelink">(<a href="_sources/mappers.txt">view source)</div>
</div>
<div class="navbanner">
<a class="totoc" href="index.html">Table of Contents</a>
»
Mapper Configuration
<div class="prevnext">
Previous:
<a href="sqlexpression.html" title="previous chapter">SQL Expression Language Tutorial</a>
Next:
<a href="session.html" title="next chapter">Using the Session</a>
</div>
<h2>
Mapper Configuration
</h2>
</div>
<ul>
<li><a class="reference internal" href="#">Mapper Configuration</a><ul>
<li><a class="reference internal" href="#id1">Mapper Configuration</a><ul>
<li><a class="reference internal" href="#customizing-column-properties">Customizing Column Properties</a></li>
<li><a class="reference internal" href="#deferred-column-loading">Deferred Column Loading</a></li>
<li><a class="reference internal" href="#sql-expressions-as-mapped-attributes">SQL Expressions as Mapped Attributes</a></li>
<li><a class="reference internal" href="#changing-attribute-behavior">Changing Attribute Behavior</a><ul>
<li><a class="reference internal" href="#simple-validators">Simple Validators</a></li>
<li><a class="reference internal" href="#using-descriptors">Using Descriptors</a></li>
<li><a class="reference internal" href="#custom-comparators">Custom Comparators</a></li>
</ul>
</li>
<li><a class="reference internal" href="#composite-column-types">Composite Column Types</a></li>
<li><a class="reference internal" href="#controlling-ordering">Controlling Ordering</a></li>
<li><a class="reference internal" href="#mapping-class-inheritance-hierarchies">Mapping Class Inheritance Hierarchies</a><ul>
<li><a class="reference internal" href="#joined-table-inheritance">Joined Table Inheritance</a><ul>
<li><a class="reference internal" href="#controlling-which-tables-are-queried">Controlling Which Tables are Queried</a></li>
<li><a class="reference internal" href="#creating-joins-to-specific-subtypes">Creating Joins to Specific Subtypes</a></li>
</ul>
</li>
<li><a class="reference internal" href="#single-table-inheritance">Single Table Inheritance</a></li>
<li><a class="reference internal" href="#concrete-table-inheritance">Concrete Table Inheritance</a></li>
<li><a class="reference internal" href="#using-relationships-with-inheritance">Using Relationships with Inheritance</a></li>
</ul>
</li>
<li><a class="reference internal" href="#mapping-a-class-against-multiple-tables">Mapping a Class against Multiple Tables</a></li>
<li><a class="reference internal" href="#mapping-a-class-against-arbitrary-selects">Mapping a Class against Arbitrary Selects</a></li>
<li><a class="reference internal" href="#multiple-mappers-for-one-class">Multiple Mappers for One Class</a></li>
<li><a class="reference internal" href="#multiple-persistence-mappers-for-one-class">Multiple “Persistence” Mappers for One Class</a></li>
<li><a class="reference internal" href="#constructors-and-object-initialization">Constructors and Object Initialization</a></li>
<li><a class="reference internal" href="#extending-mapper">Extending Mapper</a></li>
</ul>
</li>
<li><a class="reference internal" href="#relationship-configuration">Relationship Configuration</a><ul>
<li><a class="reference internal" href="#basic-relational-patterns">Basic Relational Patterns</a><ul>
<li><a class="reference internal" href="#one-to-many">One To Many</a></li>
<li><a class="reference internal" href="#many-to-one">Many To One</a></li>
<li><a class="reference internal" href="#one-to-one">One To One</a></li>
<li><a class="reference internal" href="#many-to-many">Many To Many</a></li>
<li><a class="reference internal" href="#association-object">Association Object</a></li>
</ul>
</li>
<li><a class="reference internal" href="#adjacency-list-relationships">Adjacency List Relationships</a><ul>
<li><a class="reference internal" href="#self-referential-query-strategies">Self-Referential Query Strategies</a></li>
<li><a class="reference internal" href="#configuring-eager-loading">Configuring Eager Loading</a></li>
</ul>
</li>
<li><a class="reference internal" href="#specifying-alternate-join-conditions-to-relationship">Specifying Alternate Join Conditions to relationship()</a><ul>
<li><a class="reference internal" href="#specifying-foreign-keys">Specifying Foreign Keys</a></li>
<li><a class="reference internal" href="#building-query-enabled-properties">Building Query-Enabled Properties</a></li>
<li><a class="reference internal" href="#multiple-relationships-against-the-same-parent-child">Multiple Relationships against the Same Parent/Child</a></li>
</ul>
</li>
<li><a class="reference internal" href="#rows-that-point-to-themselves-mutually-dependent-rows">Rows that point to themselves / Mutually Dependent Rows</a></li>
<li><a class="reference internal" href="#advdatamapping-entitycollections">Alternate Collection Implementations</a><ul>
<li><a class="reference internal" href="#custom-collection-implementations">Custom Collection Implementations</a></li>
<li><a class="reference internal" href="#annotating-custom-collections-via-decorators">Annotating Custom Collections via Decorators</a></li>
<li><a class="reference internal" href="#dictionary-based-collections">Dictionary-Based Collections</a></li>
<li><a class="reference internal" href="#instrumentation-and-custom-types">Instrumentation and Custom Types</a></li>
</ul>
</li>
<li><a class="reference internal" href="#configuring-loader-strategies-lazy-loading-eager-loading">Configuring Loader Strategies: Lazy Loading, Eager Loading</a><ul>
<li><a class="reference internal" href="#what-kind-of-loading-to-use">What Kind of Loading to Use ?</a></li>
<li><a class="reference internal" href="#routing-explicit-joins-statements-into-eagerly-loaded-collections">Routing Explicit Joins/Statements into Eagerly Loaded Collections</a></li>
</ul>
</li>
<li><a class="reference internal" href="#working-with-large-collections">Working with Large Collections</a><ul>
<li><a class="reference internal" href="#dynamic-relationship-loaders">Dynamic Relationship Loaders</a></li>
<li><a class="reference internal" href="#setting-noload">Setting Noload</a></li>
<li><a class="reference internal" href="#using-passive-deletes">Using Passive Deletes</a></li>
</ul>
</li>
<li><a class="reference internal" href="#mutable-primary-keys-update-cascades">Mutable Primary Keys / Update Cascades</a></li>
</ul>
</li>
</ul>
</li>
</ul>
<div class="clearboth"></div>
</div>
<div class="document">
<div class="body">
<div class="section" id="mapper-configuration">
<span id="datamapping-toplevel"></span><h1>Mapper Configuration<a class="headerlink" href="#mapper-configuration" title="Permalink to this headline">¶</a></h1>
<p>This section references most major configurational patterns involving the <a class="reference internal" href="reference/orm/mapping.html#sqlalchemy.orm.mapper" title="sqlalchemy.orm.mapper"><tt class="xref py py-func docutils literal"><span class="pre">mapper()</span></tt></a> and <a class="reference internal" href="reference/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> functions. It assumes you’ve worked through <a class="reference internal" href="ormtutorial.html"><em>Object Relational Tutorial</em></a> and know how to construct and use rudimentary mappers and relationships.</p>
<div class="section" id="id1">
<h2>Mapper Configuration<a class="headerlink" href="#id1" title="Permalink to this headline">¶</a></h2>
<div class="section" id="customizing-column-properties">
<h3>Customizing Column Properties<a class="headerlink" href="#customizing-column-properties" title="Permalink to this headline">¶</a></h3>
<p>The default behavior of a <tt class="docutils literal"><span class="pre">mapper</span></tt> is to assemble all the columns in the mapped <a class="reference internal" href="reference/sqlalchemy/schema.html#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal"><span class="pre">Table</span></tt></a> into mapped object attributes. This behavior can be modified in several ways, as well as enhanced by SQL expressions.</p>
<p>To load only a part of the columns referenced by a table as attributes, use the <tt class="docutils literal"><span class="pre">include_properties</span></tt> and <tt class="docutils literal"><span class="pre">exclude_properties</span></tt> arguments:</p>
<div class="highlight-python"><div class="highlight"><pre><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">include_properties</span><span class="o">=</span><span class="p">[</span><span class="s">'user_id'</span><span class="p">,</span> <span class="s">'user_name'</span><span class="p">])</span>
<span class="n">mapper</span><span class="p">(</span><span class="n">Address</span><span class="p">,</span> <span class="n">addresses_table</span><span class="p">,</span> <span class="n">exclude_properties</span><span class="o">=</span><span class="p">[</span><span class="s">'street'</span><span class="p">,</span> <span class="s">'city'</span><span class="p">,</span> <span class="s">'state'</span><span class="p">,</span> <span class="s">'zip'</span><span class="p">])</span></pre></div>
</div>
<p>To change the name of the attribute mapped to a particular column, place the <a class="reference internal" href="reference/sqlalchemy/schema.html#sqlalchemy.schema.Column" title="sqlalchemy.schema.Column"><tt class="xref py py-class docutils literal"><span class="pre">Column</span></tt></a> object in the <tt class="docutils literal"><span class="pre">properties</span></tt> dictionary with the desired key:</p>
<div class="highlight-python"><div class="highlight"><pre><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">'id'</span><span class="p">:</span> <span class="n">users_table</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">user_id</span><span class="p">,</span>
<span class="s">'name'</span><span class="p">:</span> <span class="n">users_table</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">user_name</span><span class="p">,</span>
<span class="p">})</span></pre></div>
</div>
<p>To change the names of all attributes using a prefix, use the <tt class="docutils literal"><span class="pre">column_prefix</span></tt> option. This is useful for classes which wish to add their own <tt class="docutils literal"><span class="pre">property</span></tt> accessors:</p>
<div class="highlight-python"><div class="highlight"><pre><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">column_prefix</span><span class="o">=</span><span class="s">'_'</span><span class="p">)</span></pre></div>
</div>
<p>The above will place attribute names such as <tt class="docutils literal"><span class="pre">_user_id</span></tt>, <tt class="docutils literal"><span class="pre">_user_name</span></tt>, <tt class="docutils literal"><span class="pre">_password</span></tt> etc. on the mapped <tt class="docutils literal"><span class="pre">User</span></tt> class.</p>
<p>To place multiple columns which are known to be “synonymous” based on foreign key relationship or join condition into the same mapped attribute, put them together using a list, as below where we map to a <a class="reference internal" href="reference/sqlalchemy/expressions.html#sqlalchemy.sql.expression.Join" title="sqlalchemy.sql.expression.Join"><tt class="xref py py-class docutils literal"><span class="pre">Join</span></tt></a>:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="c"># join users and addresses</span>
<span class="n">usersaddresses</span> <span class="o">=</span> <span class="n">sql</span><span class="o">.</span><span class="n">join</span><span class="p">(</span><span class="n">users_table</span><span class="p">,</span> <span class="n">addresses_table</span><span class="p">,</span> \
<span class="n">users_table</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">user_id</span> <span class="o">==</span> <span class="n">addresses_table</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">user_id</span><span class="p">)</span>
<span class="n">mapper</span><span class="p">(</span><span class="n">User</span><span class="p">,</span> <span class="n">usersaddresses</span><span class="p">,</span> <span class="n">properties</span><span class="o">=</span><span class="p">{</span>
<span class="s">'id'</span><span class="p">:[</span><span class="n">users_table</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">user_id</span><span class="p">,</span> <span class="n">addresses_table</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">user_id</span><span class="p">],</span>
<span class="p">})</span></pre></div>
</div>
</div>
<div class="section" id="deferred-column-loading">
<h3>Deferred Column Loading<a class="headerlink" href="#deferred-column-loading" title="Permalink to this headline">¶</a></h3>
<p>This feature allows particular columns of a table to not be loaded by default, instead being loaded later on when first referenced. It is essentially “column-level lazy loading”. This feature is useful when one wants to avoid loading a large text or binary field into memory when it’s not needed. Individual columns can be lazy loaded by themselves or placed into groups that lazy-load together:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">book_excerpts</span> <span class="o">=</span> <span class="n">Table</span><span class="p">(</span><span class="s">'books'</span><span class="p">,</span> <span class="n">db</span><span class="p">,</span>
<span class="n">Column</span><span class="p">(</span><span class="s">'book_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">'title'</span><span class="p">,</span> <span class="n">String</span><span class="p">(</span><span class="mi">200</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="n">Column</span><span class="p">(</span><span class="s">'summary'</span><span class="p">,</span> <span class="n">String</span><span class="p">(</span><span class="mi">2000</span><span class="p">)),</span>
<span class="n">Column</span><span class="p">(</span><span class="s">'excerpt'</span><span class="p">,</span> <span class="n">String</span><span class="p">),</span>
<span class="n">Column</span><span class="p">(</span><span class="s">'photo'</span><span class="p">,</span> <span class="n">Binary</span><span class="p">)</span>
<span class="p">)</span>
<span class="k">class</span> <span class="nc">Book</span><span class="p">(</span><span class="nb">object</span><span class="p">):</span>
<span class="k">pass</span>
<span class="c"># define a mapper that will load each of 'excerpt' and 'photo' in</span>
<span class="c"># separate, individual-row SELECT statements when each attribute</span>
<span class="c"># is first referenced on the individual object instance</span>
<span class="n">mapper</span><span class="p">(</span><span class="n">Book</span><span class="p">,</span> <span class="n">book_excerpts</span><span class="p">,</span> <span class="n">properties</span><span class="o">=</span><span class="p">{</span>
<span class="s">'excerpt'</span><span class="p">:</span> <span class="n">deferred</span><span class="p">(</span><span class="n">book_excerpts</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">excerpt</span><span class="p">),</span>
<span class="s">'photo'</span><span class="p">:</span> <span class="n">deferred</span><span class="p">(</span><span class="n">book_excerpts</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">photo</span><span class="p">)</span>
<span class="p">})</span></pre></div>
</div>
<p>Deferred columns can be placed into groups so that they load together:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">book_excerpts</span> <span class="o">=</span> <span class="n">Table</span><span class="p">(</span><span class="s">'books'</span><span class="p">,</span> <span class="n">db</span><span class="p">,</span>
<span class="n">Column</span><span class="p">(</span><span class="s">'book_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">'title'</span><span class="p">,</span> <span class="n">String</span><span class="p">(</span><span class="mi">200</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="n">Column</span><span class="p">(</span><span class="s">'summary'</span><span class="p">,</span> <span class="n">String</span><span class="p">(</span><span class="mi">2000</span><span class="p">)),</span>
<span class="n">Column</span><span class="p">(</span><span class="s">'excerpt'</span><span class="p">,</span> <span class="n">String</span><span class="p">),</span>
<span class="n">Column</span><span class="p">(</span><span class="s">'photo1'</span><span class="p">,</span> <span class="n">Binary</span><span class="p">),</span>
<span class="n">Column</span><span class="p">(</span><span class="s">'photo2'</span><span class="p">,</span> <span class="n">Binary</span><span class="p">),</span>
<span class="n">Column</span><span class="p">(</span><span class="s">'photo3'</span><span class="p">,</span> <span class="n">Binary</span><span class="p">)</span>
<span class="p">)</span>
<span class="k">class</span> <span class="nc">Book</span><span class="p">(</span><span class="nb">object</span><span class="p">):</span>
<span class="k">pass</span>
<span class="c"># define a mapper with a 'photos' deferred group. when one photo is referenced,</span>
<span class="c"># all three photos will be loaded in one SELECT statement. The 'excerpt' will</span>
<span class="c"># be loaded separately when it is first referenced.</span>
<span class="n">mapper</span><span class="p">(</span><span class="n">Book</span><span class="p">,</span> <span class="n">book_excerpts</span><span class="p">,</span> <span class="n">properties</span> <span class="o">=</span> <span class="p">{</span>
<span class="s">'excerpt'</span><span class="p">:</span> <span class="n">deferred</span><span class="p">(</span><span class="n">book_excerpts</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">excerpt</span><span class="p">),</span>
<span class="s">'photo1'</span><span class="p">:</span> <span class="n">deferred</span><span class="p">(</span><span class="n">book_excerpts</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">photo1</span><span class="p">,</span> <span class="n">group</span><span class="o">=</span><span class="s">'photos'</span><span class="p">),</span>
<span class="s">'photo2'</span><span class="p">:</span> <span class="n">deferred</span><span class="p">(</span><span class="n">book_excerpts</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">photo2</span><span class="p">,</span> <span class="n">group</span><span class="o">=</span><span class="s">'photos'</span><span class="p">),</span>
<span class="s">'photo3'</span><span class="p">:</span> <span class="n">deferred</span><span class="p">(</span><span class="n">book_excerpts</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">photo3</span><span class="p">,</span> <span class="n">group</span><span class="o">=</span><span class="s">'photos'</span><span class="p">)</span>
<span class="p">})</span></pre></div>
</div>
<p>You can defer or undefer columns at the <a class="reference internal" href="reference/orm/query.html#sqlalchemy.orm.query.Query" title="sqlalchemy.orm.query.Query"><tt class="xref py py-class docutils literal"><span class="pre">Query</span></tt></a> level using the <tt class="docutils literal"><span class="pre">defer</span></tt> and <tt class="docutils literal"><span class="pre">undefer</span></tt> options:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">query</span> <span class="o">=</span> <span class="n">session</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="n">Book</span><span class="p">)</span>
<span class="n">query</span><span class="o">.</span><span class="n">options</span><span class="p">(</span><span class="n">defer</span><span class="p">(</span><span class="s">'summary'</span><span class="p">))</span><span class="o">.</span><span class="n">all</span><span class="p">()</span>
<span class="n">query</span><span class="o">.</span><span class="n">options</span><span class="p">(</span><span class="n">undefer</span><span class="p">(</span><span class="s">'excerpt'</span><span class="p">))</span><span class="o">.</span><span class="n">all</span><span class="p">()</span></pre></div>
</div>
<p>And an entire “deferred group”, i.e. which uses the <tt class="docutils literal"><span class="pre">group</span></tt> keyword argument to <a class="reference internal" href="reference/orm/mapping.html#sqlalchemy.orm.deferred" title="sqlalchemy.orm.deferred"><tt class="xref py py-func docutils literal"><span class="pre">deferred()</span></tt></a>, can be undeferred using <tt class="xref py py-func docutils literal"><span class="pre">undefer_group()</span></tt>, sending in the group name:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">query</span> <span class="o">=</span> <span class="n">session</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="n">Book</span><span class="p">)</span>
<span class="n">query</span><span class="o">.</span><span class="n">options</span><span class="p">(</span><span class="n">undefer_group</span><span class="p">(</span><span class="s">'photos'</span><span class="p">))</span><span class="o">.</span><span class="n">all</span><span class="p">()</span></pre></div>
</div>
</div>
<div class="section" id="sql-expressions-as-mapped-attributes">
<h3>SQL Expressions as Mapped Attributes<a class="headerlink" href="#sql-expressions-as-mapped-attributes" title="Permalink to this headline">¶</a></h3>
<p>To add a SQL clause composed of local or external columns as a read-only, mapped column attribute, use the <a class="reference internal" href="reference/orm/mapping.html#sqlalchemy.orm.column_property" title="sqlalchemy.orm.column_property"><tt class="xref py py-func docutils literal"><span class="pre">column_property()</span></tt></a> function. Any scalar-returning <a class="reference internal" href="reference/sqlalchemy/expressions.html#sqlalchemy.sql.expression.ClauseElement" title="sqlalchemy.sql.expression.ClauseElement"><tt class="xref py py-class docutils literal"><span class="pre">ClauseElement</span></tt></a> may be used, as long as it has a <tt class="docutils literal"><span class="pre">name</span></tt> attribute; usually, you’ll want to call <tt class="docutils literal"><span class="pre">label()</span></tt> to give it a specific name:</p>
<div class="highlight-python"><div class="highlight"><pre><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">'fullname'</span><span class="p">:</span> <span class="n">column_property</span><span class="p">(</span>
<span class="p">(</span><span class="n">users_table</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">firstname</span> <span class="o">+</span> <span class="s">" "</span> <span class="o">+</span> <span class="n">users_table</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">lastname</span><span class="p">)</span><span class="o">.</span><span class="n">label</span><span class="p">(</span><span class="s">'fullname'</span><span class="p">)</span>
<span class="p">)</span>
<span class="p">})</span></pre></div>
</div>
<p>Correlated subqueries may be used as well:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><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">'address_count'</span><span class="p">:</span> <span class="n">column_property</span><span class="p">(</span>
<span class="n">select</span><span class="p">(</span>
<span class="p">[</span><span class="n">func</span><span class="o">.</span><span class="n">count</span><span class="p">(</span><span class="n">addresses_table</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">address_id</span><span class="p">)],</span>
<span class="n">addresses_table</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">user_id</span><span class="o">==</span><span class="n">users_table</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">user_id</span>
<span class="p">)</span><span class="o">.</span><span class="n">label</span><span class="p">(</span><span class="s">'address_count'</span><span class="p">)</span>
<span class="p">)</span>
<span class="p">})</span></pre></div>
</div>
</div>
<div class="section" id="changing-attribute-behavior">
<h3>Changing Attribute Behavior<a class="headerlink" href="#changing-attribute-behavior" title="Permalink to this headline">¶</a></h3>
<div class="section" id="simple-validators">
<h4>Simple Validators<a class="headerlink" href="#simple-validators" title="Permalink to this headline">¶</a></h4>
<p>A quick way to add a “validation” routine to an attribute is to use the <a class="reference internal" href="reference/orm/mapping.html#sqlalchemy.orm.validates" title="sqlalchemy.orm.validates"><tt class="xref py py-func docutils literal"><span class="pre">validates()</span></tt></a> decorator. This is a shortcut for using the <a class="reference internal" href="reference/orm/utilities.html#sqlalchemy.orm.util.Validator" title="sqlalchemy.orm.util.Validator"><tt class="xref py py-class docutils literal"><span class="pre">sqlalchemy.orm.util.Validator</span></tt></a> attribute extension with individual column or relationship based attributes. An attribute validator can raise an exception, halting the process of mutating the attribute’s value, or can change the given value into something different. Validators, like all attribute extensions, are only called by normal userland code; they are not issued when the ORM is populating the object.</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="n">addresses_table</span> <span class="o">=</span> <span class="n">Table</span><span class="p">(</span><span class="s">'addresses'</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">'email'</span><span class="p">,</span> <span class="n">String</span><span class="p">)</span>
<span class="p">)</span>
<span class="k">class</span> <span class="nc">EmailAddress</span><span class="p">(</span><span class="nb">object</span><span class="p">):</span>
<span class="nd">@validates</span><span class="p">(</span><span class="s">'email'</span><span class="p">)</span>
<span class="k">def</span> <span class="nf">validate_email</span><span class="p">(</span><span class="bp">self</span><span class="p">,</span> <span class="n">key</span><span class="p">,</span> <span class="n">address</span><span class="p">):</span>
<span class="k">assert</span> <span class="s">'@'</span> <span class="ow">in</span> <span class="n">address</span>
<span class="k">return</span> <span class="n">address</span>
<span class="n">mapper</span><span class="p">(</span><span class="n">EmailAddress</span><span class="p">,</span> <span class="n">addresses_table</span><span class="p">)</span></pre></div>
</div>
<p>Validators also receive collection events, when items are added to a collection:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><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="nd">@validates</span><span class="p">(</span><span class="s">'addresses'</span><span class="p">)</span>
<span class="k">def</span> <span class="nf">validate_address</span><span class="p">(</span><span class="bp">self</span><span class="p">,</span> <span class="n">key</span><span class="p">,</span> <span class="n">address</span><span class="p">):</span>
<span class="k">assert</span> <span class="s">'@'</span> <span class="ow">in</span> <span class="n">address</span><span class="o">.</span><span class="n">email</span>
<span class="k">return</span> <span class="n">address</span></pre></div>
</div>
</div>
<div class="section" id="using-descriptors">
<span id="synonyms"></span><h4>Using Descriptors<a class="headerlink" href="#using-descriptors" title="Permalink to this headline">¶</a></h4>
<p>A more comprehensive way to produce modified behavior for an attribute is to use descriptors. These are commonly used in Python using the <tt class="docutils literal"><span class="pre">property()</span></tt> function. The standard SQLAlchemy technique for descriptors is to create a plain descriptor, and to have it read/write from a mapped attribute with a different name. To have the descriptor named the same as a column, map the column under a different name, i.e.:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="k">class</span> <span class="nc">EmailAddress</span><span class="p">(</span><span class="nb">object</span><span class="p">):</span>
<span class="k">def</span> <span class="nf">_set_email</span><span class="p">(</span><span class="bp">self</span><span class="p">,</span> <span class="n">email</span><span class="p">):</span>
<span class="bp">self</span><span class="o">.</span><span class="n">_email</span> <span class="o">=</span> <span class="n">email</span>
<span class="k">def</span> <span class="nf">_get_email</span><span class="p">(</span><span class="bp">self</span><span class="p">):</span>
<span class="k">return</span> <span class="bp">self</span><span class="o">.</span><span class="n">_email</span>
<span class="n">email</span> <span class="o">=</span> <span class="nb">property</span><span class="p">(</span><span class="n">_get_email</span><span class="p">,</span> <span class="n">_set_email</span><span class="p">)</span>
<span class="n">mapper</span><span class="p">(</span><span class="n">MyAddress</span><span class="p">,</span> <span class="n">addresses_table</span><span class="p">,</span> <span class="n">properties</span><span class="o">=</span><span class="p">{</span>
<span class="s">'_email'</span><span class="p">:</span> <span class="n">addresses_table</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">email</span>
<span class="p">})</span></pre></div>
</div>
<p>However, the approach above is not complete. While our <tt class="docutils literal"><span class="pre">EmailAddress</span></tt> object will shuttle the value through the <tt class="docutils literal"><span class="pre">email</span></tt> descriptor and into the <tt class="docutils literal"><span class="pre">_email</span></tt> mapped attribute, the class level <tt class="docutils literal"><span class="pre">EmailAddress.email</span></tt> attribute does not have the usual expression semantics usable with <a class="reference internal" href="reference/orm/query.html#sqlalchemy.orm.query.Query" title="sqlalchemy.orm.query.Query"><tt class="xref py py-class docutils literal"><span class="pre">Query</span></tt></a>. To provide these, we instead use the <a class="reference internal" href="reference/orm/mapping.html#sqlalchemy.orm.synonym" title="sqlalchemy.orm.synonym"><tt class="xref py py-func docutils literal"><span class="pre">synonym()</span></tt></a> function as follows:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="n">mapper</span><span class="p">(</span><span class="n">EmailAddress</span><span class="p">,</span> <span class="n">addresses_table</span><span class="p">,</span> <span class="n">properties</span><span class="o">=</span><span class="p">{</span>
<span class="s">'email'</span><span class="p">:</span> <span class="n">synonym</span><span class="p">(</span><span class="s">'_email'</span><span class="p">,</span> <span class="n">map_column</span><span class="o">=</span><span class="bp">True</span><span class="p">)</span>
<span class="p">})</span></pre></div>
</div>
<p>The <tt class="docutils literal"><span class="pre">email</span></tt> attribute is now usable in the same way as any other mapped attribute, including filter expressions, get/set operations, etc.:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="n">address</span> <span class="o">=</span> <span class="n">session</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="n">EmailAddress</span><span class="p">)</span><span class="o">.</span><span class="n">filter</span><span class="p">(</span><span class="n">EmailAddress</span><span class="o">.</span><span class="n">email</span> <span class="o">==</span> <span class="s">'some address'</span><span class="p">)</span><span class="o">.</span><span class="n">one</span><span class="p">()</span>
<span class="n">address</span><span class="o">.</span><span class="n">email</span> <span class="o">=</span> <span class="s">'some other address'</span>
<span class="n">session</span><span class="o">.</span><span class="n">flush</span><span class="p">()</span>
<span class="n">q</span> <span class="o">=</span> <span class="n">session</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="n">EmailAddress</span><span class="p">)</span><span class="o">.</span><span class="n">filter_by</span><span class="p">(</span><span class="n">email</span><span class="o">=</span><span class="s">'some other address'</span><span class="p">)</span></pre></div>
</div>
<p>If the mapped class does not provide a property, the <a class="reference internal" href="reference/orm/mapping.html#sqlalchemy.orm.synonym" title="sqlalchemy.orm.synonym"><tt class="xref py py-func docutils literal"><span class="pre">synonym()</span></tt></a> construct will create a default getter/setter object automatically.</p>
</div>
<div class="section" id="custom-comparators">
<span id="id2"></span><h4>Custom Comparators<a class="headerlink" href="#custom-comparators" title="Permalink to this headline">¶</a></h4>
<p>The expressions returned by comparison operations, such as <tt class="docutils literal"><span class="pre">User.name=='ed'</span></tt>, can be customized. SQLAlchemy attributes generate these expressions using <a class="reference internal" href="reference/orm/interfaces.html#sqlalchemy.orm.interfaces.PropComparator" title="sqlalchemy.orm.interfaces.PropComparator"><tt class="xref py py-class docutils literal"><span class="pre">PropComparator</span></tt></a> objects, which provide common Python expression overrides including <tt class="docutils literal"><span class="pre">__eq__()</span></tt>, <tt class="docutils literal"><span class="pre">__ne__()</span></tt>, <tt class="docutils literal"><span class="pre">__lt__()</span></tt>, and so on. Any mapped attribute can be passed a user-defined class via the <tt class="docutils literal"><span class="pre">comparator_factory</span></tt> keyword argument, which subclasses the appropriate <a class="reference internal" href="reference/orm/interfaces.html#sqlalchemy.orm.interfaces.PropComparator" title="sqlalchemy.orm.interfaces.PropComparator"><tt class="xref py py-class docutils literal"><span class="pre">PropComparator</span></tt></a> in use, which can provide any or all of these methods:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="kn">from</span> <span class="nn">sqlalchemy.orm.properties</span> <span class="kn">import</span> <span class="n">ColumnProperty</span>
<span class="k">class</span> <span class="nc">MyComparator</span><span class="p">(</span><span class="n">ColumnProperty</span><span class="o">.</span><span class="n">Comparator</span><span class="p">):</span>
<span class="k">def</span> <span class="nf">__eq__</span><span class="p">(</span><span class="bp">self</span><span class="p">,</span> <span class="n">other</span><span class="p">):</span>
<span class="k">return</span> <span class="n">func</span><span class="o">.</span><span class="n">lower</span><span class="p">(</span><span class="bp">self</span><span class="o">.</span><span class="n">__clause_element__</span><span class="p">())</span> <span class="o">==</span> <span class="n">func</span><span class="o">.</span><span class="n">lower</span><span class="p">(</span><span class="n">other</span><span class="p">)</span>
<span class="n">mapper</span><span class="p">(</span><span class="n">EmailAddress</span><span class="p">,</span> <span class="n">addresses_table</span><span class="p">,</span> <span class="n">properties</span><span class="o">=</span><span class="p">{</span>
<span class="s">'email'</span><span class="p">:</span><span class="n">column_property</span><span class="p">(</span><span class="n">addresses_table</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">email</span><span class="p">,</span> <span class="n">comparator_factory</span><span class="o">=</span><span class="n">MyComparator</span><span class="p">)</span>
<span class="p">})</span></pre></div>
</div>
<p>Above, comparisons on the <tt class="docutils literal"><span class="pre">email</span></tt> column are wrapped in the SQL lower() function to produce case-insensitive matching:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="o">>>></span> <span class="nb">str</span><span class="p">(</span><span class="n">EmailAddress</span><span class="o">.</span><span class="n">email</span> <span class="o">==</span> <span class="s">'SomeAddress@foo.com'</span><span class="p">)</span>
<span class="n">lower</span><span class="p">(</span><span class="n">addresses</span><span class="o">.</span><span class="n">email</span><span class="p">)</span> <span class="o">=</span> <span class="n">lower</span><span class="p">(:</span><span class="n">lower_1</span><span class="p">)</span></pre></div>
</div>
<p>The <tt class="docutils literal"><span class="pre">__clause_element__()</span></tt> method is provided by the base <tt class="docutils literal"><span class="pre">Comparator</span></tt> class in use, and represents the SQL element which best matches what this attribute represents. For a column-based attribute, it’s the mapped column. For a composite attribute, it’s a <tt class="xref py py-class docutils literal"><span class="pre">ClauseList</span></tt> consisting of each column represented. For a relationship, it’s the table mapped by the local mapper (not the remote mapper). <tt class="docutils literal"><span class="pre">__clause_element__()</span></tt> should be honored by the custom comparator class in most cases since the resulting element will be applied any translations which are in effect, such as the correctly aliased member when using an <tt class="docutils literal"><span class="pre">aliased()</span></tt> construct or certain <a class="reference internal" href="reference/orm/query.html#sqlalchemy.orm.query.Query.with_polymorphic" title="sqlalchemy.orm.query.Query.with_polymorphic"><tt class="xref py py-func docutils literal"><span class="pre">with_polymorphic()</span></tt></a> scenarios.</p>
<p>There are four kinds of <tt class="docutils literal"><span class="pre">Comparator</span></tt> classes which may be subclassed, as according to the type of mapper property configured:</p>
<blockquote>
<ul class="simple">
<li><a class="reference internal" href="reference/orm/mapping.html#sqlalchemy.orm.column_property" title="sqlalchemy.orm.column_property"><tt class="xref py py-func docutils literal"><span class="pre">column_property()</span></tt></a> attribute - <tt class="docutils literal"><span class="pre">sqlalchemy.orm.properties.ColumnProperty.Comparator</span></tt></li>
<li><a class="reference internal" href="reference/orm/mapping.html#sqlalchemy.orm.composite" title="sqlalchemy.orm.composite"><tt class="xref py py-func docutils literal"><span class="pre">composite()</span></tt></a> attribute - <tt class="docutils literal"><span class="pre">sqlalchemy.orm.properties.CompositeProperty.Comparator</span></tt></li>
<li><a class="reference internal" href="reference/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> attribute - <tt class="docutils literal"><span class="pre">sqlalchemy.orm.properties.RelationshipProperty.Comparator</span></tt></li>
<li><a class="reference internal" href="reference/orm/mapping.html#sqlalchemy.orm.comparable_property" title="sqlalchemy.orm.comparable_property"><tt class="xref py py-func docutils literal"><span class="pre">comparable_property()</span></tt></a> attribute - <tt class="docutils literal"><span class="pre">sqlalchemy.orm.interfaces.PropComparator</span></tt></li>
</ul>
</blockquote>
<p>When using <a class="reference internal" href="reference/orm/mapping.html#sqlalchemy.orm.comparable_property" title="sqlalchemy.orm.comparable_property"><tt class="xref py py-func docutils literal"><span class="pre">comparable_property()</span></tt></a>, which is a mapper property that isn’t tied to any column or mapped table, the <tt class="docutils literal"><span class="pre">__clause_element__()</span></tt> method of <a class="reference internal" href="reference/orm/interfaces.html#sqlalchemy.orm.interfaces.PropComparator" title="sqlalchemy.orm.interfaces.PropComparator"><tt class="xref py py-class docutils literal"><span class="pre">PropComparator</span></tt></a> should also be implemented.</p>
<p>The <tt class="docutils literal"><span class="pre">comparator_factory</span></tt> argument is accepted by all <tt class="docutils literal"><span class="pre">MapperProperty</span></tt>-producing functions: <a class="reference internal" href="reference/orm/mapping.html#sqlalchemy.orm.column_property" title="sqlalchemy.orm.column_property"><tt class="xref py py-func docutils literal"><span class="pre">column_property()</span></tt></a>, <a class="reference internal" href="reference/orm/mapping.html#sqlalchemy.orm.composite" title="sqlalchemy.orm.composite"><tt class="xref py py-func docutils literal"><span class="pre">composite()</span></tt></a>, <a class="reference internal" href="reference/orm/mapping.html#sqlalchemy.orm.comparable_property" title="sqlalchemy.orm.comparable_property"><tt class="xref py py-func docutils literal"><span class="pre">comparable_property()</span></tt></a>, <a class="reference internal" href="reference/orm/mapping.html#sqlalchemy.orm.synonym" title="sqlalchemy.orm.synonym"><tt class="xref py py-func docutils literal"><span class="pre">synonym()</span></tt></a>, <a class="reference internal" href="reference/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>, <a class="reference internal" href="reference/orm/mapping.html#sqlalchemy.orm.backref" title="sqlalchemy.orm.backref"><tt class="xref py py-func docutils literal"><span class="pre">backref()</span></tt></a>, <a class="reference internal" href="reference/orm/mapping.html#sqlalchemy.orm.deferred" title="sqlalchemy.orm.deferred"><tt class="xref py py-func docutils literal"><span class="pre">deferred()</span></tt></a>, and <a class="reference internal" href="reference/orm/mapping.html#sqlalchemy.orm.dynamic_loader" title="sqlalchemy.orm.dynamic_loader"><tt class="xref py py-func docutils literal"><span class="pre">dynamic_loader()</span></tt></a>.</p>
</div>
</div>
<div class="section" id="composite-column-types">
<h3>Composite Column Types<a class="headerlink" href="#composite-column-types" title="Permalink to this headline">¶</a></h3>
<p>Sets of columns can be associated with a single datatype. The ORM treats the group of columns like a single column which accepts and returns objects using the custom datatype you provide. In this example, we’ll create a table <tt class="docutils literal"><span class="pre">vertices</span></tt> which stores a pair of x/y coordinates, and a custom datatype <tt class="docutils literal"><span class="pre">Point</span></tt> which is a composite type of an x and y column:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="n">vertices</span> <span class="o">=</span> <span class="n">Table</span><span class="p">(</span><span class="s">'vertices'</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">'x1'</span><span class="p">,</span> <span class="n">Integer</span><span class="p">),</span>
<span class="n">Column</span><span class="p">(</span><span class="s">'y1'</span><span class="p">,</span> <span class="n">Integer</span><span class="p">),</span>
<span class="n">Column</span><span class="p">(</span><span class="s">'x2'</span><span class="p">,</span> <span class="n">Integer</span><span class="p">),</span>
<span class="n">Column</span><span class="p">(</span><span class="s">'y2'</span><span class="p">,</span> <span class="n">Integer</span><span class="p">),</span>
<span class="p">)</span></pre></div>
</div>
<p>The requirements for the custom datatype class are that it have a constructor which accepts positional arguments corresponding to its column format, and also provides a method <tt class="docutils literal"><span class="pre">__composite_values__()</span></tt> which returns the state of the object as a list or tuple, in order of its column-based attributes. It also should supply adequate <tt class="docutils literal"><span class="pre">__eq__()</span></tt> and <tt class="docutils literal"><span class="pre">__ne__()</span></tt> methods which test the equality of two instances, and may optionally provide a <tt class="docutils literal"><span class="pre">__set_composite_values__</span></tt> method which is used to set internal state in some cases (typically when default values have been generated during a flush):</p>
<div class="highlight-python"><div class="highlight"><pre><span class="k">class</span> <span class="nc">Point</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">x</span><span class="p">,</span> <span class="n">y</span><span class="p">):</span>
<span class="bp">self</span><span class="o">.</span><span class="n">x</span> <span class="o">=</span> <span class="n">x</span>
<span class="bp">self</span><span class="o">.</span><span class="n">y</span> <span class="o">=</span> <span class="n">y</span>
<span class="k">def</span> <span class="nf">__composite_values__</span><span class="p">(</span><span class="bp">self</span><span class="p">):</span>
<span class="k">return</span> <span class="p">[</span><span class="bp">self</span><span class="o">.</span><span class="n">x</span><span class="p">,</span> <span class="bp">self</span><span class="o">.</span><span class="n">y</span><span class="p">]</span>
<span class="k">def</span> <span class="nf">__set_composite_values__</span><span class="p">(</span><span class="bp">self</span><span class="p">,</span> <span class="n">x</span><span class="p">,</span> <span class="n">y</span><span class="p">):</span>
<span class="bp">self</span><span class="o">.</span><span class="n">x</span> <span class="o">=</span> <span class="n">x</span>
<span class="bp">self</span><span class="o">.</span><span class="n">y</span> <span class="o">=</span> <span class="n">y</span>
<span class="k">def</span> <span class="nf">__eq__</span><span class="p">(</span><span class="bp">self</span><span class="p">,</span> <span class="n">other</span><span class="p">):</span>
<span class="k">return</span> <span class="n">other</span><span class="o">.</span><span class="n">x</span> <span class="o">==</span> <span class="bp">self</span><span class="o">.</span><span class="n">x</span> <span class="ow">and</span> <span class="n">other</span><span class="o">.</span><span class="n">y</span> <span class="o">==</span> <span class="bp">self</span><span class="o">.</span><span class="n">y</span>
<span class="k">def</span> <span class="nf">__ne__</span><span class="p">(</span><span class="bp">self</span><span class="p">,</span> <span class="n">other</span><span class="p">):</span>
<span class="k">return</span> <span class="ow">not</span> <span class="bp">self</span><span class="o">.</span><span class="n">__eq__</span><span class="p">(</span><span class="n">other</span><span class="p">)</span></pre></div>
</div>
<p>If <tt class="docutils literal"><span class="pre">__set_composite_values__()</span></tt> is not provided, the names of the mapped columns are taken as the names of attributes on the object, and <tt class="docutils literal"><span class="pre">setattr()</span></tt> is used to set data.</p>
<p>Setting up the mapping uses the <a class="reference internal" href="reference/orm/mapping.html#sqlalchemy.orm.composite" title="sqlalchemy.orm.composite"><tt class="xref py py-func docutils literal"><span class="pre">composite()</span></tt></a> function:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="k">class</span> <span class="nc">Vertex</span><span class="p">(</span><span class="nb">object</span><span class="p">):</span>
<span class="k">pass</span>
<span class="n">mapper</span><span class="p">(</span><span class="n">Vertex</span><span class="p">,</span> <span class="n">vertices</span><span class="p">,</span> <span class="n">properties</span><span class="o">=</span><span class="p">{</span>
<span class="s">'start'</span><span class="p">:</span> <span class="n">composite</span><span class="p">(</span><span class="n">Point</span><span class="p">,</span> <span class="n">vertices</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">x1</span><span class="p">,</span> <span class="n">vertices</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">y1</span><span class="p">),</span>
<span class="s">'end'</span><span class="p">:</span> <span class="n">composite</span><span class="p">(</span><span class="n">Point</span><span class="p">,</span> <span class="n">vertices</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">x2</span><span class="p">,</span> <span class="n">vertices</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">y2</span><span class="p">)</span>
<span class="p">})</span></pre></div>
</div>
<p>We can now use the <tt class="docutils literal"><span class="pre">Vertex</span></tt> instances as well as querying as though the <tt class="docutils literal"><span class="pre">start</span></tt> and <tt class="docutils literal"><span class="pre">end</span></tt> attributes are regular scalar attributes:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">session</span> <span class="o">=</span> <span class="n">Session</span><span class="p">()</span>
<span class="n">v</span> <span class="o">=</span> <span class="n">Vertex</span><span class="p">(</span><span class="n">Point</span><span class="p">(</span><span class="mi">3</span><span class="p">,</span> <span class="mi">4</span><span class="p">),</span> <span class="n">Point</span><span class="p">(</span><span class="mi">5</span><span class="p">,</span> <span class="mi">6</span><span class="p">))</span>
<span class="n">session</span><span class="o">.</span><span class="n">save</span><span class="p">(</span><span class="n">v</span><span class="p">)</span>
<span class="n">v2</span> <span class="o">=</span> <span class="n">session</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="n">Vertex</span><span class="p">)</span><span class="o">.</span><span class="n">filter</span><span class="p">(</span><span class="n">Vertex</span><span class="o">.</span><span class="n">start</span> <span class="o">==</span> <span class="n">Point</span><span class="p">(</span><span class="mi">3</span><span class="p">,</span> <span class="mi">4</span><span class="p">))</span></pre></div>
</div>
<p>The “equals” comparison operation by default produces an AND of all corresponding columns equated to one another. This can be changed using the <tt class="docutils literal"><span class="pre">comparator_factory</span></tt>, described in <a class="reference internal" href="#custom-comparators"><em>Custom Comparators</em></a>:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="kn">from</span> <span class="nn">sqlalchemy.orm.properties</span> <span class="kn">import</span> <span class="n">CompositeProperty</span>
<span class="kn">from</span> <span class="nn">sqlalchemy</span> <span class="kn">import</span> <span class="n">sql</span>
<span class="k">class</span> <span class="nc">PointComparator</span><span class="p">(</span><span class="n">CompositeProperty</span><span class="o">.</span><span class="n">Comparator</span><span class="p">):</span>
<span class="k">def</span> <span class="nf">__gt__</span><span class="p">(</span><span class="bp">self</span><span class="p">,</span> <span class="n">other</span><span class="p">):</span>
<span class="sd">"""define the 'greater than' operation"""</span>
<span class="k">return</span> <span class="n">sql</span><span class="o">.</span><span class="n">and_</span><span class="p">(</span><span class="o">*</span><span class="p">[</span><span class="n">a</span><span class="o">></span><span class="n">b</span> <span class="k">for</span> <span class="n">a</span><span class="p">,</span> <span class="n">b</span> <span class="ow">in</span>
<span class="nb">zip</span><span class="p">(</span><span class="bp">self</span><span class="o">.</span><span class="n">__clause_element__</span><span class="p">()</span><span class="o">.</span><span class="n">clauses</span><span class="p">,</span>
<span class="n">other</span><span class="o">.</span><span class="n">__composite_values__</span><span class="p">())])</span>
<span class="n">maper</span><span class="p">(</span><span class="n">Vertex</span><span class="p">,</span> <span class="n">vertices</span><span class="p">,</span> <span class="n">properties</span><span class="o">=</span><span class="p">{</span>
<span class="s">'start'</span><span class="p">:</span> <span class="n">composite</span><span class="p">(</span><span class="n">Point</span><span class="p">,</span> <span class="n">vertices</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">x1</span><span class="p">,</span> <span class="n">vertices</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">y1</span><span class="p">,</span> <span class="n">comparator_factory</span><span class="o">=</span><span class="n">PointComparator</span><span class="p">),</span>
<span class="s">'end'</span><span class="p">:</span> <span class="n">composite</span><span class="p">(</span><span class="n">Point</span><span class="p">,</span> <span class="n">vertices</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">x2</span><span class="p">,</span> <span class="n">vertices</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">y2</span><span class="p">,</span> <span class="n">comparator_factory</span><span class="o">=</span><span class="n">PointComparator</span><span class="p">)</span>
<span class="p">})</span></pre></div>
</div>
</div>
<div class="section" id="controlling-ordering">
<h3>Controlling Ordering<a class="headerlink" href="#controlling-ordering" title="Permalink to this headline">¶</a></h3>
<p>The ORM does not generate ordering for any query unless explicitly configured.</p>
<p>The “default” ordering for a collection, which applies to list-based collections, can be configured using the <tt class="docutils literal"><span class="pre">order_by</span></tt> keyword argument on <a class="reference internal" href="reference/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>:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">mapper</span><span class="p">(</span><span class="n">Address</span><span class="p">,</span> <span class="n">addresses_table</span><span class="p">)</span>
<span class="c"># order address objects by address id</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">'addresses'</span><span class="p">:</span> <span class="n">relationship</span><span class="p">(</span><span class="n">Address</span><span class="p">,</span> <span class="n">order_by</span><span class="o">=</span><span class="n">addresses_table</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">address_id</span><span class="p">)</span>
<span class="p">})</span></pre></div>
</div>
<p>Note that when using joined eager loaders with relationships, the tables used by the eager load’s join are anonymously aliased. You can only order by these columns if you specify it at the <a class="reference internal" href="reference/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> level. To control ordering at the query level based on a related table, you <tt class="docutils literal"><span class="pre">join()</span></tt> to that relationship, then order by it:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">session</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="n">User</span><span class="p">)</span><span class="o">.</span><span class="n">join</span><span class="p">(</span><span class="s">'addresses'</span><span class="p">)</span><span class="o">.</span><span class="n">order_by</span><span class="p">(</span><span class="n">Address</span><span class="o">.</span><span class="n">street</span><span class="p">)</span></pre></div>
</div>
<p>Ordering for rows loaded through <a class="reference internal" href="reference/orm/query.html#sqlalchemy.orm.query.Query" title="sqlalchemy.orm.query.Query"><tt class="xref py py-class docutils literal"><span class="pre">Query</span></tt></a> is usually specified using the <tt class="docutils literal"><span class="pre">order_by()</span></tt> generative method. There is also an option to set a default ordering for Queries which are against a single mapped entity and where there was no explicit <tt class="docutils literal"><span class="pre">order_by()</span></tt> stated, which is the <tt class="docutils literal"><span class="pre">order_by</span></tt> keyword argument to <tt class="docutils literal"><span class="pre">mapper()</span></tt>:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="c"># order by a column</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">order_by</span><span class="o">=</span><span class="n">users_table</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">user_id</span><span class="p">)</span>
<span class="c"># order by multiple items</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">order_by</span><span class="o">=</span><span class="p">[</span><span class="n">users_table</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">user_id</span><span class="p">,</span> <span class="n">users_table</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">user_name</span><span class="o">.</span><span class="n">desc</span><span class="p">()])</span></pre></div>
</div>
<p>Above, a <a class="reference internal" href="reference/orm/query.html#sqlalchemy.orm.query.Query" title="sqlalchemy.orm.query.Query"><tt class="xref py py-class docutils literal"><span class="pre">Query</span></tt></a> issued for the <tt class="docutils literal"><span class="pre">User</span></tt> class will use the value of the mapper’s <tt class="docutils literal"><span class="pre">order_by</span></tt> setting if the <a class="reference internal" href="reference/orm/query.html#sqlalchemy.orm.query.Query" title="sqlalchemy.orm.query.Query"><tt class="xref py py-class docutils literal"><span class="pre">Query</span></tt></a> itself has no ordering specified.</p>
</div>
<div class="section" id="mapping-class-inheritance-hierarchies">
<span id="datamapping-inheritance"></span><h3>Mapping Class Inheritance Hierarchies<a class="headerlink" href="#mapping-class-inheritance-hierarchies" title="Permalink to this headline">¶</a></h3>
<p>SQLAlchemy supports three forms of inheritance: <em>single table inheritance</em>, where several types of classes are stored in one table, <em>concrete table inheritance</em>, where each type of class is stored in its own table, and <em>joined table inheritance</em>, where the parent/child classes are stored in their own tables that are joined together in a select. Whereas support for single and joined table inheritance is strong, concrete table inheritance is a less common scenario with some particular problems so is not quite as flexible.</p>
<p>When mappers are configured in an inheritance relationship, SQLAlchemy has the ability to load elements “polymorphically”, meaning that a single query can return objects of multiple types.</p>
<p>For the following sections, assume this class relationship:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="k">class</span> <span class="nc">Employee</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">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="bp">self</span><span class="o">.</span><span class="n">__class__</span><span class="o">.</span><span class="n">__name__</span> <span class="o">+</span> <span class="s">" "</span> <span class="o">+</span> <span class="bp">self</span><span class="o">.</span><span class="n">name</span>
<span class="k">class</span> <span class="nc">Manager</span><span class="p">(</span><span class="n">Employee</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="n">manager_data</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="bp">self</span><span class="o">.</span><span class="n">manager_data</span> <span class="o">=</span> <span class="n">manager_data</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="bp">self</span><span class="o">.</span><span class="n">__class__</span><span class="o">.</span><span class="n">__name__</span> <span class="o">+</span> <span class="s">" "</span> <span class="o">+</span> <span class="bp">self</span><span class="o">.</span><span class="n">name</span> <span class="o">+</span> <span class="s">" "</span> <span class="o">+</span> <span class="bp">self</span><span class="o">.</span><span class="n">manager_data</span>
<span class="k">class</span> <span class="nc">Engineer</span><span class="p">(</span><span class="n">Employee</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="n">engineer_info</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="bp">self</span><span class="o">.</span><span class="n">engineer_info</span> <span class="o">=</span> <span class="n">engineer_info</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="bp">self</span><span class="o">.</span><span class="n">__class__</span><span class="o">.</span><span class="n">__name__</span> <span class="o">+</span> <span class="s">" "</span> <span class="o">+</span> <span class="bp">self</span><span class="o">.</span><span class="n">name</span> <span class="o">+</span> <span class="s">" "</span> <span class="o">+</span> <span class="bp">self</span><span class="o">.</span><span class="n">engineer_info</span></pre></div>
</div>
<div class="section" id="joined-table-inheritance">
<h4>Joined Table Inheritance<a class="headerlink" href="#joined-table-inheritance" title="Permalink to this headline">¶</a></h4>
<p>In joined table inheritance, each class along a particular classes’ list of parents is represented by a unique table. The total set of attributes for a particular instance is represented as a join along all tables in its inheritance path. Here, we first define a table to represent the <tt class="docutils literal"><span class="pre">Employee</span></tt> class. This table will contain a primary key column (or columns), and a column for each attribute that’s represented by <tt class="docutils literal"><span class="pre">Employee</span></tt>. In this case it’s just <tt class="docutils literal"><span class="pre">name</span></tt>:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">employees</span> <span class="o">=</span> <span class="n">Table</span><span class="p">(</span><span class="s">'employees'</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">'employee_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">50</span><span class="p">)),</span>
<span class="n">Column</span><span class="p">(</span><span class="s">'type'</span><span class="p">,</span> <span class="n">String</span><span class="p">(</span><span class="mi">30</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></pre></div>
</div>
<p>The table also has a column called <tt class="docutils literal"><span class="pre">type</span></tt>. It is strongly advised in both single- and joined- table inheritance scenarios that the root table contains a column whose sole purpose is that of the <strong>discriminator</strong>; it stores a value which indicates the type of object represented within the row. The column may be of any desired datatype. While there are some “tricks” to work around the requirement that there be a discriminator column, they are more complicated to configure when one wishes to load polymorphically.</p>
<p>Next we define individual tables for each of <tt class="docutils literal"><span class="pre">Engineer</span></tt> and <tt class="docutils literal"><span class="pre">Manager</span></tt>, which contain columns that represent the attributes unique to the subclass they represent. Each table also must contain a primary key column (or columns), and in most cases a foreign key reference to the parent table. It is standard practice that the same column is used for both of these roles, and that the column is also named the same as that of the parent table. However this is optional in SQLAlchemy; separate columns may be used for primary key and parent-relationship, the column may be named differently than that of the parent, and even a custom join condition can be specified between parent and child tables instead of using a foreign key:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">engineers</span> <span class="o">=</span> <span class="n">Table</span><span class="p">(</span><span class="s">'engineers'</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">'employee_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">'employees.employee_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">'engineer_info'</span><span class="p">,</span> <span class="n">String</span><span class="p">(</span><span class="mi">50</span><span class="p">)),</span>
<span class="p">)</span>
<span class="n">managers</span> <span class="o">=</span> <span class="n">Table</span><span class="p">(</span><span class="s">'managers'</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">'employee_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">'employees.employee_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">'manager_data'</span><span class="p">,</span> <span class="n">String</span><span class="p">(</span><span class="mi">50</span><span class="p">)),</span>
<span class="p">)</span></pre></div>
</div>
<p>One natural effect of the joined table inheritance configuration is that the identity of any mapped object can be determined entirely from the base table. This has obvious advantages, so SQLAlchemy always considers the primary key columns of a joined inheritance class to be those of the base table only, unless otherwise manually configured. In other words, the <tt class="docutils literal"><span class="pre">employee_id</span></tt> column of both the <tt class="docutils literal"><span class="pre">engineers</span></tt> and <tt class="docutils literal"><span class="pre">managers</span></tt> table is not used to locate the <tt class="docutils literal"><span class="pre">Engineer</span></tt> or <tt class="docutils literal"><span class="pre">Manager</span></tt> object itself - only the value in <tt class="docutils literal"><span class="pre">employees.employee_id</span></tt> is considered, and the primary key in this case is non-composite. <tt class="docutils literal"><span class="pre">engineers.employee_id</span></tt> and <tt class="docutils literal"><span class="pre">managers.employee_id</span></tt> are still of course critical to the proper operation of the pattern overall as they are used to locate the joined row, once the parent row has been determined, either through a distinct SELECT statement or all at once within a JOIN.</p>
<p>We then configure mappers as usual, except we use some additional arguments to indicate the inheritance relationship, the polymorphic discriminator column, and the <strong>polymorphic identity</strong> of each class; this is the value that will be stored in the polymorphic discriminator column.</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="n">mapper</span><span class="p">(</span><span class="n">Employee</span><span class="p">,</span> <span class="n">employees</span><span class="p">,</span> <span class="n">polymorphic_on</span><span class="o">=</span><span class="n">employees</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">type</span><span class="p">,</span> <span class="n">polymorphic_identity</span><span class="o">=</span><span class="s">'employee'</span><span class="p">)</span>
<span class="n">mapper</span><span class="p">(</span><span class="n">Engineer</span><span class="p">,</span> <span class="n">engineers</span><span class="p">,</span> <span class="n">inherits</span><span class="o">=</span><span class="n">Employee</span><span class="p">,</span> <span class="n">polymorphic_identity</span><span class="o">=</span><span class="s">'engineer'</span><span class="p">)</span>
<span class="n">mapper</span><span class="p">(</span><span class="n">Manager</span><span class="p">,</span> <span class="n">managers</span><span class="p">,</span> <span class="n">inherits</span><span class="o">=</span><span class="n">Employee</span><span class="p">,</span> <span class="n">polymorphic_identity</span><span class="o">=</span><span class="s">'manager'</span><span class="p">)</span></pre></div>
</div>
<p>And that’s it. Querying against <tt class="docutils literal"><span class="pre">Employee</span></tt> will return a combination of <tt class="docutils literal"><span class="pre">Employee</span></tt>, <tt class="docutils literal"><span class="pre">Engineer</span></tt> and <tt class="docutils literal"><span class="pre">Manager</span></tt> objects. Newly saved <tt class="docutils literal"><span class="pre">Engineer</span></tt>, <tt class="docutils literal"><span class="pre">Manager</span></tt>, and <tt class="docutils literal"><span class="pre">Employee</span></tt> objects will automatically populate the <tt class="docutils literal"><span class="pre">employees.type</span></tt> column with <tt class="docutils literal"><span class="pre">engineer</span></tt>, <tt class="docutils literal"><span class="pre">manager</span></tt>, or <tt class="docutils literal"><span class="pre">employee</span></tt>, as appropriate.</p>
<div class="section" id="controlling-which-tables-are-queried">
<h5>Controlling Which Tables are Queried<a class="headerlink" href="#controlling-which-tables-are-queried" title="Permalink to this headline">¶</a></h5>
<p>The <a class="reference internal" href="reference/orm/query.html#sqlalchemy.orm.query.Query.with_polymorphic" title="sqlalchemy.orm.query.Query.with_polymorphic"><tt class="xref py py-func docutils literal"><span class="pre">with_polymorphic()</span></tt></a> method of <a class="reference internal" href="reference/orm/query.html#sqlalchemy.orm.query.Query" title="sqlalchemy.orm.query.Query"><tt class="xref py py-class docutils literal"><span class="pre">Query</span></tt></a> affects the specific subclass tables which the Query selects from. Normally, a query such as this:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="n">session</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="n">Employee</span><span class="p">)</span><span class="o">.</span><span class="n">all</span><span class="p">()</span></pre></div>
</div>
<p>...selects only from the <tt class="docutils literal"><span class="pre">employees</span></tt> table. When loading fresh from the database, our joined-table setup will query from the parent table only, using SQL such as this:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><div class='show_sql'>
SELECT employees.employee_id AS employees_employee_id, employees.name AS employees_name, employees.type AS employees_type
FROM employees
[]</div></pre></div>
</div>
<p>As attributes are requested from those <tt class="docutils literal"><span class="pre">Employee</span></tt> objects which are represented in either the <tt class="docutils literal"><span class="pre">engineers</span></tt> or <tt class="docutils literal"><span class="pre">managers</span></tt> child tables, a second load is issued for the columns in that related row, if the data was not already loaded. So above, after accessing the objects you’d see further SQL issued along the lines of:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><div class='show_sql'>
SELECT managers.employee_id AS managers_employee_id, managers.manager_data AS managers_manager_data
FROM managers
WHERE ? = managers.employee_id
[5]
SELECT engineers.employee_id AS engineers_employee_id, engineers.engineer_info AS engineers_engineer_info
FROM engineers
WHERE ? = engineers.employee_id
[2]</div></pre></div>
</div>
<p>This behavior works well when issuing searches for small numbers of items, such as when using <tt class="docutils literal"><span class="pre">get()</span></tt>, since the full range of joined tables are not pulled in to the SQL statement unnecessarily. But when querying a larger span of rows which are known to be of many types, you may want to actively join to some or all of the joined tables. The <tt class="docutils literal"><span class="pre">with_polymorphic</span></tt> feature of <a class="reference internal" href="reference/orm/query.html#sqlalchemy.orm.query.Query" title="sqlalchemy.orm.query.Query"><tt class="xref py py-class docutils literal"><span class="pre">Query</span></tt></a> and <tt class="docutils literal"><span class="pre">mapper</span></tt> provides this.</p>
<p>Telling our query to polymorphically load <tt class="docutils literal"><span class="pre">Engineer</span></tt> and <tt class="docutils literal"><span class="pre">Manager</span></tt> objects:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="n">query</span> <span class="o">=</span> <span class="n">session</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="n">Employee</span><span class="p">)</span><span class="o">.</span><span class="n">with_polymorphic</span><span class="p">([</span><span class="n">Engineer</span><span class="p">,</span> <span class="n">Manager</span><span class="p">])</span></pre></div>
</div>
<p>produces a query which joins the <tt class="docutils literal"><span class="pre">employees</span></tt> table to both the <tt class="docutils literal"><span class="pre">engineers</span></tt> and <tt class="docutils literal"><span class="pre">managers</span></tt> tables like the following:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="n">query</span><span class="o">.</span><span class="n">all</span><span class="p">()</span>
<div class='show_sql'>
SELECT employees.employee_id AS employees_employee_id, engineers.employee_id AS engineers_employee_id, managers.employee_id AS managers_employee_id, employees.name AS employees_name, employees.type AS employees_type, engineers.engineer_info AS engineers_engineer_info, managers.manager_data AS managers_manager_data
FROM employees LEFT OUTER JOIN engineers ON employees.employee_id = engineers.employee_id LEFT OUTER JOIN managers ON employees.employee_id = managers.employee_id
[]</div></pre></div>
</div>
<p><a class="reference internal" href="reference/orm/query.html#sqlalchemy.orm.query.Query.with_polymorphic" title="sqlalchemy.orm.query.Query.with_polymorphic"><tt class="xref py py-func docutils literal"><span class="pre">with_polymorphic()</span></tt></a> accepts a single class or mapper, a list of classes/mappers, or the string <tt class="docutils literal"><span class="pre">'*'</span></tt> to indicate all subclasses:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="c"># join to the engineers table</span>
<span class="n">query</span><span class="o">.</span><span class="n">with_polymorphic</span><span class="p">(</span><span class="n">Engineer</span><span class="p">)</span>
<span class="c"># join to the engineers and managers tables</span>
<span class="n">query</span><span class="o">.</span><span class="n">with_polymorphic</span><span class="p">([</span><span class="n">Engineer</span><span class="p">,</span> <span class="n">Manager</span><span class="p">])</span>
<span class="c"># join to all subclass tables</span>
<span class="n">query</span><span class="o">.</span><span class="n">with_polymorphic</span><span class="p">(</span><span class="s">'*'</span><span class="p">)</span></pre></div>
</div>
<p>It also accepts a second argument <tt class="docutils literal"><span class="pre">selectable</span></tt> which replaces the automatic join creation and instead selects directly from the selectable given. This feature is normally used with “concrete” inheritance, described later, but can be used with any kind of inheritance setup in the case that specialized SQL should be used to load polymorphically:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="c"># custom selectable</span>
<span class="n">query</span><span class="o">.</span><span class="n">with_polymorphic</span><span class="p">([</span><span class="n">Engineer</span><span class="p">,</span> <span class="n">Manager</span><span class="p">],</span> <span class="n">employees</span><span class="o">.</span><span class="n">outerjoin</span><span class="p">(</span><span class="n">managers</span><span class="p">)</span><span class="o">.</span><span class="n">outerjoin</span><span class="p">(</span><span class="n">engineers</span><span class="p">))</span></pre></div>
</div>
<p><a class="reference internal" href="reference/orm/query.html#sqlalchemy.orm.query.Query.with_polymorphic" title="sqlalchemy.orm.query.Query.with_polymorphic"><tt class="xref py py-func docutils literal"><span class="pre">with_polymorphic()</span></tt></a> is also needed
when you wish to add filter criteria that are specific to one or more
subclasses; It makes the subclasses’ columns available to the WHERE clause:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="n">session</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="n">Employee</span><span class="p">)</span><span class="o">.</span><span class="n">with_polymorphic</span><span class="p">([</span><span class="n">Engineer</span><span class="p">,</span> <span class="n">Manager</span><span class="p">])</span><span class="o">.</span>\
<span class="nb">filter</span><span class="p">(</span><span class="n">or_</span><span class="p">(</span><span class="n">Engineer</span><span class="o">.</span><span class="n">engineer_info</span><span class="o">==</span><span class="s">'w'</span><span class="p">,</span> <span class="n">Manager</span><span class="o">.</span><span class="n">manager_data</span><span class="o">==</span><span class="s">'q'</span><span class="p">))</span></pre></div>
</div>
<p>Note that if you only need to load a single subtype, such as just the <tt class="docutils literal"><span class="pre">Engineer</span></tt> objects, <a class="reference internal" href="reference/orm/query.html#sqlalchemy.orm.query.Query.with_polymorphic" title="sqlalchemy.orm.query.Query.with_polymorphic"><tt class="xref py py-func docutils literal"><span class="pre">with_polymorphic()</span></tt></a> is not needed since you would query against the <tt class="docutils literal"><span class="pre">Engineer</span></tt> class directly.</p>
<p>The mapper also accepts <tt class="docutils literal"><span class="pre">with_polymorphic</span></tt> as a configurational argument so that the joined-style load will be issued automatically. This argument may be the string <tt class="docutils literal"><span class="pre">'*'</span></tt>, a list of classes, or a tuple consisting of either, followed by a selectable.</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="n">mapper</span><span class="p">(</span><span class="n">Employee</span><span class="p">,</span> <span class="n">employees</span><span class="p">,</span> <span class="n">polymorphic_on</span><span class="o">=</span><span class="n">employees</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">type</span><span class="p">,</span> \
<span class="n">polymorphic_identity</span><span class="o">=</span><span class="s">'employee'</span><span class="p">,</span> <span class="n">with_polymorphic</span><span class="o">=</span><span class="s">'*'</span><span class="p">)</span>
<span class="n">mapper</span><span class="p">(</span><span class="n">Engineer</span><span class="p">,</span> <span class="n">engineers</span><span class="p">,</span> <span class="n">inherits</span><span class="o">=</span><span class="n">Employee</span><span class="p">,</span> <span class="n">polymorphic_identity</span><span class="o">=</span><span class="s">'engineer'</span><span class="p">)</span>
<span class="n">mapper</span><span class="p">(</span><span class="n">Manager</span><span class="p">,</span> <span class="n">managers</span><span class="p">,</span> <span class="n">inherits</span><span class="o">=</span><span class="n">Employee</span><span class="p">,</span> <span class="n">polymorphic_identity</span><span class="o">=</span><span class="s">'manager'</span><span class="p">)</span></pre></div>
</div>
<p>The above mapping will produce a query similar to that of <tt class="docutils literal"><span class="pre">with_polymorphic('*')</span></tt> for every query of <tt class="docutils literal"><span class="pre">Employee</span></tt> objects.</p>
<p>Using <a class="reference internal" href="reference/orm/query.html#sqlalchemy.orm.query.Query.with_polymorphic" title="sqlalchemy.orm.query.Query.with_polymorphic"><tt class="xref py py-func docutils literal"><span class="pre">with_polymorphic()</span></tt></a> with <a class="reference internal" href="reference/orm/query.html#sqlalchemy.orm.query.Query" title="sqlalchemy.orm.query.Query"><tt class="xref py py-class docutils literal"><span class="pre">Query</span></tt></a> will override the mapper-level <tt class="docutils literal"><span class="pre">with_polymorphic</span></tt> setting.</p>
</div>
<div class="section" id="creating-joins-to-specific-subtypes">
<h5>Creating Joins to Specific Subtypes<a class="headerlink" href="#creating-joins-to-specific-subtypes" title="Permalink to this headline">¶</a></h5>
<p>The <a class="reference internal" href="reference/orm/interfaces.html#sqlalchemy.orm.interfaces.PropComparator.of_type" title="sqlalchemy.orm.interfaces.PropComparator.of_type"><tt class="xref py py-func docutils literal"><span class="pre">of_type()</span></tt></a> method is a helper which allows the construction of joins along <a class="reference internal" href="reference/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> paths while narrowing the criterion to specific subclasses. Suppose the <tt class="docutils literal"><span class="pre">employees</span></tt> table represents a collection of employees which are associated with a <tt class="docutils literal"><span class="pre">Company</span></tt> object. We’ll add a <tt class="docutils literal"><span class="pre">company_id</span></tt> column to the <tt class="docutils literal"><span class="pre">employees</span></tt> table and a new table <tt class="docutils literal"><span class="pre">companies</span></tt>:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="n">companies</span> <span class="o">=</span> <span class="n">Table</span><span class="p">(</span><span class="s">'companies'</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">'company_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">50</span><span class="p">))</span>
<span class="p">)</span>
<span class="n">employees</span> <span class="o">=</span> <span class="n">Table</span><span class="p">(</span><span class="s">'employees'</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">'employee_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">50</span><span class="p">)),</span>
<span class="n">Column</span><span class="p">(</span><span class="s">'type'</span><span class="p">,</span> <span class="n">String</span><span class="p">(</span><span class="mi">30</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="n">Column</span><span class="p">(</span><span class="s">'company_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">'companies.company_id'</span><span class="p">))</span>
<span class="p">)</span>
<span class="k">class</span> <span class="nc">Company</span><span class="p">(</span><span class="nb">object</span><span class="p">):</span>
<span class="k">pass</span>
<span class="n">mapper</span><span class="p">(</span><span class="n">Company</span><span class="p">,</span> <span class="n">companies</span><span class="p">,</span> <span class="n">properties</span><span class="o">=</span><span class="p">{</span>
<span class="s">'employees'</span><span class="p">:</span> <span class="n">relationship</span><span class="p">(</span><span class="n">Employee</span><span class="p">)</span>
<span class="p">})</span></pre></div>
</div>
<p>When querying from <tt class="docutils literal"><span class="pre">Company</span></tt> onto the <tt class="docutils literal"><span class="pre">Employee</span></tt> relationship, the <tt class="docutils literal"><span class="pre">join()</span></tt> method as well as the <tt class="docutils literal"><span class="pre">any()</span></tt> and <tt class="docutils literal"><span class="pre">has()</span></tt> operators will create a join from <tt class="docutils literal"><span class="pre">companies</span></tt> to <tt class="docutils literal"><span class="pre">employees</span></tt>, without including <tt class="docutils literal"><span class="pre">engineers</span></tt> or <tt class="docutils literal"><span class="pre">managers</span></tt> in the mix. If we wish to have criterion which is specifically against the <tt class="docutils literal"><span class="pre">Engineer</span></tt> class, we can tell those methods to join or subquery against the joined table representing the subclass using the <a class="reference internal" href="reference/orm/interfaces.html#sqlalchemy.orm.interfaces.PropComparator.of_type" title="sqlalchemy.orm.interfaces.PropComparator.of_type"><tt class="xref py py-func docutils literal"><span class="pre">of_type()</span></tt></a> operator:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="n">session</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="n">Company</span><span class="p">)</span><span class="o">.</span><span class="n">join</span><span class="p">(</span><span class="n">Company</span><span class="o">.</span><span class="n">employees</span><span class="o">.</span><span class="n">of_type</span><span class="p">(</span><span class="n">Engineer</span><span class="p">))</span><span class="o">.</span><span class="n">filter</span><span class="p">(</span><span class="n">Engineer</span><span class="o">.</span><span class="n">engineer_info</span><span class="o">==</span><span class="s">'someinfo'</span><span class="p">)</span></pre></div>
</div>
<p>A longhand version of this would involve spelling out the full target selectable within a 2-tuple:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="n">session</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="n">Company</span><span class="p">)</span><span class="o">.</span><span class="n">join</span><span class="p">((</span><span class="n">employees</span><span class="o">.</span><span class="n">join</span><span class="p">(</span><span class="n">engineers</span><span class="p">),</span> <span class="n">Company</span><span class="o">.</span><span class="n">employees</span><span class="p">))</span><span class="o">.</span><span class="n">filter</span><span class="p">(</span><span class="n">Engineer</span><span class="o">.</span><span class="n">engineer_info</span><span class="o">==</span><span class="s">'someinfo'</span><span class="p">)</span></pre></div>
</div>
<p>Currently, <a class="reference internal" href="reference/orm/interfaces.html#sqlalchemy.orm.interfaces.PropComparator.of_type" title="sqlalchemy.orm.interfaces.PropComparator.of_type"><tt class="xref py py-func docutils literal"><span class="pre">of_type()</span></tt></a> accepts a single class argument. It may be expanded later on to accept multiple classes. For now, to join to any group of subclasses, the longhand notation allows this flexibility:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="n">session</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="n">Company</span><span class="p">)</span><span class="o">.</span><span class="n">join</span><span class="p">((</span><span class="n">employees</span><span class="o">.</span><span class="n">outerjoin</span><span class="p">(</span><span class="n">engineers</span><span class="p">)</span><span class="o">.</span><span class="n">outerjoin</span><span class="p">(</span><span class="n">managers</span><span class="p">),</span> <span class="n">Company</span><span class="o">.</span><span class="n">employees</span><span class="p">))</span><span class="o">.</span>\
<span class="nb">filter</span><span class="p">(</span><span class="n">or_</span><span class="p">(</span><span class="n">Engineer</span><span class="o">.</span><span class="n">engineer_info</span><span class="o">==</span><span class="s">'someinfo'</span><span class="p">,</span> <span class="n">Manager</span><span class="o">.</span><span class="n">manager_data</span><span class="o">==</span><span class="s">'somedata'</span><span class="p">))</span></pre></div>
</div>
<p>The <tt class="docutils literal"><span class="pre">any()</span></tt> and <tt class="docutils literal"><span class="pre">has()</span></tt> operators also can be used with <a class="reference internal" href="reference/orm/interfaces.html#sqlalchemy.orm.interfaces.PropComparator.of_type" title="sqlalchemy.orm.interfaces.PropComparator.of_type"><tt class="xref py py-func docutils literal"><span class="pre">of_type()</span></tt></a> when the embedded criterion is in terms of a subclass:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="n">session</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="n">Company</span><span class="p">)</span><span class="o">.</span><span class="n">filter</span><span class="p">(</span><span class="n">Company</span><span class="o">.</span><span class="n">employees</span><span class="o">.</span><span class="n">of_type</span><span class="p">(</span><span class="n">Engineer</span><span class="p">)</span><span class="o">.</span><span class="n">any</span><span class="p">(</span><span class="n">Engineer</span><span class="o">.</span><span class="n">engineer_info</span><span class="o">==</span><span class="s">'someinfo'</span><span class="p">))</span><span class="o">.</span><span class="n">all</span><span class="p">()</span></pre></div>
</div>
<p>Note that the <tt class="docutils literal"><span class="pre">any()</span></tt> and <tt class="docutils literal"><span class="pre">has()</span></tt> are both shorthand for a correlated EXISTS query. To build one by hand looks like:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="n">session</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="n">Company</span><span class="p">)</span><span class="o">.</span><span class="n">filter</span><span class="p">(</span>
<span class="n">exists</span><span class="p">([</span><span class="mi">1</span><span class="p">],</span>
<span class="n">and_</span><span class="p">(</span><span class="n">Engineer</span><span class="o">.</span><span class="n">engineer_info</span><span class="o">==</span><span class="s">'someinfo'</span><span class="p">,</span> <span class="n">employees</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">company_id</span><span class="o">==</span><span class="n">companies</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">company_id</span><span class="p">),</span>
<span class="n">from_obj</span><span class="o">=</span><span class="n">employees</span><span class="o">.</span><span class="n">join</span><span class="p">(</span><span class="n">engineers</span><span class="p">)</span>
<span class="p">)</span>
<span class="p">)</span><span class="o">.</span><span class="n">all</span><span class="p">()</span></pre></div>
</div>
<p>The EXISTS subquery above selects from the join of <tt class="docutils literal"><span class="pre">employees</span></tt> to <tt class="docutils literal"><span class="pre">engineers</span></tt>, and also specifies criterion which correlates the EXISTS subselect back to the parent <tt class="docutils literal"><span class="pre">companies</span></tt> table.</p>
</div>
</div>
<div class="section" id="single-table-inheritance">
<h4>Single Table Inheritance<a class="headerlink" href="#single-table-inheritance" title="Permalink to this headline">¶</a></h4>
<p>Single table inheritance is where the attributes of the base class as well as all subclasses are represented within a single table. A column is present in the table for every attribute mapped to the base class and all subclasses; the columns which correspond to a single subclass are nullable. This configuration looks much like joined-table inheritance except there’s only one table. In this case, a <tt class="docutils literal"><span class="pre">type</span></tt> column is required, as there would be no other way to discriminate between classes. The table is specified in the base mapper only; for the inheriting classes, leave their <tt class="docutils literal"><span class="pre">table</span></tt> parameter blank:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="n">employees_table</span> <span class="o">=</span> <span class="n">Table</span><span class="p">(</span><span class="s">'employees'</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">'employee_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">50</span><span class="p">)),</span>
<span class="n">Column</span><span class="p">(</span><span class="s">'manager_data'</span><span class="p">,</span> <span class="n">String</span><span class="p">(</span><span class="mi">50</span><span class="p">)),</span>
<span class="n">Column</span><span class="p">(</span><span class="s">'engineer_info'</span><span class="p">,</span> <span class="n">String</span><span class="p">(</span><span class="mi">50</span><span class="p">)),</span>
<span class="n">Column</span><span class="p">(</span><span class="s">'type'</span><span class="p">,</span> <span class="n">String</span><span class="p">(</span><span class="mi">20</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">employee_mapper</span> <span class="o">=</span> <span class="n">mapper</span><span class="p">(</span><span class="n">Employee</span><span class="p">,</span> <span class="n">employees_table</span><span class="p">,</span> \
<span class="n">polymorphic_on</span><span class="o">=</span><span class="n">employees_table</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">type</span><span class="p">,</span> <span class="n">polymorphic_identity</span><span class="o">=</span><span class="s">'employee'</span><span class="p">)</span>
<span class="n">manager_mapper</span> <span class="o">=</span> <span class="n">mapper</span><span class="p">(</span><span class="n">Manager</span><span class="p">,</span> <span class="n">inherits</span><span class="o">=</span><span class="n">employee_mapper</span><span class="p">,</span> <span class="n">polymorphic_identity</span><span class="o">=</span><span class="s">'manager'</span><span class="p">)</span>
<span class="n">engineer_mapper</span> <span class="o">=</span> <span class="n">mapper</span><span class="p">(</span><span class="n">Engineer</span><span class="p">,</span> <span class="n">inherits</span><span class="o">=</span><span class="n">employee_mapper</span><span class="p">,</span> <span class="n">polymorphic_identity</span><span class="o">=</span><span class="s">'engineer'</span><span class="p">)</span></pre></div>
</div>
<p>Note that the mappers for the derived classes Manager and Engineer omit the specification of their associated table, as it is inherited from the employee_mapper. Omitting the table specification for derived mappers in single-table inheritance is required.</p>
</div>
<div class="section" id="concrete-table-inheritance">
<span id="concrete-inheritance"></span><h4>Concrete Table Inheritance<a class="headerlink" href="#concrete-table-inheritance" title="Permalink to this headline">¶</a></h4>
<p>This form of inheritance maps each class to a distinct table, as below:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="n">employees_table</span> <span class="o">=</span> <span class="n">Table</span><span class="p">(</span><span class="s">'employees'</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">'employee_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">50</span><span class="p">)),</span>
<span class="p">)</span>
<span class="n">managers_table</span> <span class="o">=</span> <span class="n">Table</span><span class="p">(</span><span class="s">'managers'</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">'employee_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">50</span><span class="p">)),</span>
<span class="n">Column</span><span class="p">(</span><span class="s">'manager_data'</span><span class="p">,</span> <span class="n">String</span><span class="p">(</span><span class="mi">50</span><span class="p">)),</span>
<span class="p">)</span>
<span class="n">engineers_table</span> <span class="o">=</span> <span class="n">Table</span><span class="p">(</span><span class="s">'engineers'</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">'employee_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">50</span><span class="p">)),</span>
<span class="n">Column</span><span class="p">(</span><span class="s">'engineer_info'</span><span class="p">,</span> <span class="n">String</span><span class="p">(</span><span class="mi">50</span><span class="p">)),</span>
<span class="p">)</span></pre></div>
</div>
<p>Notice in this case there is no <tt class="docutils literal"><span class="pre">type</span></tt> column. If polymorphic loading is not required, there’s no advantage to using <tt class="docutils literal"><span class="pre">inherits</span></tt> here; you just define a separate mapper for each class.</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="n">mapper</span><span class="p">(</span><span class="n">Employee</span><span class="p">,</span> <span class="n">employees_table</span><span class="p">)</span>
<span class="n">mapper</span><span class="p">(</span><span class="n">Manager</span><span class="p">,</span> <span class="n">managers_table</span><span class="p">)</span>
<span class="n">mapper</span><span class="p">(</span><span class="n">Engineer</span><span class="p">,</span> <span class="n">engineers_table</span><span class="p">)</span></pre></div>
</div>
<p>To load polymorphically, the <tt class="docutils literal"><span class="pre">with_polymorphic</span></tt> argument is required, along with a selectable indicating how rows should be loaded. In this case we must construct a UNION of all three tables. SQLAlchemy includes a helper function to create these called <a class="reference internal" href="reference/orm/utilities.html#sqlalchemy.orm.util.polymorphic_union" title="sqlalchemy.orm.util.polymorphic_union"><tt class="xref py py-func docutils literal"><span class="pre">polymorphic_union()</span></tt></a>, which will map all the different columns into a structure of selects with the same numbers and names of columns, and also generate a virtual <tt class="docutils literal"><span class="pre">type</span></tt> column for each subselect:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="n">pjoin</span> <span class="o">=</span> <span class="n">polymorphic_union</span><span class="p">({</span>
<span class="s">'employee'</span><span class="p">:</span> <span class="n">employees_table</span><span class="p">,</span>
<span class="s">'manager'</span><span class="p">:</span> <span class="n">managers_table</span><span class="p">,</span>
<span class="s">'engineer'</span><span class="p">:</span> <span class="n">engineers_table</span>
<span class="p">},</span> <span class="s">'type'</span><span class="p">,</span> <span class="s">'pjoin'</span><span class="p">)</span>
<span class="n">employee_mapper</span> <span class="o">=</span> <span class="n">mapper</span><span class="p">(</span><span class="n">Employee</span><span class="p">,</span> <span class="n">employees_table</span><span class="p">,</span> <span class="n">with_polymorphic</span><span class="o">=</span><span class="p">(</span><span class="s">'*'</span><span class="p">,</span> <span class="n">pjoin</span><span class="p">),</span> \
<span class="n">polymorphic_on</span><span class="o">=</span><span class="n">pjoin</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">type</span><span class="p">,</span> <span class="n">polymorphic_identity</span><span class="o">=</span><span class="s">'employee'</span><span class="p">)</span>
<span class="n">manager_mapper</span> <span class="o">=</span> <span class="n">mapper</span><span class="p">(</span><span class="n">Manager</span><span class="p">,</span> <span class="n">managers_table</span><span class="p">,</span> <span class="n">inherits</span><span class="o">=</span><span class="n">employee_mapper</span><span class="p">,</span> \
<span class="n">concrete</span><span class="o">=</span><span class="bp">True</span><span class="p">,</span> <span class="n">polymorphic_identity</span><span class="o">=</span><span class="s">'manager'</span><span class="p">)</span>
<span class="n">engineer_mapper</span> <span class="o">=</span> <span class="n">mapper</span><span class="p">(</span><span class="n">Engineer</span><span class="p">,</span> <span class="n">engineers_table</span><span class="p">,</span> <span class="n">inherits</span><span class="o">=</span><span class="n">employee_mapper</span><span class="p">,</span> \
<span class="n">concrete</span><span class="o">=</span><span class="bp">True</span><span class="p">,</span> <span class="n">polymorphic_identity</span><span class="o">=</span><span class="s">'engineer'</span><span class="p">)</span></pre></div>
</div>
<p>Upon select, the polymorphic union produces a query like this:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="n">session</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="n">Employee</span><span class="p">)</span><span class="o">.</span><span class="n">all</span><span class="p">()</span>
<div class='show_sql'>
SELECT pjoin.type AS pjoin_type, pjoin.manager_data AS pjoin_manager_data, pjoin.employee_id AS pjoin_employee_id,
pjoin.name AS pjoin_name, pjoin.engineer_info AS pjoin_engineer_info
FROM (
SELECT employees.employee_id AS employee_id, CAST(NULL AS VARCHAR(50)) AS manager_data, employees.name AS name,
CAST(NULL AS VARCHAR(50)) AS engineer_info, 'employee' AS type
FROM employees
UNION ALL
SELECT managers.employee_id AS employee_id, managers.manager_data AS manager_data, managers.name AS name,
CAST(NULL AS VARCHAR(50)) AS engineer_info, 'manager' AS type
FROM managers
UNION ALL
SELECT engineers.employee_id AS employee_id, CAST(NULL AS VARCHAR(50)) AS manager_data, engineers.name AS name,
engineers.engineer_info AS engineer_info, 'engineer' AS type
FROM engineers
) AS pjoin
[]</div></pre></div>
</div>
</div>
<div class="section" id="using-relationships-with-inheritance">
<h4>Using Relationships with Inheritance<a class="headerlink" href="#using-relationships-with-inheritance" title="Permalink to this headline">¶</a></h4>
<p>Both joined-table and single table inheritance scenarios produce mappings which are usable in <a class="reference internal" href="reference/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> functions; that is, it’s possible to map a parent object to a child object which is polymorphic. Similarly, inheriting mappers can have <a class="reference internal" href="reference/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> objects of their own at any level, which are inherited to each child class. The only requirement for relationships is that there is a table relationship between parent and child. An example is the following modification to the joined table inheritance example, which sets a bi-directional relationship between <tt class="docutils literal"><span class="pre">Employee</span></tt> and <tt class="docutils literal"><span class="pre">Company</span></tt>:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="n">employees_table</span> <span class="o">=</span> <span class="n">Table</span><span class="p">(</span><span class="s">'employees'</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">'employee_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">50</span><span class="p">)),</span>
<span class="n">Column</span><span class="p">(</span><span class="s">'company_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">'companies.company_id'</span><span class="p">))</span>
<span class="p">)</span>
<span class="n">companies</span> <span class="o">=</span> <span class="n">Table</span><span class="p">(</span><span class="s">'companies'</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">'company_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">50</span><span class="p">)))</span>
<span class="k">class</span> <span class="nc">Company</span><span class="p">(</span><span class="nb">object</span><span class="p">):</span>
<span class="k">pass</span>
<span class="n">mapper</span><span class="p">(</span><span class="n">Company</span><span class="p">,</span> <span class="n">companies</span><span class="p">,</span> <span class="n">properties</span><span class="o">=</span><span class="p">{</span>
<span class="s">'employees'</span><span class="p">:</span> <span class="n">relationship</span><span class="p">(</span><span class="n">Employee</span><span class="p">,</span> <span class="n">backref</span><span class="o">=</span><span class="s">'company'</span><span class="p">)</span>
<span class="p">})</span></pre></div>
</div>
<p>SQLAlchemy has a lot of experience in this area; the optimized “outer join” approach can be used freely for parent and child relationships, eager loads are fully useable, <a class="reference internal" href="reference/orm/query.html#sqlalchemy.orm.aliased" title="sqlalchemy.orm.aliased"><tt class="xref py py-func docutils literal"><span class="pre">aliased()</span></tt></a> objects and other techniques are fully supported as well.</p>
<p>In a concrete inheritance scenario, mapping relationships is more difficult since the distinct classes do not share a table. In this case, you <em>can</em> establish a relationship from parent to child if a join condition can be constructed from parent to child, if each child table contains a foreign key to the parent:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="n">companies</span> <span class="o">=</span> <span class="n">Table</span><span class="p">(</span><span class="s">'companies'</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">50</span><span class="p">)))</span>
<span class="n">employees_table</span> <span class="o">=</span> <span class="n">Table</span><span class="p">(</span><span class="s">'employees'</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">'employee_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">50</span><span class="p">)),</span>
<span class="n">Column</span><span class="p">(</span><span class="s">'company_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">'companies.id'</span><span class="p">))</span>
<span class="p">)</span>
<span class="n">managers_table</span> <span class="o">=</span> <span class="n">Table</span><span class="p">(</span><span class="s">'managers'</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">'employee_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">50</span><span class="p">)),</span>
<span class="n">Column</span><span class="p">(</span><span class="s">'manager_data'</span><span class="p">,</span> <span class="n">String</span><span class="p">(</span><span class="mi">50</span><span class="p">)),</span>
<span class="n">Column</span><span class="p">(</span><span class="s">'company_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">'companies.id'</span><span class="p">))</span>
<span class="p">)</span>
<span class="n">engineers_table</span> <span class="o">=</span> <span class="n">Table</span><span class="p">(</span><span class="s">'engineers'</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">'employee_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">50</span><span class="p">)),</span>
<span class="n">Column</span><span class="p">(</span><span class="s">'engineer_info'</span><span class="p">,</span> <span class="n">String</span><span class="p">(</span><span class="mi">50</span><span class="p">)),</span>
<span class="n">Column</span><span class="p">(</span><span class="s">'company_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">'companies.id'</span><span class="p">))</span>
<span class="p">)</span>
<span class="n">mapper</span><span class="p">(</span><span class="n">Employee</span><span class="p">,</span> <span class="n">employees_table</span><span class="p">,</span> <span class="n">with_polymorphic</span><span class="o">=</span><span class="p">(</span><span class="s">'*'</span><span class="p">,</span> <span class="n">pjoin</span><span class="p">),</span> <span class="n">polymorphic_on</span><span class="o">=</span><span class="n">pjoin</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">type</span><span class="p">,</span> <span class="n">polymorphic_identity</span><span class="o">=</span><span class="s">'employee'</span><span class="p">)</span>
<span class="n">mapper</span><span class="p">(</span><span class="n">Manager</span><span class="p">,</span> <span class="n">managers_table</span><span class="p">,</span> <span class="n">inherits</span><span class="o">=</span><span class="n">employee_mapper</span><span class="p">,</span> <span class="n">concrete</span><span class="o">=</span><span class="bp">True</span><span class="p">,</span> <span class="n">polymorphic_identity</span><span class="o">=</span><span class="s">'manager'</span><span class="p">)</span>
<span class="n">mapper</span><span class="p">(</span><span class="n">Engineer</span><span class="p">,</span> <span class="n">engineers_table</span><span class="p">,</span> <span class="n">inherits</span><span class="o">=</span><span class="n">employee_mapper</span><span class="p">,</span> <span class="n">concrete</span><span class="o">=</span><span class="bp">True</span><span class="p">,</span> <span class="n">polymorphic_identity</span><span class="o">=</span><span class="s">'engineer'</span><span class="p">)</span>
<span class="n">mapper</span><span class="p">(</span><span class="n">Company</span><span class="p">,</span> <span class="n">companies</span><span class="p">,</span> <span class="n">properties</span><span class="o">=</span><span class="p">{</span>
<span class="s">'employees'</span><span class="p">:</span> <span class="n">relationship</span><span class="p">(</span><span class="n">Employee</span><span class="p">)</span>
<span class="p">})</span></pre></div>
</div>
<p>The big limitation with concrete table inheritance is that <a class="reference internal" href="reference/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> objects placed on each concrete mapper do <strong>not</strong> propagate to child mappers. If you want to have the same <a class="reference internal" href="reference/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> objects set up on all concrete mappers, they must be configured manually on each. To configure back references in such a configuration the <tt class="docutils literal"><span class="pre">back_populates</span></tt> keyword may be used instead of <tt class="docutils literal"><span class="pre">backref</span></tt>, such as below where both <tt class="docutils literal"><span class="pre">A(object)</span></tt> and <tt class="docutils literal"><span class="pre">B(A)</span></tt> bidirectionally reference <tt class="docutils literal"><span class="pre">C</span></tt>:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">ajoin</span> <span class="o">=</span> <span class="n">polymorphic_union</span><span class="p">({</span>
<span class="s">'a'</span><span class="p">:</span><span class="n">a_table</span><span class="p">,</span>
<span class="s">'b'</span><span class="p">:</span><span class="n">b_table</span>
<span class="p">},</span> <span class="s">'type'</span><span class="p">,</span> <span class="s">'ajoin'</span><span class="p">)</span>
<span class="n">mapper</span><span class="p">(</span><span class="n">A</span><span class="p">,</span> <span class="n">a_table</span><span class="p">,</span> <span class="n">with_polymorphic</span><span class="o">=</span><span class="p">(</span><span class="s">'*'</span><span class="p">,</span> <span class="n">ajoin</span><span class="p">),</span>
<span class="n">polymorphic_on</span><span class="o">=</span><span class="n">ajoin</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">type</span><span class="p">,</span> <span class="n">polymorphic_identity</span><span class="o">=</span><span class="s">'a'</span><span class="p">,</span>
<span class="n">properties</span><span class="o">=</span><span class="p">{</span>
<span class="s">'some_c'</span><span class="p">:</span><span class="n">relationship</span><span class="p">(</span><span class="n">C</span><span class="p">,</span> <span class="n">back_populates</span><span class="o">=</span><span class="s">'many_a'</span><span class="p">)</span>
<span class="p">})</span>
<span class="n">mapper</span><span class="p">(</span><span class="n">B</span><span class="p">,</span> <span class="n">b_table</span><span class="p">,</span><span class="n">inherits</span><span class="o">=</span><span class="n">A</span><span class="p">,</span> <span class="n">concrete</span><span class="o">=</span><span class="bp">True</span><span class="p">,</span>
<span class="n">polymorphic_identity</span><span class="o">=</span><span class="s">'b'</span><span class="p">,</span>
<span class="n">properties</span><span class="o">=</span><span class="p">{</span>
<span class="s">'some_c'</span><span class="p">:</span><span class="n">relationship</span><span class="p">(</span><span class="n">C</span><span class="p">,</span> <span class="n">back_populates</span><span class="o">=</span><span class="s">'many_a'</span><span class="p">)</span>
<span class="p">})</span>
<span class="n">mapper</span><span class="p">(</span><span class="n">C</span><span class="p">,</span> <span class="n">c_table</span><span class="p">,</span> <span class="n">properties</span><span class="o">=</span><span class="p">{</span>
<span class="s">'many_a'</span><span class="p">:</span><span class="n">relationship</span><span class="p">(</span><span class="n">A</span><span class="p">,</span> <span class="n">collection_class</span><span class="o">=</span><span class="nb">set</span><span class="p">,</span> <span class="n">back_populates</span><span class="o">=</span><span class="s">'some_c'</span><span class="p">),</span>
<span class="p">})</span></pre></div>
</div>
</div>
</div>
<div class="section" id="mapping-a-class-against-multiple-tables">
<h3>Mapping a Class against Multiple Tables<a class="headerlink" href="#mapping-a-class-against-multiple-tables" title="Permalink to this headline">¶</a></h3>
<p>Mappers can be constructed against arbitrary relational units (called <tt class="docutils literal"><span class="pre">Selectables</span></tt>) as well as plain <tt class="docutils literal"><span class="pre">Tables</span></tt>. For example, The <tt class="docutils literal"><span class="pre">join</span></tt> keyword from the SQL package creates a neat selectable unit comprised of multiple tables, complete with its own composite primary key, which can be passed in to a mapper as the table.</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="c"># a class</span>
<span class="k">class</span> <span class="nc">AddressUser</span><span class="p">(</span><span class="nb">object</span><span class="p">):</span>
<span class="k">pass</span>
<span class="c"># define a Join</span>
<span class="n">j</span> <span class="o">=</span> <span class="n">join</span><span class="p">(</span><span class="n">users_table</span><span class="p">,</span> <span class="n">addresses_table</span><span class="p">)</span>
<span class="c"># map to it - the identity of an AddressUser object will be</span>
<span class="c"># based on (user_id, address_id) since those are the primary keys involved</span>
<span class="n">mapper</span><span class="p">(</span><span class="n">AddressUser</span><span class="p">,</span> <span class="n">j</span><span class="p">,</span> <span class="n">properties</span><span class="o">=</span><span class="p">{</span>
<span class="s">'user_id'</span><span class="p">:</span> <span class="p">[</span><span class="n">users_table</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">user_id</span><span class="p">,</span> <span class="n">addresses_table</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">user_id</span><span class="p">]</span>
<span class="p">})</span></pre></div>
</div>
<p>A second example:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="c"># many-to-many join on an association table</span>
<span class="n">j</span> <span class="o">=</span> <span class="n">join</span><span class="p">(</span><span class="n">users_table</span><span class="p">,</span> <span class="n">userkeywords</span><span class="p">,</span>
<span class="n">users_table</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">user_id</span><span class="o">==</span><span class="n">userkeywords</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">user_id</span><span class="p">)</span><span class="o">.</span><span class="n">join</span><span class="p">(</span><span class="n">keywords</span><span class="p">,</span>
<span class="n">userkeywords</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">keyword_id</span><span class="o">==</span><span class="n">keywords</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">keyword_id</span><span class="p">)</span>
<span class="c"># a class</span>
<span class="k">class</span> <span class="nc">KeywordUser</span><span class="p">(</span><span class="nb">object</span><span class="p">):</span>
<span class="k">pass</span>
<span class="c"># map to it - the identity of a KeywordUser object will be</span>
<span class="c"># (user_id, keyword_id) since those are the primary keys involved</span>
<span class="n">mapper</span><span class="p">(</span><span class="n">KeywordUser</span><span class="p">,</span> <span class="n">j</span><span class="p">,</span> <span class="n">properties</span><span class="o">=</span><span class="p">{</span>
<span class="s">'user_id'</span><span class="p">:</span> <span class="p">[</span><span class="n">users_table</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">user_id</span><span class="p">,</span> <span class="n">userkeywords</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">user_id</span><span class="p">],</span>
<span class="s">'keyword_id'</span><span class="p">:</span> <span class="p">[</span><span class="n">userkeywords</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">keyword_id</span><span class="p">,</span> <span class="n">keywords</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">keyword_id</span><span class="p">]</span>
<span class="p">})</span></pre></div>
</div>
<p>In both examples above, “composite” columns were added as properties to the mappers; these are aggregations of multiple columns into one mapper property, which instructs the mapper to keep both of those columns set at the same value.</p>
</div>
<div class="section" id="mapping-a-class-against-arbitrary-selects">
<h3>Mapping a Class against Arbitrary Selects<a class="headerlink" href="#mapping-a-class-against-arbitrary-selects" title="Permalink to this headline">¶</a></h3>
<p>Similar to mapping against a join, a plain select() object can be used with a mapper as well. Below, an example select which contains two aggregate functions and a group_by is mapped to a class:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="n">s</span> <span class="o">=</span> <span class="n">select</span><span class="p">([</span><span class="n">customers</span><span class="p">,</span>
<span class="n">func</span><span class="o">.</span><span class="n">count</span><span class="p">(</span><span class="n">orders</span><span class="p">)</span><span class="o">.</span><span class="n">label</span><span class="p">(</span><span class="s">'order_count'</span><span class="p">),</span>
<span class="n">func</span><span class="o">.</span><span class="n">max</span><span class="p">(</span><span class="n">orders</span><span class="o">.</span><span class="n">price</span><span class="p">)</span><span class="o">.</span><span class="n">label</span><span class="p">(</span><span class="s">'highest_order'</span><span class="p">)],</span>
<span class="n">customers</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">customer_id</span><span class="o">==</span><span class="n">orders</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">customer_id</span><span class="p">,</span>
<span class="n">group_by</span><span class="o">=</span><span class="p">[</span><span class="n">c</span> <span class="k">for</span> <span class="n">c</span> <span class="ow">in</span> <span class="n">customers</span><span class="o">.</span><span class="n">c</span><span class="p">]</span>
<span class="p">)</span><span class="o">.</span><span class="n">alias</span><span class="p">(</span><span class="s">'somealias'</span><span class="p">)</span>
<span class="k">class</span> <span class="nc">Customer</span><span class="p">(</span><span class="nb">object</span><span class="p">):</span>
<span class="k">pass</span>
<span class="n">mapper</span><span class="p">(</span><span class="n">Customer</span><span class="p">,</span> <span class="n">s</span><span class="p">)</span></pre></div>
</div>
<p>Above, the “customers” table is joined against the “orders” table to produce a full row for each customer row, the total count of related rows in the “orders” table, and the highest price in the “orders” table, grouped against the full set of columns in the “customers” table. That query is then mapped against the Customer class. New instances of Customer will contain attributes for each column in the “customers” table as well as an “order_count” and “highest_order” attribute. Updates to the Customer object will only be reflected in the “customers” table and not the “orders” table. This is because the primary key columns of the “orders” table are not represented in this mapper and therefore the table is not affected by save or delete operations.</p>
</div>
<div class="section" id="multiple-mappers-for-one-class">
<h3>Multiple Mappers for One Class<a class="headerlink" href="#multiple-mappers-for-one-class" title="Permalink to this headline">¶</a></h3>
<p>The first mapper created for a certain class is known as that class’s “primary mapper.” Other mappers can be created as well on the “load side” - these are called <strong>secondary mappers</strong>. This is a mapper that must be constructed with the keyword argument <tt class="docutils literal"><span class="pre">non_primary=True</span></tt>, and represents a load-only mapper. Objects that are loaded with a secondary mapper will have their save operation processed by the primary mapper. It is also invalid to add new <a class="reference internal" href="reference/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> objects to a non-primary mapper. To use this mapper with the Session, specify it to the <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.session.Session.query" title="sqlalchemy.orm.session.Session.query"><tt class="xref py py-class docutils literal"><span class="pre">query</span></tt></a> method:</p>
<p>example:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="c"># primary mapper</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="c"># make a secondary mapper to load User against a join</span>
<span class="n">othermapper</span> <span class="o">=</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="o">.</span><span class="n">join</span><span class="p">(</span><span class="n">someothertable</span><span class="p">),</span> <span class="n">non_primary</span><span class="o">=</span><span class="bp">True</span><span class="p">)</span>
<span class="c"># select</span>
<span class="n">result</span> <span class="o">=</span> <span class="n">session</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="n">othermapper</span><span class="p">)</span><span class="o">.</span><span class="n">select</span><span class="p">()</span></pre></div>
</div>
<p>The “non primary mapper” is a rarely needed feature of SQLAlchemy; in most cases, the <a class="reference internal" href="reference/orm/query.html#sqlalchemy.orm.query.Query" title="sqlalchemy.orm.query.Query"><tt class="xref py py-class docutils literal"><span class="pre">Query</span></tt></a> object can produce any kind of query that’s desired. It’s recommended that a straight <a class="reference internal" href="reference/orm/query.html#sqlalchemy.orm.query.Query" title="sqlalchemy.orm.query.Query"><tt class="xref py py-class docutils literal"><span class="pre">Query</span></tt></a> be used in place of a non-primary mapper unless the mapper approach is absolutely needed. Current use cases for the “non primary mapper” are when you want to map the class to a particular select statement or view to which additional query criterion can be added, and for when the particular mapped select statement or view is to be placed in a <a class="reference internal" href="reference/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> of a parent mapper.</p>
</div>
<div class="section" id="multiple-persistence-mappers-for-one-class">
<h3>Multiple “Persistence” Mappers for One Class<a class="headerlink" href="#multiple-persistence-mappers-for-one-class" title="Permalink to this headline">¶</a></h3>
<p>The non_primary mapper defines alternate mappers for the purposes of loading objects. What if we want the same class to be <em>persisted</em> differently, such as to different tables ? SQLAlchemy
refers to this as the “entity name” pattern, and in Python one can use a recipe which creates
anonymous subclasses which are distinctly mapped. See the recipe at <a class="reference external" href="http://www.sqlalchemy.org/trac/wiki/UsageRecipes/EntityName">Entity Name</a>.</p>
</div>
<div class="section" id="constructors-and-object-initialization">
<h3>Constructors and Object Initialization<a class="headerlink" href="#constructors-and-object-initialization" title="Permalink to this headline">¶</a></h3>
<p>Mapping imposes no restrictions or requirements on the constructor (<tt class="docutils literal"><span class="pre">__init__</span></tt>) method for the class. You are free to require any arguments for the function
that you wish, assign attributes to the instance that are unknown to the ORM, and generally do anything else you would normally do when writing a constructor
for a Python class.</p>
<p>The SQLAlchemy ORM does not call <tt class="docutils literal"><span class="pre">__init__</span></tt> when recreating objects from database rows. The ORM’s process is somewhat akin to the Python standard library’s
<tt class="docutils literal"><span class="pre">pickle</span></tt> module, invoking the low level <tt class="docutils literal"><span class="pre">__new__</span></tt> method and then quietly restoring attributes directly on the instance rather than calling <tt class="docutils literal"><span class="pre">__init__</span></tt>.</p>
<p>If you need to do some setup on database-loaded instances before they’re ready to use, you can use the <tt class="docutils literal"><span class="pre">@reconstructor</span></tt> decorator to tag a method as the ORM
counterpart to <tt class="docutils literal"><span class="pre">__init__</span></tt>. SQLAlchemy will call this method with no arguments every time it loads or reconstructs one of your instances. This is useful for
recreating transient properties that are normally assigned in your <tt class="docutils literal"><span class="pre">__init__</span></tt>:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="kn">from</span> <span class="nn">sqlalchemy</span> <span class="kn">import</span> <span class="n">orm</span>
<span class="k">class</span> <span class="nc">MyMappedClass</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">data</span><span class="p">):</span>
<span class="bp">self</span><span class="o">.</span><span class="n">data</span> <span class="o">=</span> <span class="n">data</span>
<span class="c"># we need stuff on all instances, but not in the database.</span>
<span class="bp">self</span><span class="o">.</span><span class="n">stuff</span> <span class="o">=</span> <span class="p">[]</span>
<span class="nd">@orm.reconstructor</span>
<span class="k">def</span> <span class="nf">init_on_load</span><span class="p">(</span><span class="bp">self</span><span class="p">):</span>
<span class="bp">self</span><span class="o">.</span><span class="n">stuff</span> <span class="o">=</span> <span class="p">[]</span></pre></div>
</div>
<p>When <tt class="docutils literal"><span class="pre">obj</span> <span class="pre">=</span> <span class="pre">MyMappedClass()</span></tt> is executed, Python calls the <tt class="docutils literal"><span class="pre">__init__</span></tt> method as normal and the <tt class="docutils literal"><span class="pre">data</span></tt> argument is required. When instances are loaded
during a <a class="reference internal" href="reference/orm/query.html#sqlalchemy.orm.query.Query" title="sqlalchemy.orm.query.Query"><tt class="xref py py-class docutils literal"><span class="pre">Query</span></tt></a> operation as in <tt class="docutils literal"><span class="pre">query(MyMappedClass).one()</span></tt>, <tt class="docutils literal"><span class="pre">init_on_load</span></tt> is called instead.</p>
<p>Any method may be tagged as the <a class="reference internal" href="reference/orm/mapping.html#sqlalchemy.orm.reconstructor" title="sqlalchemy.orm.reconstructor"><tt class="xref py py-func docutils literal"><span class="pre">reconstructor()</span></tt></a>, even the <tt class="docutils literal"><span class="pre">__init__</span></tt> method. SQLAlchemy will call the reconstructor method with no arguments. Scalar
(non-collection) database-mapped attributes of the instance will be available for use within the function. Eagerly-loaded collections are generally not yet
available and will usually only contain the first element. ORM state changes made to objects at this stage will not be recorded for the next flush()
operation, so the activity within a reconstructor should be conservative.</p>
<p>While the ORM does not call your <tt class="docutils literal"><span class="pre">__init__</span></tt> method, it will modify the class’s <tt class="docutils literal"><span class="pre">__init__</span></tt> slightly. The method is lightly wrapped to act as a trigger for
the ORM, allowing mappers to be compiled automatically and will fire a <a class="reference internal" href="reference/orm/interfaces.html#sqlalchemy.orm.interfaces.MapperExtension.init_instance" title="sqlalchemy.orm.interfaces.MapperExtension.init_instance"><tt class="xref py py-func docutils literal"><span class="pre">init_instance()</span></tt></a> event that <a class="reference internal" href="reference/orm/interfaces.html#sqlalchemy.orm.interfaces.MapperExtension" title="sqlalchemy.orm.interfaces.MapperExtension"><tt class="xref py py-class docutils literal"><span class="pre">MapperExtension</span></tt></a> objects may listen for.
<a class="reference internal" href="reference/orm/interfaces.html#sqlalchemy.orm.interfaces.MapperExtension" title="sqlalchemy.orm.interfaces.MapperExtension"><tt class="xref py py-class docutils literal"><span class="pre">MapperExtension</span></tt></a> objects can also listen for a <tt class="docutils literal"><span class="pre">reconstruct_instance</span></tt> event, analogous to the <a class="reference internal" href="reference/orm/mapping.html#sqlalchemy.orm.reconstructor" title="sqlalchemy.orm.reconstructor"><tt class="xref py py-func docutils literal"><span class="pre">reconstructor()</span></tt></a> decorator above.</p>
</div>
<div class="section" id="extending-mapper">
<span id="id3"></span><h3>Extending Mapper<a class="headerlink" href="#extending-mapper" title="Permalink to this headline">¶</a></h3>
<p>Mappers can have functionality augmented or replaced at many points in its execution via the usage of the MapperExtension class. This class is just a series of “hooks” where various functionality takes place. An application can make its own MapperExtension objects, overriding only the methods it needs. Methods that are not overridden return the special value <tt class="docutils literal"><span class="pre">sqlalchemy.orm.EXT_CONTINUE</span></tt> to allow processing to continue to the next MapperExtension or simply proceed normally if there are no more extensions.</p>
<p>API documentation for MapperExtension: <a class="reference internal" href="reference/orm/interfaces.html#sqlalchemy.orm.interfaces.MapperExtension" title="sqlalchemy.orm.interfaces.MapperExtension"><tt class="xref py py-class docutils literal"><span class="pre">sqlalchemy.orm.interfaces.MapperExtension</span></tt></a></p>
<p>To use MapperExtension, make your own subclass of it and just send it off to a mapper:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">m</span> <span class="o">=</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">extension</span><span class="o">=</span><span class="n">MyExtension</span><span class="p">())</span></pre></div>
</div>
<p>Multiple extensions will be chained together and processed in order; they are specified as a list:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">m</span> <span class="o">=</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">extension</span><span class="o">=</span><span class="p">[</span><span class="n">ext1</span><span class="p">,</span> <span class="n">ext2</span><span class="p">,</span> <span class="n">ext3</span><span class="p">])</span></pre></div>
</div>
</div>
</div>
<div class="section" id="relationship-configuration">
<span id="advdatamapping-relationship"></span><h2>Relationship Configuration<a class="headerlink" href="#relationship-configuration" title="Permalink to this headline">¶</a></h2>
<div class="section" id="basic-relational-patterns">
<h3>Basic Relational Patterns<a class="headerlink" href="#basic-relational-patterns" title="Permalink to this headline">¶</a></h3>
<p>A quick walkthrough of the basic relational patterns. Note that the <a class="reference internal" href="reference/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> function is known as <a class="reference internal" href="reference/orm/mapping.html#sqlalchemy.orm.relation" title="sqlalchemy.orm.relation"><tt class="xref py py-func docutils literal"><span class="pre">relation()</span></tt></a>
in all SQLAlchemy versions prior to 0.6beta2, including the 0.5 and 0.4 series.</p>
<div class="section" id="one-to-many">
<h4>One To Many<a class="headerlink" href="#one-to-many" title="Permalink to this headline">¶</a></h4>
<p>A one to many relationship places a foreign key in the child table referencing the parent. SQLAlchemy creates the relationship as a collection on the parent object containing instances of the child object.</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="n">parent_table</span> <span class="o">=</span> <span class="n">Table</span><span class="p">(</span><span class="s">'parent'</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">child_table</span> <span class="o">=</span> <span class="n">Table</span><span class="p">(</span><span class="s">'child'</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">'parent_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">'parent.id'</span><span class="p">)))</span>
<span class="k">class</span> <span class="nc">Parent</span><span class="p">(</span><span class="nb">object</span><span class="p">):</span>
<span class="k">pass</span>
<span class="k">class</span> <span class="nc">Child</span><span class="p">(</span><span class="nb">object</span><span class="p">):</span>
<span class="k">pass</span>
<span class="n">mapper</span><span class="p">(</span><span class="n">Parent</span><span class="p">,</span> <span class="n">parent_table</span><span class="p">,</span> <span class="n">properties</span><span class="o">=</span><span class="p">{</span>
<span class="s">'children'</span><span class="p">:</span> <span class="n">relationship</span><span class="p">(</span><span class="n">Child</span><span class="p">)</span>
<span class="p">})</span>
<span class="n">mapper</span><span class="p">(</span><span class="n">Child</span><span class="p">,</span> <span class="n">child_table</span><span class="p">)</span></pre></div>
</div>
<p>To establish a bi-directional relationship in one-to-many, where the “reverse” side is a many to one, specify the <tt class="docutils literal"><span class="pre">backref</span></tt> option:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="n">mapper</span><span class="p">(</span><span class="n">Parent</span><span class="p">,</span> <span class="n">parent_table</span><span class="p">,</span> <span class="n">properties</span><span class="o">=</span><span class="p">{</span>
<span class="s">'children'</span><span class="p">:</span> <span class="n">relationship</span><span class="p">(</span><span class="n">Child</span><span class="p">,</span> <span class="n">backref</span><span class="o">=</span><span class="s">'parent'</span><span class="p">)</span>
<span class="p">})</span>
<span class="n">mapper</span><span class="p">(</span><span class="n">Child</span><span class="p">,</span> <span class="n">child_table</span><span class="p">)</span></pre></div>
</div>
<p><tt class="docutils literal"><span class="pre">Child</span></tt> will get a <tt class="docutils literal"><span class="pre">parent</span></tt> attribute with many-to-one semantics.</p>
</div>
<div class="section" id="many-to-one">
<h4>Many To One<a class="headerlink" href="#many-to-one" title="Permalink to this headline">¶</a></h4>
<p>Many to one places a foreign key in the parent table referencing the child. The mapping setup is identical to one-to-many, however SQLAlchemy creates the relationship as a scalar attribute on the parent object referencing a single instance of the child object.</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="n">parent_table</span> <span class="o">=</span> <span class="n">Table</span><span class="p">(</span><span class="s">'parent'</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">'child_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">'child.id'</span><span class="p">)))</span>
<span class="n">child_table</span> <span class="o">=</span> <span class="n">Table</span><span class="p">(</span><span class="s">'child'</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="p">)</span>
<span class="k">class</span> <span class="nc">Parent</span><span class="p">(</span><span class="nb">object</span><span class="p">):</span>
<span class="k">pass</span>
<span class="k">class</span> <span class="nc">Child</span><span class="p">(</span><span class="nb">object</span><span class="p">):</span>
<span class="k">pass</span>
<span class="n">mapper</span><span class="p">(</span><span class="n">Parent</span><span class="p">,</span> <span class="n">parent_table</span><span class="p">,</span> <span class="n">properties</span><span class="o">=</span><span class="p">{</span>
<span class="s">'child'</span><span class="p">:</span> <span class="n">relationship</span><span class="p">(</span><span class="n">Child</span><span class="p">)</span>
<span class="p">})</span>
<span class="n">mapper</span><span class="p">(</span><span class="n">Child</span><span class="p">,</span> <span class="n">child_table</span><span class="p">)</span></pre></div>
</div>
<p>Backref behavior is available here as well, where <tt class="docutils literal"><span class="pre">backref="parents"</span></tt> will place a one-to-many collection on the <tt class="docutils literal"><span class="pre">Child</span></tt> class.</p>
</div>
<div class="section" id="one-to-one">
<h4>One To One<a class="headerlink" href="#one-to-one" title="Permalink to this headline">¶</a></h4>
<p>One To One is essentially a bi-directional relationship with a scalar attribute on both sides. To achieve this, the <tt class="docutils literal"><span class="pre">uselist=False</span></tt> flag indicates the placement of a scalar attribute instead of a collection on the “many” side of the relationship. To convert one-to-many into one-to-one:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="n">mapper</span><span class="p">(</span><span class="n">Parent</span><span class="p">,</span> <span class="n">parent_table</span><span class="p">,</span> <span class="n">properties</span><span class="o">=</span><span class="p">{</span>
<span class="s">'child'</span><span class="p">:</span> <span class="n">relationship</span><span class="p">(</span><span class="n">Child</span><span class="p">,</span> <span class="n">uselist</span><span class="o">=</span><span class="bp">False</span><span class="p">,</span> <span class="n">backref</span><span class="o">=</span><span class="s">'parent'</span><span class="p">)</span>
<span class="p">})</span></pre></div>
</div>
<p>Or to turn many-to-one into one-to-one:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="n">mapper</span><span class="p">(</span><span class="n">Parent</span><span class="p">,</span> <span class="n">parent_table</span><span class="p">,</span> <span class="n">properties</span><span class="o">=</span><span class="p">{</span>
<span class="s">'child'</span><span class="p">:</span> <span class="n">relationship</span><span class="p">(</span><span class="n">Child</span><span class="p">,</span> <span class="n">backref</span><span class="o">=</span><span class="n">backref</span><span class="p">(</span><span class="s">'parent'</span><span class="p">,</span> <span class="n">uselist</span><span class="o">=</span><span class="bp">False</span><span class="p">))</span>
<span class="p">})</span></pre></div>
</div>
</div>
<div class="section" id="many-to-many">
<h4>Many To Many<a class="headerlink" href="#many-to-many" title="Permalink to this headline">¶</a></h4>
<p>Many to Many adds an association table between two classes. The association table is indicated by the <tt class="docutils literal"><span class="pre">secondary</span></tt> argument to <a class="reference internal" href="reference/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>.</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="n">left_table</span> <span class="o">=</span> <span class="n">Table</span><span class="p">(</span><span class="s">'left'</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">right_table</span> <span class="o">=</span> <span class="n">Table</span><span class="p">(</span><span class="s">'right'</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">association_table</span> <span class="o">=</span> <span class="n">Table</span><span class="p">(</span><span class="s">'association'</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">'left_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">'left.id'</span><span class="p">)),</span>
<span class="n">Column</span><span class="p">(</span><span class="s">'right_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">'right.id'</span><span class="p">)),</span>
<span class="p">)</span>
<span class="n">mapper</span><span class="p">(</span><span class="n">Parent</span><span class="p">,</span> <span class="n">left_table</span><span class="p">,</span> <span class="n">properties</span><span class="o">=</span><span class="p">{</span>
<span class="s">'children'</span><span class="p">:</span> <span class="n">relationship</span><span class="p">(</span><span class="n">Child</span><span class="p">,</span> <span class="n">secondary</span><span class="o">=</span><span class="n">association_table</span><span class="p">)</span>
<span class="p">})</span>
<span class="n">mapper</span><span class="p">(</span><span class="n">Child</span><span class="p">,</span> <span class="n">right_table</span><span class="p">)</span></pre></div>
</div>
<p>For a bi-directional relationship, both sides of the relationship contain a collection by default, which can be modified on either side via the <tt class="docutils literal"><span class="pre">uselist</span></tt> flag to be scalar. The <tt class="docutils literal"><span class="pre">backref</span></tt> keyword will automatically use the same <tt class="docutils literal"><span class="pre">secondary</span></tt> argument for the reverse relationship:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="n">mapper</span><span class="p">(</span><span class="n">Parent</span><span class="p">,</span> <span class="n">left_table</span><span class="p">,</span> <span class="n">properties</span><span class="o">=</span><span class="p">{</span>
<span class="s">'children'</span><span class="p">:</span> <span class="n">relationship</span><span class="p">(</span><span class="n">Child</span><span class="p">,</span> <span class="n">secondary</span><span class="o">=</span><span class="n">association_table</span><span class="p">,</span> <span class="n">backref</span><span class="o">=</span><span class="s">'parents'</span><span class="p">)</span>
<span class="p">})</span></pre></div>
</div>
</div>
<div class="section" id="association-object">
<span id="association-pattern"></span><h4>Association Object<a class="headerlink" href="#association-object" title="Permalink to this headline">¶</a></h4>
<p>The association object pattern is a variant on many-to-many: it specifically is used when your association table contains additional columns beyond those which are foreign keys to the left and right tables. Instead of using the <tt class="docutils literal"><span class="pre">secondary</span></tt> argument, you map a new class directly to the association table. The left side of the relationship references the association object via one-to-many, and the association class references the right side via many-to-one.</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="n">left_table</span> <span class="o">=</span> <span class="n">Table</span><span class="p">(</span><span class="s">'left'</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">right_table</span> <span class="o">=</span> <span class="n">Table</span><span class="p">(</span><span class="s">'right'</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">association_table</span> <span class="o">=</span> <span class="n">Table</span><span class="p">(</span><span class="s">'association'</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">'left_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">'left.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">'right_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">'right.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">'data'</span><span class="p">,</span> <span class="n">String</span><span class="p">(</span><span class="mi">50</span><span class="p">))</span>
<span class="p">)</span>
<span class="n">mapper</span><span class="p">(</span><span class="n">Parent</span><span class="p">,</span> <span class="n">left_table</span><span class="p">,</span> <span class="n">properties</span><span class="o">=</span><span class="p">{</span>
<span class="s">'children'</span><span class="p">:</span><span class="n">relationship</span><span class="p">(</span><span class="n">Association</span><span class="p">)</span>
<span class="p">})</span>
<span class="n">mapper</span><span class="p">(</span><span class="n">Association</span><span class="p">,</span> <span class="n">association_table</span><span class="p">,</span> <span class="n">properties</span><span class="o">=</span><span class="p">{</span>
<span class="s">'child'</span><span class="p">:</span><span class="n">relationship</span><span class="p">(</span><span class="n">Child</span><span class="p">)</span>
<span class="p">})</span>
<span class="n">mapper</span><span class="p">(</span><span class="n">Child</span><span class="p">,</span> <span class="n">right_table</span><span class="p">)</span></pre></div>
</div>
<p>The bi-directional version adds backrefs to both relationships:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="n">mapper</span><span class="p">(</span><span class="n">Parent</span><span class="p">,</span> <span class="n">left_table</span><span class="p">,</span> <span class="n">properties</span><span class="o">=</span><span class="p">{</span>
<span class="s">'children'</span><span class="p">:</span><span class="n">relationship</span><span class="p">(</span><span class="n">Association</span><span class="p">,</span> <span class="n">backref</span><span class="o">=</span><span class="s">"parent"</span><span class="p">)</span>
<span class="p">})</span>
<span class="n">mapper</span><span class="p">(</span><span class="n">Association</span><span class="p">,</span> <span class="n">association_table</span><span class="p">,</span> <span class="n">properties</span><span class="o">=</span><span class="p">{</span>
<span class="s">'child'</span><span class="p">:</span><span class="n">relationship</span><span class="p">(</span><span class="n">Child</span><span class="p">,</span> <span class="n">backref</span><span class="o">=</span><span class="s">"parent_assocs"</span><span class="p">)</span>
<span class="p">})</span>
<span class="n">mapper</span><span class="p">(</span><span class="n">Child</span><span class="p">,</span> <span class="n">right_table</span><span class="p">)</span></pre></div>
</div>
<p>Working with the association pattern in its direct form requires that child objects are associated with an association instance before being appended to the parent; similarly, access from parent to child goes through the association object:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="c"># create parent, append a child via association</span>
<span class="n">p</span> <span class="o">=</span> <span class="n">Parent</span><span class="p">()</span>
<span class="n">a</span> <span class="o">=</span> <span class="n">Association</span><span class="p">()</span>
<span class="n">a</span><span class="o">.</span><span class="n">child</span> <span class="o">=</span> <span class="n">Child</span><span class="p">()</span>
<span class="n">p</span><span class="o">.</span><span class="n">children</span><span class="o">.</span><span class="n">append</span><span class="p">(</span><span class="n">a</span><span class="p">)</span>
<span class="c"># iterate through child objects via association, including association</span>
<span class="c"># attributes</span>
<span class="k">for</span> <span class="n">assoc</span> <span class="ow">in</span> <span class="n">p</span><span class="o">.</span><span class="n">children</span><span class="p">:</span>
<span class="k">print</span> <span class="n">assoc</span><span class="o">.</span><span class="n">data</span>
<span class="k">print</span> <span class="n">assoc</span><span class="o">.</span><span class="n">child</span></pre></div>
</div>
<p>To enhance the association object pattern such that direct access to the <tt class="docutils literal"><span class="pre">Association</span></tt> object is optional, SQLAlchemy provides the <a class="reference internal" href="reference/ext/associationproxy.html#associationproxy"><em>associationproxy</em></a>.</p>
<p><strong>Important Note</strong>: it is strongly advised that the <tt class="docutils literal"><span class="pre">secondary</span></tt> table argument not be combined with the Association Object pattern, unless the <a class="reference internal" href="reference/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> which contains the <tt class="docutils literal"><span class="pre">secondary</span></tt> argument is marked <tt class="docutils literal"><span class="pre">viewonly=True</span></tt>. Otherwise, SQLAlchemy may persist conflicting data to the underlying association table since it is represented by two conflicting mappings. The Association Proxy pattern should be favored in the case where access to the underlying association data is only sometimes needed.</p>
</div>
</div>
<div class="section" id="adjacency-list-relationships">
<h3>Adjacency List Relationships<a class="headerlink" href="#adjacency-list-relationships" title="Permalink to this headline">¶</a></h3>
<p>The <strong>adjacency list</strong> pattern is a common relational pattern whereby a table contains a foreign key reference to itself. This is the most common and simple way to represent hierarchical data in flat tables. The other way is the “nested sets” model, sometimes called “modified preorder”. Despite what many online articles say about modified preorder, the adjacency list model is probably the most appropriate pattern for the large majority of hierarchical storage needs, for reasons of concurrency, reduced complexity, and that modified preorder has little advantage over an application which can fully load subtrees into the application space.</p>
<p>SQLAlchemy commonly refers to an adjacency list relationship as a <strong>self-referential mapper</strong>. In this example, we’ll work with a single table called <tt class="docutils literal"><span class="pre">treenodes</span></tt> to represent a tree structure:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">nodes</span> <span class="o">=</span> <span class="n">Table</span><span class="p">(</span><span class="s">'treenodes'</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">'parent_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">'treenodes.id'</span><span class="p">)),</span>
<span class="n">Column</span><span class="p">(</span><span class="s">'data'</span><span class="p">,</span> <span class="n">String</span><span class="p">(</span><span class="mi">50</span><span class="p">)),</span>
<span class="p">)</span></pre></div>
</div>
<p>A graph such as the following:</p>
<div class="highlight-python"><pre>root --+---> child1
+---> child2 --+--> subchild1
| +--> subchild2
+---> child3</pre>
</div>
<p>Would be represented with data such as:</p>
<div class="highlight-python"><pre>id parent_id data
--- ------- ----
1 NULL root
2 1 child1
3 1 child2
4 3 subchild1
5 3 subchild2
6 1 child3</pre>
</div>
<p>SQLAlchemy’s <tt class="docutils literal"><span class="pre">mapper()</span></tt> configuration for a self-referential one-to-many relationship is exactly like a “normal” one-to-many relationship. When SQLAlchemy encounters the foreign key relationship from <tt class="docutils literal"><span class="pre">treenodes</span></tt> to <tt class="docutils literal"><span class="pre">treenodes</span></tt>, it assumes one-to-many unless told otherwise:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="c"># entity class</span>
<span class="k">class</span> <span class="nc">Node</span><span class="p">(</span><span class="nb">object</span><span class="p">):</span>
<span class="k">pass</span>
<span class="n">mapper</span><span class="p">(</span><span class="n">Node</span><span class="p">,</span> <span class="n">nodes</span><span class="p">,</span> <span class="n">properties</span><span class="o">=</span><span class="p">{</span>
<span class="s">'children'</span><span class="p">:</span> <span class="n">relationship</span><span class="p">(</span><span class="n">Node</span><span class="p">)</span>
<span class="p">})</span></pre></div>
</div>
<p>To create a many-to-one relationship from child to parent, an extra indicator of the “remote side” is added, which contains the <a class="reference internal" href="reference/sqlalchemy/schema.html#sqlalchemy.schema.Column" title="sqlalchemy.schema.Column"><tt class="xref py py-class docutils literal"><span class="pre">Column</span></tt></a> object or objects indicating the remote side of the relationship:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="n">mapper</span><span class="p">(</span><span class="n">Node</span><span class="p">,</span> <span class="n">nodes</span><span class="p">,</span> <span class="n">properties</span><span class="o">=</span><span class="p">{</span>
<span class="s">'parent'</span><span class="p">:</span> <span class="n">relationship</span><span class="p">(</span><span class="n">Node</span><span class="p">,</span> <span class="n">remote_side</span><span class="o">=</span><span class="p">[</span><span class="n">nodes</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">id</span><span class="p">])</span>
<span class="p">})</span></pre></div>
</div>
<p>And the bi-directional version combines both:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="n">mapper</span><span class="p">(</span><span class="n">Node</span><span class="p">,</span> <span class="n">nodes</span><span class="p">,</span> <span class="n">properties</span><span class="o">=</span><span class="p">{</span>
<span class="s">'children'</span><span class="p">:</span> <span class="n">relationship</span><span class="p">(</span><span class="n">Node</span><span class="p">,</span> <span class="n">backref</span><span class="o">=</span><span class="n">backref</span><span class="p">(</span><span class="s">'parent'</span><span class="p">,</span> <span class="n">remote_side</span><span class="o">=</span><span class="p">[</span><span class="n">nodes</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">id</span><span class="p">]))</span>
<span class="p">})</span></pre></div>
</div>
<p>There are several examples included with SQLAlchemy illustrating self-referential strategies; these include <a class="reference internal" href="examples.html#examples-adjacencylist"><em>Adjacency List</em></a> and <a class="reference internal" href="examples.html#examples-xmlpersistence"><em>XML Persistence</em></a>.</p>
<div class="section" id="self-referential-query-strategies">
<h4>Self-Referential Query Strategies<a class="headerlink" href="#self-referential-query-strategies" title="Permalink to this headline">¶</a></h4>
<p>Querying self-referential structures is done in the same way as any other query in SQLAlchemy, such as below, we query for any node whose <tt class="docutils literal"><span class="pre">data</span></tt> attribute stores the value <tt class="docutils literal"><span class="pre">child2</span></tt>:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="c"># get all nodes named 'child2'</span>
<span class="n">session</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="n">Node</span><span class="p">)</span><span class="o">.</span><span class="n">filter</span><span class="p">(</span><span class="n">Node</span><span class="o">.</span><span class="n">data</span><span class="o">==</span><span class="s">'child2'</span><span class="p">)</span></pre></div>
</div>
<p>On the subject of joins, i.e. those described in <cite>datamapping_joins</cite>, self-referential structures require the usage of aliases so that the same table can be referenced multiple times within the FROM clause of the query. Aliasing can be done either manually using the <tt class="docutils literal"><span class="pre">nodes</span></tt> <a class="reference internal" href="reference/sqlalchemy/schema.html#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal"><span class="pre">Table</span></tt></a> object as a source of aliases:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="c"># get all nodes named 'subchild1' with a parent named 'child2'</span>
<span class="n">nodealias</span> <span class="o">=</span> <span class="n">nodes</span><span class="o">.</span><span class="n">alias</span><span class="p">()</span>
<a href='#' class='sql_link'>sql</a><span class="n">session</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="n">Node</span><span class="p">)</span><span class="o">.</span><span class="n">filter</span><span class="p">(</span><span class="n">Node</span><span class="o">.</span><span class="n">data</span><span class="o">==</span><span class="s">'subchild1'</span><span class="p">)</span><span class="o">.</span>\
<span class="nb">filter</span><span class="p">(</span><span class="n">and_</span><span class="p">(</span><span class="n">Node</span><span class="o">.</span><span class="n">parent_id</span><span class="o">==</span><span class="n">nodealias</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">id</span><span class="p">,</span> <span class="n">nodealias</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">data</span><span class="o">==</span><span class="s">'child2'</span><span class="p">))</span><span class="o">.</span><span class="n">all</span><span class="p">()</span>
<div class='popup_sql'>SELECT treenodes.id AS treenodes_id, treenodes.parent_id AS treenodes_parent_id, treenodes.data AS treenodes_data
FROM treenodes, treenodes AS treenodes_1
WHERE treenodes.data = ? AND treenodes.parent_id = treenodes_1.id AND treenodes_1.data = ?
['subchild1', 'child2']</div></pre></div>
</div>
<p>or automatically, using <tt class="docutils literal"><span class="pre">join()</span></tt> with <tt class="docutils literal"><span class="pre">aliased=True</span></tt>:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="c"># get all nodes named 'subchild1' with a parent named 'child2'</span>
<a href='#' class='sql_link'>sql</a><span class="n">session</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="n">Node</span><span class="p">)</span><span class="o">.</span><span class="n">filter</span><span class="p">(</span><span class="n">Node</span><span class="o">.</span><span class="n">data</span><span class="o">==</span><span class="s">'subchild1'</span><span class="p">)</span><span class="o">.</span>\
<span class="n">join</span><span class="p">(</span><span class="s">'parent'</span><span class="p">,</span> <span class="n">aliased</span><span class="o">=</span><span class="bp">True</span><span class="p">)</span><span class="o">.</span><span class="n">filter</span><span class="p">(</span><span class="n">Node</span><span class="o">.</span><span class="n">data</span><span class="o">==</span><span class="s">'child2'</span><span class="p">)</span><span class="o">.</span><span class="n">all</span><span class="p">()</span>
<div class='popup_sql'>SELECT treenodes.id AS treenodes_id, treenodes.parent_id AS treenodes_parent_id, treenodes.data AS treenodes_data
FROM treenodes JOIN treenodes AS treenodes_1 ON treenodes_1.id = treenodes.parent_id
WHERE treenodes.data = ? AND treenodes_1.data = ?
['subchild1', 'child2']</div></pre></div>
</div>
<p>To add criterion to multiple points along a longer join, use <tt class="docutils literal"><span class="pre">from_joinpoint=True</span></tt>:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="c"># get all nodes named 'subchild1' with a parent named 'child2' and a grandparent 'root'</span>
<a href='#' class='sql_link'>sql</a><span class="n">session</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="n">Node</span><span class="p">)</span><span class="o">.</span><span class="n">filter</span><span class="p">(</span><span class="n">Node</span><span class="o">.</span><span class="n">data</span><span class="o">==</span><span class="s">'subchild1'</span><span class="p">)</span><span class="o">.</span>\
<span class="n">join</span><span class="p">(</span><span class="s">'parent'</span><span class="p">,</span> <span class="n">aliased</span><span class="o">=</span><span class="bp">True</span><span class="p">)</span><span class="o">.</span><span class="n">filter</span><span class="p">(</span><span class="n">Node</span><span class="o">.</span><span class="n">data</span><span class="o">==</span><span class="s">'child2'</span><span class="p">)</span><span class="o">.</span>\
<span class="n">join</span><span class="p">(</span><span class="s">'parent'</span><span class="p">,</span> <span class="n">aliased</span><span class="o">=</span><span class="bp">True</span><span class="p">,</span> <span class="n">from_joinpoint</span><span class="o">=</span><span class="bp">True</span><span class="p">)</span><span class="o">.</span><span class="n">filter</span><span class="p">(</span><span class="n">Node</span><span class="o">.</span><span class="n">data</span><span class="o">==</span><span class="s">'root'</span><span class="p">)</span><span class="o">.</span><span class="n">all</span><span class="p">()</span>
<div class='popup_sql'>SELECT treenodes.id AS treenodes_id, treenodes.parent_id AS treenodes_parent_id, treenodes.data AS treenodes_data
FROM treenodes JOIN treenodes AS treenodes_1 ON treenodes_1.id = treenodes.parent_id JOIN treenodes AS treenodes_2 ON treenodes_2.id = treenodes_1.parent_id
WHERE treenodes.data = ? AND treenodes_1.data = ? AND treenodes_2.data = ?
['subchild1', 'child2', 'root']</div></pre></div>
</div>
</div>
<div class="section" id="configuring-eager-loading">
<h4>Configuring Eager Loading<a class="headerlink" href="#configuring-eager-loading" title="Permalink to this headline">¶</a></h4>
<p>Eager loading of relationships occurs using joins or outerjoins from parent to child table during a normal query operation, such that the parent and its child collection can be populated from a single SQL statement, or a second statement for all collections at once. SQLAlchemy’s joined and subquery eager loading uses aliased tables in all cases when joining to related items, so it is compatible with self-referential joining. However, to use eager loading with a self-referential relationship, SQLAlchemy needs to be told how many levels deep it should join; otherwise the eager load will not take place. This depth setting is configured via <tt class="docutils literal"><span class="pre">join_depth</span></tt>:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="n">mapper</span><span class="p">(</span><span class="n">Node</span><span class="p">,</span> <span class="n">nodes</span><span class="p">,</span> <span class="n">properties</span><span class="o">=</span><span class="p">{</span>
<span class="s">'children'</span><span class="p">:</span> <span class="n">relationship</span><span class="p">(</span><span class="n">Node</span><span class="p">,</span> <span class="n">lazy</span><span class="o">=</span><span class="s">'joined'</span><span class="p">,</span> <span class="n">join_depth</span><span class="o">=</span><span class="mi">2</span><span class="p">)</span>
<span class="p">})</span>
<a href='#' class='sql_link'>sql</a><span class="n">session</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="n">Node</span><span class="p">)</span><span class="o">.</span><span class="n">all</span><span class="p">()</span>
<div class='popup_sql'>SELECT treenodes_1.id AS treenodes_1_id, treenodes_1.parent_id AS treenodes_1_parent_id, treenodes_1.data AS treenodes_1_data, treenodes_2.id AS treenodes_2_id, treenodes_2.parent_id AS treenodes_2_parent_id, treenodes_2.data AS treenodes_2_data, treenodes.id AS treenodes_id, treenodes.parent_id AS treenodes_parent_id, treenodes.data AS treenodes_data
FROM treenodes LEFT OUTER JOIN treenodes AS treenodes_2 ON treenodes.id = treenodes_2.parent_id LEFT OUTER JOIN treenodes AS treenodes_1 ON treenodes_2.id = treenodes_1.parent_id
[]</div></pre></div>
</div>
</div>
</div>
<div class="section" id="specifying-alternate-join-conditions-to-relationship">
<h3>Specifying Alternate Join Conditions to relationship()<a class="headerlink" href="#specifying-alternate-join-conditions-to-relationship" title="Permalink to this headline">¶</a></h3>
<p>The <a class="reference internal" href="reference/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> function uses the foreign key relationship between the parent and child tables to formulate the <strong>primary join condition</strong> between parent and child; in the case of a many-to-many relationship it also formulates the <strong>secondary join condition</strong>:</p>
<div class="highlight-python"><pre>one to many/many to one:
------------------------
parent_table --> parent_table.c.id == child_table.c.parent_id --> child_table
primaryjoin
many to many:
-------------
parent_table --> parent_table.c.id == secondary_table.c.parent_id -->
primaryjoin
secondary_table.c.child_id == child_table.c.id --> child_table
secondaryjoin</pre>
</div>
<p>If you are working with a <a class="reference internal" href="reference/sqlalchemy/schema.html#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal"><span class="pre">Table</span></tt></a> which has no <a class="reference internal" href="reference/sqlalchemy/schema.html#sqlalchemy.schema.ForeignKey" title="sqlalchemy.schema.ForeignKey"><tt class="xref py py-class docutils literal"><span class="pre">ForeignKey</span></tt></a> objects on it (which can be the case when using reflected tables with MySQL), or if the join condition cannot be expressed by a simple foreign key relationship, use the <tt class="docutils literal"><span class="pre">primaryjoin</span></tt> and possibly <tt class="docutils literal"><span class="pre">secondaryjoin</span></tt> conditions to create the appropriate relationship.</p>
<p>In this example we create a relationship <tt class="docutils literal"><span class="pre">boston_addresses</span></tt> which will only load the user addresses with a city of “Boston”:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><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">pass</span>
<span class="k">class</span> <span class="nc">Address</span><span class="p">(</span><span class="nb">object</span><span class="p">):</span>
<span class="k">pass</span>
<span class="n">mapper</span><span class="p">(</span><span class="n">Address</span><span class="p">,</span> <span class="n">addresses_table</span><span class="p">)</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">'boston_addresses'</span><span class="p">:</span> <span class="n">relationship</span><span class="p">(</span><span class="n">Address</span><span class="p">,</span> <span class="n">primaryjoin</span><span class="o">=</span>
<span class="n">and_</span><span class="p">(</span><span class="n">users_table</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">user_id</span><span class="o">==</span><span class="n">addresses_table</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">user_id</span><span class="p">,</span>
<span class="n">addresses_table</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">city</span><span class="o">==</span><span class="s">'Boston'</span><span class="p">))</span>
<span class="p">})</span></pre></div>
</div>
<p>Many to many relationships can be customized by one or both of <tt class="docutils literal"><span class="pre">primaryjoin</span></tt> and <tt class="docutils literal"><span class="pre">secondaryjoin</span></tt>, shown below with just the default many-to-many relationship explicitly set:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><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">pass</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">pass</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">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">'keywords'</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="n">primaryjoin</span><span class="o">=</span><span class="n">users_table</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">user_id</span><span class="o">==</span><span class="n">userkeywords_table</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">user_id</span><span class="p">,</span>
<span class="n">secondaryjoin</span><span class="o">=</span><span class="n">userkeywords_table</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">keyword_id</span><span class="o">==</span><span class="n">keywords_table</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">keyword_id</span>
<span class="p">)</span>
<span class="p">})</span></pre></div>
</div>
<div class="section" id="specifying-foreign-keys">
<h4>Specifying Foreign Keys<a class="headerlink" href="#specifying-foreign-keys" title="Permalink to this headline">¶</a></h4>
<p>When using <tt class="docutils literal"><span class="pre">primaryjoin</span></tt> and <tt class="docutils literal"><span class="pre">secondaryjoin</span></tt>, SQLAlchemy also needs to be aware of which columns in the relationship reference the other. In most cases, a <a class="reference internal" href="reference/sqlalchemy/schema.html#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal"><span class="pre">Table</span></tt></a> construct will have <a class="reference internal" href="reference/sqlalchemy/schema.html#sqlalchemy.schema.ForeignKey" title="sqlalchemy.schema.ForeignKey"><tt class="xref py py-class docutils literal"><span class="pre">ForeignKey</span></tt></a> constructs which take care of this; however, in the case of reflected tables on a database that does not report FKs (like MySQL ISAM) or when using join conditions on columns that don’t have foreign keys, the <a class="reference internal" href="reference/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> needs to be told specifically which columns are “foreign” using the <tt class="docutils literal"><span class="pre">foreign_keys</span></tt> collection:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="n">mapper</span><span class="p">(</span><span class="n">Address</span><span class="p">,</span> <span class="n">addresses_table</span><span class="p">)</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">'addresses'</span><span class="p">:</span> <span class="n">relationship</span><span class="p">(</span><span class="n">Address</span><span class="p">,</span> <span class="n">primaryjoin</span><span class="o">=</span>
<span class="n">users_table</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">user_id</span><span class="o">==</span><span class="n">addresses_table</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">user_id</span><span class="p">,</span>
<span class="n">foreign_keys</span><span class="o">=</span><span class="p">[</span><span class="n">addresses_table</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">user_id</span><span class="p">])</span>
<span class="p">})</span></pre></div>
</div>
</div>
<div class="section" id="building-query-enabled-properties">
<h4>Building Query-Enabled Properties<a class="headerlink" href="#building-query-enabled-properties" title="Permalink to this headline">¶</a></h4>
<p>Very ambitious custom join conditions may fail to be directly persistable, and in some cases may not even load correctly. To remove the persistence part of the equation, use the flag <tt class="docutils literal"><span class="pre">viewonly=True</span></tt> on the <a class="reference internal" href="reference/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>, which establishes it as a read-only attribute (data written to the collection will be ignored on flush()). However, in extreme cases, consider using a regular Python property in conjunction with <a class="reference internal" href="reference/orm/query.html#sqlalchemy.orm.query.Query" title="sqlalchemy.orm.query.Query"><tt class="xref py py-class docutils literal"><span class="pre">Query</span></tt></a> as follows:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><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">_get_addresses</span><span class="p">(</span><span class="bp">self</span><span class="p">):</span>
<span class="k">return</span> <span class="n">object_session</span><span class="p">(</span><span class="bp">self</span><span class="p">)</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="n">Address</span><span class="p">)</span><span class="o">.</span><span class="n">with_parent</span><span class="p">(</span><span class="bp">self</span><span class="p">)</span><span class="o">.</span><span class="n">filter</span><span class="p">(</span><span class="o">...</span><span class="p">)</span><span class="o">.</span><span class="n">all</span><span class="p">()</span>
<span class="n">addresses</span> <span class="o">=</span> <span class="nb">property</span><span class="p">(</span><span class="n">_get_addresses</span><span class="p">)</span></pre></div>
</div>
</div>
<div class="section" id="multiple-relationships-against-the-same-parent-child">
<h4>Multiple Relationships against the Same Parent/Child<a class="headerlink" href="#multiple-relationships-against-the-same-parent-child" title="Permalink to this headline">¶</a></h4>
<p>Theres no restriction on how many times you can relate from parent to child. SQLAlchemy can usually figure out what you want, particularly if the join conditions are straightforward. Below we add a <tt class="docutils literal"><span class="pre">newyork_addresses</span></tt> attribute to complement the <tt class="docutils literal"><span class="pre">boston_addresses</span></tt> attribute:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><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">'boston_addresses'</span><span class="p">:</span> <span class="n">relationship</span><span class="p">(</span><span class="n">Address</span><span class="p">,</span> <span class="n">primaryjoin</span><span class="o">=</span>
<span class="n">and_</span><span class="p">(</span><span class="n">users_table</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">user_id</span><span class="o">==</span><span class="n">addresses_table</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">user_id</span><span class="p">,</span>
<span class="n">addresses_table</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">city</span><span class="o">==</span><span class="s">'Boston'</span><span class="p">)),</span>
<span class="s">'newyork_addresses'</span><span class="p">:</span> <span class="n">relationship</span><span class="p">(</span><span class="n">Address</span><span class="p">,</span> <span class="n">primaryjoin</span><span class="o">=</span>
<span class="n">and_</span><span class="p">(</span><span class="n">users_table</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">user_id</span><span class="o">==</span><span class="n">addresses_table</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">user_id</span><span class="p">,</span>
<span class="n">addresses_table</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">city</span><span class="o">==</span><span class="s">'New York'</span><span class="p">)),</span>
<span class="p">})</span></pre></div>
</div>
</div>
</div>
<div class="section" id="rows-that-point-to-themselves-mutually-dependent-rows">
<span id="alternate-collection-implementations"></span><h3>Rows that point to themselves / Mutually Dependent Rows<a class="headerlink" href="#rows-that-point-to-themselves-mutually-dependent-rows" title="Permalink to this headline">¶</a></h3>
<p>This is a very specific case where relationship() must perform an INSERT and a second UPDATE in order to properly populate a row (and vice versa an UPDATE and DELETE in order to delete without violating foreign key constraints). The two use cases are:</p>
<blockquote>
<ul class="simple">
<li>A table contains a foreign key to itself, and a single row will have a foreign key value pointing to its own primary key.</li>
<li>Two tables each contain a foreign key referencing the other table, with a row in each table referencing the other.</li>
</ul>
</blockquote>
<p>For example:</p>
<div class="highlight-python"><pre> user
---------------------------------
user_id name related_user_id
1 'ed' 1</pre>
</div>
<p>Or:</p>
<div class="highlight-python"><pre> widget entry
------------------------------------------- ---------------------------------
widget_id name favorite_entry_id entry_id name widget_id
1 'somewidget' 5 5 'someentry' 1</pre>
</div>
<p>In the first case, a row points to itself. Technically, a database that uses sequences such as PostgreSQL or Oracle can INSERT the row at once using a previously generated value, but databases which rely upon autoincrement-style primary key identifiers cannot. The <a class="reference internal" href="reference/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> always assumes a “parent/child” model of row population during flush, so unless you are populating the primary key/foreign key columns directly, <a class="reference internal" href="reference/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> needs to use two statements.</p>
<p>In the second case, the “widget” row must be inserted before any referring “entry” rows, but then the “favorite_entry_id” column of that “widget” row cannot be set until the “entry” rows have been generated. In this case, it’s typically impossible to insert the “widget” and “entry” rows using just two INSERT statements; an UPDATE must be performed in order to keep foreign key constraints fulfilled. The exception is if the foreign keys are configured as “deferred until commit” (a feature some databases support) and if the identifiers were populated manually (again essentially bypassing <a class="reference internal" href="reference/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>).</p>
<p>To enable the UPDATE after INSERT / UPDATE before DELETE behavior on <a class="reference internal" href="reference/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>, use the <tt class="docutils literal"><span class="pre">post_update</span></tt> flag on <em>one</em> of the relationships, preferably the many-to-one side:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">mapper</span><span class="p">(</span><span class="n">Widget</span><span class="p">,</span> <span class="n">widget</span><span class="p">,</span> <span class="n">properties</span><span class="o">=</span><span class="p">{</span>
<span class="s">'entries'</span><span class="p">:</span><span class="n">relationship</span><span class="p">(</span><span class="n">Entry</span><span class="p">,</span> <span class="n">primaryjoin</span><span class="o">=</span><span class="n">widget</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">widget_id</span><span class="o">==</span><span class="n">entry</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">widget_id</span><span class="p">),</span>
<span class="s">'favorite_entry'</span><span class="p">:</span><span class="n">relationship</span><span class="p">(</span><span class="n">Entry</span><span class="p">,</span> <span class="n">primaryjoin</span><span class="o">=</span><span class="n">widget</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">favorite_entry_id</span><span class="o">==</span><span class="n">entry</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">entry_id</span><span class="p">,</span> <span class="n">post_update</span><span class="o">=</span><span class="bp">True</span><span class="p">)</span>
<span class="p">})</span></pre></div>
</div>
<p>When a structure using the above mapping is flushed, the “widget” row will be INSERTed minus the “favorite_entry_id” value, then all the “entry” rows will be INSERTed referencing the parent “widget” row, and then an UPDATE statement will populate the “favorite_entry_id” column of the “widget” table (it’s one row at a time for the time being).</p>
</div>
<div class="section" id="advdatamapping-entitycollections">
<span id="id4"></span><h3>Alternate Collection Implementations<a class="headerlink" href="#advdatamapping-entitycollections" title="Permalink to this headline">¶</a></h3>
<p>Mapping a one-to-many or many-to-many relationship results in a collection of values accessible through an attribute on the parent instance. By default, this collection is a <tt class="docutils literal"><span class="pre">list</span></tt>:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="n">mapper</span><span class="p">(</span><span class="n">Parent</span><span class="p">,</span> <span class="n">properties</span><span class="o">=</span><span class="p">{</span>
<span class="n">children</span> <span class="o">=</span> <span class="n">relationship</span><span class="p">(</span><span class="n">Child</span><span class="p">)</span>
<span class="p">})</span>
<span class="n">parent</span> <span class="o">=</span> <span class="n">Parent</span><span class="p">()</span>
<span class="n">parent</span><span class="o">.</span><span class="n">children</span><span class="o">.</span><span class="n">append</span><span class="p">(</span><span class="n">Child</span><span class="p">())</span>
<span class="k">print</span> <span class="n">parent</span><span class="o">.</span><span class="n">children</span><span class="p">[</span><span class="mi">0</span><span class="p">]</span></pre></div>
</div>
<p>Collections are not limited to lists. Sets, mutable sequences and almost any other Python object that can act as a container can be used in place of the default list, by specifying the <tt class="docutils literal"><span class="pre">collection_class</span></tt> option on <a class="reference internal" href="reference/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>.</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="c"># use a set</span>
<span class="n">mapper</span><span class="p">(</span><span class="n">Parent</span><span class="p">,</span> <span class="n">properties</span><span class="o">=</span><span class="p">{</span>
<span class="n">children</span> <span class="o">=</span> <span class="n">relationship</span><span class="p">(</span><span class="n">Child</span><span class="p">,</span> <span class="n">collection_class</span><span class="o">=</span><span class="nb">set</span><span class="p">)</span>
<span class="p">})</span>
<span class="n">parent</span> <span class="o">=</span> <span class="n">Parent</span><span class="p">()</span>
<span class="n">child</span> <span class="o">=</span> <span class="n">Child</span><span class="p">()</span>
<span class="n">parent</span><span class="o">.</span><span class="n">children</span><span class="o">.</span><span class="n">add</span><span class="p">(</span><span class="n">child</span><span class="p">)</span>
<span class="k">assert</span> <span class="n">child</span> <span class="ow">in</span> <span class="n">parent</span><span class="o">.</span><span class="n">children</span></pre></div>
</div>
<div class="section" id="custom-collection-implementations">
<h4>Custom Collection Implementations<a class="headerlink" href="#custom-collection-implementations" title="Permalink to this headline">¶</a></h4>
<p>You can use your own types for collections as well. For most cases, simply inherit from <tt class="docutils literal"><span class="pre">list</span></tt> or <tt class="docutils literal"><span class="pre">set</span></tt> and add the custom behavior.</p>
<p>Collections in SQLAlchemy are transparently <em>instrumented</em>. Instrumentation means that normal operations on the collection are tracked and result in changes being written to the database at flush time. Additionally, collection operations can fire <em>events</em> which indicate some secondary operation must take place. Examples of a secondary operation include saving the child item in the parent’s <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.session.Session" title="sqlalchemy.orm.session.Session"><tt class="xref py py-class docutils literal"><span class="pre">Session</span></tt></a> (i.e. the <tt class="docutils literal"><span class="pre">save-update</span></tt> cascade), as well as synchronizing the state of a bi-directional relationship (i.e. a <tt class="docutils literal"><span class="pre">backref</span></tt>).</p>
<p>The collections package understands the basic interface of lists, sets and dicts and will automatically apply instrumentation to those built-in types and their subclasses. Object-derived types that implement a basic collection interface are detected and instrumented via duck-typing:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="k">class</span> <span class="nc">ListLike</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="bp">self</span><span class="o">.</span><span class="n">data</span> <span class="o">=</span> <span class="p">[]</span>
<span class="k">def</span> <span class="nf">append</span><span class="p">(</span><span class="bp">self</span><span class="p">,</span> <span class="n">item</span><span class="p">):</span>
<span class="bp">self</span><span class="o">.</span><span class="n">data</span><span class="o">.</span><span class="n">append</span><span class="p">(</span><span class="n">item</span><span class="p">)</span>
<span class="k">def</span> <span class="nf">remove</span><span class="p">(</span><span class="bp">self</span><span class="p">,</span> <span class="n">item</span><span class="p">):</span>
<span class="bp">self</span><span class="o">.</span><span class="n">data</span><span class="o">.</span><span class="n">remove</span><span class="p">(</span><span class="n">item</span><span class="p">)</span>
<span class="k">def</span> <span class="nf">extend</span><span class="p">(</span><span class="bp">self</span><span class="p">,</span> <span class="n">items</span><span class="p">):</span>
<span class="bp">self</span><span class="o">.</span><span class="n">data</span><span class="o">.</span><span class="n">extend</span><span class="p">(</span><span class="n">items</span><span class="p">)</span>
<span class="k">def</span> <span class="nf">__iter__</span><span class="p">(</span><span class="bp">self</span><span class="p">):</span>
<span class="k">return</span> <span class="nb">iter</span><span class="p">(</span><span class="bp">self</span><span class="o">.</span><span class="n">data</span><span class="p">)</span>
<span class="k">def</span> <span class="nf">foo</span><span class="p">(</span><span class="bp">self</span><span class="p">):</span>
<span class="k">return</span> <span class="s">'foo'</span></pre></div>
</div>
<p><tt class="docutils literal"><span class="pre">append</span></tt>, <tt class="docutils literal"><span class="pre">remove</span></tt>, and <tt class="docutils literal"><span class="pre">extend</span></tt> are known list-like methods, and will be instrumented automatically. <tt class="docutils literal"><span class="pre">__iter__</span></tt> is not a mutator method and won’t be instrumented, and <tt class="docutils literal"><span class="pre">foo</span></tt> won’t be either.</p>
<p>Duck-typing (i.e. guesswork) isn’t rock-solid, of course, so you can be explicit about the interface you are implementing by providing an <tt class="docutils literal"><span class="pre">__emulates__</span></tt> class attribute:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="k">class</span> <span class="nc">SetLike</span><span class="p">(</span><span class="nb">object</span><span class="p">):</span>
<span class="n">__emulates__</span> <span class="o">=</span> <span class="nb">set</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="bp">self</span><span class="o">.</span><span class="n">data</span> <span class="o">=</span> <span class="nb">set</span><span class="p">()</span>
<span class="k">def</span> <span class="nf">append</span><span class="p">(</span><span class="bp">self</span><span class="p">,</span> <span class="n">item</span><span class="p">):</span>
<span class="bp">self</span><span class="o">.</span><span class="n">data</span><span class="o">.</span><span class="n">add</span><span class="p">(</span><span class="n">item</span><span class="p">)</span>
<span class="k">def</span> <span class="nf">remove</span><span class="p">(</span><span class="bp">self</span><span class="p">,</span> <span class="n">item</span><span class="p">):</span>
<span class="bp">self</span><span class="o">.</span><span class="n">data</span><span class="o">.</span><span class="n">remove</span><span class="p">(</span><span class="n">item</span><span class="p">)</span>
<span class="k">def</span> <span class="nf">__iter__</span><span class="p">(</span><span class="bp">self</span><span class="p">):</span>
<span class="k">return</span> <span class="nb">iter</span><span class="p">(</span><span class="bp">self</span><span class="o">.</span><span class="n">data</span><span class="p">)</span></pre></div>
</div>
<p>This class looks list-like because of <tt class="docutils literal"><span class="pre">append</span></tt>, but <tt class="docutils literal"><span class="pre">__emulates__</span></tt> forces it to set-like. <tt class="docutils literal"><span class="pre">remove</span></tt> is known to be part of the set interface and will be instrumented.</p>
<p>But this class won’t work quite yet: a little glue is needed to adapt it for use by SQLAlchemy. The ORM needs to know which methods to use to append, remove and iterate over members of the collection. When using a type like <tt class="docutils literal"><span class="pre">list</span></tt> or <tt class="docutils literal"><span class="pre">set</span></tt>, the appropriate methods are well-known and used automatically when present. This set-like class does not provide the expected <tt class="docutils literal"><span class="pre">add</span></tt> method, so we must supply an explicit mapping for the ORM via a decorator.</p>
</div>
<div class="section" id="annotating-custom-collections-via-decorators">
<h4>Annotating Custom Collections via Decorators<a class="headerlink" href="#annotating-custom-collections-via-decorators" title="Permalink to this headline">¶</a></h4>
<p>Decorators can be used to tag the individual methods the ORM needs to manage collections. Use them when your class doesn’t quite meet the regular interface for its container type, or you simply would like to use a different method to get the job done.</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="kn">from</span> <span class="nn">sqlalchemy.orm.collections</span> <span class="kn">import</span> <span class="n">collection</span>
<span class="k">class</span> <span class="nc">SetLike</span><span class="p">(</span><span class="nb">object</span><span class="p">):</span>
<span class="n">__emulates__</span> <span class="o">=</span> <span class="nb">set</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="bp">self</span><span class="o">.</span><span class="n">data</span> <span class="o">=</span> <span class="nb">set</span><span class="p">()</span>
<span class="nd">@collection.appender</span>
<span class="k">def</span> <span class="nf">append</span><span class="p">(</span><span class="bp">self</span><span class="p">,</span> <span class="n">item</span><span class="p">):</span>
<span class="bp">self</span><span class="o">.</span><span class="n">data</span><span class="o">.</span><span class="n">add</span><span class="p">(</span><span class="n">item</span><span class="p">)</span>
<span class="k">def</span> <span class="nf">remove</span><span class="p">(</span><span class="bp">self</span><span class="p">,</span> <span class="n">item</span><span class="p">):</span>
<span class="bp">self</span><span class="o">.</span><span class="n">data</span><span class="o">.</span><span class="n">remove</span><span class="p">(</span><span class="n">item</span><span class="p">)</span>
<span class="k">def</span> <span class="nf">__iter__</span><span class="p">(</span><span class="bp">self</span><span class="p">):</span>
<span class="k">return</span> <span class="nb">iter</span><span class="p">(</span><span class="bp">self</span><span class="o">.</span><span class="n">data</span><span class="p">)</span></pre></div>
</div>
<p>And that’s all that’s needed to complete the example. SQLAlchemy will add instances via the <tt class="docutils literal"><span class="pre">append</span></tt> method. <tt class="docutils literal"><span class="pre">remove</span></tt> and <tt class="docutils literal"><span class="pre">__iter__</span></tt> are the default methods for sets and will be used for removing and iteration. Default methods can be changed as well:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="kn">from</span> <span class="nn">sqlalchemy.orm.collections</span> <span class="kn">import</span> <span class="n">collection</span>
<span class="k">class</span> <span class="nc">MyList</span><span class="p">(</span><span class="nb">list</span><span class="p">):</span>
<span class="nd">@collection.remover</span>
<span class="k">def</span> <span class="nf">zark</span><span class="p">(</span><span class="bp">self</span><span class="p">,</span> <span class="n">item</span><span class="p">):</span>
<span class="c"># do something special...</span>
<span class="nd">@collection.iterator</span>
<span class="k">def</span> <span class="nf">hey_use_this_instead_for_iteration</span><span class="p">(</span><span class="bp">self</span><span class="p">):</span>
<span class="c"># ...</span></pre></div>
</div>
<p>There is no requirement to be list-, or set-like at all. Collection classes can be any shape, so long as they have the append, remove and iterate interface marked for SQLAlchemy’s use. Append and remove methods will be called with a mapped entity as the single argument, and iterator methods are called with no arguments and must return an iterator.</p>
</div>
<div class="section" id="dictionary-based-collections">
<h4>Dictionary-Based Collections<a class="headerlink" href="#dictionary-based-collections" title="Permalink to this headline">¶</a></h4>
<p>A <tt class="docutils literal"><span class="pre">dict</span></tt> can be used as a collection, but a keying strategy is needed to map entities loaded by the ORM to key, value pairs. The <a class="reference internal" href="reference/orm/collections.html#module-sqlalchemy.orm.collections"><tt class="xref py py-mod docutils literal"><span class="pre">sqlalchemy.orm.collections</span></tt></a> package provides several built-in types for dictionary-based collections:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="kn">from</span> <span class="nn">sqlalchemy.orm.collections</span> <span class="kn">import</span> <span class="n">column_mapped_collection</span><span class="p">,</span> <span class="n">attribute_mapped_collection</span><span class="p">,</span> <span class="n">mapped_collection</span>
<span class="n">mapper</span><span class="p">(</span><span class="n">Item</span><span class="p">,</span> <span class="n">items_table</span><span class="p">,</span> <span class="n">properties</span><span class="o">=</span><span class="p">{</span>
<span class="c"># key by column</span>
<span class="s">'notes'</span><span class="p">:</span> <span class="n">relationship</span><span class="p">(</span><span class="n">Note</span><span class="p">,</span> <span class="n">collection_class</span><span class="o">=</span><span class="n">column_mapped_collection</span><span class="p">(</span><span class="n">notes_table</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">keyword</span><span class="p">)),</span>
<span class="c"># or named attribute</span>
<span class="s">'notes2'</span><span class="p">:</span> <span class="n">relationship</span><span class="p">(</span><span class="n">Note</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">'keyword'</span><span class="p">)),</span>
<span class="c"># or any callable</span>
<span class="s">'notes3'</span><span class="p">:</span> <span class="n">relationship</span><span class="p">(</span><span class="n">Note</span><span class="p">,</span> <span class="n">collection_class</span><span class="o">=</span><span class="n">mapped_collection</span><span class="p">(</span><span class="k">lambda</span> <span class="n">entity</span><span class="p">:</span> <span class="n">entity</span><span class="o">.</span><span class="n">a</span> <span class="o">+</span> <span class="n">entity</span><span class="o">.</span><span class="n">b</span><span class="p">))</span>
<span class="p">})</span>
<span class="c"># ...</span>
<span class="n">item</span> <span class="o">=</span> <span class="n">Item</span><span class="p">()</span>
<span class="n">item</span><span class="o">.</span><span class="n">notes</span><span class="p">[</span><span class="s">'color'</span><span class="p">]</span> <span class="o">=</span> <span class="n">Note</span><span class="p">(</span><span class="s">'color'</span><span class="p">,</span> <span class="s">'blue'</span><span class="p">)</span>
<span class="k">print</span> <span class="n">item</span><span class="o">.</span><span class="n">notes</span><span class="p">[</span><span class="s">'color'</span><span class="p">]</span></pre></div>
</div>
<p>These functions each provide a <tt class="docutils literal"><span class="pre">dict</span></tt> subclass with decorated <tt class="docutils literal"><span class="pre">set</span></tt> and <tt class="docutils literal"><span class="pre">remove</span></tt> methods and the keying strategy of your choice.</p>
<p>The <a class="reference internal" href="reference/orm/collections.html#sqlalchemy.orm.collections.MappedCollection" title="sqlalchemy.orm.collections.MappedCollection"><tt class="xref py py-class docutils literal"><span class="pre">sqlalchemy.orm.collections.MappedCollection</span></tt></a> class can be used as a base class for your custom types or as a mix-in to quickly add <tt class="docutils literal"><span class="pre">dict</span></tt> collection support to other classes. It uses a keying function to delegate to <tt class="docutils literal"><span class="pre">__setitem__</span></tt> and <tt class="docutils literal"><span class="pre">__delitem__</span></tt>:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="kn">from</span> <span class="nn">sqlalchemy.util</span> <span class="kn">import</span> <span class="n">OrderedDict</span>
<span class="kn">from</span> <span class="nn">sqlalchemy.orm.collections</span> <span class="kn">import</span> <span class="n">MappedCollection</span>
<span class="k">class</span> <span class="nc">NodeMap</span><span class="p">(</span><span class="n">OrderedDict</span><span class="p">,</span> <span class="n">MappedCollection</span><span class="p">):</span>
<span class="sd">"""Holds 'Node' objects, keyed by the 'name' attribute with insert order maintained."""</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="o">*</span><span class="n">args</span><span class="p">,</span> <span class="o">**</span><span class="n">kw</span><span class="p">):</span>
<span class="n">MappedCollection</span><span class="o">.</span><span class="n">__init__</span><span class="p">(</span><span class="bp">self</span><span class="p">,</span> <span class="n">keyfunc</span><span class="o">=</span><span class="k">lambda</span> <span class="n">node</span><span class="p">:</span> <span class="n">node</span><span class="o">.</span><span class="n">name</span><span class="p">)</span>
<span class="n">OrderedDict</span><span class="o">.</span><span class="n">__init__</span><span class="p">(</span><span class="bp">self</span><span class="p">,</span> <span class="o">*</span><span class="n">args</span><span class="p">,</span> <span class="o">**</span><span class="n">kw</span><span class="p">)</span></pre></div>
</div>
<p>The ORM understands the <tt class="docutils literal"><span class="pre">dict</span></tt> interface just like lists and sets, and will automatically instrument all dict-like methods if you choose to subclass <tt class="docutils literal"><span class="pre">dict</span></tt> or provide dict-like collection behavior in a duck-typed class. You must decorate appender and remover methods, however- there are no compatible methods in the basic dictionary interface for SQLAlchemy to use by default. Iteration will go through <tt class="docutils literal"><span class="pre">itervalues()</span></tt> unless otherwise decorated.</p>
</div>
<div class="section" id="instrumentation-and-custom-types">
<h4>Instrumentation and Custom Types<a class="headerlink" href="#instrumentation-and-custom-types" title="Permalink to this headline">¶</a></h4>
<p>Many custom types and existing library classes can be used as a entity collection type as-is without further ado. However, it is important to note that the instrumentation process _will_ modify the type, adding decorators around methods automatically.</p>
<p>The decorations are lightweight and no-op outside of relationships, but they do add unneeded overhead when triggered elsewhere. When using a library class as a collection, it can be good practice to use the “trivial subclass” trick to restrict the decorations to just your usage in relationships. For example:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="k">class</span> <span class="nc">MyAwesomeList</span><span class="p">(</span><span class="n">some</span><span class="o">.</span><span class="n">great</span><span class="o">.</span><span class="n">library</span><span class="o">.</span><span class="n">AwesomeList</span><span class="p">):</span>
<span class="k">pass</span>
<span class="c"># ... relationship(..., collection_class=MyAwesomeList)</span></pre></div>
</div>
<p>The ORM uses this approach for built-ins, quietly substituting a trivial subclass when a <tt class="docutils literal"><span class="pre">list</span></tt>, <tt class="docutils literal"><span class="pre">set</span></tt> or <tt class="docutils literal"><span class="pre">dict</span></tt> is used directly.</p>
<p>The collections package provides additional decorators and support for authoring custom types. See the <a class="reference internal" href="reference/orm/collections.html#module-sqlalchemy.orm.collections"><tt class="xref py py-mod docutils literal"><span class="pre">sqlalchemy.orm.collections</span></tt></a> package for more information and discussion of advanced usage and Python 2.3-compatible decoration options.</p>
</div>
</div>
<div class="section" id="configuring-loader-strategies-lazy-loading-eager-loading">
<span id="mapper-loader-strategies"></span><h3>Configuring Loader Strategies: Lazy Loading, Eager Loading<a class="headerlink" href="#configuring-loader-strategies-lazy-loading-eager-loading" title="Permalink to this headline">¶</a></h3>
<div class="admonition note">
<p class="first admonition-title">Note</p>
<p class="last">SQLAlchemy version 0.6beta3 introduces the <a class="reference internal" href="reference/orm/query.html#sqlalchemy.orm.joinedload" title="sqlalchemy.orm.joinedload"><tt class="xref py py-func docutils literal"><span class="pre">joinedload()</span></tt></a>, <a class="reference internal" href="reference/orm/query.html#sqlalchemy.orm.joinedload_all" title="sqlalchemy.orm.joinedload_all"><tt class="xref py py-func docutils literal"><span class="pre">joinedload_all()</span></tt></a>, <a class="reference internal" href="reference/orm/query.html#sqlalchemy.orm.subqueryload" title="sqlalchemy.orm.subqueryload"><tt class="xref py py-func docutils literal"><span class="pre">subqueryload()</span></tt></a> and <a class="reference internal" href="reference/orm/query.html#sqlalchemy.orm.subqueryload_all" title="sqlalchemy.orm.subqueryload_all"><tt class="xref py py-func docutils literal"><span class="pre">subqueryload_all()</span></tt></a> functions described in this section. In previous versions, including 0.5 and 0.4, use <a class="reference internal" href="reference/orm/query.html#sqlalchemy.orm.eagerload" title="sqlalchemy.orm.eagerload"><tt class="xref py py-func docutils literal"><span class="pre">eagerload()</span></tt></a> and <a class="reference internal" href="reference/orm/query.html#sqlalchemy.orm.eagerload_all" title="sqlalchemy.orm.eagerload_all"><tt class="xref py py-func docutils literal"><span class="pre">eagerload_all()</span></tt></a>. Additionally, the <tt class="docutils literal"><span class="pre">lazy</span></tt> keyword argument on <a class="reference internal" href="reference/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> accepts the values <tt class="xref docutils literal"><span class="pre">True</span></tt>, <tt class="xref docutils literal"><span class="pre">False</span></tt> and <tt class="xref docutils literal"><span class="pre">None</span></tt> in previous versions, whereas in the latest 0.6 it also accepts the arguments <tt class="docutils literal"><span class="pre">select</span></tt>, <tt class="docutils literal"><span class="pre">joined</span></tt>, <tt class="docutils literal"><span class="pre">noload</span></tt>, and <tt class="docutils literal"><span class="pre">subquery</span></tt>.</p>
</div>
<p>In the <a class="reference internal" href="ormtutorial.html"><em>Object Relational Tutorial</em></a>, we introduced the concept of <strong>Eager Loading</strong>. We used an <tt class="docutils literal"><span class="pre">option</span></tt> in conjunction with the <a class="reference internal" href="reference/orm/query.html#sqlalchemy.orm.query.Query" title="sqlalchemy.orm.query.Query"><tt class="xref py py-class docutils literal"><span class="pre">Query</span></tt></a> object in order to indicate that a relationship should be loaded at the same time as the parent, within a single SQL query:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><a href='#' class='sql_link'>sql</a><span class="o">>>></span> <span class="n">jack</span> <span class="o">=</span> <span class="n">session</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="n">User</span><span class="p">)</span><span class="o">.</span><span class="n">options</span><span class="p">(</span><span class="n">joinedload</span><span class="p">(</span><span class="s">'addresses'</span><span class="p">))</span><span class="o">.</span><span class="n">filter_by</span><span class="p">(</span><span class="n">name</span><span class="o">=</span><span class="s">'jack'</span><span class="p">)</span><span class="o">.</span><span class="n">all</span><span class="p">()</span>
<div class='popup_sql'>SELECT addresses_1.id AS addresses_1_id, addresses_1.email_address AS addresses_1_email_address,
addresses_1.user_id AS addresses_1_user_id, users.id AS users_id, users.name AS users_name,
users.fullname AS users_fullname, users.password AS users_password
FROM users LEFT OUTER JOIN addresses AS addresses_1 ON users.id = addresses_1.user_id
WHERE users.name = ?
['jack']</div></pre></div>
</div>
<p>By default, all inter-object relationships are <strong>lazy loading</strong>. The scalar or collection attribute associated with a <a class="reference internal" href="reference/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> contains a trigger which fires the first time the attribute is accessed, which issues a SQL call at that point:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><a href='#' class='sql_link'>sql</a><span class="o">>>></span> <span class="n">jack</span><span class="o">.</span><span class="n">addresses</span>
<div class='popup_sql'>SELECT addresses.id AS addresses_id, addresses.email_address AS addresses_email_address,
addresses.user_id AS addresses_user_id
FROM addresses
WHERE ? = addresses.user_id
[5]</div><span class="p">[</span><span class="o"><</span><span class="n">Address</span><span class="p">(</span><span class="s">u'jack@google.com'</span><span class="p">)</span><span class="o">></span><span class="p">,</span> <span class="o"><</span><span class="n">Address</span><span class="p">(</span><span class="s">u'j25@yahoo.com'</span><span class="p">)</span><span class="o">></span><span class="p">]</span></pre></div>
</div>
<p>A second option for eager loading exists, called “subquery” loading. This kind of eager loading emits an additional SQL statement for each collection requested, aggregated across all parent objects:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><a href='#' class='sql_link'>sql</a><span class="o">>>></span><span class="n">jack</span> <span class="o">=</span> <span class="n">session</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="n">User</span><span class="p">)</span><span class="o">.</span><span class="n">options</span><span class="p">(</span><span class="n">subqueryload</span><span class="p">(</span><span class="s">'addresses'</span><span class="p">))</span><span class="o">.</span><span class="n">filter_by</span><span class="p">(</span><span class="n">name</span><span class="o">=</span><span class="s">'jack'</span><span class="p">)</span><span class="o">.</span><span class="n">all</span><span class="p">()</span>
<div class='popup_sql'>SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname,
users.password AS users_password
FROM users
WHERE users.name = ?
('jack',)
SELECT addresses.id AS addresses_id, addresses.email_address AS addresses_email_address,
addresses.user_id AS addresses_user_id, anon_1.users_id AS anon_1_users_id
FROM (SELECT users.id AS users_id
FROM users
WHERE users.name = ?) AS anon_1 JOIN addresses ON anon_1.users_id = addresses.user_id
ORDER BY anon_1.users_id, addresses.id
('jack',)</div></pre></div>
</div>
<p>The default <strong>loader strategy</strong> for any <a class="reference internal" href="reference/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> is configured by the <tt class="docutils literal"><span class="pre">lazy</span></tt> keyword argument, which defaults to <tt class="docutils literal"><span class="pre">select</span></tt>. Below we set it as <tt class="docutils literal"><span class="pre">joined</span></tt> so that the <tt class="docutils literal"><span class="pre">children</span></tt> relationship is eager loading, using a join:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="c"># load the 'children' collection using LEFT OUTER JOIN</span>
<span class="n">mapper</span><span class="p">(</span><span class="n">Parent</span><span class="p">,</span> <span class="n">parent_table</span><span class="p">,</span> <span class="n">properties</span><span class="o">=</span><span class="p">{</span>
<span class="s">'children'</span><span class="p">:</span> <span class="n">relationship</span><span class="p">(</span><span class="n">Child</span><span class="p">,</span> <span class="n">lazy</span><span class="o">=</span><span class="s">'joined'</span><span class="p">)</span>
<span class="p">})</span></pre></div>
</div>
<p>We can also set it to eagerly load using a second query for all collections, using <tt class="docutils literal"><span class="pre">subquery</span></tt>:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="c"># load the 'children' attribute using a join to a subquery</span>
<span class="n">mapper</span><span class="p">(</span><span class="n">Parent</span><span class="p">,</span> <span class="n">parent_table</span><span class="p">,</span> <span class="n">properties</span><span class="o">=</span><span class="p">{</span>
<span class="s">'children'</span><span class="p">:</span> <span class="n">relationship</span><span class="p">(</span><span class="n">Child</span><span class="p">,</span> <span class="n">lazy</span><span class="o">=</span><span class="s">'subquery'</span><span class="p">)</span>
<span class="p">})</span></pre></div>
</div>
<p>When querying, all three choices of loader strategy are available on a per-query basis, using the <a class="reference internal" href="reference/orm/query.html#sqlalchemy.orm.joinedload" title="sqlalchemy.orm.joinedload"><tt class="xref py py-func docutils literal"><span class="pre">joinedload()</span></tt></a>, <a class="reference internal" href="reference/orm/query.html#sqlalchemy.orm.subqueryload" title="sqlalchemy.orm.subqueryload"><tt class="xref py py-func docutils literal"><span class="pre">subqueryload()</span></tt></a> and <a class="reference internal" href="reference/orm/query.html#sqlalchemy.orm.lazyload" title="sqlalchemy.orm.lazyload"><tt class="xref py py-func docutils literal"><span class="pre">lazyload()</span></tt></a> query options:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="c"># set children to load lazily</span>
<span class="n">session</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="n">Parent</span><span class="p">)</span><span class="o">.</span><span class="n">options</span><span class="p">(</span><span class="n">lazyload</span><span class="p">(</span><span class="s">'children'</span><span class="p">))</span><span class="o">.</span><span class="n">all</span><span class="p">()</span>
<span class="c"># set children to load eagerly with a join</span>
<span class="n">session</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="n">Parent</span><span class="p">)</span><span class="o">.</span><span class="n">options</span><span class="p">(</span><span class="n">joinedload</span><span class="p">(</span><span class="s">'children'</span><span class="p">))</span><span class="o">.</span><span class="n">all</span><span class="p">()</span>
<span class="c"># set children to load eagerly with a second statement</span>
<span class="n">session</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="n">Parent</span><span class="p">)</span><span class="o">.</span><span class="n">options</span><span class="p">(</span><span class="n">subqueryload</span><span class="p">(</span><span class="s">'children'</span><span class="p">))</span><span class="o">.</span><span class="n">all</span><span class="p">()</span></pre></div>
</div>
<p>To reference a relationship that is deeper than one level, separate the names by periods:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="n">session</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="n">Parent</span><span class="p">)</span><span class="o">.</span><span class="n">options</span><span class="p">(</span><span class="n">joinedload</span><span class="p">(</span><span class="s">'foo.bar.bat'</span><span class="p">))</span><span class="o">.</span><span class="n">all</span><span class="p">()</span></pre></div>
</div>
<p>When using dot-separated names with <a class="reference internal" href="reference/orm/query.html#sqlalchemy.orm.joinedload" title="sqlalchemy.orm.joinedload"><tt class="xref py py-func docutils literal"><span class="pre">joinedload()</span></tt></a> or <a class="reference internal" href="reference/orm/query.html#sqlalchemy.orm.subqueryload" title="sqlalchemy.orm.subqueryload"><tt class="xref py py-func docutils literal"><span class="pre">subqueryload()</span></tt></a>, option applies <strong>only</strong> to the actual attribute named, and <strong>not</strong> its ancestors. For example, suppose a mapping from <tt class="docutils literal"><span class="pre">A</span></tt> to <tt class="docutils literal"><span class="pre">B</span></tt> to <tt class="docutils literal"><span class="pre">C</span></tt>, where the relationships, named <tt class="docutils literal"><span class="pre">atob</span></tt> and <tt class="docutils literal"><span class="pre">btoc</span></tt>, are both lazy-loading. A statement like the following:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="n">session</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="n">A</span><span class="p">)</span><span class="o">.</span><span class="n">options</span><span class="p">(</span><span class="n">joinedload</span><span class="p">(</span><span class="s">'atob.btoc'</span><span class="p">))</span><span class="o">.</span><span class="n">all</span><span class="p">()</span></pre></div>
</div>
<p>will load only <tt class="docutils literal"><span class="pre">A</span></tt> objects to start. When the <tt class="docutils literal"><span class="pre">atob</span></tt> attribute on each <tt class="docutils literal"><span class="pre">A</span></tt> is accessed, the returned <tt class="docutils literal"><span class="pre">B</span></tt> objects will <em>eagerly</em> load their <tt class="docutils literal"><span class="pre">C</span></tt> objects.</p>
<p>Therefore, to modify the eager load to load both <tt class="docutils literal"><span class="pre">atob</span></tt> as well as <tt class="docutils literal"><span class="pre">btoc</span></tt>, place joinedloads for both:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="n">session</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="n">A</span><span class="p">)</span><span class="o">.</span><span class="n">options</span><span class="p">(</span><span class="n">joinedload</span><span class="p">(</span><span class="s">'atob'</span><span class="p">),</span> <span class="n">joinedload</span><span class="p">(</span><span class="s">'atob.btoc'</span><span class="p">))</span><span class="o">.</span><span class="n">all</span><span class="p">()</span></pre></div>
</div>
<p>or more simply just use <a class="reference internal" href="reference/orm/query.html#sqlalchemy.orm.joinedload_all" title="sqlalchemy.orm.joinedload_all"><tt class="xref py py-func docutils literal"><span class="pre">joinedload_all()</span></tt></a> or <a class="reference internal" href="reference/orm/query.html#sqlalchemy.orm.subqueryload_all" title="sqlalchemy.orm.subqueryload_all"><tt class="xref py py-func docutils literal"><span class="pre">subqueryload_all()</span></tt></a>:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="n">session</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="n">A</span><span class="p">)</span><span class="o">.</span><span class="n">options</span><span class="p">(</span><span class="n">joinedload_all</span><span class="p">(</span><span class="s">'atob.btoc'</span><span class="p">))</span><span class="o">.</span><span class="n">all</span><span class="p">()</span></pre></div>
</div>
<p>There are two other loader strategies available, <strong>dynamic loading</strong> and <strong>no loading</strong>; these are described in <a class="reference internal" href="#largecollections"><em>Working with Large Collections</em></a>.</p>
<div class="section" id="what-kind-of-loading-to-use">
<h4>What Kind of Loading to Use ?<a class="headerlink" href="#what-kind-of-loading-to-use" title="Permalink to this headline">¶</a></h4>
<p>Which type of loading to use typically comes down to optimizing the tradeoff between number of SQL executions, complexity of SQL emitted, and amount of data fetched. Lets take two examples, a <a class="reference internal" href="reference/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> which references a collection, and a <a class="reference internal" href="reference/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> that references a scalar many-to-one reference.</p>
<ul class="simple">
<li>One to Many Collection</li>
</ul>
<blockquote>
<ul class="simple">
<li>When using the default lazy loading, if you load 100 objects, and then access a collection on each of
them, a total of 101 SQL statements will be emitted, although each statement will typically be a
simple SELECT without any joins.</li>
<li>When using joined loading, the load of 100 objects and their collections will emit only one SQL
statement. However, the
total number of rows fetched will be equal to the sum of the size of all the collections, plus one
extra row for each parent object that has an empty collection. Each row will also contain the full
set of columns represented by the parents, repeated for each collection item - SQLAlchemy does not
re-fetch these columns other than those of the primary key, however most DBAPIs (with some
exceptions) will transmit the full data of each parent over the wire to the client connection in
any case. Therefore joined eager loading only makes sense when the size of the collections are
relatively small. The LEFT OUTER JOIN can also be performance intensive compared to an INNER join.</li>
<li>When using subquery loading, the load of 100 objects will emit two SQL statements. The second
statement will fetch a total number of rows equal to the sum of the size of all collections. An
INNER JOIN is used, and a minimum of parent columns are requested, only the primary keys. So a
subquery load makes sense when the collections are larger.</li>
<li>When multiple levels of depth are used with joined or subquery loading, loading collections-within-
collections will multiply the total number of rows fetched in a cartesian fashion. Both forms
of eager loading always join from the original parent class.</li>
</ul>
</blockquote>
<ul class="simple">
<li>Many to One Reference</li>
</ul>
<blockquote>
<ul class="simple">
<li>When using the default lazy loading, a load of 100 objects will like in the case of the collection
emit as many as 101 SQL statements. However - there is a significant exception to this, in that
if the many-to-one reference is a simple foreign key reference to the target’s primary key, each
reference will be checked first in the current identity map using <tt class="docutils literal"><span class="pre">query.get()</span></tt>. So here,
if the collection of objects references a relatively small set of target objects, or the full set
of possible target objects have already been loaded into the session and are strongly referenced,
using the default of <cite>lazy=’select’</cite> is by far the most efficient way to go.</li>
<li>When using joined loading, the load of 100 objects will emit only one SQL statement. The join
will be a LEFT OUTER JOIN, and the total number of rows will be equal to 100 in all cases.
If you know that each parent definitely has a child (i.e. the foreign
key reference is NOT NULL), the joined load can be configured with <tt class="docutils literal"><span class="pre">innerjoin=True</span></tt>, which is
usually specified within the <a class="reference internal" href="reference/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>. For a load of objects where
there are many possible target references which may have not been loaded already, joined loading
with an INNER JOIN is extremely efficient.</li>
<li>Subquery loading will issue a second load for all the child objects, so for a load of 100 objects
there would be two SQL statements emitted. There’s probably not much advantage here over
joined loading, however, except perhaps that subquery loading can use an INNER JOIN in all cases
whereas joined loading requires that the foreign key is NOT NULL.</li>
</ul>
</blockquote>
</div>
<div class="section" id="routing-explicit-joins-statements-into-eagerly-loaded-collections">
<h4>Routing Explicit Joins/Statements into Eagerly Loaded Collections<a class="headerlink" href="#routing-explicit-joins-statements-into-eagerly-loaded-collections" title="Permalink to this headline">¶</a></h4>
<p>The behavior of <a class="reference internal" href="reference/orm/query.html#sqlalchemy.orm.joinedload" title="sqlalchemy.orm.joinedload"><tt class="xref py py-func docutils literal"><span class="pre">joinedload()</span></tt></a> is such that joins are created automatically, the results of which are routed into collections and scalar references on loaded objects. It is often the case that a query already includes the necessary joins which represent a particular collection or scalar reference, and the joins added by the joinedload feature are redundant - yet you’d still like the collections/references to be populated.</p>
<p>For this SQLAlchemy supplies the <a class="reference internal" href="reference/orm/query.html#sqlalchemy.orm.contains_eager" title="sqlalchemy.orm.contains_eager"><tt class="xref py py-func docutils literal"><span class="pre">contains_eager()</span></tt></a> option. This option is used in the same manner as the <a class="reference internal" href="reference/orm/query.html#sqlalchemy.orm.joinedload" title="sqlalchemy.orm.joinedload"><tt class="xref py py-func docutils literal"><span class="pre">joinedload()</span></tt></a> option except it is assumed that the <a class="reference internal" href="reference/orm/query.html#sqlalchemy.orm.query.Query" title="sqlalchemy.orm.query.Query"><tt class="xref py py-class docutils literal"><span class="pre">Query</span></tt></a> will specify the appropriate joins explicitly. Below it’s used with a <tt class="docutils literal"><span class="pre">from_statement</span></tt> load:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="c"># mapping is the users->addresses mapping</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">'addresses'</span><span class="p">:</span> <span class="n">relationship</span><span class="p">(</span><span class="n">Address</span><span class="p">,</span> <span class="n">addresses_table</span><span class="p">)</span>
<span class="p">})</span>
<span class="c"># define a query on USERS with an outer join to ADDRESSES</span>
<span class="n">statement</span> <span class="o">=</span> <span class="n">users_table</span><span class="o">.</span><span class="n">outerjoin</span><span class="p">(</span><span class="n">addresses_table</span><span class="p">)</span><span class="o">.</span><span class="n">select</span><span class="p">()</span><span class="o">.</span><span class="n">apply_labels</span><span class="p">()</span>
<span class="c"># construct a Query object which expects the "addresses" results</span>
<span class="n">query</span> <span class="o">=</span> <span class="n">session</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="n">User</span><span class="p">)</span><span class="o">.</span><span class="n">options</span><span class="p">(</span><span class="n">contains_eager</span><span class="p">(</span><span class="s">'addresses'</span><span class="p">))</span>
<span class="c"># get results normally</span>
<span class="n">r</span> <span class="o">=</span> <span class="n">query</span><span class="o">.</span><span class="n">from_statement</span><span class="p">(</span><span class="n">statement</span><span class="p">)</span></pre></div>
</div>
<p>It works just as well with an inline <tt class="docutils literal"><span class="pre">Query.join()</span></tt> or <tt class="docutils literal"><span class="pre">Query.outerjoin()</span></tt>:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">session</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="n">User</span><span class="p">)</span><span class="o">.</span><span class="n">outerjoin</span><span class="p">(</span><span class="n">User</span><span class="o">.</span><span class="n">addresses</span><span class="p">)</span><span class="o">.</span><span class="n">options</span><span class="p">(</span><span class="n">contains_eager</span><span class="p">(</span><span class="n">User</span><span class="o">.</span><span class="n">addresses</span><span class="p">))</span><span class="o">.</span><span class="n">all</span><span class="p">()</span></pre></div>
</div>
<p>If the “eager” portion of the statement is “aliased”, the <tt class="docutils literal"><span class="pre">alias</span></tt> keyword argument to <a class="reference internal" href="reference/orm/query.html#sqlalchemy.orm.contains_eager" title="sqlalchemy.orm.contains_eager"><tt class="xref py py-func docutils literal"><span class="pre">contains_eager()</span></tt></a> may be used to indicate it. This is a string alias name or reference to an actual <a class="reference internal" href="reference/sqlalchemy/expressions.html#sqlalchemy.sql.expression.Alias" title="sqlalchemy.sql.expression.Alias"><tt class="xref py py-class docutils literal"><span class="pre">Alias</span></tt></a> (or other selectable) object:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="c"># use an alias of the Address entity</span>
<span class="n">adalias</span> <span class="o">=</span> <span class="n">aliased</span><span class="p">(</span><span class="n">Address</span><span class="p">)</span>
<span class="c"># construct a Query object which expects the "addresses" results</span>
<span class="n">query</span> <span class="o">=</span> <span class="n">session</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="n">User</span><span class="p">)</span><span class="o">.</span>\
<span class="n">outerjoin</span><span class="p">((</span><span class="n">adalias</span><span class="p">,</span> <span class="n">User</span><span class="o">.</span><span class="n">addresses</span><span class="p">))</span><span class="o">.</span>\
<span class="n">options</span><span class="p">(</span><span class="n">contains_eager</span><span class="p">(</span><span class="n">User</span><span class="o">.</span><span class="n">addresses</span><span class="p">,</span> <span class="n">alias</span><span class="o">=</span><span class="n">adalias</span><span class="p">))</span>
<span class="c"># get results normally</span>
<a href='#' class='sql_link'>sql</a><span class="n">r</span> <span class="o">=</span> <span class="n">query</span><span class="o">.</span><span class="n">all</span><span class="p">()</span>
<div class='popup_sql'>SELECT users.user_id AS users_user_id, users.user_name AS users_user_name, adalias.address_id AS adalias_address_id,
adalias.user_id AS adalias_user_id, adalias.email_address AS adalias_email_address, (...other columns...)
FROM users LEFT OUTER JOIN email_addresses AS email_addresses_1 ON users.user_id = email_addresses_1.user_id</div></pre></div>
</div>
<p>The <tt class="docutils literal"><span class="pre">alias</span></tt> argument is used only as a source of columns to match up to the result set. You can use it even to match up the result to arbitrary label names in a string SQL statement, by passing a selectable() which links those labels to the mapped <a class="reference internal" href="reference/sqlalchemy/schema.html#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal"><span class="pre">Table</span></tt></a>:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="c"># label the columns of the addresses table</span>
<span class="n">eager_columns</span> <span class="o">=</span> <span class="n">select</span><span class="p">([</span>
<span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">address_id</span><span class="o">.</span><span class="n">label</span><span class="p">(</span><span class="s">'a1'</span><span class="p">),</span>
<span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">email_address</span><span class="o">.</span><span class="n">label</span><span class="p">(</span><span class="s">'a2'</span><span class="p">),</span>
<span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">user_id</span><span class="o">.</span><span class="n">label</span><span class="p">(</span><span class="s">'a3'</span><span class="p">)])</span>
<span class="c"># select from a raw SQL statement which uses those label names for the</span>
<span class="c"># addresses table. contains_eager() matches them up.</span>
<span class="n">query</span> <span class="o">=</span> <span class="n">session</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="n">User</span><span class="p">)</span><span class="o">.</span>\
<span class="n">from_statement</span><span class="p">(</span><span class="s">"select users.*, addresses.address_id as a1, "</span>
<span class="s">"addresses.email_address as a2, addresses.user_id as a3 "</span>
<span class="s">"from users left outer join addresses on users.user_id=addresses.user_id"</span><span class="p">)</span><span class="o">.</span>\
<span class="n">options</span><span class="p">(</span><span class="n">contains_eager</span><span class="p">(</span><span class="n">User</span><span class="o">.</span><span class="n">addresses</span><span class="p">,</span> <span class="n">alias</span><span class="o">=</span><span class="n">eager_columns</span><span class="p">))</span></pre></div>
</div>
<p>The path given as the argument to <a class="reference internal" href="reference/orm/query.html#sqlalchemy.orm.contains_eager" title="sqlalchemy.orm.contains_eager"><tt class="xref py py-func docutils literal"><span class="pre">contains_eager()</span></tt></a> needs to be a full path from the starting entity. For example if we were loading <tt class="docutils literal"><span class="pre">Users->orders->Order->items->Item</span></tt>, the string version would look like:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">query</span><span class="p">(</span><span class="n">User</span><span class="p">)</span><span class="o">.</span><span class="n">options</span><span class="p">(</span><span class="n">contains_eager</span><span class="p">(</span><span class="s">'orders'</span><span class="p">,</span> <span class="s">'items'</span><span class="p">))</span></pre></div>
</div>
<p>Or using the class-bound descriptor:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">query</span><span class="p">(</span><span class="n">User</span><span class="p">)</span><span class="o">.</span><span class="n">options</span><span class="p">(</span><span class="n">contains_eager</span><span class="p">(</span><span class="n">User</span><span class="o">.</span><span class="n">orders</span><span class="p">,</span> <span class="n">Order</span><span class="o">.</span><span class="n">items</span><span class="p">))</span></pre></div>
</div>
<p>A variant on <a class="reference internal" href="reference/orm/query.html#sqlalchemy.orm.contains_eager" title="sqlalchemy.orm.contains_eager"><tt class="xref py py-func docutils literal"><span class="pre">contains_eager()</span></tt></a> is the <tt class="docutils literal"><span class="pre">contains_alias()</span></tt> option, which is used in the rare case that the parent object is loaded from an alias within a user-defined SELECT statement:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="c"># define an aliased UNION called 'ulist'</span>
<span class="n">statement</span> <span class="o">=</span> <span class="n">users</span><span class="o">.</span><span class="n">select</span><span class="p">(</span><span class="n">users</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">user_id</span><span class="o">==</span><span class="mi">7</span><span class="p">)</span><span class="o">.</span><span class="n">union</span><span class="p">(</span><span class="n">users</span><span class="o">.</span><span class="n">select</span><span class="p">(</span><span class="n">users</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">user_id</span><span class="o">></span><span class="mi">7</span><span class="p">))</span><span class="o">.</span><span class="n">alias</span><span class="p">(</span><span class="s">'ulist'</span><span class="p">)</span>
<span class="c"># add on an eager load of "addresses"</span>
<span class="n">statement</span> <span class="o">=</span> <span class="n">statement</span><span class="o">.</span><span class="n">outerjoin</span><span class="p">(</span><span class="n">addresses</span><span class="p">)</span><span class="o">.</span><span class="n">select</span><span class="p">()</span><span class="o">.</span><span class="n">apply_labels</span><span class="p">()</span>
<span class="c"># create query, indicating "ulist" is an alias for the main table, "addresses" property should</span>
<span class="c"># be eager loaded</span>
<span class="n">query</span> <span class="o">=</span> <span class="n">session</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="n">User</span><span class="p">)</span><span class="o">.</span><span class="n">options</span><span class="p">(</span><span class="n">contains_alias</span><span class="p">(</span><span class="s">'ulist'</span><span class="p">),</span> <span class="n">contains_eager</span><span class="p">(</span><span class="s">'addresses'</span><span class="p">))</span>
<span class="c"># results</span>
<span class="n">r</span> <span class="o">=</span> <span class="n">query</span><span class="o">.</span><span class="n">from_statement</span><span class="p">(</span><span class="n">statement</span><span class="p">)</span></pre></div>
</div>
</div>
</div>
<div class="section" id="working-with-large-collections">
<span id="largecollections"></span><h3>Working with Large Collections<a class="headerlink" href="#working-with-large-collections" title="Permalink to this headline">¶</a></h3>
<p>The default behavior of <a class="reference internal" href="reference/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> is to fully load the collection of items in, as according to the loading strategy of the relationship. Additionally, the Session by default only knows how to delete objects which are actually present within the session. When a parent instance is marked for deletion and flushed, the Session loads its full list of child items in so that they may either be deleted as well, or have their foreign key value set to null; this is to avoid constraint violations. For large collections of child items, there are several strategies to bypass full loading of child items both at load time as well as deletion time.</p>
<div class="section" id="dynamic-relationship-loaders">
<h4>Dynamic Relationship Loaders<a class="headerlink" href="#dynamic-relationship-loaders" title="Permalink to this headline">¶</a></h4>
<p>The most useful by far is the <a class="reference internal" href="reference/orm/mapping.html#sqlalchemy.orm.dynamic_loader" title="sqlalchemy.orm.dynamic_loader"><tt class="xref py py-func docutils literal"><span class="pre">dynamic_loader()</span></tt></a> relationship. This is a variant of <a class="reference internal" href="reference/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> which returns a <a class="reference internal" href="reference/orm/query.html#sqlalchemy.orm.query.Query" title="sqlalchemy.orm.query.Query"><tt class="xref py py-class docutils literal"><span class="pre">Query</span></tt></a> object in place of a collection when accessed. <a class="reference internal" href="reference/orm/query.html#sqlalchemy.orm.query.Query.filter" title="sqlalchemy.orm.query.Query.filter"><tt class="xref py py-func docutils literal"><span class="pre">filter()</span></tt></a> criterion may be applied as well as limits and offsets, either explicitly or via array slices:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><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">'posts'</span><span class="p">:</span> <span class="n">dynamic_loader</span><span class="p">(</span><span class="n">Post</span><span class="p">)</span>
<span class="p">})</span>
<span class="n">jack</span> <span class="o">=</span> <span class="n">session</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="n">User</span><span class="p">)</span><span class="o">.</span><span class="n">get</span><span class="p">(</span><span class="nb">id</span><span class="p">)</span>
<span class="c"># filter Jack's blog posts</span>
<span class="n">posts</span> <span class="o">=</span> <span class="n">jack</span><span class="o">.</span><span class="n">posts</span><span class="o">.</span><span class="n">filter</span><span class="p">(</span><span class="n">Post</span><span class="o">.</span><span class="n">headline</span><span class="o">==</span><span class="s">'this is a post'</span><span class="p">)</span>
<span class="c"># apply array slices</span>
<span class="n">posts</span> <span class="o">=</span> <span class="n">jack</span><span class="o">.</span><span class="n">posts</span><span class="p">[</span><span class="mi">5</span><span class="p">:</span><span class="mi">20</span><span class="p">]</span></pre></div>
</div>
<p>The dynamic relationship supports limited write operations, via the <tt class="docutils literal"><span class="pre">append()</span></tt> and <tt class="docutils literal"><span class="pre">remove()</span></tt> methods. Since the read side of the dynamic relationship always queries the database, changes to the underlying collection will not be visible until the data has been flushed:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="n">oldpost</span> <span class="o">=</span> <span class="n">jack</span><span class="o">.</span><span class="n">posts</span><span class="o">.</span><span class="n">filter</span><span class="p">(</span><span class="n">Post</span><span class="o">.</span><span class="n">headline</span><span class="o">==</span><span class="s">'old post'</span><span class="p">)</span><span class="o">.</span><span class="n">one</span><span class="p">()</span>
<span class="n">jack</span><span class="o">.</span><span class="n">posts</span><span class="o">.</span><span class="n">remove</span><span class="p">(</span><span class="n">oldpost</span><span class="p">)</span>
<span class="n">jack</span><span class="o">.</span><span class="n">posts</span><span class="o">.</span><span class="n">append</span><span class="p">(</span><span class="n">Post</span><span class="p">(</span><span class="s">'new post'</span><span class="p">))</span></pre></div>
</div>
<p>To place a dynamic relationship on a backref, use <tt class="docutils literal"><span class="pre">lazy='dynamic'</span></tt>:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="n">mapper</span><span class="p">(</span><span class="n">Post</span><span class="p">,</span> <span class="n">posts_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="n">backref</span><span class="p">(</span><span class="s">'posts'</span><span class="p">,</span> <span class="n">lazy</span><span class="o">=</span><span class="s">'dynamic'</span><span class="p">))</span>
<span class="p">})</span></pre></div>
</div>
<p>Note that eager/lazy loading options cannot be used in conjunction dynamic relationships at this time.</p>
</div>
<div class="section" id="setting-noload">
<h4>Setting Noload<a class="headerlink" href="#setting-noload" title="Permalink to this headline">¶</a></h4>
<p>The opposite of the dynamic relationship is simply “noload”, specified using <tt class="docutils literal"><span class="pre">lazy='noload'</span></tt>:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="n">mapper</span><span class="p">(</span><span class="n">MyClass</span><span class="p">,</span> <span class="n">table</span><span class="p">,</span> <span class="n">properties</span><span class="o">=</span><span class="p">{</span>
<span class="s">'children'</span><span class="p">:</span> <span class="n">relationship</span><span class="p">(</span><span class="n">MyOtherClass</span><span class="p">,</span> <span class="n">lazy</span><span class="o">=</span><span class="s">'noload'</span><span class="p">)</span>
<span class="p">})</span></pre></div>
</div>
<p>Above, the <tt class="docutils literal"><span class="pre">children</span></tt> collection is fully writeable, and changes to it will be persisted to the database as well as locally available for reading at the time they are added. However when instances of <tt class="docutils literal"><span class="pre">MyClass</span></tt> are freshly loaded from the database, the <tt class="docutils literal"><span class="pre">children</span></tt> collection stays empty.</p>
</div>
<div class="section" id="using-passive-deletes">
<h4>Using Passive Deletes<a class="headerlink" href="#using-passive-deletes" title="Permalink to this headline">¶</a></h4>
<p>Use <tt class="docutils literal"><span class="pre">passive_deletes=True</span></tt> to disable child object loading on a DELETE operation, in conjunction with “ON DELETE (CASCADE|SET NULL)” on your database to automatically cascade deletes to child objects. Note that “ON DELETE” is not supported on SQLite, and requires <tt class="docutils literal"><span class="pre">InnoDB</span></tt> tables when using MySQL:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="n">mytable</span> <span class="o">=</span> <span class="n">Table</span><span class="p">(</span><span class="s">'mytable'</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="p">)</span>
<span class="n">myothertable</span> <span class="o">=</span> <span class="n">Table</span><span class="p">(</span><span class="s">'myothertable'</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">'parent_id'</span><span class="p">,</span> <span class="n">Integer</span><span class="p">),</span>
<span class="n">ForeignKeyConstraint</span><span class="p">([</span><span class="s">'parent_id'</span><span class="p">],</span> <span class="p">[</span><span class="s">'mytable.id'</span><span class="p">],</span> <span class="n">ondelete</span><span class="o">=</span><span class="s">"CASCADE"</span><span class="p">),</span>
<span class="p">)</span>
<span class="n">mapper</span><span class="p">(</span><span class="n">MyOtherClass</span><span class="p">,</span> <span class="n">myothertable</span><span class="p">)</span>
<span class="n">mapper</span><span class="p">(</span><span class="n">MyClass</span><span class="p">,</span> <span class="n">mytable</span><span class="p">,</span> <span class="n">properties</span><span class="o">=</span><span class="p">{</span>
<span class="s">'children'</span><span class="p">:</span> <span class="n">relationship</span><span class="p">(</span><span class="n">MyOtherClass</span><span class="p">,</span> <span class="n">cascade</span><span class="o">=</span><span class="s">"all, delete-orphan"</span><span class="p">,</span> <span class="n">passive_deletes</span><span class="o">=</span><span class="bp">True</span><span class="p">)</span>
<span class="p">})</span></pre></div>
</div>
<p>When <tt class="docutils literal"><span class="pre">passive_deletes</span></tt> is applied, the <tt class="docutils literal"><span class="pre">children</span></tt> relationship will not be loaded into memory when an instance of <tt class="docutils literal"><span class="pre">MyClass</span></tt> is marked for deletion. The <tt class="docutils literal"><span class="pre">cascade="all,</span> <span class="pre">delete-orphan"</span></tt> <em>will</em> take effect for instances of <tt class="docutils literal"><span class="pre">MyOtherClass</span></tt> which are currently present in the session; however for instances of <tt class="docutils literal"><span class="pre">MyOtherClass</span></tt> which are not loaded, SQLAlchemy assumes that “ON DELETE CASCADE” rules will ensure that those rows are deleted by the database and that no foreign key violation will occur.</p>
</div>
</div>
<div class="section" id="mutable-primary-keys-update-cascades">
<h3>Mutable Primary Keys / Update Cascades<a class="headerlink" href="#mutable-primary-keys-update-cascades" title="Permalink to this headline">¶</a></h3>
<p>When the primary key of an entity changes, related items which reference the primary key must also be updated as well. For databases which enforce referential integrity, it’s required to use the database’s ON UPDATE CASCADE functionality in order to propagate primary key changes. For those which don’t, the <tt class="docutils literal"><span class="pre">passive_updates</span></tt> flag can be set to <tt class="xref docutils literal"><span class="pre">False</span></tt> which instructs SQLAlchemy to issue UPDATE statements individually. The <tt class="docutils literal"><span class="pre">passive_updates</span></tt> flag can also be <tt class="xref docutils literal"><span class="pre">False</span></tt> in conjunction with ON UPDATE CASCADE functionality, although in that case it issues UPDATE statements unnecessarily.</p>
<p>A typical mutable primary key setup might look like:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="n">users</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">'username'</span><span class="p">,</span> <span class="n">String</span><span class="p">(</span><span class="mi">50</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">'fullname'</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">addresses</span> <span class="o">=</span> <span class="n">Table</span><span class="p">(</span><span class="s">'addresses'</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">'email'</span><span class="p">,</span> <span class="n">String</span><span class="p">(</span><span class="mi">50</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">'username'</span><span class="p">,</span> <span class="n">String</span><span class="p">(</span><span class="mi">50</span><span class="p">),</span> <span class="n">ForeignKey</span><span class="p">(</span><span class="s">'users.username'</span><span class="p">,</span> <span class="n">onupdate</span><span class="o">=</span><span class="s">"cascade"</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">pass</span>
<span class="k">class</span> <span class="nc">Address</span><span class="p">(</span><span class="nb">object</span><span class="p">):</span>
<span class="k">pass</span>
<span class="n">mapper</span><span class="p">(</span><span class="n">User</span><span class="p">,</span> <span class="n">users</span><span class="p">,</span> <span class="n">properties</span><span class="o">=</span><span class="p">{</span>
<span class="s">'addresses'</span><span class="p">:</span> <span class="n">relationship</span><span class="p">(</span><span class="n">Address</span><span class="p">,</span> <span class="n">passive_updates</span><span class="o">=</span><span class="bp">False</span><span class="p">)</span>
<span class="p">})</span>
<span class="n">mapper</span><span class="p">(</span><span class="n">Address</span><span class="p">,</span> <span class="n">addresses</span><span class="p">)</span></pre></div>
</div>
<p>passive_updates is set to <tt class="xref docutils literal"><span class="pre">True</span></tt> by default. Foreign key references to non-primary key columns are supported as well.</p>
</div>
</div>
</div>
</div>
</div>
<div class="bottomnav">
<div class="prevnext">
Previous:
<a href="sqlexpression.html" title="previous chapter">SQL Expression Language Tutorial</a>
Next:
<a href="session.html" title="next chapter">Using the Session</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>
|
