<!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>
Relationship Configuration
—
SQLAlchemy 0.8 Documentation
</title>
<!-- begin iterate through SQLA + sphinx environment css_files -->
<link rel="stylesheet" href="../_static/pygments.css" type="text/css" />
<link rel="stylesheet" href="../_static/docs.css" type="text/css" />
<link rel="stylesheet" href="../_static/changelog.css" type="text/css" />
<link rel="stylesheet" href="../_static/sphinx_paramlinks.css" type="text/css" />
<!-- end iterate through SQLA + sphinx environment css_files -->
<!-- begin layout.mako headers -->
<script type="text/javascript">
var DOCUMENTATION_OPTIONS = {
URL_ROOT: '../',
VERSION: '0.8.7',
COLLAPSE_MODINDEX: false,
FILE_SUFFIX: '.html'
};
</script>
<!-- begin iterate through sphinx environment script_files -->
<script type="text/javascript" src="../_static/jquery.js"></script>
<script type="text/javascript" src="../_static/underscore.js"></script>
<script type="text/javascript" src="../_static/doctools.js"></script>
<!-- end iterate through sphinx environment script_files -->
<script type="text/javascript" src="../_static/detectmobile.js"></script>
<script type="text/javascript" src="../_static/init.js"></script>
<link rel="index" title="Index" href="../genindex.html" />
<link rel="search" title="Search" href="../search.html" />
<link rel="copyright" title="Copyright" href="../copyright.html" />
<link rel="top" title="SQLAlchemy 0.8 Documentation" href="../index.html" />
<link rel="up" title="SQLAlchemy ORM" href="index.html" />
<link rel="next" title="Collection Configuration and Techniques" href="collections.html" />
<link rel="prev" title="Mapper Configuration" href="mapper_config.html" />
<!-- end layout.mako headers -->
</head>
<body>
<div id="docs-container">
<div id="docs-top-navigation-container" class="body-background">
<div id="docs-header">
<div id="docs-version-header">
Release: <span class="version-num">0.8.7</span> | Release Date: July 22, 2014
</div>
<h1>SQLAlchemy 0.8 Documentation</h1>
</div>
</div>
<div id="docs-body-container">
<div id="fixed-sidebar" class="withsidebar">
<div id="docs-sidebar-popout">
<h3><a href="../index.html">SQLAlchemy 0.8 Documentation</a></h3>
<p id="sidebar-paginate">
<a href="index.html" title="SQLAlchemy ORM">Up</a> |
<a href="mapper_config.html" title="Mapper Configuration">Prev</a> |
<a href="collections.html" title="Collection Configuration and Techniques">Next</a>
</p>
<p id="sidebar-topnav">
<a href="../index.html">Contents</a> |
<a href="../genindex.html">Index</a>
</p>
<div id="sidebar-search">
<form class="search" action="../search.html" method="get">
<input type="text" name="q" size="12" /> <input type="submit" value="Search" />
<input type="hidden" name="check_keywords" value="yes" />
<input type="hidden" name="area" value="default" />
</form>
</div>
</div>
<div id="docs-sidebar">
<h3><a href="#">
Relationship Configuration
</a></h3>
<ul>
<li><a class="reference internal" href="#">Relationship Configuration</a><ul>
<li><a class="reference internal" href="#basic-relational-patterns">Basic Relational Patterns</a><ul>
<li><a class="reference internal" href="#one-to-many">One To Many</a></li>
<li><a class="reference internal" href="#many-to-one">Many To One</a></li>
<li><a class="reference internal" href="#one-to-one">One To One</a></li>
<li><a class="reference internal" href="#many-to-many">Many To Many</a><ul>
<li><a class="reference internal" href="#deleting-rows-from-the-many-to-many-table">Deleting Rows from the Many to Many Table</a></li>
</ul>
</li>
<li><a class="reference internal" href="#association-object">Association Object</a></li>
</ul>
</li>
<li><a class="reference internal" href="#adjacency-list-relationships">Adjacency List Relationships</a><ul>
<li><a class="reference internal" href="#composite-adjacency-lists">Composite Adjacency Lists</a></li>
<li><a class="reference internal" href="#self-referential-query-strategies">Self-Referential Query Strategies</a></li>
<li><a class="reference internal" href="#configuring-self-referential-eager-loading">Configuring Self-Referential Eager Loading</a></li>
</ul>
</li>
<li><a class="reference internal" href="#linking-relationships-with-backref">Linking Relationships with Backref</a><ul>
<li><a class="reference internal" href="#backref-arguments">Backref Arguments</a></li>
<li><a class="reference internal" href="#one-way-backrefs">One Way Backrefs</a></li>
</ul>
</li>
<li><a class="reference internal" href="#configuring-how-relationship-joins">Configuring how Relationship Joins</a><ul>
<li><a class="reference internal" href="#handling-multiple-join-paths">Handling Multiple Join Paths</a></li>
<li><a class="reference internal" href="#specifying-alternate-join-conditions">Specifying Alternate Join Conditions</a></li>
<li><a class="reference internal" href="#creating-custom-foreign-conditions">Creating Custom Foreign Conditions</a></li>
<li><a class="reference internal" href="#self-referential-many-to-many-relationship">Self-Referential Many-to-Many Relationship</a></li>
<li><a class="reference internal" href="#building-query-enabled-properties">Building Query-Enabled Properties</a></li>
</ul>
</li>
<li><a class="reference internal" href="#rows-that-point-to-themselves-mutually-dependent-rows">Rows that point to themselves / Mutually Dependent Rows</a></li>
<li><a class="reference internal" href="#mutable-primary-keys-update-cascades">Mutable Primary Keys / Update Cascades</a></li>
<li><a class="reference internal" href="#relationships-api">Relationships API</a></li>
</ul>
</li>
</ul>
</div>
</div>
<div id="docs-body" class="withsidebar" >
<span class="target" id="module-sqlalchemy.orm"></span><div class="section" id="relationship-configuration">
<span id="relationship-config-toplevel"></span><h1>Relationship Configuration<a class="headerlink" href="#relationship-configuration" title="Permalink to this headline">¶</a></h1>
<p>This section describes the <a class="reference internal" href="#sqlalchemy.orm.relationship" title="sqlalchemy.orm.relationship"><tt class="xref py py-func docutils literal"><span class="pre">relationship()</span></tt></a> function and in depth discussion
of its usage. The reference material here continues into the next section,
<a class="reference internal" href="collections.html"><em>Collection Configuration and Techniques</em></a>, which has additional detail on configuration
of collections via <a class="reference internal" href="#sqlalchemy.orm.relationship" title="sqlalchemy.orm.relationship"><tt class="xref py py-func docutils literal"><span class="pre">relationship()</span></tt></a>.</p>
<div class="section" id="basic-relational-patterns">
<span id="relationship-patterns"></span><h2>Basic Relational Patterns<a class="headerlink" href="#basic-relational-patterns" title="Permalink to this headline">¶</a></h2>
<p>A quick walkthrough of the basic relational patterns.</p>
<p>The imports used for each of the following sections is as follows:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="kn">from</span> <span class="nn">sqlalchemy</span> <span class="kn">import</span> <span class="n">Table</span><span class="p">,</span> <span class="n">Column</span><span class="p">,</span> <span class="n">Integer</span><span class="p">,</span> <span class="n">ForeignKey</span>
<span class="kn">from</span> <span class="nn">sqlalchemy.orm</span> <span class="kn">import</span> <span class="n">relationship</span><span class="p">,</span> <span class="n">backref</span>
<span class="kn">from</span> <span class="nn">sqlalchemy.ext.declarative</span> <span class="kn">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></pre></div>
</div>
<div class="section" id="one-to-many">
<h3>One To Many<a class="headerlink" href="#one-to-many" title="Permalink to this headline">¶</a></h3>
<p>A one to many relationship places a foreign key on the child table referencing
the parent. <a class="reference internal" href="#sqlalchemy.orm.relationship" title="sqlalchemy.orm.relationship"><tt class="xref py py-func docutils literal"><span class="pre">relationship()</span></tt></a> is then specified on the parent, as referencing
a collection of items represented by the child:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="k">class</span> <span class="nc">Parent</span><span class="p">(</span><span class="n">Base</span><span class="p">):</span>
<span class="n">__tablename__</span> <span class="o">=</span> <span class="s">'parent'</span>
<span class="nb">id</span> <span class="o">=</span> <span class="n">Column</span><span class="p">(</span><span class="n">Integer</span><span class="p">,</span> <span class="n">primary_key</span><span class="o">=</span><span class="bp">True</span><span class="p">)</span>
<span class="n">children</span> <span class="o">=</span> <span class="n">relationship</span><span class="p">(</span><span class="s">"Child"</span><span class="p">)</span>
<span class="k">class</span> <span class="nc">Child</span><span class="p">(</span><span class="n">Base</span><span class="p">):</span>
<span class="n">__tablename__</span> <span class="o">=</span> <span class="s">'child'</span>
<span class="nb">id</span> <span class="o">=</span> <span class="n">Column</span><span class="p">(</span><span class="n">Integer</span><span class="p">,</span> <span class="n">primary_key</span><span class="o">=</span><span class="bp">True</span><span class="p">)</span>
<span class="n">parent_id</span> <span class="o">=</span> <span class="n">Column</span><span class="p">(</span><span class="n">Integer</span><span class="p">,</span> <span class="n">ForeignKey</span><span class="p">(</span><span class="s">'parent.id'</span><span class="p">))</span></pre></div>
</div>
<p>To establish a bidirectional relationship in one-to-many, where the “reverse”
side is a many to one, specify the <a class="reference internal" href="#sqlalchemy.orm.relationship.params.backref" title="sqlalchemy.orm.relationship"><tt class="xref py py-paramref docutils literal"><span class="pre">backref</span></tt></a> option:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="k">class</span> <span class="nc">Parent</span><span class="p">(</span><span class="n">Base</span><span class="p">):</span>
<span class="n">__tablename__</span> <span class="o">=</span> <span class="s">'parent'</span>
<span class="nb">id</span> <span class="o">=</span> <span class="n">Column</span><span class="p">(</span><span class="n">Integer</span><span class="p">,</span> <span class="n">primary_key</span><span class="o">=</span><span class="bp">True</span><span class="p">)</span>
<span class="n">children</span> <span class="o">=</span> <span class="n">relationship</span><span class="p">(</span><span class="s">"Child"</span><span class="p">,</span> <span class="n">backref</span><span class="o">=</span><span class="s">"parent"</span><span class="p">)</span>
<span class="k">class</span> <span class="nc">Child</span><span class="p">(</span><span class="n">Base</span><span class="p">):</span>
<span class="n">__tablename__</span> <span class="o">=</span> <span class="s">'child'</span>
<span class="nb">id</span> <span class="o">=</span> <span class="n">Column</span><span class="p">(</span><span class="n">Integer</span><span class="p">,</span> <span class="n">primary_key</span><span class="o">=</span><span class="bp">True</span><span class="p">)</span>
<span class="n">parent_id</span> <span class="o">=</span> <span class="n">Column</span><span class="p">(</span><span class="n">Integer</span><span class="p">,</span> <span class="n">ForeignKey</span><span class="p">(</span><span class="s">'parent.id'</span><span class="p">))</span></pre></div>
</div>
<p><tt class="docutils literal"><span class="pre">Child</span></tt> will get a <tt class="docutils literal"><span class="pre">parent</span></tt> attribute with many-to-one semantics.</p>
</div>
<div class="section" id="many-to-one">
<h3>Many To One<a class="headerlink" href="#many-to-one" title="Permalink to this headline">¶</a></h3>
<p>Many to one places a foreign key in the parent table referencing the child.
<a class="reference internal" href="#sqlalchemy.orm.relationship" title="sqlalchemy.orm.relationship"><tt class="xref py py-func docutils literal"><span class="pre">relationship()</span></tt></a> is declared on the parent, where a new scalar-holding
attribute will be created:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="k">class</span> <span class="nc">Parent</span><span class="p">(</span><span class="n">Base</span><span class="p">):</span>
<span class="n">__tablename__</span> <span class="o">=</span> <span class="s">'parent'</span>
<span class="nb">id</span> <span class="o">=</span> <span class="n">Column</span><span class="p">(</span><span class="n">Integer</span><span class="p">,</span> <span class="n">primary_key</span><span class="o">=</span><span class="bp">True</span><span class="p">)</span>
<span class="n">child_id</span> <span class="o">=</span> <span class="n">Column</span><span class="p">(</span><span class="n">Integer</span><span class="p">,</span> <span class="n">ForeignKey</span><span class="p">(</span><span class="s">'child.id'</span><span class="p">))</span>
<span class="n">child</span> <span class="o">=</span> <span class="n">relationship</span><span class="p">(</span><span class="s">"Child"</span><span class="p">)</span>
<span class="k">class</span> <span class="nc">Child</span><span class="p">(</span><span class="n">Base</span><span class="p">):</span>
<span class="n">__tablename__</span> <span class="o">=</span> <span class="s">'child'</span>
<span class="nb">id</span> <span class="o">=</span> <span class="n">Column</span><span class="p">(</span><span class="n">Integer</span><span class="p">,</span> <span class="n">primary_key</span><span class="o">=</span><span class="bp">True</span><span class="p">)</span></pre></div>
</div>
<p>Bidirectional behavior is achieved by setting
<a class="reference internal" href="#sqlalchemy.orm.relationship.params.backref" title="sqlalchemy.orm.relationship"><tt class="xref py py-paramref docutils literal"><span class="pre">backref</span></tt></a> to the value <tt class="docutils literal"><span class="pre">"parents"</span></tt>, which
will place a one-to-many collection on the <tt class="docutils literal"><span class="pre">Child</span></tt> class:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="k">class</span> <span class="nc">Parent</span><span class="p">(</span><span class="n">Base</span><span class="p">):</span>
<span class="n">__tablename__</span> <span class="o">=</span> <span class="s">'parent'</span>
<span class="nb">id</span> <span class="o">=</span> <span class="n">Column</span><span class="p">(</span><span class="n">Integer</span><span class="p">,</span> <span class="n">primary_key</span><span class="o">=</span><span class="bp">True</span><span class="p">)</span>
<span class="n">child_id</span> <span class="o">=</span> <span class="n">Column</span><span class="p">(</span><span class="n">Integer</span><span class="p">,</span> <span class="n">ForeignKey</span><span class="p">(</span><span class="s">'child.id'</span><span class="p">))</span>
<span class="n">child</span> <span class="o">=</span> <span class="n">relationship</span><span class="p">(</span><span class="s">"Child"</span><span class="p">,</span> <span class="n">backref</span><span class="o">=</span><span class="s">"parents"</span><span class="p">)</span></pre></div>
</div>
</div>
<div class="section" id="one-to-one">
<span id="relationships-one-to-one"></span><h3>One To One<a class="headerlink" href="#one-to-one" title="Permalink to this headline">¶</a></h3>
<p>One To One is essentially a bidirectional relationship with a scalar
attribute on both sides. To achieve this, the <a class="reference internal" href="#sqlalchemy.orm.relationship.params.uselist" title="sqlalchemy.orm.relationship"><tt class="xref py py-paramref docutils literal"><span class="pre">uselist</span></tt></a> flag indicates
the placement of a scalar attribute instead of a collection on the “many” side
of the relationship. To convert one-to-many into one-to-one:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="k">class</span> <span class="nc">Parent</span><span class="p">(</span><span class="n">Base</span><span class="p">):</span>
<span class="n">__tablename__</span> <span class="o">=</span> <span class="s">'parent'</span>
<span class="nb">id</span> <span class="o">=</span> <span class="n">Column</span><span class="p">(</span><span class="n">Integer</span><span class="p">,</span> <span class="n">primary_key</span><span class="o">=</span><span class="bp">True</span><span class="p">)</span>
<span class="n">child</span> <span class="o">=</span> <span class="n">relationship</span><span class="p">(</span><span class="s">"Child"</span><span class="p">,</span> <span class="n">uselist</span><span class="o">=</span><span class="bp">False</span><span class="p">,</span> <span class="n">backref</span><span class="o">=</span><span class="s">"parent"</span><span class="p">)</span>
<span class="k">class</span> <span class="nc">Child</span><span class="p">(</span><span class="n">Base</span><span class="p">):</span>
<span class="n">__tablename__</span> <span class="o">=</span> <span class="s">'child'</span>
<span class="nb">id</span> <span class="o">=</span> <span class="n">Column</span><span class="p">(</span><span class="n">Integer</span><span class="p">,</span> <span class="n">primary_key</span><span class="o">=</span><span class="bp">True</span><span class="p">)</span>
<span class="n">parent_id</span> <span class="o">=</span> <span class="n">Column</span><span class="p">(</span><span class="n">Integer</span><span class="p">,</span> <span class="n">ForeignKey</span><span class="p">(</span><span class="s">'parent.id'</span><span class="p">))</span></pre></div>
</div>
<p>Or to turn a one-to-many backref into one-to-one, use the <a class="reference internal" href="#sqlalchemy.orm.backref" title="sqlalchemy.orm.backref"><tt class="xref py py-func docutils literal"><span class="pre">backref()</span></tt></a> function
to provide arguments for the reverse side:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="k">class</span> <span class="nc">Parent</span><span class="p">(</span><span class="n">Base</span><span class="p">):</span>
<span class="n">__tablename__</span> <span class="o">=</span> <span class="s">'parent'</span>
<span class="nb">id</span> <span class="o">=</span> <span class="n">Column</span><span class="p">(</span><span class="n">Integer</span><span class="p">,</span> <span class="n">primary_key</span><span class="o">=</span><span class="bp">True</span><span class="p">)</span>
<span class="n">child_id</span> <span class="o">=</span> <span class="n">Column</span><span class="p">(</span><span class="n">Integer</span><span class="p">,</span> <span class="n">ForeignKey</span><span class="p">(</span><span class="s">'child.id'</span><span class="p">))</span>
<span class="n">child</span> <span class="o">=</span> <span class="n">relationship</span><span class="p">(</span><span class="s">"Child"</span><span class="p">,</span> <span class="n">backref</span><span class="o">=</span><span class="n">backref</span><span class="p">(</span><span class="s">"parent"</span><span class="p">,</span> <span class="n">uselist</span><span class="o">=</span><span class="bp">False</span><span class="p">))</span>
<span class="k">class</span> <span class="nc">Child</span><span class="p">(</span><span class="n">Base</span><span class="p">):</span>
<span class="n">__tablename__</span> <span class="o">=</span> <span class="s">'child'</span>
<span class="nb">id</span> <span class="o">=</span> <span class="n">Column</span><span class="p">(</span><span class="n">Integer</span><span class="p">,</span> <span class="n">primary_key</span><span class="o">=</span><span class="bp">True</span><span class="p">)</span></pre></div>
</div>
</div>
<div class="section" id="many-to-many">
<span id="relationships-many-to-many"></span><h3>Many To Many<a class="headerlink" href="#many-to-many" title="Permalink to this headline">¶</a></h3>
<p>Many to Many adds an association table between two classes. The association
table is indicated by the <a class="reference internal" href="#sqlalchemy.orm.relationship.params.secondary" title="sqlalchemy.orm.relationship"><tt class="xref py py-paramref docutils literal"><span class="pre">secondary</span></tt></a> argument to
<a class="reference internal" href="#sqlalchemy.orm.relationship" title="sqlalchemy.orm.relationship"><tt class="xref py py-func docutils literal"><span class="pre">relationship()</span></tt></a>. Usually, the <a class="reference internal" href="../core/metadata.html#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal"><span class="pre">Table</span></tt></a> uses the <a class="reference internal" href="../core/metadata.html#sqlalchemy.schema.MetaData" title="sqlalchemy.schema.MetaData"><tt class="xref py py-class docutils literal"><span class="pre">MetaData</span></tt></a>
object associated with the declarative base class, so that the <a class="reference internal" href="../core/constraints.html#sqlalchemy.schema.ForeignKey" title="sqlalchemy.schema.ForeignKey"><tt class="xref py py-class docutils literal"><span class="pre">ForeignKey</span></tt></a>
directives can locate the remote tables with which to link:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">association_table</span> <span class="o">=</span> <span class="n">Table</span><span class="p">(</span><span class="s">'association'</span><span class="p">,</span> <span class="n">Base</span><span class="o">.</span><span class="n">metadata</span><span class="p">,</span>
<span class="n">Column</span><span class="p">(</span><span class="s">'left_id'</span><span class="p">,</span> <span class="n">Integer</span><span class="p">,</span> <span class="n">ForeignKey</span><span class="p">(</span><span class="s">'left.id'</span><span class="p">)),</span>
<span class="n">Column</span><span class="p">(</span><span class="s">'right_id'</span><span class="p">,</span> <span class="n">Integer</span><span class="p">,</span> <span class="n">ForeignKey</span><span class="p">(</span><span class="s">'right.id'</span><span class="p">))</span>
<span class="p">)</span>
<span class="k">class</span> <span class="nc">Parent</span><span class="p">(</span><span class="n">Base</span><span class="p">):</span>
<span class="n">__tablename__</span> <span class="o">=</span> <span class="s">'left'</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="bp">True</span><span class="p">)</span>
<span class="n">children</span> <span class="o">=</span> <span class="n">relationship</span><span class="p">(</span><span class="s">"Child"</span><span class="p">,</span>
<span class="n">secondary</span><span class="o">=</span><span class="n">association_table</span><span class="p">)</span>
<span class="k">class</span> <span class="nc">Child</span><span class="p">(</span><span class="n">Base</span><span class="p">):</span>
<span class="n">__tablename__</span> <span class="o">=</span> <span class="s">'right'</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="bp">True</span><span class="p">)</span></pre></div>
</div>
<p>For a bidirectional relationship, both sides of the relationship contain a
collection. The <a class="reference internal" href="#sqlalchemy.orm.relationship.params.backref" title="sqlalchemy.orm.relationship"><tt class="xref py py-paramref docutils literal"><span class="pre">backref</span></tt></a> keyword will automatically use
the same <a class="reference internal" href="#sqlalchemy.orm.relationship.params.secondary" title="sqlalchemy.orm.relationship"><tt class="xref py py-paramref docutils literal"><span class="pre">secondary</span></tt></a> argument for the reverse relationship:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">association_table</span> <span class="o">=</span> <span class="n">Table</span><span class="p">(</span><span class="s">'association'</span><span class="p">,</span> <span class="n">Base</span><span class="o">.</span><span class="n">metadata</span><span class="p">,</span>
<span class="n">Column</span><span class="p">(</span><span class="s">'left_id'</span><span class="p">,</span> <span class="n">Integer</span><span class="p">,</span> <span class="n">ForeignKey</span><span class="p">(</span><span class="s">'left.id'</span><span class="p">)),</span>
<span class="n">Column</span><span class="p">(</span><span class="s">'right_id'</span><span class="p">,</span> <span class="n">Integer</span><span class="p">,</span> <span class="n">ForeignKey</span><span class="p">(</span><span class="s">'right.id'</span><span class="p">))</span>
<span class="p">)</span>
<span class="k">class</span> <span class="nc">Parent</span><span class="p">(</span><span class="n">Base</span><span class="p">):</span>
<span class="n">__tablename__</span> <span class="o">=</span> <span class="s">'left'</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="bp">True</span><span class="p">)</span>
<span class="n">children</span> <span class="o">=</span> <span class="n">relationship</span><span class="p">(</span><span class="s">"Child"</span><span class="p">,</span>
<span class="n">secondary</span><span class="o">=</span><span class="n">association_table</span><span class="p">,</span>
<span class="n">backref</span><span class="o">=</span><span class="s">"parents"</span><span class="p">)</span>
<span class="k">class</span> <span class="nc">Child</span><span class="p">(</span><span class="n">Base</span><span class="p">):</span>
<span class="n">__tablename__</span> <span class="o">=</span> <span class="s">'right'</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="bp">True</span><span class="p">)</span></pre></div>
</div>
<p>The <a class="reference internal" href="#sqlalchemy.orm.relationship.params.secondary" title="sqlalchemy.orm.relationship"><tt class="xref py py-paramref docutils literal"><span class="pre">secondary</span></tt></a> argument of <a class="reference internal" href="#sqlalchemy.orm.relationship" title="sqlalchemy.orm.relationship"><tt class="xref py py-func docutils literal"><span class="pre">relationship()</span></tt></a> also accepts a callable
that returns the ultimate argument, which is evaluated only when mappers are
first used. Using this, we can define the <tt class="docutils literal"><span class="pre">association_table</span></tt> at a later
point, as long as it’s available to the callable after all module initialization
is complete:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="k">class</span> <span class="nc">Parent</span><span class="p">(</span><span class="n">Base</span><span class="p">):</span>
<span class="n">__tablename__</span> <span class="o">=</span> <span class="s">'left'</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="bp">True</span><span class="p">)</span>
<span class="n">children</span> <span class="o">=</span> <span class="n">relationship</span><span class="p">(</span><span class="s">"Child"</span><span class="p">,</span>
<span class="n">secondary</span><span class="o">=</span><span class="k">lambda</span><span class="p">:</span> <span class="n">association_table</span><span class="p">,</span>
<span class="n">backref</span><span class="o">=</span><span class="s">"parents"</span><span class="p">)</span></pre></div>
</div>
<p>With the declarative extension in use, the traditional “string name of the table”
is accepted as well, matching the name of the table as stored in <tt class="docutils literal"><span class="pre">Base.metadata.tables</span></tt>:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="k">class</span> <span class="nc">Parent</span><span class="p">(</span><span class="n">Base</span><span class="p">):</span>
<span class="n">__tablename__</span> <span class="o">=</span> <span class="s">'left'</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="bp">True</span><span class="p">)</span>
<span class="n">children</span> <span class="o">=</span> <span class="n">relationship</span><span class="p">(</span><span class="s">"Child"</span><span class="p">,</span>
<span class="n">secondary</span><span class="o">=</span><span class="s">"association"</span><span class="p">,</span>
<span class="n">backref</span><span class="o">=</span><span class="s">"parents"</span><span class="p">)</span></pre></div>
</div>
<div class="section" id="deleting-rows-from-the-many-to-many-table">
<span id="relationships-many-to-many-deletion"></span><h4>Deleting Rows from the Many to Many Table<a class="headerlink" href="#deleting-rows-from-the-many-to-many-table" title="Permalink to this headline">¶</a></h4>
<p>A behavior which is unique to the <a class="reference internal" href="#sqlalchemy.orm.relationship.params.secondary" title="sqlalchemy.orm.relationship"><tt class="xref py py-paramref docutils literal"><span class="pre">secondary</span></tt></a> argument to <a class="reference internal" href="#sqlalchemy.orm.relationship" title="sqlalchemy.orm.relationship"><tt class="xref py py-func docutils literal"><span class="pre">relationship()</span></tt></a>
is that the <a class="reference internal" href="../core/metadata.html#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal"><span class="pre">Table</span></tt></a> which is specified here is automatically subject
to INSERT and DELETE statements, as objects are added or removed from the collection.
There is <strong>no need to delete from this table manually</strong>. The act of removing a
record from the collection will have the effect of the row being deleted on flush:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="c"># row will be deleted from the "secondary" table</span>
<span class="c"># automatically</span>
<span class="n">myparent</span><span class="o">.</span><span class="n">children</span><span class="o">.</span><span class="n">remove</span><span class="p">(</span><span class="n">somechild</span><span class="p">)</span></pre></div>
</div>
<p>A question which often arises is how the row in the “secondary” table can be deleted
when the child object is handed directly to <a class="reference internal" href="session.html#sqlalchemy.orm.session.Session.delete" title="sqlalchemy.orm.session.Session.delete"><tt class="xref py py-meth docutils literal"><span class="pre">Session.delete()</span></tt></a>:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">session</span><span class="o">.</span><span class="n">delete</span><span class="p">(</span><span class="n">somechild</span><span class="p">)</span></pre></div>
</div>
<p>There are several possibilities here:</p>
<ul class="simple">
<li>If there is a <a class="reference internal" href="#sqlalchemy.orm.relationship" title="sqlalchemy.orm.relationship"><tt class="xref py py-func docutils literal"><span class="pre">relationship()</span></tt></a> from <tt class="docutils literal"><span class="pre">Parent</span></tt> to <tt class="docutils literal"><span class="pre">Child</span></tt>, but there is
<strong>not</strong> a reverse-relationship that links a particular <tt class="docutils literal"><span class="pre">Child</span></tt> to each <tt class="docutils literal"><span class="pre">Parent</span></tt>,
SQLAlchemy will not have any awareness that when deleting this particular
<tt class="docutils literal"><span class="pre">Child</span></tt> object, it needs to maintain the “secondary” table that links it to
the <tt class="docutils literal"><span class="pre">Parent</span></tt>. No delete of the “secondary” table will occur.</li>
<li>If there is a relationship that links a particular <tt class="docutils literal"><span class="pre">Child</span></tt> to each <tt class="docutils literal"><span class="pre">Parent</span></tt>,
suppose it’s called <tt class="docutils literal"><span class="pre">Child.parents</span></tt>, SQLAlchemy by default will load in
the <tt class="docutils literal"><span class="pre">Child.parents</span></tt> collection to locate all <tt class="docutils literal"><span class="pre">Parent</span></tt> objects, and remove
each row from the “secondary” table which establishes this link. Note that
this relationship does not need to be bidrectional; SQLAlchemy is strictly
looking at every <a class="reference internal" href="#sqlalchemy.orm.relationship" title="sqlalchemy.orm.relationship"><tt class="xref py py-func docutils literal"><span class="pre">relationship()</span></tt></a> associated with the <tt class="docutils literal"><span class="pre">Child</span></tt> object
being deleted.</li>
<li>A higher performing option here is to use ON DELETE CASCADE directives
with the foreign keys used by the database. Assuming the database supports
this feature, the database itself can be made to automatically delete rows in the
“secondary” table as referencing rows in “child” are deleted. SQLAlchemy
can be instructed to forego actively loading in the <tt class="docutils literal"><span class="pre">Child.parents</span></tt>
collection in this case using the <a class="reference internal" href="#sqlalchemy.orm.relationship.params.passive_deletes" title="sqlalchemy.orm.relationship"><tt class="xref py py-paramref docutils literal"><span class="pre">passive_deletes</span></tt></a>
directive on <a class="reference internal" href="#sqlalchemy.orm.relationship" title="sqlalchemy.orm.relationship"><tt class="xref py py-func docutils literal"><span class="pre">relationship()</span></tt></a>; see <a class="reference internal" href="collections.html#passive-deletes"><em>Using Passive Deletes</em></a> for more details
on this.</li>
</ul>
<p>Note again, these behaviors are <em>only</em> relevant to the <a class="reference internal" href="#sqlalchemy.orm.relationship.params.secondary" title="sqlalchemy.orm.relationship"><tt class="xref py py-paramref docutils literal"><span class="pre">secondary</span></tt></a> option
used with <a class="reference internal" href="#sqlalchemy.orm.relationship" title="sqlalchemy.orm.relationship"><tt class="xref py py-func docutils literal"><span class="pre">relationship()</span></tt></a>. If dealing with association tables that
are mapped explicitly and are <em>not</em> present in the <a class="reference internal" href="#sqlalchemy.orm.relationship.params.secondary" title="sqlalchemy.orm.relationship"><tt class="xref py py-paramref docutils literal"><span class="pre">secondary</span></tt></a> option
of a relevant <a class="reference internal" href="#sqlalchemy.orm.relationship" title="sqlalchemy.orm.relationship"><tt class="xref py py-func docutils literal"><span class="pre">relationship()</span></tt></a>, cascade rules can be used instead
to automatically delete entities in reaction to a related entity being
deleted - see <a class="reference internal" href="session.html#unitofwork-cascades"><em>Cascades</em></a> for information on this feature.</p>
</div>
</div>
<div class="section" id="association-object">
<span id="association-pattern"></span><h3>Association Object<a class="headerlink" href="#association-object" title="Permalink to this headline">¶</a></h3>
<p>The association object pattern is a variant on many-to-many: it’s used
when your association table contains additional columns beyond those
which are foreign keys to the left and right tables. Instead of using
the <a class="reference internal" href="#sqlalchemy.orm.relationship.params.secondary" title="sqlalchemy.orm.relationship"><tt class="xref py py-paramref docutils literal"><span class="pre">secondary</span></tt></a> argument, you map a new class
directly to the association table. The left side of the relationship
references the association object via one-to-many, and the association
class references the right side via many-to-one. Below we illustrate
an association table mapped to the <tt class="docutils literal"><span class="pre">Association</span></tt> class which
includes a column called <tt class="docutils literal"><span class="pre">extra_data</span></tt>, which is a string value that
is stored along with each association between <tt class="docutils literal"><span class="pre">Parent</span></tt> and
<tt class="docutils literal"><span class="pre">Child</span></tt>:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="k">class</span> <span class="nc">Association</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="s">'association'</span>
<span class="n">left_id</span> <span class="o">=</span> <span class="n">Column</span><span class="p">(</span><span class="n">Integer</span><span class="p">,</span> <span class="n">ForeignKey</span><span class="p">(</span><span class="s">'left.id'</span><span class="p">),</span> <span class="n">primary_key</span><span class="o">=</span><span class="bp">True</span><span class="p">)</span>
<span class="n">right_id</span> <span class="o">=</span> <span class="n">Column</span><span class="p">(</span><span class="n">Integer</span><span class="p">,</span> <span class="n">ForeignKey</span><span class="p">(</span><span class="s">'right.id'</span><span class="p">),</span> <span class="n">primary_key</span><span class="o">=</span><span class="bp">True</span><span class="p">)</span>
<span class="n">extra_data</span> <span class="o">=</span> <span class="n">Column</span><span class="p">(</span><span class="n">String</span><span class="p">(</span><span class="mi">50</span><span class="p">))</span>
<span class="n">child</span> <span class="o">=</span> <span class="n">relationship</span><span class="p">(</span><span class="s">"Child"</span><span class="p">)</span>
<span class="k">class</span> <span class="nc">Parent</span><span class="p">(</span><span class="n">Base</span><span class="p">):</span>
<span class="n">__tablename__</span> <span class="o">=</span> <span class="s">'left'</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="bp">True</span><span class="p">)</span>
<span class="n">children</span> <span class="o">=</span> <span class="n">relationship</span><span class="p">(</span><span class="s">"Association"</span><span class="p">)</span>
<span class="k">class</span> <span class="nc">Child</span><span class="p">(</span><span class="n">Base</span><span class="p">):</span>
<span class="n">__tablename__</span> <span class="o">=</span> <span class="s">'right'</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="bp">True</span><span class="p">)</span></pre></div>
</div>
<p>The bidirectional version adds backrefs to both relationships:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="k">class</span> <span class="nc">Association</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="s">'association'</span>
<span class="n">left_id</span> <span class="o">=</span> <span class="n">Column</span><span class="p">(</span><span class="n">Integer</span><span class="p">,</span> <span class="n">ForeignKey</span><span class="p">(</span><span class="s">'left.id'</span><span class="p">),</span> <span class="n">primary_key</span><span class="o">=</span><span class="bp">True</span><span class="p">)</span>
<span class="n">right_id</span> <span class="o">=</span> <span class="n">Column</span><span class="p">(</span><span class="n">Integer</span><span class="p">,</span> <span class="n">ForeignKey</span><span class="p">(</span><span class="s">'right.id'</span><span class="p">),</span> <span class="n">primary_key</span><span class="o">=</span><span class="bp">True</span><span class="p">)</span>
<span class="n">extra_data</span> <span class="o">=</span> <span class="n">Column</span><span class="p">(</span><span class="n">String</span><span class="p">(</span><span class="mi">50</span><span class="p">))</span>
<span class="n">child</span> <span class="o">=</span> <span class="n">relationship</span><span class="p">(</span><span class="s">"Child"</span><span class="p">,</span> <span class="n">backref</span><span class="o">=</span><span class="s">"parent_assocs"</span><span class="p">)</span>
<span class="k">class</span> <span class="nc">Parent</span><span class="p">(</span><span class="n">Base</span><span class="p">):</span>
<span class="n">__tablename__</span> <span class="o">=</span> <span class="s">'left'</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="bp">True</span><span class="p">)</span>
<span class="n">children</span> <span class="o">=</span> <span class="n">relationship</span><span class="p">(</span><span class="s">"Association"</span><span class="p">,</span> <span class="n">backref</span><span class="o">=</span><span class="s">"parent"</span><span class="p">)</span>
<span class="k">class</span> <span class="nc">Child</span><span class="p">(</span><span class="n">Base</span><span class="p">):</span>
<span class="n">__tablename__</span> <span class="o">=</span> <span class="s">'right'</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="bp">True</span><span class="p">)</span></pre></div>
</div>
<p>Working with the association pattern in its direct form requires that child
objects are associated with an association instance before being appended to
the parent; similarly, access from parent to child goes through the
association object:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="c"># create parent, append a child via association</span>
<span class="n">p</span> <span class="o">=</span> <span class="n">Parent</span><span class="p">()</span>
<span class="n">a</span> <span class="o">=</span> <span class="n">Association</span><span class="p">(</span><span class="n">extra_data</span><span class="o">=</span><span class="s">"some data"</span><span class="p">)</span>
<span class="n">a</span><span class="o">.</span><span class="n">child</span> <span class="o">=</span> <span class="n">Child</span><span class="p">()</span>
<span class="n">p</span><span class="o">.</span><span class="n">children</span><span class="o">.</span><span class="n">append</span><span class="p">(</span><span class="n">a</span><span class="p">)</span>
<span class="c"># iterate through child objects via association, including association</span>
<span class="c"># attributes</span>
<span class="k">for</span> <span class="n">assoc</span> <span class="ow">in</span> <span class="n">p</span><span class="o">.</span><span class="n">children</span><span class="p">:</span>
<span class="k">print</span> <span class="n">assoc</span><span class="o">.</span><span class="n">extra_data</span>
<span class="k">print</span> <span class="n">assoc</span><span class="o">.</span><span class="n">child</span></pre></div>
</div>
<p>To enhance the association object pattern such that direct
access to the <tt class="docutils literal"><span class="pre">Association</span></tt> object is optional, SQLAlchemy
provides the <a class="reference internal" href="extensions/associationproxy.html"><em>Association Proxy</em></a> extension. This
extension allows the configuration of attributes which will
access two “hops” with a single access, one “hop” to the
associated object, and a second to a target attribute.</p>
<div class="admonition note">
<p class="first admonition-title">Note</p>
<p class="last">When using the association object pattern, it is advisable that the
association-mapped table not be used as the
<a class="reference internal" href="#sqlalchemy.orm.relationship.params.secondary" title="sqlalchemy.orm.relationship"><tt class="xref py py-paramref docutils literal"><span class="pre">secondary</span></tt></a> argument on a
<a class="reference internal" href="#sqlalchemy.orm.relationship" title="sqlalchemy.orm.relationship"><tt class="xref py py-func docutils literal"><span class="pre">relationship()</span></tt></a> elsewhere, unless that <a class="reference internal" href="#sqlalchemy.orm.relationship" title="sqlalchemy.orm.relationship"><tt class="xref py py-func docutils literal"><span class="pre">relationship()</span></tt></a>
contains the option <a class="reference internal" href="#sqlalchemy.orm.relationship.params.viewonly" title="sqlalchemy.orm.relationship"><tt class="xref py py-paramref docutils literal"><span class="pre">viewonly</span></tt></a> set to
<tt class="docutils literal"><span class="pre">True</span></tt>. SQLAlchemy otherwise may attempt to emit redundant INSERT
and DELETE statements on the same table, if similar state is
detected on the related attribute as well as the associated object.</p>
</div>
</div>
</div>
<div class="section" id="adjacency-list-relationships">
<span id="self-referential"></span><h2>Adjacency List Relationships<a class="headerlink" href="#adjacency-list-relationships" title="Permalink to this headline">¶</a></h2>
<p>The <strong>adjacency list</strong> pattern is a common relational pattern whereby a table
contains a foreign key reference to itself. This is the most common
way to represent hierarchical data in flat tables. Other methods
include <strong>nested sets</strong>, sometimes called “modified preorder”,
as well as <strong>materialized path</strong>. Despite the appeal that modified preorder
has when evaluated for its fluency within SQL queries, the adjacency list model is
probably the most appropriate pattern for the large majority of hierarchical
storage needs, for reasons of concurrency, reduced complexity, and that
modified preorder has little advantage over an application which can fully
load subtrees into the application space.</p>
<p>In this example, we’ll work with a single mapped
class called <tt class="docutils literal"><span class="pre">Node</span></tt>, representing a tree structure:</p>
<div class="highlight-python"><div class="highlight"><pre><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="s">'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="bp">True</span><span class="p">)</span>
<span class="n">parent_id</span> <span class="o">=</span> <span class="n">Column</span><span class="p">(</span><span class="n">Integer</span><span class="p">,</span> <span class="n">ForeignKey</span><span class="p">(</span><span class="s">'node.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><span class="mi">50</span><span class="p">))</span>
<span class="n">children</span> <span class="o">=</span> <span class="n">relationship</span><span class="p">(</span><span class="s">"Node"</span><span class="p">)</span></pre></div>
</div>
<p>With this structure, a graph such as the following:</p>
<div class="highlight-python"><pre>root --+---> child1
+---> child2 --+--> subchild1
| +--> subchild2
+---> child3</pre>
</div>
<p>Would be represented with data such as:</p>
<div class="highlight-python"><pre>id parent_id data
--- ------- ----
1 NULL root
2 1 child1
3 1 child2
4 3 subchild1
5 3 subchild2
6 1 child3</pre>
</div>
<p>The <a class="reference internal" href="#sqlalchemy.orm.relationship" title="sqlalchemy.orm.relationship"><tt class="xref py py-func docutils literal"><span class="pre">relationship()</span></tt></a> configuration here works in the
same way as a “normal” one-to-many relationship, with the
exception that the “direction”, i.e. whether the relationship
is one-to-many or many-to-one, is assumed by default to
be one-to-many. To establish the relationship as many-to-one,
an extra directive is added known as <tt class="docutils literal"><span class="pre">remote_side</span></tt>, which
is a <a class="reference internal" href="../core/metadata.html#sqlalchemy.schema.Column" title="sqlalchemy.schema.Column"><tt class="xref py py-class docutils literal"><span class="pre">Column</span></tt></a> or collection of <a class="reference internal" href="../core/metadata.html#sqlalchemy.schema.Column" title="sqlalchemy.schema.Column"><tt class="xref py py-class docutils literal"><span class="pre">Column</span></tt></a> objects
that indicate those which should be considered to be “remote”:</p>
<div class="highlight-python"><div class="highlight"><pre><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="s">'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="bp">True</span><span class="p">)</span>
<span class="n">parent_id</span> <span class="o">=</span> <span class="n">Column</span><span class="p">(</span><span class="n">Integer</span><span class="p">,</span> <span class="n">ForeignKey</span><span class="p">(</span><span class="s">'node.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><span class="mi">50</span><span class="p">))</span>
<span class="n">parent</span> <span class="o">=</span> <span class="n">relationship</span><span class="p">(</span><span class="s">"Node"</span><span class="p">,</span> <span class="n">remote_side</span><span class="o">=</span><span class="p">[</span><span class="nb">id</span><span class="p">])</span></pre></div>
</div>
<p>Where above, the <tt class="docutils literal"><span class="pre">id</span></tt> column is applied as the <tt class="docutils literal"><span class="pre">remote_side</span></tt>
of the <tt class="docutils literal"><span class="pre">parent</span></tt> <a class="reference internal" href="#sqlalchemy.orm.relationship" title="sqlalchemy.orm.relationship"><tt class="xref py py-func docutils literal"><span class="pre">relationship()</span></tt></a>, thus establishing
<tt class="docutils literal"><span class="pre">parent_id</span></tt> as the “local” side, and the relationship
then behaves as a many-to-one.</p>
<p>As always, both directions can be combined into a bidirectional
relationship using the <a class="reference internal" href="#sqlalchemy.orm.backref" title="sqlalchemy.orm.backref"><tt class="xref py py-func docutils literal"><span class="pre">backref()</span></tt></a> function:</p>
<div class="highlight-python"><div class="highlight"><pre><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="s">'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="bp">True</span><span class="p">)</span>
<span class="n">parent_id</span> <span class="o">=</span> <span class="n">Column</span><span class="p">(</span><span class="n">Integer</span><span class="p">,</span> <span class="n">ForeignKey</span><span class="p">(</span><span class="s">'node.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><span class="mi">50</span><span class="p">))</span>
<span class="n">children</span> <span class="o">=</span> <span class="n">relationship</span><span class="p">(</span><span class="s">"Node"</span><span class="p">,</span>
<span class="n">backref</span><span class="o">=</span><span class="n">backref</span><span class="p">(</span><span class="s">'parent'</span><span class="p">,</span> <span class="n">remote_side</span><span class="o">=</span><span class="p">[</span><span class="nb">id</span><span class="p">])</span>
<span class="p">)</span></pre></div>
</div>
<p>There are several examples included with SQLAlchemy illustrating
self-referential strategies; these include <a class="reference internal" href="examples.html#examples-adjacencylist"><em>Adjacency List</em></a> and
<a class="reference internal" href="examples.html#examples-xmlpersistence"><em>XML Persistence</em></a>.</p>
<div class="section" id="composite-adjacency-lists">
<h3>Composite Adjacency Lists<a class="headerlink" href="#composite-adjacency-lists" title="Permalink to this headline">¶</a></h3>
<p>A sub-category of the adjacency list relationship is the rare
case where a particular column is present on both the “local” and
“remote” side of the join condition. An example is the <tt class="docutils literal"><span class="pre">Folder</span></tt>
class below; using a composite primary key, the <tt class="docutils literal"><span class="pre">account_id</span></tt>
column refers to itself, to indicate sub folders which are within
the same account as that of the parent; while <tt class="docutils literal"><span class="pre">folder_id</span></tt> refers
to a specific folder within that account:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="k">class</span> <span class="nc">Folder</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="s">'folder'</span>
<span class="n">__table_args__</span> <span class="o">=</span> <span class="p">(</span>
<span class="n">ForeignKeyConstraint</span><span class="p">(</span>
<span class="p">[</span><span class="s">'account_id'</span><span class="p">,</span> <span class="s">'parent_id'</span><span class="p">],</span>
<span class="p">[</span><span class="s">'folder.account_id'</span><span class="p">,</span> <span class="s">'folder.folder_id'</span><span class="p">]),</span>
<span class="p">)</span>
<span class="n">account_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="bp">True</span><span class="p">)</span>
<span class="n">folder_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="bp">True</span><span class="p">)</span>
<span class="n">parent_id</span> <span class="o">=</span> <span class="n">Column</span><span class="p">(</span><span class="n">Integer</span><span class="p">)</span>
<span class="n">name</span> <span class="o">=</span> <span class="n">Column</span><span class="p">(</span><span class="n">String</span><span class="p">)</span>
<span class="n">parent_folder</span> <span class="o">=</span> <span class="n">relationship</span><span class="p">(</span><span class="s">"Folder"</span><span class="p">,</span>
<span class="n">backref</span><span class="o">=</span><span class="s">"child_folders"</span><span class="p">,</span>
<span class="n">remote_side</span><span class="o">=</span><span class="p">[</span><span class="n">account_id</span><span class="p">,</span> <span class="n">folder_id</span><span class="p">]</span>
<span class="p">)</span></pre></div>
</div>
<p>Above, we pass <tt class="docutils literal"><span class="pre">account_id</span></tt> into the <tt class="docutils literal"><span class="pre">remote_side</span></tt> list.
<a class="reference internal" href="#sqlalchemy.orm.relationship" title="sqlalchemy.orm.relationship"><tt class="xref py py-func docutils literal"><span class="pre">relationship()</span></tt></a> recognizes that the <tt class="docutils literal"><span class="pre">account_id</span></tt> column here
is on both sides, and aligns the “remote” column along with the
<tt class="docutils literal"><span class="pre">folder_id</span></tt> column, which it recognizes as uniquely present on
the “remote” side.</p>
<div class="versionadded">
<p><span>New in version 0.8: </span>Support for self-referential composite keys in <a class="reference internal" href="#sqlalchemy.orm.relationship" title="sqlalchemy.orm.relationship"><tt class="xref py py-func docutils literal"><span class="pre">relationship()</span></tt></a>
where a column points to itself.</p>
</div>
</div>
<div class="section" id="self-referential-query-strategies">
<h3>Self-Referential Query Strategies<a class="headerlink" href="#self-referential-query-strategies" title="Permalink to this headline">¶</a></h3>
<p>Querying of self-referential structures works like any other query:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="c"># get all nodes named 'child2'</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">filter</span><span class="p">(</span><span class="n">Node</span><span class="o">.</span><span class="n">data</span><span class="o">==</span><span class="s">'child2'</span><span class="p">)</span></pre></div>
</div>
<p>However extra care is needed when attempting to join along
the foreign key from one level of the tree to the next. In SQL,
a join from a table to itself requires that at least one side of the
expression be “aliased” so that it can be unambiguously referred to.</p>
<p>Recall from <a class="reference internal" href="tutorial.html#ormtutorial-aliases"><em>Using Aliases</em></a> in the ORM tutorial that the
<a class="reference internal" href="query.html#sqlalchemy.orm.aliased" title="sqlalchemy.orm.aliased"><tt class="xref py py-func docutils literal"><span class="pre">orm.aliased()</span></tt></a> construct is normally used to provide an “alias” of
an ORM entity. Joining from <tt class="docutils literal"><span class="pre">Node</span></tt> to itself using this technique
looks like:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="kn">from</span> <span class="nn">sqlalchemy.orm</span> <span class="kn">import</span> <span class="n">aliased</span>
<span class="n">nodealias</span> <span class="o">=</span> <span class="n">aliased</span><span class="p">(</span><span class="n">Node</span><span class="p">)</span>
<a href='#' class='sql_link'>sql</a><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">filter</span><span class="p">(</span><span class="n">Node</span><span class="o">.</span><span class="n">data</span><span class="o">==</span><span class="s">'subchild1'</span><span class="p">)</span><span class="o">.</span>\
<span class="n">join</span><span class="p">(</span><span class="n">nodealias</span><span class="p">,</span> <span class="n">Node</span><span class="o">.</span><span class="n">parent</span><span class="p">)</span><span class="o">.</span>\
<span class="nb">filter</span><span class="p">(</span><span class="n">nodealias</span><span class="o">.</span><span class="n">data</span><span class="o">==</span><span class="s">"child2"</span><span class="p">)</span><span class="o">.</span>\
<span class="nb">all</span><span class="p">()</span>
<div class='popup_sql'>SELECT node.id AS node_id,
node.parent_id AS node_parent_id,
node.data AS node_data
FROM node JOIN node AS node_1
ON node.parent_id = node_1.id
WHERE node.data = ?
AND node_1.data = ?
['subchild1', 'child2']</div></pre></div>
</div>
<p><a class="reference internal" href="query.html#sqlalchemy.orm.query.Query.join" title="sqlalchemy.orm.query.Query.join"><tt class="xref py py-meth docutils literal"><span class="pre">Query.join()</span></tt></a> also includes a feature known as
<a class="reference internal" href="query.html#sqlalchemy.orm.query.Query.join.params.aliased" title="sqlalchemy.orm.query.Query.join"><tt class="xref py py-paramref docutils literal"><span class="pre">Query.join.aliased</span></tt></a> that can shorten the verbosity self-
referential joins, at the expense of query flexibility. This feature
performs a similar “aliasing” step to that above, without the need for
an explicit entity. Calls to <a class="reference internal" href="query.html#sqlalchemy.orm.query.Query.filter" title="sqlalchemy.orm.query.Query.filter"><tt class="xref py py-meth docutils literal"><span class="pre">Query.filter()</span></tt></a> and similar
subsequent to the aliased join will <strong>adapt</strong> the <tt class="docutils literal"><span class="pre">Node</span></tt> entity to
be that of the alias:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><a href='#' class='sql_link'>sql</a><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">filter</span><span class="p">(</span><span class="n">Node</span><span class="o">.</span><span class="n">data</span><span class="o">==</span><span class="s">'subchild1'</span><span class="p">)</span><span class="o">.</span>\
<span class="n">join</span><span class="p">(</span><span class="n">Node</span><span class="o">.</span><span class="n">parent</span><span class="p">,</span> <span class="n">aliased</span><span class="o">=</span><span class="bp">True</span><span class="p">)</span><span class="o">.</span>\
<span class="nb">filter</span><span class="p">(</span><span class="n">Node</span><span class="o">.</span><span class="n">data</span><span class="o">==</span><span class="s">'child2'</span><span class="p">)</span><span class="o">.</span>\
<span class="nb">all</span><span class="p">()</span>
<div class='popup_sql'>SELECT node.id AS node_id,
node.parent_id AS node_parent_id,
node.data AS node_data
FROM node
JOIN node AS node_1 ON node_1.id = node.parent_id
WHERE node.data = ? AND node_1.data = ?
['subchild1', 'child2']</div></pre></div>
</div>
<p>To add criterion to multiple points along a longer join, add
<a class="reference internal" href="query.html#sqlalchemy.orm.query.Query.join.params.from_joinpoint" title="sqlalchemy.orm.query.Query.join"><tt class="xref py py-paramref docutils literal"><span class="pre">Query.join.from_joinpoint</span></tt></a> to the additional
<a class="reference internal" href="query.html#sqlalchemy.orm.query.Query.join" title="sqlalchemy.orm.query.Query.join"><tt class="xref py py-meth docutils literal"><span class="pre">join()</span></tt></a> calls:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="c"># get all nodes named 'subchild1' with a</span>
<span class="c"># parent named 'child2' and a grandparent 'root'</span>
<a href='#' class='sql_link'>sql</a><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="nb">filter</span><span class="p">(</span><span class="n">Node</span><span class="o">.</span><span class="n">data</span><span class="o">==</span><span class="s">'subchild1'</span><span class="p">)</span><span class="o">.</span>\
<span class="n">join</span><span class="p">(</span><span class="n">Node</span><span class="o">.</span><span class="n">parent</span><span class="p">,</span> <span class="n">aliased</span><span class="o">=</span><span class="bp">True</span><span class="p">)</span><span class="o">.</span>\
<span class="nb">filter</span><span class="p">(</span><span class="n">Node</span><span class="o">.</span><span class="n">data</span><span class="o">==</span><span class="s">'child2'</span><span class="p">)</span><span class="o">.</span>\
<span class="n">join</span><span class="p">(</span><span class="n">Node</span><span class="o">.</span><span class="n">parent</span><span class="p">,</span> <span class="n">aliased</span><span class="o">=</span><span class="bp">True</span><span class="p">,</span> <span class="n">from_joinpoint</span><span class="o">=</span><span class="bp">True</span><span class="p">)</span><span class="o">.</span>\
<span class="nb">filter</span><span class="p">(</span><span class="n">Node</span><span class="o">.</span><span class="n">data</span><span class="o">==</span><span class="s">'root'</span><span class="p">)</span><span class="o">.</span>\
<span class="nb">all</span><span class="p">()</span>
<div class='popup_sql'>SELECT node.id AS node_id,
node.parent_id AS node_parent_id,
node.data AS node_data
FROM node
JOIN node AS node_1 ON node_1.id = node.parent_id
JOIN node AS node_2 ON node_2.id = node_1.parent_id
WHERE node.data = ?
AND node_1.data = ?
AND node_2.data = ?
['subchild1', 'child2', 'root']</div></pre></div>
</div>
<p><a class="reference internal" href="query.html#sqlalchemy.orm.query.Query.reset_joinpoint" title="sqlalchemy.orm.query.Query.reset_joinpoint"><tt class="xref py py-meth docutils literal"><span class="pre">Query.reset_joinpoint()</span></tt></a> will also remove the “aliasing” from filtering
calls:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">session</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="n">Node</span><span class="p">)</span><span class="o">.</span>\
<span class="n">join</span><span class="p">(</span><span class="n">Node</span><span class="o">.</span><span class="n">children</span><span class="p">,</span> <span class="n">aliased</span><span class="o">=</span><span class="bp">True</span><span class="p">)</span><span class="o">.</span>\
<span class="nb">filter</span><span class="p">(</span><span class="n">Node</span><span class="o">.</span><span class="n">data</span> <span class="o">==</span> <span class="s">'foo'</span><span class="p">)</span><span class="o">.</span>\
<span class="n">reset_joinpoint</span><span class="p">()</span><span class="o">.</span>\
<span class="nb">filter</span><span class="p">(</span><span class="n">Node</span><span class="o">.</span><span class="n">data</span> <span class="o">==</span> <span class="s">'bar'</span><span class="p">)</span></pre></div>
</div>
<p>For an example of using <a class="reference internal" href="query.html#sqlalchemy.orm.query.Query.join.params.aliased" title="sqlalchemy.orm.query.Query.join"><tt class="xref py py-paramref docutils literal"><span class="pre">Query.join.aliased</span></tt></a> to
arbitrarily join along a chain of self-referential nodes, see
<a class="reference internal" href="examples.html#examples-xmlpersistence"><em>XML Persistence</em></a>.</p>
</div>
<div class="section" id="configuring-self-referential-eager-loading">
<span id="self-referential-eager-loading"></span><h3>Configuring Self-Referential Eager Loading<a class="headerlink" href="#configuring-self-referential-eager-loading" title="Permalink to this headline">¶</a></h3>
<p>Eager loading of relationships occurs using joins or outerjoins from parent to
child table during a normal query operation, such that the parent and its
immediate child collection or reference can be populated from a single SQL
statement, or a second statement for all immediate child collections.
SQLAlchemy’s joined and subquery eager loading use aliased tables in all cases
when joining to related items, so are compatible with self-referential
joining. However, to use eager loading with a self-referential relationship,
SQLAlchemy needs to be told how many levels deep it should join and/or query;
otherwise the eager load will not take place at all. This depth setting is
configured via <a class="reference internal" href="mapper_config.html#sqlalchemy.orm.mapper.Mapper.relationships.params.join_depth" title="sqlalchemy.orm.mapper.Mapper.relationships"><tt class="xref py py-paramref docutils literal"><span class="pre">join_depth</span></tt></a>:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><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="s">'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="bp">True</span><span class="p">)</span>
<span class="n">parent_id</span> <span class="o">=</span> <span class="n">Column</span><span class="p">(</span><span class="n">Integer</span><span class="p">,</span> <span class="n">ForeignKey</span><span class="p">(</span><span class="s">'node.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><span class="mi">50</span><span class="p">))</span>
<span class="n">children</span> <span class="o">=</span> <span class="n">relationship</span><span class="p">(</span><span class="s">"Node"</span><span class="p">,</span>
<span class="n">lazy</span><span class="o">=</span><span class="s">"joined"</span><span class="p">,</span>
<span class="n">join_depth</span><span class="o">=</span><span class="mi">2</span><span class="p">)</span>
<a href='#' class='sql_link'>sql</a><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">all</span><span class="p">()</span>
<div class='popup_sql'>SELECT node_1.id AS node_1_id,
node_1.parent_id AS node_1_parent_id,
node_1.data AS node_1_data,
node_2.id AS node_2_id,
node_2.parent_id AS node_2_parent_id,
node_2.data AS node_2_data,
node.id AS node_id,
node.parent_id AS node_parent_id,
node.data AS node_data
FROM node
LEFT OUTER JOIN node AS node_2
ON node.id = node_2.parent_id
LEFT OUTER JOIN node AS node_1
ON node_2.id = node_1.parent_id
[]</div></pre></div>
</div>
</div>
</div>
<div class="section" id="linking-relationships-with-backref">
<span id="relationships-backref"></span><h2>Linking Relationships with Backref<a class="headerlink" href="#linking-relationships-with-backref" title="Permalink to this headline">¶</a></h2>
<p>The <a class="reference internal" href="#sqlalchemy.orm.relationship.params.backref" title="sqlalchemy.orm.relationship"><tt class="xref py py-paramref docutils literal"><span class="pre">backref</span></tt></a> keyword argument was first introduced in <a class="reference internal" href="tutorial.html"><em>Object Relational Tutorial</em></a>, and has been
mentioned throughout many of the examples here. What does it actually do ? Let’s start
with the canonical <tt class="docutils literal"><span class="pre">User</span></tt> and <tt class="docutils literal"><span class="pre">Address</span></tt> scenario:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="kn">from</span> <span class="nn">sqlalchemy</span> <span class="kn">import</span> <span class="n">Integer</span><span class="p">,</span> <span class="n">ForeignKey</span><span class="p">,</span> <span class="n">String</span><span class="p">,</span> <span class="n">Column</span>
<span class="kn">from</span> <span class="nn">sqlalchemy.ext.declarative</span> <span class="kn">import</span> <span class="n">declarative_base</span>
<span class="kn">from</span> <span class="nn">sqlalchemy.orm</span> <span class="kn">import</span> <span class="n">relationship</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">User</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="s">'user'</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="bp">True</span><span class="p">)</span>
<span class="n">name</span> <span class="o">=</span> <span class="n">Column</span><span class="p">(</span><span class="n">String</span><span class="p">)</span>
<span class="n">addresses</span> <span class="o">=</span> <span class="n">relationship</span><span class="p">(</span><span class="s">"Address"</span><span class="p">,</span> <span class="n">backref</span><span class="o">=</span><span class="s">"user"</span><span class="p">)</span>
<span class="k">class</span> <span class="nc">Address</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="s">'address'</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="bp">True</span><span class="p">)</span>
<span class="n">email</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">user_id</span> <span class="o">=</span> <span class="n">Column</span><span class="p">(</span><span class="n">Integer</span><span class="p">,</span> <span class="n">ForeignKey</span><span class="p">(</span><span class="s">'user.id'</span><span class="p">))</span></pre></div>
</div>
<p>The above configuration establishes a collection of <tt class="docutils literal"><span class="pre">Address</span></tt> objects on <tt class="docutils literal"><span class="pre">User</span></tt> called
<tt class="docutils literal"><span class="pre">User.addresses</span></tt>. It also establishes a <tt class="docutils literal"><span class="pre">.user</span></tt> attribute on <tt class="docutils literal"><span class="pre">Address</span></tt> which will
refer to the parent <tt class="docutils literal"><span class="pre">User</span></tt> object.</p>
<p>In fact, the <a class="reference internal" href="#sqlalchemy.orm.relationship.params.backref" title="sqlalchemy.orm.relationship"><tt class="xref py py-paramref docutils literal"><span class="pre">backref</span></tt></a> keyword is only a common shortcut for placing a second
<a class="reference internal" href="#sqlalchemy.orm.relationship" title="sqlalchemy.orm.relationship"><tt class="xref py py-func docutils literal"><span class="pre">relationship()</span></tt></a> onto the <tt class="docutils literal"><span class="pre">Address</span></tt> mapping, including the establishment
of an event listener on both sides which will mirror attribute operations
in both directions. The above configuration is equivalent to:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="kn">from</span> <span class="nn">sqlalchemy</span> <span class="kn">import</span> <span class="n">Integer</span><span class="p">,</span> <span class="n">ForeignKey</span><span class="p">,</span> <span class="n">String</span><span class="p">,</span> <span class="n">Column</span>
<span class="kn">from</span> <span class="nn">sqlalchemy.ext.declarative</span> <span class="kn">import</span> <span class="n">declarative_base</span>
<span class="kn">from</span> <span class="nn">sqlalchemy.orm</span> <span class="kn">import</span> <span class="n">relationship</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">User</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="s">'user'</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="bp">True</span><span class="p">)</span>
<span class="n">name</span> <span class="o">=</span> <span class="n">Column</span><span class="p">(</span><span class="n">String</span><span class="p">)</span>
<span class="n">addresses</span> <span class="o">=</span> <span class="n">relationship</span><span class="p">(</span><span class="s">"Address"</span><span class="p">,</span> <span class="n">back_populates</span><span class="o">=</span><span class="s">"user"</span><span class="p">)</span>
<span class="k">class</span> <span class="nc">Address</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="s">'address'</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="bp">True</span><span class="p">)</span>
<span class="n">email</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">user_id</span> <span class="o">=</span> <span class="n">Column</span><span class="p">(</span><span class="n">Integer</span><span class="p">,</span> <span class="n">ForeignKey</span><span class="p">(</span><span class="s">'user.id'</span><span class="p">))</span>
<span class="n">user</span> <span class="o">=</span> <span class="n">relationship</span><span class="p">(</span><span class="s">"User"</span><span class="p">,</span> <span class="n">back_populates</span><span class="o">=</span><span class="s">"addresses"</span><span class="p">)</span></pre></div>
</div>
<p>Above, we add a <tt class="docutils literal"><span class="pre">.user</span></tt> relationship to <tt class="docutils literal"><span class="pre">Address</span></tt> explicitly. On
both relationships, the <a class="reference internal" href="#sqlalchemy.orm.relationship.params.back_populates" title="sqlalchemy.orm.relationship"><tt class="xref py py-paramref docutils literal"><span class="pre">back_populates</span></tt></a> directive tells each relationship
about the other one, indicating that they should establish “bidirectional”
behavior between each other. The primary effect of this configuration
is that the relationship adds event handlers to both attributes
which have the behavior of “when an append or set event occurs here, set ourselves
onto the incoming attribute using this particular attribute name”.
The behavior is illustrated as follows. Start with a <tt class="docutils literal"><span class="pre">User</span></tt> and an <tt class="docutils literal"><span class="pre">Address</span></tt>
instance. The <tt class="docutils literal"><span class="pre">.addresses</span></tt> collection is empty, and the <tt class="docutils literal"><span class="pre">.user</span></tt> attribute
is <tt class="docutils literal"><span class="pre">None</span></tt>:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="n">u1</span> <span class="o">=</span> <span class="n">User</span><span class="p">()</span>
<span class="gp">>>> </span><span class="n">a1</span> <span class="o">=</span> <span class="n">Address</span><span class="p">()</span>
<span class="gp">>>> </span><span class="n">u1</span><span class="o">.</span><span class="n">addresses</span>
<span class="go">[]</span>
<span class="gp">>>> </span><span class="k">print</span> <span class="n">a1</span><span class="o">.</span><span class="n">user</span>
<span class="go">None</span></pre></div>
</div>
<p>However, once the <tt class="docutils literal"><span class="pre">Address</span></tt> is appended to the <tt class="docutils literal"><span class="pre">u1.addresses</span></tt> collection,
both the collection and the scalar attribute have been populated:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="n">u1</span><span class="o">.</span><span class="n">addresses</span><span class="o">.</span><span class="n">append</span><span class="p">(</span><span class="n">a1</span><span class="p">)</span>
<span class="gp">>>> </span><span class="n">u1</span><span class="o">.</span><span class="n">addresses</span>
<span class="go">[<__main__.Address object at 0x12a6ed0>]</span>
<span class="gp">>>> </span><span class="n">a1</span><span class="o">.</span><span class="n">user</span>
<span class="go"><__main__.User object at 0x12a6590></span></pre></div>
</div>
<p>This behavior of course works in reverse for removal operations as well, as well
as for equivalent operations on both sides. Such as
when <tt class="docutils literal"><span class="pre">.user</span></tt> is set again to <tt class="docutils literal"><span class="pre">None</span></tt>, the <tt class="docutils literal"><span class="pre">Address</span></tt> object is removed
from the reverse collection:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="n">a1</span><span class="o">.</span><span class="n">user</span> <span class="o">=</span> <span class="bp">None</span>
<span class="gp">>>> </span><span class="n">u1</span><span class="o">.</span><span class="n">addresses</span>
<span class="go">[]</span></pre></div>
</div>
<p>The manipulation of the <tt class="docutils literal"><span class="pre">.addresses</span></tt> collection and the <tt class="docutils literal"><span class="pre">.user</span></tt> attribute
occurs entirely in Python without any interaction with the SQL database.
Without this behavior, the proper state would be apparent on both sides once the
data has been flushed to the database, and later reloaded after a commit or
expiration operation occurs. The <a class="reference internal" href="#sqlalchemy.orm.relationship.params.backref" title="sqlalchemy.orm.relationship"><tt class="xref py py-paramref docutils literal"><span class="pre">backref</span></tt></a>/<a class="reference internal" href="#sqlalchemy.orm.relationship.params.back_populates" title="sqlalchemy.orm.relationship"><tt class="xref py py-paramref docutils literal"><span class="pre">back_populates</span></tt></a> behavior has the advantage
that common bidirectional operations can reflect the correct state without requiring
a database round trip.</p>
<p>Remember, when the <a class="reference internal" href="#sqlalchemy.orm.relationship.params.backref" title="sqlalchemy.orm.relationship"><tt class="xref py py-paramref docutils literal"><span class="pre">backref</span></tt></a> keyword is used on a single relationship, it’s
exactly the same as if the above two relationships were created individually
using <a class="reference internal" href="#sqlalchemy.orm.relationship.params.back_populates" title="sqlalchemy.orm.relationship"><tt class="xref py py-paramref docutils literal"><span class="pre">back_populates</span></tt></a> on each.</p>
<div class="section" id="backref-arguments">
<h3>Backref Arguments<a class="headerlink" href="#backref-arguments" title="Permalink to this headline">¶</a></h3>
<p>We’ve established that the <a class="reference internal" href="#sqlalchemy.orm.relationship.params.backref" title="sqlalchemy.orm.relationship"><tt class="xref py py-paramref docutils literal"><span class="pre">backref</span></tt></a> keyword is merely a shortcut for building
two individual <a class="reference internal" href="#sqlalchemy.orm.relationship" title="sqlalchemy.orm.relationship"><tt class="xref py py-func docutils literal"><span class="pre">relationship()</span></tt></a> constructs that refer to each other. Part of
the behavior of this shortcut is that certain configurational arguments applied to
the <a class="reference internal" href="#sqlalchemy.orm.relationship" title="sqlalchemy.orm.relationship"><tt class="xref py py-func docutils literal"><span class="pre">relationship()</span></tt></a>
will also be applied to the other direction - namely those arguments that describe
the relationship at a schema level, and are unlikely to be different in the reverse
direction. The usual case
here is a many-to-many <a class="reference internal" href="#sqlalchemy.orm.relationship" title="sqlalchemy.orm.relationship"><tt class="xref py py-func docutils literal"><span class="pre">relationship()</span></tt></a> that has a <a class="reference internal" href="#sqlalchemy.orm.relationship.params.secondary" title="sqlalchemy.orm.relationship"><tt class="xref py py-paramref docutils literal"><span class="pre">secondary</span></tt></a> argument,
or a one-to-many or many-to-one which has a <a class="reference internal" href="#sqlalchemy.orm.relationship.params.primaryjoin" title="sqlalchemy.orm.relationship"><tt class="xref py py-paramref docutils literal"><span class="pre">primaryjoin</span></tt></a> argument (the
<a class="reference internal" href="#sqlalchemy.orm.relationship.params.primaryjoin" title="sqlalchemy.orm.relationship"><tt class="xref py py-paramref docutils literal"><span class="pre">primaryjoin</span></tt></a> argument is discussed in <a class="reference internal" href="#relationship-primaryjoin"><em>Specifying Alternate Join Conditions</em></a>). Such
as if we limited the list of <tt class="docutils literal"><span class="pre">Address</span></tt> objects to those which start with “tony”:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="kn">from</span> <span class="nn">sqlalchemy</span> <span class="kn">import</span> <span class="n">Integer</span><span class="p">,</span> <span class="n">ForeignKey</span><span class="p">,</span> <span class="n">String</span><span class="p">,</span> <span class="n">Column</span>
<span class="kn">from</span> <span class="nn">sqlalchemy.ext.declarative</span> <span class="kn">import</span> <span class="n">declarative_base</span>
<span class="kn">from</span> <span class="nn">sqlalchemy.orm</span> <span class="kn">import</span> <span class="n">relationship</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">User</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="s">'user'</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="bp">True</span><span class="p">)</span>
<span class="n">name</span> <span class="o">=</span> <span class="n">Column</span><span class="p">(</span><span class="n">String</span><span class="p">)</span>
<span class="n">addresses</span> <span class="o">=</span> <span class="n">relationship</span><span class="p">(</span><span class="s">"Address"</span><span class="p">,</span>
<span class="n">primaryjoin</span><span class="o">=</span><span class="s">"and_(User.id==Address.user_id, "</span>
<span class="s">"Address.email.startswith('tony'))"</span><span class="p">,</span>
<span class="n">backref</span><span class="o">=</span><span class="s">"user"</span><span class="p">)</span>
<span class="k">class</span> <span class="nc">Address</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="s">'address'</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="bp">True</span><span class="p">)</span>
<span class="n">email</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">user_id</span> <span class="o">=</span> <span class="n">Column</span><span class="p">(</span><span class="n">Integer</span><span class="p">,</span> <span class="n">ForeignKey</span><span class="p">(</span><span class="s">'user.id'</span><span class="p">))</span></pre></div>
</div>
<p>We can observe, by inspecting the resulting property, that both sides
of the relationship have this join condition applied:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="k">print</span> <span class="n">User</span><span class="o">.</span><span class="n">addresses</span><span class="o">.</span><span class="n">property</span><span class="o">.</span><span class="n">primaryjoin</span>
<span class="go">"user".id = address.user_id AND address.email LIKE :email_1 || '%%'</span>
<span class="go">>>></span>
<span class="gp">>>> </span><span class="k">print</span> <span class="n">Address</span><span class="o">.</span><span class="n">user</span><span class="o">.</span><span class="n">property</span><span class="o">.</span><span class="n">primaryjoin</span>
<span class="go">"user".id = address.user_id AND address.email LIKE :email_1 || '%%'</span>
<span class="go">>>></span></pre></div>
</div>
<p>This reuse of arguments should pretty much do the “right thing” - it
uses only arguments that are applicable, and in the case of a many-to-
many relationship, will reverse the usage of
<a class="reference internal" href="#sqlalchemy.orm.relationship.params.primaryjoin" title="sqlalchemy.orm.relationship"><tt class="xref py py-paramref docutils literal"><span class="pre">primaryjoin</span></tt></a> and
<a class="reference internal" href="#sqlalchemy.orm.relationship.params.secondaryjoin" title="sqlalchemy.orm.relationship"><tt class="xref py py-paramref docutils literal"><span class="pre">secondaryjoin</span></tt></a> to correspond to the other
direction (see the example in <a class="reference internal" href="#self-referential-many-to-many"><em>Self-Referential Many-to-Many Relationship</em></a> for
this).</p>
<p>It’s very often the case however that we’d like to specify arguments
that are specific to just the side where we happened to place the
“backref”. This includes <a class="reference internal" href="#sqlalchemy.orm.relationship" title="sqlalchemy.orm.relationship"><tt class="xref py py-func docutils literal"><span class="pre">relationship()</span></tt></a> arguments like
<a class="reference internal" href="#sqlalchemy.orm.relationship.params.lazy" title="sqlalchemy.orm.relationship"><tt class="xref py py-paramref docutils literal"><span class="pre">lazy</span></tt></a>,
<a class="reference internal" href="#sqlalchemy.orm.relationship.params.remote_side" title="sqlalchemy.orm.relationship"><tt class="xref py py-paramref docutils literal"><span class="pre">remote_side</span></tt></a>,
<a class="reference internal" href="#sqlalchemy.orm.relationship.params.cascade" title="sqlalchemy.orm.relationship"><tt class="xref py py-paramref docutils literal"><span class="pre">cascade</span></tt></a> and
<a class="reference internal" href="#sqlalchemy.orm.relationship.params.cascade_backrefs" title="sqlalchemy.orm.relationship"><tt class="xref py py-paramref docutils literal"><span class="pre">cascade_backrefs</span></tt></a>. For this case we use
the <a class="reference internal" href="#sqlalchemy.orm.backref" title="sqlalchemy.orm.backref"><tt class="xref py py-func docutils literal"><span class="pre">backref()</span></tt></a> function in place of a string:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="c"># <other imports></span>
<span class="kn">from</span> <span class="nn">sqlalchemy.orm</span> <span class="kn">import</span> <span class="n">backref</span>
<span class="k">class</span> <span class="nc">User</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="s">'user'</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="bp">True</span><span class="p">)</span>
<span class="n">name</span> <span class="o">=</span> <span class="n">Column</span><span class="p">(</span><span class="n">String</span><span class="p">)</span>
<span class="n">addresses</span> <span class="o">=</span> <span class="n">relationship</span><span class="p">(</span><span class="s">"Address"</span><span class="p">,</span>
<span class="n">backref</span><span class="o">=</span><span class="n">backref</span><span class="p">(</span><span class="s">"user"</span><span class="p">,</span> <span class="n">lazy</span><span class="o">=</span><span class="s">"joined"</span><span class="p">))</span></pre></div>
</div>
<p>Where above, we placed a <tt class="docutils literal"><span class="pre">lazy="joined"</span></tt> directive only on the <tt class="docutils literal"><span class="pre">Address.user</span></tt>
side, indicating that when a query against <tt class="docutils literal"><span class="pre">Address</span></tt> is made, a join to the <tt class="docutils literal"><span class="pre">User</span></tt>
entity should be made automatically which will populate the <tt class="docutils literal"><span class="pre">.user</span></tt> attribute of each
returned <tt class="docutils literal"><span class="pre">Address</span></tt>. The <a class="reference internal" href="#sqlalchemy.orm.backref" title="sqlalchemy.orm.backref"><tt class="xref py py-func docutils literal"><span class="pre">backref()</span></tt></a> function formatted the arguments we gave
it into a form that is interpreted by the receiving <a class="reference internal" href="#sqlalchemy.orm.relationship" title="sqlalchemy.orm.relationship"><tt class="xref py py-func docutils literal"><span class="pre">relationship()</span></tt></a> as additional
arguments to be applied to the new relationship it creates.</p>
</div>
<div class="section" id="one-way-backrefs">
<h3>One Way Backrefs<a class="headerlink" href="#one-way-backrefs" title="Permalink to this headline">¶</a></h3>
<p>An unusual case is that of the “one way backref”. This is where the
“back-populating” behavior of the backref is only desirable in one
direction. An example of this is a collection which contains a
filtering <a class="reference internal" href="#sqlalchemy.orm.relationship.params.primaryjoin" title="sqlalchemy.orm.relationship"><tt class="xref py py-paramref docutils literal"><span class="pre">primaryjoin</span></tt></a> condition. We’d
like to append items to this collection as needed, and have them
populate the “parent” object on the incoming object. However, we’d
also like to have items that are not part of the collection, but still
have the same “parent” association - these items should never be in
the collection.</p>
<p>Taking our previous example, where we established a
<a class="reference internal" href="#sqlalchemy.orm.relationship.params.primaryjoin" title="sqlalchemy.orm.relationship"><tt class="xref py py-paramref docutils literal"><span class="pre">primaryjoin</span></tt></a> that limited the collection
only to <tt class="docutils literal"><span class="pre">Address</span></tt> objects whose email address started with the word
<tt class="docutils literal"><span class="pre">tony</span></tt>, the usual backref behavior is that all items populate in
both directions. We wouldn’t want this behavior for a case like the
following:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="n">u1</span> <span class="o">=</span> <span class="n">User</span><span class="p">()</span>
<span class="gp">>>> </span><span class="n">a1</span> <span class="o">=</span> <span class="n">Address</span><span class="p">(</span><span class="n">email</span><span class="o">=</span><span class="s">'mary'</span><span class="p">)</span>
<span class="gp">>>> </span><span class="n">a1</span><span class="o">.</span><span class="n">user</span> <span class="o">=</span> <span class="n">u1</span>
<span class="gp">>>> </span><span class="n">u1</span><span class="o">.</span><span class="n">addresses</span>
<span class="go">[<__main__.Address object at 0x1411910>]</span></pre></div>
</div>
<p>Above, the <tt class="docutils literal"><span class="pre">Address</span></tt> object that doesn’t match the criterion of “starts with ‘tony’”
is present in the <tt class="docutils literal"><span class="pre">addresses</span></tt> collection of <tt class="docutils literal"><span class="pre">u1</span></tt>. After these objects are flushed,
the transaction committed and their attributes expired for a re-load, the <tt class="docutils literal"><span class="pre">addresses</span></tt>
collection will hit the database on next access and no longer have this <tt class="docutils literal"><span class="pre">Address</span></tt> object
present, due to the filtering condition. But we can do away with this unwanted side
of the “backref” behavior on the Python side by using two separate <a class="reference internal" href="#sqlalchemy.orm.relationship" title="sqlalchemy.orm.relationship"><tt class="xref py py-func docutils literal"><span class="pre">relationship()</span></tt></a> constructs,
placing <a class="reference internal" href="#sqlalchemy.orm.relationship.params.back_populates" title="sqlalchemy.orm.relationship"><tt class="xref py py-paramref docutils literal"><span class="pre">back_populates</span></tt></a> only on one side:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="kn">from</span> <span class="nn">sqlalchemy</span> <span class="kn">import</span> <span class="n">Integer</span><span class="p">,</span> <span class="n">ForeignKey</span><span class="p">,</span> <span class="n">String</span><span class="p">,</span> <span class="n">Column</span>
<span class="kn">from</span> <span class="nn">sqlalchemy.ext.declarative</span> <span class="kn">import</span> <span class="n">declarative_base</span>
<span class="kn">from</span> <span class="nn">sqlalchemy.orm</span> <span class="kn">import</span> <span class="n">relationship</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">User</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="s">'user'</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="bp">True</span><span class="p">)</span>
<span class="n">name</span> <span class="o">=</span> <span class="n">Column</span><span class="p">(</span><span class="n">String</span><span class="p">)</span>
<span class="n">addresses</span> <span class="o">=</span> <span class="n">relationship</span><span class="p">(</span><span class="s">"Address"</span><span class="p">,</span>
<span class="n">primaryjoin</span><span class="o">=</span><span class="s">"and_(User.id==Address.user_id, "</span>
<span class="s">"Address.email.startswith('tony'))"</span><span class="p">,</span>
<span class="n">back_populates</span><span class="o">=</span><span class="s">"user"</span><span class="p">)</span>
<span class="k">class</span> <span class="nc">Address</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="s">'address'</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="bp">True</span><span class="p">)</span>
<span class="n">email</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">user_id</span> <span class="o">=</span> <span class="n">Column</span><span class="p">(</span><span class="n">Integer</span><span class="p">,</span> <span class="n">ForeignKey</span><span class="p">(</span><span class="s">'user.id'</span><span class="p">))</span>
<span class="n">user</span> <span class="o">=</span> <span class="n">relationship</span><span class="p">(</span><span class="s">"User"</span><span class="p">)</span></pre></div>
</div>
<p>With the above scenario, appending an <tt class="docutils literal"><span class="pre">Address</span></tt> object to the <tt class="docutils literal"><span class="pre">.addresses</span></tt>
collection of a <tt class="docutils literal"><span class="pre">User</span></tt> will always establish the <tt class="docutils literal"><span class="pre">.user</span></tt> attribute on that
<tt class="docutils literal"><span class="pre">Address</span></tt>:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="n">u1</span> <span class="o">=</span> <span class="n">User</span><span class="p">()</span>
<span class="gp">>>> </span><span class="n">a1</span> <span class="o">=</span> <span class="n">Address</span><span class="p">(</span><span class="n">email</span><span class="o">=</span><span class="s">'tony'</span><span class="p">)</span>
<span class="gp">>>> </span><span class="n">u1</span><span class="o">.</span><span class="n">addresses</span><span class="o">.</span><span class="n">append</span><span class="p">(</span><span class="n">a1</span><span class="p">)</span>
<span class="gp">>>> </span><span class="n">a1</span><span class="o">.</span><span class="n">user</span>
<span class="go"><__main__.User object at 0x1411850></span></pre></div>
</div>
<p>However, applying a <tt class="docutils literal"><span class="pre">User</span></tt> to the <tt class="docutils literal"><span class="pre">.user</span></tt> attribute of an <tt class="docutils literal"><span class="pre">Address</span></tt>,
will not append the <tt class="docutils literal"><span class="pre">Address</span></tt> object to the collection:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="n">a2</span> <span class="o">=</span> <span class="n">Address</span><span class="p">(</span><span class="n">email</span><span class="o">=</span><span class="s">'mary'</span><span class="p">)</span>
<span class="gp">>>> </span><span class="n">a2</span><span class="o">.</span><span class="n">user</span> <span class="o">=</span> <span class="n">u1</span>
<span class="gp">>>> </span><span class="n">a2</span> <span class="ow">in</span> <span class="n">u1</span><span class="o">.</span><span class="n">addresses</span>
<span class="go">False</span></pre></div>
</div>
<p>Of course, we’ve disabled some of the usefulness of
<a class="reference internal" href="#sqlalchemy.orm.relationship.params.backref" title="sqlalchemy.orm.relationship"><tt class="xref py py-paramref docutils literal"><span class="pre">backref</span></tt></a> here, in that when we do append an
<tt class="docutils literal"><span class="pre">Address</span></tt> that corresponds to the criteria of
<tt class="docutils literal"><span class="pre">email.startswith('tony')</span></tt>, it won’t show up in the
<tt class="docutils literal"><span class="pre">User.addresses</span></tt> collection until the session is flushed, and the
attributes reloaded after a commit or expire operation. While we
could consider an attribute event that checks this criterion in
Python, this starts to cross the line of duplicating too much SQL
behavior in Python. The backref behavior itself is only a slight
transgression of this philosophy - SQLAlchemy tries to keep these to a
minimum overall.</p>
</div>
</div>
<div class="section" id="configuring-how-relationship-joins">
<span id="relationship-configure-joins"></span><h2>Configuring how Relationship Joins<a class="headerlink" href="#configuring-how-relationship-joins" title="Permalink to this headline">¶</a></h2>
<p><a class="reference internal" href="#sqlalchemy.orm.relationship" title="sqlalchemy.orm.relationship"><tt class="xref py py-func docutils literal"><span class="pre">relationship()</span></tt></a> will normally create a join between two tables
by examining the foreign key relationship between the two tables
to determine which columns should be compared. There are a variety
of situations where this behavior needs to be customized.</p>
<div class="section" id="handling-multiple-join-paths">
<span id="relationship-foreign-keys"></span><h3>Handling Multiple Join Paths<a class="headerlink" href="#handling-multiple-join-paths" title="Permalink to this headline">¶</a></h3>
<p>One of the most common situations to deal with is when
there are more than one foreign key path between two tables.</p>
<p>Consider a <tt class="docutils literal"><span class="pre">Customer</span></tt> class that contains two foreign keys to an <tt class="docutils literal"><span class="pre">Address</span></tt>
class:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="kn">from</span> <span class="nn">sqlalchemy</span> <span class="kn">import</span> <span class="n">Integer</span><span class="p">,</span> <span class="n">ForeignKey</span><span class="p">,</span> <span class="n">String</span><span class="p">,</span> <span class="n">Column</span>
<span class="kn">from</span> <span class="nn">sqlalchemy.ext.declarative</span> <span class="kn">import</span> <span class="n">declarative_base</span>
<span class="kn">from</span> <span class="nn">sqlalchemy.orm</span> <span class="kn">import</span> <span class="n">relationship</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">Customer</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="s">'customer'</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="bp">True</span><span class="p">)</span>
<span class="n">name</span> <span class="o">=</span> <span class="n">Column</span><span class="p">(</span><span class="n">String</span><span class="p">)</span>
<span class="n">billing_address_id</span> <span class="o">=</span> <span class="n">Column</span><span class="p">(</span><span class="n">Integer</span><span class="p">,</span> <span class="n">ForeignKey</span><span class="p">(</span><span class="s">"address.id"</span><span class="p">))</span>
<span class="n">shipping_address_id</span> <span class="o">=</span> <span class="n">Column</span><span class="p">(</span><span class="n">Integer</span><span class="p">,</span> <span class="n">ForeignKey</span><span class="p">(</span><span class="s">"address.id"</span><span class="p">))</span>
<span class="n">billing_address</span> <span class="o">=</span> <span class="n">relationship</span><span class="p">(</span><span class="s">"Address"</span><span class="p">)</span>
<span class="n">shipping_address</span> <span class="o">=</span> <span class="n">relationship</span><span class="p">(</span><span class="s">"Address"</span><span class="p">)</span>
<span class="k">class</span> <span class="nc">Address</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="s">'address'</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="bp">True</span><span class="p">)</span>
<span class="n">street</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">city</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">state</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="nb">zip</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>The above mapping, when we attempt to use it, will produce the error:</p>
<div class="highlight-python"><pre>sqlalchemy.exc.AmbiguousForeignKeysError: Could not determine join
condition between parent/child tables on relationship
Customer.billing_address - there are multiple foreign key
paths linking the tables. Specify the 'foreign_keys' argument,
providing a list of those columns which should be
counted as containing a foreign key reference to the parent table.</pre>
</div>
<p>The above message is pretty long. There are many potential messages
that <a class="reference internal" href="#sqlalchemy.orm.relationship" title="sqlalchemy.orm.relationship"><tt class="xref py py-func docutils literal"><span class="pre">relationship()</span></tt></a> can return, which have been carefully tailored
to detect a variety of common configurational issues; most will suggest
the additional configuration that’s needed to resolve the ambiguity
or other missing information.</p>
<p>In this case, the message wants us to qualify each <a class="reference internal" href="#sqlalchemy.orm.relationship" title="sqlalchemy.orm.relationship"><tt class="xref py py-func docutils literal"><span class="pre">relationship()</span></tt></a>
by instructing for each one which foreign key column should be considered, and
the appropriate form is as follows:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="k">class</span> <span class="nc">Customer</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="s">'customer'</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="bp">True</span><span class="p">)</span>
<span class="n">name</span> <span class="o">=</span> <span class="n">Column</span><span class="p">(</span><span class="n">String</span><span class="p">)</span>
<span class="n">billing_address_id</span> <span class="o">=</span> <span class="n">Column</span><span class="p">(</span><span class="n">Integer</span><span class="p">,</span> <span class="n">ForeignKey</span><span class="p">(</span><span class="s">"address.id"</span><span class="p">))</span>
<span class="n">shipping_address_id</span> <span class="o">=</span> <span class="n">Column</span><span class="p">(</span><span class="n">Integer</span><span class="p">,</span> <span class="n">ForeignKey</span><span class="p">(</span><span class="s">"address.id"</span><span class="p">))</span>
<span class="n">billing_address</span> <span class="o">=</span> <span class="n">relationship</span><span class="p">(</span><span class="s">"Address"</span><span class="p">,</span> <span class="n">foreign_keys</span><span class="o">=</span><span class="p">[</span><span class="n">billing_address_id</span><span class="p">])</span>
<span class="n">shipping_address</span> <span class="o">=</span> <span class="n">relationship</span><span class="p">(</span><span class="s">"Address"</span><span class="p">,</span> <span class="n">foreign_keys</span><span class="o">=</span><span class="p">[</span><span class="n">shipping_address_id</span><span class="p">])</span></pre></div>
</div>
<p>Above, we specify the <tt class="docutils literal"><span class="pre">foreign_keys</span></tt> argument, which is a <a class="reference internal" href="../core/metadata.html#sqlalchemy.schema.Column" title="sqlalchemy.schema.Column"><tt class="xref py py-class docutils literal"><span class="pre">Column</span></tt></a> or list
of <a class="reference internal" href="../core/metadata.html#sqlalchemy.schema.Column" title="sqlalchemy.schema.Column"><tt class="xref py py-class docutils literal"><span class="pre">Column</span></tt></a> objects which indicate those columns to be considered “foreign”,
or in other words, the columns that contain a value referring to a parent table.
Loading the <tt class="docutils literal"><span class="pre">Customer.billing_address</span></tt> relationship from a <tt class="docutils literal"><span class="pre">Customer</span></tt>
object will use the value present in <tt class="docutils literal"><span class="pre">billing_address_id</span></tt> in order to
identify the row in <tt class="docutils literal"><span class="pre">Address</span></tt> to be loaded; similarly, <tt class="docutils literal"><span class="pre">shipping_address_id</span></tt>
is used for the <tt class="docutils literal"><span class="pre">shipping_address</span></tt> relationship. The linkage of the two
columns also plays a role during persistence; the newly generated primary key
of a just-inserted <tt class="docutils literal"><span class="pre">Address</span></tt> object will be copied into the appropriate
foreign key column of an associated <tt class="docutils literal"><span class="pre">Customer</span></tt> object during a flush.</p>
<p>When specifying <tt class="docutils literal"><span class="pre">foreign_keys</span></tt> with Declarative, we can also use string
names to specify, however it is important that if using a list, the <strong>list
is part of the string</strong>:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">billing_address</span> <span class="o">=</span> <span class="n">relationship</span><span class="p">(</span><span class="s">"Address"</span><span class="p">,</span> <span class="n">foreign_keys</span><span class="o">=</span><span class="s">"[Customer.billing_address_id]"</span><span class="p">)</span></pre></div>
</div>
<p>In this specific example, the list is not necessary in any case as there’s only
one <a class="reference internal" href="../core/metadata.html#sqlalchemy.schema.Column" title="sqlalchemy.schema.Column"><tt class="xref py py-class docutils literal"><span class="pre">Column</span></tt></a> we need:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">billing_address</span> <span class="o">=</span> <span class="n">relationship</span><span class="p">(</span><span class="s">"Address"</span><span class="p">,</span> <span class="n">foreign_keys</span><span class="o">=</span><span class="s">"Customer.billing_address_id"</span><span class="p">)</span></pre></div>
</div>
<div class="versionchanged">
<p><span>Changed in version 0.8: </span><a class="reference internal" href="#sqlalchemy.orm.relationship" title="sqlalchemy.orm.relationship"><tt class="xref py py-func docutils literal"><span class="pre">relationship()</span></tt></a> can resolve ambiguity between foreign key targets on the
basis of the <tt class="docutils literal"><span class="pre">foreign_keys</span></tt> argument alone; the <a class="reference internal" href="#sqlalchemy.orm.relationship.params.primaryjoin" title="sqlalchemy.orm.relationship"><tt class="xref py py-paramref docutils literal"><span class="pre">primaryjoin</span></tt></a>
argument is no longer needed in this situation.</p>
</div>
</div>
<div class="section" id="specifying-alternate-join-conditions">
<span id="relationship-primaryjoin"></span><h3>Specifying Alternate Join Conditions<a class="headerlink" href="#specifying-alternate-join-conditions" title="Permalink to this headline">¶</a></h3>
<p>The default behavior of <a class="reference internal" href="#sqlalchemy.orm.relationship" title="sqlalchemy.orm.relationship"><tt class="xref py py-func docutils literal"><span class="pre">relationship()</span></tt></a> when constructing a join
is that it equates the value of primary key columns
on one side to that of foreign-key-referring columns on the other.
We can change this criterion to be anything we’d like using the <tt class="docutils literal"><span class="pre">primaryjoin</span></tt>
argument, as well as the <tt class="docutils literal"><span class="pre">secondaryjoin</span></tt> argument in the case when
a “secondary” table is used.</p>
<p>In the example below, using the <tt class="docutils literal"><span class="pre">User</span></tt> class
as well as an <tt class="docutils literal"><span class="pre">Address</span></tt> class which stores a street address, we
create a relationship <tt class="docutils literal"><span class="pre">boston_addresses</span></tt> which will only
load those <tt class="docutils literal"><span class="pre">Address</span></tt> objects which specify a city of “Boston”:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="kn">from</span> <span class="nn">sqlalchemy</span> <span class="kn">import</span> <span class="n">Integer</span><span class="p">,</span> <span class="n">ForeignKey</span><span class="p">,</span> <span class="n">String</span><span class="p">,</span> <span class="n">Column</span>
<span class="kn">from</span> <span class="nn">sqlalchemy.ext.declarative</span> <span class="kn">import</span> <span class="n">declarative_base</span>
<span class="kn">from</span> <span class="nn">sqlalchemy.orm</span> <span class="kn">import</span> <span class="n">relationship</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">User</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="s">'user'</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="bp">True</span><span class="p">)</span>
<span class="n">name</span> <span class="o">=</span> <span class="n">Column</span><span class="p">(</span><span class="n">String</span><span class="p">)</span>
<span class="n">boston_addresses</span> <span class="o">=</span> <span class="n">relationship</span><span class="p">(</span><span class="s">"Address"</span><span class="p">,</span>
<span class="n">primaryjoin</span><span class="o">=</span><span class="s">"and_(User.id==Address.user_id, "</span>
<span class="s">"Address.city=='Boston')"</span><span class="p">)</span>
<span class="k">class</span> <span class="nc">Address</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="s">'address'</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="bp">True</span><span class="p">)</span>
<span class="n">user_id</span> <span class="o">=</span> <span class="n">Column</span><span class="p">(</span><span class="n">Integer</span><span class="p">,</span> <span class="n">ForeignKey</span><span class="p">(</span><span class="s">'user.id'</span><span class="p">))</span>
<span class="n">street</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">city</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">state</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="nb">zip</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>Within this string SQL expression, we made use of the <a class="reference internal" href="../core/sqlelement.html#sqlalchemy.sql.expression.and_" title="sqlalchemy.sql.expression.and_"><tt class="xref py py-func docutils literal"><span class="pre">and_()</span></tt></a> conjunction construct to establish
two distinct predicates for the join condition - joining both the <tt class="docutils literal"><span class="pre">User.id</span></tt> and
<tt class="docutils literal"><span class="pre">Address.user_id</span></tt> columns to each other, as well as limiting rows in <tt class="docutils literal"><span class="pre">Address</span></tt>
to just <tt class="docutils literal"><span class="pre">city='Boston'</span></tt>. When using Declarative, rudimentary SQL functions like
<a class="reference internal" href="../core/sqlelement.html#sqlalchemy.sql.expression.and_" title="sqlalchemy.sql.expression.and_"><tt class="xref py py-func docutils literal"><span class="pre">and_()</span></tt></a> are automatically available in the evaluated namespace of a string
<a class="reference internal" href="#sqlalchemy.orm.relationship" title="sqlalchemy.orm.relationship"><tt class="xref py py-func docutils literal"><span class="pre">relationship()</span></tt></a> argument.</p>
<p>The custom criteria we use in a <a class="reference internal" href="#sqlalchemy.orm.relationship.params.primaryjoin" title="sqlalchemy.orm.relationship"><tt class="xref py py-paramref docutils literal"><span class="pre">primaryjoin</span></tt></a>
is generally only significant when SQLAlchemy is rendering SQL in
order to load or represent this relationship. That is, it’s used in
the SQL statement that’s emitted in order to perform a per-attribute
lazy load, or when a join is constructed at query time, such as via
<a class="reference internal" href="query.html#sqlalchemy.orm.query.Query.join" title="sqlalchemy.orm.query.Query.join"><tt class="xref py py-meth docutils literal"><span class="pre">Query.join()</span></tt></a>, or via the eager “joined” or “subquery” styles of
loading. When in-memory objects are being manipulated, we can place
any <tt class="docutils literal"><span class="pre">Address</span></tt> object we’d like into the <tt class="docutils literal"><span class="pre">boston_addresses</span></tt>
collection, regardless of what the value of the <tt class="docutils literal"><span class="pre">.city</span></tt> attribute
is. The objects will remain present in the collection until the
attribute is expired and re-loaded from the database where the
criterion is applied. When a flush occurs, the objects inside of
<tt class="docutils literal"><span class="pre">boston_addresses</span></tt> will be flushed unconditionally, assigning value
of the primary key <tt class="docutils literal"><span class="pre">user.id</span></tt> column onto the foreign-key-holding
<tt class="docutils literal"><span class="pre">address.user_id</span></tt> column for each row. The <tt class="docutils literal"><span class="pre">city</span></tt> criteria has no
effect here, as the flush process only cares about synchronizing
primary key values into referencing foreign key values.</p>
</div>
<div class="section" id="creating-custom-foreign-conditions">
<span id="relationship-custom-foreign"></span><h3>Creating Custom Foreign Conditions<a class="headerlink" href="#creating-custom-foreign-conditions" title="Permalink to this headline">¶</a></h3>
<p>Another element of the primary join condition is how those columns
considered “foreign” are determined. Usually, some subset of
<a class="reference internal" href="../core/metadata.html#sqlalchemy.schema.Column" title="sqlalchemy.schema.Column"><tt class="xref py py-class docutils literal"><span class="pre">Column</span></tt></a> objects will specify <a class="reference internal" href="../core/constraints.html#sqlalchemy.schema.ForeignKey" title="sqlalchemy.schema.ForeignKey"><tt class="xref py py-class docutils literal"><span class="pre">ForeignKey</span></tt></a>, or
otherwise be part of a <a class="reference internal" href="../core/constraints.html#sqlalchemy.schema.ForeignKeyConstraint" title="sqlalchemy.schema.ForeignKeyConstraint"><tt class="xref py py-class docutils literal"><span class="pre">ForeignKeyConstraint</span></tt></a> that’s relevant
to the join condition. <a class="reference internal" href="#sqlalchemy.orm.relationship" title="sqlalchemy.orm.relationship"><tt class="xref py py-func docutils literal"><span class="pre">relationship()</span></tt></a> looks to this foreign key
status as it decides how it should load and persist data for this
relationship. However, the <a class="reference internal" href="#sqlalchemy.orm.relationship.params.primaryjoin" title="sqlalchemy.orm.relationship"><tt class="xref py py-paramref docutils literal"><span class="pre">primaryjoin</span></tt></a>
argument can be used to create a join condition that doesn’t involve
any “schema” level foreign keys. We can combine
<a class="reference internal" href="#sqlalchemy.orm.relationship.params.primaryjoin" title="sqlalchemy.orm.relationship"><tt class="xref py py-paramref docutils literal"><span class="pre">primaryjoin</span></tt></a> along with
<a class="reference internal" href="#sqlalchemy.orm.relationship.params.foreign_keys" title="sqlalchemy.orm.relationship"><tt class="xref py py-paramref docutils literal"><span class="pre">foreign_keys</span></tt></a> and
<a class="reference internal" href="#sqlalchemy.orm.relationship.params.remote_side" title="sqlalchemy.orm.relationship"><tt class="xref py py-paramref docutils literal"><span class="pre">remote_side</span></tt></a> explicitly in order to
establish such a join.</p>
<p>Below, a class <tt class="docutils literal"><span class="pre">HostEntry</span></tt> joins to itself, equating the string <tt class="docutils literal"><span class="pre">content</span></tt>
column to the <tt class="docutils literal"><span class="pre">ip_address</span></tt> column, which is a Postgresql type called <tt class="docutils literal"><span class="pre">INET</span></tt>.
We need to use <a class="reference internal" href="../core/sqlelement.html#sqlalchemy.sql.expression.cast" title="sqlalchemy.sql.expression.cast"><tt class="xref py py-func docutils literal"><span class="pre">cast()</span></tt></a> in order to cast one side of the join to the
type of the other:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="kn">from</span> <span class="nn">sqlalchemy</span> <span class="kn">import</span> <span class="n">cast</span><span class="p">,</span> <span class="n">String</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="kn">import</span> <span class="n">relationship</span>
<span class="kn">from</span> <span class="nn">sqlalchemy.dialects.postgresql</span> <span class="kn">import</span> <span class="n">INET</span>
<span class="kn">from</span> <span class="nn">sqlalchemy.ext.declarative</span> <span class="kn">import</span> <span class="n">declarative_base</span>
<span class="n">Base</span> <span class="o">=</span> <span class="n">declarative_base</span><span class="p">()</span>
<span class="k">class</span> <span class="nc">HostEntry</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="s">'host_entry'</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="bp">True</span><span class="p">)</span>
<span class="n">ip_address</span> <span class="o">=</span> <span class="n">Column</span><span class="p">(</span><span class="n">INET</span><span class="p">)</span>
<span class="n">content</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">50</span><span class="p">))</span>
<span class="c"># relationship() using explicit foreign_keys, remote_side</span>
<span class="n">parent_host</span> <span class="o">=</span> <span class="n">relationship</span><span class="p">(</span><span class="s">"HostEntry"</span><span class="p">,</span>
<span class="n">primaryjoin</span><span class="o">=</span><span class="n">ip_address</span> <span class="o">==</span> <span class="n">cast</span><span class="p">(</span><span class="n">content</span><span class="p">,</span> <span class="n">INET</span><span class="p">),</span>
<span class="n">foreign_keys</span><span class="o">=</span><span class="n">content</span><span class="p">,</span>
<span class="n">remote_side</span><span class="o">=</span><span class="n">ip_address</span>
<span class="p">)</span></pre></div>
</div>
<p>The above relationship will produce a join like:</p>
<div class="highlight-python"><pre>SELECT host_entry.id, host_entry.ip_address, host_entry.content
FROM host_entry JOIN host_entry AS host_entry_1
ON host_entry_1.ip_address = CAST(host_entry.content AS INET)</pre>
</div>
<p>An alternative syntax to the above is to use the <a class="reference internal" href="#sqlalchemy.orm.foreign" title="sqlalchemy.orm.foreign"><tt class="xref py py-func docutils literal"><span class="pre">foreign()</span></tt></a> and
<a class="reference internal" href="#sqlalchemy.orm.remote" title="sqlalchemy.orm.remote"><tt class="xref py py-func docutils literal"><span class="pre">remote()</span></tt></a> <a class="reference internal" href="../glossary.html#term-annotations"><em class="xref std std-term">annotations</em></a>, inline within the <a class="reference internal" href="#sqlalchemy.orm.relationship.params.primaryjoin" title="sqlalchemy.orm.relationship"><tt class="xref py py-paramref docutils literal"><span class="pre">primaryjoin</span></tt></a> expression.
This syntax represents the annotations that <a class="reference internal" href="#sqlalchemy.orm.relationship" title="sqlalchemy.orm.relationship"><tt class="xref py py-func docutils literal"><span class="pre">relationship()</span></tt></a> normally
applies by itself to the join condition given the <a class="reference internal" href="#sqlalchemy.orm.relationship.params.foreign_keys" title="sqlalchemy.orm.relationship"><tt class="xref py py-paramref docutils literal"><span class="pre">foreign_keys</span></tt></a> and
<a class="reference internal" href="#sqlalchemy.orm.relationship.params.remote_side" title="sqlalchemy.orm.relationship"><tt class="xref py py-paramref docutils literal"><span class="pre">remote_side</span></tt></a> arguments; the functions are provided in the API in the
rare case that <a class="reference internal" href="#sqlalchemy.orm.relationship" title="sqlalchemy.orm.relationship"><tt class="xref py py-func docutils literal"><span class="pre">relationship()</span></tt></a> can’t determine the exact location
of these features on its own:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="kn">from</span> <span class="nn">sqlalchemy.orm</span> <span class="kn">import</span> <span class="n">foreign</span><span class="p">,</span> <span class="n">remote</span>
<span class="k">class</span> <span class="nc">HostEntry</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="s">'host_entry'</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="bp">True</span><span class="p">)</span>
<span class="n">ip_address</span> <span class="o">=</span> <span class="n">Column</span><span class="p">(</span><span class="n">INET</span><span class="p">)</span>
<span class="n">content</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">50</span><span class="p">))</span>
<span class="c"># relationship() using explicit foreign() and remote() annotations</span>
<span class="c"># in lieu of separate arguments</span>
<span class="n">parent_host</span> <span class="o">=</span> <span class="n">relationship</span><span class="p">(</span><span class="s">"HostEntry"</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">ip_address</span><span class="p">)</span> <span class="o">==</span> \
<span class="n">cast</span><span class="p">(</span><span class="n">foreign</span><span class="p">(</span><span class="n">content</span><span class="p">),</span> <span class="n">INET</span><span class="p">),</span>
<span class="p">)</span></pre></div>
</div>
</div>
<div class="section" id="self-referential-many-to-many-relationship">
<span id="self-referential-many-to-many"></span><h3>Self-Referential Many-to-Many Relationship<a class="headerlink" href="#self-referential-many-to-many-relationship" title="Permalink to this headline">¶</a></h3>
<p>Many to many relationships can be customized by one or both of <a class="reference internal" href="#sqlalchemy.orm.relationship.params.primaryjoin" title="sqlalchemy.orm.relationship"><tt class="xref py py-paramref docutils literal"><span class="pre">primaryjoin</span></tt></a>
and <a class="reference internal" href="#sqlalchemy.orm.relationship.params.secondaryjoin" title="sqlalchemy.orm.relationship"><tt class="xref py py-paramref docutils literal"><span class="pre">secondaryjoin</span></tt></a> - the latter is significant for a relationship that
specifies a many-to-many reference using the <a class="reference internal" href="#sqlalchemy.orm.relationship.params.secondary" title="sqlalchemy.orm.relationship"><tt class="xref py py-paramref docutils literal"><span class="pre">secondary</span></tt></a> argument.
A common situation which involves the usage of <a class="reference internal" href="#sqlalchemy.orm.relationship.params.primaryjoin" title="sqlalchemy.orm.relationship"><tt class="xref py py-paramref docutils literal"><span class="pre">primaryjoin</span></tt></a> and <a class="reference internal" href="#sqlalchemy.orm.relationship.params.secondaryjoin" title="sqlalchemy.orm.relationship"><tt class="xref py py-paramref docutils literal"><span class="pre">secondaryjoin</span></tt></a>
is when establishing a many-to-many relationship from a class to itself, as shown below:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="kn">from</span> <span class="nn">sqlalchemy</span> <span class="kn">import</span> <span class="n">Integer</span><span class="p">,</span> <span class="n">ForeignKey</span><span class="p">,</span> <span class="n">String</span><span class="p">,</span> <span class="n">Column</span><span class="p">,</span> <span class="n">Table</span>
<span class="kn">from</span> <span class="nn">sqlalchemy.ext.declarative</span> <span class="kn">import</span> <span class="n">declarative_base</span>
<span class="kn">from</span> <span class="nn">sqlalchemy.orm</span> <span class="kn">import</span> <span class="n">relationship</span>
<span class="n">Base</span> <span class="o">=</span> <span class="n">declarative_base</span><span class="p">()</span>
<span class="n">node_to_node</span> <span class="o">=</span> <span class="n">Table</span><span class="p">(</span><span class="s">"node_to_node"</span><span class="p">,</span> <span class="n">Base</span><span class="o">.</span><span class="n">metadata</span><span class="p">,</span>
<span class="n">Column</span><span class="p">(</span><span class="s">"left_node_id"</span><span class="p">,</span> <span class="n">Integer</span><span class="p">,</span> <span class="n">ForeignKey</span><span class="p">(</span><span class="s">"node.id"</span><span class="p">),</span> <span class="n">primary_key</span><span class="o">=</span><span class="bp">True</span><span class="p">),</span>
<span class="n">Column</span><span class="p">(</span><span class="s">"right_node_id"</span><span class="p">,</span> <span class="n">Integer</span><span class="p">,</span> <span class="n">ForeignKey</span><span class="p">(</span><span class="s">"node.id"</span><span class="p">),</span> <span class="n">primary_key</span><span class="o">=</span><span class="bp">True</span><span class="p">)</span>
<span class="p">)</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="s">'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="bp">True</span><span class="p">)</span>
<span class="n">label</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">right_nodes</span> <span class="o">=</span> <span class="n">relationship</span><span class="p">(</span><span class="s">"Node"</span><span class="p">,</span>
<span class="n">secondary</span><span class="o">=</span><span class="n">node_to_node</span><span class="p">,</span>
<span class="n">primaryjoin</span><span class="o">=</span><span class="nb">id</span><span class="o">==</span><span class="n">node_to_node</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">left_node_id</span><span class="p">,</span>
<span class="n">secondaryjoin</span><span class="o">=</span><span class="nb">id</span><span class="o">==</span><span class="n">node_to_node</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">right_node_id</span><span class="p">,</span>
<span class="n">backref</span><span class="o">=</span><span class="s">"left_nodes"</span>
<span class="p">)</span></pre></div>
</div>
<p>Where above, SQLAlchemy can’t know automatically which columns should connect
to which for the <tt class="docutils literal"><span class="pre">right_nodes</span></tt> and <tt class="docutils literal"><span class="pre">left_nodes</span></tt> relationships. The <a class="reference internal" href="#sqlalchemy.orm.relationship.params.primaryjoin" title="sqlalchemy.orm.relationship"><tt class="xref py py-paramref docutils literal"><span class="pre">primaryjoin</span></tt></a>
and <a class="reference internal" href="#sqlalchemy.orm.relationship.params.secondaryjoin" title="sqlalchemy.orm.relationship"><tt class="xref py py-paramref docutils literal"><span class="pre">secondaryjoin</span></tt></a> arguments establish how we’d like to join to the association table.
In the Declarative form above, as we are declaring these conditions within the Python
block that corresponds to the <tt class="docutils literal"><span class="pre">Node</span></tt> class, the <tt class="docutils literal"><span class="pre">id</span></tt> variable is available directly
as the <a class="reference internal" href="../core/metadata.html#sqlalchemy.schema.Column" title="sqlalchemy.schema.Column"><tt class="xref py py-class docutils literal"><span class="pre">Column</span></tt></a> object we wish to join with.</p>
<p>A classical mapping situation here is similar, where <tt class="docutils literal"><span class="pre">node_to_node</span></tt> can be joined
to <tt class="docutils literal"><span class="pre">node.c.id</span></tt>:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="kn">from</span> <span class="nn">sqlalchemy</span> <span class="kn">import</span> <span class="n">Integer</span><span class="p">,</span> <span class="n">ForeignKey</span><span class="p">,</span> <span class="n">String</span><span class="p">,</span> <span class="n">Column</span><span class="p">,</span> <span class="n">Table</span><span class="p">,</span> <span class="n">MetaData</span>
<span class="kn">from</span> <span class="nn">sqlalchemy.orm</span> <span class="kn">import</span> <span class="n">relationship</span><span class="p">,</span> <span class="n">mapper</span>
<span class="n">metadata</span> <span class="o">=</span> <span class="n">MetaData</span><span class="p">()</span>
<span class="n">node_to_node</span> <span class="o">=</span> <span class="n">Table</span><span class="p">(</span><span class="s">"node_to_node"</span><span class="p">,</span> <span class="n">metadata</span><span class="p">,</span>
<span class="n">Column</span><span class="p">(</span><span class="s">"left_node_id"</span><span class="p">,</span> <span class="n">Integer</span><span class="p">,</span> <span class="n">ForeignKey</span><span class="p">(</span><span class="s">"node.id"</span><span class="p">),</span> <span class="n">primary_key</span><span class="o">=</span><span class="bp">True</span><span class="p">),</span>
<span class="n">Column</span><span class="p">(</span><span class="s">"right_node_id"</span><span class="p">,</span> <span class="n">Integer</span><span class="p">,</span> <span class="n">ForeignKey</span><span class="p">(</span><span class="s">"node.id"</span><span class="p">),</span> <span class="n">primary_key</span><span class="o">=</span><span class="bp">True</span><span class="p">)</span>
<span class="p">)</span>
<span class="n">node</span> <span class="o">=</span> <span class="n">Table</span><span class="p">(</span><span class="s">"node"</span><span class="p">,</span> <span class="n">metadata</span><span class="p">,</span>
<span class="n">Column</span><span class="p">(</span><span class="s">'id'</span><span class="p">,</span> <span class="n">Integer</span><span class="p">,</span> <span class="n">primary_key</span><span class="o">=</span><span class="bp">True</span><span class="p">),</span>
<span class="n">Column</span><span class="p">(</span><span class="s">'label'</span><span class="p">,</span> <span class="n">String</span><span class="p">)</span>
<span class="p">)</span>
<span class="k">class</span> <span class="nc">Node</span><span class="p">(</span><span class="nb">object</span><span class="p">):</span>
<span class="k">pass</span>
<span class="n">mapper</span><span class="p">(</span><span class="n">Node</span><span class="p">,</span> <span class="n">node</span><span class="p">,</span> <span class="n">properties</span><span class="o">=</span><span class="p">{</span>
<span class="s">'right_nodes'</span><span class="p">:</span><span class="n">relationship</span><span class="p">(</span><span class="n">Node</span><span class="p">,</span>
<span class="n">secondary</span><span class="o">=</span><span class="n">node_to_node</span><span class="p">,</span>
<span class="n">primaryjoin</span><span class="o">=</span><span class="n">node</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">node_to_node</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">left_node_id</span><span class="p">,</span>
<span class="n">secondaryjoin</span><span class="o">=</span><span class="n">node</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">node_to_node</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">right_node_id</span><span class="p">,</span>
<span class="n">backref</span><span class="o">=</span><span class="s">"left_nodes"</span>
<span class="p">)})</span></pre></div>
</div>
<p>Note that in both examples, the <a class="reference internal" href="#sqlalchemy.orm.relationship.params.backref" title="sqlalchemy.orm.relationship"><tt class="xref py py-paramref docutils literal"><span class="pre">backref</span></tt></a>
keyword specifies a <tt class="docutils literal"><span class="pre">left_nodes</span></tt> backref - when
<a class="reference internal" href="#sqlalchemy.orm.relationship" title="sqlalchemy.orm.relationship"><tt class="xref py py-func docutils literal"><span class="pre">relationship()</span></tt></a> creates the second relationship in the reverse
direction, it’s smart enough to reverse the
<a class="reference internal" href="#sqlalchemy.orm.relationship.params.primaryjoin" title="sqlalchemy.orm.relationship"><tt class="xref py py-paramref docutils literal"><span class="pre">primaryjoin</span></tt></a> and
<a class="reference internal" href="#sqlalchemy.orm.relationship.params.secondaryjoin" title="sqlalchemy.orm.relationship"><tt class="xref py py-paramref docutils literal"><span class="pre">secondaryjoin</span></tt></a> arguments.</p>
</div>
<div class="section" id="building-query-enabled-properties">
<h3>Building Query-Enabled Properties<a class="headerlink" href="#building-query-enabled-properties" title="Permalink to this headline">¶</a></h3>
<p>Very ambitious custom join conditions may fail to be directly persistable, and
in some cases may not even load correctly. To remove the persistence part of
the equation, use the flag <a class="reference internal" href="#sqlalchemy.orm.relationship.params.viewonly" title="sqlalchemy.orm.relationship"><tt class="xref py py-paramref docutils literal"><span class="pre">viewonly</span></tt></a> on the
<a class="reference internal" href="#sqlalchemy.orm.relationship" title="sqlalchemy.orm.relationship"><tt class="xref py py-func docutils literal"><span class="pre">relationship()</span></tt></a>, which establishes it as a read-only
attribute (data written to the collection will be ignored on flush()).
However, in extreme cases, consider using a regular Python property in
conjunction with <a class="reference internal" href="query.html#sqlalchemy.orm.query.Query" title="sqlalchemy.orm.query.Query"><tt class="xref py py-class docutils literal"><span class="pre">Query</span></tt></a> as follows:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="k">class</span> <span class="nc">User</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="s">'user'</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="bp">True</span><span class="p">)</span>
<span class="k">def</span> <span class="nf">_get_addresses</span><span class="p">(</span><span class="bp">self</span><span class="p">):</span>
<span class="k">return</span> <span class="n">object_session</span><span class="p">(</span><span class="bp">self</span><span class="p">)</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="n">Address</span><span class="p">)</span><span class="o">.</span><span class="n">with_parent</span><span class="p">(</span><span class="bp">self</span><span class="p">)</span><span class="o">.</span><span class="n">filter</span><span class="p">(</span><span class="o">...</span><span class="p">)</span><span class="o">.</span><span class="n">all</span><span class="p">()</span>
<span class="n">addresses</span> <span class="o">=</span> <span class="nb">property</span><span class="p">(</span><span class="n">_get_addresses</span><span class="p">)</span></pre></div>
</div>
</div>
</div>
<div class="section" id="rows-that-point-to-themselves-mutually-dependent-rows">
<span id="post-update"></span><h2>Rows that point to themselves / Mutually Dependent Rows<a class="headerlink" href="#rows-that-point-to-themselves-mutually-dependent-rows" title="Permalink to this headline">¶</a></h2>
<p>This is a very specific case where relationship() must perform an INSERT and a
second UPDATE in order to properly populate a row (and vice versa an UPDATE
and DELETE in order to delete without violating foreign key constraints). The
two use cases are:</p>
<ul class="simple">
<li>A table contains a foreign key to itself, and a single row will
have a foreign key value pointing to its own primary key.</li>
<li>Two tables each contain a foreign key referencing the other
table, with a row in each table referencing the other.</li>
</ul>
<p>For example:</p>
<div class="highlight-python"><pre> user
---------------------------------
user_id name related_user_id
1 'ed' 1</pre>
</div>
<p>Or:</p>
<div class="highlight-python"><pre> widget entry
------------------------------------------- ---------------------------------
widget_id name favorite_entry_id entry_id name widget_id
1 'somewidget' 5 5 'someentry' 1</pre>
</div>
<p>In the first case, a row points to itself. Technically, a database that uses
sequences such as PostgreSQL or Oracle can INSERT the row at once using a
previously generated value, but databases which rely upon autoincrement-style
primary key identifiers cannot. The <a class="reference internal" href="#sqlalchemy.orm.relationship" title="sqlalchemy.orm.relationship"><tt class="xref py py-func docutils literal"><span class="pre">relationship()</span></tt></a>
always assumes a “parent/child” model of row population during flush, so
unless you are populating the primary key/foreign key columns directly,
<a class="reference internal" href="#sqlalchemy.orm.relationship" title="sqlalchemy.orm.relationship"><tt class="xref py py-func docutils literal"><span class="pre">relationship()</span></tt></a> needs to use two statements.</p>
<p>In the second case, the “widget” row must be inserted before any referring
“entry” rows, but then the “favorite_entry_id” column of that “widget” row
cannot be set until the “entry” rows have been generated. In this case, it’s
typically impossible to insert the “widget” and “entry” rows using just two
INSERT statements; an UPDATE must be performed in order to keep foreign key
constraints fulfilled. The exception is if the foreign keys are configured as
“deferred until commit” (a feature some databases support) and if the
identifiers were populated manually (again essentially bypassing
<a class="reference internal" href="#sqlalchemy.orm.relationship" title="sqlalchemy.orm.relationship"><tt class="xref py py-func docutils literal"><span class="pre">relationship()</span></tt></a>).</p>
<p>To enable the usage of a supplementary UPDATE statement,
we use the <a class="reference internal" href="#sqlalchemy.orm.relationship.params.post_update" title="sqlalchemy.orm.relationship"><tt class="xref py py-paramref docutils literal"><span class="pre">post_update</span></tt></a> option
of <a class="reference internal" href="#sqlalchemy.orm.relationship" title="sqlalchemy.orm.relationship"><tt class="xref py py-func docutils literal"><span class="pre">relationship()</span></tt></a>. This specifies that the linkage between the
two rows should be created using an UPDATE statement after both rows
have been INSERTED; it also causes the rows to be de-associated with
each other via UPDATE before a DELETE is emitted. The flag should
be placed on just <em>one</em> of the relationships, preferably the
many-to-one side. Below we illustrate
a complete example, including two <a class="reference internal" href="../core/constraints.html#sqlalchemy.schema.ForeignKey" title="sqlalchemy.schema.ForeignKey"><tt class="xref py py-class docutils literal"><span class="pre">ForeignKey</span></tt></a> constructs, one which
specifies <a class="reference internal" href="../core/constraints.html#sqlalchemy.schema.ForeignKey.params.use_alter" title="sqlalchemy.schema.ForeignKey"><tt class="xref py py-paramref docutils literal"><span class="pre">use_alter</span></tt></a> to help with emitting CREATE TABLE statements:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="kn">from</span> <span class="nn">sqlalchemy</span> <span class="kn">import</span> <span class="n">Integer</span><span class="p">,</span> <span class="n">ForeignKey</span><span class="p">,</span> <span class="n">Column</span>
<span class="kn">from</span> <span class="nn">sqlalchemy.ext.declarative</span> <span class="kn">import</span> <span class="n">declarative_base</span>
<span class="kn">from</span> <span class="nn">sqlalchemy.orm</span> <span class="kn">import</span> <span class="n">relationship</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">Entry</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="s">'entry'</span>
<span class="n">entry_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="bp">True</span><span class="p">)</span>
<span class="n">widget_id</span> <span class="o">=</span> <span class="n">Column</span><span class="p">(</span><span class="n">Integer</span><span class="p">,</span> <span class="n">ForeignKey</span><span class="p">(</span><span class="s">'widget.widget_id'</span><span class="p">))</span>
<span class="n">name</span> <span class="o">=</span> <span class="n">Column</span><span class="p">(</span><span class="n">String</span><span class="p">(</span><span class="mi">50</span><span class="p">))</span>
<span class="k">class</span> <span class="nc">Widget</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="s">'widget'</span>
<span class="n">widget_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="bp">True</span><span class="p">)</span>
<span class="n">favorite_entry_id</span> <span class="o">=</span> <span class="n">Column</span><span class="p">(</span><span class="n">Integer</span><span class="p">,</span>
<span class="n">ForeignKey</span><span class="p">(</span><span class="s">'entry.entry_id'</span><span class="p">,</span>
<span class="n">use_alter</span><span class="o">=</span><span class="bp">True</span><span class="p">,</span>
<span class="n">name</span><span class="o">=</span><span class="s">"fk_favorite_entry"</span><span class="p">))</span>
<span class="n">name</span> <span class="o">=</span> <span class="n">Column</span><span class="p">(</span><span class="n">String</span><span class="p">(</span><span class="mi">50</span><span class="p">))</span>
<span class="n">entries</span> <span class="o">=</span> <span class="n">relationship</span><span class="p">(</span><span class="n">Entry</span><span class="p">,</span> <span class="n">primaryjoin</span><span class="o">=</span>
<span class="n">widget_id</span><span class="o">==</span><span class="n">Entry</span><span class="o">.</span><span class="n">widget_id</span><span class="p">)</span>
<span class="n">favorite_entry</span> <span class="o">=</span> <span class="n">relationship</span><span class="p">(</span><span class="n">Entry</span><span class="p">,</span>
<span class="n">primaryjoin</span><span class="o">=</span>
<span class="n">favorite_entry_id</span><span class="o">==</span><span class="n">Entry</span><span class="o">.</span><span class="n">entry_id</span><span class="p">,</span>
<span class="n">post_update</span><span class="o">=</span><span class="bp">True</span><span class="p">)</span></pre></div>
</div>
<p>When a structure against the above configuration is flushed, the “widget” row will be
INSERTed minus the “favorite_entry_id” value, then all the “entry” rows will
be INSERTed referencing the parent “widget” row, and then an UPDATE statement
will populate the “favorite_entry_id” column of the “widget” table (it’s one
row at a time for the time being):</p>
<div class="highlight-pycon+sql"><div class="highlight"><pre><span class="gp">>>> </span><span class="n">w1</span> <span class="o">=</span> <span class="n">Widget</span><span class="p">(</span><span class="n">name</span><span class="o">=</span><span class="s">'somewidget'</span><span class="p">)</span>
<span class="gp">>>> </span><span class="n">e1</span> <span class="o">=</span> <span class="n">Entry</span><span class="p">(</span><span class="n">name</span><span class="o">=</span><span class="s">'someentry'</span><span class="p">)</span>
<span class="gp">>>> </span><span class="n">w1</span><span class="o">.</span><span class="n">favorite_entry</span> <span class="o">=</span> <span class="n">e1</span>
<span class="gp">>>> </span><span class="n">w1</span><span class="o">.</span><span class="n">entries</span> <span class="o">=</span> <span class="p">[</span><span class="n">e1</span><span class="p">]</span>
<span class="gp">>>> </span><span class="n">session</span><span class="o">.</span><span class="n">add_all</span><span class="p">([</span><span class="n">w1</span><span class="p">,</span> <span class="n">e1</span><span class="p">])</span>
<a href='#' class='sql_link'>sql</a><span class="gp">>>> </span><span class="n">session</span><span class="o">.</span><span class="n">commit</span><span class="p">()</span>
<div class='popup_sql'>BEGIN (implicit)
INSERT INTO widget (favorite_entry_id, name) VALUES (?, ?)
(None, 'somewidget')
INSERT INTO entry (widget_id, name) VALUES (?, ?)
(1, 'someentry')
UPDATE widget SET favorite_entry_id=? WHERE widget.widget_id = ?
(1, 1)
COMMIT</div></pre></div>
</div>
<p>An additional configuration we can specify is to supply a more
comprehensive foreign key constraint on <tt class="docutils literal"><span class="pre">Widget</span></tt>, such that
it’s guaranteed that <tt class="docutils literal"><span class="pre">favorite_entry_id</span></tt> refers to an <tt class="docutils literal"><span class="pre">Entry</span></tt>
that also refers to this <tt class="docutils literal"><span class="pre">Widget</span></tt>. We can use a composite foreign key,
as illustrated below:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="kn">from</span> <span class="nn">sqlalchemy</span> <span class="kn">import</span> <span class="n">Integer</span><span class="p">,</span> <span class="n">ForeignKey</span><span class="p">,</span> <span class="n">String</span><span class="p">,</span> \
<span class="n">Column</span><span class="p">,</span> <span class="n">UniqueConstraint</span><span class="p">,</span> <span class="n">ForeignKeyConstraint</span>
<span class="kn">from</span> <span class="nn">sqlalchemy.ext.declarative</span> <span class="kn">import</span> <span class="n">declarative_base</span>
<span class="kn">from</span> <span class="nn">sqlalchemy.orm</span> <span class="kn">import</span> <span class="n">relationship</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">Entry</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="s">'entry'</span>
<span class="n">entry_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="bp">True</span><span class="p">)</span>
<span class="n">widget_id</span> <span class="o">=</span> <span class="n">Column</span><span class="p">(</span><span class="n">Integer</span><span class="p">,</span> <span class="n">ForeignKey</span><span class="p">(</span><span class="s">'widget.widget_id'</span><span class="p">))</span>
<span class="n">name</span> <span class="o">=</span> <span class="n">Column</span><span class="p">(</span><span class="n">String</span><span class="p">(</span><span class="mi">50</span><span class="p">))</span>
<span class="n">__table_args__</span> <span class="o">=</span> <span class="p">(</span>
<span class="n">UniqueConstraint</span><span class="p">(</span><span class="s">"entry_id"</span><span class="p">,</span> <span class="s">"widget_id"</span><span class="p">),</span>
<span class="p">)</span>
<span class="k">class</span> <span class="nc">Widget</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="s">'widget'</span>
<span class="n">widget_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">autoincrement</span><span class="o">=</span><span class="s">'ignore_fk'</span><span class="p">,</span> <span class="n">primary_key</span><span class="o">=</span><span class="bp">True</span><span class="p">)</span>
<span class="n">favorite_entry_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">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">50</span><span class="p">))</span>
<span class="n">__table_args__</span> <span class="o">=</span> <span class="p">(</span>
<span class="n">ForeignKeyConstraint</span><span class="p">(</span>
<span class="p">[</span><span class="s">"widget_id"</span><span class="p">,</span> <span class="s">"favorite_entry_id"</span><span class="p">],</span>
<span class="p">[</span><span class="s">"entry.widget_id"</span><span class="p">,</span> <span class="s">"entry.entry_id"</span><span class="p">],</span>
<span class="n">name</span><span class="o">=</span><span class="s">"fk_favorite_entry"</span><span class="p">,</span> <span class="n">use_alter</span><span class="o">=</span><span class="bp">True</span>
<span class="p">),</span>
<span class="p">)</span>
<span class="n">entries</span> <span class="o">=</span> <span class="n">relationship</span><span class="p">(</span><span class="n">Entry</span><span class="p">,</span> <span class="n">primaryjoin</span><span class="o">=</span>
<span class="n">widget_id</span><span class="o">==</span><span class="n">Entry</span><span class="o">.</span><span class="n">widget_id</span><span class="p">,</span>
<span class="n">foreign_keys</span><span class="o">=</span><span class="n">Entry</span><span class="o">.</span><span class="n">widget_id</span><span class="p">)</span>
<span class="n">favorite_entry</span> <span class="o">=</span> <span class="n">relationship</span><span class="p">(</span><span class="n">Entry</span><span class="p">,</span>
<span class="n">primaryjoin</span><span class="o">=</span>
<span class="n">favorite_entry_id</span><span class="o">==</span><span class="n">Entry</span><span class="o">.</span><span class="n">entry_id</span><span class="p">,</span>
<span class="n">foreign_keys</span><span class="o">=</span><span class="n">favorite_entry_id</span><span class="p">,</span>
<span class="n">post_update</span><span class="o">=</span><span class="bp">True</span><span class="p">)</span></pre></div>
</div>
<p>The above mapping features a composite <a class="reference internal" href="../core/constraints.html#sqlalchemy.schema.ForeignKeyConstraint" title="sqlalchemy.schema.ForeignKeyConstraint"><tt class="xref py py-class docutils literal"><span class="pre">ForeignKeyConstraint</span></tt></a>
bridging the <tt class="docutils literal"><span class="pre">widget_id</span></tt> and <tt class="docutils literal"><span class="pre">favorite_entry_id</span></tt> columns. To ensure
that <tt class="docutils literal"><span class="pre">Widget.widget_id</span></tt> remains an “autoincrementing” column we specify
<a class="reference internal" href="../core/metadata.html#sqlalchemy.schema.Column.params.autoincrement" title="sqlalchemy.schema.Column"><tt class="xref py py-paramref docutils literal"><span class="pre">autoincrement</span></tt></a> to the value <tt class="docutils literal"><span class="pre">"ignore_fk"</span></tt>
on <a class="reference internal" href="../core/metadata.html#sqlalchemy.schema.Column" title="sqlalchemy.schema.Column"><tt class="xref py py-class docutils literal"><span class="pre">Column</span></tt></a>, and additionally on each
<a class="reference internal" href="#sqlalchemy.orm.relationship" title="sqlalchemy.orm.relationship"><tt class="xref py py-func docutils literal"><span class="pre">relationship()</span></tt></a> we must limit those columns considered as part of
the foreign key for the purposes of joining and cross-population.</p>
</div>
<div class="section" id="mutable-primary-keys-update-cascades">
<span id="passive-updates"></span><h2>Mutable Primary Keys / Update Cascades<a class="headerlink" href="#mutable-primary-keys-update-cascades" title="Permalink to this headline">¶</a></h2>
<p>When the primary key of an entity changes, related items
which reference the primary key must also be updated as
well. For databases which enforce referential integrity,
it’s required to use the database’s ON UPDATE CASCADE
functionality in order to propagate primary key changes
to referenced foreign keys - the values cannot be out
of sync for any moment.</p>
<p>For databases that don’t support this, such as SQLite and
MySQL without their referential integrity options turned
on, the <a class="reference internal" href="#sqlalchemy.orm.relationship.params.passive_updates" title="sqlalchemy.orm.relationship"><tt class="xref py py-paramref docutils literal"><span class="pre">passive_updates</span></tt></a> flag can
be set to <tt class="docutils literal"><span class="pre">False</span></tt>, most preferably on a one-to-many or
many-to-many <a class="reference internal" href="#sqlalchemy.orm.relationship" title="sqlalchemy.orm.relationship"><tt class="xref py py-func docutils literal"><span class="pre">relationship()</span></tt></a>, which instructs
SQLAlchemy to issue UPDATE statements individually for
objects referenced in the collection, loading them into
memory if not already locally present. The
<a class="reference internal" href="#sqlalchemy.orm.relationship.params.passive_updates" title="sqlalchemy.orm.relationship"><tt class="xref py py-paramref docutils literal"><span class="pre">passive_updates</span></tt></a> flag can also be <tt class="docutils literal"><span class="pre">False</span></tt> in
conjunction with ON UPDATE CASCADE functionality,
although in that case the unit of work will be issuing
extra SELECT and UPDATE statements unnecessarily.</p>
<p>A typical mutable primary key setup might look like:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="k">class</span> <span class="nc">User</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="s">'user'</span>
<span class="n">username</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">50</span><span class="p">),</span> <span class="n">primary_key</span><span class="o">=</span><span class="bp">True</span><span class="p">)</span>
<span class="n">fullname</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">100</span><span class="p">))</span>
<span class="c"># passive_updates=False *only* needed if the database</span>
<span class="c"># does not implement ON UPDATE CASCADE</span>
<span class="n">addresses</span> <span class="o">=</span> <span class="n">relationship</span><span class="p">(</span><span class="s">"Address"</span><span class="p">,</span> <span class="n">passive_updates</span><span class="o">=</span><span class="bp">False</span><span class="p">)</span>
<span class="k">class</span> <span class="nc">Address</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="s">'address'</span>
<span class="n">email</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">50</span><span class="p">),</span> <span class="n">primary_key</span><span class="o">=</span><span class="bp">True</span><span class="p">)</span>
<span class="n">username</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">50</span><span class="p">),</span>
<span class="n">ForeignKey</span><span class="p">(</span><span class="s">'user.username'</span><span class="p">,</span> <span class="n">onupdate</span><span class="o">=</span><span class="s">"cascade"</span><span class="p">)</span>
<span class="p">)</span></pre></div>
</div>
<p><a class="reference internal" href="#sqlalchemy.orm.relationship.params.passive_updates" title="sqlalchemy.orm.relationship"><tt class="xref py py-paramref docutils literal"><span class="pre">passive_updates</span></tt></a> is set to <tt class="docutils literal"><span class="pre">True</span></tt> by default,
indicating that ON UPDATE CASCADE is expected to be in
place in the usual case for foreign keys that expect
to have a mutating parent key.</p>
<p>A <a class="reference internal" href="#sqlalchemy.orm.relationship.params.passive_updates" title="sqlalchemy.orm.relationship"><tt class="xref py py-paramref docutils literal"><span class="pre">passive_updates</span></tt></a> setting of False may be configured on any
direction of relationship, i.e. one-to-many, many-to-one,
and many-to-many, although it is much more effective when
placed just on the one-to-many or many-to-many side.
Configuring the <a class="reference internal" href="#sqlalchemy.orm.relationship.params.passive_updates" title="sqlalchemy.orm.relationship"><tt class="xref py py-paramref docutils literal"><span class="pre">passive_updates</span></tt></a>
to False only on the
many-to-one side will have only a partial effect, as the
unit of work searches only through the current identity
map for objects that may be referencing the one with a
mutating primary key, not throughout the database.</p>
</div>
<div class="section" id="relationships-api">
<h2>Relationships API<a class="headerlink" href="#relationships-api" title="Permalink to this headline">¶</a></h2>
<dl class="function">
<dt id="sqlalchemy.orm.relationship">
<tt class="descclassname">sqlalchemy.orm.</tt><tt class="descname">relationship</tt><big>(</big><em>argument</em>, <em>secondary=None</em>, <em>**kwargs</em><big>)</big><a class="headerlink" href="#sqlalchemy.orm.relationship" title="Permalink to this definition">¶</a></dt>
<dd><p>Provide a relationship between two mapped classes.</p>
<p>This corresponds to a parent-child or associative table relationship. The
constructed class is an instance of <a class="reference internal" href="internals.html#sqlalchemy.orm.properties.RelationshipProperty" title="sqlalchemy.orm.properties.RelationshipProperty"><tt class="xref py py-class docutils literal"><span class="pre">RelationshipProperty</span></tt></a>.</p>
<p>A typical <a class="reference internal" href="#sqlalchemy.orm.relationship" title="sqlalchemy.orm.relationship"><tt class="xref py py-func docutils literal"><span class="pre">relationship()</span></tt></a>, used in a classical mapping:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">mapper</span><span class="p">(</span><span class="n">Parent</span><span class="p">,</span> <span class="n">properties</span><span class="o">=</span><span class="p">{</span>
<span class="s">'children'</span><span class="p">:</span> <span class="n">relationship</span><span class="p">(</span><span class="n">Child</span><span class="p">)</span>
<span class="p">})</span></pre></div>
</div>
<p>Some arguments accepted by <a class="reference internal" href="#sqlalchemy.orm.relationship" title="sqlalchemy.orm.relationship"><tt class="xref py py-func docutils literal"><span class="pre">relationship()</span></tt></a> optionally accept a
callable function, which when called produces the desired value.
The callable is invoked by the parent <a class="reference internal" href="mapper_config.html#sqlalchemy.orm.mapper.Mapper" title="sqlalchemy.orm.mapper.Mapper"><tt class="xref py py-class docutils literal"><span class="pre">Mapper</span></tt></a> at “mapper
initialization” time, which happens only when mappers are first used, and
is assumed to be after all mappings have been constructed. This can be
used to resolve order-of-declaration and other dependency issues, such as
if <tt class="docutils literal"><span class="pre">Child</span></tt> is declared below <tt class="docutils literal"><span class="pre">Parent</span></tt> in the same file:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">mapper</span><span class="p">(</span><span class="n">Parent</span><span class="p">,</span> <span class="n">properties</span><span class="o">=</span><span class="p">{</span>
<span class="s">"children"</span><span class="p">:</span><span class="n">relationship</span><span class="p">(</span><span class="k">lambda</span><span class="p">:</span> <span class="n">Child</span><span class="p">,</span>
<span class="n">order_by</span><span class="o">=</span><span class="k">lambda</span><span class="p">:</span> <span class="n">Child</span><span class="o">.</span><span class="n">id</span><span class="p">)</span>
<span class="p">})</span></pre></div>
</div>
<p>When using the <a class="reference internal" href="extensions/declarative.html"><em>Declarative</em></a> extension, the Declarative
initializer allows string arguments to be passed to <a class="reference internal" href="#sqlalchemy.orm.relationship" title="sqlalchemy.orm.relationship"><tt class="xref py py-func docutils literal"><span class="pre">relationship()</span></tt></a>.
These string arguments are converted into callables that evaluate
the string as Python code, using the Declarative
class-registry as a namespace. This allows the lookup of related
classes to be automatic via their string name, and removes the need to
import related classes at all into the local module space:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="kn">from</span> <span class="nn">sqlalchemy.ext.declarative</span> <span class="kn">import</span> <span class="n">declarative_base</span>
<span class="n">Base</span> <span class="o">=</span> <span class="n">declarative_base</span><span class="p">()</span>
<span class="k">class</span> <span class="nc">Parent</span><span class="p">(</span><span class="n">Base</span><span class="p">):</span>
<span class="n">__tablename__</span> <span class="o">=</span> <span class="s">'parent'</span>
<span class="nb">id</span> <span class="o">=</span> <span class="n">Column</span><span class="p">(</span><span class="n">Integer</span><span class="p">,</span> <span class="n">primary_key</span><span class="o">=</span><span class="bp">True</span><span class="p">)</span>
<span class="n">children</span> <span class="o">=</span> <span class="n">relationship</span><span class="p">(</span><span class="s">"Child"</span><span class="p">,</span> <span class="n">order_by</span><span class="o">=</span><span class="s">"Child.id"</span><span class="p">)</span></pre></div>
</div>
<div class="admonition seealso">
<p class="first admonition-title">See also</p>
<p><a class="reference internal" href="#"><em>Relationship Configuration</em></a> - Full introductory and reference
documentation for <a class="reference internal" href="#sqlalchemy.orm.relationship" title="sqlalchemy.orm.relationship"><tt class="xref py py-func docutils literal"><span class="pre">relationship()</span></tt></a>.</p>
<p class="last"><a class="reference internal" href="tutorial.html#orm-tutorial-relationship"><em>Building a Relationship</em></a> - ORM tutorial introduction.</p>
</div>
<table class="docutils field-list" frame="void" rules="none">
<col class="field-name" />
<col class="field-body" />
<tbody valign="top">
<tr class="field-odd field"><th class="field-name">Parameters:</th><td class="field-body"><ul class="first last simple">
<li><span class="target" id="sqlalchemy.orm.relationship.params.argument"></span><strong>argument</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.orm.relationship.params.argument">¶</a> – <p>a mapped class, or actual <a class="reference internal" href="mapper_config.html#sqlalchemy.orm.mapper.Mapper" title="sqlalchemy.orm.mapper.Mapper"><tt class="xref py py-class docutils literal"><span class="pre">Mapper</span></tt></a> instance, representing the
target of the relationship.</p>
<p><a class="reference internal" href="#sqlalchemy.orm.relationship.params.argument" title="sqlalchemy.orm.relationship"><tt class="xref py py-paramref docutils literal"><span class="pre">argument</span></tt></a> may also be passed as a callable function
which is evaluated at mapper initialization time, and may be passed as a
Python-evaluable string when using Declarative.</p>
<div class="admonition seealso">
<p class="first admonition-title">See also</p>
<p class="last"><a class="reference internal" href="extensions/declarative.html#declarative-configuring-relationships"><em>Configuring Relationships</em></a> - further detail
on relationship configuration when using Declarative.</p>
</div>
</li>
<li><span class="target" id="sqlalchemy.orm.relationship.params.secondary"></span><strong>secondary</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.orm.relationship.params.secondary">¶</a> – <p>for a many-to-many relationship, specifies the intermediary
table, and is typically an instance of <a class="reference internal" href="../core/metadata.html#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal"><span class="pre">Table</span></tt></a>.
In less common circumstances, the argument may also be specified
as an <a class="reference internal" href="../core/selectable.html#sqlalchemy.sql.expression.Alias" title="sqlalchemy.sql.expression.Alias"><tt class="xref py py-class docutils literal"><span class="pre">Alias</span></tt></a> construct, or even a <a class="reference internal" href="../core/selectable.html#sqlalchemy.sql.expression.Join" title="sqlalchemy.sql.expression.Join"><tt class="xref py py-class docutils literal"><span class="pre">Join</span></tt></a> construct.</p>
<p><a class="reference internal" href="#sqlalchemy.orm.relationship.params.secondary" title="sqlalchemy.orm.relationship"><tt class="xref py py-paramref docutils literal"><span class="pre">secondary</span></tt></a> may
also be passed as a callable function which is evaluated at
mapper initialization time. When using Declarative, it may also
be a string argument noting the name of a <a class="reference internal" href="../core/metadata.html#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal"><span class="pre">Table</span></tt></a> that is
present in the <a class="reference internal" href="../core/metadata.html#sqlalchemy.schema.MetaData" title="sqlalchemy.schema.MetaData"><tt class="xref py py-class docutils literal"><span class="pre">MetaData</span></tt></a> collection associated with the
parent-mapped <a class="reference internal" href="../core/metadata.html#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal"><span class="pre">Table</span></tt></a>.</p>
<p>The <a class="reference internal" href="#sqlalchemy.orm.relationship.params.secondary" title="sqlalchemy.orm.relationship"><tt class="xref py py-paramref docutils literal"><span class="pre">secondary</span></tt></a> keyword argument is typically
applied in the case where the intermediary <a class="reference internal" href="../core/metadata.html#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal"><span class="pre">Table</span></tt></a> is not
otherwise exprssed in any direct class mapping. If the “secondary” table
is also explicitly mapped elsewhere
(e.g. as in <a class="reference internal" href="#association-pattern"><em>Association Object</em></a>), one should consider applying
the <a class="reference internal" href="#sqlalchemy.orm.relationship.params.viewonly" title="sqlalchemy.orm.relationship"><tt class="xref py py-paramref docutils literal"><span class="pre">viewonly</span></tt></a> flag so that this <a class="reference internal" href="#sqlalchemy.orm.relationship" title="sqlalchemy.orm.relationship"><tt class="xref py py-func docutils literal"><span class="pre">relationship()</span></tt></a>
is not used for persistence operations which may conflict with those
of the association object pattern.</p>
<div class="admonition seealso">
<p class="first admonition-title">See also</p>
<p><a class="reference internal" href="#relationships-many-to-many"><em>Many To Many</em></a> - Reference example of “many to many”.</p>
<p><a class="reference internal" href="tutorial.html#orm-tutorial-many-to-many"><em>Building a Many To Many Relationship</em></a> - ORM tutorial introduction to
many-to-many relationships.</p>
<p><a class="reference internal" href="#self-referential-many-to-many"><em>Self-Referential Many-to-Many Relationship</em></a> - Specifics on using many-to-many
in a self-referential case.</p>
<p><a class="reference internal" href="extensions/declarative.html#declarative-many-to-many"><em>Configuring Many-to-Many Relationships</em></a> - Additional options when using
Declarative.</p>
<p class="last"><a class="reference internal" href="#association-pattern"><em>Association Object</em></a> - an alternative to <a class="reference internal" href="#sqlalchemy.orm.relationship.params.secondary" title="sqlalchemy.orm.relationship"><tt class="xref py py-paramref docutils literal"><span class="pre">secondary</span></tt></a>
when composing association table relationships, allowing additional
attributes to be specified on the association table.</p>
</div>
</li>
<li><span class="target" id="sqlalchemy.orm.relationship.params.active_history"></span><strong>active_history=False</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.orm.relationship.params.active_history">¶</a> – When <tt class="docutils literal"><span class="pre">True</span></tt>, indicates that the “previous” value for a
many-to-one reference should be loaded when replaced, if
not already loaded. Normally, history tracking logic for
simple many-to-ones only needs to be aware of the “new”
value in order to perform a flush. This flag is available
for applications that make use of
<a class="reference internal" href="session.html#sqlalchemy.orm.attributes.get_history" title="sqlalchemy.orm.attributes.get_history"><tt class="xref py py-func docutils literal"><span class="pre">attributes.get_history()</span></tt></a> which also need to know
the “previous” value of the attribute.</li>
<li><span class="target" id="sqlalchemy.orm.relationship.params.backref"></span><strong>backref</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.orm.relationship.params.backref">¶</a> – <p>indicates the string name of a property to be placed on the related
mapper’s class that will handle this relationship in the other
direction. The other property will be created automatically
when the mappers are configured. Can also be passed as a
<a class="reference internal" href="#sqlalchemy.orm.backref" title="sqlalchemy.orm.backref"><tt class="xref py py-func docutils literal"><span class="pre">backref()</span></tt></a> object to control the configuration of the
new relationship.</p>
<div class="admonition seealso">
<p class="first admonition-title">See also</p>
<p><a class="reference internal" href="#relationships-backref"><em>Linking Relationships with Backref</em></a> - Introductory documentation and
examples.</p>
<p><a class="reference internal" href="#sqlalchemy.orm.relationship.params.back_populates" title="sqlalchemy.orm.relationship"><tt class="xref py py-paramref docutils literal"><span class="pre">back_populates</span></tt></a> - alternative form
of backref specification.</p>
<p class="last"><a class="reference internal" href="#sqlalchemy.orm.backref" title="sqlalchemy.orm.backref"><tt class="xref py py-func docutils literal"><span class="pre">backref()</span></tt></a> - allows control over <a class="reference internal" href="#sqlalchemy.orm.relationship" title="sqlalchemy.orm.relationship"><tt class="xref py py-func docutils literal"><span class="pre">relationship()</span></tt></a>
configuration when using <a class="reference internal" href="#sqlalchemy.orm.relationship.params.backref" title="sqlalchemy.orm.relationship"><tt class="xref py py-paramref docutils literal"><span class="pre">backref</span></tt></a>.</p>
</div>
</li>
<li><span class="target" id="sqlalchemy.orm.relationship.params.back_populates"></span><strong>back_populates</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.orm.relationship.params.back_populates">¶</a> – <p>Takes a string name and has the same meaning as <a class="reference internal" href="#sqlalchemy.orm.relationship.params.backref" title="sqlalchemy.orm.relationship"><tt class="xref py py-paramref docutils literal"><span class="pre">backref</span></tt></a>,
except the complementing property is <strong>not</strong> created automatically,
and instead must be configured explicitly on the other mapper. The
complementing property should also indicate <a class="reference internal" href="#sqlalchemy.orm.relationship.params.back_populates" title="sqlalchemy.orm.relationship"><tt class="xref py py-paramref docutils literal"><span class="pre">back_populates</span></tt></a>
to this relationship to ensure proper functioning.</p>
<div class="admonition seealso">
<p class="first admonition-title">See also</p>
<p><a class="reference internal" href="#relationships-backref"><em>Linking Relationships with Backref</em></a> - Introductory documentation and
examples.</p>
<p class="last"><a class="reference internal" href="#sqlalchemy.orm.relationship.params.backref" title="sqlalchemy.orm.relationship"><tt class="xref py py-paramref docutils literal"><span class="pre">backref</span></tt></a> - alternative form
of backref specification.</p>
</div>
</li>
<li><span class="target" id="sqlalchemy.orm.relationship.params.cascade"></span><strong>cascade</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.orm.relationship.params.cascade">¶</a> – <p>a comma-separated list of cascade rules which determines how
Session operations should be “cascaded” from parent to child.
This defaults to <tt class="docutils literal"><span class="pre">False</span></tt>, which means the default cascade
should be used - this default cascade is <tt class="docutils literal"><span class="pre">"save-update,</span> <span class="pre">merge"</span></tt>.</p>
<p>The available cascades are <tt class="docutils literal"><span class="pre">save-update</span></tt>, <tt class="docutils literal"><span class="pre">merge</span></tt>,
<tt class="docutils literal"><span class="pre">expunge</span></tt>, <tt class="docutils literal"><span class="pre">delete</span></tt>, <tt class="docutils literal"><span class="pre">delete-orphan</span></tt>, and <tt class="docutils literal"><span class="pre">refresh-expire</span></tt>.
An additional option, <tt class="docutils literal"><span class="pre">all</span></tt> indicates shorthand for
<tt class="docutils literal"><span class="pre">"save-update,</span> <span class="pre">merge,</span> <span class="pre">refresh-expire,</span>
<span class="pre">expunge,</span> <span class="pre">delete"</span></tt>, and is often used as in <tt class="docutils literal"><span class="pre">"all,</span> <span class="pre">delete-orphan"</span></tt>
to indicate that related objects should follow along with the
parent object in all cases, and be deleted when de-associated.</p>
<div class="admonition seealso">
<p class="first admonition-title">See also</p>
<p><a class="reference internal" href="session.html#unitofwork-cascades"><em>Cascades</em></a> - Full detail on each of the available
cascade options.</p>
<p class="last"><a class="reference internal" href="tutorial.html#tutorial-delete-cascade"><em>Configuring delete/delete-orphan Cascade</em></a> - Tutorial example describing
a delete cascade.</p>
</div>
</li>
<li><span class="target" id="sqlalchemy.orm.relationship.params.cascade_backrefs"></span><strong>cascade_backrefs=True</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.orm.relationship.params.cascade_backrefs">¶</a> – <p>a boolean value indicating if the <tt class="docutils literal"><span class="pre">save-update</span></tt> cascade should
operate along an assignment event intercepted by a backref.
When set to <tt class="docutils literal"><span class="pre">False</span></tt>, the attribute managed by this relationship
will not cascade an incoming transient object into the session of a
persistent parent, if the event is received via backref.</p>
<div class="admonition seealso">
<p class="first admonition-title">See also</p>
<p class="last"><a class="reference internal" href="session.html#backref-cascade"><em>Controlling Cascade on Backrefs</em></a> - Full discussion and examples on how
the <a class="reference internal" href="#sqlalchemy.orm.relationship.params.cascade_backrefs" title="sqlalchemy.orm.relationship"><tt class="xref py py-paramref docutils literal"><span class="pre">cascade_backrefs</span></tt></a> option is used.</p>
</div>
</li>
<li><span class="target" id="sqlalchemy.orm.relationship.params.collection_class"></span><strong>collection_class</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.orm.relationship.params.collection_class">¶</a> – <p>a class or callable that returns a new list-holding object. will
be used in place of a plain list for storing elements.</p>
<div class="admonition seealso">
<p class="first admonition-title">See also</p>
<p class="last"><a class="reference internal" href="collections.html#custom-collections"><em>Customizing Collection Access</em></a> - Introductory documentation and
examples.</p>
</div>
</li>
<li><span class="target" id="sqlalchemy.orm.relationship.params.comparator_factory"></span><strong>comparator_factory</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.orm.relationship.params.comparator_factory">¶</a> – <p>a class which extends <a class="reference internal" href="internals.html#sqlalchemy.orm.properties.RelationshipProperty.Comparator" title="sqlalchemy.orm.properties.RelationshipProperty.Comparator"><tt class="xref py py-class docutils literal"><span class="pre">RelationshipProperty.Comparator</span></tt></a> which
provides custom SQL clause generation for comparison operations.</p>
<div class="admonition seealso">
<p class="first admonition-title">See also</p>
<p><a class="reference internal" href="internals.html#sqlalchemy.orm.interfaces.PropComparator" title="sqlalchemy.orm.interfaces.PropComparator"><tt class="xref py py-class docutils literal"><span class="pre">PropComparator</span></tt></a> - some detail on redefining comparators
at this level.</p>
<p class="last"><a class="reference internal" href="mapper_config.html#custom-comparators"><em>Operator Customization</em></a> - Brief intro to this feature.</p>
</div>
</li>
<li><span class="target" id="sqlalchemy.orm.relationship.params.distinct_target_key"></span><strong>distinct_target_key=None</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.orm.relationship.params.distinct_target_key">¶</a> – <p>Indicate if a “subquery” eager load should apply the DISTINCT
keyword to the innermost SELECT statement. When left as <tt class="docutils literal"><span class="pre">None</span></tt>,
the DISTINCT keyword will be applied in those cases when the target
columns do not comprise the full primary key of the target table.
When set to <tt class="docutils literal"><span class="pre">True</span></tt>, the DISTINCT keyword is applied to the innermost
SELECT unconditionally.</p>
<p>It may be desirable to set this flag to False when the DISTINCT is
reducing performance of the innermost subquery beyond that of what
duplicate innermost rows may be causing.</p>
<div class="versionadded">
<p><span>New in version 0.8.3: </span>- <a class="reference internal" href="#sqlalchemy.orm.relationship.params.distinct_target_key" title="sqlalchemy.orm.relationship"><tt class="xref py py-paramref docutils literal"><span class="pre">distinct_target_key</span></tt></a>
allows the
subquery eager loader to apply a DISTINCT modifier to the
innermost SELECT.</p>
</div>
<div class="admonition seealso">
<p class="first admonition-title">See also</p>
<p class="last"><a class="reference internal" href="loading.html"><em>Relationship Loading Techniques</em></a> - includes an introduction to subquery
eager loading.</p>
</div>
</li>
<li><span class="target" id="sqlalchemy.orm.relationship.params.doc"></span><strong>doc</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.orm.relationship.params.doc">¶</a> – docstring which will be applied to the resulting descriptor.</li>
<li><span class="target" id="sqlalchemy.orm.relationship.params.extension"></span><strong>extension</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.orm.relationship.params.extension">¶</a> – <p>an <a class="reference internal" href="deprecated.html#sqlalchemy.orm.interfaces.AttributeExtension" title="sqlalchemy.orm.interfaces.AttributeExtension"><tt class="xref py py-class docutils literal"><span class="pre">AttributeExtension</span></tt></a> instance, or list of extensions,
which will be prepended to the list of attribute listeners for
the resulting descriptor placed on the class.</p>
<div class="deprecated">
<p><span>Deprecated since version 0.7: </span>Please see <a class="reference internal" href="events.html#sqlalchemy.orm.events.AttributeEvents" title="sqlalchemy.orm.events.AttributeEvents"><tt class="xref py py-class docutils literal"><span class="pre">AttributeEvents</span></tt></a>.</p>
</div>
</li>
<li><span class="target" id="sqlalchemy.orm.relationship.params.foreign_keys"></span><strong>foreign_keys</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.orm.relationship.params.foreign_keys">¶</a> – <p>a list of columns which are to be used as “foreign key”
columns, or columns which refer to the value in a remote
column, within the context of this <a class="reference internal" href="#sqlalchemy.orm.relationship" title="sqlalchemy.orm.relationship"><tt class="xref py py-func docutils literal"><span class="pre">relationship()</span></tt></a>
object’s <a class="reference internal" href="#sqlalchemy.orm.relationship.params.primaryjoin" title="sqlalchemy.orm.relationship"><tt class="xref py py-paramref docutils literal"><span class="pre">primaryjoin</span></tt></a> condition.
That is, if the <a class="reference internal" href="#sqlalchemy.orm.relationship.params.primaryjoin" title="sqlalchemy.orm.relationship"><tt class="xref py py-paramref docutils literal"><span class="pre">primaryjoin</span></tt></a>
condition of this <a class="reference internal" href="#sqlalchemy.orm.relationship" title="sqlalchemy.orm.relationship"><tt class="xref py py-func docutils literal"><span class="pre">relationship()</span></tt></a> is <tt class="docutils literal"><span class="pre">a.id</span> <span class="pre">==</span>
<span class="pre">b.a_id</span></tt>, and the values in <tt class="docutils literal"><span class="pre">b.a_id</span></tt> are required to be
present in <tt class="docutils literal"><span class="pre">a.id</span></tt>, then the “foreign key” column of this
<a class="reference internal" href="#sqlalchemy.orm.relationship" title="sqlalchemy.orm.relationship"><tt class="xref py py-func docutils literal"><span class="pre">relationship()</span></tt></a> is <tt class="docutils literal"><span class="pre">b.a_id</span></tt>.</p>
<p>In normal cases, the <a class="reference internal" href="#sqlalchemy.orm.relationship.params.foreign_keys" title="sqlalchemy.orm.relationship"><tt class="xref py py-paramref docutils literal"><span class="pre">foreign_keys</span></tt></a>
parameter is <strong>not required.</strong> <a class="reference internal" href="#sqlalchemy.orm.relationship" title="sqlalchemy.orm.relationship"><tt class="xref py py-func docutils literal"><span class="pre">relationship()</span></tt></a> will
automatically determine which columns in the
<a class="reference internal" href="#sqlalchemy.orm.relationship.params.primaryjoin" title="sqlalchemy.orm.relationship"><tt class="xref py py-paramref docutils literal"><span class="pre">primaryjoin</span></tt></a> conditition are to be
considered “foreign key” columns based on those
<a class="reference internal" href="../core/metadata.html#sqlalchemy.schema.Column" title="sqlalchemy.schema.Column"><tt class="xref py py-class docutils literal"><span class="pre">Column</span></tt></a> objects that specify <a class="reference internal" href="../core/constraints.html#sqlalchemy.schema.ForeignKey" title="sqlalchemy.schema.ForeignKey"><tt class="xref py py-class docutils literal"><span class="pre">ForeignKey</span></tt></a>,
or are otherwise listed as referencing columns in a
<a class="reference internal" href="../core/constraints.html#sqlalchemy.schema.ForeignKeyConstraint" title="sqlalchemy.schema.ForeignKeyConstraint"><tt class="xref py py-class docutils literal"><span class="pre">ForeignKeyConstraint</span></tt></a> construct.
<a class="reference internal" href="#sqlalchemy.orm.relationship.params.foreign_keys" title="sqlalchemy.orm.relationship"><tt class="xref py py-paramref docutils literal"><span class="pre">foreign_keys</span></tt></a> is only needed when:</p>
<blockquote>
<div><ol class="arabic">
<li>There is more than one way to construct a join from the local
table to the remote table, as there are multiple foreign key
references present. Setting <tt class="docutils literal"><span class="pre">foreign_keys</span></tt> will limit the
<a class="reference internal" href="#sqlalchemy.orm.relationship" title="sqlalchemy.orm.relationship"><tt class="xref py py-func docutils literal"><span class="pre">relationship()</span></tt></a> to consider just those columns specified
here as “foreign”.<div class="versionchanged">
<p><span>Changed in version 0.8: </span>A multiple-foreign key join ambiguity can be resolved by
setting the <a class="reference internal" href="#sqlalchemy.orm.relationship.params.foreign_keys" title="sqlalchemy.orm.relationship"><tt class="xref py py-paramref docutils literal"><span class="pre">foreign_keys</span></tt></a> parameter alone, without the
need to explicitly set <a class="reference internal" href="#sqlalchemy.orm.relationship.params.primaryjoin" title="sqlalchemy.orm.relationship"><tt class="xref py py-paramref docutils literal"><span class="pre">primaryjoin</span></tt></a> as well.</p>
</div>
</li>
<li>The <a class="reference internal" href="../core/metadata.html#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal"><span class="pre">Table</span></tt></a> being mapped does not actually have
<a class="reference internal" href="../core/constraints.html#sqlalchemy.schema.ForeignKey" title="sqlalchemy.schema.ForeignKey"><tt class="xref py py-class docutils literal"><span class="pre">ForeignKey</span></tt></a> or <a class="reference internal" href="../core/constraints.html#sqlalchemy.schema.ForeignKeyConstraint" title="sqlalchemy.schema.ForeignKeyConstraint"><tt class="xref py py-class docutils literal"><span class="pre">ForeignKeyConstraint</span></tt></a>
constructs present, often because the table
was reflected from a database that does not support foreign key
reflection (MySQL MyISAM).</li>
<li>The <a class="reference internal" href="#sqlalchemy.orm.relationship.params.primaryjoin" title="sqlalchemy.orm.relationship"><tt class="xref py py-paramref docutils literal"><span class="pre">primaryjoin</span></tt></a> argument is used to construct a non-standard
join condition, which makes use of columns or expressions that do
not normally refer to their “parent” column, such as a join condition
expressed by a complex comparison using a SQL function.</li>
</ol>
</div></blockquote>
<p>The <a class="reference internal" href="#sqlalchemy.orm.relationship" title="sqlalchemy.orm.relationship"><tt class="xref py py-func docutils literal"><span class="pre">relationship()</span></tt></a> construct will raise informative
error messages that suggest the use of the
<a class="reference internal" href="#sqlalchemy.orm.relationship.params.foreign_keys" title="sqlalchemy.orm.relationship"><tt class="xref py py-paramref docutils literal"><span class="pre">foreign_keys</span></tt></a> parameter when
presented with an ambiguous condition. In typical cases,
if <a class="reference internal" href="#sqlalchemy.orm.relationship" title="sqlalchemy.orm.relationship"><tt class="xref py py-func docutils literal"><span class="pre">relationship()</span></tt></a> doesn’t raise any exceptions, the
<a class="reference internal" href="#sqlalchemy.orm.relationship.params.foreign_keys" title="sqlalchemy.orm.relationship"><tt class="xref py py-paramref docutils literal"><span class="pre">foreign_keys</span></tt></a> parameter is usually
not needed.</p>
<p><a class="reference internal" href="#sqlalchemy.orm.relationship.params.foreign_keys" title="sqlalchemy.orm.relationship"><tt class="xref py py-paramref docutils literal"><span class="pre">foreign_keys</span></tt></a> may also be passed as a callable function
which is evaluated at mapper initialization time, and may be passed as a
Python-evaluable string when using Declarative.</p>
<div class="admonition seealso">
<p class="first admonition-title">See also</p>
<p><a class="reference internal" href="#relationship-foreign-keys"><em>Handling Multiple Join Paths</em></a></p>
<p><a class="reference internal" href="#relationship-custom-foreign"><em>Creating Custom Foreign Conditions</em></a></p>
<p class="last"><a class="reference internal" href="#sqlalchemy.orm.foreign" title="sqlalchemy.orm.foreign"><tt class="xref py py-func docutils literal"><span class="pre">foreign()</span></tt></a> - allows direct annotation of the “foreign” columns
within a <a class="reference internal" href="#sqlalchemy.orm.relationship.params.primaryjoin" title="sqlalchemy.orm.relationship"><tt class="xref py py-paramref docutils literal"><span class="pre">primaryjoin</span></tt></a> condition.</p>
</div>
<div class="versionadded">
<p><span>New in version 0.8: </span>The <a class="reference internal" href="#sqlalchemy.orm.foreign" title="sqlalchemy.orm.foreign"><tt class="xref py py-func docutils literal"><span class="pre">foreign()</span></tt></a> annotation can also be applied
directly to the <a class="reference internal" href="#sqlalchemy.orm.relationship.params.primaryjoin" title="sqlalchemy.orm.relationship"><tt class="xref py py-paramref docutils literal"><span class="pre">primaryjoin</span></tt></a> expression, which is an alternate,
more specific system of describing which columns in a particular
<a class="reference internal" href="#sqlalchemy.orm.relationship.params.primaryjoin" title="sqlalchemy.orm.relationship"><tt class="xref py py-paramref docutils literal"><span class="pre">primaryjoin</span></tt></a> should be considered “foreign”.</p>
</div>
</li>
<li><span class="target" id="sqlalchemy.orm.relationship.params.info"></span><strong>info</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.orm.relationship.params.info">¶</a> – <p>Optional data dictionary which will be populated into the
<a class="reference internal" href="internals.html#sqlalchemy.orm.interfaces.MapperProperty.info" title="sqlalchemy.orm.interfaces.MapperProperty.info"><tt class="xref py py-attr docutils literal"><span class="pre">MapperProperty.info</span></tt></a> attribute of this object.</p>
<div class="versionadded">
<p><span>New in version 0.8.</span></p>
</div>
</li>
<li><span class="target" id="sqlalchemy.orm.relationship.params.innerjoin"></span><strong>innerjoin=False</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.orm.relationship.params.innerjoin">¶</a> – <p>when <tt class="docutils literal"><span class="pre">True</span></tt>, joined eager loads will use an inner join to join
against related tables instead of an outer join. The purpose
of this option is generally one of performance, as inner joins
generally perform better than outer joins.</p>
<p>This flag can be set to <tt class="docutils literal"><span class="pre">True</span></tt> when the relationship references an
object via many-to-one using local foreign keys that are not nullable,
or when the reference is one-to-one or a collection that is guaranteed
to have one or at least one entry.</p>
<div class="admonition seealso">
<p class="first admonition-title">See also</p>
<p class="last"><a class="reference internal" href="loading.html#what-kind-of-loading"><em>What Kind of Loading to Use ?</em></a> - Discussion of some details of
various loader options.</p>
</div>
</li>
<li><span class="target" id="sqlalchemy.orm.relationship.params.join_depth"></span><strong>join_depth</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.orm.relationship.params.join_depth">¶</a> – <p>when non-<tt class="docutils literal"><span class="pre">None</span></tt>, an integer value indicating how many levels
deep “eager” loaders should join on a self-referring or cyclical
relationship. The number counts how many times the same Mapper
shall be present in the loading condition along a particular join
branch. When left at its default of <tt class="docutils literal"><span class="pre">None</span></tt>, eager loaders
will stop chaining when they encounter a the same target mapper
which is already higher up in the chain. This option applies
both to joined- and subquery- eager loaders.</p>
<div class="admonition seealso">
<p class="first admonition-title">See also</p>
<p class="last"><a class="reference internal" href="#self-referential-eager-loading"><em>Configuring Self-Referential Eager Loading</em></a> - Introductory documentation
and examples.</p>
</div>
</li>
<li><span class="target" id="sqlalchemy.orm.relationship.params.lazy"></span><strong>lazy=’select’</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.orm.relationship.params.lazy">¶</a> – <p>specifies
how the related items should be loaded. Default value is
<tt class="docutils literal"><span class="pre">select</span></tt>. Values include:</p>
<ul>
<li><tt class="docutils literal"><span class="pre">select</span></tt> - items should be loaded lazily when the property is first
accessed, using a separate SELECT statement, or identity map
fetch for simple many-to-one references.</li>
<li><tt class="docutils literal"><span class="pre">immediate</span></tt> - items should be loaded as the parents are loaded,
using a separate SELECT statement, or identity map fetch for
simple many-to-one references.</li>
<li><tt class="docutils literal"><span class="pre">joined</span></tt> - items should be loaded “eagerly” in the same query as
that of the parent, using a JOIN or LEFT OUTER JOIN. Whether
the join is “outer” or not is determined by the <tt class="docutils literal"><span class="pre">innerjoin</span></tt>
parameter.</li>
<li><tt class="docutils literal"><span class="pre">subquery</span></tt> - items should be loaded “eagerly” as the parents are
loaded, using one additional SQL statement, which issues a JOIN to a
subquery of the original statement, for each collection requested.</li>
<li><tt class="docutils literal"><span class="pre">noload</span></tt> - no loading should occur at any time. This is to
support “write-only” attributes, or attributes which are
populated in some manner specific to the application.</li>
<li><tt class="docutils literal"><span class="pre">dynamic</span></tt> - the attribute will return a pre-configured
<a class="reference internal" href="query.html#sqlalchemy.orm.query.Query" title="sqlalchemy.orm.query.Query"><tt class="xref py py-class docutils literal"><span class="pre">Query</span></tt></a> object for all read
operations, onto which further filtering operations can be
applied before iterating the results. See
the section <a class="reference internal" href="collections.html#dynamic-relationship"><em>Dynamic Relationship Loaders</em></a> for more details.</li>
<li>True - a synonym for ‘select’</li>
<li>False - a synonym for ‘joined’</li>
<li>None - a synonym for ‘noload’</li>
</ul>
<div class="admonition seealso">
<p class="first admonition-title">See also</p>
<p><a class="reference internal" href="loading.html"><em>Relationship Loading Techniques</em></a> - Full documentation on relationship loader
configuration.</p>
<p class="last"><a class="reference internal" href="collections.html#dynamic-relationship"><em>Dynamic Relationship Loaders</em></a> - detail on the <tt class="docutils literal"><span class="pre">dynamic</span></tt> option.</p>
</div>
</li>
<li><span class="target" id="sqlalchemy.orm.relationship.params.load_on_pending"></span><strong>load_on_pending=False</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.orm.relationship.params.load_on_pending">¶</a> – <p>Indicates loading behavior for transient or pending parent objects.</p>
<p>When set to <tt class="docutils literal"><span class="pre">True</span></tt>, causes the lazy-loader to
issue a query for a parent object that is not persistent, meaning it has
never been flushed. This may take effect for a pending object when
autoflush is disabled, or for a transient object that has been
“attached” to a <a class="reference internal" href="session.html#sqlalchemy.orm.session.Session" title="sqlalchemy.orm.session.Session"><tt class="xref py py-class docutils literal"><span class="pre">Session</span></tt></a> but is not part of its pending
collection.</p>
<p>The <a class="reference internal" href="#sqlalchemy.orm.relationship.params.load_on_pending" title="sqlalchemy.orm.relationship"><tt class="xref py py-paramref docutils literal"><span class="pre">load_on_pending</span></tt></a> flag does not improve behavior
when the ORM is used normally - object references should be constructed
at the object level, not at the foreign key level, so that they
are present in an ordinary way before a flush proceeds. This flag
is not not intended for general use.</p>
<div class="admonition seealso">
<p class="first admonition-title">See also</p>
<p class="last"><a class="reference internal" href="session.html#sqlalchemy.orm.session.Session.enable_relationship_loading" title="sqlalchemy.orm.session.Session.enable_relationship_loading"><tt class="xref py py-meth docutils literal"><span class="pre">Session.enable_relationship_loading()</span></tt></a> - this method establishes
“load on pending” behavior for the whole object, and also allows
loading on objects that remain transient or detached.</p>
</div>
</li>
<li><span class="target" id="sqlalchemy.orm.relationship.params.order_by"></span><strong>order_by</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.orm.relationship.params.order_by">¶</a> – <p>indicates the ordering that should be applied when loading these
items. <a class="reference internal" href="#sqlalchemy.orm.relationship.params.order_by" title="sqlalchemy.orm.relationship"><tt class="xref py py-paramref docutils literal"><span class="pre">order_by</span></tt></a> is expected to refer to one
of the <a class="reference internal" href="../core/metadata.html#sqlalchemy.schema.Column" title="sqlalchemy.schema.Column"><tt class="xref py py-class docutils literal"><span class="pre">Column</span></tt></a>
objects to which the target class is mapped, or
the attribute itself bound to the target class which refers
to the column.</p>
<p><a class="reference internal" href="#sqlalchemy.orm.relationship.params.order_by" title="sqlalchemy.orm.relationship"><tt class="xref py py-paramref docutils literal"><span class="pre">order_by</span></tt></a> may also be passed as a callable function
which is evaluated at mapper initialization time, and may be passed as a
Python-evaluable string when using Declarative.</p>
</li>
<li><span class="target" id="sqlalchemy.orm.relationship.params.passive_deletes"></span><strong>passive_deletes=False</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.orm.relationship.params.passive_deletes">¶</a> – <p>Indicates loading behavior during delete operations.</p>
<p>A value of True indicates that unloaded child items should not
be loaded during a delete operation on the parent. Normally,
when a parent item is deleted, all child items are loaded so
that they can either be marked as deleted, or have their
foreign key to the parent set to NULL. Marking this flag as
True usually implies an ON DELETE <CASCADE|SET NULL> rule is in
place which will handle updating/deleting child rows on the
database side.</p>
<p>Additionally, setting the flag to the string value ‘all’ will
disable the “nulling out” of the child foreign keys, when there
is no delete or delete-orphan cascade enabled. This is
typically used when a triggering or error raise scenario is in
place on the database side. Note that the foreign key
attributes on in-session child objects will not be changed
after a flush occurs so this is a very special use-case
setting.</p>
<div class="admonition seealso">
<p class="first admonition-title">See also</p>
<p class="last"><a class="reference internal" href="collections.html#passive-deletes"><em>Using Passive Deletes</em></a> - Introductory documentation
and examples.</p>
</div>
</li>
<li><span class="target" id="sqlalchemy.orm.relationship.params.passive_updates"></span><strong>passive_updates=True</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.orm.relationship.params.passive_updates">¶</a> – <p>Indicates loading and INSERT/UPDATE/DELETE behavior when the
source of a foreign key value changes (i.e. an “on update”
cascade), which are typically the primary key columns of the
source row.</p>
<p>When True, it is assumed that ON UPDATE CASCADE is configured on
the foreign key in the database, and that the database will
handle propagation of an UPDATE from a source column to
dependent rows. Note that with databases which enforce
referential integrity (i.e. PostgreSQL, MySQL with InnoDB tables),
ON UPDATE CASCADE is required for this operation. The
relationship() will update the value of the attribute on related
items which are locally present in the session during a flush.</p>
<p>When False, it is assumed that the database does not enforce
referential integrity and will not be issuing its own CASCADE
operation for an update. The relationship() will issue the
appropriate UPDATE statements to the database in response to the
change of a referenced key, and items locally present in the
session during a flush will also be refreshed.</p>
<p>This flag should probably be set to False if primary key changes
are expected and the database in use doesn’t support CASCADE
(i.e. SQLite, MySQL MyISAM tables).</p>
<div class="admonition seealso">
<p class="first admonition-title">See also</p>
<p><a class="reference internal" href="#passive-updates"><em>Mutable Primary Keys / Update Cascades</em></a> - Introductory documentation and
examples.</p>
<p class="last"><a class="reference internal" href="mapper_config.html#sqlalchemy.orm.mapper.params.passive_updates" title="sqlalchemy.orm.mapper"><tt class="xref py py-paramref docutils literal"><span class="pre">mapper.passive_updates</span></tt></a> - a similar flag which
takes effect for joined-table inheritance mappings.</p>
</div>
</li>
<li><span class="target" id="sqlalchemy.orm.relationship.params.post_update"></span><strong>post_update</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.orm.relationship.params.post_update">¶</a> – <p>this indicates that the relationship should be handled by a
second UPDATE statement after an INSERT or before a
DELETE. Currently, it also will issue an UPDATE after the
instance was UPDATEd as well, although this technically should
be improved. This flag is used to handle saving bi-directional
dependencies between two individual rows (i.e. each row
references the other), where it would otherwise be impossible to
INSERT or DELETE both rows fully since one row exists before the
other. Use this flag when a particular mapping arrangement will
incur two rows that are dependent on each other, such as a table
that has a one-to-many relationship to a set of child rows, and
also has a column that references a single child row within that
list (i.e. both tables contain a foreign key to each other). If
a flush operation returns an error that a “cyclical
dependency” was detected, this is a cue that you might want to
use <a class="reference internal" href="#sqlalchemy.orm.relationship.params.post_update" title="sqlalchemy.orm.relationship"><tt class="xref py py-paramref docutils literal"><span class="pre">post_update</span></tt></a> to “break” the cycle.</p>
<div class="admonition seealso">
<p class="first admonition-title">See also</p>
<p class="last"><a class="reference internal" href="#post-update"><em>Rows that point to themselves / Mutually Dependent Rows</em></a> - Introductory documentation and examples.</p>
</div>
</li>
<li><span class="target" id="sqlalchemy.orm.relationship.params.primaryjoin"></span><strong>primaryjoin</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.orm.relationship.params.primaryjoin">¶</a> – <p>a SQL expression that will be used as the primary
join of this child object against the parent object, or in a
many-to-many relationship the join of the primary object to the
association table. By default, this value is computed based on the
foreign key relationships of the parent and child tables (or association
table).</p>
<p><a class="reference internal" href="#sqlalchemy.orm.relationship.params.primaryjoin" title="sqlalchemy.orm.relationship"><tt class="xref py py-paramref docutils literal"><span class="pre">primaryjoin</span></tt></a> may also be passed as a callable function
which is evaluated at mapper initialization time, and may be passed as a
Python-evaluable string when using Declarative.</p>
<div class="admonition seealso">
<p class="first admonition-title">See also</p>
<p class="last"><a class="reference internal" href="#relationship-primaryjoin"><em>Specifying Alternate Join Conditions</em></a></p>
</div>
</li>
<li><span class="target" id="sqlalchemy.orm.relationship.params.remote_side"></span><strong>remote_side</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.orm.relationship.params.remote_side">¶</a> – <p>used for self-referential relationships, indicates the column or
list of columns that form the “remote side” of the relationship.</p>
<p><a class="reference internal" href="#sqlalchemy.orm.relationship.params.remote_side" title="sqlalchemy.orm.relationship"><tt class="xref py py-paramref docutils literal"><span class="pre">relationship.remote_side</span></tt></a> may also be passed as a callable function
which is evaluated at mapper initialization time, and may be passed as a
Python-evaluable string when using Declarative.</p>
<div class="versionchanged">
<p><span>Changed in version 0.8: </span>The <a class="reference internal" href="#sqlalchemy.orm.remote" title="sqlalchemy.orm.remote"><tt class="xref py py-func docutils literal"><span class="pre">remote()</span></tt></a> annotation can also be applied
directly to the <tt class="docutils literal"><span class="pre">primaryjoin</span></tt> expression, which is an alternate,
more specific system of describing which columns in a particular
<tt class="docutils literal"><span class="pre">primaryjoin</span></tt> should be considered “remote”.</p>
</div>
<div class="admonition seealso">
<p class="first admonition-title">See also</p>
<p><a class="reference internal" href="#self-referential"><em>Adjacency List Relationships</em></a> - in-depth explaination of how
<a class="reference internal" href="#sqlalchemy.orm.relationship.params.remote_side" title="sqlalchemy.orm.relationship"><tt class="xref py py-paramref docutils literal"><span class="pre">remote_side</span></tt></a>
is used to configure self-referential relationships.</p>
<p class="last"><a class="reference internal" href="#sqlalchemy.orm.remote" title="sqlalchemy.orm.remote"><tt class="xref py py-func docutils literal"><span class="pre">remote()</span></tt></a> - an annotation function that accomplishes the same
purpose as <a class="reference internal" href="#sqlalchemy.orm.relationship.params.remote_side" title="sqlalchemy.orm.relationship"><tt class="xref py py-paramref docutils literal"><span class="pre">remote_side</span></tt></a>, typically
when a custom <a class="reference internal" href="#sqlalchemy.orm.relationship.params.primaryjoin" title="sqlalchemy.orm.relationship"><tt class="xref py py-paramref docutils literal"><span class="pre">primaryjoin</span></tt></a> condition
is used.</p>
</div>
</li>
<li><span class="target" id="sqlalchemy.orm.relationship.params.query_class"></span><strong>query_class</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.orm.relationship.params.query_class">¶</a> – <p>a <a class="reference internal" href="query.html#sqlalchemy.orm.query.Query" title="sqlalchemy.orm.query.Query"><tt class="xref py py-class docutils literal"><span class="pre">Query</span></tt></a> subclass that will be used as the base of the
“appender query” returned by a “dynamic” relationship, that
is, a relationship that specifies <tt class="docutils literal"><span class="pre">lazy="dynamic"</span></tt> or was
otherwise constructed using the <a class="reference internal" href="#sqlalchemy.orm.dynamic_loader" title="sqlalchemy.orm.dynamic_loader"><tt class="xref py py-func docutils literal"><span class="pre">orm.dynamic_loader()</span></tt></a>
function.</p>
<div class="admonition seealso">
<p class="first admonition-title">See also</p>
<p class="last"><a class="reference internal" href="collections.html#dynamic-relationship"><em>Dynamic Relationship Loaders</em></a> - Introduction to “dynamic” relationship
loaders.</p>
</div>
</li>
<li><span class="target" id="sqlalchemy.orm.relationship.params.secondaryjoin"></span><strong>secondaryjoin</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.orm.relationship.params.secondaryjoin">¶</a> – <p>a SQL expression that will be used as the join of
an association table to the child object. By default, this value is
computed based on the foreign key relationships of the association and
child tables.</p>
<p><a class="reference internal" href="#sqlalchemy.orm.relationship.params.secondaryjoin" title="sqlalchemy.orm.relationship"><tt class="xref py py-paramref docutils literal"><span class="pre">secondaryjoin</span></tt></a> may also be passed as a callable function
which is evaluated at mapper initialization time, and may be passed as a
Python-evaluable string when using Declarative.</p>
<div class="admonition seealso">
<p class="first admonition-title">See also</p>
<p class="last"><a class="reference internal" href="#relationship-primaryjoin"><em>Specifying Alternate Join Conditions</em></a></p>
</div>
</li>
<li><span class="target" id="sqlalchemy.orm.relationship.params.single_parent"></span><strong>single_parent</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.orm.relationship.params.single_parent">¶</a> – <p>when True, installs a validator which will prevent objects
from being associated with more than one parent at a time.
This is used for many-to-one or many-to-many relationships that
should be treated either as one-to-one or one-to-many. Its usage
is optional, except for <a class="reference internal" href="#sqlalchemy.orm.relationship" title="sqlalchemy.orm.relationship"><tt class="xref py py-func docutils literal"><span class="pre">relationship()</span></tt></a> constructs which
are many-to-one or many-to-many and also
specify the <tt class="docutils literal"><span class="pre">delete-orphan</span></tt> cascade option. The <a class="reference internal" href="#sqlalchemy.orm.relationship" title="sqlalchemy.orm.relationship"><tt class="xref py py-func docutils literal"><span class="pre">relationship()</span></tt></a>
construct itself will raise an error instructing when this option
is required.</p>
<div class="admonition seealso">
<p class="first admonition-title">See also</p>
<p class="last"><a class="reference internal" href="session.html#unitofwork-cascades"><em>Cascades</em></a> - includes detail on when the
<a class="reference internal" href="#sqlalchemy.orm.relationship.params.single_parent" title="sqlalchemy.orm.relationship"><tt class="xref py py-paramref docutils literal"><span class="pre">single_parent</span></tt></a> flag may be appropriate.</p>
</div>
</li>
<li><span class="target" id="sqlalchemy.orm.relationship.params.uselist"></span><strong>uselist</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.orm.relationship.params.uselist">¶</a> – <p>a boolean that indicates if this property should be loaded as a
list or a scalar. In most cases, this value is determined
automatically by <a class="reference internal" href="#sqlalchemy.orm.relationship" title="sqlalchemy.orm.relationship"><tt class="xref py py-func docutils literal"><span class="pre">relationship()</span></tt></a> at mapper configuration
time, based on the type and direction
of the relationship - one to many forms a list, many to one
forms a scalar, many to many is a list. If a scalar is desired
where normally a list would be present, such as a bi-directional
one-to-one relationship, set <a class="reference internal" href="#sqlalchemy.orm.relationship.params.uselist" title="sqlalchemy.orm.relationship"><tt class="xref py py-paramref docutils literal"><span class="pre">uselist</span></tt></a> to False.</p>
<p>The <a class="reference internal" href="#sqlalchemy.orm.relationship.params.uselist" title="sqlalchemy.orm.relationship"><tt class="xref py py-paramref docutils literal"><span class="pre">uselist</span></tt></a> flag is also available on an
existing <a class="reference internal" href="#sqlalchemy.orm.relationship" title="sqlalchemy.orm.relationship"><tt class="xref py py-func docutils literal"><span class="pre">relationship()</span></tt></a> construct as a read-only attribute, which
can be used to determine if this <a class="reference internal" href="#sqlalchemy.orm.relationship" title="sqlalchemy.orm.relationship"><tt class="xref py py-func docutils literal"><span class="pre">relationship()</span></tt></a> deals with
collections or scalar attributes:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="n">User</span><span class="o">.</span><span class="n">addresses</span><span class="o">.</span><span class="n">property</span><span class="o">.</span><span class="n">uselist</span>
<span class="go">True</span></pre></div>
</div>
<div class="admonition seealso">
<p class="first admonition-title">See also</p>
<p class="last"><a class="reference internal" href="#relationships-one-to-one"><em>One To One</em></a> - Introduction to the “one to one”
relationship pattern, which is typically when the
<a class="reference internal" href="#sqlalchemy.orm.relationship.params.uselist" title="sqlalchemy.orm.relationship"><tt class="xref py py-paramref docutils literal"><span class="pre">uselist</span></tt></a> flag is needed.</p>
</div>
</li>
<li><span class="target" id="sqlalchemy.orm.relationship.params.viewonly"></span><strong>viewonly=False</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.orm.relationship.params.viewonly">¶</a> – <p>when set to True, the relationship is used only for loading objects,
and not for any persistence operation. A <a class="reference internal" href="#sqlalchemy.orm.relationship" title="sqlalchemy.orm.relationship"><tt class="xref py py-func docutils literal"><span class="pre">relationship()</span></tt></a>
which specifies <a class="reference internal" href="#sqlalchemy.orm.relationship.params.viewonly" title="sqlalchemy.orm.relationship"><tt class="xref py py-paramref docutils literal"><span class="pre">viewonly</span></tt></a> can work
with a wider range of SQL operations within the <a class="reference internal" href="#sqlalchemy.orm.relationship.params.primaryjoin" title="sqlalchemy.orm.relationship"><tt class="xref py py-paramref docutils literal"><span class="pre">primaryjoin</span></tt></a>
condition, including operations that feature the use of
a variety of comparison operators as well as SQL functions such
as <a class="reference internal" href="../core/sqlelement.html#sqlalchemy.sql.expression.cast" title="sqlalchemy.sql.expression.cast"><tt class="xref py py-func docutils literal"><span class="pre">cast()</span></tt></a>. The <a class="reference internal" href="#sqlalchemy.orm.relationship.params.viewonly" title="sqlalchemy.orm.relationship"><tt class="xref py py-paramref docutils literal"><span class="pre">viewonly</span></tt></a>
flag is also of general use when defining any kind of <a class="reference internal" href="#sqlalchemy.orm.relationship" title="sqlalchemy.orm.relationship"><tt class="xref py py-func docutils literal"><span class="pre">relationship()</span></tt></a>
that doesn’t represent the full set of related objects, to prevent
modifications of the collection from resulting in persistence operations.</p>
<div class="admonition seealso">
<p class="first admonition-title">See also</p>
<p class="last"><em class="xref std std-ref">relationship_custom_operator</em> - Introduces the most common
use case for <a class="reference internal" href="#sqlalchemy.orm.relationship.params.viewonly" title="sqlalchemy.orm.relationship"><tt class="xref py py-paramref docutils literal"><span class="pre">viewonly</span></tt></a>, that
of a non-equality comparison in a <a class="reference internal" href="#sqlalchemy.orm.relationship.params.primaryjoin" title="sqlalchemy.orm.relationship"><tt class="xref py py-paramref docutils literal"><span class="pre">primaryjoin</span></tt></a>
condition.</p>
</div>
</li>
</ul>
</td>
</tr>
</tbody>
</table>
</dd></dl>
<dl class="function">
<dt id="sqlalchemy.orm.backref">
<tt class="descclassname">sqlalchemy.orm.</tt><tt class="descname">backref</tt><big>(</big><em>name</em>, <em>**kwargs</em><big>)</big><a class="headerlink" href="#sqlalchemy.orm.backref" title="Permalink to this definition">¶</a></dt>
<dd><p>Create a back reference with explicit keyword arguments, which are the
same arguments one can send to <a class="reference internal" href="#sqlalchemy.orm.relationship" title="sqlalchemy.orm.relationship"><tt class="xref py py-func docutils literal"><span class="pre">relationship()</span></tt></a>.</p>
<p>Used with the <tt class="docutils literal"><span class="pre">backref</span></tt> keyword argument to <a class="reference internal" href="#sqlalchemy.orm.relationship" title="sqlalchemy.orm.relationship"><tt class="xref py py-func docutils literal"><span class="pre">relationship()</span></tt></a> in
place of a string argument, e.g.:</p>
<div class="highlight-python"><pre>'items':relationship(SomeItem, backref=backref('parent', lazy='subquery'))</pre>
</div>
</dd></dl>
<dl class="function">
<dt id="sqlalchemy.orm.relation">
<tt class="descclassname">sqlalchemy.orm.</tt><tt class="descname">relation</tt><big>(</big><em>*arg</em>, <em>**kw</em><big>)</big><a class="headerlink" href="#sqlalchemy.orm.relation" title="Permalink to this definition">¶</a></dt>
<dd><p>A synonym for <a class="reference internal" href="#sqlalchemy.orm.relationship" title="sqlalchemy.orm.relationship"><tt class="xref py py-func docutils literal"><span class="pre">relationship()</span></tt></a>.</p>
</dd></dl>
<dl class="function">
<dt id="sqlalchemy.orm.dynamic_loader">
<tt class="descclassname">sqlalchemy.orm.</tt><tt class="descname">dynamic_loader</tt><big>(</big><em>argument</em>, <em>**kw</em><big>)</big><a class="headerlink" href="#sqlalchemy.orm.dynamic_loader" title="Permalink to this definition">¶</a></dt>
<dd><p>Construct a dynamically-loading mapper property.</p>
<p>This is essentially the same as
using the <tt class="docutils literal"><span class="pre">lazy='dynamic'</span></tt> argument with <a class="reference internal" href="#sqlalchemy.orm.relationship" title="sqlalchemy.orm.relationship"><tt class="xref py py-func docutils literal"><span class="pre">relationship()</span></tt></a>:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">dynamic_loader</span><span class="p">(</span><span class="n">SomeClass</span><span class="p">)</span>
<span class="c"># is the same as</span>
<span class="n">relationship</span><span class="p">(</span><span class="n">SomeClass</span><span class="p">,</span> <span class="n">lazy</span><span class="o">=</span><span class="s">"dynamic"</span><span class="p">)</span></pre></div>
</div>
<p>See the section <a class="reference internal" href="collections.html#dynamic-relationship"><em>Dynamic Relationship Loaders</em></a> for more details
on dynamic loading.</p>
</dd></dl>
<dl class="function">
<dt id="sqlalchemy.orm.foreign">
<tt class="descclassname">sqlalchemy.orm.</tt><tt class="descname">foreign</tt><big>(</big><em>expr</em><big>)</big><a class="headerlink" href="#sqlalchemy.orm.foreign" title="Permalink to this definition">¶</a></dt>
<dd><p>Annotate a portion of a primaryjoin expression
with a ‘foreign’ annotation.</p>
<p>See the section <a class="reference internal" href="#relationship-custom-foreign"><em>Creating Custom Foreign Conditions</em></a> for a
description of use.</p>
<div class="versionadded">
<p><span>New in version 0.8.</span></p>
</div>
<div class="admonition seealso">
<p class="first admonition-title">See also</p>
<p><a class="reference internal" href="#relationship-custom-foreign"><em>Creating Custom Foreign Conditions</em></a></p>
<p class="last"><a class="reference internal" href="#sqlalchemy.orm.remote" title="sqlalchemy.orm.remote"><tt class="xref py py-func docutils literal"><span class="pre">remote()</span></tt></a></p>
</div>
</dd></dl>
<dl class="function">
<dt id="sqlalchemy.orm.remote">
<tt class="descclassname">sqlalchemy.orm.</tt><tt class="descname">remote</tt><big>(</big><em>expr</em><big>)</big><a class="headerlink" href="#sqlalchemy.orm.remote" title="Permalink to this definition">¶</a></dt>
<dd><p>Annotate a portion of a primaryjoin expression
with a ‘remote’ annotation.</p>
<p>See the section <a class="reference internal" href="#relationship-custom-foreign"><em>Creating Custom Foreign Conditions</em></a> for a
description of use.</p>
<div class="versionadded">
<p><span>New in version 0.8.</span></p>
</div>
<div class="admonition seealso">
<p class="first admonition-title">See also</p>
<p><a class="reference internal" href="#relationship-custom-foreign"><em>Creating Custom Foreign Conditions</em></a></p>
<p class="last"><a class="reference internal" href="#sqlalchemy.orm.foreign" title="sqlalchemy.orm.foreign"><tt class="xref py py-func docutils literal"><span class="pre">foreign()</span></tt></a></p>
</div>
</dd></dl>
</div>
</div>
</div>
</div>
<div id="docs-bottom-navigation" class="docs-navigation-links">
Previous:
<a href="mapper_config.html" title="previous chapter">Mapper Configuration</a>
Next:
<a href="collections.html" title="next chapter">Collection Configuration and Techniques</a>
<div id="docs-copyright">
© <a href="../copyright.html">Copyright</a> 2007-2014, the SQLAlchemy authors and contributors.
Created using <a href="http://sphinx.pocoo.org/">Sphinx</a> 1.2b1.
</div>
</div>
</div>
</body>
</html>
