133
133
<dl class="glossary docutils">
134
<dt id="term-acid"><span id="term-acid-model"></span>ACID<br />ACID model</dt>
135
<dd><p class="first">An acronym for “Atomicity, Consistency, Isolation,
136
Durability”; a set of properties that guarantee that
137
database transactions are processed reliably.
139
<div class="last admonition seealso">
140
<p class="first admonition-title">See also</p>
141
<p><a class="reference internal" href="#term-atomicity"><em class="xref std std-term">atomicity</em></a></p>
142
<p><a class="reference internal" href="#term-consistency"><em class="xref std std-term">consistency</em></a></p>
143
<p><a class="reference internal" href="#term-isolation"><em class="xref std std-term">isolation</em></a></p>
144
<p><a class="reference internal" href="#term-durability"><em class="xref std std-term">durability</em></a></p>
145
<p class="last"><a class="reference external" href="http://en.wikipedia.org/wiki/ACID_Model">http://en.wikipedia.org/wiki/ACID_Model</a></p>
134
148
<dt id="term-annotations">annotations</dt>
135
149
<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
150
additional information along with <a class="reference internal" href="core/sqlelement.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
151
dictionary is associated with a copy of the object, which contains key/value
138
152
pairs significant to various internal systems, mostly within the ORM:</p>
139
153
<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
154
<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>,
156
<p class="last">The annotation system differs from the public dictionary <a class="reference internal" href="core/metadata.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>
157
in that the above annotation operation creates a <em>copy</em> of the new <a class="reference internal" href="core/metadata.html#sqlalchemy.schema.Column" title="sqlalchemy.schema.Column"><tt class="xref py py-class docutils literal"><span class="pre">Column</span></tt></a>,
144
158
rather than considering all annotation values to be part of a single
145
159
unit. The ORM creates copies of expression objects in order to
146
160
apply annotations that are specific to their context, such as to differentiate
151
165
in terms of one particular table alias or another, based on its position
152
166
within the join expression.</p>
168
<dt id="term-atomicity">atomicity</dt>
169
<dd><p class="first">Atomicity is one of the components of the <a class="reference internal" href="#term-acid"><em class="xref std std-term">ACID</em></a> model,
170
and requires that each transaction is “all or nothing”:
171
if one part of the transaction fails, the entire transaction
172
fails, and the database state is left unchanged. An atomic
173
system must guarantee atomicity in each and every situation,
174
including power failures, errors, and crashes.
176
<div class="last admonition seealso">
177
<p class="first admonition-title">See also</p>
178
<p><a class="reference internal" href="#term-acid"><em class="xref std std-term">ACID</em></a></p>
179
<p class="last"><a class="reference external" href="http://en.wikipedia.org/wiki/Atomicity_(database_systems">http://en.wikipedia.org/wiki/Atomicity_(database_systems</a>)</p>
154
182
<dt id="term-columns-clause">columns clause</dt>
155
183
<dd><p class="first">The portion of the <tt class="docutils literal"><span class="pre">SELECT</span></tt> statement which enumerates the
156
184
SQL expressions to be returned in the result set. The expressions
163
191
<p class="last">Above, the list of columns <tt class="docutils literal"><span class="pre">user_acount.name</span></tt>,
164
192
<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>
194
<dt id="term-consistency">consistency</dt>
195
<dd><p class="first">Consistency is one of the compoments of the <a class="reference internal" href="#term-acid"><em class="xref std std-term">ACID</em></a> model,
196
and ensures that any transaction will
197
bring the database from one valid state to another. Any data
198
written to the database must be valid according to all defined
199
rules, including but not limited to <em class="xref std std-term">constraints</em>, cascades,
200
triggers, and any combination thereof.
202
<div class="last admonition seealso">
203
<p class="first admonition-title">See also</p>
204
<p><a class="reference internal" href="#term-acid"><em class="xref std std-term">ACID</em></a></p>
205
<p class="last"><a class="reference external" href="http://en.wikipedia.org/wiki/Consistency_(database_systems">http://en.wikipedia.org/wiki/Consistency_(database_systems</a>)</p>
166
208
<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
209
<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
210
enclosing <tt class="docutils literal"><span class="pre">SELECT</span></tt>.</p>
241
283
<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
284
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
285
starting out as <a class="reference internal" href="core/metadata.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
286
by the <a class="reference internal" href="#term-instrumentation"><em class="xref std std-term">instrumentation</em></a> system with instances
245
287
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
288
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
266
308
to determine what kind of mapped class should be applied to a particular
267
309
incoming result row. In SQLAlchemy, the classes are always part
268
310
of a hierarchy mapping using inheritance mapping.</p>
269
<div class="admonition-see-also last admonition seealso">
311
<div class="last admonition seealso">
270
312
<p class="first admonition-title">See also</p>
271
313
<p class="last"><a class="reference internal" href="orm/inheritance.html"><em>Mapping Class Inheritance Hierarchies</em></a></p>
316
<dt id="term-durability">durability</dt>
317
<dd><p class="first">Durability is a property of the <a class="reference internal" href="#term-acid"><em class="xref std std-term">ACID</em></a> model
318
which means that once a transaction has been committed,
319
it will remain so, even in the event of power loss, crashes,
320
or errors. In a relational database, for instance, once a
321
group of SQL statements execute, the results need to be stored
322
permanently (even if the database crashes immediately
325
<div class="last admonition seealso">
326
<p class="first admonition-title">See also</p>
327
<p><a class="reference internal" href="#term-acid"><em class="xref std std-term">ACID</em></a></p>
328
<p class="last"><a class="reference external" href="http://en.wikipedia.org/wiki/Durability_(database_systems">http://en.wikipedia.org/wiki/Durability_(database_systems</a>)</p>
274
331
<dt id="term-from-clause">FROM clause</dt>
275
332
<dd><p class="first">The portion of the <tt class="docutils literal"><span class="pre">SELECT</span></tt> statement which incicates the initial
276
333
source of rows.</p>
290
347
<dt id="term-generative">generative</dt>
291
348
<dd>A term that SQLAlchemy uses to refer what’s normally known
292
349
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>
350
<dt id="term-identity-map">identity map</dt>
351
<dd><p class="first">A mapping between Python objects and their database identities.
352
The identity map is a collection that’s associated with an
353
ORM <a class="reference internal" href="#term-session"><em class="xref std std-term">session</em></a> object, and maintains a single instance
354
of every database object keyed to its identity. The advantage
355
to this pattern is that all operations which occur for a particular
356
database identity are transparently coordinated onto a single
357
object instance. When using an identity map in conjunction with
358
an <a class="reference internal" href="#term-isolated"><em class="xref std std-term">isolated</em></a> transaction, having a reference
359
to an object that’s known to have a particular primary key can
360
be considered from a practical standpoint to be a
361
proxy to the actual database row.</p>
362
<div class="last admonition seealso">
363
<p class="first admonition-title">See also</p>
364
<p class="last">Martin Fowler - Identity Map - <a class="reference external" href="http://martinfowler.com/eaaCatalog/identityMap.html">http://martinfowler.com/eaaCatalog/identityMap.html</a></p>
293
367
<dt id="term-instrumentation"><span id="term-instrumented"></span>instrumentation<br />instrumented</dt>
294
368
<dd>Instrumentation refers to the process of augmenting the functionality
295
369
and attribute set of a particular class. Ideally, the
301
375
class which each represent a particular database column
302
376
or relationship to a related class.</dd>
377
<dt id="term-isolation"><span id="term-isolated"></span>isolation<br />isolated</dt>
378
<dd><p class="first">The isolation property of the <a class="reference internal" href="#term-acid"><em class="xref std std-term">ACID</em></a> model
379
ensures that the concurrent execution
380
of transactions results in a system state that would be
381
obtained if transactions were executed serially, i.e. one
382
after the other. Each transaction must execute in total
383
isolation i.e. if T1 and T2 execute concurrently then each
384
should remain independent of the other.
386
<div class="last admonition seealso">
387
<p class="first admonition-title">See also</p>
388
<p><a class="reference internal" href="#term-acid"><em class="xref std std-term">ACID</em></a></p>
389
<p class="last"><a class="reference external" href="http://en.wikipedia.org/wiki/Isolation_(database_systems">http://en.wikipedia.org/wiki/Isolation_(database_systems</a>)</p>
303
392
<dt id="term-lazy-load"><span id="term-lazy-loads"></span>lazy load<br />lazy loads</dt>
304
393
<dd><p class="first">In object relational mapping, a “lazy load” refers to an
305
394
attribute that does not contain its database-side value
332
421
a new object (or in some cases the same object) with
333
422
additional state added to the object.</p>
334
423
<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>
424
method chaining are the <a class="reference internal" href="core/selectable.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
425
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
426
For example, a <a class="reference internal" href="core/selectable.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
427
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>
428
as an ORDER BY clause by calling upon the <a class="reference internal" href="core/selectable.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>
429
and <a class="reference internal" href="core/selectable.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
430
<div class="highlight-python"><pre>stmt = select([user.c.name]).\
342
431
where(user.c.id > 5).\
343
432
where(user.c.name.like('e%').\
344
433
order_by(user.c.name)</pre>
346
435
<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
436
<a class="reference internal" href="core/selectable.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">
438
<div class="last admonition seealso">
350
439
<p class="first admonition-title">See also</p>
351
440
<p class="last"><a class="reference internal" href="#term-generative"><em class="xref std std-term">generative</em></a></p>
401
490
what we are actually “releasing” is any transactional
402
491
state which as accumulated upon the connection. In most
403
492
situations, the proces of selecting from tables, emitting
404
updates, etc. acquires <em class="xref std std-term">isolated</em> state upon
493
updates, etc. acquires <a class="reference internal" href="#term-isolated"><em class="xref std std-term">isolated</em></a> state upon
405
494
that connection as well as potential row or table locks.
406
495
This state is all local to a particular transaction
407
496
on the connection, and is released when we emit a rollback.
411
500
connection is set up to be used again, it’s in a “clean”
412
501
state with no references held to the previous series
413
502
of operations.</p>
414
<div class="admonition-see-also last admonition seealso">
503
<div class="last admonition seealso">
415
504
<p class="first admonition-title">See also</p>
416
505
<p class="last"><a class="reference internal" href="core/pooling.html"><em>Connection Pooling</em></a></p>
508
<dt id="term-returning">RETURNING</dt>
509
<dd><p class="first">This is a non-SQL standard clause provided in various forms by
510
certain backends, which provides the service of returning a result
511
set upon execution of an INSERT, UPDATE or DELETE statement. Any set
512
of columns from the matched rows can be returned, as though they were
513
produced from a SELECT statement.</p>
514
<p>The RETURNING clause provides both a dramatic performance boost to
515
common update/select scenarios, including retrieval of inline- or
516
default- generated primary key values and defaults at the moment they
517
were created, as well as a way to get at server-generated
518
default values in an atomic way.</p>
519
<p>An example of RETURNING, idiomatic to Postgresql, looks like:</p>
520
<div class="highlight-python"><pre>INSERT INTO user_account (name) VALUES ('new name') RETURNING id, timestamp</pre>
522
<p>Above, the INSERT statement will provide upon execution a result set
523
which includes the values of the columns <tt class="docutils literal"><span class="pre">user_account.id</span></tt> and
524
<tt class="docutils literal"><span class="pre">user_account.timestamp</span></tt>, which above should have been generated as default
525
values as they are not included otherwise (but note any series of columns
526
or SQL expressions can be placed into RETURNING, not just default-value columns).</p>
527
<p>The backends that currently support
528
RETURNING or a similar construct are Postgresql, SQL Server, Oracle,
529
and Firebird. The Postgresql and Firebird implementations are generally
530
full featured, whereas the implementations of SQL Server and Oracle
531
have caveats. On SQL Server, the clause is known as “OUTPUT INSERTED”
532
for INSERT and UPDATE statements and “OUTPUT DELETED” for DELETE statements;
533
the key caveat is that triggers are not supported in conjunction with this
534
keyword. On Oracle, it is known as “RETURNING...INTO”, and requires that the
535
value be placed into an OUT paramter, meaning not only is the syntax awkward,
536
but it can also only be used for one row at a time.</p>
537
<p class="last">SQLAlchemy’s <a class="reference internal" href="core/dml.html#sqlalchemy.sql.expression.UpdateBase.returning" title="sqlalchemy.sql.expression.UpdateBase.returning"><tt class="xref py py-meth docutils literal"><span class="pre">UpdateBase.returning()</span></tt></a> system provides a layer of abstraction
538
on top of the RETURNING systems of these backends to provide a consistent
539
interface for returning columns. The ORM also includes many optimizations
540
that make use of RETURNING when available.</p>
542
<dt id="term-session">Session</dt>
543
<dd><p class="first">The container or scope for ORM database operations. Sessions
544
load instances from the database, track changes to mapped
545
instances and persist changes in a single unit of work when
547
<div class="last admonition seealso">
548
<p class="first admonition-title">See also</p>
549
<p class="last"><a class="reference internal" href="orm/session.html"><em>Using the Session</em></a></p>
419
552
<dt id="term-subquery">subquery</dt>
420
553
<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
554
<tt class="docutils literal"><span class="pre">SELECT</span></tt>.</p>