17
Annotations are a concept used internally by SQLAlchemy in order to store
18
additional information along with :class:`.ClauseElement` objects. A Python
19
dictionary is associated with a copy of the object, which contains key/value
20
pairs significant to various internal systems, mostly within the ORM::
22
some_column = Column('some_column', Integer)
23
some_column_annotated = some_column._annotate({"entity": User})
25
The annotation system differs from the public dictionary :attr:`.Column.info`
26
in that the above annotation operation creates a *copy* of the new :class:`.Column`,
27
rather than considering all annotation values to be part of a single
28
unit. The ORM creates copies of expression objects in order to
29
apply annotations that are specific to their context, such as to differentiate
30
columns that should render themselves as relative to a joined-inheritance
31
entity versus those which should render relative to their immediate parent
32
table alone, as well as to differentiate columns within the "join condition"
33
of a relationship where the column in some cases needs to be expressed
34
in terms of one particular table alias or another, based on its position
35
within the join expression.
39
In Python, a descriptor is an object attribute with “binding behavior”, one whose attribute access has been overridden by methods in the `descriptor protocol <http://docs.python.org/howto/descriptor.html>`_.
40
Those methods are __get__(), __set__(), and __delete__(). If any of those methods are defined
41
for an object, it is said to be a descriptor.
43
In SQLAlchemy, descriptors are used heavily in order to provide attribute behavior
44
on mapped classes. When a class is mapped as such::
49
id = Column(Integer, primary_key=True)
52
The ``MyClass`` class will be :term:`mapped` when its definition
53
is complete, at which point the ``id`` and ``data`` attributes,
54
starting out as :class:`.Column` objects, will be replaced
55
by the :term:`instrumentation` system with instances
56
of :class:`.InstrumentedAttribute`, which are descriptors that
57
provide the above mentioned ``__get__()``, ``__set__()`` and
58
``__delete__()`` methods. The :class:`.InstrumentedAttribute`
59
will generate a SQL expression when used at the class level::
61
>>> print MyClass.data == 5
64
and at the instance level, keeps track of changes to values,
65
and also :term:`lazy loads` unloaded attributes
70
>>> m1.data = "some data"
72
>>> from sqlalchemy import inspect
73
>>> inspect(m1).attrs.data.history.added
77
A result-set column which is used during :term:`polymorphic` loading
78
to determine what kind of mapped class should be applied to a particular
79
incoming result row. In SQLAlchemy, the classes are always part
80
of a hierarchy mapping using inheritance mapping.
84
:ref:`inheritance_toplevel`
88
Instrumentation refers to the process of augmenting the functionality
89
and attribute set of a particular class. Ideally, the
90
behavior of the class should remain close to a regular
91
class, except that additional behviors and features are
92
made available. The SQLAlchemy :term:`mapping` process,
93
among other things, adds database-enabled :term:`descriptors`
95
class which each represent a particular database column
96
or relationship to a related class.
100
In object relational mapping, a "lazy load" refers to an
101
attribute that does not contain its database-side value
102
for some period of time, typically when the object is
103
first loaded. Instead, the attribute receives a
104
*memoization* that causes it to go out to the database
105
and load its data when it's first used. Using this pattern,
106
the complexity and time spent within object fetches can
107
sometimes be reduced, in that
108
attributes for related tables don't need to be addressed
113
`Lazy Load (on Martin Fowler) <http://martinfowler.com/eaaCatalog/lazyLoad.html>`_
115
:term:`N plus one problem`
121
We say a class is "mapped" when it has been passed through the
122
:func:`.orm.mapper` function. This process associates the
123
class with a database table or other :term:`selectable`
124
construct, so that instances of it can be persisted
125
using a :class:`.Session` as well as loaded using a
129
The N plus one problem is a common side effect of the
130
:term:`lazy load` pattern, whereby an application wishes
131
to iterate through a related attribute or collection on
132
each member of a result set of objects, where that
133
attribute or collection is set to be loaded via the lazy
134
load pattern. The net result is that a SELECT statement
135
is emitted to load the initial result set of parent objects;
136
then, as the application iterates through each member,
137
an additional SELECT statement is emitted for each member
138
in order to load the related attribute or collection for
139
that member. The end result is that for a result set of
140
N parent objects, there will be N + 1 SELECT statements emitted.
142
The N plus one problem is alleviated using :term:`eager loading`.
150
Refers to a function that handles several types at once. In SQLAlchemy,
151
the term is usually applied to the concept of an ORM mapped class
152
whereby a query operation will return different subclasses
153
based on information in the result set, typically by checking the
154
value of a particular column in the result known as the :term:`discriminator`.
156
Polymorphic loading in SQLAlchemy implies that a one or a
157
combination of three different schemes are used to map a hierarchy
158
of classes; "joined", "single", and "concrete". The section
159
:ref:`inheritance_toplevel` describes inheritance mapping fully.
162
A term that SQLAlchemy uses to refer what's normally known
163
as :term:`method chaining`; see that term for details.
166
An object-oriented technique whereby the state of an object
167
is constructed by calling methods on the object. The
168
object features any number of methods, each of which return
169
a new object (or in some cases the same object) with
170
additional state added to the object.
172
The two SQLAlchemy objects that make the most use of
173
method chaining are the :class:`~.expression.Select`
174
object and the :class:`~.orm.query.Query` object.
175
For example, a :class:`~.expression.Select` object can
176
be assigned two expressions to its WHERE clause as well
177
as an ORDER BY clause by calling upon the :meth:`~.Select.where`
178
and :meth:`~.Select.order_by` methods::
180
stmt = select([user.c.name]).\
181
where(user.c.id > 5).\
182
where(user.c.name.like('e%').\
183
order_by(user.c.name)
185
Each method call above returns a copy of the original
186
:class:`~.expression.Select` object with additional qualifiers
18
This term refers to when an operation terminates some state which
19
corresponds to a service of some kind. Specifically within
20
SQLAlchemy, it usually refers to a reference to a database connection,
21
and typically a transaction associated with that connection.
22
When we say "the operation releases transactional resources",
23
it means basically that we have a :class:`.Connection` object
24
and we are calling the :meth:`.Connection.close` method, which has
25
the effect of the underlying DBAPI connection being returned
26
to the connection pool. The connection pool, when it receives
27
a connection for return, unconditionally calls the ``rollback()``
28
method of the DBAPI connection, so that any locks or data snapshots within
29
that connection are removed. Then, the connection is either
30
stored locally in memory, still connected but not in a transaction,
31
for subsequent reuse by another operation, or it is closed
32
immediately, depending on the configuration and current
33
state of the connection pool.
196
In the context of SQLAlchemy, the term "released"
197
refers to the process of ending the usage of a particular
198
database connection. SQLAlchemy features the usage
199
of connection pools, which allows configurability as to
200
the lifespan of database connections. When using a pooled
201
connection, the process of "closing" it, i.e. invoking
202
a statement like ``connection.close()``, may have the effect
203
of the connection being returned to an existing pool,
204
or it may have the effect of actually shutting down the
205
underlying TCP/IP connection referred to by that connection -
206
which one takes place depends on configuration as well
207
as the current state of the pool. So we used the term
208
*released* instead, to mean "do whatever it is you do
209
with connections when we're done using them".
211
The term will sometimes be used in the phrase, "release
212
transactional resources", to indicate more explicitly that
213
what we are actually "releasing" is any transactional
214
state which as accumulated upon the connection. In most
215
situations, the proces of selecting from tables, emitting
216
updates, etc. acquires :term:`isolated` state upon
217
that connection as well as potential row or table locks.
218
This state is all local to a particular transaction
219
on the connection, and is released when we emit a rollback.
220
An important feature of the connection pool is that when
221
we return a connection to the pool, the ``connection.rollback()``
222
method of the DBAPI is called as well, so that as the
223
connection is set up to be used again, it's in a "clean"
224
state with no references held to the previous series
37
229
:ref:`pooling_toplevel`
232
DBAPI is shorthand for the phrase "Python Database API
233
Specification". This is a widely used specification
234
within Python to define common usage patterns for all
235
database connection packages. The DBAPI is a "low level"
236
API which is typically the lowest level system used
237
in a Python application to talk to a database. SQLAlchemy's
238
:term:`dialect` system is constructed around the
239
operation of the DBAPI, providing individual dialect
240
classes which service a specific DBAPI on top of a
241
specific database engine; for example, the :func:`.create_engine`
242
URL ``postgresql+psycopg2://@localhost/test``
243
refers to the :mod:`psycopg2 <.postgresql.psycopg2>`
244
DBAPI/dialect combination, whereas the URL ``mysql+mysqldb://@localhost/test``
245
refers to the :mod:`MySQL for Python <.mysql.mysqldb>`
246
DBAPI DBAPI/dialect combination.
250
`PEP 249 - Python Database API Specification v2.0 <http://www.python.org/dev/peps/pep-0249/>`_
254
This pattern is where the system transparently keeps
255
track of changes to objects and periodically flushes all those
256
pending changes out to the database. SQLAlchemy's Session
257
implements this pattern fully in a manner similar to that of
262
`Unit of Work by Martin Fowler <http://martinfowler.com/eaaCatalog/unitOfWork.html>`_
267
The portion of the ``SELECT`` statement which enumerates the
268
SQL expressions to be returned in the result set. The expressions
269
follow the ``SELECT`` keyword directly and are a comma-separated
270
list of individual expressions.
276
SELECT user_account.name, user_account.email
277
FROM user_account WHERE user_account.name = 'fred'
279
Above, the list of columns ``user_acount.name``,
280
``user_account.email`` is the columns clause of the ``SELECT``.
283
The portion of the ``SELECT`` statement which indicates criteria
284
by which rows should be filtered. It is a single SQL expression
285
which follows the keyword ``WHERE``.
289
SELECT user_account.name, user_account.email
291
WHERE user_account.name = 'fred' AND user_account.status = 'E'
293
Above, the phrase ``WHERE user_account.name = 'fred' AND user_account.status = 'E'``
294
comprises the WHERE clause of the ``SELECT``.
297
The portion of the ``SELECT`` statement which incicates the initial
300
A simple ``SELECT`` will feature one or more table names in its
301
FROM clause. Multiple sources are separated by a comma:
305
SELECT user.name, address.email_address
307
WHERE user.id=address.user_id
309
The FROM clause is also where explicit joins are specified. We can
310
rewrite the above ``SELECT`` using a single ``FROM`` element which consists
311
of a ``JOIN`` of the two tables:
315
SELECT user.name, address.email_address
316
FROM user JOIN address ON user.id=address.user_id
320
Refers to a ``SELECT`` statement that is embedded within an enclosing
323
A subquery comes in two general flavors, one known as a "scalar select"
324
which specifically must return exactly one row and one column, and the
325
other form which acts as a "derived table" and serves as a source of
326
rows for the FROM clause of another select. A scalar select is eligble
327
to be placed in the :term:`WHERE clause`, :term:`columns clause`,
328
ORDER BY clause or HAVING clause of the enclosing select, whereas the
329
derived table form is eligible to be placed in the FROM clause of the
330
enclosing ``SELECT``.
334
1. a scalar subquery placed in the :term:`columns clause` of an enclosing
335
``SELECT``. The subquery in this example is a :term:`correlated subquery` because part
336
of the rows which it selects from are given via the enclosing statement.
340
SELECT id, (SELECT name FROM address WHERE address.user_id=user.id)
343
2. a scalar subquery placed in the :term:`WHERE clause` of an enclosing
344
``SELECT``. This subquery in this example is not correlated as it selects a fixed result.
348
SELECT id, name FROM user
349
WHERE status=(SELECT status_id FROM status_code WHERE code='C')
351
3. a derived table subquery placed in the :term:`FROM clause` of an enclosing
352
``SELECT``. Such a subquery is almost always given an alias name.
356
SELECT user.id, user.name, ad_subq.email_address
359
(select user_id, email_address FROM address WHERE address_type='Q') AS ad_subq
360
ON user.id = ad_subq.user_id
364
correlated subqueries
365
A :term:`subquery` is correlated if it depends on data in the
366
enclosing ``SELECT``.
368
Below, a subquery selects the aggregate value ``MIN(a.id)``
369
from the ``email_address`` table, such that
370
it will be invoked for each value of ``user_account.id``, correlating
371
the value of this column against the ``email_address.user_account_id``
376
SELECT user_account.name, email_address.email
378
JOIN email_address ON user_account.id=email_address.user_account_id
379
WHERE email_address.id = (
380
SELECT MIN(a.id) FROM email_address AS a
381
WHERE a.user_account_id=user_account.id
384
The above subquery refers to the ``user_account`` table, which is not itself
385
in the ``FROM`` clause of this nested query. Instead, the ``user_account``
386
table is recieved from the enclosing query, where each row selected from
387
``user_account`` results in a distinct execution of the subquery.
389
A correlated subquery is in most cases present in the :term:`WHERE clause`
390
or :term:`columns clause` of the immediately enclosing ``SELECT``
391
statement, as well as in the ORDER BY or HAVING clause.
393
In less common cases, a correlated subquery may be present in the
394
:term:`FROM clause` of an enclosing ``SELECT``; in these cases the
395
correlation is typically due to the enclosing ``SELECT`` itself being
396
enclosed in the WHERE,
397
ORDER BY, columns or HAVING clause of another ``SELECT``, such as:
401
SELECT parent.id FROM parent
404
SELECT child.id AS id, child.parent_id AS parent_id, child.pos AS pos
406
WHERE child.parent_id = parent.id ORDER BY child.pos
410
Correlation from one ``SELECT`` directly to one which encloses the correlated
411
query via its ``FROM``
412
clause is not possible, because the correlation can only proceed once the
413
original source rows from the enclosing statement's FROM clause are available.
added
removed
doc/_sources/glossary.txt
