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
|
<!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>
What’s new in SQLAlchemy 0.4?
—
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" />
<link rel="up" title="Changes and Migration" href="index.html" />
<link rel="prev" title="What’s new in SQLAlchemy 0.5?" href="migration_05.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="Changes and Migration">Up</a> |
<a href="migration_05.html" title="What’s new in SQLAlchemy 0.5?">Prev</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="#">
What’s new in SQLAlchemy 0.4?
</a></h3>
<ul>
<li><a class="reference internal" href="#">What’s new in SQLAlchemy 0.4?</a><ul>
<li><a class="reference internal" href="#first-things-first">First Things First</a></li>
<li><a class="reference internal" href="#module-imports">Module Imports</a></li>
<li><a class="reference internal" href="#object-relational-mapping">Object Relational Mapping</a><ul>
<li><a class="reference internal" href="#querying">Querying</a><ul>
<li><a class="reference internal" href="#new-query-api">New Query API</a></li>
<li><a class="reference internal" href="#new-property-based-expression-constructs">New Property-Based Expression Constructs</a></li>
<li><a class="reference internal" href="#automatic-join-aliasing">Automatic Join Aliasing</a></li>
<li><a class="reference internal" href="#self-referential-queries">Self-referential Queries</a></li>
<li><a class="reference internal" href="#query-populate-existing"><tt class="docutils literal"><span class="pre">query.populate_existing()</span></tt></a></li>
</ul>
</li>
<li><a class="reference internal" href="#relations">Relations</a><ul>
<li><a class="reference internal" href="#sql-clauses-embedded-in-updates-inserts">SQL Clauses Embedded in Updates/Inserts</a></li>
<li><a class="reference internal" href="#self-referential-and-cyclical-eager-loading">Self-referential and Cyclical Eager Loading</a></li>
<li><a class="reference internal" href="#composite-types">Composite Types</a></li>
<li><a class="reference internal" href="#dynamic-loader-relations"><tt class="docutils literal"><span class="pre">dynamic_loader()</span></tt> relations</a></li>
<li><a class="reference internal" href="#new-options-undefer-group-eagerload-all">New Options: <tt class="docutils literal"><span class="pre">undefer_group()</span></tt>, <tt class="docutils literal"><span class="pre">eagerload_all()</span></tt></a></li>
<li><a class="reference internal" href="#new-collection-api">New Collection API</a></li>
<li><a class="reference internal" href="#mapped-relations-from-external-tables-subqueries">Mapped Relations from External Tables/Subqueries</a></li>
</ul>
</li>
<li><a class="reference internal" href="#horizontal-scaling-sharding-api">Horizontal Scaling (Sharding) API</a></li>
<li><a class="reference internal" href="#sessions">Sessions</a><ul>
<li><a class="reference internal" href="#new-session-create-paradigm-sessioncontext-assignmapper-deprecated">New Session Create Paradigm; SessionContext, assignmapper Deprecated</a></li>
<li><a class="reference internal" href="#sessions-are-again-weak-referencing-by-default">Sessions are again Weak Referencing By Default</a></li>
<li><a class="reference internal" href="#auto-transactional-sessions">Auto-Transactional Sessions</a></li>
<li><a class="reference internal" href="#auto-flushing-sessions">Auto-Flushing Sessions</a></li>
<li><a class="reference internal" href="#transactional-methods-moved-onto-sessions">Transactional methods moved onto sessions</a></li>
<li><a class="reference internal" href="#nested-session-transactions-with-savepoint">Nested Session Transactions with SAVEPOINT</a></li>
<li><a class="reference internal" href="#two-phase-commit-sessions">Two-Phase Commit Sessions</a></li>
</ul>
</li>
<li><a class="reference internal" href="#inheritance">Inheritance</a><ul>
<li><a class="reference internal" href="#polymorphic-inheritance-with-no-joins-or-unions">Polymorphic Inheritance with No Joins or Unions</a></li>
<li><a class="reference internal" href="#better-polymorphic-behavior-with-get">Better Polymorphic Behavior with <tt class="docutils literal"><span class="pre">get()</span></tt></a></li>
</ul>
</li>
<li><a class="reference internal" href="#types">Types</a><ul>
<li><a class="reference internal" href="#custom-subclasses-of-sqlalchemy-types-typedecorator">Custom Subclasses of <tt class="docutils literal"><span class="pre">sqlalchemy.types.TypeDecorator</span></tt></a></li>
</ul>
</li>
</ul>
</li>
<li><a class="reference internal" href="#sql-expressions">SQL Expressions</a><ul>
<li><a class="reference internal" href="#all-new-deterministic-label-alias-generation">All New, Deterministic Label/Alias Generation</a></li>
<li><a class="reference internal" href="#generative-select-constructs">Generative select() Constructs</a></li>
<li><a class="reference internal" href="#new-operator-system">New Operator System</a></li>
<li><a class="reference internal" href="#all-type-keyword-arguments-renamed-to-type">All <tt class="docutils literal"><span class="pre">type</span></tt> Keyword Arguments Renamed to <tt class="docutils literal"><span class="pre">type_</span></tt></a></li>
<li><a class="reference internal" href="#in-function-changed-to-accept-sequence-or-selectable">in_ Function Changed to Accept Sequence or Selectable</a></li>
</ul>
</li>
<li><a class="reference internal" href="#schema-and-reflection">Schema and Reflection</a><ul>
<li><a class="reference internal" href="#metadata-boundmetadata-dynamicmetadata"><tt class="docutils literal"><span class="pre">MetaData</span></tt>, <tt class="docutils literal"><span class="pre">BoundMetaData</span></tt>, <tt class="docutils literal"><span class="pre">DynamicMetaData</span></tt>...</a></li>
<li><a class="reference internal" href="#one-step-multi-table-reflection">One Step Multi-Table Reflection</a></li>
</ul>
</li>
<li><a class="reference internal" href="#sql-execution">SQL Execution</a><ul>
<li><a class="reference internal" href="#engine-connectable-and-bind-to-are-all-now-bind"><tt class="docutils literal"><span class="pre">engine</span></tt>, <tt class="docutils literal"><span class="pre">connectable</span></tt>, and <tt class="docutils literal"><span class="pre">bind_to</span></tt> are all now <tt class="docutils literal"><span class="pre">bind</span></tt></a></li>
<li><a class="reference internal" href="#transactions-nestedtransactions-and-twophasetransactions"><tt class="docutils literal"><span class="pre">Transactions</span></tt>, <tt class="docutils literal"><span class="pre">NestedTransactions</span></tt> and <tt class="docutils literal"><span class="pre">TwoPhaseTransactions</span></tt></a></li>
<li><a class="reference internal" href="#connection-pool-events">Connection Pool Events</a></li>
<li><a class="reference internal" href="#oracle-engine-fixed">Oracle Engine Fixed</a></li>
<li><a class="reference internal" href="#out-parameters-for-oracle">Out Parameters for Oracle</a></li>
<li><a class="reference internal" href="#connection-bound-metadata-sessions">Connection-bound <tt class="docutils literal"><span class="pre">MetaData</span></tt>, <tt class="docutils literal"><span class="pre">Sessions</span></tt></a></li>
<li><a class="reference internal" href="#faster-more-foolproof-resultproxy-objects">Faster, More Foolproof <tt class="docutils literal"><span class="pre">ResultProxy</span></tt> Objects</a></li>
</ul>
</li>
</ul>
</li>
</ul>
</div>
</div>
<div id="docs-body" class="withsidebar" >
<div class="section" id="what-s-new-in-sqlalchemy-0-4">
<h1>What’s new in SQLAlchemy 0.4?<a class="headerlink" href="#what-s-new-in-sqlalchemy-0-4" title="Permalink to this headline">¶</a></h1>
<div class="admonition-about-this-document admonition">
<p class="first admonition-title">About this Document</p>
<p>This document describes changes between SQLAlchemy version 0.3,
last released October 14, 2007, and SQLAlchemy version 0.4,
last released October 12, 2008.</p>
<p class="last">Document date: March 21, 2008</p>
</div>
<div class="section" id="first-things-first">
<h2>First Things First<a class="headerlink" href="#first-things-first" title="Permalink to this headline">¶</a></h2>
<p>If you’re using any ORM features, make sure you import from
<tt class="docutils literal"><span class="pre">sqlalchemy.orm</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="o">*</span>
<span class="kn">from</span> <span class="nn">sqlalchemy.orm</span> <span class="kn">import</span> <span class="o">*</span></pre></div>
</div>
<p>Secondly, anywhere you used to say <tt class="docutils literal"><span class="pre">engine=</span></tt>,
<tt class="docutils literal"><span class="pre">connectable=</span></tt>, <tt class="docutils literal"><span class="pre">bind_to=</span></tt>, <tt class="docutils literal"><span class="pre">something.engine</span></tt>,
<tt class="docutils literal"><span class="pre">metadata.connect()</span></tt>, use <tt class="docutils literal"><span class="pre">bind</span></tt>:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">myengine</span> <span class="o">=</span> <span class="n">create_engine</span><span class="p">(</span><span class="s">'sqlite://'</span><span class="p">)</span>
<span class="n">meta</span> <span class="o">=</span> <span class="n">MetaData</span><span class="p">(</span><span class="n">myengine</span><span class="p">)</span>
<span class="n">meta2</span> <span class="o">=</span> <span class="n">MetaData</span><span class="p">()</span>
<span class="n">meta2</span><span class="o">.</span><span class="n">bind</span> <span class="o">=</span> <span class="n">myengine</span>
<span class="n">session</span> <span class="o">=</span> <span class="n">create_session</span><span class="p">(</span><span class="n">bind</span><span class="o">=</span><span class="n">myengine</span><span class="p">)</span>
<span class="n">statement</span> <span class="o">=</span> <span class="n">select</span><span class="p">([</span><span class="n">table</span><span class="p">],</span> <span class="n">bind</span><span class="o">=</span><span class="n">myengine</span><span class="p">)</span></pre></div>
</div>
<p>Got those ? Good! You’re now (95%) 0.4 compatible. If
you’re using 0.3.10, you can make these changes immediately;
they’ll work there too.</p>
</div>
<div class="section" id="module-imports">
<h2>Module Imports<a class="headerlink" href="#module-imports" title="Permalink to this headline">¶</a></h2>
<p>In 0.3, “<tt class="docutils literal"><span class="pre">from</span> <span class="pre">sqlachemy</span> <span class="pre">import</span> <span class="pre">*</span></tt>” would import all of
sqlachemy’s sub-modules into your namespace. Version 0.4 no
longer imports sub-modules into the namespace. This may mean
you need to add extra imports into your code.</p>
<p>In 0.3, this code worked:</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="o">*</span>
<span class="k">class</span> <span class="nc">UTCDateTime</span><span class="p">(</span><span class="n">types</span><span class="o">.</span><span class="n">TypeDecorator</span><span class="p">):</span>
<span class="k">pass</span></pre></div>
</div>
<p>In 0.4, one must do:</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="o">*</span>
<span class="kn">from</span> <span class="nn">sqlalchemy</span> <span class="kn">import</span> <span class="n">types</span>
<span class="k">class</span> <span class="nc">UTCDateTime</span><span class="p">(</span><span class="n">types</span><span class="o">.</span><span class="n">TypeDecorator</span><span class="p">):</span>
<span class="k">pass</span></pre></div>
</div>
</div>
<div class="section" id="object-relational-mapping">
<h2>Object Relational Mapping<a class="headerlink" href="#object-relational-mapping" title="Permalink to this headline">¶</a></h2>
<div class="section" id="querying">
<h3>Querying<a class="headerlink" href="#querying" title="Permalink to this headline">¶</a></h3>
<div class="section" id="new-query-api">
<h4>New Query API<a class="headerlink" href="#new-query-api" title="Permalink to this headline">¶</a></h4>
<p>Query is standardized on the generative interface (old
interface is still there, just deprecated). While most of
the generative interface is available in 0.3, the 0.4 Query
has the inner guts to match the generative outside, and has
a lot more tricks. All result narrowing is via <tt class="docutils literal"><span class="pre">filter()</span></tt>
and <tt class="docutils literal"><span class="pre">filter_by()</span></tt>, limiting/offset is either through array
slices or <tt class="docutils literal"><span class="pre">limit()</span></tt>/<tt class="docutils literal"><span class="pre">offset()</span></tt>, joining is via
<tt class="docutils literal"><span class="pre">join()</span></tt> and <tt class="docutils literal"><span class="pre">outerjoin()</span></tt> (or more manually, through
<tt class="docutils literal"><span class="pre">select_from()</span></tt> as well as manually-formed criteria).</p>
<p>To avoid deprecation warnings, you must make some changes to
your 03 code</p>
<p>User.query.get_by( **kwargs )</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">User</span><span class="o">.</span><span class="n">query</span><span class="o">.</span><span class="n">filter_by</span><span class="p">(</span><span class="o">**</span><span class="n">kwargs</span><span class="p">)</span><span class="o">.</span><span class="n">first</span><span class="p">()</span></pre></div>
</div>
<p>User.query.select_by( **kwargs )</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">User</span><span class="o">.</span><span class="n">query</span><span class="o">.</span><span class="n">filter_by</span><span class="p">(</span><span class="o">**</span><span class="n">kwargs</span><span class="p">)</span><span class="o">.</span><span class="n">all</span><span class="p">()</span></pre></div>
</div>
<p>User.query.select()</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">User</span><span class="o">.</span><span class="n">query</span><span class="o">.</span><span class="n">filter</span><span class="p">(</span><span class="n">xxx</span><span class="p">)</span><span class="o">.</span><span class="n">all</span><span class="p">()</span></pre></div>
</div>
</div>
<div class="section" id="new-property-based-expression-constructs">
<h4>New Property-Based Expression Constructs<a class="headerlink" href="#new-property-based-expression-constructs" title="Permalink to this headline">¶</a></h4>
<p>By far the most palpable difference within the ORM is that
you can now construct your query criterion using class-based
attributes directly. The ”.c.” prefix is no longer needed
when working with mapped classes:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">session</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="n">User</span><span class="p">)</span><span class="o">.</span><span class="n">filter</span><span class="p">(</span><span class="n">and_</span><span class="p">(</span><span class="n">User</span><span class="o">.</span><span class="n">name</span> <span class="o">==</span> <span class="s">'fred'</span><span class="p">,</span> <span class="n">User</span><span class="o">.</span><span class="n">id</span> <span class="o">></span> <span class="mi">17</span><span class="p">))</span></pre></div>
</div>
<p>While simple column-based comparisons are no big deal, the
class attributes have some new “higher level” constructs
available, including what was previously only available in
<tt class="docutils literal"><span class="pre">filter_by()</span></tt>:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="c"># comparison of scalar relations to an instance</span>
<span class="nb">filter</span><span class="p">(</span><span class="n">Address</span><span class="o">.</span><span class="n">user</span> <span class="o">==</span> <span class="n">user</span><span class="p">)</span>
<span class="c"># return all users who contain a particular address</span>
<span class="nb">filter</span><span class="p">(</span><span class="n">User</span><span class="o">.</span><span class="n">addresses</span><span class="o">.</span><span class="n">contains</span><span class="p">(</span><span class="n">address</span><span class="p">))</span>
<span class="c"># return all users who *dont* contain the address</span>
<span class="nb">filter</span><span class="p">(</span><span class="o">~</span><span class="n">User</span><span class="o">.</span><span class="n">address</span><span class="o">.</span><span class="n">contains</span><span class="p">(</span><span class="n">address</span><span class="p">))</span>
<span class="c"># return all users who contain a particular address with</span>
<span class="c"># the email_address like '%foo%'</span>
<span class="nb">filter</span><span class="p">(</span><span class="n">User</span><span class="o">.</span><span class="n">addresses</span><span class="o">.</span><span class="n">any</span><span class="p">(</span><span class="n">Address</span><span class="o">.</span><span class="n">email_address</span><span class="o">.</span><span class="n">like</span><span class="p">(</span><span class="s">'</span><span class="si">%f</span><span class="s">oo%'</span><span class="p">)))</span>
<span class="c"># same, email address equals 'foo@bar.com'. can fall back to keyword</span>
<span class="c"># args for simple comparisons</span>
<span class="nb">filter</span><span class="p">(</span><span class="n">User</span><span class="o">.</span><span class="n">addresses</span><span class="o">.</span><span class="n">any</span><span class="p">(</span><span class="n">email_address</span> <span class="o">=</span> <span class="s">'foo@bar.com'</span><span class="p">))</span>
<span class="c"># return all Addresses whose user attribute has the username 'ed'</span>
<span class="nb">filter</span><span class="p">(</span><span class="n">Address</span><span class="o">.</span><span class="n">user</span><span class="o">.</span><span class="n">has</span><span class="p">(</span><span class="n">name</span><span class="o">=</span><span class="s">'ed'</span><span class="p">))</span>
<span class="c"># return all Addresses whose user attribute has the username 'ed'</span>
<span class="c"># and an id > 5 (mixing clauses with kwargs)</span>
<span class="nb">filter</span><span class="p">(</span><span class="n">Address</span><span class="o">.</span><span class="n">user</span><span class="o">.</span><span class="n">has</span><span class="p">(</span><span class="n">User</span><span class="o">.</span><span class="n">id</span> <span class="o">></span> <span class="mi">5</span><span class="p">,</span> <span class="n">name</span><span class="o">=</span><span class="s">'ed'</span><span class="p">))</span></pre></div>
</div>
<p>The <tt class="docutils literal"><span class="pre">Column</span></tt> collection remains available on mapped
classes in the <tt class="docutils literal"><span class="pre">.c</span></tt> attribute. Note that property-based
expressions are only available with mapped properties of
mapped classes. <tt class="docutils literal"><span class="pre">.c</span></tt> is still used to access columns in
regular tables and selectable objects produced from SQL
Expressions.</p>
</div>
<div class="section" id="automatic-join-aliasing">
<h4>Automatic Join Aliasing<a class="headerlink" href="#automatic-join-aliasing" title="Permalink to this headline">¶</a></h4>
<p>We’ve had join() and outerjoin() for a while now:</p>
<div class="highlight-python"><pre>session.query(Order).join('items')...</pre>
</div>
<p>Now you can alias them:</p>
<div class="highlight-python"><pre>session.query(Order).join('items', aliased=True).
filter(Item.name='item 1').join('items', aliased=True).filter(Item.name=='item 3')</pre>
</div>
<p>The above will create two joins from orders->items using
aliases. the <tt class="docutils literal"><span class="pre">filter()</span></tt> call subsequent to each will
adjust its table criterion to that of the alias. To get at
the <tt class="docutils literal"><span class="pre">Item</span></tt> objects, use <tt class="docutils literal"><span class="pre">add_entity()</span></tt> and target each
join with an <tt class="docutils literal"><span class="pre">id</span></tt>:</p>
<div class="highlight-python"><pre>session.query(Order).join('items', id='j1', aliased=True).
filter(Item.name == 'item 1').join('items', aliased=True, id='j2').
filter(Item.name == 'item 3').add_entity(Item, id='j1').add_entity(Item, id='j2')</pre>
</div>
<p>Returns tuples in the form: <tt class="docutils literal"><span class="pre">(Order,</span> <span class="pre">Item,</span> <span class="pre">Item)</span></tt>.</p>
</div>
<div class="section" id="self-referential-queries">
<h4>Self-referential Queries<a class="headerlink" href="#self-referential-queries" title="Permalink to this headline">¶</a></h4>
<p>So query.join() can make aliases now. What does that give
us ? Self-referential queries ! Joins can be done without
any <tt class="docutils literal"><span class="pre">Alias</span></tt> objects:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="c"># standard self-referential TreeNode mapper with backref</span>
<span class="n">mapper</span><span class="p">(</span><span class="n">TreeNode</span><span class="p">,</span> <span class="n">tree_nodes</span><span class="p">,</span> <span class="n">properties</span><span class="o">=</span><span class="p">{</span>
<span class="s">'children'</span><span class="p">:</span><span class="n">relation</span><span class="p">(</span><span class="n">TreeNode</span><span class="p">,</span> <span class="n">backref</span><span class="o">=</span><span class="n">backref</span><span class="p">(</span><span class="s">'parent'</span><span class="p">,</span> <span class="n">remote_side</span><span class="o">=</span><span class="n">tree_nodes</span><span class="o">.</span><span class="n">id</span><span class="p">))</span>
<span class="p">})</span>
<span class="c"># query for node with child containing "bar" two levels deep</span>
<span class="n">session</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="n">TreeNode</span><span class="p">)</span><span class="o">.</span><span class="n">join</span><span class="p">([</span><span class="s">"children"</span><span class="p">,</span> <span class="s">"children"</span><span class="p">],</span> <span class="n">aliased</span><span class="o">=</span><span class="bp">True</span><span class="p">)</span><span class="o">.</span><span class="n">filter_by</span><span class="p">(</span><span class="n">name</span><span class="o">=</span><span class="s">'bar'</span><span class="p">)</span></pre></div>
</div>
<p>To add criterion for each table along the way in an aliased
join, you can use <tt class="docutils literal"><span class="pre">from_joinpoint</span></tt> to keep joining against
the same line of aliases:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="c"># search for the treenode along the path "n1/n12/n122"</span>
<span class="c"># first find a Node with name="n122"</span>
<span class="n">q</span> <span class="o">=</span> <span class="n">sess</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="n">Node</span><span class="p">)</span><span class="o">.</span><span class="n">filter_by</span><span class="p">(</span><span class="n">name</span><span class="o">=</span><span class="s">'n122'</span><span class="p">)</span>
<span class="c"># then join to parent with "n12"</span>
<span class="n">q</span> <span class="o">=</span> <span class="n">q</span><span class="o">.</span><span class="n">join</span><span class="p">(</span><span class="s">'parent'</span><span class="p">,</span> <span class="n">aliased</span><span class="o">=</span><span class="bp">True</span><span class="p">)</span><span class="o">.</span><span class="n">filter_by</span><span class="p">(</span><span class="n">name</span><span class="o">=</span><span class="s">'n12'</span><span class="p">)</span>
<span class="c"># join again to the next parent with 'n1'. use 'from_joinpoint'</span>
<span class="c"># so we join from the previous point, instead of joining off the</span>
<span class="c"># root table</span>
<span class="n">q</span> <span class="o">=</span> <span class="n">q</span><span class="o">.</span><span class="n">join</span><span class="p">(</span><span class="s">'parent'</span><span class="p">,</span> <span class="n">aliased</span><span class="o">=</span><span class="bp">True</span><span class="p">,</span> <span class="n">from_joinpoint</span><span class="o">=</span><span class="bp">True</span><span class="p">)</span><span class="o">.</span><span class="n">filter_by</span><span class="p">(</span><span class="n">name</span><span class="o">=</span><span class="s">'n1'</span><span class="p">)</span>
<span class="n">node</span> <span class="o">=</span> <span class="n">q</span><span class="o">.</span><span class="n">first</span><span class="p">()</span></pre></div>
</div>
</div>
<div class="section" id="query-populate-existing">
<h4><tt class="docutils literal"><span class="pre">query.populate_existing()</span></tt><a class="headerlink" href="#query-populate-existing" title="Permalink to this headline">¶</a></h4>
<p>The eager version of <tt class="docutils literal"><span class="pre">query.load()</span></tt> (or
<tt class="docutils literal"><span class="pre">session.refresh()</span></tt>). Every instance loaded from the
query, including all eagerly loaded items, get refreshed
immediately if already present in the session:</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">Blah</span><span class="p">)</span><span class="o">.</span><span class="n">populate_existing</span><span class="p">()</span><span class="o">.</span><span class="n">all</span><span class="p">()</span></pre></div>
</div>
</div>
</div>
<div class="section" id="relations">
<h3>Relations<a class="headerlink" href="#relations" title="Permalink to this headline">¶</a></h3>
<div class="section" id="sql-clauses-embedded-in-updates-inserts">
<h4>SQL Clauses Embedded in Updates/Inserts<a class="headerlink" href="#sql-clauses-embedded-in-updates-inserts" title="Permalink to this headline">¶</a></h4>
<p>For inline execution of SQL clauses, embedded right in the
UPDATE or INSERT, during a <tt class="docutils literal"><span class="pre">flush()</span></tt>:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">myobject</span><span class="o">.</span><span class="n">foo</span> <span class="o">=</span> <span class="n">mytable</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">value</span> <span class="o">+</span> <span class="mi">1</span>
<span class="n">user</span><span class="o">.</span><span class="n">pwhash</span> <span class="o">=</span> <span class="n">func</span><span class="o">.</span><span class="n">md5</span><span class="p">(</span><span class="n">password</span><span class="p">)</span>
<span class="n">order</span><span class="o">.</span><span class="n">hash</span> <span class="o">=</span> <span class="n">text</span><span class="p">(</span><span class="s">"select hash from hashing_table"</span><span class="p">)</span></pre></div>
</div>
<p>The column-attribute is set up with a deferred loader after
the operation, so that it issues the SQL to load the new
value when you next access.</p>
</div>
<div class="section" id="self-referential-and-cyclical-eager-loading">
<h4>Self-referential and Cyclical Eager Loading<a class="headerlink" href="#self-referential-and-cyclical-eager-loading" title="Permalink to this headline">¶</a></h4>
<p>Since our alias-fu has improved, <tt class="docutils literal"><span class="pre">relation()</span></tt> can join
along the same table *any number of times*; you tell it how
deep you want to go. Lets show the self-referential
<tt class="docutils literal"><span class="pre">TreeNode</span></tt> more clearly:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">nodes</span> <span class="o">=</span> <span class="n">Table</span><span class="p">(</span><span class="s">'nodes'</span><span class="p">,</span> <span class="n">metadata</span><span class="p">,</span>
<span class="n">Column</span><span class="p">(</span><span class="s">'id'</span><span class="p">,</span> <span class="n">Integer</span><span class="p">,</span> <span class="n">primary_key</span><span class="o">=</span><span class="bp">True</span><span class="p">),</span>
<span class="n">Column</span><span class="p">(</span><span class="s">'parent_id'</span><span class="p">,</span> <span class="n">Integer</span><span class="p">,</span> <span class="n">ForeignKey</span><span class="p">(</span><span class="s">'nodes.id'</span><span class="p">)),</span>
<span class="n">Column</span><span class="p">(</span><span class="s">'name'</span><span class="p">,</span> <span class="n">String</span><span class="p">(</span><span class="mi">30</span><span class="p">)))</span>
<span class="k">class</span> <span class="nc">TreeNode</span><span class="p">(</span><span class="nb">object</span><span class="p">):</span>
<span class="k">pass</span>
<span class="n">mapper</span><span class="p">(</span><span class="n">TreeNode</span><span class="p">,</span> <span class="n">nodes</span><span class="p">,</span> <span class="n">properties</span><span class="o">=</span><span class="p">{</span>
<span class="s">'children'</span><span class="p">:</span><span class="n">relation</span><span class="p">(</span><span class="n">TreeNode</span><span class="p">,</span> <span class="n">lazy</span><span class="o">=</span><span class="bp">False</span><span class="p">,</span> <span class="n">join_depth</span><span class="o">=</span><span class="mi">3</span><span class="p">)</span>
<span class="p">})</span></pre></div>
</div>
<p>So what happens when we say:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">create_session</span><span class="p">()</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="n">TreeNode</span><span class="p">)</span><span class="o">.</span><span class="n">all</span><span class="p">()</span></pre></div>
</div>
<p>? A join along aliases, three levels deep off the parent:</p>
<div class="highlight-python"><pre>SELECT
nodes_3.id AS nodes_3_id, nodes_3.parent_id AS nodes_3_parent_id, nodes_3.name AS nodes_3_name,
nodes_2.id AS nodes_2_id, nodes_2.parent_id AS nodes_2_parent_id, nodes_2.name AS nodes_2_name,
nodes_1.id AS nodes_1_id, nodes_1.parent_id AS nodes_1_parent_id, nodes_1.name AS nodes_1_name,
nodes.id AS nodes_id, nodes.parent_id AS nodes_parent_id, nodes.name AS nodes_name
FROM nodes LEFT OUTER JOIN nodes AS nodes_1 ON nodes.id = nodes_1.parent_id
LEFT OUTER JOIN nodes AS nodes_2 ON nodes_1.id = nodes_2.parent_id
LEFT OUTER JOIN nodes AS nodes_3 ON nodes_2.id = nodes_3.parent_id
ORDER BY nodes.oid, nodes_1.oid, nodes_2.oid, nodes_3.oid</pre>
</div>
<p>Notice the nice clean alias names too. The joining doesn’t
care if it’s against the same immediate table or some other
object which then cycles back to the beginning. Any kind
of chain of eager loads can cycle back onto itself when
<tt class="docutils literal"><span class="pre">join_depth</span></tt> is specified. When not present, eager
loading automatically stops when it hits a cycle.</p>
</div>
<div class="section" id="composite-types">
<h4>Composite Types<a class="headerlink" href="#composite-types" title="Permalink to this headline">¶</a></h4>
<p>This is one from the Hibernate camp. Composite Types let
you define a custom datatype that is composed of more than
one column (or one column, if you wanted). Lets define a
new type, <tt class="docutils literal"><span class="pre">Point</span></tt>. Stores an x/y coordinate:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="k">class</span> <span class="nc">Point</span><span class="p">(</span><span class="nb">object</span><span class="p">):</span>
<span class="k">def</span> <span class="nf">__init__</span><span class="p">(</span><span class="bp">self</span><span class="p">,</span> <span class="n">x</span><span class="p">,</span> <span class="n">y</span><span class="p">):</span>
<span class="bp">self</span><span class="o">.</span><span class="n">x</span> <span class="o">=</span> <span class="n">x</span>
<span class="bp">self</span><span class="o">.</span><span class="n">y</span> <span class="o">=</span> <span class="n">y</span>
<span class="k">def</span> <span class="nf">__composite_values__</span><span class="p">(</span><span class="bp">self</span><span class="p">):</span>
<span class="k">return</span> <span class="bp">self</span><span class="o">.</span><span class="n">x</span><span class="p">,</span> <span class="bp">self</span><span class="o">.</span><span class="n">y</span>
<span class="k">def</span> <span class="nf">__eq__</span><span class="p">(</span><span class="bp">self</span><span class="p">,</span> <span class="n">other</span><span class="p">):</span>
<span class="k">return</span> <span class="n">other</span><span class="o">.</span><span class="n">x</span> <span class="o">==</span> <span class="bp">self</span><span class="o">.</span><span class="n">x</span> <span class="ow">and</span> <span class="n">other</span><span class="o">.</span><span class="n">y</span> <span class="o">==</span> <span class="bp">self</span><span class="o">.</span><span class="n">y</span>
<span class="k">def</span> <span class="nf">__ne__</span><span class="p">(</span><span class="bp">self</span><span class="p">,</span> <span class="n">other</span><span class="p">):</span>
<span class="k">return</span> <span class="ow">not</span> <span class="bp">self</span><span class="o">.</span><span class="n">__eq__</span><span class="p">(</span><span class="n">other</span><span class="p">)</span></pre></div>
</div>
<p>The way the <tt class="docutils literal"><span class="pre">Point</span></tt> object is defined is specific to a
custom type; constructor takes a list of arguments, and the
<tt class="docutils literal"><span class="pre">__composite_values__()</span></tt> method produces a sequence of
those arguments. The order will match up to our mapper, as
we’ll see in a moment.</p>
<p>Let’s create a table of vertices storing two points per row:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">vertices</span> <span class="o">=</span> <span class="n">Table</span><span class="p">(</span><span class="s">'vertices'</span><span class="p">,</span> <span class="n">metadata</span><span class="p">,</span>
<span class="n">Column</span><span class="p">(</span><span class="s">'id'</span><span class="p">,</span> <span class="n">Integer</span><span class="p">,</span> <span class="n">primary_key</span><span class="o">=</span><span class="bp">True</span><span class="p">),</span>
<span class="n">Column</span><span class="p">(</span><span class="s">'x1'</span><span class="p">,</span> <span class="n">Integer</span><span class="p">),</span>
<span class="n">Column</span><span class="p">(</span><span class="s">'y1'</span><span class="p">,</span> <span class="n">Integer</span><span class="p">),</span>
<span class="n">Column</span><span class="p">(</span><span class="s">'x2'</span><span class="p">,</span> <span class="n">Integer</span><span class="p">),</span>
<span class="n">Column</span><span class="p">(</span><span class="s">'y2'</span><span class="p">,</span> <span class="n">Integer</span><span class="p">),</span>
<span class="p">)</span></pre></div>
</div>
<p>Then, map it ! We’ll create a <tt class="docutils literal"><span class="pre">Vertex</span></tt> object which
stores two <tt class="docutils literal"><span class="pre">Point</span></tt> objects:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="k">class</span> <span class="nc">Vertex</span><span class="p">(</span><span class="nb">object</span><span class="p">):</span>
<span class="k">def</span> <span class="nf">__init__</span><span class="p">(</span><span class="bp">self</span><span class="p">,</span> <span class="n">start</span><span class="p">,</span> <span class="n">end</span><span class="p">):</span>
<span class="bp">self</span><span class="o">.</span><span class="n">start</span> <span class="o">=</span> <span class="n">start</span>
<span class="bp">self</span><span class="o">.</span><span class="n">end</span> <span class="o">=</span> <span class="n">end</span>
<span class="n">mapper</span><span class="p">(</span><span class="n">Vertex</span><span class="p">,</span> <span class="n">vertices</span><span class="p">,</span> <span class="n">properties</span><span class="o">=</span><span class="p">{</span>
<span class="s">'start'</span><span class="p">:</span><span class="n">composite</span><span class="p">(</span><span class="n">Point</span><span class="p">,</span> <span class="n">vertices</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">x1</span><span class="p">,</span> <span class="n">vertices</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">y1</span><span class="p">),</span>
<span class="s">'end'</span><span class="p">:</span><span class="n">composite</span><span class="p">(</span><span class="n">Point</span><span class="p">,</span> <span class="n">vertices</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">x2</span><span class="p">,</span> <span class="n">vertices</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">y2</span><span class="p">)</span>
<span class="p">})</span></pre></div>
</div>
<p>Once you’ve set up your composite type, it’s usable just
like any other type:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">v</span> <span class="o">=</span> <span class="n">Vertex</span><span class="p">(</span><span class="n">Point</span><span class="p">(</span><span class="mi">3</span><span class="p">,</span> <span class="mi">4</span><span class="p">),</span> <span class="n">Point</span><span class="p">(</span><span class="mi">26</span><span class="p">,</span><span class="mi">15</span><span class="p">))</span>
<span class="n">session</span><span class="o">.</span><span class="n">save</span><span class="p">(</span><span class="n">v</span><span class="p">)</span>
<span class="n">session</span><span class="o">.</span><span class="n">flush</span><span class="p">()</span>
<span class="c"># works in queries too</span>
<span class="n">q</span> <span class="o">=</span> <span class="n">session</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="n">Vertex</span><span class="p">)</span><span class="o">.</span><span class="n">filter</span><span class="p">(</span><span class="n">Vertex</span><span class="o">.</span><span class="n">start</span> <span class="o">==</span> <span class="n">Point</span><span class="p">(</span><span class="mi">3</span><span class="p">,</span> <span class="mi">4</span><span class="p">))</span></pre></div>
</div>
<p>If you’d like to define the way the mapped attributes
generate SQL clauses when used in expressions, create your
own <tt class="docutils literal"><span class="pre">sqlalchemy.orm.PropComparator</span></tt> subclass, defining any
of the common operators (like <tt class="docutils literal"><span class="pre">__eq__()</span></tt>, <tt class="docutils literal"><span class="pre">__le__()</span></tt>,
etc.), and send it in to <tt class="docutils literal"><span class="pre">composite()</span></tt>. Composite types
work as primary keys too, and are usable in <tt class="docutils literal"><span class="pre">query.get()</span></tt>:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="c"># a Document class which uses a composite Version</span>
<span class="c"># object as primary key</span>
<span class="n">document</span> <span class="o">=</span> <span class="n">query</span><span class="o">.</span><span class="n">get</span><span class="p">(</span><span class="n">Version</span><span class="p">(</span><span class="mi">1</span><span class="p">,</span> <span class="s">'a'</span><span class="p">))</span></pre></div>
</div>
</div>
<div class="section" id="dynamic-loader-relations">
<h4><tt class="docutils literal"><span class="pre">dynamic_loader()</span></tt> relations<a class="headerlink" href="#dynamic-loader-relations" title="Permalink to this headline">¶</a></h4>
<p>A <tt class="docutils literal"><span class="pre">relation()</span></tt> that returns a live <tt class="docutils literal"><span class="pre">Query</span></tt> object for
all read operations. Write operations are limited to just
<tt class="docutils literal"><span class="pre">append()</span></tt> and <tt class="docutils literal"><span class="pre">remove()</span></tt>, changes to the collection are
not visible until the session is flushed. This feature is
particularly handy with an “autoflushing” session which will
flush before each query.</p>
<div class="highlight-python"><pre>mapper(Foo, foo_table, properties={
'bars':dynamic_loader(Bar, backref='foo', <other relation() opts>)
})
session = create_session(autoflush=True)
foo = session.query(Foo).first()
foo.bars.append(Bar(name='lala'))
for bar in foo.bars.filter(Bar.name=='lala'):
print bar
session.commit()</pre>
</div>
</div>
<div class="section" id="new-options-undefer-group-eagerload-all">
<h4>New Options: <tt class="docutils literal"><span class="pre">undefer_group()</span></tt>, <tt class="docutils literal"><span class="pre">eagerload_all()</span></tt><a class="headerlink" href="#new-options-undefer-group-eagerload-all" title="Permalink to this headline">¶</a></h4>
<p>A couple of query options which are handy.
<tt class="docutils literal"><span class="pre">undefer_group()</span></tt> marks a whole group of “deferred”
columns as undeferred:</p>
<div class="highlight-python"><pre>mapper(Class, table, properties={
'foo' : deferred(table.c.foo, group='group1'),
'bar' : deferred(table.c.bar, group='group1'),
'bat' : deferred(table.c.bat, group='group1'),
)
session.query(Class).options(undefer_group('group1')).filter(...).all()</pre>
</div>
<p>and <tt class="docutils literal"><span class="pre">eagerload_all()</span></tt> sets a chain of attributes to be
eager in one pass:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">mapper</span><span class="p">(</span><span class="n">Foo</span><span class="p">,</span> <span class="n">foo_table</span><span class="p">,</span> <span class="n">properties</span><span class="o">=</span><span class="p">{</span>
<span class="s">'bar'</span><span class="p">:</span><span class="n">relation</span><span class="p">(</span><span class="n">Bar</span><span class="p">)</span>
<span class="p">})</span>
<span class="n">mapper</span><span class="p">(</span><span class="n">Bar</span><span class="p">,</span> <span class="n">bar_table</span><span class="p">,</span> <span class="n">properties</span><span class="o">=</span><span class="p">{</span>
<span class="s">'bat'</span><span class="p">:</span><span class="n">relation</span><span class="p">(</span><span class="n">Bat</span><span class="p">)</span>
<span class="p">})</span>
<span class="n">mapper</span><span class="p">(</span><span class="n">Bat</span><span class="p">,</span> <span class="n">bat_table</span><span class="p">)</span>
<span class="c"># eager load bar and bat</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">options</span><span class="p">(</span><span class="n">eagerload_all</span><span class="p">(</span><span class="s">'bar.bat'</span><span class="p">))</span><span class="o">.</span><span class="n">filter</span><span class="p">(</span><span class="o">...</span><span class="p">)</span><span class="o">.</span><span class="n">all</span><span class="p">()</span></pre></div>
</div>
</div>
<div class="section" id="new-collection-api">
<h4>New Collection API<a class="headerlink" href="#new-collection-api" title="Permalink to this headline">¶</a></h4>
<p>Collections are no longer proxied by an
{{{InstrumentedList}}} proxy, and access to members, methods
and attributes is direct. Decorators now intercept objects
entering and leaving the collection, and it is now possible
to easily write a custom collection class that manages its
own membership. Flexible decorators also replace the named
method interface of custom collections in 0.3, allowing any
class to be easily adapted to use as a collection container.</p>
<p>Dictionary-based collections are now much easier to use and
fully <tt class="docutils literal"><span class="pre">dict</span></tt>-like. Changing <tt class="docutils literal"><span class="pre">__iter__</span></tt> is no longer
needed for <tt class="docutils literal"><span class="pre">dict``s,</span> <span class="pre">and</span> <span class="pre">new</span> <span class="pre">built-in</span> <span class="pre">``dict</span></tt> types cover
many needs:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="c"># use a dictionary relation keyed by a column</span>
<span class="n">relation</span><span class="p">(</span><span class="n">Item</span><span class="p">,</span> <span class="n">collection_class</span><span class="o">=</span><span class="n">column_mapped_collection</span><span class="p">(</span><span class="n">items</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">keyword</span><span class="p">))</span>
<span class="c"># or named attribute</span>
<span class="n">relation</span><span class="p">(</span><span class="n">Item</span><span class="p">,</span> <span class="n">collection_class</span><span class="o">=</span><span class="n">attribute_mapped_collection</span><span class="p">(</span><span class="s">'keyword'</span><span class="p">))</span>
<span class="c"># or any function you like</span>
<span class="n">relation</span><span class="p">(</span><span class="n">Item</span><span class="p">,</span> <span class="n">collection_class</span><span class="o">=</span><span class="n">mapped_collection</span><span class="p">(</span><span class="k">lambda</span> <span class="n">entity</span><span class="p">:</span> <span class="n">entity</span><span class="o">.</span><span class="n">a</span> <span class="o">+</span> <span class="n">entity</span><span class="o">.</span><span class="n">b</span><span class="p">))</span></pre></div>
</div>
<p>Existing 0.3 <tt class="docutils literal"><span class="pre">dict</span></tt>-like and freeform object derived
collection classes will need to be updated for the new API.
In most cases this is simply a matter of adding a couple
decorators to the class definition.</p>
</div>
<div class="section" id="mapped-relations-from-external-tables-subqueries">
<h4>Mapped Relations from External Tables/Subqueries<a class="headerlink" href="#mapped-relations-from-external-tables-subqueries" title="Permalink to this headline">¶</a></h4>
<p>This feature quietly appeared in 0.3 but has been improved
in 0.4 thanks to better ability to convert subqueries
against a table into subqueries against an alias of that
table; this is key for eager loading, aliased joins in
queries, etc. It reduces the need to create mappers against
select statements when you just need to add some extra
columns or subqueries:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">mapper</span><span class="p">(</span><span class="n">User</span><span class="p">,</span> <span class="n">users</span><span class="p">,</span> <span class="n">properties</span><span class="o">=</span><span class="p">{</span>
<span class="s">'fullname'</span><span class="p">:</span> <span class="n">column_property</span><span class="p">((</span><span class="n">users</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">firstname</span> <span class="o">+</span> <span class="n">users</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">lastname</span><span class="p">)</span><span class="o">.</span><span class="n">label</span><span class="p">(</span><span class="s">'fullname'</span><span class="p">)),</span>
<span class="s">'numposts'</span><span class="p">:</span> <span class="n">column_property</span><span class="p">(</span>
<span class="n">select</span><span class="p">([</span><span class="n">func</span><span class="o">.</span><span class="n">count</span><span class="p">(</span><span class="mi">1</span><span class="p">)],</span> <span class="n">users</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">id</span><span class="o">==</span><span class="n">posts</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">user_id</span><span class="p">)</span><span class="o">.</span><span class="n">correlate</span><span class="p">(</span><span class="n">users</span><span class="p">)</span><span class="o">.</span><span class="n">label</span><span class="p">(</span><span class="s">'posts'</span><span class="p">)</span>
<span class="p">)</span>
<span class="p">})</span></pre></div>
</div>
<p>a typical query looks like:</p>
<div class="highlight-python"><pre>SELECT (SELECT count(1) FROM posts WHERE users.id = posts.user_id) AS count,
users.firstname || users.lastname AS fullname,
users.id AS users_id, users.firstname AS users_firstname, users.lastname AS users_lastname
FROM users ORDER BY users.oid</pre>
</div>
</div>
</div>
<div class="section" id="horizontal-scaling-sharding-api">
<h3>Horizontal Scaling (Sharding) API<a class="headerlink" href="#horizontal-scaling-sharding-api" title="Permalink to this headline">¶</a></h3>
<p>[browser:/sqlalchemy/trunk/examples/sharding/attribute_shard
.py]</p>
</div>
<div class="section" id="sessions">
<h3>Sessions<a class="headerlink" href="#sessions" title="Permalink to this headline">¶</a></h3>
<div class="section" id="new-session-create-paradigm-sessioncontext-assignmapper-deprecated">
<h4>New Session Create Paradigm; SessionContext, assignmapper Deprecated<a class="headerlink" href="#new-session-create-paradigm-sessioncontext-assignmapper-deprecated" title="Permalink to this headline">¶</a></h4>
<p>That’s right, the whole shebang is being replaced with two
configurational functions. Using both will produce the most
0.1-ish feel we’ve had since 0.1 (i.e., the least amount of
typing).</p>
<p>Configure your own <tt class="docutils literal"><span class="pre">Session</span></tt> class right where you define
your <tt class="docutils literal"><span class="pre">engine</span></tt> (or anywhere):</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.orm</span> <span class="kn">import</span> <span class="n">sessionmaker</span>
<span class="n">engine</span> <span class="o">=</span> <span class="n">create_engine</span><span class="p">(</span><span class="s">'myengine://'</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="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">True</span><span class="p">,</span> <span class="n">transactional</span><span class="o">=</span><span class="bp">True</span><span class="p">)</span>
<span class="c"># use the new Session() freely</span>
<span class="n">sess</span> <span class="o">=</span> <span class="n">Session</span><span class="p">()</span>
<span class="n">sess</span><span class="o">.</span><span class="n">save</span><span class="p">(</span><span class="n">someobject</span><span class="p">)</span>
<span class="n">sess</span><span class="o">.</span><span class="n">flush</span><span class="p">()</span></pre></div>
</div>
<p>If you need to post-configure your Session, say with an
engine, add it later with <tt class="docutils literal"><span class="pre">configure()</span></tt>:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">Session</span><span class="o">.</span><span class="n">configure</span><span class="p">(</span><span class="n">bind</span><span class="o">=</span><span class="n">create_engine</span><span class="p">(</span><span class="o">...</span><span class="p">))</span></pre></div>
</div>
<p>All the behaviors of <tt class="docutils literal"><span class="pre">SessionContext</span></tt> and the <tt class="docutils literal"><span class="pre">query</span></tt>
and <tt class="docutils literal"><span class="pre">__init__</span></tt> methods of <tt class="docutils literal"><span class="pre">assignmapper</span></tt> are moved into
the new <tt class="docutils literal"><span class="pre">scoped_session()</span></tt> function, which is compatible
with both <tt class="docutils literal"><span class="pre">sessionmaker</span></tt> as well as <tt class="docutils literal"><span class="pre">create_session()</span></tt>:</p>
<div class="highlight-python"><div class="highlight"><pre><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">Session</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">autoflush</span><span class="o">=</span><span class="bp">True</span><span class="p">,</span> <span class="n">transactional</span><span class="o">=</span><span class="bp">True</span><span class="p">))</span>
<span class="n">Session</span><span class="o">.</span><span class="n">configure</span><span class="p">(</span><span class="n">bind</span><span class="o">=</span><span class="n">engine</span><span class="p">)</span>
<span class="n">u</span> <span class="o">=</span> <span class="n">User</span><span class="p">(</span><span class="n">name</span><span class="o">=</span><span class="s">'wendy'</span><span class="p">)</span>
<span class="n">sess</span> <span class="o">=</span> <span class="n">Session</span><span class="p">()</span>
<span class="n">sess</span><span class="o">.</span><span class="n">save</span><span class="p">(</span><span class="n">u</span><span class="p">)</span>
<span class="n">sess</span><span class="o">.</span><span class="n">commit</span><span class="p">()</span>
<span class="c"># Session constructor is thread-locally scoped. Everyone gets the same</span>
<span class="c"># Session in the thread when scope="thread".</span>
<span class="n">sess2</span> <span class="o">=</span> <span class="n">Session</span><span class="p">()</span>
<span class="k">assert</span> <span class="n">sess</span> <span class="ow">is</span> <span class="n">sess2</span></pre></div>
</div>
<p>When using a thread-local <tt class="docutils literal"><span class="pre">Session</span></tt>, the returned class
has all of <tt class="docutils literal"><span class="pre">Session's</span></tt> interface implemented as
classmethods, and “assignmapper“‘s functionality is
available using the <tt class="docutils literal"><span class="pre">mapper</span></tt> classmethod. Just like the
old <tt class="docutils literal"><span class="pre">objectstore</span></tt> days....</p>
<div class="highlight-python"><div class="highlight"><pre><span class="c"># "assignmapper"-like functionality available via ScopedSession.mapper</span>
<span class="n">Session</span><span class="o">.</span><span class="n">mapper</span><span class="p">(</span><span class="n">User</span><span class="p">,</span> <span class="n">users_table</span><span class="p">)</span>
<span class="n">u</span> <span class="o">=</span> <span class="n">User</span><span class="p">(</span><span class="n">name</span><span class="o">=</span><span class="s">'wendy'</span><span class="p">)</span>
<span class="n">Session</span><span class="o">.</span><span class="n">commit</span><span class="p">()</span></pre></div>
</div>
</div>
<div class="section" id="sessions-are-again-weak-referencing-by-default">
<h4>Sessions are again Weak Referencing By Default<a class="headerlink" href="#sessions-are-again-weak-referencing-by-default" title="Permalink to this headline">¶</a></h4>
<p>The weak_identity_map flag is now set to <tt class="docutils literal"><span class="pre">True</span></tt> by default
on Session. Instances which are externally deferenced and
fall out of scope are removed from the session
automatically. However, items which have “dirty” changes
present will remain strongly referenced until those changes
are flushed at which case the object reverts to being weakly
referenced (this works for ‘mutable’ types, like picklable
attributes, as well). Setting weak_identity_map to
<tt class="docutils literal"><span class="pre">False</span></tt> restores the old strong-referencing behavior for
those of you using the session like a cache.</p>
</div>
<div class="section" id="auto-transactional-sessions">
<h4>Auto-Transactional Sessions<a class="headerlink" href="#auto-transactional-sessions" title="Permalink to this headline">¶</a></h4>
<p>As you might have noticed above, we are calling <tt class="docutils literal"><span class="pre">commit()</span></tt>
on <tt class="docutils literal"><span class="pre">Session</span></tt>. The flag <tt class="docutils literal"><span class="pre">transactional=True</span></tt> means the
<tt class="docutils literal"><span class="pre">Session</span></tt> is always in a transaction, <tt class="docutils literal"><span class="pre">commit()</span></tt>
persists permanently.</p>
</div>
<div class="section" id="auto-flushing-sessions">
<h4>Auto-Flushing Sessions<a class="headerlink" href="#auto-flushing-sessions" title="Permalink to this headline">¶</a></h4>
<p>Also, <tt class="docutils literal"><span class="pre">autoflush=True</span></tt> means the <tt class="docutils literal"><span class="pre">Session</span></tt> will
<tt class="docutils literal"><span class="pre">flush()</span></tt> before each <tt class="docutils literal"><span class="pre">query</span></tt> as well as when you call
<tt class="docutils literal"><span class="pre">flush()</span></tt> or <tt class="docutils literal"><span class="pre">commit()</span></tt>. So now this will work:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">Session</span> <span class="o">=</span> <span class="n">sessionmaker</span><span class="p">(</span><span class="n">bind</span><span class="o">=</span><span class="n">engine</span><span class="p">,</span> <span class="n">autoflush</span><span class="o">=</span><span class="bp">True</span><span class="p">,</span> <span class="n">transactional</span><span class="o">=</span><span class="bp">True</span><span class="p">)</span>
<span class="n">u</span> <span class="o">=</span> <span class="n">User</span><span class="p">(</span><span class="n">name</span><span class="o">=</span><span class="s">'wendy'</span><span class="p">)</span>
<span class="n">sess</span> <span class="o">=</span> <span class="n">Session</span><span class="p">()</span>
<span class="n">sess</span><span class="o">.</span><span class="n">save</span><span class="p">(</span><span class="n">u</span><span class="p">)</span>
<span class="c"># wendy is flushed, comes right back from a query</span>
<span class="n">wendy</span> <span class="o">=</span> <span class="n">sess</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="n">User</span><span class="p">)</span><span class="o">.</span><span class="n">filter_by</span><span class="p">(</span><span class="n">name</span><span class="o">=</span><span class="s">'wendy'</span><span class="p">)</span><span class="o">.</span><span class="n">one</span><span class="p">()</span></pre></div>
</div>
</div>
<div class="section" id="transactional-methods-moved-onto-sessions">
<h4>Transactional methods moved onto sessions<a class="headerlink" href="#transactional-methods-moved-onto-sessions" title="Permalink to this headline">¶</a></h4>
<p><tt class="docutils literal"><span class="pre">commit()</span></tt> and <tt class="docutils literal"><span class="pre">rollback()</span></tt>, as well as <tt class="docutils literal"><span class="pre">begin()</span></tt> are
now directly on <tt class="docutils literal"><span class="pre">Session</span></tt>. No more need to use
<tt class="docutils literal"><span class="pre">SessionTransaction</span></tt> for anything (it remains in the
background).</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">Session</span> <span class="o">=</span> <span class="n">sessionmaker</span><span class="p">(</span><span class="n">autoflush</span><span class="o">=</span><span class="bp">True</span><span class="p">,</span> <span class="n">transactional</span><span class="o">=</span><span class="bp">False</span><span class="p">)</span>
<span class="n">sess</span> <span class="o">=</span> <span class="n">Session</span><span class="p">()</span>
<span class="n">sess</span><span class="o">.</span><span class="n">begin</span><span class="p">()</span>
<span class="c"># use the session</span>
<span class="n">sess</span><span class="o">.</span><span class="n">commit</span><span class="p">()</span> <span class="c"># commit transaction</span></pre></div>
</div>
<p>Sharing a <tt class="docutils literal"><span class="pre">Session</span></tt> with an enclosing engine-level (i.e.
non-ORM) transaction is easy:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">Session</span> <span class="o">=</span> <span class="n">sessionmaker</span><span class="p">(</span><span class="n">autoflush</span><span class="o">=</span><span class="bp">True</span><span class="p">,</span> <span class="n">transactional</span><span class="o">=</span><span class="bp">False</span><span class="p">)</span>
<span class="n">conn</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">trans</span> <span class="o">=</span> <span class="n">conn</span><span class="o">.</span><span class="n">begin</span><span class="p">()</span>
<span class="n">sess</span> <span class="o">=</span> <span class="n">Session</span><span class="p">(</span><span class="n">bind</span><span class="o">=</span><span class="n">conn</span><span class="p">)</span>
<span class="c"># ... session is transactional</span>
<span class="c"># commit the outermost transaction</span>
<span class="n">trans</span><span class="o">.</span><span class="n">commit</span><span class="p">()</span></pre></div>
</div>
</div>
<div class="section" id="nested-session-transactions-with-savepoint">
<h4>Nested Session Transactions with SAVEPOINT<a class="headerlink" href="#nested-session-transactions-with-savepoint" title="Permalink to this headline">¶</a></h4>
<p>Available at the Engine and ORM level. ORM docs so far:</p>
<p><a class="reference external" href="http://www.sqlalchemy.org/docs/04/session.html#unitofwork_ma">http://www.sqlalchemy.org/docs/04/session.html#unitofwork_ma</a>
naging</p>
</div>
<div class="section" id="two-phase-commit-sessions">
<h4>Two-Phase Commit Sessions<a class="headerlink" href="#two-phase-commit-sessions" title="Permalink to this headline">¶</a></h4>
<p>Available at the Engine and ORM level. ORM docs so far:</p>
<p><a class="reference external" href="http://www.sqlalchemy.org/docs/04/session.html#unitofwork_ma">http://www.sqlalchemy.org/docs/04/session.html#unitofwork_ma</a>
naging</p>
</div>
</div>
<div class="section" id="inheritance">
<h3>Inheritance<a class="headerlink" href="#inheritance" title="Permalink to this headline">¶</a></h3>
<div class="section" id="polymorphic-inheritance-with-no-joins-or-unions">
<h4>Polymorphic Inheritance with No Joins or Unions<a class="headerlink" href="#polymorphic-inheritance-with-no-joins-or-unions" title="Permalink to this headline">¶</a></h4>
<p>New docs for inheritance: <a class="reference external" href="http://www.sqlalchemy.org/docs/04">http://www.sqlalchemy.org/docs/04</a>
/mappers.html#advdatamapping_mapper_inheritance_joined</p>
</div>
<div class="section" id="better-polymorphic-behavior-with-get">
<h4>Better Polymorphic Behavior with <tt class="docutils literal"><span class="pre">get()</span></tt><a class="headerlink" href="#better-polymorphic-behavior-with-get" title="Permalink to this headline">¶</a></h4>
<p>All classes within a joined-table inheritance hierarchy get
an <tt class="docutils literal"><span class="pre">_instance_key</span></tt> using the base class, i.e.
<tt class="docutils literal"><span class="pre">(BaseClass,</span> <span class="pre">(1,</span> <span class="pre">),</span> <span class="pre">None)</span></tt>. That way when you call
<tt class="docutils literal"><span class="pre">get()</span></tt> a <tt class="docutils literal"><span class="pre">Query</span></tt> against the base class, it can locate
subclass instances in the current identity map without
querying the database.</p>
</div>
</div>
<div class="section" id="types">
<h3>Types<a class="headerlink" href="#types" title="Permalink to this headline">¶</a></h3>
<div class="section" id="custom-subclasses-of-sqlalchemy-types-typedecorator">
<h4>Custom Subclasses of <tt class="docutils literal"><span class="pre">sqlalchemy.types.TypeDecorator</span></tt><a class="headerlink" href="#custom-subclasses-of-sqlalchemy-types-typedecorator" title="Permalink to this headline">¶</a></h4>
<p>There is a <a class="reference external" href="http://www.sqlalchemy.org/docs/04/types.html#types_custom">New API</a> for subclassing a TypeDecorator.
Using the 0.3 API causes compilation errors in some cases.</p>
</div>
</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="all-new-deterministic-label-alias-generation">
<h3>All New, Deterministic Label/Alias Generation<a class="headerlink" href="#all-new-deterministic-label-alias-generation" title="Permalink to this headline">¶</a></h3>
<p>All the “anonymous” labels and aliases use a simple
<name>_<number> format now. SQL is much easier to read and
is compatible with plan optimizer caches. Just check out
some of the examples in the tutorials:
<a class="reference external" href="http://www.sqlalchemy.org/docs/04/ormtutorial.html">http://www.sqlalchemy.org/docs/04/ormtutorial.html</a>
<a class="reference external" href="http://www.sqlalchemy.org/docs/04/sqlexpression.html">http://www.sqlalchemy.org/docs/04/sqlexpression.html</a></p>
</div>
<div class="section" id="generative-select-constructs">
<h3>Generative select() Constructs<a class="headerlink" href="#generative-select-constructs" title="Permalink to this headline">¶</a></h3>
<p>This is definitely the way to go with <tt class="docutils literal"><span class="pre">select()</span></tt>. See htt
p://www.sqlalchemy.org/docs/04/sqlexpression.html#sql_transf
orm .</p>
</div>
<div class="section" id="new-operator-system">
<h3>New Operator System<a class="headerlink" href="#new-operator-system" title="Permalink to this headline">¶</a></h3>
<p>SQL operators and more or less every SQL keyword there is
are now abstracted into the compiler layer. They now act
intelligently and are type/backend aware, see: <a class="reference external" href="http://www.sq">http://www.sq</a>
lalchemy.org/docs/04/sqlexpression.html#sql_operators</p>
</div>
<div class="section" id="all-type-keyword-arguments-renamed-to-type">
<h3>All <tt class="docutils literal"><span class="pre">type</span></tt> Keyword Arguments Renamed to <tt class="docutils literal"><span class="pre">type_</span></tt><a class="headerlink" href="#all-type-keyword-arguments-renamed-to-type" title="Permalink to this headline">¶</a></h3>
<p>Just like it says:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">b</span> <span class="o">=</span> <span class="n">bindparam</span><span class="p">(</span><span class="s">'foo'</span><span class="p">,</span> <span class="n">type_</span><span class="o">=</span><span class="n">String</span><span class="p">)</span></pre></div>
</div>
</div>
<div class="section" id="in-function-changed-to-accept-sequence-or-selectable">
<h3>in_ Function Changed to Accept Sequence or Selectable<a class="headerlink" href="#in-function-changed-to-accept-sequence-or-selectable" title="Permalink to this headline">¶</a></h3>
<p>The in_ function now takes a sequence of values or a
selectable as its sole argument. The previous API of passing
in values as positional arguments still works, but is now
deprecated. This means that</p>
<div class="highlight-python"><pre>my_table.select(my_table.c.id.in_(1,2,3)
my_table.select(my_table.c.id.in_(*listOfIds)</pre>
</div>
<p>should be changed to</p>
<div class="highlight-python"><pre>my_table.select(my_table.c.id.in_([1,2,3])
my_table.select(my_table.c.id.in_(listOfIds)</pre>
</div>
</div>
</div>
<div class="section" id="schema-and-reflection">
<h2>Schema and Reflection<a class="headerlink" href="#schema-and-reflection" title="Permalink to this headline">¶</a></h2>
<div class="section" id="metadata-boundmetadata-dynamicmetadata">
<h3><tt class="docutils literal"><span class="pre">MetaData</span></tt>, <tt class="docutils literal"><span class="pre">BoundMetaData</span></tt>, <tt class="docutils literal"><span class="pre">DynamicMetaData</span></tt>...<a class="headerlink" href="#metadata-boundmetadata-dynamicmetadata" title="Permalink to this headline">¶</a></h3>
<p>In the 0.3.x series, <tt class="docutils literal"><span class="pre">BoundMetaData</span></tt> and
<tt class="docutils literal"><span class="pre">DynamicMetaData</span></tt> were deprecated in favor of <tt class="docutils literal"><span class="pre">MetaData</span></tt>
and <tt class="docutils literal"><span class="pre">ThreadLocalMetaData</span></tt>. The older names have been
removed in 0.4. Updating is simple:</p>
<div class="highlight-python"><pre>+-------------------------------------+-------------------------+
|If You Had | Now Use |
+=====================================+=========================+
| ``MetaData`` | ``MetaData`` |
+-------------------------------------+-------------------------+
| ``BoundMetaData`` | ``MetaData`` |
+-------------------------------------+-------------------------+
| ``DynamicMetaData`` (with one | ``MetaData`` |
| engine or threadlocal=False) | |
+-------------------------------------+-------------------------+
| ``DynamicMetaData`` | ``ThreadLocalMetaData`` |
| (with different engines per thread) | |
+-------------------------------------+-------------------------+</pre>
</div>
<p>The seldom-used <tt class="docutils literal"><span class="pre">name</span></tt> parameter to <tt class="docutils literal"><span class="pre">MetaData</span></tt> types has
been removed. The <tt class="docutils literal"><span class="pre">ThreadLocalMetaData</span></tt> constructor now
takes no arguments. Both types can now be bound to an
<tt class="docutils literal"><span class="pre">Engine</span></tt> or a single <tt class="docutils literal"><span class="pre">Connection</span></tt>.</p>
</div>
<div class="section" id="one-step-multi-table-reflection">
<h3>One Step Multi-Table Reflection<a class="headerlink" href="#one-step-multi-table-reflection" title="Permalink to this headline">¶</a></h3>
<p>You can now load table definitions and automatically create
<tt class="docutils literal"><span class="pre">Table</span></tt> objects from an entire database or schema in one
pass:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="n">metadata</span> <span class="o">=</span> <span class="n">MetaData</span><span class="p">(</span><span class="n">myengine</span><span class="p">,</span> <span class="n">reflect</span><span class="o">=</span><span class="bp">True</span><span class="p">)</span>
<span class="gp">>>> </span><span class="n">metadata</span><span class="o">.</span><span class="n">tables</span><span class="o">.</span><span class="n">keys</span><span class="p">()</span>
<span class="go">['table_a', 'table_b', 'table_c', '...']</span></pre></div>
</div>
<p><tt class="docutils literal"><span class="pre">MetaData</span></tt> also gains a <tt class="docutils literal"><span class="pre">.reflect()</span></tt> method enabling
finer control over the loading process, including
specification of a subset of available tables to load.</p>
</div>
</div>
<div class="section" id="sql-execution">
<h2>SQL Execution<a class="headerlink" href="#sql-execution" title="Permalink to this headline">¶</a></h2>
<div class="section" id="engine-connectable-and-bind-to-are-all-now-bind">
<h3><tt class="docutils literal"><span class="pre">engine</span></tt>, <tt class="docutils literal"><span class="pre">connectable</span></tt>, and <tt class="docutils literal"><span class="pre">bind_to</span></tt> are all now <tt class="docutils literal"><span class="pre">bind</span></tt><a class="headerlink" href="#engine-connectable-and-bind-to-are-all-now-bind" title="Permalink to this headline">¶</a></h3>
</div>
<div class="section" id="transactions-nestedtransactions-and-twophasetransactions">
<h3><tt class="docutils literal"><span class="pre">Transactions</span></tt>, <tt class="docutils literal"><span class="pre">NestedTransactions</span></tt> and <tt class="docutils literal"><span class="pre">TwoPhaseTransactions</span></tt><a class="headerlink" href="#transactions-nestedtransactions-and-twophasetransactions" title="Permalink to this headline">¶</a></h3>
</div>
<div class="section" id="connection-pool-events">
<h3>Connection Pool Events<a class="headerlink" href="#connection-pool-events" title="Permalink to this headline">¶</a></h3>
<p>The connection pool now fires events when new DB-API
connections are created, checked out and checked back into
the pool. You can use these to execute session-scoped SQL
setup statements on fresh connections, for example.</p>
</div>
<div class="section" id="oracle-engine-fixed">
<h3>Oracle Engine Fixed<a class="headerlink" href="#oracle-engine-fixed" title="Permalink to this headline">¶</a></h3>
<p>In 0.3.11, there were bugs in the Oracle Engine on how
Primary Keys are handled. These bugs could cause programs
that worked fine with other engines, such as sqlite, to fail
when using the Oracle Engine. In 0.4, the Oracle Engine has
been reworked, fixing these Primary Key problems.</p>
</div>
<div class="section" id="out-parameters-for-oracle">
<h3>Out Parameters for Oracle<a class="headerlink" href="#out-parameters-for-oracle" title="Permalink to this headline">¶</a></h3>
<div class="highlight-python"><div class="highlight"><pre><span class="n">result</span> <span class="o">=</span> <span class="n">engine</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="n">text</span><span class="p">(</span><span class="s">"begin foo(:x, :y, :z); end;"</span><span class="p">,</span> <span class="n">bindparams</span><span class="o">=</span><span class="p">[</span><span class="n">bindparam</span><span class="p">(</span><span class="s">'x'</span><span class="p">,</span> <span class="n">Numeric</span><span class="p">),</span> <span class="n">outparam</span><span class="p">(</span><span class="s">'y'</span><span class="p">,</span> <span class="n">Numeric</span><span class="p">),</span> <span class="n">outparam</span><span class="p">(</span><span class="s">'z'</span><span class="p">,</span> <span class="n">Numeric</span><span class="p">)]),</span> <span class="n">x</span><span class="o">=</span><span class="mi">5</span><span class="p">)</span>
<span class="k">assert</span> <span class="n">result</span><span class="o">.</span><span class="n">out_parameters</span> <span class="o">==</span> <span class="p">{</span><span class="s">'y'</span><span class="p">:</span><span class="mi">10</span><span class="p">,</span> <span class="s">'z'</span><span class="p">:</span><span class="mi">75</span><span class="p">}</span></pre></div>
</div>
</div>
<div class="section" id="connection-bound-metadata-sessions">
<h3>Connection-bound <tt class="docutils literal"><span class="pre">MetaData</span></tt>, <tt class="docutils literal"><span class="pre">Sessions</span></tt><a class="headerlink" href="#connection-bound-metadata-sessions" title="Permalink to this headline">¶</a></h3>
<p><tt class="docutils literal"><span class="pre">MetaData</span></tt> and <tt class="docutils literal"><span class="pre">Session</span></tt> can be explicitly bound to a
connection:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">conn</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">sess</span> <span class="o">=</span> <span class="n">create_session</span><span class="p">(</span><span class="n">bind</span><span class="o">=</span><span class="n">conn</span><span class="p">)</span></pre></div>
</div>
</div>
<div class="section" id="faster-more-foolproof-resultproxy-objects">
<h3>Faster, More Foolproof <tt class="docutils literal"><span class="pre">ResultProxy</span></tt> Objects<a class="headerlink" href="#faster-more-foolproof-resultproxy-objects" title="Permalink to this headline">¶</a></h3>
</div>
</div>
</div>
</div>
</div>
<div id="docs-bottom-navigation" class="docs-navigation-links">
Previous:
<a href="migration_05.html" title="previous chapter">What’s new in SQLAlchemy 0.5?</a>
<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>
|
