<!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>
Sessions / Queries
—
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="Frequently Asked Questions" href="index.html" />
<link rel="next" title="Error Messages" href="../errors.html" />
<link rel="prev" title="Performance" href="performance.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="Frequently Asked Questions">Frequently Asked Questions</a>
</h3>
<ul>
<li><span class="link-container"><a class="reference external" href="connections.html">Connections / Engines</a></span></li>
<li><span class="link-container"><a class="reference external" href="metadata_schema.html">MetaData / Schema</a></span></li>
<li><span class="link-container"><a class="reference external" href="sqlexpressions.html">SQL Expressions</a></span></li>
<li><span class="link-container"><a class="reference external" href="ormconfiguration.html">ORM Configuration</a></span></li>
<li><span class="link-container"><a class="reference external" href="performance.html">Performance</a></span></li>
<li class="selected"><span class="link-container"><strong>Sessions / Queries</strong><a class="paramlink headerlink reference internal" href="#">¶</a></span><ul>
<li><span class="link-container"><a class="reference external" href="#i-m-re-loading-data-with-my-session-but-it-isn-t-seeing-changes-that-i-committed-elsewhere">I’m re-loading data with my Session but it isn’t seeing changes that I committed elsewhere</a></span></li>
<li><span class="link-container"><a class="reference external" href="#this-session-s-transaction-has-been-rolled-back-due-to-a-previous-exception-during-flush-or-similar">“This Session’s transaction has been rolled back due to a previous exception during flush.” (or similar)</a></span><ul>
<li><span class="link-container"><a class="reference external" href="#but-why-does-flush-insist-on-issuing-a-rollback">But why does flush() insist on issuing a ROLLBACK?</a></span></li>
<li><span class="link-container"><a class="reference external" href="#but-why-isn-t-the-one-automatic-call-to-rollback-enough-why-must-i-rollback-again">But why isn’t the one automatic call to ROLLBACK enough? Why must I ROLLBACK again?</a></span></li>
</ul>
</li>
<li><span class="link-container"><a class="reference external" href="#how-do-i-make-a-query-that-always-adds-a-certain-filter-to-every-query">How do I make a Query that always adds a certain filter to every query?</a></span></li>
<li><span class="link-container"><a class="reference external" href="#i-ve-created-a-mapping-against-an-outer-join-and-while-the-query-returns-rows-no-objects-are-returned-why-not">I’ve created a mapping against an Outer Join, and while the query returns rows, no objects are returned. Why not?</a></span></li>
<li><span class="link-container"><a class="reference external" href="#i-m-using-joinedload-or-lazy-false-to-create-a-join-outer-join-and-sqlalchemy-is-not-constructing-the-correct-query-when-i-try-to-add-a-where-order-by-limit-etc-which-relies-upon-the-outer-join">I’m using <code class="docutils literal notranslate"><span class="pre">joinedload()</span></code> or <code class="docutils literal notranslate"><span class="pre">lazy=False</span></code> to create a JOIN/OUTER JOIN and SQLAlchemy is not constructing the correct query when I try to add a WHERE, ORDER BY, LIMIT, etc. (which relies upon the (OUTER) JOIN)</a></span></li>
<li><span class="link-container"><a class="reference external" href="#query-has-no-len-why-not">Query has no <code class="docutils literal notranslate"><span class="pre">__len__()</span></code>, why not?</a></span></li>
<li><span class="link-container"><a class="reference external" href="#how-do-i-use-textual-sql-with-orm-queries">How Do I use Textual SQL with ORM Queries?</a></span></li>
<li><span class="link-container"><a class="reference external" href="#i-m-calling-session-delete-myobject-and-it-isn-t-removed-from-the-parent-collection">I’m calling <code class="docutils literal notranslate"><span class="pre">Session.delete(myobject)</span></code> and it isn’t removed from the parent collection!</a></span></li>
<li><span class="link-container"><a class="reference external" href="#why-isn-t-my-init-called-when-i-load-objects">why isn’t my <code class="docutils literal notranslate"><span class="pre">__init__()</span></code> called when I load objects?</a></span></li>
<li><span class="link-container"><a class="reference external" href="#how-do-i-use-on-delete-cascade-with-sa-s-orm">how do I use ON DELETE CASCADE with SA’s ORM?</a></span></li>
<li><span class="link-container"><a class="reference external" href="#i-set-the-foo-id-attribute-on-my-instance-to-7-but-the-foo-attribute-is-still-none-shouldn-t-it-have-loaded-foo-with-id-7">I set the “foo_id” attribute on my instance to “7”, but the “foo” attribute is still <code class="docutils literal notranslate"><span class="pre">None</span></code> - shouldn’t it have loaded Foo with id #7?</a></span></li>
<li><span class="link-container"><a class="reference external" href="#how-do-i-walk-all-objects-that-are-related-to-a-given-object">How do I walk all objects that are related to a given object?</a></span></li>
<li><span class="link-container"><a class="reference external" href="#is-there-a-way-to-automagically-have-only-unique-keywords-or-other-kinds-of-objects-without-doing-a-query-for-the-keyword-and-getting-a-reference-to-the-row-containing-that-keyword">Is there a way to automagically have only unique keywords (or other kinds of objects) without doing a query for the keyword and getting a reference to the row containing that keyword?</a></span></li>
<li><span class="link-container"><a class="reference external" href="#why-does-post-update-emit-update-in-addition-to-the-first-update">Why does post_update emit UPDATE in addition to the first UPDATE?</a></span></li>
</ul>
</li>
</ul>
</div>
</div>
</div>
<div id="docs-body" class="withsidebar" >
<div class="section" id="sessions-queries">
<h1>Sessions / Queries<a class="headerlink" href="#sessions-queries" title="Permalink to this headline">¶</a></h1>
<div class="contents faq local topic" id="contents">
<ul class="simple">
<li><p><a class="reference internal" href="#i-m-re-loading-data-with-my-session-but-it-isn-t-seeing-changes-that-i-committed-elsewhere" id="id1">I’m re-loading data with my Session but it isn’t seeing changes that I committed elsewhere</a></p></li>
<li><p><a class="reference internal" href="#this-session-s-transaction-has-been-rolled-back-due-to-a-previous-exception-during-flush-or-similar" id="id2">“This Session’s transaction has been rolled back due to a previous exception during flush.” (or similar)</a></p>
<ul>
<li><p><a class="reference internal" href="#but-why-does-flush-insist-on-issuing-a-rollback" id="id3">But why does flush() insist on issuing a ROLLBACK?</a></p></li>
<li><p><a class="reference internal" href="#but-why-isn-t-the-one-automatic-call-to-rollback-enough-why-must-i-rollback-again" id="id4">But why isn’t the one automatic call to ROLLBACK enough? Why must I ROLLBACK again?</a></p></li>
</ul>
</li>
<li><p><a class="reference internal" href="#how-do-i-make-a-query-that-always-adds-a-certain-filter-to-every-query" id="id5">How do I make a Query that always adds a certain filter to every query?</a></p></li>
<li><p><a class="reference internal" href="#i-ve-created-a-mapping-against-an-outer-join-and-while-the-query-returns-rows-no-objects-are-returned-why-not" id="id6">I’ve created a mapping against an Outer Join, and while the query returns rows, no objects are returned. Why not?</a></p></li>
<li><p><a class="reference internal" href="#i-m-using-joinedload-or-lazy-false-to-create-a-join-outer-join-and-sqlalchemy-is-not-constructing-the-correct-query-when-i-try-to-add-a-where-order-by-limit-etc-which-relies-upon-the-outer-join" id="id7">I’m using <code class="docutils literal notranslate"><span class="pre">joinedload()</span></code> or <code class="docutils literal notranslate"><span class="pre">lazy=False</span></code> to create a JOIN/OUTER JOIN and SQLAlchemy is not constructing the correct query when I try to add a WHERE, ORDER BY, LIMIT, etc. (which relies upon the (OUTER) JOIN)</a></p></li>
<li><p><a class="reference internal" href="#query-has-no-len-why-not" id="id8">Query has no <code class="docutils literal notranslate"><span class="pre">__len__()</span></code>, why not?</a></p></li>
<li><p><a class="reference internal" href="#how-do-i-use-textual-sql-with-orm-queries" id="id9">How Do I use Textual SQL with ORM Queries?</a></p></li>
<li><p><a class="reference internal" href="#i-m-calling-session-delete-myobject-and-it-isn-t-removed-from-the-parent-collection" id="id10">I’m calling <code class="docutils literal notranslate"><span class="pre">Session.delete(myobject)</span></code> and it isn’t removed from the parent collection!</a></p></li>
<li><p><a class="reference internal" href="#why-isn-t-my-init-called-when-i-load-objects" id="id11">why isn’t my <code class="docutils literal notranslate"><span class="pre">__init__()</span></code> called when I load objects?</a></p></li>
<li><p><a class="reference internal" href="#how-do-i-use-on-delete-cascade-with-sa-s-orm" id="id12">how do I use ON DELETE CASCADE with SA’s ORM?</a></p></li>
<li><p><a class="reference internal" href="#i-set-the-foo-id-attribute-on-my-instance-to-7-but-the-foo-attribute-is-still-none-shouldn-t-it-have-loaded-foo-with-id-7" id="id13">I set the “foo_id” attribute on my instance to “7”, but the “foo” attribute is still <code class="docutils literal notranslate"><span class="pre">None</span></code> - shouldn’t it have loaded Foo with id #7?</a></p></li>
<li><p><a class="reference internal" href="#how-do-i-walk-all-objects-that-are-related-to-a-given-object" id="id14">How do I walk all objects that are related to a given object?</a></p></li>
<li><p><a class="reference internal" href="#is-there-a-way-to-automagically-have-only-unique-keywords-or-other-kinds-of-objects-without-doing-a-query-for-the-keyword-and-getting-a-reference-to-the-row-containing-that-keyword" id="id15">Is there a way to automagically have only unique keywords (or other kinds of objects) without doing a query for the keyword and getting a reference to the row containing that keyword?</a></p></li>
<li><p><a class="reference internal" href="#why-does-post-update-emit-update-in-addition-to-the-first-update" id="id16">Why does post_update emit UPDATE in addition to the first UPDATE?</a></p></li>
</ul>
</div>
<div class="section" id="i-m-re-loading-data-with-my-session-but-it-isn-t-seeing-changes-that-i-committed-elsewhere">
<h2>I’m re-loading data with my Session but it isn’t seeing changes that I committed elsewhere<a class="headerlink" href="#i-m-re-loading-data-with-my-session-but-it-isn-t-seeing-changes-that-i-committed-elsewhere" title="Permalink to this headline">¶</a></h2>
<p>The main issue regarding this behavior is that the session acts as though
the transaction is in the <em>serializable</em> isolation state, even if it’s not
(and it usually is not). In practical terms, this means that the session
does not alter any data that it’s already read within the scope of a transaction.</p>
<p>If the term “isolation level” is unfamiliar, then you first need to read this link:</p>
<p><a class="reference external" href="https://en.wikipedia.org/wiki/Isolation_%28database_systems%29">Isolation Level</a></p>
<p>In short, serializable isolation level generally means
that once you SELECT a series of rows in a transaction, you will get
<em>the identical data</em> back each time you re-emit that SELECT. If you are in
the next-lower isolation level, “repeatable read”, you’ll
see newly added rows (and no longer see deleted rows), but for rows that
you’ve <em>already</em> loaded, you won’t see any change. Only if you are in a
lower isolation level, e.g. “read committed”, does it become possible to
see a row of data change its value.</p>
<p>For information on controlling the isolation level when using the
SQLAlchemy ORM, see <a class="reference internal" href="../orm/session_transaction.html#session-transaction-isolation"><span class="std std-ref">Setting Transaction Isolation Levels</span></a>.</p>
<p>To simplify things dramatically, 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> itself works in
terms of a completely isolated transaction, and doesn’t overwrite any mapped attributes
it’s already read unless you tell it to. The use case of trying to re-read
data you’ve already loaded in an ongoing transaction is an <em>uncommon</em> use
case that in many cases has no effect, so this is considered to be the
exception, not the norm; to work within this exception, several methods
are provided to allow specific data to be reloaded within the context
of an ongoing transaction.</p>
<p>To understand what we mean by “the transaction” when we talk about 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>, your <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> is intended to only work within
a transaction. An overview of this is at <a class="reference internal" href="../orm/session_transaction.html#unitofwork-transaction"><span class="std std-ref">Managing Transactions</span></a>.</p>
<p>Once we’ve figured out what our isolation level is, and we think that
our isolation level is set at a low enough level so that if we re-SELECT a row,
we should see new data in our <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>, how do we see it?</p>
<p>Three ways, from most common to least:</p>
<ol class="arabic simple">
<li><p>We simply end our transaction and start a new one on next access
with our <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> by calling <a class="reference internal" href="../orm/session_api.html#sqlalchemy.orm.session.Session.commit" title="sqlalchemy.orm.session.Session.commit"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Session.commit()</span></code></a> (note
that if 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> is in the lesser-used “autocommit”
mode, there would be a call to <a class="reference internal" href="../orm/session_api.html#sqlalchemy.orm.session.Session.begin" title="sqlalchemy.orm.session.Session.begin"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Session.begin()</span></code></a> as well). The
vast majority of applications and use cases do not have any issues
with not being able to “see” data in other transactions because
they stick to this pattern, which is at the core of the best practice of
<strong>short lived transactions</strong>.
See <a class="reference internal" href="../orm/session_basics.html#session-faq-whentocreate"><span class="std std-ref">When do I construct a Session, when do I commit it, and when do I close it?</span></a> for some thoughts on this.</p></li>
<li><p>We tell our <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> to re-read rows that it has already read,
either when we next query for them using <a class="reference internal" href="../orm/session_api.html#sqlalchemy.orm.session.Session.expire_all" title="sqlalchemy.orm.session.Session.expire_all"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Session.expire_all()</span></code></a>
or <a class="reference internal" href="../orm/session_api.html#sqlalchemy.orm.session.Session.expire" title="sqlalchemy.orm.session.Session.expire"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Session.expire()</span></code></a>, or immediately on an object using
<code class="xref py py-class docutils literal notranslate"><span class="pre">Session.refresh</span></code>. See <a class="reference internal" href="../orm/session_state_management.html#session-expire"><span class="std std-ref">Refreshing / Expiring</span></a> for detail on this.</p></li>
<li><p>We can run whole queries while setting them to definitely overwrite
already-loaded objects as they read rows by using
<a class="reference internal" href="../orm/query.html#sqlalchemy.orm.query.Query.populate_existing" title="sqlalchemy.orm.query.Query.populate_existing"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Query.populate_existing()</span></code></a>.</p></li>
</ol>
<p>But remember, <strong>the ORM cannot see changes in rows if our isolation
level is repeatable read or higher, unless we start a new transaction</strong>.</p>
</div>
<div class="section" id="this-session-s-transaction-has-been-rolled-back-due-to-a-previous-exception-during-flush-or-similar">
<h2>“This Session’s transaction has been rolled back due to a previous exception during flush.” (or similar)<a class="headerlink" href="#this-session-s-transaction-has-been-rolled-back-due-to-a-previous-exception-during-flush-or-similar" title="Permalink to this headline">¶</a></h2>
<p>This is an error that occurs when a <a class="reference internal" href="../orm/session_api.html#sqlalchemy.orm.session.Session.flush" title="sqlalchemy.orm.session.Session.flush"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Session.flush()</span></code></a> raises an exception, rolls back
the transaction, but further commands upon the <cite>Session</cite> are called without an
explicit call to <a class="reference internal" href="../orm/session_api.html#sqlalchemy.orm.session.Session.rollback" title="sqlalchemy.orm.session.Session.rollback"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Session.rollback()</span></code></a> or <a class="reference internal" href="../orm/session_api.html#sqlalchemy.orm.session.Session.close" title="sqlalchemy.orm.session.Session.close"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Session.close()</span></code></a>.</p>
<p>It usually corresponds to an application that catches an exception
upon <a class="reference internal" href="../orm/session_api.html#sqlalchemy.orm.session.Session.flush" title="sqlalchemy.orm.session.Session.flush"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Session.flush()</span></code></a> or <a class="reference internal" href="../orm/session_api.html#sqlalchemy.orm.session.Session.commit" title="sqlalchemy.orm.session.Session.commit"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Session.commit()</span></code></a> and
does not properly handle the exception. For example:</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">create_engine</span><span class="p">,</span> <span class="n">Column</span><span class="p">,</span> <span class="n">Integer</span>
<span class="kn">from</span> <span class="nn">sqlalchemy.orm</span> <span class="k">import</span> <span class="n">sessionmaker</span>
<span class="kn">from</span> <span class="nn">sqlalchemy.ext.declarative</span> <span class="k">import</span> <span class="n">declarative_base</span>
<span class="n">Base</span> <span class="o">=</span> <span class="n">declarative_base</span><span class="p">(</span><span class="n">create_engine</span><span class="p">(</span><span class="s1">'sqlite://'</span><span class="p">))</span>
<span class="k">class</span> <span class="nc">Foo</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">'foo'</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">Base</span><span class="o">.</span><span class="n">metadata</span><span class="o">.</span><span class="n">create_all</span><span class="p">()</span>
<span class="n">session</span> <span class="o">=</span> <span class="n">sessionmaker</span><span class="p">()()</span>
<span class="c1"># constraint violation</span>
<span class="n">session</span><span class="o">.</span><span class="n">add_all</span><span class="p">([</span><span class="n">Foo</span><span class="p">(</span><span class="nb">id</span><span class="o">=</span><span class="mi">1</span><span class="p">),</span> <span class="n">Foo</span><span class="p">(</span><span class="nb">id</span><span class="o">=</span><span class="mi">1</span><span class="p">)])</span>
<span class="k">try</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="k">except</span><span class="p">:</span>
<span class="c1"># ignore error</span>
<span class="k">pass</span>
<span class="c1"># continue using session without rolling back</span>
<span class="n">session</span><span class="o">.</span><span class="n">commit</span><span class="p">()</span></pre></div>
</div>
<p>The usage of 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> should fit within a structure similar to this:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="k">try</span><span class="p">:</span>
<span class="o"><</span><span class="n">use</span> <span class="n">session</span><span class="o">></span>
<span class="n">session</span><span class="o">.</span><span class="n">commit</span><span class="p">()</span>
<span class="k">except</span><span class="p">:</span>
<span class="n">session</span><span class="o">.</span><span class="n">rollback</span><span class="p">()</span>
<span class="k">raise</span>
<span class="k">finally</span><span class="p">:</span>
<span class="n">session</span><span class="o">.</span><span class="n">close</span><span class="p">()</span> <span class="c1"># optional, depends on use case</span></pre></div>
</div>
<p>Many things can cause a failure within the try/except besides flushes. You
should always have some kind of “framing” of your session operations so that
connection and transaction resources have a definitive boundary, otherwise
your application doesn’t really have its usage of resources under control.
This is not to say that you need to put try/except blocks all throughout your
application - on the contrary, this would be a terrible idea. You should
architect your application such that there is one (or few) point(s) of
“framing” around session operations.</p>
<p>For a detailed discussion on how to organize usage of 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>,
please see <a class="reference internal" href="../orm/session_basics.html#session-faq-whentocreate"><span class="std std-ref">When do I construct a Session, when do I commit it, and when do I close it?</span></a>.</p>
<div class="section" id="but-why-does-flush-insist-on-issuing-a-rollback">
<h3>But why does flush() insist on issuing a ROLLBACK?<a class="headerlink" href="#but-why-does-flush-insist-on-issuing-a-rollback" title="Permalink to this headline">¶</a></h3>
<p>It would be great if <a class="reference internal" href="../orm/session_api.html#sqlalchemy.orm.session.Session.flush" title="sqlalchemy.orm.session.Session.flush"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Session.flush()</span></code></a> could partially complete and then not roll
back, however this is beyond its current capabilities since its internal
bookkeeping would have to be modified such that it can be halted at any time
and be exactly consistent with what’s been flushed to the database. While this
is theoretically possible, the usefulness of the enhancement is greatly
decreased by the fact that many database operations require a ROLLBACK in any
case. Postgres in particular has operations which, once failed, the
transaction is not allowed to continue:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">test</span><span class="o">=></span> <span class="n">create</span> <span class="n">table</span> <span class="n">foo</span><span class="p">(</span><span class="nb">id</span> <span class="n">integer</span> <span class="n">primary</span> <span class="n">key</span><span class="p">);</span>
<span class="n">NOTICE</span><span class="p">:</span> <span class="n">CREATE</span> <span class="n">TABLE</span> <span class="o">/</span> <span class="n">PRIMARY</span> <span class="n">KEY</span> <span class="n">will</span> <span class="n">create</span> <span class="n">implicit</span> <span class="n">index</span> <span class="s2">"foo_pkey"</span> <span class="k">for</span> <span class="n">table</span> <span class="s2">"foo"</span>
<span class="n">CREATE</span> <span class="n">TABLE</span>
<span class="n">test</span><span class="o">=></span> <span class="n">begin</span><span class="p">;</span>
<span class="n">BEGIN</span>
<span class="n">test</span><span class="o">=></span> <span class="n">insert</span> <span class="n">into</span> <span class="n">foo</span> <span class="n">values</span><span class="p">(</span><span class="mi">1</span><span class="p">);</span>
<span class="n">INSERT</span> <span class="mi">0</span> <span class="mi">1</span>
<span class="n">test</span><span class="o">=></span> <span class="n">commit</span><span class="p">;</span>
<span class="n">COMMIT</span>
<span class="n">test</span><span class="o">=></span> <span class="n">begin</span><span class="p">;</span>
<span class="n">BEGIN</span>
<span class="n">test</span><span class="o">=></span> <span class="n">insert</span> <span class="n">into</span> <span class="n">foo</span> <span class="n">values</span><span class="p">(</span><span class="mi">1</span><span class="p">);</span>
<span class="n">ERROR</span><span class="p">:</span> <span class="n">duplicate</span> <span class="n">key</span> <span class="n">value</span> <span class="n">violates</span> <span class="n">unique</span> <span class="n">constraint</span> <span class="s2">"foo_pkey"</span>
<span class="n">test</span><span class="o">=></span> <span class="n">insert</span> <span class="n">into</span> <span class="n">foo</span> <span class="n">values</span><span class="p">(</span><span class="mi">2</span><span class="p">);</span>
<span class="n">ERROR</span><span class="p">:</span> <span class="n">current</span> <span class="n">transaction</span> <span class="ow">is</span> <span class="n">aborted</span><span class="p">,</span> <span class="n">commands</span> <span class="n">ignored</span> <span class="n">until</span> <span class="n">end</span> <span class="n">of</span> <span class="n">transaction</span> <span class="n">block</span></pre></div>
</div>
<p>What SQLAlchemy offers that solves both issues is support of SAVEPOINT, via
<a class="reference internal" href="../orm/session_api.html#sqlalchemy.orm.session.Session.begin_nested" title="sqlalchemy.orm.session.Session.begin_nested"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Session.begin_nested()</span></code></a>. Using <a class="reference internal" href="../orm/session_api.html#sqlalchemy.orm.session.Session.begin_nested" title="sqlalchemy.orm.session.Session.begin_nested"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Session.begin_nested()</span></code></a>, you can frame an operation that may
potentially fail within a transaction, and then “roll back” to the point
before its failure while maintaining the enclosing transaction.</p>
</div>
<div class="section" id="but-why-isn-t-the-one-automatic-call-to-rollback-enough-why-must-i-rollback-again">
<h3>But why isn’t the one automatic call to ROLLBACK enough? Why must I ROLLBACK again?<a class="headerlink" href="#but-why-isn-t-the-one-automatic-call-to-rollback-enough-why-must-i-rollback-again" title="Permalink to this headline">¶</a></h3>
<p>This is again a matter of 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> providing a consistent interface and
refusing to guess about what context its being used. For example, 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> supports “framing” above within multiple levels. Such as, suppose
you had a decorator <code class="docutils literal notranslate"><span class="pre">@with_session()</span></code>, which did this:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="k">def</span> <span class="nf">with_session</span><span class="p">(</span><span class="n">fn</span><span class="p">):</span>
<span class="k">def</span> <span class="nf">go</span><span class="p">(</span><span class="o">*</span><span class="n">args</span><span class="p">,</span> <span class="o">**</span><span class="n">kw</span><span class="p">):</span>
<span class="n">session</span><span class="o">.</span><span class="n">begin</span><span class="p">(</span><span class="n">subtransactions</span><span class="o">=</span><span class="kc">True</span><span class="p">)</span>
<span class="k">try</span><span class="p">:</span>
<span class="n">ret</span> <span class="o">=</span> <span class="n">fn</span><span class="p">(</span><span class="o">*</span><span class="n">args</span><span class="p">,</span> <span class="o">**</span><span class="n">kw</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="k">return</span> <span class="n">ret</span>
<span class="k">except</span><span class="p">:</span>
<span class="n">session</span><span class="o">.</span><span class="n">rollback</span><span class="p">()</span>
<span class="k">raise</span>
<span class="k">return</span> <span class="n">go</span></pre></div>
</div>
<p>The above decorator begins a transaction if one does not exist already, and
then commits it, if it were the creator. The “subtransactions” flag means that
if <a class="reference internal" href="../orm/session_api.html#sqlalchemy.orm.session.Session.begin" title="sqlalchemy.orm.session.Session.begin"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Session.begin()</span></code></a> were already called by an enclosing function, nothing happens
except a counter is incremented - this counter is decremented when <a class="reference internal" href="../orm/session_api.html#sqlalchemy.orm.session.Session.commit" title="sqlalchemy.orm.session.Session.commit"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Session.commit()</span></code></a>
is called and only when it goes back to zero does the actual COMMIT happen. It
allows this usage pattern:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="nd">@with_session</span>
<span class="k">def</span> <span class="nf">one</span><span class="p">():</span>
<span class="c1"># do stuff</span>
<span class="n">two</span><span class="p">()</span>
<span class="nd">@with_session</span>
<span class="k">def</span> <span class="nf">two</span><span class="p">():</span>
<span class="c1"># etc.</span>
<span class="n">one</span><span class="p">()</span>
<span class="n">two</span><span class="p">()</span></pre></div>
</div>
<p><code class="docutils literal notranslate"><span class="pre">one()</span></code> can call <code class="docutils literal notranslate"><span class="pre">two()</span></code>, or <code class="docutils literal notranslate"><span class="pre">two()</span></code> can be called by itself, and the
<code class="docutils literal notranslate"><span class="pre">@with_session</span></code> decorator ensures the appropriate “framing” - the transaction
boundaries stay on the outermost call level. As you can see, if <code class="docutils literal notranslate"><span class="pre">two()</span></code> calls
<code class="docutils literal notranslate"><span class="pre">flush()</span></code> which throws an exception and then issues a <code class="docutils literal notranslate"><span class="pre">rollback()</span></code>, there will
<em>always</em> be a second <code class="docutils literal notranslate"><span class="pre">rollback()</span></code> performed by the decorator, and possibly a
third corresponding to two levels of decorator. If the <code class="docutils literal notranslate"><span class="pre">flush()</span></code> pushed the
<code class="docutils literal notranslate"><span class="pre">rollback()</span></code> all the way out to the top of the stack, and then we said that
all remaining <code class="docutils literal notranslate"><span class="pre">rollback()</span></code> calls are moot, there is some silent behavior going
on there. A poorly written enclosing method might suppress the exception, and
then call <code class="docutils literal notranslate"><span class="pre">commit()</span></code> assuming nothing is wrong, and then you have a silent
failure condition. The main reason people get this error in fact is because
they didn’t write clean “framing” code and they would have had other problems
down the road.</p>
<p>If you think the above use case is a little exotic, the same kind of thing
comes into play if you want to SAVEPOINT- you might call <code class="docutils literal notranslate"><span class="pre">begin_nested()</span></code>
several times, and the <code class="docutils literal notranslate"><span class="pre">commit()</span></code>/<code class="docutils literal notranslate"><span class="pre">rollback()</span></code> calls each resolve the most
recent <code class="docutils literal notranslate"><span class="pre">begin_nested()</span></code>. The meaning of <code class="docutils literal notranslate"><span class="pre">rollback()</span></code> or <code class="docutils literal notranslate"><span class="pre">commit()</span></code> is
dependent upon which enclosing block it is called, and you might have any
sequence of <code class="docutils literal notranslate"><span class="pre">rollback()</span></code>/<code class="docutils literal notranslate"><span class="pre">commit()</span></code> in any order, and its the level of nesting
that determines their behavior.</p>
<p>In both of the above cases, if <code class="docutils literal notranslate"><span class="pre">flush()</span></code> broke the nesting of transaction
blocks, the behavior is, depending on scenario, anywhere from “magic” to
silent failure to blatant interruption of code flow.</p>
<p><code class="docutils literal notranslate"><span class="pre">flush()</span></code> makes its own “subtransaction”, so that a transaction is started up
regardless of the external transactional state, and when complete it calls
<code class="docutils literal notranslate"><span class="pre">commit()</span></code>, or <code class="docutils literal notranslate"><span class="pre">rollback()</span></code> upon failure - but that <code class="docutils literal notranslate"><span class="pre">rollback()</span></code> corresponds
to its own subtransaction - it doesn’t want to guess how you’d like to handle
the external “framing” of the transaction, which could be nested many levels
with any combination of subtransactions and real SAVEPOINTs. The job of
starting/ending the “frame” is kept consistently with the code external to the
<code class="docutils literal notranslate"><span class="pre">flush()</span></code>, and we made a decision that this was the most consistent approach.</p>
</div>
</div>
<div class="section" id="how-do-i-make-a-query-that-always-adds-a-certain-filter-to-every-query">
<h2>How do I make a Query that always adds a certain filter to every query?<a class="headerlink" href="#how-do-i-make-a-query-that-always-adds-a-certain-filter-to-every-query" title="Permalink to this headline">¶</a></h2>
<p>See the recipe at <a class="reference external" href="http://www.sqlalchemy.org/trac/wiki/UsageRecipes/PreFilteredQuery">PreFilteredQuery</a>.</p>
</div>
<div class="section" id="i-ve-created-a-mapping-against-an-outer-join-and-while-the-query-returns-rows-no-objects-are-returned-why-not">
<h2>I’ve created a mapping against an Outer Join, and while the query returns rows, no objects are returned. Why not?<a class="headerlink" href="#i-ve-created-a-mapping-against-an-outer-join-and-while-the-query-returns-rows-no-objects-are-returned-why-not" title="Permalink to this headline">¶</a></h2>
<p>Rows returned by an outer join may contain NULL for part of the primary key,
as the primary key is the composite of both tables. 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 ignores incoming rows
that don’t have an acceptable primary key. Based on the setting of the <code class="docutils literal notranslate"><span class="pre">allow_partial_pks</span></code>
flag on <a class="reference internal" href="../orm/mapping_api.html#sqlalchemy.orm.mapper" title="sqlalchemy.orm.mapper"><code class="xref py py-func docutils literal notranslate"><span class="pre">mapper()</span></code></a>, a primary key is accepted if the value has at least one non-NULL
value, or alternatively if the value has no NULL values. See <code class="docutils literal notranslate"><span class="pre">allow_partial_pks</span></code>
at <a class="reference internal" href="../orm/mapping_api.html#sqlalchemy.orm.mapper" title="sqlalchemy.orm.mapper"><code class="xref py py-func docutils literal notranslate"><span class="pre">mapper()</span></code></a>.</p>
</div>
<div class="section" id="i-m-using-joinedload-or-lazy-false-to-create-a-join-outer-join-and-sqlalchemy-is-not-constructing-the-correct-query-when-i-try-to-add-a-where-order-by-limit-etc-which-relies-upon-the-outer-join">
<h2>I’m using <code class="docutils literal notranslate"><span class="pre">joinedload()</span></code> or <code class="docutils literal notranslate"><span class="pre">lazy=False</span></code> to create a JOIN/OUTER JOIN and SQLAlchemy is not constructing the correct query when I try to add a WHERE, ORDER BY, LIMIT, etc. (which relies upon the (OUTER) JOIN)<a class="headerlink" href="#i-m-using-joinedload-or-lazy-false-to-create-a-join-outer-join-and-sqlalchemy-is-not-constructing-the-correct-query-when-i-try-to-add-a-where-order-by-limit-etc-which-relies-upon-the-outer-join" title="Permalink to this headline">¶</a></h2>
<p>The joins generated by joined eager loading are only used to fully load related
collections, and are designed to have no impact on the primary results of the query.
Since they are anonymously aliased, they cannot be referenced directly.</p>
<p>For detail on this behavior, see <a class="reference internal" href="../orm/loading_relationships.html#zen-of-eager-loading"><span class="std std-ref">The Zen of Joined Eager Loading</span></a>.</p>
</div>
<div class="section" id="query-has-no-len-why-not">
<h2>Query has no <code class="docutils literal notranslate"><span class="pre">__len__()</span></code>, why not?<a class="headerlink" href="#query-has-no-len-why-not" title="Permalink to this headline">¶</a></h2>
<p>The Python <code class="docutils literal notranslate"><span class="pre">__len__()</span></code> magic method applied to an object allows the <code class="docutils literal notranslate"><span class="pre">len()</span></code>
builtin to be used to determine the length of the collection. It’s intuitive
that a SQL query object would link <code class="docutils literal notranslate"><span class="pre">__len__()</span></code> to the <a class="reference internal" href="../orm/query.html#sqlalchemy.orm.query.Query.count" title="sqlalchemy.orm.query.Query.count"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Query.count()</span></code></a>
method, which emits a <cite>SELECT COUNT</cite>. The reason this is not possible is
because evaluating the query as a list would incur two SQL calls instead of
one:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="k">class</span> <span class="nc">Iterates</span><span class="p">(</span><span class="nb">object</span><span class="p">):</span>
<span class="k">def</span> <span class="nf">__len__</span><span class="p">(</span><span class="bp">self</span><span class="p">):</span>
<span class="nb">print</span><span class="p">(</span><span class="s2">"LEN!"</span><span class="p">)</span>
<span class="k">return</span> <span class="mi">5</span>
<span class="k">def</span> <span class="nf">__iter__</span><span class="p">(</span><span class="bp">self</span><span class="p">):</span>
<span class="nb">print</span><span class="p">(</span><span class="s2">"ITER!"</span><span class="p">)</span>
<span class="k">return</span> <span class="nb">iter</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> <span class="mi">4</span><span class="p">,</span> <span class="mi">5</span><span class="p">])</span>
<span class="nb">list</span><span class="p">(</span><span class="n">Iterates</span><span class="p">())</span></pre></div>
</div>
<p>output:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span>ITER!
LEN!</pre></div>
</div>
</div>
<div class="section" id="how-do-i-use-textual-sql-with-orm-queries">
<h2>How Do I use Textual SQL with ORM Queries?<a class="headerlink" href="#how-do-i-use-textual-sql-with-orm-queries" title="Permalink to this headline">¶</a></h2>
<p>See:</p>
<ul class="simple">
<li><p><a class="reference internal" href="../orm/tutorial.html#orm-tutorial-literal-sql"><span class="std std-ref">Using Textual SQL</span></a> - Ad-hoc textual blocks with <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></p></li>
<li><p><a class="reference internal" href="../orm/persistence_techniques.html#session-sql-expressions"><span class="std std-ref">Using SQL Expressions with Sessions</span></a> - Using <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> with textual SQL directly.</p></li>
</ul>
</div>
<div class="section" id="i-m-calling-session-delete-myobject-and-it-isn-t-removed-from-the-parent-collection">
<h2>I’m calling <code class="docutils literal notranslate"><span class="pre">Session.delete(myobject)</span></code> and it isn’t removed from the parent collection!<a class="headerlink" href="#i-m-calling-session-delete-myobject-and-it-isn-t-removed-from-the-parent-collection" title="Permalink to this headline">¶</a></h2>
<p>See <a class="reference internal" href="../orm/session_basics.html#session-deleting-from-collections"><span class="std std-ref">Deleting Objects Referenced from Collections and Scalar Relationships</span></a> for a description of this behavior.</p>
</div>
<div class="section" id="why-isn-t-my-init-called-when-i-load-objects">
<h2>why isn’t my <code class="docutils literal notranslate"><span class="pre">__init__()</span></code> called when I load objects?<a class="headerlink" href="#why-isn-t-my-init-called-when-i-load-objects" title="Permalink to this headline">¶</a></h2>
<p>See <a class="reference internal" href="../orm/constructors.html#mapping-constructors"><span class="std std-ref">Constructors and Object Initialization</span></a> for a description of this behavior.</p>
</div>
<div class="section" id="how-do-i-use-on-delete-cascade-with-sa-s-orm">
<h2>how do I use ON DELETE CASCADE with SA’s ORM?<a class="headerlink" href="#how-do-i-use-on-delete-cascade-with-sa-s-orm" title="Permalink to this headline">¶</a></h2>
<p>SQLAlchemy will always issue UPDATE or DELETE statements for dependent
rows which are currently loaded in 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>. For rows which
are not loaded, it will by default issue SELECT statements to load
those rows and update/delete those as well; in other words it assumes
there is no ON DELETE CASCADE configured.
To configure SQLAlchemy to cooperate with ON DELETE CASCADE, see
<a class="reference internal" href="../orm/collections.html#passive-deletes"><span class="std std-ref">Using Passive Deletes</span></a>.</p>
</div>
<div class="section" id="i-set-the-foo-id-attribute-on-my-instance-to-7-but-the-foo-attribute-is-still-none-shouldn-t-it-have-loaded-foo-with-id-7">
<h2>I set the “foo_id” attribute on my instance to “7”, but the “foo” attribute is still <code class="docutils literal notranslate"><span class="pre">None</span></code> - shouldn’t it have loaded Foo with id #7?<a class="headerlink" href="#i-set-the-foo-id-attribute-on-my-instance-to-7-but-the-foo-attribute-is-still-none-shouldn-t-it-have-loaded-foo-with-id-7" title="Permalink to this headline">¶</a></h2>
<p>The ORM is not constructed in such a way as to support
immediate population of relationships driven from foreign
key attribute changes - instead, it is designed to work the
other way around - foreign key attributes are handled by the
ORM behind the scenes, the end user sets up object
relationships naturally. Therefore, the recommended way to
set <code class="docutils literal notranslate"><span class="pre">o.foo</span></code> is to do just that - set it!:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">foo</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">Foo</span><span class="p">)</span><span class="o">.</span><span class="n">get</span><span class="p">(</span><span class="mi">7</span><span class="p">)</span>
<span class="n">o</span><span class="o">.</span><span class="n">foo</span> <span class="o">=</span> <span class="n">foo</span>
<span class="n">Session</span><span class="o">.</span><span class="n">commit</span><span class="p">()</span></pre></div>
</div>
<p>Manipulation of foreign key attributes is of course entirely legal. However,
setting a foreign-key attribute to a new value currently does not trigger
an “expire” event of the <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> in which it’s involved. This means
that for the following sequence:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">o</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">SomeClass</span><span class="p">)</span><span class="o">.</span><span class="n">first</span><span class="p">()</span>
<span class="k">assert</span> <span class="n">o</span><span class="o">.</span><span class="n">foo</span> <span class="ow">is</span> <span class="kc">None</span> <span class="c1"># accessing an un-set attribute sets it to None</span>
<span class="n">o</span><span class="o">.</span><span class="n">foo_id</span> <span class="o">=</span> <span class="mi">7</span></pre></div>
</div>
<p><code class="docutils literal notranslate"><span class="pre">o.foo</span></code> is initialized to <code class="docutils literal notranslate"><span class="pre">None</span></code> when we first accessed it. Setting
<code class="docutils literal notranslate"><span class="pre">o.foo_id</span> <span class="pre">=</span> <span class="pre">7</span></code> will have the value of “7” as pending, but no flush
has occurred - so <code class="docutils literal notranslate"><span class="pre">o.foo</span></code> is still <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="c1"># attribute is already set to None, has not been</span>
<span class="c1"># reconciled with o.foo_id = 7 yet</span>
<span class="k">assert</span> <span class="n">o</span><span class="o">.</span><span class="n">foo</span> <span class="ow">is</span> <span class="kc">None</span></pre></div>
</div>
<p>For <code class="docutils literal notranslate"><span class="pre">o.foo</span></code> to load based on the foreign key mutation is usually achieved
naturally after the commit, which both flushes the new foreign key value
and expires all state:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">Session</span><span class="o">.</span><span class="n">commit</span><span class="p">()</span> <span class="c1"># expires all attributes</span>
<span class="n">foo_7</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">Foo</span><span class="p">)</span><span class="o">.</span><span class="n">get</span><span class="p">(</span><span class="mi">7</span><span class="p">)</span>
<span class="k">assert</span> <span class="n">o</span><span class="o">.</span><span class="n">foo</span> <span class="ow">is</span> <span class="n">foo_7</span> <span class="c1"># o.foo lazyloads on access</span></pre></div>
</div>
<p>A more minimal operation is to expire the attribute individually - this can
be performed for any <a class="reference internal" href="../glossary.html#term-persistent"><span class="xref std std-term">persistent</span></a> object using <a class="reference internal" href="../orm/session_api.html#sqlalchemy.orm.session.Session.expire" title="sqlalchemy.orm.session.Session.expire"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Session.expire()</span></code></a>:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">o</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">SomeClass</span><span class="p">)</span><span class="o">.</span><span class="n">first</span><span class="p">()</span>
<span class="n">o</span><span class="o">.</span><span class="n">foo_id</span> <span class="o">=</span> <span class="mi">7</span>
<span class="n">Session</span><span class="o">.</span><span class="n">expire</span><span class="p">(</span><span class="n">o</span><span class="p">,</span> <span class="p">[</span><span class="s1">'foo'</span><span class="p">])</span> <span class="c1"># object must be persistent for this</span>
<span class="n">foo_7</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">Foo</span><span class="p">)</span><span class="o">.</span><span class="n">get</span><span class="p">(</span><span class="mi">7</span><span class="p">)</span>
<span class="k">assert</span> <span class="n">o</span><span class="o">.</span><span class="n">foo</span> <span class="ow">is</span> <span class="n">foo_7</span> <span class="c1"># o.foo lazyloads on access</span></pre></div>
</div>
<p>Note that if the object is not persistent but present in 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>,
it’s known as <a class="reference internal" href="../glossary.html#term-pending"><span class="xref std std-term">pending</span></a>. This means the row for the object has not been
INSERTed into the database yet. For such an object, setting <code class="docutils literal notranslate"><span class="pre">foo_id</span></code> does not
have meaning until the row is inserted; otherwise there is no row yet:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">new_obj</span> <span class="o">=</span> <span class="n">SomeClass</span><span class="p">()</span>
<span class="n">new_obj</span><span class="o">.</span><span class="n">foo_id</span> <span class="o">=</span> <span class="mi">7</span>
<span class="n">Session</span><span class="o">.</span><span class="n">add</span><span class="p">(</span><span class="n">new_obj</span><span class="p">)</span>
<span class="c1"># accessing an un-set attribute sets it to None</span>
<span class="k">assert</span> <span class="n">new_obj</span><span class="o">.</span><span class="n">foo</span> <span class="ow">is</span> <span class="kc">None</span>
<span class="n">Session</span><span class="o">.</span><span class="n">flush</span><span class="p">()</span> <span class="c1"># emits INSERT</span>
<span class="c1"># expire this because we already set .foo to None</span>
<span class="n">Session</span><span class="o">.</span><span class="n">expire</span><span class="p">(</span><span class="n">o</span><span class="p">,</span> <span class="p">[</span><span class="s1">'foo'</span><span class="p">])</span>
<span class="k">assert</span> <span class="n">new_obj</span><span class="o">.</span><span class="n">foo</span> <span class="ow">is</span> <span class="n">foo_7</span> <span class="c1"># now it loads</span></pre></div>
</div>
<div class="topic">
<p class="topic-title first">Attribute loading for non-persistent objects</p>
<p>One variant on the “pending” behavior above is if we use the flag
<code class="docutils literal notranslate"><span class="pre">load_on_pending</span></code> on <a class="reference internal" href="../orm/relationship_api.html#sqlalchemy.orm.relationship" title="sqlalchemy.orm.relationship"><code class="xref py py-func docutils literal notranslate"><span class="pre">relationship()</span></code></a>. When this flag is set, the
lazy loader will emit for <code class="docutils literal notranslate"><span class="pre">new_obj.foo</span></code> before the INSERT proceeds; another
variant of this is to use the <a class="reference internal" href="../orm/session_api.html#sqlalchemy.orm.session.Session.enable_relationship_loading" title="sqlalchemy.orm.session.Session.enable_relationship_loading"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Session.enable_relationship_loading()</span></code></a>
method, which can “attach” an object to a <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> in such a way that
many-to-one relationships load as according to foreign key attributes
regardless of the object being in any particular state.
Both techniques are <strong>not recommended for general use</strong>; they were added to suit
specific programming scenarios encountered by users which involve the repurposing
of the ORM’s usual object states.</p>
</div>
<p>The recipe <a class="reference external" href="http://www.sqlalchemy.org/trac/wiki/UsageRecipes/ExpireRelationshipOnFKChange">ExpireRelationshipOnFKChange</a> features an example using SQLAlchemy events
in order to coordinate the setting of foreign key attributes with many-to-one
relationships.</p>
</div>
<div class="section" id="how-do-i-walk-all-objects-that-are-related-to-a-given-object">
<span id="faq-walk-objects"></span><h2>How do I walk all objects that are related to a given object?<a class="headerlink" href="#how-do-i-walk-all-objects-that-are-related-to-a-given-object" title="Permalink to this headline">¶</a></h2>
<p>An object that has other objects related to it will correspond to the
<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> constructs set up between mappers. This code fragment will
iterate all the objects, correcting for cycles as well:</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">inspect</span>
<span class="k">def</span> <span class="nf">walk</span><span class="p">(</span><span class="n">obj</span><span class="p">):</span>
<span class="n">deque</span> <span class="o">=</span> <span class="p">[</span><span class="n">obj</span><span class="p">]</span>
<span class="n">seen</span> <span class="o">=</span> <span class="nb">set</span><span class="p">()</span>
<span class="k">while</span> <span class="n">deque</span><span class="p">:</span>
<span class="n">obj</span> <span class="o">=</span> <span class="n">deque</span><span class="o">.</span><span class="n">pop</span><span class="p">(</span><span class="mi">0</span><span class="p">)</span>
<span class="k">if</span> <span class="n">obj</span> <span class="ow">in</span> <span class="n">seen</span><span class="p">:</span>
<span class="k">continue</span>
<span class="k">else</span><span class="p">:</span>
<span class="n">seen</span><span class="o">.</span><span class="n">add</span><span class="p">(</span><span class="n">obj</span><span class="p">)</span>
<span class="k">yield</span> <span class="n">obj</span>
<span class="n">insp</span> <span class="o">=</span> <span class="n">inspect</span><span class="p">(</span><span class="n">obj</span><span class="p">)</span>
<span class="k">for</span> <span class="n">relationship</span> <span class="ow">in</span> <span class="n">insp</span><span class="o">.</span><span class="n">mapper</span><span class="o">.</span><span class="n">relationships</span><span class="p">:</span>
<span class="n">related</span> <span class="o">=</span> <span class="nb">getattr</span><span class="p">(</span><span class="n">obj</span><span class="p">,</span> <span class="n">relationship</span><span class="o">.</span><span class="n">key</span><span class="p">)</span>
<span class="k">if</span> <span class="n">relationship</span><span class="o">.</span><span class="n">uselist</span><span class="p">:</span>
<span class="n">deque</span><span class="o">.</span><span class="n">extend</span><span class="p">(</span><span class="n">related</span><span class="p">)</span>
<span class="k">elif</span> <span class="n">related</span> <span class="ow">is</span> <span class="ow">not</span> <span class="kc">None</span><span class="p">:</span>
<span class="n">deque</span><span class="o">.</span><span class="n">append</span><span class="p">(</span><span class="n">related</span><span class="p">)</span></pre></div>
</div>
<p>The function can be demonstrated as follows:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">Base</span> <span class="o">=</span> <span class="n">declarative_base</span><span class="p">()</span>
<span class="k">class</span> <span class="nc">A</span><span class="p">(</span><span class="n">Base</span><span class="p">):</span>
<span class="n">__tablename__</span> <span class="o">=</span> <span class="s1">'a'</span>
<span class="nb">id</span> <span class="o">=</span> <span class="n">Column</span><span class="p">(</span><span class="n">Integer</span><span class="p">,</span> <span class="n">primary_key</span><span class="o">=</span><span class="kc">True</span><span class="p">)</span>
<span class="n">bs</span> <span class="o">=</span> <span class="n">relationship</span><span class="p">(</span><span class="s2">"B"</span><span class="p">,</span> <span class="n">backref</span><span class="o">=</span><span class="s2">"a"</span><span class="p">)</span>
<span class="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">c_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">'c.id'</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="n">backref</span><span class="o">=</span><span class="s2">"bs"</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">a1</span> <span class="o">=</span> <span class="n">A</span><span class="p">(</span><span class="n">bs</span><span class="o">=</span><span class="p">[</span><span class="n">B</span><span class="p">(),</span> <span class="n">B</span><span class="p">(</span><span class="n">c</span><span class="o">=</span><span class="n">C</span><span class="p">())])</span>
<span class="k">for</span> <span class="n">obj</span> <span class="ow">in</span> <span class="n">walk</span><span class="p">(</span><span class="n">a1</span><span class="p">):</span>
<span class="nb">print</span><span class="p">(</span><span class="n">obj</span><span class="p">)</span></pre></div>
</div>
<p>Output:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="o"><</span><span class="n">__main__</span><span class="o">.</span><span class="n">A</span> <span class="nb">object</span> <span class="n">at</span> <span class="mh">0x10303b190</span><span class="o">></span>
<span class="o"><</span><span class="n">__main__</span><span class="o">.</span><span class="n">B</span> <span class="nb">object</span> <span class="n">at</span> <span class="mh">0x103025210</span><span class="o">></span>
<span class="o"><</span><span class="n">__main__</span><span class="o">.</span><span class="n">B</span> <span class="nb">object</span> <span class="n">at</span> <span class="mh">0x10303b0d0</span><span class="o">></span>
<span class="o"><</span><span class="n">__main__</span><span class="o">.</span><span class="n">C</span> <span class="nb">object</span> <span class="n">at</span> <span class="mh">0x103025490</span><span class="o">></span></pre></div>
</div>
</div>
<div class="section" id="is-there-a-way-to-automagically-have-only-unique-keywords-or-other-kinds-of-objects-without-doing-a-query-for-the-keyword-and-getting-a-reference-to-the-row-containing-that-keyword">
<h2>Is there a way to automagically have only unique keywords (or other kinds of objects) without doing a query for the keyword and getting a reference to the row containing that keyword?<a class="headerlink" href="#is-there-a-way-to-automagically-have-only-unique-keywords-or-other-kinds-of-objects-without-doing-a-query-for-the-keyword-and-getting-a-reference-to-the-row-containing-that-keyword" title="Permalink to this headline">¶</a></h2>
<p>When people read the many-to-many example in the docs, they get hit with the
fact that if you create the same <code class="docutils literal notranslate"><span class="pre">Keyword</span></code> twice, it gets put in the DB twice.
Which is somewhat inconvenient.</p>
<p>This <a class="reference external" href="http://www.sqlalchemy.org/trac/wiki/UsageRecipes/UniqueObject">UniqueObject</a> recipe was created to address this issue.</p>
</div>
<div class="section" id="why-does-post-update-emit-update-in-addition-to-the-first-update">
<span id="faq-post-update-update"></span><h2>Why does post_update emit UPDATE in addition to the first UPDATE?<a class="headerlink" href="#why-does-post-update-emit-update-in-addition-to-the-first-update" title="Permalink to this headline">¶</a></h2>
<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. While the primary purpose of this
UPDATE statement is that it pairs up with an INSERT or DELETE of that row, so
that it can post-set or pre-unset a foreign key reference in order to break a
cycle with a mutually dependent foreign key, it currently is also bundled as a
second UPDATE that emits when the target row itself is subject to an UPDATE.
In this case, the UPDATE emitted by post_update is <em>usually</em> unnecessary
and will often appear wasteful.</p>
<p>However, some research into trying to remove this “UPDATE / UPDATE” behavior
reveals that major changes to the unit of work process would need to occur not
just throughout the post_update implementation, but also in areas that aren’t
related to post_update for this to work, in that the order of operations would
need to be reversed on the non-post_update side in some cases, which in turn
can impact other cases, such as correctly handling an UPDATE of a referenced
primary key value (see <a class="reference external" href="http://www.sqlalchemy.org/trac/ticket/1063">#1063</a> for a proof of concept).</p>
<p>The answer is that “post_update” is used to break a cycle between two
mutually dependent foreign keys, and to have this cycle breaking be limited
to just INSERT/DELETE of the target table implies that the ordering of UPDATE
statements elsewhere would need to be liberalized, leading to breakage
in other edge cases.</p>
</div>
</div>
</div>
</div>
<div id="docs-bottom-navigation" class="docs-navigation-links, withsidebar">
Previous:
<a href="performance.html" title="previous chapter">Performance</a>
Next:
<a href="../errors.html" title="next chapter">Error Messages</a>
<div id="docs-copyright">
© <a href="../copyright.html">Copyright</a> 2007-2019, the SQLAlchemy authors and contributors.
Created using <a href="http://sphinx.pocoo.org/">Sphinx</a> 2.0.1.
</div>
</div>
</div>
<script type="text/javascript">
var DOCUMENTATION_OPTIONS = {
URL_ROOT: '../',
VERSION: '1.2.19',
COLLAPSE_MODINDEX: false,
FILE_SUFFIX: '.html'
};
</script>
<script type="text/javascript" id="documentation_options" data-url_root="../" src="../_static/documentation_options.js"></script>
<!-- begin iterate through sphinx environment script_files -->
<script type="text/javascript" src="../_static/jquery.js"></script>
<script type="text/javascript" src="../_static/underscore.js"></script>
<script type="text/javascript" src="../_static/doctools.js"></script>
<script type="text/javascript" src="../_static/language_data.js"></script>
<!-- end iterate through sphinx environment script_files -->
<script type="text/javascript" src="../_static/detectmobile.js"></script>
<script type="text/javascript" src="../_static/init.js"></script>
</body>
</html>
distrib
>
Mageia
>
7
>
armv7hl
>
media
>
core-updates
>
by-pkgid
>
b0b6ffab06cbeede296e36ce94734bf8
>
files
>
801
