<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head> <meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> <title> ORM Configuration — SQLAlchemy 1.2 Documentation </title> <!-- begin iterate through site-imported + sphinx environment css_files --> <link rel="stylesheet" href="../_static/pygments.css" type="text/css" /> <link rel="stylesheet" href="../_static/docs.css" type="text/css" /> <link rel="stylesheet" href="../_static/changelog.css" type="text/css" /> <link rel="stylesheet" href="../_static/sphinx_paramlinks.css" type="text/css" /> <!-- end iterate through site-imported + sphinx environment css_files --> <!-- begin layout.mako headers --> <link rel="index" title="Index" href="../genindex.html" /> <link rel="search" title="Search" href="../search.html" /> <link rel="copyright" title="Copyright" href="../copyright.html" /> <link rel="top" title="SQLAlchemy 1.2 Documentation" href="../index.html" /> <link rel="up" title="Frequently Asked Questions" href="index.html" /> <link rel="next" title="Performance" href="performance.html" /> <link rel="prev" title="SQL Expressions" href="sqlexpressions.html" /> <!-- end layout.mako headers --> </head> <body> <div id="docs-container"> <div id="docs-top-navigation-container" class="body-background"> <div id="docs-header"> <div id="docs-version-header"> Release: <span class="version-num">1.2.19</span> | Release Date: April 15, 2019 </div> <h1>SQLAlchemy 1.2 Documentation</h1> </div> </div> <div id="docs-body-container"> <div id="fixed-sidebar" class="withsidebar"> <div id="docs-sidebar-popout"> <h3><a href="../index.html">SQLAlchemy 1.2 Documentation</a></h3> <p id="sidebar-topnav"> <a href="../contents.html">Contents</a> | <a href="../genindex.html">Index</a> </p> <div id="sidebar-search"> <form class="search" action="../search.html" method="get"> <label> Search terms: <input type="text" placeholder="search..." name="q" size="12" /> </label> <input type="hidden" name="check_keywords" value="yes" /> <input type="hidden" name="area" value="default" /> </form> </div> </div> <div id="docs-sidebar"> <div id="sidebar-banner"> </div> <div id="docs-sidebar-inner"> <h3> <a href="index.html" title="Frequently Asked Questions">Frequently Asked Questions</a> </h3> <ul> <li><span class="link-container"><a class="reference external" href="connections.html">Connections / Engines</a></span></li> <li><span class="link-container"><a class="reference external" href="metadata_schema.html">MetaData / Schema</a></span></li> <li><span class="link-container"><a class="reference external" href="sqlexpressions.html">SQL Expressions</a></span></li> <li class="selected"><span class="link-container"><strong>ORM Configuration</strong><a class="paramlink headerlink reference internal" href="#">¶</a></span><ul> <li><span class="link-container"><a class="reference external" href="#how-do-i-map-a-table-that-has-no-primary-key">How do I map a table that has no primary key?</a></span></li> <li><span class="link-container"><a class="reference external" href="#how-do-i-configure-a-column-that-is-a-python-reserved-word-or-similar">How do I configure a Column that is a Python reserved word or similar?</a></span></li> <li><span class="link-container"><a class="reference external" href="#how-do-i-get-a-list-of-all-columns-relationships-mapped-attributes-etc-given-a-mapped-class">How do I get a list of all columns, relationships, mapped attributes, etc. given a mapped class?</a></span></li> <li><span class="link-container"><a class="reference external" href="#i-m-getting-a-warning-or-error-about-implicitly-combining-column-x-under-attribute-y">I’m getting a warning or error about “Implicitly combining column X under attribute Y”</a></span></li> <li><span class="link-container"><a class="reference external" href="#i-m-using-declarative-and-setting-primaryjoin-secondaryjoin-using-an-and-or-or-and-i-am-getting-an-error-message-about-foreign-keys">I’m using Declarative and setting primaryjoin/secondaryjoin using an <code class="docutils literal notranslate"><span class="pre">and_()</span></code> or <code class="docutils literal notranslate"><span class="pre">or_()</span></code>, and I am getting an error message about foreign keys.</a></span></li> <li><span class="link-container"><a class="reference external" href="#why-is-order-by-required-with-limit-especially-with-subqueryload">Why is <code class="docutils literal notranslate"><span class="pre">ORDER</span> <span class="pre">BY</span></code> required with <code class="docutils literal notranslate"><span class="pre">LIMIT</span></code> (especially with <code class="docutils literal notranslate"><span class="pre">subqueryload()</span></code>)?</a></span></li> </ul> </li> <li><span class="link-container"><a class="reference external" href="performance.html">Performance</a></span></li> <li><span class="link-container"><a class="reference external" href="sessions.html">Sessions / Queries</a></span></li> </ul> </div> </div> </div> <div id="docs-body" class="withsidebar" > <div class="section" id="orm-configuration"> <h1>ORM Configuration<a class="headerlink" href="#orm-configuration" title="Permalink to this headline">¶</a></h1> <div class="contents faq local topic" id="contents"> <ul class="simple"> <li><p><a class="reference internal" href="#how-do-i-map-a-table-that-has-no-primary-key" id="id1">How do I map a table that has no primary key?</a></p></li> <li><p><a class="reference internal" href="#how-do-i-configure-a-column-that-is-a-python-reserved-word-or-similar" id="id2">How do I configure a Column that is a Python reserved word or similar?</a></p></li> <li><p><a class="reference internal" href="#how-do-i-get-a-list-of-all-columns-relationships-mapped-attributes-etc-given-a-mapped-class" id="id3">How do I get a list of all columns, relationships, mapped attributes, etc. given a mapped class?</a></p></li> <li><p><a class="reference internal" href="#i-m-getting-a-warning-or-error-about-implicitly-combining-column-x-under-attribute-y" id="id4">I’m getting a warning or error about “Implicitly combining column X under attribute Y”</a></p></li> <li><p><a class="reference internal" href="#i-m-using-declarative-and-setting-primaryjoin-secondaryjoin-using-an-and-or-or-and-i-am-getting-an-error-message-about-foreign-keys" id="id5">I’m using Declarative and setting primaryjoin/secondaryjoin using an <code class="docutils literal notranslate"><span class="pre">and_()</span></code> or <code class="docutils literal notranslate"><span class="pre">or_()</span></code>, and I am getting an error message about foreign keys.</a></p></li> <li><p><a class="reference internal" href="#why-is-order-by-required-with-limit-especially-with-subqueryload" id="id6">Why is <code class="docutils literal notranslate"><span class="pre">ORDER</span> <span class="pre">BY</span></code> required with <code class="docutils literal notranslate"><span class="pre">LIMIT</span></code> (especially with <code class="docutils literal notranslate"><span class="pre">subqueryload()</span></code>)?</a></p></li> </ul> </div> <div class="section" id="how-do-i-map-a-table-that-has-no-primary-key"> <span id="faq-mapper-primary-key"></span><h2>How do I map a table that has no primary key?<a class="headerlink" href="#how-do-i-map-a-table-that-has-no-primary-key" title="Permalink to this headline">¶</a></h2> <p>The SQLAlchemy ORM, in order to map to a particular table, needs there to be at least one column denoted as a primary key column; multiple-column, i.e. composite, primary keys are of course entirely feasible as well. These columns do <strong>not</strong> need to be actually known to the database as primary key columns, though it’s a good idea that they are. It’s only necessary that the columns <em>behave</em> as a primary key does, e.g. as a unique and not nullable identifier for a row.</p> <p>Most ORMs require that objects have some kind of primary key defined because the object in memory must correspond to a uniquely identifiable row in the database table; at the very least, this allows the object can be targeted for UPDATE and DELETE statements which will affect only that object’s row and no other. However, the importance of the primary key goes far beyond that. In SQLAlchemy, all ORM-mapped objects are at all times linked uniquely within a <a class="reference internal" href="../orm/session_api.html#sqlalchemy.orm.session.Session" title="sqlalchemy.orm.session.Session"><code class="xref py py-class docutils literal notranslate"><span class="pre">Session</span></code></a> to their specific database row using a pattern called the <a class="reference internal" href="../glossary.html#term-identity-map"><span class="xref std std-term">identity map</span></a>, a pattern that’s central to the unit of work system employed by SQLAlchemy, and is also key to the most common (and not-so-common) patterns of ORM usage.</p> <div class="admonition note"> <p class="admonition-title">Note</p> <p>It’s important to note that we’re only talking about the SQLAlchemy ORM; an application which builds on Core and deals only with <a class="reference internal" href="../core/metadata.html#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><code class="xref py py-class docutils literal notranslate"><span class="pre">Table</span></code></a> objects, <a class="reference internal" href="../core/selectable.html#sqlalchemy.sql.expression.select" title="sqlalchemy.sql.expression.select"><code class="xref py py-func docutils literal notranslate"><span class="pre">select()</span></code></a> constructs and the like, <strong>does not</strong> need any primary key to be present on or associated with a table in any way (though again, in SQL, all tables should really have some kind of primary key, lest you need to actually update or delete specific rows).</p> </div> <p>In almost all cases, a table does have a so-called <a class="reference internal" href="../glossary.html#term-candidate-key"><span class="xref std std-term">candidate key</span></a>, which is a column or series of columns that uniquely identify a row. If a table truly doesn’t have this, and has actual fully duplicate rows, the table is not corresponding to <a class="reference external" href="http://en.wikipedia.org/wiki/First_normal_form">first normal form</a> and cannot be mapped. Otherwise, whatever columns comprise the best candidate key can be applied directly to the mapper:</p> <div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="k">class</span> <span class="nc">SomeClass</span><span class="p">(</span><span class="n">Base</span><span class="p">):</span> <span class="n">__table__</span> <span class="o">=</span> <span class="n">some_table_with_no_pk</span> <span class="n">__mapper_args__</span> <span class="o">=</span> <span class="p">{</span> <span class="s1">'primary_key'</span><span class="p">:[</span><span class="n">some_table_with_no_pk</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">uid</span><span class="p">,</span> <span class="n">some_table_with_no_pk</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">bar</span><span class="p">]</span> <span class="p">}</span></pre></div> </div> <p>Better yet is when using fully declared table metadata, use the <code class="docutils literal notranslate"><span class="pre">primary_key=True</span></code> flag on those columns:</p> <div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="k">class</span> <span class="nc">SomeClass</span><span class="p">(</span><span class="n">Base</span><span class="p">):</span> <span class="n">__tablename__</span> <span class="o">=</span> <span class="s2">"some_table_with_no_pk"</span> <span class="n">uid</span> <span class="o">=</span> <span class="n">Column</span><span class="p">(</span><span class="n">Integer</span><span class="p">,</span> <span class="n">primary_key</span><span class="o">=</span><span class="kc">True</span><span class="p">)</span> <span class="n">bar</span> <span class="o">=</span> <span class="n">Column</span><span class="p">(</span><span class="n">String</span><span class="p">,</span> <span class="n">primary_key</span><span class="o">=</span><span class="kc">True</span><span class="p">)</span></pre></div> </div> <p>All tables in a relational database should have primary keys. Even a many-to-many association table - the primary key would be the composite of the two association columns:</p> <div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">CREATE</span> <span class="n">TABLE</span> <span class="n">my_association</span> <span class="p">(</span> <span class="n">user_id</span> <span class="n">INTEGER</span> <span class="n">REFERENCES</span> <span class="n">user</span><span class="p">(</span><span class="nb">id</span><span class="p">),</span> <span class="n">account_id</span> <span class="n">INTEGER</span> <span class="n">REFERENCES</span> <span class="n">account</span><span class="p">(</span><span class="nb">id</span><span class="p">),</span> <span class="n">PRIMARY</span> <span class="n">KEY</span> <span class="p">(</span><span class="n">user_id</span><span class="p">,</span> <span class="n">account_id</span><span class="p">)</span> <span class="p">)</span></pre></div> </div> </div> <div class="section" id="how-do-i-configure-a-column-that-is-a-python-reserved-word-or-similar"> <h2>How do I configure a Column that is a Python reserved word or similar?<a class="headerlink" href="#how-do-i-configure-a-column-that-is-a-python-reserved-word-or-similar" title="Permalink to this headline">¶</a></h2> <p>Column-based attributes can be given any name desired in the mapping. See <a class="reference internal" href="../orm/mapping_columns.html#mapper-column-distinct-names"><span class="std std-ref">Naming Columns Distinctly from Attribute Names</span></a>.</p> </div> <div class="section" id="how-do-i-get-a-list-of-all-columns-relationships-mapped-attributes-etc-given-a-mapped-class"> <h2>How do I get a list of all columns, relationships, mapped attributes, etc. given a mapped class?<a class="headerlink" href="#how-do-i-get-a-list-of-all-columns-relationships-mapped-attributes-etc-given-a-mapped-class" title="Permalink to this headline">¶</a></h2> <p>This information is all available from the <a class="reference internal" href="../orm/mapping_api.html#sqlalchemy.orm.mapper.Mapper" title="sqlalchemy.orm.mapper.Mapper"><code class="xref py py-class docutils literal notranslate"><span class="pre">Mapper</span></code></a> object.</p> <p>To get at the <a class="reference internal" href="../orm/mapping_api.html#sqlalchemy.orm.mapper.Mapper" title="sqlalchemy.orm.mapper.Mapper"><code class="xref py py-class docutils literal notranslate"><span class="pre">Mapper</span></code></a> for a particular mapped class, call the <a class="reference internal" href="../core/inspection.html#sqlalchemy.inspection.inspect" title="sqlalchemy.inspection.inspect"><code class="xref py py-func docutils literal notranslate"><span class="pre">inspect()</span></code></a> function on it:</p> <div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="kn">from</span> <span class="nn">sqlalchemy</span> <span class="k">import</span> <span class="n">inspect</span> <span class="n">mapper</span> <span class="o">=</span> <span class="n">inspect</span><span class="p">(</span><span class="n">MyClass</span><span class="p">)</span></pre></div> </div> <p>From there, all information about the class can be accessed through properties such as:</p> <ul class="simple"> <li><p><a class="reference internal" href="../orm/mapping_api.html#sqlalchemy.orm.mapper.Mapper.attrs" title="sqlalchemy.orm.mapper.Mapper.attrs"><code class="xref py py-attr docutils literal notranslate"><span class="pre">Mapper.attrs</span></code></a> - a namespace of all mapped attributes. The attributes themselves are instances of <a class="reference internal" href="../orm/internals.html#sqlalchemy.orm.interfaces.MapperProperty" title="sqlalchemy.orm.interfaces.MapperProperty"><code class="xref py py-class docutils literal notranslate"><span class="pre">MapperProperty</span></code></a>, which contain additional attributes that can lead to the mapped SQL expression or column, if applicable.</p></li> <li><p><a class="reference internal" href="../orm/mapping_api.html#sqlalchemy.orm.mapper.Mapper.column_attrs" title="sqlalchemy.orm.mapper.Mapper.column_attrs"><code class="xref py py-attr docutils literal notranslate"><span class="pre">Mapper.column_attrs</span></code></a> - the mapped attribute namespace limited to column and SQL expression attributes. You might want to use <a class="reference internal" href="../orm/mapping_api.html#sqlalchemy.orm.mapper.Mapper.columns" title="sqlalchemy.orm.mapper.Mapper.columns"><code class="xref py py-attr docutils literal notranslate"><span class="pre">Mapper.columns</span></code></a> to get at the <a class="reference internal" href="../core/metadata.html#sqlalchemy.schema.Column" title="sqlalchemy.schema.Column"><code class="xref py py-class docutils literal notranslate"><span class="pre">Column</span></code></a> objects directly.</p></li> <li><p><a class="reference internal" href="../orm/mapping_api.html#sqlalchemy.orm.mapper.Mapper.relationships" title="sqlalchemy.orm.mapper.Mapper.relationships"><code class="xref py py-attr docutils literal notranslate"><span class="pre">Mapper.relationships</span></code></a> - namespace of all <a class="reference internal" href="../orm/internals.html#sqlalchemy.orm.properties.RelationshipProperty" title="sqlalchemy.orm.properties.RelationshipProperty"><code class="xref py py-class docutils literal notranslate"><span class="pre">RelationshipProperty</span></code></a> attributes.</p></li> <li><p><a class="reference internal" href="../orm/mapping_api.html#sqlalchemy.orm.mapper.Mapper.all_orm_descriptors" title="sqlalchemy.orm.mapper.Mapper.all_orm_descriptors"><code class="xref py py-attr docutils literal notranslate"><span class="pre">Mapper.all_orm_descriptors</span></code></a> - namespace of all mapped attributes, plus user-defined attributes defined using systems such as <a class="reference internal" href="../orm/extensions/hybrid.html#sqlalchemy.ext.hybrid.hybrid_property" title="sqlalchemy.ext.hybrid.hybrid_property"><code class="xref py py-class docutils literal notranslate"><span class="pre">hybrid_property</span></code></a>, <a class="reference internal" href="../orm/extensions/associationproxy.html#sqlalchemy.ext.associationproxy.AssociationProxy" title="sqlalchemy.ext.associationproxy.AssociationProxy"><code class="xref py py-class docutils literal notranslate"><span class="pre">AssociationProxy</span></code></a> and others.</p></li> <li><p><a class="reference internal" href="../orm/mapping_api.html#sqlalchemy.orm.mapper.Mapper.columns" title="sqlalchemy.orm.mapper.Mapper.columns"><code class="xref py py-attr docutils literal notranslate"><span class="pre">Mapper.columns</span></code></a> - A namespace of <a class="reference internal" href="../core/metadata.html#sqlalchemy.schema.Column" title="sqlalchemy.schema.Column"><code class="xref py py-class docutils literal notranslate"><span class="pre">Column</span></code></a> objects and other named SQL expressions associated with the mapping.</p></li> <li><p><a class="reference internal" href="../orm/mapping_api.html#sqlalchemy.orm.mapper.Mapper.mapped_table" title="sqlalchemy.orm.mapper.Mapper.mapped_table"><code class="xref py py-attr docutils literal notranslate"><span class="pre">Mapper.mapped_table</span></code></a> - The <a class="reference internal" href="../core/metadata.html#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><code class="xref py py-class docutils literal notranslate"><span class="pre">Table</span></code></a> or other selectable to which this mapper is mapped.</p></li> <li><p><a class="reference internal" href="../orm/mapping_api.html#sqlalchemy.orm.mapper.Mapper.local_table" title="sqlalchemy.orm.mapper.Mapper.local_table"><code class="xref py py-attr docutils literal notranslate"><span class="pre">Mapper.local_table</span></code></a> - The <a class="reference internal" href="../core/metadata.html#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><code class="xref py py-class docutils literal notranslate"><span class="pre">Table</span></code></a> that is “local” to this mapper; this differs from <a class="reference internal" href="../orm/mapping_api.html#sqlalchemy.orm.mapper.Mapper.mapped_table" title="sqlalchemy.orm.mapper.Mapper.mapped_table"><code class="xref py py-attr docutils literal notranslate"><span class="pre">Mapper.mapped_table</span></code></a> in the case of a mapper mapped using inheritance to a composed selectable.</p></li> </ul> </div> <div class="section" id="i-m-getting-a-warning-or-error-about-implicitly-combining-column-x-under-attribute-y"> <span id="faq-combining-columns"></span><h2>I’m getting a warning or error about “Implicitly combining column X under attribute Y”<a class="headerlink" href="#i-m-getting-a-warning-or-error-about-implicitly-combining-column-x-under-attribute-y" title="Permalink to this headline">¶</a></h2> <p>This condition refers to when a mapping contains two columns that are being mapped under the same attribute name due to their name, but there’s no indication that this is intentional. A mapped class needs to have explicit names for every attribute that is to store an independent value; when two columns have the same name and aren’t disambiguated, they fall under the same attribute and the effect is that the value from one column is <strong>copied</strong> into the other, based on which column was assigned to the attribute first.</p> <p>This behavior is often desirable and is allowed without warning in the case where the two columns are linked together via a foreign key relationship within an inheritance mapping. When the warning or exception occurs, the issue can be resolved by either assigning the columns to differently-named attributes, or if combining them together is desired, by using <a class="reference internal" href="../orm/mapping_columns.html#sqlalchemy.orm.column_property" title="sqlalchemy.orm.column_property"><code class="xref py py-func docutils literal notranslate"><span class="pre">column_property()</span></code></a> to make this explicit.</p> <p>Given the example as follows:</p> <div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="kn">from</span> <span class="nn">sqlalchemy</span> <span class="k">import</span> <span class="n">Integer</span><span class="p">,</span> <span class="n">Column</span><span class="p">,</span> <span class="n">ForeignKey</span> <span class="kn">from</span> <span class="nn">sqlalchemy.ext.declarative</span> <span class="k">import</span> <span class="n">declarative_base</span> <span class="n">Base</span> <span class="o">=</span> <span class="n">declarative_base</span><span class="p">()</span> <span class="k">class</span> <span class="nc">A</span><span class="p">(</span><span class="n">Base</span><span class="p">):</span> <span class="n">__tablename__</span> <span class="o">=</span> <span class="s1">'a'</span> <span class="nb">id</span> <span class="o">=</span> <span class="n">Column</span><span class="p">(</span><span class="n">Integer</span><span class="p">,</span> <span class="n">primary_key</span><span class="o">=</span><span class="kc">True</span><span class="p">)</span> <span class="k">class</span> <span class="nc">B</span><span class="p">(</span><span class="n">A</span><span class="p">):</span> <span class="n">__tablename__</span> <span class="o">=</span> <span class="s1">'b'</span> <span class="nb">id</span> <span class="o">=</span> <span class="n">Column</span><span class="p">(</span><span class="n">Integer</span><span class="p">,</span> <span class="n">primary_key</span><span class="o">=</span><span class="kc">True</span><span class="p">)</span> <span class="n">a_id</span> <span class="o">=</span> <span class="n">Column</span><span class="p">(</span><span class="n">Integer</span><span class="p">,</span> <span class="n">ForeignKey</span><span class="p">(</span><span class="s1">'a.id'</span><span class="p">))</span></pre></div> </div> <p>As of SQLAlchemy version 0.9.5, the above condition is detected, and will warn that the <code class="docutils literal notranslate"><span class="pre">id</span></code> column of <code class="docutils literal notranslate"><span class="pre">A</span></code> and <code class="docutils literal notranslate"><span class="pre">B</span></code> is being combined under the same-named attribute <code class="docutils literal notranslate"><span class="pre">id</span></code>, which above is a serious issue since it means that a <code class="docutils literal notranslate"><span class="pre">B</span></code> object’s primary key will always mirror that of its <code class="docutils literal notranslate"><span class="pre">A</span></code>.</p> <p>A mapping which resolves this is as follows:</p> <div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="k">class</span> <span class="nc">A</span><span class="p">(</span><span class="n">Base</span><span class="p">):</span> <span class="n">__tablename__</span> <span class="o">=</span> <span class="s1">'a'</span> <span class="nb">id</span> <span class="o">=</span> <span class="n">Column</span><span class="p">(</span><span class="n">Integer</span><span class="p">,</span> <span class="n">primary_key</span><span class="o">=</span><span class="kc">True</span><span class="p">)</span> <span class="k">class</span> <span class="nc">B</span><span class="p">(</span><span class="n">A</span><span class="p">):</span> <span class="n">__tablename__</span> <span class="o">=</span> <span class="s1">'b'</span> <span class="n">b_id</span> <span class="o">=</span> <span class="n">Column</span><span class="p">(</span><span class="s1">'id'</span><span class="p">,</span> <span class="n">Integer</span><span class="p">,</span> <span class="n">primary_key</span><span class="o">=</span><span class="kc">True</span><span class="p">)</span> <span class="n">a_id</span> <span class="o">=</span> <span class="n">Column</span><span class="p">(</span><span class="n">Integer</span><span class="p">,</span> <span class="n">ForeignKey</span><span class="p">(</span><span class="s1">'a.id'</span><span class="p">))</span></pre></div> </div> <p>Suppose we did want <code class="docutils literal notranslate"><span class="pre">A.id</span></code> and <code class="docutils literal notranslate"><span class="pre">B.id</span></code> to be mirrors of each other, despite the fact that <code class="docutils literal notranslate"><span class="pre">B.a_id</span></code> is where <code class="docutils literal notranslate"><span class="pre">A.id</span></code> is related. We could combine them together using <a class="reference internal" href="../orm/mapping_columns.html#sqlalchemy.orm.column_property" title="sqlalchemy.orm.column_property"><code class="xref py py-func docutils literal notranslate"><span class="pre">column_property()</span></code></a>:</p> <div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="k">class</span> <span class="nc">A</span><span class="p">(</span><span class="n">Base</span><span class="p">):</span> <span class="n">__tablename__</span> <span class="o">=</span> <span class="s1">'a'</span> <span class="nb">id</span> <span class="o">=</span> <span class="n">Column</span><span class="p">(</span><span class="n">Integer</span><span class="p">,</span> <span class="n">primary_key</span><span class="o">=</span><span class="kc">True</span><span class="p">)</span> <span class="k">class</span> <span class="nc">B</span><span class="p">(</span><span class="n">A</span><span class="p">):</span> <span class="n">__tablename__</span> <span class="o">=</span> <span class="s1">'b'</span> <span class="c1"># probably not what you want, but this is a demonstration</span> <span class="nb">id</span> <span class="o">=</span> <span class="n">column_property</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">primary_key</span><span class="o">=</span><span class="kc">True</span><span class="p">),</span> <span class="n">A</span><span class="o">.</span><span class="n">id</span><span class="p">)</span> <span class="n">a_id</span> <span class="o">=</span> <span class="n">Column</span><span class="p">(</span><span class="n">Integer</span><span class="p">,</span> <span class="n">ForeignKey</span><span class="p">(</span><span class="s1">'a.id'</span><span class="p">))</span></pre></div> </div> </div> <div class="section" id="i-m-using-declarative-and-setting-primaryjoin-secondaryjoin-using-an-and-or-or-and-i-am-getting-an-error-message-about-foreign-keys"> <h2>I’m using Declarative and setting primaryjoin/secondaryjoin using an <code class="docutils literal notranslate"><span class="pre">and_()</span></code> or <code class="docutils literal notranslate"><span class="pre">or_()</span></code>, and I am getting an error message about foreign keys.<a class="headerlink" href="#i-m-using-declarative-and-setting-primaryjoin-secondaryjoin-using-an-and-or-or-and-i-am-getting-an-error-message-about-foreign-keys" title="Permalink to this headline">¶</a></h2> <p>Are you doing this?:</p> <div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="k">class</span> <span class="nc">MyClass</span><span class="p">(</span><span class="n">Base</span><span class="p">):</span> <span class="c1"># ....</span> <span class="n">foo</span> <span class="o">=</span> <span class="n">relationship</span><span class="p">(</span><span class="s2">"Dest"</span><span class="p">,</span> <span class="n">primaryjoin</span><span class="o">=</span><span class="n">and_</span><span class="p">(</span><span class="s2">"MyClass.id==Dest.foo_id"</span><span class="p">,</span> <span class="s2">"MyClass.foo==Dest.bar"</span><span class="p">))</span></pre></div> </div> <p>That’s an <code class="docutils literal notranslate"><span class="pre">and_()</span></code> of two string expressions, which SQLAlchemy cannot apply any mapping towards. Declarative allows <a class="reference internal" href="../orm/relationship_api.html#sqlalchemy.orm.relationship" title="sqlalchemy.orm.relationship"><code class="xref py py-func docutils literal notranslate"><span class="pre">relationship()</span></code></a> arguments to be specified as strings, which are converted into expression objects using <code class="docutils literal notranslate"><span class="pre">eval()</span></code>. But this doesn’t occur inside of an <code class="docutils literal notranslate"><span class="pre">and_()</span></code> expression - it’s a special operation declarative applies only to the <em>entirety</em> of what’s passed to primaryjoin or other arguments as a string:</p> <div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="k">class</span> <span class="nc">MyClass</span><span class="p">(</span><span class="n">Base</span><span class="p">):</span> <span class="c1"># ....</span> <span class="n">foo</span> <span class="o">=</span> <span class="n">relationship</span><span class="p">(</span><span class="s2">"Dest"</span><span class="p">,</span> <span class="n">primaryjoin</span><span class="o">=</span><span class="s2">"and_(MyClass.id==Dest.foo_id, MyClass.foo==Dest.bar)"</span><span class="p">)</span></pre></div> </div> <p>Or if the objects you need are already available, skip the strings:</p> <div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="k">class</span> <span class="nc">MyClass</span><span class="p">(</span><span class="n">Base</span><span class="p">):</span> <span class="c1"># ....</span> <span class="n">foo</span> <span class="o">=</span> <span class="n">relationship</span><span class="p">(</span><span class="n">Dest</span><span class="p">,</span> <span class="n">primaryjoin</span><span class="o">=</span><span class="n">and_</span><span class="p">(</span><span class="n">MyClass</span><span class="o">.</span><span class="n">id</span><span class="o">==</span><span class="n">Dest</span><span class="o">.</span><span class="n">foo_id</span><span class="p">,</span> <span class="n">MyClass</span><span class="o">.</span><span class="n">foo</span><span class="o">==</span><span class="n">Dest</span><span class="o">.</span><span class="n">bar</span><span class="p">))</span></pre></div> </div> <p>The same idea applies to all the other arguments, such as <code class="docutils literal notranslate"><span class="pre">foreign_keys</span></code>:</p> <div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="c1"># wrong !</span> <span class="n">foo</span> <span class="o">=</span> <span class="n">relationship</span><span class="p">(</span><span class="n">Dest</span><span class="p">,</span> <span class="n">foreign_keys</span><span class="o">=</span><span class="p">[</span><span class="s2">"Dest.foo_id"</span><span class="p">,</span> <span class="s2">"Dest.bar_id"</span><span class="p">])</span> <span class="c1"># correct !</span> <span class="n">foo</span> <span class="o">=</span> <span class="n">relationship</span><span class="p">(</span><span class="n">Dest</span><span class="p">,</span> <span class="n">foreign_keys</span><span class="o">=</span><span class="s2">"[Dest.foo_id, Dest.bar_id]"</span><span class="p">)</span> <span class="c1"># also correct !</span> <span class="n">foo</span> <span class="o">=</span> <span class="n">relationship</span><span class="p">(</span><span class="n">Dest</span><span class="p">,</span> <span class="n">foreign_keys</span><span class="o">=</span><span class="p">[</span><span class="n">Dest</span><span class="o">.</span><span class="n">foo_id</span><span class="p">,</span> <span class="n">Dest</span><span class="o">.</span><span class="n">bar_id</span><span class="p">])</span> <span class="c1"># if you're using columns from the class that you're inside of, just use the column objects !</span> <span class="k">class</span> <span class="nc">MyClass</span><span class="p">(</span><span class="n">Base</span><span class="p">):</span> <span class="n">foo_id</span> <span class="o">=</span> <span class="n">Column</span><span class="p">(</span><span class="o">...</span><span class="p">)</span> <span class="n">bar_id</span> <span class="o">=</span> <span class="n">Column</span><span class="p">(</span><span class="o">...</span><span class="p">)</span> <span class="c1"># ...</span> <span class="n">foo</span> <span class="o">=</span> <span class="n">relationship</span><span class="p">(</span><span class="n">Dest</span><span class="p">,</span> <span class="n">foreign_keys</span><span class="o">=</span><span class="p">[</span><span class="n">foo_id</span><span class="p">,</span> <span class="n">bar_id</span><span class="p">])</span></pre></div> </div> </div> <div class="section" id="why-is-order-by-required-with-limit-especially-with-subqueryload"> <span id="faq-subqueryload-limit-sort"></span><h2>Why is <code class="docutils literal notranslate"><span class="pre">ORDER</span> <span class="pre">BY</span></code> required with <code class="docutils literal notranslate"><span class="pre">LIMIT</span></code> (especially with <code class="docutils literal notranslate"><span class="pre">subqueryload()</span></code>)?<a class="headerlink" href="#why-is-order-by-required-with-limit-especially-with-subqueryload" title="Permalink to this headline">¶</a></h2> <p>A relational database can return rows in any arbitrary order, when an explicit ordering is not set. While this ordering very often corresponds to the natural order of rows within a table, this is not the case for all databases and all queries. The consequence of this is that any query that limits rows using <code class="docutils literal notranslate"><span class="pre">LIMIT</span></code> or <code class="docutils literal notranslate"><span class="pre">OFFSET</span></code> should <strong>always</strong> specify an <code class="docutils literal notranslate"><span class="pre">ORDER</span> <span class="pre">BY</span></code>. Otherwise, it is not deterministic which rows will actually be returned.</p> <p>When we use a SQLAlchemy method like <a class="reference internal" href="../orm/query.html#sqlalchemy.orm.query.Query.first" title="sqlalchemy.orm.query.Query.first"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Query.first()</span></code></a>, we are in fact applying a <code class="docutils literal notranslate"><span class="pre">LIMIT</span></code> of one to the query, so without an explicit ordering it is not deterministic what row we actually get back. While we may not notice this for simple queries on databases that usually returns rows in their natural order, it becomes much more of an issue if we also use <a class="reference internal" href="../orm/loading_relationships.html#sqlalchemy.orm.subqueryload" title="sqlalchemy.orm.subqueryload"><code class="xref py py-func docutils literal notranslate"><span class="pre">orm.subqueryload()</span></code></a> to load related collections, and we may not be loading the collections as intended.</p> <p>SQLAlchemy implements <a class="reference internal" href="../orm/loading_relationships.html#sqlalchemy.orm.subqueryload" title="sqlalchemy.orm.subqueryload"><code class="xref py py-func docutils literal notranslate"><span class="pre">orm.subqueryload()</span></code></a> by issuing a separate query, the results of which are matched up to the results from the first query. We see two queries emitted like this:</p> <div class="highlight-python+sql notranslate"><div class="highlight"><pre><span></span><span class="o">>>></span> <span class="n">session</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="n">User</span><span class="p">)</span><span class="o">.</span><span class="n">options</span><span class="p">(</span><span class="n">subqueryload</span><span class="p">(</span><span class="n">User</span><span class="o">.</span><span class="n">addresses</span><span class="p">))</span><span class="o">.</span><span class="n">all</span><span class="p">()</span> <div class='show_sql'>-- the "main" query SELECT users.id AS users_id FROM users </div><div class='show_sql'>-- the "load" query issued by subqueryload SELECT addresses.id AS addresses_id, addresses.user_id AS addresses_user_id, anon_1.users_id AS anon_1_users_id FROM (SELECT users.id AS users_id FROM users) AS anon_1 JOIN addresses ON anon_1.users_id = addresses.user_id ORDER BY anon_1.users_id</div></pre></div> </div> <p>The second query embeds the first query as a source of rows. When the inner query uses <code class="docutils literal notranslate"><span class="pre">OFFSET</span></code> and/or <code class="docutils literal notranslate"><span class="pre">LIMIT</span></code> without ordering, the two queries may not see the same results:</p> <div class="highlight-python+sql notranslate"><div class="highlight"><pre><span></span><span class="o">>>></span> <span class="n">user</span> <span class="o">=</span> <span class="n">session</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="n">User</span><span class="p">)</span><span class="o">.</span><span class="n">options</span><span class="p">(</span><span class="n">subqueryload</span><span class="p">(</span><span class="n">User</span><span class="o">.</span><span class="n">addresses</span><span class="p">))</span><span class="o">.</span><span class="n">first</span><span class="p">()</span> <div class='show_sql'>-- the "main" query SELECT users.id AS users_id FROM users LIMIT 1 </div><div class='show_sql'>-- the "load" query issued by subqueryload SELECT addresses.id AS addresses_id, addresses.user_id AS addresses_user_id, anon_1.users_id AS anon_1_users_id FROM (SELECT users.id AS users_id FROM users LIMIT 1) AS anon_1 JOIN addresses ON anon_1.users_id = addresses.user_id ORDER BY anon_1.users_id</div></pre></div> </div> <p>Depending on database specifics, there is a chance we may get a result like the following for the two queries:</p> <div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="o">--</span> <span class="n">query</span> <span class="c1">#1</span> <span class="o">+--------+</span> <span class="o">|</span><span class="n">users_id</span><span class="o">|</span> <span class="o">+--------+</span> <span class="o">|</span> <span class="mi">1</span><span class="o">|</span> <span class="o">+--------+</span> <span class="o">--</span> <span class="n">query</span> <span class="c1">#2</span> <span class="o">+------------+-----------------+---------------+</span> <span class="o">|</span><span class="n">addresses_id</span><span class="o">|</span><span class="n">addresses_user_id</span><span class="o">|</span><span class="n">anon_1_users_id</span><span class="o">|</span> <span class="o">+------------+-----------------+---------------+</span> <span class="o">|</span> <span class="mi">3</span><span class="o">|</span> <span class="mi">2</span><span class="o">|</span> <span class="mi">2</span><span class="o">|</span> <span class="o">+------------+-----------------+---------------+</span> <span class="o">|</span> <span class="mi">4</span><span class="o">|</span> <span class="mi">2</span><span class="o">|</span> <span class="mi">2</span><span class="o">|</span> <span class="o">+------------+-----------------+---------------+</span></pre></div> </div> <p>Above, we receive two <code class="docutils literal notranslate"><span class="pre">addresses</span></code> rows for <code class="docutils literal notranslate"><span class="pre">user.id</span></code> of 2, and none for 1. We’ve wasted two rows and failed to actually load the collection. This is an insidious error because without looking at the SQL and the results, the ORM will not show that there’s any issue; if we access the <code class="docutils literal notranslate"><span class="pre">addresses</span></code> for the <code class="docutils literal notranslate"><span class="pre">User</span></code> we have, it will emit a lazy load for the collection and we won’t see that anything actually went wrong.</p> <p>The solution to this problem is to always specify a deterministic sort order, so that the main query always returns the same set of rows. This generally means that you should <a class="reference internal" href="../orm/query.html#sqlalchemy.orm.query.Query.order_by" title="sqlalchemy.orm.query.Query.order_by"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Query.order_by()</span></code></a> on a unique column on the table. The primary key is a good choice for this:</p> <div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">session</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="n">User</span><span class="p">)</span><span class="o">.</span><span class="n">options</span><span class="p">(</span><span class="n">subqueryload</span><span class="p">(</span><span class="n">User</span><span class="o">.</span><span class="n">addresses</span><span class="p">))</span><span class="o">.</span><span class="n">order_by</span><span class="p">(</span><span class="n">User</span><span class="o">.</span><span class="n">id</span><span class="p">)</span><span class="o">.</span><span class="n">first</span><span class="p">()</span></pre></div> </div> <p>Note that the <a class="reference internal" href="../orm/loading_relationships.html#sqlalchemy.orm.joinedload" title="sqlalchemy.orm.joinedload"><code class="xref py py-func docutils literal notranslate"><span class="pre">joinedload()</span></code></a> eager loader strategy does not suffer from the same problem because only one query is ever issued, so the load query cannot be different from the main query. Similarly, the <a class="reference internal" href="../orm/loading_relationships.html#sqlalchemy.orm.selectinload" title="sqlalchemy.orm.selectinload"><code class="xref py py-func docutils literal notranslate"><span class="pre">selectinload()</span></code></a> eager loader strategy also does not have this issue as it links its collection loads directly to primary key values just loaded.</p> <div class="admonition seealso"> <p class="admonition-title">See also</p> <p><a class="reference internal" href="../orm/loading_relationships.html#subqueryload-ordering"><span class="std std-ref">The Importance of Ordering</span></a></p> </div> </div> </div> </div> </div> <div id="docs-bottom-navigation" class="docs-navigation-links, withsidebar"> Previous: <a href="sqlexpressions.html" title="previous chapter">SQL Expressions</a> Next: <a href="performance.html" title="next chapter">Performance</a> <div id="docs-copyright"> © <a href="../copyright.html">Copyright</a> 2007-2019, the SQLAlchemy authors and contributors. Created using <a href="http://sphinx.pocoo.org/">Sphinx</a> 2.0.1. </div> </div> </div> <script type="text/javascript"> var DOCUMENTATION_OPTIONS = { URL_ROOT: '../', VERSION: '1.2.19', COLLAPSE_MODINDEX: false, FILE_SUFFIX: '.html' }; </script> <script type="text/javascript" id="documentation_options" data-url_root="../" src="../_static/documentation_options.js"></script> <!-- begin iterate through sphinx environment script_files --> <script type="text/javascript" src="../_static/jquery.js"></script> <script type="text/javascript" src="../_static/underscore.js"></script> <script type="text/javascript" src="../_static/doctools.js"></script> <script type="text/javascript" src="../_static/language_data.js"></script> <!-- end iterate through sphinx environment script_files --> <script type="text/javascript" src="../_static/detectmobile.js"></script> <script type="text/javascript" src="../_static/init.js"></script> </body> </html>