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