1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
1001
1002
1003
1004
1005
1006
1007
1008
1009
1010
1011
1012
1013
1014
1015
1016
1017
1018
1019
1020
1021
1022
1023
1024
1025
1026
1027
1028
1029
1030
1031
1032
1033
1034
1035
1036
1037
1038
1039
1040
1041
1042
1043
1044
1045
1046
1047
1048
1049
1050
1051
1052
1053
1054
1055
1056
1057
1058
1059
1060
1061
1062
1063
1064
1065
1066
1067
1068
1069
1070
1071
1072
1073
1074
1075
1076
1077
1078
1079
1080
1081
1082
1083
1084
1085
1086
1087
1088
1089
1090
1091
1092
1093
1094
1095
1096
1097
1098
1099
1100
1101
1102
1103
1104
1105
1106
1107
1108
1109
1110
1111
1112
1113
1114
1115
1116
1117
1118
1119
1120
1121
1122
1123
1124
1125
1126
1127
1128
1129
1130
1131
1132
1133
1134
1135
1136
1137
1138
1139
1140
1141
1142
1143
1144
1145
1146
1147
1148
1149
1150
1151
1152
1153
1154
1155
1156
1157
1158
1159
1160
1161
1162
1163
1164
1165
1166
1167
1168
1169
1170
1171
1172
1173
1174
1175
1176
1177
1178
1179
1180
1181
1182
1183
1184
1185
1186
1187
1188
1189
1190
1191
1192
1193
1194
1195
1196
1197
1198
1199
1200
1201
1202
1203
1204
1205
1206
1207
1208
1209
1210
1211
1212
1213
1214
1215
1216
1217
1218
1219
1220
1221
1222
1223
1224
1225
1226
1227
1228
1229
1230
1231
1232
1233
1234
1235
1236
1237
1238
1239
1240
1241
1242
1243
1244
1245
1246
1247
1248
1249
1250
1251
1252
1253
1254
1255
1256
1257
1258
1259
1260
1261
1262
1263
1264
1265
1266
1267
1268
1269
1270
1271
1272
1273
1274
1275
1276
1277
1278
1279
1280
1281
1282
1283
1284
1285
1286
1287
1288
1289
1290
1291
1292
1293
1294
1295
1296
1297
1298
1299
1300
1301
1302
1303
1304
1305
1306
1307
1308
1309
1310
1311
1312
1313
1314
1315
1316
1317
1318
1319
1320
1321
1322
1323
1324
1325
1326
1327
1328
1329
1330
1331
1332
1333
1334
1335
1336
1337
1338
1339
1340
1341
1342
1343
1344
1345
1346
1347
1348
1349
1350
1351
1352
1353
1354
1355
1356
1357
1358
1359
1360
1361
1362
1363
1364
1365
1366
1367
1368
1369
1370
1371
1372
1373
1374
1375
1376
1377
1378
1379
1380
1381
1382
1383
1384
1385
1386
1387
1388
1389
1390
1391
1392
1393
1394
1395
1396
1397
1398
1399
1400
1401
1402
1403
1404
1405
1406
1407
1408
1409
1410
1411
1412
1413
1414
1415
1416
1417
1418
1419
1420
1421
1422
1423
1424
1425
1426
1427
1428
1429
1430
1431
1432
1433
1434
1435
1436
1437
1438
1439
1440
1441
1442
1443
1444
1445
1446
1447
1448
1449
1450
1451
1452
1453
1454
1455
1456
1457
1458
1459
1460
1461
1462
1463
1464
1465
1466
1467
1468
1469
1470
1471
1472
1473
1474
1475
1476
1477
1478
1479
1480
1481
1482
1483
1484
1485
1486
1487
1488
1489
1490
1491
1492
1493
1494
1495
1496
1497
1498
|
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>
Frequently Asked Questions
—
SQLAlchemy 0.9 Documentation
</title>
<!-- begin iterate through SQLA + sphinx environment css_files -->
<link rel="stylesheet" href="_static/pygments.css" type="text/css" />
<link rel="stylesheet" href="_static/docs.css" type="text/css" />
<link rel="stylesheet" href="_static/sphinx_paramlinks.css" type="text/css" />
<link rel="stylesheet" href="_static/changelog.css" type="text/css" />
<!-- end iterate through SQLA + sphinx environment css_files -->
<!-- begin layout.mako headers -->
<script type="text/javascript">
var DOCUMENTATION_OPTIONS = {
URL_ROOT: './',
VERSION: '0.9.8',
COLLAPSE_MODINDEX: false,
FILE_SUFFIX: '.html'
};
</script>
<!-- begin iterate through sphinx environment script_files -->
<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>
<!-- end iterate through sphinx environment script_files -->
<script type="text/javascript" src="_static/detectmobile.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="copyright" title="Copyright" href="copyright.html" />
<link rel="top" title="SQLAlchemy 0.9 Documentation" href="index.html" />
<!-- end layout.mako headers -->
</head>
<body>
<div id="docs-container">
<div id="docs-top-navigation-container" class="body-background">
<div id="docs-header">
<div id="docs-version-header">
Release: <span class="version-num">0.9.8</span> | Release Date: October 13, 2014
</div>
<h1>SQLAlchemy 0.9 Documentation</h1>
</div>
</div>
<div id="docs-body-container">
<div id="fixed-sidebar" class="withsidebar">
<div id="docs-sidebar-popout">
<h3><a href="index.html">SQLAlchemy 0.9 Documentation</a></h3>
<p id="sidebar-paginate">
<a href="index.html" title="SQLAlchemy 0.9 Documentation">Up</a> |
</p>
<p id="sidebar-topnav">
<a href="index.html">Contents</a> |
<a href="genindex.html">Index</a>
</p>
<div id="sidebar-search">
<form class="search" action="search.html" method="get">
<input type="text" name="q" size="12" /> <input type="submit" value="Search" />
<input type="hidden" name="check_keywords" value="yes" />
<input type="hidden" name="area" value="default" />
</form>
</div>
</div>
<div id="docs-sidebar">
<h3><a href="#">
Frequently Asked Questions
</a></h3>
<ul>
<li><a class="reference internal" href="#">Frequently Asked Questions</a><ul>
<li><a class="reference internal" href="#connections-engines">Connections / Engines</a><ul>
<li><a class="reference internal" href="#how-do-i-configure-logging">How do I configure logging?</a></li>
<li><a class="reference internal" href="#how-do-i-pool-database-connections-are-my-connections-pooled">How do I pool database connections? Are my connections pooled?</a></li>
<li><a class="reference internal" href="#how-do-i-pass-custom-connect-arguments-to-my-database-api">How do I pass custom connect arguments to my database API?</a></li>
<li><a class="reference internal" href="#mysql-server-has-gone-away">“MySQL Server has gone away”</a></li>
<li><a class="reference internal" href="#why-does-sqlalchemy-issue-so-many-rollbacks">Why does SQLAlchemy issue so many ROLLBACKs?</a><ul>
<li><a class="reference internal" href="#i-m-on-myisam-how-do-i-turn-it-off">I’m on MyISAM - how do I turn it off?</a></li>
<li><a class="reference internal" href="#i-m-on-sql-server-how-do-i-turn-those-rollbacks-into-commits">I’m on SQL Server - how do I turn those ROLLBACKs into COMMITs?</a></li>
</ul>
</li>
<li><a class="reference internal" href="#i-am-using-multiple-connections-with-a-sqlite-database-typically-to-test-transaction-operation-and-my-test-program-is-not-working">I am using multiple connections with a SQLite database (typically to test transaction operation), and my test program is not working!</a></li>
<li><a class="reference internal" href="#how-do-i-get-at-the-raw-dbapi-connection-when-using-an-engine">How do I get at the raw DBAPI connection when using an Engine?</a></li>
</ul>
</li>
<li><a class="reference internal" href="#metadata-schema">MetaData / Schema</a><ul>
<li><a class="reference internal" href="#my-program-is-hanging-when-i-say-table-drop-metadata-drop-all">My program is hanging when I say <tt class="docutils literal"><span class="pre">table.drop()</span></tt> / <tt class="docutils literal"><span class="pre">metadata.drop_all()</span></tt></a></li>
<li><a class="reference internal" href="#does-sqlalchemy-support-alter-table-create-view-create-trigger-schema-upgrade-functionality">Does SQLAlchemy support ALTER TABLE, CREATE VIEW, CREATE TRIGGER, Schema Upgrade Functionality?</a></li>
<li><a class="reference internal" href="#how-can-i-sort-table-objects-in-order-of-their-dependency">How can I sort Table objects in order of their dependency?</a></li>
<li><a class="reference internal" href="#how-can-i-get-the-create-table-drop-table-output-as-a-string">How can I get the CREATE TABLE/ DROP TABLE output as a string?</a></li>
<li><a class="reference internal" href="#how-can-i-subclass-table-column-to-provide-certain-behaviors-configurations">How can I subclass Table/Column to provide certain behaviors/configurations?</a></li>
</ul>
</li>
<li><a class="reference internal" href="#sql-expressions">SQL Expressions</a><ul>
<li><a class="reference internal" href="#how-do-i-render-sql-expressions-as-strings-possibly-with-bound-parameters-inlined">How do I render SQL expressions as strings, possibly with bound parameters inlined?</a></li>
<li><a class="reference internal" href="#why-does-col-in-produce-col-col-why-not-1-0">Why does <tt class="docutils literal"><span class="pre">.col.in_([])</span></tt> Produce <tt class="docutils literal"><span class="pre">col</span> <span class="pre">!=</span> <span class="pre">col</span></tt>? Why not <tt class="docutils literal"><span class="pre">1=0</span></tt>?</a></li>
</ul>
</li>
<li><a class="reference internal" href="#orm-configuration">ORM Configuration</a><ul>
<li><a class="reference internal" href="#how-do-i-map-a-table-that-has-no-primary-key">How do I map a table that has no primary key?</a></li>
<li><a class="reference internal" href="#how-do-i-configure-a-column-that-is-a-python-reserved-word-or-similar">How do I configure a Column that is a Python reserved word or similar?</a></li>
<li><a class="reference internal" href="#how-do-i-get-a-list-of-all-columns-relationships-mapped-attributes-etc-given-a-mapped-class">How do I get a list of all columns, relationships, mapped attributes, etc. given a mapped class?</a></li>
<li><a class="reference internal" href="#i-m-getting-a-warning-or-error-about-implicitly-combining-column-x-under-attribute-y">I’m getting a warning or error about “Implicitly combining column X under attribute Y”</a></li>
<li><a class="reference internal" href="#i-m-using-declarative-and-setting-primaryjoin-secondaryjoin-using-an-and-or-or-and-i-am-getting-an-error-message-about-foreign-keys">I’m using Declarative and setting primaryjoin/secondaryjoin using an <tt class="docutils literal"><span class="pre">and_()</span></tt> or <tt class="docutils literal"><span class="pre">or_()</span></tt>, and I am getting an error message about foreign keys.</a></li>
</ul>
</li>
<li><a class="reference internal" href="#performance">Performance</a><ul>
<li><a class="reference internal" href="#how-can-i-profile-a-sqlalchemy-powered-application">How can I profile a SQLAlchemy powered application?</a><ul>
<li><a class="reference internal" href="#query-profiling">Query Profiling</a></li>
<li><a class="reference internal" href="#code-profiling">Code Profiling</a></li>
<li><a class="reference internal" href="#execution-slowness">Execution Slowness</a></li>
<li><a class="reference internal" href="#result-fetching-slowness-core">Result Fetching Slowness - Core</a></li>
<li><a class="reference internal" href="#result-fetching-slowness-orm">Result Fetching Slowness - ORM</a></li>
</ul>
</li>
<li><a class="reference internal" href="#i-m-inserting-400-000-rows-with-the-orm-and-it-s-really-slow">I’m inserting 400,000 rows with the ORM and it’s really slow!</a></li>
</ul>
</li>
<li><a class="reference internal" href="#sessions-queries">Sessions / Queries</a><ul>
<li><a class="reference internal" href="#this-session-s-transaction-has-been-rolled-back-due-to-a-previous-exception-during-flush-or-similar">“This Session’s transaction has been rolled back due to a previous exception during flush.” (or similar)</a><ul>
<li><a class="reference internal" href="#but-why-does-flush-insist-on-issuing-a-rollback">But why does flush() insist on issuing a ROLLBACK?</a></li>
<li><a class="reference internal" href="#but-why-isn-t-the-one-automatic-call-to-rollback-enough-why-must-i-rollback-again">But why isn’t the one automatic call to ROLLBACK enough? Why must I ROLLBACK again?</a></li>
</ul>
</li>
<li><a class="reference internal" href="#how-do-i-make-a-query-that-always-adds-a-certain-filter-to-every-query">How do I make a Query that always adds a certain filter to every query?</a></li>
<li><a class="reference internal" href="#i-ve-created-a-mapping-against-an-outer-join-and-while-the-query-returns-rows-no-objects-are-returned-why-not">I’ve created a mapping against an Outer Join, and while the query returns rows, no objects are returned. Why not?</a></li>
<li><a class="reference internal" href="#i-m-using-joinedload-or-lazy-false-to-create-a-join-outer-join-and-sqlalchemy-is-not-constructing-the-correct-query-when-i-try-to-add-a-where-order-by-limit-etc-which-relies-upon-the-outer-join">I’m using <tt class="docutils literal"><span class="pre">joinedload()</span></tt> or <tt class="docutils literal"><span class="pre">lazy=False</span></tt> to create a JOIN/OUTER JOIN and SQLAlchemy is not constructing the correct query when I try to add a WHERE, ORDER BY, LIMIT, etc. (which relies upon the (OUTER) JOIN)</a></li>
<li><a class="reference internal" href="#query-has-no-len-why-not">Query has no <tt class="docutils literal"><span class="pre">__len__()</span></tt>, why not?</a></li>
<li><a class="reference internal" href="#how-do-i-use-textual-sql-with-orm-queries">How Do I use Textual SQL with ORM Queries?</a></li>
<li><a class="reference internal" href="#i-m-calling-session-delete-myobject-and-it-isn-t-removed-from-the-parent-collection">I’m calling <tt class="docutils literal"><span class="pre">Session.delete(myobject)</span></tt> and it isn’t removed from the parent collection!</a></li>
<li><a class="reference internal" href="#why-isn-t-my-init-called-when-i-load-objects">why isn’t my <tt class="docutils literal"><span class="pre">__init__()</span></tt> called when I load objects?</a></li>
<li><a class="reference internal" href="#how-do-i-use-on-delete-cascade-with-sa-s-orm">how do I use ON DELETE CASCADE with SA’s ORM?</a></li>
<li><a class="reference internal" href="#i-set-the-foo-id-attribute-on-my-instance-to-7-but-the-foo-attribute-is-still-none-shouldn-t-it-have-loaded-foo-with-id-7">I set the “foo_id” attribute on my instance to “7”, but the “foo” attribute is still <tt class="docutils literal"><span class="pre">None</span></tt> - shouldn’t it have loaded Foo with id #7?</a></li>
<li><a class="reference internal" href="#is-there-a-way-to-automagically-have-only-unique-keywords-or-other-kinds-of-objects-without-doing-a-query-for-the-keyword-and-getting-a-reference-to-the-row-containing-that-keyword">Is there a way to automagically have only unique keywords (or other kinds of objects) without doing a query for the keyword and getting a reference to the row containing that keyword?</a></li>
</ul>
</li>
</ul>
</li>
</ul>
</div>
</div>
<div id="docs-body" class="withsidebar" >
<div class="section" id="frequently-asked-questions">
<span id="faq-toplevel"></span><h1>Frequently Asked Questions<a class="headerlink" href="#frequently-asked-questions" title="Permalink to this headline">¶</a></h1>
<div class="contents faq local topic" id="contents">
<ul class="simple">
<li><a class="reference internal" href="#connections-engines" id="id1">Connections / Engines</a><ul>
<li><a class="reference internal" href="#how-do-i-configure-logging" id="id2">How do I configure logging?</a></li>
<li><a class="reference internal" href="#how-do-i-pool-database-connections-are-my-connections-pooled" id="id3">How do I pool database connections? Are my connections pooled?</a></li>
<li><a class="reference internal" href="#how-do-i-pass-custom-connect-arguments-to-my-database-api" id="id4">How do I pass custom connect arguments to my database API?</a></li>
<li><a class="reference internal" href="#mysql-server-has-gone-away" id="id5">“MySQL Server has gone away”</a></li>
<li><a class="reference internal" href="#why-does-sqlalchemy-issue-so-many-rollbacks" id="id6">Why does SQLAlchemy issue so many ROLLBACKs?</a><ul>
<li><a class="reference internal" href="#i-m-on-myisam-how-do-i-turn-it-off" id="id7">I’m on MyISAM - how do I turn it off?</a></li>
<li><a class="reference internal" href="#i-m-on-sql-server-how-do-i-turn-those-rollbacks-into-commits" id="id8">I’m on SQL Server - how do I turn those ROLLBACKs into COMMITs?</a></li>
</ul>
</li>
<li><a class="reference internal" href="#i-am-using-multiple-connections-with-a-sqlite-database-typically-to-test-transaction-operation-and-my-test-program-is-not-working" id="id9">I am using multiple connections with a SQLite database (typically to test transaction operation), and my test program is not working!</a></li>
<li><a class="reference internal" href="#how-do-i-get-at-the-raw-dbapi-connection-when-using-an-engine" id="id10">How do I get at the raw DBAPI connection when using an Engine?</a></li>
</ul>
</li>
<li><a class="reference internal" href="#metadata-schema" id="id11">MetaData / Schema</a><ul>
<li><a class="reference internal" href="#my-program-is-hanging-when-i-say-table-drop-metadata-drop-all" id="id12">My program is hanging when I say <tt class="docutils literal"><span class="pre">table.drop()</span></tt> / <tt class="docutils literal"><span class="pre">metadata.drop_all()</span></tt></a></li>
<li><a class="reference internal" href="#does-sqlalchemy-support-alter-table-create-view-create-trigger-schema-upgrade-functionality" id="id13">Does SQLAlchemy support ALTER TABLE, CREATE VIEW, CREATE TRIGGER, Schema Upgrade Functionality?</a></li>
<li><a class="reference internal" href="#how-can-i-sort-table-objects-in-order-of-their-dependency" id="id14">How can I sort Table objects in order of their dependency?</a></li>
<li><a class="reference internal" href="#how-can-i-get-the-create-table-drop-table-output-as-a-string" id="id15">How can I get the CREATE TABLE/ DROP TABLE output as a string?</a></li>
<li><a class="reference internal" href="#how-can-i-subclass-table-column-to-provide-certain-behaviors-configurations" id="id16">How can I subclass Table/Column to provide certain behaviors/configurations?</a></li>
</ul>
</li>
<li><a class="reference internal" href="#sql-expressions" id="id17">SQL Expressions</a><ul>
<li><a class="reference internal" href="#how-do-i-render-sql-expressions-as-strings-possibly-with-bound-parameters-inlined" id="id18">How do I render SQL expressions as strings, possibly with bound parameters inlined?</a></li>
<li><a class="reference internal" href="#why-does-col-in-produce-col-col-why-not-1-0" id="id19">Why does <tt class="docutils literal"><span class="pre">.col.in_([])</span></tt> Produce <tt class="docutils literal"><span class="pre">col</span> <span class="pre">!=</span> <span class="pre">col</span></tt>? Why not <tt class="docutils literal"><span class="pre">1=0</span></tt>?</a></li>
</ul>
</li>
<li><a class="reference internal" href="#orm-configuration" id="id20">ORM Configuration</a><ul>
<li><a class="reference internal" href="#how-do-i-map-a-table-that-has-no-primary-key" id="id21">How do I map a table that has no primary key?</a></li>
<li><a class="reference internal" href="#how-do-i-configure-a-column-that-is-a-python-reserved-word-or-similar" id="id22">How do I configure a Column that is a Python reserved word or similar?</a></li>
<li><a class="reference internal" href="#how-do-i-get-a-list-of-all-columns-relationships-mapped-attributes-etc-given-a-mapped-class" id="id23">How do I get a list of all columns, relationships, mapped attributes, etc. given a mapped class?</a></li>
<li><a class="reference internal" href="#i-m-getting-a-warning-or-error-about-implicitly-combining-column-x-under-attribute-y" id="id24">I’m getting a warning or error about “Implicitly combining column X under attribute Y”</a></li>
<li><a class="reference internal" href="#i-m-using-declarative-and-setting-primaryjoin-secondaryjoin-using-an-and-or-or-and-i-am-getting-an-error-message-about-foreign-keys" id="id25">I’m using Declarative and setting primaryjoin/secondaryjoin using an <tt class="docutils literal"><span class="pre">and_()</span></tt> or <tt class="docutils literal"><span class="pre">or_()</span></tt>, and I am getting an error message about foreign keys.</a></li>
</ul>
</li>
<li><a class="reference internal" href="#performance" id="id26">Performance</a><ul>
<li><a class="reference internal" href="#how-can-i-profile-a-sqlalchemy-powered-application" id="id27">How can I profile a SQLAlchemy powered application?</a><ul>
<li><a class="reference internal" href="#query-profiling" id="id28">Query Profiling</a></li>
<li><a class="reference internal" href="#code-profiling" id="id29">Code Profiling</a></li>
<li><a class="reference internal" href="#execution-slowness" id="id30">Execution Slowness</a></li>
<li><a class="reference internal" href="#result-fetching-slowness-core" id="id31">Result Fetching Slowness - Core</a></li>
<li><a class="reference internal" href="#result-fetching-slowness-orm" id="id32">Result Fetching Slowness - ORM</a></li>
</ul>
</li>
<li><a class="reference internal" href="#i-m-inserting-400-000-rows-with-the-orm-and-it-s-really-slow" id="id33">I’m inserting 400,000 rows with the ORM and it’s really slow!</a></li>
</ul>
</li>
<li><a class="reference internal" href="#sessions-queries" id="id34">Sessions / Queries</a><ul>
<li><a class="reference internal" href="#this-session-s-transaction-has-been-rolled-back-due-to-a-previous-exception-during-flush-or-similar" id="id35">“This Session’s transaction has been rolled back due to a previous exception during flush.” (or similar)</a><ul>
<li><a class="reference internal" href="#but-why-does-flush-insist-on-issuing-a-rollback" id="id36">But why does flush() insist on issuing a ROLLBACK?</a></li>
<li><a class="reference internal" href="#but-why-isn-t-the-one-automatic-call-to-rollback-enough-why-must-i-rollback-again" id="id37">But why isn’t the one automatic call to ROLLBACK enough? Why must I ROLLBACK again?</a></li>
</ul>
</li>
<li><a class="reference internal" href="#how-do-i-make-a-query-that-always-adds-a-certain-filter-to-every-query" id="id38">How do I make a Query that always adds a certain filter to every query?</a></li>
<li><a class="reference internal" href="#i-ve-created-a-mapping-against-an-outer-join-and-while-the-query-returns-rows-no-objects-are-returned-why-not" id="id39">I’ve created a mapping against an Outer Join, and while the query returns rows, no objects are returned. Why not?</a></li>
<li><a class="reference internal" href="#i-m-using-joinedload-or-lazy-false-to-create-a-join-outer-join-and-sqlalchemy-is-not-constructing-the-correct-query-when-i-try-to-add-a-where-order-by-limit-etc-which-relies-upon-the-outer-join" id="id40">I’m using <tt class="docutils literal"><span class="pre">joinedload()</span></tt> or <tt class="docutils literal"><span class="pre">lazy=False</span></tt> to create a JOIN/OUTER JOIN and SQLAlchemy is not constructing the correct query when I try to add a WHERE, ORDER BY, LIMIT, etc. (which relies upon the (OUTER) JOIN)</a></li>
<li><a class="reference internal" href="#query-has-no-len-why-not" id="id41">Query has no <tt class="docutils literal"><span class="pre">__len__()</span></tt>, why not?</a></li>
<li><a class="reference internal" href="#how-do-i-use-textual-sql-with-orm-queries" id="id42">How Do I use Textual SQL with ORM Queries?</a></li>
<li><a class="reference internal" href="#i-m-calling-session-delete-myobject-and-it-isn-t-removed-from-the-parent-collection" id="id43">I’m calling <tt class="docutils literal"><span class="pre">Session.delete(myobject)</span></tt> and it isn’t removed from the parent collection!</a></li>
<li><a class="reference internal" href="#why-isn-t-my-init-called-when-i-load-objects" id="id44">why isn’t my <tt class="docutils literal"><span class="pre">__init__()</span></tt> called when I load objects?</a></li>
<li><a class="reference internal" href="#how-do-i-use-on-delete-cascade-with-sa-s-orm" id="id45">how do I use ON DELETE CASCADE with SA’s ORM?</a></li>
<li><a class="reference internal" href="#i-set-the-foo-id-attribute-on-my-instance-to-7-but-the-foo-attribute-is-still-none-shouldn-t-it-have-loaded-foo-with-id-7" id="id46">I set the “foo_id” attribute on my instance to “7”, but the “foo” attribute is still <tt class="docutils literal"><span class="pre">None</span></tt> - shouldn’t it have loaded Foo with id #7?</a></li>
<li><a class="reference internal" href="#is-there-a-way-to-automagically-have-only-unique-keywords-or-other-kinds-of-objects-without-doing-a-query-for-the-keyword-and-getting-a-reference-to-the-row-containing-that-keyword" id="id47">Is there a way to automagically have only unique keywords (or other kinds of objects) without doing a query for the keyword and getting a reference to the row containing that keyword?</a></li>
</ul>
</li>
</ul>
</div>
<div class="section" id="connections-engines">
<h2>Connections / Engines<a class="headerlink" href="#connections-engines" title="Permalink to this headline">¶</a></h2>
<div class="section" id="how-do-i-configure-logging">
<h3>How do I configure logging?<a class="headerlink" href="#how-do-i-configure-logging" title="Permalink to this headline">¶</a></h3>
<p>See <a class="reference internal" href="core/engines.html#dbengine-logging"><em>Configuring Logging</em></a>.</p>
</div>
<div class="section" id="how-do-i-pool-database-connections-are-my-connections-pooled">
<h3>How do I pool database connections? Are my connections pooled?<a class="headerlink" href="#how-do-i-pool-database-connections-are-my-connections-pooled" title="Permalink to this headline">¶</a></h3>
<p>SQLAlchemy performs application-level connection pooling automatically
in most cases. With the exception of SQLite, a <a class="reference internal" href="core/connections.html#sqlalchemy.engine.Engine" title="sqlalchemy.engine.Engine"><tt class="xref py py-class docutils literal"><span class="pre">Engine</span></tt></a> object
refers to a <a class="reference internal" href="core/pooling.html#sqlalchemy.pool.QueuePool" title="sqlalchemy.pool.QueuePool"><tt class="xref py py-class docutils literal"><span class="pre">QueuePool</span></tt></a> as a source of connectivity.</p>
<p>For more detail, see <a class="reference internal" href="core/engines.html"><em>Engine Configuration</em></a> and <a class="reference internal" href="core/pooling.html"><em>Connection Pooling</em></a>.</p>
</div>
<div class="section" id="how-do-i-pass-custom-connect-arguments-to-my-database-api">
<h3>How do I pass custom connect arguments to my database API?<a class="headerlink" href="#how-do-i-pass-custom-connect-arguments-to-my-database-api" title="Permalink to this headline">¶</a></h3>
<p>The <a class="reference internal" href="core/engines.html#sqlalchemy.create_engine" title="sqlalchemy.create_engine"><tt class="xref py py-func docutils literal"><span class="pre">create_engine()</span></tt></a> call accepts additional arguments either
directly via the <tt class="docutils literal"><span class="pre">connect_args</span></tt> keyword argument:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">e</span> <span class="o">=</span> <span class="n">create_engine</span><span class="p">(</span><span class="s">"mysql://scott:tiger@localhost/test"</span><span class="p">,</span>
<span class="n">connect_args</span><span class="o">=</span><span class="p">{</span><span class="s">"encoding"</span><span class="p">:</span> <span class="s">"utf8"</span><span class="p">})</span></pre></div>
</div>
<p>Or for basic string and integer arguments, they can usually be specified
in the query string of the URL:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">e</span> <span class="o">=</span> <span class="n">create_engine</span><span class="p">(</span><span class="s">"mysql://scott:tiger@localhost/test?encoding=utf8"</span><span class="p">)</span></pre></div>
</div>
<div class="admonition seealso">
<p class="first admonition-title">See also</p>
<p class="last"><a class="reference internal" href="core/engines.html#custom-dbapi-args"><em>Custom DBAPI connect() arguments</em></a></p>
</div>
</div>
<div class="section" id="mysql-server-has-gone-away">
<h3>“MySQL Server has gone away”<a class="headerlink" href="#mysql-server-has-gone-away" title="Permalink to this headline">¶</a></h3>
<p>There are two major causes for this error:</p>
<p>1. The MySQL client closes connections which have been idle for a set period
of time, defaulting to eight hours. This can be avoided by using the <tt class="docutils literal"><span class="pre">pool_recycle</span></tt>
setting with <a class="reference internal" href="core/engines.html#sqlalchemy.create_engine" title="sqlalchemy.create_engine"><tt class="xref py py-func docutils literal"><span class="pre">create_engine()</span></tt></a>, described at <a class="reference internal" href="dialects/mysql.html#mysql-connection-timeouts"><em>Connection Timeouts</em></a>.</p>
<p>2. Usage of the MySQLdb <a class="reference internal" href="glossary.html#term-dbapi"><em class="xref std std-term">DBAPI</em></a>, or a similar DBAPI, in a non-threadsafe manner, or in an otherwise
inappropriate way. The MySQLdb connection object is not threadsafe - this expands
out to any SQLAlchemy system that links to a single connection, which includes the ORM
<a class="reference internal" href="orm/session.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>. For background
on how <a class="reference internal" href="orm/session.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> should be used in a multithreaded environment,
see <a class="reference internal" href="orm/session.html#session-faq-threadsafe"><em>Is the session thread-safe?</em></a>.</p>
</div>
<div class="section" id="why-does-sqlalchemy-issue-so-many-rollbacks">
<h3>Why does SQLAlchemy issue so many ROLLBACKs?<a class="headerlink" href="#why-does-sqlalchemy-issue-so-many-rollbacks" title="Permalink to this headline">¶</a></h3>
<p>SQLAlchemy currently assumes DBAPI connections are in “non-autocommit” mode -
this is the default behavior of the Python database API, meaning it
must be assumed that a transaction is always in progress. The
connection pool issues <tt class="docutils literal"><span class="pre">connection.rollback()</span></tt> when a connection is returned.
This is so that any transactional resources remaining on the connection are
released. On a database like Postgresql or MSSQL where table resources are
aggressively locked, this is critical so that rows and tables don’t remain
locked within connections that are no longer in use. An application can
otherwise hang. It’s not just for locks, however, and is equally critical on
any database that has any kind of transaction isolation, including MySQL with
InnoDB. Any connection that is still inside an old transaction will return
stale data, if that data was already queried on that connection within
isolation. For background on why you might see stale data even on MySQL, see
<a class="reference external" href="http://dev.mysql.com/doc/refman/5.1/en/innodb-transaction-model.html">http://dev.mysql.com/doc/refman/5.1/en/innodb-transaction-model.html</a></p>
<div class="section" id="i-m-on-myisam-how-do-i-turn-it-off">
<h4>I’m on MyISAM - how do I turn it off?<a class="headerlink" href="#i-m-on-myisam-how-do-i-turn-it-off" title="Permalink to this headline">¶</a></h4>
<p>The behavior of the connection pool’s connection return behavior can be
configured using <tt class="docutils literal"><span class="pre">reset_on_return</span></tt>:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="kn">from</span> <span class="nn">sqlalchemy</span> <span class="kn">import</span> <span class="n">create_engine</span>
<span class="kn">from</span> <span class="nn">sqlalchemy.pool</span> <span class="kn">import</span> <span class="n">QueuePool</span>
<span class="n">engine</span> <span class="o">=</span> <span class="n">create_engine</span><span class="p">(</span><span class="s">'mysql://scott:tiger@localhost/myisam_database'</span><span class="p">,</span> <span class="n">pool</span><span class="o">=</span><span class="n">QueuePool</span><span class="p">(</span><span class="n">reset_on_return</span><span class="o">=</span><span class="bp">False</span><span class="p">))</span></pre></div>
</div>
</div>
<div class="section" id="i-m-on-sql-server-how-do-i-turn-those-rollbacks-into-commits">
<h4>I’m on SQL Server - how do I turn those ROLLBACKs into COMMITs?<a class="headerlink" href="#i-m-on-sql-server-how-do-i-turn-those-rollbacks-into-commits" title="Permalink to this headline">¶</a></h4>
<p><tt class="docutils literal"><span class="pre">reset_on_return</span></tt> accepts the values <tt class="docutils literal"><span class="pre">commit</span></tt>, <tt class="docutils literal"><span class="pre">rollback</span></tt> in addition
to <tt class="docutils literal"><span class="pre">True</span></tt>, <tt class="docutils literal"><span class="pre">False</span></tt>, and <tt class="docutils literal"><span class="pre">None</span></tt>. Setting to <tt class="docutils literal"><span class="pre">commit</span></tt> will cause
a COMMIT as any connection is returned to the pool:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">engine</span> <span class="o">=</span> <span class="n">create_engine</span><span class="p">(</span><span class="s">'mssql://scott:tiger@mydsn'</span><span class="p">,</span> <span class="n">pool</span><span class="o">=</span><span class="n">QueuePool</span><span class="p">(</span><span class="n">reset_on_return</span><span class="o">=</span><span class="s">'commit'</span><span class="p">))</span></pre></div>
</div>
</div>
</div>
<div class="section" id="i-am-using-multiple-connections-with-a-sqlite-database-typically-to-test-transaction-operation-and-my-test-program-is-not-working">
<h3>I am using multiple connections with a SQLite database (typically to test transaction operation), and my test program is not working!<a class="headerlink" href="#i-am-using-multiple-connections-with-a-sqlite-database-typically-to-test-transaction-operation-and-my-test-program-is-not-working" title="Permalink to this headline">¶</a></h3>
<p>If using a SQLite <tt class="docutils literal"><span class="pre">:memory:</span></tt> database, or a version of SQLAlchemy prior
to version 0.7, the default connection pool is the <a class="reference internal" href="core/pooling.html#sqlalchemy.pool.SingletonThreadPool" title="sqlalchemy.pool.SingletonThreadPool"><tt class="xref py py-class docutils literal"><span class="pre">SingletonThreadPool</span></tt></a>,
which maintains exactly one SQLite connection per thread. So two
connections in use in the same thread will actually be the same SQLite
connection. Make sure you’re not using a :memory: database and
use <a class="reference internal" href="core/pooling.html#sqlalchemy.pool.NullPool" title="sqlalchemy.pool.NullPool"><tt class="xref py py-class docutils literal"><span class="pre">NullPool</span></tt></a>, which is the default for non-memory databases in
current SQLAlchemy versions.</p>
<div class="admonition seealso">
<p class="first admonition-title">See also</p>
<p class="last"><a class="reference internal" href="dialects/sqlite.html#pysqlite-threading-pooling"><em>Threading/Pooling Behavior</em></a> - info on PySQLite’s behavior.</p>
</div>
</div>
<div class="section" id="how-do-i-get-at-the-raw-dbapi-connection-when-using-an-engine">
<h3>How do I get at the raw DBAPI connection when using an Engine?<a class="headerlink" href="#how-do-i-get-at-the-raw-dbapi-connection-when-using-an-engine" title="Permalink to this headline">¶</a></h3>
<p>With a regular SA engine-level Connection, you can get at a pool-proxied
version of the DBAPI connection via the <a class="reference internal" href="core/connections.html#sqlalchemy.engine.Connection.connection" title="sqlalchemy.engine.Connection.connection"><tt class="xref py py-attr docutils literal"><span class="pre">Connection.connection</span></tt></a> attribute on
<a class="reference internal" href="core/connections.html#sqlalchemy.engine.Connection" title="sqlalchemy.engine.Connection"><tt class="xref py py-class docutils literal"><span class="pre">Connection</span></tt></a>, and for the really-real DBAPI connection you can call the
<tt class="xref py py-attr docutils literal"><span class="pre">ConnectionFairy.connection</span></tt> attribute on that - but there should never be any need to access
the non-pool-proxied DBAPI connection, as all methods are proxied through:</p>
<div class="highlight-python"><pre>engine = create_engine(...)
conn = engine.connect()
conn.connection.<do DBAPI things>
cursor = conn.connection.cursor(<DBAPI specific arguments..>)</pre>
</div>
<p>You must ensure that you revert any isolation level settings or other
operation-specific settings on the connection back to normal before returning
it to the pool.</p>
<p>As an alternative to reverting settings, you can call the <a class="reference internal" href="core/connections.html#sqlalchemy.engine.Connection.detach" title="sqlalchemy.engine.Connection.detach"><tt class="xref py py-meth docutils literal"><span class="pre">Connection.detach()</span></tt></a> method on
either <a class="reference internal" href="core/connections.html#sqlalchemy.engine.Connection" title="sqlalchemy.engine.Connection"><tt class="xref py py-class docutils literal"><span class="pre">Connection</span></tt></a> or the proxied connection, which will de-associate
the connection from the pool such that it will be closed and discarded
when <a class="reference internal" href="core/connections.html#sqlalchemy.engine.Connection.close" title="sqlalchemy.engine.Connection.close"><tt class="xref py py-meth docutils literal"><span class="pre">Connection.close()</span></tt></a> is called:</p>
<div class="highlight-python"><pre>conn = engine.connect()
conn.detach() # detaches the DBAPI connection from the connection pool
conn.connection.<go nuts>
conn.close() # connection is closed for real, the pool replaces it with a new connection</pre>
</div>
</div>
</div>
<div class="section" id="metadata-schema">
<h2>MetaData / Schema<a class="headerlink" href="#metadata-schema" title="Permalink to this headline">¶</a></h2>
<div class="section" id="my-program-is-hanging-when-i-say-table-drop-metadata-drop-all">
<h3>My program is hanging when I say <tt class="docutils literal"><span class="pre">table.drop()</span></tt> / <tt class="docutils literal"><span class="pre">metadata.drop_all()</span></tt><a class="headerlink" href="#my-program-is-hanging-when-i-say-table-drop-metadata-drop-all" title="Permalink to this headline">¶</a></h3>
<p>This usually corresponds to two conditions: 1. using PostgreSQL, which is really
strict about table locks, and 2. you have a connection still open which
contains locks on the table and is distinct from the connection being used for
the DROP statement. Heres the most minimal version of the pattern:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">connection</span> <span class="o">=</span> <span class="n">engine</span><span class="o">.</span><span class="n">connect</span><span class="p">()</span>
<span class="n">result</span> <span class="o">=</span> <span class="n">connection</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="n">mytable</span><span class="o">.</span><span class="n">select</span><span class="p">())</span>
<span class="n">mytable</span><span class="o">.</span><span class="n">drop</span><span class="p">(</span><span class="n">engine</span><span class="p">)</span></pre></div>
</div>
<p>Above, a connection pool connection is still checked out; furthermore, the
result object above also maintains a link to this connection. If
“implicit execution” is used, the result will hold this connection opened until
the result object is closed or all rows are exhausted.</p>
<p>The call to <tt class="docutils literal"><span class="pre">mytable.drop(engine)</span></tt> attempts to emit DROP TABLE on a second
connection procured from the <a class="reference internal" href="core/connections.html#sqlalchemy.engine.Engine" title="sqlalchemy.engine.Engine"><tt class="xref py py-class docutils literal"><span class="pre">Engine</span></tt></a> which will lock.</p>
<p>The solution is to close out all connections before emitting DROP TABLE:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">connection</span> <span class="o">=</span> <span class="n">engine</span><span class="o">.</span><span class="n">connect</span><span class="p">()</span>
<span class="n">result</span> <span class="o">=</span> <span class="n">connection</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="n">mytable</span><span class="o">.</span><span class="n">select</span><span class="p">())</span>
<span class="c"># fully read result sets</span>
<span class="n">result</span><span class="o">.</span><span class="n">fetchall</span><span class="p">()</span>
<span class="c"># close connections</span>
<span class="n">connection</span><span class="o">.</span><span class="n">close</span><span class="p">()</span>
<span class="c"># now locks are removed</span>
<span class="n">mytable</span><span class="o">.</span><span class="n">drop</span><span class="p">(</span><span class="n">engine</span><span class="p">)</span></pre></div>
</div>
</div>
<div class="section" id="does-sqlalchemy-support-alter-table-create-view-create-trigger-schema-upgrade-functionality">
<h3>Does SQLAlchemy support ALTER TABLE, CREATE VIEW, CREATE TRIGGER, Schema Upgrade Functionality?<a class="headerlink" href="#does-sqlalchemy-support-alter-table-create-view-create-trigger-schema-upgrade-functionality" title="Permalink to this headline">¶</a></h3>
<p>General ALTER support isn’t present in SQLAlchemy directly. For special DDL
on an ad-hoc basis, the <a class="reference internal" href="core/ddl.html#sqlalchemy.schema.DDL" title="sqlalchemy.schema.DDL"><tt class="xref py py-class docutils literal"><span class="pre">DDL</span></tt></a> and related constructs can be used.
See <a class="reference internal" href="core/ddl.html"><em>Customizing DDL</em></a> for a discussion on this subject.</p>
<p>A more comprehensive option is to use schema migration tools, such as Alembic
or SQLAlchemy-Migrate; see <a class="reference internal" href="core/metadata.html#schema-migrations"><em>Altering Schemas through Migrations</em></a> for discussion on this.</p>
</div>
<div class="section" id="how-can-i-sort-table-objects-in-order-of-their-dependency">
<h3>How can I sort Table objects in order of their dependency?<a class="headerlink" href="#how-can-i-sort-table-objects-in-order-of-their-dependency" title="Permalink to this headline">¶</a></h3>
<p>This is available via the <a class="reference internal" href="core/metadata.html#sqlalchemy.schema.MetaData.sorted_tables" title="sqlalchemy.schema.MetaData.sorted_tables"><tt class="xref py py-attr docutils literal"><span class="pre">MetaData.sorted_tables</span></tt></a> function:</p>
<div class="highlight-python"><pre>metadata = MetaData()
# ... add Table objects to metadata
ti = metadata.sorted_tables:
for t in ti:
print t</pre>
</div>
</div>
<div class="section" id="how-can-i-get-the-create-table-drop-table-output-as-a-string">
<h3>How can I get the CREATE TABLE/ DROP TABLE output as a string?<a class="headerlink" href="#how-can-i-get-the-create-table-drop-table-output-as-a-string" title="Permalink to this headline">¶</a></h3>
<p>Modern SQLAlchemy has clause constructs which represent DDL operations. These
can be rendered to strings like any other SQL expression:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="kn">from</span> <span class="nn">sqlalchemy.schema</span> <span class="kn">import</span> <span class="n">CreateTable</span>
<span class="k">print</span> <span class="n">CreateTable</span><span class="p">(</span><span class="n">mytable</span><span class="p">)</span></pre></div>
</div>
<p>To get the string specific to a certain engine:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="k">print</span> <span class="n">CreateTable</span><span class="p">(</span><span class="n">mytable</span><span class="p">)</span><span class="o">.</span><span class="n">compile</span><span class="p">(</span><span class="n">engine</span><span class="p">)</span></pre></div>
</div>
<p>There’s also a special form of <a class="reference internal" href="core/connections.html#sqlalchemy.engine.Engine" title="sqlalchemy.engine.Engine"><tt class="xref py py-class docutils literal"><span class="pre">Engine</span></tt></a> that can let you dump an entire
metadata creation sequence, using this recipe:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="k">def</span> <span class="nf">dump</span><span class="p">(</span><span class="n">sql</span><span class="p">,</span> <span class="o">*</span><span class="n">multiparams</span><span class="p">,</span> <span class="o">**</span><span class="n">params</span><span class="p">):</span>
<span class="k">print</span> <span class="n">sql</span><span class="o">.</span><span class="n">compile</span><span class="p">(</span><span class="n">dialect</span><span class="o">=</span><span class="n">engine</span><span class="o">.</span><span class="n">dialect</span><span class="p">)</span>
<span class="n">engine</span> <span class="o">=</span> <span class="n">create_engine</span><span class="p">(</span><span class="s">'postgresql://'</span><span class="p">,</span> <span class="n">strategy</span><span class="o">=</span><span class="s">'mock'</span><span class="p">,</span> <span class="n">executor</span><span class="o">=</span><span class="n">dump</span><span class="p">)</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> <span class="n">checkfirst</span><span class="o">=</span><span class="bp">False</span><span class="p">)</span></pre></div>
</div>
<p>The <a class="reference external" href="https://bitbucket.org/zzzeek/alembic">Alembic</a> tool also supports
an “offline” SQL generation mode that renders database migrations as SQL scripts.</p>
</div>
<div class="section" id="how-can-i-subclass-table-column-to-provide-certain-behaviors-configurations">
<h3>How can I subclass Table/Column to provide certain behaviors/configurations?<a class="headerlink" href="#how-can-i-subclass-table-column-to-provide-certain-behaviors-configurations" title="Permalink to this headline">¶</a></h3>
<p><a class="reference internal" href="core/metadata.html#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal"><span class="pre">Table</span></tt></a> and <a class="reference internal" href="core/metadata.html#sqlalchemy.schema.Column" title="sqlalchemy.schema.Column"><tt class="xref py py-class docutils literal"><span class="pre">Column</span></tt></a> are not good targets for direct subclassing.
However, there are simple ways to get on-construction behaviors using creation
functions, and behaviors related to the linkages between schema objects such as
constraint conventions or naming conventions using attachment events.
An example of many of these
techniques can be seen at <a class="reference external" href="http://www.sqlalchemy.org/trac/wiki/UsageRecipes/NamingConventions">Naming Conventions</a>.</p>
</div>
</div>
<div class="section" id="sql-expressions">
<h2>SQL Expressions<a class="headerlink" href="#sql-expressions" title="Permalink to this headline">¶</a></h2>
<div class="section" id="how-do-i-render-sql-expressions-as-strings-possibly-with-bound-parameters-inlined">
<span id="faq-sql-expression-string"></span><h3>How do I render SQL expressions as strings, possibly with bound parameters inlined?<a class="headerlink" href="#how-do-i-render-sql-expressions-as-strings-possibly-with-bound-parameters-inlined" title="Permalink to this headline">¶</a></h3>
<p>The “stringification” of a SQLAlchemy statement or Query in the vast majority
of cases is as simple as:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="k">print</span><span class="p">(</span><span class="nb">str</span><span class="p">(</span><span class="n">statement</span><span class="p">))</span></pre></div>
</div>
<p>this applies both to an ORM <a class="reference internal" href="orm/query.html#sqlalchemy.orm.query.Query" title="sqlalchemy.orm.query.Query"><tt class="xref py py-class docutils literal"><span class="pre">Query</span></tt></a> as well as any <a class="reference internal" href="core/selectable.html#sqlalchemy.sql.expression.select" title="sqlalchemy.sql.expression.select"><tt class="xref py py-func docutils literal"><span class="pre">select()</span></tt></a> or other
statement. Additionally, to get the statement as compiled to a
specific dialect or engine, if the statement itself is not already
bound to one you can pass this in to <a class="reference internal" href="core/sqlelement.html#sqlalchemy.sql.expression.ClauseElement.compile" title="sqlalchemy.sql.expression.ClauseElement.compile"><tt class="xref py py-meth docutils literal"><span class="pre">ClauseElement.compile()</span></tt></a>:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="k">print</span><span class="p">(</span><span class="n">statement</span><span class="o">.</span><span class="n">compile</span><span class="p">(</span><span class="n">someengine</span><span class="p">))</span></pre></div>
</div>
<p>or without an <a class="reference internal" href="core/connections.html#sqlalchemy.engine.Engine" title="sqlalchemy.engine.Engine"><tt class="xref py py-class docutils literal"><span class="pre">Engine</span></tt></a>:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="kn">from</span> <span class="nn">sqlalchemy.dialects</span> <span class="kn">import</span> <span class="n">postgresql</span>
<span class="k">print</span><span class="p">(</span><span class="n">statement</span><span class="o">.</span><span class="n">compile</span><span class="p">(</span><span class="n">dialect</span><span class="o">=</span><span class="n">postgresql</span><span class="o">.</span><span class="n">dialect</span><span class="p">()))</span></pre></div>
</div>
<p>When given an ORM <a class="reference internal" href="orm/query.html#sqlalchemy.orm.query.Query" title="sqlalchemy.orm.query.Query"><tt class="xref py py-class docutils literal"><span class="pre">Query</span></tt></a> object, in order to get at the
<a class="reference internal" href="core/sqlelement.html#sqlalchemy.sql.expression.ClauseElement.compile" title="sqlalchemy.sql.expression.ClauseElement.compile"><tt class="xref py py-meth docutils literal"><span class="pre">ClauseElement.compile()</span></tt></a>
method we only need access the <a class="reference internal" href="orm/query.html#sqlalchemy.orm.query.Query.statement" title="sqlalchemy.orm.query.Query.statement"><tt class="xref py py-attr docutils literal"><span class="pre">statement</span></tt></a>
accessor first:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">statement</span> <span class="o">=</span> <span class="n">query</span><span class="o">.</span><span class="n">statement</span>
<span class="k">print</span><span class="p">(</span><span class="n">statement</span><span class="o">.</span><span class="n">compile</span><span class="p">(</span><span class="n">someengine</span><span class="p">))</span></pre></div>
</div>
<p>The above forms will render the SQL statement as it is passed to the Python
<a class="reference internal" href="glossary.html#term-dbapi"><em class="xref std std-term">DBAPI</em></a>, which includes that bound parameters are not rendered inline.
SQLAlchemy normally does not stringify bound parameters, as this is handled
appropriately by the Python DBAPI, not to mention bypassing bound
parameters is probably the most widely exploited security hole in
modern web applications. SQLAlchemy has limited ability to do this
stringification in certain circumstances such as that of emitting DDL.
In order to access this functionality one can use the <tt class="docutils literal"><span class="pre">literal_binds</span></tt>
flag, passed to <tt class="docutils literal"><span class="pre">compile_kwargs</span></tt>:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="kn">from</span> <span class="nn">sqlalchemy.sql</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">select</span>
<span class="n">t</span> <span class="o">=</span> <span class="n">table</span><span class="p">(</span><span class="s">'t'</span><span class="p">,</span> <span class="n">column</span><span class="p">(</span><span class="s">'x'</span><span class="p">))</span>
<span class="n">s</span> <span class="o">=</span> <span class="n">select</span><span class="p">([</span><span class="n">t</span><span class="p">])</span><span class="o">.</span><span class="n">where</span><span class="p">(</span><span class="n">t</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">x</span> <span class="o">==</span> <span class="mi">5</span><span class="p">)</span>
<span class="k">print</span><span class="p">(</span><span class="n">s</span><span class="o">.</span><span class="n">compile</span><span class="p">(</span><span class="n">compile_kwargs</span><span class="o">=</span><span class="p">{</span><span class="s">"literal_binds"</span><span class="p">:</span> <span class="bp">True</span><span class="p">}))</span></pre></div>
</div>
<p>the above approach has the caveats that it is only supported for basic
types, such as ints and strings, and furthermore if a <a class="reference internal" href="core/sqlelement.html#sqlalchemy.sql.expression.bindparam" title="sqlalchemy.sql.expression.bindparam"><tt class="xref py py-func docutils literal"><span class="pre">bindparam()</span></tt></a>
without a pre-set value is used directly, it won’t be able to
stringify that either.</p>
<p>To support inline literal rendering for types not supported, implement
a <a class="reference internal" href="core/types.html#sqlalchemy.types.TypeDecorator" title="sqlalchemy.types.TypeDecorator"><tt class="xref py py-class docutils literal"><span class="pre">TypeDecorator</span></tt></a> for the target type which includes a
<a class="reference internal" href="core/types.html#sqlalchemy.types.TypeDecorator.process_literal_param" title="sqlalchemy.types.TypeDecorator.process_literal_param"><tt class="xref py py-meth docutils literal"><span class="pre">TypeDecorator.process_literal_param()</span></tt></a> method:</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">TypeDecorator</span><span class="p">,</span> <span class="n">Integer</span>
<span class="k">class</span> <span class="nc">MyFancyType</span><span class="p">(</span><span class="n">TypeDecorator</span><span class="p">):</span>
<span class="n">impl</span> <span class="o">=</span> <span class="n">Integer</span>
<span class="k">def</span> <span class="nf">process_literal_param</span><span class="p">(</span><span class="bp">self</span><span class="p">,</span> <span class="n">value</span><span class="p">,</span> <span class="n">dialect</span><span class="p">):</span>
<span class="k">return</span> <span class="s">"my_fancy_formatting(</span><span class="si">%s</span><span class="s">)"</span> <span class="o">%</span> <span class="n">value</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">MetaData</span>
<span class="n">tab</span> <span class="o">=</span> <span class="n">Table</span><span class="p">(</span><span class="s">'mytable'</span><span class="p">,</span> <span class="n">MetaData</span><span class="p">(),</span> <span class="n">Column</span><span class="p">(</span><span class="s">'x'</span><span class="p">,</span> <span class="n">MyFancyType</span><span class="p">()))</span>
<span class="k">print</span><span class="p">(</span>
<span class="n">tab</span><span class="o">.</span><span class="n">select</span><span class="p">()</span><span class="o">.</span><span class="n">where</span><span class="p">(</span><span class="n">tab</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">x</span> <span class="o">></span> <span class="mi">5</span><span class="p">)</span><span class="o">.</span><span class="n">compile</span><span class="p">(</span>
<span class="n">compile_kwargs</span><span class="o">=</span><span class="p">{</span><span class="s">"literal_binds"</span><span class="p">:</span> <span class="bp">True</span><span class="p">})</span>
<span class="p">)</span></pre></div>
</div>
<p>producing output like:</p>
<div class="highlight-python"><pre>SELECT mytable.x
FROM mytable
WHERE mytable.x > my_fancy_formatting(5)</pre>
</div>
</div>
<div class="section" id="why-does-col-in-produce-col-col-why-not-1-0">
<h3>Why does <tt class="docutils literal"><span class="pre">.col.in_([])</span></tt> Produce <tt class="docutils literal"><span class="pre">col</span> <span class="pre">!=</span> <span class="pre">col</span></tt>? Why not <tt class="docutils literal"><span class="pre">1=0</span></tt>?<a class="headerlink" href="#why-does-col-in-produce-col-col-why-not-1-0" title="Permalink to this headline">¶</a></h3>
<p>A little introduction to the issue. The IN operator in SQL, given a list of
elements to compare against a column, generally does not accept an empty list,
that is while it is valid to say:</p>
<div class="highlight-python"><pre>column IN (1, 2, 3)</pre>
</div>
<p>it’s not valid to say:</p>
<div class="highlight-python"><pre>column IN ()</pre>
</div>
<p>SQLAlchemy’s <tt class="xref py py-meth docutils literal"><span class="pre">Operators.in_()</span></tt> operator, when given an empty list, produces this
expression:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">column</span> <span class="o">!=</span> <span class="n">column</span></pre></div>
</div>
<p>As of version 0.6, it also produces a warning stating that a less efficient
comparison operation will be rendered. This expression is the only one that is
both database agnostic and produces correct results.</p>
<p>For example, the naive approach of “just evaluate to false, by comparing 1=0
or 1!=1”, does not handle nulls properly. An expression like:</p>
<div class="highlight-python"><pre>NOT column != column</pre>
</div>
<p>will not return a row when “column” is null, but an expression which does not
take the column into account:</p>
<div class="highlight-python"><pre>NOT 1=0</pre>
</div>
<p>will.</p>
<p>Closer to the mark is the following CASE expression:</p>
<div class="highlight-python"><pre>CASE WHEN column IS NOT NULL THEN 1=0 ELSE NULL END</pre>
</div>
<p>We don’t use this expression due to its verbosity, and its also not
typically accepted by Oracle within a WHERE clause - depending
on how you phrase it, you’ll either get “ORA-00905: missing keyword” or
“ORA-00920: invalid relational operator”. It’s also still less efficient than
just rendering SQL without the clause altogether (or not issuing the SQL at
all, if the statement is just a simple search).</p>
<p>The best approach therefore is to avoid the usage of IN given an argument list
of zero length. Instead, don’t emit the Query in the first place, if no rows
should be returned. The warning is best promoted to a full error condition
using the Python warnings filter (see <a class="reference external" href="http://docs.python.org/library/warnings.html">http://docs.python.org/library/warnings.html</a>).</p>
</div>
</div>
<div class="section" id="orm-configuration">
<h2>ORM Configuration<a class="headerlink" href="#orm-configuration" title="Permalink to this headline">¶</a></h2>
<div class="section" id="how-do-i-map-a-table-that-has-no-primary-key">
<span id="faq-mapper-primary-key"></span><h3>How do I map a table that has no primary key?<a class="headerlink" href="#how-do-i-map-a-table-that-has-no-primary-key" title="Permalink to this headline">¶</a></h3>
<p>The SQLAlchemy ORM, in order to map to a particular table, needs there to be
at least one column denoted as a primary key column; multiple-column,
i.e. composite, primary keys are of course entirely feasible as well. These
columns do <strong>not</strong> need to be actually known to the database as primary key
columns, though it’s a good idea that they are. It’s only necessary that the columns
<em>behave</em> as a primary key does, e.g. as a unique and not nullable identifier
for a row.</p>
<p>Most ORMs require that objects have some kind of primary key defined
because the object in memory must correspond to a uniquely identifiable
row in the database table; at the very least, this allows the
object can be targeted for UPDATE and DELETE statements which will affect only
that object’s row and no other. However, the importance of the primary key
goes far beyond that. In SQLAlchemy, all ORM-mapped objects are at all times
linked uniquely within a <a class="reference internal" href="orm/session.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>
to their specific database row using a pattern called the <a class="reference internal" href="glossary.html#term-identity-map"><em class="xref std std-term">identity map</em></a>,
a pattern that’s central to the unit of work system employed by SQLAlchemy,
and is also key to the most common (and not-so-common) patterns of ORM usage.</p>
<div class="admonition note">
<p class="first admonition-title">Note</p>
<p class="last">It’s important to note that we’re only talking about the SQLAlchemy ORM; an
application which builds on Core and deals only with <a class="reference internal" href="core/metadata.html#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal"><span class="pre">Table</span></tt></a> objects,
<a class="reference internal" href="core/selectable.html#sqlalchemy.sql.expression.select" title="sqlalchemy.sql.expression.select"><tt class="xref py py-func docutils literal"><span class="pre">select()</span></tt></a> constructs and the like, <strong>does not</strong> need any primary key
to be present on or associated with a table in any way (though again, in SQL, all tables
should really have some kind of primary key, lest you need to actually
update or delete specific rows).</p>
</div>
<p>In almost all cases, a table does have a so-called <a class="reference internal" href="glossary.html#term-candidate-key"><em class="xref std std-term">candidate key</em></a>, which is a column or series
of columns that uniquely identify a row. If a table truly doesn’t have this, and has actual
fully duplicate rows, the table is not corresponding to <a class="reference external" href="http://en.wikipedia.org/wiki/First_normal_form">first normal form</a> and cannot be mapped. Otherwise, whatever columns comprise the best candidate key can be
applied directly to the mapper:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="k">class</span> <span class="nc">SomeClass</span><span class="p">(</span><span class="n">Base</span><span class="p">):</span>
<span class="n">__table__</span> <span class="o">=</span> <span class="n">some_table_with_no_pk</span>
<span class="n">__mapper_args__</span> <span class="o">=</span> <span class="p">{</span>
<span class="s">'primary_key'</span><span class="p">:[</span><span class="n">some_table_with_no_pk</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">uid</span><span class="p">,</span> <span class="n">some_table_with_no_pk</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">bar</span><span class="p">]</span>
<span class="p">}</span></pre></div>
</div>
<p>Better yet is when using fully declared table metadata, use the <tt class="docutils literal"><span class="pre">primary_key=True</span></tt>
flag on those columns:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="k">class</span> <span class="nc">SomeClass</span><span class="p">(</span><span class="n">Base</span><span class="p">):</span>
<span class="n">__tablename__</span> <span class="o">=</span> <span class="s">"some_table_with_no_pk"</span>
<span class="n">uid</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="n">bar</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">primary_key</span><span class="o">=</span><span class="bp">True</span><span class="p">)</span></pre></div>
</div>
<p>All tables in a relational database should have primary keys. Even a many-to-many
association table - the primary key would be the composite of the two association
columns:</p>
<div class="highlight-python"><pre>CREATE TABLE my_association (
user_id INTEGER REFERENCES user(id),
account_id INTEGER REFERENCES account(id),
PRIMARY KEY (user_id, account_id)
)</pre>
</div>
</div>
<div class="section" id="how-do-i-configure-a-column-that-is-a-python-reserved-word-or-similar">
<h3>How do I configure a Column that is a Python reserved word or similar?<a class="headerlink" href="#how-do-i-configure-a-column-that-is-a-python-reserved-word-or-similar" title="Permalink to this headline">¶</a></h3>
<p>Column-based attributes can be given any name desired in the mapping. See
<a class="reference internal" href="orm/mapper_config.html#mapper-column-distinct-names"><em>Naming Columns Distinctly from Attribute Names</em></a>.</p>
</div>
<div class="section" id="how-do-i-get-a-list-of-all-columns-relationships-mapped-attributes-etc-given-a-mapped-class">
<h3>How do I get a list of all columns, relationships, mapped attributes, etc. given a mapped class?<a class="headerlink" href="#how-do-i-get-a-list-of-all-columns-relationships-mapped-attributes-etc-given-a-mapped-class" title="Permalink to this headline">¶</a></h3>
<p>This information is all available from the <a class="reference internal" href="orm/mapper_config.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.</p>
<p>To get at the <a class="reference internal" href="orm/mapper_config.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> for a particular mapped class, call the
<a class="reference internal" href="core/inspection.html#sqlalchemy.inspection.inspect" title="sqlalchemy.inspection.inspect"><tt class="xref py py-func docutils literal"><span class="pre">inspect()</span></tt></a> function on it:</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">inspect</span>
<span class="n">mapper</span> <span class="o">=</span> <span class="n">inspect</span><span class="p">(</span><span class="n">MyClass</span><span class="p">)</span></pre></div>
</div>
<p>From there, all information about the class can be acquired using such methods as:</p>
<ul class="simple">
<li><a class="reference internal" href="orm/mapper_config.html#sqlalchemy.orm.mapper.Mapper.attrs" title="sqlalchemy.orm.mapper.Mapper.attrs"><tt class="xref py py-attr docutils literal"><span class="pre">Mapper.attrs</span></tt></a> - a namespace of all mapped attributes. The attributes
themselves are instances of <a class="reference internal" href="orm/internals.html#sqlalchemy.orm.interfaces.MapperProperty" title="sqlalchemy.orm.interfaces.MapperProperty"><tt class="xref py py-class docutils literal"><span class="pre">MapperProperty</span></tt></a>, which contain additional
attributes that can lead to the mapped SQL expression or column, if applicable.</li>
<li><a class="reference internal" href="orm/mapper_config.html#sqlalchemy.orm.mapper.Mapper.column_attrs" title="sqlalchemy.orm.mapper.Mapper.column_attrs"><tt class="xref py py-attr docutils literal"><span class="pre">Mapper.column_attrs</span></tt></a> - the mapped attribute namespace
limited to column and SQL expression attributes. You might want to use
<a class="reference internal" href="orm/mapper_config.html#sqlalchemy.orm.mapper.Mapper.columns" title="sqlalchemy.orm.mapper.Mapper.columns"><tt class="xref py py-attr docutils literal"><span class="pre">Mapper.columns</span></tt></a> to get at the <a class="reference internal" href="core/metadata.html#sqlalchemy.schema.Column" title="sqlalchemy.schema.Column"><tt class="xref py py-class docutils literal"><span class="pre">Column</span></tt></a> objects directly.</li>
<li><a class="reference internal" href="orm/mapper_config.html#sqlalchemy.orm.mapper.Mapper.relationships" title="sqlalchemy.orm.mapper.Mapper.relationships"><tt class="xref py py-attr docutils literal"><span class="pre">Mapper.relationships</span></tt></a> - namespace of all <a class="reference internal" href="orm/internals.html#sqlalchemy.orm.properties.RelationshipProperty" title="sqlalchemy.orm.properties.RelationshipProperty"><tt class="xref py py-class docutils literal"><span class="pre">RelationshipProperty</span></tt></a> attributes.</li>
<li><a class="reference internal" href="orm/mapper_config.html#sqlalchemy.orm.mapper.Mapper.all_orm_descriptors" title="sqlalchemy.orm.mapper.Mapper.all_orm_descriptors"><tt class="xref py py-attr docutils literal"><span class="pre">Mapper.all_orm_descriptors</span></tt></a> - namespace of all mapped attributes, plus user-defined
attributes defined using systems such as <a class="reference internal" href="orm/extensions/hybrid.html#sqlalchemy.ext.hybrid.hybrid_property" title="sqlalchemy.ext.hybrid.hybrid_property"><tt class="xref py py-class docutils literal"><span class="pre">hybrid_property</span></tt></a>, <a class="reference internal" href="orm/extensions/associationproxy.html#sqlalchemy.ext.associationproxy.AssociationProxy" title="sqlalchemy.ext.associationproxy.AssociationProxy"><tt class="xref py py-class docutils literal"><span class="pre">AssociationProxy</span></tt></a> and others.</li>
<li><a class="reference internal" href="orm/mapper_config.html#sqlalchemy.orm.mapper.Mapper.columns" title="sqlalchemy.orm.mapper.Mapper.columns"><tt class="xref py py-attr docutils literal"><span class="pre">Mapper.columns</span></tt></a> - A namespace of <a class="reference internal" href="core/metadata.html#sqlalchemy.schema.Column" title="sqlalchemy.schema.Column"><tt class="xref py py-class docutils literal"><span class="pre">Column</span></tt></a> objects and other named
SQL expressions associated with the mapping.</li>
<li><a class="reference internal" href="orm/mapper_config.html#sqlalchemy.orm.mapper.Mapper.mapped_table" title="sqlalchemy.orm.mapper.Mapper.mapped_table"><tt class="xref py py-attr docutils literal"><span class="pre">Mapper.mapped_table</span></tt></a> - The <a class="reference internal" href="core/metadata.html#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal"><span class="pre">Table</span></tt></a> or other selectable to which
this mapper is mapped.</li>
<li><a class="reference internal" href="orm/mapper_config.html#sqlalchemy.orm.mapper.Mapper.local_table" title="sqlalchemy.orm.mapper.Mapper.local_table"><tt class="xref py py-attr docutils literal"><span class="pre">Mapper.local_table</span></tt></a> - The <a class="reference internal" href="core/metadata.html#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal"><span class="pre">Table</span></tt></a> that is “local” to this mapper;
this differs from <a class="reference internal" href="orm/mapper_config.html#sqlalchemy.orm.mapper.Mapper.mapped_table" title="sqlalchemy.orm.mapper.Mapper.mapped_table"><tt class="xref py py-attr docutils literal"><span class="pre">Mapper.mapped_table</span></tt></a> in the case of a mapper mapped
using inheritance to a composed selectable.</li>
</ul>
</div>
<div class="section" id="i-m-getting-a-warning-or-error-about-implicitly-combining-column-x-under-attribute-y">
<span id="faq-combining-columns"></span><h3>I’m getting a warning or error about “Implicitly combining column X under attribute Y”<a class="headerlink" href="#i-m-getting-a-warning-or-error-about-implicitly-combining-column-x-under-attribute-y" title="Permalink to this headline">¶</a></h3>
<p>This condition refers to when a mapping contains two columns that are being
mapped under the same attribute name due to their name, but there’s no indication
that this is intentional. A mapped class needs to have explicit names for
every attribute that is to store an independent value; when two columns have the
same name and aren’t disambiguated, they fall under the same attribute and
the effect is that the value from one column is <strong>copied</strong> into the other, based
on which column was assigned to the attribute first.</p>
<p>This behavior is often desirable and is allowed without warning in the case
where the two columns are linked together via a foreign key relationship
within an inheritance mapping. When the warning or exception occurs, the
issue can be resolved by either assigning the columns to differently-named
attributes, or if combining them together is desired, by using
<a class="reference internal" href="orm/mapper_config.html#sqlalchemy.orm.column_property" title="sqlalchemy.orm.column_property"><tt class="xref py py-func docutils literal"><span class="pre">column_property()</span></tt></a> to make this explicit.</p>
<p>Given the example as follows:</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">Integer</span><span class="p">,</span> <span class="n">Column</span><span class="p">,</span> <span class="n">ForeignKey</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="n">Base</span> <span class="o">=</span> <span class="n">declarative_base</span><span class="p">()</span>
<span class="k">class</span> <span class="nc">A</span><span class="p">(</span><span class="n">Base</span><span class="p">):</span>
<span class="n">__tablename__</span> <span class="o">=</span> <span class="s">'a'</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="k">class</span> <span class="nc">B</span><span class="p">(</span><span class="n">A</span><span class="p">):</span>
<span class="n">__tablename__</span> <span class="o">=</span> <span class="s">'b'</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="n">a_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">'a.id'</span><span class="p">))</span></pre></div>
</div>
<p>As of SQLAlchemy version 0.9.5, the above condition is detected, and will
warn that the <tt class="docutils literal"><span class="pre">id</span></tt> column of <tt class="docutils literal"><span class="pre">A</span></tt> and <tt class="docutils literal"><span class="pre">B</span></tt> is being combined under
the same-named attribute <tt class="docutils literal"><span class="pre">id</span></tt>, which above is a serious issue since it means
that a <tt class="docutils literal"><span class="pre">B</span></tt> object’s primary key will always mirror that of its <tt class="docutils literal"><span class="pre">A</span></tt>.</p>
<p>A mapping which resolves this is as follows:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="k">class</span> <span class="nc">A</span><span class="p">(</span><span class="n">Base</span><span class="p">):</span>
<span class="n">__tablename__</span> <span class="o">=</span> <span class="s">'a'</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="k">class</span> <span class="nc">B</span><span class="p">(</span><span class="n">A</span><span class="p">):</span>
<span class="n">__tablename__</span> <span class="o">=</span> <span class="s">'b'</span>
<span class="n">b_id</span> <span class="o">=</span> <span class="n">Column</span><span class="p">(</span><span class="s">'id'</span><span class="p">,</span> <span class="n">Integer</span><span class="p">,</span> <span class="n">primary_key</span><span class="o">=</span><span class="bp">True</span><span class="p">)</span>
<span class="n">a_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">'a.id'</span><span class="p">))</span></pre></div>
</div>
<p>Suppose we did want <tt class="docutils literal"><span class="pre">A.id</span></tt> and <tt class="docutils literal"><span class="pre">B.id</span></tt> to be mirrors of each other, despite
the fact that <tt class="docutils literal"><span class="pre">B.a_id</span></tt> is where <tt class="docutils literal"><span class="pre">A.id</span></tt> is related. We could combine
them together using <a class="reference internal" href="orm/mapper_config.html#sqlalchemy.orm.column_property" title="sqlalchemy.orm.column_property"><tt class="xref py py-func docutils literal"><span class="pre">column_property()</span></tt></a>:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="k">class</span> <span class="nc">A</span><span class="p">(</span><span class="n">Base</span><span class="p">):</span>
<span class="n">__tablename__</span> <span class="o">=</span> <span class="s">'a'</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="k">class</span> <span class="nc">B</span><span class="p">(</span><span class="n">A</span><span class="p">):</span>
<span class="n">__tablename__</span> <span class="o">=</span> <span class="s">'b'</span>
<span class="c"># probably not what you want, but this is a demonstration</span>
<span class="nb">id</span> <span class="o">=</span> <span class="n">column_property</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">primary_key</span><span class="o">=</span><span class="bp">True</span><span class="p">),</span> <span class="n">A</span><span class="o">.</span><span class="n">id</span><span class="p">)</span>
<span class="n">a_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">'a.id'</span><span class="p">))</span></pre></div>
</div>
</div>
<div class="section" id="i-m-using-declarative-and-setting-primaryjoin-secondaryjoin-using-an-and-or-or-and-i-am-getting-an-error-message-about-foreign-keys">
<h3>I’m using Declarative and setting primaryjoin/secondaryjoin using an <tt class="docutils literal"><span class="pre">and_()</span></tt> or <tt class="docutils literal"><span class="pre">or_()</span></tt>, and I am getting an error message about foreign keys.<a class="headerlink" href="#i-m-using-declarative-and-setting-primaryjoin-secondaryjoin-using-an-and-or-or-and-i-am-getting-an-error-message-about-foreign-keys" title="Permalink to this headline">¶</a></h3>
<p>Are you doing this?:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="k">class</span> <span class="nc">MyClass</span><span class="p">(</span><span class="n">Base</span><span class="p">):</span>
<span class="c"># ....</span>
<span class="n">foo</span> <span class="o">=</span> <span class="n">relationship</span><span class="p">(</span><span class="s">"Dest"</span><span class="p">,</span> <span class="n">primaryjoin</span><span class="o">=</span><span class="n">and_</span><span class="p">(</span><span class="s">"MyClass.id==Dest.foo_id"</span><span class="p">,</span> <span class="s">"MyClass.foo==Dest.bar"</span><span class="p">))</span></pre></div>
</div>
<p>That’s an <tt class="docutils literal"><span class="pre">and_()</span></tt> of two string expressions, which SQLAlchemy cannot apply any mapping towards. Declarative allows <a class="reference internal" href="orm/relationships.html#sqlalchemy.orm.relationship" title="sqlalchemy.orm.relationship"><tt class="xref py py-func docutils literal"><span class="pre">relationship()</span></tt></a> arguments to be specified as strings, which are converted into expression objects using <tt class="docutils literal"><span class="pre">eval()</span></tt>. But this doesn’t occur inside of an <tt class="docutils literal"><span class="pre">and_()</span></tt> expression - it’s a special operation declarative applies only to the <em>entirety</em> of what’s passed to primaryjoin or other arguments as a string:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="k">class</span> <span class="nc">MyClass</span><span class="p">(</span><span class="n">Base</span><span class="p">):</span>
<span class="c"># ....</span>
<span class="n">foo</span> <span class="o">=</span> <span class="n">relationship</span><span class="p">(</span><span class="s">"Dest"</span><span class="p">,</span> <span class="n">primaryjoin</span><span class="o">=</span><span class="s">"and_(MyClass.id==Dest.foo_id, MyClass.foo==Dest.bar)"</span><span class="p">)</span></pre></div>
</div>
<p>Or if the objects you need are already available, skip the strings:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="k">class</span> <span class="nc">MyClass</span><span class="p">(</span><span class="n">Base</span><span class="p">):</span>
<span class="c"># ....</span>
<span class="n">foo</span> <span class="o">=</span> <span class="n">relationship</span><span class="p">(</span><span class="n">Dest</span><span class="p">,</span> <span class="n">primaryjoin</span><span class="o">=</span><span class="n">and_</span><span class="p">(</span><span class="n">MyClass</span><span class="o">.</span><span class="n">id</span><span class="o">==</span><span class="n">Dest</span><span class="o">.</span><span class="n">foo_id</span><span class="p">,</span> <span class="n">MyClass</span><span class="o">.</span><span class="n">foo</span><span class="o">==</span><span class="n">Dest</span><span class="o">.</span><span class="n">bar</span><span class="p">))</span></pre></div>
</div>
<p>The same idea applies to all the other arguments, such as <tt class="docutils literal"><span class="pre">foreign_keys</span></tt>:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="c"># wrong !</span>
<span class="n">foo</span> <span class="o">=</span> <span class="n">relationship</span><span class="p">(</span><span class="n">Dest</span><span class="p">,</span> <span class="n">foreign_keys</span><span class="o">=</span><span class="p">[</span><span class="s">"Dest.foo_id"</span><span class="p">,</span> <span class="s">"Dest.bar_id"</span><span class="p">])</span>
<span class="c"># correct !</span>
<span class="n">foo</span> <span class="o">=</span> <span class="n">relationship</span><span class="p">(</span><span class="n">Dest</span><span class="p">,</span> <span class="n">foreign_keys</span><span class="o">=</span><span class="s">"[Dest.foo_id, Dest.bar_id]"</span><span class="p">)</span>
<span class="c"># also correct !</span>
<span class="n">foo</span> <span class="o">=</span> <span class="n">relationship</span><span class="p">(</span><span class="n">Dest</span><span class="p">,</span> <span class="n">foreign_keys</span><span class="o">=</span><span class="p">[</span><span class="n">Dest</span><span class="o">.</span><span class="n">foo_id</span><span class="p">,</span> <span class="n">Dest</span><span class="o">.</span><span class="n">bar_id</span><span class="p">])</span>
<span class="c"># if you're using columns from the class that you're inside of, just use the column objects !</span>
<span class="k">class</span> <span class="nc">MyClass</span><span class="p">(</span><span class="n">Base</span><span class="p">):</span>
<span class="n">foo_id</span> <span class="o">=</span> <span class="n">Column</span><span class="p">(</span><span class="o">...</span><span class="p">)</span>
<span class="n">bar_id</span> <span class="o">=</span> <span class="n">Column</span><span class="p">(</span><span class="o">...</span><span class="p">)</span>
<span class="c"># ...</span>
<span class="n">foo</span> <span class="o">=</span> <span class="n">relationship</span><span class="p">(</span><span class="n">Dest</span><span class="p">,</span> <span class="n">foreign_keys</span><span class="o">=</span><span class="p">[</span><span class="n">foo_id</span><span class="p">,</span> <span class="n">bar_id</span><span class="p">])</span></pre></div>
</div>
</div>
</div>
<div class="section" id="performance">
<h2>Performance<a class="headerlink" href="#performance" title="Permalink to this headline">¶</a></h2>
<div class="section" id="how-can-i-profile-a-sqlalchemy-powered-application">
<h3>How can I profile a SQLAlchemy powered application?<a class="headerlink" href="#how-can-i-profile-a-sqlalchemy-powered-application" title="Permalink to this headline">¶</a></h3>
<p>Looking for performance issues typically involves two stratgies. One
is query profiling, and the other is code profiling.</p>
<div class="section" id="query-profiling">
<h4>Query Profiling<a class="headerlink" href="#query-profiling" title="Permalink to this headline">¶</a></h4>
<p>Sometimes just plain SQL logging (enabled via python’s logging module
or via the <tt class="docutils literal"><span class="pre">echo=True</span></tt> argument on <a class="reference internal" href="core/engines.html#sqlalchemy.create_engine" title="sqlalchemy.create_engine"><tt class="xref py py-func docutils literal"><span class="pre">create_engine()</span></tt></a>) can give an
idea how long things are taking. For example, if you log something
right after a SQL operation, you’d see something like this in your
log:</p>
<div class="highlight-python"><pre>17:37:48,325 INFO [sqlalchemy.engine.base.Engine.0x...048c] SELECT ...
17:37:48,326 INFO [sqlalchemy.engine.base.Engine.0x...048c] {<params>}
17:37:48,660 DEBUG [myapp.somemessage]</pre>
</div>
<p>if you logged <tt class="docutils literal"><span class="pre">myapp.somemessage</span></tt> right after the operation, you know
it took 334ms to complete the SQL part of things.</p>
<p>Logging SQL will also illustrate if dozens/hundreds of queries are
being issued which could be better organized into much fewer queries.
When using the SQLAlchemy ORM, the “eager loading”
feature is provided to partially (<a class="reference internal" href="orm/loading.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>) or fully
(<a class="reference internal" href="orm/loading.html#sqlalchemy.orm.joinedload" title="sqlalchemy.orm.joinedload"><tt class="xref py py-func docutils literal"><span class="pre">joinedload()</span></tt></a>, <a class="reference internal" href="orm/loading.html#sqlalchemy.orm.subqueryload" title="sqlalchemy.orm.subqueryload"><tt class="xref py py-func docutils literal"><span class="pre">subqueryload()</span></tt></a>)
automate this activity, but without
the ORM “eager loading” typically means to use joins so that results across multiple
tables can be loaded in one result set instead of multiplying numbers
of queries as more depth is added (i.e. <tt class="docutils literal"><span class="pre">r</span> <span class="pre">+</span> <span class="pre">r*r2</span> <span class="pre">+</span> <span class="pre">r*r2*r3</span></tt> ...)</p>
<p>For more long-term profiling of queries, or to implement an application-side
“slow query” monitor, events can be used to intercept cursor executions,
using a recipe like the following:</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">event</span>
<span class="kn">from</span> <span class="nn">sqlalchemy.engine</span> <span class="kn">import</span> <span class="n">Engine</span>
<span class="kn">import</span> <span class="nn">time</span>
<span class="kn">import</span> <span class="nn">logging</span>
<span class="n">logging</span><span class="o">.</span><span class="n">basicConfig</span><span class="p">()</span>
<span class="n">logger</span> <span class="o">=</span> <span class="n">logging</span><span class="o">.</span><span class="n">getLogger</span><span class="p">(</span><span class="s">"myapp.sqltime"</span><span class="p">)</span>
<span class="n">logger</span><span class="o">.</span><span class="n">setLevel</span><span class="p">(</span><span class="n">logging</span><span class="o">.</span><span class="n">DEBUG</span><span class="p">)</span>
<span class="nd">@event.listens_for</span><span class="p">(</span><span class="n">Engine</span><span class="p">,</span> <span class="s">"before_cursor_execute"</span><span class="p">)</span>
<span class="k">def</span> <span class="nf">before_cursor_execute</span><span class="p">(</span><span class="n">conn</span><span class="p">,</span> <span class="n">cursor</span><span class="p">,</span> <span class="n">statement</span><span class="p">,</span>
<span class="n">parameters</span><span class="p">,</span> <span class="n">context</span><span class="p">,</span> <span class="n">executemany</span><span class="p">):</span>
<span class="n">conn</span><span class="o">.</span><span class="n">info</span><span class="o">.</span><span class="n">setdefault</span><span class="p">(</span><span class="s">'query_start_time'</span><span class="p">,</span> <span class="p">[])</span><span class="o">.</span><span class="n">append</span><span class="p">(</span><span class="n">time</span><span class="o">.</span><span class="n">time</span><span class="p">())</span>
<span class="n">logger</span><span class="o">.</span><span class="n">debug</span><span class="p">(</span><span class="s">"Start Query: </span><span class="si">%s</span><span class="s">"</span> <span class="o">%</span> <span class="n">statement</span><span class="p">)</span>
<span class="nd">@event.listens_for</span><span class="p">(</span><span class="n">Engine</span><span class="p">,</span> <span class="s">"after_cursor_execute"</span><span class="p">)</span>
<span class="k">def</span> <span class="nf">after_cursor_execute</span><span class="p">(</span><span class="n">conn</span><span class="p">,</span> <span class="n">cursor</span><span class="p">,</span> <span class="n">statement</span><span class="p">,</span>
<span class="n">parameters</span><span class="p">,</span> <span class="n">context</span><span class="p">,</span> <span class="n">executemany</span><span class="p">):</span>
<span class="n">total</span> <span class="o">=</span> <span class="n">time</span><span class="o">.</span><span class="n">time</span><span class="p">()</span> <span class="o">-</span> <span class="n">conn</span><span class="o">.</span><span class="n">info</span><span class="p">[</span><span class="s">'query_start_time'</span><span class="p">]</span><span class="o">.</span><span class="n">pop</span><span class="p">(</span><span class="o">-</span><span class="mi">1</span><span class="p">)</span>
<span class="n">logger</span><span class="o">.</span><span class="n">debug</span><span class="p">(</span><span class="s">"Query Complete!"</span><span class="p">)</span>
<span class="n">logger</span><span class="o">.</span><span class="n">debug</span><span class="p">(</span><span class="s">"Total Time: </span><span class="si">%f</span><span class="s">"</span> <span class="o">%</span> <span class="n">total</span><span class="p">)</span></pre></div>
</div>
<p>Above, we use the <a class="reference internal" href="core/events.html#sqlalchemy.events.ConnectionEvents.before_cursor_execute" title="sqlalchemy.events.ConnectionEvents.before_cursor_execute"><tt class="xref py py-meth docutils literal"><span class="pre">ConnectionEvents.before_cursor_execute()</span></tt></a> and
<a class="reference internal" href="core/events.html#sqlalchemy.events.ConnectionEvents.after_cursor_execute" title="sqlalchemy.events.ConnectionEvents.after_cursor_execute"><tt class="xref py py-meth docutils literal"><span class="pre">ConnectionEvents.after_cursor_execute()</span></tt></a> events to establish an interception
point around when a statement is executed. We attach a timer onto the
connection using the <tt class="xref py py-class docutils literal"><span class="pre">_ConnectionRecord.info</span></tt> dictionary; we use a
stack here for the occasional case where the cursor execute events may be nested.</p>
</div>
<div class="section" id="code-profiling">
<h4>Code Profiling<a class="headerlink" href="#code-profiling" title="Permalink to this headline">¶</a></h4>
<p>If logging reveals that individual queries are taking too long, you’d
need a breakdown of how much time was spent within the database
processing the query, sending results over the network, being handled
by the <a class="reference internal" href="glossary.html#term-dbapi"><em class="xref std std-term">DBAPI</em></a>, and finally being received by SQLAlchemy’s result set
and/or ORM layer. Each of these stages can present their own
individual bottlenecks, depending on specifics.</p>
<p>For that you need to use the
<a class="reference external" href="https://docs.python.org/2/library/profile.html">Python Profiling Module</a>.
Below is a simple recipe which works profiling into a context manager:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="kn">import</span> <span class="nn">cProfile</span>
<span class="kn">import</span> <span class="nn">StringIO</span>
<span class="kn">import</span> <span class="nn">pstats</span>
<span class="kn">import</span> <span class="nn">contextlib</span>
<span class="nd">@contextlib.contextmanager</span>
<span class="k">def</span> <span class="nf">profiled</span><span class="p">():</span>
<span class="n">pr</span> <span class="o">=</span> <span class="n">cProfile</span><span class="o">.</span><span class="n">Profile</span><span class="p">()</span>
<span class="n">pr</span><span class="o">.</span><span class="n">enable</span><span class="p">()</span>
<span class="k">yield</span>
<span class="n">pr</span><span class="o">.</span><span class="n">disable</span><span class="p">()</span>
<span class="n">s</span> <span class="o">=</span> <span class="n">StringIO</span><span class="o">.</span><span class="n">StringIO</span><span class="p">()</span>
<span class="n">ps</span> <span class="o">=</span> <span class="n">pstats</span><span class="o">.</span><span class="n">Stats</span><span class="p">(</span><span class="n">pr</span><span class="p">,</span> <span class="n">stream</span><span class="o">=</span><span class="n">s</span><span class="p">)</span><span class="o">.</span><span class="n">sort_stats</span><span class="p">(</span><span class="s">'cumulative'</span><span class="p">)</span>
<span class="n">ps</span><span class="o">.</span><span class="n">print_stats</span><span class="p">()</span>
<span class="c"># uncomment this to see who's calling what</span>
<span class="c"># ps.print_callers()</span>
<span class="k">print</span> <span class="n">s</span><span class="o">.</span><span class="n">getvalue</span><span class="p">()</span></pre></div>
</div>
<p>To profile a section of code:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="k">with</span> <span class="n">profiled</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">FooClass</span><span class="p">)</span><span class="o">.</span><span class="n">filter</span><span class="p">(</span><span class="n">FooClass</span><span class="o">.</span><span class="n">somevalue</span><span class="o">==</span><span class="mi">8</span><span class="p">)</span><span class="o">.</span><span class="n">all</span><span class="p">()</span></pre></div>
</div>
<p>The output of profiling can be used to give an idea where time is
being spent. A section of profiling output looks like this:</p>
<div class="highlight-python"><pre>13726 function calls (13042 primitive calls) in 0.014 seconds
Ordered by: cumulative time
ncalls tottime percall cumtime percall filename:lineno(function)
222/21 0.001 0.000 0.011 0.001 lib/sqlalchemy/orm/loading.py:26(instances)
220/20 0.002 0.000 0.010 0.001 lib/sqlalchemy/orm/loading.py:327(_instance)
220/20 0.000 0.000 0.010 0.000 lib/sqlalchemy/orm/loading.py:284(populate_state)
20 0.000 0.000 0.010 0.000 lib/sqlalchemy/orm/strategies.py:987(load_collection_from_subq)
20 0.000 0.000 0.009 0.000 lib/sqlalchemy/orm/strategies.py:935(get)
1 0.000 0.000 0.009 0.009 lib/sqlalchemy/orm/strategies.py:940(_load)
21 0.000 0.000 0.008 0.000 lib/sqlalchemy/orm/strategies.py:942(<genexpr>)
2 0.000 0.000 0.004 0.002 lib/sqlalchemy/orm/query.py:2400(__iter__)
2 0.000 0.000 0.002 0.001 lib/sqlalchemy/orm/query.py:2414(_execute_and_instances)
2 0.000 0.000 0.002 0.001 lib/sqlalchemy/engine/base.py:659(execute)
2 0.000 0.000 0.002 0.001 lib/sqlalchemy/sql/elements.py:321(_execute_on_connection)
2 0.000 0.000 0.002 0.001 lib/sqlalchemy/engine/base.py:788(_execute_clauseelement)
...</pre>
</div>
<p>Above, we can see that the <tt class="docutils literal"><span class="pre">instances()</span></tt> SQLAlchemy function was called 222
times (recursively, and 21 times from the outside), taking a total of .011
seconds for all calls combined.</p>
</div>
<div class="section" id="execution-slowness">
<h4>Execution Slowness<a class="headerlink" href="#execution-slowness" title="Permalink to this headline">¶</a></h4>
<p>The specifics of these calls can tell us where the time is being spent.
If for example, you see time being spent within <tt class="docutils literal"><span class="pre">cursor.execute()</span></tt>,
e.g. against the DBAPI:</p>
<div class="highlight-python"><pre>2 0.102 0.102 0.204 0.102 {method 'execute' of 'sqlite3.Cursor' objects}</pre>
</div>
<p>this would indicate that the database is taking a long time to start returning
results, and it means your query should be optimized, either by adding indexes
or restructuring the query and/or underlying schema. For that task,
analysis of the query plan is warranted, using a system such as EXPLAIN,
SHOW PLAN, etc. as is provided by the database backend.</p>
</div>
<div class="section" id="result-fetching-slowness-core">
<h4>Result Fetching Slowness - Core<a class="headerlink" href="#result-fetching-slowness-core" title="Permalink to this headline">¶</a></h4>
<p>If on the other hand you see many thousands of calls related to fetching rows,
or very long calls to <tt class="docutils literal"><span class="pre">fetchall()</span></tt>, it may
mean your query is returning more rows than expected, or that the fetching
of rows itself is slow. The ORM itself typically uses <tt class="docutils literal"><span class="pre">fetchall()</span></tt> to fetch
rows (or <tt class="docutils literal"><span class="pre">fetchmany()</span></tt> if the <a class="reference internal" href="orm/query.html#sqlalchemy.orm.query.Query.yield_per" title="sqlalchemy.orm.query.Query.yield_per"><tt class="xref py py-meth docutils literal"><span class="pre">Query.yield_per()</span></tt></a> option is used).</p>
<p>An inordinately large number of rows would be indicated
by a very slow call to <tt class="docutils literal"><span class="pre">fetchall()</span></tt> at the DBAPI level:</p>
<div class="highlight-python"><pre>2 0.300 0.600 0.300 0.600 {method 'fetchall' of 'sqlite3.Cursor' objects}</pre>
</div>
<p>An unexpectedly large number of rows, even if the ultimate result doesn’t seem
to have many rows, can be the result of a cartesian product - when multiple
sets of rows are combined together without appropriately joining the tables
together. It’s often easy to produce this behavior with SQLAlchemy Core or
ORM query if the wrong <a class="reference internal" href="core/metadata.html#sqlalchemy.schema.Column" title="sqlalchemy.schema.Column"><tt class="xref py py-class docutils literal"><span class="pre">Column</span></tt></a> objects are used in a complex query,
pulling in additional FROM clauses that are unexpected.</p>
<p>On the other hand, a fast call to <tt class="docutils literal"><span class="pre">fetchall()</span></tt> at the DBAPI level, but then
slowness when SQLAlchemy’s <a class="reference internal" href="core/connections.html#sqlalchemy.engine.ResultProxy" title="sqlalchemy.engine.ResultProxy"><tt class="xref py py-class docutils literal"><span class="pre">ResultProxy</span></tt></a> is asked to do a <tt class="docutils literal"><span class="pre">fetchall()</span></tt>,
may indicate slowness in processing of datatypes, such as unicode conversions
and similar:</p>
<div class="highlight-python"><pre># the DBAPI cursor is fast...
2 0.020 0.040 0.020 0.040 {method 'fetchall' of 'sqlite3.Cursor' objects}
...
# but SQLAlchemy's result proxy is slow, this is type-level processing
2 0.100 0.200 0.100 0.200 lib/sqlalchemy/engine/result.py:778(fetchall)</pre>
</div>
<p>In some cases, a backend might be doing type-level processing that isn’t
needed. More specifically, seeing calls within the type API that are slow
are better indicators - below is what it looks like when we use a type like
this:</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">TypeDecorator</span>
<span class="kn">import</span> <span class="nn">time</span>
<span class="k">class</span> <span class="nc">Foo</span><span class="p">(</span><span class="n">TypeDecorator</span><span class="p">):</span>
<span class="n">impl</span> <span class="o">=</span> <span class="n">String</span>
<span class="k">def</span> <span class="nf">process_result_value</span><span class="p">(</span><span class="bp">self</span><span class="p">,</span> <span class="n">value</span><span class="p">,</span> <span class="n">thing</span><span class="p">):</span>
<span class="c"># intentionally add slowness for illustration purposes</span>
<span class="n">time</span><span class="o">.</span><span class="n">sleep</span><span class="p">(</span><span class="o">.</span><span class="mo">001</span><span class="p">)</span>
<span class="k">return</span> <span class="n">value</span></pre></div>
</div>
<p>the profiling output of this intentionally slow operation can be seen like this:</p>
<div class="highlight-python"><pre>200 0.001 0.000 0.237 0.001 lib/sqlalchemy/sql/type_api.py:911(process)
200 0.001 0.000 0.236 0.001 test.py:28(process_result_value)
200 0.235 0.001 0.235 0.001 {time.sleep}</pre>
</div>
<p>that is, we see many expensive calls within the <tt class="docutils literal"><span class="pre">type_api</span></tt> system, and the actual
time consuming thing is the <tt class="docutils literal"><span class="pre">time.sleep()</span></tt> call.</p>
<p>Make sure to check the <a class="reference internal" href="dialects/index.html"><em>Dialect documentation</em></a>
for notes on known performance tuning suggestions at this level, especially for
databases like Oracle. There may be systems related to ensuring numeric accuracy
or string processing that may not be needed in all cases.</p>
<p>There also may be even more low-level points at which row-fetching performance is suffering;
for example, if time spent seems to focus on a call like <tt class="docutils literal"><span class="pre">socket.receive()</span></tt>,
that could indicate that everything is fast except for the actual network connection,
and too much time is spent with data moving over the network.</p>
</div>
<div class="section" id="result-fetching-slowness-orm">
<h4>Result Fetching Slowness - ORM<a class="headerlink" href="#result-fetching-slowness-orm" title="Permalink to this headline">¶</a></h4>
<p>To detect slowness in ORM fetching of rows (which is the most common area
of performance concern), calls like <tt class="docutils literal"><span class="pre">populate_state()</span></tt> and <tt class="docutils literal"><span class="pre">_instance()</span></tt> will
illustrate individual ORM object populations:</p>
<div class="highlight-python"><pre># the ORM calls _instance for each ORM-loaded row it sees, and
# populate_state for each ORM-loaded row that results in the population
# of an object's attributes
220/20 0.001 0.000 0.010 0.000 lib/sqlalchemy/orm/loading.py:327(_instance)
220/20 0.000 0.000 0.009 0.000 lib/sqlalchemy/orm/loading.py:284(populate_state)</pre>
</div>
<p>The ORM’s slowness in turning rows into ORM-mapped objects is a product
of the complexity of this operation combined with the overhead of cPython.
Common strategies to mitigate this include:</p>
<ul>
<li><p class="first">fetch individual columns instead of full entities, that is:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">session</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="n">User</span><span class="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></pre></div>
</div>
<p>instead of:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">session</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="n">User</span><span class="p">)</span></pre></div>
</div>
</li>
<li><p class="first">Use <a class="reference internal" href="orm/query.html#sqlalchemy.orm.query.Bundle" title="sqlalchemy.orm.query.Bundle"><tt class="xref py py-class docutils literal"><span class="pre">Bundle</span></tt></a> objects to organize column-based results:</p>
<div class="highlight-python"><pre>u_b = Bundle('user', User.id, User.name)
a_b = Bundle('address', Address.id, Address.email)
for user, address in session.query(u_b, a_b).join(User.addresses):
# ...</pre>
</div>
</li>
<li><p class="first">Use result caching - see <a class="reference internal" href="orm/examples.html#examples-caching"><em>Dogpile Caching</em></a> for an in-depth example
of this.</p>
</li>
<li><p class="first">Consider a faster interpreter like that of Pypy.</p>
</li>
</ul>
<p>The output of a profile can be a little daunting but after some
practice they are very easy to read.</p>
<p>If you’re feeling ambitious, there’s also a more involved example of
SQLAlchemy profiling within the SQLAlchemy unit tests in the
<tt class="docutils literal"><span class="pre">tests/aaa_profiling</span></tt> section. Tests in this area
use decorators that assert a
maximum number of method calls being used for particular operations,
so that if something inefficient gets checked in, the tests will
reveal it (it is important to note that in cPython, function calls have
the highest overhead of any operation, and the count of calls is more
often than not nearly proportional to time spent). Of note are the
the “zoomark” tests which use a fancy “SQL capturing” scheme which
cuts out the overhead of the DBAPI from the equation - although that
technique isn’t really necessary for garden-variety profiling.</p>
</div>
</div>
<div class="section" id="i-m-inserting-400-000-rows-with-the-orm-and-it-s-really-slow">
<h3>I’m inserting 400,000 rows with the ORM and it’s really slow!<a class="headerlink" href="#i-m-inserting-400-000-rows-with-the-orm-and-it-s-really-slow" title="Permalink to this headline">¶</a></h3>
<p>The SQLAlchemy ORM uses the <a class="reference internal" href="glossary.html#term-unit-of-work"><em class="xref std std-term">unit of work</em></a> pattern when synchronizing
changes to the database. This pattern goes far beyond simple “inserts”
of data. It includes that attributes which are assigned on objects are
received using an attribute instrumentation system which tracks
changes on objects as they are made, includes that all rows inserted
are tracked in an identity map which has the effect that for each row
SQLAlchemy must retrieve its “last inserted id” if not already given,
and also involves that rows to be inserted are scanned and sorted for
dependencies as needed. Objects are also subject to a fair degree of
bookkeeping in order to keep all of this running, which for a very
large number of rows at once can create an inordinate amount of time
spent with large data structures, hence it’s best to chunk these.</p>
<p>Basically, unit of work is a large degree of automation in order to
automate the task of persisting a complex object graph into a
relational database with no explicit persistence code, and this
automation has a price.</p>
<p>ORMs are basically not intended for high-performance bulk inserts -
this is the whole reason SQLAlchemy offers the Core in addition to the
ORM as a first-class component.</p>
<p>For the use case of fast bulk inserts, the
SQL generation and execution system that the ORM builds on top of
is part of the Core. Using this system directly, we can produce an INSERT that
is competitive with using the raw database API directly.</p>
<p>The example below illustrates time-based tests for four different
methods of inserting rows, going from the most automated to the least.
With cPython 2.7, runtimes observed:</p>
<div class="highlight-python"><pre>classics-MacBook-Pro:sqlalchemy classic$ python test.py
SQLAlchemy ORM: Total time for 100000 records 14.3528850079 secs
SQLAlchemy ORM pk given: Total time for 100000 records 10.0164160728 secs
SQLAlchemy Core: Total time for 100000 records 0.775382995605 secs
sqlite3: Total time for 100000 records 0.676795005798 sec</pre>
</div>
<p>We can reduce the time by a factor of three using recent versions of <a class="reference external" href="http://pypy.org/">Pypy</a>:</p>
<div class="highlight-python"><pre>classics-MacBook-Pro:sqlalchemy classic$ /usr/local/src/pypy-2.1-beta2-osx64/bin/pypy test.py
SQLAlchemy ORM: Total time for 100000 records 5.88369488716 secs
SQLAlchemy ORM pk given: Total time for 100000 records 3.52294301987 secs
SQLAlchemy Core: Total time for 100000 records 0.613556146622 secs
sqlite3: Total time for 100000 records 0.442467927933 sec</pre>
</div>
<p>Script:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="kn">import</span> <span class="nn">time</span>
<span class="kn">import</span> <span class="nn">sqlite3</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="kn">from</span> <span class="nn">sqlalchemy</span> <span class="kn">import</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">create_engine</span>
<span class="kn">from</span> <span class="nn">sqlalchemy.orm</span> <span class="kn">import</span> <span class="n">scoped_session</span><span class="p">,</span> <span class="n">sessionmaker</span>
<span class="n">Base</span> <span class="o">=</span> <span class="n">declarative_base</span><span class="p">()</span>
<span class="n">DBSession</span> <span class="o">=</span> <span class="n">scoped_session</span><span class="p">(</span><span class="n">sessionmaker</span><span class="p">())</span>
<span class="n">engine</span> <span class="o">=</span> <span class="bp">None</span>
<span class="k">class</span> <span class="nc">Customer</span><span class="p">(</span><span class="n">Base</span><span class="p">):</span>
<span class="n">__tablename__</span> <span class="o">=</span> <span class="s">"customer"</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="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="mi">255</span><span class="p">))</span>
<span class="k">def</span> <span class="nf">init_sqlalchemy</span><span class="p">(</span><span class="n">dbname</span><span class="o">=</span><span class="s">'sqlite:///sqlalchemy.db'</span><span class="p">):</span>
<span class="k">global</span> <span class="n">engine</span>
<span class="n">engine</span> <span class="o">=</span> <span class="n">create_engine</span><span class="p">(</span><span class="n">dbname</span><span class="p">,</span> <span class="n">echo</span><span class="o">=</span><span class="bp">False</span><span class="p">)</span>
<span class="n">DBSession</span><span class="o">.</span><span class="n">remove</span><span class="p">()</span>
<span class="n">DBSession</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="n">autoflush</span><span class="o">=</span><span class="bp">False</span><span class="p">,</span> <span class="n">expire_on_commit</span><span class="o">=</span><span class="bp">False</span><span class="p">)</span>
<span class="n">Base</span><span class="o">.</span><span class="n">metadata</span><span class="o">.</span><span class="n">drop_all</span><span class="p">(</span><span class="n">engine</span><span class="p">)</span>
<span class="n">Base</span><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>
<span class="k">def</span> <span class="nf">test_sqlalchemy_orm</span><span class="p">(</span><span class="n">n</span><span class="o">=</span><span class="mi">100000</span><span class="p">):</span>
<span class="n">init_sqlalchemy</span><span class="p">()</span>
<span class="n">t0</span> <span class="o">=</span> <span class="n">time</span><span class="o">.</span><span class="n">time</span><span class="p">()</span>
<span class="k">for</span> <span class="n">i</span> <span class="ow">in</span> <span class="nb">range</span><span class="p">(</span><span class="n">n</span><span class="p">):</span>
<span class="n">customer</span> <span class="o">=</span> <span class="n">Customer</span><span class="p">()</span>
<span class="n">customer</span><span class="o">.</span><span class="n">name</span> <span class="o">=</span> <span class="s">'NAME '</span> <span class="o">+</span> <span class="nb">str</span><span class="p">(</span><span class="n">i</span><span class="p">)</span>
<span class="n">DBSession</span><span class="o">.</span><span class="n">add</span><span class="p">(</span><span class="n">customer</span><span class="p">)</span>
<span class="k">if</span> <span class="n">i</span> <span class="o">%</span> <span class="mi">1000</span> <span class="o">==</span> <span class="mi">0</span><span class="p">:</span>
<span class="n">DBSession</span><span class="o">.</span><span class="n">flush</span><span class="p">()</span>
<span class="n">DBSession</span><span class="o">.</span><span class="n">commit</span><span class="p">()</span>
<span class="k">print</span><span class="p">(</span><span class="s">"SQLAlchemy ORM: Total time for "</span> <span class="o">+</span> <span class="nb">str</span><span class="p">(</span><span class="n">n</span><span class="p">)</span> <span class="o">+</span>
<span class="s">" records "</span> <span class="o">+</span> <span class="nb">str</span><span class="p">(</span><span class="n">time</span><span class="o">.</span><span class="n">time</span><span class="p">()</span> <span class="o">-</span> <span class="n">t0</span><span class="p">)</span> <span class="o">+</span> <span class="s">" secs"</span><span class="p">)</span>
<span class="k">def</span> <span class="nf">test_sqlalchemy_orm_pk_given</span><span class="p">(</span><span class="n">n</span><span class="o">=</span><span class="mi">100000</span><span class="p">):</span>
<span class="n">init_sqlalchemy</span><span class="p">()</span>
<span class="n">t0</span> <span class="o">=</span> <span class="n">time</span><span class="o">.</span><span class="n">time</span><span class="p">()</span>
<span class="k">for</span> <span class="n">i</span> <span class="ow">in</span> <span class="nb">range</span><span class="p">(</span><span class="n">n</span><span class="p">):</span>
<span class="n">customer</span> <span class="o">=</span> <span class="n">Customer</span><span class="p">(</span><span class="nb">id</span><span class="o">=</span><span class="n">i</span><span class="o">+</span><span class="mi">1</span><span class="p">,</span> <span class="n">name</span><span class="o">=</span><span class="s">"NAME "</span> <span class="o">+</span> <span class="nb">str</span><span class="p">(</span><span class="n">i</span><span class="p">))</span>
<span class="n">DBSession</span><span class="o">.</span><span class="n">add</span><span class="p">(</span><span class="n">customer</span><span class="p">)</span>
<span class="k">if</span> <span class="n">i</span> <span class="o">%</span> <span class="mi">1000</span> <span class="o">==</span> <span class="mi">0</span><span class="p">:</span>
<span class="n">DBSession</span><span class="o">.</span><span class="n">flush</span><span class="p">()</span>
<span class="n">DBSession</span><span class="o">.</span><span class="n">commit</span><span class="p">()</span>
<span class="k">print</span><span class="p">(</span><span class="s">"SQLAlchemy ORM pk given: Total time for "</span> <span class="o">+</span> <span class="nb">str</span><span class="p">(</span><span class="n">n</span><span class="p">)</span> <span class="o">+</span>
<span class="s">" records "</span> <span class="o">+</span> <span class="nb">str</span><span class="p">(</span><span class="n">time</span><span class="o">.</span><span class="n">time</span><span class="p">()</span> <span class="o">-</span> <span class="n">t0</span><span class="p">)</span> <span class="o">+</span> <span class="s">" secs"</span><span class="p">)</span>
<span class="k">def</span> <span class="nf">test_sqlalchemy_core</span><span class="p">(</span><span class="n">n</span><span class="o">=</span><span class="mi">100000</span><span class="p">):</span>
<span class="n">init_sqlalchemy</span><span class="p">()</span>
<span class="n">t0</span> <span class="o">=</span> <span class="n">time</span><span class="o">.</span><span class="n">time</span><span class="p">()</span>
<span class="n">engine</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span>
<span class="n">Customer</span><span class="o">.</span><span class="n">__table__</span><span class="o">.</span><span class="n">insert</span><span class="p">(),</span>
<span class="p">[{</span><span class="s">"name"</span><span class="p">:</span> <span class="s">'NAME '</span> <span class="o">+</span> <span class="nb">str</span><span class="p">(</span><span class="n">i</span><span class="p">)}</span> <span class="k">for</span> <span class="n">i</span> <span class="ow">in</span> <span class="nb">range</span><span class="p">(</span><span class="n">n</span><span class="p">)]</span>
<span class="p">)</span>
<span class="k">print</span><span class="p">(</span><span class="s">"SQLAlchemy Core: Total time for "</span> <span class="o">+</span> <span class="nb">str</span><span class="p">(</span><span class="n">n</span><span class="p">)</span> <span class="o">+</span>
<span class="s">" records "</span> <span class="o">+</span> <span class="nb">str</span><span class="p">(</span><span class="n">time</span><span class="o">.</span><span class="n">time</span><span class="p">()</span> <span class="o">-</span> <span class="n">t0</span><span class="p">)</span> <span class="o">+</span> <span class="s">" secs"</span><span class="p">)</span>
<span class="k">def</span> <span class="nf">init_sqlite3</span><span class="p">(</span><span class="n">dbname</span><span class="p">):</span>
<span class="n">conn</span> <span class="o">=</span> <span class="n">sqlite3</span><span class="o">.</span><span class="n">connect</span><span class="p">(</span><span class="n">dbname</span><span class="p">)</span>
<span class="n">c</span> <span class="o">=</span> <span class="n">conn</span><span class="o">.</span><span class="n">cursor</span><span class="p">()</span>
<span class="n">c</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="s">"DROP TABLE IF EXISTS customer"</span><span class="p">)</span>
<span class="n">c</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="s">"CREATE TABLE customer (id INTEGER NOT NULL, "</span>
<span class="s">"name VARCHAR(255), PRIMARY KEY(id))"</span><span class="p">)</span>
<span class="n">conn</span><span class="o">.</span><span class="n">commit</span><span class="p">()</span>
<span class="k">return</span> <span class="n">conn</span>
<span class="k">def</span> <span class="nf">test_sqlite3</span><span class="p">(</span><span class="n">n</span><span class="o">=</span><span class="mi">100000</span><span class="p">,</span> <span class="n">dbname</span><span class="o">=</span><span class="s">'sqlite3.db'</span><span class="p">):</span>
<span class="n">conn</span> <span class="o">=</span> <span class="n">init_sqlite3</span><span class="p">(</span><span class="n">dbname</span><span class="p">)</span>
<span class="n">c</span> <span class="o">=</span> <span class="n">conn</span><span class="o">.</span><span class="n">cursor</span><span class="p">()</span>
<span class="n">t0</span> <span class="o">=</span> <span class="n">time</span><span class="o">.</span><span class="n">time</span><span class="p">()</span>
<span class="k">for</span> <span class="n">i</span> <span class="ow">in</span> <span class="nb">range</span><span class="p">(</span><span class="n">n</span><span class="p">):</span>
<span class="n">row</span> <span class="o">=</span> <span class="p">(</span><span class="s">'NAME '</span> <span class="o">+</span> <span class="nb">str</span><span class="p">(</span><span class="n">i</span><span class="p">),)</span>
<span class="n">c</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="s">"INSERT INTO customer (name) VALUES (?)"</span><span class="p">,</span> <span class="n">row</span><span class="p">)</span>
<span class="n">conn</span><span class="o">.</span><span class="n">commit</span><span class="p">()</span>
<span class="k">print</span><span class="p">(</span><span class="s">"sqlite3: Total time for "</span> <span class="o">+</span> <span class="nb">str</span><span class="p">(</span><span class="n">n</span><span class="p">)</span> <span class="o">+</span>
<span class="s">" records "</span> <span class="o">+</span> <span class="nb">str</span><span class="p">(</span><span class="n">time</span><span class="o">.</span><span class="n">time</span><span class="p">()</span> <span class="o">-</span> <span class="n">t0</span><span class="p">)</span> <span class="o">+</span> <span class="s">" sec"</span><span class="p">)</span>
<span class="k">if</span> <span class="n">__name__</span> <span class="o">==</span> <span class="s">'__main__'</span><span class="p">:</span>
<span class="n">test_sqlalchemy_orm</span><span class="p">(</span><span class="mi">100000</span><span class="p">)</span>
<span class="n">test_sqlalchemy_orm_pk_given</span><span class="p">(</span><span class="mi">100000</span><span class="p">)</span>
<span class="n">test_sqlalchemy_core</span><span class="p">(</span><span class="mi">100000</span><span class="p">)</span>
<span class="n">test_sqlite3</span><span class="p">(</span><span class="mi">100000</span><span class="p">)</span></pre></div>
</div>
</div>
</div>
<div class="section" id="sessions-queries">
<h2>Sessions / Queries<a class="headerlink" href="#sessions-queries" title="Permalink to this headline">¶</a></h2>
<div class="section" id="this-session-s-transaction-has-been-rolled-back-due-to-a-previous-exception-during-flush-or-similar">
<h3>“This Session’s transaction has been rolled back due to a previous exception during flush.” (or similar)<a class="headerlink" href="#this-session-s-transaction-has-been-rolled-back-due-to-a-previous-exception-during-flush-or-similar" title="Permalink to this headline">¶</a></h3>
<p>This is an error that occurs when a <a class="reference internal" href="orm/session.html#sqlalchemy.orm.session.Session.flush" title="sqlalchemy.orm.session.Session.flush"><tt class="xref py py-meth docutils literal"><span class="pre">Session.flush()</span></tt></a> raises an exception, rolls back
the transaction, but further commands upon the <cite>Session</cite> are called without an
explicit call to <a class="reference internal" href="orm/session.html#sqlalchemy.orm.session.Session.rollback" title="sqlalchemy.orm.session.Session.rollback"><tt class="xref py py-meth docutils literal"><span class="pre">Session.rollback()</span></tt></a> or <a class="reference internal" href="orm/session.html#sqlalchemy.orm.session.Session.close" title="sqlalchemy.orm.session.Session.close"><tt class="xref py py-meth docutils literal"><span class="pre">Session.close()</span></tt></a>.</p>
<p>It usually corresponds to an application that catches an exception
upon <a class="reference internal" href="orm/session.html#sqlalchemy.orm.session.Session.flush" title="sqlalchemy.orm.session.Session.flush"><tt class="xref py py-meth docutils literal"><span class="pre">Session.flush()</span></tt></a> or <a class="reference internal" href="orm/session.html#sqlalchemy.orm.session.Session.commit" title="sqlalchemy.orm.session.Session.commit"><tt class="xref py py-meth docutils literal"><span class="pre">Session.commit()</span></tt></a> and
does not properly handle the exception. For example:</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">create_engine</span><span class="p">,</span> <span class="n">Column</span><span class="p">,</span> <span class="n">Integer</span>
<span class="kn">from</span> <span class="nn">sqlalchemy.orm</span> <span class="kn">import</span> <span class="n">sessionmaker</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="n">Base</span> <span class="o">=</span> <span class="n">declarative_base</span><span class="p">(</span><span class="n">create_engine</span><span class="p">(</span><span class="s">'sqlite://'</span><span class="p">))</span>
<span class="k">class</span> <span class="nc">Foo</span><span class="p">(</span><span class="n">Base</span><span class="p">):</span>
<span class="n">__tablename__</span> <span class="o">=</span> <span class="s">'foo'</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="n">Base</span><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">session</span> <span class="o">=</span> <span class="n">sessionmaker</span><span class="p">()()</span>
<span class="c"># constraint violation</span>
<span class="n">session</span><span class="o">.</span><span class="n">add_all</span><span class="p">([</span><span class="n">Foo</span><span class="p">(</span><span class="nb">id</span><span class="o">=</span><span class="mi">1</span><span class="p">),</span> <span class="n">Foo</span><span class="p">(</span><span class="nb">id</span><span class="o">=</span><span class="mi">1</span><span class="p">)])</span>
<span class="k">try</span><span class="p">:</span>
<span class="n">session</span><span class="o">.</span><span class="n">commit</span><span class="p">()</span>
<span class="k">except</span><span class="p">:</span>
<span class="c"># ignore error</span>
<span class="k">pass</span>
<span class="c"># continue using session without rolling back</span>
<span class="n">session</span><span class="o">.</span><span class="n">commit</span><span class="p">()</span></pre></div>
</div>
<p>The usage of the <a class="reference internal" href="orm/session.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> should fit within a structure similar to this:</p>
<div class="highlight-python"><pre>try:
<use session>
session.commit()
except:
session.rollback()
raise
finally:
session.close() # optional, depends on use case</pre>
</div>
<p>Many things can cause a failure within the try/except besides flushes. You
should always have some kind of “framing” of your session operations so that
connection and transaction resources have a definitive boundary, otherwise
your application doesn’t really have its usage of resources under control.
This is not to say that you need to put try/except blocks all throughout your
application - on the contrary, this would be a terrible idea. You should
architect your application such that there is one (or few) point(s) of
“framing” around session operations.</p>
<p>For a detailed discussion on how to organize usage of the <a class="reference internal" href="orm/session.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>,
please see <a class="reference internal" href="orm/session.html#session-faq-whentocreate"><em>When do I construct a Session, when do I commit it, and when do I close it?</em></a>.</p>
<div class="section" id="but-why-does-flush-insist-on-issuing-a-rollback">
<h4>But why does flush() insist on issuing a ROLLBACK?<a class="headerlink" href="#but-why-does-flush-insist-on-issuing-a-rollback" title="Permalink to this headline">¶</a></h4>
<p>It would be great if <a class="reference internal" href="orm/session.html#sqlalchemy.orm.session.Session.flush" title="sqlalchemy.orm.session.Session.flush"><tt class="xref py py-meth docutils literal"><span class="pre">Session.flush()</span></tt></a> could partially complete and then not roll
back, however this is beyond its current capabilities since its internal
bookkeeping would have to be modified such that it can be halted at any time
and be exactly consistent with what’s been flushed to the database. While this
is theoretically possible, the usefulness of the enhancement is greatly
decreased by the fact that many database operations require a ROLLBACK in any
case. Postgres in particular has operations which, once failed, the
transaction is not allowed to continue:</p>
<div class="highlight-python"><pre>test=> create table foo(id integer primary key);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "foo_pkey" for table "foo"
CREATE TABLE
test=> begin;
BEGIN
test=> insert into foo values(1);
INSERT 0 1
test=> commit;
COMMIT
test=> begin;
BEGIN
test=> insert into foo values(1);
ERROR: duplicate key value violates unique constraint "foo_pkey"
test=> insert into foo values(2);
ERROR: current transaction is aborted, commands ignored until end of transaction block</pre>
</div>
<p>What SQLAlchemy offers that solves both issues is support of SAVEPOINT, via
<a class="reference internal" href="orm/session.html#sqlalchemy.orm.session.Session.begin_nested" title="sqlalchemy.orm.session.Session.begin_nested"><tt class="xref py py-meth docutils literal"><span class="pre">Session.begin_nested()</span></tt></a>. Using <a class="reference internal" href="orm/session.html#sqlalchemy.orm.session.Session.begin_nested" title="sqlalchemy.orm.session.Session.begin_nested"><tt class="xref py py-meth docutils literal"><span class="pre">Session.begin_nested()</span></tt></a>, you can frame an operation that may
potentially fail within a transaction, and then “roll back” to the point
before its failure while maintaining the enclosing transaction.</p>
</div>
<div class="section" id="but-why-isn-t-the-one-automatic-call-to-rollback-enough-why-must-i-rollback-again">
<h4>But why isn’t the one automatic call to ROLLBACK enough? Why must I ROLLBACK again?<a class="headerlink" href="#but-why-isn-t-the-one-automatic-call-to-rollback-enough-why-must-i-rollback-again" title="Permalink to this headline">¶</a></h4>
<p>This is again a matter of the <a class="reference internal" href="orm/session.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> providing a consistent interface and
refusing to guess about what context its being used. For example, the
<a class="reference internal" href="orm/session.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> supports “framing” above within multiple levels. Such as, suppose
you had a decorator <tt class="docutils literal"><span class="pre">@with_session()</span></tt>, which did this:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="k">def</span> <span class="nf">with_session</span><span class="p">(</span><span class="n">fn</span><span class="p">):</span>
<span class="k">def</span> <span class="nf">go</span><span class="p">(</span><span class="o">*</span><span class="n">args</span><span class="p">,</span> <span class="o">**</span><span class="n">kw</span><span class="p">):</span>
<span class="n">session</span><span class="o">.</span><span class="n">begin</span><span class="p">(</span><span class="n">subtransactions</span><span class="o">=</span><span class="bp">True</span><span class="p">)</span>
<span class="k">try</span><span class="p">:</span>
<span class="n">ret</span> <span class="o">=</span> <span class="n">fn</span><span class="p">(</span><span class="o">*</span><span class="n">args</span><span class="p">,</span> <span class="o">**</span><span class="n">kw</span><span class="p">)</span>
<span class="n">session</span><span class="o">.</span><span class="n">commit</span><span class="p">()</span>
<span class="k">return</span> <span class="n">ret</span>
<span class="k">except</span><span class="p">:</span>
<span class="n">session</span><span class="o">.</span><span class="n">rollback</span><span class="p">()</span>
<span class="k">raise</span>
<span class="k">return</span> <span class="n">go</span></pre></div>
</div>
<p>The above decorator begins a transaction if one does not exist already, and
then commits it, if it were the creator. The “subtransactions” flag means that
if <a class="reference internal" href="orm/session.html#sqlalchemy.orm.session.Session.begin" title="sqlalchemy.orm.session.Session.begin"><tt class="xref py py-meth docutils literal"><span class="pre">Session.begin()</span></tt></a> were already called by an enclosing function, nothing happens
except a counter is incremented - this counter is decremented when <a class="reference internal" href="orm/session.html#sqlalchemy.orm.session.Session.commit" title="sqlalchemy.orm.session.Session.commit"><tt class="xref py py-meth docutils literal"><span class="pre">Session.commit()</span></tt></a>
is called and only when it goes back to zero does the actual COMMIT happen. It
allows this usage pattern:</p>
<div class="highlight-python"><pre>@with_session
def one():
# do stuff
two()
@with_session
def two():
# etc.
one()
two()</pre>
</div>
<p><tt class="docutils literal"><span class="pre">one()</span></tt> can call <tt class="docutils literal"><span class="pre">two()</span></tt>, or <tt class="docutils literal"><span class="pre">two()</span></tt> can be called by itself, and the
<tt class="docutils literal"><span class="pre">@with_session</span></tt> decorator ensures the appropriate “framing” - the transaction
boundaries stay on the outermost call level. As you can see, if <tt class="docutils literal"><span class="pre">two()</span></tt> calls
<tt class="docutils literal"><span class="pre">flush()</span></tt> which throws an exception and then issues a <tt class="docutils literal"><span class="pre">rollback()</span></tt>, there will
<em>always</em> be a second <tt class="docutils literal"><span class="pre">rollback()</span></tt> performed by the decorator, and possibly a
third corresponding to two levels of decorator. If the <tt class="docutils literal"><span class="pre">flush()</span></tt> pushed the
<tt class="docutils literal"><span class="pre">rollback()</span></tt> all the way out to the top of the stack, and then we said that
all remaining <tt class="docutils literal"><span class="pre">rollback()</span></tt> calls are moot, there is some silent behavior going
on there. A poorly written enclosing method might suppress the exception, and
then call <tt class="docutils literal"><span class="pre">commit()</span></tt> assuming nothing is wrong, and then you have a silent
failure condition. The main reason people get this error in fact is because
they didn’t write clean “framing” code and they would have had other problems
down the road.</p>
<p>If you think the above use case is a little exotic, the same kind of thing
comes into play if you want to SAVEPOINT- you might call <tt class="docutils literal"><span class="pre">begin_nested()</span></tt>
several times, and the <tt class="docutils literal"><span class="pre">commit()</span></tt>/<tt class="docutils literal"><span class="pre">rollback()</span></tt> calls each resolve the most
recent <tt class="docutils literal"><span class="pre">begin_nested()</span></tt>. The meaning of <tt class="docutils literal"><span class="pre">rollback()</span></tt> or <tt class="docutils literal"><span class="pre">commit()</span></tt> is
dependent upon which enclosing block it is called, and you might have any
sequence of <tt class="docutils literal"><span class="pre">rollback()</span></tt>/<tt class="docutils literal"><span class="pre">commit()</span></tt> in any order, and its the level of nesting
that determines their behavior.</p>
<p>In both of the above cases, if <tt class="docutils literal"><span class="pre">flush()</span></tt> broke the nesting of transaction
blocks, the behavior is, depending on scenario, anywhere from “magic” to
silent failure to blatant interruption of code flow.</p>
<p><tt class="docutils literal"><span class="pre">flush()</span></tt> makes its own “subtransaction”, so that a transaction is started up
regardless of the external transactional state, and when complete it calls
<tt class="docutils literal"><span class="pre">commit()</span></tt>, or <tt class="docutils literal"><span class="pre">rollback()</span></tt> upon failure - but that <tt class="docutils literal"><span class="pre">rollback()</span></tt> corresponds
to its own subtransaction - it doesn’t want to guess how you’d like to handle
the external “framing” of the transaction, which could be nested many levels
with any combination of subtransactions and real SAVEPOINTs. The job of
starting/ending the “frame” is kept consistently with the code external to the
<tt class="docutils literal"><span class="pre">flush()</span></tt>, and we made a decision that this was the most consistent approach.</p>
</div>
</div>
<div class="section" id="how-do-i-make-a-query-that-always-adds-a-certain-filter-to-every-query">
<h3>How do I make a Query that always adds a certain filter to every query?<a class="headerlink" href="#how-do-i-make-a-query-that-always-adds-a-certain-filter-to-every-query" title="Permalink to this headline">¶</a></h3>
<p>See the recipe at <a class="reference external" href="http://www.sqlalchemy.org/trac/wiki/UsageRecipes/PreFilteredQuery">PreFilteredQuery</a>.</p>
</div>
<div class="section" id="i-ve-created-a-mapping-against-an-outer-join-and-while-the-query-returns-rows-no-objects-are-returned-why-not">
<h3>I’ve created a mapping against an Outer Join, and while the query returns rows, no objects are returned. Why not?<a class="headerlink" href="#i-ve-created-a-mapping-against-an-outer-join-and-while-the-query-returns-rows-no-objects-are-returned-why-not" title="Permalink to this headline">¶</a></h3>
<p>Rows returned by an outer join may contain NULL for part of the primary key,
as the primary key is the composite of both tables. The <a class="reference internal" href="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 ignores incoming rows
that don’t have an acceptable primary key. Based on the setting of the <tt class="docutils literal"><span class="pre">allow_partial_pks</span></tt>
flag on <a class="reference internal" href="orm/mapper_config.html#sqlalchemy.orm.mapper" title="sqlalchemy.orm.mapper"><tt class="xref py py-func docutils literal"><span class="pre">mapper()</span></tt></a>, a primary key is accepted if the value has at least one non-NULL
value, or alternatively if the value has no NULL values. See <tt class="docutils literal"><span class="pre">allow_partial_pks</span></tt>
at <a class="reference internal" href="orm/mapper_config.html#sqlalchemy.orm.mapper" title="sqlalchemy.orm.mapper"><tt class="xref py py-func docutils literal"><span class="pre">mapper()</span></tt></a>.</p>
</div>
<div class="section" id="i-m-using-joinedload-or-lazy-false-to-create-a-join-outer-join-and-sqlalchemy-is-not-constructing-the-correct-query-when-i-try-to-add-a-where-order-by-limit-etc-which-relies-upon-the-outer-join">
<h3>I’m using <tt class="docutils literal"><span class="pre">joinedload()</span></tt> or <tt class="docutils literal"><span class="pre">lazy=False</span></tt> to create a JOIN/OUTER JOIN and SQLAlchemy is not constructing the correct query when I try to add a WHERE, ORDER BY, LIMIT, etc. (which relies upon the (OUTER) JOIN)<a class="headerlink" href="#i-m-using-joinedload-or-lazy-false-to-create-a-join-outer-join-and-sqlalchemy-is-not-constructing-the-correct-query-when-i-try-to-add-a-where-order-by-limit-etc-which-relies-upon-the-outer-join" title="Permalink to this headline">¶</a></h3>
<p>The joins generated by joined eager loading are only used to fully load related
collections, and are designed to have no impact on the primary results of the query.
Since they are anonymously aliased, they cannot be referenced directly.</p>
<p>For detail on this beahvior, see <a class="reference internal" href="orm/loading.html"><em>Relationship Loading Techniques</em></a>.</p>
</div>
<div class="section" id="query-has-no-len-why-not">
<h3>Query has no <tt class="docutils literal"><span class="pre">__len__()</span></tt>, why not?<a class="headerlink" href="#query-has-no-len-why-not" title="Permalink to this headline">¶</a></h3>
<p>The Python <tt class="docutils literal"><span class="pre">__len__()</span></tt> magic method applied to an object allows the <tt class="docutils literal"><span class="pre">len()</span></tt>
builtin to be used to determine the length of the collection. It’s intuitive
that a SQL query object would link <tt class="docutils literal"><span class="pre">__len__()</span></tt> to the <a class="reference internal" href="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">Query.count()</span></tt></a>
method, which emits a <cite>SELECT COUNT</cite>. The reason this is not possible is
because evaluating the query as a list would incur two SQL calls instead of
one:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="k">class</span> <span class="nc">Iterates</span><span class="p">(</span><span class="nb">object</span><span class="p">):</span>
<span class="k">def</span> <span class="nf">__len__</span><span class="p">(</span><span class="bp">self</span><span class="p">):</span>
<span class="k">print</span> <span class="s">"LEN!"</span>
<span class="k">return</span> <span class="mi">5</span>
<span class="k">def</span> <span class="nf">__iter__</span><span class="p">(</span><span class="bp">self</span><span class="p">):</span>
<span class="k">print</span> <span class="s">"ITER!"</span>
<span class="k">return</span> <span class="nb">iter</span><span class="p">([</span><span class="mi">1</span><span class="p">,</span> <span class="mi">2</span><span class="p">,</span> <span class="mi">3</span><span class="p">,</span> <span class="mi">4</span><span class="p">,</span> <span class="mi">5</span><span class="p">])</span>
<span class="nb">list</span><span class="p">(</span><span class="n">Iterates</span><span class="p">())</span></pre></div>
</div>
<p>output:</p>
<div class="highlight-python"><pre>ITER!
LEN!</pre>
</div>
</div>
<div class="section" id="how-do-i-use-textual-sql-with-orm-queries">
<h3>How Do I use Textual SQL with ORM Queries?<a class="headerlink" href="#how-do-i-use-textual-sql-with-orm-queries" title="Permalink to this headline">¶</a></h3>
<p>See:</p>
<ul class="simple">
<li><a class="reference internal" href="orm/tutorial.html#orm-tutorial-literal-sql"><em>Using Literal SQL</em></a> - Ad-hoc textual blocks with <a class="reference internal" href="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></li>
<li><a class="reference internal" href="orm/session.html#session-sql-expressions"><em>Using SQL Expressions with Sessions</em></a> - Using <a class="reference internal" href="orm/session.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> with textual SQL directly.</li>
</ul>
</div>
<div class="section" id="i-m-calling-session-delete-myobject-and-it-isn-t-removed-from-the-parent-collection">
<h3>I’m calling <tt class="docutils literal"><span class="pre">Session.delete(myobject)</span></tt> and it isn’t removed from the parent collection!<a class="headerlink" href="#i-m-calling-session-delete-myobject-and-it-isn-t-removed-from-the-parent-collection" title="Permalink to this headline">¶</a></h3>
<p>See <a class="reference internal" href="orm/session.html#session-deleting-from-collections"><em>Deleting from Collections</em></a> for a description of this behavior.</p>
</div>
<div class="section" id="why-isn-t-my-init-called-when-i-load-objects">
<h3>why isn’t my <tt class="docutils literal"><span class="pre">__init__()</span></tt> called when I load objects?<a class="headerlink" href="#why-isn-t-my-init-called-when-i-load-objects" title="Permalink to this headline">¶</a></h3>
<p>See <a class="reference internal" href="orm/mapper_config.html#mapping-constructors"><em>Constructors and Object Initialization</em></a> for a description of this behavior.</p>
</div>
<div class="section" id="how-do-i-use-on-delete-cascade-with-sa-s-orm">
<h3>how do I use ON DELETE CASCADE with SA’s ORM?<a class="headerlink" href="#how-do-i-use-on-delete-cascade-with-sa-s-orm" title="Permalink to this headline">¶</a></h3>
<p>SQLAlchemy will always issue UPDATE or DELETE statements for dependent
rows which are currently loaded in the <a class="reference internal" href="orm/session.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>. For rows which
are not loaded, it will by default issue SELECT statements to load
those rows and udpate/delete those as well; in other words it assumes
there is no ON DELETE CASCADE configured.
To configure SQLAlchemy to cooperate with ON DELETE CASCADE, see
<a class="reference internal" href="orm/collections.html#passive-deletes"><em>Using Passive Deletes</em></a>.</p>
</div>
<div class="section" id="i-set-the-foo-id-attribute-on-my-instance-to-7-but-the-foo-attribute-is-still-none-shouldn-t-it-have-loaded-foo-with-id-7">
<h3>I set the “foo_id” attribute on my instance to “7”, but the “foo” attribute is still <tt class="docutils literal"><span class="pre">None</span></tt> - shouldn’t it have loaded Foo with id #7?<a class="headerlink" href="#i-set-the-foo-id-attribute-on-my-instance-to-7-but-the-foo-attribute-is-still-none-shouldn-t-it-have-loaded-foo-with-id-7" title="Permalink to this headline">¶</a></h3>
<p>The ORM is not constructed in such a way as to support
immediate population of relationships driven from foreign
key attribute changes - instead, it is designed to work the
other way around - foreign key attributes are handled by the
ORM behind the scenes, the end user sets up object
relationships naturally. Therefore, the recommended way to
set <tt class="docutils literal"><span class="pre">o.foo</span></tt> is to do just that - set it!:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">foo</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">Foo</span><span class="p">)</span><span class="o">.</span><span class="n">get</span><span class="p">(</span><span class="mi">7</span><span class="p">)</span>
<span class="n">o</span><span class="o">.</span><span class="n">foo</span> <span class="o">=</span> <span class="n">foo</span>
<span class="n">Session</span><span class="o">.</span><span class="n">commit</span><span class="p">()</span></pre></div>
</div>
<p>Manipulation of foreign key attributes is of course entirely legal. However,
setting a foreign-key attribute to a new value currently does not trigger
an “expire” event of the <a class="reference internal" href="orm/relationships.html#sqlalchemy.orm.relationship" title="sqlalchemy.orm.relationship"><tt class="xref py py-func docutils literal"><span class="pre">relationship()</span></tt></a> in which it’s involved. This means
that for the following sequence:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">o</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">SomeClass</span><span class="p">)</span><span class="o">.</span><span class="n">first</span><span class="p">()</span>
<span class="k">assert</span> <span class="n">o</span><span class="o">.</span><span class="n">foo</span> <span class="ow">is</span> <span class="bp">None</span> <span class="c"># accessing an un-set attribute sets it to None</span>
<span class="n">o</span><span class="o">.</span><span class="n">foo_id</span> <span class="o">=</span> <span class="mi">7</span></pre></div>
</div>
<p><tt class="docutils literal"><span class="pre">o.foo</span></tt> is initialized to <tt class="docutils literal"><span class="pre">None</span></tt> when we first accessed it. Setting
<tt class="docutils literal"><span class="pre">o.foo_id</span> <span class="pre">=</span> <span class="pre">7</span></tt> will have the value of “7” as pending, but no flush
has occurred - so <tt class="docutils literal"><span class="pre">o.foo</span></tt> is still <tt class="docutils literal"><span class="pre">None</span></tt>:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="c"># attribute is already set to None, has not been</span>
<span class="c"># reconciled with o.foo_id = 7 yet</span>
<span class="k">assert</span> <span class="n">o</span><span class="o">.</span><span class="n">foo</span> <span class="ow">is</span> <span class="bp">None</span></pre></div>
</div>
<p>For <tt class="docutils literal"><span class="pre">o.foo</span></tt> to load based on the foreign key mutation is usually achieved
naturally after the commit, which both flushes the new foreign key value
and expires all state:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">Session</span><span class="o">.</span><span class="n">commit</span><span class="p">()</span> <span class="c"># expires all attributes</span>
<span class="n">foo_7</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">Foo</span><span class="p">)</span><span class="o">.</span><span class="n">get</span><span class="p">(</span><span class="mi">7</span><span class="p">)</span>
<span class="k">assert</span> <span class="n">o</span><span class="o">.</span><span class="n">foo</span> <span class="ow">is</span> <span class="n">foo_7</span> <span class="c"># o.foo lazyloads on access</span></pre></div>
</div>
<p>A more minimal operation is to expire the attribute individually - this can
be performed for any <a class="reference internal" href="glossary.html#term-persistent"><em class="xref std std-term">persistent</em></a> object using <a class="reference internal" href="orm/session.html#sqlalchemy.orm.session.Session.expire" title="sqlalchemy.orm.session.Session.expire"><tt class="xref py py-meth docutils literal"><span class="pre">Session.expire()</span></tt></a>:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">o</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">SomeClass</span><span class="p">)</span><span class="o">.</span><span class="n">first</span><span class="p">()</span>
<span class="n">o</span><span class="o">.</span><span class="n">foo_id</span> <span class="o">=</span> <span class="mi">7</span>
<span class="n">Session</span><span class="o">.</span><span class="n">expire</span><span class="p">(</span><span class="n">o</span><span class="p">,</span> <span class="p">[</span><span class="s">'foo'</span><span class="p">])</span> <span class="c"># object must be persistent for this</span>
<span class="n">foo_7</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">Foo</span><span class="p">)</span><span class="o">.</span><span class="n">get</span><span class="p">(</span><span class="mi">7</span><span class="p">)</span>
<span class="k">assert</span> <span class="n">o</span><span class="o">.</span><span class="n">foo</span> <span class="ow">is</span> <span class="n">foo_7</span> <span class="c"># o.foo lazyloads on access</span></pre></div>
</div>
<p>Note that if the object is not persistent but present in the <a class="reference internal" href="orm/session.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>,
it’s known as <a class="reference internal" href="glossary.html#term-pending"><em class="xref std std-term">pending</em></a>. This means the row for the object has not been
INSERTed into the database yet. For such an object, setting <tt class="docutils literal"><span class="pre">foo_id</span></tt> does not
have meaning until the row is inserted; otherwise there is no row yet:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">new_obj</span> <span class="o">=</span> <span class="n">SomeClass</span><span class="p">()</span>
<span class="n">new_obj</span><span class="o">.</span><span class="n">foo_id</span> <span class="o">=</span> <span class="mi">7</span>
<span class="n">Session</span><span class="o">.</span><span class="n">add</span><span class="p">(</span><span class="n">new_obj</span><span class="p">)</span>
<span class="c"># accessing an un-set attribute sets it to None</span>
<span class="k">assert</span> <span class="n">new_obj</span><span class="o">.</span><span class="n">foo</span> <span class="ow">is</span> <span class="bp">None</span>
<span class="n">Session</span><span class="o">.</span><span class="n">flush</span><span class="p">()</span> <span class="c"># emits INSERT</span>
<span class="c"># expire this because we already set .foo to None</span>
<span class="n">Session</span><span class="o">.</span><span class="n">expire</span><span class="p">(</span><span class="n">o</span><span class="p">,</span> <span class="p">[</span><span class="s">'foo'</span><span class="p">])</span>
<span class="k">assert</span> <span class="n">new_obj</span><span class="o">.</span><span class="n">foo</span> <span class="ow">is</span> <span class="n">foo_7</span> <span class="c"># now it loads</span></pre></div>
</div>
<div class="topic">
<p class="topic-title first">Attribute loading for non-persistent objects</p>
<p>One variant on the “pending” behavior above is if we use the flag
<tt class="docutils literal"><span class="pre">load_on_pending</span></tt> on <a class="reference internal" href="orm/relationships.html#sqlalchemy.orm.relationship" title="sqlalchemy.orm.relationship"><tt class="xref py py-func docutils literal"><span class="pre">relationship()</span></tt></a>. When this flag is set, the
lazy loader will emit for <tt class="docutils literal"><span class="pre">new_obj.foo</span></tt> before the INSERT proceeds; another
variant of this is to use the <a class="reference internal" href="orm/session.html#sqlalchemy.orm.session.Session.enable_relationship_loading" title="sqlalchemy.orm.session.Session.enable_relationship_loading"><tt class="xref py py-meth docutils literal"><span class="pre">Session.enable_relationship_loading()</span></tt></a>
method, which can “attach” an object to a <a class="reference internal" href="orm/session.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> in such a way that
many-to-one relationships load as according to foreign key attributes
regardless of the object being in any particular state.
Both techniques are <strong>not recommended for general use</strong>; they were added to suit
specific programming scenarios encountered by users which involve the repurposing
of the ORM’s usual object states.</p>
</div>
<p>The recipe <a class="reference external" href="http://www.sqlalchemy.org/trac/wiki/UsageRecipes/ExpireRelationshipOnFKChange">ExpireRelationshipOnFKChange</a> features an example using SQLAlchemy events
in order to coordinate the setting of foreign key attributes with many-to-one
relationships.</p>
</div>
<div class="section" id="is-there-a-way-to-automagically-have-only-unique-keywords-or-other-kinds-of-objects-without-doing-a-query-for-the-keyword-and-getting-a-reference-to-the-row-containing-that-keyword">
<h3>Is there a way to automagically have only unique keywords (or other kinds of objects) without doing a query for the keyword and getting a reference to the row containing that keyword?<a class="headerlink" href="#is-there-a-way-to-automagically-have-only-unique-keywords-or-other-kinds-of-objects-without-doing-a-query-for-the-keyword-and-getting-a-reference-to-the-row-containing-that-keyword" title="Permalink to this headline">¶</a></h3>
<p>When people read the many-to-many example in the docs, they get hit with the
fact that if you create the same <tt class="docutils literal"><span class="pre">Keyword</span></tt> twice, it gets put in the DB twice.
Which is somewhat inconvenient.</p>
<p>This <a class="reference external" href="http://www.sqlalchemy.org/trac/wiki/UsageRecipes/UniqueObject">UniqueObject</a> recipe was created to address this issue.</p>
</div>
</div>
</div>
</div>
</div>
<div id="docs-bottom-navigation" class="docs-navigation-links">
<div id="docs-copyright">
© <a href="copyright.html">Copyright</a> 2007-2014, the SQLAlchemy authors and contributors.
Created using <a href="http://sphinx.pocoo.org/">Sphinx</a> 1.2b1.
</div>
</div>
</div>
</body>
</html>
|
