/SQLAlchemy-0.7.8/doc/orm/relationships.html
HTML | 1735 lines | 1601 code | 134 blank | 0 comment | 0 complexity | f1e1f20d4eb8b175a9244080d8c9f17c MD5 | raw file
- <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
- "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
- <html>
- <head>
- <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
-
- <title>
-
-
- Relationship Configuration
- —
- SQLAlchemy 0.7 Documentation
- </title>
-
- <link rel="stylesheet" href="../_static/pygments.css" type="text/css" />
- <link rel="stylesheet" href="../_static/docs.css" type="text/css" />
- <script type="text/javascript">
- var DOCUMENTATION_OPTIONS = {
- URL_ROOT: '../',
- VERSION: '0.7.8',
- COLLAPSE_MODINDEX: false,
- FILE_SUFFIX: '.html'
- };
- </script>
- <script type="text/javascript" src="../_static/jquery.js"></script>
- <script type="text/javascript" src="../_static/underscore.js"></script>
- <script type="text/javascript" src="../_static/doctools.js"></script>
- <script type="text/javascript" src="../_static/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.7 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" />
- </head>
- <body>
-
- <div id="docs-container">
- <div id="docs-header">
- <h1>SQLAlchemy 0.7 Documentation</h1>
- <div id="docs-search">
- Search:
- <form class="search" action="../search.html" method="get">
- <input type="text" name="q" size="18" /> <input type="submit" value="Search" />
- <input type="hidden" name="check_keywords" value="yes" />
- <input type="hidden" name="area" value="default" />
- </form>
- </div>
- <div id="docs-version-header">
- Release: <span class="version-num">0.7.8</span> | Release Date: June 16, 2012
- </div>
- </div>
- <div id="docs-top-navigation">
- <div id="docs-top-page-control" class="docs-navigation-links">
- <ul>
- <li>Prev:
- <a href="mapper_config.html" title="previous chapter">Mapper Configuration</a>
- </li>
- <li>Next:
- <a href="collections.html" title="next chapter">Collection Configuration and Techniques</a>
- </li>
- <li>
- <a href="../contents.html">Table of Contents</a> |
- <a href="../genindex.html">Index</a>
- | <a href="../_sources/orm/relationships.txt">view source
- </li>
- </ul>
- </div>
- <div id="docs-navigation-banner">
- <a href="../index.html">SQLAlchemy 0.7 Documentation</a>
- ť <a href="index.html" title="SQLAlchemy ORM">SQLAlchemy ORM</a>
- ť
- Relationship Configuration
-
- <h2>
-
- Relationship Configuration
-
- </h2>
- </div>
- </div>
- <div id="docs-body-container">
- <div id="docs-sidebar">
- <h3><a href="../index.html">Table of Contents</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></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="#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="#setting-the-primaryjoin-and-secondaryjoin">Setting the primaryjoin and secondaryjoin</a><ul>
- <li><a class="reference internal" href="#specifying-alternate-join-conditions">Specifying Alternate Join 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="#specifying-foreign-keys">Specifying Foreign Keys</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>
- <h4>Previous Topic</h4>
- <p>
- <a href="mapper_config.html" title="previous chapter">Mapper Configuration</a>
- </p>
- <h4>Next Topic</h4>
- <p>
- <a href="collections.html" title="next chapter">Collection Configuration and Techniques</a>
- </p>
- <h4>Quick Search</h4>
- <p>
- <form class="search" action="../search.html" method="get">
- <input type="text" name="q" size="18" /> <input type="submit" value="Search" />
- <input type="hidden" name="check_keywords" value="yes" />
- <input type="hidden" name="area" value="default" />
- </form>
- </p>
- </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 <tt class="docutils literal"><span class="pre">backref</span></tt> 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 specifying <tt class="docutils literal"><span class="pre">backref="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">
- <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 <tt class="docutils literal"><span class="pre">uselist=False</span></tt> 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 <tt class="docutils literal"><span class="pre">secondary</span></tt> 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/schema.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/schema.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/schema.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 <tt class="docutils literal"><span class="pre">backref</span></tt> keyword will automatically use
- the same <tt class="docutils literal"><span class="pre">secondary</span></tt> 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 <tt class="docutils literal"><span class="pre">secondary</span></tt> 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>
- <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
- <tt class="docutils literal"><span class="pre">secondary</span></tt> 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 <tt class="docutils literal"><span class="pre">secondary</span></tt> 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 <tt class="docutils literal"><span class="pre">viewonly=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">
- <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/schema.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/schema.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="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-class 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 <tt class="docutils literal"><span class="pre">aliased=True</span></tt> 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 <tt class="docutils literal"><span class="pre">from_joinpoint=True</span></tt>
- 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 <tt class="docutils literal"><span class="pre">aliased=True</span></tt> 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">
- <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 <tt class="docutils literal"><span class="pre">join_depth</span></tt>:</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 <tt class="docutils literal"><span class="pre">backref</span></tt> 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 <tt class="docutils literal"><span class="pre">backref</span></tt> keyword is only a common shortcut for placing a second
- <tt class="docutils literal"><span class="pre">relationship</span></tt> 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 <tt class="docutils literal"><span class="pre">back_populates</span></tt> 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 <tt class="docutils literal"><span class="pre">backref</span></tt>/<tt class="docutils literal"><span class="pre">back_populates</span></tt> behavior has the advantage
- that common bidirectional operations can reflect the correct state without requiring
- a database round trip.</p>
- <p>Remember, when the <tt class="docutils literal"><span class="pre">backref</span></tt> keyword is used on a single relationship, it’s
- exactly the same as if the above two relationships were created individually
- using <tt class="docutils literal"><span class="pre">back_populates</span></tt> 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 <tt class="docutils literal"><span class="pre">backref</span></tt> 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 <tt class="docutils literal"><span class="pre">secondary</span></tt> argument,
- or a one-to-many or many-to-one which has a <tt class="docutils literal"><span class="pre">primaryjoin</span></tt> argument (the
- <tt class="docutils literal"><span class="pre">primaryjoin</span></tt> argument is discussed in <a class="reference internal" href="#relationship-primaryjoin"><em>Setting the primaryjoin and secondaryjoin</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 <tt class="docutils literal"><span class="pre">primaryjoin</span></tt> and <tt class="docutils literal"><span class="pre">secondaryjoin</span></tt>
- 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 <tt class="docutils literal"><span class="pre">lazy</span></tt>, <tt class="docutils literal"><span class="pre">remote_side</span></tt>,
- <tt class="docutils literal"><span class="pre">cascade</span></tt> and <tt class="docutils literal"><span class="pre">cascade_backrefs</span></tt>. 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 <tt class="docutils literal"><span class="pre">primaryjoin</span></tt> 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 <tt class="docutils literal"><span class="pre">primaryjoin</span></tt> 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 <tt class="docutils literal"><span class="pre">back_populates</span></tt> 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 <tt class="docutils literal"><span class="pre">backref</span></tt> 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="setting-the-primaryjoin-and-secondaryjoin">
- <span id="relationship-primaryjoin"></span><h2>Setting the primaryjoin and secondaryjoin<a class="headerlink" href="#setting-the-primaryjoin-and-secondaryjoin" title="Permalink to this headline">ś</a></h2>
- <p>A common scenario arises when we attempt to relate two
- classes together, where there exist multiple ways to join the
- 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.ArgumentError: Could not determine join condition between
- parent/child tables on relationship Customer.billing_address. Specify a
- 'primaryjoin' expression. If 'secondary' is present, 'secondaryjoin' is
- needed as well.</pre>
- </div>
- <p>What this error means is that if you have a <tt class="docutils literal"><span class="pre">Customer</span></tt> object, and wish
- to load in an associated <tt class="docutils literal"><span class="pre">Address</span></tt>, there is the choice of retrieving
- the <tt class="docutils literal"><span class="pre">Address</span></tt> referred to by the <tt class="docutils literal"><span class="pre">billing_address_id</span></tt> column or the one
- referred to by the <tt class="docutils literal"><span class="pre">shipping_address_id</span></tt> column. 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>,
- as it is, cannot determine its full configuration. The examples at
- <a class="reference internal" href="#relationship-patterns"><em>Basic Relational Patterns</em></a> didn’t have this issue, because in each of those examples
- there was only <strong>one</strong> way to refer to the related table.</p>
- <p>To resolve this issue, <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> accepts an argument named
- <tt class="docutils literal"><span class="pre">primaryjoin</span></tt> which accepts a Python-based SQL expression, using the system described
- at <a class="reference internal" href="../core/tutorial.html"><em>SQL Expression Language Tutorial</em></a>, that describes how the two tables should be joined
- together. When using the declarative system, we often will specify this Python
- expression within a string, which is late-evaluated by the mapping configuration
- system so that it has access to the full namespace of available classes:</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">primaryjoin</span><span class="o">=</span><span class="s">"Address.id==Customer.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">primaryjoin</span><span class="o">=</span><span class="s">"Address.id==Customer.shipping_address_id"</span><span class="p">)</span></pre></div>
- </div>
- <p>Above, 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>
- <div class="section" id="specifying-alternate-join-conditions">
- <h3>Specifying Alternate Join Conditions<a class="headerlink" href="#specifying-alternate-join-conditions" title="Permalink to this headline">ś</a></h3>
- <p>The open-ended nature of <tt class="docutils literal"><span class="pre">primaryjoin</span></tt> also allows us to customize how
- related items are loaded. 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">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/expression_api.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/expression_api.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>When using classical mappings, we have the advantage of the <a class="reference internal" href="../core/schema.html#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal"><span class="pre">Table</span></tt></a> objects
- already being present when the mapping is defined, so that the SQL expression
- can be created immediately:</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">relationship</span><span class="p">,</span> <span class="n">mapper</span>
- <span class="k">class</span> <span class="nc">User</span><span class="p">(</span><span class="nb">object</span><span class="p">):</span>
- <span class="k">pass</span>
- <span class="k">class</span> <span class="nc">Address</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">Address</span><span class="p">,</span> <span class="n">addresses_table</span><span class="p">)</span>
- <span class="n">mapper</span><span class="p">(</span><span class="n">User</span><span class="p">,</span> <span class="n">users_table</span><span class="p">,</span> <span class="n">properties</span><span class="o">=</span><span class="p">{</span>
- <span class="s">'boston_addresses'</span><span class="p">:</span> <span class="n">relationship</span><span class="p">(</span><span class="n">Address</span><span class="p">,</span> <span class="n">primaryjoin</span><span class="o">=</span>
- <span class="n">and_</span><span class="p">(</span><span class="n">users_table</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">id</span><span class="o">==</span><span class="n">addresses_table</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">user_id</span><span class="p">,</span>
- <span class="n">addresses_table</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">city</span><span class="o">==</span><span class="s">'Boston'</span><span class="p">))</span>
- <span class="p">})</span></pre></div>
- </div>
- <p>Note that the custom criteria we use in a <tt class="docutils literal"><span class="pre">primaryjoin</span></tt> 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="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 <tt class="docutils literal"><span class="pre">primaryjoin</span></tt>
- and <tt class="docutils literal"><span class="pre">secondaryjoin</span></tt> - the latter is significant for a relationship that
- specifies a many-to-many reference using the <tt class="docutils literal"><span class="pre">secondary</span></tt> argument.
- A common situation which involves the usage of <tt class="docutils literal"><span class="pre">primaryjoin</span></tt> and <tt class="docutils literal"><span class="pre">secondaryjoin</span></tt>
- 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 <tt class="docutils literal"><span class="pre">primaryjoin</span></tt>
- and <tt class="docutils literal"><span class="pre">secondaryjoin</span></tt> 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 <tt class="docutils literal"><span class="pre">Column</span></tt> 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 <tt class="docutils literal"><span class="pre">backref</span></tt> 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 <tt class="docutils literal"><span class="pre">primaryjoin</span></tt> and <tt class="docutils literal"><span class="pre">secondaryjoin</span></tt> arguments.</p>
- </div>
- <div class="section" id="specifying-foreign-keys">
- <h3>Specifying Foreign Keys<a class="headerlink" href="#specifying-foreign-keys" title="Permalink to this headline">ś</a></h3>
- <p>When using <tt class="docutils literal"><span class="pre">primaryjoin</span></tt> and <tt class="docutils literal"><span class="pre">secondaryjoin</span></tt>, SQLAlchemy also needs to be
- aware of which columns in the relationship reference the other. In most cases,
- a <a class="reference internal" href="../core/schema.html#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal"><span class="pre">Table</span></tt></a> construct will have
- <a class="reference internal" href="../core/schema.html#sqlalchemy.schema.ForeignKey" title="sqlalchemy.schema.ForeignKey"><tt class="xref py py-class docutils literal"><span class="pre">ForeignKey</span></tt></a> constructs which take care of this;
- however, in the case of reflected tables on a database that does not report
- FKs (like MySQL ISAM) or when using join conditions on columns that don’t have
- foreign keys, 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> needs to be told
- specifically which columns are “foreign” using the <tt class="docutils literal"><span class="pre">foreign_keys</span></tt>
- collection:</p>
- <div class="highlight-python+sql"><div class="highlight"><pre><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">__table__</span> <span class="o">=</span> <span class="n">addresses_table</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">__table__</span> <span class="o">=</span> <span class="n">users_table</span>
- <span class="n">addresses</span> <span class="o">=</span> <span class="n">relationship</span><span class="p">(</span><span class="n">Address</span><span class="p">,</span>
- <span class="n">primaryjoin</span><span class="o">=</span>
- <span class="n">users_table</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">user_id</span><span class="o">==</span><span class="n">addresses_table</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">user_id</span><span class="p">,</span>
- <span class="n">foreign_keys</span><span class="o">=</span><span class="p">[</span><span class="n">addresses_table</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">user_id</span><span class="p">])</span></pre></div>
- </div>
- </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 <tt class="docutils literal"><span class="pre">viewonly=True</span></tt> 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 <tt class="docutils literal"><span class="pre">post_update</span></tt> 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/schema.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 <tt class="docutils literal"><span class="pre">use_alter=True</span></tt> 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/schema.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
- <tt class="docutils literal"><span class="pre">autoincrement='ignore_fk'</span></tt> on <a class="reference internal" href="../core/schema.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>
- <p class="versionadded">
- <span class="versionmodified">New in version 0.7.4: </span><tt class="docutils literal"><span class="pre">autoincrement='ignore_fk'</span></tt> on <a class="reference internal" href="../core/schema.html#sqlalchemy.schema.Column" title="sqlalchemy.schema.Column"><tt class="xref py py-class docutils literal"><span class="pre">Column</span></tt></a>.</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 <tt class="docutils literal"><span class="pre">passive_updates</span></tt> 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
- <tt class="docutils literal"><span class="pre">passive_updates</span></tt> 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><tt class="docutils literal"><span class="pre">passive_updates</span></tt> 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><tt class="docutils literal"><span class="pre">passive_updates=False</span></tt> 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 <tt class="docutils literal"><span class="pre">passive_updates=False</span></tt> 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 of a primary Mapper to a secondary Mapper.</p>
- <p class="versionchanged">
- <span class="versionmodified">Changed in version 0.6: </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> is historically known as <a class="reference internal" href="#sqlalchemy.orm.relation" title="sqlalchemy.orm.relation"><tt class="xref py py-func docutils literal"><span class="pre">relation()</span></tt></a>.</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>
- <p>A full array of examples and reference documentation regarding
- <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 at <a class="reference internal" href="#"><em>Relationship Configuration</em></a>.</p>
- <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><strong>argument</strong> – <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><tt class="docutils literal"><span class="pre">argument</span></tt> 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><strong>secondary</strong> – <p>for a many-to-many relationship, specifies the intermediary
- table, and is an instance of <a class="reference internal" href="../core/schema.html#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal"><span class="pre">Table</span></tt></a>. The <tt class="docutils literal"><span class="pre">secondary</span></tt> keyword
- argument should generally only
- be used for a table that is not otherwise expressed in any class
- mapping, unless this relationship is declared as view only, otherwise
- conflicting persistence operations can occur.</p>
- <p><tt class="docutils literal"><span class="pre">secondary</span></tt> may
- also be passed as a callable function which is evaluated at
- mapper initialization time.</p>
- </li>
- <li><strong>active_history=False</strong> – 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><strong>backref</strong> – 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.</li>
- <li><strong>back_populates</strong> – Takes a string name and has the same meaning as <tt class="docutils literal"><span class="pre">backref</span></tt>,
- 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 <tt class="docutils literal"><span class="pre">back_populates</span></tt>
- to this relationship to ensure proper functioning.</li>
- <li><strong>cascade</strong> – <blockquote>
- <div>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. The default value is <tt class="docutils literal"><span class="pre">"save-update,</span> <span class="pre">merge"</span></tt>.<p>Available cascades are:</p>
- <ul>
- <li><tt class="docutils literal"><span class="pre">save-update</span></tt> - cascade the <a class="reference internal" href="session.html#sqlalchemy.orm.session.Session.add" title="sqlalchemy.orm.session.Session.add"><tt class="xref py py-meth docutils literal"><span class="pre">Session.add()</span></tt></a>
- operation. This cascade applies both to future and
- past calls to <a class="reference internal" href="session.html#sqlalchemy.orm.session.Session.add" title="sqlalchemy.orm.session.Session.add"><tt class="xref py py-meth docutils literal"><span class="pre">add()</span></tt></a>,
- meaning new items added to a collection or scalar relationship
- get placed into the same session as that of the parent, and
- also applies to items which have been removed from this
- relationship but are still part of unflushed history.</li>
- <li><tt class="docutils literal"><span class="pre">merge</span></tt> - cascade the <a class="reference internal" href="session.html#sqlalchemy.orm.session.Session.merge" title="sqlalchemy.orm.session.Session.merge"><tt class="xref py py-meth docutils literal"><span class="pre">merge()</span></tt></a>
- operation</li>
- <li><tt class="docutils literal"><span class="pre">expunge</span></tt> - cascade the <a class="reference internal" href="session.html#sqlalchemy.orm.session.Session.expunge" title="sqlalchemy.orm.session.Session.expunge"><tt class="xref py py-meth docutils literal"><span class="pre">Session.expunge()</span></tt></a>
- operation</li>
- <li><tt class="docutils literal"><span class="pre">delete</span></tt> - cascade the <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>
- operation</li>
- <li><tt class="docutils literal"><span class="pre">delete-orphan</span></tt> - if an item of the child’s type is
- detached from its parent, mark it for deletion.<p class="versionchanged">
- <span class="versionmodified">Changed in version 0.7: </span>This option does not prevent
- a new instance of the child object from being persisted
- without a parent to start with; to constrain against
- that case, ensure the child’s foreign key column(s)
- is configured as NOT NULL</p>
- </li>
- <li><tt class="docutils literal"><span class="pre">refresh-expire</span></tt> - cascade the <a class="reference internal" href="session.html#sqlalchemy.orm.session.Session.expire" title="sqlalchemy.orm.session.Session.expire"><tt class="xref py py-meth docutils literal"><span class="pre">Session.expire()</span></tt></a>
- and <a class="reference internal" href="session.html#sqlalchemy.orm.session.Session.refresh" title="sqlalchemy.orm.session.Session.refresh"><tt class="xref py py-meth docutils literal"><span class="pre">refresh()</span></tt></a> operations</li>
- <li><tt class="docutils literal"><span class="pre">all</span></tt> - shorthand for “save-update,merge, refresh-expire,
- expunge, delete”</li>
- </ul>
- </div></blockquote>
- <p>See the section <a class="reference internal" href="session.html#unitofwork-cascades"><em>Cascades</em></a> for more background
- on configuring cascades.</p>
- </li>
- <li><strong>cascade_backrefs=True</strong> – <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>
- <p>That is:</p>
- <div class="highlight-python"><div class="highlight"><pre><span class="n">mapper</span><span class="p">(</span><span class="n">A</span><span class="p">,</span> <span class="n">a_table</span><span class="p">,</span> <span class="n">properties</span><span class="o">=</span><span class="p">{</span>
- <span class="s">'bs'</span><span class="p">:</span><span class="n">relationship</span><span class="p">(</span><span class="n">B</span><span class="p">,</span> <span class="n">backref</span><span class="o">=</span><span class="s">"a"</span><span class="p">,</span> <span class="n">cascade_backrefs</span><span class="o">=</span><span class="bp">False</span><span class="p">)</span>
- <span class="p">})</span></pre></div>
- </div>
- <p>If an <tt class="docutils literal"><span class="pre">A()</span></tt> is present in the session, assigning it to
- the “a” attribute on a transient <tt class="docutils literal"><span class="pre">B()</span></tt> will not place
- the <tt class="docutils literal"><span class="pre">B()</span></tt> into the session. To set the flag in the other
- direction, i.e. so that <tt class="docutils literal"><span class="pre">A().bs.append(B())</span></tt> won’t add
- a transient <tt class="docutils literal"><span class="pre">A()</span></tt> into the session for a persistent <tt class="docutils literal"><span class="pre">B()</span></tt>:</p>
- <div class="highlight-python"><div class="highlight"><pre><span class="n">mapper</span><span class="p">(</span><span class="n">A</span><span class="p">,</span> <span class="n">a_table</span><span class="p">,</span> <span class="n">properties</span><span class="o">=</span><span class="p">{</span>
- <span class="s">'bs'</span><span class="p">:</span><span class="n">relationship</span><span class="p">(</span><span class="n">B</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">"a"</span><span class="p">,</span> <span class="n">cascade_backrefs</span><span class="o">=</span><span class="bp">False</span><span class="p">)</span>
- <span class="p">)</span>
- <span class="p">})</span></pre></div>
- </div>
- <p>See the section <a class="reference internal" href="session.html#unitofwork-cascades"><em>Cascades</em></a> for more background
- on configuring cascades.</p>
- </li>
- <li><strong>collection_class</strong> – a class or callable that returns a new list-holding object. will
- be used in place of a plain list for storing elements.
- Behavior of this attribute is described in detail at
- <a class="reference internal" href="collections.html#custom-collections"><em>Customizing Collection Access</em></a>.</li>
- <li><strong>comparator_factory</strong> – 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.</li>
- <li><strong>doc</strong> – docstring which will be applied to the resulting descriptor.</li>
- <li><strong>extension</strong> – 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.
- <strong>Deprecated.</strong> 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>.</li>
- <li><strong>foreign_keys</strong> – <p>a list of columns which are to be used as “foreign key” columns.
- Normally, <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> uses the <a class="reference internal" href="../core/schema.html#sqlalchemy.schema.ForeignKey" title="sqlalchemy.schema.ForeignKey"><tt class="xref py py-class docutils literal"><span class="pre">ForeignKey</span></tt></a>
- and <a class="reference internal" href="../core/schema.html#sqlalchemy.schema.ForeignKeyConstraint" title="sqlalchemy.schema.ForeignKeyConstraint"><tt class="xref py py-class docutils literal"><span class="pre">ForeignKeyConstraint</span></tt></a> objects present within the
- mapped or secondary <a class="reference internal" href="../core/schema.html#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal"><span class="pre">Table</span></tt></a> to determine the “foreign” side of
- the join condition. This is used to construct SQL clauses in order
- to load objects, as well as to “synchronize” values from
- primary key columns to referencing foreign key columns.
- The <tt class="docutils literal"><span class="pre">foreign_keys</span></tt> parameter overrides the notion of what’s
- “foreign” in the table metadata, allowing the specification
- of a list of <a class="reference internal" href="../core/schema.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 should be considered
- part of the foreign key.</p>
- <p>There are only two use cases for <tt class="docutils literal"><span class="pre">foreign_keys</span></tt> - one, when it is not
- convenient for <a class="reference internal" href="../core/schema.html#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal"><span class="pre">Table</span></tt></a> metadata to contain its own foreign key
- metadata (which should be almost never, unless reflecting a large amount of
- tables from a MySQL MyISAM schema, or a schema that doesn’t actually
- have foreign keys on it). The other is for extremely
- rare and exotic composite foreign key setups where some columns
- should artificially not be considered as foreign.</p>
- <p><tt class="docutils literal"><span class="pre">foreign_keys</span></tt> 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><strong>innerjoin=False</strong> – <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. Another reason can be
- the use of <tt class="docutils literal"><span class="pre">with_lockmode</span></tt>, which does not support 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>
- </li>
- <li><strong>join_depth</strong> – 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.</li>
- <li><strong>lazy=’select’</strong> – <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.<p class="versionadded">
- <span class="versionmodified">New in version 0.6.5.</span></p>
- </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” within the same
- query as that of the parent, using a second SQL statement
- which issues a JOIN to a subquery of the original
- statement.</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>
- <p>Detailed discussion of loader strategies is at <a class="reference internal" href="loading.html"><em>Relationship Loading Techniques</em></a>.</p>
- </li>
- <li><strong>load_on_pending=False</strong> – <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. Attachment of transient objects to the session without
- moving to the “pending” state is not a supported behavior at this time.</p>
- <p>Note that the load of related objects on a pending or transient object
- also does not trigger any attribute change events - no user-defined
- events will be emitted for these attributes, and if and when the
- object is ultimately flushed, only the user-specific foreign key
- attributes will be part of the modified state.</p>
- <p>The load_on_pending 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 flush() proceeds. This flag
- is not not intended for general use.</p>
- <p>New in 0.6.5.</p>
- </li>
- <li><strong>order_by</strong> – <p>indicates the ordering that should be applied when loading these
- items. <tt class="docutils literal"><span class="pre">order_by</span></tt> is expected to refer to one of the <a class="reference internal" href="../core/schema.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><tt class="docutils literal"><span class="pre">order_by</span></tt> 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><strong>passive_deletes=False</strong> – <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>
- </li>
- <li><strong>passive_updates=True</strong> – <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>
- <p>Also see the passive_updates flag on <tt class="docutils literal"><span class="pre">mapper()</span></tt>.</p>
- <p>A future SQLAlchemy release will provide a “detect” feature for
- this flag.</p>
- </li>
- <li><strong>post_update</strong> – 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 <tt class="docutils literal"><span class="pre">flush()</span></tt> operation returns an error that a “cyclical
- dependency” was detected, this is a cue that you might want to
- use <tt class="docutils literal"><span class="pre">post_update</span></tt> to “break” the cycle.</li>
- <li><strong>primaryjoin</strong> – <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><tt class="docutils literal"><span class="pre">primaryjoin</span></tt> 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><strong>remote_side</strong> – <p>used for self-referential relationships, indicates the column or
- list of columns that form the “remote side” of the relationship.</p>
- <p><tt class="docutils literal"><span class="pre">remote_side</span></tt> 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><strong>query_class</strong> – 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.</li>
- <li><strong>secondaryjoin</strong> – <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><tt class="docutils literal"><span class="pre">secondaryjoin</span></tt> 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><strong>single_parent=(True|False)</strong> – 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 unless delete-orphan cascade is also
- set on this relationship(), in which case its required.</li>
- <li><strong>uselist=(True|False)</strong> – 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 <tt class="docutils literal"><span class="pre">relationship()</span></tt>, 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 uselist to False.</li>
- <li><strong>viewonly=False</strong> – when set to True, the relationship is used only for loading objects
- within the relationship, and has no effect on the unit-of-work
- flush process. Relationships with viewonly can specify any kind of
- join conditions to provide additional views of related objects
- onto a parent object. Note that the functionality of a viewonly
- relationship has its limits - complicated join conditions may
- not compile into eager or lazy loaders properly. If this is the
- case, use an alternative method.</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>
- </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-2012, the SQLAlchemy authors and contributors.
- Created using <a href="http://sphinx.pocoo.org/">Sphinx</a> 1.1.3.
- </div>
- </div>
- </div>
-
- </body>
- </html>