<!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.9?
—
SQLAlchemy 1.2 Documentation
</title>
<!-- begin iterate through site-imported + 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/changelog.css" type="text/css" />
<link rel="stylesheet" href="../_static/sphinx_paramlinks.css" type="text/css" />
<!-- end iterate through site-imported + sphinx environment css_files -->
<!-- begin layout.mako headers -->
<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 1.2 Documentation" href="../index.html" />
<link rel="up" title="Changes and Migration" href="index.html" />
<link rel="next" title="What’s New in SQLAlchemy 0.8?" href="migration_08.html" />
<link rel="prev" title="What’s New in SQLAlchemy 1.0?" href="migration_10.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">1.2.19</span>
| Release Date: April 15, 2019
</div>
<h1>SQLAlchemy 1.2 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 1.2 Documentation</a></h3>
<p id="sidebar-topnav">
<a href="../contents.html">Contents</a> |
<a href="../genindex.html">Index</a>
</p>
<div id="sidebar-search">
<form class="search" action="../search.html" method="get">
<label>
Search terms:
<input type="text" placeholder="search..." name="q" size="12" />
</label>
<input type="hidden" name="check_keywords" value="yes" />
<input type="hidden" name="area" value="default" />
</form>
</div>
</div>
<div id="docs-sidebar">
<div id="sidebar-banner">
</div>
<div id="docs-sidebar-inner">
<h3>
<a href="index.html" title="Changes and Migration">Changes and Migration</a>
</h3>
<ul>
<li><span class="link-container"><a class="reference external" href="migration_12.html">What’s New in SQLAlchemy 1.2?</a></span></li>
<li><span class="link-container"><a class="reference external" href="changelog_12.html">1.2 Changelog</a></span></li>
<li><span class="link-container"><a class="reference external" href="changelog_11.html">1.1 Changelog</a></span></li>
<li><span class="link-container"><a class="reference external" href="changelog_10.html">1.0 Changelog</a></span></li>
<li><span class="link-container"><a class="reference external" href="changelog_09.html">0.9 Changelog</a></span></li>
<li><span class="link-container"><a class="reference external" href="changelog_08.html">0.8 Changelog</a></span></li>
<li><span class="link-container"><a class="reference external" href="changelog_07.html">0.7 Changelog</a></span></li>
<li><span class="link-container"><a class="reference external" href="changelog_06.html">0.6 Changelog</a></span></li>
<li><span class="link-container"><a class="reference external" href="changelog_05.html">0.5 Changelog</a></span></li>
<li><span class="link-container"><a class="reference external" href="changelog_04.html">0.4 Changelog</a></span></li>
<li><span class="link-container"><a class="reference external" href="changelog_03.html">0.3 Changelog</a></span></li>
<li><span class="link-container"><a class="reference external" href="changelog_02.html">0.2 Changelog</a></span></li>
<li><span class="link-container"><a class="reference external" href="changelog_01.html">0.1 Changelog</a></span></li>
<li><span class="link-container"><a class="reference external" href="migration_11.html">What’s New in SQLAlchemy 1.1?</a></span></li>
<li><span class="link-container"><a class="reference external" href="migration_10.html">What’s New in SQLAlchemy 1.0?</a></span></li>
<li class="selected"><span class="link-container"><strong>What’s New in SQLAlchemy 0.9?</strong><a class="paramlink headerlink reference internal" href="#">¶</a></span><ul>
<li><span class="link-container"><a class="reference external" href="#introduction">Introduction</a></span></li>
<li><span class="link-container"><a class="reference external" href="#platform-support">Platform Support</a></span><ul>
<li><span class="link-container"><a class="reference external" href="#targeting-python-2-6-and-up-now-python-3-without-2to3">Targeting Python 2.6 and Up Now, Python 3 without 2to3</a></span></li>
<li><span class="link-container"><a class="reference external" href="#c-extensions-supported-on-python-3">C Extensions Supported on Python 3</a></span></li>
</ul>
</li>
<li><span class="link-container"><a class="reference external" href="#behavioral-changes-orm">Behavioral Changes - ORM</a></span><ul>
<li><span class="link-container"><a class="reference external" href="#composite-attributes-are-now-returned-as-their-object-form-when-queried-on-a-per-attribute-basis">Composite attributes are now returned as their object form when queried on a per-attribute basis</a></span></li>
<li><span class="link-container"><a class="reference external" href="#query-select-from-no-longer-applies-the-clause-to-corresponding-entities"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Query.select_from()</span></code> no longer applies the clause to corresponding entities</a></span></li>
<li><span class="link-container"><a class="reference external" href="#viewonly-true-on-relationship-prevents-history-from-taking-effect"><code class="docutils literal notranslate"><span class="pre">viewonly=True</span></code> on <code class="docutils literal notranslate"><span class="pre">relationship()</span></code> prevents history from taking effect</a></span></li>
<li><span class="link-container"><a class="reference external" href="#association-proxy-sql-expression-improvements-and-fixes">Association Proxy SQL Expression Improvements and Fixes</a></span></li>
<li><span class="link-container"><a class="reference external" href="#association-proxy-missing-scalar-returns-none">Association Proxy Missing Scalar returns None</a></span></li>
<li><span class="link-container"><a class="reference external" href="#attributes-get-history-will-query-from-the-db-by-default-if-value-not-present">attributes.get_history() will query from the DB by default if value not present</a></span></li>
</ul>
</li>
<li><span class="link-container"><a class="reference external" href="#behavioral-changes-core">Behavioral Changes - Core</a></span><ul>
<li><span class="link-container"><a class="reference external" href="#type-objects-no-longer-accept-ignored-keyword-arguments">Type objects no longer accept ignored keyword arguments</a></span></li>
<li><span class="link-container"><a class="reference external" href="#none-can-no-longer-be-used-as-a-partial-and-constructor"><code class="docutils literal notranslate"><span class="pre">None</span></code> can no longer be used as a “partial AND” constructor</a></span></li>
<li><span class="link-container"><a class="reference external" href="#the-password-portion-of-a-create-engine-no-longer-considers-the-sign-as-an-encoded-space">The “password” portion of a <code class="docutils literal notranslate"><span class="pre">create_engine()</span></code> no longer considers the <code class="docutils literal notranslate"><span class="pre">+</span></code> sign as an encoded space</a></span></li>
<li><span class="link-container"><a class="reference external" href="#the-precedence-rules-for-collate-have-been-changed">The precedence rules for COLLATE have been changed</a></span></li>
<li><span class="link-container"><a class="reference external" href="#postgresql-create-type-x-as-enum-now-applies-quoting-to-values">PostgreSQL CREATE TYPE <x> AS ENUM now applies quoting to values</a></span></li>
</ul>
</li>
<li><span class="link-container"><a class="reference external" href="#new-features">New Features</a></span><ul>
<li><span class="link-container"><a class="reference external" href="#event-removal-api">Event Removal API</a></span></li>
<li><span class="link-container"><a class="reference external" href="#new-query-options-api-load-only-option">New Query Options API; <code class="docutils literal notranslate"><span class="pre">load_only()</span></code> option</a></span><ul>
<li><span class="link-container"><a class="reference external" href="#the-load-class">The Load Class</a></span></li>
<li><span class="link-container"><a class="reference external" href="#load-only">Load Only</a></span></li>
<li><span class="link-container"><a class="reference external" href="#class-specific-wildcards">Class-specific Wildcards</a></span></li>
</ul>
</li>
<li><span class="link-container"><a class="reference external" href="#new-text-capabilities">New <code class="docutils literal notranslate"><span class="pre">text()</span></code> Capabilities</a></span></li>
<li><span class="link-container"><a class="reference external" href="#insert-from-select">INSERT from SELECT</a></span></li>
<li><span class="link-container"><a class="reference external" href="#new-for-update-support-on-select-query">New FOR UPDATE support on <code class="docutils literal notranslate"><span class="pre">select()</span></code>, <code class="docutils literal notranslate"><span class="pre">Query()</span></code></a></span></li>
<li><span class="link-container"><a class="reference external" href="#floating-point-string-conversion-precision-configurable-for-native-floating-point-types">Floating Point String-Conversion Precision Configurable for Native Floating Point Types</a></span></li>
<li><span class="link-container"><a class="reference external" href="#column-bundles-for-orm-queries">Column Bundles for ORM queries</a></span></li>
<li><span class="link-container"><a class="reference external" href="#server-side-version-counting">Server Side Version Counting</a></span></li>
<li><span class="link-container"><a class="reference external" href="#include-backrefs-false-option-for-validates"><code class="docutils literal notranslate"><span class="pre">include_backrefs=False</span></code> option for <code class="docutils literal notranslate"><span class="pre">@validates</span></code></a></span></li>
<li><span class="link-container"><a class="reference external" href="#postgresql-json-type">PostgreSQL JSON Type</a></span></li>
<li><span class="link-container"><a class="reference external" href="#automap-extension">Automap Extension</a></span></li>
</ul>
</li>
<li><span class="link-container"><a class="reference external" href="#behavioral-improvements">Behavioral Improvements</a></span><ul>
<li><span class="link-container"><a class="reference external" href="#many-join-and-left-outer-join-expressions-will-no-longer-be-wrapped-in-select-from-as-anon-1">Many JOIN and LEFT OUTER JOIN expressions will no longer be wrapped in (SELECT * FROM ..) AS ANON_1</a></span></li>
<li><span class="link-container"><a class="reference external" href="#right-nested-inner-joins-available-in-joined-eager-loads">Right-nested inner joins available in joined eager loads</a></span></li>
<li><span class="link-container"><a class="reference external" href="#orm-can-efficiently-fetch-just-generated-insert-update-defaults-using-returning">ORM can efficiently fetch just-generated INSERT/UPDATE defaults using RETURNING</a></span></li>
<li><span class="link-container"><a class="reference external" href="#subquery-eager-loading-will-apply-distinct-to-the-innermost-select-for-some-queries">Subquery Eager Loading will apply DISTINCT to the innermost SELECT for some queries</a></span></li>
<li><span class="link-container"><a class="reference external" href="#backref-handlers-can-now-propagate-more-than-one-level-deep">Backref handlers can now propagate more than one level deep</a></span></li>
<li><span class="link-container"><a class="reference external" href="#the-typing-system-now-handles-the-task-of-rendering-literal-bind-values">The typing system now handles the task of rendering “literal bind” values</a></span></li>
<li><span class="link-container"><a class="reference external" href="#schema-identifiers-now-carry-along-their-own-quoting-information">Schema identifiers now carry along their own quoting information</a></span></li>
<li><span class="link-container"><a class="reference external" href="#improved-rendering-of-boolean-constants-null-constants-conjunctions">Improved rendering of Boolean constants, NULL constants, conjunctions</a></span></li>
<li><span class="link-container"><a class="reference external" href="#label-constructs-can-now-render-as-their-name-alone-in-an-order-by">Label constructs can now render as their name alone in an ORDER BY</a></span></li>
<li><span class="link-container"><a class="reference external" href="#rowproxy-now-has-tuple-sorting-behavior"><code class="docutils literal notranslate"><span class="pre">RowProxy</span></code> now has tuple-sorting behavior</a></span></li>
<li><span class="link-container"><a class="reference external" href="#a-bindparam-construct-with-no-type-gets-upgraded-via-copy-when-a-type-is-available">A bindparam() construct with no type gets upgraded via copy when a type is available</a></span></li>
<li><span class="link-container"><a class="reference external" href="#columns-can-reliably-get-their-type-from-a-column-referred-to-via-foreignkey">Columns can reliably get their type from a column referred to via ForeignKey</a></span></li>
</ul>
</li>
<li><span class="link-container"><a class="reference external" href="#dialect-changes">Dialect Changes</a></span><ul>
<li><span class="link-container"><a class="reference external" href="#firebird-fdb-is-now-the-default-firebird-dialect">Firebird <code class="docutils literal notranslate"><span class="pre">fdb</span></code> is now the default Firebird dialect.</a></span></li>
<li><span class="link-container"><a class="reference external" href="#firebird-fdb-and-kinterbasdb-set-retaining-false-by-default">Firebird <code class="docutils literal notranslate"><span class="pre">fdb</span></code> and <code class="docutils literal notranslate"><span class="pre">kinterbasdb</span></code> set <code class="docutils literal notranslate"><span class="pre">retaining=False</span></code> by default</a></span></li>
</ul>
</li>
</ul>
</li>
<li><span class="link-container"><a class="reference external" href="migration_08.html">What’s New in SQLAlchemy 0.8?</a></span></li>
<li><span class="link-container"><a class="reference external" href="migration_07.html">What’s New in SQLAlchemy 0.7?</a></span></li>
<li><span class="link-container"><a class="reference external" href="migration_06.html">What’s New in SQLAlchemy 0.6?</a></span></li>
<li><span class="link-container"><a class="reference external" href="migration_05.html">What’s new in SQLAlchemy 0.5?</a></span></li>
<li><span class="link-container"><a class="reference external" href="migration_04.html">What’s new in SQLAlchemy 0.4?</a></span></li>
</ul>
</div>
</div>
</div>
<div id="docs-body" class="withsidebar" >
<div class="section" id="what-s-new-in-sqlalchemy-0-9">
<h1>What’s New in SQLAlchemy 0.9?<a class="headerlink" href="#what-s-new-in-sqlalchemy-0-9" title="Permalink to this headline">¶</a></h1>
<div class="admonition-about-this-document admonition">
<p class="admonition-title">About this Document</p>
<p>This document describes changes between SQLAlchemy version 0.8,
undergoing maintenance releases as of May, 2013,
and SQLAlchemy version 0.9, which had its first production
release on December 30, 2013.</p>
<p>Document last updated: June 10, 2015</p>
</div>
<div class="section" id="introduction">
<h2>Introduction<a class="headerlink" href="#introduction" title="Permalink to this headline">¶</a></h2>
<p>This guide introduces what’s new in SQLAlchemy version 0.9,
and also documents changes which affect users migrating
their applications from the 0.8 series of SQLAlchemy to 0.9.</p>
<p>Please carefully review
<a class="reference internal" href="#behavioral-changes-orm-09"><span class="std std-ref">Behavioral Changes - ORM</span></a> and <a class="reference internal" href="#behavioral-changes-core-09"><span class="std std-ref">Behavioral Changes - Core</span></a> for
potentially backwards-incompatible changes.</p>
</div>
<div class="section" id="platform-support">
<h2>Platform Support<a class="headerlink" href="#platform-support" title="Permalink to this headline">¶</a></h2>
<div class="section" id="targeting-python-2-6-and-up-now-python-3-without-2to3">
<h3>Targeting Python 2.6 and Up Now, Python 3 without 2to3<a class="headerlink" href="#targeting-python-2-6-and-up-now-python-3-without-2to3" title="Permalink to this headline">¶</a></h3>
<p>The first achievement of the 0.9 release is to remove the dependency
on the 2to3 tool for Python 3 compatibility. To make this
more straightforward, the lowest Python release targeted now
is 2.6, which features a wide degree of cross-compatibility with
Python 3. All SQLAlchemy modules and unit tests are now interpreted
equally well with any Python interpreter from 2.6 forward, including
the 3.1 and 3.2 interpreters.</p>
<p><a class="reference external" href="http://www.sqlalchemy.org/trac/ticket/2671">#2671</a></p>
</div>
<div class="section" id="c-extensions-supported-on-python-3">
<h3>C Extensions Supported on Python 3<a class="headerlink" href="#c-extensions-supported-on-python-3" title="Permalink to this headline">¶</a></h3>
<p>The C extensions have been ported to support Python 3 and now build
in both Python 2 and Python 3 environments.</p>
<p><a class="reference external" href="http://www.sqlalchemy.org/trac/ticket/2161">#2161</a></p>
</div>
</div>
<div class="section" id="behavioral-changes-orm">
<span id="behavioral-changes-orm-09"></span><h2>Behavioral Changes - ORM<a class="headerlink" href="#behavioral-changes-orm" title="Permalink to this headline">¶</a></h2>
<div class="section" id="composite-attributes-are-now-returned-as-their-object-form-when-queried-on-a-per-attribute-basis">
<span id="migration-2824"></span><h3>Composite attributes are now returned as their object form when queried on a per-attribute basis<a class="headerlink" href="#composite-attributes-are-now-returned-as-their-object-form-when-queried-on-a-per-attribute-basis" title="Permalink to this headline">¶</a></h3>
<p>Using a <a class="reference internal" href="../orm/query.html#sqlalchemy.orm.query.Query" title="sqlalchemy.orm.query.Query"><code class="xref py py-class docutils literal notranslate"><span class="pre">Query</span></code></a> in conjunction with a composite attribute now returns the object
type maintained by that composite, rather than being broken out into individual
columns. Using the mapping setup at <a class="reference internal" href="../orm/composites.html#mapper-composite"><span class="std std-ref">Composite Column Types</span></a>:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="gp">>>> </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="o">.</span><span class="n">start</span><span class="p">,</span> <span class="n">Vertex</span><span class="o">.</span><span class="n">end</span><span class="p">)</span><span class="o">.</span>\
<span class="gp">... </span> <span class="nb">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><span class="o">.</span><span class="n">all</span><span class="p">()</span>
<span class="go">[(Point(x=3, y=4), Point(x=5, y=6))]</span></pre></div>
</div>
<p>This change is backwards-incompatible with code that expects the individual attribute
to be expanded into individual columns. To get that behavior, use the <code class="docutils literal notranslate"><span class="pre">.clauses</span></code>
accessor:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="gp">>>> </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="o">.</span><span class="n">start</span><span class="o">.</span><span class="n">clauses</span><span class="p">,</span> <span class="n">Vertex</span><span class="o">.</span><span class="n">end</span><span class="o">.</span><span class="n">clauses</span><span class="p">)</span><span class="o">.</span>\
<span class="gp">... </span> <span class="nb">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><span class="o">.</span><span class="n">all</span><span class="p">()</span>
<span class="go">[(3, 4, 5, 6)]</span></pre></div>
</div>
<div class="admonition seealso">
<p class="admonition-title">See also</p>
<p><a class="reference internal" href="#change-2824"><span class="std std-ref">Column Bundles for ORM queries</span></a></p>
</div>
<p><a class="reference external" href="http://www.sqlalchemy.org/trac/ticket/2824">#2824</a></p>
</div>
<div class="section" id="query-select-from-no-longer-applies-the-clause-to-corresponding-entities">
<span id="migration-2736"></span><h3><a class="reference internal" href="../orm/query.html#sqlalchemy.orm.query.Query.select_from" title="sqlalchemy.orm.query.Query.select_from"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Query.select_from()</span></code></a> no longer applies the clause to corresponding entities<a class="headerlink" href="#query-select-from-no-longer-applies-the-clause-to-corresponding-entities" title="Permalink to this headline">¶</a></h3>
<p>The <a class="reference internal" href="../orm/query.html#sqlalchemy.orm.query.Query.select_from" title="sqlalchemy.orm.query.Query.select_from"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Query.select_from()</span></code></a> method has been popularized in recent versions
as a means of controlling the first thing that a <a class="reference internal" href="../orm/query.html#sqlalchemy.orm.query.Query" title="sqlalchemy.orm.query.Query"><code class="xref py py-class docutils literal notranslate"><span class="pre">Query</span></code></a> object
“selects from”, typically for the purposes of controlling how a JOIN will
render.</p>
<p>Consider the following example against the usual <code class="docutils literal notranslate"><span class="pre">User</span></code> mapping:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">select_stmt</span> <span class="o">=</span> <span class="n">select</span><span class="p">([</span><span class="n">User</span><span class="p">])</span><span class="o">.</span><span class="n">where</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">7</span><span class="p">)</span><span class="o">.</span><span class="n">alias</span><span class="p">()</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">User</span><span class="p">)</span><span class="o">.</span>\
<span class="n">join</span><span class="p">(</span><span class="n">select_stmt</span><span class="p">,</span> <span class="n">User</span><span class="o">.</span><span class="n">id</span> <span class="o">==</span> <span class="n">select_stmt</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">id</span><span class="p">)</span><span class="o">.</span>\
<span class="nb">filter</span><span class="p">(</span><span class="n">User</span><span class="o">.</span><span class="n">name</span> <span class="o">==</span> <span class="s1">'ed'</span><span class="p">)</span></pre></div>
</div>
<p>The above statement predictably renders SQL like the following:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">SELECT</span> <span class="s2">"user"</span><span class="o">.</span><span class="n">id</span> <span class="n">AS</span> <span class="n">user_id</span><span class="p">,</span> <span class="s2">"user"</span><span class="o">.</span><span class="n">name</span> <span class="n">AS</span> <span class="n">user_name</span>
<span class="n">FROM</span> <span class="s2">"user"</span> <span class="n">JOIN</span> <span class="p">(</span><span class="n">SELECT</span> <span class="s2">"user"</span><span class="o">.</span><span class="n">id</span> <span class="n">AS</span> <span class="nb">id</span><span class="p">,</span> <span class="s2">"user"</span><span class="o">.</span><span class="n">name</span> <span class="n">AS</span> <span class="n">name</span>
<span class="n">FROM</span> <span class="s2">"user"</span>
<span class="n">WHERE</span> <span class="s2">"user"</span><span class="o">.</span><span class="n">id</span> <span class="o">=</span> <span class="p">:</span><span class="n">id_1</span><span class="p">)</span> <span class="n">AS</span> <span class="n">anon_1</span> <span class="n">ON</span> <span class="s2">"user"</span><span class="o">.</span><span class="n">id</span> <span class="o">=</span> <span class="n">anon_1</span><span class="o">.</span><span class="n">id</span>
<span class="n">WHERE</span> <span class="s2">"user"</span><span class="o">.</span><span class="n">name</span> <span class="o">=</span> <span class="p">:</span><span class="n">name_1</span></pre></div>
</div>
<p>If we wanted to reverse the order of the left and right elements of the
JOIN, the documentation would lead us to believe we could use
<a class="reference internal" href="../orm/query.html#sqlalchemy.orm.query.Query.select_from" title="sqlalchemy.orm.query.Query.select_from"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Query.select_from()</span></code></a> to do so:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></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">User</span><span class="p">)</span><span class="o">.</span>\
<span class="n">select_from</span><span class="p">(</span><span class="n">select_stmt</span><span class="p">)</span><span class="o">.</span>\
<span class="n">join</span><span class="p">(</span><span class="n">User</span><span class="p">,</span> <span class="n">User</span><span class="o">.</span><span class="n">id</span> <span class="o">==</span> <span class="n">select_stmt</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">id</span><span class="p">)</span><span class="o">.</span>\
<span class="nb">filter</span><span class="p">(</span><span class="n">User</span><span class="o">.</span><span class="n">name</span> <span class="o">==</span> <span class="s1">'ed'</span><span class="p">)</span></pre></div>
</div>
<p>However, in version 0.8 and earlier, the above use of <a class="reference internal" href="../orm/query.html#sqlalchemy.orm.query.Query.select_from" title="sqlalchemy.orm.query.Query.select_from"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Query.select_from()</span></code></a>
would apply the <code class="docutils literal notranslate"><span class="pre">select_stmt</span></code> to <strong>replace</strong> the <code class="docutils literal notranslate"><span class="pre">User</span></code> entity, as it
selects from the <code class="docutils literal notranslate"><span class="pre">user</span></code> table which is compatible with <code class="docutils literal notranslate"><span class="pre">User</span></code>:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="o">--</span> <span class="n">SQLAlchemy</span> <span class="mf">0.8</span> <span class="ow">and</span> <span class="n">earlier</span><span class="o">...</span>
<span class="n">SELECT</span> <span class="n">anon_1</span><span class="o">.</span><span class="n">id</span> <span class="n">AS</span> <span class="n">anon_1_id</span><span class="p">,</span> <span class="n">anon_1</span><span class="o">.</span><span class="n">name</span> <span class="n">AS</span> <span class="n">anon_1_name</span>
<span class="n">FROM</span> <span class="p">(</span><span class="n">SELECT</span> <span class="s2">"user"</span><span class="o">.</span><span class="n">id</span> <span class="n">AS</span> <span class="nb">id</span><span class="p">,</span> <span class="s2">"user"</span><span class="o">.</span><span class="n">name</span> <span class="n">AS</span> <span class="n">name</span>
<span class="n">FROM</span> <span class="s2">"user"</span>
<span class="n">WHERE</span> <span class="s2">"user"</span><span class="o">.</span><span class="n">id</span> <span class="o">=</span> <span class="p">:</span><span class="n">id_1</span><span class="p">)</span> <span class="n">AS</span> <span class="n">anon_1</span> <span class="n">JOIN</span> <span class="s2">"user"</span> <span class="n">ON</span> <span class="n">anon_1</span><span class="o">.</span><span class="n">id</span> <span class="o">=</span> <span class="n">anon_1</span><span class="o">.</span><span class="n">id</span>
<span class="n">WHERE</span> <span class="n">anon_1</span><span class="o">.</span><span class="n">name</span> <span class="o">=</span> <span class="p">:</span><span class="n">name_1</span></pre></div>
</div>
<p>The above statement is a mess, the ON clause refers <code class="docutils literal notranslate"><span class="pre">anon_1.id</span> <span class="pre">=</span> <span class="pre">anon_1.id</span></code>,
our WHERE clause has been replaced with <code class="docutils literal notranslate"><span class="pre">anon_1</span></code> as well.</p>
<p>This behavior is quite intentional, but has a different use case from that
which has become popular for <a class="reference internal" href="../orm/query.html#sqlalchemy.orm.query.Query.select_from" title="sqlalchemy.orm.query.Query.select_from"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Query.select_from()</span></code></a>. The above behavior
is now available by a new method known as <a class="reference internal" href="../orm/query.html#sqlalchemy.orm.query.Query.select_entity_from" title="sqlalchemy.orm.query.Query.select_entity_from"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Query.select_entity_from()</span></code></a>.
This is a lesser used behavior that in modern SQLAlchemy is roughly equivalent
to selecting from a customized <a class="reference internal" href="../orm/query.html#sqlalchemy.orm.aliased" title="sqlalchemy.orm.aliased"><code class="xref py py-func docutils literal notranslate"><span class="pre">aliased()</span></code></a> construct:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">select_stmt</span> <span class="o">=</span> <span class="n">select</span><span class="p">([</span><span class="n">User</span><span class="p">])</span><span class="o">.</span><span class="n">where</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">7</span><span class="p">)</span>
<span class="n">user_from_stmt</span> <span class="o">=</span> <span class="n">aliased</span><span class="p">(</span><span class="n">User</span><span class="p">,</span> <span class="n">select_stmt</span><span class="o">.</span><span class="n">alias</span><span class="p">())</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">user_from_stmt</span><span class="p">)</span><span class="o">.</span><span class="n">filter</span><span class="p">(</span><span class="n">user_from_stmt</span><span class="o">.</span><span class="n">name</span> <span class="o">==</span> <span class="s1">'ed'</span><span class="p">)</span></pre></div>
</div>
<p>So with SQLAlchemy 0.9, our query that selects from <code class="docutils literal notranslate"><span class="pre">select_stmt</span></code> produces
the SQL we expect:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="o">--</span> <span class="n">SQLAlchemy</span> <span class="mf">0.9</span>
<span class="n">SELECT</span> <span class="s2">"user"</span><span class="o">.</span><span class="n">id</span> <span class="n">AS</span> <span class="n">user_id</span><span class="p">,</span> <span class="s2">"user"</span><span class="o">.</span><span class="n">name</span> <span class="n">AS</span> <span class="n">user_name</span>
<span class="n">FROM</span> <span class="p">(</span><span class="n">SELECT</span> <span class="s2">"user"</span><span class="o">.</span><span class="n">id</span> <span class="n">AS</span> <span class="nb">id</span><span class="p">,</span> <span class="s2">"user"</span><span class="o">.</span><span class="n">name</span> <span class="n">AS</span> <span class="n">name</span>
<span class="n">FROM</span> <span class="s2">"user"</span>
<span class="n">WHERE</span> <span class="s2">"user"</span><span class="o">.</span><span class="n">id</span> <span class="o">=</span> <span class="p">:</span><span class="n">id_1</span><span class="p">)</span> <span class="n">AS</span> <span class="n">anon_1</span> <span class="n">JOIN</span> <span class="s2">"user"</span> <span class="n">ON</span> <span class="s2">"user"</span><span class="o">.</span><span class="n">id</span> <span class="o">=</span> <span class="nb">id</span>
<span class="n">WHERE</span> <span class="s2">"user"</span><span class="o">.</span><span class="n">name</span> <span class="o">=</span> <span class="p">:</span><span class="n">name_1</span></pre></div>
</div>
<p>The <a class="reference internal" href="../orm/query.html#sqlalchemy.orm.query.Query.select_entity_from" title="sqlalchemy.orm.query.Query.select_entity_from"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Query.select_entity_from()</span></code></a> method will be available in SQLAlchemy
<strong>0.8.2</strong>, so applications which rely on the old behavior can transition
to this method first, ensure all tests continue to function, then upgrade
to 0.9 without issue.</p>
<p><a class="reference external" href="http://www.sqlalchemy.org/trac/ticket/2736">#2736</a></p>
</div>
<div class="section" id="viewonly-true-on-relationship-prevents-history-from-taking-effect">
<span id="migration-2833"></span><h3><code class="docutils literal notranslate"><span class="pre">viewonly=True</span></code> on <code class="docutils literal notranslate"><span class="pre">relationship()</span></code> prevents history from taking effect<a class="headerlink" href="#viewonly-true-on-relationship-prevents-history-from-taking-effect" title="Permalink to this headline">¶</a></h3>
<p>The <code class="docutils literal notranslate"><span class="pre">viewonly</span></code> flag on <a class="reference internal" href="../orm/relationship_api.html#sqlalchemy.orm.relationship" title="sqlalchemy.orm.relationship"><code class="xref py py-func docutils literal notranslate"><span class="pre">relationship()</span></code></a> is applied to prevent changes
to the target attribute from having any effect within the flush process.
This is achieved by eliminating the attribute from being considered during
the flush. However, up until now, changes to the attribute would still
register the parent object as “dirty” and trigger a potential flush. The change
is that the <code class="docutils literal notranslate"><span class="pre">viewonly</span></code> flag now prevents history from being set for the
target attribute as well. Attribute events like backrefs and user-defined events
still continue to function normally.</p>
<p>The change is illustrated as follows:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="kn">from</span> <span class="nn">sqlalchemy</span> <span class="k">import</span> <span class="n">Column</span><span class="p">,</span> <span class="n">Integer</span><span class="p">,</span> <span class="n">ForeignKey</span><span class="p">,</span> <span class="n">create_engine</span>
<span class="kn">from</span> <span class="nn">sqlalchemy.orm</span> <span class="k">import</span> <span class="n">backref</span><span class="p">,</span> <span class="n">relationship</span><span class="p">,</span> <span class="n">Session</span>
<span class="kn">from</span> <span class="nn">sqlalchemy.ext.declarative</span> <span class="k">import</span> <span class="n">declarative_base</span>
<span class="kn">from</span> <span class="nn">sqlalchemy</span> <span class="k">import</span> <span class="n">inspect</span>
<span class="n">Base</span> <span class="o">=</span> <span class="n">declarative_base</span><span class="p">()</span>
<span class="k">class</span> <span class="nc">A</span><span class="p">(</span><span class="n">Base</span><span class="p">):</span>
<span class="n">__tablename__</span> <span class="o">=</span> <span class="s1">'a'</span>
<span class="nb">id</span> <span class="o">=</span> <span class="n">Column</span><span class="p">(</span><span class="n">Integer</span><span class="p">,</span> <span class="n">primary_key</span><span class="o">=</span><span class="kc">True</span><span class="p">)</span>
<span class="k">class</span> <span class="nc">B</span><span class="p">(</span><span class="n">Base</span><span class="p">):</span>
<span class="n">__tablename__</span> <span class="o">=</span> <span class="s1">'b'</span>
<span class="nb">id</span> <span class="o">=</span> <span class="n">Column</span><span class="p">(</span><span class="n">Integer</span><span class="p">,</span> <span class="n">primary_key</span><span class="o">=</span><span class="kc">True</span><span class="p">)</span>
<span class="n">a_id</span> <span class="o">=</span> <span class="n">Column</span><span class="p">(</span><span class="n">Integer</span><span class="p">,</span> <span class="n">ForeignKey</span><span class="p">(</span><span class="s1">'a.id'</span><span class="p">))</span>
<span class="n">a</span> <span class="o">=</span> <span class="n">relationship</span><span class="p">(</span><span class="s2">"A"</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="s2">"bs"</span><span class="p">,</span> <span class="n">viewonly</span><span class="o">=</span><span class="kc">True</span><span class="p">))</span>
<span class="n">e</span> <span class="o">=</span> <span class="n">create_engine</span><span class="p">(</span><span class="s2">"sqlite://"</span><span class="p">)</span>
<span class="n">Base</span><span class="o">.</span><span class="n">metadata</span><span class="o">.</span><span class="n">create_all</span><span class="p">(</span><span class="n">e</span><span class="p">)</span>
<span class="n">a</span> <span class="o">=</span> <span class="n">A</span><span class="p">()</span>
<span class="n">b</span> <span class="o">=</span> <span class="n">B</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">e</span><span class="p">)</span>
<span class="n">sess</span><span class="o">.</span><span class="n">add_all</span><span class="p">([</span><span class="n">a</span><span class="p">,</span> <span class="n">b</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="n">b</span><span class="o">.</span><span class="n">a</span> <span class="o">=</span> <span class="n">a</span>
<span class="k">assert</span> <span class="n">b</span> <span class="ow">in</span> <span class="n">sess</span><span class="o">.</span><span class="n">dirty</span>
<span class="c1"># before 0.9.0</span>
<span class="c1"># assert a in sess.dirty</span>
<span class="c1"># assert inspect(a).attrs.bs.history.has_changes()</span>
<span class="c1"># after 0.9.0</span>
<span class="k">assert</span> <span class="n">a</span> <span class="ow">not</span> <span class="ow">in</span> <span class="n">sess</span><span class="o">.</span><span class="n">dirty</span>
<span class="k">assert</span> <span class="ow">not</span> <span class="n">inspect</span><span class="p">(</span><span class="n">a</span><span class="p">)</span><span class="o">.</span><span class="n">attrs</span><span class="o">.</span><span class="n">bs</span><span class="o">.</span><span class="n">history</span><span class="o">.</span><span class="n">has_changes</span><span class="p">()</span></pre></div>
</div>
<p><a class="reference external" href="http://www.sqlalchemy.org/trac/ticket/2833">#2833</a></p>
</div>
<div class="section" id="association-proxy-sql-expression-improvements-and-fixes">
<span id="migration-2751"></span><h3>Association Proxy SQL Expression Improvements and Fixes<a class="headerlink" href="#association-proxy-sql-expression-improvements-and-fixes" title="Permalink to this headline">¶</a></h3>
<p>The <code class="docutils literal notranslate"><span class="pre">==</span></code> and <code class="docutils literal notranslate"><span class="pre">!=</span></code> operators as implemented by an association proxy
that refers to a scalar value on a scalar relationship now produces
a more complete SQL expression, intended to take into account
the “association” row being present or not when the comparison is against
<code class="docutils literal notranslate"><span class="pre">None</span></code>.</p>
<p>Consider this mapping:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="k">class</span> <span class="nc">A</span><span class="p">(</span><span class="n">Base</span><span class="p">):</span>
<span class="n">__tablename__</span> <span class="o">=</span> <span class="s1">'a'</span>
<span class="nb">id</span> <span class="o">=</span> <span class="n">Column</span><span class="p">(</span><span class="n">Integer</span><span class="p">,</span> <span class="n">primary_key</span><span class="o">=</span><span class="kc">True</span><span class="p">)</span>
<span class="n">b_id</span> <span class="o">=</span> <span class="n">Column</span><span class="p">(</span><span class="n">Integer</span><span class="p">,</span> <span class="n">ForeignKey</span><span class="p">(</span><span class="s1">'b.id'</span><span class="p">),</span> <span class="n">primary_key</span><span class="o">=</span><span class="kc">True</span><span class="p">)</span>
<span class="n">b</span> <span class="o">=</span> <span class="n">relationship</span><span class="p">(</span><span class="s2">"B"</span><span class="p">)</span>
<span class="n">b_value</span> <span class="o">=</span> <span class="n">association_proxy</span><span class="p">(</span><span class="s2">"b"</span><span class="p">,</span> <span class="s2">"value"</span><span class="p">)</span>
<span class="k">class</span> <span class="nc">B</span><span class="p">(</span><span class="n">Base</span><span class="p">):</span>
<span class="n">__tablename__</span> <span class="o">=</span> <span class="s1">'b'</span>
<span class="nb">id</span> <span class="o">=</span> <span class="n">Column</span><span class="p">(</span><span class="n">Integer</span><span class="p">,</span> <span class="n">primary_key</span><span class="o">=</span><span class="kc">True</span><span class="p">)</span>
<span class="n">value</span> <span class="o">=</span> <span class="n">Column</span><span class="p">(</span><span class="n">String</span><span class="p">)</span></pre></div>
</div>
<p>Up through 0.8, a query like the following:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">s</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="n">A</span><span class="p">)</span><span class="o">.</span><span class="n">filter</span><span class="p">(</span><span class="n">A</span><span class="o">.</span><span class="n">b_value</span> <span class="o">==</span> <span class="kc">None</span><span class="p">)</span><span class="o">.</span><span class="n">all</span><span class="p">()</span></pre></div>
</div>
<p>would produce:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">SELECT</span> <span class="n">a</span><span class="o">.</span><span class="n">id</span> <span class="n">AS</span> <span class="n">a_id</span><span class="p">,</span> <span class="n">a</span><span class="o">.</span><span class="n">b_id</span> <span class="n">AS</span> <span class="n">a_b_id</span>
<span class="n">FROM</span> <span class="n">a</span>
<span class="n">WHERE</span> <span class="n">EXISTS</span> <span class="p">(</span><span class="n">SELECT</span> <span class="mi">1</span>
<span class="n">FROM</span> <span class="n">b</span>
<span class="n">WHERE</span> <span class="n">b</span><span class="o">.</span><span class="n">id</span> <span class="o">=</span> <span class="n">a</span><span class="o">.</span><span class="n">b_id</span> <span class="n">AND</span> <span class="n">b</span><span class="o">.</span><span class="n">value</span> <span class="n">IS</span> <span class="n">NULL</span><span class="p">)</span></pre></div>
</div>
<p>In 0.9, it now produces:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">SELECT</span> <span class="n">a</span><span class="o">.</span><span class="n">id</span> <span class="n">AS</span> <span class="n">a_id</span><span class="p">,</span> <span class="n">a</span><span class="o">.</span><span class="n">b_id</span> <span class="n">AS</span> <span class="n">a_b_id</span>
<span class="n">FROM</span> <span class="n">a</span>
<span class="n">WHERE</span> <span class="p">(</span><span class="n">EXISTS</span> <span class="p">(</span><span class="n">SELECT</span> <span class="mi">1</span>
<span class="n">FROM</span> <span class="n">b</span>
<span class="n">WHERE</span> <span class="n">b</span><span class="o">.</span><span class="n">id</span> <span class="o">=</span> <span class="n">a</span><span class="o">.</span><span class="n">b_id</span> <span class="n">AND</span> <span class="n">b</span><span class="o">.</span><span class="n">value</span> <span class="n">IS</span> <span class="n">NULL</span><span class="p">))</span> <span class="n">OR</span> <span class="n">a</span><span class="o">.</span><span class="n">b_id</span> <span class="n">IS</span> <span class="n">NULL</span></pre></div>
</div>
<p>The difference being, it not only checks <code class="docutils literal notranslate"><span class="pre">b.value</span></code>, it also checks
if <code class="docutils literal notranslate"><span class="pre">a</span></code> refers to no <code class="docutils literal notranslate"><span class="pre">b</span></code> row at all. This will return different
results versus prior versions, for a system that uses this type of
comparison where some parent rows have no association row.</p>
<p>More critically, a correct expression is emitted for <code class="docutils literal notranslate"><span class="pre">A.b_value</span> <span class="pre">!=</span> <span class="pre">None</span></code>.
In 0.8, this would return <code class="docutils literal notranslate"><span class="pre">True</span></code> for <code class="docutils literal notranslate"><span class="pre">A</span></code> rows that had no <code class="docutils literal notranslate"><span class="pre">b</span></code>:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">SELECT</span> <span class="n">a</span><span class="o">.</span><span class="n">id</span> <span class="n">AS</span> <span class="n">a_id</span><span class="p">,</span> <span class="n">a</span><span class="o">.</span><span class="n">b_id</span> <span class="n">AS</span> <span class="n">a_b_id</span>
<span class="n">FROM</span> <span class="n">a</span>
<span class="n">WHERE</span> <span class="n">NOT</span> <span class="p">(</span><span class="n">EXISTS</span> <span class="p">(</span><span class="n">SELECT</span> <span class="mi">1</span>
<span class="n">FROM</span> <span class="n">b</span>
<span class="n">WHERE</span> <span class="n">b</span><span class="o">.</span><span class="n">id</span> <span class="o">=</span> <span class="n">a</span><span class="o">.</span><span class="n">b_id</span> <span class="n">AND</span> <span class="n">b</span><span class="o">.</span><span class="n">value</span> <span class="n">IS</span> <span class="n">NULL</span><span class="p">))</span></pre></div>
</div>
<p>Now in 0.9, the check has been reworked so that it ensures
the A.b_id row is present, in addition to <code class="docutils literal notranslate"><span class="pre">B.value</span></code> being
non-NULL:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">SELECT</span> <span class="n">a</span><span class="o">.</span><span class="n">id</span> <span class="n">AS</span> <span class="n">a_id</span><span class="p">,</span> <span class="n">a</span><span class="o">.</span><span class="n">b_id</span> <span class="n">AS</span> <span class="n">a_b_id</span>
<span class="n">FROM</span> <span class="n">a</span>
<span class="n">WHERE</span> <span class="n">EXISTS</span> <span class="p">(</span><span class="n">SELECT</span> <span class="mi">1</span>
<span class="n">FROM</span> <span class="n">b</span>
<span class="n">WHERE</span> <span class="n">b</span><span class="o">.</span><span class="n">id</span> <span class="o">=</span> <span class="n">a</span><span class="o">.</span><span class="n">b_id</span> <span class="n">AND</span> <span class="n">b</span><span class="o">.</span><span class="n">value</span> <span class="n">IS</span> <span class="n">NOT</span> <span class="n">NULL</span><span class="p">)</span></pre></div>
</div>
<p>In addition, the <code class="docutils literal notranslate"><span class="pre">has()</span></code> operator is enhanced such that you can
call it against a scalar column value with no criterion only,
and it will produce criteria that checks for the association row
being present or not:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">s</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="n">A</span><span class="p">)</span><span class="o">.</span><span class="n">filter</span><span class="p">(</span><span class="n">A</span><span class="o">.</span><span class="n">b_value</span><span class="o">.</span><span class="n">has</span><span class="p">())</span><span class="o">.</span><span class="n">all</span><span class="p">()</span></pre></div>
</div>
<p>output:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">SELECT</span> <span class="n">a</span><span class="o">.</span><span class="n">id</span> <span class="n">AS</span> <span class="n">a_id</span><span class="p">,</span> <span class="n">a</span><span class="o">.</span><span class="n">b_id</span> <span class="n">AS</span> <span class="n">a_b_id</span>
<span class="n">FROM</span> <span class="n">a</span>
<span class="n">WHERE</span> <span class="n">EXISTS</span> <span class="p">(</span><span class="n">SELECT</span> <span class="mi">1</span>
<span class="n">FROM</span> <span class="n">b</span>
<span class="n">WHERE</span> <span class="n">b</span><span class="o">.</span><span class="n">id</span> <span class="o">=</span> <span class="n">a</span><span class="o">.</span><span class="n">b_id</span><span class="p">)</span></pre></div>
</div>
<p>This is equivalent to <code class="docutils literal notranslate"><span class="pre">A.b.has()</span></code>, but allows one to query
against <code class="docutils literal notranslate"><span class="pre">b_value</span></code> directly.</p>
<p><a class="reference external" href="http://www.sqlalchemy.org/trac/ticket/2751">#2751</a></p>
</div>
<div class="section" id="association-proxy-missing-scalar-returns-none">
<span id="migration-2810"></span><h3>Association Proxy Missing Scalar returns None<a class="headerlink" href="#association-proxy-missing-scalar-returns-none" title="Permalink to this headline">¶</a></h3>
<p>An association proxy from a scalar attribute to a scalar will now return
<code class="docutils literal notranslate"><span class="pre">None</span></code> if the proxied object isn’t present. This is consistent with the
fact that missing many-to-ones return None in SQLAlchemy, so should the
proxied value. E.g.:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="kn">from</span> <span class="nn">sqlalchemy</span> <span class="k">import</span> <span class="o">*</span>
<span class="kn">from</span> <span class="nn">sqlalchemy.orm</span> <span class="k">import</span> <span class="o">*</span>
<span class="kn">from</span> <span class="nn">sqlalchemy.ext.declarative</span> <span class="k">import</span> <span class="n">declarative_base</span>
<span class="kn">from</span> <span class="nn">sqlalchemy.ext.associationproxy</span> <span class="k">import</span> <span class="n">association_proxy</span>
<span class="n">Base</span> <span class="o">=</span> <span class="n">declarative_base</span><span class="p">()</span>
<span class="k">class</span> <span class="nc">A</span><span class="p">(</span><span class="n">Base</span><span class="p">):</span>
<span class="n">__tablename__</span> <span class="o">=</span> <span class="s1">'a'</span>
<span class="nb">id</span> <span class="o">=</span> <span class="n">Column</span><span class="p">(</span><span class="n">Integer</span><span class="p">,</span> <span class="n">primary_key</span><span class="o">=</span><span class="kc">True</span><span class="p">)</span>
<span class="n">b</span> <span class="o">=</span> <span class="n">relationship</span><span class="p">(</span><span class="s2">"B"</span><span class="p">,</span> <span class="n">uselist</span><span class="o">=</span><span class="kc">False</span><span class="p">)</span>
<span class="n">bname</span> <span class="o">=</span> <span class="n">association_proxy</span><span class="p">(</span><span class="s2">"b"</span><span class="p">,</span> <span class="s2">"name"</span><span class="p">)</span>
<span class="k">class</span> <span class="nc">B</span><span class="p">(</span><span class="n">Base</span><span class="p">):</span>
<span class="n">__tablename__</span> <span class="o">=</span> <span class="s1">'b'</span>
<span class="nb">id</span> <span class="o">=</span> <span class="n">Column</span><span class="p">(</span><span class="n">Integer</span><span class="p">,</span> <span class="n">primary_key</span><span class="o">=</span><span class="kc">True</span><span class="p">)</span>
<span class="n">a_id</span> <span class="o">=</span> <span class="n">Column</span><span class="p">(</span><span class="n">Integer</span><span class="p">,</span> <span class="n">ForeignKey</span><span class="p">(</span><span class="s1">'a.id'</span><span class="p">))</span>
<span class="n">name</span> <span class="o">=</span> <span class="n">Column</span><span class="p">(</span><span class="n">String</span><span class="p">)</span>
<span class="n">a1</span> <span class="o">=</span> <span class="n">A</span><span class="p">()</span>
<span class="c1"># this is how m2o's always have worked</span>
<span class="k">assert</span> <span class="n">a1</span><span class="o">.</span><span class="n">b</span> <span class="ow">is</span> <span class="kc">None</span>
<span class="c1"># but prior to 0.9, this would raise AttributeError,</span>
<span class="c1"># now returns None just like the proxied value.</span>
<span class="k">assert</span> <span class="n">a1</span><span class="o">.</span><span class="n">bname</span> <span class="ow">is</span> <span class="kc">None</span></pre></div>
</div>
<p><a class="reference external" href="http://www.sqlalchemy.org/trac/ticket/2810">#2810</a></p>
</div>
<div class="section" id="attributes-get-history-will-query-from-the-db-by-default-if-value-not-present">
<span id="change-2787"></span><h3>attributes.get_history() will query from the DB by default if value not present<a class="headerlink" href="#attributes-get-history-will-query-from-the-db-by-default-if-value-not-present" title="Permalink to this headline">¶</a></h3>
<p>A bugfix regarding <a class="reference internal" href="../orm/session_api.html#sqlalchemy.orm.attributes.get_history" title="sqlalchemy.orm.attributes.get_history"><code class="xref py py-func docutils literal notranslate"><span class="pre">attributes.get_history()</span></code></a> allows a column-based attribute
to query out to the database for an unloaded value, assuming the <code class="docutils literal notranslate"><span class="pre">passive</span></code>
flag is left at its default of <code class="docutils literal notranslate"><span class="pre">PASSIVE_OFF</span></code>. Previously, this flag would
not be honored. Additionally, a new method <a class="reference internal" href="../orm/internals.html#sqlalchemy.orm.state.AttributeState.load_history" title="sqlalchemy.orm.state.AttributeState.load_history"><code class="xref py py-meth docutils literal notranslate"><span class="pre">AttributeState.load_history()</span></code></a>
is added to complement the <a class="reference internal" href="../orm/internals.html#sqlalchemy.orm.state.AttributeState.history" title="sqlalchemy.orm.state.AttributeState.history"><code class="xref py py-attr docutils literal notranslate"><span class="pre">AttributeState.history</span></code></a> attribute, which
will emit loader callables for an unloaded attribute.</p>
<p>This is a small change demonstrated as follows:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="kn">from</span> <span class="nn">sqlalchemy</span> <span class="k">import</span> <span class="n">Column</span><span class="p">,</span> <span class="n">Integer</span><span class="p">,</span> <span class="n">String</span><span class="p">,</span> <span class="n">create_engine</span><span class="p">,</span> <span class="n">inspect</span>
<span class="kn">from</span> <span class="nn">sqlalchemy.orm</span> <span class="k">import</span> <span class="n">Session</span><span class="p">,</span> <span class="n">attributes</span>
<span class="kn">from</span> <span class="nn">sqlalchemy.ext.declarative</span> <span class="k">import</span> <span class="n">declarative_base</span>
<span class="n">Base</span> <span class="o">=</span> <span class="n">declarative_base</span><span class="p">()</span>
<span class="k">class</span> <span class="nc">A</span><span class="p">(</span><span class="n">Base</span><span class="p">):</span>
<span class="n">__tablename__</span> <span class="o">=</span> <span class="s1">'a'</span>
<span class="nb">id</span> <span class="o">=</span> <span class="n">Column</span><span class="p">(</span><span class="n">Integer</span><span class="p">,</span> <span class="n">primary_key</span><span class="o">=</span><span class="kc">True</span><span class="p">)</span>
<span class="n">data</span> <span class="o">=</span> <span class="n">Column</span><span class="p">(</span><span class="n">String</span><span class="p">)</span>
<span class="n">e</span> <span class="o">=</span> <span class="n">create_engine</span><span class="p">(</span><span class="s2">"sqlite://"</span><span class="p">,</span> <span class="n">echo</span><span class="o">=</span><span class="kc">True</span><span class="p">)</span>
<span class="n">Base</span><span class="o">.</span><span class="n">metadata</span><span class="o">.</span><span class="n">create_all</span><span class="p">(</span><span class="n">e</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">e</span><span class="p">)</span>
<span class="n">a1</span> <span class="o">=</span> <span class="n">A</span><span class="p">(</span><span class="n">data</span><span class="o">=</span><span class="s1">'a1'</span><span class="p">)</span>
<span class="n">sess</span><span class="o">.</span><span class="n">add</span><span class="p">(</span><span class="n">a1</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="c1"># a1 is now expired</span>
<span class="c1"># history doesn't emit loader callables</span>
<span class="k">assert</span> <span class="n">inspect</span><span class="p">(</span><span class="n">a1</span><span class="p">)</span><span class="o">.</span><span class="n">attrs</span><span class="o">.</span><span class="n">data</span><span class="o">.</span><span class="n">history</span> <span class="o">==</span> <span class="p">(</span><span class="kc">None</span><span class="p">,</span> <span class="kc">None</span><span class="p">,</span> <span class="kc">None</span><span class="p">)</span>
<span class="c1"># in 0.8, this would fail to load the unloaded state.</span>
<span class="k">assert</span> <span class="n">attributes</span><span class="o">.</span><span class="n">get_history</span><span class="p">(</span><span class="n">a1</span><span class="p">,</span> <span class="s1">'data'</span><span class="p">)</span> <span class="o">==</span> <span class="p">((),</span> <span class="p">[</span><span class="s1">'a1'</span><span class="p">,],</span> <span class="p">())</span>
<span class="c1"># load_history() is now equivalent to get_history() with</span>
<span class="c1"># passive=PASSIVE_OFF ^ INIT_OK</span>
<span class="k">assert</span> <span class="n">inspect</span><span class="p">(</span><span class="n">a1</span><span class="p">)</span><span class="o">.</span><span class="n">attrs</span><span class="o">.</span><span class="n">data</span><span class="o">.</span><span class="n">load_history</span><span class="p">()</span> <span class="o">==</span> <span class="p">((),</span> <span class="p">[</span><span class="s1">'a1'</span><span class="p">,],</span> <span class="p">())</span></pre></div>
</div>
<p><a class="reference external" href="http://www.sqlalchemy.org/trac/ticket/2787">#2787</a></p>
</div>
</div>
<div class="section" id="behavioral-changes-core">
<span id="behavioral-changes-core-09"></span><h2>Behavioral Changes - Core<a class="headerlink" href="#behavioral-changes-core" title="Permalink to this headline">¶</a></h2>
<div class="section" id="type-objects-no-longer-accept-ignored-keyword-arguments">
<h3>Type objects no longer accept ignored keyword arguments<a class="headerlink" href="#type-objects-no-longer-accept-ignored-keyword-arguments" title="Permalink to this headline">¶</a></h3>
<p>Up through the 0.8 series, most type objects accepted arbitrary keyword
arguments which were silently ignored:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="kn">from</span> <span class="nn">sqlalchemy</span> <span class="k">import</span> <span class="n">Date</span><span class="p">,</span> <span class="n">Integer</span>
<span class="c1"># storage_format argument here has no effect on any backend;</span>
<span class="c1"># it needs to be on the SQLite-specific type</span>
<span class="n">d</span> <span class="o">=</span> <span class="n">Date</span><span class="p">(</span><span class="n">storage_format</span><span class="o">=</span><span class="s2">"</span><span class="si">%(day)02d</span><span class="s2">.</span><span class="si">%(month)02d</span><span class="s2">.</span><span class="si">%(year)04d</span><span class="s2">"</span><span class="p">)</span>
<span class="c1"># display_width argument here has no effect on any backend;</span>
<span class="c1"># it needs to be on the MySQL-specific type</span>
<span class="n">i</span> <span class="o">=</span> <span class="n">Integer</span><span class="p">(</span><span class="n">display_width</span><span class="o">=</span><span class="mi">5</span><span class="p">)</span></pre></div>
</div>
<p>This was a very old bug for which a deprecation warning was added to the
0.8 series, but because nobody ever runs Python with the “-W” flag, it
was mostly never seen:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span>$ python -W always::DeprecationWarning ~/dev/sqlalchemy/test.py
/Users/classic/dev/sqlalchemy/test.py:5: SADeprecationWarning: Passing arguments to
type object constructor <class 'sqlalchemy.types.Date'> is deprecated
d = Date(storage_format="%(day)02d.%(month)02d.%(year)04d")
/Users/classic/dev/sqlalchemy/test.py:9: SADeprecationWarning: Passing arguments to
type object constructor <class 'sqlalchemy.types.Integer'> is deprecated
i = Integer(display_width=5)</pre></div>
</div>
<p>As of the 0.9 series the “catch all” constructor is removed from
<a class="reference internal" href="../core/type_api.html#sqlalchemy.types.TypeEngine" title="sqlalchemy.types.TypeEngine"><code class="xref py py-class docutils literal notranslate"><span class="pre">TypeEngine</span></code></a>, and these meaningless arguments are no longer accepted.</p>
<p>The correct way to make use of dialect-specific arguments such as
<code class="docutils literal notranslate"><span class="pre">storage_format</span></code> and <code class="docutils literal notranslate"><span class="pre">display_width</span></code> is to use the appropriate
dialect-specific types:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="kn">from</span> <span class="nn">sqlalchemy.dialects.sqlite</span> <span class="k">import</span> <span class="n">DATE</span>
<span class="kn">from</span> <span class="nn">sqlalchemy.dialects.mysql</span> <span class="k">import</span> <span class="n">INTEGER</span>
<span class="n">d</span> <span class="o">=</span> <span class="n">DATE</span><span class="p">(</span><span class="n">storage_format</span><span class="o">=</span><span class="s2">"</span><span class="si">%(day)02d</span><span class="s2">.</span><span class="si">%(month)02d</span><span class="s2">.</span><span class="si">%(year)04d</span><span class="s2">"</span><span class="p">)</span>
<span class="n">i</span> <span class="o">=</span> <span class="n">INTEGER</span><span class="p">(</span><span class="n">display_width</span><span class="o">=</span><span class="mi">5</span><span class="p">)</span></pre></div>
</div>
<p>What about the case where we want the dialect-agnostic type also? We
use the <a class="reference internal" href="../core/type_api.html#sqlalchemy.types.TypeEngine.with_variant" title="sqlalchemy.types.TypeEngine.with_variant"><code class="xref py py-meth docutils literal notranslate"><span class="pre">TypeEngine.with_variant()</span></code></a> method:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="kn">from</span> <span class="nn">sqlalchemy</span> <span class="k">import</span> <span class="n">Date</span><span class="p">,</span> <span class="n">Integer</span>
<span class="kn">from</span> <span class="nn">sqlalchemy.dialects.sqlite</span> <span class="k">import</span> <span class="n">DATE</span>
<span class="kn">from</span> <span class="nn">sqlalchemy.dialects.mysql</span> <span class="k">import</span> <span class="n">INTEGER</span>
<span class="n">d</span> <span class="o">=</span> <span class="n">Date</span><span class="p">()</span><span class="o">.</span><span class="n">with_variant</span><span class="p">(</span>
<span class="n">DATE</span><span class="p">(</span><span class="n">storage_format</span><span class="o">=</span><span class="s2">"</span><span class="si">%(day)02d</span><span class="s2">.</span><span class="si">%(month)02d</span><span class="s2">.</span><span class="si">%(year)04d</span><span class="s2">"</span><span class="p">),</span>
<span class="s2">"sqlite"</span>
<span class="p">)</span>
<span class="n">i</span> <span class="o">=</span> <span class="n">Integer</span><span class="p">()</span><span class="o">.</span><span class="n">with_variant</span><span class="p">(</span>
<span class="n">INTEGER</span><span class="p">(</span><span class="n">display_width</span><span class="o">=</span><span class="mi">5</span><span class="p">),</span>
<span class="s2">"mysql"</span>
<span class="p">)</span></pre></div>
</div>
<p><a class="reference internal" href="../core/type_api.html#sqlalchemy.types.TypeEngine.with_variant" title="sqlalchemy.types.TypeEngine.with_variant"><code class="xref py py-meth docutils literal notranslate"><span class="pre">TypeEngine.with_variant()</span></code></a> isn’t new, it was added in SQLAlchemy
0.7.2. So code that is running on the 0.8 series can be corrected to use
this approach and tested before upgrading to 0.9.</p>
</div>
<div class="section" id="none-can-no-longer-be-used-as-a-partial-and-constructor">
<h3><code class="docutils literal notranslate"><span class="pre">None</span></code> can no longer be used as a “partial AND” constructor<a class="headerlink" href="#none-can-no-longer-be-used-as-a-partial-and-constructor" title="Permalink to this headline">¶</a></h3>
<p><code class="docutils literal notranslate"><span class="pre">None</span></code> can no longer be used as the “backstop” to form an AND condition piecemeal.
This pattern was not a documented pattern even though some SQLAlchemy internals
made use of it:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">condition</span> <span class="o">=</span> <span class="kc">None</span>
<span class="k">for</span> <span class="n">cond</span> <span class="ow">in</span> <span class="n">conditions</span><span class="p">:</span>
<span class="n">condition</span> <span class="o">=</span> <span class="n">condition</span> <span class="o">&</span> <span class="n">cond</span>
<span class="k">if</span> <span class="n">condition</span> <span class="ow">is</span> <span class="ow">not</span> <span class="kc">None</span><span class="p">:</span>
<span class="n">stmt</span> <span class="o">=</span> <span class="n">stmt</span><span class="o">.</span><span class="n">where</span><span class="p">(</span><span class="n">condition</span><span class="p">)</span></pre></div>
</div>
<p>The above sequence, when <code class="docutils literal notranslate"><span class="pre">conditions</span></code> is non-empty, will on 0.9 produce
<code class="docutils literal notranslate"><span class="pre">SELECT</span> <span class="pre">..</span> <span class="pre">WHERE</span> <span class="pre"><condition></span> <span class="pre">AND</span> <span class="pre">NULL</span></code>. The <code class="docutils literal notranslate"><span class="pre">None</span></code> is no longer implicitly
ignored, and is instead consistent with when <code class="docutils literal notranslate"><span class="pre">None</span></code> is interpreted in other
contexts besides that of a conjunction.</p>
<p>The correct code for both 0.8 and 0.9 should read:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="kn">from</span> <span class="nn">sqlalchemy.sql</span> <span class="k">import</span> <span class="n">and_</span>
<span class="k">if</span> <span class="n">conditions</span><span class="p">:</span>
<span class="n">stmt</span> <span class="o">=</span> <span class="n">stmt</span><span class="o">.</span><span class="n">where</span><span class="p">(</span><span class="n">and_</span><span class="p">(</span><span class="o">*</span><span class="n">conditions</span><span class="p">))</span></pre></div>
</div>
<p>Another variant that works on all backends on 0.9, but on 0.8 only works on
backends that support boolean constants:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="kn">from</span> <span class="nn">sqlalchemy.sql</span> <span class="k">import</span> <span class="n">true</span>
<span class="n">condition</span> <span class="o">=</span> <span class="n">true</span><span class="p">()</span>
<span class="k">for</span> <span class="n">cond</span> <span class="ow">in</span> <span class="n">conditions</span><span class="p">:</span>
<span class="n">condition</span> <span class="o">=</span> <span class="n">cond</span> <span class="o">&</span> <span class="n">condition</span>
<span class="n">stmt</span> <span class="o">=</span> <span class="n">stmt</span><span class="o">.</span><span class="n">where</span><span class="p">(</span><span class="n">condition</span><span class="p">)</span></pre></div>
</div>
<p>On 0.8, this will produce a SELECT statement that always has <code class="docutils literal notranslate"><span class="pre">AND</span> <span class="pre">true</span></code>
in the WHERE clause, which is not accepted by backends that don’t support
boolean constants (MySQL, MSSQL). On 0.9, the <code class="docutils literal notranslate"><span class="pre">true</span></code> constant will be dropped
within an <code class="docutils literal notranslate"><span class="pre">and_()</span></code> conjunction.</p>
<div class="admonition seealso">
<p class="admonition-title">See also</p>
<p><a class="reference internal" href="#migration-2804"><span class="std std-ref">Improved rendering of Boolean constants, NULL constants, conjunctions</span></a></p>
</div>
</div>
<div class="section" id="the-password-portion-of-a-create-engine-no-longer-considers-the-sign-as-an-encoded-space">
<span id="migration-2873"></span><h3>The “password” portion of a <code class="docutils literal notranslate"><span class="pre">create_engine()</span></code> no longer considers the <code class="docutils literal notranslate"><span class="pre">+</span></code> sign as an encoded space<a class="headerlink" href="#the-password-portion-of-a-create-engine-no-longer-considers-the-sign-as-an-encoded-space" title="Permalink to this headline">¶</a></h3>
<p>For whatever reason, the Python function <code class="docutils literal notranslate"><span class="pre">unquote_plus()</span></code> was applied to the
“password” field of a URL, which is an incorrect application of the
encoding rules described in <a class="reference external" href="http://www.ietf.org/rfc/rfc1738.txt">RFC 1738</a>
in that it escaped spaces as plus signs. The stringification of a URL
now only encodes “:”, “@”, or “/” and nothing else, and is now applied to both the
<code class="docutils literal notranslate"><span class="pre">username</span></code> and <code class="docutils literal notranslate"><span class="pre">password</span></code> fields (previously it only applied to the
password). On parsing, encoded characters are converted, but plus signs and
spaces are passed through as is:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="c1"># password: "pass word + other:words"</span>
<span class="n">dbtype</span><span class="p">:</span><span class="o">//</span><span class="n">user</span><span class="p">:</span><span class="k">pass</span> <span class="n">word</span> <span class="o">+</span> <span class="n">other</span><span class="o">%</span><span class="mi">3</span><span class="n">Awords</span><span class="nd">@host</span><span class="o">/</span><span class="n">dbname</span>
<span class="c1"># password: "apples/oranges"</span>
<span class="n">dbtype</span><span class="p">:</span><span class="o">//</span><span class="n">username</span><span class="p">:</span><span class="n">apples</span><span class="o">%</span><span class="mi">2</span><span class="n">Foranges</span><span class="nd">@hostspec</span><span class="o">/</span><span class="n">database</span>
<span class="c1"># password: "apples@oranges@@"</span>
<span class="n">dbtype</span><span class="p">:</span><span class="o">//</span><span class="n">username</span><span class="p">:</span><span class="n">apples</span><span class="o">%</span><span class="mi">40</span><span class="n">oranges</span><span class="o">%</span><span class="mi">40</span><span class="o">%</span><span class="mi">40</span><span class="nd">@hostspec</span><span class="o">/</span><span class="n">database</span>
<span class="c1"># password: '', username is "username@"</span>
<span class="n">dbtype</span><span class="p">:</span><span class="o">//</span><span class="n">username</span><span class="o">%</span><span class="mi">40</span><span class="p">:</span><span class="nd">@hostspec</span><span class="o">/</span><span class="n">database</span></pre></div>
</div>
<p><a class="reference external" href="http://www.sqlalchemy.org/trac/ticket/2873">#2873</a></p>
</div>
<div class="section" id="the-precedence-rules-for-collate-have-been-changed">
<span id="migration-2879"></span><h3>The precedence rules for COLLATE have been changed<a class="headerlink" href="#the-precedence-rules-for-collate-have-been-changed" title="Permalink to this headline">¶</a></h3>
<p>Previously, an expression like the following:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="nb">print</span><span class="p">((</span><span class="n">column</span><span class="p">(</span><span class="s1">'x'</span><span class="p">)</span> <span class="o">==</span> <span class="s1">'somevalue'</span><span class="p">)</span><span class="o">.</span><span class="n">collate</span><span class="p">(</span><span class="s2">"en_EN"</span><span class="p">))</span></pre></div>
</div>
<p>would produce an expression like this:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="o">--</span> <span class="mf">0.8</span> <span class="n">behavior</span>
<span class="p">(</span><span class="n">x</span> <span class="o">=</span> <span class="p">:</span><span class="n">x_1</span><span class="p">)</span> <span class="n">COLLATE</span> <span class="n">en_EN</span></pre></div>
</div>
<p>The above is misunderstood by MSSQL and is generally not the syntax suggested
for any database. The expression will now produce the syntax illustrated
by that of most database documentation:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="o">--</span> <span class="mf">0.9</span> <span class="n">behavior</span>
<span class="n">x</span> <span class="o">=</span> <span class="p">:</span><span class="n">x_1</span> <span class="n">COLLATE</span> <span class="n">en_EN</span></pre></div>
</div>
<p>The potentially backwards incompatible change arises if the <a class="reference internal" href="../core/custom_types.html#sqlalchemy.types.TypeDecorator.Comparator.collate" title="sqlalchemy.types.TypeDecorator.Comparator.collate"><code class="xref py py-meth docutils literal notranslate"><span class="pre">collate()</span></code></a>
operator is being applied to the right-hand column, as follows:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="nb">print</span><span class="p">(</span><span class="n">column</span><span class="p">(</span><span class="s1">'x'</span><span class="p">)</span> <span class="o">==</span> <span class="n">literal</span><span class="p">(</span><span class="s1">'somevalue'</span><span class="p">)</span><span class="o">.</span><span class="n">collate</span><span class="p">(</span><span class="s2">"en_EN"</span><span class="p">))</span></pre></div>
</div>
<p>In 0.8, this produces:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">x</span> <span class="o">=</span> <span class="p">:</span><span class="n">param_1</span> <span class="n">COLLATE</span> <span class="n">en_EN</span></pre></div>
</div>
<p>However in 0.9, will now produce the more accurate, but probably not what you
want, form of:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">x</span> <span class="o">=</span> <span class="p">(:</span><span class="n">param_1</span> <span class="n">COLLATE</span> <span class="n">en_EN</span><span class="p">)</span></pre></div>
</div>
<p>The <a class="reference internal" href="../core/sqlelement.html#sqlalchemy.sql.operators.ColumnOperators.collate" title="sqlalchemy.sql.operators.ColumnOperators.collate"><code class="xref py py-meth docutils literal notranslate"><span class="pre">ColumnOperators.collate()</span></code></a> operator now works more appropriately within an
<code class="docutils literal notranslate"><span class="pre">ORDER</span> <span class="pre">BY</span></code> expression as well, as a specific precedence has been given to the
<code class="docutils literal notranslate"><span class="pre">ASC</span></code> and <code class="docutils literal notranslate"><span class="pre">DESC</span></code> operators which will again ensure no parentheses are
generated:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="gp">>>> </span><span class="c1"># 0.8</span>
<span class="gp">>>> </span><span class="nb">print</span><span class="p">(</span><span class="n">column</span><span class="p">(</span><span class="s1">'x'</span><span class="p">)</span><span class="o">.</span><span class="n">collate</span><span class="p">(</span><span class="s1">'en_EN'</span><span class="p">)</span><span class="o">.</span><span class="n">desc</span><span class="p">())</span>
<span class="go">(x COLLATE en_EN) DESC</span>
<span class="gp">>>> </span><span class="c1"># 0.9</span>
<span class="gp">>>> </span><span class="nb">print</span><span class="p">(</span><span class="n">column</span><span class="p">(</span><span class="s1">'x'</span><span class="p">)</span><span class="o">.</span><span class="n">collate</span><span class="p">(</span><span class="s1">'en_EN'</span><span class="p">)</span><span class="o">.</span><span class="n">desc</span><span class="p">())</span>
<span class="go">x COLLATE en_EN DESC</span></pre></div>
</div>
<p><a class="reference external" href="http://www.sqlalchemy.org/trac/ticket/2879">#2879</a></p>
</div>
<div class="section" id="postgresql-create-type-x-as-enum-now-applies-quoting-to-values">
<span id="migration-2878"></span><h3>PostgreSQL CREATE TYPE <x> AS ENUM now applies quoting to values<a class="headerlink" href="#postgresql-create-type-x-as-enum-now-applies-quoting-to-values" title="Permalink to this headline">¶</a></h3>
<p>The <a class="reference internal" href="../dialects/postgresql.html#sqlalchemy.dialects.postgresql.ENUM" title="sqlalchemy.dialects.postgresql.ENUM"><code class="xref py py-class docutils literal notranslate"><span class="pre">postgresql.ENUM</span></code></a> type will now apply escaping to single quote
signs within the enumerated values:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="gp">>>> </span><span class="kn">from</span> <span class="nn">sqlalchemy.dialects</span> <span class="k">import</span> <span class="n">postgresql</span>
<span class="gp">>>> </span><span class="nb">type</span> <span class="o">=</span> <span class="n">postgresql</span><span class="o">.</span><span class="n">ENUM</span><span class="p">(</span><span class="s1">'one'</span><span class="p">,</span> <span class="s1">'two'</span><span class="p">,</span> <span class="s2">"three's"</span><span class="p">,</span> <span class="n">name</span><span class="o">=</span><span class="s2">"myenum"</span><span class="p">)</span>
<span class="gp">>>> </span><span class="kn">from</span> <span class="nn">sqlalchemy.dialects.postgresql</span> <span class="k">import</span> <span class="n">base</span>
<span class="gp">>>> </span><span class="nb">print</span><span class="p">(</span><span class="n">base</span><span class="o">.</span><span class="n">CreateEnumType</span><span class="p">(</span><span class="nb">type</span><span class="p">)</span><span class="o">.</span><span class="n">compile</span><span class="p">(</span><span class="n">dialect</span><span class="o">=</span><span class="n">postgresql</span><span class="o">.</span><span class="n">dialect</span><span class="p">()))</span>
<span class="go">CREATE TYPE myenum AS ENUM ('one','two','three''s')</span></pre></div>
</div>
<p>Existing workarounds which already escape single quote signs will need to be
modified, else they will now double-escape.</p>
<p><a class="reference external" href="http://www.sqlalchemy.org/trac/ticket/2878">#2878</a></p>
</div>
</div>
<div class="section" id="new-features">
<h2>New Features<a class="headerlink" href="#new-features" title="Permalink to this headline">¶</a></h2>
<div class="section" id="event-removal-api">
<span id="feature-2268"></span><h3>Event Removal API<a class="headerlink" href="#event-removal-api" title="Permalink to this headline">¶</a></h3>
<p>Events established using <a class="reference internal" href="../core/event.html#sqlalchemy.event.listen" title="sqlalchemy.event.listen"><code class="xref py py-func docutils literal notranslate"><span class="pre">event.listen()</span></code></a> or <a class="reference internal" href="../core/event.html#sqlalchemy.event.listens_for" title="sqlalchemy.event.listens_for"><code class="xref py py-func docutils literal notranslate"><span class="pre">event.listens_for()</span></code></a>
can now be removed using the new <a class="reference internal" href="../core/event.html#sqlalchemy.event.remove" title="sqlalchemy.event.remove"><code class="xref py py-func docutils literal notranslate"><span class="pre">event.remove()</span></code></a> function. The <code class="docutils literal notranslate"><span class="pre">target</span></code>,
<code class="docutils literal notranslate"><span class="pre">identifier</span></code> and <code class="docutils literal notranslate"><span class="pre">fn</span></code> arguments sent to <a class="reference internal" href="../core/event.html#sqlalchemy.event.remove" title="sqlalchemy.event.remove"><code class="xref py py-func docutils literal notranslate"><span class="pre">event.remove()</span></code></a> need to match
exactly those which were sent for listening, and the event will be removed
from all locations in which it had been established:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="nd">@event</span><span class="o">.</span><span class="n">listens_for</span><span class="p">(</span><span class="n">MyClass</span><span class="p">,</span> <span class="s2">"before_insert"</span><span class="p">,</span> <span class="n">propagate</span><span class="o">=</span><span class="kc">True</span><span class="p">)</span>
<span class="k">def</span> <span class="nf">my_before_insert</span><span class="p">(</span><span class="n">mapper</span><span class="p">,</span> <span class="n">connection</span><span class="p">,</span> <span class="n">target</span><span class="p">):</span>
<span class="sd">"""listen for before_insert"""</span>
<span class="c1"># ...</span>
<span class="n">event</span><span class="o">.</span><span class="n">remove</span><span class="p">(</span><span class="n">MyClass</span><span class="p">,</span> <span class="s2">"before_insert"</span><span class="p">,</span> <span class="n">my_before_insert</span><span class="p">)</span></pre></div>
</div>
<p>In the example above, the <code class="docutils literal notranslate"><span class="pre">propagate=True</span></code> flag is set. This
means <code class="docutils literal notranslate"><span class="pre">my_before_insert()</span></code> is established as a listener for <code class="docutils literal notranslate"><span class="pre">MyClass</span></code>
as well as all subclasses of <code class="docutils literal notranslate"><span class="pre">MyClass</span></code>.
The system tracks everywhere that the <code class="docutils literal notranslate"><span class="pre">my_before_insert()</span></code>
listener function had been placed as a result of this call and removes it as
a result of calling <a class="reference internal" href="../core/event.html#sqlalchemy.event.remove" title="sqlalchemy.event.remove"><code class="xref py py-func docutils literal notranslate"><span class="pre">event.remove()</span></code></a>.</p>
<p>The removal system uses a registry to associate arguments passed to
<a class="reference internal" href="../core/event.html#sqlalchemy.event.listen" title="sqlalchemy.event.listen"><code class="xref py py-func docutils literal notranslate"><span class="pre">event.listen()</span></code></a> with collections of event listeners, which are in many
cases wrapped versions of the original user-supplied function. This registry
makes heavy use of weak references in order to allow all the contained contents,
such as listener targets, to be garbage collected when they go out of scope.</p>
<p><a class="reference external" href="http://www.sqlalchemy.org/trac/ticket/2268">#2268</a></p>
</div>
<div class="section" id="new-query-options-api-load-only-option">
<span id="feature-1418"></span><h3>New Query Options API; <code class="docutils literal notranslate"><span class="pre">load_only()</span></code> option<a class="headerlink" href="#new-query-options-api-load-only-option" title="Permalink to this headline">¶</a></h3>
<p>The system of loader options such as <a class="reference internal" href="../orm/loading_relationships.html#sqlalchemy.orm.joinedload" title="sqlalchemy.orm.joinedload"><code class="xref py py-func docutils literal notranslate"><span class="pre">orm.joinedload()</span></code></a>,
<a class="reference internal" href="../orm/loading_relationships.html#sqlalchemy.orm.subqueryload" title="sqlalchemy.orm.subqueryload"><code class="xref py py-func docutils literal notranslate"><span class="pre">orm.subqueryload()</span></code></a>, <a class="reference internal" href="../orm/loading_relationships.html#sqlalchemy.orm.lazyload" title="sqlalchemy.orm.lazyload"><code class="xref py py-func docutils literal notranslate"><span class="pre">orm.lazyload()</span></code></a>, <a class="reference internal" href="../orm/loading_columns.html#sqlalchemy.orm.defer" title="sqlalchemy.orm.defer"><code class="xref py py-func docutils literal notranslate"><span class="pre">orm.defer()</span></code></a>, etc.
all build upon a new system known as <a class="reference internal" href="../orm/loading_relationships.html#sqlalchemy.orm.Load" title="sqlalchemy.orm.Load"><code class="xref py py-class docutils literal notranslate"><span class="pre">Load</span></code></a>. <a class="reference internal" href="../orm/loading_relationships.html#sqlalchemy.orm.Load" title="sqlalchemy.orm.Load"><code class="xref py py-class docutils literal notranslate"><span class="pre">Load</span></code></a> provides
a “method chained” (a.k.a. <a class="reference internal" href="../glossary.html#term-generative"><span class="xref std std-term">generative</span></a>) approach to loader options, so that
instead of joining together long paths using dots or multiple attribute names,
an explicit loader style is given for each path.</p>
<p>While the new way is slightly more verbose, it is simpler to understand
in that there is no ambiguity in what options are being applied to which paths;
it simplifies the method signatures of the options and provides greater flexibility
particularly for column-based options. The old systems are to remain functional
indefinitely as well and all styles can be mixed.</p>
<p><strong>Old Way</strong></p>
<p>To set a certain style of loading along every link in a multi-element path, the <code class="docutils literal notranslate"><span class="pre">_all()</span></code>
option has to be used:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></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">options</span><span class="p">(</span><span class="n">joinedload_all</span><span class="p">(</span><span class="s2">"orders.items.keywords"</span><span class="p">))</span></pre></div>
</div>
<p><strong>New Way</strong></p>
<p>Loader options are now chainable, so the same <code class="docutils literal notranslate"><span class="pre">joinedload(x)</span></code> method is applied
equally to each link, without the need to keep straight between
<a class="reference internal" href="../orm/loading_relationships.html#sqlalchemy.orm.joinedload" title="sqlalchemy.orm.joinedload"><code class="xref py py-func docutils literal notranslate"><span class="pre">joinedload()</span></code></a> and <a class="reference internal" href="../orm/loading_relationships.html#sqlalchemy.orm.joinedload_all" title="sqlalchemy.orm.joinedload_all"><code class="xref py py-func docutils literal notranslate"><span class="pre">joinedload_all()</span></code></a>:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></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">options</span><span class="p">(</span><span class="n">joinedload</span><span class="p">(</span><span class="s2">"orders"</span><span class="p">)</span><span class="o">.</span><span class="n">joinedload</span><span class="p">(</span><span class="s2">"items"</span><span class="p">)</span><span class="o">.</span><span class="n">joinedload</span><span class="p">(</span><span class="s2">"keywords"</span><span class="p">))</span></pre></div>
</div>
<p><strong>Old Way</strong></p>
<p>Setting an option on path that is based on a subclass requires that all
links in the path be spelled out as class bound attributes, since the
<a class="reference internal" href="../orm/internals.html#sqlalchemy.orm.interfaces.PropComparator.of_type" title="sqlalchemy.orm.interfaces.PropComparator.of_type"><code class="xref py py-meth docutils literal notranslate"><span class="pre">PropComparator.of_type()</span></code></a> method needs to be called:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">session</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="n">Company</span><span class="p">)</span><span class="o">.</span>\
<span class="n">options</span><span class="p">(</span>
<span class="n">subqueryload_all</span><span class="p">(</span>
<span class="n">Company</span><span class="o">.</span><span class="n">employees</span><span class="o">.</span><span class="n">of_type</span><span class="p">(</span><span class="n">Engineer</span><span class="p">),</span>
<span class="n">Engineer</span><span class="o">.</span><span class="n">machines</span>
<span class="p">)</span>
<span class="p">)</span></pre></div>
</div>
<p><strong>New Way</strong></p>
<p>Only those elements in the path that actually need <a class="reference internal" href="../orm/internals.html#sqlalchemy.orm.interfaces.PropComparator.of_type" title="sqlalchemy.orm.interfaces.PropComparator.of_type"><code class="xref py py-meth docutils literal notranslate"><span class="pre">PropComparator.of_type()</span></code></a>
need to be set as a class-bound attribute, string-based names can be resumed
afterwards:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">session</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="n">Company</span><span class="p">)</span><span class="o">.</span>\
<span class="n">options</span><span class="p">(</span>
<span class="n">subqueryload</span><span class="p">(</span><span class="n">Company</span><span class="o">.</span><span class="n">employees</span><span class="o">.</span><span class="n">of_type</span><span class="p">(</span><span class="n">Engineer</span><span class="p">))</span><span class="o">.</span>
<span class="n">subqueryload</span><span class="p">(</span><span class="s2">"machines"</span><span class="p">)</span>
<span class="p">)</span>
<span class="p">)</span></pre></div>
</div>
<p><strong>Old Way</strong></p>
<p>Setting the loader option on the last link in a long path uses a syntax
that looks a lot like it should be setting the option for all links in the
path, causing confusion:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></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">options</span><span class="p">(</span><span class="n">subqueryload</span><span class="p">(</span><span class="s2">"orders.items.keywords"</span><span class="p">))</span></pre></div>
</div>
<p><strong>New Way</strong></p>
<p>A path can now be spelled out using <a class="reference internal" href="../orm/loading_relationships.html#sqlalchemy.orm.defaultload" title="sqlalchemy.orm.defaultload"><code class="xref py py-func docutils literal notranslate"><span class="pre">defaultload()</span></code></a> for entries in the
path where the existing loader style should be unchanged. More verbose
but the intent is clearer:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></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">options</span><span class="p">(</span><span class="n">defaultload</span><span class="p">(</span><span class="s2">"orders"</span><span class="p">)</span><span class="o">.</span><span class="n">defaultload</span><span class="p">(</span><span class="s2">"items"</span><span class="p">)</span><span class="o">.</span><span class="n">subqueryload</span><span class="p">(</span><span class="s2">"keywords"</span><span class="p">))</span></pre></div>
</div>
<p>The dotted style can still be taken advantage of, particularly in the case
of skipping over several path elements:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></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">options</span><span class="p">(</span><span class="n">defaultload</span><span class="p">(</span><span class="s2">"orders.items"</span><span class="p">)</span><span class="o">.</span><span class="n">subqueryload</span><span class="p">(</span><span class="s2">"keywords"</span><span class="p">))</span></pre></div>
</div>
<p><strong>Old Way</strong></p>
<p>The <a class="reference internal" href="../orm/loading_columns.html#sqlalchemy.orm.defer" title="sqlalchemy.orm.defer"><code class="xref py py-func docutils literal notranslate"><span class="pre">defer()</span></code></a> option on a path needed to be spelled out with the full
path for each column:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></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">options</span><span class="p">(</span><span class="n">defer</span><span class="p">(</span><span class="s2">"orders.description"</span><span class="p">),</span> <span class="n">defer</span><span class="p">(</span><span class="s2">"orders.isopen"</span><span class="p">))</span></pre></div>
</div>
<p><strong>New Way</strong></p>
<p>A single <a class="reference internal" href="../orm/loading_relationships.html#sqlalchemy.orm.Load" title="sqlalchemy.orm.Load"><code class="xref py py-class docutils literal notranslate"><span class="pre">Load</span></code></a> object that arrives at the target path can have
<a class="reference internal" href="../orm/query.html#sqlalchemy.orm.strategy_options.Load.defer" title="sqlalchemy.orm.strategy_options.Load.defer"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Load.defer()</span></code></a> called upon it repeatedly:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></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">options</span><span class="p">(</span><span class="n">defaultload</span><span class="p">(</span><span class="s2">"orders"</span><span class="p">)</span><span class="o">.</span><span class="n">defer</span><span class="p">(</span><span class="s2">"description"</span><span class="p">)</span><span class="o">.</span><span class="n">defer</span><span class="p">(</span><span class="s2">"isopen"</span><span class="p">))</span></pre></div>
</div>
<div class="section" id="the-load-class">
<h4>The Load Class<a class="headerlink" href="#the-load-class" title="Permalink to this headline">¶</a></h4>
<p>The <a class="reference internal" href="../orm/loading_relationships.html#sqlalchemy.orm.Load" title="sqlalchemy.orm.Load"><code class="xref py py-class docutils literal notranslate"><span class="pre">Load</span></code></a> class can be used directly to provide a “bound” target,
especially when multiple parent entities are present:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="kn">from</span> <span class="nn">sqlalchemy.orm</span> <span class="k">import</span> <span class="n">Load</span>
<span class="n">query</span><span class="p">(</span><span class="n">User</span><span class="p">,</span> <span class="n">Address</span><span class="p">)</span><span class="o">.</span><span class="n">options</span><span class="p">(</span><span class="n">Load</span><span class="p">(</span><span class="n">Address</span><span class="p">)</span><span class="o">.</span><span class="n">joinedload</span><span class="p">(</span><span class="s2">"entries"</span><span class="p">))</span></pre></div>
</div>
</div>
<div class="section" id="load-only">
<h4>Load Only<a class="headerlink" href="#load-only" title="Permalink to this headline">¶</a></h4>
<p>A new option <a class="reference internal" href="../orm/loading_columns.html#sqlalchemy.orm.load_only" title="sqlalchemy.orm.load_only"><code class="xref py py-func docutils literal notranslate"><span class="pre">load_only()</span></code></a> achieves a “defer everything but” style of load,
loading only the given columns and deferring the rest:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="kn">from</span> <span class="nn">sqlalchemy.orm</span> <span class="k">import</span> <span class="n">load_only</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">options</span><span class="p">(</span><span class="n">load_only</span><span class="p">(</span><span class="s2">"name"</span><span class="p">,</span> <span class="s2">"fullname"</span><span class="p">))</span>
<span class="c1"># specify explicit parent entity</span>
<span class="n">query</span><span class="p">(</span><span class="n">User</span><span class="p">,</span> <span class="n">Address</span><span class="p">)</span><span class="o">.</span><span class="n">options</span><span class="p">(</span><span class="n">Load</span><span class="p">(</span><span class="n">User</span><span class="p">)</span><span class="o">.</span><span class="n">load_only</span><span class="p">(</span><span class="s2">"name"</span><span class="p">,</span> <span class="s2">"fullname"</span><span class="p">))</span>
<span class="c1"># specify path</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">options</span><span class="p">(</span><span class="n">joinedload</span><span class="p">(</span><span class="n">User</span><span class="o">.</span><span class="n">addresses</span><span class="p">)</span><span class="o">.</span><span class="n">load_only</span><span class="p">(</span><span class="s2">"email_address"</span><span class="p">))</span></pre></div>
</div>
</div>
<div class="section" id="class-specific-wildcards">
<h4>Class-specific Wildcards<a class="headerlink" href="#class-specific-wildcards" title="Permalink to this headline">¶</a></h4>
<p>Using <a class="reference internal" href="../orm/loading_relationships.html#sqlalchemy.orm.Load" title="sqlalchemy.orm.Load"><code class="xref py py-class docutils literal notranslate"><span class="pre">Load</span></code></a>, a wildcard may be used to set the loading for all
relationships (or perhaps columns) on a given entity, without affecting any
others:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="c1"># lazyload all User relationships</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">options</span><span class="p">(</span><span class="n">Load</span><span class="p">(</span><span class="n">User</span><span class="p">)</span><span class="o">.</span><span class="n">lazyload</span><span class="p">(</span><span class="s2">"*"</span><span class="p">))</span>
<span class="c1"># undefer all User columns</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">options</span><span class="p">(</span><span class="n">Load</span><span class="p">(</span><span class="n">User</span><span class="p">)</span><span class="o">.</span><span class="n">undefer</span><span class="p">(</span><span class="s2">"*"</span><span class="p">))</span>
<span class="c1"># lazyload all Address relationships</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">options</span><span class="p">(</span><span class="n">defaultload</span><span class="p">(</span><span class="n">User</span><span class="o">.</span><span class="n">addresses</span><span class="p">)</span><span class="o">.</span><span class="n">lazyload</span><span class="p">(</span><span class="s2">"*"</span><span class="p">))</span>
<span class="c1"># undefer all Address columns</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">options</span><span class="p">(</span><span class="n">defaultload</span><span class="p">(</span><span class="n">User</span><span class="o">.</span><span class="n">addresses</span><span class="p">)</span><span class="o">.</span><span class="n">undefer</span><span class="p">(</span><span class="s2">"*"</span><span class="p">))</span></pre></div>
</div>
<p><a class="reference external" href="http://www.sqlalchemy.org/trac/ticket/1418">#1418</a></p>
</div>
</div>
<div class="section" id="new-text-capabilities">
<span id="feature-2877"></span><h3>New <code class="docutils literal notranslate"><span class="pre">text()</span></code> Capabilities<a class="headerlink" href="#new-text-capabilities" title="Permalink to this headline">¶</a></h3>
<p>The <a class="reference internal" href="../core/sqlelement.html#sqlalchemy.sql.expression.text" title="sqlalchemy.sql.expression.text"><code class="xref py py-func docutils literal notranslate"><span class="pre">text()</span></code></a> construct gains new methods:</p>
<ul>
<li><p><a class="reference internal" href="../core/sqlelement.html#sqlalchemy.sql.expression.TextClause.bindparams" title="sqlalchemy.sql.expression.TextClause.bindparams"><code class="xref py py-meth docutils literal notranslate"><span class="pre">TextClause.bindparams()</span></code></a> allows bound parameter types and values
to be set flexibly:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="c1"># setup values</span>
<span class="n">stmt</span> <span class="o">=</span> <span class="n">text</span><span class="p">(</span><span class="s2">"SELECT id, name FROM user "</span>
<span class="s2">"WHERE name=:name AND timestamp=:timestamp"</span><span class="p">)</span><span class="o">.</span>\
<span class="n">bindparams</span><span class="p">(</span><span class="n">name</span><span class="o">=</span><span class="s2">"ed"</span><span class="p">,</span> <span class="n">timestamp</span><span class="o">=</span><span class="n">datetime</span><span class="p">(</span><span class="mi">2012</span><span class="p">,</span> <span class="mi">11</span><span class="p">,</span> <span class="mi">10</span><span class="p">,</span> <span class="mi">15</span><span class="p">,</span> <span class="mi">12</span><span class="p">,</span> <span class="mi">35</span><span class="p">))</span>
<span class="c1"># setup types and/or values</span>
<span class="n">stmt</span> <span class="o">=</span> <span class="n">text</span><span class="p">(</span><span class="s2">"SELECT id, name FROM user "</span>
<span class="s2">"WHERE name=:name AND timestamp=:timestamp"</span><span class="p">)</span><span class="o">.</span>\
<span class="n">bindparams</span><span class="p">(</span>
<span class="n">bindparam</span><span class="p">(</span><span class="s2">"name"</span><span class="p">,</span> <span class="n">value</span><span class="o">=</span><span class="s2">"ed"</span><span class="p">),</span>
<span class="n">bindparam</span><span class="p">(</span><span class="s2">"timestamp"</span><span class="p">,</span> <span class="n">type_</span><span class="o">=</span><span class="n">DateTime</span><span class="p">()</span>
<span class="p">)</span><span class="o">.</span><span class="n">bindparam</span><span class="p">(</span><span class="n">timestamp</span><span class="o">=</span><span class="n">datetime</span><span class="p">(</span><span class="mi">2012</span><span class="p">,</span> <span class="mi">11</span><span class="p">,</span> <span class="mi">10</span><span class="p">,</span> <span class="mi">15</span><span class="p">,</span> <span class="mi">12</span><span class="p">,</span> <span class="mi">35</span><span class="p">))</span></pre></div>
</div>
</li>
<li><p><a class="reference internal" href="../core/sqlelement.html#sqlalchemy.sql.expression.TextClause.columns" title="sqlalchemy.sql.expression.TextClause.columns"><code class="xref py py-meth docutils literal notranslate"><span class="pre">TextClause.columns()</span></code></a> supersedes the <code class="docutils literal notranslate"><span class="pre">typemap</span></code> option
of <a class="reference internal" href="../core/sqlelement.html#sqlalchemy.sql.expression.text" title="sqlalchemy.sql.expression.text"><code class="xref py py-func docutils literal notranslate"><span class="pre">text()</span></code></a>, returning a new construct <a class="reference internal" href="../core/selectable.html#sqlalchemy.sql.expression.TextAsFrom" title="sqlalchemy.sql.expression.TextAsFrom"><code class="xref py py-class docutils literal notranslate"><span class="pre">TextAsFrom</span></code></a>:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="c1"># turn a text() into an alias(), with a .c. collection:</span>
<span class="n">stmt</span> <span class="o">=</span> <span class="n">text</span><span class="p">(</span><span class="s2">"SELECT id, name FROM user"</span><span class="p">)</span><span class="o">.</span><span class="n">columns</span><span class="p">(</span><span class="nb">id</span><span class="o">=</span><span class="n">Integer</span><span class="p">,</span> <span class="n">name</span><span class="o">=</span><span class="n">String</span><span class="p">)</span>
<span class="n">stmt</span> <span class="o">=</span> <span class="n">stmt</span><span class="o">.</span><span class="n">alias</span><span class="p">()</span>
<span class="n">stmt</span> <span class="o">=</span> <span class="n">select</span><span class="p">([</span><span class="n">addresses</span><span class="p">])</span><span class="o">.</span><span class="n">select_from</span><span class="p">(</span>
<span class="n">addresses</span><span class="o">.</span><span class="n">join</span><span class="p">(</span><span class="n">stmt</span><span class="p">),</span> <span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">user_id</span> <span class="o">==</span> <span class="n">stmt</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">id</span><span class="p">)</span>
<span class="c1"># or into a cte():</span>
<span class="n">stmt</span> <span class="o">=</span> <span class="n">text</span><span class="p">(</span><span class="s2">"SELECT id, name FROM user"</span><span class="p">)</span><span class="o">.</span><span class="n">columns</span><span class="p">(</span><span class="nb">id</span><span class="o">=</span><span class="n">Integer</span><span class="p">,</span> <span class="n">name</span><span class="o">=</span><span class="n">String</span><span class="p">)</span>
<span class="n">stmt</span> <span class="o">=</span> <span class="n">stmt</span><span class="o">.</span><span class="n">cte</span><span class="p">(</span><span class="s2">"x"</span><span class="p">)</span>
<span class="n">stmt</span> <span class="o">=</span> <span class="n">select</span><span class="p">([</span><span class="n">addresses</span><span class="p">])</span><span class="o">.</span><span class="n">select_from</span><span class="p">(</span>
<span class="n">addresses</span><span class="o">.</span><span class="n">join</span><span class="p">(</span><span class="n">stmt</span><span class="p">),</span> <span class="n">addresses</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">user_id</span> <span class="o">==</span> <span class="n">stmt</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">id</span><span class="p">)</span></pre></div>
</div>
</li>
</ul>
<p><a class="reference external" href="http://www.sqlalchemy.org/trac/ticket/2877">#2877</a></p>
</div>
<div class="section" id="insert-from-select">
<span id="feature-722"></span><h3>INSERT from SELECT<a class="headerlink" href="#insert-from-select" title="Permalink to this headline">¶</a></h3>
<p>After literally years of pointless procrastination this relatively minor
syntactical feature has been added, and is also backported to 0.8.3,
so technically isn’t “new” in 0.9. A <a class="reference internal" href="../core/selectable.html#sqlalchemy.sql.expression.select" title="sqlalchemy.sql.expression.select"><code class="xref py py-func docutils literal notranslate"><span class="pre">select()</span></code></a> construct or other
compatible construct can be passed to the new method <a class="reference internal" href="../core/dml.html#sqlalchemy.sql.expression.Insert.from_select" title="sqlalchemy.sql.expression.Insert.from_select"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Insert.from_select()</span></code></a>
where it will be used to render an <code class="docutils literal notranslate"><span class="pre">INSERT</span> <span class="pre">..</span> <span class="pre">SELECT</span></code> construct:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="gp">>>> </span><span class="kn">from</span> <span class="nn">sqlalchemy.sql</span> <span class="k">import</span> <span class="n">table</span><span class="p">,</span> <span class="n">column</span>
<span class="gp">>>> </span><span class="n">t1</span> <span class="o">=</span> <span class="n">table</span><span class="p">(</span><span class="s1">'t1'</span><span class="p">,</span> <span class="n">column</span><span class="p">(</span><span class="s1">'a'</span><span class="p">),</span> <span class="n">column</span><span class="p">(</span><span class="s1">'b'</span><span class="p">))</span>
<span class="gp">>>> </span><span class="n">t2</span> <span class="o">=</span> <span class="n">table</span><span class="p">(</span><span class="s1">'t2'</span><span class="p">,</span> <span class="n">column</span><span class="p">(</span><span class="s1">'x'</span><span class="p">),</span> <span class="n">column</span><span class="p">(</span><span class="s1">'y'</span><span class="p">))</span>
<span class="gp">>>> </span><span class="nb">print</span><span class="p">(</span><span class="n">t1</span><span class="o">.</span><span class="n">insert</span><span class="p">()</span><span class="o">.</span><span class="n">from_select</span><span class="p">([</span><span class="s1">'a'</span><span class="p">,</span> <span class="s1">'b'</span><span class="p">],</span> <span class="n">t2</span><span class="o">.</span><span class="n">select</span><span class="p">()</span><span class="o">.</span><span class="n">where</span><span class="p">(</span><span class="n">t2</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">y</span> <span class="o">==</span> <span class="mi">5</span><span class="p">)))</span>
<span class="go">INSERT INTO t1 (a, b) SELECT t2.x, t2.y</span>
<span class="go">FROM t2</span>
<span class="go">WHERE t2.y = :y_1</span></pre></div>
</div>
<p>The construct is smart enough to also accommodate ORM objects such as classes
and <a class="reference internal" href="../orm/query.html#sqlalchemy.orm.query.Query" title="sqlalchemy.orm.query.Query"><code class="xref py py-class docutils literal notranslate"><span class="pre">Query</span></code></a> objects:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">s</span> <span class="o">=</span> <span class="n">Session</span><span class="p">()</span>
<span class="n">q</span> <span class="o">=</span> <span class="n">s</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="n">User</span><span class="o">.</span><span class="n">id</span><span class="p">,</span> <span class="n">User</span><span class="o">.</span><span class="n">name</span><span class="p">)</span><span class="o">.</span><span class="n">filter_by</span><span class="p">(</span><span class="n">name</span><span class="o">=</span><span class="s1">'ed'</span><span class="p">)</span>
<span class="n">ins</span> <span class="o">=</span> <span class="n">insert</span><span class="p">(</span><span class="n">Address</span><span class="p">)</span><span class="o">.</span><span class="n">from_select</span><span class="p">((</span><span class="n">Address</span><span class="o">.</span><span class="n">id</span><span class="p">,</span> <span class="n">Address</span><span class="o">.</span><span class="n">email_address</span><span class="p">),</span> <span class="n">q</span><span class="p">)</span></pre></div>
</div>
<p>rendering:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">INSERT</span> <span class="n">INTO</span> <span class="n">addresses</span> <span class="p">(</span><span class="nb">id</span><span class="p">,</span> <span class="n">email_address</span><span class="p">)</span>
<span class="n">SELECT</span> <span class="n">users</span><span class="o">.</span><span class="n">id</span> <span class="n">AS</span> <span class="n">users_id</span><span class="p">,</span> <span class="n">users</span><span class="o">.</span><span class="n">name</span> <span class="n">AS</span> <span class="n">users_name</span>
<span class="n">FROM</span> <span class="n">users</span> <span class="n">WHERE</span> <span class="n">users</span><span class="o">.</span><span class="n">name</span> <span class="o">=</span> <span class="p">:</span><span class="n">name_1</span></pre></div>
</div>
<p><a class="reference external" href="http://www.sqlalchemy.org/trac/ticket/722">#722</a></p>
</div>
<div class="section" id="new-for-update-support-on-select-query">
<span id="feature-github-42"></span><h3>New FOR UPDATE support on <code class="docutils literal notranslate"><span class="pre">select()</span></code>, <code class="docutils literal notranslate"><span class="pre">Query()</span></code><a class="headerlink" href="#new-for-update-support-on-select-query" title="Permalink to this headline">¶</a></h3>
<p>An attempt is made to simplify the specification of the <code class="docutils literal notranslate"><span class="pre">FOR</span> <span class="pre">UPDATE</span></code>
clause on <code class="docutils literal notranslate"><span class="pre">SELECT</span></code> statements made within Core and ORM, and support is added
for the <code class="docutils literal notranslate"><span class="pre">FOR</span> <span class="pre">UPDATE</span> <span class="pre">OF</span></code> SQL supported by PostgreSQL and Oracle.</p>
<p>Using the core <a class="reference internal" href="../core/selectable.html#sqlalchemy.sql.expression.GenerativeSelect.with_for_update" title="sqlalchemy.sql.expression.GenerativeSelect.with_for_update"><code class="xref py py-meth docutils literal notranslate"><span class="pre">GenerativeSelect.with_for_update()</span></code></a>, options like <code class="docutils literal notranslate"><span class="pre">FOR</span> <span class="pre">SHARE</span></code> and
<code class="docutils literal notranslate"><span class="pre">NOWAIT</span></code> can be specified individually, rather than linking to arbitrary
string codes:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">stmt</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="o">.</span><span class="n">with_for_update</span><span class="p">(</span><span class="n">read</span><span class="o">=</span><span class="kc">True</span><span class="p">,</span> <span class="n">nowait</span><span class="o">=</span><span class="kc">True</span><span class="p">,</span> <span class="n">of</span><span class="o">=</span><span class="n">table</span><span class="p">)</span></pre></div>
</div>
<p>On Posgtresql the above statement might render like:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">SELECT</span> <span class="n">table</span><span class="o">.</span><span class="n">a</span><span class="p">,</span> <span class="n">table</span><span class="o">.</span><span class="n">b</span> <span class="n">FROM</span> <span class="n">table</span> <span class="n">FOR</span> <span class="n">SHARE</span> <span class="n">OF</span> <span class="n">table</span> <span class="n">NOWAIT</span></pre></div>
</div>
<p>The <a class="reference internal" href="../orm/query.html#sqlalchemy.orm.query.Query" title="sqlalchemy.orm.query.Query"><code class="xref py py-class docutils literal notranslate"><span class="pre">Query</span></code></a> object gains a similar method <a class="reference internal" href="../orm/query.html#sqlalchemy.orm.query.Query.with_for_update" title="sqlalchemy.orm.query.Query.with_for_update"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Query.with_for_update()</span></code></a>
which behaves in the same way. This method supersedes the existing
<a class="reference internal" href="../orm/query.html#sqlalchemy.orm.query.Query.with_lockmode" title="sqlalchemy.orm.query.Query.with_lockmode"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Query.with_lockmode()</span></code></a> method, which translated <code class="docutils literal notranslate"><span class="pre">FOR</span> <span class="pre">UPDATE</span></code> clauses
using a different system. At the moment, the “lockmode” string argument is still
accepted by the <a class="reference internal" href="../orm/session_api.html#sqlalchemy.orm.session.Session.refresh" title="sqlalchemy.orm.session.Session.refresh"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Session.refresh()</span></code></a> method.</p>
</div>
<div class="section" id="floating-point-string-conversion-precision-configurable-for-native-floating-point-types">
<span id="feature-2867"></span><h3>Floating Point String-Conversion Precision Configurable for Native Floating Point Types<a class="headerlink" href="#floating-point-string-conversion-precision-configurable-for-native-floating-point-types" title="Permalink to this headline">¶</a></h3>
<p>The conversion which SQLAlchemy does whenever a DBAPI returns a Python
floating point type which is to be converted into a Python <code class="docutils literal notranslate"><span class="pre">Decimal()</span></code>
necessarily involves an intermediary step which converts the floating point
value to a string. The scale used for this string conversion was previously
hardcoded to 10, and is now configurable. The setting is available on
both the <a class="reference internal" href="../core/type_basics.html#sqlalchemy.types.Numeric" title="sqlalchemy.types.Numeric"><code class="xref py py-class docutils literal notranslate"><span class="pre">Numeric</span></code></a> as well as the <a class="reference internal" href="../core/type_basics.html#sqlalchemy.types.Float" title="sqlalchemy.types.Float"><code class="xref py py-class docutils literal notranslate"><span class="pre">Float</span></code></a>
type, as well as all SQL- and dialect-specific descendant types, using the
parameter <code class="docutils literal notranslate"><span class="pre">decimal_return_scale</span></code>. If the type supports a <code class="docutils literal notranslate"><span class="pre">.scale</span></code> parameter,
as is the case with <a class="reference internal" href="../core/type_basics.html#sqlalchemy.types.Numeric" title="sqlalchemy.types.Numeric"><code class="xref py py-class docutils literal notranslate"><span class="pre">Numeric</span></code></a> and some float types such as
<a class="reference internal" href="../dialects/mysql.html#sqlalchemy.dialects.mysql.DOUBLE" title="sqlalchemy.dialects.mysql.DOUBLE"><code class="xref py py-class docutils literal notranslate"><span class="pre">mysql.DOUBLE</span></code></a>, the value of <code class="docutils literal notranslate"><span class="pre">.scale</span></code> is used as the default
for <code class="docutils literal notranslate"><span class="pre">.decimal_return_scale</span></code> if it is not otherwise specified. If both
<code class="docutils literal notranslate"><span class="pre">.scale</span></code> and <code class="docutils literal notranslate"><span class="pre">.decimal_return_scale</span></code> are absent, then the default of
10 takes place. E.g.:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="kn">from</span> <span class="nn">sqlalchemy.dialects.mysql</span> <span class="k">import</span> <span class="n">DOUBLE</span>
<span class="kn">import</span> <span class="nn">decimal</span>
<span class="n">data</span> <span class="o">=</span> <span class="n">Table</span><span class="p">(</span><span class="s1">'data'</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="s1">'double_value'</span><span class="p">,</span>
<span class="n">mysql</span><span class="o">.</span><span class="n">DOUBLE</span><span class="p">(</span><span class="n">decimal_return_scale</span><span class="o">=</span><span class="mi">12</span><span class="p">,</span> <span class="n">asdecimal</span><span class="o">=</span><span class="kc">True</span><span class="p">))</span>
<span class="p">)</span>
<span class="n">conn</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span>
<span class="n">data</span><span class="o">.</span><span class="n">insert</span><span class="p">(),</span>
<span class="n">double_value</span><span class="o">=</span><span class="mf">45.768392065789</span><span class="p">,</span>
<span class="p">)</span>
<span class="n">result</span> <span class="o">=</span> <span class="n">conn</span><span class="o">.</span><span class="n">scalar</span><span class="p">(</span><span class="n">select</span><span class="p">([</span><span class="n">data</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">double_value</span><span class="p">]))</span>
<span class="c1"># previously, this would typically be Decimal("45.7683920658"),</span>
<span class="c1"># e.g. trimmed to 10 decimal places</span>
<span class="c1"># now we get 12, as requested, as MySQL can support this</span>
<span class="c1"># much precision for DOUBLE</span>
<span class="k">assert</span> <span class="n">result</span> <span class="o">==</span> <span class="n">decimal</span><span class="o">.</span><span class="n">Decimal</span><span class="p">(</span><span class="s2">"45.768392065789"</span><span class="p">)</span></pre></div>
</div>
<p><a class="reference external" href="http://www.sqlalchemy.org/trac/ticket/2867">#2867</a></p>
</div>
<div class="section" id="column-bundles-for-orm-queries">
<span id="change-2824"></span><h3>Column Bundles for ORM queries<a class="headerlink" href="#column-bundles-for-orm-queries" title="Permalink to this headline">¶</a></h3>
<p>The <a class="reference internal" href="../orm/query.html#sqlalchemy.orm.query.Bundle" title="sqlalchemy.orm.query.Bundle"><code class="xref py py-class docutils literal notranslate"><span class="pre">Bundle</span></code></a> allows for querying of sets of columns, which are then
grouped into one name under the tuple returned by the query. The initial
purposes of <a class="reference internal" href="../orm/query.html#sqlalchemy.orm.query.Bundle" title="sqlalchemy.orm.query.Bundle"><code class="xref py py-class docutils literal notranslate"><span class="pre">Bundle</span></code></a> are 1. to allow “composite” ORM columns to be
returned as a single value in a column-based result set, rather than expanding
them out into individual columns and 2. to allow the creation of custom result-set
constructs within the ORM, using ad-hoc columns and return types, without involving
the more heavyweight mechanics of mapped classes.</p>
<div class="admonition seealso">
<p class="admonition-title">See also</p>
<p><a class="reference internal" href="#migration-2824"><span class="std std-ref">Composite attributes are now returned as their object form when queried on a per-attribute basis</span></a></p>
<p><a class="reference internal" href="../orm/loading_columns.html#bundles"><span class="std std-ref">Column Bundles</span></a></p>
</div>
<p><a class="reference external" href="http://www.sqlalchemy.org/trac/ticket/2824">#2824</a></p>
</div>
<div class="section" id="server-side-version-counting">
<h3>Server Side Version Counting<a class="headerlink" href="#server-side-version-counting" title="Permalink to this headline">¶</a></h3>
<p>The versioning feature of the ORM (now also documented at <a class="reference internal" href="../orm/versioning.html#mapper-version-counter"><span class="std std-ref">Configuring a Version Counter</span></a>)
can now make use of server-side version counting schemes, such as those produced
by triggers or database system columns, as well as conditional programmatic schemes outside
of the version_id_counter function itself. By providing the value <code class="docutils literal notranslate"><span class="pre">False</span></code>
to the <code class="docutils literal notranslate"><span class="pre">version_id_generator</span></code> parameter, the ORM will use the already-set version
identifier, or alternatively fetch the version identifier
from each row at the same time the INSERT or UPDATE is emitted. When using a
server-generated version identifier, it is strongly
recommended that this feature be used only on a backend with strong RETURNING
support (PostgreSQL, SQL Server; Oracle also supports RETURNING but the cx_oracle
driver has only limited support), else the additional SELECT statements will
add significant performance
overhead. The example provided at <a class="reference internal" href="../orm/versioning.html#server-side-version-counter"><span class="std std-ref">Server Side Version Counters</span></a> illustrates
the usage of the PostgreSQL <code class="docutils literal notranslate"><span class="pre">xmin</span></code> system column in order to integrate it with
the ORM’s versioning feature.</p>
<div class="admonition seealso">
<p class="admonition-title">See also</p>
<p><a class="reference internal" href="../orm/versioning.html#server-side-version-counter"><span class="std std-ref">Server Side Version Counters</span></a></p>
</div>
<p><a class="reference external" href="http://www.sqlalchemy.org/trac/ticket/2793">#2793</a></p>
</div>
<div class="section" id="include-backrefs-false-option-for-validates">
<span id="feature-1535"></span><h3><code class="docutils literal notranslate"><span class="pre">include_backrefs=False</span></code> option for <code class="docutils literal notranslate"><span class="pre">@validates</span></code><a class="headerlink" href="#include-backrefs-false-option-for-validates" title="Permalink to this headline">¶</a></h3>
<p>The <a class="reference internal" href="../orm/mapped_attributes.html#sqlalchemy.orm.validates" title="sqlalchemy.orm.validates"><code class="xref py py-func docutils literal notranslate"><span class="pre">validates()</span></code></a> function now accepts an option <code class="docutils literal notranslate"><span class="pre">include_backrefs=True</span></code>,
which will bypass firing the validator for the case where the event initiated
from a backref:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="kn">from</span> <span class="nn">sqlalchemy</span> <span class="k">import</span> <span class="n">Column</span><span class="p">,</span> <span class="n">Integer</span><span class="p">,</span> <span class="n">ForeignKey</span>
<span class="kn">from</span> <span class="nn">sqlalchemy.orm</span> <span class="k">import</span> <span class="n">relationship</span><span class="p">,</span> <span class="n">validates</span>
<span class="kn">from</span> <span class="nn">sqlalchemy.ext.declarative</span> <span class="k">import</span> <span class="n">declarative_base</span>
<span class="n">Base</span> <span class="o">=</span> <span class="n">declarative_base</span><span class="p">()</span>
<span class="k">class</span> <span class="nc">A</span><span class="p">(</span><span class="n">Base</span><span class="p">):</span>
<span class="n">__tablename__</span> <span class="o">=</span> <span class="s1">'a'</span>
<span class="nb">id</span> <span class="o">=</span> <span class="n">Column</span><span class="p">(</span><span class="n">Integer</span><span class="p">,</span> <span class="n">primary_key</span><span class="o">=</span><span class="kc">True</span><span class="p">)</span>
<span class="n">bs</span> <span class="o">=</span> <span class="n">relationship</span><span class="p">(</span><span class="s2">"B"</span><span class="p">,</span> <span class="n">backref</span><span class="o">=</span><span class="s2">"a"</span><span class="p">)</span>
<span class="nd">@validates</span><span class="p">(</span><span class="s2">"bs"</span><span class="p">)</span>
<span class="k">def</span> <span class="nf">validate_bs</span><span class="p">(</span><span class="bp">self</span><span class="p">,</span> <span class="n">key</span><span class="p">,</span> <span class="n">item</span><span class="p">):</span>
<span class="nb">print</span><span class="p">(</span><span class="s2">"A.bs validator"</span><span class="p">)</span>
<span class="k">return</span> <span class="n">item</span>
<span class="k">class</span> <span class="nc">B</span><span class="p">(</span><span class="n">Base</span><span class="p">):</span>
<span class="n">__tablename__</span> <span class="o">=</span> <span class="s1">'b'</span>
<span class="nb">id</span> <span class="o">=</span> <span class="n">Column</span><span class="p">(</span><span class="n">Integer</span><span class="p">,</span> <span class="n">primary_key</span><span class="o">=</span><span class="kc">True</span><span class="p">)</span>
<span class="n">a_id</span> <span class="o">=</span> <span class="n">Column</span><span class="p">(</span><span class="n">Integer</span><span class="p">,</span> <span class="n">ForeignKey</span><span class="p">(</span><span class="s1">'a.id'</span><span class="p">))</span>
<span class="nd">@validates</span><span class="p">(</span><span class="s2">"a"</span><span class="p">,</span> <span class="n">include_backrefs</span><span class="o">=</span><span class="kc">False</span><span class="p">)</span>
<span class="k">def</span> <span class="nf">validate_a</span><span class="p">(</span><span class="bp">self</span><span class="p">,</span> <span class="n">key</span><span class="p">,</span> <span class="n">item</span><span class="p">):</span>
<span class="nb">print</span><span class="p">(</span><span class="s2">"B.a validator"</span><span class="p">)</span>
<span class="k">return</span> <span class="n">item</span>
<span class="n">a1</span> <span class="o">=</span> <span class="n">A</span><span class="p">()</span>
<span class="n">a1</span><span class="o">.</span><span class="n">bs</span><span class="o">.</span><span class="n">append</span><span class="p">(</span><span class="n">B</span><span class="p">())</span> <span class="c1"># prints only "A.bs validator"</span></pre></div>
</div>
<p><a class="reference external" href="http://www.sqlalchemy.org/trac/ticket/1535">#1535</a></p>
</div>
<div class="section" id="postgresql-json-type">
<h3>PostgreSQL JSON Type<a class="headerlink" href="#postgresql-json-type" title="Permalink to this headline">¶</a></h3>
<p>The PostgreSQL dialect now features a <a class="reference internal" href="../dialects/postgresql.html#sqlalchemy.dialects.postgresql.JSON" title="sqlalchemy.dialects.postgresql.JSON"><code class="xref py py-class docutils literal notranslate"><span class="pre">postgresql.JSON</span></code></a> type to
complement the <a class="reference internal" href="../dialects/postgresql.html#sqlalchemy.dialects.postgresql.HSTORE" title="sqlalchemy.dialects.postgresql.HSTORE"><code class="xref py py-class docutils literal notranslate"><span class="pre">postgresql.HSTORE</span></code></a> type.</p>
<div class="admonition seealso">
<p class="admonition-title">See also</p>
<p><a class="reference internal" href="../dialects/postgresql.html#sqlalchemy.dialects.postgresql.JSON" title="sqlalchemy.dialects.postgresql.JSON"><code class="xref py py-class docutils literal notranslate"><span class="pre">postgresql.JSON</span></code></a></p>
</div>
<p><a class="reference external" href="http://www.sqlalchemy.org/trac/ticket/2581">#2581</a></p>
</div>
<div class="section" id="automap-extension">
<span id="feature-automap"></span><h3>Automap Extension<a class="headerlink" href="#automap-extension" title="Permalink to this headline">¶</a></h3>
<p>A new extension is added in <strong>0.9.1</strong> known as <a class="reference internal" href="../orm/extensions/automap.html#module-sqlalchemy.ext.automap" title="sqlalchemy.ext.automap"><code class="xref py py-mod docutils literal notranslate"><span class="pre">sqlalchemy.ext.automap</span></code></a>. This is an
<strong>experimental</strong> extension which expands upon the functionality of Declarative
as well as the <a class="reference internal" href="../orm/extensions/declarative/api.html#sqlalchemy.ext.declarative.DeferredReflection" title="sqlalchemy.ext.declarative.DeferredReflection"><code class="xref py py-class docutils literal notranslate"><span class="pre">DeferredReflection</span></code></a> class. Essentially, the extension
provides a base class <a class="reference internal" href="../orm/extensions/automap.html#sqlalchemy.ext.automap.AutomapBase" title="sqlalchemy.ext.automap.AutomapBase"><code class="xref py py-class docutils literal notranslate"><span class="pre">AutomapBase</span></code></a> which automatically generates
mapped classes and relationships between them based on given table metadata.</p>
<p>The <a class="reference internal" href="../core/metadata.html#sqlalchemy.schema.MetaData" title="sqlalchemy.schema.MetaData"><code class="xref py py-class docutils literal notranslate"><span class="pre">MetaData</span></code></a> in use normally might be produced via reflection, but
there is no requirement that reflection is used. The most basic usage
illustrates how <a class="reference internal" href="../orm/extensions/automap.html#module-sqlalchemy.ext.automap" title="sqlalchemy.ext.automap"><code class="xref py py-mod docutils literal notranslate"><span class="pre">sqlalchemy.ext.automap</span></code></a> is able to deliver mapped
classes, including relationships, based on a reflected schema:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="kn">from</span> <span class="nn">sqlalchemy.ext.automap</span> <span class="k">import</span> <span class="n">automap_base</span>
<span class="kn">from</span> <span class="nn">sqlalchemy.orm</span> <span class="k">import</span> <span class="n">Session</span>
<span class="kn">from</span> <span class="nn">sqlalchemy</span> <span class="k">import</span> <span class="n">create_engine</span>
<span class="n">Base</span> <span class="o">=</span> <span class="n">automap_base</span><span class="p">()</span>
<span class="c1"># engine, suppose it has two tables 'user' and 'address' set up</span>
<span class="n">engine</span> <span class="o">=</span> <span class="n">create_engine</span><span class="p">(</span><span class="s2">"sqlite:///mydatabase.db"</span><span class="p">)</span>
<span class="c1"># reflect the tables</span>
<span class="n">Base</span><span class="o">.</span><span class="n">prepare</span><span class="p">(</span><span class="n">engine</span><span class="p">,</span> <span class="n">reflect</span><span class="o">=</span><span class="kc">True</span><span class="p">)</span>
<span class="c1"># mapped classes are now created with names matching that of the table</span>
<span class="c1"># name.</span>
<span class="n">User</span> <span class="o">=</span> <span class="n">Base</span><span class="o">.</span><span class="n">classes</span><span class="o">.</span><span class="n">user</span>
<span class="n">Address</span> <span class="o">=</span> <span class="n">Base</span><span class="o">.</span><span class="n">classes</span><span class="o">.</span><span class="n">address</span>
<span class="n">session</span> <span class="o">=</span> <span class="n">Session</span><span class="p">(</span><span class="n">engine</span><span class="p">)</span>
<span class="c1"># rudimentary relationships are produced</span>
<span class="n">session</span><span class="o">.</span><span class="n">add</span><span class="p">(</span><span class="n">Address</span><span class="p">(</span><span class="n">email_address</span><span class="o">=</span><span class="s2">"foo@bar.com"</span><span class="p">,</span> <span class="n">user</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="s2">"foo"</span><span class="p">)))</span>
<span class="n">session</span><span class="o">.</span><span class="n">commit</span><span class="p">()</span>
<span class="c1"># collection-based relationships are by default named "<classname>_collection"</span>
<span class="nb">print</span><span class="p">(</span><span class="n">u1</span><span class="o">.</span><span class="n">address_collection</span><span class="p">)</span></pre></div>
</div>
<p>Beyond that, the <a class="reference internal" href="../orm/extensions/automap.html#sqlalchemy.ext.automap.AutomapBase" title="sqlalchemy.ext.automap.AutomapBase"><code class="xref py py-class docutils literal notranslate"><span class="pre">AutomapBase</span></code></a> class is a declarative base, and supports
all the features that declarative does. The “automapping” feature can be used
with an existing, explicitly declared schema to generate relationships and
missing classes only. Naming schemes and relationship-production routines
can be dropped in using callable functions.</p>
<p>It is hoped that the <a class="reference internal" href="../orm/extensions/automap.html#sqlalchemy.ext.automap.AutomapBase" title="sqlalchemy.ext.automap.AutomapBase"><code class="xref py py-class docutils literal notranslate"><span class="pre">AutomapBase</span></code></a> system provides a quick
and modernized solution to the problem that the very famous
<a class="reference external" href="https://sqlsoup.readthedocs.io/en/latest/">SQLSoup</a>
also tries to solve, that of generating a quick and rudimentary object
model from an existing database on the fly. By addressing the issue strictly
at the mapper configuration level, and integrating fully with existing
Declarative class techniques, <a class="reference internal" href="../orm/extensions/automap.html#sqlalchemy.ext.automap.AutomapBase" title="sqlalchemy.ext.automap.AutomapBase"><code class="xref py py-class docutils literal notranslate"><span class="pre">AutomapBase</span></code></a> seeks to provide
a well-integrated approach to the issue of expediently auto-generating ad-hoc
mappings.</p>
<div class="admonition seealso">
<p class="admonition-title">See also</p>
<p><a class="reference internal" href="../orm/extensions/automap.html"><span class="std std-ref">Automap</span></a></p>
</div>
</div>
</div>
<div class="section" id="behavioral-improvements">
<h2>Behavioral Improvements<a class="headerlink" href="#behavioral-improvements" title="Permalink to this headline">¶</a></h2>
<p>Improvements that should produce no compatibility issues except in exceedingly
rare and unusual hypothetical cases, but are good to be aware of in case there are
unexpected issues.</p>
<div class="section" id="many-join-and-left-outer-join-expressions-will-no-longer-be-wrapped-in-select-from-as-anon-1">
<span id="feature-joins-09"></span><h3>Many JOIN and LEFT OUTER JOIN expressions will no longer be wrapped in (SELECT * FROM ..) AS ANON_1<a class="headerlink" href="#many-join-and-left-outer-join-expressions-will-no-longer-be-wrapped-in-select-from-as-anon-1" title="Permalink to this headline">¶</a></h3>
<p>For many years, the SQLAlchemy ORM has been held back from being able to nest
a JOIN inside the right side of an existing JOIN (typically a LEFT OUTER JOIN,
as INNER JOINs could always be flattened):</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">SELECT</span> <span class="n">a</span><span class="o">.*</span><span class="p">,</span> <span class="n">b</span><span class="o">.*</span><span class="p">,</span> <span class="n">c</span><span class="o">.*</span> <span class="n">FROM</span> <span class="n">a</span> <span class="n">LEFT</span> <span class="n">OUTER</span> <span class="n">JOIN</span> <span class="p">(</span><span class="n">b</span> <span class="n">JOIN</span> <span class="n">c</span> <span class="n">ON</span> <span class="n">b</span><span class="o">.</span><span class="n">id</span> <span class="o">=</span> <span class="n">c</span><span class="o">.</span><span class="n">id</span><span class="p">)</span> <span class="n">ON</span> <span class="n">a</span><span class="o">.</span><span class="n">id</span></pre></div>
</div>
<p>This was due to the fact that SQLite up until version <strong>3.7.16</strong> cannot parse a statement of the above format:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">SQLite</span> <span class="n">version</span> <span class="mf">3.7</span><span class="o">.</span><span class="mf">15.2</span> <span class="mi">2013</span><span class="o">-</span><span class="mi">01</span><span class="o">-</span><span class="mi">09</span> <span class="mi">11</span><span class="p">:</span><span class="mi">53</span><span class="p">:</span><span class="mi">05</span>
<span class="n">Enter</span> <span class="s2">".help"</span> <span class="k">for</span> <span class="n">instructions</span>
<span class="n">Enter</span> <span class="n">SQL</span> <span class="n">statements</span> <span class="n">terminated</span> <span class="k">with</span> <span class="n">a</span> <span class="s2">";"</span>
<span class="n">sqlite</span><span class="o">></span> <span class="n">create</span> <span class="n">table</span> <span class="n">a</span><span class="p">(</span><span class="nb">id</span> <span class="n">integer</span><span class="p">);</span>
<span class="n">sqlite</span><span class="o">></span> <span class="n">create</span> <span class="n">table</span> <span class="n">b</span><span class="p">(</span><span class="nb">id</span> <span class="n">integer</span><span class="p">);</span>
<span class="n">sqlite</span><span class="o">></span> <span class="n">create</span> <span class="n">table</span> <span class="n">c</span><span class="p">(</span><span class="nb">id</span> <span class="n">integer</span><span class="p">);</span>
<span class="n">sqlite</span><span class="o">></span> <span class="n">select</span> <span class="n">a</span><span class="o">.</span><span class="n">id</span><span class="p">,</span> <span class="n">b</span><span class="o">.</span><span class="n">id</span><span class="p">,</span> <span class="n">c</span><span class="o">.</span><span class="n">id</span> <span class="kn">from</span> <span class="nn">a</span> <span class="n">left</span> <span class="n">outer</span> <span class="n">join</span> <span class="p">(</span><span class="n">b</span> <span class="n">join</span> <span class="n">c</span> <span class="n">on</span> <span class="n">b</span><span class="o">.</span><span class="n">id</span><span class="o">=</span><span class="n">c</span><span class="o">.</span><span class="n">id</span><span class="p">)</span> <span class="n">on</span> <span class="n">b</span><span class="o">.</span><span class="n">id</span><span class="o">=</span><span class="n">a</span><span class="o">.</span><span class="n">id</span><span class="p">;</span>
<span class="n">Error</span><span class="p">:</span> <span class="n">no</span> <span class="n">such</span> <span class="n">column</span><span class="p">:</span> <span class="n">b</span><span class="o">.</span><span class="n">id</span></pre></div>
</div>
<p>Right-outer-joins are of course another way to work around right-side
parenthesization; this would be significantly complicated and visually unpleasant
to implement, but fortunately SQLite doesn’t support RIGHT OUTER JOIN either :):</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">sqlite</span><span class="o">></span> <span class="n">select</span> <span class="n">a</span><span class="o">.</span><span class="n">id</span><span class="p">,</span> <span class="n">b</span><span class="o">.</span><span class="n">id</span><span class="p">,</span> <span class="n">c</span><span class="o">.</span><span class="n">id</span> <span class="kn">from</span> <span class="nn">b</span> <span class="n">join</span> <span class="n">c</span> <span class="n">on</span> <span class="n">b</span><span class="o">.</span><span class="n">id</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">right</span> <span class="n">outer</span> <span class="n">join</span> <span class="n">a</span> <span class="n">on</span> <span class="n">b</span><span class="o">.</span><span class="n">id</span><span class="o">=</span><span class="n">a</span><span class="o">.</span><span class="n">id</span><span class="p">;</span>
<span class="n">Error</span><span class="p">:</span> <span class="n">RIGHT</span> <span class="ow">and</span> <span class="n">FULL</span> <span class="n">OUTER</span> <span class="n">JOINs</span> <span class="n">are</span> <span class="ow">not</span> <span class="n">currently</span> <span class="n">supported</span></pre></div>
</div>
<p>Back in 2005, it wasn’t clear if other databases had trouble with this form,
but today it seems clear every database tested except SQLite now supports it
(Oracle 8, a very old database, doesn’t support the JOIN keyword at all,
but SQLAlchemy has always had a simple rewriting scheme in place for Oracle’s syntax).
To make matters worse, SQLAlchemy’s usual workaround of applying a
SELECT often degrades performance on platforms like PostgreSQL and MySQL:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">SELECT</span> <span class="n">a</span><span class="o">.*</span><span class="p">,</span> <span class="n">anon_1</span><span class="o">.*</span> <span class="n">FROM</span> <span class="n">a</span> <span class="n">LEFT</span> <span class="n">OUTER</span> <span class="n">JOIN</span> <span class="p">(</span>
<span class="n">SELECT</span> <span class="n">b</span><span class="o">.</span><span class="n">id</span> <span class="n">AS</span> <span class="n">b_id</span><span class="p">,</span> <span class="n">c</span><span class="o">.</span><span class="n">id</span> <span class="n">AS</span> <span class="n">c_id</span>
<span class="n">FROM</span> <span class="n">b</span> <span class="n">JOIN</span> <span class="n">c</span> <span class="n">ON</span> <span class="n">b</span><span class="o">.</span><span class="n">id</span> <span class="o">=</span> <span class="n">c</span><span class="o">.</span><span class="n">id</span>
<span class="p">)</span> <span class="n">AS</span> <span class="n">anon_1</span> <span class="n">ON</span> <span class="n">a</span><span class="o">.</span><span class="n">id</span><span class="o">=</span><span class="n">anon_1</span><span class="o">.</span><span class="n">b_id</span></pre></div>
</div>
<p>A JOIN like the above form is commonplace when working with joined-table inheritance structures;
any time <a class="reference internal" href="../orm/query.html#sqlalchemy.orm.query.Query.join" title="sqlalchemy.orm.query.Query.join"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Query.join()</span></code></a> is used to join from some parent to a joined-table subclass, or
when <a class="reference internal" href="../orm/loading_relationships.html#sqlalchemy.orm.joinedload" title="sqlalchemy.orm.joinedload"><code class="xref py py-func docutils literal notranslate"><span class="pre">joinedload()</span></code></a> is used similarly, SQLAlchemy’s ORM would always make sure a nested
JOIN was never rendered, lest the query wouldn’t be able to run on SQLite. Even though
the Core has always supported a JOIN of the more compact form, the ORM had to avoid it.</p>
<p>An additional issue would arise when producing joins across many-to-many relationships
where special criteria is present in the ON clause. Consider an eager load join like the following:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">session</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="n">Order</span><span class="p">)</span><span class="o">.</span><span class="n">outerjoin</span><span class="p">(</span><span class="n">Order</span><span class="o">.</span><span class="n">items</span><span class="p">)</span></pre></div>
</div>
<p>Assuming a many-to-many from <code class="docutils literal notranslate"><span class="pre">Order</span></code> to <code class="docutils literal notranslate"><span class="pre">Item</span></code> which actually refers to a subclass
like <code class="docutils literal notranslate"><span class="pre">Subitem</span></code>, the SQL for the above would look like:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">SELECT</span> <span class="n">order</span><span class="o">.</span><span class="n">id</span><span class="p">,</span> <span class="n">order</span><span class="o">.</span><span class="n">name</span>
<span class="n">FROM</span> <span class="n">order</span> <span class="n">LEFT</span> <span class="n">OUTER</span> <span class="n">JOIN</span> <span class="n">order_item</span> <span class="n">ON</span> <span class="n">order</span><span class="o">.</span><span class="n">id</span> <span class="o">=</span> <span class="n">order_item</span><span class="o">.</span><span class="n">order_id</span>
<span class="n">LEFT</span> <span class="n">OUTER</span> <span class="n">JOIN</span> <span class="n">item</span> <span class="n">ON</span> <span class="n">order_item</span><span class="o">.</span><span class="n">item_id</span> <span class="o">=</span> <span class="n">item</span><span class="o">.</span><span class="n">id</span> <span class="n">AND</span> <span class="n">item</span><span class="o">.</span><span class="n">type</span> <span class="o">=</span> <span class="s1">'subitem'</span></pre></div>
</div>
<p>What’s wrong with the above query? Basically, that it will load many <code class="docutils literal notranslate"><span class="pre">order</span></code> /
<code class="docutils literal notranslate"><span class="pre">order_item</span></code> rows where the criteria of <code class="docutils literal notranslate"><span class="pre">item.type</span> <span class="pre">==</span> <span class="pre">'subitem'</span></code> is not true.</p>
<p>As of SQLAlchemy 0.9, an entirely new approach has been taken. The ORM no longer
worries about nesting JOINs in the right side of an enclosing JOIN, and it now will
render these as often as possible while still returning the correct results. When
the SQL statement is passed to be compiled, the <strong>dialect compiler</strong> will <strong>rewrite the join</strong>
to suit the target backend, if that backend is known to not support a right-nested
JOIN (which currently is only SQLite - if other backends have this issue please
let us know!).</p>
<p>So a regular <code class="docutils literal notranslate"><span class="pre">query(Parent).join(Subclass)</span></code> will now usually produce a simpler
expression:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">SELECT</span> <span class="n">parent</span><span class="o">.</span><span class="n">id</span> <span class="n">AS</span> <span class="n">parent_id</span>
<span class="n">FROM</span> <span class="n">parent</span> <span class="n">JOIN</span> <span class="p">(</span>
<span class="n">base_table</span> <span class="n">JOIN</span> <span class="n">subclass_table</span>
<span class="n">ON</span> <span class="n">base_table</span><span class="o">.</span><span class="n">id</span> <span class="o">=</span> <span class="n">subclass_table</span><span class="o">.</span><span class="n">id</span><span class="p">)</span> <span class="n">ON</span> <span class="n">parent</span><span class="o">.</span><span class="n">id</span> <span class="o">=</span> <span class="n">base_table</span><span class="o">.</span><span class="n">parent_id</span></pre></div>
</div>
<p>Joined eager loads like <code class="docutils literal notranslate"><span class="pre">query(Parent).options(joinedload(Parent.subclasses))</span></code>
will alias the individual tables instead of wrapping in an <code class="docutils literal notranslate"><span class="pre">ANON_1</span></code>:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">SELECT</span> <span class="n">parent</span><span class="o">.*</span><span class="p">,</span> <span class="n">base_table_1</span><span class="o">.*</span><span class="p">,</span> <span class="n">subclass_table_1</span><span class="o">.*</span> <span class="n">FROM</span> <span class="n">parent</span>
<span class="n">LEFT</span> <span class="n">OUTER</span> <span class="n">JOIN</span> <span class="p">(</span>
<span class="n">base_table</span> <span class="n">AS</span> <span class="n">base_table_1</span> <span class="n">JOIN</span> <span class="n">subclass_table</span> <span class="n">AS</span> <span class="n">subclass_table_1</span>
<span class="n">ON</span> <span class="n">base_table_1</span><span class="o">.</span><span class="n">id</span> <span class="o">=</span> <span class="n">subclass_table_1</span><span class="o">.</span><span class="n">id</span><span class="p">)</span>
<span class="n">ON</span> <span class="n">parent</span><span class="o">.</span><span class="n">id</span> <span class="o">=</span> <span class="n">base_table_1</span><span class="o">.</span><span class="n">parent_id</span></pre></div>
</div>
<p>Many-to-many joins and eagerloads will right nest the “secondary” and “right” tables:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">SELECT</span> <span class="n">order</span><span class="o">.</span><span class="n">id</span><span class="p">,</span> <span class="n">order</span><span class="o">.</span><span class="n">name</span>
<span class="n">FROM</span> <span class="n">order</span> <span class="n">LEFT</span> <span class="n">OUTER</span> <span class="n">JOIN</span>
<span class="p">(</span><span class="n">order_item</span> <span class="n">JOIN</span> <span class="n">item</span> <span class="n">ON</span> <span class="n">order_item</span><span class="o">.</span><span class="n">item_id</span> <span class="o">=</span> <span class="n">item</span><span class="o">.</span><span class="n">id</span> <span class="n">AND</span> <span class="n">item</span><span class="o">.</span><span class="n">type</span> <span class="o">=</span> <span class="s1">'subitem'</span><span class="p">)</span>
<span class="n">ON</span> <span class="n">order_item</span><span class="o">.</span><span class="n">order_id</span> <span class="o">=</span> <span class="n">order</span><span class="o">.</span><span class="n">id</span></pre></div>
</div>
<p>All of these joins, when rendered with a <a class="reference internal" href="../core/selectable.html#sqlalchemy.sql.expression.Select" title="sqlalchemy.sql.expression.Select"><code class="xref py py-class docutils literal notranslate"><span class="pre">Select</span></code></a> statement that specifically
specifies <code class="docutils literal notranslate"><span class="pre">use_labels=True</span></code>, which is true for all the queries the ORM emits,
are candidates for “join rewriting”, which is the process of rewriting all those right-nested
joins into nested SELECT statements, while maintaining the identical labeling used by
the <a class="reference internal" href="../core/selectable.html#sqlalchemy.sql.expression.Select" title="sqlalchemy.sql.expression.Select"><code class="xref py py-class docutils literal notranslate"><span class="pre">Select</span></code></a>. So SQLite, the one database that won’t support this very
common SQL syntax even in 2013, shoulders the extra complexity itself,
with the above queries rewritten as:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span>-- sqlite only!
SELECT parent.id AS parent_id
FROM parent JOIN (
SELECT base_table.id AS base_table_id,
base_table.parent_id AS base_table_parent_id,
subclass_table.id AS subclass_table_id
FROM base_table JOIN subclass_table ON base_table.id = subclass_table.id
) AS anon_1 ON parent.id = anon_1.base_table_parent_id
-- sqlite only!
SELECT parent.id AS parent_id, anon_1.subclass_table_1_id AS subclass_table_1_id,
anon_1.base_table_1_id AS base_table_1_id,
anon_1.base_table_1_parent_id AS base_table_1_parent_id
FROM parent LEFT OUTER JOIN (
SELECT base_table_1.id AS base_table_1_id,
base_table_1.parent_id AS base_table_1_parent_id,
subclass_table_1.id AS subclass_table_1_id
FROM base_table AS base_table_1
JOIN subclass_table AS subclass_table_1 ON base_table_1.id = subclass_table_1.id
) AS anon_1 ON parent.id = anon_1.base_table_1_parent_id
-- sqlite only!
SELECT "order".id AS order_id
FROM "order" LEFT OUTER JOIN (
SELECT order_item_1.order_id AS order_item_1_order_id,
order_item_1.item_id AS order_item_1_item_id,
item.id AS item_id, item.type AS item_type
FROM order_item AS order_item_1
JOIN item ON item.id = order_item_1.item_id AND item.type IN (?)
) AS anon_1 ON "order".id = anon_1.order_item_1_order_id</pre></div>
</div>
<div class="admonition note">
<p class="admonition-title">Note</p>
<p>As of SQLAlchemy 1.1, the workarounds present in this feature for SQLite
will automatically disable themselves when SQLite version <strong>3.7.16</strong>
or greater is detected, as SQLite has repaired support for right-nested joins.</p>
</div>
<p>The <a class="reference internal" href="../core/selectable.html#sqlalchemy.sql.expression.Join.alias" title="sqlalchemy.sql.expression.Join.alias"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Join.alias()</span></code></a>, <a class="reference internal" href="../orm/query.html#sqlalchemy.orm.aliased" title="sqlalchemy.orm.aliased"><code class="xref py py-func docutils literal notranslate"><span class="pre">aliased()</span></code></a> and <a class="reference internal" href="../orm/inheritance_loading.html#sqlalchemy.orm.with_polymorphic" title="sqlalchemy.orm.with_polymorphic"><code class="xref py py-func docutils literal notranslate"><span class="pre">with_polymorphic()</span></code></a> functions now
support a new argument, <code class="docutils literal notranslate"><span class="pre">flat=True</span></code>, which is used to construct aliases of joined-table
entities without embedding into a SELECT. This flag is not on by default, to help with
backwards compatibility - but now a “polymorphic” selectable can be joined as a target
without any subqueries generated:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">employee_alias</span> <span class="o">=</span> <span class="n">with_polymorphic</span><span class="p">(</span><span class="n">Person</span><span class="p">,</span> <span class="p">[</span><span class="n">Engineer</span><span class="p">,</span> <span class="n">Manager</span><span class="p">],</span> <span class="n">flat</span><span class="o">=</span><span class="kc">True</span><span class="p">)</span>
<span class="n">session</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="n">Company</span><span class="p">)</span><span class="o">.</span><span class="n">join</span><span class="p">(</span>
<span class="n">Company</span><span class="o">.</span><span class="n">employees</span><span class="o">.</span><span class="n">of_type</span><span class="p">(</span><span class="n">employee_alias</span><span class="p">)</span>
<span class="p">)</span><span class="o">.</span><span class="n">filter</span><span class="p">(</span>
<span class="n">or_</span><span class="p">(</span>
<span class="n">Engineer</span><span class="o">.</span><span class="n">primary_language</span> <span class="o">==</span> <span class="s1">'python'</span><span class="p">,</span>
<span class="n">Manager</span><span class="o">.</span><span class="n">manager_name</span> <span class="o">==</span> <span class="s1">'dilbert'</span>
<span class="p">)</span>
<span class="p">)</span></pre></div>
</div>
<p>Generates (everywhere except SQLite):</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">SELECT</span> <span class="n">companies</span><span class="o">.</span><span class="n">company_id</span> <span class="n">AS</span> <span class="n">companies_company_id</span><span class="p">,</span> <span class="n">companies</span><span class="o">.</span><span class="n">name</span> <span class="n">AS</span> <span class="n">companies_name</span>
<span class="n">FROM</span> <span class="n">companies</span> <span class="n">JOIN</span> <span class="p">(</span>
<span class="n">people</span> <span class="n">AS</span> <span class="n">people_1</span>
<span class="n">LEFT</span> <span class="n">OUTER</span> <span class="n">JOIN</span> <span class="n">engineers</span> <span class="n">AS</span> <span class="n">engineers_1</span> <span class="n">ON</span> <span class="n">people_1</span><span class="o">.</span><span class="n">person_id</span> <span class="o">=</span> <span class="n">engineers_1</span><span class="o">.</span><span class="n">person_id</span>
<span class="n">LEFT</span> <span class="n">OUTER</span> <span class="n">JOIN</span> <span class="n">managers</span> <span class="n">AS</span> <span class="n">managers_1</span> <span class="n">ON</span> <span class="n">people_1</span><span class="o">.</span><span class="n">person_id</span> <span class="o">=</span> <span class="n">managers_1</span><span class="o">.</span><span class="n">person_id</span>
<span class="p">)</span> <span class="n">ON</span> <span class="n">companies</span><span class="o">.</span><span class="n">company_id</span> <span class="o">=</span> <span class="n">people_1</span><span class="o">.</span><span class="n">company_id</span>
<span class="n">WHERE</span> <span class="n">engineers</span><span class="o">.</span><span class="n">primary_language</span> <span class="o">=</span> <span class="o">%</span><span class="p">(</span><span class="n">primary_language_1</span><span class="p">)</span><span class="n">s</span>
<span class="n">OR</span> <span class="n">managers</span><span class="o">.</span><span class="n">manager_name</span> <span class="o">=</span> <span class="o">%</span><span class="p">(</span><span class="n">manager_name_1</span><span class="p">)</span><span class="n">s</span></pre></div>
</div>
<p><a class="reference external" href="http://www.sqlalchemy.org/trac/ticket/2369">#2369</a> <a class="reference external" href="http://www.sqlalchemy.org/trac/ticket/2587">#2587</a></p>
</div>
<div class="section" id="right-nested-inner-joins-available-in-joined-eager-loads">
<span id="feature-2976"></span><h3>Right-nested inner joins available in joined eager loads<a class="headerlink" href="#right-nested-inner-joins-available-in-joined-eager-loads" title="Permalink to this headline">¶</a></h3>
<p>As of version 0.9.4, the above mentioned right-nested joining can be enabled
in the case of a joined eager load where an “outer” join is linked to an “inner”
on the right side.</p>
<p>Normally, a joined eager load chain like the following:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></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">options</span><span class="p">(</span><span class="n">joinedload</span><span class="p">(</span><span class="s2">"orders"</span><span class="p">,</span> <span class="n">innerjoin</span><span class="o">=</span><span class="kc">False</span><span class="p">)</span><span class="o">.</span><span class="n">joinedload</span><span class="p">(</span><span class="s2">"items"</span><span class="p">,</span> <span class="n">innerjoin</span><span class="o">=</span><span class="kc">True</span><span class="p">))</span></pre></div>
</div>
<p>Would not produce an inner join; because of the LEFT OUTER JOIN from user->order,
joined eager loading could not use an INNER join from order->items without changing
the user rows that are returned, and would instead ignore the “chained” <code class="docutils literal notranslate"><span class="pre">innerjoin=True</span></code>
directive. How 0.9.0 should have delivered this would be that instead of:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">FROM</span> <span class="n">users</span> <span class="n">LEFT</span> <span class="n">OUTER</span> <span class="n">JOIN</span> <span class="n">orders</span> <span class="n">ON</span> <span class="o"><</span><span class="n">onclause</span><span class="o">></span> <span class="n">LEFT</span> <span class="n">OUTER</span> <span class="n">JOIN</span> <span class="n">items</span> <span class="n">ON</span> <span class="o"><</span><span class="n">onclause</span><span class="o">></span></pre></div>
</div>
<p>the new “right-nested joins are OK” logic would kick in, and we’d get:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">FROM</span> <span class="n">users</span> <span class="n">LEFT</span> <span class="n">OUTER</span> <span class="n">JOIN</span> <span class="p">(</span><span class="n">orders</span> <span class="n">JOIN</span> <span class="n">items</span> <span class="n">ON</span> <span class="o"><</span><span class="n">onclause</span><span class="o">></span><span class="p">)</span> <span class="n">ON</span> <span class="o"><</span><span class="n">onclause</span><span class="o">></span></pre></div>
</div>
<p>Since we missed the boat on that, to avoid further regressions we’ve added the above
functionality by specifying the string <code class="docutils literal notranslate"><span class="pre">"nested"</span></code> to <a class="reference internal" href="../orm/query.html#sqlalchemy.orm.strategy_options.Load.joinedload.params.innerjoin" title="sqlalchemy.orm.strategy_options.Load.joinedload"><code class="xref py py-paramref docutils literal notranslate"><span class="pre">joinedload.innerjoin</span></code></a>:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></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">options</span><span class="p">(</span><span class="n">joinedload</span><span class="p">(</span><span class="s2">"orders"</span><span class="p">,</span> <span class="n">innerjoin</span><span class="o">=</span><span class="kc">False</span><span class="p">)</span><span class="o">.</span><span class="n">joinedload</span><span class="p">(</span><span class="s2">"items"</span><span class="p">,</span> <span class="n">innerjoin</span><span class="o">=</span><span class="s2">"nested"</span><span class="p">))</span></pre></div>
</div>
<p>This feature is new in 0.9.4.</p>
<p><a class="reference external" href="http://www.sqlalchemy.org/trac/ticket/2976">#2976</a></p>
</div>
<div class="section" id="orm-can-efficiently-fetch-just-generated-insert-update-defaults-using-returning">
<h3>ORM can efficiently fetch just-generated INSERT/UPDATE defaults using RETURNING<a class="headerlink" href="#orm-can-efficiently-fetch-just-generated-insert-update-defaults-using-returning" title="Permalink to this headline">¶</a></h3>
<p>The <a class="reference internal" href="../orm/mapping_api.html#sqlalchemy.orm.mapper.Mapper" title="sqlalchemy.orm.mapper.Mapper"><code class="xref py py-class docutils literal notranslate"><span class="pre">Mapper</span></code></a> has long supported an undocumented flag known as
<code class="docutils literal notranslate"><span class="pre">eager_defaults=True</span></code>. The effect of this flag is that when an INSERT or UPDATE
proceeds, and the row is known to have server-generated default values,
a SELECT would immediately follow it in order to “eagerly” load those new values.
Normally, the server-generated columns are marked as “expired” on the object,
so that no overhead is incurred unless the application actually accesses these
columns soon after the flush. The <code class="docutils literal notranslate"><span class="pre">eager_defaults</span></code> flag was therefore not
of much use as it could only decrease performance, and was present only to support
exotic event schemes where users needed default values to be available
immediately within the flush process.</p>
<p>In 0.9, as a result of the version id enhancements, <code class="docutils literal notranslate"><span class="pre">eager_defaults</span></code> can now
emit a RETURNING clause for these values, so on a backend with strong RETURNING
support in particular PostgreSQL, the ORM can fetch newly generated default
and SQL expression values inline with the INSERT or UPDATE. <code class="docutils literal notranslate"><span class="pre">eager_defaults</span></code>,
when enabled, makes use of RETURNING automatically when the target backend
and <a class="reference internal" href="../core/metadata.html#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><code class="xref py py-class docutils literal notranslate"><span class="pre">Table</span></code></a> supports “implicit returning”.</p>
</div>
<div class="section" id="subquery-eager-loading-will-apply-distinct-to-the-innermost-select-for-some-queries">
<span id="change-2836"></span><h3>Subquery Eager Loading will apply DISTINCT to the innermost SELECT for some queries<a class="headerlink" href="#subquery-eager-loading-will-apply-distinct-to-the-innermost-select-for-some-queries" title="Permalink to this headline">¶</a></h3>
<p>In an effort to reduce the number of duplicate rows that can be generated
by subquery eager loading when a many-to-one relationship is involved, a
DISTINCT keyword will be applied to the innermost SELECT when the join is
targeting columns that do not comprise the primary key, as in when loading
along a many to one.</p>
<p>That is, when subquery loading on a many-to-one from A->B:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">SELECT</span> <span class="n">b</span><span class="o">.</span><span class="n">id</span> <span class="n">AS</span> <span class="n">b_id</span><span class="p">,</span> <span class="n">b</span><span class="o">.</span><span class="n">name</span> <span class="n">AS</span> <span class="n">b_name</span><span class="p">,</span> <span class="n">anon_1</span><span class="o">.</span><span class="n">b_id</span> <span class="n">AS</span> <span class="n">a_b_id</span>
<span class="n">FROM</span> <span class="p">(</span><span class="n">SELECT</span> <span class="n">DISTINCT</span> <span class="n">a_b_id</span> <span class="n">FROM</span> <span class="n">a</span><span class="p">)</span> <span class="n">AS</span> <span class="n">anon_1</span>
<span class="n">JOIN</span> <span class="n">b</span> <span class="n">ON</span> <span class="n">b</span><span class="o">.</span><span class="n">id</span> <span class="o">=</span> <span class="n">anon_1</span><span class="o">.</span><span class="n">a_b_id</span></pre></div>
</div>
<p>Since <code class="docutils literal notranslate"><span class="pre">a.b_id</span></code> is a non-distinct foreign key, DISTINCT is applied so that
redundant <code class="docutils literal notranslate"><span class="pre">a.b_id</span></code> are eliminated. The behavior can be turned on or off
unconditionally for a particular <a class="reference internal" href="../orm/relationship_api.html#sqlalchemy.orm.relationship" title="sqlalchemy.orm.relationship"><code class="xref py py-func docutils literal notranslate"><span class="pre">relationship()</span></code></a> using the flag
<code class="docutils literal notranslate"><span class="pre">distinct_target_key</span></code>, setting the value to <code class="docutils literal notranslate"><span class="pre">True</span></code> for unconditionally
on, <code class="docutils literal notranslate"><span class="pre">False</span></code> for unconditionally off, and <code class="docutils literal notranslate"><span class="pre">None</span></code> for the feature to take
effect when the target SELECT is against columns that do not comprise a full
primary key. In 0.9, <code class="docutils literal notranslate"><span class="pre">None</span></code> is the default.</p>
<p>The option is also backported to 0.8 where the <code class="docutils literal notranslate"><span class="pre">distinct_target_key</span></code>
option defaults to <code class="docutils literal notranslate"><span class="pre">False</span></code>.</p>
<p>While the feature here is designed to help performance by eliminating
duplicate rows, the <code class="docutils literal notranslate"><span class="pre">DISTINCT</span></code> keyword in SQL itself can have a negative
performance impact. If columns in the SELECT are not indexed, <code class="docutils literal notranslate"><span class="pre">DISTINCT</span></code>
will likely perform an <code class="docutils literal notranslate"><span class="pre">ORDER</span> <span class="pre">BY</span></code> on the rowset which can be expensive.
By keeping the feature limited just to foreign keys which are hopefully
indexed in any case, it’s expected that the new defaults are reasonable.</p>
<p>The feature also does not eliminate every possible dupe-row scenario; if
a many-to-one is present elsewhere in the chain of joins, dupe rows may still
be present.</p>
<p><a class="reference external" href="http://www.sqlalchemy.org/trac/ticket/2836">#2836</a></p>
</div>
<div class="section" id="backref-handlers-can-now-propagate-more-than-one-level-deep">
<span id="migration-2789"></span><h3>Backref handlers can now propagate more than one level deep<a class="headerlink" href="#backref-handlers-can-now-propagate-more-than-one-level-deep" title="Permalink to this headline">¶</a></h3>
<p>The mechanism by which attribute events pass along their “initiator”, that is
the object associated with the start of the event, has been changed; instead
of a <code class="xref py py-class docutils literal notranslate"><span class="pre">AttributeImpl</span></code> being passed, a new object <a class="reference internal" href="../orm/internals.html#sqlalchemy.orm.attributes.Event" title="sqlalchemy.orm.attributes.Event"><code class="xref py py-class docutils literal notranslate"><span class="pre">attributes.Event</span></code></a>
is passed instead; this object refers to the <code class="xref py py-class docutils literal notranslate"><span class="pre">AttributeImpl</span></code> as well as
to an “operation token”, representing if the operation is an append, remove,
or replace operation.</p>
<p>The attribute event system no longer looks at this “initiator” object in order to halt a
recursive series of attribute events. Instead, the system of preventing endless
recursion due to mutually-dependent backref handlers has been moved
to the ORM backref event handlers specifically, which now take over the role
of ensuring that a chain of mutually-dependent events (such as append to collection
A.bs, set many-to-one attribute B.a in response) doesn’t go into an endless recursion
stream. The rationale here is that the backref system, given more detail and control
over event propagation, can finally allow operations more than one level deep
to occur; the typical scenario is when a collection append results in a many-to-one
replacement operation, which in turn should cause the item to be removed from a
previous collection:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="k">class</span> <span class="nc">Parent</span><span class="p">(</span><span class="n">Base</span><span class="p">):</span>
<span class="n">__tablename__</span> <span class="o">=</span> <span class="s1">'parent'</span>
<span class="nb">id</span> <span class="o">=</span> <span class="n">Column</span><span class="p">(</span><span class="n">Integer</span><span class="p">,</span> <span class="n">primary_key</span><span class="o">=</span><span class="kc">True</span><span class="p">)</span>
<span class="n">children</span> <span class="o">=</span> <span class="n">relationship</span><span class="p">(</span><span class="s2">"Child"</span><span class="p">,</span> <span class="n">backref</span><span class="o">=</span><span class="s2">"parent"</span><span class="p">)</span>
<span class="k">class</span> <span class="nc">Child</span><span class="p">(</span><span class="n">Base</span><span class="p">):</span>
<span class="n">__tablename__</span> <span class="o">=</span> <span class="s1">'child'</span>
<span class="nb">id</span> <span class="o">=</span> <span class="n">Column</span><span class="p">(</span><span class="n">Integer</span><span class="p">,</span> <span class="n">primary_key</span><span class="o">=</span><span class="kc">True</span><span class="p">)</span>
<span class="n">parent_id</span> <span class="o">=</span> <span class="n">Column</span><span class="p">(</span><span class="n">ForeignKey</span><span class="p">(</span><span class="s1">'parent.id'</span><span class="p">))</span>
<span class="n">p1</span> <span class="o">=</span> <span class="n">Parent</span><span class="p">()</span>
<span class="n">p2</span> <span class="o">=</span> <span class="n">Parent</span><span class="p">()</span>
<span class="n">c1</span> <span class="o">=</span> <span class="n">Child</span><span class="p">()</span>
<span class="n">p1</span><span class="o">.</span><span class="n">children</span><span class="o">.</span><span class="n">append</span><span class="p">(</span><span class="n">c1</span><span class="p">)</span>
<span class="k">assert</span> <span class="n">c1</span><span class="o">.</span><span class="n">parent</span> <span class="ow">is</span> <span class="n">p1</span> <span class="c1"># backref event establishes c1.parent as p1</span>
<span class="n">p2</span><span class="o">.</span><span class="n">children</span><span class="o">.</span><span class="n">append</span><span class="p">(</span><span class="n">c1</span><span class="p">)</span>
<span class="k">assert</span> <span class="n">c1</span><span class="o">.</span><span class="n">parent</span> <span class="ow">is</span> <span class="n">p2</span> <span class="c1"># backref event establishes c1.parent as p2</span>
<span class="k">assert</span> <span class="n">c1</span> <span class="ow">not</span> <span class="ow">in</span> <span class="n">p1</span><span class="o">.</span><span class="n">children</span> <span class="c1"># second backref event removes c1 from p1.children</span></pre></div>
</div>
<p>Above, prior to this change, the <code class="docutils literal notranslate"><span class="pre">c1</span></code> object would still have been present
in <code class="docutils literal notranslate"><span class="pre">p1.children</span></code>, even though it is also present in <code class="docutils literal notranslate"><span class="pre">p2.children</span></code> at the
same time; the backref handlers would have stopped at replacing <code class="docutils literal notranslate"><span class="pre">c1.parent</span></code> with
<code class="docutils literal notranslate"><span class="pre">p2</span></code> instead of <code class="docutils literal notranslate"><span class="pre">p1</span></code>. In 0.9, using the more detailed <a class="reference internal" href="../orm/internals.html#sqlalchemy.orm.attributes.Event" title="sqlalchemy.orm.attributes.Event"><code class="xref py py-class docutils literal notranslate"><span class="pre">Event</span></code></a>
object as well as letting the backref handlers make more detailed decisions about
these objects, the propagation can continue onto removing <code class="docutils literal notranslate"><span class="pre">c1</span></code> from <code class="docutils literal notranslate"><span class="pre">p1.children</span></code>
while maintaining a check against the propagation from going into an endless
recursive loop.</p>
<p>End-user code which a. makes use of the <a class="reference internal" href="../orm/events.html#sqlalchemy.orm.events.AttributeEvents.set" title="sqlalchemy.orm.events.AttributeEvents.set"><code class="xref py py-meth docutils literal notranslate"><span class="pre">AttributeEvents.set()</span></code></a>,
<a class="reference internal" href="../orm/events.html#sqlalchemy.orm.events.AttributeEvents.append" title="sqlalchemy.orm.events.AttributeEvents.append"><code class="xref py py-meth docutils literal notranslate"><span class="pre">AttributeEvents.append()</span></code></a>, or <a class="reference internal" href="../orm/events.html#sqlalchemy.orm.events.AttributeEvents.remove" title="sqlalchemy.orm.events.AttributeEvents.remove"><code class="xref py py-meth docutils literal notranslate"><span class="pre">AttributeEvents.remove()</span></code></a> events,
and b. initiates further attribute modification operations as a result of these
events may need to be modified to prevent recursive loops, as the attribute system
no longer stops a chain of events from propagating endlessly in the absence of the backref
event handlers. Additionally, code which depends upon the value of the <code class="docutils literal notranslate"><span class="pre">initiator</span></code>
will need to be adjusted to the new API, and furthermore must be ready for the
value of <code class="docutils literal notranslate"><span class="pre">initiator</span></code> to change from its original value within a string of
backref-initiated events, as the backref handlers may now swap in a
new <code class="docutils literal notranslate"><span class="pre">initiator</span></code> value for some operations.</p>
<p><a class="reference external" href="http://www.sqlalchemy.org/trac/ticket/2789">#2789</a></p>
</div>
<div class="section" id="the-typing-system-now-handles-the-task-of-rendering-literal-bind-values">
<span id="change-2838"></span><h3>The typing system now handles the task of rendering “literal bind” values<a class="headerlink" href="#the-typing-system-now-handles-the-task-of-rendering-literal-bind-values" title="Permalink to this headline">¶</a></h3>
<p>A new method is added to <a class="reference internal" href="../core/type_api.html#sqlalchemy.types.TypeEngine" title="sqlalchemy.types.TypeEngine"><code class="xref py py-class docutils literal notranslate"><span class="pre">TypeEngine</span></code></a> <a class="reference internal" href="../core/type_api.html#sqlalchemy.types.TypeEngine.literal_processor" title="sqlalchemy.types.TypeEngine.literal_processor"><code class="xref py py-meth docutils literal notranslate"><span class="pre">TypeEngine.literal_processor()</span></code></a>
as well as <a class="reference internal" href="../core/custom_types.html#sqlalchemy.types.TypeDecorator.process_literal_param" title="sqlalchemy.types.TypeDecorator.process_literal_param"><code class="xref py py-meth docutils literal notranslate"><span class="pre">TypeDecorator.process_literal_param()</span></code></a> for <a class="reference internal" href="../core/custom_types.html#sqlalchemy.types.TypeDecorator" title="sqlalchemy.types.TypeDecorator"><code class="xref py py-class docutils literal notranslate"><span class="pre">TypeDecorator</span></code></a>
which take on the task of rendering so-called “inline literal parameters” - parameters
that normally render as “bound” values, but are instead being rendered inline
into the SQL statement due to the compiler configuration. This feature is used
when generating DDL for constructs such as <a class="reference internal" href="../core/constraints.html#sqlalchemy.schema.CheckConstraint" title="sqlalchemy.schema.CheckConstraint"><code class="xref py py-class docutils literal notranslate"><span class="pre">CheckConstraint</span></code></a>, as well
as by Alembic when using constructs such as <code class="docutils literal notranslate"><span class="pre">op.inline_literal()</span></code>. Previously,
a simple “isinstance” check checked for a few basic types, and the “bind processor”
was used unconditionally, leading to such issues as strings being encoded into utf-8
prematurely.</p>
<p>Custom types written with <a class="reference internal" href="../core/custom_types.html#sqlalchemy.types.TypeDecorator" title="sqlalchemy.types.TypeDecorator"><code class="xref py py-class docutils literal notranslate"><span class="pre">TypeDecorator</span></code></a> should continue to work in
“inline literal” scenarios, as the <a class="reference internal" href="../core/custom_types.html#sqlalchemy.types.TypeDecorator.process_literal_param" title="sqlalchemy.types.TypeDecorator.process_literal_param"><code class="xref py py-meth docutils literal notranslate"><span class="pre">TypeDecorator.process_literal_param()</span></code></a>
falls back to <a class="reference internal" href="../core/custom_types.html#sqlalchemy.types.TypeDecorator.process_bind_param" title="sqlalchemy.types.TypeDecorator.process_bind_param"><code class="xref py py-meth docutils literal notranslate"><span class="pre">TypeDecorator.process_bind_param()</span></code></a> by default, as these methods
usually handle a data manipulation, not as much how the data is presented to the
database. <a class="reference internal" href="../core/custom_types.html#sqlalchemy.types.TypeDecorator.process_literal_param" title="sqlalchemy.types.TypeDecorator.process_literal_param"><code class="xref py py-meth docutils literal notranslate"><span class="pre">TypeDecorator.process_literal_param()</span></code></a> can be specified to
specifically produce a string representing how a value should be rendered
into an inline DDL statement.</p>
<p><a class="reference external" href="http://www.sqlalchemy.org/trac/ticket/2838">#2838</a></p>
</div>
<div class="section" id="schema-identifiers-now-carry-along-their-own-quoting-information">
<span id="change-2812"></span><h3>Schema identifiers now carry along their own quoting information<a class="headerlink" href="#schema-identifiers-now-carry-along-their-own-quoting-information" title="Permalink to this headline">¶</a></h3>
<p>This change simplifies the Core’s usage of so-called “quote” flags, such
as the <code class="docutils literal notranslate"><span class="pre">quote</span></code> flag passed to <a class="reference internal" href="../core/metadata.html#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><code class="xref py py-class docutils literal notranslate"><span class="pre">Table</span></code></a> and <a class="reference internal" href="../core/metadata.html#sqlalchemy.schema.Column" title="sqlalchemy.schema.Column"><code class="xref py py-class docutils literal notranslate"><span class="pre">Column</span></code></a>. The flag
is now internalized within the string name itself, which is now represented
as an instance of <a class="reference internal" href="../core/sqlelement.html#sqlalchemy.sql.elements.quoted_name" title="sqlalchemy.sql.elements.quoted_name"><code class="xref py py-class docutils literal notranslate"><span class="pre">quoted_name</span></code></a>, a string subclass. The
<a class="reference internal" href="../core/internals.html#sqlalchemy.sql.compiler.IdentifierPreparer" title="sqlalchemy.sql.compiler.IdentifierPreparer"><code class="xref py py-class docutils literal notranslate"><span class="pre">IdentifierPreparer</span></code></a> now relies solely on the quoting preferences
reported by the <a class="reference internal" href="../core/sqlelement.html#sqlalchemy.sql.elements.quoted_name" title="sqlalchemy.sql.elements.quoted_name"><code class="xref py py-class docutils literal notranslate"><span class="pre">quoted_name</span></code></a> object rather than checking for any
explicit <code class="docutils literal notranslate"><span class="pre">quote</span></code> flags in most cases. The issue resolved here includes
that various case-sensitive methods such as <a class="reference internal" href="../core/connections.html#sqlalchemy.engine.Engine.has_table" title="sqlalchemy.engine.Engine.has_table"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Engine.has_table()</span></code></a> as well
as similar methods within dialects now function with explicitly quoted names,
without the need to complicate or introduce backwards-incompatible changes
to those APIs (many of which are 3rd party) with the details of quoting flags -
in particular, a wider range of identifiers now function correctly with the
so-called “uppercase” backends like Oracle, Firebird, and DB2 (backends that
store and report upon table and column names using all uppercase for case
insensitive names).</p>
<p>The <a class="reference internal" href="../core/sqlelement.html#sqlalchemy.sql.elements.quoted_name" title="sqlalchemy.sql.elements.quoted_name"><code class="xref py py-class docutils literal notranslate"><span class="pre">quoted_name</span></code></a> object is used internally as needed; however if
other keywords require fixed quoting preferences, the class is available
publicly.</p>
<p><a class="reference external" href="http://www.sqlalchemy.org/trac/ticket/2812">#2812</a></p>
</div>
<div class="section" id="improved-rendering-of-boolean-constants-null-constants-conjunctions">
<span id="migration-2804"></span><h3>Improved rendering of Boolean constants, NULL constants, conjunctions<a class="headerlink" href="#improved-rendering-of-boolean-constants-null-constants-conjunctions" title="Permalink to this headline">¶</a></h3>
<p>New capabilities have been added to the <a class="reference internal" href="../core/sqlelement.html#sqlalchemy.sql.expression.true" title="sqlalchemy.sql.expression.true"><code class="xref py py-func docutils literal notranslate"><span class="pre">true()</span></code></a> and <a class="reference internal" href="../core/sqlelement.html#sqlalchemy.sql.expression.false" title="sqlalchemy.sql.expression.false"><code class="xref py py-func docutils literal notranslate"><span class="pre">false()</span></code></a>
constants, in particular in conjunction with <a class="reference internal" href="../core/sqlelement.html#sqlalchemy.sql.expression.and_" title="sqlalchemy.sql.expression.and_"><code class="xref py py-func docutils literal notranslate"><span class="pre">and_()</span></code></a> and <a class="reference internal" href="../core/sqlelement.html#sqlalchemy.sql.expression.or_" title="sqlalchemy.sql.expression.or_"><code class="xref py py-func docutils literal notranslate"><span class="pre">or_()</span></code></a>
functions as well as the behavior of the WHERE/HAVING clauses in conjunction
with these types, boolean types overall, and the <a class="reference internal" href="../core/sqlelement.html#sqlalchemy.sql.expression.null" title="sqlalchemy.sql.expression.null"><code class="xref py py-func docutils literal notranslate"><span class="pre">null()</span></code></a> constant.</p>
<p>Starting with a table such as this:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="kn">from</span> <span class="nn">sqlalchemy</span> <span class="k">import</span> <span class="n">Table</span><span class="p">,</span> <span class="n">Boolean</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="n">MetaData</span>
<span class="n">t1</span> <span class="o">=</span> <span class="n">Table</span><span class="p">(</span><span class="s1">'t'</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="s1">'x'</span><span class="p">,</span> <span class="n">Boolean</span><span class="p">()),</span> <span class="n">Column</span><span class="p">(</span><span class="s1">'y'</span><span class="p">,</span> <span class="n">Integer</span><span class="p">))</span></pre></div>
</div>
<p>A select construct will now render the boolean column as a binary expression
on backends that don’t feature <code class="docutils literal notranslate"><span class="pre">true</span></code>/<code class="docutils literal notranslate"><span class="pre">false</span></code> constant behavior:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="gp">>>> </span><span class="kn">from</span> <span class="nn">sqlalchemy</span> <span class="k">import</span> <span class="n">select</span><span class="p">,</span> <span class="n">and_</span><span class="p">,</span> <span class="n">false</span><span class="p">,</span> <span class="n">true</span>
<span class="gp">>>> </span><span class="kn">from</span> <span class="nn">sqlalchemy.dialects</span> <span class="k">import</span> <span class="n">mysql</span><span class="p">,</span> <span class="n">postgresql</span>
<span class="gp">>>> </span><span class="nb">print</span><span class="p">(</span><span class="n">select</span><span class="p">([</span><span class="n">t1</span><span class="p">])</span><span class="o">.</span><span class="n">where</span><span class="p">(</span><span class="n">t1</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">x</span><span class="p">)</span><span class="o">.</span><span class="n">compile</span><span class="p">(</span><span class="n">dialect</span><span class="o">=</span><span class="n">mysql</span><span class="o">.</span><span class="n">dialect</span><span class="p">()))</span>
<span class="go">SELECT t.x, t.y FROM t WHERE t.x = 1</span></pre></div>
</div>
<p>The <a class="reference internal" href="../core/sqlelement.html#sqlalchemy.sql.expression.and_" title="sqlalchemy.sql.expression.and_"><code class="xref py py-func docutils literal notranslate"><span class="pre">and_()</span></code></a> and <a class="reference internal" href="../core/sqlelement.html#sqlalchemy.sql.expression.or_" title="sqlalchemy.sql.expression.or_"><code class="xref py py-func docutils literal notranslate"><span class="pre">or_()</span></code></a> constructs will now exhibit quasi
“short circuit” behavior, that is truncating a rendered expression, when a
<a class="reference internal" href="../core/sqlelement.html#sqlalchemy.sql.expression.true" title="sqlalchemy.sql.expression.true"><code class="xref py py-func docutils literal notranslate"><span class="pre">true()</span></code></a> or <a class="reference internal" href="../core/sqlelement.html#sqlalchemy.sql.expression.false" title="sqlalchemy.sql.expression.false"><code class="xref py py-func docutils literal notranslate"><span class="pre">false()</span></code></a> constant is present:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="gp">>>> </span><span class="nb">print</span><span class="p">(</span><span class="n">select</span><span class="p">([</span><span class="n">t1</span><span class="p">])</span><span class="o">.</span><span class="n">where</span><span class="p">(</span><span class="n">and_</span><span class="p">(</span><span class="n">t1</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">y</span> <span class="o">></span> <span class="mi">5</span><span class="p">,</span> <span class="n">false</span><span class="p">()))</span><span class="o">.</span><span class="n">compile</span><span class="p">(</span>
<span class="gp">... </span> <span class="n">dialect</span><span class="o">=</span><span class="n">postgresql</span><span class="o">.</span><span class="n">dialect</span><span class="p">()))</span>
<span class="go">SELECT t.x, t.y FROM t WHERE false</span></pre></div>
</div>
<p><a class="reference internal" href="../core/sqlelement.html#sqlalchemy.sql.expression.true" title="sqlalchemy.sql.expression.true"><code class="xref py py-func docutils literal notranslate"><span class="pre">true()</span></code></a> can be used as the base to build up an expression:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="gp">>>> </span><span class="n">expr</span> <span class="o">=</span> <span class="n">true</span><span class="p">()</span>
<span class="gp">>>> </span><span class="n">expr</span> <span class="o">=</span> <span class="n">expr</span> <span class="o">&</span> <span class="p">(</span><span class="n">t1</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">y</span> <span class="o">></span> <span class="mi">5</span><span class="p">)</span>
<span class="gp">>>> </span><span class="nb">print</span><span class="p">(</span><span class="n">select</span><span class="p">([</span><span class="n">t1</span><span class="p">])</span><span class="o">.</span><span class="n">where</span><span class="p">(</span><span class="n">expr</span><span class="p">))</span>
<span class="go">SELECT t.x, t.y FROM t WHERE t.y > :y_1</span></pre></div>
</div>
<p>The boolean constants <a class="reference internal" href="../core/sqlelement.html#sqlalchemy.sql.expression.true" title="sqlalchemy.sql.expression.true"><code class="xref py py-func docutils literal notranslate"><span class="pre">true()</span></code></a> and <a class="reference internal" href="../core/sqlelement.html#sqlalchemy.sql.expression.false" title="sqlalchemy.sql.expression.false"><code class="xref py py-func docutils literal notranslate"><span class="pre">false()</span></code></a> themselves render as
<code class="docutils literal notranslate"><span class="pre">0</span> <span class="pre">=</span> <span class="pre">1</span></code> and <code class="docutils literal notranslate"><span class="pre">1</span> <span class="pre">=</span> <span class="pre">1</span></code> for a backend with no boolean constants:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="gp">>>> </span><span class="nb">print</span><span class="p">(</span><span class="n">select</span><span class="p">([</span><span class="n">t1</span><span class="p">])</span><span class="o">.</span><span class="n">where</span><span class="p">(</span><span class="n">and_</span><span class="p">(</span><span class="n">t1</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">y</span> <span class="o">></span> <span class="mi">5</span><span class="p">,</span> <span class="n">false</span><span class="p">()))</span><span class="o">.</span><span class="n">compile</span><span class="p">(</span>
<span class="gp">... </span> <span class="n">dialect</span><span class="o">=</span><span class="n">mysql</span><span class="o">.</span><span class="n">dialect</span><span class="p">()))</span>
<span class="go">SELECT t.x, t.y FROM t WHERE 0 = 1</span></pre></div>
</div>
<p>Interpretation of <code class="docutils literal notranslate"><span class="pre">None</span></code>, while not particularly valid SQL, is at least
now consistent:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="gp">>>> </span><span class="nb">print</span><span class="p">(</span><span class="n">select</span><span class="p">([</span><span class="n">t1</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">x</span><span class="p">])</span><span class="o">.</span><span class="n">where</span><span class="p">(</span><span class="kc">None</span><span class="p">))</span>
<span class="go">SELECT t.x FROM t WHERE NULL</span>
<span class="gp">>>> </span><span class="nb">print</span><span class="p">(</span><span class="n">select</span><span class="p">([</span><span class="n">t1</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">x</span><span class="p">])</span><span class="o">.</span><span class="n">where</span><span class="p">(</span><span class="kc">None</span><span class="p">)</span><span class="o">.</span><span class="n">where</span><span class="p">(</span><span class="kc">None</span><span class="p">))</span>
<span class="go">SELECT t.x FROM t WHERE NULL AND NULL</span>
<span class="gp">>>> </span><span class="nb">print</span><span class="p">(</span><span class="n">select</span><span class="p">([</span><span class="n">t1</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">x</span><span class="p">])</span><span class="o">.</span><span class="n">where</span><span class="p">(</span><span class="n">and_</span><span class="p">(</span><span class="kc">None</span><span class="p">,</span> <span class="kc">None</span><span class="p">)))</span>
<span class="go">SELECT t.x FROM t WHERE NULL AND NULL</span></pre></div>
</div>
<p><a class="reference external" href="http://www.sqlalchemy.org/trac/ticket/2804">#2804</a></p>
</div>
<div class="section" id="label-constructs-can-now-render-as-their-name-alone-in-an-order-by">
<span id="migration-1068"></span><h3>Label constructs can now render as their name alone in an ORDER BY<a class="headerlink" href="#label-constructs-can-now-render-as-their-name-alone-in-an-order-by" title="Permalink to this headline">¶</a></h3>
<p>For the case where a <a class="reference internal" href="../core/sqlelement.html#sqlalchemy.sql.expression.Label" title="sqlalchemy.sql.expression.Label"><code class="xref py py-class docutils literal notranslate"><span class="pre">Label</span></code></a> is used in both the columns clause
as well as the ORDER BY clause of a SELECT, the label will render as
just its name in the ORDER BY clause, assuming the underlying dialect
reports support of this feature.</p>
<p>E.g. an example like:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="kn">from</span> <span class="nn">sqlalchemy.sql</span> <span class="k">import</span> <span class="n">table</span><span class="p">,</span> <span class="n">column</span><span class="p">,</span> <span class="n">select</span><span class="p">,</span> <span class="n">func</span>
<span class="n">t</span> <span class="o">=</span> <span class="n">table</span><span class="p">(</span><span class="s1">'t'</span><span class="p">,</span> <span class="n">column</span><span class="p">(</span><span class="s1">'c1'</span><span class="p">),</span> <span class="n">column</span><span class="p">(</span><span class="s1">'c2'</span><span class="p">))</span>
<span class="n">expr</span> <span class="o">=</span> <span class="p">(</span><span class="n">func</span><span class="o">.</span><span class="n">foo</span><span class="p">(</span><span class="n">t</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">c1</span><span class="p">)</span> <span class="o">+</span> <span class="n">t</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">c2</span><span class="p">)</span><span class="o">.</span><span class="n">label</span><span class="p">(</span><span class="s2">"expr"</span><span class="p">)</span>
<span class="n">stmt</span> <span class="o">=</span> <span class="n">select</span><span class="p">([</span><span class="n">expr</span><span class="p">])</span><span class="o">.</span><span class="n">order_by</span><span class="p">(</span><span class="n">expr</span><span class="p">)</span>
<span class="nb">print</span><span class="p">(</span><span class="n">stmt</span><span class="p">)</span></pre></div>
</div>
<p>Prior to 0.9 would render as:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">SELECT</span> <span class="n">foo</span><span class="p">(</span><span class="n">t</span><span class="o">.</span><span class="n">c1</span><span class="p">)</span> <span class="o">+</span> <span class="n">t</span><span class="o">.</span><span class="n">c2</span> <span class="n">AS</span> <span class="n">expr</span>
<span class="n">FROM</span> <span class="n">t</span> <span class="n">ORDER</span> <span class="n">BY</span> <span class="n">foo</span><span class="p">(</span><span class="n">t</span><span class="o">.</span><span class="n">c1</span><span class="p">)</span> <span class="o">+</span> <span class="n">t</span><span class="o">.</span><span class="n">c2</span></pre></div>
</div>
<p>And now renders as:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">SELECT</span> <span class="n">foo</span><span class="p">(</span><span class="n">t</span><span class="o">.</span><span class="n">c1</span><span class="p">)</span> <span class="o">+</span> <span class="n">t</span><span class="o">.</span><span class="n">c2</span> <span class="n">AS</span> <span class="n">expr</span>
<span class="n">FROM</span> <span class="n">t</span> <span class="n">ORDER</span> <span class="n">BY</span> <span class="n">expr</span></pre></div>
</div>
<p>The ORDER BY only renders the label if the label isn’t further
embedded into an expression within the ORDER BY, other than a simple
<code class="docutils literal notranslate"><span class="pre">ASC</span></code> or <code class="docutils literal notranslate"><span class="pre">DESC</span></code>.</p>
<p>The above format works on all databases tested, but might have
compatibility issues with older database versions (MySQL 4? Oracle 8?
etc.). Based on user reports we can add rules that will disable the
feature based on database version detection.</p>
<p><a class="reference external" href="http://www.sqlalchemy.org/trac/ticket/1068">#1068</a></p>
</div>
<div class="section" id="rowproxy-now-has-tuple-sorting-behavior">
<span id="migration-2848"></span><h3><code class="docutils literal notranslate"><span class="pre">RowProxy</span></code> now has tuple-sorting behavior<a class="headerlink" href="#rowproxy-now-has-tuple-sorting-behavior" title="Permalink to this headline">¶</a></h3>
<p>The <a class="reference internal" href="../core/connections.html#sqlalchemy.engine.RowProxy" title="sqlalchemy.engine.RowProxy"><code class="xref py py-class docutils literal notranslate"><span class="pre">RowProxy</span></code></a> object acts much like a tuple, but up until now
would not sort as a tuple if a list of them were sorted using <code class="docutils literal notranslate"><span class="pre">sorted()</span></code>.
The <code class="docutils literal notranslate"><span class="pre">__eq__()</span></code> method now compares both sides as a tuple and also
an <code class="docutils literal notranslate"><span class="pre">__lt__()</span></code> method has been added:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">users</span><span class="o">.</span><span class="n">insert</span><span class="p">()</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span>
<span class="nb">dict</span><span class="p">(</span><span class="n">user_id</span><span class="o">=</span><span class="mi">1</span><span class="p">,</span> <span class="n">user_name</span><span class="o">=</span><span class="s1">'foo'</span><span class="p">),</span>
<span class="nb">dict</span><span class="p">(</span><span class="n">user_id</span><span class="o">=</span><span class="mi">2</span><span class="p">,</span> <span class="n">user_name</span><span class="o">=</span><span class="s1">'bar'</span><span class="p">),</span>
<span class="nb">dict</span><span class="p">(</span><span class="n">user_id</span><span class="o">=</span><span class="mi">3</span><span class="p">,</span> <span class="n">user_name</span><span class="o">=</span><span class="s1">'def'</span><span class="p">),</span>
<span class="p">)</span>
<span class="n">rows</span> <span class="o">=</span> <span class="n">users</span><span class="o">.</span><span class="n">select</span><span class="p">()</span><span class="o">.</span><span class="n">order_by</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">user_name</span><span class="p">)</span><span class="o">.</span><span class="n">execute</span><span class="p">()</span><span class="o">.</span><span class="n">fetchall</span><span class="p">()</span>
<span class="n">eq_</span><span class="p">(</span><span class="n">rows</span><span class="p">,</span> <span class="p">[(</span><span class="mi">2</span><span class="p">,</span> <span class="s1">'bar'</span><span class="p">),</span> <span class="p">(</span><span class="mi">3</span><span class="p">,</span> <span class="s1">'def'</span><span class="p">),</span> <span class="p">(</span><span class="mi">1</span><span class="p">,</span> <span class="s1">'foo'</span><span class="p">)])</span>
<span class="n">eq_</span><span class="p">(</span><span class="nb">sorted</span><span class="p">(</span><span class="n">rows</span><span class="p">),</span> <span class="p">[(</span><span class="mi">1</span><span class="p">,</span> <span class="s1">'foo'</span><span class="p">),</span> <span class="p">(</span><span class="mi">2</span><span class="p">,</span> <span class="s1">'bar'</span><span class="p">),</span> <span class="p">(</span><span class="mi">3</span><span class="p">,</span> <span class="s1">'def'</span><span class="p">)])</span></pre></div>
</div>
<p><a class="reference external" href="http://www.sqlalchemy.org/trac/ticket/2848">#2848</a></p>
</div>
<div class="section" id="a-bindparam-construct-with-no-type-gets-upgraded-via-copy-when-a-type-is-available">
<span id="migration-2850"></span><h3>A bindparam() construct with no type gets upgraded via copy when a type is available<a class="headerlink" href="#a-bindparam-construct-with-no-type-gets-upgraded-via-copy-when-a-type-is-available" title="Permalink to this headline">¶</a></h3>
<p>The logic which “upgrades” a <a class="reference internal" href="../core/sqlelement.html#sqlalchemy.sql.expression.bindparam" title="sqlalchemy.sql.expression.bindparam"><code class="xref py py-func docutils literal notranslate"><span class="pre">bindparam()</span></code></a> construct to take on the
type of the enclosing expression has been improved in two ways. First, the
<a class="reference internal" href="../core/sqlelement.html#sqlalchemy.sql.expression.bindparam" title="sqlalchemy.sql.expression.bindparam"><code class="xref py py-func docutils literal notranslate"><span class="pre">bindparam()</span></code></a> object is <strong>copied</strong> before the new type is assigned, so that
the given <a class="reference internal" href="../core/sqlelement.html#sqlalchemy.sql.expression.bindparam" title="sqlalchemy.sql.expression.bindparam"><code class="xref py py-func docutils literal notranslate"><span class="pre">bindparam()</span></code></a> is not mutated in place. Secondly, this same
operation occurs when an <a class="reference internal" href="../core/dml.html#sqlalchemy.sql.expression.Insert" title="sqlalchemy.sql.expression.Insert"><code class="xref py py-class docutils literal notranslate"><span class="pre">Insert</span></code></a> or <a class="reference internal" href="../core/dml.html#sqlalchemy.sql.expression.Update" title="sqlalchemy.sql.expression.Update"><code class="xref py py-class docutils literal notranslate"><span class="pre">Update</span></code></a> construct is compiled,
regarding the “values” that were set in the statement via the <a class="reference internal" href="../core/dml.html#sqlalchemy.sql.expression.ValuesBase.values" title="sqlalchemy.sql.expression.ValuesBase.values"><code class="xref py py-meth docutils literal notranslate"><span class="pre">ValuesBase.values()</span></code></a>
method.</p>
<p>If given an untyped <a class="reference internal" href="../core/sqlelement.html#sqlalchemy.sql.expression.bindparam" title="sqlalchemy.sql.expression.bindparam"><code class="xref py py-func docutils literal notranslate"><span class="pre">bindparam()</span></code></a>:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">bp</span> <span class="o">=</span> <span class="n">bindparam</span><span class="p">(</span><span class="s2">"some_col"</span><span class="p">)</span></pre></div>
</div>
<p>If we use this parameter as follows:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">expr</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">col</span> <span class="o">==</span> <span class="n">bp</span></pre></div>
</div>
<p>The type for <code class="docutils literal notranslate"><span class="pre">bp</span></code> remains as <code class="docutils literal notranslate"><span class="pre">NullType</span></code>, however if <code class="docutils literal notranslate"><span class="pre">mytable.c.col</span></code>
is of type <code class="docutils literal notranslate"><span class="pre">String</span></code>, then <code class="docutils literal notranslate"><span class="pre">expr.right</span></code>, that is the right side of the
binary expression, will take on the <code class="docutils literal notranslate"><span class="pre">String</span></code> type. Previously, <code class="docutils literal notranslate"><span class="pre">bp</span></code> itself
would have been changed in place to have <code class="docutils literal notranslate"><span class="pre">String</span></code> as its type.</p>
<p>Similarly, this operation occurs in an <a class="reference internal" href="../core/dml.html#sqlalchemy.sql.expression.Insert" title="sqlalchemy.sql.expression.Insert"><code class="xref py py-class docutils literal notranslate"><span class="pre">Insert</span></code></a> or <a class="reference internal" href="../core/dml.html#sqlalchemy.sql.expression.Update" title="sqlalchemy.sql.expression.Update"><code class="xref py py-class docutils literal notranslate"><span class="pre">Update</span></code></a>:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">stmt</span> <span class="o">=</span> <span class="n">mytable</span><span class="o">.</span><span class="n">update</span><span class="p">()</span><span class="o">.</span><span class="n">values</span><span class="p">(</span><span class="n">col</span><span class="o">=</span><span class="n">bp</span><span class="p">)</span></pre></div>
</div>
<p>Above, <code class="docutils literal notranslate"><span class="pre">bp</span></code> remains unchanged, but the <code class="docutils literal notranslate"><span class="pre">String</span></code> type will be used when
the statement is executed, which we can see by examining the <code class="docutils literal notranslate"><span class="pre">binds</span></code> dictionary:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="gp">>>> </span><span class="n">compiled</span> <span class="o">=</span> <span class="n">stmt</span><span class="o">.</span><span class="n">compile</span><span class="p">()</span>
<span class="gp">>>> </span><span class="n">compiled</span><span class="o">.</span><span class="n">binds</span><span class="p">[</span><span class="s1">'some_col'</span><span class="p">]</span><span class="o">.</span><span class="n">type</span>
<span class="go">String</span></pre></div>
</div>
<p>The feature allows custom types to take their expected effect within INSERT/UPDATE
statements without needing to explicitly specify those types within every
<a class="reference internal" href="../core/sqlelement.html#sqlalchemy.sql.expression.bindparam" title="sqlalchemy.sql.expression.bindparam"><code class="xref py py-func docutils literal notranslate"><span class="pre">bindparam()</span></code></a> expression.</p>
<p>The potentially backwards-compatible changes involve two unlikely
scenarios. Since the bound parameter is
<strong>cloned</strong>, users should not be relying upon making in-place changes to a
<a class="reference internal" href="../core/sqlelement.html#sqlalchemy.sql.expression.bindparam" title="sqlalchemy.sql.expression.bindparam"><code class="xref py py-func docutils literal notranslate"><span class="pre">bindparam()</span></code></a> construct once created. Additionally, code which uses
<a class="reference internal" href="../core/sqlelement.html#sqlalchemy.sql.expression.bindparam" title="sqlalchemy.sql.expression.bindparam"><code class="xref py py-func docutils literal notranslate"><span class="pre">bindparam()</span></code></a> within an <a class="reference internal" href="../core/dml.html#sqlalchemy.sql.expression.Insert" title="sqlalchemy.sql.expression.Insert"><code class="xref py py-class docutils literal notranslate"><span class="pre">Insert</span></code></a> or <a class="reference internal" href="../core/dml.html#sqlalchemy.sql.expression.Update" title="sqlalchemy.sql.expression.Update"><code class="xref py py-class docutils literal notranslate"><span class="pre">Update</span></code></a> statement
which is relying on the fact that the <a class="reference internal" href="../core/sqlelement.html#sqlalchemy.sql.expression.bindparam" title="sqlalchemy.sql.expression.bindparam"><code class="xref py py-func docutils literal notranslate"><span class="pre">bindparam()</span></code></a> is not typed according
to the column being assigned towards will no longer function in that way.</p>
<p><a class="reference external" href="http://www.sqlalchemy.org/trac/ticket/2850">#2850</a></p>
</div>
<div class="section" id="columns-can-reliably-get-their-type-from-a-column-referred-to-via-foreignkey">
<span id="migration-1765"></span><h3>Columns can reliably get their type from a column referred to via ForeignKey<a class="headerlink" href="#columns-can-reliably-get-their-type-from-a-column-referred-to-via-foreignkey" title="Permalink to this headline">¶</a></h3>
<p>There’s a long standing behavior which says that a <a class="reference internal" href="../core/metadata.html#sqlalchemy.schema.Column" title="sqlalchemy.schema.Column"><code class="xref py py-class docutils literal notranslate"><span class="pre">Column</span></code></a> can be
declared without a type, as long as that <a class="reference internal" href="../core/metadata.html#sqlalchemy.schema.Column" title="sqlalchemy.schema.Column"><code class="xref py py-class docutils literal notranslate"><span class="pre">Column</span></code></a> is referred to
by a <a class="reference internal" href="../core/constraints.html#sqlalchemy.schema.ForeignKeyConstraint" title="sqlalchemy.schema.ForeignKeyConstraint"><code class="xref py py-class docutils literal notranslate"><span class="pre">ForeignKeyConstraint</span></code></a>, and the type from the referenced column
will be copied into this one. The problem has been that this feature never
worked very well and wasn’t maintained. The core issue was that the
<a class="reference internal" href="../core/constraints.html#sqlalchemy.schema.ForeignKey" title="sqlalchemy.schema.ForeignKey"><code class="xref py py-class docutils literal notranslate"><span class="pre">ForeignKey</span></code></a> object doesn’t know what target <a class="reference internal" href="../core/metadata.html#sqlalchemy.schema.Column" title="sqlalchemy.schema.Column"><code class="xref py py-class docutils literal notranslate"><span class="pre">Column</span></code></a> it
refers to until it is asked, typically the first time the foreign key is used
to construct a <a class="reference internal" href="../core/selectable.html#sqlalchemy.sql.expression.Join" title="sqlalchemy.sql.expression.Join"><code class="xref py py-class docutils literal notranslate"><span class="pre">Join</span></code></a>. So until that time, the parent <a class="reference internal" href="../core/metadata.html#sqlalchemy.schema.Column" title="sqlalchemy.schema.Column"><code class="xref py py-class docutils literal notranslate"><span class="pre">Column</span></code></a>
would not have a type, or more specifically, it would have a default type
of <a class="reference internal" href="../core/type_api.html#sqlalchemy.types.NullType" title="sqlalchemy.types.NullType"><code class="xref py py-class docutils literal notranslate"><span class="pre">NullType</span></code></a>.</p>
<p>While it’s taken a long time, the work to reorganize the initialization of
<a class="reference internal" href="../core/constraints.html#sqlalchemy.schema.ForeignKey" title="sqlalchemy.schema.ForeignKey"><code class="xref py py-class docutils literal notranslate"><span class="pre">ForeignKey</span></code></a> objects has been completed such that this feature can
finally work acceptably. At the core of the change is that the <a class="reference internal" href="../core/constraints.html#sqlalchemy.schema.ForeignKey.column" title="sqlalchemy.schema.ForeignKey.column"><code class="xref py py-attr docutils literal notranslate"><span class="pre">ForeignKey.column</span></code></a>
attribute no longer lazily initializes the location of the target <a class="reference internal" href="../core/metadata.html#sqlalchemy.schema.Column" title="sqlalchemy.schema.Column"><code class="xref py py-class docutils literal notranslate"><span class="pre">Column</span></code></a>;
the issue with this system was that the owning <a class="reference internal" href="../core/metadata.html#sqlalchemy.schema.Column" title="sqlalchemy.schema.Column"><code class="xref py py-class docutils literal notranslate"><span class="pre">Column</span></code></a> would be stuck
with <a class="reference internal" href="../core/type_api.html#sqlalchemy.types.NullType" title="sqlalchemy.types.NullType"><code class="xref py py-class docutils literal notranslate"><span class="pre">NullType</span></code></a> as its type until the <a class="reference internal" href="../core/constraints.html#sqlalchemy.schema.ForeignKey" title="sqlalchemy.schema.ForeignKey"><code class="xref py py-class docutils literal notranslate"><span class="pre">ForeignKey</span></code></a> happened to
be used.</p>
<p>In the new version, the <a class="reference internal" href="../core/constraints.html#sqlalchemy.schema.ForeignKey" title="sqlalchemy.schema.ForeignKey"><code class="xref py py-class docutils literal notranslate"><span class="pre">ForeignKey</span></code></a> coordinates with the eventual
<a class="reference internal" href="../core/metadata.html#sqlalchemy.schema.Column" title="sqlalchemy.schema.Column"><code class="xref py py-class docutils literal notranslate"><span class="pre">Column</span></code></a> it will refer to using internal attachment events, so that the
moment the referencing <a class="reference internal" href="../core/metadata.html#sqlalchemy.schema.Column" title="sqlalchemy.schema.Column"><code class="xref py py-class docutils literal notranslate"><span class="pre">Column</span></code></a> is associated with the
<a class="reference internal" href="../core/metadata.html#sqlalchemy.schema.MetaData" title="sqlalchemy.schema.MetaData"><code class="xref py py-class docutils literal notranslate"><span class="pre">MetaData</span></code></a>, all <a class="reference internal" href="../core/constraints.html#sqlalchemy.schema.ForeignKey" title="sqlalchemy.schema.ForeignKey"><code class="xref py py-class docutils literal notranslate"><span class="pre">ForeignKey</span></code></a> objects that
refer to it will be sent a message that they need to initialize their parent
column. This system is more complicated but works more solidly; as a bonus,
there are now tests in place for a wide variety of <a class="reference internal" href="../core/metadata.html#sqlalchemy.schema.Column" title="sqlalchemy.schema.Column"><code class="xref py py-class docutils literal notranslate"><span class="pre">Column</span></code></a> /
<a class="reference internal" href="../core/constraints.html#sqlalchemy.schema.ForeignKey" title="sqlalchemy.schema.ForeignKey"><code class="xref py py-class docutils literal notranslate"><span class="pre">ForeignKey</span></code></a> configuration scenarios and error messages have been
improved to be very specific to no less than seven different error conditions.</p>
<p>Scenarios which now work correctly include:</p>
<ol class="arabic">
<li><p>The type on a <a class="reference internal" href="../core/metadata.html#sqlalchemy.schema.Column" title="sqlalchemy.schema.Column"><code class="xref py py-class docutils literal notranslate"><span class="pre">Column</span></code></a> is immediately present as soon as the
target <a class="reference internal" href="../core/metadata.html#sqlalchemy.schema.Column" title="sqlalchemy.schema.Column"><code class="xref py py-class docutils literal notranslate"><span class="pre">Column</span></code></a> becomes associated with the same <a class="reference internal" href="../core/metadata.html#sqlalchemy.schema.MetaData" title="sqlalchemy.schema.MetaData"><code class="xref py py-class docutils literal notranslate"><span class="pre">MetaData</span></code></a>;
this works no matter which side is configured first:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="gp">>>> </span><span class="kn">from</span> <span class="nn">sqlalchemy</span> <span class="k">import</span> <span class="n">Table</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="n">Integer</span><span class="p">,</span> <span class="n">ForeignKey</span>
<span class="gp">>>> </span><span class="n">metadata</span> <span class="o">=</span> <span class="n">MetaData</span><span class="p">()</span>
<span class="gp">>>> </span><span class="n">t2</span> <span class="o">=</span> <span class="n">Table</span><span class="p">(</span><span class="s1">'t2'</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="s1">'t1id'</span><span class="p">,</span> <span class="n">ForeignKey</span><span class="p">(</span><span class="s1">'t1.id'</span><span class="p">)))</span>
<span class="gp">>>> </span><span class="n">t2</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">t1id</span><span class="o">.</span><span class="n">type</span>
<span class="go">NullType()</span>
<span class="gp">>>> </span><span class="n">t1</span> <span class="o">=</span> <span class="n">Table</span><span class="p">(</span><span class="s1">'t1'</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="s1">'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="kc">True</span><span class="p">))</span>
<span class="gp">>>> </span><span class="n">t2</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">t1id</span><span class="o">.</span><span class="n">type</span>
<span class="go">Integer()</span></pre></div>
</div>
</li>
<li><p>The system now works with <a class="reference internal" href="../core/constraints.html#sqlalchemy.schema.ForeignKeyConstraint" title="sqlalchemy.schema.ForeignKeyConstraint"><code class="xref py py-class docutils literal notranslate"><span class="pre">ForeignKeyConstraint</span></code></a> as well:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="gp">>>> </span><span class="kn">from</span> <span class="nn">sqlalchemy</span> <span class="k">import</span> <span class="n">Table</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="n">Integer</span><span class="p">,</span> <span class="n">ForeignKeyConstraint</span>
<span class="gp">>>> </span><span class="n">metadata</span> <span class="o">=</span> <span class="n">MetaData</span><span class="p">()</span>
<span class="gp">>>> </span><span class="n">t2</span> <span class="o">=</span> <span class="n">Table</span><span class="p">(</span><span class="s1">'t2'</span><span class="p">,</span> <span class="n">metadata</span><span class="p">,</span>
<span class="gp">... </span> <span class="n">Column</span><span class="p">(</span><span class="s1">'t1a'</span><span class="p">),</span> <span class="n">Column</span><span class="p">(</span><span class="s1">'t1b'</span><span class="p">),</span>
<span class="gp">... </span> <span class="n">ForeignKeyConstraint</span><span class="p">([</span><span class="s1">'t1a'</span><span class="p">,</span> <span class="s1">'t1b'</span><span class="p">],</span> <span class="p">[</span><span class="s1">'t1.a'</span><span class="p">,</span> <span class="s1">'t1.b'</span><span class="p">]))</span>
<span class="gp">>>> </span><span class="n">t2</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">t1a</span><span class="o">.</span><span class="n">type</span>
<span class="go">NullType()</span>
<span class="gp">>>> </span><span class="n">t2</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">t1b</span><span class="o">.</span><span class="n">type</span>
<span class="go">NullType()</span>
<span class="gp">>>> </span><span class="n">t1</span> <span class="o">=</span> <span class="n">Table</span><span class="p">(</span><span class="s1">'t1'</span><span class="p">,</span> <span class="n">metadata</span><span class="p">,</span>
<span class="gp">... </span> <span class="n">Column</span><span class="p">(</span><span class="s1">'a'</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="kc">True</span><span class="p">),</span>
<span class="gp">... </span> <span class="n">Column</span><span class="p">(</span><span class="s1">'b'</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="kc">True</span><span class="p">))</span>
<span class="gp">>>> </span><span class="n">t2</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">t1a</span><span class="o">.</span><span class="n">type</span>
<span class="go">Integer()</span>
<span class="gp">>>> </span><span class="n">t2</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">t1b</span><span class="o">.</span><span class="n">type</span>
<span class="go">Integer()</span></pre></div>
</div>
</li>
<li><p>It even works for “multiple hops” - that is, a <a class="reference internal" href="../core/constraints.html#sqlalchemy.schema.ForeignKey" title="sqlalchemy.schema.ForeignKey"><code class="xref py py-class docutils literal notranslate"><span class="pre">ForeignKey</span></code></a> that refers to a
<a class="reference internal" href="../core/metadata.html#sqlalchemy.schema.Column" title="sqlalchemy.schema.Column"><code class="xref py py-class docutils literal notranslate"><span class="pre">Column</span></code></a> that refers to another <a class="reference internal" href="../core/metadata.html#sqlalchemy.schema.Column" title="sqlalchemy.schema.Column"><code class="xref py py-class docutils literal notranslate"><span class="pre">Column</span></code></a>:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="gp">>>> </span><span class="kn">from</span> <span class="nn">sqlalchemy</span> <span class="k">import</span> <span class="n">Table</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="n">Integer</span><span class="p">,</span> <span class="n">ForeignKey</span>
<span class="gp">>>> </span><span class="n">metadata</span> <span class="o">=</span> <span class="n">MetaData</span><span class="p">()</span>
<span class="gp">>>> </span><span class="n">t2</span> <span class="o">=</span> <span class="n">Table</span><span class="p">(</span><span class="s1">'t2'</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="s1">'t1id'</span><span class="p">,</span> <span class="n">ForeignKey</span><span class="p">(</span><span class="s1">'t1.id'</span><span class="p">)))</span>
<span class="gp">>>> </span><span class="n">t3</span> <span class="o">=</span> <span class="n">Table</span><span class="p">(</span><span class="s1">'t3'</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="s1">'t2t1id'</span><span class="p">,</span> <span class="n">ForeignKey</span><span class="p">(</span><span class="s1">'t2.t1id'</span><span class="p">)))</span>
<span class="gp">>>> </span><span class="n">t2</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">t1id</span><span class="o">.</span><span class="n">type</span>
<span class="go">NullType()</span>
<span class="gp">>>> </span><span class="n">t3</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">t2t1id</span><span class="o">.</span><span class="n">type</span>
<span class="go">NullType()</span>
<span class="gp">>>> </span><span class="n">t1</span> <span class="o">=</span> <span class="n">Table</span><span class="p">(</span><span class="s1">'t1'</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="s1">'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="kc">True</span><span class="p">))</span>
<span class="gp">>>> </span><span class="n">t2</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">t1id</span><span class="o">.</span><span class="n">type</span>
<span class="go">Integer()</span>
<span class="gp">>>> </span><span class="n">t3</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">t2t1id</span><span class="o">.</span><span class="n">type</span>
<span class="go">Integer()</span></pre></div>
</div>
</li>
</ol>
<p><a class="reference external" href="http://www.sqlalchemy.org/trac/ticket/1765">#1765</a></p>
</div>
</div>
<div class="section" id="dialect-changes">
<h2>Dialect Changes<a class="headerlink" href="#dialect-changes" title="Permalink to this headline">¶</a></h2>
<div class="section" id="firebird-fdb-is-now-the-default-firebird-dialect">
<h3>Firebird <code class="docutils literal notranslate"><span class="pre">fdb</span></code> is now the default Firebird dialect.<a class="headerlink" href="#firebird-fdb-is-now-the-default-firebird-dialect" title="Permalink to this headline">¶</a></h3>
<p>The <code class="docutils literal notranslate"><span class="pre">fdb</span></code> dialect is now used if an engine is created without a dialect
specifier, i.e. <code class="docutils literal notranslate"><span class="pre">firebird://</span></code>. <code class="docutils literal notranslate"><span class="pre">fdb</span></code> is a <code class="docutils literal notranslate"><span class="pre">kinterbasdb</span></code> compatible
DBAPI which per the Firebird project is now their official Python driver.</p>
<p><a class="reference external" href="http://www.sqlalchemy.org/trac/ticket/2504">#2504</a></p>
</div>
<div class="section" id="firebird-fdb-and-kinterbasdb-set-retaining-false-by-default">
<h3>Firebird <code class="docutils literal notranslate"><span class="pre">fdb</span></code> and <code class="docutils literal notranslate"><span class="pre">kinterbasdb</span></code> set <code class="docutils literal notranslate"><span class="pre">retaining=False</span></code> by default<a class="headerlink" href="#firebird-fdb-and-kinterbasdb-set-retaining-false-by-default" title="Permalink to this headline">¶</a></h3>
<p>Both the <code class="docutils literal notranslate"><span class="pre">fdb</span></code> and <code class="docutils literal notranslate"><span class="pre">kinterbasdb</span></code> DBAPIs support a flag <code class="docutils literal notranslate"><span class="pre">retaining=True</span></code>
which can be passed to the <code class="docutils literal notranslate"><span class="pre">commit()</span></code> and <code class="docutils literal notranslate"><span class="pre">rollback()</span></code> methods of its
connection. The documented rationale for this flag is so that the DBAPI
can re-use internal transaction state for subsequent transactions, for the
purposes of improving performance. However, newer documentation refers
to analyses of Firebird’s “garbage collection” which expresses that this flag
can have a negative effect on the database’s ability to process cleanup
tasks, and has been reported as <em>lowering</em> performance as a result.</p>
<p>It’s not clear how this flag is actually usable given this information,
and as it appears to be only a performance enhancing feature, it now defaults
to <code class="docutils literal notranslate"><span class="pre">False</span></code>. The value can be controlled by passing the flag <code class="docutils literal notranslate"><span class="pre">retaining=True</span></code>
to the <a class="reference internal" href="../core/engines.html#sqlalchemy.create_engine" title="sqlalchemy.create_engine"><code class="xref py py-func docutils literal notranslate"><span class="pre">create_engine()</span></code></a> call. This is a new flag which is added as of
0.8.2, so applications on 0.8.2 can begin setting this to <code class="docutils literal notranslate"><span class="pre">True</span></code> or <code class="docutils literal notranslate"><span class="pre">False</span></code>
as desired.</p>
<div class="admonition seealso">
<p class="admonition-title">See also</p>
<p><a class="reference internal" href="../dialects/firebird.html#module-sqlalchemy.dialects.firebird.fdb" title="sqlalchemy.dialects.firebird.fdb"><code class="xref py py-mod docutils literal notranslate"><span class="pre">sqlalchemy.dialects.firebird.fdb</span></code></a></p>
<p><a class="reference internal" href="../dialects/firebird.html#module-sqlalchemy.dialects.firebird.kinterbasdb" title="sqlalchemy.dialects.firebird.kinterbasdb"><code class="xref py py-mod docutils literal notranslate"><span class="pre">sqlalchemy.dialects.firebird.kinterbasdb</span></code></a></p>
<p><a class="reference external" href="http://pythonhosted.org/fdb/usage-guide.html#retaining-transactions">http://pythonhosted.org/fdb/usage-guide.html#retaining-transactions</a> - information
on the “retaining” flag.</p>
</div>
<p><a class="reference external" href="http://www.sqlalchemy.org/trac/ticket/2763">#2763</a></p>
</div>
</div>
</div>
</div>
</div>
<div id="docs-bottom-navigation" class="docs-navigation-links, withsidebar">
Previous:
<a href="migration_10.html" title="previous chapter">What’s New in SQLAlchemy 1.0?</a>
Next:
<a href="migration_08.html" title="next chapter">What’s New in SQLAlchemy 0.8?</a>
<div id="docs-copyright">
© <a href="../copyright.html">Copyright</a> 2007-2019, the SQLAlchemy authors and contributors.
Created using <a href="http://sphinx.pocoo.org/">Sphinx</a> 2.0.1.
</div>
</div>
</div>
<script type="text/javascript">
var DOCUMENTATION_OPTIONS = {
URL_ROOT: '../',
VERSION: '1.2.19',
COLLAPSE_MODINDEX: false,
FILE_SUFFIX: '.html'
};
</script>
<script type="text/javascript" id="documentation_options" data-url_root="../" src="../_static/documentation_options.js"></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>
<script type="text/javascript" src="../_static/language_data.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>
</body>
</html>
distrib
>
Mageia
>
7
>
armv7hl
>
media
>
core-updates
>
by-pkgid
>
b0b6ffab06cbeede296e36ce94734bf8
>
files
>
748
