133
133
<dl class="glossary docutils">
134
<dt id="term-annotations">annotations</dt>
135
<dd><p class="first">Annotations are a concept used internally by SQLAlchemy in order to store
136
additional information along with <a class="reference internal" href="core/expression_api.html#sqlalchemy.sql.expression.ClauseElement" title="sqlalchemy.sql.expression.ClauseElement"><tt class="xref py py-class docutils literal"><span class="pre">ClauseElement</span></tt></a> objects. A Python
137
dictionary is associated with a copy of the object, which contains key/value
138
pairs significant to various internal systems, mostly within the ORM:</p>
139
<div class="highlight-python"><div class="highlight"><pre><span class="n">some_column</span> <span class="o">=</span> <span class="n">Column</span><span class="p">(</span><span class="s">'some_column'</span><span class="p">,</span> <span class="n">Integer</span><span class="p">)</span>
140
<span class="n">some_column_annotated</span> <span class="o">=</span> <span class="n">some_column</span><span class="o">.</span><span class="n">_annotate</span><span class="p">({</span><span class="s">"entity"</span><span class="p">:</span> <span class="n">User</span><span class="p">})</span></pre></div>
142
<p class="last">The annotation system differs from the public dictionary <a class="reference internal" href="core/schema.html#sqlalchemy.schema.Column.info" title="sqlalchemy.schema.Column.info"><tt class="xref py py-attr docutils literal"><span class="pre">Column.info</span></tt></a>
143
in that the above annotation operation creates a <em>copy</em> of the new <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>,
144
rather than considering all annotation values to be part of a single
145
unit. The ORM creates copies of expression objects in order to
146
apply annotations that are specific to their context, such as to differentiate
147
columns that should render themselves as relative to a joined-inheritance
148
entity versus those which should render relative to their immediate parent
149
table alone, as well as to differentiate columns within the “join condition”
150
of a relationship where the column in some cases needs to be expressed
151
in terms of one particular table alias or another, based on its position
152
within the join expression.</p>
154
<dt id="term-columns-clause">columns clause</dt>
155
<dd><p class="first">The portion of the <tt class="docutils literal"><span class="pre">SELECT</span></tt> statement which enumerates the
156
SQL expressions to be returned in the result set. The expressions
157
follow the <tt class="docutils literal"><span class="pre">SELECT</span></tt> keyword directly and are a comma-separated
158
list of individual expressions.</p>
160
<div class="highlight-sql"><div class="highlight"><pre><span class="k">SELECT</span> <span class="n">user_account</span><span class="p">.</span><span class="n">name</span><span class="p">,</span> <span class="n">user_account</span><span class="p">.</span><span class="n">email</span>
161
<span class="k">FROM</span> <span class="n">user_account</span> <span class="k">WHERE</span> <span class="n">user_account</span><span class="p">.</span><span class="n">name</span> <span class="o">=</span> <span class="s1">'fred'</span></pre></div>
163
<p class="last">Above, the list of columns <tt class="docutils literal"><span class="pre">user_acount.name</span></tt>,
164
<tt class="docutils literal"><span class="pre">user_account.email</span></tt> is the columns clause of the <tt class="docutils literal"><span class="pre">SELECT</span></tt>.</p>
166
<dt id="term-correlates"><span id="term-correlated-subquery"></span><span id="term-correlated-subqueries"></span>correlates<br />correlated subquery<br />correlated subqueries</dt>
167
<dd><p class="first">A <a class="reference internal" href="#term-subquery"><em class="xref std std-term">subquery</em></a> is correlated if it depends on data in the
168
enclosing <tt class="docutils literal"><span class="pre">SELECT</span></tt>.</p>
169
<p>Below, a subquery selects the aggregate value <tt class="docutils literal"><span class="pre">MIN(a.id)</span></tt>
170
from the <tt class="docutils literal"><span class="pre">email_address</span></tt> table, such that
171
it will be invoked for each value of <tt class="docutils literal"><span class="pre">user_account.id</span></tt>, correlating
172
the value of this column against the <tt class="docutils literal"><span class="pre">email_address.user_account_id</span></tt>
174
<div class="highlight-sql"><div class="highlight"><pre><span class="k">SELECT</span> <span class="n">user_account</span><span class="p">.</span><span class="n">name</span><span class="p">,</span> <span class="n">email_address</span><span class="p">.</span><span class="n">email</span>
175
<span class="k">FROM</span> <span class="n">user_account</span>
176
<span class="k">JOIN</span> <span class="n">email_address</span> <span class="k">ON</span> <span class="n">user_account</span><span class="p">.</span><span class="n">id</span><span class="o">=</span><span class="n">email_address</span><span class="p">.</span><span class="n">user_account_id</span>
177
<span class="k">WHERE</span> <span class="n">email_address</span><span class="p">.</span><span class="n">id</span> <span class="o">=</span> <span class="p">(</span>
178
<span class="k">SELECT</span> <span class="k">MIN</span><span class="p">(</span><span class="n">a</span><span class="p">.</span><span class="n">id</span><span class="p">)</span> <span class="k">FROM</span> <span class="n">email_address</span> <span class="k">AS</span> <span class="n">a</span>
179
<span class="k">WHERE</span> <span class="n">a</span><span class="p">.</span><span class="n">user_account_id</span><span class="o">=</span><span class="n">user_account</span><span class="p">.</span><span class="n">id</span>
180
<span class="p">)</span></pre></div>
182
<p>The above subquery refers to the <tt class="docutils literal"><span class="pre">user_account</span></tt> table, which is not itself
183
in the <tt class="docutils literal"><span class="pre">FROM</span></tt> clause of this nested query. Instead, the <tt class="docutils literal"><span class="pre">user_account</span></tt>
184
table is recieved from the enclosing query, where each row selected from
185
<tt class="docutils literal"><span class="pre">user_account</span></tt> results in a distinct execution of the subquery.</p>
186
<p>A correlated subquery is in most cases present in the <a class="reference internal" href="#term-where-clause"><em class="xref std std-term">WHERE clause</em></a>
187
or <a class="reference internal" href="#term-columns-clause"><em class="xref std std-term">columns clause</em></a> of the immediately enclosing <tt class="docutils literal"><span class="pre">SELECT</span></tt>
188
statement, as well as in the ORDER BY or HAVING clause.</p>
189
<p>In less common cases, a correlated subquery may be present in the
190
<a class="reference internal" href="#term-from-clause"><em class="xref std std-term">FROM clause</em></a> of an enclosing <tt class="docutils literal"><span class="pre">SELECT</span></tt>; in these cases the
191
correlation is typically due to the enclosing <tt class="docutils literal"><span class="pre">SELECT</span></tt> itself being
192
enclosed in the WHERE,
193
ORDER BY, columns or HAVING clause of another <tt class="docutils literal"><span class="pre">SELECT</span></tt>, such as:</p>
194
<div class="highlight-sql"><div class="highlight"><pre><span class="k">SELECT</span> <span class="n">parent</span><span class="p">.</span><span class="n">id</span> <span class="k">FROM</span> <span class="n">parent</span>
195
<span class="k">WHERE</span> <span class="k">EXISTS</span> <span class="p">(</span>
196
<span class="k">SELECT</span> <span class="o">*</span> <span class="k">FROM</span> <span class="p">(</span>
197
<span class="k">SELECT</span> <span class="n">child</span><span class="p">.</span><span class="n">id</span> <span class="k">AS</span> <span class="n">id</span><span class="p">,</span> <span class="n">child</span><span class="p">.</span><span class="n">parent_id</span> <span class="k">AS</span> <span class="n">parent_id</span><span class="p">,</span> <span class="n">child</span><span class="p">.</span><span class="n">pos</span> <span class="k">AS</span> <span class="n">pos</span>
198
<span class="k">FROM</span> <span class="n">child</span>
199
<span class="k">WHERE</span> <span class="n">child</span><span class="p">.</span><span class="n">parent_id</span> <span class="o">=</span> <span class="n">parent</span><span class="p">.</span><span class="n">id</span> <span class="k">ORDER</span> <span class="k">BY</span> <span class="n">child</span><span class="p">.</span><span class="n">pos</span>
200
<span class="k">LIMIT</span> <span class="mi">3</span><span class="p">)</span>
201
<span class="k">WHERE</span> <span class="n">id</span> <span class="o">=</span> <span class="mi">7</span><span class="p">)</span></pre></div>
203
<p class="last">Correlation from one <tt class="docutils literal"><span class="pre">SELECT</span></tt> directly to one which encloses the correlated
204
query via its <tt class="docutils literal"><span class="pre">FROM</span></tt>
205
clause is not possible, because the correlation can only proceed once the
206
original source rows from the enclosing statement’s FROM clause are available.</p>
208
<dt id="term-dbapi">DBAPI</dt>
209
<dd><p class="first">DBAPI is shorthand for the phrase “Python Database API
210
Specification”. This is a widely used specification
211
within Python to define common usage patterns for all
212
database connection packages. The DBAPI is a “low level”
213
API which is typically the lowest level system used
214
in a Python application to talk to a database. SQLAlchemy’s
215
<em class="xref std std-term">dialect</em> system is constructed around the
216
operation of the DBAPI, providing individual dialect
217
classes which service a specific DBAPI on top of a
218
specific database engine; for example, the <a class="reference internal" href="core/engines.html#sqlalchemy.create_engine" title="sqlalchemy.create_engine"><tt class="xref py py-func docutils literal"><span class="pre">create_engine()</span></tt></a>
219
URL <tt class="docutils literal"><span class="pre">postgresql+psycopg2://@localhost/test</span></tt>
220
refers to the <a class="reference internal" href="dialects/postgresql.html#module-sqlalchemy.dialects.postgresql.psycopg2" title="sqlalchemy.dialects.postgresql.psycopg2"><tt class="xref py py-mod docutils literal"><span class="pre">psycopg2</span></tt></a>
221
DBAPI/dialect combination, whereas the URL <tt class="docutils literal"><span class="pre">mysql+mysqldb://@localhost/test</span></tt>
222
refers to the <a class="reference internal" href="dialects/mysql.html#module-sqlalchemy.dialects.mysql.mysqldb" title="sqlalchemy.dialects.mysql.mysqldb"><tt class="xref py py-mod docutils literal"><span class="pre">MySQL</span> <span class="pre">for</span> <span class="pre">Python</span></tt></a>
223
DBAPI DBAPI/dialect combination.</p>
224
<div class="admonition-see-also last admonition seealso">
225
<p class="first admonition-title">See also</p>
226
<p class="last"><a class="reference external" href="http://www.python.org/dev/peps/pep-0249/">PEP 249 - Python Database API Specification v2.0</a></p>
229
<dt id="term-descriptor"><span id="term-descriptors"></span>descriptor<br />descriptors</dt>
230
<dd><p class="first">In Python, a descriptor is an object attribute with “binding behavior”, one whose attribute access has been overridden by methods in the <a class="reference external" href="http://docs.python.org/howto/descriptor.html">descriptor protocol</a>.
231
Those methods are __get__(), __set__(), and __delete__(). If any of those methods are defined
232
for an object, it is said to be a descriptor.</p>
233
<p>In SQLAlchemy, descriptors are used heavily in order to provide attribute behavior
234
on mapped classes. When a class is mapped as such:</p>
235
<div class="highlight-python"><div class="highlight"><pre><span class="k">class</span> <span class="nc">MyClass</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">'foo'</span>
238
<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>
239
<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></pre></div>
241
<p>The <tt class="docutils literal"><span class="pre">MyClass</span></tt> class will be <a class="reference internal" href="#term-mapped"><em class="xref std std-term">mapped</em></a> when its definition
242
is complete, at which point the <tt class="docutils literal"><span class="pre">id</span></tt> and <tt class="docutils literal"><span class="pre">data</span></tt> attributes,
243
starting out as <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, will be replaced
244
by the <a class="reference internal" href="#term-instrumentation"><em class="xref std std-term">instrumentation</em></a> system with instances
245
of <a class="reference internal" href="orm/internals.html#sqlalchemy.orm.attributes.InstrumentedAttribute" title="sqlalchemy.orm.attributes.InstrumentedAttribute"><tt class="xref py py-class docutils literal"><span class="pre">InstrumentedAttribute</span></tt></a>, which are descriptors that
246
provide the above mentioned <tt class="docutils literal"><span class="pre">__get__()</span></tt>, <tt class="docutils literal"><span class="pre">__set__()</span></tt> and
247
<tt class="docutils literal"><span class="pre">__delete__()</span></tt> methods. The <a class="reference internal" href="orm/internals.html#sqlalchemy.orm.attributes.InstrumentedAttribute" title="sqlalchemy.orm.attributes.InstrumentedAttribute"><tt class="xref py py-class docutils literal"><span class="pre">InstrumentedAttribute</span></tt></a>
248
will generate a SQL expression when used at the class level:</p>
249
<div class="highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="k">print</span> <span class="n">MyClass</span><span class="o">.</span><span class="n">data</span> <span class="o">==</span> <span class="mi">5</span>
250
<span class="go">data = :data_1</span></pre></div>
252
<p>and at the instance level, keeps track of changes to values,
253
and also <a class="reference internal" href="#term-lazy-loads"><em class="xref std std-term">lazy loads</em></a> unloaded attributes
254
from the database:</p>
255
<div class="last highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="n">m1</span> <span class="o">=</span> <span class="n">MyClass</span><span class="p">()</span>
256
<span class="gp">>>> </span><span class="n">m1</span><span class="o">.</span><span class="n">id</span> <span class="o">=</span> <span class="mi">5</span>
257
<span class="gp">>>> </span><span class="n">m1</span><span class="o">.</span><span class="n">data</span> <span class="o">=</span> <span class="s">"some data"</span>
259
<span class="gp">>>> </span><span class="kn">from</span> <span class="nn">sqlalchemy</span> <span class="kn">import</span> <span class="n">inspect</span>
260
<span class="gp">>>> </span><span class="n">inspect</span><span class="p">(</span><span class="n">m1</span><span class="p">)</span><span class="o">.</span><span class="n">attrs</span><span class="o">.</span><span class="n">data</span><span class="o">.</span><span class="n">history</span><span class="o">.</span><span class="n">added</span>
261
<span class="go">"some data"</span></pre></div>
264
<dt id="term-discriminator">discriminator</dt>
265
<dd><p class="first">A result-set column which is used during <a class="reference internal" href="#term-polymorphic"><em class="xref std std-term">polymorphic</em></a> loading
266
to determine what kind of mapped class should be applied to a particular
267
incoming result row. In SQLAlchemy, the classes are always part
268
of a hierarchy mapping using inheritance mapping.</p>
269
<div class="admonition-see-also last admonition seealso">
270
<p class="first admonition-title">See also</p>
271
<p class="last"><a class="reference internal" href="orm/inheritance.html"><em>Mapping Class Inheritance Hierarchies</em></a></p>
274
<dt id="term-from-clause">FROM clause</dt>
275
<dd><p class="first">The portion of the <tt class="docutils literal"><span class="pre">SELECT</span></tt> statement which incicates the initial
277
<p>A simple <tt class="docutils literal"><span class="pre">SELECT</span></tt> will feature one or more table names in its
278
FROM clause. Multiple sources are separated by a comma:</p>
279
<div class="highlight-sql"><div class="highlight"><pre><span class="k">SELECT</span> <span class="k">user</span><span class="p">.</span><span class="n">name</span><span class="p">,</span> <span class="n">address</span><span class="p">.</span><span class="n">email_address</span>
280
<span class="k">FROM</span> <span class="k">user</span><span class="p">,</span> <span class="n">address</span>
281
<span class="k">WHERE</span> <span class="k">user</span><span class="p">.</span><span class="n">id</span><span class="o">=</span><span class="n">address</span><span class="p">.</span><span class="n">user_id</span></pre></div>
283
<p>The FROM clause is also where explicit joins are specified. We can
284
rewrite the above <tt class="docutils literal"><span class="pre">SELECT</span></tt> using a single <tt class="docutils literal"><span class="pre">FROM</span></tt> element which consists
285
of a <tt class="docutils literal"><span class="pre">JOIN</span></tt> of the two tables:</p>
286
<div class="last highlight-sql"><div class="highlight"><pre><span class="k">SELECT</span> <span class="k">user</span><span class="p">.</span><span class="n">name</span><span class="p">,</span> <span class="n">address</span><span class="p">.</span><span class="n">email_address</span>
287
<span class="k">FROM</span> <span class="k">user</span> <span class="k">JOIN</span> <span class="n">address</span> <span class="k">ON</span> <span class="k">user</span><span class="p">.</span><span class="n">id</span><span class="o">=</span><span class="n">address</span><span class="p">.</span><span class="n">user_id</span></pre></div>
290
<dt id="term-generative">generative</dt>
291
<dd>A term that SQLAlchemy uses to refer what’s normally known
292
as <a class="reference internal" href="#term-method-chaining"><em class="xref std std-term">method chaining</em></a>; see that term for details.</dd>
293
<dt id="term-instrumentation"><span id="term-instrumented"></span>instrumentation<br />instrumented</dt>
294
<dd>Instrumentation refers to the process of augmenting the functionality
295
and attribute set of a particular class. Ideally, the
296
behavior of the class should remain close to a regular
297
class, except that additional behviors and features are
298
made available. The SQLAlchemy <a class="reference internal" href="#term-mapping"><em class="xref std std-term">mapping</em></a> process,
299
among other things, adds database-enabled <a class="reference internal" href="#term-descriptors"><em class="xref std std-term">descriptors</em></a>
301
class which each represent a particular database column
302
or relationship to a related class.</dd>
303
<dt id="term-lazy-load"><span id="term-lazy-loads"></span>lazy load<br />lazy loads</dt>
304
<dd><p class="first">In object relational mapping, a “lazy load” refers to an
305
attribute that does not contain its database-side value
306
for some period of time, typically when the object is
307
first loaded. Instead, the attribute receives a
308
<em>memoization</em> that causes it to go out to the database
309
and load its data when it’s first used. Using this pattern,
310
the complexity and time spent within object fetches can
311
sometimes be reduced, in that
312
attributes for related tables don’t need to be addressed
314
<div class="admonition-see-also last admonition seealso">
315
<p class="first admonition-title">See also</p>
316
<p><a class="reference external" href="http://martinfowler.com/eaaCatalog/lazyLoad.html">Lazy Load (on Martin Fowler)</a></p>
317
<p><a class="reference internal" href="#term-n-plus-one-problem"><em class="xref std std-term">N plus one problem</em></a></p>
318
<p class="last"><a class="reference internal" href="orm/loading.html"><em>Relationship Loading Techniques</em></a></p>
321
<dt id="term-mapping"><span id="term-mapped"></span>mapping<br />mapped</dt>
322
<dd>We say a class is “mapped” when it has been passed through the
323
<a class="reference internal" href="orm/mapper_config.html#sqlalchemy.orm.mapper" title="sqlalchemy.orm.mapper"><tt class="xref py py-func docutils literal"><span class="pre">orm.mapper()</span></tt></a> function. This process associates the
324
class with a database table or other <em class="xref std std-term">selectable</em>
325
construct, so that instances of it can be persisted
326
using a <a class="reference internal" href="orm/session.html#sqlalchemy.orm.session.Session" title="sqlalchemy.orm.session.Session"><tt class="xref py py-class docutils literal"><span class="pre">Session</span></tt></a> as well as loaded using a
327
<a class="reference internal" href="orm/query.html#sqlalchemy.orm.query.Query" title="sqlalchemy.orm.query.Query"><tt class="xref py py-class docutils literal"><span class="pre">Query</span></tt></a>.</dd>
328
<dt id="term-method-chaining">method chaining</dt>
329
<dd><p class="first">An object-oriented technique whereby the state of an object
330
is constructed by calling methods on the object. The
331
object features any number of methods, each of which return
332
a new object (or in some cases the same object) with
333
additional state added to the object.</p>
334
<p>The two SQLAlchemy objects that make the most use of
335
method chaining are the <a class="reference internal" href="core/expression_api.html#sqlalchemy.sql.expression.Select" title="sqlalchemy.sql.expression.Select"><tt class="xref py py-class docutils literal"><span class="pre">Select</span></tt></a>
336
object and the <a class="reference internal" href="orm/query.html#sqlalchemy.orm.query.Query" title="sqlalchemy.orm.query.Query"><tt class="xref py py-class docutils literal"><span class="pre">Query</span></tt></a> object.
337
For example, a <a class="reference internal" href="core/expression_api.html#sqlalchemy.sql.expression.Select" title="sqlalchemy.sql.expression.Select"><tt class="xref py py-class docutils literal"><span class="pre">Select</span></tt></a> object can
338
be assigned two expressions to its WHERE clause as well
339
as an ORDER BY clause by calling upon the <a class="reference internal" href="core/expression_api.html#sqlalchemy.sql.expression.Select.where" title="sqlalchemy.sql.expression.Select.where"><tt class="xref py py-meth docutils literal"><span class="pre">where()</span></tt></a>
340
and <a class="reference internal" href="core/expression_api.html#sqlalchemy.sql.expression.Select.order_by" title="sqlalchemy.sql.expression.Select.order_by"><tt class="xref py py-meth docutils literal"><span class="pre">order_by()</span></tt></a> methods:</p>
341
<div class="highlight-python"><pre>stmt = select([user.c.name]).\
342
where(user.c.id > 5).\
343
where(user.c.name.like('e%').\
344
order_by(user.c.name)</pre>
346
<p>Each method call above returns a copy of the original
347
<a class="reference internal" href="core/expression_api.html#sqlalchemy.sql.expression.Select" title="sqlalchemy.sql.expression.Select"><tt class="xref py py-class docutils literal"><span class="pre">Select</span></tt></a> object with additional qualifiers
349
<div class="admonition-see-also last admonition seealso">
350
<p class="first admonition-title">See also</p>
351
<p class="last"><a class="reference internal" href="#term-generative"><em class="xref std std-term">generative</em></a></p>
354
<dt id="term-n-plus-one-problem">N plus one problem</dt>
355
<dd><p class="first">The N plus one problem is a common side effect of the
356
<a class="reference internal" href="#term-lazy-load"><em class="xref std std-term">lazy load</em></a> pattern, whereby an application wishes
357
to iterate through a related attribute or collection on
358
each member of a result set of objects, where that
359
attribute or collection is set to be loaded via the lazy
360
load pattern. The net result is that a SELECT statement
361
is emitted to load the initial result set of parent objects;
362
then, as the application iterates through each member,
363
an additional SELECT statement is emitted for each member
364
in order to load the related attribute or collection for
365
that member. The end result is that for a result set of
366
N parent objects, there will be N + 1 SELECT statements emitted.</p>
367
<p>The N plus one problem is alleviated using <em class="xref std std-term">eager loading</em>.</p>
368
<div class="admonition-see-also last admonition seealso">
369
<p class="first admonition-title">See also</p>
370
<p class="last"><a class="reference internal" href="orm/loading.html"><em>Relationship Loading Techniques</em></a></p>
373
<dt id="term-polymorphic"><span id="term-polymorphically"></span>polymorphic<br />polymorphically</dt>
374
<dd><p class="first">Refers to a function that handles several types at once. In SQLAlchemy,
375
the term is usually applied to the concept of an ORM mapped class
376
whereby a query operation will return different subclasses
377
based on information in the result set, typically by checking the
378
value of a particular column in the result known as the <a class="reference internal" href="#term-discriminator"><em class="xref std std-term">discriminator</em></a>.</p>
379
<p class="last">Polymorphic loading in SQLAlchemy implies that a one or a
380
combination of three different schemes are used to map a hierarchy
381
of classes; “joined”, “single”, and “concrete”. The section
382
<a class="reference internal" href="orm/inheritance.html"><em>Mapping Class Inheritance Hierarchies</em></a> describes inheritance mapping fully.</p>
134
384
<dt id="term-release"><span id="term-releases"></span><span id="term-released"></span>release<br />releases<br />released</dt>
135
<dd><p class="first">This term refers to when an operation terminates some state which
136
corresponds to a service of some kind. Specifically within
137
SQLAlchemy, it usually refers to a reference to a database connection,
138
and typically a transaction associated with that connection.
139
When we say “the operation releases transactional resources”,
140
it means basically that we have a <a class="reference internal" href="core/connections.html#sqlalchemy.engine.base.Connection" title="sqlalchemy.engine.base.Connection"><tt class="xref py py-class docutils literal"><span class="pre">Connection</span></tt></a> object
141
and we are calling the <a class="reference internal" href="core/connections.html#sqlalchemy.engine.base.Connection.close" title="sqlalchemy.engine.base.Connection.close"><tt class="xref py py-meth docutils literal"><span class="pre">Connection.close()</span></tt></a> method, which has
142
the effect of the underlying DBAPI connection being returned
143
to the connection pool. The connection pool, when it receives
144
a connection for return, unconditionally calls the <tt class="docutils literal"><span class="pre">rollback()</span></tt>
145
method of the DBAPI connection, so that any locks or data snapshots within
146
that connection are removed. Then, the connection is either
147
stored locally in memory, still connected but not in a transaction,
148
for subsequent reuse by another operation, or it is closed
149
immediately, depending on the configuration and current
150
state of the connection pool.</p>
385
<dd><p class="first">In the context of SQLAlchemy, the term “released”
386
refers to the process of ending the usage of a particular
387
database connection. SQLAlchemy features the usage
388
of connection pools, which allows configurability as to
389
the lifespan of database connections. When using a pooled
390
connection, the process of “closing” it, i.e. invoking
391
a statement like <tt class="docutils literal"><span class="pre">connection.close()</span></tt>, may have the effect
392
of the connection being returned to an existing pool,
393
or it may have the effect of actually shutting down the
394
underlying TCP/IP connection referred to by that connection -
395
which one takes place depends on configuration as well
396
as the current state of the pool. So we used the term
397
<em>released</em> instead, to mean “do whatever it is you do
398
with connections when we’re done using them”.</p>
399
<p>The term will sometimes be used in the phrase, “release
400
transactional resources”, to indicate more explicitly that
401
what we are actually “releasing” is any transactional
402
state which as accumulated upon the connection. In most
403
situations, the proces of selecting from tables, emitting
404
updates, etc. acquires <em class="xref std std-term">isolated</em> state upon
405
that connection as well as potential row or table locks.
406
This state is all local to a particular transaction
407
on the connection, and is released when we emit a rollback.
408
An important feature of the connection pool is that when
409
we return a connection to the pool, the <tt class="docutils literal"><span class="pre">connection.rollback()</span></tt>
410
method of the DBAPI is called as well, so that as the
411
connection is set up to be used again, it’s in a “clean”
412
state with no references held to the previous series
151
414
<div class="admonition-see-also last admonition seealso">
152
415
<p class="first admonition-title">See also</p>
153
416
<p class="last"><a class="reference internal" href="core/pooling.html"><em>Connection Pooling</em></a></p>
419
<dt id="term-subquery">subquery</dt>
420
<dd><p class="first">Refers to a <tt class="docutils literal"><span class="pre">SELECT</span></tt> statement that is embedded within an enclosing
421
<tt class="docutils literal"><span class="pre">SELECT</span></tt>.</p>
422
<p>A subquery comes in two general flavors, one known as a “scalar select”
423
which specifically must return exactly one row and one column, and the
424
other form which acts as a “derived table” and serves as a source of
425
rows for the FROM clause of another select. A scalar select is eligble
426
to be placed in the <a class="reference internal" href="#term-where-clause"><em class="xref std std-term">WHERE clause</em></a>, <a class="reference internal" href="#term-columns-clause"><em class="xref std std-term">columns clause</em></a>,
427
ORDER BY clause or HAVING clause of the enclosing select, whereas the
428
derived table form is eligible to be placed in the FROM clause of the
429
enclosing <tt class="docutils literal"><span class="pre">SELECT</span></tt>.</p>
431
<ol class="last arabic">
432
<li><p class="first">a scalar subquery placed in the <a class="reference internal" href="#term-columns-clause"><em class="xref std std-term">columns clause</em></a> of an enclosing
433
<tt class="docutils literal"><span class="pre">SELECT</span></tt>. The subquery in this example is a <a class="reference internal" href="#term-correlated-subquery"><em class="xref std std-term">correlated subquery</em></a> because part
434
of the rows which it selects from are given via the enclosing statement.</p>
435
<div class="highlight-sql"><div class="highlight"><pre><span class="k">SELECT</span> <span class="n">id</span><span class="p">,</span> <span class="p">(</span><span class="k">SELECT</span> <span class="n">name</span> <span class="k">FROM</span> <span class="n">address</span> <span class="k">WHERE</span> <span class="n">address</span><span class="p">.</span><span class="n">user_id</span><span class="o">=</span><span class="k">user</span><span class="p">.</span><span class="n">id</span><span class="p">)</span>
436
<span class="k">FROM</span> <span class="k">user</span></pre></div>
439
<li><p class="first">a scalar subquery placed in the <a class="reference internal" href="#term-where-clause"><em class="xref std std-term">WHERE clause</em></a> of an enclosing
440
<tt class="docutils literal"><span class="pre">SELECT</span></tt>. This subquery in this example is not correlated as it selects a fixed result.</p>
441
<div class="highlight-sql"><div class="highlight"><pre><span class="k">SELECT</span> <span class="n">id</span><span class="p">,</span> <span class="n">name</span> <span class="k">FROM</span> <span class="k">user</span>
442
<span class="k">WHERE</span> <span class="n">status</span><span class="o">=</span><span class="p">(</span><span class="k">SELECT</span> <span class="n">status_id</span> <span class="k">FROM</span> <span class="n">status_code</span> <span class="k">WHERE</span> <span class="n">code</span><span class="o">=</span><span class="s1">'C'</span><span class="p">)</span></pre></div>
445
<li><p class="first">a derived table subquery placed in the <a class="reference internal" href="#term-from-clause"><em class="xref std std-term">FROM clause</em></a> of an enclosing
446
<tt class="docutils literal"><span class="pre">SELECT</span></tt>. Such a subquery is almost always given an alias name.</p>
447
<div class="highlight-sql"><div class="highlight"><pre><span class="k">SELECT</span> <span class="k">user</span><span class="p">.</span><span class="n">id</span><span class="p">,</span> <span class="k">user</span><span class="p">.</span><span class="n">name</span><span class="p">,</span> <span class="n">ad_subq</span><span class="p">.</span><span class="n">email_address</span>
448
<span class="k">FROM</span>
449
<span class="k">user</span> <span class="k">JOIN</span>
450
<span class="p">(</span><span class="k">select</span> <span class="n">user_id</span><span class="p">,</span> <span class="n">email_address</span> <span class="k">FROM</span> <span class="n">address</span> <span class="k">WHERE</span> <span class="n">address_type</span><span class="o">=</span><span class="s1">'Q'</span><span class="p">)</span> <span class="k">AS</span> <span class="n">ad_subq</span>
451
<span class="k">ON</span> <span class="k">user</span><span class="p">.</span><span class="n">id</span> <span class="o">=</span> <span class="n">ad_subq</span><span class="p">.</span><span class="n">user_id</span></pre></div>
456
<dt id="term-unit-of-work">unit of work</dt>
457
<dd><p class="first">This pattern is where the system transparently keeps
458
track of changes to objects and periodically flushes all those
459
pending changes out to the database. SQLAlchemy’s Session
460
implements this pattern fully in a manner similar to that of
462
<div class="admonition-see-also last admonition seealso">
463
<p class="first admonition-title">See also</p>
464
<p><a class="reference external" href="http://martinfowler.com/eaaCatalog/unitOfWork.html">Unit of Work by Martin Fowler</a></p>
465
<p class="last"><a class="reference internal" href="orm/session.html"><em>Using the Session</em></a></p>
468
<dt id="term-where-clause">WHERE clause</dt>
469
<dd><p class="first">The portion of the <tt class="docutils literal"><span class="pre">SELECT</span></tt> statement which indicates criteria
470
by which rows should be filtered. It is a single SQL expression
471
which follows the keyword <tt class="docutils literal"><span class="pre">WHERE</span></tt>.</p>
472
<div class="highlight-sql"><div class="highlight"><pre><span class="k">SELECT</span> <span class="n">user_account</span><span class="p">.</span><span class="n">name</span><span class="p">,</span> <span class="n">user_account</span><span class="p">.</span><span class="n">email</span>
473
<span class="k">FROM</span> <span class="n">user_account</span>
474
<span class="k">WHERE</span> <span class="n">user_account</span><span class="p">.</span><span class="n">name</span> <span class="o">=</span> <span class="s1">'fred'</span> <span class="k">AND</span> <span class="n">user_account</span><span class="p">.</span><span class="n">status</span> <span class="o">=</span> <span class="s1">'E'</span></pre></div>
476
<p class="last">Above, the phrase <tt class="docutils literal"><span class="pre">WHERE</span> <span class="pre">user_account.name</span> <span class="pre">=</span> <span class="pre">'fred'</span> <span class="pre">AND</span> <span class="pre">user_account.status</span> <span class="pre">=</span> <span class="pre">'E'</span></tt>
477
comprises the WHERE clause of the <tt class="docutils literal"><span class="pre">SELECT</span></tt>.</p>