« back to all changes in this revision
Viewing changes to doc/_sources/orm/session.txt
- browse files at revision 39
- compare with another revision
- download diff
- download tarball
- view history from revision 39
- Committer: Package Import Robot
- Author(s): Piotr Ożarowski
- Date: 2014-06-27 20:17:13 UTC
- mfrom: (1.4.28)
- Revision ID: package-import@ubuntu.com-20140627201713-g6p1kq8q1qenztrv
Tags: 0.9.6-1
* New upstream release
* Remove Python 3.X build tag files, thanks to Matthias Urlichs for the
patch (closes: #747852)
* python-fdb isn't in the Debian archive yet so default dialect for firebird://
URLs is changed to obsolete kinterbasdb, thanks to Russell Stuart for the
patch (closes: #752145)
* Remove Python 3.X build tag files, thanks to Matthias Urlichs for the
patch (closes: #747852)
* python-fdb isn't in the Debian archive yet so default dialect for firebird://
URLs is changed to obsolete kinterbasdb, thanks to Russell Stuart for the
patch (closes: #752145)
- files added:
- .pc/change_firebirds_default_dialect_to_kinterbasedb.patch
- .pc/change_firebirds_default_dialect_to_kinterbasedb.patch/lib
- .pc/change_firebirds_default_dialect_to_kinterbasedb.patch/lib/sqlalchemy
- .pc/change_firebirds_default_dialect_to_kinterbasedb.patch/lib/sqlalchemy/dialects
- .pc/change_firebirds_default_dialect_to_kinterbasedb.patch/lib/sqlalchemy/dialects/firebird
- debian/patches
- doc/_modules/examples/materialized_paths
- examples/materialized_paths
- files removed:
- doc/_sources
- doc/_sources/changelog
- doc/_sources/core
- doc/_sources/dialects
- doc/_sources/orm
- doc/_sources/orm/extensions
- files modified:
- .pc/applied-patches
added
removed
doc/_sources/orm/session.txt
1
.. _session_toplevel:
2
3
=================
4
Using the Session
5
=================
6
7
.. module:: sqlalchemy.orm.session
8
9
The :func:`.orm.mapper` function and :mod:`~sqlalchemy.ext.declarative` extensions
10
are the primary configurational interface for the ORM. Once mappings are
11
configured, the primary usage interface for persistence operations is the
12
:class:`.Session`.
13
14
What does the Session do ?
15
==========================
16
17
In the most general sense, the :class:`~.Session` establishes all
18
conversations with the database and represents a "holding zone" for all the
19
objects which you've loaded or associated with it during its lifespan. It
20
provides the entrypoint to acquire a :class:`.Query` object, which sends
21
queries to the database using the :class:`~.Session` object's current database
22
connection, populating result rows into objects that are then stored in the
23
:class:`.Session`, inside a structure called the `Identity Map
24
<http://martinfowler.com/eaaCatalog/identityMap.html>`_ - a data structure
25
that maintains unique copies of each object, where "unique" means "only one
26
object with a particular primary key".
27
28
The :class:`.Session` begins in an essentially stateless form. Once queries
29
are issued or other objects are persisted with it, it requests a connection
30
resource from an :class:`.Engine` that is associated either with the
31
:class:`.Session` itself or with the mapped :class:`.Table` objects being
32
operated upon. This connection represents an ongoing transaction, which
33
remains in effect until the :class:`.Session` is instructed to commit or roll
34
back its pending state.
35
36
All changes to objects maintained by a :class:`.Session` are tracked - before
37
the database is queried again or before the current transaction is committed,
38
it **flushes** all pending changes to the database. This is known as the `Unit
39
of Work <http://martinfowler.com/eaaCatalog/unitOfWork.html>`_ pattern.
40
41
When using a :class:`.Session`, it's important to note that the objects
42
which are associated with it are **proxy objects** to the transaction being
43
held by the :class:`.Session` - there are a variety of events that will cause
44
objects to re-access the database in order to keep synchronized. It is
45
possible to "detach" objects from a :class:`.Session`, and to continue using
46
them, though this practice has its caveats. It's intended that
47
usually, you'd re-associate detached objects with another :class:`.Session` when you
48
want to work with them again, so that they can resume their normal task of
49
representing database state.
50
51
.. _session_getting:
52
53
Getting a Session
54
=================
55
56
:class:`.Session` is a regular Python class which can
57
be directly instantiated. However, to standardize how sessions are configured
58
and acquired, the :class:`.sessionmaker` class is normally
59
used to create a top level :class:`.Session`
60
configuration which can then be used throughout an application without the
61
need to repeat the configurational arguments.
62
63
The usage of :class:`.sessionmaker` is illustrated below:
64
65
.. sourcecode:: python+sql
66
67
from sqlalchemy import create_engine
68
from sqlalchemy.orm import sessionmaker
69
70
# an Engine, which the Session will use for connection
71
# resources
72
some_engine = create_engine('postgresql://scott:tiger@localhost/')
73
74
# create a configured "Session" class
75
Session = sessionmaker(bind=some_engine)
76
77
# create a Session
78
session = Session()
79
80
# work with sess
81
myobject = MyObject('foo', 'bar')
82
session.add(myobject)
83
session.commit()
84
85
Above, the :class:`.sessionmaker` call creates a factory for us,
86
which we assign to the name ``Session``. This factory, when
87
called, will create a new :class:`.Session` object using the configurational
88
arguments we've given the factory. In this case, as is typical,
89
we've configured the factory to specify a particular :class:`.Engine` for
90
connection resources.
91
92
A typical setup will associate the :class:`.sessionmaker` with an :class:`.Engine`,
93
so that each :class:`.Session` generated will use this :class:`.Engine`
94
to acquire connection resources. This association can
95
be set up as in the example above, using the ``bind`` argument.
96
97
When you write your application, place the
98
:class:`.sessionmaker` factory at the global level. This
99
factory can then
100
be used by the rest of the applcation as the source of new :class:`.Session`
101
instances, keeping the configuration for how :class:`.Session` objects
102
are constructed in one place.
103
104
The :class:`.sessionmaker` factory can also be used in conjunction with
105
other helpers, which are passed a user-defined :class:`.sessionmaker` that
106
is then maintained by the helper. Some of these helpers are discussed in the
107
section :ref:`session_faq_whentocreate`.
108
109
Adding Additional Configuration to an Existing sessionmaker()
110
--------------------------------------------------------------
111
112
A common scenario is where the :class:`.sessionmaker` is invoked
113
at module import time, however the generation of one or more :class:`.Engine`
114
instances to be associated with the :class:`.sessionmaker` has not yet proceeded.
115
For this use case, the :class:`.sessionmaker` construct offers the
116
:meth:`.sessionmaker.configure` method, which will place additional configuration
117
directives into an existing :class:`.sessionmaker` that will take place
118
when the construct is invoked::
119
120
121
from sqlalchemy.orm import sessionmaker
122
from sqlalchemy import create_engine
123
124
# configure Session class with desired options
125
Session = sessionmaker()
126
127
# later, we create the engine
128
engine = create_engine('postgresql://...')
129
130
# associate it with our custom Session class
131
Session.configure(bind=engine)
132
133
# work with the session
134
session = Session()
135
136
Creating Ad-Hoc Session Objects with Alternate Arguments
137
---------------------------------------------------------
138
139
For the use case where an application needs to create a new :class:`.Session` with
140
special arguments that deviate from what is normally used throughout the application,
141
such as a :class:`.Session` that binds to an alternate
142
source of connectivity, or a :class:`.Session` that should
143
have other arguments such as ``expire_on_commit`` established differently from
144
what most of the application wants, specific arguments can be passed to the
145
:class:`.sessionmaker` factory's :meth:`.sessionmaker.__call__` method.
146
These arguments will override whatever
147
configurations have already been placed, such as below, where a new :class:`.Session`
148
is constructed against a specific :class:`.Connection`::
149
150
# at the module level, the global sessionmaker,
151
# bound to a specific Engine
152
Session = sessionmaker(bind=engine)
153
154
# later, some unit of code wants to create a
155
# Session that is bound to a specific Connection
156
conn = engine.connect()
157
session = Session(bind=conn)
158
159
The typical rationale for the association of a :class:`.Session` with a specific
160
:class:`.Connection` is that of a test fixture that maintains an external
161
transaction - see :ref:`session_external_transaction` for an example of this.
162
163
Using the Session
164
==================
165
166
.. _session_object_states:
167
168
Quickie Intro to Object States
169
------------------------------
170
171
It's helpful to know the states which an instance can have within a session:
172
173
* **Transient** - an instance that's not in a session, and is not saved to the
174
database; i.e. it has no database identity. The only relationship such an
175
object has to the ORM is that its class has a ``mapper()`` associated with
176
it.
177
178
* **Pending** - when you :meth:`~.Session.add` a transient
179
instance, it becomes pending. It still wasn't actually flushed to the
180
database yet, but it will be when the next flush occurs.
181
182
* **Persistent** - An instance which is present in the session and has a record
183
in the database. You get persistent instances by either flushing so that the
184
pending instances become persistent, or by querying the database for
185
existing instances (or moving persistent instances from other sessions into
186
your local session).
187
188
* **Detached** - an instance which has a record in the database, but is not in
189
any session. There's nothing wrong with this, and you can use objects
190
normally when they're detached, **except** they will not be able to issue
191
any SQL in order to load collections or attributes which are not yet loaded,
192
or were marked as "expired".
193
194
Knowing these states is important, since the
195
:class:`.Session` tries to be strict about ambiguous
196
operations (such as trying to save the same object to two different sessions
197
at the same time).
198
199
.. _session_faq:
200
201
Session Frequently Asked Questions
202
-----------------------------------
203
204
205
When do I make a :class:`.sessionmaker`?
206
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
207
208
Just one time, somewhere in your application's global scope. It should be
209
looked upon as part of your application's configuration. If your
210
application has three .py files in a package, you could, for example,
211
place the :class:`.sessionmaker` line in your ``__init__.py`` file; from
212
that point on your other modules say "from mypackage import Session". That
213
way, everyone else just uses :class:`.Session()`,
214
and the configuration of that session is controlled by that central point.
215
216
If your application starts up, does imports, but does not know what
217
database it's going to be connecting to, you can bind the
218
:class:`.Session` at the "class" level to the
219
engine later on, using :meth:`.sessionmaker.configure`.
220
221
In the examples in this section, we will frequently show the
222
:class:`.sessionmaker` being created right above the line where we actually
223
invoke :class:`.Session`. But that's just for
224
example's sake! In reality, the :class:`.sessionmaker` would be somewhere
225
at the module level. The calls to instantiate :class:`.Session`
226
would then be placed at the point in the application where database
227
conversations begin.
228
229
.. _session_faq_whentocreate:
230
231
When do I construct a :class:`.Session`, when do I commit it, and when do I close it?
232
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
233
234
.. topic:: tl;dr;
235
236
As a general rule, keep the lifecycle of the session **separate and
237
external** from functions and objects that access and/or manipulate
238
database data.
239
240
A :class:`.Session` is typically constructed at the beginning of a logical
241
operation where database access is potentially anticipated.
242
243
The :class:`.Session`, whenever it is used to talk to the database,
244
begins a database transaction as soon as it starts communicating.
245
Assuming the ``autocommit`` flag is left at its recommended default
246
of ``False``, this transaction remains in progress until the :class:`.Session`
247
is rolled back, committed, or closed. The :class:`.Session` will
248
begin a new transaction if it is used again, subsequent to the previous
249
transaction ending; from this it follows that the :class:`.Session`
250
is capable of having a lifespan across many transactions, though only
251
one at a time. We refer to these two concepts as **transaction scope**
252
and **session scope**.
253
254
The implication here is that the SQLAlchemy ORM is encouraging the
255
developer to establish these two scopes in their application,
256
including not only when the scopes begin and end, but also the
257
expanse of those scopes, for example should a single
258
:class:`.Session` instance be local to the execution flow within a
259
function or method, should it be a global object used by the
260
entire application, or somewhere in between these two.
261
262
The burden placed on the developer to determine this scope is one
263
area where the SQLAlchemy ORM necessarily has a strong opinion
264
about how the database should be used. The :term:`unit of work` pattern
265
is specifically one of accumulating changes over time and flushing
266
them periodically, keeping in-memory state in sync with what's
267
known to be present in a local transaction. This pattern is only
268
effective when meaningful transaction scopes are in place.
269
270
It's usually not very hard to determine the best points at which
271
to begin and end the scope of a :class:`.Session`, though the wide
272
variety of application architectures possible can introduce
273
challenging situations.
274
275
A common choice is to tear down the :class:`.Session` at the same
276
time the transaction ends, meaning the transaction and session scopes
277
are the same. This is a great choice to start out with as it
278
removes the need to consider session scope as separate from transaction
279
scope.
280
281
While there's no one-size-fits-all recommendation for how transaction
282
scope should be determined, there are common patterns. Especially
283
if one is writing a web application, the choice is pretty much established.
284
285
A web application is the easiest case because such an appication is already
286
constructed around a single, consistent scope - this is the **request**,
287
which represents an incoming request from a browser, the processing
288
of that request to formulate a response, and finally the delivery of that
289
response back to the client. Integrating web applications with the
290
:class:`.Session` is then the straightforward task of linking the
291
scope of the :class:`.Session` to that of the request. The :class:`.Session`
292
can be established as the request begins, or using a :term:`lazy initialization`
293
pattern which establishes one as soon as it is needed. The request
294
then proceeds, with some system in place where application logic can access
295
the current :class:`.Session` in a manner associated with how the actual
296
request object is accessed. As the request ends, the :class:`.Session`
297
is torn down as well, usually through the usage of event hooks provided
298
by the web framework. The transaction used by the :class:`.Session`
299
may also be committed at this point, or alternatively the application may
300
opt for an explicit commit pattern, only committing for those requests
301
where one is warranted, but still always tearing down the :class:`.Session`
302
unconditionally at the end.
303
304
Some web frameworks include infrastructure to assist in the task
305
of aligning the lifespan of a :class:`.Session` with that of a web request.
306
This includes products such as `Flask-SQLAlchemy <http://packages.python.org/Flask-SQLAlchemy/>`_,
307
for usage in conjunction with the Flask web framework,
308
and `Zope-SQLAlchemy <http://pypi.python.org/pypi/zope.sqlalchemy>`_,
309
typically used with the Pyramid framework.
310
SQLAlchemy recommends that these products be used as available.
311
312
In those situations where the integration libraries are not
313
provided or are insufficient, SQLAlchemy includes its own "helper" class known as
314
:class:`.scoped_session`. A tutorial on the usage of this object
315
is at :ref:`unitofwork_contextual`. It provides both a quick way
316
to associate a :class:`.Session` with the current thread, as well as
317
patterns to associate :class:`.Session` objects with other kinds of
318
scopes.
319
320
As mentioned before, for non-web applications there is no one clear
321
pattern, as applications themselves don't have just one pattern
322
of architecture. The best strategy is to attempt to demarcate
323
"operations", points at which a particular thread begins to perform
324
a series of operations for some period of time, which can be committed
325
at the end. Some examples:
326
327
* A background daemon which spawns off child forks
328
would want to create a :class:`.Session` local to each child
329
process, work with that :class:`.Session` through the life of the "job"
330
that the fork is handling, then tear it down when the job is completed.
331
332
* For a command-line script, the application would create a single, global
333
:class:`.Session` that is established when the program begins to do its
334
work, and commits it right as the program is completing its task.
335
336
* For a GUI interface-driven application, the scope of the :class:`.Session`
337
may best be within the scope of a user-generated event, such as a button
338
push. Or, the scope may correspond to explicit user interaction, such as
339
the user "opening" a series of records, then "saving" them.
340
341
As a general rule, the application should manage the lifecycle of the
342
session *externally* to functions that deal with specific data. This is a
343
fundamental separation of concerns which keeps data-specific operations
344
agnostic of the context in which they access and manipulate that data.
345
346
E.g. **don't do this**::
347
348
### this is the **wrong way to do it** ###
349
350
class ThingOne(object):
351
def go(self):
352
session = Session()
353
try:
354
session.query(FooBar).update({"x": 5})
355
session.commit()
356
except:
357
session.rollback()
358
raise
359
360
class ThingTwo(object):
361
def go(self):
362
session = Session()
363
try:
364
session.query(Widget).update({"q": 18})
365
session.commit()
366
except:
367
session.rollback()
368
raise
369
370
def run_my_program():
371
ThingOne().go()
372
ThingTwo().go()
373
374
Keep the lifecycle of the session (and usually the transaction)
375
**separate and external**::
376
377
### this is a **better** (but not the only) way to do it ###
378
379
class ThingOne(object):
380
def go(self, session):
381
session.query(FooBar).update({"x": 5})
382
383
class ThingTwo(object):
384
def go(self, session):
385
session.query(Widget).update({"q": 18})
386
387
def run_my_program():
388
session = Session()
389
try:
390
ThingOne().go(session)
391
ThingTwo().go(session)
392
393
session.commit()
394
except:
395
session.rollback()
396
raise
397
finally:
398
session.close()
399
400
The advanced developer will try to keep the details of session, transaction
401
and exception management as far as possible from the details of the program
402
doing its work. For example, we can further separate concerns using a `context manager <http://docs.python.org/3/library/contextlib.html#contextlib.contextmanager>`_::
403
404
### another way (but again *not the only way*) to do it ###
405
406
from contextlib import contextmanager
407
408
@contextmanager
409
def session_scope():
410
"""Provide a transactional scope around a series of operations."""
411
session = Session()
412
try:
413
yield session
414
session.commit()
415
except:
416
session.rollback()
417
raise
418
finally:
419
session.close()
420
421
422
def run_my_program():
423
with session_scope() as session:
424
ThingOne().go(session)
425
ThingTwo().go(session)
426
427
428
Is the Session a cache?
429
~~~~~~~~~~~~~~~~~~~~~~~~~~~
430
431
Yeee...no. It's somewhat used as a cache, in that it implements the
432
:term:`identity map` pattern, and stores objects keyed to their primary key.
433
However, it doesn't do any kind of query caching. This means, if you say
434
``session.query(Foo).filter_by(name='bar')``, even if ``Foo(name='bar')``
435
is right there, in the identity map, the session has no idea about that.
436
It has to issue SQL to the database, get the rows back, and then when it
437
sees the primary key in the row, *then* it can look in the local identity
438
map and see that the object is already there. It's only when you say
439
``query.get({some primary key})`` that the
440
:class:`~sqlalchemy.orm.session.Session` doesn't have to issue a query.
441
442
Additionally, the Session stores object instances using a weak reference
443
by default. This also defeats the purpose of using the Session as a cache.
444
445
The :class:`.Session` is not designed to be a
446
global object from which everyone consults as a "registry" of objects.
447
That's more the job of a **second level cache**. SQLAlchemy provides
448
a pattern for implementing second level caching using `dogpile.cache <http://dogpilecache.readthedocs.org/>`_,
449
via the :ref:`examples_caching` example.
450
451
How can I get the :class:`~sqlalchemy.orm.session.Session` for a certain object?
452
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
453
454
Use the :meth:`~.Session.object_session` classmethod
455
available on :class:`~sqlalchemy.orm.session.Session`::
456
457
session = Session.object_session(someobject)
458
459
The newer :ref:`core_inspection_toplevel` system can also be used::
460
461
from sqlalchemy import inspect
462
session = inspect(someobject).session
463
464
.. _session_faq_threadsafe:
465
466
Is the session thread-safe?
467
~~~~~~~~~~~~~~~~~~~~~~~~~~~
468
469
The :class:`.Session` is very much intended to be used in a
470
**non-concurrent** fashion, which usually means in only one thread at a
471
time.
472
473
The :class:`.Session` should be used in such a way that one
474
instance exists for a single series of operations within a single
475
transaction. One expedient way to get this effect is by associating
476
a :class:`.Session` with the current thread (see :ref:`unitofwork_contextual`
477
for background). Another is to use a pattern
478
where the :class:`.Session` is passed between functions and is otherwise
479
not shared with other threads.
480
481
The bigger point is that you should not *want* to use the session
482
with multiple concurrent threads. That would be like having everyone at a
483
restaurant all eat from the same plate. The session is a local "workspace"
484
that you use for a specific set of tasks; you don't want to, or need to,
485
share that session with other threads who are doing some other task.
486
487
Making sure the :class:`.Session` is only used in a single concurrent thread at a time
488
is called a "share nothing" approach to concurrency. But actually, not
489
sharing the :class:`.Session` implies a more significant pattern; it
490
means not just the :class:`.Session` object itself, but
491
also **all objects that are associated with that Session**, must be kept within
492
the scope of a single concurrent thread. The set of mapped
493
objects associated with a :class:`.Session` are essentially proxies for data
494
within database rows accessed over a database connection, and so just like
495
the :class:`.Session` itself, the whole
496
set of objects is really just a large-scale proxy for a database connection
497
(or connections). Ultimately, it's mostly the DBAPI connection itself that
498
we're keeping away from concurrent access; but since the :class:`.Session`
499
and all the objects associated with it are all proxies for that DBAPI connection,
500
the entire graph is essentially not safe for concurrent access.
501
502
If there are in fact multiple threads participating
503
in the same task, then you may consider sharing the session and its objects between
504
those threads; however, in this extremely unusual scenario the application would
505
need to ensure that a proper locking scheme is implemented so that there isn't
506
*concurrent* access to the :class:`.Session` or its state. A more common approach
507
to this situation is to maintain a single :class:`.Session` per concurrent thread,
508
but to instead *copy* objects from one :class:`.Session` to another, often
509
using the :meth:`.Session.merge` method to copy the state of an object into
510
a new object local to a different :class:`.Session`.
511
512
Querying
513
--------
514
515
The :meth:`~.Session.query` function takes one or more
516
*entities* and returns a new :class:`~sqlalchemy.orm.query.Query` object which
517
will issue mapper queries within the context of this Session. An entity is
518
defined as a mapped class, a :class:`~sqlalchemy.orm.mapper.Mapper` object, an
519
orm-enabled *descriptor*, or an ``AliasedClass`` object::
520
521
# query from a class
522
session.query(User).filter_by(name='ed').all()
523
524
# query with multiple classes, returns tuples
525
session.query(User, Address).join('addresses').filter_by(name='ed').all()
526
527
# query using orm-enabled descriptors
528
session.query(User.name, User.fullname).all()
529
530
# query from a mapper
531
user_mapper = class_mapper(User)
532
session.query(user_mapper)
533
534
When :class:`~sqlalchemy.orm.query.Query` returns results, each object
535
instantiated is stored within the identity map. When a row matches an object
536
which is already present, the same object is returned. In the latter case,
537
whether or not the row is populated onto an existing object depends upon
538
whether the attributes of the instance have been *expired* or not. A
539
default-configured :class:`~sqlalchemy.orm.session.Session` automatically
540
expires all instances along transaction boundaries, so that with a normally
541
isolated transaction, there shouldn't be any issue of instances representing
542
data which is stale with regards to the current transaction.
543
544
The :class:`.Query` object is introduced in great detail in
545
:ref:`ormtutorial_toplevel`, and further documented in
546
:ref:`query_api_toplevel`.
547
548
Adding New or Existing Items
549
----------------------------
550
551
:meth:`~.Session.add` is used to place instances in the
552
session. For *transient* (i.e. brand new) instances, this will have the effect
553
of an INSERT taking place for those instances upon the next flush. For
554
instances which are *persistent* (i.e. were loaded by this session), they are
555
already present and do not need to be added. Instances which are *detached*
556
(i.e. have been removed from a session) may be re-associated with a session
557
using this method::
558
559
user1 = User(name='user1')
560
user2 = User(name='user2')
561
session.add(user1)
562
session.add(user2)
563
564
session.commit() # write changes to the database
565
566
To add a list of items to the session at once, use
567
:meth:`~.Session.add_all`::
568
569
session.add_all([item1, item2, item3])
570
571
The :meth:`~.Session.add` operation **cascades** along
572
the ``save-update`` cascade. For more details see the section
573
:ref:`unitofwork_cascades`.
574
575
.. _unitofwork_merging:
576
577
Merging
578
-------
579
580
:meth:`~.Session.merge` transfers state from an
581
outside object into a new or already existing instance within a session. It
582
also reconciles the incoming data against the state of the
583
database, producing a history stream which will be applied towards the next
584
flush, or alternatively can be made to produce a simple "transfer" of
585
state without producing change history or accessing the database. Usage is as follows::
586
587
merged_object = session.merge(existing_object)
588
589
When given an instance, it follows these steps:
590
591
* It examines the primary key of the instance. If it's present, it attempts
592
to locate that instance in the local identity map. If the ``load=True``
593
flag is left at its default, it also checks the database for this primary
594
key if not located locally.
595
* If the given instance has no primary key, or if no instance can be found
596
with the primary key given, a new instance is created.
597
* The state of the given instance is then copied onto the located/newly
598
created instance. For attributes which are present on the source
599
instance, the value is transferred to the target instance. For mapped
600
attributes which aren't present on the source, the attribute is
601
expired on the target instance, discarding its existing value.
602
603
If the ``load=True`` flag is left at its default,
604
this copy process emits events and will load the target object's
605
unloaded collections for each attribute present on the source object,
606
so that the incoming state can be reconciled against what's
607
present in the database. If ``load``
608
is passed as ``False``, the incoming data is "stamped" directly without
609
producing any history.
610
* The operation is cascaded to related objects and collections, as
611
indicated by the ``merge`` cascade (see :ref:`unitofwork_cascades`).
612
* The new instance is returned.
613
614
With :meth:`~.Session.merge`, the given "source"
615
instance is not modifed nor is it associated with the target :class:`.Session`,
616
and remains available to be merged with any number of other :class:`.Session`
617
objects. :meth:`~.Session.merge` is useful for
618
taking the state of any kind of object structure without regard for its
619
origins or current session associations and copying its state into a
620
new session. Here's some examples:
621
622
* An application which reads an object structure from a file and wishes to
623
save it to the database might parse the file, build up the
624
structure, and then use
625
:meth:`~.Session.merge` to save it
626
to the database, ensuring that the data within the file is
627
used to formulate the primary key of each element of the
628
structure. Later, when the file has changed, the same
629
process can be re-run, producing a slightly different
630
object structure, which can then be ``merged`` in again,
631
and the :class:`~sqlalchemy.orm.session.Session` will
632
automatically update the database to reflect those
633
changes, loading each object from the database by primary key and
634
then updating its state with the new state given.
635
636
* An application is storing objects in an in-memory cache, shared by
637
many :class:`.Session` objects simultaneously. :meth:`~.Session.merge`
638
is used each time an object is retrieved from the cache to create
639
a local copy of it in each :class:`.Session` which requests it.
640
The cached object remains detached; only its state is moved into
641
copies of itself that are local to individual :class:`~.Session`
642
objects.
643
644
In the caching use case, it's common that the ``load=False`` flag
645
is used to remove the overhead of reconciling the object's state
646
with the database. There's also a "bulk" version of
647
:meth:`~.Session.merge` called :meth:`~.Query.merge_result`
648
that was designed to work with cache-extended :class:`.Query`
649
objects - see the section :ref:`examples_caching`.
650
651
* An application wants to transfer the state of a series of objects
652
into a :class:`.Session` maintained by a worker thread or other
653
concurrent system. :meth:`~.Session.merge` makes a copy of each object
654
to be placed into this new :class:`.Session`. At the end of the operation,
655
the parent thread/process maintains the objects it started with,
656
and the thread/worker can proceed with local copies of those objects.
657
658
In the "transfer between threads/processes" use case, the application
659
may want to use the ``load=False`` flag as well to avoid overhead and
660
redundant SQL queries as the data is transferred.
661
662
Merge Tips
663
~~~~~~~~~~
664
665
:meth:`~.Session.merge` is an extremely useful method for many purposes. However,
666
it deals with the intricate border between objects that are transient/detached and
667
those that are persistent, as well as the automated transferrence of state.
668
The wide variety of scenarios that can present themselves here often require a
669
more careful approach to the state of objects. Common problems with merge usually involve
670
some unexpected state regarding the object being passed to :meth:`~.Session.merge`.
671
672
Lets use the canonical example of the User and Address objects::
673
674
class User(Base):
675
__tablename__ = 'user'
676
677
id = Column(Integer, primary_key=True)
678
name = Column(String(50), nullable=False)
679
addresses = relationship("Address", backref="user")
680
681
class Address(Base):
682
__tablename__ = 'address'
683
684
id = Column(Integer, primary_key=True)
685
email_address = Column(String(50), nullable=False)
686
user_id = Column(Integer, ForeignKey('user.id'), nullable=False)
687
688
Assume a ``User`` object with one ``Address``, already persistent::
689
690
>>> u1 = User(name='ed', addresses=[Address(email_address='ed@ed.com')])
691
>>> session.add(u1)
692
>>> session.commit()
693
694
We now create ``a1``, an object outside the session, which we'd like
695
to merge on top of the existing ``Address``::
696
697
>>> existing_a1 = u1.addresses[0]
698
>>> a1 = Address(id=existing_a1.id)
699
700
A surprise would occur if we said this::
701
702
>>> a1.user = u1
703
>>> a1 = session.merge(a1)
704
>>> session.commit()
705
sqlalchemy.orm.exc.FlushError: New instance <Address at 0x1298f50>
706
with identity key (<class '__main__.Address'>, (1,)) conflicts with
707
persistent instance <Address at 0x12a25d0>
708
709
Why is that ? We weren't careful with our cascades. The assignment
710
of ``a1.user`` to a persistent object cascaded to the backref of ``User.addresses``
711
and made our ``a1`` object pending, as though we had added it. Now we have
712
*two* ``Address`` objects in the session::
713
714
>>> a1 = Address()
715
>>> a1.user = u1
716
>>> a1 in session
717
True
718
>>> existing_a1 in session
719
True
720
>>> a1 is existing_a1
721
False
722
723
Above, our ``a1`` is already pending in the session. The
724
subsequent :meth:`~.Session.merge` operation essentially
725
does nothing. Cascade can be configured via the :paramref:`~.relationship.cascade`
726
option on :func:`.relationship`, although in this case it
727
would mean removing the ``save-update`` cascade from the
728
``User.addresses`` relationship - and usually, that behavior
729
is extremely convenient. The solution here would usually be to not assign
730
``a1.user`` to an object already persistent in the target
731
session.
732
733
The ``cascade_backrefs=False`` option of :func:`.relationship`
734
will also prevent the ``Address`` from
735
being added to the session via the ``a1.user = u1`` assignment.
736
737
Further detail on cascade operation is at :ref:`unitofwork_cascades`.
738
739
Another example of unexpected state::
740
741
>>> a1 = Address(id=existing_a1.id, user_id=u1.id)
742
>>> assert a1.user is None
743
>>> True
744
>>> a1 = session.merge(a1)
745
>>> session.commit()
746
sqlalchemy.exc.IntegrityError: (IntegrityError) address.user_id
747
may not be NULL
748
749
Here, we accessed a1.user, which returned its default value
750
of ``None``, which as a result of this access, has been placed in the ``__dict__`` of
751
our object ``a1``. Normally, this operation creates no change event,
752
so the ``user_id`` attribute takes precedence during a
753
flush. But when we merge the ``Address`` object into the session, the operation
754
is equivalent to::
755
756
>>> existing_a1.id = existing_a1.id
757
>>> existing_a1.user_id = u1.id
758
>>> existing_a1.user = None
759
760
Where above, both ``user_id`` and ``user`` are assigned to, and change events
761
are emitted for both. The ``user`` association
762
takes precedence, and None is applied to ``user_id``, causing a failure.
763
764
Most :meth:`~.Session.merge` issues can be examined by first checking -
765
is the object prematurely in the session ?
766
767
.. sourcecode:: python+sql
768
769
>>> a1 = Address(id=existing_a1, user_id=user.id)
770
>>> assert a1 not in session
771
>>> a1 = session.merge(a1)
772
773
Or is there state on the object that we don't want ? Examining ``__dict__``
774
is a quick way to check::
775
776
>>> a1 = Address(id=existing_a1, user_id=user.id)
777
>>> a1.user
778
>>> a1.__dict__
779
{'_sa_instance_state': <sqlalchemy.orm.state.InstanceState object at 0x1298d10>,
780
'user_id': 1,
781
'id': 1,
782
'user': None}
783
>>> # we don't want user=None merged, remove it
784
>>> del a1.user
785
>>> a1 = session.merge(a1)
786
>>> # success
787
>>> session.commit()
788
789
Deleting
790
--------
791
792
The :meth:`~.Session.delete` method places an instance
793
into the Session's list of objects to be marked as deleted::
794
795
# mark two objects to be deleted
796
session.delete(obj1)
797
session.delete(obj2)
798
799
# commit (or flush)
800
session.commit()
801
802
.. _session_deleting_from_collections:
803
804
Deleting from Collections
805
~~~~~~~~~~~~~~~~~~~~~~~~~~
806
807
A common confusion that arises regarding :meth:`~.Session.delete` is when
808
objects which are members of a collection are being deleted. While the
809
collection member is marked for deletion from the database, this does not
810
impact the collection itself in memory until the collection is expired.
811
Below, we illustrate that even after an ``Address`` object is marked
812
for deletion, it's still present in the collection associated with the
813
parent ``User``, even after a flush::
814
815
>>> address = user.addresses[1]
816
>>> session.delete(address)
817
>>> session.flush()
818
>>> address in user.addresses
819
True
820
821
When the above session is committed, all attributes are expired. The next
822
access of ``user.addresses`` will re-load the collection, revealing the
823
desired state::
824
825
>>> session.commit()
826
>>> address in user.addresses
827
False
828
829
The usual practice of deleting items within collections is to forego the usage
830
of :meth:`~.Session.delete` directly, and instead use cascade behavior to
831
automatically invoke the deletion as a result of removing the object from
832
the parent collection. The ``delete-orphan`` cascade accomplishes this,
833
as illustrated in the example below::
834
835
mapper(User, users_table, properties={
836
'addresses':relationship(Address, cascade="all, delete, delete-orphan")
837
})
838
del user.addresses[1]
839
session.flush()
840
841
Where above, upon removing the ``Address`` object from the ``User.addresses``
842
collection, the ``delete-orphan`` cascade has the effect of marking the ``Address``
843
object for deletion in the same way as passing it to :meth:`~.Session.delete`.
844
845
See also :ref:`unitofwork_cascades` for detail on cascades.
846
847
Deleting based on Filter Criterion
848
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
849
850
The caveat with ``Session.delete()`` is that you need to have an object handy
851
already in order to delete. The Query includes a
852
:func:`~sqlalchemy.orm.query.Query.delete` method which deletes based on
853
filtering criteria::
854
855
session.query(User).filter(User.id==7).delete()
856
857
The ``Query.delete()`` method includes functionality to "expire" objects
858
already in the session which match the criteria. However it does have some
859
caveats, including that "delete" and "delete-orphan" cascades won't be fully
860
expressed for collections which are already loaded. See the API docs for
861
:meth:`~sqlalchemy.orm.query.Query.delete` for more details.
862
863
.. _session_flushing:
864
865
Flushing
866
--------
867
868
When the :class:`~sqlalchemy.orm.session.Session` is used with its default
869
configuration, the flush step is nearly always done transparently.
870
Specifically, the flush occurs before any individual
871
:class:`~sqlalchemy.orm.query.Query` is issued, as well as within the
872
:meth:`~.Session.commit` call before the transaction is
873
committed. It also occurs before a SAVEPOINT is issued when
874
:meth:`~.Session.begin_nested` is used.
875
876
Regardless of the autoflush setting, a flush can always be forced by issuing
877
:meth:`~.Session.flush`::
878
879
session.flush()
880
881
The "flush-on-Query" aspect of the behavior can be disabled by constructing
882
:class:`.sessionmaker` with the flag ``autoflush=False``::
883
884
Session = sessionmaker(autoflush=False)
885
886
Additionally, autoflush can be temporarily disabled by setting the
887
``autoflush`` flag at any time::
888
889
mysession = Session()
890
mysession.autoflush = False
891
892
Some autoflush-disable recipes are available at `DisableAutoFlush
893
<http://www.sqlalchemy.org/trac/wiki/UsageRecipes/DisableAutoflush>`_.
894
895
The flush process *always* occurs within a transaction, even if the
896
:class:`~sqlalchemy.orm.session.Session` has been configured with
897
``autocommit=True``, a setting that disables the session's persistent
898
transactional state. If no transaction is present,
899
:meth:`~.Session.flush` creates its own transaction and
900
commits it. Any failures during flush will always result in a rollback of
901
whatever transaction is present. If the Session is not in ``autocommit=True``
902
mode, an explicit call to :meth:`~.Session.rollback` is
903
required after a flush fails, even though the underlying transaction will have
904
been rolled back already - this is so that the overall nesting pattern of
905
so-called "subtransactions" is consistently maintained.
906
907
.. _session_committing:
908
909
Committing
910
----------
911
912
:meth:`~.Session.commit` is used to commit the current
913
transaction. It always issues :meth:`~.Session.flush`
914
beforehand to flush any remaining state to the database; this is independent
915
of the "autoflush" setting. If no transaction is present, it raises an error.
916
Note that the default behavior of the :class:`~sqlalchemy.orm.session.Session`
917
is that a "transaction" is always present; this behavior can be disabled by
918
setting ``autocommit=True``. In autocommit mode, a transaction can be
919
initiated by calling the :meth:`~.Session.begin` method.
920
921
.. note::
922
923
The term "transaction" here refers to a transactional
924
construct within the :class:`.Session` itself which may be
925
maintaining zero or more actual database (DBAPI) transactions. An individual
926
DBAPI connection begins participation in the "transaction" as it is first
927
used to execute a SQL statement, then remains present until the session-level
928
"transaction" is completed. See :ref:`unitofwork_transaction` for
929
further detail.
930
931
Another behavior of :meth:`~.Session.commit` is that by
932
default it expires the state of all instances present after the commit is
933
complete. This is so that when the instances are next accessed, either through
934
attribute access or by them being present in a
935
:class:`~sqlalchemy.orm.query.Query` result set, they receive the most recent
936
state. To disable this behavior, configure
937
:class:`.sessionmaker` with ``expire_on_commit=False``.
938
939
Normally, instances loaded into the :class:`~sqlalchemy.orm.session.Session`
940
are never changed by subsequent queries; the assumption is that the current
941
transaction is isolated so the state most recently loaded is correct as long
942
as the transaction continues. Setting ``autocommit=True`` works against this
943
model to some degree since the :class:`~sqlalchemy.orm.session.Session`
944
behaves in exactly the same way with regard to attribute state, except no
945
transaction is present.
946
947
.. _session_rollback:
948
949
Rolling Back
950
------------
951
952
:meth:`~.Session.rollback` rolls back the current
953
transaction. With a default configured session, the post-rollback state of the
954
session is as follows:
955
956
* All transactions are rolled back and all connections returned to the
957
connection pool, unless the Session was bound directly to a Connection, in
958
which case the connection is still maintained (but still rolled back).
959
* Objects which were initially in the *pending* state when they were added
960
to the :class:`~sqlalchemy.orm.session.Session` within the lifespan of the
961
transaction are expunged, corresponding to their INSERT statement being
962
rolled back. The state of their attributes remains unchanged.
963
* Objects which were marked as *deleted* within the lifespan of the
964
transaction are promoted back to the *persistent* state, corresponding to
965
their DELETE statement being rolled back. Note that if those objects were
966
first *pending* within the transaction, that operation takes precedence
967
instead.
968
* All objects not expunged are fully expired.
969
970
With that state understood, the :class:`~sqlalchemy.orm.session.Session` may
971
safely continue usage after a rollback occurs.
972
973
When a :meth:`~.Session.flush` fails, typically for
974
reasons like primary key, foreign key, or "not nullable" constraint
975
violations, a :meth:`~.Session.rollback` is issued
976
automatically (it's currently not possible for a flush to continue after a
977
partial failure). However, the flush process always uses its own transactional
978
demarcator called a *subtransaction*, which is described more fully in the
979
docstrings for :class:`~sqlalchemy.orm.session.Session`. What it means here is
980
that even though the database transaction has been rolled back, the end user
981
must still issue :meth:`~.Session.rollback` to fully
982
reset the state of the :class:`~sqlalchemy.orm.session.Session`.
983
984
Expunging
985
---------
986
987
Expunge removes an object from the Session, sending persistent instances to
988
the detached state, and pending instances to the transient state:
989
990
.. sourcecode:: python+sql
991
992
session.expunge(obj1)
993
994
To remove all items, call :meth:`~.Session.expunge_all`
995
(this method was formerly known as ``clear()``).
996
997
Closing
998
-------
999
1000
The :meth:`~.Session.close` method issues a
1001
:meth:`~.Session.expunge_all`, and :term:`releases` any
1002
transactional/connection resources. When connections are returned to the
1003
connection pool, transactional state is rolled back as well.
1004
1005
.. _session_expire:
1006
1007
Refreshing / Expiring
1008
---------------------
1009
1010
:term:`Expiring` means that the database-persisted data held inside a series
1011
of object attributes is erased, in such a way that when those attributes
1012
are next accessed, a SQL query is emitted which will refresh that data from
1013
the database.
1014
1015
When we talk about expiration of data we are usually talking about an object
1016
that is in the :term:`persistent` state. For example, if we load an object
1017
as follows::
1018
1019
user = session.query(User).filter_by(name='user1').first()
1020
1021
The above ``User`` object is persistent, and has a series of attributes
1022
present; if we were to look inside its ``__dict__``, we'd see that state
1023
loaded::
1024
1025
>>> user.__dict__
1026
{
1027
'id': 1, 'name': u'user1',
1028
'_sa_instance_state': <...>,
1029
}
1030
1031
where ``id`` and ``name`` refer to those columns in the database.
1032
``_sa_instance_state`` is a non-database-persisted value used by SQLAlchemy
1033
internally (it refers to the :class:`.InstanceState` for the instance.
1034
While not directly relevant to this section, if we want to get at it,
1035
we should use the :func:`.inspect` function to access it).
1036
1037
At this point, the state in our ``User`` object matches that of the loaded
1038
database row. But upon expiring the object using a method such as
1039
:meth:`.Session.expire`, we see that the state is removed::
1040
1041
>>> session.expire(user)
1042
>>> user.__dict__
1043
{'_sa_instance_state': <...>}
1044
1045
We see that while the internal "state" still hangs around, the values which
1046
correspond to the ``id`` and ``name`` columns are gone. If we were to access
1047
one of these columns and are watching SQL, we'd see this:
1048
1049
.. sourcecode:: python+sql
1050
1051
>>> print(user.name)
1052
{opensql}SELECT user.id AS user_id, user.name AS user_name
1053
FROM user
1054
WHERE user.id = ?
1055
(1,)
1056
{stop}user1
1057
1058
Above, upon accessing the expired attribute ``user.name``, the ORM initiated
1059
a :term:`lazy load` to retrieve the most recent state from the database,
1060
by emitting a SELECT for the user row to which this user refers. Afterwards,
1061
the ``__dict__`` is again populated::
1062
1063
>>> user.__dict__
1064
{
1065
'id': 1, 'name': u'user1',
1066
'_sa_instance_state': <...>,
1067
}
1068
1069
.. note:: While we are peeking inside of ``__dict__`` in order to see a bit
1070
of what SQLAlchemy does with object attributes, we **should not modify**
1071
the contents of ``__dict__`` directly, at least as far as those attributes
1072
which the SQLAlchemy ORM is maintaining (other attributes outside of SQLA's
1073
realm are fine). This is because SQLAlchemy uses :term:`descriptors` in
1074
order to track the changes we make to an object, and when we modify ``__dict__``
1075
directly, the ORM won't be able to track that we changed something.
1076
1077
Another key behavior of both :meth:`~.Session.expire` and :meth:`~.Session.refresh`
1078
is that all un-flushed changes on an object are discarded. That is,
1079
if we were to modify an attribute on our ``User``::
1080
1081
>>> user.name = 'user2'
1082
1083
but then we call :meth:`~.Session.expire` without first calling :meth:`~.Session.flush`,
1084
our pending value of ``'user2'`` is discarded::
1085
1086
>>> session.expire(user)
1087
>>> user.name
1088
'user1'
1089
1090
The :meth:`~.Session.expire` method can be used to mark as "expired" all ORM-mapped
1091
attributes for an instance::
1092
1093
# expire all ORM-mapped attributes on obj1
1094
session.expire(obj1)
1095
1096
it can also be passed a list of string attribute names, referring to specific
1097
attributes to be marked as expired::
1098
1099
# expire only attributes obj1.attr1, obj1.attr2
1100
session.expire(obj1, ['attr1', 'attr2'])
1101
1102
The :meth:`~.Session.refresh` method has a similar interface, but instead
1103
of expiring, it emits an immediate SELECT for the object's row immediately::
1104
1105
# reload all attributes on obj1
1106
session.refresh(obj1)
1107
1108
:meth:`~.Session.refresh` also accepts a list of string attribute names,
1109
but unlike :meth:`~.Session.expire`, expects at least one name to
1110
be that of a column-mapped attribute::
1111
1112
# reload obj1.attr1, obj1.attr2
1113
session.refresh(obj1, ['attr1', 'attr2'])
1114
1115
The :meth:`.Session.expire_all` method allows us to essentially call
1116
:meth:`.Session.expire` on all objects contained within the :class:`.Session`
1117
at once::
1118
1119
session.expire_all()
1120
1121
What Actually Loads
1122
~~~~~~~~~~~~~~~~~~~
1123
1124
The SELECT statement that's emitted when an object marked with :meth:`~.Session.expire`
1125
or loaded with :meth:`~.Session.refresh` varies based on several factors, including:
1126
1127
* The load of expired attributes is triggered from **column-mapped attributes only**.
1128
While any kind of attribute can be marked as expired, including a
1129
:func:`.relationship` - mapped attribute, accessing an expired :func:`.relationship`
1130
attribute will emit a load only for that attribute, using standard
1131
relationship-oriented lazy loading. Column-oriented attributes, even if
1132
expired, will not load as part of this operation, and instead will load when
1133
any column-oriented attribute is accessed.
1134
1135
* :func:`.relationship`- mapped attributes will not load in response to
1136
expired column-based attributes being accessed.
1137
1138
* Regarding relationships, :meth:`~.Session.refresh` is more restrictive than
1139
:meth:`~.Session.expire` with regards to attributes that aren't column-mapped.
1140
Calling :meth:`.refresh` and passing a list of names that only includes
1141
relationship-mapped attributes will actually raise an error.
1142
In any case, non-eager-loading :func:`.relationship` attributes will not be
1143
included in any refresh operation.
1144
1145
* :func:`.relationship` attributes configured as "eager loading" via the
1146
:paramref:`~.relationship.lazy` parameter will load in the case of
1147
:meth:`~.Session.refresh`, if either no attribute names are specified, or
1148
if their names are inclued in the list of attributes to be
1149
refreshed.
1150
1151
* Attributes that are configured as :func:`.deferred` will not normally load,
1152
during either the expired-attribute load or during a refresh.
1153
An unloaded attribute that's :func:`.deferred` instead loads on its own when directly
1154
accessed, or if part of a "group" of deferred attributes where an unloaded
1155
attribute in that group is accessed.
1156
1157
* For expired attributes that are loaded on access, a joined-inheritance table
1158
mapping will emit a SELECT that typically only includes those tables for which
1159
unloaded attributes are present. The action here is sophisticated enough
1160
to load only the parent or child table, for example, if the subset of columns
1161
that were originally expired encompass only one or the other of those tables.
1162
1163
* When :meth:`~.Session.refresh` is used on a joined-inheritance table mapping,
1164
the SELECT emitted will resemble that of when :meth:`.Session.query` is
1165
used on the target object's class. This is typically all those tables that
1166
are set up as part of the mapping.
1167
1168
1169
When to Expire or Refresh
1170
~~~~~~~~~~~~~~~~~~~~~~~~~~
1171
1172
The :class:`.Session` uses the expiration feature automatically whenever
1173
the transaction referred to by the session ends. Meaning, whenever :meth:`.Session.commit`
1174
or :meth:`.Session.rollback` is called, all objects within the :class:`.Session`
1175
are expired, using a feature equivalent to that of the :meth:`.Session.expire_all`
1176
method. The rationale is that the end of a transaction is a
1177
demarcating point at which there is no more context available in order to know
1178
what the current state of the database is, as any number of other transactions
1179
may be affecting it. Only when a new transaction starts can we again have access
1180
to the current state of the database, at which point any number of changes
1181
may have occurred.
1182
1183
.. sidebar:: Transaction Isolation
1184
1185
Of course, most databases are capable of handling
1186
multiple transactions at once, even involving the same rows of data. When
1187
a relational database handles multiple transactions involving the same
1188
tables or rows, this is when the :term:`isolation` aspect of the database comes
1189
into play. The isolation behavior of different databases varies considerably
1190
and even on a single database can be configured to behave in different ways
1191
(via the so-called :term:`isolation level` setting). In that sense, the :class:`.Session`
1192
can't fully predict when the same SELECT statement, emitted a second time,
1193
will definitely return the data we already have, or will return new data.
1194
So as a best guess, it assumes that within the scope of a transaction, unless
1195
it is known that a SQL expression has been emitted to modify a particular row,
1196
there's no need to refresh a row unless explicitly told to do so.
1197
1198
The :meth:`.Session.expire` and :meth:`.Session.refresh` methods are used in
1199
those cases when one wants to force an object to re-load its data from the
1200
database, in those cases when it is known that the current state of data
1201
is possibly stale. Reasons for this might include:
1202
1203
* some SQL has been emitted within the transaction outside of the
1204
scope of the ORM's object handling, such as if a :meth:`.Table.update` construct
1205
were emitted using the :meth:`.Session.execute` method;
1206
1207
* if the application
1208
is attempting to acquire data that is known to have been modified in a
1209
concurrent transaction, and it is also known that the isolation rules in effect
1210
allow this data to be visible.
1211
1212
The second bullet has the important caveat that "it is also known that the isolation rules in effect
1213
allow this data to be visible." This means that it cannot be assumed that an
1214
UPDATE that happened on another database connection will yet be visible here
1215
locally; in many cases, it will not. This is why if one wishes to use
1216
:meth:`.expire` or :meth:`.refresh` in order to view data between ongoing
1217
transactions, an understanding of the isolation behavior in effect is essential.
1218
1219
.. seealso::
1220
1221
:meth:`.Session.expire`
1222
1223
:meth:`.Session.expire_all`
1224
1225
:meth:`.Session.refresh`
1226
1227
:term:`isolation` - glossary explanation of isolation which includes links
1228
to Wikipedia.
1229
1230
`The SQLAlchemy Session In-Depth <http://techspot.zzzeek.org/2012/11/14/pycon-canada-the-sqlalchemy-session-in-depth/>`_ - a video + slides with an in-depth discussion of the object
1231
lifecycle including the role of data expiration.
1232
1233
1234
Session Attributes
1235
------------------
1236
1237
The :class:`~sqlalchemy.orm.session.Session` itself acts somewhat like a
1238
set-like collection. All items present may be accessed using the iterator
1239
interface::
1240
1241
for obj in session:
1242
print obj
1243
1244
And presence may be tested for using regular "contains" semantics::
1245
1246
if obj in session:
1247
print "Object is present"
1248
1249
The session is also keeping track of all newly created (i.e. pending) objects,
1250
all objects which have had changes since they were last loaded or saved (i.e.
1251
"dirty"), and everything that's been marked as deleted::
1252
1253
# pending objects recently added to the Session
1254
session.new
1255
1256
# persistent objects which currently have changes detected
1257
# (this collection is now created on the fly each time the property is called)
1258
session.dirty
1259
1260
# persistent objects that have been marked as deleted via session.delete(obj)
1261
session.deleted
1262
1263
# dictionary of all persistent objects, keyed on their
1264
# identity key
1265
session.identity_map
1266
1267
(Documentation: :attr:`.Session.new`, :attr:`.Session.dirty`,
1268
:attr:`.Session.deleted`, :attr:`.Session.identity_map`).
1269
1270
Note that objects within the session are by default *weakly referenced*. This
1271
means that when they are dereferenced in the outside application, they fall
1272
out of scope from within the :class:`~sqlalchemy.orm.session.Session` as well
1273
and are subject to garbage collection by the Python interpreter. The
1274
exceptions to this include objects which are pending, objects which are marked
1275
as deleted, or persistent objects which have pending changes on them. After a
1276
full flush, these collections are all empty, and all objects are again weakly
1277
referenced. To disable the weak referencing behavior and force all objects
1278
within the session to remain until explicitly expunged, configure
1279
:class:`.sessionmaker` with the ``weak_identity_map=False``
1280
setting.
1281
1282
.. _unitofwork_cascades:
1283
1284
Cascades
1285
========
1286
1287
Mappers support the concept of configurable :term:`cascade` behavior on
1288
:func:`~sqlalchemy.orm.relationship` constructs. This refers
1289
to how operations performed on a "parent" object relative to a
1290
particular :class:`.Session` should be propagated to items
1291
referred to by that relationship (e.g. "child" objects), and is
1292
affected by the :paramref:`.relationship.cascade` option.
1293
1294
The default behavior of cascade is limited to cascades of the
1295
so-called :ref:`cascade_save_update` and :ref:`cascade_merge` settings.
1296
The typical "alternative" setting for cascade is to add
1297
the :ref:`cascade_delete` and :ref:`cascade_delete_orphan` options;
1298
these settings are appropriate for related objects which only exist as
1299
long as they are attached to their parent, and are otherwise deleted.
1300
1301
Cascade behavior is configured using the by changing the
1302
:paramref:`~.relationship.cascade` option on
1303
:func:`~sqlalchemy.orm.relationship`::
1304
1305
class Order(Base):
1306
__tablename__ = 'order'
1307
1308
items = relationship("Item", cascade="all, delete-orphan")
1309
customer = relationship("User", cascade="save-update")
1310
1311
To set cascades on a backref, the same flag can be used with the
1312
:func:`~.sqlalchemy.orm.backref` function, which ultimately feeds
1313
its arguments back into :func:`~sqlalchemy.orm.relationship`::
1314
1315
class Item(Base):
1316
__tablename__ = 'item'
1317
1318
order = relationship("Order",
1319
backref=backref("items", cascade="all, delete-orphan")
1320
)
1321
1322
.. sidebar:: The Origins of Cascade
1323
1324
SQLAlchemy's notion of cascading behavior on relationships,
1325
as well as the options to configure them, are primarily derived
1326
from the similar feature in the Hibernate ORM; Hibernate refers
1327
to "cascade" in a few places such as in
1328
`Example: Parent/Child <https://docs.jboss.org/hibernate/orm/3.3/reference/en-US/html/example-parentchild.html>`_.
1329
If cascades are confusing, we'll refer to their conclusion,
1330
stating "The sections we have just covered can be a bit confusing.
1331
However, in practice, it all works out nicely."
1332
1333
The default value of :paramref:`~.relationship.cascade` is ``save-update, merge``.
1334
The typical alternative setting for this parameter is either
1335
``all`` or more commonly ``all, delete-orphan``. The ``all`` symbol
1336
is a synonym for ``save-update, merge, refresh-expire, expunge, delete``,
1337
and using it in conjunction with ``delete-orphan`` indicates that the child
1338
object should follow along with its parent in all cases, and be deleted once
1339
it is no longer associated with that parent.
1340
1341
The list of available values which can be specified for
1342
the :paramref:`~.relationship.cascade` parameter are described in the following subsections.
1343
1344
.. _cascade_save_update:
1345
1346
save-update
1347
-----------
1348
1349
``save-update`` cacade indicates that when an object is placed into a
1350
:class:`.Session` via :meth:`.Session.add`, all the objects associated
1351
with it via this :func:`.relationship` should also be added to that
1352
same :class:`.Session`. Suppose we have an object ``user1`` with two
1353
related objects ``address1``, ``address2``::
1354
1355
>>> user1 = User()
1356
>>> address1, address2 = Address(), Address()
1357
>>> user1.addresses = [address1, address2]
1358
1359
If we add ``user1`` to a :class:`.Session`, it will also add
1360
``address1``, ``address2`` implicitly::
1361
1362
>>> sess = Session()
1363
>>> sess.add(user1)
1364
>>> address1 in sess
1365
True
1366
1367
``save-update`` cascade also affects attribute operations for objects
1368
that are already present in a :class:`.Session`. If we add a third
1369
object, ``address3`` to the ``user1.addresses`` collection, it
1370
becomes part of the state of that :class:`.Session`::
1371
1372
>>> address3 = Address()
1373
>>> user1.append(address3)
1374
>>> address3 in sess
1375
>>> True
1376
1377
``save-update`` has the possibly surprising behavior which is that
1378
persistent objects which were *removed* from a collection
1379
or in some cases a scalar attribute
1380
may also be pulled into the :class:`.Session` of a parent object; this is
1381
so that the flush process may handle that related object appropriately.
1382
This case can usually only arise if an object is removed from one :class:`.Session`
1383
and added to another::
1384
1385
>>> user1 = sess1.query(User).filter_by(id=1).first()
1386
>>> address1 = user1.addresses[0]
1387
>>> sess1.close() # user1, address1 no longer associated with sess1
1388
>>> user1.addresses.remove(address1) # address1 no longer associated with user1
1389
>>> sess2 = Session()
1390
>>> sess2.add(user1) # ... but it still gets added to the new session,
1391
>>> address1 in sess2 # because it's still "pending" for flush
1392
True
1393
1394
The ``save-update`` cascade is on by default, and is typically taken
1395
for granted; it simplifies code by allowing a single call to
1396
:meth:`.Session.add` to register an entire structure of objects within
1397
that :class:`.Session` at once. While it can be disabled, there
1398
is usually not a need to do so.
1399
1400
One case where ``save-update`` cascade does sometimes get in the way is in that
1401
it takes place in both directions for bi-directional relationships, e.g.
1402
backrefs, meaning that the association of a child object with a particular parent
1403
can have the effect of the parent object being implicitly associated with that
1404
child object's :class:`.Session`; this pattern, as well as how to modify its
1405
behavior using the :paramref:`~.relationship.cascade_backrefs` flag,
1406
is discussed in the section :ref:`backref_cascade`.
1407
1408
.. _cascade_delete:
1409
1410
delete
1411
------
1412
1413
The ``delete`` cascade indicates that when a "parent" object
1414
is marked for deletion, its related "child" objects should also be marked
1415
for deletion. If for example we we have a relationship ``User.addresses``
1416
with ``delete`` cascade configured::
1417
1418
class User(Base):
1419
# ...
1420
1421
addresses = relationship("Address", cascade="save-update, merge, delete")
1422
1423
If using the above mapping, we have a ``User`` object and two
1424
related ``Address`` objects::
1425
1426
>>> user1 = sess.query(User).filter_by(id=1).first()
1427
>>> address1, address2 = user1.addresses
1428
1429
If we mark ``user1`` for deletion, after the flush operation proceeds,
1430
``address1`` and ``address2`` will also be deleted:
1431
1432
.. sourcecode:: python+sql
1433
1434
>>> sess.delete(user1)
1435
>>> sess.commit()
1436
{opensql}DELETE FROM address WHERE address.id = ?
1437
((1,), (2,))
1438
DELETE FROM user WHERE user.id = ?
1439
(1,)
1440
COMMIT
1441
1442
Alternatively, if our ``User.addresses`` relationship does *not* have
1443
``delete`` cascade, SQLAlchemy's default behavior is to instead de-associate
1444
``address1`` and ``address2`` from ``user1`` by setting their foreign key
1445
reference to ``NULL``. Using a mapping as follows::
1446
1447
class User(Base):
1448
# ...
1449
1450
addresses = relationship("Address")
1451
1452
Upon deletion of a parent ``User`` object, the rows in ``address`` are not
1453
deleted, but are instead de-associated:
1454
1455
.. sourcecode:: python+sql
1456
1457
>>> sess.delete(user1)
1458
>>> sess.commit()
1459
{opensql}UPDATE address SET user_id=? WHERE address.id = ?
1460
(None, 1)
1461
UPDATE address SET user_id=? WHERE address.id = ?
1462
(None, 2)
1463
DELETE FROM user WHERE user.id = ?
1464
(1,)
1465
COMMIT
1466
1467
``delete`` cascade is more often than not used in conjunction with
1468
:ref:`cascade_delete_orphan` cascade, which will emit a DELETE for the related
1469
row if the "child" object is deassociated from the parent. The combination
1470
of ``delete`` and ``delete-orphan`` cascade covers both situations where
1471
SQLAlchemy has to decide between setting a foreign key column to NULL versus
1472
deleting the row entirely.
1473
1474
.. topic:: ORM-level "delete" cascade vs. FOREIGN KEY level "ON DELETE" cascade
1475
1476
The behavior of SQLAlchemy's "delete" cascade has a lot of overlap with the
1477
``ON DELETE CASCADE`` feature of a database foreign key, as well
1478
as with that of the ``ON DELETE SET NULL`` foreign key setting when "delete"
1479
cascade is not specified. Database level "ON DELETE" cascades are specific to the
1480
"FOREIGN KEY" construct of the relational database; SQLAlchemy allows
1481
configuration of these schema-level constructs at the :term:`DDL` level
1482
using options on :class:`.ForeignKeyConstraint` which are described
1483
at :ref:`on_update_on_delete`.
1484
1485
It is important to note the differences between the ORM and the relational
1486
database's notion of "cascade" as well as how they integrate:
1487
1488
* A database level ``ON DELETE`` cascade is configured effectively
1489
on the **many-to-one** side of the relationship; that is, we configure
1490
it relative to the ``FOREIGN KEY`` constraint that is the "many" side
1491
of a relationship. At the ORM level, **this direction is reversed**.
1492
SQLAlchemy handles the deletion of "child" objects relative to a
1493
"parent" from the "parent" side, which means that ``delete`` and
1494
``delete-orphan`` cascade are configured on the **one-to-many**
1495
side.
1496
1497
* Database level foreign keys with no ``ON DELETE`` setting
1498
are often used to **prevent** a parent
1499
row from being removed, as it would necessarily leave an unhandled
1500
related row present. If this behavior is desired in a one-to-many
1501
relationship, SQLAlchemy's default behavior of setting a foreign key
1502
to ``NULL`` can be caught in one of two ways:
1503
1504
* The easiest and most common is just to to set the
1505
foreign-key-holding column to ``NOT NULL`` at the database schema
1506
level. An attempt by SQLAlchemy to set the column to NULL will
1507
fail with a simple NOT NULL constraint exception.
1508
1509
* The other, more special case way is to set the :paramref:`~.relationship.passive_deletes`
1510
flag to the string ``"all"``. This has the effect of entirely
1511
disabling SQLAlchemy's behavior of setting the foreign key column
1512
to NULL, and a DELETE will be emitted for the parent row without
1513
any affect on the child row, even if the child row is present
1514
in memory. This may be desirable in the case when
1515
database-level foreign key triggers, either special ``ON DELETE`` settings
1516
or otherwise, need to be activated in all cases when a parent row is deleted.
1517
1518
* Database level ``ON DELETE`` cascade is **vastly more efficient**
1519
than that of SQLAlchemy. The database can chain a series of cascade
1520
operations across many relationships at once; e.g. if row A is deleted,
1521
all the related rows in table B can be deleted, and all the C rows related
1522
to each of those B rows, and on and on, all within the scope of a single
1523
DELETE statement. SQLAlchemy on the other hand, in order to support
1524
the cascading delete operation fully, has to individually load each
1525
related collection in order to target all rows that then may have further
1526
related collections. That is, SQLAlchemy isn't sophisticated enough
1527
to emit a DELETE for all those related rows at once within this context.
1528
1529
* SQLAlchemy doesn't **need** to be this sophisticated, as we instead provide
1530
smooth integration with the database's own ``ON DELETE`` functionality,
1531
by using the :paramref:`~.relationship.passive_deletes` option in conjunction
1532
with properly configured foreign key constraints. Under this behavior,
1533
SQLAlchemy only emits DELETE for those rows that are already locally
1534
present in the :class:`.Session`; for any collections that are unloaded,
1535
it leaves them to the database to handle, rather than emitting a SELECT
1536
for them. The section :ref:`passive_deletes` provides an example of this use.
1537
1538
* While database-level ``ON DELETE`` functionality works only on the "many"
1539
side of a relationship, SQLAlchemy's "delete" cascade
1540
has **limited** ability to operate in the *reverse* direction as well,
1541
meaning it can be configured on the "many" side to delete an object
1542
on the "one" side when the reference on the "many" side is deleted. However
1543
this can easily result in constraint violations if there are other objects
1544
referring to this "one" side from the "many", so it typically is only
1545
useful when a relationship is in fact a "one to one". The
1546
:paramref:`~.relationship.single_parent` flag should be used to establish
1547
an in-Python assertion for this case.
1548
1549
1550
When using a :func:`.relationship` that also includes a many-to-many
1551
table using the :paramref:`~.relationship.secondary` option, SQLAlchemy's
1552
delete cascade handles the rows in this many-to-many table automatically.
1553
Just like, as described in :ref:`relationships_many_to_many_deletion`,
1554
the addition or removal of an object from a many-to-many collection
1555
results in the INSERT or DELETE of a row in the many-to-many table,
1556
the ``delete`` cascade, when activated as the result of a parent object
1557
delete operation, will DELETE not just the row in the "child" table but also
1558
in the many-to-many table.
1559
1560
.. _cascade_delete_orphan:
1561
1562
delete-orphan
1563
-------------
1564
1565
``delete-orphan`` cascade adds behavior to the ``delete`` cascade,
1566
such that a child object will be marked for deletion when it is
1567
de-associated from the parent, not just when the parent is marked
1568
for deletion. This is a common feature when dealing with a related
1569
object that is "owned" by its parent, with a NOT NULL foreign key,
1570
so that removal of the item from the parent collection results
1571
in its deletion.
1572
1573
``delete-orphan`` cascade implies that each child object can only
1574
have one parent at a time, so is configured in the vast majority of cases
1575
on a one-to-many relationship. Setting it on a many-to-one or
1576
many-to-many relationship is more awkward; for this use case,
1577
SQLAlchemy requires that the :func:`~sqlalchemy.orm.relationship`
1578
be configured with the :paramref:`~.relationship.single_parent` argument,
1579
establishes Python-side validation that ensures the object
1580
is associated with only one parent at a time.
1581
1582
.. _cascade_merge:
1583
1584
merge
1585
-----
1586
1587
``merge`` cascade indicates that the :meth:`.Session.merge`
1588
operation should be propagated from a parent that's the subject
1589
of the :meth:`.Session.merge` call down to referred objects.
1590
This cascade is also on by default.
1591
1592
.. _cascade_refresh_expire:
1593
1594
refresh-expire
1595
--------------
1596
1597
``refresh-expire`` is an uncommon option, indicating that the
1598
:meth:`.Session.expire` operation should be propagated from a parent
1599
down to referred objects. When using :meth:`.Session.refresh`,
1600
the referred objects are expired only, but not actually refreshed.
1601
1602
.. _cascade_expunge:
1603
1604
expunge
1605
-------
1606
1607
``expunge`` cascade indicates that when the parent object is removed
1608
from the :class:`.Session` using :meth:`.Session.expunge`, the
1609
operation should be propagated down to referred objects.
1610
1611
.. _backref_cascade:
1612
1613
Controlling Cascade on Backrefs
1614
-------------------------------
1615
1616
The :ref:`cascade_save_update` cascade by default takes place on attribute change events
1617
emitted from backrefs. This is probably a confusing statement more
1618
easily described through demonstration; it means that, given a mapping such as this::
1619
1620
mapper(Order, order_table, properties={
1621
'items' : relationship(Item, backref='order')
1622
})
1623
1624
If an ``Order`` is already in the session, and is assigned to the ``order``
1625
attribute of an ``Item``, the backref appends the ``Order`` to the ``items``
1626
collection of that ``Order``, resulting in the ``save-update`` cascade taking
1627
place::
1628
1629
>>> o1 = Order()
1630
>>> session.add(o1)
1631
>>> o1 in session
1632
True
1633
1634
>>> i1 = Item()
1635
>>> i1.order = o1
1636
>>> i1 in o1.items
1637
True
1638
>>> i1 in session
1639
True
1640
1641
This behavior can be disabled using the :paramref:`~.relationship.cascade_backrefs` flag::
1642
1643
mapper(Order, order_table, properties={
1644
'items' : relationship(Item, backref='order',
1645
cascade_backrefs=False)
1646
})
1647
1648
So above, the assignment of ``i1.order = o1`` will append ``i1`` to the ``items``
1649
collection of ``o1``, but will not add ``i1`` to the session. You can, of
1650
course, :meth:`~.Session.add` ``i1`` to the session at a later point. This
1651
option may be helpful for situations where an object needs to be kept out of a
1652
session until it's construction is completed, but still needs to be given
1653
associations to objects which are already persistent in the target session.
1654
1655
1656
.. _unitofwork_transaction:
1657
1658
Managing Transactions
1659
=====================
1660
1661
A newly constructed :class:`.Session` may be said to be in the "begin" state.
1662
In this state, the :class:`.Session` has not established any connection or
1663
transactional state with any of the :class:`.Engine` objects that may be associated
1664
with it.
1665
1666
The :class:`.Session` then receives requests to operate upon a database connection.
1667
Typically, this means it is called upon to execute SQL statements using a particular
1668
:class:`.Engine`, which may be via :meth:`.Session.query`, :meth:`.Session.execute`,
1669
or within a flush operation of pending data, which occurs when such state exists
1670
and :meth:`.Session.commit` or :meth:`.Session.flush` is called.
1671
1672
As these requests are received, each new :class:`.Engine` encountered is associated
1673
with an ongoing transactional state maintained by the :class:`.Session`.
1674
When the first :class:`.Engine` is operated upon, the :class:`.Session` can be said
1675
to have left the "begin" state and entered "transactional" state. For each
1676
:class:`.Engine` encountered, a :class:`.Connection` is associated with it,
1677
which is acquired via the :meth:`.Engine.contextual_connect` method. If a
1678
:class:`.Connection` was directly associated with the :class:`.Session` (see :ref:`session_external_transaction`
1679
for an example of this), it is
1680
added to the transactional state directly.
1681
1682
For each :class:`.Connection`, the :class:`.Session` also maintains a :class:`.Transaction` object,
1683
which is acquired by calling :meth:`.Connection.begin` on each :class:`.Connection`,
1684
or if the :class:`.Session`
1685
object has been established using the flag ``twophase=True``, a :class:`.TwoPhaseTransaction`
1686
object acquired via :meth:`.Connection.begin_twophase`. These transactions are all committed or
1687
rolled back corresponding to the invocation of the
1688
:meth:`.Session.commit` and :meth:`.Session.rollback` methods. A commit operation will
1689
also call the :meth:`.TwoPhaseTransaction.prepare` method on all transactions if applicable.
1690
1691
When the transactional state is completed after a rollback or commit, the :class:`.Session`
1692
:term:`releases` all :class:`.Transaction` and :class:`.Connection` resources,
1693
and goes back to the "begin" state, which
1694
will again invoke new :class:`.Connection` and :class:`.Transaction` objects as new
1695
requests to emit SQL statements are received.
1696
1697
The example below illustrates this lifecycle::
1698
1699
engine = create_engine("...")
1700
Session = sessionmaker(bind=engine)
1701
1702
# new session. no connections are in use.
1703
session = Session()
1704
try:
1705
# first query. a Connection is acquired
1706
# from the Engine, and a Transaction
1707
# started.
1708
item1 = session.query(Item).get(1)
1709
1710
# second query. the same Connection/Transaction
1711
# are used.
1712
item2 = session.query(Item).get(2)
1713
1714
# pending changes are created.
1715
item1.foo = 'bar'
1716
item2.bar = 'foo'
1717
1718
# commit. The pending changes above
1719
# are flushed via flush(), the Transaction
1720
# is committed, the Connection object closed
1721
# and discarded, the underlying DBAPI connection
1722
# returned to the connection pool.
1723
session.commit()
1724
except:
1725
# on rollback, the same closure of state
1726
# as that of commit proceeds.
1727
session.rollback()
1728
raise
1729
1730
.. _session_begin_nested:
1731
1732
Using SAVEPOINT
1733
---------------
1734
1735
SAVEPOINT transactions, if supported by the underlying engine, may be
1736
delineated using the :meth:`~.Session.begin_nested`
1737
method::
1738
1739
Session = sessionmaker()
1740
session = Session()
1741
session.add(u1)
1742
session.add(u2)
1743
1744
session.begin_nested() # establish a savepoint
1745
session.add(u3)
1746
session.rollback() # rolls back u3, keeps u1 and u2
1747
1748
session.commit() # commits u1 and u2
1749
1750
:meth:`~.Session.begin_nested` may be called any number
1751
of times, which will issue a new SAVEPOINT with a unique identifier for each
1752
call. For each :meth:`~.Session.begin_nested` call, a
1753
corresponding :meth:`~.Session.rollback` or
1754
:meth:`~.Session.commit` must be issued.
1755
1756
When :meth:`~.Session.begin_nested` is called, a
1757
:meth:`~.Session.flush` is unconditionally issued
1758
(regardless of the ``autoflush`` setting). This is so that when a
1759
:meth:`~.Session.rollback` occurs, the full state of the
1760
session is expired, thus causing all subsequent attribute/instance access to
1761
reference the full state of the :class:`~sqlalchemy.orm.session.Session` right
1762
before :meth:`~.Session.begin_nested` was called.
1763
1764
:meth:`~.Session.begin_nested`, in the same manner as the less often
1765
used :meth:`~.Session.begin` method, returns a transactional object
1766
which also works as a context manager.
1767
It can be succinctly used around individual record inserts in order to catch
1768
things like unique constraint exceptions::
1769
1770
for record in records:
1771
try:
1772
with session.begin_nested():
1773
session.merge(record)
1774
except:
1775
print "Skipped record %s" % record
1776
session.commit()
1777
1778
.. _session_autocommit:
1779
1780
Autocommit Mode
1781
---------------
1782
1783
The example of :class:`.Session` transaction lifecycle illustrated at
1784
the start of :ref:`unitofwork_transaction` applies to a :class:`.Session` configured in the
1785
default mode of ``autocommit=False``. Constructing a :class:`.Session`
1786
with ``autocommit=True`` produces a :class:`.Session` placed into "autocommit" mode, where each SQL statement
1787
invoked by a :meth:`.Session.query` or :meth:`.Session.execute` occurs
1788
using a new connection from the connection pool, discarding it after
1789
results have been iterated. The :meth:`.Session.flush` operation
1790
still occurs within the scope of a single transaction, though this transaction
1791
is closed out after the :meth:`.Session.flush` operation completes.
1792
1793
.. warning::
1794
1795
"autocommit" mode should **not be considered for general use**.
1796
If used, it should always be combined with the usage of
1797
:meth:`.Session.begin` and :meth:`.Session.commit`, to ensure
1798
a transaction demarcation.
1799
1800
Executing queries outside of a demarcated transaction is a legacy mode
1801
of usage, and can in some cases lead to concurrent connection
1802
checkouts.
1803
1804
In the absense of a demarcated transaction, the :class:`.Session`
1805
cannot make appropriate decisions as to when autoflush should
1806
occur nor when auto-expiration should occur, so these features
1807
should be disabled with ``autoflush=False, expire_on_commit=False``.
1808
1809
Modern usage of "autocommit" is for framework integrations that need to control
1810
specifically when the "begin" state occurs. A session which is configured with
1811
``autocommit=True`` may be placed into the "begin" state using the
1812
:meth:`.Session.begin` method.
1813
After the cycle completes upon :meth:`.Session.commit` or :meth:`.Session.rollback`,
1814
connection and transaction resources are :term:`released` and the :class:`.Session`
1815
goes back into "autocommit" mode, until :meth:`.Session.begin` is called again::
1816
1817
Session = sessionmaker(bind=engine, autocommit=True)
1818
session = Session()
1819
session.begin()
1820
try:
1821
item1 = session.query(Item).get(1)
1822
item2 = session.query(Item).get(2)
1823
item1.foo = 'bar'
1824
item2.bar = 'foo'
1825
session.commit()
1826
except:
1827
session.rollback()
1828
raise
1829
1830
The :meth:`.Session.begin` method also returns a transactional token which is
1831
compatible with the Python 2.6 ``with`` statement::
1832
1833
Session = sessionmaker(bind=engine, autocommit=True)
1834
session = Session()
1835
with session.begin():
1836
item1 = session.query(Item).get(1)
1837
item2 = session.query(Item).get(2)
1838
item1.foo = 'bar'
1839
item2.bar = 'foo'
1840
1841
.. _session_subtransactions:
1842
1843
Using Subtransactions with Autocommit
1844
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
1845
1846
A subtransaction indicates usage of the :meth:`.Session.begin` method in conjunction with
1847
the ``subtransactions=True`` flag. This produces a non-transactional, delimiting construct that
1848
allows nesting of calls to :meth:`~.Session.begin` and :meth:`~.Session.commit`.
1849
It's purpose is to allow the construction of code that can function within a transaction
1850
both independently of any external code that starts a transaction,
1851
as well as within a block that has already demarcated a transaction.
1852
1853
``subtransactions=True`` is generally only useful in conjunction with
1854
autocommit, and is equivalent to the pattern described at :ref:`connections_nested_transactions`,
1855
where any number of functions can call :meth:`.Connection.begin` and :meth:`.Transaction.commit`
1856
as though they are the initiator of the transaction, but in fact may be participating
1857
in an already ongoing transaction::
1858
1859
# method_a starts a transaction and calls method_b
1860
def method_a(session):
1861
session.begin(subtransactions=True)
1862
try:
1863
method_b(session)
1864
session.commit() # transaction is committed here
1865
except:
1866
session.rollback() # rolls back the transaction
1867
raise
1868
1869
# method_b also starts a transaction, but when
1870
# called from method_a participates in the ongoing
1871
# transaction.
1872
def method_b(session):
1873
session.begin(subtransactions=True)
1874
try:
1875
session.add(SomeObject('bat', 'lala'))
1876
session.commit() # transaction is not committed yet
1877
except:
1878
session.rollback() # rolls back the transaction, in this case
1879
# the one that was initiated in method_a().
1880
raise
1881
1882
# create a Session and call method_a
1883
session = Session(autocommit=True)
1884
method_a(session)
1885
session.close()
1886
1887
Subtransactions are used by the :meth:`.Session.flush` process to ensure that the
1888
flush operation takes place within a transaction, regardless of autocommit. When
1889
autocommit is disabled, it is still useful in that it forces the :class:`.Session`
1890
into a "pending rollback" state, as a failed flush cannot be resumed in mid-operation,
1891
where the end user still maintains the "scope" of the transaction overall.
1892
1893
.. _session_twophase:
1894
1895
Enabling Two-Phase Commit
1896
-------------------------
1897
1898
For backends which support two-phase operaration (currently MySQL and
1899
PostgreSQL), the session can be instructed to use two-phase commit semantics.
1900
This will coordinate the committing of transactions across databases so that
1901
the transaction is either committed or rolled back in all databases. You can
1902
also :meth:`~.Session.prepare` the session for
1903
interacting with transactions not managed by SQLAlchemy. To use two phase
1904
transactions set the flag ``twophase=True`` on the session::
1905
1906
engine1 = create_engine('postgresql://db1')
1907
engine2 = create_engine('postgresql://db2')
1908
1909
Session = sessionmaker(twophase=True)
1910
1911
# bind User operations to engine 1, Account operations to engine 2
1912
Session.configure(binds={User:engine1, Account:engine2})
1913
1914
session = Session()
1915
1916
# .... work with accounts and users
1917
1918
# commit. session will issue a flush to all DBs, and a prepare step to all DBs,
1919
# before committing both transactions
1920
session.commit()
1921
1922
Embedding SQL Insert/Update Expressions into a Flush
1923
=====================================================
1924
1925
This feature allows the value of a database column to be set to a SQL
1926
expression instead of a literal value. It's especially useful for atomic
1927
updates, calling stored procedures, etc. All you do is assign an expression to
1928
an attribute::
1929
1930
class SomeClass(object):
1931
pass
1932
mapper(SomeClass, some_table)
1933
1934
someobject = session.query(SomeClass).get(5)
1935
1936
# set 'value' attribute to a SQL expression adding one
1937
someobject.value = some_table.c.value + 1
1938
1939
# issues "UPDATE some_table SET value=value+1"
1940
session.commit()
1941
1942
This technique works both for INSERT and UPDATE statements. After the
1943
flush/commit operation, the ``value`` attribute on ``someobject`` above is
1944
expired, so that when next accessed the newly generated value will be loaded
1945
from the database.
1946
1947
.. _session_sql_expressions:
1948
1949
Using SQL Expressions with Sessions
1950
====================================
1951
1952
SQL expressions and strings can be executed via the
1953
:class:`~sqlalchemy.orm.session.Session` within its transactional context.
1954
This is most easily accomplished using the
1955
:meth:`~.Session.execute` method, which returns a
1956
:class:`~sqlalchemy.engine.ResultProxy` in the same manner as an
1957
:class:`~sqlalchemy.engine.Engine` or
1958
:class:`~sqlalchemy.engine.Connection`::
1959
1960
Session = sessionmaker(bind=engine)
1961
session = Session()
1962
1963
# execute a string statement
1964
result = session.execute("select * from table where id=:id", {'id':7})
1965
1966
# execute a SQL expression construct
1967
result = session.execute(select([mytable]).where(mytable.c.id==7))
1968
1969
The current :class:`~sqlalchemy.engine.Connection` held by the
1970
:class:`~sqlalchemy.orm.session.Session` is accessible using the
1971
:meth:`~.Session.connection` method::
1972
1973
connection = session.connection()
1974
1975
The examples above deal with a :class:`~sqlalchemy.orm.session.Session` that's
1976
bound to a single :class:`~sqlalchemy.engine.Engine` or
1977
:class:`~sqlalchemy.engine.Connection`. To execute statements using a
1978
:class:`~sqlalchemy.orm.session.Session` which is bound either to multiple
1979
engines, or none at all (i.e. relies upon bound metadata), both
1980
:meth:`~.Session.execute` and
1981
:meth:`~.Session.connection` accept a ``mapper`` keyword
1982
argument, which is passed a mapped class or
1983
:class:`~sqlalchemy.orm.mapper.Mapper` instance, which is used to locate the
1984
proper context for the desired engine::
1985
1986
Session = sessionmaker()
1987
session = Session()
1988
1989
# need to specify mapper or class when executing
1990
result = session.execute("select * from table where id=:id", {'id':7}, mapper=MyMappedClass)
1991
1992
result = session.execute(select([mytable], mytable.c.id==7), mapper=MyMappedClass)
1993
1994
connection = session.connection(MyMappedClass)
1995
1996
.. _session_external_transaction:
1997
1998
Joining a Session into an External Transaction (such as for test suites)
1999
========================================================================
2000
2001
If a :class:`.Connection` is being used which is already in a transactional
2002
state (i.e. has a :class:`.Transaction` established), a :class:`.Session` can
2003
be made to participate within that transaction by just binding the
2004
:class:`.Session` to that :class:`.Connection`. The usual rationale for this
2005
is a test suite that allows ORM code to work freely with a :class:`.Session`,
2006
including the ability to call :meth:`.Session.commit`, where afterwards the
2007
entire database interaction is rolled back::
2008
2009
from sqlalchemy.orm import sessionmaker
2010
from sqlalchemy import create_engine
2011
from unittest import TestCase
2012
2013
# global application scope. create Session class, engine
2014
Session = sessionmaker()
2015
2016
engine = create_engine('postgresql://...')
2017
2018
class SomeTest(TestCase):
2019
def setUp(self):
2020
# connect to the database
2021
self.connection = engine.connect()
2022
2023
# begin a non-ORM transaction
2024
self.trans = connection.begin()
2025
2026
# bind an individual Session to the connection
2027
self.session = Session(bind=self.connection)
2028
2029
def test_something(self):
2030
# use the session in tests.
2031
2032
self.session.add(Foo())
2033
self.session.commit()
2034
2035
def tearDown(self):
2036
self.session.close()
2037
2038
# rollback - everything that happened with the
2039
# Session above (including calls to commit())
2040
# is rolled back.
2041
self.trans.rollback()
2042
2043
# return connection to the Engine
2044
self.connection.close()
2045
2046
Above, we issue :meth:`.Session.commit` as well as
2047
:meth:`.Transaction.rollback`. This is an example of where we take advantage
2048
of the :class:`.Connection` object's ability to maintain *subtransactions*, or
2049
nested begin/commit-or-rollback pairs where only the outermost begin/commit
2050
pair actually commits the transaction, or if the outermost block rolls back,
2051
everything is rolled back.
2052
2053
.. topic:: Supporting Tests with Rollbacks
2054
2055
The above recipe works well for any kind of database enabled test, except
2056
for a test that needs to actually invoke :meth:`.Session.rollback` within
2057
the scope of the test itself. The above recipe can be expanded, such
2058
that the :class:`.Session` always runs all operations within the scope
2059
of a SAVEPOINT, which is established at the start of each transaction,
2060
so that tests can also rollback the "transaction" as well while still
2061
remaining in the scope of a larger "transaction" that's never committed,
2062
using two extra events::
2063
2064
from sqlalchemy import event
2065
2066
class SomeTest(TestCase):
2067
def setUp(self):
2068
# connect to the database
2069
self.connection = engine.connect()
2070
2071
# begin a non-ORM transaction
2072
self.trans = connection.begin()
2073
2074
# bind an individual Session to the connection
2075
self.session = Session(bind=self.connection)
2076
2077
# start the session in a SAVEPOINT...
2078
self.session.begin_nested()
2079
2080
# then each time that SAVEPOINT ends, reopen it
2081
@event.listens_for(self.session, "after_transaction_end")
2082
def restart_savepoint(session, transaction):
2083
if transaction.nested and not transaction._parent.nested:
2084
session.begin_nested()
2085
2086
2087
# ... the tearDown() method stays the same
2088
2089
.. _unitofwork_contextual:
2090
2091
Contextual/Thread-local Sessions
2092
=================================
2093
2094
Recall from the section :ref:`session_faq_whentocreate`, the concept of
2095
"session scopes" was introduced, with an emphasis on web applications
2096
and the practice of linking the scope of a :class:`.Session` with that
2097
of a web request. Most modern web frameworks include integration tools
2098
so that the scope of the :class:`.Session` can be managed automatically,
2099
and these tools should be used as they are available.
2100
2101
SQLAlchemy includes its own helper object, which helps with the establishment
2102
of user-defined :class:`.Session` scopes. It is also used by third-party
2103
integration systems to help construct their integration schemes.
2104
2105
The object is the :class:`.scoped_session` object, and it represents a
2106
**registry** of :class:`.Session` objects. If you're not familiar with the
2107
registry pattern, a good introduction can be found in `Patterns of Enterprise
2108
Architecture <http://martinfowler.com/eaaCatalog/registry.html>`_.
2109
2110
.. note::
2111
2112
The :class:`.scoped_session` object is a very popular and useful object
2113
used by many SQLAlchemy applications. However, it is important to note
2114
that it presents **only one approach** to the issue of :class:`.Session`
2115
management. If you're new to SQLAlchemy, and especially if the
2116
term "thread-local variable" seems strange to you, we recommend that
2117
if possible you familiarize first with an off-the-shelf integration
2118
system such as `Flask-SQLAlchemy <http://packages.python.org/Flask-SQLAlchemy/>`_
2119
or `zope.sqlalchemy <http://pypi.python.org/pypi/zope.sqlalchemy>`_.
2120
2121
A :class:`.scoped_session` is constructed by calling it, passing it a
2122
**factory** which can create new :class:`.Session` objects. A factory
2123
is just something that produces a new object when called, and in the
2124
case of :class:`.Session`, the most common factory is the :class:`.sessionmaker`,
2125
introduced earlier in this section. Below we illustrate this usage::
2126
2127
>>> from sqlalchemy.orm import scoped_session
2128
>>> from sqlalchemy.orm import sessionmaker
2129
2130
>>> session_factory = sessionmaker(bind=some_engine)
2131
>>> Session = scoped_session(session_factory)
2132
2133
The :class:`.scoped_session` object we've created will now call upon the
2134
:class:`.sessionmaker` when we "call" the registry::
2135
2136
>>> some_session = Session()
2137
2138
Above, ``some_session`` is an instance of :class:`.Session`, which we
2139
can now use to talk to the database. This same :class:`.Session` is also
2140
present within the :class:`.scoped_session` registry we've created. If
2141
we call upon the registry a second time, we get back the **same** :class:`.Session`::
2142
2143
>>> some_other_session = Session()
2144
>>> some_session is some_other_session
2145
True
2146
2147
This pattern allows disparate sections of the application to call upon a global
2148
:class:`.scoped_session`, so that all those areas may share the same session
2149
without the need to pass it explicitly. The :class:`.Session` we've established
2150
in our registry will remain, until we explicitly tell our registry to dispose of it,
2151
by calling :meth:`.scoped_session.remove`::
2152
2153
>>> Session.remove()
2154
2155
The :meth:`.scoped_session.remove` method first calls :meth:`.Session.close` on
2156
the current :class:`.Session`, which has the effect of releasing any connection/transactional
2157
resources owned by the :class:`.Session` first, then discarding the :class:`.Session`
2158
itself. "Releasing" here means that connections are returned to their connection pool and any transactional state is rolled back, ultimately using the ``rollback()`` method of the underlying DBAPI connection.
2159
2160
At this point, the :class:`.scoped_session` object is "empty", and will create
2161
a **new** :class:`.Session` when called again. As illustrated below, this
2162
is not the same :class:`.Session` we had before::
2163
2164
>>> new_session = Session()
2165
>>> new_session is some_session
2166
False
2167
2168
The above series of steps illustrates the idea of the "registry" pattern in a
2169
nutshell. With that basic idea in hand, we can discuss some of the details
2170
of how this pattern proceeds.
2171
2172
Implicit Method Access
2173
----------------------
2174
2175
The job of the :class:`.scoped_session` is simple; hold onto a :class:`.Session`
2176
for all who ask for it. As a means of producing more transparent access to this
2177
:class:`.Session`, the :class:`.scoped_session` also includes **proxy behavior**,
2178
meaning that the registry itself can be treated just like a :class:`.Session`
2179
directly; when methods are called on this object, they are **proxied** to the
2180
underlying :class:`.Session` being maintained by the registry::
2181
2182
Session = scoped_session(some_factory)
2183
2184
# equivalent to:
2185
#
2186
# session = Session()
2187
# print session.query(MyClass).all()
2188
#
2189
print Session.query(MyClass).all()
2190
2191
The above code accomplishes the same task as that of acquiring the current
2192
:class:`.Session` by calling upon the registry, then using that :class:`.Session`.
2193
2194
Thread-Local Scope
2195
------------------
2196
2197
Users who are familiar with multithreaded programming will note that representing
2198
anything as a global variable is usually a bad idea, as it implies that the
2199
global object will be accessed by many threads concurrently. The :class:`.Session`
2200
object is entirely designed to be used in a **non-concurrent** fashion, which
2201
in terms of multithreading means "only in one thread at a time". So our
2202
above example of :class:`.scoped_session` usage, where the same :class:`.Session`
2203
object is maintained across multiple calls, suggests that some process needs
2204
to be in place such that mutltiple calls across many threads don't actually get
2205
a handle to the same session. We call this notion **thread local storage**,
2206
which means, a special object is used that will maintain a distinct object
2207
per each application thread. Python provides this via the
2208
`threading.local() <http://docs.python.org/library/threading.html#threading.local>`_
2209
construct. The :class:`.scoped_session` object by default uses this object
2210
as storage, so that a single :class:`.Session` is maintained for all who call
2211
upon the :class:`.scoped_session` registry, but only within the scope of a single
2212
thread. Callers who call upon the registry in a different thread get a
2213
:class:`.Session` instance that is local to that other thread.
2214
2215
Using this technique, the :class:`.scoped_session` provides a quick and relatively
2216
simple (if one is familiar with thread-local storage) way of providing
2217
a single, global object in an application that is safe to be called upon
2218
from multiple threads.
2219
2220
The :meth:`.scoped_session.remove` method, as always, removes the current
2221
:class:`.Session` associated with the thread, if any. However, one advantage of the
2222
``threading.local()`` object is that if the application thread itself ends, the
2223
"storage" for that thread is also garbage collected. So it is in fact "safe" to
2224
use thread local scope with an application that spawns and tears down threads,
2225
without the need to call :meth:`.scoped_session.remove`. However, the scope
2226
of transactions themselves, i.e. ending them via :meth:`.Session.commit` or
2227
:meth:`.Session.rollback`, will usually still be something that must be explicitly
2228
arranged for at the appropriate time, unless the application actually ties the
2229
lifespan of a thread to the lifespan of a transaction.
2230
2231
.. _session_lifespan:
2232
2233
Using Thread-Local Scope with Web Applications
2234
----------------------------------------------
2235
2236
As discussed in the section :ref:`session_faq_whentocreate`, a web application
2237
is architected around the concept of a **web request**, and integrating
2238
such an application with the :class:`.Session` usually implies that the :class:`.Session`
2239
will be associated with that request. As it turns out, most Python web frameworks,
2240
with notable exceptions such as the asynchronous frameworks Twisted and
2241
Tornado, use threads in a simple way, such that a particular web request is received,
2242
processed, and completed within the scope of a single *worker thread*. When
2243
the request ends, the worker thread is released to a pool of workers where it
2244
is available to handle another request.
2245
2246
This simple correspondence of web request and thread means that to associate a
2247
:class:`.Session` with a thread implies it is also associated with the web request
2248
running within that thread, and vice versa, provided that the :class:`.Session` is
2249
created only after the web request begins and torn down just before the web request ends.
2250
So it is a common practice to use :class:`.scoped_session` as a quick way
2251
to integrate the :class:`.Session` with a web application. The sequence
2252
diagram below illustrates this flow::
2253
2254
Web Server Web Framework SQLAlchemy ORM Code
2255
-------------- -------------- ------------------------------
2256
startup -> Web framework # Session registry is established
2257
initializes Session = scoped_session(sessionmaker())
2258
2259
incoming
2260
web request -> web request -> # The registry is *optionally*
2261
starts # called upon explicitly to create
2262
# a Session local to the thread and/or request
2263
Session()
2264
2265
# the Session registry can otherwise
2266
# be used at any time, creating the
2267
# request-local Session() if not present,
2268
# or returning the existing one
2269
Session.query(MyClass) # ...
2270
2271
Session.add(some_object) # ...
2272
2273
# if data was modified, commit the
2274
# transaction
2275
Session.commit()
2276
2277
web request ends -> # the registry is instructed to
2278
# remove the Session
2279
Session.remove()
2280
2281
sends output <-
2282
outgoing web <-
2283
response
2284
2285
Using the above flow, the process of integrating the :class:`.Session` with the
2286
web application has exactly two requirements:
2287
2288
1. Create a single :class:`.scoped_session` registry when the web application
2289
first starts, ensuring that this object is accessible by the rest of the
2290
application.
2291
2. Ensure that :meth:`.scoped_session.remove` is called when the web request ends,
2292
usually by integrating with the web framework's event system to establish
2293
an "on request end" event.
2294
2295
As noted earlier, the above pattern is **just one potential way** to integrate a :class:`.Session`
2296
with a web framework, one which in particular makes the significant assumption
2297
that the **web framework associates web requests with application threads**. It is
2298
however **strongly recommended that the integration tools provided with the web framework
2299
itself be used, if available**, instead of :class:`.scoped_session`.
2300
2301
In particular, while using a thread local can be convenient, it is preferable that the :class:`.Session` be
2302
associated **directly with the request**, rather than with
2303
the current thread. The next section on custom scopes details a more advanced configuration
2304
which can combine the usage of :class:`.scoped_session` with direct request based scope, or
2305
any kind of scope.
2306
2307
Using Custom Created Scopes
2308
---------------------------
2309
2310
The :class:`.scoped_session` object's default behavior of "thread local" scope is only
2311
one of many options on how to "scope" a :class:`.Session`. A custom scope can be defined
2312
based on any existing system of getting at "the current thing we are working with".
2313
2314
Suppose a web framework defines a library function ``get_current_request()``. An application
2315
built using this framework can call this function at any time, and the result will be
2316
some kind of ``Request`` object that represents the current request being processed.
2317
If the ``Request`` object is hashable, then this function can be easily integrated with
2318
:class:`.scoped_session` to associate the :class:`.Session` with the request. Below we illustrate
2319
this in conjunction with a hypothetical event marker provided by the web framework
2320
``on_request_end``, which allows code to be invoked whenever a request ends::
2321
2322
from my_web_framework import get_current_request, on_request_end
2323
from sqlalchemy.orm import scoped_session, sessionmaker
2324
2325
Session = scoped_session(sessionmaker(bind=some_engine), scopefunc=get_current_request)
2326
2327
@on_request_end
2328
def remove_session(req):
2329
Session.remove()
2330
2331
Above, we instantiate :class:`.scoped_session` in the usual way, except that we pass
2332
our request-returning function as the "scopefunc". This instructs :class:`.scoped_session`
2333
to use this function to generate a dictionary key whenever the registry is called upon
2334
to return the current :class:`.Session`. In this case it is particularly important
2335
that we ensure a reliable "remove" system is implemented, as this dictionary is not
2336
otherwise self-managed.
2337
2338
2339
Contextual Session API
2340
----------------------
2341
2342
.. autoclass:: sqlalchemy.orm.scoping.scoped_session
2343
:members:
2344
2345
.. autoclass:: sqlalchemy.util.ScopedRegistry
2346
:members:
2347
2348
.. autoclass:: sqlalchemy.util.ThreadLocalRegistry
2349
2350
.. _session_partitioning:
2351
2352
Partitioning Strategies
2353
=======================
2354
2355
Simple Vertical Partitioning
2356
----------------------------
2357
2358
Vertical partitioning places different kinds of objects, or different tables,
2359
across multiple databases::
2360
2361
engine1 = create_engine('postgresql://db1')
2362
engine2 = create_engine('postgresql://db2')
2363
2364
Session = sessionmaker(twophase=True)
2365
2366
# bind User operations to engine 1, Account operations to engine 2
2367
Session.configure(binds={User:engine1, Account:engine2})
2368
2369
session = Session()
2370
2371
Above, operations against either class will make usage of the :class:`.Engine`
2372
linked to that class. Upon a flush operation, similar rules take place
2373
to ensure each class is written to the right database.
2374
2375
The transactions among the multiple databases can optionally be coordinated
2376
via two phase commit, if the underlying backend supports it. See
2377
:ref:`session_twophase` for an example.
2378
2379
Custom Vertical Partitioning
2380
----------------------------
2381
2382
More comprehensive rule-based class-level partitioning can be built by
2383
overriding the :meth:`.Session.get_bind` method. Below we illustrate
2384
a custom :class:`.Session` which delivers the following rules:
2385
2386
1. Flush operations are delivered to the engine named ``master``.
2387
2388
2. Operations on objects that subclass ``MyOtherClass`` all
2389
occur on the ``other`` engine.
2390
2391
3. Read operations for all other classes occur on a random
2392
choice of the ``slave1`` or ``slave2`` database.
2393
2394
::
2395
2396
engines = {
2397
'master':create_engine("sqlite:///master.db"),
2398
'other':create_engine("sqlite:///other.db"),
2399
'slave1':create_engine("sqlite:///slave1.db"),
2400
'slave2':create_engine("sqlite:///slave2.db"),
2401
}
2402
2403
from sqlalchemy.orm import Session, sessionmaker
2404
import random
2405
2406
class RoutingSession(Session):
2407
def get_bind(self, mapper=None, clause=None):
2408
if mapper and issubclass(mapper.class_, MyOtherClass):
2409
return engines['other']
2410
elif self._flushing:
2411
return engines['master']
2412
else:
2413
return engines[
2414
random.choice(['slave1','slave2'])
2415
]
2416
2417
The above :class:`.Session` class is plugged in using the ``class_``
2418
argument to :class:`.sessionmaker`::
2419
2420
Session = sessionmaker(class_=RoutingSession)
2421
2422
This approach can be combined with multiple :class:`.MetaData` objects,
2423
using an approach such as that of using the declarative ``__abstract__``
2424
keyword, described at :ref:`declarative_abstract`.
2425
2426
Horizontal Partitioning
2427
-----------------------
2428
2429
Horizontal partitioning partitions the rows of a single table (or a set of
2430
tables) across multiple databases.
2431
2432
See the "sharding" example: :ref:`examples_sharding`.
2433
2434
Sessions API
2435
============
2436
2437
Session and sessionmaker()
2438
---------------------------
2439
2440
.. autoclass:: sessionmaker
2441
:members:
2442
:inherited-members:
2443
2444
.. autoclass:: sqlalchemy.orm.session.Session
2445
:members:
2446
:inherited-members:
2447
2448
.. autoclass:: sqlalchemy.orm.session.SessionTransaction
2449
:members:
2450
2451
Session Utilites
2452
----------------
2453
2454
.. autofunction:: make_transient
2455
2456
.. autofunction:: object_session
2457
2458
.. autofunction:: sqlalchemy.orm.util.was_deleted
2459
2460
Attribute and State Management Utilities
2461
-----------------------------------------
2462
2463
These functions are provided by the SQLAlchemy attribute
2464
instrumentation API to provide a detailed interface for dealing
2465
with instances, attribute values, and history. Some of them
2466
are useful when constructing event listener functions, such as
2467
those described in :doc:`/orm/events`.
2468
2469
.. currentmodule:: sqlalchemy.orm.util
2470
2471
.. autofunction:: object_state
2472
2473
.. currentmodule:: sqlalchemy.orm.attributes
2474
2475
.. autofunction:: del_attribute
2476
2477
.. autofunction:: get_attribute
2478
2479
.. autofunction:: get_history
2480
2481
.. autofunction:: init_collection
2482
2483
.. autofunction:: flag_modified
2484
2485
.. function:: instance_state
2486
2487
Return the :class:`.InstanceState` for a given
2488
mapped object.
2489
2490
This function is the internal version
2491
of :func:`.object_state`. The
2492
:func:`.object_state` and/or the
2493
:func:`.inspect` function is preferred here
2494
as they each emit an informative exception
2495
if the given object is not mapped.
2496
2497
.. autofunction:: sqlalchemy.orm.instrumentation.is_instrumented
2498
2499
.. autofunction:: set_attribute
2500
2501
.. autofunction:: set_committed_value
2502
2503
.. autoclass:: History
2504
:members:
2505
Loggerhead is a web-based interface for Breezy
Version: 2.0.1