<!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 1.2?
—
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="1.2 Changelog" href="changelog_12.html" />
<link rel="prev" title="Changes and Migration" href="index.html" />
<!-- end layout.mako headers -->
</head>
<body>
<div id="docs-container">
<div id="docs-top-navigation-container" class="body-background">
<div id="docs-header">
<div id="docs-version-header">
Release: <span class="version-num">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 class="selected"><span class="link-container"><strong>What’s New in SQLAlchemy 1.2?</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-7-and-up">Targeting Python 2.7 and Up</a></span></li>
</ul>
</li>
<li><span class="link-container"><a class="reference external" href="#new-features-and-improvements-orm">New Features and Improvements - ORM</a></span><ul>
<li><span class="link-container"><a class="reference external" href="#baked-loading-now-the-default-for-lazy-loads">“Baked” loading now the default for lazy loads</a></span></li>
<li><span class="link-container"><a class="reference external" href="#new-selectin-eager-loading-loads-all-collections-at-once-using-in">New “selectin” eager loading, loads all collections at once using IN</a></span></li>
<li><span class="link-container"><a class="reference external" href="#selectin-polymorphic-loading-loads-subclasses-using-separate-in-queries">“selectin” polymorphic loading, loads subclasses using separate IN queries</a></span></li>
<li><span class="link-container"><a class="reference external" href="#orm-attributes-that-can-receive-ad-hoc-sql-expressions">ORM attributes that can receive ad-hoc SQL expressions</a></span></li>
<li><span class="link-container"><a class="reference external" href="#orm-support-of-multiple-table-deletes">ORM Support of multiple-table deletes</a></span></li>
<li><span class="link-container"><a class="reference external" href="#support-for-bulk-updates-of-hybrids-composites">Support for bulk updates of hybrids, composites</a></span></li>
<li><span class="link-container"><a class="reference external" href="#hybrid-attributes-support-reuse-among-subclasses-redefinition-of-getter">Hybrid attributes support reuse among subclasses, redefinition of @getter</a></span></li>
<li><span class="link-container"><a class="reference external" href="#new-bulk-replace-event">New bulk_replace event</a></span></li>
<li><span class="link-container"><a class="reference external" href="#new-modified-event-handler-for-sqlalchemy-ext-mutable">New “modified” event handler for sqlalchemy.ext.mutable</a></span></li>
<li><span class="link-container"><a class="reference external" href="#added-for-update-arguments-to-session-refresh">Added “for update” arguments to Session.refresh</a></span></li>
<li><span class="link-container"><a class="reference external" href="#in-place-mutation-operators-work-for-mutableset-mutablelist">In-place mutation operators work for MutableSet, MutableList</a></span></li>
<li><span class="link-container"><a class="reference external" href="#associationproxy-any-has-contains-work-with-chained-association-proxies">AssociationProxy any(), has(), contains() work with chained association proxies</a></span></li>
<li><span class="link-container"><a class="reference external" href="#change-4137">Identity key enhancements to support sharding</a></span></li>
</ul>
</li>
<li><span class="link-container"><a class="reference external" href="#new-features-and-improvements-core">New Features and Improvements - Core</a></span><ul>
<li><span class="link-container"><a class="reference external" href="#boolean-datatype-now-enforces-strict-true-false-none-values">Boolean datatype now enforces strict True/False/None values</a></span></li>
<li><span class="link-container"><a class="reference external" href="#pessimistic-disconnection-detection-added-to-the-connection-pool">Pessimistic disconnection detection added to the connection pool</a></span></li>
<li><span class="link-container"><a class="reference external" href="#the-in-not-in-operator-s-empty-collection-behavior-is-now-configurable-default-expression-simplified">The IN / NOT IN operator’s empty collection behavior is now configurable; default expression simplified</a></span></li>
<li><span class="link-container"><a class="reference external" href="#late-expanded-in-parameter-sets-allow-in-expressions-with-cached-statements">Late-expanded IN parameter sets allow IN expressions with cached statements</a></span></li>
<li><span class="link-container"><a class="reference external" href="#flattened-operator-precedence-for-comparison-operators">Flattened operator precedence for comparison operators</a></span></li>
<li><span class="link-container"><a class="reference external" href="#support-for-sql-comments-on-table-column-includes-ddl-reflection">Support for SQL Comments on Table, Column, includes DDL, reflection</a></span></li>
<li><span class="link-container"><a class="reference external" href="#multiple-table-criteria-support-for-delete">Multiple-table criteria support for DELETE</a></span></li>
<li><span class="link-container"><a class="reference external" href="#new-autoescape-option-for-startswith-endswith">New “autoescape” option for startswith(), endswith()</a></span></li>
<li><span class="link-container"><a class="reference external" href="#stronger-typing-added-to-float-datatypes">Stronger typing added to “float” datatypes</a></span></li>
<li><span class="link-container"><a class="reference external" href="#support-for-grouping-sets-cube-rollup">Support for GROUPING SETS, CUBE, ROLLUP</a></span></li>
<li><span class="link-container"><a class="reference external" href="#parameter-helper-for-multi-valued-insert-with-contextual-default-generator">Parameter helper for multi-valued INSERT with contextual default generator</a></span></li>
</ul>
</li>
<li><span class="link-container"><a class="reference external" href="#key-behavioral-changes-orm">Key Behavioral Changes - ORM</a></span><ul>
<li><span class="link-container"><a class="reference external" href="#the-after-rollback-session-event-now-emits-before-the-expiration-of-objects">The after_rollback() Session event now emits before the expiration of objects</a></span></li>
<li><span class="link-container"><a class="reference external" href="#fixed-issue-involving-single-table-inheritance-with-select-from">Fixed issue involving single-table inheritance with <code class="docutils literal notranslate"><span class="pre">select_from()</span></code></a></span></li>
<li><span class="link-container"><a class="reference external" href="#previous-collection-is-no-longer-mutated-upon-replacement">Previous collection is no longer mutated upon replacement</a></span></li>
<li><span class="link-container"><a class="reference external" href="#a-validates-method-receives-all-values-on-bulk-collection-set-before-comparison">A @validates method receives all values on bulk-collection set before comparison</a></span></li>
<li><span class="link-container"><a class="reference external" href="#use-flag-dirty-to-mark-an-object-as-dirty-without-any-attribute-changing">Use flag_dirty() to mark an object as “dirty” without any attribute changing</a></span></li>
<li><span class="link-container"><a class="reference external" href="#scope-keyword-removed-from-scoped-session">“scope” keyword removed from scoped_session</a></span></li>
<li><span class="link-container"><a class="reference external" href="#refinements-to-post-update-in-conjunction-with-onupdate">Refinements to post_update in conjunction with onupdate</a></span></li>
<li><span class="link-container"><a class="reference external" href="#post-update-integrates-with-orm-versioning">post_update integrates with ORM versioning</a></span></li>
</ul>
</li>
<li><span class="link-container"><a class="reference external" href="#key-behavioral-changes-core">Key Behavioral Changes - Core</a></span><ul>
<li><span class="link-container"><a class="reference external" href="#the-typing-behavior-of-custom-operators-has-been-made-consistent">The typing behavior of custom operators has been made consistent</a></span></li>
<li><span class="link-container"><a class="reference external" href="#percent-signs-in-literal-column-now-conditionally-escaped">Percent signs in literal_column() now conditionally escaped</a></span></li>
<li><span class="link-container"><a class="reference external" href="#the-column-level-collate-keyword-now-quotes-the-collation-name">The column-level COLLATE keyword now quotes the collation name</a></span></li>
</ul>
</li>
<li><span class="link-container"><a class="reference external" href="#dialect-improvements-and-changes-postgresql">Dialect Improvements and Changes - PostgreSQL</a></span><ul>
<li><span class="link-container"><a class="reference external" href="#support-for-batch-mode-fast-execution-helpers">Support for Batch Mode / Fast Execution Helpers</a></span></li>
<li><span class="link-container"><a class="reference external" href="#support-for-fields-specification-in-interval-including-full-reflection">Support for fields specification in INTERVAL, including full reflection</a></span></li>
</ul>
</li>
<li><span class="link-container"><a class="reference external" href="#dialect-improvements-and-changes-mysql">Dialect Improvements and Changes - MySQL</a></span><ul>
<li><span class="link-container"><a class="reference external" href="#support-for-insert-on-duplicate-key-update">Support for INSERT..ON DUPLICATE KEY UPDATE</a></span></li>
</ul>
</li>
<li><span class="link-container"><a class="reference external" href="#dialect-improvements-and-changes-oracle">Dialect Improvements and Changes - Oracle</a></span><ul>
<li><span class="link-container"><a class="reference external" href="#major-refactor-to-cx-oracle-dialect-typing-system">Major Refactor to cx_Oracle Dialect, Typing System</a></span></li>
<li><span class="link-container"><a class="reference external" href="#oracle-unique-check-constraints-now-reflected">Oracle Unique, Check constraints now reflected</a></span></li>
<li><span class="link-container"><a class="reference external" href="#oracle-foreign-key-constraint-names-are-now-name-normalized">Oracle foreign key constraint names are now “name normalized”</a></span></li>
</ul>
</li>
<li><span class="link-container"><a class="reference external" href="#dialect-improvements-and-changes-sql-server">Dialect Improvements and Changes - SQL Server</a></span><ul>
<li><span class="link-container"><a class="reference external" href="#sql-server-schema-names-with-embedded-dots-supported">SQL Server schema names with embedded dots supported</a></span></li>
<li><span class="link-container"><a class="reference external" href="#autocommit-isolation-level-support">AUTOCOMMIT isolation level support</a></span></li>
</ul>
</li>
</ul>
</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><span class="link-container"><a class="reference external" href="migration_09.html">What’s New in SQLAlchemy 0.9?</a></span></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-1-2">
<h1>What’s New in SQLAlchemy 1.2?<a class="headerlink" href="#what-s-new-in-sqlalchemy-1-2" 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 1.1
and SQLAlchemy version 1.2.</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 1.2,
and also documents changes which affect users migrating
their applications from the 1.1 series of SQLAlchemy to 1.2.</p>
<p>Please carefully review the sections on behavioral changes for
potentially backwards-incompatible changes in behavior.</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-7-and-up">
<h3>Targeting Python 2.7 and Up<a class="headerlink" href="#targeting-python-2-7-and-up" title="Permalink to this headline">¶</a></h3>
<p>SQLAlchemy 1.2 now moves the minimum Python version to 2.7, no longer
supporting 2.6. New language features are expected to be merged
into the 1.2 series that were not supported in Python 2.6. For Python 3 support,
SQLAlchemy is currently tested on versions 3.5 and 3.6.</p>
</div>
</div>
<div class="section" id="new-features-and-improvements-orm">
<h2>New Features and Improvements - ORM<a class="headerlink" href="#new-features-and-improvements-orm" title="Permalink to this headline">¶</a></h2>
<div class="section" id="baked-loading-now-the-default-for-lazy-loads">
<span id="change-3954"></span><h3>“Baked” loading now the default for lazy loads<a class="headerlink" href="#baked-loading-now-the-default-for-lazy-loads" title="Permalink to this headline">¶</a></h3>
<p>The <a class="reference internal" href="../orm/extensions/baked.html#module-sqlalchemy.ext.baked" title="sqlalchemy.ext.baked"><code class="xref py py-mod docutils literal notranslate"><span class="pre">sqlalchemy.ext.baked</span></code></a> extension, first introduced in the 1.0 series,
allows for the construction of a so-called <a class="reference internal" href="../orm/extensions/baked.html#sqlalchemy.ext.baked.BakedQuery" title="sqlalchemy.ext.baked.BakedQuery"><code class="xref py py-class docutils literal notranslate"><span class="pre">BakedQuery</span></code></a> object,
which is an object that generates 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 in conjunction
with a cache key representing the structure of the query; this cache key
is then linked to the resulting string SQL statement so that subsequent use
of another <a class="reference internal" href="../orm/extensions/baked.html#sqlalchemy.ext.baked.BakedQuery" title="sqlalchemy.ext.baked.BakedQuery"><code class="xref py py-class docutils literal notranslate"><span class="pre">BakedQuery</span></code></a> with the same structure will bypass all the
overhead of building 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, building the core
<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> object within, as well as the compilation of the <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>
into a string, cutting out well the majority of function call overhead normally
associated with constructing and emitting an ORM <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.</p>
<p>The <a class="reference internal" href="../orm/extensions/baked.html#sqlalchemy.ext.baked.BakedQuery" title="sqlalchemy.ext.baked.BakedQuery"><code class="xref py py-class docutils literal notranslate"><span class="pre">BakedQuery</span></code></a> is now used by default by the ORM when it generates
a “lazy” query for the lazy load of a <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> construct, e.g.
that of the default <code class="docutils literal notranslate"><span class="pre">lazy="select"</span></code> relationship loader strategy. This
will allow for a significant reduction in function calls within the scope
of an application’s use of lazy load queries to load collections and related
objects. Previously, this feature was available
in 1.0 and 1.1 through the use of a global API method or by using the
<code class="docutils literal notranslate"><span class="pre">baked_select</span></code> strategy, it’s now the only implementation for this behavior.
The feature has also been improved such that the caching can still take place
for objects that have additional loader options in effect subsequent
to the lazy load.</p>
<p>The caching behavior can be disabled on a per-relationship basis using the
<a class="reference internal" href="../orm/relationship_api.html#sqlalchemy.orm.relationship.params.bake_queries" title="sqlalchemy.orm.relationship"><code class="xref py py-paramref docutils literal notranslate"><span class="pre">relationship.bake_queries</span></code></a> flag, which is available for
very unusual cases, such as a relationship that uses a custom
<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> implementation that’s not compatible with caching.</p>
<p><a class="reference external" href="http://www.sqlalchemy.org/trac/ticket/3954">#3954</a></p>
</div>
<div class="section" id="new-selectin-eager-loading-loads-all-collections-at-once-using-in">
<span id="change-3944"></span><h3>New “selectin” eager loading, loads all collections at once using IN<a class="headerlink" href="#new-selectin-eager-loading-loads-all-collections-at-once-using-in" title="Permalink to this headline">¶</a></h3>
<p>A new eager loader called “selectin” loading is added, which in many ways
is similar to “subquery” loading, however produces a simpler SQL statement
that is cacheable as well as more efficient.</p>
<p>Given a query as below:</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="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="n">like</span><span class="p">(</span><span class="s1">'</span><span class="si">%e</span><span class="s1">d%'</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">User</span><span class="o">.</span><span class="n">addresses</span><span class="p">))</span></pre></div>
</div>
<p>The SQL produced would be the query against <code class="docutils literal notranslate"><span class="pre">User</span></code> followed by the
subqueryload for <code class="docutils literal notranslate"><span class="pre">User.addresses</span></code> (note the parameters are also listed):</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span>SELECT users.id AS users_id, users.name AS users_name
FROM users
WHERE users.name LIKE ?
('%ed%',)
SELECT addresses.id AS addresses_id,
addresses.user_id AS addresses_user_id,
addresses.email_address AS addresses_email_address,
anon_1.users_id AS anon_1_users_id
FROM (SELECT users.id AS users_id
FROM users
WHERE users.name LIKE ?) AS anon_1
JOIN addresses ON anon_1.users_id = addresses.user_id
ORDER BY anon_1.users_id
('%ed%',)</pre></div>
</div>
<p>With “selectin” loading, we instead get a SELECT that refers to the
actual primary key values loaded in the parent query:</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="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="n">like</span><span class="p">(</span><span class="s1">'</span><span class="si">%e</span><span class="s1">d%'</span><span class="p">))</span><span class="o">.</span>\
<span class="n">options</span><span class="p">(</span><span class="n">selectinload</span><span class="p">(</span><span class="n">User</span><span class="o">.</span><span class="n">addresses</span><span class="p">))</span></pre></div>
</div>
<p>Produces:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span>SELECT users.id AS users_id, users.name AS users_name
FROM users
WHERE users.name LIKE ?
('%ed%',)
SELECT users_1.id AS users_1_id,
addresses.id AS addresses_id,
addresses.user_id AS addresses_user_id,
addresses.email_address AS addresses_email_address
FROM users AS users_1
JOIN addresses ON users_1.id = addresses.user_id
WHERE users_1.id IN (?, ?)
ORDER BY users_1.id
(1, 3)</pre></div>
</div>
<p>The above SELECT statement includes these advantages:</p>
<ul class="simple">
<li><p>It doesn’t use a subquery, just an INNER JOIN, meaning it will perform
much better on a database like MySQL that doesn’t like subqueries</p></li>
<li><p>Its structure is independent of the original query; in conjunction with the
new <a class="reference internal" href="#change-3953"><span class="std std-ref">expanding IN parameter system</span></a> we can in most cases
use the “baked” query to cache the string SQL, reducing per-query overhead
significantly</p></li>
<li><p>Because the query only fetches for a given list of primary key identifiers,
“selectin” loading is potentially compatible with <a class="reference internal" href="../orm/query.html#sqlalchemy.orm.query.Query.yield_per" title="sqlalchemy.orm.query.Query.yield_per"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Query.yield_per()</span></code></a> to
operate on chunks of a SELECT result at a time, provided that the
database driver allows for multiple, simultaneous cursors (SQLite, PostgreSQL;
<strong>not</strong> MySQL drivers or SQL Server ODBC drivers). Neither joined eager
loading nor subquery eager loading are compatible with <a class="reference internal" href="../orm/query.html#sqlalchemy.orm.query.Query.yield_per" title="sqlalchemy.orm.query.Query.yield_per"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Query.yield_per()</span></code></a>.</p></li>
</ul>
<p>The disadvantages of selectin eager loading are potentially large SQL
queries, with large lists of IN parameters. The list of IN parameters themselves
are chunked in groups of 500, so a result set of more than 500 lead objects
will have more additional “SELECT IN” queries following. Also, support
for composite primary keys depends on the database’s ability to use
tuples with IN, e.g.
<code class="docutils literal notranslate"><span class="pre">(table.column_one,</span> <span class="pre">table_column_two)</span> <span class="pre">IN</span> <span class="pre">((?,</span> <span class="pre">?),</span> <span class="pre">(?,</span> <span class="pre">?)</span> <span class="pre">(?,</span> <span class="pre">?))</span></code>.
Currently, PostgreSQL and MySQL are known to be compatible with this syntax,
SQLite is not.</p>
<div class="admonition seealso">
<p class="admonition-title">See also</p>
<p><a class="reference internal" href="../orm/loading_relationships.html#selectin-eager-loading"><span class="std std-ref">Select IN loading</span></a></p>
</div>
<p><a class="reference external" href="http://www.sqlalchemy.org/trac/ticket/3944">#3944</a></p>
</div>
<div class="section" id="selectin-polymorphic-loading-loads-subclasses-using-separate-in-queries">
<span id="change-3948"></span><h3>“selectin” polymorphic loading, loads subclasses using separate IN queries<a class="headerlink" href="#selectin-polymorphic-loading-loads-subclasses-using-separate-in-queries" title="Permalink to this headline">¶</a></h3>
<p>Along similar lines as the “selectin” relationship loading feature just
described at <a class="reference internal" href="#change-3944"><span class="std std-ref">New “selectin” eager loading, loads all collections at once using IN</span></a> is “selectin” polymorphic loading. This
is a polymorphic loading feature tailored primarily towards joined eager
loading that allows the loading of the base entity to proceed with a simple
SELECT statement, but then the attributes of the additional subclasses
are loaded with additional SELECT statements:</p>
<div class="highlight-python+sql notranslate"><div class="highlight"><pre><span></span><span class="kn">from</span> <span class="nn">sqlalchemy.orm</span> <span class="kn">import</span> <span class="n">selectin_polymorphic</span>
<span class="n">query</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">Employee</span><span class="p">)</span><span class="o">.</span><span class="n">options</span><span class="p">(</span>
<span class="n">selectin_polymorphic</span><span class="p">(</span><span class="n">Employee</span><span class="p">,</span> <span class="p">[</span><span class="n">Manager</span><span class="p">,</span> <span class="n">Engineer</span><span class="p">])</span>
<span class="p">)</span>
<div class='show_sql'>query.all()
SELECT
employee.id AS employee_id,
employee.name AS employee_name,
employee.type AS employee_type
FROM employee
()
SELECT
engineer.id AS engineer_id,
employee.id AS employee_id,
employee.type AS employee_type,
engineer.engineer_name AS engineer_engineer_name
FROM employee JOIN engineer ON employee.id = engineer.id
WHERE employee.id IN (?, ?) ORDER BY employee.id
(1, 2)
SELECT
manager.id AS manager_id,
employee.id AS employee_id,
employee.type AS employee_type,
manager.manager_name AS manager_manager_name
FROM employee JOIN manager ON employee.id = manager.id
WHERE employee.id IN (?) ORDER BY employee.id
(3,)</div></pre></div>
</div>
<div class="admonition seealso">
<p class="admonition-title">See also</p>
<p><a class="reference internal" href="../orm/inheritance_loading.html#polymorphic-selectin"><span class="std std-ref">Polymorphic Selectin Loading</span></a></p>
</div>
<p><a class="reference external" href="http://www.sqlalchemy.org/trac/ticket/3948">#3948</a></p>
</div>
<div class="section" id="orm-attributes-that-can-receive-ad-hoc-sql-expressions">
<span id="change-3058"></span><h3>ORM attributes that can receive ad-hoc SQL expressions<a class="headerlink" href="#orm-attributes-that-can-receive-ad-hoc-sql-expressions" title="Permalink to this headline">¶</a></h3>
<p>A new ORM attribute type <a class="reference internal" href="../orm/loading_columns.html#sqlalchemy.orm.query_expression" title="sqlalchemy.orm.query_expression"><code class="xref py py-func docutils literal notranslate"><span class="pre">orm.query_expression()</span></code></a> is added which
is similar to <a class="reference internal" href="../orm/loading_columns.html#sqlalchemy.orm.deferred" title="sqlalchemy.orm.deferred"><code class="xref py py-func docutils literal notranslate"><span class="pre">orm.deferred()</span></code></a>, except its SQL expression
is determined at query time using a new option <a class="reference internal" href="../orm/loading_columns.html#sqlalchemy.orm.with_expression" title="sqlalchemy.orm.with_expression"><code class="xref py py-func docutils literal notranslate"><span class="pre">orm.with_expression()</span></code></a>;
if not specified, the attribute defaults to <code class="docutils literal notranslate"><span class="pre">None</span></code>:</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">query_expression</span>
<span class="kn">from</span> <span class="nn">sqlalchemy.orm</span> <span class="k">import</span> <span class="n">with_expression</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">x</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">y</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="c1"># will be None normally...</span>
<span class="n">expr</span> <span class="o">=</span> <span class="n">query_expression</span><span class="p">()</span>
<span class="c1"># but let's give it x + y</span>
<span class="n">a1</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">A</span><span class="p">)</span><span class="o">.</span><span class="n">options</span><span class="p">(</span>
<span class="n">with_expression</span><span class="p">(</span><span class="n">A</span><span class="o">.</span><span class="n">expr</span><span class="p">,</span> <span class="n">A</span><span class="o">.</span><span class="n">x</span> <span class="o">+</span> <span class="n">A</span><span class="o">.</span><span class="n">y</span><span class="p">))</span><span class="o">.</span><span class="n">first</span><span class="p">()</span>
<span class="nb">print</span><span class="p">(</span><span class="n">a1</span><span class="o">.</span><span class="n">expr</span><span class="p">)</span></pre></div>
</div>
<div class="admonition seealso">
<p class="admonition-title">See also</p>
<p><a class="reference internal" href="../orm/mapped_sql_expr.html#mapper-querytime-expression"><span class="std std-ref">Query-time SQL expressions as mapped attributes</span></a></p>
</div>
<p><a class="reference external" href="http://www.sqlalchemy.org/trac/ticket/3058">#3058</a></p>
</div>
<div class="section" id="orm-support-of-multiple-table-deletes">
<span id="change-orm-959"></span><h3>ORM Support of multiple-table deletes<a class="headerlink" href="#orm-support-of-multiple-table-deletes" title="Permalink to this headline">¶</a></h3>
<p>The ORM <a class="reference internal" href="../orm/query.html#sqlalchemy.orm.query.Query.delete" title="sqlalchemy.orm.query.Query.delete"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Query.delete()</span></code></a> method supports multiple-table criteria
for DELETE, as introduced in <a class="reference internal" href="#change-959"><span class="std std-ref">Multiple-table criteria support for DELETE</span></a>. The feature works
in the same manner as multiple-table criteria for UPDATE, first
introduced in 0.8 and described at <a class="reference internal" href="migration_08.html#change-orm-2365"><span class="std std-ref">Query.update() supports UPDATE..FROM</span></a>.</p>
<p>Below, we emit a DELETE against <code class="docutils literal notranslate"><span class="pre">SomeEntity</span></code>, adding
a FROM clause (or equivalent, depending on backend)
against <code class="docutils literal notranslate"><span class="pre">SomeOtherEntity</span></code>:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">query</span><span class="p">(</span><span class="n">SomeEntity</span><span class="p">)</span><span class="o">.</span>\
<span class="nb">filter</span><span class="p">(</span><span class="n">SomeEntity</span><span class="o">.</span><span class="n">id</span><span class="o">==</span><span class="n">SomeOtherEntity</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">SomeOtherEntity</span><span class="o">.</span><span class="n">foo</span><span class="o">==</span><span class="s1">'bar'</span><span class="p">)</span><span class="o">.</span>\
<span class="n">delete</span><span class="p">()</span></pre></div>
</div>
<div class="admonition seealso">
<p class="admonition-title">See also</p>
<p><a class="reference internal" href="#change-959"><span class="std std-ref">Multiple-table criteria support for DELETE</span></a></p>
</div>
<p><a class="reference external" href="http://www.sqlalchemy.org/trac/ticket/959">#959</a></p>
</div>
<div class="section" id="support-for-bulk-updates-of-hybrids-composites">
<span id="change-3229"></span><h3>Support for bulk updates of hybrids, composites<a class="headerlink" href="#support-for-bulk-updates-of-hybrids-composites" title="Permalink to this headline">¶</a></h3>
<p>Both hybrid attributes (e.g. <a class="reference internal" href="../orm/extensions/hybrid.html#module-sqlalchemy.ext.hybrid" title="sqlalchemy.ext.hybrid"><code class="xref py py-mod docutils literal notranslate"><span class="pre">sqlalchemy.ext.hybrid</span></code></a>) as well as composite
attributes (<a class="reference internal" href="../orm/composites.html#mapper-composite"><span class="std std-ref">Composite Column Types</span></a>) now support being used in the
SET clause of an UPDATE statement when using <a class="reference internal" href="../orm/query.html#sqlalchemy.orm.query.Query.update" title="sqlalchemy.orm.query.Query.update"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Query.update()</span></code></a>.</p>
<p>For hybrids, simple expressions can be used directly, or the new decorator
<a class="reference internal" href="../orm/extensions/hybrid.html#sqlalchemy.ext.hybrid.hybrid_property.update_expression" title="sqlalchemy.ext.hybrid.hybrid_property.update_expression"><code class="xref py py-meth docutils literal notranslate"><span class="pre">hybrid_property.update_expression()</span></code></a> can be used to break a value
into multiple columns/expressions:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="k">class</span> <span class="nc">Person</span><span class="p">(</span><span class="n">Base</span><span class="p">):</span>
<span class="c1"># ...</span>
<span class="n">first_name</span> <span class="o">=</span> <span class="n">Column</span><span class="p">(</span><span class="n">String</span><span class="p">(</span><span class="mi">10</span><span class="p">))</span>
<span class="n">last_name</span> <span class="o">=</span> <span class="n">Column</span><span class="p">(</span><span class="n">String</span><span class="p">(</span><span class="mi">10</span><span class="p">))</span>
<span class="nd">@hybrid</span><span class="o">.</span><span class="n">hybrid_property</span>
<span class="k">def</span> <span class="nf">name</span><span class="p">(</span><span class="bp">self</span><span class="p">):</span>
<span class="k">return</span> <span class="bp">self</span><span class="o">.</span><span class="n">first_name</span> <span class="o">+</span> <span class="s1">' '</span> <span class="o">+</span> <span class="bp">self</span><span class="o">.</span><span class="n">last_name</span>
<span class="nd">@name</span><span class="o">.</span><span class="n">expression</span>
<span class="k">def</span> <span class="nf">name</span><span class="p">(</span><span class="bp">cls</span><span class="p">):</span>
<span class="k">return</span> <span class="n">func</span><span class="o">.</span><span class="n">concat</span><span class="p">(</span><span class="bp">cls</span><span class="o">.</span><span class="n">first_name</span><span class="p">,</span> <span class="s1">' '</span><span class="p">,</span> <span class="bp">cls</span><span class="o">.</span><span class="n">last_name</span><span class="p">)</span>
<span class="nd">@name</span><span class="o">.</span><span class="n">update_expression</span>
<span class="k">def</span> <span class="nf">name</span><span class="p">(</span><span class="bp">cls</span><span class="p">,</span> <span class="n">value</span><span class="p">):</span>
<span class="n">f</span><span class="p">,</span> <span class="n">l</span> <span class="o">=</span> <span class="n">value</span><span class="o">.</span><span class="n">split</span><span class="p">(</span><span class="s1">' '</span><span class="p">,</span> <span class="mi">1</span><span class="p">)</span>
<span class="k">return</span> <span class="p">[(</span><span class="bp">cls</span><span class="o">.</span><span class="n">first_name</span><span class="p">,</span> <span class="n">f</span><span class="p">),</span> <span class="p">(</span><span class="bp">cls</span><span class="o">.</span><span class="n">last_name</span><span class="p">,</span> <span class="n">l</span><span class="p">)]</span></pre></div>
</div>
<p>Above, an UPDATE can be rendered using:</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">Person</span><span class="p">)</span><span class="o">.</span><span class="n">filter</span><span class="p">(</span><span class="n">Person</span><span class="o">.</span><span class="n">id</span> <span class="o">==</span> <span class="mi">5</span><span class="p">)</span><span class="o">.</span><span class="n">update</span><span class="p">(</span>
<span class="p">{</span><span class="n">Person</span><span class="o">.</span><span class="n">name</span><span class="p">:</span> <span class="s2">"Dr. No"</span><span class="p">})</span></pre></div>
</div>
<p>Similar functionality is available for composites, where composite values
will be broken out into their individual columns for bulk UPDATE:</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">Vertex</span><span class="p">)</span><span class="o">.</span><span class="n">update</span><span class="p">({</span><span class="n">Edge</span><span class="o">.</span><span class="n">start</span><span class="p">:</span> <span class="n">Point</span><span class="p">(</span><span class="mi">3</span><span class="p">,</span> <span class="mi">4</span><span class="p">)})</span></pre></div>
</div>
<div class="admonition seealso">
<p class="admonition-title">See also</p>
<p><a class="reference internal" href="../orm/extensions/hybrid.html#hybrid-bulk-update"><span class="std std-ref">Allowing Bulk ORM Update</span></a></p>
</div>
</div>
<div class="section" id="hybrid-attributes-support-reuse-among-subclasses-redefinition-of-getter">
<span id="change-3911-3912"></span><h3>Hybrid attributes support reuse among subclasses, redefinition of @getter<a class="headerlink" href="#hybrid-attributes-support-reuse-among-subclasses-redefinition-of-getter" title="Permalink to this headline">¶</a></h3>
<p>The <a class="reference internal" href="../orm/extensions/hybrid.html#sqlalchemy.ext.hybrid.hybrid_property" title="sqlalchemy.ext.hybrid.hybrid_property"><code class="xref py py-class docutils literal notranslate"><span class="pre">sqlalchemy.ext.hybrid.hybrid_property</span></code></a> class now supports
calling mutators like <code class="docutils literal notranslate"><span class="pre">@setter</span></code>, <code class="docutils literal notranslate"><span class="pre">@expression</span></code> etc. multiple times
across subclasses, and now provides a <code class="docutils literal notranslate"><span class="pre">@getter</span></code> mutator, so that
a particular hybrid can be repurposed across subclasses or other
classes. This now is similar to the behavior of <code class="docutils literal notranslate"><span class="pre">@property</span></code> in standard
Python:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="k">class</span> <span class="nc">FirstNameOnly</span><span class="p">(</span><span class="n">Base</span><span class="p">):</span>
<span class="c1"># ...</span>
<span class="n">first_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="nd">@hybrid_property</span>
<span class="k">def</span> <span class="nf">name</span><span class="p">(</span><span class="bp">self</span><span class="p">):</span>
<span class="k">return</span> <span class="bp">self</span><span class="o">.</span><span class="n">first_name</span>
<span class="nd">@name</span><span class="o">.</span><span class="n">setter</span>
<span class="k">def</span> <span class="nf">name</span><span class="p">(</span><span class="bp">self</span><span class="p">,</span> <span class="n">value</span><span class="p">):</span>
<span class="bp">self</span><span class="o">.</span><span class="n">first_name</span> <span class="o">=</span> <span class="n">value</span>
<span class="k">class</span> <span class="nc">FirstNameLastName</span><span class="p">(</span><span class="n">FirstNameOnly</span><span class="p">):</span>
<span class="c1"># ...</span>
<span class="n">last_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="nd">@FirstNameOnly</span><span class="o">.</span><span class="n">name</span><span class="o">.</span><span class="n">getter</span>
<span class="k">def</span> <span class="nf">name</span><span class="p">(</span><span class="bp">self</span><span class="p">):</span>
<span class="k">return</span> <span class="bp">self</span><span class="o">.</span><span class="n">first_name</span> <span class="o">+</span> <span class="s1">' '</span> <span class="o">+</span> <span class="bp">self</span><span class="o">.</span><span class="n">last_name</span>
<span class="nd">@name</span><span class="o">.</span><span class="n">setter</span>
<span class="k">def</span> <span class="nf">name</span><span class="p">(</span><span class="bp">self</span><span class="p">,</span> <span class="n">value</span><span class="p">):</span>
<span class="bp">self</span><span class="o">.</span><span class="n">first_name</span><span class="p">,</span> <span class="bp">self</span><span class="o">.</span><span class="n">last_name</span> <span class="o">=</span> <span class="n">value</span><span class="o">.</span><span class="n">split</span><span class="p">(</span><span class="s1">' '</span><span class="p">,</span> <span class="n">maxsplit</span><span class="o">=</span><span class="mi">1</span><span class="p">)</span>
<span class="nd">@name</span><span class="o">.</span><span class="n">expression</span>
<span class="k">def</span> <span class="nf">name</span><span class="p">(</span><span class="bp">cls</span><span class="p">):</span>
<span class="k">return</span> <span class="n">func</span><span class="o">.</span><span class="n">concat</span><span class="p">(</span><span class="bp">cls</span><span class="o">.</span><span class="n">first_name</span><span class="p">,</span> <span class="s1">' '</span><span class="p">,</span> <span class="bp">cls</span><span class="o">.</span><span class="n">last_name</span><span class="p">)</span></pre></div>
</div>
<p>Above, the <code class="docutils literal notranslate"><span class="pre">FirstNameOnly.name</span></code> hybrid is referenced by the
<code class="docutils literal notranslate"><span class="pre">FirstNameLastName</span></code> subclass in order to repurpose it specifically to the
new subclass. This is achieved by copying the hybrid object to a new one
within each call to <code class="docutils literal notranslate"><span class="pre">@getter</span></code>, <code class="docutils literal notranslate"><span class="pre">@setter</span></code>, as well as in all other
mutator methods like <code class="docutils literal notranslate"><span class="pre">@expression</span></code>, leaving the previous hybrid’s definition
intact. Previously, methods like <code class="docutils literal notranslate"><span class="pre">@setter</span></code> would modify the existing
hybrid in-place, interfering with the definition on the superclass.</p>
<div class="admonition note">
<p class="admonition-title">Note</p>
<p>Be sure to read the documentation at <a class="reference internal" href="../orm/extensions/hybrid.html#hybrid-reuse-subclass"><span class="std std-ref">Reusing Hybrid Properties across Subclasses</span></a>
for important notes regarding how to override
<a class="reference internal" href="../orm/extensions/hybrid.html#sqlalchemy.ext.hybrid.hybrid_property.expression" title="sqlalchemy.ext.hybrid.hybrid_property.expression"><code class="xref py py-meth docutils literal notranslate"><span class="pre">hybrid_property.expression()</span></code></a>
and <a class="reference internal" href="../orm/extensions/hybrid.html#sqlalchemy.ext.hybrid.hybrid_property.comparator" title="sqlalchemy.ext.hybrid.hybrid_property.comparator"><code class="xref py py-meth docutils literal notranslate"><span class="pre">hybrid_property.comparator()</span></code></a>, as a special qualifier
<a class="reference internal" href="../orm/extensions/hybrid.html#sqlalchemy.ext.hybrid.hybrid_property.overrides" title="sqlalchemy.ext.hybrid.hybrid_property.overrides"><code class="xref py py-attr docutils literal notranslate"><span class="pre">hybrid_property.overrides</span></code></a> may be necessary to avoid name
conflicts with <a class="reference internal" href="../orm/internals.html#sqlalchemy.orm.attributes.QueryableAttribute" title="sqlalchemy.orm.attributes.QueryableAttribute"><code class="xref py py-class docutils literal notranslate"><span class="pre">QueryableAttribute</span></code></a> in some cases.</p>
</div>
<div class="admonition note">
<p class="admonition-title">Note</p>
<p>This change in <code class="docutils literal notranslate"><span class="pre">@hybrid_property</span></code> implies that when adding setters and
other state to a <code class="docutils literal notranslate"><span class="pre">@hybrid_property</span></code>, the <strong>methods must retain the name
of the original hybrid</strong>, else the new hybrid with the additional state will
be present on the class as the non-matching name. This is the same behavior
as that of the <code class="docutils literal notranslate"><span class="pre">@property</span></code> construct that is part of standard Python:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="k">class</span> <span class="nc">FirstNameOnly</span><span class="p">(</span><span class="n">Base</span><span class="p">):</span>
<span class="nd">@hybrid_property</span>
<span class="k">def</span> <span class="nf">name</span><span class="p">(</span><span class="bp">self</span><span class="p">):</span>
<span class="k">return</span> <span class="bp">self</span><span class="o">.</span><span class="n">first_name</span>
<span class="c1"># WRONG - will raise AttributeError: can't set attribute when</span>
<span class="c1"># assigning to .name</span>
<span class="nd">@name</span><span class="o">.</span><span class="n">setter</span>
<span class="k">def</span> <span class="nf">_set_name</span><span class="p">(</span><span class="bp">self</span><span class="p">,</span> <span class="n">value</span><span class="p">):</span>
<span class="bp">self</span><span class="o">.</span><span class="n">first_name</span> <span class="o">=</span> <span class="n">value</span>
<span class="k">class</span> <span class="nc">FirstNameOnly</span><span class="p">(</span><span class="n">Base</span><span class="p">):</span>
<span class="nd">@hybrid_property</span>
<span class="k">def</span> <span class="nf">name</span><span class="p">(</span><span class="bp">self</span><span class="p">):</span>
<span class="k">return</span> <span class="bp">self</span><span class="o">.</span><span class="n">first_name</span>
<span class="c1"># CORRECT - note regular Python @property works the same way</span>
<span class="nd">@name</span><span class="o">.</span><span class="n">setter</span>
<span class="k">def</span> <span class="nf">name</span><span class="p">(</span><span class="bp">self</span><span class="p">,</span> <span class="n">value</span><span class="p">):</span>
<span class="bp">self</span><span class="o">.</span><span class="n">first_name</span> <span class="o">=</span> <span class="n">value</span></pre></div>
</div>
</div>
<p><a class="reference external" href="http://www.sqlalchemy.org/trac/ticket/3911">#3911</a></p>
<p><a class="reference external" href="http://www.sqlalchemy.org/trac/ticket/3912">#3912</a></p>
</div>
<div class="section" id="new-bulk-replace-event">
<span id="change-3896-event"></span><h3>New bulk_replace event<a class="headerlink" href="#new-bulk-replace-event" title="Permalink to this headline">¶</a></h3>
<p>To suit the validation use case described in <a class="reference internal" href="#change-3896-validates"><span class="std std-ref">A @validates method receives all values on bulk-collection set before comparison</span></a>,
a new <a class="reference internal" href="../orm/events.html#sqlalchemy.orm.events.AttributeEvents.bulk_replace" title="sqlalchemy.orm.events.AttributeEvents.bulk_replace"><code class="xref py py-meth docutils literal notranslate"><span class="pre">AttributeEvents.bulk_replace()</span></code></a> method is added, which is
called in conjunction with the <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> and
<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. “bulk_replace” is called before
“append” and “remove” so that the collection can be modified ahead of comparison
to the existing collection. After that, individual items
are appended to a new target collection, firing off the “append”
event for items new to the collection, as was the previous behavior.
Below illustrates both “bulk_replace” and
“append” at the same time, including that “append” will receive an object
already handled by “bulk_replace” if collection assignment is used.
A new symbol <code class="xref py py-attr docutils literal notranslate"><span class="pre">OP_BULK_REPLACE</span></code> may be used to determine
if this “append” event is the second part of a bulk replace:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="kn">from</span> <span class="nn">sqlalchemy.orm.attributes</span> <span class="k">import</span> <span class="n">OP_BULK_REPLACE</span>
<span class="nd">@event</span><span class="o">.</span><span class="n">listens_for</span><span class="p">(</span><span class="n">SomeObject</span><span class="o">.</span><span class="n">collection</span><span class="p">,</span> <span class="s2">"bulk_replace"</span><span class="p">)</span>
<span class="k">def</span> <span class="nf">process_collection</span><span class="p">(</span><span class="n">target</span><span class="p">,</span> <span class="n">values</span><span class="p">,</span> <span class="n">initiator</span><span class="p">):</span>
<span class="n">values</span><span class="p">[:]</span> <span class="o">=</span> <span class="p">[</span><span class="n">_make_value</span><span class="p">(</span><span class="n">value</span><span class="p">)</span> <span class="k">for</span> <span class="n">value</span> <span class="ow">in</span> <span class="n">values</span><span class="p">]</span>
<span class="nd">@event</span><span class="o">.</span><span class="n">listens_for</span><span class="p">(</span><span class="n">SomeObject</span><span class="o">.</span><span class="n">collection</span><span class="p">,</span> <span class="s2">"append"</span><span class="p">,</span> <span class="n">retval</span><span class="o">=</span><span class="kc">True</span><span class="p">)</span>
<span class="k">def</span> <span class="nf">process_collection</span><span class="p">(</span><span class="n">target</span><span class="p">,</span> <span class="n">value</span><span class="p">,</span> <span class="n">initiator</span><span class="p">):</span>
<span class="c1"># make sure bulk_replace didn't already do it</span>
<span class="k">if</span> <span class="n">initiator</span> <span class="ow">is</span> <span class="kc">None</span> <span class="ow">or</span> <span class="n">initiator</span><span class="o">.</span><span class="n">op</span> <span class="ow">is</span> <span class="ow">not</span> <span class="n">OP_BULK_REPLACE</span><span class="p">:</span>
<span class="k">return</span> <span class="n">_make_value</span><span class="p">(</span><span class="n">value</span><span class="p">)</span>
<span class="k">else</span><span class="p">:</span>
<span class="k">return</span> <span class="n">value</span></pre></div>
</div>
<p><a class="reference external" href="http://www.sqlalchemy.org/trac/ticket/3896">#3896</a></p>
</div>
<div class="section" id="new-modified-event-handler-for-sqlalchemy-ext-mutable">
<span id="change-3303"></span><h3>New “modified” event handler for sqlalchemy.ext.mutable<a class="headerlink" href="#new-modified-event-handler-for-sqlalchemy-ext-mutable" title="Permalink to this headline">¶</a></h3>
<p>A new event handler <a class="reference internal" href="../orm/events.html#sqlalchemy.orm.events.AttributeEvents.modified" title="sqlalchemy.orm.events.AttributeEvents.modified"><code class="xref py py-meth docutils literal notranslate"><span class="pre">AttributeEvents.modified()</span></code></a> is added, which is
triggered corresponding to calls to the <a class="reference internal" href="../orm/session_api.html#sqlalchemy.orm.attributes.flag_modified" title="sqlalchemy.orm.attributes.flag_modified"><code class="xref py py-func docutils literal notranslate"><span class="pre">attributes.flag_modified()</span></code></a>
method, which is normally called from the <a class="reference internal" href="../orm/extensions/mutable.html#module-sqlalchemy.ext.mutable" title="sqlalchemy.ext.mutable"><code class="xref py py-mod docutils literal notranslate"><span class="pre">sqlalchemy.ext.mutable</span></code></a>
extension:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></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.mutable</span> <span class="k">import</span> <span class="n">MutableDict</span>
<span class="kn">from</span> <span class="nn">sqlalchemy</span> <span class="k">import</span> <span class="n">event</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">MyDataClass</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">'my_data'</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">MutableDict</span><span class="o">.</span><span class="n">as_mutable</span><span class="p">(</span><span class="n">JSONEncodedDict</span><span class="p">))</span>
<span class="nd">@event</span><span class="o">.</span><span class="n">listens_for</span><span class="p">(</span><span class="n">MyDataClass</span><span class="o">.</span><span class="n">data</span><span class="p">,</span> <span class="s2">"modified"</span><span class="p">)</span>
<span class="k">def</span> <span class="nf">modified_json</span><span class="p">(</span><span class="n">instance</span><span class="p">):</span>
<span class="nb">print</span><span class="p">(</span><span class="s2">"json value modified:"</span><span class="p">,</span> <span class="n">instance</span><span class="o">.</span><span class="n">data</span><span class="p">)</span></pre></div>
</div>
<p>Above, the event handler will be triggered when an in-place change to the
<code class="docutils literal notranslate"><span class="pre">.data</span></code> dictionary occurs.</p>
<p><a class="reference external" href="http://www.sqlalchemy.org/trac/ticket/3303">#3303</a></p>
</div>
<div class="section" id="added-for-update-arguments-to-session-refresh">
<span id="change-3991"></span><h3>Added “for update” arguments to Session.refresh<a class="headerlink" href="#added-for-update-arguments-to-session-refresh" title="Permalink to this headline">¶</a></h3>
<p>Added new argument <a class="reference internal" href="../orm/session_api.html#sqlalchemy.orm.session.Session.refresh.params.with_for_update" title="sqlalchemy.orm.session.Session.refresh"><code class="xref py py-paramref docutils literal notranslate"><span class="pre">Session.refresh.with_for_update</span></code></a> to 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. When the <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 were deprecated in favor of <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>,
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 was never updated to reflect
the new option:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">session</span><span class="o">.</span><span class="n">refresh</span><span class="p">(</span><span class="n">some_object</span><span class="p">,</span> <span class="n">with_for_update</span><span class="o">=</span><span class="kc">True</span><span class="p">)</span></pre></div>
</div>
<p>The <a class="reference internal" href="../orm/session_api.html#sqlalchemy.orm.session.Session.refresh.params.with_for_update" title="sqlalchemy.orm.session.Session.refresh"><code class="xref py py-paramref docutils literal notranslate"><span class="pre">Session.refresh.with_for_update</span></code></a> argument accepts a dictionary
of options that will be passed as the same arguments which are sent to
<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>:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">session</span><span class="o">.</span><span class="n">refresh</span><span class="p">(</span><span class="n">some_objects</span><span class="p">,</span> <span class="n">with_for_update</span><span class="o">=</span><span class="p">{</span><span class="s2">"read"</span><span class="p">:</span> <span class="kc">True</span><span class="p">})</span></pre></div>
</div>
<p>The new parameter supersedes the <a class="reference internal" href="../orm/session_api.html#sqlalchemy.orm.session.Session.refresh.params.lockmode" title="sqlalchemy.orm.session.Session.refresh"><code class="xref py py-paramref docutils literal notranslate"><span class="pre">Session.refresh.lockmode</span></code></a>
parameter.</p>
<p><a class="reference external" href="http://www.sqlalchemy.org/trac/ticket/3991">#3991</a></p>
</div>
<div class="section" id="in-place-mutation-operators-work-for-mutableset-mutablelist">
<span id="change-3853"></span><h3>In-place mutation operators work for MutableSet, MutableList<a class="headerlink" href="#in-place-mutation-operators-work-for-mutableset-mutablelist" title="Permalink to this headline">¶</a></h3>
<p>Implemented the in-place mutation operators <code class="docutils literal notranslate"><span class="pre">__ior__</span></code>, <code class="docutils literal notranslate"><span class="pre">__iand__</span></code>,
<code class="docutils literal notranslate"><span class="pre">__ixor__</span></code> and <code class="docutils literal notranslate"><span class="pre">__isub__</span></code> for <a class="reference internal" href="../orm/extensions/mutable.html#sqlalchemy.ext.mutable.MutableSet" title="sqlalchemy.ext.mutable.MutableSet"><code class="xref py py-class docutils literal notranslate"><span class="pre">mutable.MutableSet</span></code></a> and <code class="docutils literal notranslate"><span class="pre">__iadd__</span></code>
for <a class="reference internal" href="../orm/extensions/mutable.html#sqlalchemy.ext.mutable.MutableList" title="sqlalchemy.ext.mutable.MutableList"><code class="xref py py-class docutils literal notranslate"><span class="pre">mutable.MutableList</span></code></a>. While these
methods would successfully update the collection previously, they would
not correctly fire off change events. The operators mutate the collection
as before but additionally emit the correct change event so that the change
becomes part of the next flush process:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">model</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">MyModel</span><span class="p">)</span><span class="o">.</span><span class="n">first</span><span class="p">()</span>
<span class="n">model</span><span class="o">.</span><span class="n">json_set</span> <span class="o">&=</span> <span class="p">{</span><span class="mi">1</span><span class="p">,</span> <span class="mi">3</span><span class="p">}</span></pre></div>
</div>
<p><a class="reference external" href="http://www.sqlalchemy.org/trac/ticket/3853">#3853</a></p>
</div>
<div class="section" id="associationproxy-any-has-contains-work-with-chained-association-proxies">
<span id="change-3769"></span><h3>AssociationProxy any(), has(), contains() work with chained association proxies<a class="headerlink" href="#associationproxy-any-has-contains-work-with-chained-association-proxies" title="Permalink to this headline">¶</a></h3>
<p>The <a class="reference internal" href="../orm/extensions/associationproxy.html#sqlalchemy.ext.associationproxy.AssociationProxy.any" title="sqlalchemy.ext.associationproxy.AssociationProxy.any"><code class="xref py py-meth docutils literal notranslate"><span class="pre">AssociationProxy.any()</span></code></a>, <a class="reference internal" href="../orm/extensions/associationproxy.html#sqlalchemy.ext.associationproxy.AssociationProxy.has" title="sqlalchemy.ext.associationproxy.AssociationProxy.has"><code class="xref py py-meth docutils literal notranslate"><span class="pre">AssociationProxy.has()</span></code></a>
and <a class="reference internal" href="../orm/extensions/associationproxy.html#sqlalchemy.ext.associationproxy.AssociationProxy.contains" title="sqlalchemy.ext.associationproxy.AssociationProxy.contains"><code class="xref py py-meth docutils literal notranslate"><span class="pre">AssociationProxy.contains()</span></code></a> comparison methods now support
linkage to an attribute that is
itself also an <a class="reference internal" href="../orm/extensions/associationproxy.html#sqlalchemy.ext.associationproxy.AssociationProxy" title="sqlalchemy.ext.associationproxy.AssociationProxy"><code class="xref py py-class docutils literal notranslate"><span class="pre">AssociationProxy</span></code></a>, recursively. Below, <code class="docutils literal notranslate"><span class="pre">A.b_values</span></code>
is an association proxy that links to <code class="docutils literal notranslate"><span class="pre">AtoB.bvalue</span></code>, which is
itself an association proxy onto <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="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_values</span> <span class="o">=</span> <span class="n">association_proxy</span><span class="p">(</span><span class="s2">"atob"</span><span class="p">,</span> <span class="s2">"b_value"</span><span class="p">)</span>
<span class="n">c_values</span> <span class="o">=</span> <span class="n">association_proxy</span><span class="p">(</span><span class="s2">"atob"</span><span class="p">,</span> <span class="s2">"c_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">a_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">'a.id'</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>
<span class="n">c</span> <span class="o">=</span> <span class="n">relationship</span><span class="p">(</span><span class="s2">"C"</span><span class="p">)</span>
<span class="k">class</span> <span class="nc">C</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">'c'</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">ForeignKey</span><span class="p">(</span><span class="s1">'b.id'</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>
<span class="k">class</span> <span class="nc">AtoB</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">'atob'</span>
<span class="n">a_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">'a.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_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">'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">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="s2">"atob"</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">backref</span><span class="o">=</span><span class="s2">"atob"</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="n">c_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">"c"</span><span class="p">)</span></pre></div>
</div>
<p>We can query on <code class="docutils literal notranslate"><span class="pre">A.b_values</span></code> using <a class="reference internal" href="../orm/extensions/associationproxy.html#sqlalchemy.ext.associationproxy.AssociationProxy.contains" title="sqlalchemy.ext.associationproxy.AssociationProxy.contains"><code class="xref py py-meth docutils literal notranslate"><span class="pre">AssociationProxy.contains()</span></code></a> to
query across the two proxies <code class="docutils literal notranslate"><span class="pre">A.b_values</span></code>, <code class="docutils literal notranslate"><span class="pre">AtoB.b_value</span></code>:</p>
<div class="highlight-pycon+sql notranslate"><div class="highlight"><pre><span></span><span class="gp">>>> </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_values</span><span class="o">.</span><span class="n">contains</span><span class="p">(</span><span class="s1">'hi'</span><span class="p">))</span><span class="o">.</span><span class="n">all</span><span class="p">()</span>
<div class='show_sql'>SELECT a.id AS a_id
FROM a
WHERE EXISTS (SELECT 1
FROM atob
WHERE a.id = atob.a_id AND (EXISTS (SELECT 1
FROM b
WHERE b.id = atob.b_id AND b.value = :value_1)))</div></pre></div>
</div>
<p>Similarly, we can query on <code class="docutils literal notranslate"><span class="pre">A.c_values</span></code> using <a class="reference internal" href="../orm/extensions/associationproxy.html#sqlalchemy.ext.associationproxy.AssociationProxy.any" title="sqlalchemy.ext.associationproxy.AssociationProxy.any"><code class="xref py py-meth docutils literal notranslate"><span class="pre">AssociationProxy.any()</span></code></a>
to query across the two proxies <code class="docutils literal notranslate"><span class="pre">A.c_values</span></code>, <code class="docutils literal notranslate"><span class="pre">AtoB.c_value</span></code>:</p>
<div class="highlight-pycon+sql notranslate"><div class="highlight"><pre><span></span><span class="gp">>>> </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">c_values</span><span class="o">.</span><span class="n">any</span><span class="p">(</span><span class="n">value</span><span class="o">=</span><span class="s1">'x'</span><span class="p">))</span><span class="o">.</span><span class="n">all</span><span class="p">()</span>
<div class='show_sql'>SELECT a.id AS a_id
FROM a
WHERE EXISTS (SELECT 1
FROM atob
WHERE a.id = atob.a_id AND (EXISTS (SELECT 1
FROM b
WHERE b.id = atob.b_id AND (EXISTS (SELECT 1
FROM c
WHERE b.id = c.b_id AND c.value = :value_1)))))</div></pre></div>
</div>
<p><a class="reference external" href="http://www.sqlalchemy.org/trac/ticket/3769">#3769</a></p>
</div>
<div class="section" id="change-4137">
<span id="identity-key-enhancements-to-support-sharding"></span><h3>Identity key enhancements to support sharding<a class="headerlink" href="#change-4137" title="Permalink to this headline">¶</a></h3>
<p>The identity key structure used by the ORM now contains an additional
member, so that two identical primary keys that originate from different
contexts can co-exist within the same identity map.</p>
<p>The example at <a class="reference internal" href="../orm/examples.html#examples-sharding"><span class="std std-ref">Horizontal Sharding</span></a> has been updated to illustrate this
behavior. The example shows a sharded class <code class="docutils literal notranslate"><span class="pre">WeatherLocation</span></code> that
refers to a dependent <code class="docutils literal notranslate"><span class="pre">WeatherReport</span></code> object, where the <code class="docutils literal notranslate"><span class="pre">WeatherReport</span></code>
class is mapped to a table that stores a simple integer primary key. Two
<code class="docutils literal notranslate"><span class="pre">WeatherReport</span></code> objects from different databases may have the same
primary key value. The example now illustrates that a new <code class="docutils literal notranslate"><span class="pre">identity_token</span></code>
field tracks this difference so that the two objects can co-exist in the
same identity map:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">tokyo</span> <span class="o">=</span> <span class="n">WeatherLocation</span><span class="p">(</span><span class="s1">'Asia'</span><span class="p">,</span> <span class="s1">'Tokyo'</span><span class="p">)</span>
<span class="n">newyork</span> <span class="o">=</span> <span class="n">WeatherLocation</span><span class="p">(</span><span class="s1">'North America'</span><span class="p">,</span> <span class="s1">'New York'</span><span class="p">)</span>
<span class="n">tokyo</span><span class="o">.</span><span class="n">reports</span><span class="o">.</span><span class="n">append</span><span class="p">(</span><span class="n">Report</span><span class="p">(</span><span class="mf">80.0</span><span class="p">))</span>
<span class="n">newyork</span><span class="o">.</span><span class="n">reports</span><span class="o">.</span><span class="n">append</span><span class="p">(</span><span class="n">Report</span><span class="p">(</span><span class="mi">75</span><span class="p">))</span>
<span class="n">sess</span> <span class="o">=</span> <span class="n">create_session</span><span class="p">()</span>
<span class="n">sess</span><span class="o">.</span><span class="n">add_all</span><span class="p">([</span><span class="n">tokyo</span><span class="p">,</span> <span class="n">newyork</span><span class="p">,</span> <span class="n">quito</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"># the Report class uses a simple integer primary key. So across two</span>
<span class="c1"># databases, a primary key will be repeated. The "identity_token" tracks</span>
<span class="c1"># in memory that these two identical primary keys are local to different</span>
<span class="c1"># databases.</span>
<span class="n">newyork_report</span> <span class="o">=</span> <span class="n">newyork</span><span class="o">.</span><span class="n">reports</span><span class="p">[</span><span class="mi">0</span><span class="p">]</span>
<span class="n">tokyo_report</span> <span class="o">=</span> <span class="n">tokyo</span><span class="o">.</span><span class="n">reports</span><span class="p">[</span><span class="mi">0</span><span class="p">]</span>
<span class="k">assert</span> <span class="n">inspect</span><span class="p">(</span><span class="n">newyork_report</span><span class="p">)</span><span class="o">.</span><span class="n">identity_key</span> <span class="o">==</span> <span class="p">(</span><span class="n">Report</span><span class="p">,</span> <span class="p">(</span><span class="mi">1</span><span class="p">,</span> <span class="p">),</span> <span class="s2">"north_america"</span><span class="p">)</span>
<span class="k">assert</span> <span class="n">inspect</span><span class="p">(</span><span class="n">tokyo_report</span><span class="p">)</span><span class="o">.</span><span class="n">identity_key</span> <span class="o">==</span> <span class="p">(</span><span class="n">Report</span><span class="p">,</span> <span class="p">(</span><span class="mi">1</span><span class="p">,</span> <span class="p">),</span> <span class="s2">"asia"</span><span class="p">)</span>
<span class="c1"># the token representing the originating shard is also available directly</span>
<span class="k">assert</span> <span class="n">inspect</span><span class="p">(</span><span class="n">newyork_report</span><span class="p">)</span><span class="o">.</span><span class="n">identity_token</span> <span class="o">==</span> <span class="s2">"north_america"</span>
<span class="k">assert</span> <span class="n">inspect</span><span class="p">(</span><span class="n">tokyo_report</span><span class="p">)</span><span class="o">.</span><span class="n">identity_token</span> <span class="o">==</span> <span class="s2">"asia"</span></pre></div>
</div>
<p><a class="reference external" href="http://www.sqlalchemy.org/trac/ticket/4137">#4137</a></p>
</div>
</div>
<div class="section" id="new-features-and-improvements-core">
<h2>New Features and Improvements - Core<a class="headerlink" href="#new-features-and-improvements-core" title="Permalink to this headline">¶</a></h2>
<div class="section" id="boolean-datatype-now-enforces-strict-true-false-none-values">
<span id="change-4102"></span><h3>Boolean datatype now enforces strict True/False/None values<a class="headerlink" href="#boolean-datatype-now-enforces-strict-true-false-none-values" title="Permalink to this headline">¶</a></h3>
<p>In version 1.1, the change described in <a class="reference internal" href="migration_11.html#change-3730"><span class="std std-ref">Non-native boolean integer values coerced to zero/one/None in all cases</span></a> produced an
unintended side effect of altering the way <a class="reference internal" href="../core/type_basics.html#sqlalchemy.types.Boolean" title="sqlalchemy.types.Boolean"><code class="xref py py-class docutils literal notranslate"><span class="pre">Boolean</span></code></a> behaves when
presented with a non-integer value, such as a string. In particular, the
string value <code class="docutils literal notranslate"><span class="pre">"0"</span></code>, which would previously result in the value <code class="docutils literal notranslate"><span class="pre">False</span></code>
being generated, would now produce <code class="docutils literal notranslate"><span class="pre">True</span></code>. Making matters worse, the change
in behavior was only for some backends and not others, meaning code that sends
string <code class="docutils literal notranslate"><span class="pre">"0"</span></code> values to <a class="reference internal" href="../core/type_basics.html#sqlalchemy.types.Boolean" title="sqlalchemy.types.Boolean"><code class="xref py py-class docutils literal notranslate"><span class="pre">Boolean</span></code></a> would break inconsistently across
backends.</p>
<p>The ultimate solution to this problem is that <strong>string values are not supported
with Boolean</strong>, so in 1.2 a hard <code class="docutils literal notranslate"><span class="pre">TypeError</span></code> is raised if a non-integer /
True/False/None value is passed. Additionally, only the integer values
0 and 1 are accepted.</p>
<p>To accommodate for applications that wish to have more liberal interpretation
of boolean values, the <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 be used. Below
illustrates a recipe that will allow for the “liberal” behavior of the pre-1.1
<a class="reference internal" href="../core/type_basics.html#sqlalchemy.types.Boolean" title="sqlalchemy.types.Boolean"><code class="xref py py-class docutils literal notranslate"><span class="pre">Boolean</span></code></a> datatype:</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">Boolean</span>
<span class="kn">from</span> <span class="nn">sqlalchemy</span> <span class="k">import</span> <span class="n">TypeDecorator</span>
<span class="k">class</span> <span class="nc">LiberalBoolean</span><span class="p">(</span><span class="n">TypeDecorator</span><span class="p">):</span>
<span class="n">impl</span> <span class="o">=</span> <span class="n">Boolean</span>
<span class="k">def</span> <span class="nf">process_bind_param</span><span class="p">(</span><span class="bp">self</span><span class="p">,</span> <span class="n">value</span><span class="p">,</span> <span class="n">dialect</span><span class="p">):</span>
<span class="k">if</span> <span class="n">value</span> <span class="ow">is</span> <span class="ow">not</span> <span class="kc">None</span><span class="p">:</span>
<span class="n">value</span> <span class="o">=</span> <span class="nb">bool</span><span class="p">(</span><span class="nb">int</span><span class="p">(</span><span class="n">value</span><span class="p">))</span>
<span class="k">return</span> <span class="n">value</span></pre></div>
</div>
<p><a class="reference external" href="http://www.sqlalchemy.org/trac/ticket/4102">#4102</a></p>
</div>
<div class="section" id="pessimistic-disconnection-detection-added-to-the-connection-pool">
<span id="change-3919"></span><h3>Pessimistic disconnection detection added to the connection pool<a class="headerlink" href="#pessimistic-disconnection-detection-added-to-the-connection-pool" title="Permalink to this headline">¶</a></h3>
<p>The connection pool documentation has long featured a recipe for using
the <a class="reference internal" href="../core/events.html#sqlalchemy.events.ConnectionEvents.engine_connect" title="sqlalchemy.events.ConnectionEvents.engine_connect"><code class="xref py py-meth docutils literal notranslate"><span class="pre">ConnectionEvents.engine_connect()</span></code></a> engine event to emit a simple
statement on a checked-out connection to test it for liveness. The
functionality of this recipe has now been added into the connection pool
itself, when used in conjunction with an appropriate dialect. Using
the new parameter <a class="reference internal" href="../core/engines.html#sqlalchemy.create_engine.params.pool_pre_ping" title="sqlalchemy.create_engine"><code class="xref py py-paramref docutils literal notranslate"><span class="pre">create_engine.pool_pre_ping</span></code></a>, each connection
checked out will be tested for freshness before being returned:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">engine</span> <span class="o">=</span> <span class="n">create_engine</span><span class="p">(</span><span class="s2">"mysql+pymysql://"</span><span class="p">,</span> <span class="n">pool_pre_ping</span><span class="o">=</span><span class="kc">True</span><span class="p">)</span></pre></div>
</div>
<p>While the “pre-ping” approach adds a small amount of latency to the connection
pool checkout, for a typical application that is transactionally-oriented
(which includes most ORM applications), this overhead is minimal, and
eliminates the problem of acquiring a stale connection that will raise
an error, requiring that the application either abandon or retry the operation.</p>
<p>The feature does <strong>not</strong> accommodate for connections dropped within
an ongoing transaction or SQL operation. If an application must recover
from these as well, it would need to employ its own operation retry logic
to anticipate these errors.</p>
<div class="admonition seealso">
<p class="admonition-title">See also</p>
<p><a class="reference internal" href="../core/pooling.html#pool-disconnects-pessimistic"><span class="std std-ref">Disconnect Handling - Pessimistic</span></a></p>
</div>
<p><a class="reference external" href="http://www.sqlalchemy.org/trac/ticket/3919">#3919</a></p>
</div>
<div class="section" id="the-in-not-in-operator-s-empty-collection-behavior-is-now-configurable-default-expression-simplified">
<span id="change-3907"></span><h3>The IN / NOT IN operator’s empty collection behavior is now configurable; default expression simplified<a class="headerlink" href="#the-in-not-in-operator-s-empty-collection-behavior-is-now-configurable-default-expression-simplified" title="Permalink to this headline">¶</a></h3>
<p>An expression such as <code class="docutils literal notranslate"><span class="pre">column.in_([])</span></code>, which is assumed to be false,
now produces the expression <code class="docutils literal notranslate"><span class="pre">1</span> <span class="pre">!=</span> <span class="pre">1</span></code>
by default, instead of <code class="docutils literal notranslate"><span class="pre">column</span> <span class="pre">!=</span> <span class="pre">column</span></code>. This will <strong>change the result</strong>
of a query that is comparing a SQL expression or column that evaluates to
NULL when compared to an empty set, producing a boolean value false or true
(for NOT IN) rather than NULL. The warning that would emit under
this condition is also removed. The old behavior is available using the
<a class="reference internal" href="../core/engines.html#sqlalchemy.create_engine.params.empty_in_strategy" title="sqlalchemy.create_engine"><code class="xref py py-paramref docutils literal notranslate"><span class="pre">create_engine.empty_in_strategy</span></code></a> parameter to
<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>.</p>
<p>In SQL, the IN and NOT IN operators do not support comparison to a
collection of values that is explicitly empty; meaning, this syntax is
illegal:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">mycolumn</span> <span class="n">IN</span> <span class="p">()</span></pre></div>
</div>
<p>To work around this, SQLAlchemy and other database libraries detect this
condition and render an alternative expression that evaluates to false, or
in the case of NOT IN, to true, based on the theory that “col IN ()” is always
false since nothing is in “the empty set”. Typically, in order to
produce a false/true constant that is portable across databases and works
in the context of the WHERE clause, a simple tautology such as <code class="docutils literal notranslate"><span class="pre">1</span> <span class="pre">!=</span> <span class="pre">1</span></code> is
used to evaluate to false and <code class="docutils literal notranslate"><span class="pre">1</span> <span class="pre">=</span> <span class="pre">1</span></code> to evaluate to true (a simple constant
“0” or “1” often does not work as the target of a WHERE clause).</p>
<p>SQLAlchemy in its early days began with this approach as well, but soon it
was theorized that the SQL expression <code class="docutils literal notranslate"><span class="pre">column</span> <span class="pre">IN</span> <span class="pre">()</span></code> would not evaluate to
false if the “column” were NULL; instead, the expression would produce NULL,
since “NULL” means “unknown”, and comparisons to NULL in SQL usually produce
NULL.</p>
<p>To simulate this result, SQLAlchemy changed from using <code class="docutils literal notranslate"><span class="pre">1</span> <span class="pre">!=</span> <span class="pre">1</span></code> to
instead use th expression <code class="docutils literal notranslate"><span class="pre">expr</span> <span class="pre">!=</span> <span class="pre">expr</span></code> for empty “IN” and <code class="docutils literal notranslate"><span class="pre">expr</span> <span class="pre">=</span> <span class="pre">expr</span></code>
for empty “NOT IN”; that is, instead of using a fixed value we use the
actual left-hand side of the expression. If the left-hand side of
the expression passed evaluates to NULL, then the comparison overall
also gets the NULL result instead of false or true.</p>
<p>Unfortunately, users eventually complained that this expression had a very
severe performance impact on some query planners. At that point, a warning
was added when an empty IN expression was encountered, favoring that SQLAlchemy
continues to be “correct” and urging users to avoid code that generates empty
IN predicates in general, since typically they can be safely omitted. However,
this is of course burdensome in the case of queries that are built up dynamically
from input variables, where an incoming set of values might be empty.</p>
<p>In recent months, the original assumptions of this decision have been
questioned. The notion that the expression “NULL IN ()” should return NULL was
only theoretical, and could not be tested since databases don’t support that
syntax. However, as it turns out, you can in fact ask a relational database
what value it would return for “NULL IN ()” by simulating the empty set as
follows:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">SELECT</span> <span class="n">NULL</span> <span class="n">IN</span> <span class="p">(</span><span class="n">SELECT</span> <span class="mi">1</span> <span class="n">WHERE</span> <span class="mi">1</span> <span class="o">!=</span> <span class="mi">1</span><span class="p">)</span></pre></div>
</div>
<p>With the above test, we see that the databases themselves can’t agree on
the answer. PostgreSQL, considered by most to be the most “correct” database,
returns False; because even though “NULL” represents “unknown”, the “empty set”
means nothing is present, including all unknown values. On the
other hand, MySQL and MariaDB return NULL for the above expression, defaulting
to the more common behavior of “all comparisons to NULL return NULL”.</p>
<p>SQLAlchemy’s SQL architecture is more sophisticated than it was when this
design decision was first made, so we can now allow either behavior to
be invoked at SQL string compilation time. Previously, the conversion to a
comparison expression were done at construction time, that is, the moment
the <a class="reference internal" href="../core/sqlelement.html#sqlalchemy.sql.operators.ColumnOperators.in_" title="sqlalchemy.sql.operators.ColumnOperators.in_"><code class="xref py py-meth docutils literal notranslate"><span class="pre">ColumnOperators.in_()</span></code></a> or <a class="reference internal" href="../core/sqlelement.html#sqlalchemy.sql.operators.ColumnOperators.notin_" title="sqlalchemy.sql.operators.ColumnOperators.notin_"><code class="xref py py-meth docutils literal notranslate"><span class="pre">ColumnOperators.notin_()</span></code></a> operators were invoked.
With the compilation-time behavior, the dialect itself can be instructed
to invoke either approach, that is, the “static” <code class="docutils literal notranslate"><span class="pre">1</span> <span class="pre">!=</span> <span class="pre">1</span></code> comparison or the
“dynamic” <code class="docutils literal notranslate"><span class="pre">expr</span> <span class="pre">!=</span> <span class="pre">expr</span></code> comparison. The default has been <strong>changed</strong>
to be the “static” comparison, since this agrees with the behavior that
PostgreSQL would have in any case and this is also what the vast majority
of users prefer. This will <strong>change the result</strong> of a query that is comparing
a null expression to the empty set, particularly one that is querying
for the negation <code class="docutils literal notranslate"><span class="pre">where(~null_expr.in_([]))</span></code>, since this now evaluates to true
and not NULL.</p>
<p>The behavior can now be controlled using the flag
<a class="reference internal" href="../core/engines.html#sqlalchemy.create_engine.params.empty_in_strategy" title="sqlalchemy.create_engine"><code class="xref py py-paramref docutils literal notranslate"><span class="pre">create_engine.empty_in_strategy</span></code></a>, which defaults to the
<code class="docutils literal notranslate"><span class="pre">"static"</span></code> setting, but may also be set to <code class="docutils literal notranslate"><span class="pre">"dynamic"</span></code> or
<code class="docutils literal notranslate"><span class="pre">"dynamic_warn"</span></code>, where the <code class="docutils literal notranslate"><span class="pre">"dynamic_warn"</span></code> setting is equivalent to the
previous behavior of emitting <code class="docutils literal notranslate"><span class="pre">expr</span> <span class="pre">!=</span> <span class="pre">expr</span></code> as well as a performance
warning. However, it is anticipated that most users will appreciate the
“static” default.</p>
<p><a class="reference external" href="http://www.sqlalchemy.org/trac/ticket/3907">#3907</a></p>
</div>
<div class="section" id="late-expanded-in-parameter-sets-allow-in-expressions-with-cached-statements">
<span id="change-3953"></span><h3>Late-expanded IN parameter sets allow IN expressions with cached statements<a class="headerlink" href="#late-expanded-in-parameter-sets-allow-in-expressions-with-cached-statements" title="Permalink to this headline">¶</a></h3>
<p>Added a new kind of <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> called “expanding”. This is
for use in <code class="docutils literal notranslate"><span class="pre">IN</span></code> expressions where the list of elements is rendered
into individual bound parameters at statement execution time, rather
than at statement compilation time. This allows both a single bound
parameter name to be linked to an IN expression of multiple elements,
as well as allows query caching to be used with IN expressions. The
new feature allows the related features of “select in” loading and
“polymorphic in” loading to make use of the baked query extension
to reduce call overhead:</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">where</span><span class="p">(</span>
<span class="n">table</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">in_</span><span class="p">(</span><span class="n">bindparam</span><span class="p">(</span><span class="s1">'foo'</span><span class="p">,</span> <span class="n">expanding</span><span class="o">=</span><span class="kc">True</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">stmt</span><span class="p">,</span> <span class="p">{</span><span class="s2">"foo"</span><span class="p">:</span> <span class="p">[</span><span class="mi">1</span><span class="p">,</span> <span class="mi">2</span><span class="p">,</span> <span class="mi">3</span><span class="p">]})</span></pre></div>
</div>
<p>The feature should be regarded as <strong>experimental</strong> within the 1.2 series.</p>
<p><a class="reference external" href="http://www.sqlalchemy.org/trac/ticket/3953">#3953</a></p>
</div>
<div class="section" id="flattened-operator-precedence-for-comparison-operators">
<span id="change-3999"></span><h3>Flattened operator precedence for comparison operators<a class="headerlink" href="#flattened-operator-precedence-for-comparison-operators" title="Permalink to this headline">¶</a></h3>
<p>The operator precedence for operators like IN, LIKE, equals, IS, MATCH, and
other comparison operators has been flattened into one level. This will
have the effect of more parenthesization being generated when comparison
operators are combined together, such as:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="p">(</span><span class="n">column</span><span class="p">(</span><span class="s1">'q'</span><span class="p">)</span> <span class="o">==</span> <span class="n">null</span><span class="p">())</span> <span class="o">!=</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="o">==</span> <span class="n">null</span><span class="p">())</span></pre></div>
</div>
<p>Will now generate <code class="docutils literal notranslate"><span class="pre">(q</span> <span class="pre">IS</span> <span class="pre">NULL)</span> <span class="pre">!=</span> <span class="pre">(y</span> <span class="pre">IS</span> <span class="pre">NULL)</span></code> rather than
<code class="docutils literal notranslate"><span class="pre">q</span> <span class="pre">IS</span> <span class="pre">NULL</span> <span class="pre">!=</span> <span class="pre">y</span> <span class="pre">IS</span> <span class="pre">NULL</span></code>.</p>
<p><a class="reference external" href="http://www.sqlalchemy.org/trac/ticket/3999">#3999</a></p>
</div>
<div class="section" id="support-for-sql-comments-on-table-column-includes-ddl-reflection">
<span id="change-1546"></span><h3>Support for SQL Comments on Table, Column, includes DDL, reflection<a class="headerlink" href="#support-for-sql-comments-on-table-column-includes-ddl-reflection" title="Permalink to this headline">¶</a></h3>
<p>The Core receives support for string comments associated with tables
and columns. These are specified via the <a class="reference internal" href="../core/metadata.html#sqlalchemy.schema.Table.params.comment" title="sqlalchemy.schema.Table"><code class="xref py py-paramref docutils literal notranslate"><span class="pre">Table.comment</span></code></a> and
<a class="reference internal" href="../core/metadata.html#sqlalchemy.schema.Column.params.comment" title="sqlalchemy.schema.Column"><code class="xref py py-paramref docutils literal notranslate"><span class="pre">Column.comment</span></code></a> arguments:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">Table</span><span class="p">(</span>
<span class="s1">'my_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="s1">'q'</span><span class="p">,</span> <span class="n">Integer</span><span class="p">,</span> <span class="n">comment</span><span class="o">=</span><span class="s2">"the Q value"</span><span class="p">),</span>
<span class="n">comment</span><span class="o">=</span><span class="s2">"my Q table"</span>
<span class="p">)</span></pre></div>
</div>
<p>Above, DDL will be rendered appropriately upon table create to associate
the above comments with the table/ column within the schema. When
the above table is autoloaded or inspected with <a class="reference internal" href="../core/reflection.html#sqlalchemy.engine.reflection.Inspector.get_columns" title="sqlalchemy.engine.reflection.Inspector.get_columns"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Inspector.get_columns()</span></code></a>,
the comments are included. The table comment is also available independently
using the <a class="reference internal" href="../core/reflection.html#sqlalchemy.engine.reflection.Inspector.get_table_comment" title="sqlalchemy.engine.reflection.Inspector.get_table_comment"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Inspector.get_table_comment()</span></code></a> method.</p>
<p>Current backend support includes MySQL, PostgreSQL, and Oracle.</p>
<p><a class="reference external" href="http://www.sqlalchemy.org/trac/ticket/1546">#1546</a></p>
</div>
<div class="section" id="multiple-table-criteria-support-for-delete">
<span id="change-959"></span><h3>Multiple-table criteria support for DELETE<a class="headerlink" href="#multiple-table-criteria-support-for-delete" title="Permalink to this headline">¶</a></h3>
<p>The <a class="reference internal" href="../core/dml.html#sqlalchemy.sql.expression.Delete" title="sqlalchemy.sql.expression.Delete"><code class="xref py py-class docutils literal notranslate"><span class="pre">Delete</span></code></a> construct now supports multiple-table criteria,
implemented for those backends which support it, currently these are
PostgreSQL, MySQL and Microsoft SQL Server (support is also added to the
currently non-working Sybase dialect). The feature works in the same
was as that of multiple-table criteria for UPDATE, first introduced in
the 0.7 and 0.8 series.</p>
<p>Given a statement as:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">stmt</span> <span class="o">=</span> <span class="n">users</span><span class="o">.</span><span class="n">delete</span><span class="p">()</span><span class="o">.</span>\
<span class="n">where</span><span class="p">(</span><span class="n">users</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">id</span> <span class="o">==</span> <span class="n">addresses</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="n">where</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">email_address</span><span class="o">.</span><span class="n">startswith</span><span class="p">(</span><span class="s1">'ed%'</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">stmt</span><span class="p">)</span></pre></div>
</div>
<p>The resulting SQL from the above statement on a PostgreSQL backend
would render as:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">DELETE</span> <span class="n">FROM</span> <span class="n">users</span> <span class="n">USING</span> <span class="n">addresses</span>
<span class="n">WHERE</span> <span class="n">users</span><span class="o">.</span><span class="n">id</span> <span class="o">=</span> <span class="n">addresses</span><span class="o">.</span><span class="n">id</span>
<span class="n">AND</span> <span class="p">(</span><span class="n">addresses</span><span class="o">.</span><span class="n">email_address</span> <span class="n">LIKE</span> <span class="o">%</span><span class="p">(</span><span class="n">email_address_1</span><span class="p">)</span><span class="n">s</span> <span class="o">||</span> <span class="s1">'</span><span class="si">%%</span><span class="s1">'</span><span class="p">)</span></pre></div>
</div>
<div class="admonition seealso">
<p class="admonition-title">See also</p>
<p><a class="reference internal" href="../core/tutorial.html#multi-table-deletes"><span class="std std-ref">Multiple Table Deletes</span></a></p>
</div>
<p><a class="reference external" href="http://www.sqlalchemy.org/trac/ticket/959">#959</a></p>
</div>
<div class="section" id="new-autoescape-option-for-startswith-endswith">
<span id="change-2694"></span><h3>New “autoescape” option for startswith(), endswith()<a class="headerlink" href="#new-autoescape-option-for-startswith-endswith" title="Permalink to this headline">¶</a></h3>
<p>The “autoescape” parameter is added to <a class="reference internal" href="../core/sqlelement.html#sqlalchemy.sql.operators.ColumnOperators.startswith" title="sqlalchemy.sql.operators.ColumnOperators.startswith"><code class="xref py py-meth docutils literal notranslate"><span class="pre">ColumnOperators.startswith()</span></code></a>,
<a class="reference internal" href="../core/sqlelement.html#sqlalchemy.sql.operators.ColumnOperators.endswith" title="sqlalchemy.sql.operators.ColumnOperators.endswith"><code class="xref py py-meth docutils literal notranslate"><span class="pre">ColumnOperators.endswith()</span></code></a>, <a class="reference internal" href="../core/sqlelement.html#sqlalchemy.sql.operators.ColumnOperators.contains" title="sqlalchemy.sql.operators.ColumnOperators.contains"><code class="xref py py-meth docutils literal notranslate"><span class="pre">ColumnOperators.contains()</span></code></a>.
This parameter when set to <code class="docutils literal notranslate"><span class="pre">True</span></code> will automatically escape all occurrences
of <code class="docutils literal notranslate"><span class="pre">%</span></code>, <code class="docutils literal notranslate"><span class="pre">_</span></code> with an escape character, which defaults to a forwards slash <code class="docutils literal notranslate"><span class="pre">/</span></code>;
occurrences of the escape character itself are also escaped. The forwards slash
is used to avoid conflicts with settings like PostgreSQL’s
<code class="docutils literal notranslate"><span class="pre">standard_confirming_strings</span></code>, whose default value changed as of PostgreSQL
9.1, and MySQL’s <code class="docutils literal notranslate"><span class="pre">NO_BACKSLASH_ESCAPES</span></code> settings. The existing “escape” parameter
can now be used to change the autoescape character, if desired.</p>
<div class="admonition note">
<p class="admonition-title">Note</p>
<p>This feature has been changed as of 1.2.0 from its initial
implementation in 1.2.0b2 such that autoescape is now passed as a boolean
value, rather than a specific character to use as the escape character.</p>
</div>
<p>An expression such as:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="gp">>>> </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">startswith</span><span class="p">(</span><span class="s1">'total</span><span class="si">%s</span><span class="s1">core'</span><span class="p">,</span> <span class="n">autoescape</span><span class="o">=</span><span class="kc">True</span><span class="p">)</span></pre></div>
</div>
<p>Renders as:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">x</span> <span class="n">LIKE</span> <span class="p">:</span><span class="n">x_1</span> <span class="o">||</span> <span class="s1">'%'</span> <span class="n">ESCAPE</span> <span class="s1">'/'</span></pre></div>
</div>
<p>Where the value of the parameter “x_1” is <code class="docutils literal notranslate"><span class="pre">'total/%score'</span></code>.</p>
<p>Similarly, an expression that has backslashes:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="gp">>>> </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">startswith</span><span class="p">(</span><span class="s1">'total/score'</span><span class="p">,</span> <span class="n">autoescape</span><span class="o">=</span><span class="kc">True</span><span class="p">)</span></pre></div>
</div>
<p>Will render the same way, with the value of the parameter “x_1” as
<code class="docutils literal notranslate"><span class="pre">'total//score'</span></code>.</p>
<p><a class="reference external" href="http://www.sqlalchemy.org/trac/ticket/2694">#2694</a></p>
</div>
<div class="section" id="stronger-typing-added-to-float-datatypes">
<span id="change-floats-12"></span><h3>Stronger typing added to “float” datatypes<a class="headerlink" href="#stronger-typing-added-to-float-datatypes" title="Permalink to this headline">¶</a></h3>
<p>A series of changes allow for use of 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> datatype to more
strongly link itself to Python floating point values, instead of the more
generic <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>. The changes are mostly related to ensuring
that Python floating point values are not erroneously coerced to
<code class="docutils literal notranslate"><span class="pre">Decimal()</span></code>, and are coerced to <code class="docutils literal notranslate"><span class="pre">float</span></code> if needed, on the result side,
if the application is working with plain floats.</p>
<ul>
<li><p>A plain Python “float” value passed to a SQL expression will now be
pulled into a literal parameter with the type <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>; previously,
the type was <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>, with the default “asdecimal=True” flag, which
meant the result type would coerce to <code class="docutils literal notranslate"><span class="pre">Decimal()</span></code>. In particular,
this would emit a confusing warning on SQLite:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">float_value</span> <span class="o">=</span> <span class="n">connection</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">literal</span><span class="p">(</span><span class="mf">4.56</span><span class="p">)])</span> <span class="c1"># the "BindParameter" will now be</span>
<span class="c1"># Float, not Numeric(asdecimal=True)</span>
<span class="p">)</span></pre></div>
</div>
</li>
<li><p>Math operations between <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>, <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>, and
<a class="reference internal" href="../core/type_basics.html#sqlalchemy.types.Integer" title="sqlalchemy.types.Integer"><code class="xref py py-class docutils literal notranslate"><span class="pre">Integer</span></code></a> will now preserve 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> or <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 in the resulting expression’s type, including the <code class="docutils literal notranslate"><span class="pre">asdecimal</span></code> flag
as well as if the type should be <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>:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="c1"># asdecimal flag is maintained</span>
<span class="n">expr</span> <span class="o">=</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="o">*</span> <span class="n">column</span><span class="p">(</span><span class="s1">'b'</span><span class="p">,</span> <span class="n">Numeric</span><span class="p">(</span><span class="n">asdecimal</span><span class="o">=</span><span class="kc">False</span><span class="p">))</span>
<span class="k">assert</span> <span class="n">expr</span><span class="o">.</span><span class="n">type</span><span class="o">.</span><span class="n">asdecimal</span> <span class="o">==</span> <span class="kc">False</span>
<span class="c1"># Float subclass of Numeric is maintained</span>
<span class="n">expr</span> <span class="o">=</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="o">*</span> <span class="n">column</span><span class="p">(</span><span class="s1">'b'</span><span class="p">,</span> <span class="n">Float</span><span class="p">())</span>
<span class="k">assert</span> <span class="nb">isinstance</span><span class="p">(</span><span class="n">expr</span><span class="o">.</span><span class="n">type</span><span class="p">,</span> <span class="n">Float</span><span class="p">)</span></pre></div>
</div>
</li>
<li><p>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> datatype will apply the <code class="docutils literal notranslate"><span class="pre">float()</span></code> processor to
result values unconditionally if the DBAPI is known to support native
<code class="docutils literal notranslate"><span class="pre">Decimal()</span></code> mode. Some backends do not always guarantee that a floating
point number comes back as plain float and not precision numeric such
as MySQL.</p></li>
</ul>
<p><a class="reference external" href="http://www.sqlalchemy.org/trac/ticket/4017">#4017</a></p>
<p><a class="reference external" href="http://www.sqlalchemy.org/trac/ticket/4018">#4018</a></p>
<p><a class="reference external" href="http://www.sqlalchemy.org/trac/ticket/4020">#4020</a></p>
</div>
<div class="section" id="support-for-grouping-sets-cube-rollup">
<h3>Support for GROUPING SETS, CUBE, ROLLUP<a class="headerlink" href="#support-for-grouping-sets-cube-rollup" title="Permalink to this headline">¶</a></h3>
<p>All three of GROUPING SETS, CUBE, ROLLUP are available via the
<a class="reference internal" href="../core/sqlelement.html#sqlalchemy.sql.expression.Over.func" title="sqlalchemy.sql.expression.Over.func"><code class="xref py py-attr docutils literal notranslate"><span class="pre">func</span></code></a> namespace. In the case of CUBE and ROLLUP, these functions
already work in previous versions, however for GROUPING SETS, a placeholder
is added to the compiler to allow for the space. All three functions
are named in the documentation now:</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">table</span><span class="p">,</span> <span class="n">column</span><span class="p">,</span> <span class="n">func</span><span class="p">,</span> <span class="n">tuple_</span>
<span class="gp">>>> </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="gp">... </span> <span class="n">column</span><span class="p">(</span><span class="s1">'value'</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="gp">... </span> <span class="n">column</span><span class="p">(</span><span class="s1">'y'</span><span class="p">),</span> <span class="n">column</span><span class="p">(</span><span class="s1">'z'</span><span class="p">),</span> <span class="n">column</span><span class="p">(</span><span class="s1">'q'</span><span class="p">))</span>
<span class="gp">>>> </span><span class="n">stmt</span> <span class="o">=</span> <span class="n">select</span><span class="p">([</span><span class="n">func</span><span class="o">.</span><span class="n">sum</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">value</span><span class="p">)])</span><span class="o">.</span><span class="n">group_by</span><span class="p">(</span>
<span class="gp">... </span> <span class="n">func</span><span class="o">.</span><span class="n">grouping_sets</span><span class="p">(</span>
<span class="gp">... </span> <span class="n">tuple_</span><span class="p">(</span><span class="n">t</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">x</span><span class="p">,</span> <span class="n">t</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">y</span><span class="p">),</span>
<span class="gp">... </span> <span class="n">tuple_</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">z</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">q</span><span class="p">),</span>
<span class="gp">... </span> <span class="p">)</span>
<span class="gp">... </span><span class="p">)</span>
<span class="gp">>>> </span><span class="nb">print</span><span class="p">(</span><span class="n">stmt</span><span class="p">)</span>
<span class="go">SELECT sum(t.value) AS sum_1</span>
<span class="go">FROM t GROUP BY GROUPING SETS((t.x, t.y), (t.z, t.q))</span></pre></div>
</div>
<p><a class="reference external" href="http://www.sqlalchemy.org/trac/ticket/3429">#3429</a></p>
</div>
<div class="section" id="parameter-helper-for-multi-valued-insert-with-contextual-default-generator">
<span id="change-4075"></span><h3>Parameter helper for multi-valued INSERT with contextual default generator<a class="headerlink" href="#parameter-helper-for-multi-valued-insert-with-contextual-default-generator" title="Permalink to this headline">¶</a></h3>
<p>A default generation function, e.g. that described at
<a class="reference internal" href="../core/defaults.html#context-default-functions"><span class="std std-ref">Context-Sensitive Default Functions</span></a>, can look at the current parameters relevant
to the statement via the <a class="reference internal" href="../core/internals.html#sqlalchemy.engine.default.DefaultExecutionContext.current_parameters" title="sqlalchemy.engine.default.DefaultExecutionContext.current_parameters"><code class="xref py py-attr docutils literal notranslate"><span class="pre">DefaultExecutionContext.current_parameters</span></code></a>
attribute. However, in the case of a <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> construct that specifies
multiple VALUES clauses via the <a class="reference internal" href="../core/dml.html#sqlalchemy.sql.expression.Insert.values" title="sqlalchemy.sql.expression.Insert.values"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Insert.values()</span></code></a> method, the user-defined
function is called multiple times, once for each parameter set, however there
was no way to know which subset of keys in
<a class="reference internal" href="../core/internals.html#sqlalchemy.engine.default.DefaultExecutionContext.current_parameters" title="sqlalchemy.engine.default.DefaultExecutionContext.current_parameters"><code class="xref py py-attr docutils literal notranslate"><span class="pre">DefaultExecutionContext.current_parameters</span></code></a> apply to that column. A
new function <a class="reference internal" href="../core/internals.html#sqlalchemy.engine.default.DefaultExecutionContext.get_current_parameters" title="sqlalchemy.engine.default.DefaultExecutionContext.get_current_parameters"><code class="xref py py-meth docutils literal notranslate"><span class="pre">DefaultExecutionContext.get_current_parameters()</span></code></a> is added,
which includes a keyword argument
<a class="reference internal" href="../core/internals.html#sqlalchemy.engine.default.DefaultExecutionContext.get_current_parameters.params.isolate_multiinsert_groups" title="sqlalchemy.engine.default.DefaultExecutionContext.get_current_parameters"><code class="xref py py-paramref docutils literal notranslate"><span class="pre">DefaultExecutionContext.get_current_parameters.isolate_multiinsert_groups</span></code></a>
defaulting to <code class="docutils literal notranslate"><span class="pre">True</span></code>, which performs the extra work of delivering a sub-dictionary of
<a class="reference internal" href="../core/internals.html#sqlalchemy.engine.default.DefaultExecutionContext.current_parameters" title="sqlalchemy.engine.default.DefaultExecutionContext.current_parameters"><code class="xref py py-attr docutils literal notranslate"><span class="pre">DefaultExecutionContext.current_parameters</span></code></a> which has the names
localized to the current VALUES clause being processed:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="k">def</span> <span class="nf">mydefault</span><span class="p">(</span><span class="n">context</span><span class="p">):</span>
<span class="k">return</span> <span class="n">context</span><span class="o">.</span><span class="n">get_current_parameters</span><span class="p">()[</span><span class="s1">'counter'</span><span class="p">]</span> <span class="o">+</span> <span class="mi">12</span>
<span class="n">mytable</span> <span class="o">=</span> <span class="n">Table</span><span class="p">(</span><span class="s1">'mytable'</span><span class="p">,</span> <span class="n">meta</span><span class="p">,</span>
<span class="n">Column</span><span class="p">(</span><span class="s1">'counter'</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="s1">'counter_plus_twelve'</span><span class="p">,</span>
<span class="n">Integer</span><span class="p">,</span> <span class="n">default</span><span class="o">=</span><span class="n">mydefault</span><span class="p">,</span> <span class="n">onupdate</span><span class="o">=</span><span class="n">mydefault</span><span class="p">)</span>
<span class="p">)</span>
<span class="n">stmt</span> <span class="o">=</span> <span class="n">mytable</span><span class="o">.</span><span class="n">insert</span><span class="p">()</span><span class="o">.</span><span class="n">values</span><span class="p">(</span>
<span class="p">[{</span><span class="s2">"counter"</span><span class="p">:</span> <span class="mi">5</span><span class="p">},</span> <span class="p">{</span><span class="s2">"counter"</span><span class="p">:</span> <span class="mi">18</span><span class="p">},</span> <span class="p">{</span><span class="s2">"counter"</span><span class="p">:</span> <span class="mi">20</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">stmt</span><span class="p">)</span></pre></div>
</div>
<p><a class="reference external" href="http://www.sqlalchemy.org/trac/ticket/4075">#4075</a></p>
</div>
</div>
<div class="section" id="key-behavioral-changes-orm">
<h2>Key Behavioral Changes - ORM<a class="headerlink" href="#key-behavioral-changes-orm" title="Permalink to this headline">¶</a></h2>
<div class="section" id="the-after-rollback-session-event-now-emits-before-the-expiration-of-objects">
<span id="change-3934"></span><h3>The after_rollback() Session event now emits before the expiration of objects<a class="headerlink" href="#the-after-rollback-session-event-now-emits-before-the-expiration-of-objects" title="Permalink to this headline">¶</a></h3>
<p>The <a class="reference internal" href="../orm/events.html#sqlalchemy.orm.events.SessionEvents.after_rollback" title="sqlalchemy.orm.events.SessionEvents.after_rollback"><code class="xref py py-meth docutils literal notranslate"><span class="pre">SessionEvents.after_rollback()</span></code></a> event now has access to the attribute
state of objects before their state has been expired (e.g. the “snapshot
removal”). This allows the event to be consistent with the behavior
of the <a class="reference internal" href="../orm/events.html#sqlalchemy.orm.events.SessionEvents.after_commit" title="sqlalchemy.orm.events.SessionEvents.after_commit"><code class="xref py py-meth docutils literal notranslate"><span class="pre">SessionEvents.after_commit()</span></code></a> event which also emits before the
“snapshot” has been removed:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">sess</span> <span class="o">=</span> <span class="n">Session</span><span class="p">()</span>
<span class="n">user</span> <span class="o">=</span> <span class="n">sess</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="n">User</span><span class="p">)</span><span class="o">.</span><span class="n">filter_by</span><span class="p">(</span><span class="n">name</span><span class="o">=</span><span class="s1">'x'</span><span class="p">)</span><span class="o">.</span><span class="n">first</span><span class="p">()</span>
<span class="nd">@event</span><span class="o">.</span><span class="n">listens_for</span><span class="p">(</span><span class="n">sess</span><span class="p">,</span> <span class="s2">"after_rollback"</span><span class="p">)</span>
<span class="k">def</span> <span class="nf">after_rollback</span><span class="p">(</span><span class="n">session</span><span class="p">):</span>
<span class="c1"># 'user.name' is now present, assuming it was already</span>
<span class="c1"># loaded. previously this would raise upon trying</span>
<span class="c1"># to emit a lazy load.</span>
<span class="nb">print</span><span class="p">(</span><span class="s2">"user name: </span><span class="si">%s</span><span class="s2">"</span> <span class="o">%</span> <span class="n">user</span><span class="o">.</span><span class="n">name</span><span class="p">)</span>
<span class="nd">@event</span><span class="o">.</span><span class="n">listens_for</span><span class="p">(</span><span class="n">sess</span><span class="p">,</span> <span class="s2">"after_commit"</span><span class="p">)</span>
<span class="k">def</span> <span class="nf">after_commit</span><span class="p">(</span><span class="n">session</span><span class="p">):</span>
<span class="c1"># 'user.name' is present, assuming it was already</span>
<span class="c1"># loaded. this is the existing behavior.</span>
<span class="nb">print</span><span class="p">(</span><span class="s2">"user name: </span><span class="si">%s</span><span class="s2">"</span> <span class="o">%</span> <span class="n">user</span><span class="o">.</span><span class="n">name</span><span class="p">)</span>
<span class="k">if</span> <span class="n">should_rollback</span><span class="p">:</span>
<span class="n">sess</span><span class="o">.</span><span class="n">rollback</span><span class="p">()</span>
<span class="k">else</span><span class="p">:</span>
<span class="n">sess</span><span class="o">.</span><span class="n">commit</span><span class="p">()</span></pre></div>
</div>
<p>Note that the <a class="reference internal" href="../orm/session_api.html#sqlalchemy.orm.session.Session" title="sqlalchemy.orm.session.Session"><code class="xref py py-class docutils literal notranslate"><span class="pre">Session</span></code></a> will still disallow SQL from being emitted
within this event; meaning that unloaded attributes will still not be
able to load within the scope of the event.</p>
<p><a class="reference external" href="http://www.sqlalchemy.org/trac/ticket/3934">#3934</a></p>
</div>
<div class="section" id="fixed-issue-involving-single-table-inheritance-with-select-from">
<span id="change-3891"></span><h3>Fixed issue involving single-table inheritance with <code class="docutils literal notranslate"><span class="pre">select_from()</span></code><a class="headerlink" href="#fixed-issue-involving-single-table-inheritance-with-select-from" 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 now honors the single-table inheritance
column discriminator when generating SQL; previously, only the expressions
in the query column list would be taken into account.</p>
<p>Supposing <code class="docutils literal notranslate"><span class="pre">Manager</span></code> is a subclass of <code class="docutils literal notranslate"><span class="pre">Employee</span></code>. A query like the following:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">sess</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="n">Manager</span><span class="o">.</span><span class="n">id</span><span class="p">)</span></pre></div>
</div>
<p>Would generate SQL as:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">SELECT</span> <span class="n">employee</span><span class="o">.</span><span class="n">id</span> <span class="n">FROM</span> <span class="n">employee</span> <span class="n">WHERE</span> <span class="n">employee</span><span class="o">.</span><span class="n">type</span> <span class="n">IN</span> <span class="p">(</span><span class="s1">'manager'</span><span class="p">)</span></pre></div>
</div>
<p>However, if <code class="docutils literal notranslate"><span class="pre">Manager</span></code> were only specified by <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>
and not in the columns list, the discriminator would not be added:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">sess</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="n">func</span><span class="o">.</span><span class="n">count</span><span class="p">(</span><span class="mi">1</span><span class="p">))</span><span class="o">.</span><span class="n">select_from</span><span class="p">(</span><span class="n">Manager</span><span class="p">)</span></pre></div>
</div>
<p>would generate:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">SELECT</span> <span class="n">count</span><span class="p">(</span><span class="mi">1</span><span class="p">)</span> <span class="n">FROM</span> <span class="n">employee</span></pre></div>
</div>
<p>With the fix, <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> now works correctly and we get:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">SELECT</span> <span class="n">count</span><span class="p">(</span><span class="mi">1</span><span class="p">)</span> <span class="n">FROM</span> <span class="n">employee</span> <span class="n">WHERE</span> <span class="n">employee</span><span class="o">.</span><span class="n">type</span> <span class="n">IN</span> <span class="p">(</span><span class="s1">'manager'</span><span class="p">)</span></pre></div>
</div>
<p>Applications that may have been working around this by supplying the
WHERE clause manually may need to be adjusted.</p>
<p><a class="reference external" href="http://www.sqlalchemy.org/trac/ticket/3891">#3891</a></p>
</div>
<div class="section" id="previous-collection-is-no-longer-mutated-upon-replacement">
<span id="change-3913"></span><h3>Previous collection is no longer mutated upon replacement<a class="headerlink" href="#previous-collection-is-no-longer-mutated-upon-replacement" title="Permalink to this headline">¶</a></h3>
<p>The ORM emits events whenever the members of a mapped collection change.
In the case of assigning a collection to an attribute that would replace
the previous collection, a side effect of this was that the collection
being replaced would also be mutated, which is misleading and unnecessary:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="gp">>>> </span><span class="n">a1</span><span class="p">,</span> <span class="n">a2</span><span class="p">,</span> <span class="n">a3</span> <span class="o">=</span> <span class="n">Address</span><span class="p">(</span><span class="s1">'a1'</span><span class="p">),</span> <span class="n">Address</span><span class="p">(</span><span class="s1">'a2'</span><span class="p">),</span> <span class="n">Address</span><span class="p">(</span><span class="s1">'a3'</span><span class="p">)</span>
<span class="gp">>>> </span><span class="n">user</span><span class="o">.</span><span class="n">addresses</span> <span class="o">=</span> <span class="p">[</span><span class="n">a1</span><span class="p">,</span> <span class="n">a2</span><span class="p">]</span>
<span class="gp">>>> </span><span class="n">previous_collection</span> <span class="o">=</span> <span class="n">user</span><span class="o">.</span><span class="n">addresses</span>
<span class="go"># replace the collection with a new one</span>
<span class="gp">>>> </span><span class="n">user</span><span class="o">.</span><span class="n">addresses</span> <span class="o">=</span> <span class="p">[</span><span class="n">a2</span><span class="p">,</span> <span class="n">a3</span><span class="p">]</span>
<span class="gp">>>> </span><span class="n">previous_collection</span>
<span class="go">[Address('a1'), Address('a2')]</span></pre></div>
</div>
<p>Above, prior to the change, the <code class="docutils literal notranslate"><span class="pre">previous_collection</span></code> would have had the
“a1” member removed, corresponding to the member that’s no longer in the
new collection.</p>
<p><a class="reference external" href="http://www.sqlalchemy.org/trac/ticket/3913">#3913</a></p>
</div>
<div class="section" id="a-validates-method-receives-all-values-on-bulk-collection-set-before-comparison">
<span id="change-3896-validates"></span><h3>A @validates method receives all values on bulk-collection set before comparison<a class="headerlink" href="#a-validates-method-receives-all-values-on-bulk-collection-set-before-comparison" title="Permalink to this headline">¶</a></h3>
<p>A method that uses <code class="docutils literal notranslate"><span class="pre">@validates</span></code> will now receive all members of a collection
during a “bulk set” operation, before comparison is applied against the
existing collection.</p>
<p>Given a mapping as:</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">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="nd">@validates</span><span class="p">(</span><span class="s1">'bs'</span><span class="p">)</span>
<span class="k">def</span> <span class="nf">convert_dict_to_b</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">value</span><span class="p">):</span>
<span class="k">return</span> <span class="n">B</span><span class="p">(</span><span class="n">data</span><span class="o">=</span><span class="n">value</span><span class="p">[</span><span class="s1">'data'</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">ForeignKey</span><span class="p">(</span><span class="s1">'a.id'</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></pre></div>
</div>
<p>Above, we could use the validator as follows, to convert from an incoming
dictionary to an instance of <code class="docutils literal notranslate"><span class="pre">B</span></code> upon collection append:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></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="s2">"data"</span><span class="p">:</span> <span class="s2">"b1"</span><span class="p">})</span></pre></div>
</div>
<p>However, a collection assignment would fail, since the ORM would assume
incoming objects are already instances of <code class="docutils literal notranslate"><span class="pre">B</span></code> as it attempts to compare them
to the existing members of the collection, before doing collection appends
which actually invoke the validator. This would make it impossible for bulk
set operations to accommodate non-ORM objects like dictionaries that needed
up-front modification:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></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="p">[{</span><span class="s2">"data"</span><span class="p">:</span> <span class="s2">"b1"</span><span class="p">}]</span></pre></div>
</div>
<p>The new logic uses the new <a class="reference internal" href="../orm/events.html#sqlalchemy.orm.events.AttributeEvents.bulk_replace" title="sqlalchemy.orm.events.AttributeEvents.bulk_replace"><code class="xref py py-meth docutils literal notranslate"><span class="pre">AttributeEvents.bulk_replace()</span></code></a> event to ensure
that all values are sent to the <code class="docutils literal notranslate"><span class="pre">@validates</span></code> function up front.</p>
<p>As part of this change, this means that validators will now receive
<strong>all</strong> members of a collection upon bulk set, not just the members that
are new. Supposing a simple validator such as:</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="c1"># ...</span>
<span class="nd">@validates</span><span class="p">(</span><span class="s1">'bs'</span><span class="p">)</span>
<span class="k">def</span> <span class="nf">validate_b</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">value</span><span class="p">):</span>
<span class="k">assert</span> <span class="n">value</span><span class="o">.</span><span class="n">data</span> <span class="ow">is</span> <span class="ow">not</span> <span class="kc">None</span>
<span class="k">return</span> <span class="n">value</span></pre></div>
</div>
<p>Above, if we began with a collection as:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">a1</span> <span class="o">=</span> <span class="n">A</span><span class="p">()</span>
<span class="n">b1</span><span class="p">,</span> <span class="n">b2</span> <span class="o">=</span> <span class="n">B</span><span class="p">(</span><span class="n">data</span><span class="o">=</span><span class="s2">"one"</span><span class="p">),</span> <span class="n">B</span><span class="p">(</span><span class="n">data</span><span class="o">=</span><span class="s2">"two"</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="p">[</span><span class="n">b1</span><span class="p">,</span> <span class="n">b2</span><span class="p">]</span></pre></div>
</div>
<p>And then, replaced the collection with one that overlaps the first:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">b3</span> <span class="o">=</span> <span class="n">B</span><span class="p">(</span><span class="n">data</span><span class="o">=</span><span class="s2">"three"</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="p">[</span><span class="n">b2</span><span class="p">,</span> <span class="n">b3</span><span class="p">]</span></pre></div>
</div>
<p>Previously, the second assignment would trigger the <code class="docutils literal notranslate"><span class="pre">A.validate_b</span></code>
method only once, for the <code class="docutils literal notranslate"><span class="pre">b3</span></code> object. The <code class="docutils literal notranslate"><span class="pre">b2</span></code> object would be seen
as being already present in the collection and not validated. With the new
behavior, both <code class="docutils literal notranslate"><span class="pre">b2</span></code> and <code class="docutils literal notranslate"><span class="pre">b3</span></code> are passed to <code class="docutils literal notranslate"><span class="pre">A.validate_b</span></code> before passing
onto the collection. It is thus important that validation methods employ
idempotent behavior to suit such a case.</p>
<div class="admonition seealso">
<p class="admonition-title">See also</p>
<p><a class="reference internal" href="#change-3896-event"><span class="std std-ref">New bulk_replace event</span></a></p>
</div>
<p><a class="reference external" href="http://www.sqlalchemy.org/trac/ticket/3896">#3896</a></p>
</div>
<div class="section" id="use-flag-dirty-to-mark-an-object-as-dirty-without-any-attribute-changing">
<span id="change-3753"></span><h3>Use flag_dirty() to mark an object as “dirty” without any attribute changing<a class="headerlink" href="#use-flag-dirty-to-mark-an-object-as-dirty-without-any-attribute-changing" title="Permalink to this headline">¶</a></h3>
<p>An exception is now raised if the <a class="reference internal" href="../orm/session_api.html#sqlalchemy.orm.attributes.flag_modified" title="sqlalchemy.orm.attributes.flag_modified"><code class="xref py py-func docutils literal notranslate"><span class="pre">attributes.flag_modified()</span></code></a> function
is used to mark an attribute as modified that isn’t actually loaded:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></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">'adf'</span><span class="p">)</span>
<span class="n">s</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">s</span><span class="o">.</span><span class="n">flush</span><span class="p">()</span>
<span class="c1"># expire, similarly as though we said s.commit()</span>
<span class="n">s</span><span class="o">.</span><span class="n">expire</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="c1"># will raise InvalidRequestError</span>
<span class="n">attributes</span><span class="o">.</span><span class="n">flag_modified</span><span class="p">(</span><span class="n">a1</span><span class="p">,</span> <span class="s1">'data'</span><span class="p">)</span></pre></div>
</div>
<p>This because the flush process will most likely fail in any case if the
attribute remains un-present by the time flush occurs. To mark an object
as “modified” without referring to any attribute specifically, so that it
is considered within the flush process for the purpose of custom event handlers
such as <a class="reference internal" href="../orm/events.html#sqlalchemy.orm.events.SessionEvents.before_flush" title="sqlalchemy.orm.events.SessionEvents.before_flush"><code class="xref py py-meth docutils literal notranslate"><span class="pre">SessionEvents.before_flush()</span></code></a>, use the new
<a class="reference internal" href="../orm/session_api.html#sqlalchemy.orm.attributes.flag_dirty" title="sqlalchemy.orm.attributes.flag_dirty"><code class="xref py py-func docutils literal notranslate"><span class="pre">attributes.flag_dirty()</span></code></a> function:</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">attributes</span>
<span class="n">attributes</span><span class="o">.</span><span class="n">flag_dirty</span><span class="p">(</span><span class="n">a1</span><span class="p">)</span></pre></div>
</div>
<p><a class="reference external" href="http://www.sqlalchemy.org/trac/ticket/3753">#3753</a></p>
</div>
<div class="section" id="scope-keyword-removed-from-scoped-session">
<span id="change-3796"></span><h3>“scope” keyword removed from scoped_session<a class="headerlink" href="#scope-keyword-removed-from-scoped-session" title="Permalink to this headline">¶</a></h3>
<p>A very old and undocumented keyword argument <code class="docutils literal notranslate"><span class="pre">scope</span></code> has been removed:</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">scoped_session</span>
<span class="n">Session</span> <span class="o">=</span> <span class="n">scoped_session</span><span class="p">(</span><span class="n">sessionmaker</span><span class="p">())</span>
<span class="n">session</span> <span class="o">=</span> <span class="n">Session</span><span class="p">(</span><span class="n">scope</span><span class="o">=</span><span class="kc">None</span><span class="p">)</span></pre></div>
</div>
<p>The purpose of this keyword was an attempt to allow for variable
“scopes”, where <code class="docutils literal notranslate"><span class="pre">None</span></code> indicated “no scope” and would therefore return
a new <a class="reference internal" href="../orm/session_api.html#sqlalchemy.orm.session.Session" title="sqlalchemy.orm.session.Session"><code class="xref py py-class docutils literal notranslate"><span class="pre">Session</span></code></a>. The keyword has never been documented and will
now raise <code class="docutils literal notranslate"><span class="pre">TypeError</span></code> if encountered. It is not anticipated that this
keyword is in use, however if users report issues related to this during
beta testing, it can be restored with a deprecation.</p>
<p><a class="reference external" href="http://www.sqlalchemy.org/trac/ticket/3796">#3796</a></p>
</div>
<div class="section" id="refinements-to-post-update-in-conjunction-with-onupdate">
<span id="change-3471"></span><h3>Refinements to post_update in conjunction with onupdate<a class="headerlink" href="#refinements-to-post-update-in-conjunction-with-onupdate" title="Permalink to this headline">¶</a></h3>
<p>A relationship that uses the <a class="reference internal" href="../orm/relationship_api.html#sqlalchemy.orm.relationship.params.post_update" title="sqlalchemy.orm.relationship"><code class="xref py py-paramref docutils literal notranslate"><span class="pre">relationship.post_update</span></code></a> feature
will now interact better with a column that has an <a class="reference internal" href="../core/metadata.html#sqlalchemy.schema.Column.params.onupdate" title="sqlalchemy.schema.Column"><code class="xref py py-paramref docutils literal notranslate"><span class="pre">Column.onupdate</span></code></a>
value set. If an object is inserted with an explicit value for the column,
it is re-stated during the UPDATE so that the “onupdate” rule does not
overwrite it:</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">favorite_b_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">'b.id'</span><span class="p">,</span> <span class="n">name</span><span class="o">=</span><span class="s2">"favorite_b_fk"</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">primaryjoin</span><span class="o">=</span><span class="s2">"A.id == B.a_id"</span><span class="p">)</span>
<span class="n">favorite_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">primaryjoin</span><span class="o">=</span><span class="s2">"A.favorite_b_id == B.id"</span><span class="p">,</span> <span class="n">post_update</span><span class="o">=</span><span class="kc">True</span><span class="p">)</span>
<span class="n">updated</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">onupdate</span><span class="o">=</span><span class="n">my_onupdate_function</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">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="s2">"a_fk"</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">b1</span> <span class="o">=</span> <span class="n">B</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">b1</span><span class="p">)</span>
<span class="n">a1</span><span class="o">.</span><span class="n">favorite_b</span> <span class="o">=</span> <span class="n">b1</span>
<span class="n">a1</span><span class="o">.</span><span class="n">updated</span> <span class="o">=</span> <span class="mi">5</span>
<span class="n">s</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">s</span><span class="o">.</span><span class="n">flush</span><span class="p">()</span></pre></div>
</div>
<p>Above, the previous behavior would be that an UPDATE would emit after the
INSERT, thus triggering the “onupdate” and overwriting the value
“5”. The SQL now looks like:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span>INSERT INTO a (favorite_b_id, updated) VALUES (?, ?)
(None, 5)
INSERT INTO b (a_id) VALUES (?)
(1,)
UPDATE a SET favorite_b_id=?, updated=? WHERE a.id = ?
(1, 5, 1)</pre></div>
</div>
<p>Additionally, if the value of “updated” is <em>not</em> set, then we correctly
get back the newly generated value on <code class="docutils literal notranslate"><span class="pre">a1.updated</span></code>; previously, the logic
that refreshes or expires the attribute to allow the generated value
to be present would not fire off for a post-update. The
<a class="reference internal" href="../orm/events.html#sqlalchemy.orm.events.InstanceEvents.refresh_flush" title="sqlalchemy.orm.events.InstanceEvents.refresh_flush"><code class="xref py py-meth docutils literal notranslate"><span class="pre">InstanceEvents.refresh_flush()</span></code></a> event is also emitted when a refresh
within flush occurs in this case.</p>
<p><a class="reference external" href="http://www.sqlalchemy.org/trac/ticket/3471">#3471</a></p>
<p><a class="reference external" href="http://www.sqlalchemy.org/trac/ticket/3472">#3472</a></p>
</div>
<div class="section" id="post-update-integrates-with-orm-versioning">
<span id="change-3496"></span><h3>post_update integrates with ORM versioning<a class="headerlink" href="#post-update-integrates-with-orm-versioning" title="Permalink to this headline">¶</a></h3>
<p>The post_update feature, documented at <a class="reference internal" href="../orm/relationship_persistence.html#post-update"><span class="std std-ref">Rows that point to themselves / Mutually Dependent Rows</span></a>, involves that an
UPDATE statement is emitted in response to changes to a particular
relationship-bound foreign key, in addition to the INSERT/UPDATE/DELETE that
would normally be emitted for the target row. This UPDATE statement
now participates in the versioning feature, documented at
<a class="reference internal" href="../orm/versioning.html#mapper-version-counter"><span class="std std-ref">Configuring a Version Counter</span></a>.</p>
<p>Given a mapping:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="k">class</span> <span class="nc">Node</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">'node'</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">version_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">default</span><span class="o">=</span><span class="mi">0</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">'node.id'</span><span class="p">))</span>
<span class="n">favorite_node_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">'node.id'</span><span class="p">))</span>
<span class="n">nodes</span> <span class="o">=</span> <span class="n">relationship</span><span class="p">(</span><span class="s2">"Node"</span><span class="p">,</span> <span class="n">primaryjoin</span><span class="o">=</span><span class="n">remote</span><span class="p">(</span><span class="n">parent_id</span><span class="p">)</span> <span class="o">==</span> <span class="nb">id</span><span class="p">)</span>
<span class="n">favorite_node</span> <span class="o">=</span> <span class="n">relationship</span><span class="p">(</span>
<span class="s2">"Node"</span><span class="p">,</span> <span class="n">primaryjoin</span><span class="o">=</span><span class="n">favorite_node_id</span> <span class="o">==</span> <span class="n">remote</span><span class="p">(</span><span class="nb">id</span><span class="p">),</span>
<span class="n">post_update</span><span class="o">=</span><span class="kc">True</span>
<span class="p">)</span>
<span class="n">__mapper_args__</span> <span class="o">=</span> <span class="p">{</span>
<span class="s1">'version_id_col'</span><span class="p">:</span> <span class="n">version_id</span>
<span class="p">}</span></pre></div>
</div>
<p>An UPDATE of a node that associates another node as “favorite” will
now increment the version counter as well as match the current version:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">node</span> <span class="o">=</span> <span class="n">Node</span><span class="p">()</span>
<span class="n">session</span><span class="o">.</span><span class="n">add</span><span class="p">(</span><span class="n">node</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"># node is now version #1</span>
<span class="n">node</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">Node</span><span class="p">)</span><span class="o">.</span><span class="n">get</span><span class="p">(</span><span class="n">node</span><span class="o">.</span><span class="n">id</span><span class="p">)</span>
<span class="n">node</span><span class="o">.</span><span class="n">favorite_node</span> <span class="o">=</span> <span class="n">Node</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"># node is now version #2</span></pre></div>
</div>
<p>Note that this means an object that receives an UPDATE in response to
other attributes changing, and a second UPDATE due to a post_update
relationship change, will now receive
<strong>two version counter updates for one flush</strong>. However, if the object
is subject to an INSERT within the current flush, the version counter
<strong>will not</strong> be incremented an additional time, unless a server-side
versioning scheme is in place.</p>
<p>The reason post_update emits an UPDATE even for an UPDATE is now discussed at
<a class="reference internal" href="../faq/sessions.html#faq-post-update-update"><span class="std std-ref">Why does post_update emit UPDATE in addition to the first UPDATE?</span></a>.</p>
<div class="admonition seealso">
<p class="admonition-title">See also</p>
<p><a class="reference internal" href="../orm/relationship_persistence.html#post-update"><span class="std std-ref">Rows that point to themselves / Mutually Dependent Rows</span></a></p>
<p><a class="reference internal" href="../faq/sessions.html#faq-post-update-update"><span class="std std-ref">Why does post_update emit UPDATE in addition to the first UPDATE?</span></a></p>
</div>
<p><a class="reference external" href="http://www.sqlalchemy.org/trac/ticket/3496">#3496</a></p>
</div>
</div>
<div class="section" id="key-behavioral-changes-core">
<h2>Key Behavioral Changes - Core<a class="headerlink" href="#key-behavioral-changes-core" title="Permalink to this headline">¶</a></h2>
<div class="section" id="the-typing-behavior-of-custom-operators-has-been-made-consistent">
<span id="change-4063"></span><h3>The typing behavior of custom operators has been made consistent<a class="headerlink" href="#the-typing-behavior-of-custom-operators-has-been-made-consistent" title="Permalink to this headline">¶</a></h3>
<p>User defined operators can be made on the fly using the
<a class="reference internal" href="../core/sqlelement.html#sqlalchemy.sql.operators.Operators.op" title="sqlalchemy.sql.operators.Operators.op"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Operators.op()</span></code></a> function. Previously, the typing behavior of
an expression against such an operator was inconsistent and also not
controllable.</p>
<p>Whereas in 1.1, an expression such as the following would produce
a result with no return type (assume <code class="docutils literal notranslate"><span class="pre">-%></span></code> is some special operator
supported by the database):</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="gp">>>> </span><span class="n">column</span><span class="p">(</span><span class="s1">'x'</span><span class="p">,</span> <span class="n">types</span><span class="o">.</span><span class="n">DateTime</span><span class="p">)</span><span class="o">.</span><span class="n">op</span><span class="p">(</span><span class="s1">'-%>'</span><span class="p">)(</span><span class="kc">None</span><span class="p">)</span><span class="o">.</span><span class="n">type</span>
<span class="go">NullType()</span></pre></div>
</div>
<p>Other types would use the default behavior of using the left-hand type
as the return type:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="gp">>>> </span><span class="n">column</span><span class="p">(</span><span class="s1">'x'</span><span class="p">,</span> <span class="n">types</span><span class="o">.</span><span class="n">String</span><span class="p">(</span><span class="mi">50</span><span class="p">))</span><span class="o">.</span><span class="n">op</span><span class="p">(</span><span class="s1">'-%>'</span><span class="p">)(</span><span class="kc">None</span><span class="p">)</span><span class="o">.</span><span class="n">type</span>
<span class="go">String(length=50)</span></pre></div>
</div>
<p>These behaviors were mostly by accident, so the behavior has been made
consistent with the second form, that is the default return type is the
same as the left-hand expression:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="gp">>>> </span><span class="n">column</span><span class="p">(</span><span class="s1">'x'</span><span class="p">,</span> <span class="n">types</span><span class="o">.</span><span class="n">DateTime</span><span class="p">)</span><span class="o">.</span><span class="n">op</span><span class="p">(</span><span class="s1">'-%>'</span><span class="p">)(</span><span class="kc">None</span><span class="p">)</span><span class="o">.</span><span class="n">type</span>
<span class="go">DateTime()</span></pre></div>
</div>
<p>As most user-defined operators tend to be “comparison” operators, often
one of the many special operators defined by PostgreSQL, the
<a class="reference internal" href="../core/sqlelement.html#sqlalchemy.sql.operators.Operators.op.params.is_comparison" title="sqlalchemy.sql.operators.Operators.op"><code class="xref py py-paramref docutils literal notranslate"><span class="pre">Operators.op.is_comparison</span></code></a> flag has been repaired to follow
its documented behavior of allowing the return type to be <a class="reference internal" href="../core/type_basics.html#sqlalchemy.types.Boolean" title="sqlalchemy.types.Boolean"><code class="xref py py-class docutils literal notranslate"><span class="pre">Boolean</span></code></a>
in all cases, including for <a class="reference internal" href="../core/type_basics.html#sqlalchemy.types.ARRAY" title="sqlalchemy.types.ARRAY"><code class="xref py py-class docutils literal notranslate"><span class="pre">ARRAY</span></code></a> and <a class="reference internal" href="../core/type_basics.html#sqlalchemy.types.JSON" title="sqlalchemy.types.JSON"><code class="xref py py-class docutils literal notranslate"><span class="pre">JSON</span></code></a>:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="gp">>>> </span><span class="n">column</span><span class="p">(</span><span class="s1">'x'</span><span class="p">,</span> <span class="n">types</span><span class="o">.</span><span class="n">String</span><span class="p">(</span><span class="mi">50</span><span class="p">))</span><span class="o">.</span><span class="n">op</span><span class="p">(</span><span class="s1">'-%>'</span><span class="p">,</span> <span class="n">is_comparison</span><span class="o">=</span><span class="kc">True</span><span class="p">)(</span><span class="kc">None</span><span class="p">)</span><span class="o">.</span><span class="n">type</span>
<span class="go">Boolean()</span>
<span class="gp">>>> </span><span class="n">column</span><span class="p">(</span><span class="s1">'x'</span><span class="p">,</span> <span class="n">types</span><span class="o">.</span><span class="n">ARRAY</span><span class="p">(</span><span class="n">types</span><span class="o">.</span><span class="n">Integer</span><span class="p">))</span><span class="o">.</span><span class="n">op</span><span class="p">(</span><span class="s1">'-%>'</span><span class="p">,</span> <span class="n">is_comparison</span><span class="o">=</span><span class="kc">True</span><span class="p">)(</span><span class="kc">None</span><span class="p">)</span><span class="o">.</span><span class="n">type</span>
<span class="go">Boolean()</span>
<span class="gp">>>> </span><span class="n">column</span><span class="p">(</span><span class="s1">'x'</span><span class="p">,</span> <span class="n">types</span><span class="o">.</span><span class="n">JSON</span><span class="p">())</span><span class="o">.</span><span class="n">op</span><span class="p">(</span><span class="s1">'-%>'</span><span class="p">,</span> <span class="n">is_comparison</span><span class="o">=</span><span class="kc">True</span><span class="p">)(</span><span class="kc">None</span><span class="p">)</span><span class="o">.</span><span class="n">type</span>
<span class="go">Boolean()</span></pre></div>
</div>
<p>To assist with boolean comparison operators, a new shorthand method
<a class="reference internal" href="../core/sqlelement.html#sqlalchemy.sql.operators.Operators.bool_op" title="sqlalchemy.sql.operators.Operators.bool_op"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Operators.bool_op()</span></code></a> has been added. This method should be preferred
for on-the-fly boolean operators:</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">column</span><span class="p">(</span><span class="s1">'x'</span><span class="p">,</span> <span class="n">types</span><span class="o">.</span><span class="n">Integer</span><span class="p">)</span><span class="o">.</span><span class="n">bool_op</span><span class="p">(</span><span class="s1">'-%>'</span><span class="p">)(</span><span class="mi">5</span><span class="p">))</span>
<span class="go">x -%> :x_1</span></pre></div>
</div>
</div>
<div class="section" id="percent-signs-in-literal-column-now-conditionally-escaped">
<span id="change-3740"></span><h3>Percent signs in literal_column() now conditionally escaped<a class="headerlink" href="#percent-signs-in-literal-column-now-conditionally-escaped" title="Permalink to this headline">¶</a></h3>
<p>The <a class="reference internal" href="../core/sqlelement.html#sqlalchemy.sql.expression.literal_column" title="sqlalchemy.sql.expression.literal_column"><code class="xref py py-obj docutils literal notranslate"><span class="pre">literal_column</span></code></a> construct now escapes percent sign characters
conditionally, based on whether or not the DBAPI in use makes use of a
percent-sign-sensitive paramstyle or not (e.g. ‘format’ or ‘pyformat’).</p>
<p>Previously, it was not possible to produce a <a class="reference internal" href="../core/sqlelement.html#sqlalchemy.sql.expression.literal_column" title="sqlalchemy.sql.expression.literal_column"><code class="xref py py-obj docutils literal notranslate"><span class="pre">literal_column</span></code></a>
construct that stated a single percent sign:</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">literal_column</span>
<span class="gp">>>> </span><span class="nb">print</span><span class="p">(</span><span class="n">literal_column</span><span class="p">(</span><span class="s1">'some</span><span class="si">%s</span><span class="s1">ymbol'</span><span class="p">))</span>
<span class="go">some%%symbol</span></pre></div>
</div>
<p>The percent sign is now unaffected for dialects that are not set to
use the ‘format’ or ‘pyformat’ paramstyles; dialects such most MySQL
dialects which do state one of these paramstyles will continue to escape
as is appropriate:</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">literal_column</span>
<span class="gp">>>> </span><span class="nb">print</span><span class="p">(</span><span class="n">literal_column</span><span class="p">(</span><span class="s1">'some</span><span class="si">%s</span><span class="s1">ymbol'</span><span class="p">))</span>
<span class="go">some%symbol</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="gp">>>> </span><span class="nb">print</span><span class="p">(</span><span class="n">literal_column</span><span class="p">(</span><span class="s1">'some</span><span class="si">%s</span><span class="s1">ymbol'</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">some%%symbol</span></pre></div>
</div>
<p>As part of this change, the doubling that has been present when using
operators like <a class="reference internal" href="../core/sqlelement.html#sqlalchemy.sql.operators.ColumnOperators.contains" title="sqlalchemy.sql.operators.ColumnOperators.contains"><code class="xref py py-meth docutils literal notranslate"><span class="pre">ColumnOperators.contains()</span></code></a>,
<a class="reference internal" href="../core/sqlelement.html#sqlalchemy.sql.operators.ColumnOperators.startswith" title="sqlalchemy.sql.operators.ColumnOperators.startswith"><code class="xref py py-meth docutils literal notranslate"><span class="pre">ColumnOperators.startswith()</span></code></a> and <a class="reference internal" href="../core/sqlelement.html#sqlalchemy.sql.operators.ColumnOperators.endswith" title="sqlalchemy.sql.operators.ColumnOperators.endswith"><code class="xref py py-meth docutils literal notranslate"><span class="pre">ColumnOperators.endswith()</span></code></a>
is also refined to only occur when appropriate.</p>
<p><a class="reference external" href="http://www.sqlalchemy.org/trac/ticket/3740">#3740</a></p>
</div>
<div class="section" id="the-column-level-collate-keyword-now-quotes-the-collation-name">
<span id="change-3785"></span><h3>The column-level COLLATE keyword now quotes the collation name<a class="headerlink" href="#the-column-level-collate-keyword-now-quotes-the-collation-name" title="Permalink to this headline">¶</a></h3>
<p>A bug in the <a class="reference internal" href="../core/sqlelement.html#sqlalchemy.sql.expression.collate" title="sqlalchemy.sql.expression.collate"><code class="xref py py-func docutils literal notranslate"><span class="pre">expression.collate()</span></code></a> and <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>
functions, used to supply ad-hoc column collations at the statement level,
is fixed, where a case sensitive name would not be quoted:</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">mytable</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="n">mytable</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">y</span><span class="p">])</span><span class="o">.</span>\
<span class="n">order_by</span><span class="p">(</span><span class="n">mytable</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">somecolumn</span><span class="o">.</span><span class="n">collate</span><span class="p">(</span><span class="s2">"fr_FR"</span><span class="p">))</span></pre></div>
</div>
<p>now renders:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">SELECT</span> <span class="n">mytable</span><span class="o">.</span><span class="n">x</span><span class="p">,</span> <span class="n">mytable</span><span class="o">.</span><span class="n">y</span><span class="p">,</span>
<span class="n">FROM</span> <span class="n">mytable</span> <span class="n">ORDER</span> <span class="n">BY</span> <span class="n">mytable</span><span class="o">.</span><span class="n">somecolumn</span> <span class="n">COLLATE</span> <span class="s2">"fr_FR"</span></pre></div>
</div>
<p>Previously, the case sensitive name <cite>“fr_FR”</cite> would not be quoted. Currently,
manual quoting of the “fr_FR” name is <strong>not</strong> detected, so applications that
are manually quoting the identifier should be adjusted. Note that this change
does not impact the use of collations at the type level (e.g. specified
on the datatype like <a class="reference internal" href="../core/type_basics.html#sqlalchemy.types.String" title="sqlalchemy.types.String"><code class="xref py py-class docutils literal notranslate"><span class="pre">String</span></code></a> at the table level), where quoting
is already applied.</p>
<p><a class="reference external" href="http://www.sqlalchemy.org/trac/ticket/3785">#3785</a></p>
</div>
</div>
<div class="section" id="dialect-improvements-and-changes-postgresql">
<h2>Dialect Improvements and Changes - PostgreSQL<a class="headerlink" href="#dialect-improvements-and-changes-postgresql" title="Permalink to this headline">¶</a></h2>
<div class="section" id="support-for-batch-mode-fast-execution-helpers">
<span id="change-4109"></span><h3>Support for Batch Mode / Fast Execution Helpers<a class="headerlink" href="#support-for-batch-mode-fast-execution-helpers" title="Permalink to this headline">¶</a></h3>
<p>The psycopg2 <code class="docutils literal notranslate"><span class="pre">cursor.executemany()</span></code> method has been identified as performing
poorly, particularly with INSERT statements. To alleviate this, psycopg2
has added <a class="reference external" href="http://initd.org/psycopg/docs/extras.html#fast-execution-helpers">Fast Execution Helpers</a>
which rework statements into fewer server round trips by sending multiple
DML statements in batch. SQLAlchemy 1.2 now includes support for these
helpers to be used transparently whenever the <a class="reference internal" href="../core/connections.html#sqlalchemy.engine.Engine" title="sqlalchemy.engine.Engine"><code class="xref py py-class docutils literal notranslate"><span class="pre">Engine</span></code></a> makes use
of <code class="docutils literal notranslate"><span class="pre">cursor.executemany()</span></code> to invoke a statement against multiple parameter
sets. The feature is off by default and can be enabled using the
<code class="docutils literal notranslate"><span class="pre">use_batch_mode</span></code> argument on <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>:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">engine</span> <span class="o">=</span> <span class="n">create_engine</span><span class="p">(</span>
<span class="s2">"postgresql+psycopg2://scott:tiger@host/dbname"</span><span class="p">,</span>
<span class="n">use_batch_mode</span><span class="o">=</span><span class="kc">True</span><span class="p">)</span></pre></div>
</div>
<p>The feature is considered to be experimental for the moment but may become
on by default in a future release.</p>
<div class="admonition seealso">
<p class="admonition-title">See also</p>
<p><a class="reference internal" href="../dialects/postgresql.html#psycopg2-batch-mode"><span class="std std-ref">Psycopg2 Batch Mode (Fast Execution)</span></a></p>
</div>
<p><a class="reference external" href="http://www.sqlalchemy.org/trac/ticket/4109">#4109</a></p>
</div>
<div class="section" id="support-for-fields-specification-in-interval-including-full-reflection">
<span id="change-3959"></span><h3>Support for fields specification in INTERVAL, including full reflection<a class="headerlink" href="#support-for-fields-specification-in-interval-including-full-reflection" title="Permalink to this headline">¶</a></h3>
<p>The “fields” specifier in PostgreSQL’s INTERVAL datatype allows specification
of which fields of the interval to store, including such values as “YEAR”,
“MONTH”, “YEAR TO MONTH”, etc. The <a class="reference internal" href="../dialects/postgresql.html#sqlalchemy.dialects.postgresql.INTERVAL" title="sqlalchemy.dialects.postgresql.INTERVAL"><code class="xref py py-class docutils literal notranslate"><span class="pre">postgresql.INTERVAL</span></code></a> datatype
now allows these values to be specified:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="kn">from</span> <span class="nn">sqlalchemy.dialects.postgresql</span> <span class="k">import</span> <span class="n">INTERVAL</span>
<span class="n">Table</span><span class="p">(</span>
<span class="s1">'my_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="s2">"some_interval"</span><span class="p">,</span> <span class="n">INTERVAL</span><span class="p">(</span><span class="n">fields</span><span class="o">=</span><span class="s2">"DAY TO SECOND"</span><span class="p">))</span>
<span class="p">)</span></pre></div>
</div>
<p>Additionally, all INTERVAL datatypes can now be reflected independently
of the “fields” specifier present; the “fields” parameter in the datatype
itself will also be present:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="gp">>>> </span><span class="n">inspect</span><span class="p">(</span><span class="n">engine</span><span class="p">)</span><span class="o">.</span><span class="n">get_columns</span><span class="p">(</span><span class="s2">"my_table"</span><span class="p">)</span>
<span class="go">[{'comment': None,</span>
<span class="go"> 'name': u'some_interval', 'nullable': True,</span>
<span class="go"> 'default': None, 'autoincrement': False,</span>
<span class="go"> 'type': INTERVAL(fields=u'day to second')}]</span></pre></div>
</div>
<p><a class="reference external" href="http://www.sqlalchemy.org/trac/ticket/3959">#3959</a></p>
</div>
</div>
<div class="section" id="dialect-improvements-and-changes-mysql">
<h2>Dialect Improvements and Changes - MySQL<a class="headerlink" href="#dialect-improvements-and-changes-mysql" title="Permalink to this headline">¶</a></h2>
<div class="section" id="support-for-insert-on-duplicate-key-update">
<span id="change-4009"></span><h3>Support for INSERT..ON DUPLICATE KEY UPDATE<a class="headerlink" href="#support-for-insert-on-duplicate-key-update" title="Permalink to this headline">¶</a></h3>
<p>The <code class="docutils literal notranslate"><span class="pre">ON</span> <span class="pre">DUPLICATE</span> <span class="pre">KEY</span> <span class="pre">UPDATE</span></code> clause of <code class="docutils literal notranslate"><span class="pre">INSERT</span></code> supported by MySQL
is now supported using a MySQL-specific version of the
<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> object, via <a class="reference internal" href="../dialects/mysql.html#sqlalchemy.dialects.mysql.dml.insert" title="sqlalchemy.dialects.mysql.dml.insert"><code class="xref py py-func docutils literal notranslate"><span class="pre">sqlalchemy.dialects.mysql.dml.insert()</span></code></a>.
This <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> subclass adds a new method
<a class="reference internal" href="../dialects/mysql.html#sqlalchemy.dialects.mysql.dml.Insert.on_duplicate_key_update" title="sqlalchemy.dialects.mysql.dml.Insert.on_duplicate_key_update"><code class="xref py py-meth docutils literal notranslate"><span class="pre">on_duplicate_key_update()</span></code></a> that implements MySQL’s syntax:</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">insert</span>
<span class="n">insert_stmt</span> <span class="o">=</span> <span class="n">insert</span><span class="p">(</span><span class="n">my_table</span><span class="p">)</span><span class="o">.</span> \
<span class="n">values</span><span class="p">(</span><span class="nb">id</span><span class="o">=</span><span class="s1">'some_id'</span><span class="p">,</span> <span class="n">data</span><span class="o">=</span><span class="s1">'some data to insert'</span><span class="p">)</span>
<span class="n">on_conflict_stmt</span> <span class="o">=</span> <span class="n">insert_stmt</span><span class="o">.</span><span class="n">on_duplicate_key_update</span><span class="p">(</span>
<span class="n">data</span><span class="o">=</span><span class="n">insert_stmt</span><span class="o">.</span><span class="n">inserted</span><span class="o">.</span><span class="n">data</span><span class="p">,</span>
<span class="n">status</span><span class="o">=</span><span class="s1">'U'</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">on_conflict_stmt</span><span class="p">)</span></pre></div>
</div>
<p>The above will render:</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">my_table</span> <span class="p">(</span><span class="nb">id</span><span class="p">,</span> <span class="n">data</span><span class="p">)</span>
<span class="n">VALUES</span> <span class="p">(:</span><span class="nb">id</span><span class="p">,</span> <span class="p">:</span><span class="n">data</span><span class="p">)</span>
<span class="n">ON</span> <span class="n">DUPLICATE</span> <span class="n">KEY</span> <span class="n">UPDATE</span> <span class="n">data</span><span class="o">=</span><span class="n">VALUES</span><span class="p">(</span><span class="n">data</span><span class="p">),</span> <span class="n">status</span><span class="o">=</span><span class="p">:</span><span class="n">status_1</span></pre></div>
</div>
<div class="admonition seealso">
<p class="admonition-title">See also</p>
<p><a class="reference internal" href="../dialects/mysql.html#mysql-insert-on-duplicate-key-update"><span class="std std-ref">INSERT…ON DUPLICATE KEY UPDATE (Upsert)</span></a></p>
</div>
<p><a class="reference external" href="http://www.sqlalchemy.org/trac/ticket/4009">#4009</a></p>
</div>
</div>
<div class="section" id="dialect-improvements-and-changes-oracle">
<h2>Dialect Improvements and Changes - Oracle<a class="headerlink" href="#dialect-improvements-and-changes-oracle" title="Permalink to this headline">¶</a></h2>
<div class="section" id="major-refactor-to-cx-oracle-dialect-typing-system">
<span id="change-cxoracle-12"></span><h3>Major Refactor to cx_Oracle Dialect, Typing System<a class="headerlink" href="#major-refactor-to-cx-oracle-dialect-typing-system" title="Permalink to this headline">¶</a></h3>
<p>With the introduction of the 6.x series of the cx_Oracle DBAPI, SQLAlchemy’s
cx_Oracle dialect has been reworked and simplified to take advantage of recent
improvements in cx_Oracle as well as dropping support for patterns that were
more relevant before the 5.x series of cx_Oracle.</p>
<ul>
<li><p>The minimum cx_Oracle version supported is now 5.1.3; 5.3 or the most recent
6.x series are recommended.</p></li>
<li><p>The handling of datatypes has been refactored. The <code class="docutils literal notranslate"><span class="pre">cursor.setinputsizes()</span></code>
method is no longer used for any datatype except LOB types, per advice from
cx_Oracle’s developers. As a result, the parameters <code class="docutils literal notranslate"><span class="pre">auto_setinputsizes</span></code>
and <code class="docutils literal notranslate"><span class="pre">exclude_setinputsizes</span></code> are deprecated and no longer have any effect.</p></li>
<li><p>The <code class="docutils literal notranslate"><span class="pre">coerce_to_decimal</span></code> flag, when set to False to indicate that coercion
of numeric types with precision and scale to <code class="docutils literal notranslate"><span class="pre">Decimal</span></code> should not occur,
only impacts untyped (e.g. plain string with no <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> objects)
statements. A Core expression that includes a <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> type or
subtype will now follow the decimal coercion rules of that type.</p></li>
<li><p>The “two phase” transaction support in the dialect, already dropped for the
6.x series of cx_Oracle, has now been removed entirely as this feature has
never worked correctly and is unlikely to have been in production use.
As a result, the <code class="docutils literal notranslate"><span class="pre">allow_twophase</span></code> dialect flag is deprecated and also has no
effect.</p></li>
<li><p>Fixed a bug involving the column keys present with RETURNING. Given
a statement as follows:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">result</span> <span class="o">=</span> <span class="n">conn</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="n">table</span><span class="o">.</span><span class="n">insert</span><span class="p">()</span><span class="o">.</span><span class="n">values</span><span class="p">(</span><span class="n">x</span><span class="o">=</span><span class="mi">5</span><span class="p">)</span><span class="o">.</span><span class="n">returning</span><span class="p">(</span><span class="n">table</span><span class="o">.</span><span class="n">c</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">c</span><span class="o">.</span><span class="n">b</span><span class="p">))</span></pre></div>
</div>
<p>Previously, the keys in each row of the result would be <code class="docutils literal notranslate"><span class="pre">ret_0</span></code> and <code class="docutils literal notranslate"><span class="pre">ret_1</span></code>,
which are identifiers internal to the cx_Oracle RETURNING implementation.
The keys will now be <code class="docutils literal notranslate"><span class="pre">a</span></code> and <code class="docutils literal notranslate"><span class="pre">b</span></code> as is expected for other dialects.</p>
</li>
<li><p>cx_Oracle’s LOB datatype represents return values as a <code class="docutils literal notranslate"><span class="pre">cx_Oracle.LOB</span></code>
object, which is a cursor-associated proxy that returns the ultimate data
value via a <code class="docutils literal notranslate"><span class="pre">.read()</span></code> method. Historically, if more rows were read before
these LOB objects were consumed (specifically, more rows than the value of
cursor.arraysize which causes a new batch of rows to be read), these LOB
objects would raise the error “LOB variable no longer valid after subsequent
fetch”. SQLAlchemy worked around this by both automatically calling
<code class="docutils literal notranslate"><span class="pre">.read()</span></code> upon these LOBs within its typing system, as well as using a
special <code class="docutils literal notranslate"><span class="pre">BufferedColumnResultSet</span></code> which would ensure this data was buffered
in case a call like <code class="docutils literal notranslate"><span class="pre">cursor.fetchmany()</span></code> or <code class="docutils literal notranslate"><span class="pre">cursor.fetchall()</span></code> were
used.</p>
<p>The dialect now makes use of a cx_Oracle outputtypehandler to handle these
<code class="docutils literal notranslate"><span class="pre">.read()</span></code> calls, so that they are always called up front regardless of how
many rows are being fetched, so that this error can no longer occur. As a
result, the use of the <code class="docutils literal notranslate"><span class="pre">BufferedColumnResultSet</span></code>, as well as some other
internals to the Core <code class="docutils literal notranslate"><span class="pre">ResultSet</span></code> that were specific to this use case,
have been removed. The type objects are also simplified as they no longer
need to process a binary column result.</p>
<p>Additionally, cx_Oracle 6.x has removed the conditions under which this error
occurs in any case, so the error is no longer possible. The error
can occur on SQLAlchemy in the case that the seldom (if ever) used
<code class="docutils literal notranslate"><span class="pre">auto_convert_lobs=False</span></code> option is in use, in conjunction with the
previous 5.x series of cx_Oracle, and more rows are read before the LOB
objects can be consumed. Upgrading to cx_Oracle 6.x will resolve that issue.</p>
</li>
</ul>
</div>
<div class="section" id="oracle-unique-check-constraints-now-reflected">
<span id="change-4003"></span><h3>Oracle Unique, Check constraints now reflected<a class="headerlink" href="#oracle-unique-check-constraints-now-reflected" title="Permalink to this headline">¶</a></h3>
<p>UNIQUE and CHECK constraints now reflect via
<a class="reference internal" href="../core/reflection.html#sqlalchemy.engine.reflection.Inspector.get_unique_constraints" title="sqlalchemy.engine.reflection.Inspector.get_unique_constraints"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Inspector.get_unique_constraints()</span></code></a> and
<a class="reference internal" href="../core/reflection.html#sqlalchemy.engine.reflection.Inspector.get_check_constraints" title="sqlalchemy.engine.reflection.Inspector.get_check_constraints"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Inspector.get_check_constraints()</span></code></a>. A <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> object that’s
reflected will now include <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> objects as well.
See the notes at <a class="reference internal" href="../dialects/oracle.html#oracle-constraint-reflection"><span class="std std-ref">Constraint Reflection</span></a> for information
on behavioral quirks here, including that most <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> objects
will still not include any <a class="reference internal" href="../core/constraints.html#sqlalchemy.schema.UniqueConstraint" title="sqlalchemy.schema.UniqueConstraint"><code class="xref py py-class docutils literal notranslate"><span class="pre">UniqueConstraint</span></code></a> objects as these
usually represent via <a class="reference internal" href="../core/constraints.html#sqlalchemy.schema.Index" title="sqlalchemy.schema.Index"><code class="xref py py-class docutils literal notranslate"><span class="pre">Index</span></code></a>.</p>
<div class="admonition seealso">
<p class="admonition-title">See also</p>
<p><a class="reference internal" href="../dialects/oracle.html#oracle-constraint-reflection"><span class="std std-ref">Constraint Reflection</span></a></p>
</div>
<p><a class="reference external" href="http://www.sqlalchemy.org/trac/ticket/4003">#4003</a></p>
</div>
<div class="section" id="oracle-foreign-key-constraint-names-are-now-name-normalized">
<span id="change-3276"></span><h3>Oracle foreign key constraint names are now “name normalized”<a class="headerlink" href="#oracle-foreign-key-constraint-names-are-now-name-normalized" title="Permalink to this headline">¶</a></h3>
<p>The names of foreign key constraints as delivered to 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> object during table reflection as well as
within the <a class="reference internal" href="../core/reflection.html#sqlalchemy.engine.reflection.Inspector.get_foreign_keys" title="sqlalchemy.engine.reflection.Inspector.get_foreign_keys"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Inspector.get_foreign_keys()</span></code></a> method will now be
“name normalized”, that is, expressed as lower case for a case insensitive
name, rather than the raw UPPERCASE format that Oracle uses:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="gp">>>> </span><span class="n">insp</span><span class="o">.</span><span class="n">get_indexes</span><span class="p">(</span><span class="s2">"addresses"</span><span class="p">)</span>
<span class="go">[{'unique': False, 'column_names': [u'user_id'],</span>
<span class="go"> 'name': u'address_idx', 'dialect_options': {}}]</span>
<span class="gp">>>> </span><span class="n">insp</span><span class="o">.</span><span class="n">get_pk_constraint</span><span class="p">(</span><span class="s2">"addresses"</span><span class="p">)</span>
<span class="go">{'name': u'pk_cons', 'constrained_columns': [u'id']}</span>
<span class="gp">>>> </span><span class="n">insp</span><span class="o">.</span><span class="n">get_foreign_keys</span><span class="p">(</span><span class="s2">"addresses"</span><span class="p">)</span>
<span class="go">[{'referred_table': u'users', 'referred_columns': [u'id'],</span>
<span class="go"> 'referred_schema': None, 'name': u'user_id_fk',</span>
<span class="go"> 'constrained_columns': [u'user_id']}]</span></pre></div>
</div>
<p>Previously, the foreign keys result would look like:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="p">[{</span><span class="s1">'referred_table'</span><span class="p">:</span> <span class="sa">u</span><span class="s1">'users'</span><span class="p">,</span> <span class="s1">'referred_columns'</span><span class="p">:</span> <span class="p">[</span><span class="sa">u</span><span class="s1">'id'</span><span class="p">],</span>
<span class="s1">'referred_schema'</span><span class="p">:</span> <span class="kc">None</span><span class="p">,</span> <span class="s1">'name'</span><span class="p">:</span> <span class="s1">'USER_ID_FK'</span><span class="p">,</span>
<span class="s1">'constrained_columns'</span><span class="p">:</span> <span class="p">[</span><span class="sa">u</span><span class="s1">'user_id'</span><span class="p">]}]</span></pre></div>
</div>
<p>Where the above could create problems particularly with Alembic autogenerate.</p>
<p><a class="reference external" href="http://www.sqlalchemy.org/trac/ticket/3276">#3276</a></p>
</div>
</div>
<div class="section" id="dialect-improvements-and-changes-sql-server">
<h2>Dialect Improvements and Changes - SQL Server<a class="headerlink" href="#dialect-improvements-and-changes-sql-server" title="Permalink to this headline">¶</a></h2>
<div class="section" id="sql-server-schema-names-with-embedded-dots-supported">
<span id="change-2626"></span><h3>SQL Server schema names with embedded dots supported<a class="headerlink" href="#sql-server-schema-names-with-embedded-dots-supported" title="Permalink to this headline">¶</a></h3>
<p>The SQL Server dialect has a behavior such that a schema name with a dot inside
of it is assumed to be a “database”.”owner” identifier pair, which is
necessarily split up into these separate components during table and component
reflection operations, as well as when rendering quoting for the schema name so
that the two symbols are quoted separately. The schema argument can
now be passed using brackets to manually specify where this split
occurs, allowing database and/or owner names that themselves contain one
or more dots:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">Table</span><span class="p">(</span>
<span class="s2">"some_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="s2">"q"</span><span class="p">,</span> <span class="n">String</span><span class="p">(</span><span class="mi">50</span><span class="p">)),</span>
<span class="n">schema</span><span class="o">=</span><span class="s2">"[MyDataBase.dbo]"</span>
<span class="p">)</span></pre></div>
</div>
<p>The above table will consider the “owner” to be <code class="docutils literal notranslate"><span class="pre">MyDataBase.dbo</span></code>, which
will also be quoted upon render, and the “database” as None. To individually
refer to database name and owner, use two pairs of brackets:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">Table</span><span class="p">(</span>
<span class="s2">"some_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="s2">"q"</span><span class="p">,</span> <span class="n">String</span><span class="p">(</span><span class="mi">50</span><span class="p">)),</span>
<span class="n">schema</span><span class="o">=</span><span class="s2">"[MyDataBase.SomeDB].[MyDB.owner]"</span>
<span class="p">)</span></pre></div>
</div>
<p>Additionally, 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> construct is now honored when
passed to “schema” by the SQL Server dialect; the given symbol will
not be split on the dot if the quote flag is True and will be interpreted
as the “owner”.</p>
<div class="admonition seealso">
<p class="admonition-title">See also</p>
<p><a class="reference internal" href="../dialects/mssql.html#multipart-schema-names"><span class="std std-ref">Multipart Schema Names</span></a></p>
</div>
<p><a class="reference external" href="http://www.sqlalchemy.org/trac/ticket/2626">#2626</a></p>
</div>
<div class="section" id="autocommit-isolation-level-support">
<h3>AUTOCOMMIT isolation level support<a class="headerlink" href="#autocommit-isolation-level-support" title="Permalink to this headline">¶</a></h3>
<p>Both the PyODBC and pymssql dialects now support the “AUTOCOMMIT” isolation
level as set by <a class="reference internal" href="../core/connections.html#sqlalchemy.engine.Connection.execution_options" title="sqlalchemy.engine.Connection.execution_options"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Connection.execution_options()</span></code></a> which will establish
the correct flags on the DBAPI connection object.</p>
</div>
</div>
</div>
</div>
</div>
<div id="docs-bottom-navigation" class="docs-navigation-links, withsidebar">
Previous:
<a href="index.html" title="previous chapter">Changes and Migration</a>
Next:
<a href="changelog_12.html" title="next chapter">1.2 Changelog</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>
