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
|
<!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>
Object Relational Tutorial
— 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="SQL Expression Language Tutorial" href="sqlexpression.html" />
<link rel="prev" title="Overview / Installation" href="intro.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/ormtutorial.txt">view source)</div>
</div>
<div class="navbanner">
<a class="totoc" href="index.html">Table of Contents</a>
»
Object Relational Tutorial
<div class="prevnext">
Previous:
<a href="intro.html" title="previous chapter">Overview / Installation</a>
Next:
<a href="sqlexpression.html" title="next chapter">SQL Expression Language Tutorial</a>
</div>
<h2>
Object Relational Tutorial
</h2>
</div>
<ul>
<li><a class="reference internal" href="#">Object Relational Tutorial</a><ul>
<li><a class="reference internal" href="#version-check">Version Check</a></li>
<li><a class="reference internal" href="#connecting">Connecting</a></li>
<li><a class="reference internal" href="#define-and-create-a-table">Define and Create a Table</a></li>
<li><a class="reference internal" href="#define-a-python-class-to-be-mapped">Define a Python Class to be Mapped</a></li>
<li><a class="reference internal" href="#setting-up-the-mapping">Setting up the Mapping</a></li>
<li><a class="reference internal" href="#creating-table-class-and-mapper-all-at-once-declaratively">Creating Table, Class and Mapper All at Once Declaratively</a></li>
<li><a class="reference internal" href="#creating-a-session">Creating a Session</a></li>
<li><a class="reference internal" href="#adding-new-objects">Adding new Objects</a></li>
<li><a class="reference internal" href="#rolling-back">Rolling Back</a></li>
<li><a class="reference internal" href="#querying">Querying</a><ul>
<li><a class="reference internal" href="#common-filter-operators">Common Filter Operators</a></li>
<li><a class="reference internal" href="#returning-lists-and-scalars">Returning Lists and Scalars</a></li>
<li><a class="reference internal" href="#using-literal-sql">Using Literal SQL</a></li>
<li><a class="reference internal" href="#counting">Counting</a></li>
</ul>
</li>
<li><a class="reference internal" href="#building-a-relationship">Building a Relationship</a></li>
<li><a class="reference internal" href="#working-with-related-objects">Working with Related Objects</a></li>
<li><a class="reference internal" href="#querying-with-joins">Querying with Joins</a><ul>
<li><a class="reference internal" href="#using-join-to-eagerly-load-collections-attributes">Using join() to Eagerly Load Collections/Attributes</a></li>
<li><a class="reference internal" href="#using-aliases">Using Aliases</a></li>
<li><a class="reference internal" href="#using-subqueries">Using Subqueries</a></li>
<li><a class="reference internal" href="#selecting-entities-from-subqueries">Selecting Entities from Subqueries</a></li>
<li><a class="reference internal" href="#using-exists">Using EXISTS</a></li>
<li><a class="reference internal" href="#common-relationship-operators">Common Relationship Operators</a></li>
</ul>
</li>
<li><a class="reference internal" href="#deleting">Deleting</a><ul>
<li><a class="reference internal" href="#configuring-delete-delete-orphan-cascade">Configuring delete/delete-orphan Cascade</a></li>
</ul>
</li>
<li><a class="reference internal" href="#building-a-many-to-many-relationship">Building a Many To Many Relationship</a></li>
<li><a class="reference internal" href="#further-reference">Further Reference</a></li>
</ul>
</li>
</ul>
<div class="clearboth"></div>
</div>
<div class="document">
<div class="body">
<div class="section" id="object-relational-tutorial">
<span id="ormtutorial-toplevel"></span><h1>Object Relational Tutorial<a class="headerlink" href="#object-relational-tutorial" title="Permalink to this headline">¶</a></h1>
<p>In this tutorial we will cover a basic SQLAlchemy object-relational mapping scenario, where we store and retrieve Python objects from a database representation. The tutorial is in doctest format, meaning each <tt class="docutils literal"><span class="pre">>>></span></tt> line represents something you can type at a Python command prompt, and the following text represents the expected return value.</p>
<div class="section" id="version-check">
<h2>Version Check<a class="headerlink" href="#version-check" title="Permalink to this headline">¶</a></h2>
<p>A quick check to verify that we are on at least <strong>version 0.6</strong> of SQLAlchemy:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="kn">import</span> <span class="nn">sqlalchemy</span>
<span class="gp">>>> </span><span class="n">sqlalchemy</span><span class="o">.</span><span class="n">__version__</span>
<span class="go">0.6.0</span></pre></div>
</div>
</div>
<div class="section" id="connecting">
<h2>Connecting<a class="headerlink" href="#connecting" title="Permalink to this headline">¶</a></h2>
<p>For this tutorial we will use an in-memory-only SQLite database. To connect we use <a class="reference internal" href="reference/sqlalchemy/connections.html#sqlalchemy.create_engine" title="sqlalchemy.create_engine"><tt class="xref py py-func docutils literal"><span class="pre">create_engine()</span></tt></a>:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="kn">from</span> <span class="nn">sqlalchemy</span> <span class="kn">import</span> <span class="n">create_engine</span>
<span class="gp">>>> </span><span class="n">engine</span> <span class="o">=</span> <span class="n">create_engine</span><span class="p">(</span><span class="s">'sqlite:///:memory:'</span><span class="p">,</span> <span class="n">echo</span><span class="o">=</span><span class="bp">True</span><span class="p">)</span></pre></div>
</div>
<p>The <tt class="docutils literal"><span class="pre">echo</span></tt> flag is a shortcut to setting up SQLAlchemy logging, which is accomplished via Python’s standard <tt class="docutils literal"><span class="pre">logging</span></tt> module. With it enabled, we’ll see all the generated SQL produced. If you are working through this tutorial and want less output generated, set it to <tt class="xref docutils literal"><span class="pre">False</span></tt>. This tutorial will format the SQL behind a popup window so it doesn’t get in our way; just click the “SQL” links to see what’s being generated.</p>
</div>
<div class="section" id="define-and-create-a-table">
<h2>Define and Create a Table<a class="headerlink" href="#define-and-create-a-table" title="Permalink to this headline">¶</a></h2>
<p>Next we want to tell SQLAlchemy about our tables. We will start with just a single table called <tt class="docutils literal"><span class="pre">users</span></tt>, which will store records for the end-users using our application (lets assume it’s a website). We define our tables within a catalog called <a class="reference internal" href="reference/sqlalchemy/schema.html#sqlalchemy.schema.MetaData" title="sqlalchemy.schema.MetaData"><tt class="xref py py-class docutils literal"><span class="pre">MetaData</span></tt></a>, using the <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, which is used in a manner similar to SQL’s CREATE TABLE syntax:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="kn">from</span> <span class="nn">sqlalchemy</span> <span class="kn">import</span> <span class="n">Table</span><span class="p">,</span> <span class="n">Column</span><span class="p">,</span> <span class="n">Integer</span><span class="p">,</span> <span class="n">String</span><span class="p">,</span> <span class="n">MetaData</span><span class="p">,</span> <span class="n">ForeignKey</span>
<span class="gp">>>> </span><span class="n">metadata</span> <span class="o">=</span> <span class="n">MetaData</span><span class="p">()</span>
<span class="gp">>>> </span><span class="n">users_table</span> <span class="o">=</span> <span class="n">Table</span><span class="p">(</span><span class="s">'users'</span><span class="p">,</span> <span class="n">metadata</span><span class="p">,</span>
<span class="gp">... </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="gp">... </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="gp">... </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="gp">... </span> <span class="n">Column</span><span class="p">(</span><span class="s">'password'</span><span class="p">,</span> <span class="n">String</span><span class="p">)</span>
<span class="gp">... </span><span class="p">)</span></pre></div>
</div>
<p><a class="reference internal" href="metadata.html"><em>Database Meta Data</em></a> covers all about how to define <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> objects, as well as how to load their definition from an existing database (known as <strong>reflection</strong>).</p>
<p>Next, we can issue CREATE TABLE statements derived from our table metadata, by calling <a class="reference internal" href="reference/sqlalchemy/schema.html#sqlalchemy.schema.MetaData.create_all" title="sqlalchemy.schema.MetaData.create_all"><tt class="xref py py-func docutils literal"><span class="pre">create_all()</span></tt></a> and passing it the <tt class="docutils literal"><span class="pre">engine</span></tt> instance which points to our database. This will check for the presence of a table first before creating, so it’s safe to call multiple times:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><a href='#' class='sql_link'>sql</a><span class="o">>>></span> <span class="n">metadata</span><span class="o">.</span><span class="n">create_all</span><span class="p">(</span><span class="n">engine</span><span class="p">)</span>
<div class='popup_sql'>PRAGMA table_info("users")
()
CREATE TABLE users (
id INTEGER NOT NULL,
name VARCHAR,
fullname VARCHAR,
password VARCHAR,
PRIMARY KEY (id)
)
()
COMMIT</div></pre></div>
</div>
<div class="admonition note">
<p class="first admonition-title">Note</p>
<p>Users familiar with the syntax of CREATE TABLE may notice that the
VARCHAR columns were generated without a length; on SQLite and Postgresql,
this is a valid datatype, but on others, it’s not allowed. So if running
this tutorial on one of those databases, and you wish to use SQLAlchemy to
issue CREATE TABLE, a “length” may be provided to the <a class="reference internal" href="reference/sqlalchemy/types.html#sqlalchemy.types.String" title="sqlalchemy.types.String"><tt class="xref py py-class docutils literal"><span class="pre">String</span></tt></a> type as
below:</p>
<div class="highlight-python"><div class="highlight"><pre><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></pre></div>
</div>
<p>The length field on <a class="reference internal" href="reference/sqlalchemy/types.html#sqlalchemy.types.String" title="sqlalchemy.types.String"><tt class="xref py py-class docutils literal"><span class="pre">String</span></tt></a>, as well as similar precision/scale fields
available on <a class="reference internal" href="reference/sqlalchemy/types.html#sqlalchemy.types.Integer" title="sqlalchemy.types.Integer"><tt class="xref py py-class docutils literal"><span class="pre">Integer</span></tt></a>, <a class="reference internal" href="reference/sqlalchemy/types.html#sqlalchemy.types.Numeric" title="sqlalchemy.types.Numeric"><tt class="xref py py-class docutils literal"><span class="pre">Numeric</span></tt></a>, etc. are not referenced by
SQLAlchemy other than when creating tables.</p>
<p>Additionally, Firebird and Oracle require sequences to generate new
primary key identifiers, and SQLAlchemy doesn’t generate or assume these
without being instructed. For that, you use the <a class="reference internal" href="reference/sqlalchemy/schema.html#sqlalchemy.schema.Sequence" title="sqlalchemy.schema.Sequence"><tt class="xref py py-class docutils literal"><span class="pre">Sequence</span></tt></a> construct:</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">Sequence</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">Sequence</span><span class="p">(</span><span class="s">'user_id_seq'</span><span class="p">),</span> <span class="n">primary_key</span><span class="o">=</span><span class="bp">True</span><span class="p">)</span></pre></div>
</div>
<p>A full, foolproof <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> is therefore:</p>
<div class="last highlight-python"><div class="highlight"><pre><span class="n">users_table</span> <span class="o">=</span> <span class="n">Table</span><span class="p">(</span><span class="s">'users'</span><span class="p">,</span> <span class="n">metadata</span><span class="p">,</span>
<span class="n">Column</span><span class="p">(</span><span class="s">'id'</span><span class="p">,</span> <span class="n">Integer</span><span class="p">,</span> <span class="n">Sequence</span><span class="p">(</span><span class="s">'user_id_seq'</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">'fullname'</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">'password'</span><span class="p">,</span> <span class="n">String</span><span class="p">(</span><span class="mi">12</span><span class="p">))</span>
<span class="p">)</span></pre></div>
</div>
</div>
</div>
<div class="section" id="define-a-python-class-to-be-mapped">
<h2>Define a Python Class to be Mapped<a class="headerlink" href="#define-a-python-class-to-be-mapped" title="Permalink to this headline">¶</a></h2>
<p>While the <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 defines information about our database, it does not say anything about the definition or behavior of the business objects used by our application; SQLAlchemy views this as a separate concern. To correspond to our <tt class="docutils literal"><span class="pre">users</span></tt> table, let’s create a rudimentary <tt class="docutils literal"><span class="pre">User</span></tt> class. It only need subclass Python’s built-in <tt class="docutils literal"><span class="pre">object</span></tt> class (i.e. it’s a new style class):</p>
<div class="highlight-python"><div class="highlight"><pre><span class="gp">>>> </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="gp">... </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">fullname</span><span class="p">,</span> <span class="n">password</span><span class="p">):</span>
<span class="gp">... </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="gp">... </span> <span class="bp">self</span><span class="o">.</span><span class="n">fullname</span> <span class="o">=</span> <span class="n">fullname</span>
<span class="gp">... </span> <span class="bp">self</span><span class="o">.</span><span class="n">password</span> <span class="o">=</span> <span class="n">password</span>
<span class="gp">...</span>
<span class="gp">... </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="gp">... </span> <span class="k">return</span> <span class="s">"<User('</span><span class="si">%s</span><span class="s">','</span><span class="si">%s</span><span class="s">', '</span><span class="si">%s</span><span class="s">')>"</span> <span class="o">%</span> <span class="p">(</span><span class="bp">self</span><span class="o">.</span><span class="n">name</span><span class="p">,</span> <span class="bp">self</span><span class="o">.</span><span class="n">fullname</span><span class="p">,</span> <span class="bp">self</span><span class="o">.</span><span class="n">password</span><span class="p">)</span></pre></div>
</div>
<p>The class has an <tt class="docutils literal"><span class="pre">__init__()</span></tt> and a <tt class="docutils literal"><span class="pre">__repr__()</span></tt> method for convenience. These methods are both entirely optional, and can be of any form. SQLAlchemy never calls <tt class="docutils literal"><span class="pre">__init__()</span></tt> directly.</p>
</div>
<div class="section" id="setting-up-the-mapping">
<h2>Setting up the Mapping<a class="headerlink" href="#setting-up-the-mapping" title="Permalink to this headline">¶</a></h2>
<p>With our <tt class="docutils literal"><span class="pre">users_table</span></tt> and <tt class="docutils literal"><span class="pre">User</span></tt> class, we now want to map the two together. That’s where the SQLAlchemy ORM package comes in. We’ll use the <tt class="docutils literal"><span class="pre">mapper</span></tt> function to create a <strong>mapping</strong> between <tt class="docutils literal"><span class="pre">users_table</span></tt> and <tt class="docutils literal"><span class="pre">User</span></tt>:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="kn">from</span> <span class="nn">sqlalchemy.orm</span> <span class="kn">import</span> <span class="n">mapper</span>
<span class="gp">>>> </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="go"><Mapper at 0x...; User></span></pre></div>
</div>
<p>The <tt class="docutils literal"><span class="pre">mapper()</span></tt> function creates a new <a class="reference internal" href="reference/orm/mapping.html#sqlalchemy.orm.mapper.Mapper" title="sqlalchemy.orm.mapper.Mapper"><tt class="xref py py-class docutils literal"><span class="pre">Mapper</span></tt></a> object and stores it away for future reference, associated with our class. Let’s now create and inspect a <tt class="docutils literal"><span class="pre">User</span></tt> object:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="n">ed_user</span> <span class="o">=</span> <span class="n">User</span><span class="p">(</span><span class="s">'ed'</span><span class="p">,</span> <span class="s">'Ed Jones'</span><span class="p">,</span> <span class="s">'edspassword'</span><span class="p">)</span>
<span class="gp">>>> </span><span class="n">ed_user</span><span class="o">.</span><span class="n">name</span>
<span class="go">'ed'</span>
<span class="gp">>>> </span><span class="n">ed_user</span><span class="o">.</span><span class="n">password</span>
<span class="go">'edspassword'</span>
<span class="gp">>>> </span><span class="nb">str</span><span class="p">(</span><span class="n">ed_user</span><span class="o">.</span><span class="n">id</span><span class="p">)</span>
<span class="go">'None'</span></pre></div>
</div>
<p>The <tt class="docutils literal"><span class="pre">id</span></tt> attribute, which while not defined by our <tt class="docutils literal"><span class="pre">__init__()</span></tt> method, exists due to the <tt class="docutils literal"><span class="pre">id</span></tt> column present within the <tt class="docutils literal"><span class="pre">users_table</span></tt> object. By default, the <tt class="docutils literal"><span class="pre">mapper</span></tt> creates class attributes for all columns present within the <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>. These class attributes exist as Python descriptors, and define <strong>instrumentation</strong> for the mapped class. The functionality of this instrumentation is very rich and includes the ability to track modifications and automatically load new data from the database when needed.</p>
<p>Since we have not yet told SQLAlchemy to persist <tt class="docutils literal"><span class="pre">Ed</span> <span class="pre">Jones</span></tt> within the database, its id is <tt class="xref docutils literal"><span class="pre">None</span></tt>. When we persist the object later, this attribute will be populated with a newly generated value.</p>
</div>
<div class="section" id="creating-table-class-and-mapper-all-at-once-declaratively">
<h2>Creating Table, Class and Mapper All at Once Declaratively<a class="headerlink" href="#creating-table-class-and-mapper-all-at-once-declaratively" title="Permalink to this headline">¶</a></h2>
<p>The preceding approach to configuration involved 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>, a user-defined class, and
a call to``mapper()``. This illustrates classical SQLAlchemy usage, which values
the highest separation of concerns possible.
A large number of applications don’t require this degree of
separation, and for those SQLAlchemy offers an alternate “shorthand”
configurational style called <a class="reference internal" href="reference/ext/declarative.html#module-sqlalchemy.ext.declarative"><tt class="xref py py-mod docutils literal"><span class="pre">declarative</span></tt></a>.
For many applications, this is the only style of configuration needed.
Our above example using this style is as follows:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="kn">from</span> <span class="nn">sqlalchemy.ext.declarative</span> <span class="kn">import</span> <span class="n">declarative_base</span>
<span class="gp">>>> </span><span class="n">Base</span> <span class="o">=</span> <span class="n">declarative_base</span><span class="p">()</span>
<span class="gp">>>> </span><span class="k">class</span> <span class="nc">User</span><span class="p">(</span><span class="n">Base</span><span class="p">):</span>
<span class="gp">... </span> <span class="n">__tablename__</span> <span class="o">=</span> <span class="s">'users'</span>
<span class="gp">...</span>
<span class="gp">... </span> <span class="nb">id</span> <span class="o">=</span> <span class="n">Column</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="gp">... </span> <span class="n">name</span> <span class="o">=</span> <span class="n">Column</span><span class="p">(</span><span class="n">String</span><span class="p">)</span>
<span class="gp">... </span> <span class="n">fullname</span> <span class="o">=</span> <span class="n">Column</span><span class="p">(</span><span class="n">String</span><span class="p">)</span>
<span class="gp">... </span> <span class="n">password</span> <span class="o">=</span> <span class="n">Column</span><span class="p">(</span><span class="n">String</span><span class="p">)</span>
<span class="gp">...</span>
<span class="gp">... </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">fullname</span><span class="p">,</span> <span class="n">password</span><span class="p">):</span>
<span class="gp">... </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="gp">... </span> <span class="bp">self</span><span class="o">.</span><span class="n">fullname</span> <span class="o">=</span> <span class="n">fullname</span>
<span class="gp">... </span> <span class="bp">self</span><span class="o">.</span><span class="n">password</span> <span class="o">=</span> <span class="n">password</span>
<span class="gp">...</span>
<span class="gp">... </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="gp">... </span> <span class="k">return</span> <span class="s">"<User('</span><span class="si">%s</span><span class="s">','</span><span class="si">%s</span><span class="s">', '</span><span class="si">%s</span><span class="s">')>"</span> <span class="o">%</span> <span class="p">(</span><span class="bp">self</span><span class="o">.</span><span class="n">name</span><span class="p">,</span> <span class="bp">self</span><span class="o">.</span><span class="n">fullname</span><span class="p">,</span> <span class="bp">self</span><span class="o">.</span><span class="n">password</span><span class="p">)</span></pre></div>
</div>
<p>Above, the <a class="reference internal" href="reference/ext/declarative.html#sqlalchemy.ext.declarative.declarative_base" title="sqlalchemy.ext.declarative.declarative_base"><tt class="xref py py-func docutils literal"><span class="pre">declarative_base()</span></tt></a> function defines a new class which
we name <tt class="docutils literal"><span class="pre">Base</span></tt>, from which all of our ORM-enabled classes will
derive. Note that we define <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>
objects with no “name” field, since it’s inferred from the given
attribute name.</p>
<p>The underlying <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 created by our
<a class="reference internal" href="reference/ext/declarative.html#sqlalchemy.ext.declarative.declarative_base" title="sqlalchemy.ext.declarative.declarative_base"><tt class="xref py py-func docutils literal"><span class="pre">declarative_base()</span></tt></a> version of <tt class="docutils literal"><span class="pre">User</span></tt> is accessible via the
<tt class="docutils literal"><span class="pre">__table__</span></tt> attribute:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="n">users_table</span> <span class="o">=</span> <span class="n">User</span><span class="o">.</span><span class="n">__table__</span></pre></div>
</div>
<p>The owning <a class="reference internal" href="reference/sqlalchemy/schema.html#sqlalchemy.schema.MetaData" title="sqlalchemy.schema.MetaData"><tt class="xref py py-class docutils literal"><span class="pre">MetaData</span></tt></a> object is available as well:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="n">metadata</span> <span class="o">=</span> <span class="n">Base</span><span class="o">.</span><span class="n">metadata</span></pre></div>
</div>
<p>Full documentation for <a class="reference internal" href="reference/ext/declarative.html#module-sqlalchemy.ext.declarative"><tt class="xref py py-mod docutils literal"><span class="pre">declarative</span></tt></a> can be found
in the <a class="reference internal" href="reference/index.html"><em>API Reference</em></a> section for <a class="reference internal" href="reference/ext/declarative.html"><em>declarative</em></a>.</p>
<p>Yet another “declarative” method is available for SQLAlchemy as a third party library called <a class="reference external" href="http://elixir.ematia.de/">Elixir</a>. This is a full-featured configurational product which also includes many higher level mapping configurations built in. Like declarative, once classes and mappings are defined, ORM usage is the same as with a classical SQLAlchemy configuration.</p>
</div>
<div class="section" id="creating-a-session">
<h2>Creating a Session<a class="headerlink" href="#creating-a-session" title="Permalink to this headline">¶</a></h2>
<p>We’re now ready to start talking to the database. The ORM’s “handle” to the database is the <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>. When we first set up the application, at the same level as our <a class="reference internal" href="reference/sqlalchemy/connections.html#sqlalchemy.create_engine" title="sqlalchemy.create_engine"><tt class="xref py py-func docutils literal"><span class="pre">create_engine()</span></tt></a> statement, we define a <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> class which will serve as a factory for new <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> objects:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="o">>>></span> <span class="kn">from</span> <span class="nn">sqlalchemy.orm</span> <span class="kn">import</span> <span class="n">sessionmaker</span>
<span class="o">>>></span> <span class="n">Session</span> <span class="o">=</span> <span class="n">sessionmaker</span><span class="p">(</span><span class="n">bind</span><span class="o">=</span><span class="n">engine</span><span class="p">)</span></pre></div>
</div>
<p>In the case where your application does not yet have an <a class="reference internal" href="reference/sqlalchemy/connections.html#sqlalchemy.engine.base.Engine" title="sqlalchemy.engine.base.Engine"><tt class="xref py py-class docutils literal"><span class="pre">Engine</span></tt></a> when you define your module-level objects, just set it up like this:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="o">>>></span> <span class="n">Session</span> <span class="o">=</span> <span class="n">sessionmaker</span><span class="p">()</span></pre></div>
</div>
<p>Later, when you create your engine with <a class="reference internal" href="reference/sqlalchemy/connections.html#sqlalchemy.create_engine" title="sqlalchemy.create_engine"><tt class="xref py py-func docutils literal"><span class="pre">create_engine()</span></tt></a>, connect it to the <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> using <tt class="docutils literal"><span class="pre">configure()</span></tt>:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="o">>>></span> <span class="n">Session</span><span class="o">.</span><span class="n">configure</span><span class="p">(</span><span class="n">bind</span><span class="o">=</span><span class="n">engine</span><span class="p">)</span> <span class="c"># once engine is available</span></pre></div>
</div>
<p>This custom-made <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> class will create new <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> objects which are bound to our database. Other transactional characteristics may be defined when calling <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.sessionmaker" title="sqlalchemy.orm.sessionmaker"><tt class="xref py py-func docutils literal"><span class="pre">sessionmaker()</span></tt></a> as well; these are described in a later chapter. Then, whenever you need to have a conversation with the database, you instantiate a <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>:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="n">session</span> <span class="o">=</span> <span class="n">Session</span><span class="p">()</span></pre></div>
</div>
<p>The above <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> is associated with our SQLite <tt class="docutils literal"><span class="pre">engine</span></tt>, but it hasn’t opened any connections yet. When it’s first used, it retrieves a connection from a pool of connections maintained by the <tt class="docutils literal"><span class="pre">engine</span></tt>, and holds onto it until we commit all changes and/or close the session object.</p>
</div>
<div class="section" id="adding-new-objects">
<h2>Adding new Objects<a class="headerlink" href="#adding-new-objects" title="Permalink to this headline">¶</a></h2>
<p>To persist our <tt class="docutils literal"><span class="pre">User</span></tt> object, we <tt class="docutils literal"><span class="pre">add()</span></tt> it to our <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>:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="n">ed_user</span> <span class="o">=</span> <span class="n">User</span><span class="p">(</span><span class="s">'ed'</span><span class="p">,</span> <span class="s">'Ed Jones'</span><span class="p">,</span> <span class="s">'edspassword'</span><span class="p">)</span>
<span class="gp">>>> </span><span class="n">session</span><span class="o">.</span><span class="n">add</span><span class="p">(</span><span class="n">ed_user</span><span class="p">)</span></pre></div>
</div>
<p>At this point, the instance is <strong>pending</strong>; no SQL has yet been issued. The <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> will issue the SQL to persist <tt class="docutils literal"><span class="pre">Ed</span> <span class="pre">Jones</span></tt> as soon as is needed, using a process known as a <strong>flush</strong>. If we query the database for <tt class="docutils literal"><span class="pre">Ed</span> <span class="pre">Jones</span></tt>, all pending information will first be flushed, and the query is issued afterwards.</p>
<p>For example, below we create a new <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 which loads instances of <tt class="docutils literal"><span class="pre">User</span></tt>. We “filter by” the <tt class="docutils literal"><span class="pre">name</span></tt> attribute of <tt class="docutils literal"><span class="pre">ed</span></tt>, and indicate that we’d like only the first result in the full list of rows. A <tt class="docutils literal"><span class="pre">User</span></tt> instance is returned which is equivalent to that which we’ve added:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><a href='#' class='sql_link'>sql</a><span class="o">>>></span> <span class="n">our_user</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">filter_by</span><span class="p">(</span><span class="n">name</span><span class="o">=</span><span class="s">'ed'</span><span class="p">)</span><span class="o">.</span><span class="n">first</span><span class="p">()</span>
<div class='popup_sql'>BEGIN
INSERT INTO users (name, fullname, password) VALUES (?, ?, ?)
('ed', 'Ed Jones', 'edspassword')
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 = ?
LIMIT 1 OFFSET 0
('ed',)</div><span class="o">>>></span> <span class="n">our_user</span>
<span class="o"><</span><span class="n">User</span><span class="p">(</span><span class="s">'ed'</span><span class="p">,</span><span class="s">'Ed Jones'</span><span class="p">,</span> <span class="s">'edspassword'</span><span class="p">)</span><span class="o">></span></pre></div>
</div>
<p>In fact, the <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> has identified that the row returned is the <strong>same</strong> row as one already represented within its internal map of objects, so we actually got back the identical instance as that which we just added:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="n">ed_user</span> <span class="ow">is</span> <span class="n">our_user</span>
<span class="go">True</span></pre></div>
</div>
<p>The ORM concept at work here is known as an <strong>identity map</strong> and ensures that all operations upon a particular row within a <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> operate upon the same set of data. Once an object with a particular primary key is present in the <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>, all SQL queries on that <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> will always return the same Python object for that particular primary key; it also will raise an error if an attempt is made to place a second, already-persisted object with the same primary key within the session.</p>
<p>We can add more <tt class="docutils literal"><span class="pre">User</span></tt> objects at once using <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.session.Session.add_all" title="sqlalchemy.orm.session.Session.add_all"><tt class="xref py py-func docutils literal"><span class="pre">add_all()</span></tt></a>:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="o">>>></span> <span class="n">session</span><span class="o">.</span><span class="n">add_all</span><span class="p">([</span>
<span class="o">...</span> <span class="n">User</span><span class="p">(</span><span class="s">'wendy'</span><span class="p">,</span> <span class="s">'Wendy Williams'</span><span class="p">,</span> <span class="s">'foobar'</span><span class="p">),</span>
<span class="o">...</span> <span class="n">User</span><span class="p">(</span><span class="s">'mary'</span><span class="p">,</span> <span class="s">'Mary Contrary'</span><span class="p">,</span> <span class="s">'xxg527'</span><span class="p">),</span>
<span class="o">...</span> <span class="n">User</span><span class="p">(</span><span class="s">'fred'</span><span class="p">,</span> <span class="s">'Fred Flinstone'</span><span class="p">,</span> <span class="s">'blah'</span><span class="p">)])</span></pre></div>
</div>
<p>Also, Ed has already decided his password isn’t too secure, so lets change it:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="o">>>></span> <span class="n">ed_user</span><span class="o">.</span><span class="n">password</span> <span class="o">=</span> <span class="s">'f8s7ccs'</span></pre></div>
</div>
<p>The <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> is paying attention. It knows, for example, that <tt class="docutils literal"><span class="pre">Ed</span> <span class="pre">Jones</span></tt> has been modified:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="o">>>></span> <span class="n">session</span><span class="o">.</span><span class="n">dirty</span>
<span class="n">IdentitySet</span><span class="p">([</span><span class="o"><</span><span class="n">User</span><span class="p">(</span><span class="s">'ed'</span><span class="p">,</span><span class="s">'Ed Jones'</span><span class="p">,</span> <span class="s">'f8s7ccs'</span><span class="p">)</span><span class="o">></span><span class="p">])</span></pre></div>
</div>
<p>and that three new <tt class="docutils literal"><span class="pre">User</span></tt> objects are pending:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="o">>>></span> <span class="n">session</span><span class="o">.</span><span class="n">new</span>
<span class="n">IdentitySet</span><span class="p">([</span><span class="o"><</span><span class="n">User</span><span class="p">(</span><span class="s">'wendy'</span><span class="p">,</span><span class="s">'Wendy Williams'</span><span class="p">,</span> <span class="s">'foobar'</span><span class="p">)</span><span class="o">></span><span class="p">,</span>
<span class="o"><</span><span class="n">User</span><span class="p">(</span><span class="s">'mary'</span><span class="p">,</span><span class="s">'Mary Contrary'</span><span class="p">,</span> <span class="s">'xxg527'</span><span class="p">)</span><span class="o">></span><span class="p">,</span>
<span class="o"><</span><span class="n">User</span><span class="p">(</span><span class="s">'fred'</span><span class="p">,</span><span class="s">'Fred Flinstone'</span><span class="p">,</span> <span class="s">'blah'</span><span class="p">)</span><span class="o">></span><span class="p">])</span></pre></div>
</div>
<p>We tell the <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> that we’d like to issue all remaining changes to the database and commit the transaction, which has been in progress throughout. We do this via <tt class="docutils literal"><span class="pre">commit()</span></tt>:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><a href='#' class='sql_link'>sql</a><span class="o">>>></span> <span class="n">session</span><span class="o">.</span><span class="n">commit</span><span class="p">()</span>
<div class='popup_sql'>UPDATE users SET password=? WHERE users.id = ?
('f8s7ccs', 1)
INSERT INTO users (name, fullname, password) VALUES (?, ?, ?)
('wendy', 'Wendy Williams', 'foobar')
INSERT INTO users (name, fullname, password) VALUES (?, ?, ?)
('mary', 'Mary Contrary', 'xxg527')
INSERT INTO users (name, fullname, password) VALUES (?, ?, ?)
('fred', 'Fred Flinstone', 'blah')
COMMIT</div></pre></div>
</div>
<p><tt class="docutils literal"><span class="pre">commit()</span></tt> flushes whatever remaining changes remain to the database, and commits the transaction. The connection resources referenced by the session are now returned to the connection pool. Subsequent operations with this session will occur in a <strong>new</strong> transaction, which will again re-acquire connection resources when first needed.</p>
<p>If we look at Ed’s <tt class="docutils literal"><span class="pre">id</span></tt> attribute, which earlier was <tt class="xref docutils literal"><span class="pre">None</span></tt>, it now has a value:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><a href='#' class='sql_link'>sql</a><span class="o">>>></span> <span class="n">ed_user</span><span class="o">.</span><span class="n">id</span>
<div class='popup_sql'>BEGIN
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.id = ?
(1,)</div><span class="mi">1</span></pre></div>
</div>
<p>After the <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> inserts new rows in the database, all newly generated identifiers and database-generated defaults become available on the instance, either immediately or via load-on-first-access. In this case, the entire row was re-loaded on access because a new transaction was begun after we issued <tt class="docutils literal"><span class="pre">commit()</span></tt>. SQLAlchemy by default refreshes data from a previous transaction the first time it’s accessed within a new transaction, so that the most recent state is available. The level of reloading is configurable as is described in the chapter on Sessions.</p>
</div>
<div class="section" id="rolling-back">
<h2>Rolling Back<a class="headerlink" href="#rolling-back" title="Permalink to this headline">¶</a></h2>
<p>Since the <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> works within a transaction, we can roll back changes made too. Let’s make two changes that we’ll revert; <tt class="docutils literal"><span class="pre">ed_user</span></tt>‘s user name gets set to <tt class="docutils literal"><span class="pre">Edwardo</span></tt>:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="o">>>></span> <span class="n">ed_user</span><span class="o">.</span><span class="n">name</span> <span class="o">=</span> <span class="s">'Edwardo'</span></pre></div>
</div>
<p>and we’ll add another erroneous user, <tt class="docutils literal"><span class="pre">fake_user</span></tt>:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="o">>>></span> <span class="n">fake_user</span> <span class="o">=</span> <span class="n">User</span><span class="p">(</span><span class="s">'fakeuser'</span><span class="p">,</span> <span class="s">'Invalid'</span><span class="p">,</span> <span class="s">'12345'</span><span class="p">)</span>
<span class="o">>>></span> <span class="n">session</span><span class="o">.</span><span class="n">add</span><span class="p">(</span><span class="n">fake_user</span><span class="p">)</span></pre></div>
</div>
<p>Querying the session, we can see that they’re flushed into the current transaction:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><a href='#' class='sql_link'>sql</a><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">filter</span><span class="p">(</span><span class="n">User</span><span class="o">.</span><span class="n">name</span><span class="o">.</span><span class="n">in_</span><span class="p">([</span><span class="s">'Edwardo'</span><span class="p">,</span> <span class="s">'fakeuser'</span><span class="p">]))</span><span class="o">.</span><span class="n">all</span><span class="p">()</span>
<div class='popup_sql'>UPDATE users SET name=? WHERE users.id = ?
('Edwardo', 1)
INSERT INTO users (name, fullname, password) VALUES (?, ?, ?)
('fakeuser', 'Invalid', '12345')
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 IN (?, ?)
('Edwardo', 'fakeuser')</div><span class="p">[</span><span class="o"><</span><span class="n">User</span><span class="p">(</span><span class="s">'Edwardo'</span><span class="p">,</span><span class="s">'Ed Jones'</span><span class="p">,</span> <span class="s">'f8s7ccs'</span><span class="p">)</span><span class="o">></span><span class="p">,</span> <span class="o"><</span><span class="n">User</span><span class="p">(</span><span class="s">'fakeuser'</span><span class="p">,</span><span class="s">'Invalid'</span><span class="p">,</span> <span class="s">'12345'</span><span class="p">)</span><span class="o">></span><span class="p">]</span></pre></div>
</div>
<p>Rolling back, we can see that <tt class="docutils literal"><span class="pre">ed_user</span></tt>‘s name is back to <tt class="docutils literal"><span class="pre">ed</span></tt>, and <tt class="docutils literal"><span class="pre">fake_user</span></tt> has been kicked out of the session:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><a href='#' class='sql_link'>sql</a><span class="o">>>></span> <span class="n">session</span><span class="o">.</span><span class="n">rollback</span><span class="p">()</span>
<div class='popup_sql'>ROLLBACK</div>
<a href='#' class='sql_link'>sql</a><span class="o">>>></span> <span class="n">ed_user</span><span class="o">.</span><span class="n">name</span>
<div class='popup_sql'>BEGIN
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.id = ?
(1,)</div><span class="s">u'ed'</span>
<span class="o">>>></span> <span class="n">fake_user</span> <span class="ow">in</span> <span class="n">session</span>
<span class="bp">False</span></pre></div>
</div>
<p>issuing a SELECT illustrates the changes made to the database:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><a href='#' class='sql_link'>sql</a><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">filter</span><span class="p">(</span><span class="n">User</span><span class="o">.</span><span class="n">name</span><span class="o">.</span><span class="n">in_</span><span class="p">([</span><span class="s">'ed'</span><span class="p">,</span> <span class="s">'fakeuser'</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 IN (?, ?)
('ed', 'fakeuser')</div><span class="p">[</span><span class="o"><</span><span class="n">User</span><span class="p">(</span><span class="s">'ed'</span><span class="p">,</span><span class="s">'Ed Jones'</span><span class="p">,</span> <span class="s">'f8s7ccs'</span><span class="p">)</span><span class="o">></span><span class="p">]</span></pre></div>
</div>
</div>
<div class="section" id="querying">
<span id="ormtutorial-querying"></span><h2>Querying<a class="headerlink" href="#querying" title="Permalink to this headline">¶</a></h2>
<p>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> is created using 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> function on <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>. This function takes a variable number of arguments, which can be any combination of classes and class-instrumented descriptors. Below, we indicate 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> which loads <tt class="docutils literal"><span class="pre">User</span></tt> instances. When evaluated in an iterative context, the list of <tt class="docutils literal"><span class="pre">User</span></tt> objects present is returned:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><a href='#' class='sql_link'>sql</a><span class="o">>>></span> <span class="k">for</span> <span class="n">instance</span> <span class="ow">in</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">order_by</span><span class="p">(</span><span class="n">User</span><span class="o">.</span><span class="n">id</span><span class="p">):</span>
<span class="o">...</span> <span class="k">print</span> <span class="n">instance</span><span class="o">.</span><span class="n">name</span><span class="p">,</span> <span class="n">instance</span><span class="o">.</span><span class="n">fullname</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 ORDER BY users.id
()</div><span class="n">ed</span> <span class="n">Ed</span> <span class="n">Jones</span>
<span class="n">wendy</span> <span class="n">Wendy</span> <span class="n">Williams</span>
<span class="n">mary</span> <span class="n">Mary</span> <span class="n">Contrary</span>
<span class="n">fred</span> <span class="n">Fred</span> <span class="n">Flinstone</span></pre></div>
</div>
<p>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> also accepts ORM-instrumented descriptors as arguments. Any time multiple class entities or column-based entities are expressed as arguments 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> function, the return result is expressed as tuples:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><a href='#' class='sql_link'>sql</a><span class="o">>>></span> <span class="k">for</span> <span class="n">name</span><span class="p">,</span> <span class="n">fullname</span> <span class="ow">in</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="o">.</span><span class="n">name</span><span class="p">,</span> <span class="n">User</span><span class="o">.</span><span class="n">fullname</span><span class="p">):</span>
<span class="o">...</span> <span class="k">print</span> <span class="n">name</span><span class="p">,</span> <span class="n">fullname</span>
<div class='popup_sql'>SELECT users.name AS users_name, users.fullname AS users_fullname
FROM users
()</div><span class="n">ed</span> <span class="n">Ed</span> <span class="n">Jones</span>
<span class="n">wendy</span> <span class="n">Wendy</span> <span class="n">Williams</span>
<span class="n">mary</span> <span class="n">Mary</span> <span class="n">Contrary</span>
<span class="n">fred</span> <span class="n">Fred</span> <span class="n">Flinstone</span></pre></div>
</div>
<p>The tuples returned by <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> are <em>named</em> tuples, and can be treated much like an ordinary Python object. The names are the same as the attribute’s name for an attribute, and the class name for a class:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><a href='#' class='sql_link'>sql</a><span class="o">>>></span> <span class="k">for</span> <span class="n">row</span> <span class="ow">in</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="n">User</span><span class="o">.</span><span class="n">name</span><span class="p">)</span><span class="o">.</span><span class="n">all</span><span class="p">():</span>
<span class="o">...</span> <span class="k">print</span> <span class="n">row</span><span class="o">.</span><span class="n">User</span><span class="p">,</span> <span class="n">row</span><span class="o">.</span><span class="n">name</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
()</div><span class="o"><</span><span class="n">User</span><span class="p">(</span><span class="s">'ed'</span><span class="p">,</span><span class="s">'Ed Jones'</span><span class="p">,</span> <span class="s">'f8s7ccs'</span><span class="p">)</span><span class="o">></span> <span class="n">ed</span>
<span class="o"><</span><span class="n">User</span><span class="p">(</span><span class="s">'wendy'</span><span class="p">,</span><span class="s">'Wendy Williams'</span><span class="p">,</span> <span class="s">'foobar'</span><span class="p">)</span><span class="o">></span> <span class="n">wendy</span>
<span class="o"><</span><span class="n">User</span><span class="p">(</span><span class="s">'mary'</span><span class="p">,</span><span class="s">'Mary Contrary'</span><span class="p">,</span> <span class="s">'xxg527'</span><span class="p">)</span><span class="o">></span> <span class="n">mary</span>
<span class="o"><</span><span class="n">User</span><span class="p">(</span><span class="s">'fred'</span><span class="p">,</span><span class="s">'Fred Flinstone'</span><span class="p">,</span> <span class="s">'blah'</span><span class="p">)</span><span class="o">></span> <span class="n">fred</span></pre></div>
</div>
<p>You can control the names using the <tt class="docutils literal"><span class="pre">label()</span></tt> construct for scalar attributes and <tt class="docutils literal"><span class="pre">aliased()</span></tt> for class constructs:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="o">>>></span> <span class="kn">from</span> <span class="nn">sqlalchemy.orm</span> <span class="kn">import</span> <span class="n">aliased</span>
<span class="o">>>></span> <span class="n">user_alias</span> <span class="o">=</span> <span class="n">aliased</span><span class="p">(</span><span class="n">User</span><span class="p">,</span> <span class="n">name</span><span class="o">=</span><span class="s">'user_alias'</span><span class="p">)</span>
<a href='#' class='sql_link'>sql</a><span class="o">>>></span> <span class="k">for</span> <span class="n">row</span> <span class="ow">in</span> <span class="n">session</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="n">user_alias</span><span class="p">,</span> <span class="n">user_alias</span><span class="o">.</span><span class="n">name</span><span class="o">.</span><span class="n">label</span><span class="p">(</span><span class="s">'name_label'</span><span class="p">))</span><span class="o">.</span><span class="n">all</span><span class="p">():</span>
<span class="o">...</span> <span class="k">print</span> <span class="n">row</span><span class="o">.</span><span class="n">user_alias</span><span class="p">,</span> <span class="n">row</span><span class="o">.</span><span class="n">name_label</span>
<div class='popup_sql'>SELECT users_1.id AS users_1_id, users_1.name AS users_1_name, users_1.fullname AS users_1_fullname, users_1.password AS users_1_password, users_1.name AS name_label
FROM users AS users_1
()</div><span class="o"><</span><span class="n">User</span><span class="p">(</span><span class="s">'ed'</span><span class="p">,</span><span class="s">'Ed Jones'</span><span class="p">,</span> <span class="s">'f8s7ccs'</span><span class="p">)</span><span class="o">></span> <span class="n">ed</span>
<span class="o"><</span><span class="n">User</span><span class="p">(</span><span class="s">'wendy'</span><span class="p">,</span><span class="s">'Wendy Williams'</span><span class="p">,</span> <span class="s">'foobar'</span><span class="p">)</span><span class="o">></span> <span class="n">wendy</span>
<span class="o"><</span><span class="n">User</span><span class="p">(</span><span class="s">'mary'</span><span class="p">,</span><span class="s">'Mary Contrary'</span><span class="p">,</span> <span class="s">'xxg527'</span><span class="p">)</span><span class="o">></span> <span class="n">mary</span>
<span class="o"><</span><span class="n">User</span><span class="p">(</span><span class="s">'fred'</span><span class="p">,</span><span class="s">'Fred Flinstone'</span><span class="p">,</span> <span class="s">'blah'</span><span class="p">)</span><span class="o">></span> <span class="n">fred</span></pre></div>
</div>
<p>Basic operations 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> include issuing LIMIT and OFFSET, most conveniently using Python array slices and typically in conjunction with ORDER BY:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><a href='#' class='sql_link'>sql</a><span class="o">>>></span> <span class="k">for</span> <span class="n">u</span> <span class="ow">in</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">order_by</span><span class="p">(</span><span class="n">User</span><span class="o">.</span><span class="n">id</span><span class="p">)[</span><span class="mi">1</span><span class="p">:</span><span class="mi">3</span><span class="p">]:</span>
<span class="o">...</span> <span class="k">print</span> <span class="n">u</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 ORDER BY users.id
LIMIT 2 OFFSET 1
()</div><span class="o"><</span><span class="n">User</span><span class="p">(</span><span class="s">'wendy'</span><span class="p">,</span><span class="s">'Wendy Williams'</span><span class="p">,</span> <span class="s">'foobar'</span><span class="p">)</span><span class="o">></span>
<span class="o"><</span><span class="n">User</span><span class="p">(</span><span class="s">'mary'</span><span class="p">,</span><span class="s">'Mary Contrary'</span><span class="p">,</span> <span class="s">'xxg527'</span><span class="p">)</span><span class="o">></span></pre></div>
</div>
<p>and filtering results, which is accomplished either with <a class="reference internal" href="reference/orm/query.html#sqlalchemy.orm.query.Query.filter_by" title="sqlalchemy.orm.query.Query.filter_by"><tt class="xref py py-func docutils literal"><span class="pre">filter_by()</span></tt></a>, which uses keyword arguments:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><a href='#' class='sql_link'>sql</a><span class="o">>>></span> <span class="k">for</span> <span class="n">name</span><span class="p">,</span> <span class="ow">in</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="o">.</span><span class="n">name</span><span class="p">)</span><span class="o">.</span><span class="n">filter_by</span><span class="p">(</span><span class="n">fullname</span><span class="o">=</span><span class="s">'Ed Jones'</span><span class="p">):</span>
<span class="o">...</span> <span class="k">print</span> <span class="n">name</span>
<div class='popup_sql'>SELECT users.name AS users_name FROM users
WHERE users.fullname = ?
('Ed Jones',)</div><span class="n">ed</span></pre></div>
</div>
<p>...or <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>, which uses more flexible SQL expression language constructs. These allow you to use regular Python operators with the class-level attributes on your mapped class:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><a href='#' class='sql_link'>sql</a><span class="o">>>></span> <span class="k">for</span> <span class="n">name</span><span class="p">,</span> <span class="ow">in</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="o">.</span><span class="n">name</span><span class="p">)</span><span class="o">.</span><span class="n">filter</span><span class="p">(</span><span class="n">User</span><span class="o">.</span><span class="n">fullname</span><span class="o">==</span><span class="s">'Ed Jones'</span><span class="p">):</span>
<span class="o">...</span> <span class="k">print</span> <span class="n">name</span>
<div class='popup_sql'>SELECT users.name AS users_name FROM users
WHERE users.fullname = ?
('Ed Jones',)</div><span class="n">ed</span></pre></div>
</div>
<p>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 is fully <em>generative</em>, meaning that most method calls return a new <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 upon which further criteria may be added. For example, to query for users named “ed” with a full name of “Ed Jones”, you can call <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> twice, which joins criteria using <tt class="docutils literal"><span class="pre">AND</span></tt>:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><a href='#' class='sql_link'>sql</a><span class="o">>>></span> <span class="k">for</span> <span class="n">user</span> <span class="ow">in</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">filter</span><span class="p">(</span><span class="n">User</span><span class="o">.</span><span class="n">name</span><span class="o">==</span><span class="s">'ed'</span><span class="p">)</span><span class="o">.</span><span class="n">filter</span><span class="p">(</span><span class="n">User</span><span class="o">.</span><span class="n">fullname</span><span class="o">==</span><span class="s">'Ed Jones'</span><span class="p">):</span>
<span class="o">...</span> <span class="k">print</span> <span class="n">user</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 = ? AND users.fullname = ?
('ed', 'Ed Jones')</div><span class="o"><</span><span class="n">User</span><span class="p">(</span><span class="s">'ed'</span><span class="p">,</span><span class="s">'Ed Jones'</span><span class="p">,</span> <span class="s">'f8s7ccs'</span><span class="p">)</span><span class="o">></span></pre></div>
</div>
<div class="section" id="common-filter-operators">
<h3>Common Filter Operators<a class="headerlink" href="#common-filter-operators" title="Permalink to this headline">¶</a></h3>
<p>Here’s a rundown of some of the most common operators used in <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>:</p>
<ul>
<li><p class="first">equals:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">query</span><span class="o">.</span><span class="n">filter</span><span class="p">(</span><span class="n">User</span><span class="o">.</span><span class="n">name</span> <span class="o">==</span> <span class="s">'ed'</span><span class="p">)</span></pre></div>
</div>
</li>
<li><p class="first">not equals:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">query</span><span class="o">.</span><span class="n">filter</span><span class="p">(</span><span class="n">User</span><span class="o">.</span><span class="n">name</span> <span class="o">!=</span> <span class="s">'ed'</span><span class="p">)</span></pre></div>
</div>
</li>
<li><p class="first">LIKE:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">query</span><span class="o">.</span><span class="n">filter</span><span class="p">(</span><span class="n">User</span><span class="o">.</span><span class="n">name</span><span class="o">.</span><span class="n">like</span><span class="p">(</span><span class="s">'</span><span class="si">%e</span><span class="s">d%'</span><span class="p">))</span></pre></div>
</div>
</li>
<li><p class="first">IN:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">query</span><span class="o">.</span><span class="n">filter</span><span class="p">(</span><span class="n">User</span><span class="o">.</span><span class="n">name</span><span class="o">.</span><span class="n">in_</span><span class="p">([</span><span class="s">'ed'</span><span class="p">,</span> <span class="s">'wendy'</span><span class="p">,</span> <span class="s">'jack'</span><span class="p">]))</span>
<span class="c"># works with query objects too:</span>
<span class="n">query</span><span class="o">.</span><span class="n">filter</span><span class="p">(</span><span class="n">User</span><span class="o">.</span><span class="n">name</span><span class="o">.</span><span class="n">in_</span><span class="p">(</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="o">.</span><span class="n">name</span><span class="p">)</span><span class="o">.</span><span class="n">filter</span><span class="p">(</span><span class="n">User</span><span class="o">.</span><span class="n">name</span><span class="o">.</span><span class="n">like</span><span class="p">(</span><span class="s">'</span><span class="si">%e</span><span class="s">d%'</span><span class="p">))))</span></pre></div>
</div>
</li>
<li><p class="first">NOT IN:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">query</span><span class="o">.</span><span class="n">filter</span><span class="p">(</span><span class="o">~</span><span class="n">User</span><span class="o">.</span><span class="n">name</span><span class="o">.</span><span class="n">in_</span><span class="p">([</span><span class="s">'ed'</span><span class="p">,</span> <span class="s">'wendy'</span><span class="p">,</span> <span class="s">'jack'</span><span class="p">]))</span></pre></div>
</div>
</li>
<li><p class="first">IS NULL:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="nb">filter</span><span class="p">(</span><span class="n">User</span><span class="o">.</span><span class="n">name</span> <span class="o">==</span> <span class="bp">None</span><span class="p">)</span></pre></div>
</div>
</li>
<li><p class="first">IS NOT NULL:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="nb">filter</span><span class="p">(</span><span class="n">User</span><span class="o">.</span><span class="n">name</span> <span class="o">!=</span> <span class="bp">None</span><span class="p">)</span></pre></div>
</div>
</li>
<li><p class="first">AND:</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">and_</span>
<span class="nb">filter</span><span class="p">(</span><span class="n">and_</span><span class="p">(</span><span class="n">User</span><span class="o">.</span><span class="n">name</span> <span class="o">==</span> <span class="s">'ed'</span><span class="p">,</span> <span class="n">User</span><span class="o">.</span><span class="n">fullname</span> <span class="o">==</span> <span class="s">'Ed Jones'</span><span class="p">))</span>
<span class="c"># or call filter()/filter_by() multiple times</span>
<span class="nb">filter</span><span class="p">(</span><span class="n">User</span><span class="o">.</span><span class="n">name</span> <span class="o">==</span> <span class="s">'ed'</span><span class="p">)</span><span class="o">.</span><span class="n">filter</span><span class="p">(</span><span class="n">User</span><span class="o">.</span><span class="n">fullname</span> <span class="o">==</span> <span class="s">'Ed Jones'</span><span class="p">)</span></pre></div>
</div>
</li>
<li><p class="first">OR:</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">or_</span>
<span class="nb">filter</span><span class="p">(</span><span class="n">or_</span><span class="p">(</span><span class="n">User</span><span class="o">.</span><span class="n">name</span> <span class="o">==</span> <span class="s">'ed'</span><span class="p">,</span> <span class="n">User</span><span class="o">.</span><span class="n">name</span> <span class="o">==</span> <span class="s">'wendy'</span><span class="p">))</span></pre></div>
</div>
</li>
<li><p class="first">match:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">query</span><span class="o">.</span><span class="n">filter</span><span class="p">(</span><span class="n">User</span><span class="o">.</span><span class="n">name</span><span class="o">.</span><span class="n">match</span><span class="p">(</span><span class="s">'wendy'</span><span class="p">))</span></pre></div>
</div>
</li>
</ul>
<blockquote>
The contents of the match parameter are database backend specific.</blockquote>
</div>
<div class="section" id="returning-lists-and-scalars">
<h3>Returning Lists and Scalars<a class="headerlink" href="#returning-lists-and-scalars" title="Permalink to this headline">¶</a></h3>
<p>The <a class="reference internal" href="reference/orm/query.html#sqlalchemy.orm.query.Query.all" title="sqlalchemy.orm.query.Query.all"><tt class="xref py py-meth docutils literal"><span class="pre">all()</span></tt></a>, <a class="reference internal" href="reference/orm/query.html#sqlalchemy.orm.query.Query.one" title="sqlalchemy.orm.query.Query.one"><tt class="xref py py-meth docutils literal"><span class="pre">one()</span></tt></a>, and <a class="reference internal" href="reference/orm/query.html#sqlalchemy.orm.query.Query.first" title="sqlalchemy.orm.query.Query.first"><tt class="xref py py-meth docutils literal"><span class="pre">first()</span></tt></a> methods 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> immediately issue SQL and return a non-iterator value. <a class="reference internal" href="reference/orm/query.html#sqlalchemy.orm.query.Query.all" title="sqlalchemy.orm.query.Query.all"><tt class="xref py py-meth docutils literal"><span class="pre">all()</span></tt></a> returns a list:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="o">>>></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">filter</span><span class="p">(</span><span class="n">User</span><span class="o">.</span><span class="n">name</span><span class="o">.</span><span class="n">like</span><span class="p">(</span><span class="s">'</span><span class="si">%e</span><span class="s">d'</span><span class="p">))</span><span class="o">.</span><span class="n">order_by</span><span class="p">(</span><span class="n">User</span><span class="o">.</span><span class="n">id</span><span class="p">)</span>
<a href='#' class='sql_link'>sql</a><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.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, users.password AS users_password
FROM users
WHERE users.name LIKE ? ORDER BY users.id
('%ed',)</div><span class="p">[</span><span class="o"><</span><span class="n">User</span><span class="p">(</span><span class="s">'ed'</span><span class="p">,</span><span class="s">'Ed Jones'</span><span class="p">,</span> <span class="s">'f8s7ccs'</span><span class="p">)</span><span class="o">></span><span class="p">,</span> <span class="o"><</span><span class="n">User</span><span class="p">(</span><span class="s">'fred'</span><span class="p">,</span><span class="s">'Fred Flinstone'</span><span class="p">,</span> <span class="s">'blah'</span><span class="p">)</span><span class="o">></span><span class="p">]</span></pre></div>
</div>
<p><a class="reference internal" href="reference/orm/query.html#sqlalchemy.orm.query.Query.first" title="sqlalchemy.orm.query.Query.first"><tt class="xref py py-meth docutils literal"><span class="pre">first()</span></tt></a> applies a limit of one and returns the first result as a scalar:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><a href='#' class='sql_link'>sql</a><span class="o">>>></span> <span class="n">query</span><span class="o">.</span><span class="n">first</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 LIKE ? ORDER BY users.id
LIMIT 1 OFFSET 0
('%ed',)</div><span class="o"><</span><span class="n">User</span><span class="p">(</span><span class="s">'ed'</span><span class="p">,</span><span class="s">'Ed Jones'</span><span class="p">,</span> <span class="s">'f8s7ccs'</span><span class="p">)</span><span class="o">></span></pre></div>
</div>
<p><a class="reference internal" href="reference/orm/query.html#sqlalchemy.orm.query.Query.one" title="sqlalchemy.orm.query.Query.one"><tt class="xref py py-meth docutils literal"><span class="pre">one()</span></tt></a>, fully fetches all rows, and if not exactly one object identity or composite row is present in the result, raises an error:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><a href='#' class='sql_link'>sql</a><span class="o">>>></span> <span class="kn">from</span> <span class="nn">sqlalchemy.orm.exc</span> <span class="kn">import</span> <span class="n">MultipleResultsFound</span>
<span class="o">>>></span> <span class="k">try</span><span class="p">:</span>
<span class="o">...</span> <span class="n">user</span> <span class="o">=</span> <span class="n">query</span><span class="o">.</span><span class="n">one</span><span class="p">()</span>
<span class="o">...</span> <span class="k">except</span> <span class="n">MultipleResultsFound</span><span class="p">,</span> <span class="n">e</span><span class="p">:</span>
<span class="o">...</span> <span class="k">print</span> <span class="n">e</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 LIKE ? ORDER BY users.id
('%ed',)</div><span class="n">Multiple</span> <span class="n">rows</span> <span class="n">were</span> <span class="n">found</span> <span class="k">for</span> <span class="n">one</span><span class="p">()</span></pre></div>
</div>
<div class="highlight-python+sql"><div class="highlight"><pre><a href='#' class='sql_link'>sql</a><span class="o">>>></span> <span class="kn">from</span> <span class="nn">sqlalchemy.orm.exc</span> <span class="kn">import</span> <span class="n">NoResultFound</span>
<span class="o">>>></span> <span class="k">try</span><span class="p">:</span>
<span class="o">...</span> <span class="n">user</span> <span class="o">=</span> <span class="n">query</span><span class="o">.</span><span class="n">filter</span><span class="p">(</span><span class="n">User</span><span class="o">.</span><span class="n">id</span> <span class="o">==</span> <span class="mi">99</span><span class="p">)</span><span class="o">.</span><span class="n">one</span><span class="p">()</span>
<span class="o">...</span> <span class="k">except</span> <span class="n">NoResultFound</span><span class="p">,</span> <span class="n">e</span><span class="p">:</span>
<span class="o">...</span> <span class="k">print</span> <span class="n">e</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 LIKE ? AND users.id = ? ORDER BY users.id
('%ed', 99)</div><span class="n">No</span> <span class="n">row</span> <span class="n">was</span> <span class="n">found</span> <span class="k">for</span> <span class="n">one</span><span class="p">()</span></pre></div>
</div>
</div>
<div class="section" id="using-literal-sql">
<h3>Using Literal SQL<a class="headerlink" href="#using-literal-sql" title="Permalink to this headline">¶</a></h3>
<p>Literal strings can be used flexibly 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>. Most methods accept strings in addition to SQLAlchemy clause constructs. For example, <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-meth docutils literal"><span class="pre">filter()</span></tt></a> and <a class="reference internal" href="reference/orm/query.html#sqlalchemy.orm.query.Query.order_by" title="sqlalchemy.orm.query.Query.order_by"><tt class="xref py py-meth docutils literal"><span class="pre">order_by()</span></tt></a>:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><a href='#' class='sql_link'>sql</a><span class="o">>>></span> <span class="k">for</span> <span class="n">user</span> <span class="ow">in</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">filter</span><span class="p">(</span><span class="s">"id<224"</span><span class="p">)</span><span class="o">.</span><span class="n">order_by</span><span class="p">(</span><span class="s">"id"</span><span class="p">)</span><span class="o">.</span><span class="n">all</span><span class="p">():</span>
<span class="o">...</span> <span class="k">print</span> <span class="n">user</span><span class="o">.</span><span class="n">name</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 id<224 ORDER BY id
()</div><span class="n">ed</span>
<span class="n">wendy</span>
<span class="n">mary</span>
<span class="n">fred</span></pre></div>
</div>
<p>Bind parameters can be specified with string-based SQL, using a colon. To specify the values, use the <a class="reference internal" href="reference/orm/query.html#sqlalchemy.orm.query.Query.params" title="sqlalchemy.orm.query.Query.params"><tt class="xref py py-meth docutils literal"><span class="pre">params()</span></tt></a> method:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><a href='#' class='sql_link'>sql</a><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">filter</span><span class="p">(</span><span class="s">"id<:value and name=:name"</span><span class="p">)</span><span class="o">.</span>\
<span class="o">...</span> <span class="n">params</span><span class="p">(</span><span class="n">value</span><span class="o">=</span><span class="mi">224</span><span class="p">,</span> <span class="n">name</span><span class="o">=</span><span class="s">'fred'</span><span class="p">)</span><span class="o">.</span><span class="n">order_by</span><span class="p">(</span><span class="n">User</span><span class="o">.</span><span class="n">id</span><span class="p">)</span><span class="o">.</span><span class="n">one</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 id<? and name=? ORDER BY users.id
(224, 'fred')</div><span class="o"><</span><span class="n">User</span><span class="p">(</span><span class="s">'fred'</span><span class="p">,</span><span class="s">'Fred Flinstone'</span><span class="p">,</span> <span class="s">'blah'</span><span class="p">)</span><span class="o">></span></pre></div>
</div>
<p>To use an entirely string-based statement, using <a class="reference internal" href="reference/orm/query.html#sqlalchemy.orm.query.Query.from_statement" title="sqlalchemy.orm.query.Query.from_statement"><tt class="xref py py-meth docutils literal"><span class="pre">from_statement()</span></tt></a>; just ensure that the columns clause of the statement contains the column names normally used by the mapper (below illustrated using an asterisk):</p>
<div class="highlight-python+sql"><div class="highlight"><pre><a href='#' class='sql_link'>sql</a><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 * FROM users where name=:name"</span><span class="p">)</span><span class="o">.</span><span class="n">params</span><span class="p">(</span><span class="n">name</span><span class="o">=</span><span class="s">'ed'</span><span class="p">)</span><span class="o">.</span><span class="n">all</span><span class="p">()</span>
<div class='popup_sql'>SELECT * FROM users where name=?
('ed',)</div><span class="p">[</span><span class="o"><</span><span class="n">User</span><span class="p">(</span><span class="s">'ed'</span><span class="p">,</span><span class="s">'Ed Jones'</span><span class="p">,</span> <span class="s">'f8s7ccs'</span><span class="p">)</span><span class="o">></span><span class="p">]</span></pre></div>
</div>
<p>You can use <a class="reference internal" href="reference/orm/query.html#sqlalchemy.orm.query.Query.from_statement" title="sqlalchemy.orm.query.Query.from_statement"><tt class="xref py py-meth docutils literal"><span class="pre">from_statement()</span></tt></a> to go completely “raw”, using string names to identify desired columns:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><a href='#' class='sql_link'>sql</a><span class="o">>>></span> <span class="n">session</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="s">"id"</span><span class="p">,</span> <span class="s">"name"</span><span class="p">,</span> <span class="s">"thenumber12"</span><span class="p">)</span><span class="o">.</span><span class="n">from_statement</span><span class="p">(</span><span class="s">"SELECT id, name, 12 as thenumber12 FROM users where name=:name"</span><span class="p">)</span><span class="o">.</span><span class="n">params</span><span class="p">(</span><span class="n">name</span><span class="o">=</span><span class="s">'ed'</span><span class="p">)</span><span class="o">.</span><span class="n">all</span><span class="p">()</span>
<div class='popup_sql'>SELECT id, name, 12 as thenumber12 FROM users where name=?
('ed',)</div><span class="p">[(</span><span class="mi">1</span><span class="p">,</span> <span class="s">u'ed'</span><span class="p">,</span> <span class="mi">12</span><span class="p">)]</span></pre></div>
</div>
</div>
<div class="section" id="counting">
<h3>Counting<a class="headerlink" href="#counting" title="Permalink to this headline">¶</a></h3>
<p><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> includes a convenience method for counting called <a class="reference internal" href="reference/orm/query.html#sqlalchemy.orm.query.Query.count" title="sqlalchemy.orm.query.Query.count"><tt class="xref py py-meth docutils literal"><span class="pre">count()</span></tt></a>:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><a href='#' class='sql_link'>sql</a><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">filter</span><span class="p">(</span><span class="n">User</span><span class="o">.</span><span class="n">name</span><span class="o">.</span><span class="n">like</span><span class="p">(</span><span class="s">'</span><span class="si">%e</span><span class="s">d'</span><span class="p">))</span><span class="o">.</span><span class="n">count</span><span class="p">()</span>
<div class='popup_sql'>SELECT count(1) AS count_1
FROM users
WHERE users.name LIKE ?
('%ed',)</div><span class="mi">2</span></pre></div>
</div>
<p>The <a class="reference internal" href="reference/orm/query.html#sqlalchemy.orm.query.Query.count" title="sqlalchemy.orm.query.Query.count"><tt class="xref py py-meth docutils literal"><span class="pre">count()</span></tt></a> method is used to determine how many rows the SQL statement would return, and is mainly intended to return a simple count of a single type of entity, in this case <tt class="docutils literal"><span class="pre">User</span></tt>. For more complicated sets of columns or entities where the “thing to be counted” needs to be indicated more specifically, <a class="reference internal" href="reference/orm/query.html#sqlalchemy.orm.query.Query.count" title="sqlalchemy.orm.query.Query.count"><tt class="xref py py-meth docutils literal"><span class="pre">count()</span></tt></a> is probably not what you want. Below, a query for individual columns does return the expected result:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><a href='#' class='sql_link'>sql</a><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="o">.</span><span class="n">id</span><span class="p">,</span> <span class="n">User</span><span class="o">.</span><span class="n">name</span><span class="p">)</span><span class="o">.</span><span class="n">filter</span><span class="p">(</span><span class="n">User</span><span class="o">.</span><span class="n">name</span><span class="o">.</span><span class="n">like</span><span class="p">(</span><span class="s">'</span><span class="si">%e</span><span class="s">d'</span><span class="p">))</span><span class="o">.</span><span class="n">count</span><span class="p">()</span>
<div class='popup_sql'>SELECT count(1) AS count_1
FROM (SELECT users.id AS users_id, users.name AS users_name
FROM users
WHERE users.name LIKE ?) AS anon_1
('%ed',)</div><span class="mi">2</span></pre></div>
</div>
<p>...but if you look at the generated SQL, SQLAlchemy saw that we were placing individual column expressions and decided to wrap whatever it was we were doing in a subquery, so as to be assured that it returns the “number of rows”. This defensive behavior is not really needed here and in other cases is not what we want at all, such as if we wanted a grouping of counts per name:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><a href='#' class='sql_link'>sql</a><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="o">.</span><span class="n">name</span><span class="p">)</span><span class="o">.</span><span class="n">group_by</span><span class="p">(</span><span class="n">User</span><span class="o">.</span><span class="n">name</span><span class="p">)</span><span class="o">.</span><span class="n">count</span><span class="p">()</span>
<div class='popup_sql'>SELECT count(1) AS count_1
FROM (SELECT users.name AS users_name
FROM users GROUP BY users.name) AS anon_1
()</div><span class="mi">4</span></pre></div>
</div>
<p>We don’t want the number <tt class="docutils literal"><span class="pre">4</span></tt>, we wanted some rows back. So for detailed queries where you need to count something specific, use the <tt class="docutils literal"><span class="pre">func.count()</span></tt> function as a column expression:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="o">>>></span> <span class="kn">from</span> <span class="nn">sqlalchemy</span> <span class="kn">import</span> <span class="n">func</span>
<a href='#' class='sql_link'>sql</a><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">func</span><span class="o">.</span><span class="n">count</span><span class="p">(</span><span class="n">User</span><span class="o">.</span><span class="n">name</span><span class="p">),</span> <span class="n">User</span><span class="o">.</span><span class="n">name</span><span class="p">)</span><span class="o">.</span><span class="n">group_by</span><span class="p">(</span><span class="n">User</span><span class="o">.</span><span class="n">name</span><span class="p">)</span><span class="o">.</span><span class="n">all</span><span class="p">()</span>
<div class='popup_sql'>SELECT count(users.name) AS count_1, users.name AS users_name
FROM users GROUP BY users.name</div><span class="p">()</span>
<span class="p">[(</span><span class="mi">1</span><span class="p">,</span> <span class="s">u'ed'</span><span class="p">),</span> <span class="p">(</span><span class="mi">1</span><span class="p">,</span> <span class="s">u'fred'</span><span class="p">),</span> <span class="p">(</span><span class="mi">1</span><span class="p">,</span> <span class="s">u'mary'</span><span class="p">),</span> <span class="p">(</span><span class="mi">1</span><span class="p">,</span> <span class="s">u'wendy'</span><span class="p">)]</span></pre></div>
</div>
</div>
</div>
<div class="section" id="building-a-relationship">
<h2>Building a Relationship<a class="headerlink" href="#building-a-relationship" title="Permalink to this headline">¶</a></h2>
<p>Now let’s consider a second table to be dealt with. Users in our system also can store any number of email addresses associated with their username. This implies a basic one to many association from the <tt class="docutils literal"><span class="pre">users_table</span></tt> to a new table which stores email addresses, which we will call <tt class="docutils literal"><span class="pre">addresses</span></tt>. Using declarative, we define this table along with its mapped class, <tt class="docutils literal"><span class="pre">Address</span></tt>:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="o">>>></span> <span class="kn">from</span> <span class="nn">sqlalchemy</span> <span class="kn">import</span> <span class="n">ForeignKey</span>
<span class="o">>>></span> <span class="kn">from</span> <span class="nn">sqlalchemy.orm</span> <span class="kn">import</span> <span class="n">relationship</span><span class="p">,</span> <span class="n">backref</span>
<span class="o">>>></span> <span class="k">class</span> <span class="nc">Address</span><span class="p">(</span><span class="n">Base</span><span class="p">):</span>
<span class="o">...</span> <span class="n">__tablename__</span> <span class="o">=</span> <span class="s">'addresses'</span>
<span class="o">...</span> <span class="nb">id</span> <span class="o">=</span> <span class="n">Column</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="o">...</span> <span class="n">email_address</span> <span class="o">=</span> <span class="n">Column</span><span class="p">(</span><span class="n">String</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="o">...</span> <span class="n">user_id</span> <span class="o">=</span> <span class="n">Column</span><span class="p">(</span><span class="n">Integer</span><span class="p">,</span> <span class="n">ForeignKey</span><span class="p">(</span><span class="s">'users.id'</span><span class="p">))</span>
<span class="o">...</span>
<span class="o">...</span> <span class="n">user</span> <span class="o">=</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">'addresses'</span><span class="p">,</span> <span class="n">order_by</span><span class="o">=</span><span class="nb">id</span><span class="p">))</span>
<span class="o">...</span>
<span class="o">...</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">email_address</span><span class="p">):</span>
<span class="o">...</span> <span class="bp">self</span><span class="o">.</span><span class="n">email_address</span> <span class="o">=</span> <span class="n">email_address</span>
<span class="o">...</span>
<span class="o">...</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="o">...</span> <span class="k">return</span> <span class="s">"<Address('</span><span class="si">%s</span><span class="s">')>"</span> <span class="o">%</span> <span class="bp">self</span><span class="o">.</span><span class="n">email_address</span></pre></div>
</div>
<p>The above class introduces a <strong>foreign key</strong> constraint which references the <tt class="docutils literal"><span class="pre">users</span></tt> table. This defines for SQLAlchemy the relationship between the two tables at the database level. The relationship between the <tt class="docutils literal"><span class="pre">User</span></tt> and <tt class="docutils literal"><span class="pre">Address</span></tt> classes is defined separately using 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, which defines an attribute <tt class="docutils literal"><span class="pre">user</span></tt> to be placed on the <tt class="docutils literal"><span class="pre">Address</span></tt> class, as well as an <tt class="docutils literal"><span class="pre">addresses</span></tt> collection to be placed on the <tt class="docutils literal"><span class="pre">User</span></tt> class. Such a relationship is known as a <strong>bidirectional</strong> relationship. Because of the placement of the foreign key, from <tt class="docutils literal"><span class="pre">Address</span></tt> to <tt class="docutils literal"><span class="pre">User</span></tt> it is <strong>many to one</strong>, and from <tt class="docutils literal"><span class="pre">User</span></tt> to <tt class="docutils literal"><span class="pre">Address</span></tt> it is <strong>one to many</strong>. SQLAlchemy is automatically aware of many-to-one/one-to-many based on foreign keys.</p>
<div class="admonition note">
<p class="first admonition-title">Note</p>
<p class="last">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 has historically been 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>, which is the name that’s available in all versions of SQLAlchemy prior to 0.6beta2, including the 0.5 and 0.4 series. <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 only available starting with SQLAlchemy 0.6beta2. <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> will remain available in SQLAlchemy for the foreseeable future to enable cross-compatibility.</p>
</div>
<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 is extremely flexible, and could just have easily been defined on the <tt class="docutils literal"><span class="pre">User</span></tt> class:</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="n">Base</span><span class="p">):</span>
<span class="c"># ....</span>
<span class="n">addresses</span> <span class="o">=</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">Address</span><span class="o">.</span><span class="n">id</span><span class="p">,</span> <span class="n">backref</span><span class="o">=</span><span class="s">"user"</span><span class="p">)</span></pre></div>
</div>
<p>We are also free to not define a backref, and to define 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> only on one class and not the other. It is also possible to define two separate <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> constructs for either direction, which is generally safe for many-to-one and one-to-many relationships, but not for many-to-many relationships.</p>
<p>When using the <tt class="docutils literal"><span class="pre">declarative</span></tt> extension, <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> gives us the option to use strings for most arguments that concern the target class, in the case that the target class has not yet been defined. This <strong>only</strong> works in conjunction with <tt class="docutils literal"><span class="pre">declarative</span></tt>:</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="n">Base</span><span class="p">):</span>
<span class="o">....</span>
<span class="n">addresses</span> <span class="o">=</span> <span class="n">relationship</span><span class="p">(</span><span class="s">"Address"</span><span class="p">,</span> <span class="n">order_by</span><span class="o">=</span><span class="s">"Address.id"</span><span class="p">,</span> <span class="n">backref</span><span class="o">=</span><span class="s">"user"</span><span class="p">)</span></pre></div>
</div>
<p>When <tt class="docutils literal"><span class="pre">declarative</span></tt> is not in use, you typically define your <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> well after the target classes and <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> objects have been defined, so string expressions are not needed.</p>
<p>We’ll need to create the <tt class="docutils literal"><span class="pre">addresses</span></tt> table in the database, so we will issue another CREATE from our metadata, which will skip over tables which have already been created:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><a href='#' class='sql_link'>sql</a><span class="o">>>></span> <span class="n">metadata</span><span class="o">.</span><span class="n">create_all</span><span class="p">(</span><span class="n">engine</span><span class="p">)</span>
<div class='popup_sql'>PRAGMA table_info("users")
()
PRAGMA table_info("addresses")
()
CREATE TABLE addresses (
id INTEGER NOT NULL,
email_address VARCHAR NOT NULL,
user_id INTEGER,
PRIMARY KEY (id),
FOREIGN KEY(user_id) REFERENCES users (id)
)
()
COMMIT</div></pre></div>
</div>
</div>
<div class="section" id="working-with-related-objects">
<h2>Working with Related Objects<a class="headerlink" href="#working-with-related-objects" title="Permalink to this headline">¶</a></h2>
<p>Now when we create a <tt class="docutils literal"><span class="pre">User</span></tt>, a blank <tt class="docutils literal"><span class="pre">addresses</span></tt> collection will be present. Various collection types, such as sets and dictionaries, are possible here (see <a class="reference internal" href="mappers.html#advdatamapping-entitycollections"><em>Alternate Collection Implementations</em></a> for details), but by default, the collection is a Python list.</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="o">>>></span> <span class="n">jack</span> <span class="o">=</span> <span class="n">User</span><span class="p">(</span><span class="s">'jack'</span><span class="p">,</span> <span class="s">'Jack Bean'</span><span class="p">,</span> <span class="s">'gjffdd'</span><span class="p">)</span>
<span class="o">>>></span> <span class="n">jack</span><span class="o">.</span><span class="n">addresses</span>
<span class="p">[]</span></pre></div>
</div>
<p>We are free to add <tt class="docutils literal"><span class="pre">Address</span></tt> objects on our <tt class="docutils literal"><span class="pre">User</span></tt> object. In this case we just assign a full list directly:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="o">>>></span> <span class="n">jack</span><span class="o">.</span><span class="n">addresses</span> <span class="o">=</span> <span class="p">[</span><span class="n">Address</span><span class="p">(</span><span class="n">email_address</span><span class="o">=</span><span class="s">'jack@google.com'</span><span class="p">),</span> <span class="n">Address</span><span class="p">(</span><span class="n">email_address</span><span class="o">=</span><span class="s">'j25@yahoo.com'</span><span class="p">)]</span></pre></div>
</div>
<p>When using a bidirectional relationship, elements added in one direction automatically become visible in the other direction. This is the basic behavior of the <strong>backref</strong> keyword, which maintains the relationship purely in memory, without using any SQL:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="o">>>></span> <span class="n">jack</span><span class="o">.</span><span class="n">addresses</span><span class="p">[</span><span class="mi">1</span><span class="p">]</span>
<span class="o"><</span><span class="n">Address</span><span class="p">(</span><span class="s">'j25@yahoo.com'</span><span class="p">)</span><span class="o">></span>
<span class="o">>>></span> <span class="n">jack</span><span class="o">.</span><span class="n">addresses</span><span class="p">[</span><span class="mi">1</span><span class="p">]</span><span class="o">.</span><span class="n">user</span>
<span class="o"><</span><span class="n">User</span><span class="p">(</span><span class="s">'jack'</span><span class="p">,</span><span class="s">'Jack Bean'</span><span class="p">,</span> <span class="s">'gjffdd'</span><span class="p">)</span><span class="o">></span></pre></div>
</div>
<p>Let’s add and commit <tt class="docutils literal"><span class="pre">Jack</span> <span class="pre">Bean</span></tt> to the database. <tt class="docutils literal"><span class="pre">jack</span></tt> as well as the two <tt class="docutils literal"><span class="pre">Address</span></tt> members in his <tt class="docutils literal"><span class="pre">addresses</span></tt> collection are both added to the session at once, using a process known as <strong>cascading</strong>:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="o">>>></span> <span class="n">session</span><span class="o">.</span><span class="n">add</span><span class="p">(</span><span class="n">jack</span><span class="p">)</span>
<a href='#' class='sql_link'>sql</a><span class="o">>>></span> <span class="n">session</span><span class="o">.</span><span class="n">commit</span><span class="p">()</span>
<div class='popup_sql'>INSERT INTO users (name, fullname, password) VALUES (?, ?, ?)
('jack', 'Jack Bean', 'gjffdd')
INSERT INTO addresses (email_address, user_id) VALUES (?, ?)
('jack@google.com', 5)
INSERT INTO addresses (email_address, user_id) VALUES (?, ?)
('j25@yahoo.com', 5)
COMMIT</div></pre></div>
</div>
<p>Querying for Jack, we get just Jack back. No SQL is yet issued for Jack’s addresses:</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">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">one</span><span class="p">()</span>
<div class='popup_sql'>BEGIN
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',)</div><span class="o">>>></span> <span class="n">jack</span>
<span class="o"><</span><span class="n">User</span><span class="p">(</span><span class="s">'jack'</span><span class="p">,</span><span class="s">'Jack Bean'</span><span class="p">,</span> <span class="s">'gjffdd'</span><span class="p">)</span><span class="o">></span></pre></div>
</div>
<p>Let’s look at the <tt class="docutils literal"><span class="pre">addresses</span></tt> collection. Watch the SQL:</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 ORDER BY addresses.id
(5,)</div><span class="p">[</span><span class="o"><</span><span class="n">Address</span><span class="p">(</span><span class="s">'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">'j25@yahoo.com'</span><span class="p">)</span><span class="o">></span><span class="p">]</span></pre></div>
</div>
<p>When we accessed the <tt class="docutils literal"><span class="pre">addresses</span></tt> collection, SQL was suddenly issued. This is an example of a <strong>lazy loading relationship</strong>. The <tt class="docutils literal"><span class="pre">addresses</span></tt> collection is now loaded and behaves just like an ordinary list.</p>
<p>If you want to reduce the number of queries (dramatically, in many cases), we can apply an <strong>eager load</strong> to the query operation, 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> function. This function is a <strong>query option</strong> that gives additional instructions to the query on how we would like it to load, in this case we’d like to indicate that we’d like <tt class="docutils literal"><span class="pre">addresses</span></tt> to load “eagerly”. SQLAlchemy then constructs an outer join between the <tt class="docutils literal"><span class="pre">users</span></tt> and <tt class="docutils literal"><span class="pre">addresses</span></tt> tables, and loads them at once, populating the <tt class="docutils literal"><span class="pre">addresses</span></tt> collection on each <tt class="docutils literal"><span class="pre">User</span></tt> object if it’s not already populated:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="o">>>></span> <span class="kn">from</span> <span class="nn">sqlalchemy.orm</span> <span class="kn">import</span> <span class="n">joinedload</span>
<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="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="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">one</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, 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
FROM users LEFT OUTER JOIN addresses AS addresses_1 ON users.id = addresses_1.user_id
WHERE users.name = ? ORDER BY addresses_1.id
('jack',)</div><span class="o">>>></span> <span class="n">jack</span>
<span class="o"><</span><span class="n">User</span><span class="p">(</span><span class="s">'jack'</span><span class="p">,</span><span class="s">'Jack Bean'</span><span class="p">,</span> <span class="s">'gjffdd'</span><span class="p">)</span><span class="o">></span>
<span class="o">>>></span> <span class="n">jack</span><span class="o">.</span><span class="n">addresses</span>
<span class="p">[</span><span class="o"><</span><span class="n">Address</span><span class="p">(</span><span class="s">'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">'j25@yahoo.com'</span><span class="p">)</span><span class="o">></span><span class="p">]</span></pre></div>
</div>
<p>See <a class="reference internal" href="mappers.html#mapper-loader-strategies"><em>Configuring Loader Strategies: Lazy Loading, Eager Loading</em></a> for information on <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> and its new brother, <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>. We’ll also see another way to “eagerly” load in the next section.</p>
</div>
<div class="section" id="querying-with-joins">
<h2>Querying with Joins<a class="headerlink" href="#querying-with-joins" title="Permalink to this headline">¶</a></h2>
<p>While <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> created a JOIN specifically to populate a collection, we can also work explicitly with joins in many ways. For example, to construct a simple inner join between <tt class="docutils literal"><span class="pre">User</span></tt> and <tt class="docutils literal"><span class="pre">Address</span></tt>, we can just <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-meth docutils literal"><span class="pre">filter()</span></tt></a> their related columns together. Below we load the <tt class="docutils literal"><span class="pre">User</span></tt> and <tt class="docutils literal"><span class="pre">Address</span></tt> entities at once using this method:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><a href='#' class='sql_link'>sql</a><span class="o">>>></span> <span class="k">for</span> <span class="n">u</span><span class="p">,</span> <span class="n">a</span> <span class="ow">in</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="n">Address</span><span class="p">)</span><span class="o">.</span><span class="n">filter</span><span class="p">(</span><span class="n">User</span><span class="o">.</span><span class="n">id</span><span class="o">==</span><span class="n">Address</span><span class="o">.</span><span class="n">user_id</span><span class="p">)</span><span class="o">.</span>\
<span class="o">...</span> <span class="nb">filter</span><span class="p">(</span><span class="n">Address</span><span class="o">.</span><span class="n">email_address</span><span class="o">==</span><span class="s">'jack@google.com'</span><span class="p">)</span><span class="o">.</span><span class="n">all</span><span class="p">():</span>
<span class="o">...</span> <span class="k">print</span> <span class="n">u</span><span class="p">,</span> <span class="n">a</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, addresses.id AS addresses_id,
addresses.email_address AS addresses_email_address, addresses.user_id AS addresses_user_id
FROM users, addresses
WHERE users.id = addresses.user_id AND addresses.email_address = ?
('jack@google.com',)</div><span class="o"><</span><span class="n">User</span><span class="p">(</span><span class="s">'jack'</span><span class="p">,</span><span class="s">'Jack Bean'</span><span class="p">,</span> <span class="s">'gjffdd'</span><span class="p">)</span><span class="o">></span> <span class="o"><</span><span class="n">Address</span><span class="p">(</span><span class="s">'jack@google.com'</span><span class="p">)</span><span class="o">></span></pre></div>
</div>
<p>Or we can make a real JOIN construct; the most common way is to use <a class="reference internal" href="reference/orm/query.html#sqlalchemy.orm.query.Query.join" title="sqlalchemy.orm.query.Query.join"><tt class="xref py py-meth docutils literal"><span class="pre">join()</span></tt></a>:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><a href='#' class='sql_link'>sql</a><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">join</span><span class="p">(</span><span class="n">Address</span><span class="p">)</span><span class="o">.</span>\
<span class="o">...</span> <span class="nb">filter</span><span class="p">(</span><span class="n">Address</span><span class="o">.</span><span class="n">email_address</span><span class="o">==</span><span class="s">'jack@google.com'</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 JOIN addresses ON users.id = addresses.user_id
WHERE addresses.email_address = ?
('jack@google.com',)</div><span class="p">[</span><span class="o"><</span><span class="n">User</span><span class="p">(</span><span class="s">'jack'</span><span class="p">,</span><span class="s">'Jack Bean'</span><span class="p">,</span> <span class="s">'gjffdd'</span><span class="p">)</span><span class="o">></span><span class="p">]</span></pre></div>
</div>
<p><a class="reference internal" href="reference/orm/query.html#sqlalchemy.orm.query.Query.join" title="sqlalchemy.orm.query.Query.join"><tt class="xref py py-meth docutils literal"><span class="pre">join()</span></tt></a> knows how to join between <tt class="docutils literal"><span class="pre">User</span></tt> and <tt class="docutils literal"><span class="pre">Address</span></tt> because there’s only one foreign key between them. If there were no foreign keys, or several, <a class="reference internal" href="reference/orm/query.html#sqlalchemy.orm.query.Query.join" title="sqlalchemy.orm.query.Query.join"><tt class="xref py py-meth docutils literal"><span class="pre">join()</span></tt></a> works better when one of the following forms are used:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">query</span><span class="o">.</span><span class="n">join</span><span class="p">((</span><span class="n">Address</span><span class="p">,</span> <span class="n">User</span><span class="o">.</span><span class="n">id</span><span class="o">==</span><span class="n">Address</span><span class="o">.</span><span class="n">user_id</span><span class="p">))</span> <span class="c"># explicit condition (note the tuple)</span>
<span class="n">query</span><span class="o">.</span><span class="n">join</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="c"># specify relationship from left to right</span>
<span class="n">query</span><span class="o">.</span><span class="n">join</span><span class="p">((</span><span class="n">Address</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="c"># same, with explicit target</span>
<span class="n">query</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="c"># same, using a string</span></pre></div>
</div>
<p>Note that when <a class="reference internal" href="reference/orm/query.html#sqlalchemy.orm.query.Query.join" title="sqlalchemy.orm.query.Query.join"><tt class="xref py py-meth docutils literal"><span class="pre">join()</span></tt></a> is called with an explicit target as well as an ON clause, we use a tuple as the argument. This is so that multiple joins can be chained together, as in:</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">Foo</span><span class="p">)</span><span class="o">.</span><span class="n">join</span><span class="p">(</span>
<span class="n">Foo</span><span class="o">.</span><span class="n">bars</span><span class="p">,</span>
<span class="p">(</span><span class="n">Bat</span><span class="p">,</span> <span class="n">bar</span><span class="o">.</span><span class="n">bats</span><span class="p">),</span>
<span class="p">(</span><span class="n">Widget</span><span class="p">,</span> <span class="n">Bat</span><span class="o">.</span><span class="n">widget_id</span><span class="o">==</span><span class="n">Widget</span><span class="o">.</span><span class="n">id</span><span class="p">)</span>
<span class="p">)</span></pre></div>
</div>
<p>The above would produce SQL something like <tt class="docutils literal"><span class="pre">foo</span> <span class="pre">JOIN</span> <span class="pre">bars</span> <span class="pre">ON</span> <span class="pre"><onclause></span> <span class="pre">JOIN</span> <span class="pre">bats</span> <span class="pre">ON</span> <span class="pre"><onclause></span> <span class="pre">JOIN</span> <span class="pre">widgets</span> <span class="pre">ON</span> <span class="pre"><onclause></span></tt>.</p>
<p>The general functionality of <a class="reference internal" href="reference/orm/query.html#sqlalchemy.orm.query.Query.join" title="sqlalchemy.orm.query.Query.join"><tt class="xref py py-meth docutils literal"><span class="pre">join()</span></tt></a> is also available as a standalone function <a class="reference internal" href="reference/orm/query.html#sqlalchemy.orm.join" title="sqlalchemy.orm.join"><tt class="xref py py-func docutils literal"><span class="pre">join()</span></tt></a>, which is an ORM-enabled version of the same function present in the SQL expression language. This function accepts two or three arguments (left side, right side, optional ON clause) and can be used in conjunction with
the <a class="reference internal" href="reference/orm/query.html#sqlalchemy.orm.query.Query.select_from" title="sqlalchemy.orm.query.Query.select_from"><tt class="xref py py-meth docutils literal"><span class="pre">select_from()</span></tt></a> method to set an explicit FROM clause:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="o">>>></span> <span class="kn">from</span> <span class="nn">sqlalchemy.orm</span> <span class="kn">import</span> <span class="n">join</span>
<a href='#' class='sql_link'>sql</a><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="o">...</span> <span class="n">select_from</span><span class="p">(</span><span class="n">join</span><span class="p">(</span><span class="n">User</span><span class="p">,</span> <span class="n">Address</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="o">...</span> <span class="nb">filter</span><span class="p">(</span><span class="n">Address</span><span class="o">.</span><span class="n">email_address</span><span class="o">==</span><span class="s">'jack@google.com'</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 JOIN addresses ON users.id = addresses.user_id
WHERE addresses.email_address = ?
('jack@google.com',)</div><span class="p">[</span><span class="o"><</span><span class="n">User</span><span class="p">(</span><span class="s">'jack'</span><span class="p">,</span><span class="s">'Jack Bean'</span><span class="p">,</span> <span class="s">'gjffdd'</span><span class="p">)</span><span class="o">></span><span class="p">]</span></pre></div>
</div>
<div class="section" id="using-join-to-eagerly-load-collections-attributes">
<h3>Using join() to Eagerly Load Collections/Attributes<a class="headerlink" href="#using-join-to-eagerly-load-collections-attributes" title="Permalink to this headline">¶</a></h3>
<p>The “eager loading” capabilities of 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> function and the join-construction capabilities of <a class="reference internal" href="reference/orm/query.html#sqlalchemy.orm.query.Query.join" title="sqlalchemy.orm.query.Query.join"><tt class="xref py py-meth docutils literal"><span class="pre">join()</span></tt></a> or an equivalent can be combined together using 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 is typically used
for a query that is already joining to some related entity (more often than not via many-to-one), and you’d like the related entity to also be loaded onto the resulting objects
in one step without the need for additional queries and without the “automatic” join embedded
by 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> function:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="o">>>></span> <span class="kn">from</span> <span class="nn">sqlalchemy.orm</span> <span class="kn">import</span> <span class="n">contains_eager</span>
<a href='#' class='sql_link'>sql</a><span class="o">>>></span> <span class="k">for</span> <span class="n">address</span> <span class="ow">in</span> <span class="n">session</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="o">...</span> <span class="n">join</span><span class="p">(</span><span class="n">Address</span><span class="o">.</span><span class="n">user</span><span class="p">)</span><span class="o">.</span>\
<span class="o">...</span> <span class="nb">filter</span><span class="p">(</span><span class="n">User</span><span class="o">.</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="o">...</span> <span class="n">options</span><span class="p">(</span><span class="n">contains_eager</span><span class="p">(</span><span class="n">Address</span><span class="o">.</span><span class="n">user</span><span class="p">)):</span>
<span class="o">...</span> <span class="k">print</span> <span class="n">address</span><span class="p">,</span> <span class="n">address</span><span class="o">.</span><span class="n">user</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, addresses.id AS addresses_id,
addresses.email_address AS addresses_email_address, addresses.user_id AS addresses_user_id
FROM addresses JOIN users ON users.id = addresses.user_id
WHERE users.name = ?
('jack',)</div><span class="o"><</span><span class="n">Address</span><span class="p">(</span><span class="s">'jack@google.com'</span><span class="p">)</span><span class="o">></span> <span class="o"><</span><span class="n">User</span><span class="p">(</span><span class="s">'jack'</span><span class="p">,</span><span class="s">'Jack Bean'</span><span class="p">,</span> <span class="s">'gjffdd'</span><span class="p">)</span><span class="o">></span>
<span class="o"><</span><span class="n">Address</span><span class="p">(</span><span class="s">'j25@yahoo.com'</span><span class="p">)</span><span class="o">></span> <span class="o"><</span><span class="n">User</span><span class="p">(</span><span class="s">'jack'</span><span class="p">,</span><span class="s">'Jack Bean'</span><span class="p">,</span> <span class="s">'gjffdd'</span><span class="p">)</span><span class="o">></span></pre></div>
</div>
<p>Note that above the join was used both to limit the rows to just those <tt class="docutils literal"><span class="pre">Address</span></tt> objects which
had a related <tt class="docutils literal"><span class="pre">User</span></tt> object with the name “jack”. It’s safe to have the <tt class="docutils literal"><span class="pre">Address.user</span></tt> attribute populated with this user using an inner join. However, when filtering on a join that
is filtering on a particular member of a collection, using <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> to populate a related collection may populate the collection with only part of what it actually references, since the collection itself is filtered.</p>
</div>
<div class="section" id="using-aliases">
<h3>Using Aliases<a class="headerlink" href="#using-aliases" title="Permalink to this headline">¶</a></h3>
<p>When querying across multiple tables, if the same table needs to be referenced more than once, SQL typically requires that the table be <em>aliased</em> with another name, so that it can be distinguished against other occurrences of that table. 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> supports this most explicitly using the <tt class="docutils literal"><span class="pre">aliased</span></tt> construct. Below we join to the <tt class="docutils literal"><span class="pre">Address</span></tt> entity twice, to locate a user who has two distinct email addresses at the same time:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="o">>>></span> <span class="kn">from</span> <span class="nn">sqlalchemy.orm</span> <span class="kn">import</span> <span class="n">aliased</span>
<span class="o">>>></span> <span class="n">adalias1</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="o">>>></span> <span class="n">adalias2</span> <span class="o">=</span> <span class="n">aliased</span><span class="p">(</span><span class="n">Address</span><span class="p">)</span>
<a href='#' class='sql_link'>sql</a><span class="o">>>></span> <span class="k">for</span> <span class="n">username</span><span class="p">,</span> <span class="n">email1</span><span class="p">,</span> <span class="n">email2</span> <span class="ow">in</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="o">.</span><span class="n">name</span><span class="p">,</span> <span class="n">adalias1</span><span class="o">.</span><span class="n">email_address</span><span class="p">,</span> <span class="n">adalias2</span><span class="o">.</span><span class="n">email_address</span><span class="p">)</span><span class="o">.</span>\
<span class="o">...</span> <span class="n">join</span><span class="p">((</span><span class="n">adalias1</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="p">(</span><span class="n">adalias2</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="o">...</span> <span class="nb">filter</span><span class="p">(</span><span class="n">adalias1</span><span class="o">.</span><span class="n">email_address</span><span class="o">==</span><span class="s">'jack@google.com'</span><span class="p">)</span><span class="o">.</span>\
<span class="o">...</span> <span class="nb">filter</span><span class="p">(</span><span class="n">adalias2</span><span class="o">.</span><span class="n">email_address</span><span class="o">==</span><span class="s">'j25@yahoo.com'</span><span class="p">):</span>
<span class="o">...</span> <span class="k">print</span> <span class="n">username</span><span class="p">,</span> <span class="n">email1</span><span class="p">,</span> <span class="n">email2</span>
<div class='popup_sql'>SELECT users.name AS users_name, addresses_1.email_address AS addresses_1_email_address,
addresses_2.email_address AS addresses_2_email_address
FROM users JOIN addresses AS addresses_1 ON users.id = addresses_1.user_id
JOIN addresses AS addresses_2 ON users.id = addresses_2.user_id
WHERE addresses_1.email_address = ? AND addresses_2.email_address = ?
('jack@google.com', 'j25@yahoo.com')</div><span class="n">jack</span> <span class="n">jack</span><span class="nd">@google.com</span> <span class="n">j25</span><span class="nd">@yahoo.com</span></pre></div>
</div>
</div>
<div class="section" id="using-subqueries">
<h3>Using Subqueries<a class="headerlink" href="#using-subqueries" title="Permalink to this headline">¶</a></h3>
<p>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> is suitable for generating statements which can be used as subqueries. Suppose we wanted to load <tt class="docutils literal"><span class="pre">User</span></tt> objects along with a count of how many <tt class="docutils literal"><span class="pre">Address</span></tt> records each user has. The best way to generate SQL like this is to get the count of addresses grouped by user ids, and JOIN to the parent. In this case we use a LEFT OUTER JOIN so that we get rows back for those users who don’t have any addresses, e.g.:</p>
<div class="highlight-python"><pre>SELECT users.*, adr_count.address_count FROM users LEFT OUTER JOIN
(SELECT user_id, count(*) AS address_count FROM addresses GROUP BY user_id) AS adr_count
ON users.id=adr_count.user_id</pre>
</div>
<p>Using 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>, we build a statement like this from the inside out. The <tt class="docutils literal"><span class="pre">statement</span></tt> accessor returns a SQL expression representing the statement generated by a particular <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> - this is an instance of a <tt class="docutils literal"><span class="pre">select()</span></tt> construct, which are described in <a class="reference internal" href="sqlexpression.html"><em>SQL Expression Language Tutorial</em></a>:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="kn">from</span> <span class="nn">sqlalchemy.sql</span> <span class="kn">import</span> <span class="n">func</span>
<span class="gp">>>> </span><span class="n">stmt</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">Address</span><span class="o">.</span><span class="n">user_id</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="s">'*'</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="o">.</span><span class="n">group_by</span><span class="p">(</span><span class="n">Address</span><span class="o">.</span><span class="n">user_id</span><span class="p">)</span><span class="o">.</span><span class="n">subquery</span><span class="p">()</span></pre></div>
</div>
<p>The <tt class="docutils literal"><span class="pre">func</span></tt> keyword generates SQL functions, and the <tt class="docutils literal"><span class="pre">subquery()</span></tt> method on <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> produces a SQL expression construct representing a SELECT statement embedded within an alias (it’s actually shorthand for <tt class="docutils literal"><span class="pre">query.statement.alias()</span></tt>).</p>
<p>Once we have our statement, it behaves like 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, such as the one we created for <tt class="docutils literal"><span class="pre">users</span></tt> at the start of this tutorial. The columns on the statement are accessible through an attribute called <tt class="docutils literal"><span class="pre">c</span></tt>:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><a href='#' class='sql_link'>sql</a><span class="o">>>></span> <span class="k">for</span> <span class="n">u</span><span class="p">,</span> <span class="n">count</span> <span class="ow">in</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="n">stmt</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">address_count</span><span class="p">)</span><span class="o">.</span>\
<span class="o">...</span> <span class="n">outerjoin</span><span class="p">((</span><span class="n">stmt</span><span class="p">,</span> <span class="n">User</span><span class="o">.</span><span class="n">id</span><span class="o">==</span><span class="n">stmt</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">order_by</span><span class="p">(</span><span class="n">User</span><span class="o">.</span><span class="n">id</span><span class="p">):</span>
<span class="o">...</span> <span class="k">print</span> <span class="n">u</span><span class="p">,</span> <span class="n">count</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,
anon_1.address_count AS anon_1_address_count
FROM users LEFT OUTER JOIN (SELECT addresses.user_id AS user_id, count(?) AS address_count
FROM addresses GROUP BY addresses.user_id) AS anon_1 ON users.id = anon_1.user_id
ORDER BY users.id
('*',)</div><span class="o"><</span><span class="n">User</span><span class="p">(</span><span class="s">'ed'</span><span class="p">,</span><span class="s">'Ed Jones'</span><span class="p">,</span> <span class="s">'f8s7ccs'</span><span class="p">)</span><span class="o">></span> <span class="bp">None</span>
<span class="o"><</span><span class="n">User</span><span class="p">(</span><span class="s">'wendy'</span><span class="p">,</span><span class="s">'Wendy Williams'</span><span class="p">,</span> <span class="s">'foobar'</span><span class="p">)</span><span class="o">></span> <span class="bp">None</span>
<span class="o"><</span><span class="n">User</span><span class="p">(</span><span class="s">'mary'</span><span class="p">,</span><span class="s">'Mary Contrary'</span><span class="p">,</span> <span class="s">'xxg527'</span><span class="p">)</span><span class="o">></span> <span class="bp">None</span>
<span class="o"><</span><span class="n">User</span><span class="p">(</span><span class="s">'fred'</span><span class="p">,</span><span class="s">'Fred Flinstone'</span><span class="p">,</span> <span class="s">'blah'</span><span class="p">)</span><span class="o">></span> <span class="bp">None</span>
<span class="o"><</span><span class="n">User</span><span class="p">(</span><span class="s">'jack'</span><span class="p">,</span><span class="s">'Jack Bean'</span><span class="p">,</span> <span class="s">'gjffdd'</span><span class="p">)</span><span class="o">></span> <span class="mi">2</span></pre></div>
</div>
</div>
<div class="section" id="selecting-entities-from-subqueries">
<h3>Selecting Entities from Subqueries<a class="headerlink" href="#selecting-entities-from-subqueries" title="Permalink to this headline">¶</a></h3>
<p>Above, we just selected a result that included a column from a subquery. What if we wanted our subquery to map to an entity ? For this we use <tt class="docutils literal"><span class="pre">aliased()</span></tt> to associate an “alias” of a mapped class to a subquery:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><a href='#' class='sql_link'>sql</a><span class="o">>>></span> <span class="n">stmt</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">Address</span><span class="p">)</span><span class="o">.</span><span class="n">filter</span><span class="p">(</span><span class="n">Address</span><span class="o">.</span><span class="n">email_address</span> <span class="o">!=</span> <span class="s">'j25@yahoo.com'</span><span class="p">)</span><span class="o">.</span><span class="n">subquery</span><span class="p">()</span>
<span class="o">>>></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="n">stmt</span><span class="p">)</span>
<span class="o">>>></span> <span class="k">for</span> <span class="n">user</span><span class="p">,</span> <span class="n">address</span> <span class="ow">in</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="n">adalias</span><span class="p">)</span><span class="o">.</span><span class="n">join</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="k">print</span> <span class="n">user</span><span class="p">,</span> <span class="n">address</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, anon_1.id AS anon_1_id,
anon_1.email_address AS anon_1_email_address, anon_1.user_id AS anon_1_user_id
FROM users JOIN (SELECT addresses.id AS id, addresses.email_address AS email_address, addresses.user_id AS user_id
FROM addresses
WHERE addresses.email_address != ?) AS anon_1 ON users.id = anon_1.user_id
('j25@yahoo.com',)</div><span class="o"><</span><span class="n">User</span><span class="p">(</span><span class="s">'jack'</span><span class="p">,</span><span class="s">'Jack Bean'</span><span class="p">,</span> <span class="s">'gjffdd'</span><span class="p">)</span><span class="o">></span> <span class="o"><</span><span class="n">Address</span><span class="p">(</span><span class="s">'jack@google.com'</span><span class="p">)</span><span class="o">></span></pre></div>
</div>
</div>
<div class="section" id="using-exists">
<h3>Using EXISTS<a class="headerlink" href="#using-exists" title="Permalink to this headline">¶</a></h3>
<p>The EXISTS keyword in SQL is a boolean operator which returns True if the given expression contains any rows. It may be used in many scenarios in place of joins, and is also useful for locating rows which do not have a corresponding row in a related table.</p>
<p>There is an explicit EXISTS construct, which looks like this:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="o">>>></span> <span class="kn">from</span> <span class="nn">sqlalchemy.sql</span> <span class="kn">import</span> <span class="n">exists</span>
<span class="o">>>></span> <span class="n">stmt</span> <span class="o">=</span> <span class="n">exists</span><span class="p">()</span><span class="o">.</span><span class="n">where</span><span class="p">(</span><span class="n">Address</span><span class="o">.</span><span class="n">user_id</span><span class="o">==</span><span class="n">User</span><span class="o">.</span><span class="n">id</span><span class="p">)</span>
<a href='#' class='sql_link'>sql</a><span class="o">>>></span> <span class="k">for</span> <span class="n">name</span><span class="p">,</span> <span class="ow">in</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="o">.</span><span class="n">name</span><span class="p">)</span><span class="o">.</span><span class="n">filter</span><span class="p">(</span><span class="n">stmt</span><span class="p">):</span>
<span class="o">...</span> <span class="k">print</span> <span class="n">name</span>
<div class='popup_sql'>SELECT users.name AS users_name
FROM users
WHERE EXISTS (SELECT *
FROM addresses
WHERE addresses.user_id = users.id)
()</div><span class="n">jack</span></pre></div>
</div>
<p>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> features several operators which make usage of EXISTS automatically. Above, the statement can be expressed along the <tt class="docutils literal"><span class="pre">User.addresses</span></tt> relationship using <tt class="docutils literal"><span class="pre">any()</span></tt>:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><a href='#' class='sql_link'>sql</a><span class="o">>>></span> <span class="k">for</span> <span class="n">name</span><span class="p">,</span> <span class="ow">in</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="o">.</span><span class="n">name</span><span class="p">)</span><span class="o">.</span><span class="n">filter</span><span class="p">(</span><span class="n">User</span><span class="o">.</span><span class="n">addresses</span><span class="o">.</span><span class="n">any</span><span class="p">()):</span>
<span class="o">...</span> <span class="k">print</span> <span class="n">name</span>
<div class='popup_sql'>SELECT users.name AS users_name
FROM users
WHERE EXISTS (SELECT 1
FROM addresses
WHERE users.id = addresses.user_id)
()</div><span class="n">jack</span></pre></div>
</div>
<p><tt class="docutils literal"><span class="pre">any()</span></tt> takes criterion as well, to limit the rows matched:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><a href='#' class='sql_link'>sql</a><span class="o">>>></span> <span class="k">for</span> <span class="n">name</span><span class="p">,</span> <span class="ow">in</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="o">.</span><span class="n">name</span><span class="p">)</span><span class="o">.</span>\
<span class="o">...</span> <span class="nb">filter</span><span class="p">(</span><span class="n">User</span><span class="o">.</span><span class="n">addresses</span><span class="o">.</span><span class="n">any</span><span class="p">(</span><span class="n">Address</span><span class="o">.</span><span class="n">email_address</span><span class="o">.</span><span class="n">like</span><span class="p">(</span><span class="s">'</span><span class="si">%g</span><span class="s">oogle%'</span><span class="p">))):</span>
<span class="o">...</span> <span class="k">print</span> <span class="n">name</span>
<div class='popup_sql'>SELECT users.name AS users_name
FROM users
WHERE EXISTS (SELECT 1
FROM addresses
WHERE users.id = addresses.user_id AND addresses.email_address LIKE ?)
('%google%',)</div><span class="n">jack</span></pre></div>
</div>
<p><tt class="docutils literal"><span class="pre">has()</span></tt> is the same operator as <tt class="docutils literal"><span class="pre">any()</span></tt> for many-to-one relationships (note the <tt class="docutils literal"><span class="pre">~</span></tt> operator here too, which means “NOT”):</p>
<div class="highlight-python+sql"><div class="highlight"><pre><a href='#' class='sql_link'>sql</a><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">Address</span><span class="p">)</span><span class="o">.</span><span class="n">filter</span><span class="p">(</span><span class="o">~</span><span class="n">Address</span><span class="o">.</span><span class="n">user</span><span class="o">.</span><span class="n">has</span><span class="p">(</span><span class="n">User</span><span class="o">.</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.id AS addresses_id, addresses.email_address AS addresses_email_address,
addresses.user_id AS addresses_user_id
FROM addresses
WHERE NOT (EXISTS (SELECT 1
FROM users
WHERE users.id = addresses.user_id AND users.name = ?))
('jack',)</div><span class="p">[]</span></pre></div>
</div>
</div>
<div class="section" id="common-relationship-operators">
<h3>Common Relationship Operators<a class="headerlink" href="#common-relationship-operators" title="Permalink to this headline">¶</a></h3>
<p>Here’s all the operators which build on relationships:</p>
<ul>
<li><p class="first">equals (used for many-to-one):</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">query</span><span class="o">.</span><span class="n">filter</span><span class="p">(</span><span class="n">Address</span><span class="o">.</span><span class="n">user</span> <span class="o">==</span> <span class="n">someuser</span><span class="p">)</span></pre></div>
</div>
</li>
<li><p class="first">not equals (used for many-to-one):</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">query</span><span class="o">.</span><span class="n">filter</span><span class="p">(</span><span class="n">Address</span><span class="o">.</span><span class="n">user</span> <span class="o">!=</span> <span class="n">someuser</span><span class="p">)</span></pre></div>
</div>
</li>
<li><p class="first">IS NULL (used for many-to-one):</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">query</span><span class="o">.</span><span class="n">filter</span><span class="p">(</span><span class="n">Address</span><span class="o">.</span><span class="n">user</span> <span class="o">==</span> <span class="bp">None</span><span class="p">)</span></pre></div>
</div>
</li>
<li><p class="first">contains (used for one-to-many and many-to-many collections):</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">query</span><span class="o">.</span><span class="n">filter</span><span class="p">(</span><span class="n">User</span><span class="o">.</span><span class="n">addresses</span><span class="o">.</span><span class="n">contains</span><span class="p">(</span><span class="n">someaddress</span><span class="p">))</span></pre></div>
</div>
</li>
<li><p class="first">any (used for one-to-many and many-to-many collections):</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">query</span><span class="o">.</span><span class="n">filter</span><span class="p">(</span><span class="n">User</span><span class="o">.</span><span class="n">addresses</span><span class="o">.</span><span class="n">any</span><span class="p">(</span><span class="n">Address</span><span class="o">.</span><span class="n">email_address</span> <span class="o">==</span> <span class="s">'bar'</span><span class="p">))</span>
<span class="c"># also takes keyword arguments:</span>
<span class="n">query</span><span class="o">.</span><span class="n">filter</span><span class="p">(</span><span class="n">User</span><span class="o">.</span><span class="n">addresses</span><span class="o">.</span><span class="n">any</span><span class="p">(</span><span class="n">email_address</span><span class="o">=</span><span class="s">'bar'</span><span class="p">))</span></pre></div>
</div>
</li>
<li><p class="first">has (used for many-to-one):</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">query</span><span class="o">.</span><span class="n">filter</span><span class="p">(</span><span class="n">Address</span><span class="o">.</span><span class="n">user</span><span class="o">.</span><span class="n">has</span><span class="p">(</span><span class="n">name</span><span class="o">=</span><span class="s">'ed'</span><span class="p">))</span></pre></div>
</div>
</li>
<li><p class="first">with_parent (used for any relationship):</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">Address</span><span class="p">)</span><span class="o">.</span><span class="n">with_parent</span><span class="p">(</span><span class="n">someuser</span><span class="p">,</span> <span class="s">'addresses'</span><span class="p">)</span></pre></div>
</div>
</li>
</ul>
</div>
</div>
<div class="section" id="deleting">
<h2>Deleting<a class="headerlink" href="#deleting" title="Permalink to this headline">¶</a></h2>
<p>Let’s try to delete <tt class="docutils literal"><span class="pre">jack</span></tt> and see how that goes. We’ll mark as deleted in the session, then we’ll issue a <tt class="docutils literal"><span class="pre">count</span></tt> query to see that no rows remain:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="o">>>></span> <span class="n">session</span><span class="o">.</span><span class="n">delete</span><span class="p">(</span><span class="n">jack</span><span class="p">)</span>
<a href='#' class='sql_link'>sql</a><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">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">count</span><span class="p">()</span>
<div class='popup_sql'>UPDATE addresses SET user_id=? WHERE addresses.id = ?
(None, 1)
UPDATE addresses SET user_id=? WHERE addresses.id = ?
(None, 2)
DELETE FROM users WHERE users.id = ?
(5,)
SELECT count(1) AS count_1
FROM users
WHERE users.name = ?
('jack',)</div><span class="mi">0</span></pre></div>
</div>
<p>So far, so good. How about Jack’s <tt class="docutils literal"><span class="pre">Address</span></tt> 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">session</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">filter</span><span class="p">(</span>
<span class="o">...</span> <span class="n">Address</span><span class="o">.</span><span class="n">email_address</span><span class="o">.</span><span class="n">in_</span><span class="p">([</span><span class="s">'jack@google.com'</span><span class="p">,</span> <span class="s">'j25@yahoo.com'</span><span class="p">])</span>
<span class="o">...</span> <span class="p">)</span><span class="o">.</span><span class="n">count</span><span class="p">()</span>
<div class='popup_sql'>SELECT count(1) AS count_1
FROM addresses
WHERE addresses.email_address IN (?, ?)
('jack@google.com', 'j25@yahoo.com')</div><span class="mi">2</span></pre></div>
</div>
<p>Uh oh, they’re still there ! Analyzing the flush SQL, we can see that the <tt class="docutils literal"><span class="pre">user_id</span></tt> column of each address was set to NULL, but the rows weren’t deleted. SQLAlchemy doesn’t assume that deletes cascade, you have to tell it to do so.</p>
<div class="section" id="configuring-delete-delete-orphan-cascade">
<h3>Configuring delete/delete-orphan Cascade<a class="headerlink" href="#configuring-delete-delete-orphan-cascade" title="Permalink to this headline">¶</a></h3>
<p>We will configure <strong>cascade</strong> options on the <tt class="docutils literal"><span class="pre">User.addresses</span></tt> relationship to change the behavior. While SQLAlchemy allows you to add new attributes and relationships to mappings at any point in time, in this case the existing relationship needs to be removed, so we need to tear down the mappings completely and start again. This is not a typical operation and is here just for illustrative purposes.</p>
<p>Removing all ORM state is as follows:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="o">>>></span> <span class="n">session</span><span class="o">.</span><span class="n">close</span><span class="p">()</span> <span class="c"># roll back and close the transaction</span>
<span class="o">>>></span> <span class="kn">from</span> <span class="nn">sqlalchemy.orm</span> <span class="kn">import</span> <span class="n">clear_mappers</span>
<span class="o">>>></span> <span class="n">clear_mappers</span><span class="p">()</span> <span class="c"># clear mappers</span></pre></div>
</div>
<p>Below, we use <tt class="docutils literal"><span class="pre">mapper()</span></tt> to reconfigure an ORM mapping for <tt class="docutils literal"><span class="pre">User</span></tt> and <tt class="docutils literal"><span class="pre">Address</span></tt>, on our existing but currently un-mapped classes. The <tt class="docutils literal"><span class="pre">User.addresses</span></tt> relationship now has <tt class="docutils literal"><span class="pre">delete,</span> <span class="pre">delete-orphan</span></tt> cascade on it, which indicates that DELETE operations will cascade to attached <tt class="docutils literal"><span class="pre">Address</span></tt> objects as well as <tt class="docutils literal"><span class="pre">Address</span></tt> objects which are removed from their parent:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><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">properties</span><span class="o">=</span><span class="p">{</span>
<span class="o">...</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">backref</span><span class="o">=</span><span class="s">'user'</span><span class="p">,</span> <span class="n">cascade</span><span class="o">=</span><span class="s">"all, delete, delete-orphan"</span><span class="p">)</span>
<span class="o">...</span> <span class="p">})</span>
<span class="o"><</span><span class="n">Mapper</span> <span class="n">at</span> <span class="mi">0</span><span class="n">x</span><span class="o">...</span><span class="p">;</span> <span class="n">User</span><span class="o">></span>
<span class="o">>>></span> <span class="n">addresses_table</span> <span class="o">=</span> <span class="n">Address</span><span class="o">.</span><span class="n">__table__</span>
<span class="o">>>></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="o"><</span><span class="n">Mapper</span> <span class="n">at</span> <span class="mi">0</span><span class="n">x</span><span class="o">...</span><span class="p">;</span> <span class="n">Address</span><span class="o">></span></pre></div>
</div>
<p>Now when we load Jack (below using <tt class="docutils literal"><span class="pre">get()</span></tt>, which loads by primary key), removing an address from his <tt class="docutils literal"><span class="pre">addresses</span></tt> collection will result in that <tt class="docutils literal"><span class="pre">Address</span></tt> being deleted:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="c"># load Jack by primary key</span>
<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">get</span><span class="p">(</span><span class="mi">5</span><span class="p">)</span>
<div class='popup_sql'>BEGIN
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.id = ?
(5,)</div>
<span class="c"># remove one Address (lazy load fires off)</span>
<a href='#' class='sql_link'>sql</a><span class="o">>>></span> <span class="k">del</span> <span class="n">jack</span><span class="o">.</span><span class="n">addresses</span><span class="p">[</span><span class="mi">1</span><span class="p">]</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="c"># only one address remains</span>
<a href='#' class='sql_link'>sql</a><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">Address</span><span class="p">)</span><span class="o">.</span><span class="n">filter</span><span class="p">(</span>
<span class="o">...</span> <span class="n">Address</span><span class="o">.</span><span class="n">email_address</span><span class="o">.</span><span class="n">in_</span><span class="p">([</span><span class="s">'jack@google.com'</span><span class="p">,</span> <span class="s">'j25@yahoo.com'</span><span class="p">])</span>
<span class="o">...</span> <span class="p">)</span><span class="o">.</span><span class="n">count</span><span class="p">()</span>
<div class='popup_sql'>DELETE FROM addresses WHERE addresses.id = ?
(2,)
SELECT count(1) AS count_1
FROM addresses
WHERE addresses.email_address IN (?, ?)
('jack@google.com', 'j25@yahoo.com')</div><span class="mi">1</span></pre></div>
</div>
<p>Deleting Jack will delete both Jack and his remaining <tt class="docutils literal"><span class="pre">Address</span></tt>:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="o">>>></span> <span class="n">session</span><span class="o">.</span><span class="n">delete</span><span class="p">(</span><span class="n">jack</span><span class="p">)</span>
<a href='#' class='sql_link'>sql</a><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">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">count</span><span class="p">()</span>
<div class='popup_sql'>DELETE FROM addresses WHERE addresses.id = ?
(1,)
DELETE FROM users WHERE users.id = ?
(5,)
SELECT count(1) AS count_1
FROM users
WHERE users.name = ?
('jack',)</div><span class="mi">0</span>
<a href='#' class='sql_link'>sql</a><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">Address</span><span class="p">)</span><span class="o">.</span><span class="n">filter</span><span class="p">(</span>
<span class="o">...</span> <span class="n">Address</span><span class="o">.</span><span class="n">email_address</span><span class="o">.</span><span class="n">in_</span><span class="p">([</span><span class="s">'jack@google.com'</span><span class="p">,</span> <span class="s">'j25@yahoo.com'</span><span class="p">])</span>
<span class="o">...</span> <span class="p">)</span><span class="o">.</span><span class="n">count</span><span class="p">()</span>
<div class='popup_sql'>SELECT count(1) AS count_1
FROM addresses
WHERE addresses.email_address IN (?, ?)
('jack@google.com', 'j25@yahoo.com')</div><span class="mi">0</span></pre></div>
</div>
</div>
</div>
<div class="section" id="building-a-many-to-many-relationship">
<h2>Building a Many To Many Relationship<a class="headerlink" href="#building-a-many-to-many-relationship" title="Permalink to this headline">¶</a></h2>
<p>We’re moving into the bonus round here, but lets show off a many-to-many relationship. We’ll sneak in some other features too, just to take a tour. We’ll make our application a blog application, where users can write <tt class="docutils literal"><span class="pre">BlogPost</span></tt> items, which have <tt class="docutils literal"><span class="pre">Keyword</span></tt> items associated with them.</p>
<p>The declarative setup is as follows:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="o">>>></span> <span class="kn">from</span> <span class="nn">sqlalchemy</span> <span class="kn">import</span> <span class="n">Text</span>
<span class="o">>>></span> <span class="c"># association table</span>
<span class="o">>>></span> <span class="n">post_keywords</span> <span class="o">=</span> <span class="n">Table</span><span class="p">(</span><span class="s">'post_keywords'</span><span class="p">,</span> <span class="n">metadata</span><span class="p">,</span>
<span class="o">...</span> <span class="n">Column</span><span class="p">(</span><span class="s">'post_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">'posts.id'</span><span class="p">)),</span>
<span class="o">...</span> <span class="n">Column</span><span class="p">(</span><span class="s">'keyword_id'</span><span class="p">,</span> <span class="n">Integer</span><span class="p">,</span> <span class="n">ForeignKey</span><span class="p">(</span><span class="s">'keywords.id'</span><span class="p">))</span>
<span class="o">...</span> <span class="p">)</span>
<span class="o">>>></span> <span class="k">class</span> <span class="nc">BlogPost</span><span class="p">(</span><span class="n">Base</span><span class="p">):</span>
<span class="o">...</span> <span class="n">__tablename__</span> <span class="o">=</span> <span class="s">'posts'</span>
<span class="o">...</span>
<span class="o">...</span> <span class="nb">id</span> <span class="o">=</span> <span class="n">Column</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="o">...</span> <span class="n">user_id</span> <span class="o">=</span> <span class="n">Column</span><span class="p">(</span><span class="n">Integer</span><span class="p">,</span> <span class="n">ForeignKey</span><span class="p">(</span><span class="s">'users.id'</span><span class="p">))</span>
<span class="o">...</span> <span class="n">headline</span> <span class="o">=</span> <span class="n">Column</span><span class="p">(</span><span class="n">String</span><span class="p">(</span><span class="mi">255</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="o">...</span> <span class="n">body</span> <span class="o">=</span> <span class="n">Column</span><span class="p">(</span><span class="n">Text</span><span class="p">)</span>
<span class="o">...</span>
<span class="o">...</span> <span class="c"># many to many BlogPost<->Keyword</span>
<span class="o">...</span> <span class="n">keywords</span> <span class="o">=</span> <span class="n">relationship</span><span class="p">(</span><span class="s">'Keyword'</span><span class="p">,</span> <span class="n">secondary</span><span class="o">=</span><span class="n">post_keywords</span><span class="p">,</span> <span class="n">backref</span><span class="o">=</span><span class="s">'posts'</span><span class="p">)</span>
<span class="o">...</span>
<span class="o">...</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">headline</span><span class="p">,</span> <span class="n">body</span><span class="p">,</span> <span class="n">author</span><span class="p">):</span>
<span class="o">...</span> <span class="bp">self</span><span class="o">.</span><span class="n">author</span> <span class="o">=</span> <span class="n">author</span>
<span class="o">...</span> <span class="bp">self</span><span class="o">.</span><span class="n">headline</span> <span class="o">=</span> <span class="n">headline</span>
<span class="o">...</span> <span class="bp">self</span><span class="o">.</span><span class="n">body</span> <span class="o">=</span> <span class="n">body</span>
<span class="o">...</span>
<span class="o">...</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="o">...</span> <span class="k">return</span> <span class="s">"BlogPost(</span><span class="si">%r</span><span class="s">, </span><span class="si">%r</span><span class="s">, </span><span class="si">%r</span><span class="s">)"</span> <span class="o">%</span> <span class="p">(</span><span class="bp">self</span><span class="o">.</span><span class="n">headline</span><span class="p">,</span> <span class="bp">self</span><span class="o">.</span><span class="n">body</span><span class="p">,</span> <span class="bp">self</span><span class="o">.</span><span class="n">author</span><span class="p">)</span>
<span class="o">>>></span> <span class="k">class</span> <span class="nc">Keyword</span><span class="p">(</span><span class="n">Base</span><span class="p">):</span>
<span class="o">...</span> <span class="n">__tablename__</span> <span class="o">=</span> <span class="s">'keywords'</span>
<span class="o">...</span>
<span class="o">...</span> <span class="nb">id</span> <span class="o">=</span> <span class="n">Column</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="o">...</span> <span class="n">keyword</span> <span class="o">=</span> <span class="n">Column</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">nullable</span><span class="o">=</span><span class="bp">False</span><span class="p">,</span> <span class="n">unique</span><span class="o">=</span><span class="bp">True</span><span class="p">)</span>
<span class="o">...</span>
<span class="o">...</span> <span class="k">def</span> <span class="nf">__init__</span><span class="p">(</span><span class="bp">self</span><span class="p">,</span> <span class="n">keyword</span><span class="p">):</span>
<span class="o">...</span> <span class="bp">self</span><span class="o">.</span><span class="n">keyword</span> <span class="o">=</span> <span class="n">keyword</span></pre></div>
</div>
<p>Above, the many-to-many relationship is <tt class="docutils literal"><span class="pre">BlogPost.keywords</span></tt>. The defining feature of a many-to-many relationship is the <tt class="docutils literal"><span class="pre">secondary</span></tt> keyword argument which references 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> object representing the association table. This table only contains columns which reference the two sides of the relationship; if it has <em>any</em> other columns, such as its own primary key, or foreign keys to other tables, SQLAlchemy requires a different usage pattern called the “association object”, described at <a class="reference internal" href="mappers.html#association-pattern"><em>Association Object</em></a>.</p>
<p>The many-to-many relationship is also bi-directional using the <tt class="docutils literal"><span class="pre">backref</span></tt> keyword. This is the one case where usage of <tt class="docutils literal"><span class="pre">backref</span></tt> is generally required, since if a separate <tt class="docutils literal"><span class="pre">posts</span></tt> relationship were added to the <tt class="docutils literal"><span class="pre">Keyword</span></tt> entity, both relationships would independently add and remove rows from the <tt class="docutils literal"><span class="pre">post_keywords</span></tt> table and produce conflicts.</p>
<p>We would also like our <tt class="docutils literal"><span class="pre">BlogPost</span></tt> class to have an <tt class="docutils literal"><span class="pre">author</span></tt> field. We will add this as another bidirectional relationship, except one issue we’ll have is that a single user might have lots of blog posts. When we access <tt class="docutils literal"><span class="pre">User.posts</span></tt>, we’d like to be able to filter results further so as not to load the entire collection. For this we use a setting accepted by <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> called <tt class="docutils literal"><span class="pre">lazy='dynamic'</span></tt>, which configures an alternate <strong>loader strategy</strong> on the attribute. To use it on the “reverse” side of 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>, we use the <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> function:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="o">>>></span> <span class="kn">from</span> <span class="nn">sqlalchemy.orm</span> <span class="kn">import</span> <span class="n">backref</span>
<span class="o">>>></span> <span class="c"># "dynamic" loading relationship to User</span>
<span class="o">>>></span> <span class="n">BlogPost</span><span class="o">.</span><span class="n">author</span> <span class="o">=</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></pre></div>
</div>
<p>Create new tables:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><a href='#' class='sql_link'>sql</a><span class="o">>>></span> <span class="n">metadata</span><span class="o">.</span><span class="n">create_all</span><span class="p">(</span><span class="n">engine</span><span class="p">)</span>
<div class='popup_sql'>PRAGMA table_info("users")
()
PRAGMA table_info("addresses")
()
PRAGMA table_info("posts")
()
PRAGMA table_info("keywords")
()
PRAGMA table_info("post_keywords")
()
CREATE TABLE posts (
id INTEGER NOT NULL,
user_id INTEGER,
headline VARCHAR(255) NOT NULL,
body TEXT,
PRIMARY KEY (id),
FOREIGN KEY(user_id) REFERENCES users (id)
)
()
COMMIT
CREATE TABLE keywords (
id INTEGER NOT NULL,
keyword VARCHAR(50) NOT NULL,
PRIMARY KEY (id),
UNIQUE (keyword)
)
()
COMMIT
CREATE TABLE post_keywords (
post_id INTEGER,
keyword_id INTEGER,
FOREIGN KEY(post_id) REFERENCES posts (id),
FOREIGN KEY(keyword_id) REFERENCES keywords (id)
)
()
COMMIT</div></pre></div>
</div>
<p>Usage is not too different from what we’ve been doing. Let’s give Wendy some blog posts:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><a href='#' class='sql_link'>sql</a><span class="o">>>></span> <span class="n">wendy</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">filter_by</span><span class="p">(</span><span class="n">name</span><span class="o">=</span><span class="s">'wendy'</span><span class="p">)</span><span class="o">.</span><span class="n">one</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 = ?
('wendy',)</div><span class="o">>>></span> <span class="n">post</span> <span class="o">=</span> <span class="n">BlogPost</span><span class="p">(</span><span class="s">"Wendy's Blog Post"</span><span class="p">,</span> <span class="s">"This is a test"</span><span class="p">,</span> <span class="n">wendy</span><span class="p">)</span>
<span class="o">>>></span> <span class="n">session</span><span class="o">.</span><span class="n">add</span><span class="p">(</span><span class="n">post</span><span class="p">)</span></pre></div>
</div>
<p>We’re storing keywords uniquely in the database, but we know that we don’t have any yet, so we can just create them:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="o">>>></span> <span class="n">post</span><span class="o">.</span><span class="n">keywords</span><span class="o">.</span><span class="n">append</span><span class="p">(</span><span class="n">Keyword</span><span class="p">(</span><span class="s">'wendy'</span><span class="p">))</span>
<span class="o">>>></span> <span class="n">post</span><span class="o">.</span><span class="n">keywords</span><span class="o">.</span><span class="n">append</span><span class="p">(</span><span class="n">Keyword</span><span class="p">(</span><span class="s">'firstpost'</span><span class="p">))</span></pre></div>
</div>
<p>We can now look up all blog posts with the keyword ‘firstpost’. We’ll use the <tt class="docutils literal"><span class="pre">any</span></tt> operator to locate “blog posts where any of its keywords has the keyword string ‘firstpost’”:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><a href='#' class='sql_link'>sql</a><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">BlogPost</span><span class="p">)</span><span class="o">.</span><span class="n">filter</span><span class="p">(</span><span class="n">BlogPost</span><span class="o">.</span><span class="n">keywords</span><span class="o">.</span><span class="n">any</span><span class="p">(</span><span class="n">keyword</span><span class="o">=</span><span class="s">'firstpost'</span><span class="p">))</span><span class="o">.</span><span class="n">all</span><span class="p">()</span>
<div class='popup_sql'>INSERT INTO keywords (keyword) VALUES (?)
('wendy',)
INSERT INTO keywords (keyword) VALUES (?)
('firstpost',)
INSERT INTO posts (user_id, headline, body) VALUES (?, ?, ?)
(2, "Wendy's Blog Post", 'This is a test')
INSERT INTO post_keywords (post_id, keyword_id) VALUES (?, ?)
((1, 1), (1, 2))
SELECT posts.id AS posts_id, posts.user_id AS posts_user_id, posts.headline AS posts_headline, posts.body AS posts_body
FROM posts
WHERE EXISTS (SELECT 1
FROM post_keywords, keywords
WHERE posts.id = post_keywords.post_id AND keywords.id = post_keywords.keyword_id AND keywords.keyword = ?)
('firstpost',)</div><span class="p">[</span><span class="n">BlogPost</span><span class="p">(</span><span class="s">"Wendy's Blog Post"</span><span class="p">,</span> <span class="s">'This is a test'</span><span class="p">,</span> <span class="o"><</span><span class="n">User</span><span class="p">(</span><span class="s">'wendy'</span><span class="p">,</span><span class="s">'Wendy Williams'</span><span class="p">,</span> <span class="s">'foobar'</span><span class="p">)</span><span class="o">></span><span class="p">)]</span></pre></div>
</div>
<p>If we want to look up just Wendy’s posts, we can tell the query to narrow down to her as a parent:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><a href='#' class='sql_link'>sql</a><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">BlogPost</span><span class="p">)</span><span class="o">.</span><span class="n">filter</span><span class="p">(</span><span class="n">BlogPost</span><span class="o">.</span><span class="n">author</span><span class="o">==</span><span class="n">wendy</span><span class="p">)</span><span class="o">.</span>\
<span class="o">...</span> <span class="nb">filter</span><span class="p">(</span><span class="n">BlogPost</span><span class="o">.</span><span class="n">keywords</span><span class="o">.</span><span class="n">any</span><span class="p">(</span><span class="n">keyword</span><span class="o">=</span><span class="s">'firstpost'</span><span class="p">))</span><span class="o">.</span><span class="n">all</span><span class="p">()</span>
<div class='popup_sql'>SELECT posts.id AS posts_id, posts.user_id AS posts_user_id, posts.headline AS posts_headline, posts.body AS posts_body
FROM posts
WHERE ? = posts.user_id AND (EXISTS (SELECT 1
FROM post_keywords, keywords
WHERE posts.id = post_keywords.post_id AND keywords.id = post_keywords.keyword_id AND keywords.keyword = ?))
(2, 'firstpost')</div><span class="p">[</span><span class="n">BlogPost</span><span class="p">(</span><span class="s">"Wendy's Blog Post"</span><span class="p">,</span> <span class="s">'This is a test'</span><span class="p">,</span> <span class="o"><</span><span class="n">User</span><span class="p">(</span><span class="s">'wendy'</span><span class="p">,</span><span class="s">'Wendy Williams'</span><span class="p">,</span> <span class="s">'foobar'</span><span class="p">)</span><span class="o">></span><span class="p">)]</span></pre></div>
</div>
<p>Or we can use Wendy’s own <tt class="docutils literal"><span class="pre">posts</span></tt> relationship, which is a “dynamic” relationship, to query straight from there:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><a href='#' class='sql_link'>sql</a><span class="o">>>></span> <span class="n">wendy</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">BlogPost</span><span class="o">.</span><span class="n">keywords</span><span class="o">.</span><span class="n">any</span><span class="p">(</span><span class="n">keyword</span><span class="o">=</span><span class="s">'firstpost'</span><span class="p">))</span><span class="o">.</span><span class="n">all</span><span class="p">()</span>
<div class='popup_sql'>SELECT posts.id AS posts_id, posts.user_id AS posts_user_id, posts.headline AS posts_headline, posts.body AS posts_body
FROM posts
WHERE ? = posts.user_id AND (EXISTS (SELECT 1
FROM post_keywords, keywords
WHERE posts.id = post_keywords.post_id AND keywords.id = post_keywords.keyword_id AND keywords.keyword = ?))
(2, 'firstpost')</div><span class="p">[</span><span class="n">BlogPost</span><span class="p">(</span><span class="s">"Wendy's Blog Post"</span><span class="p">,</span> <span class="s">'This is a test'</span><span class="p">,</span> <span class="o"><</span><span class="n">User</span><span class="p">(</span><span class="s">'wendy'</span><span class="p">,</span><span class="s">'Wendy Williams'</span><span class="p">,</span> <span class="s">'foobar'</span><span class="p">)</span><span class="o">></span><span class="p">)]</span></pre></div>
</div>
</div>
<div class="section" id="further-reference">
<h2>Further Reference<a class="headerlink" href="#further-reference" title="Permalink to this headline">¶</a></h2>
<p>Query Reference: <a class="reference internal" href="reference/orm/query.html"><em>Querying</em></a></p>
<p>Further information on mapping setups are in <a class="reference internal" href="mappers.html"><em>Mapper Configuration</em></a>.</p>
<p>Further information on working with Sessions: <a class="reference internal" href="session.html"><em>Using the Session</em></a>.</p>
</div>
</div>
</div>
</div>
<div class="bottomnav">
<div class="prevnext">
Previous:
<a href="intro.html" title="previous chapter">Overview / Installation</a>
Next:
<a href="sqlexpression.html" title="next chapter">SQL Expression Language Tutorial</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>
|
