PageRenderTime 68ms CodeModel.GetById 21ms RepoModel.GetById 0ms app.codeStats 1ms

/SQLAlchemy-0.7.8/doc/orm/relationships.html

#
HTML | 1735 lines | 1601 code | 134 blank | 0 comment | 0 complexity | f1e1f20d4eb8b175a9244080d8c9f17c MD5 | raw file

Large files files are truncated, but you can click here to view the full file

  1. <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
  2. "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
  3. <html>
  4. <head>
  5. <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
  6. <title>
  7. Relationship Configuration
  8. &mdash;
  9. SQLAlchemy 0.7 Documentation
  10. </title>
  11. <link rel="stylesheet" href="../_static/pygments.css" type="text/css" />
  12. <link rel="stylesheet" href="../_static/docs.css" type="text/css" />
  13. <script type="text/javascript">
  14. var DOCUMENTATION_OPTIONS = {
  15. URL_ROOT: '../',
  16. VERSION: '0.7.8',
  17. COLLAPSE_MODINDEX: false,
  18. FILE_SUFFIX: '.html'
  19. };
  20. </script>
  21. <script type="text/javascript" src="../_static/jquery.js"></script>
  22. <script type="text/javascript" src="../_static/underscore.js"></script>
  23. <script type="text/javascript" src="../_static/doctools.js"></script>
  24. <script type="text/javascript" src="../_static/init.js"></script>
  25. <link rel="index" title="Index" href="../genindex.html" />
  26. <link rel="search" title="Search" href="../search.html" />
  27. <link rel="copyright" title="Copyright" href="../copyright.html" />
  28. <link rel="top" title="SQLAlchemy 0.7 Documentation" href="../index.html" />
  29. <link rel="up" title="SQLAlchemy ORM" href="index.html" />
  30. <link rel="next" title="Collection Configuration and Techniques" href="collections.html" />
  31. <link rel="prev" title="Mapper Configuration" href="mapper_config.html" />
  32. </head>
  33. <body>
  34. <div id="docs-container">
  35. <div id="docs-header">
  36. <h1>SQLAlchemy 0.7 Documentation</h1>
  37. <div id="docs-search">
  38. Search:
  39. <form class="search" action="../search.html" method="get">
  40. <input type="text" name="q" size="18" /> <input type="submit" value="Search" />
  41. <input type="hidden" name="check_keywords" value="yes" />
  42. <input type="hidden" name="area" value="default" />
  43. </form>
  44. </div>
  45. <div id="docs-version-header">
  46. Release: <span class="version-num">0.7.8</span> | Release Date: June 16, 2012
  47. </div>
  48. </div>
  49. <div id="docs-top-navigation">
  50. <div id="docs-top-page-control" class="docs-navigation-links">
  51. <ul>
  52. <li>Prev:
  53. <a href="mapper_config.html" title="previous chapter">Mapper Configuration</a>
  54. </li>
  55. <li>Next:
  56. <a href="collections.html" title="next chapter">Collection Configuration and Techniques</a>
  57. </li>
  58. <li>
  59. <a href="../contents.html">Table of Contents</a> |
  60. <a href="../genindex.html">Index</a>
  61. | <a href="../_sources/orm/relationships.txt">view source
  62. </li>
  63. </ul>
  64. </div>
  65. <div id="docs-navigation-banner">
  66. <a href="../index.html">SQLAlchemy 0.7 Documentation</a>
  67. ť <a href="index.html" title="SQLAlchemy ORM">SQLAlchemy ORM</a>
  68. ť
  69. Relationship Configuration
  70. <h2>
  71. Relationship Configuration
  72. </h2>
  73. </div>
  74. </div>
  75. <div id="docs-body-container">
  76. <div id="docs-sidebar">
  77. <h3><a href="../index.html">Table of Contents</a></h3>
  78. <ul>
  79. <li><a class="reference internal" href="#">Relationship Configuration</a><ul>
  80. <li><a class="reference internal" href="#basic-relational-patterns">Basic Relational Patterns</a><ul>
  81. <li><a class="reference internal" href="#one-to-many">One To Many</a></li>
  82. <li><a class="reference internal" href="#many-to-one">Many To One</a></li>
  83. <li><a class="reference internal" href="#one-to-one">One To One</a></li>
  84. <li><a class="reference internal" href="#many-to-many">Many To Many</a></li>
  85. <li><a class="reference internal" href="#association-object">Association Object</a></li>
  86. </ul>
  87. </li>
  88. <li><a class="reference internal" href="#adjacency-list-relationships">Adjacency List Relationships</a><ul>
  89. <li><a class="reference internal" href="#self-referential-query-strategies">Self-Referential Query Strategies</a></li>
  90. <li><a class="reference internal" href="#configuring-self-referential-eager-loading">Configuring Self-Referential Eager Loading</a></li>
  91. </ul>
  92. </li>
  93. <li><a class="reference internal" href="#linking-relationships-with-backref">Linking Relationships with Backref</a><ul>
  94. <li><a class="reference internal" href="#backref-arguments">Backref Arguments</a></li>
  95. <li><a class="reference internal" href="#one-way-backrefs">One Way Backrefs</a></li>
  96. </ul>
  97. </li>
  98. <li><a class="reference internal" href="#setting-the-primaryjoin-and-secondaryjoin">Setting the primaryjoin and secondaryjoin</a><ul>
  99. <li><a class="reference internal" href="#specifying-alternate-join-conditions">Specifying Alternate Join Conditions</a></li>
  100. <li><a class="reference internal" href="#self-referential-many-to-many-relationship">Self-Referential Many-to-Many Relationship</a></li>
  101. <li><a class="reference internal" href="#specifying-foreign-keys">Specifying Foreign Keys</a></li>
  102. <li><a class="reference internal" href="#building-query-enabled-properties">Building Query-Enabled Properties</a></li>
  103. </ul>
  104. </li>
  105. <li><a class="reference internal" href="#rows-that-point-to-themselves-mutually-dependent-rows">Rows that point to themselves / Mutually Dependent Rows</a></li>
  106. <li><a class="reference internal" href="#mutable-primary-keys-update-cascades">Mutable Primary Keys / Update Cascades</a></li>
  107. <li><a class="reference internal" href="#relationships-api">Relationships API</a></li>
  108. </ul>
  109. </li>
  110. </ul>
  111. <h4>Previous Topic</h4>
  112. <p>
  113. <a href="mapper_config.html" title="previous chapter">Mapper Configuration</a>
  114. </p>
  115. <h4>Next Topic</h4>
  116. <p>
  117. <a href="collections.html" title="next chapter">Collection Configuration and Techniques</a>
  118. </p>
  119. <h4>Quick Search</h4>
  120. <p>
  121. <form class="search" action="../search.html" method="get">
  122. <input type="text" name="q" size="18" /> <input type="submit" value="Search" />
  123. <input type="hidden" name="check_keywords" value="yes" />
  124. <input type="hidden" name="area" value="default" />
  125. </form>
  126. </p>
  127. </div>
  128. <div id="docs-body" class="withsidebar" >
  129. <span class="target" id="module-sqlalchemy.orm"></span><div class="section" id="relationship-configuration">
  130. <span id="relationship-config-toplevel"></span><h1>Relationship Configuration<a class="headerlink" href="#relationship-configuration" title="Permalink to this headline">ś</a></h1>
  131. <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
  132. of its usage. The reference material here continues into the next section,
  133. <a class="reference internal" href="collections.html"><em>Collection Configuration and Techniques</em></a>, which has additional detail on configuration
  134. 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>
  135. <div class="section" id="basic-relational-patterns">
  136. <span id="relationship-patterns"></span><h2>Basic Relational Patterns<a class="headerlink" href="#basic-relational-patterns" title="Permalink to this headline">ś</a></h2>
  137. <p>A quick walkthrough of the basic relational patterns.</p>
  138. <p>The imports used for each of the following sections is as follows:</p>
  139. <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>
  140. <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>
  141. <span class="kn">from</span> <span class="nn">sqlalchemy.ext.declarative</span> <span class="kn">import</span> <span class="n">declarative_base</span>
  142. <span class="n">Base</span> <span class="o">=</span> <span class="n">declarative_base</span><span class="p">()</span></pre></div>
  143. </div>
  144. <div class="section" id="one-to-many">
  145. <h3>One To Many<a class="headerlink" href="#one-to-many" title="Permalink to this headline">ś</a></h3>
  146. <p>A one to many relationship places a foreign key on the child table referencing
  147. 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
  148. a collection of items represented by the child:</p>
  149. <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>
  150. <span class="n">__tablename__</span> <span class="o">=</span> <span class="s">&#39;parent&#39;</span>
  151. <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>
  152. <span class="n">children</span> <span class="o">=</span> <span class="n">relationship</span><span class="p">(</span><span class="s">&quot;Child&quot;</span><span class="p">)</span>
  153. <span class="k">class</span> <span class="nc">Child</span><span class="p">(</span><span class="n">Base</span><span class="p">):</span>
  154. <span class="n">__tablename__</span> <span class="o">=</span> <span class="s">&#39;child&#39;</span>
  155. <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>
  156. <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">&#39;parent.id&#39;</span><span class="p">))</span></pre></div>
  157. </div>
  158. <p>To establish a bidirectional relationship in one-to-many, where the &#8220;reverse&#8221;
  159. side is a many to one, specify the <tt class="docutils literal"><span class="pre">backref</span></tt> option:</p>
  160. <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>
  161. <span class="n">__tablename__</span> <span class="o">=</span> <span class="s">&#39;parent&#39;</span>
  162. <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>
  163. <span class="n">children</span> <span class="o">=</span> <span class="n">relationship</span><span class="p">(</span><span class="s">&quot;Child&quot;</span><span class="p">,</span> <span class="n">backref</span><span class="o">=</span><span class="s">&quot;parent&quot;</span><span class="p">)</span>
  164. <span class="k">class</span> <span class="nc">Child</span><span class="p">(</span><span class="n">Base</span><span class="p">):</span>
  165. <span class="n">__tablename__</span> <span class="o">=</span> <span class="s">&#39;child&#39;</span>
  166. <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>
  167. <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">&#39;parent.id&#39;</span><span class="p">))</span></pre></div>
  168. </div>
  169. <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>
  170. </div>
  171. <div class="section" id="many-to-one">
  172. <h3>Many To One<a class="headerlink" href="#many-to-one" title="Permalink to this headline">ś</a></h3>
  173. <p>Many to one places a foreign key in the parent table referencing the child.
  174. <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
  175. attribute will be created:</p>
  176. <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>
  177. <span class="n">__tablename__</span> <span class="o">=</span> <span class="s">&#39;parent&#39;</span>
  178. <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>
  179. <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">&#39;child.id&#39;</span><span class="p">))</span>
  180. <span class="n">child</span> <span class="o">=</span> <span class="n">relationship</span><span class="p">(</span><span class="s">&quot;Child&quot;</span><span class="p">)</span>
  181. <span class="k">class</span> <span class="nc">Child</span><span class="p">(</span><span class="n">Base</span><span class="p">):</span>
  182. <span class="n">__tablename__</span> <span class="o">=</span> <span class="s">&#39;child&#39;</span>
  183. <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>
  184. </div>
  185. <p>Bidirectional behavior is achieved by specifying <tt class="docutils literal"><span class="pre">backref=&quot;parents&quot;</span></tt>,
  186. which will place a one-to-many collection on the <tt class="docutils literal"><span class="pre">Child</span></tt> class:</p>
  187. <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>
  188. <span class="n">__tablename__</span> <span class="o">=</span> <span class="s">&#39;parent&#39;</span>
  189. <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>
  190. <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">&#39;child.id&#39;</span><span class="p">))</span>
  191. <span class="n">child</span> <span class="o">=</span> <span class="n">relationship</span><span class="p">(</span><span class="s">&quot;Child&quot;</span><span class="p">,</span> <span class="n">backref</span><span class="o">=</span><span class="s">&quot;parents&quot;</span><span class="p">)</span></pre></div>
  192. </div>
  193. </div>
  194. <div class="section" id="one-to-one">
  195. <h3>One To One<a class="headerlink" href="#one-to-one" title="Permalink to this headline">ś</a></h3>
  196. <p>One To One is essentially a bidirectional relationship with a scalar
  197. attribute on both sides. To achieve this, the <tt class="docutils literal"><span class="pre">uselist=False</span></tt> flag indicates
  198. the placement of a scalar attribute instead of a collection on the &#8220;many&#8221; side
  199. of the relationship. To convert one-to-many into one-to-one:</p>
  200. <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>
  201. <span class="n">__tablename__</span> <span class="o">=</span> <span class="s">&#39;parent&#39;</span>
  202. <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>
  203. <span class="n">child</span> <span class="o">=</span> <span class="n">relationship</span><span class="p">(</span><span class="s">&quot;Child&quot;</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">&quot;parent&quot;</span><span class="p">)</span>
  204. <span class="k">class</span> <span class="nc">Child</span><span class="p">(</span><span class="n">Base</span><span class="p">):</span>
  205. <span class="n">__tablename__</span> <span class="o">=</span> <span class="s">&#39;child&#39;</span>
  206. <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>
  207. <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">&#39;parent.id&#39;</span><span class="p">))</span></pre></div>
  208. </div>
  209. <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
  210. to provide arguments for the reverse side:</p>
  211. <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>
  212. <span class="n">__tablename__</span> <span class="o">=</span> <span class="s">&#39;parent&#39;</span>
  213. <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>
  214. <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">&#39;child.id&#39;</span><span class="p">))</span>
  215. <span class="n">child</span> <span class="o">=</span> <span class="n">relationship</span><span class="p">(</span><span class="s">&quot;Child&quot;</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">&quot;parent&quot;</span><span class="p">,</span> <span class="n">uselist</span><span class="o">=</span><span class="bp">False</span><span class="p">))</span>
  216. <span class="k">class</span> <span class="nc">Child</span><span class="p">(</span><span class="n">Base</span><span class="p">):</span>
  217. <span class="n">__tablename__</span> <span class="o">=</span> <span class="s">&#39;child&#39;</span>
  218. <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>
  219. </div>
  220. </div>
  221. <div class="section" id="many-to-many">
  222. <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>
  223. <p>Many to Many adds an association table between two classes. The association
  224. table is indicated by the <tt class="docutils literal"><span class="pre">secondary</span></tt> argument to
  225. <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>
  226. 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>
  227. directives can locate the remote tables with which to link:</p>
  228. <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">&#39;association&#39;</span><span class="p">,</span> <span class="n">Base</span><span class="o">.</span><span class="n">metadata</span><span class="p">,</span>
  229. <span class="n">Column</span><span class="p">(</span><span class="s">&#39;left_id&#39;</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">&#39;left.id&#39;</span><span class="p">)),</span>
  230. <span class="n">Column</span><span class="p">(</span><span class="s">&#39;right_id&#39;</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">&#39;right.id&#39;</span><span class="p">))</span>
  231. <span class="p">)</span>
  232. <span class="k">class</span> <span class="nc">Parent</span><span class="p">(</span><span class="n">Base</span><span class="p">):</span>
  233. <span class="n">__tablename__</span> <span class="o">=</span> <span class="s">&#39;left&#39;</span>
  234. <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>
  235. <span class="n">children</span> <span class="o">=</span> <span class="n">relationship</span><span class="p">(</span><span class="s">&quot;Child&quot;</span><span class="p">,</span>
  236. <span class="n">secondary</span><span class="o">=</span><span class="n">association_table</span><span class="p">)</span>
  237. <span class="k">class</span> <span class="nc">Child</span><span class="p">(</span><span class="n">Base</span><span class="p">):</span>
  238. <span class="n">__tablename__</span> <span class="o">=</span> <span class="s">&#39;right&#39;</span>
  239. <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>
  240. </div>
  241. <p>For a bidirectional relationship, both sides of the relationship contain a
  242. collection. The <tt class="docutils literal"><span class="pre">backref</span></tt> keyword will automatically use
  243. the same <tt class="docutils literal"><span class="pre">secondary</span></tt> argument for the reverse relationship:</p>
  244. <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">&#39;association&#39;</span><span class="p">,</span> <span class="n">Base</span><span class="o">.</span><span class="n">metadata</span><span class="p">,</span>
  245. <span class="n">Column</span><span class="p">(</span><span class="s">&#39;left_id&#39;</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">&#39;left.id&#39;</span><span class="p">)),</span>
  246. <span class="n">Column</span><span class="p">(</span><span class="s">&#39;right_id&#39;</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">&#39;right.id&#39;</span><span class="p">))</span>
  247. <span class="p">)</span>
  248. <span class="k">class</span> <span class="nc">Parent</span><span class="p">(</span><span class="n">Base</span><span class="p">):</span>
  249. <span class="n">__tablename__</span> <span class="o">=</span> <span class="s">&#39;left&#39;</span>
  250. <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>
  251. <span class="n">children</span> <span class="o">=</span> <span class="n">relationship</span><span class="p">(</span><span class="s">&quot;Child&quot;</span><span class="p">,</span>
  252. <span class="n">secondary</span><span class="o">=</span><span class="n">association_table</span><span class="p">,</span>
  253. <span class="n">backref</span><span class="o">=</span><span class="s">&quot;parents&quot;</span><span class="p">)</span>
  254. <span class="k">class</span> <span class="nc">Child</span><span class="p">(</span><span class="n">Base</span><span class="p">):</span>
  255. <span class="n">__tablename__</span> <span class="o">=</span> <span class="s">&#39;right&#39;</span>
  256. <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>
  257. </div>
  258. <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
  259. that returns the ultimate argument, which is evaluated only when mappers are
  260. first used. Using this, we can define the <tt class="docutils literal"><span class="pre">association_table</span></tt> at a later
  261. point, as long as it&#8217;s available to the callable after all module initialization
  262. is complete:</p>
  263. <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>
  264. <span class="n">__tablename__</span> <span class="o">=</span> <span class="s">&#39;left&#39;</span>
  265. <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>
  266. <span class="n">children</span> <span class="o">=</span> <span class="n">relationship</span><span class="p">(</span><span class="s">&quot;Child&quot;</span><span class="p">,</span>
  267. <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>
  268. <span class="n">backref</span><span class="o">=</span><span class="s">&quot;parents&quot;</span><span class="p">)</span></pre></div>
  269. </div>
  270. <p>With the declarative extension in use, the traditional &#8220;string name of the table&#8221;
  271. 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>
  272. <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>
  273. <span class="n">__tablename__</span> <span class="o">=</span> <span class="s">&#39;left&#39;</span>
  274. <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>
  275. <span class="n">children</span> <span class="o">=</span> <span class="n">relationship</span><span class="p">(</span><span class="s">&quot;Child&quot;</span><span class="p">,</span>
  276. <span class="n">secondary</span><span class="o">=</span><span class="s">&quot;association&quot;</span><span class="p">,</span>
  277. <span class="n">backref</span><span class="o">=</span><span class="s">&quot;parents&quot;</span><span class="p">)</span></pre></div>
  278. </div>
  279. </div>
  280. <div class="section" id="association-object">
  281. <span id="association-pattern"></span><h3>Association Object<a class="headerlink" href="#association-object" title="Permalink to this headline">ś</a></h3>
  282. <p>The association object pattern is a variant on many-to-many: it&#8217;s
  283. used when your association table contains additional columns beyond those
  284. which are foreign keys to the left and right tables. Instead of using the
  285. <tt class="docutils literal"><span class="pre">secondary</span></tt> argument, you map a new class directly to the association table.
  286. The left side of the relationship references the association object via
  287. one-to-many, and the association class references the right side via
  288. many-to-one. Below we illustrate an association table mapped to the
  289. <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>,
  290. which is a string value that is stored along with each association
  291. between <tt class="docutils literal"><span class="pre">Parent</span></tt> and <tt class="docutils literal"><span class="pre">Child</span></tt>:</p>
  292. <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>
  293. <span class="n">__tablename__</span> <span class="o">=</span> <span class="s">&#39;association&#39;</span>
  294. <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">&#39;left.id&#39;</span><span class="p">),</span> <span class="n">primary_key</span><span class="o">=</span><span class="bp">True</span><span class="p">)</span>
  295. <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">&#39;right.id&#39;</span><span class="p">),</span> <span class="n">primary_key</span><span class="o">=</span><span class="bp">True</span><span class="p">)</span>
  296. <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>
  297. <span class="n">child</span> <span class="o">=</span> <span class="n">relationship</span><span class="p">(</span><span class="s">&quot;Child&quot;</span><span class="p">)</span>
  298. <span class="k">class</span> <span class="nc">Parent</span><span class="p">(</span><span class="n">Base</span><span class="p">):</span>
  299. <span class="n">__tablename__</span> <span class="o">=</span> <span class="s">&#39;left&#39;</span>
  300. <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>
  301. <span class="n">children</span> <span class="o">=</span> <span class="n">relationship</span><span class="p">(</span><span class="s">&quot;Association&quot;</span><span class="p">)</span>
  302. <span class="k">class</span> <span class="nc">Child</span><span class="p">(</span><span class="n">Base</span><span class="p">):</span>
  303. <span class="n">__tablename__</span> <span class="o">=</span> <span class="s">&#39;right&#39;</span>
  304. <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>
  305. </div>
  306. <p>The bidirectional version adds backrefs to both relationships:</p>
  307. <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>
  308. <span class="n">__tablename__</span> <span class="o">=</span> <span class="s">&#39;association&#39;</span>
  309. <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">&#39;left.id&#39;</span><span class="p">),</span> <span class="n">primary_key</span><span class="o">=</span><span class="bp">True</span><span class="p">)</span>
  310. <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">&#39;right.id&#39;</span><span class="p">),</span> <span class="n">primary_key</span><span class="o">=</span><span class="bp">True</span><span class="p">)</span>
  311. <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>
  312. <span class="n">child</span> <span class="o">=</span> <span class="n">relationship</span><span class="p">(</span><span class="s">&quot;Child&quot;</span><span class="p">,</span> <span class="n">backref</span><span class="o">=</span><span class="s">&quot;parent_assocs&quot;</span><span class="p">)</span>
  313. <span class="k">class</span> <span class="nc">Parent</span><span class="p">(</span><span class="n">Base</span><span class="p">):</span>
  314. <span class="n">__tablename__</span> <span class="o">=</span> <span class="s">&#39;left&#39;</span>
  315. <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>
  316. <span class="n">children</span> <span class="o">=</span> <span class="n">relationship</span><span class="p">(</span><span class="s">&quot;Association&quot;</span><span class="p">,</span> <span class="n">backref</span><span class="o">=</span><span class="s">&quot;parent&quot;</span><span class="p">)</span>
  317. <span class="k">class</span> <span class="nc">Child</span><span class="p">(</span><span class="n">Base</span><span class="p">):</span>
  318. <span class="n">__tablename__</span> <span class="o">=</span> <span class="s">&#39;right&#39;</span>
  319. <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>
  320. </div>
  321. <p>Working with the association pattern in its direct form requires that child
  322. objects are associated with an association instance before being appended to
  323. the parent; similarly, access from parent to child goes through the
  324. association object:</p>
  325. <div class="highlight-python"><div class="highlight"><pre><span class="c"># create parent, append a child via association</span>
  326. <span class="n">p</span> <span class="o">=</span> <span class="n">Parent</span><span class="p">()</span>
  327. <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">&quot;some data&quot;</span><span class="p">)</span>
  328. <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>
  329. <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>
  330. <span class="c"># iterate through child objects via association, including association</span>
  331. <span class="c"># attributes</span>
  332. <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>
  333. <span class="k">print</span> <span class="n">assoc</span><span class="o">.</span><span class="n">extra_data</span>
  334. <span class="k">print</span> <span class="n">assoc</span><span class="o">.</span><span class="n">child</span></pre></div>
  335. </div>
  336. <p>To enhance the association object pattern such that direct
  337. access to the <tt class="docutils literal"><span class="pre">Association</span></tt> object is optional, SQLAlchemy
  338. provides the <a class="reference internal" href="extensions/associationproxy.html"><em>Association Proxy</em></a> extension. This
  339. extension allows the configuration of attributes which will
  340. access two &#8220;hops&#8221; with a single access, one &#8220;hop&#8221; to the
  341. associated object, and a second to a target attribute.</p>
  342. <div class="admonition note">
  343. <p class="first admonition-title">Note</p>
  344. <p class="last">When using the association object pattern, it is
  345. advisable that the association-mapped table not be used
  346. 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>
  347. 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
  348. the option <tt class="docutils literal"><span class="pre">viewonly=True</span></tt>. SQLAlchemy otherwise
  349. may attempt to emit redundant INSERT and DELETE
  350. statements on the same table, if similar state is detected
  351. on the related attribute as well as the associated
  352. object.</p>
  353. </div>
  354. </div>
  355. </div>
  356. <div class="section" id="adjacency-list-relationships">
  357. <h2>Adjacency List Relationships<a class="headerlink" href="#adjacency-list-relationships" title="Permalink to this headline">ś</a></h2>
  358. <p>The <strong>adjacency list</strong> pattern is a common relational pattern whereby a table
  359. contains a foreign key reference to itself. This is the most common
  360. way to represent hierarchical data in flat tables. Other methods
  361. include <strong>nested sets</strong>, sometimes called &#8220;modified preorder&#8221;,
  362. as well as <strong>materialized path</strong>. Despite the appeal that modified preorder
  363. has when evaluated for its fluency within SQL queries, the adjacency list model is
  364. probably the most appropriate pattern for the large majority of hierarchical
  365. storage needs, for reasons of concurrency, reduced complexity, and that
  366. modified preorder has little advantage over an application which can fully
  367. load subtrees into the application space.</p>
  368. <p>In this example, we&#8217;ll work with a single mapped
  369. class called <tt class="docutils literal"><span class="pre">Node</span></tt>, representing a tree structure:</p>
  370. <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>
  371. <span class="n">__tablename__</span> <span class="o">=</span> <span class="s">&#39;node&#39;</span>
  372. <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>
  373. <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">&#39;node.id&#39;</span><span class="p">))</span>
  374. <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>
  375. <span class="n">children</span> <span class="o">=</span> <span class="n">relationship</span><span class="p">(</span><span class="s">&quot;Node&quot;</span><span class="p">)</span></pre></div>
  376. </div>
  377. <p>With this structure, a graph such as the following:</p>
  378. <div class="highlight-python"><pre>root --+---&gt; child1
  379. +---&gt; child2 --+--&gt; subchild1
  380. | +--&gt; subchild2
  381. +---&gt; child3</pre>
  382. </div>
  383. <p>Would be represented with data such as:</p>
  384. <div class="highlight-python"><pre>id parent_id data
  385. --- ------- ----
  386. 1 NULL root
  387. 2 1 child1
  388. 3 1 child2
  389. 4 3 subchild1
  390. 5 3 subchild2
  391. 6 1 child3</pre>
  392. </div>
  393. <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
  394. same way as a &#8220;normal&#8221; one-to-many relationship, with the
  395. exception that the &#8220;direction&#8221;, i.e. whether the relationship
  396. is one-to-many or many-to-one, is assumed by default to
  397. be one-to-many. To establish the relationship as many-to-one,
  398. an extra directive is added known as <tt class="docutils literal"><span class="pre">remote_side</span></tt>, which
  399. 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
  400. that indicate those which should be considered to be &#8220;remote&#8221;:</p>
  401. <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>
  402. <span class="n">__tablename__</span> <span class="o">=</span> <span class="s">&#39;node&#39;</span>
  403. <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>
  404. <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">&#39;node.id&#39;</span><span class="p">))</span>
  405. <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>
  406. <span class="n">parent</span> <span class="o">=</span> <span class="n">relationship</span><span class="p">(</span><span class="s">&quot;Node&quot;</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>
  407. </div>
  408. <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>
  409. 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
  410. <tt class="docutils literal"><span class="pre">parent_id</span></tt> as the &#8220;local&#8221; side, and the relationship
  411. then behaves as a many-to-one.</p>
  412. <p>As always, both directions can be combined into a bidirectional
  413. 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>
  414. <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>
  415. <span class="n">__tablename__</span> <span class="o">=</span> <span class="s">&#39;node&#39;</span>
  416. <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>
  417. <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">&#39;node.id&#39;</span><span class="p">))</span>
  418. <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>
  419. <span class="n">children</span> <span class="o">=</span> <span class="n">relationship</span><span class="p">(</span><span class="s">&quot;Node&quot;</span><span class="p">,</span>
  420. <span class="n">backref</span><span class="o">=</span><span class="n">backref</span><span class="p">(</span><span class="s">&#39;parent&#39;</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>
  421. <span class="p">)</span></pre></div>
  422. </div>
  423. <p>There are several examples included with SQLAlchemy illustrating
  424. self-referential strategies; these include <a class="reference internal" href="examples.html#examples-adjacencylist"><em>Adjacency List</em></a> and
  425. <a class="reference internal" href="examples.html#examples-xmlpersistence"><em>XML Persistence</em></a>.</p>
  426. <div class="section" id="self-referential-query-strategies">
  427. <h3>Self-Referential Query Strategies<a class="headerlink" href="#self-referential-query-strategies" title="Permalink to this headline">ś</a></h3>
  428. <p>Querying of self-referential structures works like any other query:</p>
  429. <div class="highlight-python"><div class="highlight"><pre><span class="c"># get all nodes named &#39;child2&#39;</span>
  430. <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">&#39;child2&#39;</span><span class="p">)</span></pre></div>
  431. </div>
  432. <p>However extra care is needed when attempting to join along
  433. the foreign key from one level of the tree to the next. In SQL,
  434. a join from a table to itself requires that at least one side of the
  435. expression be &#8220;aliased&#8221; so that it can be unambiguously referred to.</p>
  436. <p>Recall from <a class="ref…

Large files files are truncated, but you can click here to view the full file