File: //usr/share/doc/python-sqlalchemy-0.9.8/doc/changelog/migration_04.html
<!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>