1
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
2
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
4
<html xmlns="http://www.w3.org/1999/xhtml">
6
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
11
What’s new in SQLAlchemy 0.4?
13
SQLAlchemy 0.8 Documentation
17
<link rel="stylesheet" href="../_static/pygments.css" type="text/css" />
18
<link rel="stylesheet" href="../_static/docs.css" type="text/css" />
20
<script type="text/javascript">
21
var DOCUMENTATION_OPTIONS = {
24
COLLAPSE_MODINDEX: false,
28
<script type="text/javascript" src="../_static/jquery.js"></script>
29
<script type="text/javascript" src="../_static/underscore.js"></script>
30
<script type="text/javascript" src="../_static/doctools.js"></script>
31
<script type="text/javascript" src="../_static/init.js"></script>
32
<link rel="index" title="Index" href="../genindex.html" />
33
<link rel="search" title="Search" href="../search.html" />
34
<link rel="copyright" title="Copyright" href="../copyright.html" />
35
<link rel="top" title="SQLAlchemy 0.8 Documentation" href="../index.html" />
36
<link rel="up" title="Changes and Migration" href="index.html" />
37
<link rel="prev" title="What’s new in SQLAlchemy 0.5?" href="migration_05.html" />
52
<div id="docs-container">
56
<div id="docs-header">
57
<h1>SQLAlchemy 0.8 Documentation</h1>
59
<div id="docs-search">
61
<form class="search" action="../search.html" method="get">
62
<input type="text" name="q" size="18" /> <input type="submit" value="Search" />
63
<input type="hidden" name="check_keywords" value="yes" />
64
<input type="hidden" name="area" value="default" />
68
<div id="docs-version-header">
69
Release: <span class="version-num">0.8.2</span> | Release Date: July 3, 2013
76
<div id="docs-top-navigation">
77
<div id="docs-top-page-control" class="docs-navigation-links">
80
<a href="migration_05.html" title="previous chapter">What’s new in SQLAlchemy 0.5?</a>
84
<a href="../contents.html">Table of Contents</a> |
85
<a href="../genindex.html">Index</a>
86
| <a href="../_sources/changelog/migration_04.txt">view source
91
<div id="docs-navigation-banner">
92
<a href="../index.html">SQLAlchemy 0.8 Documentation</a>
93
» <a href="index.html" title="Changes and Migration">Changes and Migration</a>
95
What’s new in SQLAlchemy 0.4?
100
What’s new in SQLAlchemy 0.4?
107
<div id="docs-body-container">
109
<div id="docs-sidebar">
110
<h3><a href="../index.html">Table of Contents</a></h3>
112
<li><a class="reference internal" href="#">What’s new in SQLAlchemy 0.4?</a><ul>
113
<li><a class="reference internal" href="#first-things-first">First Things First</a></li>
114
<li><a class="reference internal" href="#module-imports">Module Imports</a></li>
115
<li><a class="reference internal" href="#object-relational-mapping">Object Relational Mapping</a><ul>
116
<li><a class="reference internal" href="#querying">Querying</a><ul>
117
<li><a class="reference internal" href="#new-query-api">New Query API</a></li>
118
<li><a class="reference internal" href="#new-property-based-expression-constructs">New Property-Based Expression Constructs</a></li>
119
<li><a class="reference internal" href="#automatic-join-aliasing">Automatic Join Aliasing</a></li>
120
<li><a class="reference internal" href="#self-referential-queries">Self-referential Queries</a></li>
121
<li><a class="reference internal" href="#query-populate-existing"><tt class="docutils literal"><span class="pre">query.populate_existing()</span></tt></a></li>
124
<li><a class="reference internal" href="#relations">Relations</a><ul>
125
<li><a class="reference internal" href="#sql-clauses-embedded-in-updates-inserts">SQL Clauses Embedded in Updates/Inserts</a></li>
126
<li><a class="reference internal" href="#self-referential-and-cyclical-eager-loading">Self-referential and Cyclical Eager Loading</a></li>
127
<li><a class="reference internal" href="#composite-types">Composite Types</a></li>
128
<li><a class="reference internal" href="#dynamic-loader-relations"><tt class="docutils literal"><span class="pre">dynamic_loader()</span></tt> relations</a></li>
129
<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>
130
<li><a class="reference internal" href="#new-collection-api">New Collection API</a></li>
131
<li><a class="reference internal" href="#mapped-relations-from-external-tables-subqueries">Mapped Relations from External Tables/Subqueries</a></li>
134
<li><a class="reference internal" href="#horizontal-scaling-sharding-api">Horizontal Scaling (Sharding) API</a></li>
135
<li><a class="reference internal" href="#sessions">Sessions</a><ul>
136
<li><a class="reference internal" href="#new-session-create-paradigm-sessioncontext-assignmapper-deprecated">New Session Create Paradigm; SessionContext, assignmapper Deprecated</a></li>
137
<li><a class="reference internal" href="#sessions-are-again-weak-referencing-by-default">Sessions are again Weak Referencing By Default</a></li>
138
<li><a class="reference internal" href="#auto-transactional-sessions">Auto-Transactional Sessions</a></li>
139
<li><a class="reference internal" href="#auto-flushing-sessions">Auto-Flushing Sessions</a></li>
140
<li><a class="reference internal" href="#transactional-methods-moved-onto-sessions">Transactional methods moved onto sessions</a></li>
141
<li><a class="reference internal" href="#nested-session-transactions-with-savepoint">Nested Session Transactions with SAVEPOINT</a></li>
142
<li><a class="reference internal" href="#two-phase-commit-sessions">Two-Phase Commit Sessions</a></li>
145
<li><a class="reference internal" href="#inheritance">Inheritance</a><ul>
146
<li><a class="reference internal" href="#polymorphic-inheritance-with-no-joins-or-unions">Polymorphic Inheritance with No Joins or Unions</a></li>
147
<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>
150
<li><a class="reference internal" href="#types">Types</a><ul>
151
<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>
156
<li><a class="reference internal" href="#sql-expressions">SQL Expressions</a><ul>
157
<li><a class="reference internal" href="#all-new-deterministic-label-alias-generation">All New, Deterministic Label/Alias Generation</a></li>
158
<li><a class="reference internal" href="#generative-select-constructs">Generative select() Constructs</a></li>
159
<li><a class="reference internal" href="#new-operator-system">New Operator System</a></li>
160
<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>
161
<li><a class="reference internal" href="#in-function-changed-to-accept-sequence-or-selectable">in_ Function Changed to Accept Sequence or Selectable</a></li>
164
<li><a class="reference internal" href="#schema-and-reflection">Schema and Reflection</a><ul>
165
<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>
166
<li><a class="reference internal" href="#one-step-multi-table-reflection">One Step Multi-Table Reflection</a></li>
169
<li><a class="reference internal" href="#sql-execution">SQL Execution</a><ul>
170
<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>
171
<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>
172
<li><a class="reference internal" href="#connection-pool-events">Connection Pool Events</a></li>
173
<li><a class="reference internal" href="#oracle-engine-fixed">Oracle Engine Fixed</a></li>
174
<li><a class="reference internal" href="#out-parameters-for-oracle">Out Parameters for Oracle</a></li>
175
<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>
176
<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>
184
<h4>Previous Topic</h4>
186
<a href="migration_05.html" title="previous chapter">What’s new in SQLAlchemy 0.5?</a>
190
<h4>Quick Search</h4>
192
<form class="search" action="../search.html" method="get">
193
<input type="text" name="q" size="18" /> <input type="submit" value="Search" />
194
<input type="hidden" name="check_keywords" value="yes" />
195
<input type="hidden" name="area" value="default" />
201
<div id="docs-body" class="withsidebar" >
203
<div class="section" id="what-s-new-in-sqlalchemy-0-4">
204
<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>
205
<div class="admonition-about-this-document admonition">
206
<p class="first admonition-title">About this Document</p>
207
<p>This document describes changes between SQLAlchemy version 0.3,
208
last released October 14, 2007, and SQLAlchemy version 0.4,
209
last released October 12, 2008.</p>
210
<p class="last">Document date: March 21, 2008</p>
212
<div class="section" id="first-things-first">
213
<h2>First Things First<a class="headerlink" href="#first-things-first" title="Permalink to this headline">¶</a></h2>
214
<p>If you’re using any ORM features, make sure you import from
215
<tt class="docutils literal"><span class="pre">sqlalchemy.orm</span></tt>:</p>
216
<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>
217
<span class="kn">from</span> <span class="nn">sqlalchemy.orm</span> <span class="kn">import</span> <span class="o">*</span></pre></div>
219
<p>Secondly, anywhere you used to say <tt class="docutils literal"><span class="pre">engine=</span></tt>,
220
<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>,
221
<tt class="docutils literal"><span class="pre">metadata.connect()</span></tt>, use <tt class="docutils literal"><span class="pre">bind</span></tt>:</p>
222
<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>
224
<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>
226
<span class="n">meta2</span> <span class="o">=</span> <span class="n">MetaData</span><span class="p">()</span>
227
<span class="n">meta2</span><span class="o">.</span><span class="n">bind</span> <span class="o">=</span> <span class="n">myengine</span>
229
<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>
231
<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>
233
<p>Got those ? Good! You’re now (95%) 0.4 compatible. If
234
you’re using 0.3.10, you can make these changes immediately;
235
they’ll work there too.</p>
237
<div class="section" id="module-imports">
238
<h2>Module Imports<a class="headerlink" href="#module-imports" title="Permalink to this headline">¶</a></h2>
239
<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
240
sqlachemy’s sub-modules into your namespace. Version 0.4 no
241
longer imports sub-modules into the namespace. This may mean
242
you need to add extra imports into your code.</p>
243
<p>In 0.3, this code worked:</p>
244
<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>
246
<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>
247
<span class="k">pass</span></pre></div>
249
<p>In 0.4, one must do:</p>
250
<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>
251
<span class="kn">from</span> <span class="nn">sqlalchemy</span> <span class="kn">import</span> <span class="n">types</span>
253
<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>
254
<span class="k">pass</span></pre></div>
257
<div class="section" id="object-relational-mapping">
258
<h2>Object Relational Mapping<a class="headerlink" href="#object-relational-mapping" title="Permalink to this headline">¶</a></h2>
259
<div class="section" id="querying">
260
<h3>Querying<a class="headerlink" href="#querying" title="Permalink to this headline">¶</a></h3>
261
<div class="section" id="new-query-api">
262
<h4>New Query API<a class="headerlink" href="#new-query-api" title="Permalink to this headline">¶</a></h4>
263
<p>Query is standardized on the generative interface (old
264
interface is still there, just deprecated). While most of
265
the generative interface is available in 0.3, the 0.4 Query
266
has the inner guts to match the generative outside, and has
267
a lot more tricks. All result narrowing is via <tt class="docutils literal"><span class="pre">filter()</span></tt>
268
and <tt class="docutils literal"><span class="pre">filter_by()</span></tt>, limiting/offset is either through array
269
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
270
<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
271
<tt class="docutils literal"><span class="pre">select_from()</span></tt> as well as manually-formed criteria).</p>
272
<p>To avoid deprecation warnings, you must make some changes to
274
<p>User.query.get_by( **kwargs )</p>
275
<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>
277
<p>User.query.select_by( **kwargs )</p>
278
<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>
280
<p>User.query.select()</p>
281
<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>
284
<div class="section" id="new-property-based-expression-constructs">
285
<h4>New Property-Based Expression Constructs<a class="headerlink" href="#new-property-based-expression-constructs" title="Permalink to this headline">¶</a></h4>
286
<p>By far the most palpable difference within the ORM is that
287
you can now construct your query criterion using class-based
288
attributes directly. The ”.c.” prefix is no longer needed
289
when working with mapped classes:</p>
290
<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>
292
<p>While simple column-based comparisons are no big deal, the
293
class attributes have some new “higher level” constructs
294
available, including what was previously only available in
295
<tt class="docutils literal"><span class="pre">filter_by()</span></tt>:</p>
296
<div class="highlight-python"><div class="highlight"><pre><span class="c"># comparison of scalar relations to an instance</span>
297
<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>
299
<span class="c"># return all users who contain a particular address</span>
300
<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>
302
<span class="c"># return all users who *dont* contain the address</span>
303
<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>
305
<span class="c"># return all users who contain a particular address with</span>
306
<span class="c"># the email_address like '%foo%'</span>
307
<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>
309
<span class="c"># same, email address equals 'foo@bar.com'. can fall back to keyword</span>
310
<span class="c"># args for simple comparisons</span>
311
<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>
313
<span class="c"># return all Addresses whose user attribute has the username 'ed'</span>
314
<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>
316
<span class="c"># return all Addresses whose user attribute has the username 'ed'</span>
317
<span class="c"># and an id > 5 (mixing clauses with kwargs)</span>
318
<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>
320
<p>The <tt class="docutils literal"><span class="pre">Column</span></tt> collection remains available on mapped
321
classes in the <tt class="docutils literal"><span class="pre">.c</span></tt> attribute. Note that property-based
322
expressions are only available with mapped properties of
323
mapped classes. <tt class="docutils literal"><span class="pre">.c</span></tt> is still used to access columns in
324
regular tables and selectable objects produced from SQL
327
<div class="section" id="automatic-join-aliasing">
328
<h4>Automatic Join Aliasing<a class="headerlink" href="#automatic-join-aliasing" title="Permalink to this headline">¶</a></h4>
329
<p>We’ve had join() and outerjoin() for a while now:</p>
330
<div class="highlight-python"><pre>session.query(Order).join('items')...</pre>
332
<p>Now you can alias them:</p>
333
<div class="highlight-python"><pre>session.query(Order).join('items', aliased=True).
334
filter(Item.name='item 1').join('items', aliased=True).filter(Item.name=='item 3')</pre>
336
<p>The above will create two joins from orders->items using
337
aliases. the <tt class="docutils literal"><span class="pre">filter()</span></tt> call subsequent to each will
338
adjust its table criterion to that of the alias. To get at
339
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
340
join with an <tt class="docutils literal"><span class="pre">id</span></tt>:</p>
341
<div class="highlight-python"><pre>session.query(Order).join('items', id='j1', aliased=True).
342
filter(Item.name == 'item 1').join('items', aliased=True, id='j2').
343
filter(Item.name == 'item 3').add_entity(Item, id='j1').add_entity(Item, id='j2')</pre>
345
<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>
347
<div class="section" id="self-referential-queries">
348
<h4>Self-referential Queries<a class="headerlink" href="#self-referential-queries" title="Permalink to this headline">¶</a></h4>
349
<p>So query.join() can make aliases now. What does that give
350
us ? Self-referential queries ! Joins can be done without
351
any <tt class="docutils literal"><span class="pre">Alias</span></tt> objects:</p>
352
<div class="highlight-python"><div class="highlight"><pre><span class="c"># standard self-referential TreeNode mapper with backref</span>
353
<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>
354
<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>
355
<span class="p">})</span>
357
<span class="c"># query for node with child containing "bar" two levels deep</span>
358
<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>
360
<p>To add criterion for each table along the way in an aliased
361
join, you can use <tt class="docutils literal"><span class="pre">from_joinpoint</span></tt> to keep joining against
362
the same line of aliases:</p>
363
<div class="highlight-python"><div class="highlight"><pre><span class="c"># search for the treenode along the path "n1/n12/n122"</span>
365
<span class="c"># first find a Node with name="n122"</span>
366
<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>
368
<span class="c"># then join to parent with "n12"</span>
369
<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>
371
<span class="c"># join again to the next parent with 'n1'. use 'from_joinpoint'</span>
372
<span class="c"># so we join from the previous point, instead of joining off the</span>
373
<span class="c"># root table</span>
374
<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>
376
<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>
379
<div class="section" id="query-populate-existing">
380
<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>
381
<p>The eager version of <tt class="docutils literal"><span class="pre">query.load()</span></tt> (or
382
<tt class="docutils literal"><span class="pre">session.refresh()</span></tt>). Every instance loaded from the
383
query, including all eagerly loaded items, get refreshed
384
immediately if already present in the session:</p>
385
<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>
389
<div class="section" id="relations">
390
<h3>Relations<a class="headerlink" href="#relations" title="Permalink to this headline">¶</a></h3>
391
<div class="section" id="sql-clauses-embedded-in-updates-inserts">
392
<h4>SQL Clauses Embedded in Updates/Inserts<a class="headerlink" href="#sql-clauses-embedded-in-updates-inserts" title="Permalink to this headline">¶</a></h4>
393
<p>For inline execution of SQL clauses, embedded right in the
394
UPDATE or INSERT, during a <tt class="docutils literal"><span class="pre">flush()</span></tt>:</p>
395
<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>
397
<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>
399
<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>
401
<p>The column-attribute is set up with a deferred loader after
402
the operation, so that it issues the SQL to load the new
403
value when you next access.</p>
405
<div class="section" id="self-referential-and-cyclical-eager-loading">
406
<h4>Self-referential and Cyclical Eager Loading<a class="headerlink" href="#self-referential-and-cyclical-eager-loading" title="Permalink to this headline">¶</a></h4>
407
<p>Since our alias-fu has improved, <tt class="docutils literal"><span class="pre">relation()</span></tt> can join
408
along the same table *any number of times*; you tell it how
409
deep you want to go. Lets show the self-referential
410
<tt class="docutils literal"><span class="pre">TreeNode</span></tt> more clearly:</p>
411
<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>
412
<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>
413
<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>
414
<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>
416
<span class="k">class</span> <span class="nc">TreeNode</span><span class="p">(</span><span class="nb">object</span><span class="p">):</span>
417
<span class="k">pass</span>
419
<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>
420
<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>
421
<span class="p">})</span></pre></div>
423
<p>So what happens when we say:</p>
424
<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>
426
<p>? A join along aliases, three levels deep off the parent:</p>
427
<div class="highlight-python"><pre>SELECT
428
nodes_3.id AS nodes_3_id, nodes_3.parent_id AS nodes_3_parent_id, nodes_3.name AS nodes_3_name,
429
nodes_2.id AS nodes_2_id, nodes_2.parent_id AS nodes_2_parent_id, nodes_2.name AS nodes_2_name,
430
nodes_1.id AS nodes_1_id, nodes_1.parent_id AS nodes_1_parent_id, nodes_1.name AS nodes_1_name,
431
nodes.id AS nodes_id, nodes.parent_id AS nodes_parent_id, nodes.name AS nodes_name
432
FROM nodes LEFT OUTER JOIN nodes AS nodes_1 ON nodes.id = nodes_1.parent_id
433
LEFT OUTER JOIN nodes AS nodes_2 ON nodes_1.id = nodes_2.parent_id
434
LEFT OUTER JOIN nodes AS nodes_3 ON nodes_2.id = nodes_3.parent_id
435
ORDER BY nodes.oid, nodes_1.oid, nodes_2.oid, nodes_3.oid</pre>
437
<p>Notice the nice clean alias names too. The joining doesn’t
438
care if it’s against the same immediate table or some other
439
object which then cycles back to the beginining. Any kind
440
of chain of eager loads can cycle back onto itself when
441
<tt class="docutils literal"><span class="pre">join_depth</span></tt> is specified. When not present, eager
442
loading automatically stops when it hits a cycle.</p>
444
<div class="section" id="composite-types">
445
<h4>Composite Types<a class="headerlink" href="#composite-types" title="Permalink to this headline">¶</a></h4>
446
<p>This is one from the Hibernate camp. Composite Types let
447
you define a custom datatype that is composed of more than
448
one column (or one column, if you wanted). Lets define a
449
new type, <tt class="docutils literal"><span class="pre">Point</span></tt>. Stores an x/y coordinate:</p>
450
<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>
451
<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>
452
<span class="bp">self</span><span class="o">.</span><span class="n">x</span> <span class="o">=</span> <span class="n">x</span>
453
<span class="bp">self</span><span class="o">.</span><span class="n">y</span> <span class="o">=</span> <span class="n">y</span>
454
<span class="k">def</span> <span class="nf">__composite_values__</span><span class="p">(</span><span class="bp">self</span><span class="p">):</span>
455
<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>
456
<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>
457
<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>
458
<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>
459
<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>
461
<p>The way the <tt class="docutils literal"><span class="pre">Point</span></tt> object is defined is specific to a
462
custom type; constructor takes a list of arguments, and the
463
<tt class="docutils literal"><span class="pre">__composite_values__()</span></tt> method produces a sequence of
464
those arguments. The order will match up to our mapper, as
465
we’ll see in a moment.</p>
466
<p>Let’s create a table of vertices storing two points per row:</p>
467
<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>
468
<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>
469
<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>
470
<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>
471
<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>
472
<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>
473
<span class="p">)</span></pre></div>
475
<p>Then, map it ! We’ll create a <tt class="docutils literal"><span class="pre">Vertex</span></tt> object which
476
stores two <tt class="docutils literal"><span class="pre">Point</span></tt> objects:</p>
477
<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>
478
<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>
479
<span class="bp">self</span><span class="o">.</span><span class="n">start</span> <span class="o">=</span> <span class="n">start</span>
480
<span class="bp">self</span><span class="o">.</span><span class="n">end</span> <span class="o">=</span> <span class="n">end</span>
482
<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>
483
<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>
484
<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>
485
<span class="p">})</span></pre></div>
487
<p>Once you’ve set up your composite type, it’s usable just
488
like any other type:</p>
489
<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>
490
<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>
491
<span class="n">session</span><span class="o">.</span><span class="n">flush</span><span class="p">()</span>
493
<span class="c"># works in queries too</span>
494
<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>
496
<p>If you’d like to define the way the mapped attributes
497
generate SQL clauses when used in expressions, create your
498
own <tt class="docutils literal"><span class="pre">sqlalchemy.orm.PropComparator</span></tt> subclass, defining any
499
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>,
500
etc.), and send it in to <tt class="docutils literal"><span class="pre">composite()</span></tt>. Composite types
501
work as primary keys too, and are usable in <tt class="docutils literal"><span class="pre">query.get()</span></tt>:</p>
502
<div class="highlight-python"><div class="highlight"><pre><span class="c"># a Document class which uses a composite Version</span>
503
<span class="c"># object as primary key</span>
504
<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>
507
<div class="section" id="dynamic-loader-relations">
508
<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>
509
<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
510
all read operations. Write operations are limited to just
511
<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
512
not visible until the session is flushed. This feature is
513
particularly handy with an “autoflushing” session which will
514
flush before each query.</p>
515
<div class="highlight-python"><pre>mapper(Foo, foo_table, properties={
516
'bars':dynamic_loader(Bar, backref='foo', <other relation() opts>)
519
session = create_session(autoflush=True)
520
foo = session.query(Foo).first()
522
foo.bars.append(Bar(name='lala'))
524
for bar in foo.bars.filter(Bar.name=='lala'):
527
session.commit()</pre>
530
<div class="section" id="new-options-undefer-group-eagerload-all">
531
<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>
532
<p>A couple of query options which are handy.
533
<tt class="docutils literal"><span class="pre">undefer_group()</span></tt> marks a whole group of “deferred”
534
columns as undeferred:</p>
535
<div class="highlight-python"><pre>mapper(Class, table, properties={
536
'foo' : deferred(table.c.foo, group='group1'),
537
'bar' : deferred(table.c.bar, group='group1'),
538
'bat' : deferred(table.c.bat, group='group1'),
541
session.query(Class).options(undefer_group('group1')).filter(...).all()</pre>
543
<p>and <tt class="docutils literal"><span class="pre">eagerload_all()</span></tt> sets a chain of attributes to be
544
eager in one pass:</p>
545
<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>
546
<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>
547
<span class="p">})</span>
548
<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>
549
<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>
550
<span class="p">})</span>
551
<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>
553
<span class="c"># eager load bar and bat</span>
554
<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>
557
<div class="section" id="new-collection-api">
558
<h4>New Collection API<a class="headerlink" href="#new-collection-api" title="Permalink to this headline">¶</a></h4>
559
<p>Collections are no longer proxied by an
560
{{{InstrumentedList}}} proxy, and access to members, methods
561
and attributes is direct. Decorators now intercept objects
562
entering and leaving the collection, and it is now possible
563
to easily write a custom collection class that manages its
564
own membership. Flexible decorators also replace the named
565
method interface of custom collections in 0.3, allowing any
566
class to be easily adapted to use as a collection container.</p>
567
<p>Dictionary-based collections are now much easier to use and
568
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
569
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
571
<div class="highlight-python"><div class="highlight"><pre><span class="c"># use a dictionary relation keyed by a column</span>
572
<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>
573
<span class="c"># or named attribute</span>
574
<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>
575
<span class="c"># or any function you like</span>
576
<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>
578
<p>Existing 0.3 <tt class="docutils literal"><span class="pre">dict</span></tt>-like and freeform object derived
579
collection classes will need to be updated for the new API.
580
In most cases this is simply a matter of adding a couple
581
decorators to the class definition.</p>
583
<div class="section" id="mapped-relations-from-external-tables-subqueries">
584
<h4>Mapped Relations from External Tables/Subqueries<a class="headerlink" href="#mapped-relations-from-external-tables-subqueries" title="Permalink to this headline">¶</a></h4>
585
<p>This feature quietly appeared in 0.3 but has been improved
586
in 0.4 thanks to better ability to convert subqueries
587
against a table into subqueries against an alias of that
588
table; this is key for eager loading, aliased joins in
589
queries, etc. It reduces the need to create mappers against
590
select statements when you just need to add some extra
591
columns or subqueries:</p>
592
<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>
593
<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>
594
<span class="s">'numposts'</span><span class="p">:</span> <span class="n">column_property</span><span class="p">(</span>
595
<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>
596
<span class="p">)</span>
597
<span class="p">})</span></pre></div>
599
<p>a typical query looks like:</p>
600
<div class="highlight-python"><pre>SELECT (SELECT count(1) FROM posts WHERE users.id = posts.user_id) AS count,
601
users.firstname || users.lastname AS fullname,
602
users.id AS users_id, users.firstname AS users_firstname, users.lastname AS users_lastname
603
FROM users ORDER BY users.oid</pre>
607
<div class="section" id="horizontal-scaling-sharding-api">
608
<h3>Horizontal Scaling (Sharding) API<a class="headerlink" href="#horizontal-scaling-sharding-api" title="Permalink to this headline">¶</a></h3>
609
<p>[browser:/sqlalchemy/trunk/examples/sharding/attribute_shard
612
<div class="section" id="sessions">
613
<h3>Sessions<a class="headerlink" href="#sessions" title="Permalink to this headline">¶</a></h3>
614
<div class="section" id="new-session-create-paradigm-sessioncontext-assignmapper-deprecated">
615
<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>
616
<p>That’s right, the whole shebang is being replaced with two
617
configurational functions. Using both will produce the most
618
0.1-ish feel we’ve had since 0.1 (i.e., the least amount of
620
<p>Configure your own <tt class="docutils literal"><span class="pre">Session</span></tt> class right where you define
621
your <tt class="docutils literal"><span class="pre">engine</span></tt> (or anywhere):</p>
622
<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>
623
<span class="kn">from</span> <span class="nn">sqlalchemy.orm</span> <span class="kn">import</span> <span class="n">sessionmaker</span>
625
<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>
626
<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>
628
<span class="c"># use the new Session() freely</span>
629
<span class="n">sess</span> <span class="o">=</span> <span class="n">Session</span><span class="p">()</span>
630
<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>
631
<span class="n">sess</span><span class="o">.</span><span class="n">flush</span><span class="p">()</span></pre></div>
633
<p>If you need to post-configure your Session, say with an
634
engine, add it later with <tt class="docutils literal"><span class="pre">configure()</span></tt>:</p>
635
<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>
637
<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>
638
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
639
the new <tt class="docutils literal"><span class="pre">scoped_session()</span></tt> function, which is compatible
640
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>
641
<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>
643
<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>
644
<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>
646
<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>
648
<span class="n">sess</span> <span class="o">=</span> <span class="n">Session</span><span class="p">()</span>
649
<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>
650
<span class="n">sess</span><span class="o">.</span><span class="n">commit</span><span class="p">()</span>
652
<span class="c"># Session constructor is thread-locally scoped. Everyone gets the same</span>
653
<span class="c"># Session in the thread when scope="thread".</span>
654
<span class="n">sess2</span> <span class="o">=</span> <span class="n">Session</span><span class="p">()</span>
655
<span class="k">assert</span> <span class="n">sess</span> <span class="ow">is</span> <span class="n">sess2</span></pre></div>
657
<p>When using a thread-local <tt class="docutils literal"><span class="pre">Session</span></tt>, the returned class
658
has all of <tt class="docutils literal"><span class="pre">Session's</span></tt> interface implemented as
659
classmethods, and “assignmapper“‘s functionality is
660
available using the <tt class="docutils literal"><span class="pre">mapper</span></tt> classmethod. Just like the
661
old <tt class="docutils literal"><span class="pre">objectstore</span></tt> days....</p>
662
<div class="highlight-python"><div class="highlight"><pre><span class="c"># "assignmapper"-like functionality available via ScopedSession.mapper</span>
663
<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>
665
<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>
667
<span class="n">Session</span><span class="o">.</span><span class="n">commit</span><span class="p">()</span></pre></div>
670
<div class="section" id="sessions-are-again-weak-referencing-by-default">
671
<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>
672
<p>The weak_identity_map flag is now set to <tt class="docutils literal"><span class="pre">True</span></tt> by default
673
on Session. Instances which are externally deferenced and
674
fall out of scope are removed from the session
675
automatically. However, items which have “dirty” changes
676
present will remain strongly referenced until those changes
677
are flushed at which case the object reverts to being weakly
678
referenced (this works for ‘mutable’ types, like picklable
679
attributes, as well). Setting weak_identity_map to
680
<tt class="docutils literal"><span class="pre">False</span></tt> restores the old strong-referencing behavior for
681
those of you using the session like a cache.</p>
683
<div class="section" id="auto-transactional-sessions">
684
<h4>Auto-Transactional Sessions<a class="headerlink" href="#auto-transactional-sessions" title="Permalink to this headline">¶</a></h4>
685
<p>As you might have noticed above, we are calling <tt class="docutils literal"><span class="pre">commit()</span></tt>
686
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
687
<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>
688
persists permanently.</p>
690
<div class="section" id="auto-flushing-sessions">
691
<h4>Auto-Flushing Sessions<a class="headerlink" href="#auto-flushing-sessions" title="Permalink to this headline">¶</a></h4>
692
<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
693
<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
694
<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>
695
<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>
697
<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>
699
<span class="n">sess</span> <span class="o">=</span> <span class="n">Session</span><span class="p">()</span>
700
<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>
702
<span class="c"># wendy is flushed, comes right back from a query</span>
703
<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>
706
<div class="section" id="transactional-methods-moved-onto-sessions">
707
<h4>Transactional methods moved onto sessions<a class="headerlink" href="#transactional-methods-moved-onto-sessions" title="Permalink to this headline">¶</a></h4>
708
<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
709
now directly on <tt class="docutils literal"><span class="pre">Session</span></tt>. No more need to use
710
<tt class="docutils literal"><span class="pre">SessionTransaction</span></tt> for anything (it remains in the
712
<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>
714
<span class="n">sess</span> <span class="o">=</span> <span class="n">Session</span><span class="p">()</span>
715
<span class="n">sess</span><span class="o">.</span><span class="n">begin</span><span class="p">()</span>
717
<span class="c"># use the session</span>
719
<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>
721
<p>Sharing a <tt class="docutils literal"><span class="pre">Session</span></tt> with an enclosing engine-level (i.e.
722
non-ORM) transaction is easy:</p>
723
<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>
725
<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>
726
<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>
727
<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>
729
<span class="c"># ... session is transactional</span>
731
<span class="c"># commit the outermost transaction</span>
732
<span class="n">trans</span><span class="o">.</span><span class="n">commit</span><span class="p">()</span></pre></div>
735
<div class="section" id="nested-session-transactions-with-savepoint">
736
<h4>Nested Session Transactions with SAVEPOINT<a class="headerlink" href="#nested-session-transactions-with-savepoint" title="Permalink to this headline">¶</a></h4>
737
<p>Available at the Engine and ORM level. ORM docs so far:</p>
738
<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>
741
<div class="section" id="two-phase-commit-sessions">
742
<h4>Two-Phase Commit Sessions<a class="headerlink" href="#two-phase-commit-sessions" title="Permalink to this headline">¶</a></h4>
743
<p>Available at the Engine and ORM level. ORM docs so far:</p>
744
<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>
748
<div class="section" id="inheritance">
749
<h3>Inheritance<a class="headerlink" href="#inheritance" title="Permalink to this headline">¶</a></h3>
750
<div class="section" id="polymorphic-inheritance-with-no-joins-or-unions">
751
<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>
752
<p>New docs for inheritance: <a class="reference external" href="http://www.sqlalchemy.org/docs/04">http://www.sqlalchemy.org/docs/04</a>
753
/mappers.html#advdatamapping_mapper_inheritance_joined</p>
755
<div class="section" id="better-polymorphic-behavior-with-get">
756
<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>
757
<p>All classes within a joined-table inheritance hierarchy get
758
an <tt class="docutils literal"><span class="pre">_instance_key</span></tt> using the base class, i.e.
759
<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
760
<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
761
subclass instances in the current identity map without
762
querying the database.</p>
765
<div class="section" id="types">
766
<h3>Types<a class="headerlink" href="#types" title="Permalink to this headline">¶</a></h3>
767
<div class="section" id="custom-subclasses-of-sqlalchemy-types-typedecorator">
768
<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>
769
<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.
770
Using the 0.3 API causes compilation errors in some cases.</p>
774
<div class="section" id="sql-expressions">
775
<h2>SQL Expressions<a class="headerlink" href="#sql-expressions" title="Permalink to this headline">¶</a></h2>
776
<div class="section" id="all-new-deterministic-label-alias-generation">
777
<h3>All New, Deterministic Label/Alias Generation<a class="headerlink" href="#all-new-deterministic-label-alias-generation" title="Permalink to this headline">¶</a></h3>
778
<p>All the “anonymous” labels and aliases use a simple
779
<name>_<number> format now. SQL is much easier to read and
780
is compatible with plan optimizer caches. Just check out
781
some of the examples in the tutorials:
782
<a class="reference external" href="http://www.sqlalchemy.org/docs/04/ormtutorial.html">http://www.sqlalchemy.org/docs/04/ormtutorial.html</a>
783
<a class="reference external" href="http://www.sqlalchemy.org/docs/04/sqlexpression.html">http://www.sqlalchemy.org/docs/04/sqlexpression.html</a></p>
785
<div class="section" id="generative-select-constructs">
786
<h3>Generative select() Constructs<a class="headerlink" href="#generative-select-constructs" title="Permalink to this headline">¶</a></h3>
787
<p>This is definitely the way to go with <tt class="docutils literal"><span class="pre">select()</span></tt>. See htt
788
p://www.sqlalchemy.org/docs/04/sqlexpression.html#sql_transf
791
<div class="section" id="new-operator-system">
792
<h3>New Operator System<a class="headerlink" href="#new-operator-system" title="Permalink to this headline">¶</a></h3>
793
<p>SQL operators and more or less every SQL keyword there is
794
are now abstracted into the compiler layer. They now act
795
intelligently and are type/backend aware, see: <a class="reference external" href="http://www.sq">http://www.sq</a>
796
lalchemy.org/docs/04/sqlexpression.html#sql_operators</p>
798
<div class="section" id="all-type-keyword-arguments-renamed-to-type">
799
<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>
800
<p>Just like it says:</p>
801
<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>
804
<div class="section" id="in-function-changed-to-accept-sequence-or-selectable">
805
<h3><a href="#id1"><span class="problematic" id="id2">in_</span></a> 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>
806
<p>The <a href="#id3"><span class="problematic" id="id4">in_</span></a> function now takes a sequence of values or a
807
selectable as its sole argument. The previous API of passing
808
in values as positional arguments still works, but is now
809
deprecated. This means that</p>
810
<div class="highlight-python"><pre>my_table.select(my_table.c.id.in_(1,2,3)
811
my_table.select(my_table.c.id.in_(*listOfIds)</pre>
813
<p>should be changed to</p>
814
<div class="highlight-python"><pre>my_table.select(my_table.c.id.in_([1,2,3])
815
my_table.select(my_table.c.id.in_(listOfIds)</pre>
819
<div class="section" id="schema-and-reflection">
820
<h2>Schema and Reflection<a class="headerlink" href="#schema-and-reflection" title="Permalink to this headline">¶</a></h2>
821
<div class="section" id="metadata-boundmetadata-dynamicmetadata">
822
<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>
823
<p>In the 0.3.x series, <tt class="docutils literal"><span class="pre">BoundMetaData</span></tt> and
824
<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>
825
and <tt class="docutils literal"><span class="pre">ThreadLocalMetaData</span></tt>. The older names have been
826
removed in 0.4. Updating is simple:</p>
827
<div class="highlight-python"><pre>+-------------------------------------+-------------------------+
828
|If You Had | Now Use |
829
+=====================================+=========================+
830
| ``MetaData`` | ``MetaData`` |
831
+-------------------------------------+-------------------------+
832
| ``BoundMetaData`` | ``MetaData`` |
833
+-------------------------------------+-------------------------+
834
| ``DynamicMetaData`` (with one | ``MetaData`` |
835
| engine or threadlocal=False) | |
836
+-------------------------------------+-------------------------+
837
| ``DynamicMetaData`` | ``ThreadLocalMetaData`` |
838
| (with different engines per thread) | |
839
+-------------------------------------+-------------------------+</pre>
841
<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
842
been removed. The <tt class="docutils literal"><span class="pre">ThreadLocalMetaData</span></tt> constructor now
843
takes no arguments. Both types can now be bound to an
844
<tt class="docutils literal"><span class="pre">Engine</span></tt> or a single <tt class="docutils literal"><span class="pre">Connection</span></tt>.</p>
846
<div class="section" id="one-step-multi-table-reflection">
847
<h3>One Step Multi-Table Reflection<a class="headerlink" href="#one-step-multi-table-reflection" title="Permalink to this headline">¶</a></h3>
848
<p>You can now load table definitions and automatically create
849
<tt class="docutils literal"><span class="pre">Table</span></tt> objects from an entire database or schema in one
851
<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>
852
<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>
853
<span class="go">['table_a', 'table_b', 'table_c', '...']</span></pre></div>
855
<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
856
finer control over the loading process, including
857
specification of a subset of available tables to load.</p>
860
<div class="section" id="sql-execution">
861
<h2>SQL Execution<a class="headerlink" href="#sql-execution" title="Permalink to this headline">¶</a></h2>
862
<div class="section" id="engine-connectable-and-bind-to-are-all-now-bind">
863
<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>
865
<div class="section" id="transactions-nestedtransactions-and-twophasetransactions">
866
<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>
868
<div class="section" id="connection-pool-events">
869
<h3>Connection Pool Events<a class="headerlink" href="#connection-pool-events" title="Permalink to this headline">¶</a></h3>
870
<p>The connection pool now fires events when new DB-API
871
connections are created, checked out and checked back into
872
the pool. You can use these to execute session-scoped SQL
873
setup statements on fresh connections, for example.</p>
875
<div class="section" id="oracle-engine-fixed">
876
<h3>Oracle Engine Fixed<a class="headerlink" href="#oracle-engine-fixed" title="Permalink to this headline">¶</a></h3>
877
<p>In 0.3.11, there were bugs in the Oracle Engine on how
878
Primary Keys are handled. These bugs could cause programs
879
that worked fine with other engines, such as sqlite, to fail
880
when using the Oracle Engine. In 0.4, the Oracle Engine has
881
been reworked, fixing these Primary Key problems.</p>
883
<div class="section" id="out-parameters-for-oracle">
884
<h3>Out Parameters for Oracle<a class="headerlink" href="#out-parameters-for-oracle" title="Permalink to this headline">¶</a></h3>
885
<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>
886
<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>
889
<div class="section" id="connection-bound-metadata-sessions">
890
<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>
891
<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
893
<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>
894
<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>
897
<div class="section" id="faster-more-foolproof-resultproxy-objects">
898
<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>
907
<div id="docs-bottom-navigation" class="docs-navigation-links">
909
<a href="migration_05.html" title="previous chapter">What’s new in SQLAlchemy 0.5?</a>
911
<div id="docs-copyright">
912
© <a href="../copyright.html">Copyright</a> 2007-2013, the SQLAlchemy authors and contributors.
913
Created using <a href="http://sphinx.pocoo.org/">Sphinx</a> 1.1.3.