1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
|
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>
Using the Session
— SQLAlchemy 0.6.3 Documentation</title>
<link rel="stylesheet" href="_static/pygments.css" type="text/css" />
<link rel="stylesheet" href="_static/docs.css" type="text/css" />
<script type="text/javascript">
var DOCUMENTATION_OPTIONS = {
URL_ROOT: '#',
VERSION: '0.6.3',
COLLAPSE_MODINDEX: false,
FILE_SUFFIX: '.html'
};
</script>
<script type="text/javascript" src="_static/jquery.js"></script>
<script type="text/javascript" src="_static/underscore.js"></script>
<script type="text/javascript" src="_static/doctools.js"></script>
<script type="text/javascript" src="_static/init.js"></script>
<link rel="index" title="Index" href="genindex.html" />
<link rel="search" title="Search" href="search.html" />
<link rel="top" title="SQLAlchemy 0.6.3 Documentation" href="index.html" />
<link rel="next" title="Database Engines" href="dbengine.html" />
<link rel="prev" title="Mapper Configuration" href="mappers.html" />
</head>
<body>
<h1>SQLAlchemy 0.6.3 Documentation</h1>
<div id="search">
Search:
<form class="search" action="search.html" method="get">
<input type="text" name="q" size="18" /> <input type="submit" value="Search" />
<input type="hidden" name="check_keywords" value="yes" />
<input type="hidden" name="area" value="default" />
</form>
</div>
<div class="versionheader">
Version: <span class="versionnum">0.6.3</span> Last Updated: 07/15/2010 12:35:47
</div>
<div class="clearboth"></div>
<div class="topnav">
<div id="pagecontrol">
<a href="reference/index.html">API Reference</a>
|
<a href="genindex.html">Index</a>
<div class="sourcelink">(<a href="_sources/session.txt">view source)</div>
</div>
<div class="navbanner">
<a class="totoc" href="index.html">Table of Contents</a>
»
Using the Session
<div class="prevnext">
Previous:
<a href="mappers.html" title="previous chapter">Mapper Configuration</a>
Next:
<a href="dbengine.html" title="next chapter">Database Engines</a>
</div>
<h2>
Using the Session
</h2>
</div>
<ul>
<li><a class="reference internal" href="#">Using the Session</a><ul>
<li><a class="reference internal" href="#what-does-the-session-do">What does the Session do ?</a></li>
<li><a class="reference internal" href="#getting-a-session">Getting a Session</a><ul>
<li><a class="reference internal" href="#using-a-sessionmaker-configuration">Using a sessionmaker() Configuration</a></li>
<li><a class="reference internal" href="#binding-session-to-an-engine">Binding Session to an Engine</a></li>
<li><a class="reference internal" href="#binding-session-to-a-connection">Binding Session to a Connection</a></li>
<li><a class="reference internal" href="#using-create-session">Using create_session()</a></li>
<li><a class="reference internal" href="#configurational-arguments">Configurational Arguments</a></li>
</ul>
</li>
<li><a class="reference internal" href="#id1">Using the Session</a><ul>
<li><a class="reference internal" href="#quickie-intro-to-object-states">Quickie Intro to Object States</a></li>
<li><a class="reference internal" href="#frequently-asked-questions">Frequently Asked Questions</a></li>
<li><a class="reference internal" href="#querying">Querying</a></li>
<li><a class="reference internal" href="#adding-new-or-existing-items">Adding New or Existing Items</a></li>
<li><a class="reference internal" href="#merging">Merging</a></li>
<li><a class="reference internal" href="#deleting">Deleting</a><ul>
<li><a class="reference internal" href="#deleting-based-on-filter-criterion">Deleting based on Filter Criterion</a></li>
</ul>
</li>
<li><a class="reference internal" href="#flushing">Flushing</a></li>
<li><a class="reference internal" href="#committing">Committing</a></li>
<li><a class="reference internal" href="#rolling-back">Rolling Back</a></li>
<li><a class="reference internal" href="#expunging">Expunging</a></li>
<li><a class="reference internal" href="#closing">Closing</a></li>
<li><a class="reference internal" href="#refreshing-expiring">Refreshing / Expiring</a></li>
<li><a class="reference internal" href="#session-attributes">Session Attributes</a></li>
</ul>
</li>
<li><a class="reference internal" href="#cascades">Cascades</a></li>
<li><a class="reference internal" href="#managing-transactions">Managing Transactions</a><ul>
<li><a class="reference internal" href="#using-savepoint">Using SAVEPOINT</a></li>
<li><a class="reference internal" href="#enabling-two-phase-commit">Enabling Two-Phase Commit</a></li>
</ul>
</li>
<li><a class="reference internal" href="#embedding-sql-insert-update-expressions-into-a-flush">Embedding SQL Insert/Update Expressions into a Flush</a></li>
<li><a class="reference internal" href="#using-sql-expressions-with-sessions">Using SQL Expressions with Sessions</a></li>
<li><a class="reference internal" href="#joining-a-session-into-an-external-transaction">Joining a Session into an External Transaction</a></li>
<li><a class="reference internal" href="#contextual-thread-local-sessions">Contextual/Thread-local Sessions</a><ul>
<li><a class="reference internal" href="#creating-a-thread-local-context">Creating a Thread-local Context</a></li>
<li><a class="reference internal" href="#lifespan-of-a-contextual-session">Lifespan of a Contextual Session</a></li>
</ul>
</li>
<li><a class="reference internal" href="#partitioning-strategies">Partitioning Strategies</a><ul>
<li><a class="reference internal" href="#vertical-partitioning">Vertical Partitioning</a></li>
<li><a class="reference internal" href="#horizontal-partitioning">Horizontal Partitioning</a></li>
</ul>
</li>
<li><a class="reference internal" href="#extending-session">Extending Session</a></li>
</ul>
</li>
</ul>
<div class="clearboth"></div>
</div>
<div class="document">
<div class="body">
<div class="section" id="using-the-session">
<span id="session-toplevel"></span><h1>Using the Session<a class="headerlink" href="#using-the-session" title="Permalink to this headline">¶</a></h1>
<p>The <cite>Mapper</cite> is the entrypoint to the configurational API of the SQLAlchemy object relational mapper. But the primary object one works with when using the ORM is the <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.session.Session" title="sqlalchemy.orm.session.Session"><tt class="xref py py-class docutils literal"><span class="pre">Session</span></tt></a>.</p>
<div class="section" id="what-does-the-session-do">
<h2>What does the Session do ?<a class="headerlink" href="#what-does-the-session-do" title="Permalink to this headline">¶</a></h2>
<p>In the most general sense, the <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.session.Session" title="sqlalchemy.orm.session.Session"><tt class="xref py py-class docutils literal"><span class="pre">Session</span></tt></a> establishes all conversations with the database and represents a “holding zone” for all the mapped instances which you’ve loaded or created during its lifespan. It implements the <a class="reference external" href="http://martinfowler.com/eaaCatalog/unitOfWork.html">Unit of Work</a> pattern, which means it keeps track of all changes which occur, and is capable of <strong>flushing</strong> those changes to the database as appropriate. Another important facet of the <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.session.Session" title="sqlalchemy.orm.session.Session"><tt class="xref py py-class docutils literal"><span class="pre">Session</span></tt></a> is that it’s also maintaining <strong>unique</strong> copies of each instance, where “unique” means “only one object with a particular primary key” - this pattern is called the <a class="reference external" href="http://martinfowler.com/eaaCatalog/identityMap.html">Identity Map</a>.</p>
<p>Beyond that, the <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.session.Session" title="sqlalchemy.orm.session.Session"><tt class="xref py py-class docutils literal"><span class="pre">Session</span></tt></a> implements an interface which lets you move objects in or out of the session in a variety of ways, it provides the entryway to a <a class="reference internal" href="reference/orm/query.html#sqlalchemy.orm.query.Query" title="sqlalchemy.orm.query.Query"><tt class="xref py py-class docutils literal"><span class="pre">Query</span></tt></a> object which is used to query the database for data, and it also provides a transactional context for SQL operations which rides on top of the transactional capabilities of <a class="reference internal" href="reference/sqlalchemy/connections.html#sqlalchemy.engine.base.Engine" title="sqlalchemy.engine.base.Engine"><tt class="xref py py-class docutils literal"><span class="pre">Engine</span></tt></a> and <a class="reference internal" href="reference/sqlalchemy/connections.html#sqlalchemy.engine.base.Connection" title="sqlalchemy.engine.base.Connection"><tt class="xref py py-class docutils literal"><span class="pre">Connection</span></tt></a> objects.</p>
</div>
<div class="section" id="getting-a-session">
<h2>Getting a Session<a class="headerlink" href="#getting-a-session" title="Permalink to this headline">¶</a></h2>
<p><a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.session.Session" title="sqlalchemy.orm.session.Session"><tt class="xref py py-class docutils literal"><span class="pre">Session</span></tt></a> is a regular Python class which can be directly instantiated. However, to standardize how sessions are configured and acquired, the <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.sessionmaker" title="sqlalchemy.orm.sessionmaker"><tt class="xref py py-func docutils literal"><span class="pre">sessionmaker()</span></tt></a> function is normally used to create a top level <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.session.Session" title="sqlalchemy.orm.session.Session"><tt class="xref py py-class docutils literal"><span class="pre">Session</span></tt></a> configuration which can then be used throughout an application without the need to repeat the configurational arguments.</p>
<div class="section" id="using-a-sessionmaker-configuration">
<h3>Using a sessionmaker() Configuration<a class="headerlink" href="#using-a-sessionmaker-configuration" title="Permalink to this headline">¶</a></h3>
<p>The usage of <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.sessionmaker" title="sqlalchemy.orm.sessionmaker"><tt class="xref py py-func docutils literal"><span class="pre">sessionmaker()</span></tt></a> is illustrated below:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="kn">from</span> <span class="nn">sqlalchemy.orm</span> <span class="kn">import</span> <span class="n">sessionmaker</span>
<span class="c"># create a configured "Session" class</span>
<span class="n">Session</span> <span class="o">=</span> <span class="n">sessionmaker</span><span class="p">(</span><span class="n">bind</span><span class="o">=</span><span class="n">some_engine</span><span class="p">)</span>
<span class="c"># create a Session</span>
<span class="n">session</span> <span class="o">=</span> <span class="n">Session</span><span class="p">()</span>
<span class="c"># work with sess</span>
<span class="n">myobject</span> <span class="o">=</span> <span class="n">MyObject</span><span class="p">(</span><span class="s">'foo'</span><span class="p">,</span> <span class="s">'bar'</span><span class="p">)</span>
<span class="n">session</span><span class="o">.</span><span class="n">add</span><span class="p">(</span><span class="n">myobject</span><span class="p">)</span>
<span class="n">session</span><span class="o">.</span><span class="n">commit</span><span class="p">()</span>
<span class="c"># close when finished</span>
<span class="n">session</span><span class="o">.</span><span class="n">close</span><span class="p">()</span></pre></div>
</div>
<p>Above, the <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.sessionmaker" title="sqlalchemy.orm.sessionmaker"><tt class="xref py py-func docutils literal"><span class="pre">sessionmaker()</span></tt></a> call creates a class for us, which we assign to the name <tt class="docutils literal"><span class="pre">Session</span></tt>. This class is a subclass of the actual <tt class="docutils literal"><span class="pre">sqlalchemy.orm.session.Session</span></tt> class, which will instantiate with a particular bound engine.</p>
<p>When you write your application, place the call to <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.sessionmaker" title="sqlalchemy.orm.sessionmaker"><tt class="xref py py-func docutils literal"><span class="pre">sessionmaker()</span></tt></a> somewhere global, and then make your new <tt class="docutils literal"><span class="pre">Session</span></tt> class available to the rest of your application.</p>
</div>
<div class="section" id="binding-session-to-an-engine">
<h3>Binding Session to an Engine<a class="headerlink" href="#binding-session-to-an-engine" title="Permalink to this headline">¶</a></h3>
<p>In our previous example regarding <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.sessionmaker" title="sqlalchemy.orm.sessionmaker"><tt class="xref py py-func docutils literal"><span class="pre">sessionmaker()</span></tt></a>, we specified a <tt class="docutils literal"><span class="pre">bind</span></tt> for a particular <a class="reference internal" href="reference/sqlalchemy/connections.html#sqlalchemy.engine.base.Engine" title="sqlalchemy.engine.base.Engine"><tt class="xref py py-class docutils literal"><span class="pre">Engine</span></tt></a>. If we’d like to construct a <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.sessionmaker" title="sqlalchemy.orm.sessionmaker"><tt class="xref py py-func docutils literal"><span class="pre">sessionmaker()</span></tt></a> without an engine available and bind it later on, or to specify other options to an existing <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.sessionmaker" title="sqlalchemy.orm.sessionmaker"><tt class="xref py py-func docutils literal"><span class="pre">sessionmaker()</span></tt></a>, we may use the <tt class="docutils literal"><span class="pre">configure()</span></tt> method:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="c"># configure Session class with desired options</span>
<span class="n">Session</span> <span class="o">=</span> <span class="n">sessionmaker</span><span class="p">()</span>
<span class="c"># later, we create the engine</span>
<span class="n">engine</span> <span class="o">=</span> <span class="n">create_engine</span><span class="p">(</span><span class="s">'postgresql://...'</span><span class="p">)</span>
<span class="c"># associate it with our custom Session class</span>
<span class="n">Session</span><span class="o">.</span><span class="n">configure</span><span class="p">(</span><span class="n">bind</span><span class="o">=</span><span class="n">engine</span><span class="p">)</span>
<span class="c"># work with the session</span>
<span class="n">session</span> <span class="o">=</span> <span class="n">Session</span><span class="p">()</span></pre></div>
</div>
<p>It’s actually entirely optional to bind a Session to an engine. If the underlying mapped <a class="reference internal" href="reference/sqlalchemy/schema.html#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal"><span class="pre">Table</span></tt></a> objects use “bound” metadata, the <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.session.Session" title="sqlalchemy.orm.session.Session"><tt class="xref py py-class docutils literal"><span class="pre">Session</span></tt></a> will make use of the bound engine instead (or will even use multiple engines if multiple binds are present within the mapped tables). “Bound” metadata is described at <a class="reference internal" href="metadata.html#metadata-binding"><em>Creating and Dropping Database Tables</em></a>.</p>
<p>The <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.session.Session" title="sqlalchemy.orm.session.Session"><tt class="xref py py-class docutils literal"><span class="pre">Session</span></tt></a> also has the ability to be bound to multiple engines explicitly. Descriptions of these scenarios are described in <a class="reference internal" href="#session-partitioning"><em>Partitioning Strategies</em></a>.</p>
</div>
<div class="section" id="binding-session-to-a-connection">
<h3>Binding Session to a Connection<a class="headerlink" href="#binding-session-to-a-connection" title="Permalink to this headline">¶</a></h3>
<p>The <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.session.Session" title="sqlalchemy.orm.session.Session"><tt class="xref py py-class docutils literal"><span class="pre">Session</span></tt></a> can also be explicitly bound to an individual database <a class="reference internal" href="reference/sqlalchemy/connections.html#sqlalchemy.engine.base.Connection" title="sqlalchemy.engine.base.Connection"><tt class="xref py py-class docutils literal"><span class="pre">Connection</span></tt></a>. Reasons for doing this may include to join a <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.session.Session" title="sqlalchemy.orm.session.Session"><tt class="xref py py-class docutils literal"><span class="pre">Session</span></tt></a> with an ongoing transaction local to a specific <a class="reference internal" href="reference/sqlalchemy/connections.html#sqlalchemy.engine.base.Connection" title="sqlalchemy.engine.base.Connection"><tt class="xref py py-class docutils literal"><span class="pre">Connection</span></tt></a> object, or to bypass connection pooling by just having connections persistently checked out and associated with distinct, long running sessions:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="c"># global application scope. create Session class, engine</span>
<span class="n">Session</span> <span class="o">=</span> <span class="n">sessionmaker</span><span class="p">()</span>
<span class="n">engine</span> <span class="o">=</span> <span class="n">create_engine</span><span class="p">(</span><span class="s">'postgresql://...'</span><span class="p">)</span>
<span class="o">...</span>
<span class="c"># local scope, such as within a controller function</span>
<span class="c"># connect to the database</span>
<span class="n">connection</span> <span class="o">=</span> <span class="n">engine</span><span class="o">.</span><span class="n">connect</span><span class="p">()</span>
<span class="c"># bind an individual Session to the connection</span>
<span class="n">session</span> <span class="o">=</span> <span class="n">Session</span><span class="p">(</span><span class="n">bind</span><span class="o">=</span><span class="n">connection</span><span class="p">)</span></pre></div>
</div>
</div>
<div class="section" id="using-create-session">
<h3>Using create_session()<a class="headerlink" href="#using-create-session" title="Permalink to this headline">¶</a></h3>
<p>As an alternative to <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.sessionmaker" title="sqlalchemy.orm.sessionmaker"><tt class="xref py py-func docutils literal"><span class="pre">sessionmaker()</span></tt></a>, <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.create_session" title="sqlalchemy.orm.create_session"><tt class="xref py py-func docutils literal"><span class="pre">create_session()</span></tt></a> is a function which calls the normal <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.session.Session" title="sqlalchemy.orm.session.Session"><tt class="xref py py-class docutils literal"><span class="pre">Session</span></tt></a> constructor directly. All arguments are passed through and the new <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.session.Session" title="sqlalchemy.orm.session.Session"><tt class="xref py py-class docutils literal"><span class="pre">Session</span></tt></a> object is returned:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">session</span> <span class="o">=</span> <span class="n">create_session</span><span class="p">(</span><span class="n">bind</span><span class="o">=</span><span class="n">myengine</span><span class="p">,</span> <span class="n">autocommit</span><span class="o">=</span><span class="bp">True</span><span class="p">,</span> <span class="n">autoflush</span><span class="o">=</span><span class="bp">False</span><span class="p">)</span></pre></div>
</div>
<p>Note that <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.create_session" title="sqlalchemy.orm.create_session"><tt class="xref py py-func docutils literal"><span class="pre">create_session()</span></tt></a> disables all optional “automation” by default. Called with no arguments, the session produced is not autoflushing, does not auto-expire, and does not maintain a transaction (i.e. it begins and commits a new transaction for each <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.session.Session.flush" title="sqlalchemy.orm.session.Session.flush"><tt class="xref py py-func docutils literal"><span class="pre">flush()</span></tt></a>). SQLAlchemy uses <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.create_session" title="sqlalchemy.orm.create_session"><tt class="xref py py-func docutils literal"><span class="pre">create_session()</span></tt></a> extensively within its own unit tests.</p>
</div>
<div class="section" id="configurational-arguments">
<h3>Configurational Arguments<a class="headerlink" href="#configurational-arguments" title="Permalink to this headline">¶</a></h3>
<p>Configurational arguments accepted by <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.sessionmaker" title="sqlalchemy.orm.sessionmaker"><tt class="xref py py-func docutils literal"><span class="pre">sessionmaker()</span></tt></a> and <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.create_session" title="sqlalchemy.orm.create_session"><tt class="xref py py-func docutils literal"><span class="pre">create_session()</span></tt></a> are the same as that of the <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.session.Session" title="sqlalchemy.orm.session.Session"><tt class="xref py py-class docutils literal"><span class="pre">Session</span></tt></a> class itself, and are described at <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.sessionmaker" title="sqlalchemy.orm.sessionmaker"><tt class="xref py py-func docutils literal"><span class="pre">sqlalchemy.orm.sessionmaker()</span></tt></a>.</p>
<p>Note that the defaults of <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.create_session" title="sqlalchemy.orm.create_session"><tt class="xref py py-func docutils literal"><span class="pre">create_session()</span></tt></a> are the opposite of that of <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.sessionmaker" title="sqlalchemy.orm.sessionmaker"><tt class="xref py py-func docutils literal"><span class="pre">sessionmaker()</span></tt></a>: autoflush and expire_on_commit are False, autocommit is True. It is recommended to use the <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.sessionmaker" title="sqlalchemy.orm.sessionmaker"><tt class="xref py py-func docutils literal"><span class="pre">sessionmaker()</span></tt></a> function instead of <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.create_session" title="sqlalchemy.orm.create_session"><tt class="xref py py-func docutils literal"><span class="pre">create_session()</span></tt></a>. <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.create_session" title="sqlalchemy.orm.create_session"><tt class="xref py py-func docutils literal"><span class="pre">create_session()</span></tt></a> is used to get a session with no automation turned on and is useful for testing.</p>
</div>
</div>
<div class="section" id="id1">
<h2>Using the Session<a class="headerlink" href="#id1" title="Permalink to this headline">¶</a></h2>
<div class="section" id="quickie-intro-to-object-states">
<h3>Quickie Intro to Object States<a class="headerlink" href="#quickie-intro-to-object-states" title="Permalink to this headline">¶</a></h3>
<p>It’s helpful to know the states which an instance can have within a session:</p>
<ul class="simple">
<li><em>Transient</em> - an instance that’s not in a session, and is not saved to the database; i.e. it has no database identity. The only relationship such an object has to the ORM is that its class has a <tt class="docutils literal"><span class="pre">mapper()</span></tt> associated with it.</li>
<li><em>Pending</em> - when you <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.session.Session.add" title="sqlalchemy.orm.session.Session.add"><tt class="xref py py-func docutils literal"><span class="pre">add()</span></tt></a> a transient instance, it becomes pending. It still wasn’t actually flushed to the database yet, but it will be when the next flush occurs.</li>
<li><em>Persistent</em> - An instance which is present in the session and has a record in the database. You get persistent instances by either flushing so that the pending instances become persistent, or by querying the database for existing instances (or moving persistent instances from other sessions into your local session).</li>
<li><em>Detached</em> - an instance which has a record in the database, but is not in any session. There’s nothing wrong with this, and you can use objects normally when they’re detached, <strong>except</strong> they will not be able to issue any SQL in order to load collections or attributes which are not yet loaded, or were marked as “expired”.</li>
</ul>
<p>Knowing these states is important, since the <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.session.Session" title="sqlalchemy.orm.session.Session"><tt class="xref py py-class docutils literal"><span class="pre">Session</span></tt></a> tries to be strict about ambiguous operations (such as trying to save the same object to two different sessions at the same time).</p>
</div>
<div class="section" id="frequently-asked-questions">
<h3>Frequently Asked Questions<a class="headerlink" href="#frequently-asked-questions" title="Permalink to this headline">¶</a></h3>
<ul>
<li><p class="first">When do I make a <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.sessionmaker" title="sqlalchemy.orm.sessionmaker"><tt class="xref py py-func docutils literal"><span class="pre">sessionmaker()</span></tt></a> ?</p>
<blockquote>
<p>Just one time, somewhere in your application’s global scope. It should be looked upon as part of your application’s configuration. If your application has three .py files in a package, you could, for example, place the <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.sessionmaker" title="sqlalchemy.orm.sessionmaker"><tt class="xref py py-func docutils literal"><span class="pre">sessionmaker()</span></tt></a> line in your <tt class="docutils literal"><span class="pre">__init__.py</span></tt> file; from that point on your other modules say “from mypackage import Session”. That way, everyone else just uses <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.session.Session" title="sqlalchemy.orm.session.Session"><tt class="xref py py-class docutils literal"><span class="pre">Session()</span></tt></a>, and the configuration of that session is controlled by that central point.</p>
<p>If your application starts up, does imports, but does not know what database it’s going to be connecting to, you can bind the <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.session.Session" title="sqlalchemy.orm.session.Session"><tt class="xref py py-class docutils literal"><span class="pre">Session</span></tt></a> at the “class” level to the engine later on, using <tt class="docutils literal"><span class="pre">configure()</span></tt>.</p>
<p>In the examples in this section, we will frequently show the <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.sessionmaker" title="sqlalchemy.orm.sessionmaker"><tt class="xref py py-func docutils literal"><span class="pre">sessionmaker()</span></tt></a> being created right above the line where we actually invoke <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.session.Session" title="sqlalchemy.orm.session.Session"><tt class="xref py py-class docutils literal"><span class="pre">Session()</span></tt></a>. But that’s just for example’s sake ! In reality, the <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.sessionmaker" title="sqlalchemy.orm.sessionmaker"><tt class="xref py py-func docutils literal"><span class="pre">sessionmaker()</span></tt></a> would be somewhere at the module level, and your individual <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.session.Session" title="sqlalchemy.orm.session.Session"><tt class="xref py py-class docutils literal"><span class="pre">Session()</span></tt></a> calls would be sprinkled all throughout your app, such as in a web application within each controller method.</p>
</blockquote>
</li>
<li><p class="first">When do I make a <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.session.Session" title="sqlalchemy.orm.session.Session"><tt class="xref py py-class docutils literal"><span class="pre">Session</span></tt></a> ?</p>
<blockquote>
<p>You typically invoke <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.session.Session" title="sqlalchemy.orm.session.Session"><tt class="xref py py-class docutils literal"><span class="pre">Session()</span></tt></a> when you first need to talk to your database, and want to save some objects or load some existing ones. Then, you work with it, save your changes, and then dispose of it....or at the very least <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.session.Session.close" title="sqlalchemy.orm.session.Session.close"><tt class="xref py py-func docutils literal"><span class="pre">close()</span></tt></a> it. It’s not a “global” kind of object, and should be handled more like a “local variable”, as it’s generally <strong>not</strong> safe to use with concurrent threads. Sessions are very inexpensive to make, and don’t use any resources whatsoever until they are first used...so create some !</p>
<p>There is also a pattern whereby you’re using a <strong>contextual session</strong>, this is described later in <a class="reference internal" href="#unitofwork-contextual"><em>Contextual/Thread-local Sessions</em></a>. In this pattern, a helper object is maintaining a <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.session.Session" title="sqlalchemy.orm.session.Session"><tt class="xref py py-class docutils literal"><span class="pre">Session</span></tt></a> for you, most commonly one that is local to the current thread (and sometimes also local to an application instance). SQLAlchemy has worked this pattern out such that it still <em>looks</em> like you’re creating a new session as you need one...so in that case, it’s still a guaranteed win to just say <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.session.Session" title="sqlalchemy.orm.session.Session"><tt class="xref py py-class docutils literal"><span class="pre">Session()</span></tt></a> whenever you want a session.</p>
</blockquote>
</li>
<li><p class="first">Is the Session a cache ?</p>
<blockquote>
<p>Yeee...no. It’s somewhat used as a cache, in that it implements the identity map pattern, and stores objects keyed to their primary key. However, it doesn’t do any kind of query caching. This means, if you say <tt class="docutils literal"><span class="pre">session.query(Foo).filter_by(name='bar')</span></tt>, even if <tt class="docutils literal"><span class="pre">Foo(name='bar')</span></tt> is right there, in the identity map, the session has no idea about that. It has to issue SQL to the database, get the rows back, and then when it sees the primary key in the row, <em>then</em> it can look in the local identity map and see that the object is already there. It’s only when you say <tt class="docutils literal"><span class="pre">query.get({some</span> <span class="pre">primary</span> <span class="pre">key})</span></tt> that the <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.session.Session" title="sqlalchemy.orm.session.Session"><tt class="xref py py-class docutils literal"><span class="pre">Session</span></tt></a> doesn’t have to issue a query.</p>
<p>Additionally, the Session stores object instances using a weak reference by default. This also defeats the purpose of using the Session as a cache, unless the <tt class="docutils literal"><span class="pre">weak_identity_map</span></tt> flag is set to <tt class="xref docutils literal"><span class="pre">False</span></tt>.</p>
<p>The <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.session.Session" title="sqlalchemy.orm.session.Session"><tt class="xref py py-class docutils literal"><span class="pre">Session</span></tt></a> is not designed to be a global object from which everyone consults as a “registry” of objects. That is the job of a <strong>second level cache</strong>. A good library for implementing second level caching is <a class="reference external" href="http://www.danga.com/memcached/">Memcached</a>. It <em>is</em> possible to “sort of” use the <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.session.Session" title="sqlalchemy.orm.session.Session"><tt class="xref py py-class docutils literal"><span class="pre">Session</span></tt></a> in this manner, if you set it to be non-transactional and it never flushes any SQL, but it’s not a terrific solution, since if concurrent threads load the same objects at the same time, you may have multiple copies of the same objects present in collections.</p>
</blockquote>
</li>
<li><p class="first">How can I get the <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.session.Session" title="sqlalchemy.orm.session.Session"><tt class="xref py py-class docutils literal"><span class="pre">Session</span></tt></a> for a certain object ?</p>
<blockquote>
<p>Use the <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.session.Session.object_session" title="sqlalchemy.orm.session.Session.object_session"><tt class="xref py py-func docutils literal"><span class="pre">object_session()</span></tt></a> classmethod available on <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.session.Session" title="sqlalchemy.orm.session.Session"><tt class="xref py py-class docutils literal"><span class="pre">Session</span></tt></a>:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">session</span> <span class="o">=</span> <span class="n">Session</span><span class="o">.</span><span class="n">object_session</span><span class="p">(</span><span class="n">someobject</span><span class="p">)</span></pre></div>
</div>
</blockquote>
</li>
</ul>
<ul id="index-0">
<li><p class="first">Is the session thread-safe?</p>
<blockquote>
<p>Nope. It has no thread synchronization of any kind built in, and particularly when you do a flush operation, it definitely is not open to concurrent threads accessing it, because it holds onto a single database connection at that point. If you use a session which is non-transactional for read operations only, it’s still not thread-“safe”, but you also wont get any catastrophic failures either, since it checks out and returns connections to the connection pool on an as-needed basis; it’s just that different threads might load the same objects independently of each other, but only one will wind up in the identity map (however, the other one might still live in a collection somewhere).</p>
<p>But the bigger point here is, you should not <em>want</em> to use the session with multiple concurrent threads. That would be like having everyone at a restaurant all eat from the same plate. The session is a local “workspace” that you use for a specific set of tasks; you don’t want to, or need to, share that session with other threads who are doing some other task. If, on the other hand, there are other threads participating in the same task you are, such as in a desktop graphical application, then you would be sharing the session with those threads, but you also will have implemented a proper locking scheme (or your graphical framework does) so that those threads do not collide.</p>
</blockquote>
</li>
</ul>
</div>
<div class="section" id="querying">
<h3>Querying<a class="headerlink" href="#querying" title="Permalink to this headline">¶</a></h3>
<p>The <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.session.Session.query" title="sqlalchemy.orm.session.Session.query"><tt class="xref py py-func docutils literal"><span class="pre">query()</span></tt></a> function takes one or more <em>entities</em> and returns a new <a class="reference internal" href="reference/orm/query.html#sqlalchemy.orm.query.Query" title="sqlalchemy.orm.query.Query"><tt class="xref py py-class docutils literal"><span class="pre">Query</span></tt></a> object which will issue mapper queries within the context of this Session. An entity is defined as a mapped class, a <a class="reference internal" href="reference/orm/mapping.html#sqlalchemy.orm.mapper.Mapper" title="sqlalchemy.orm.mapper.Mapper"><tt class="xref py py-class docutils literal"><span class="pre">Mapper</span></tt></a> object, an orm-enabled <em>descriptor</em>, or an <tt class="docutils literal"><span class="pre">AliasedClass</span></tt> object:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="c"># query from a class</span>
<span class="n">session</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="n">User</span><span class="p">)</span><span class="o">.</span><span class="n">filter_by</span><span class="p">(</span><span class="n">name</span><span class="o">=</span><span class="s">'ed'</span><span class="p">)</span><span class="o">.</span><span class="n">all</span><span class="p">()</span>
<span class="c"># query with multiple classes, returns tuples</span>
<span class="n">session</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="n">User</span><span class="p">,</span> <span class="n">Address</span><span class="p">)</span><span class="o">.</span><span class="n">join</span><span class="p">(</span><span class="s">'addresses'</span><span class="p">)</span><span class="o">.</span><span class="n">filter_by</span><span class="p">(</span><span class="n">name</span><span class="o">=</span><span class="s">'ed'</span><span class="p">)</span><span class="o">.</span><span class="n">all</span><span class="p">()</span>
<span class="c"># query using orm-enabled descriptors</span>
<span class="n">session</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="n">User</span><span class="o">.</span><span class="n">name</span><span class="p">,</span> <span class="n">User</span><span class="o">.</span><span class="n">fullname</span><span class="p">)</span><span class="o">.</span><span class="n">all</span><span class="p">()</span>
<span class="c"># query from a mapper</span>
<span class="n">user_mapper</span> <span class="o">=</span> <span class="n">class_mapper</span><span class="p">(</span><span class="n">User</span><span class="p">)</span>
<span class="n">session</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="n">user_mapper</span><span class="p">)</span></pre></div>
</div>
<p>When <a class="reference internal" href="reference/orm/query.html#sqlalchemy.orm.query.Query" title="sqlalchemy.orm.query.Query"><tt class="xref py py-class docutils literal"><span class="pre">Query</span></tt></a> returns results, each object instantiated is stored within the identity map. When a row matches an object which is already present, the same object is returned. In the latter case, whether or not the row is populated onto an existing object depends upon whether the attributes of the instance have been <em>expired</em> or not. A default-configured <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.session.Session" title="sqlalchemy.orm.session.Session"><tt class="xref py py-class docutils literal"><span class="pre">Session</span></tt></a> automatically expires all instances along transaction boundaries, so that with a normally isolated transaction, there shouldn’t be any issue of instances representing data which is stale with regards to the current transaction.</p>
</div>
<div class="section" id="adding-new-or-existing-items">
<h3>Adding New or Existing Items<a class="headerlink" href="#adding-new-or-existing-items" title="Permalink to this headline">¶</a></h3>
<p><a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.session.Session.add" title="sqlalchemy.orm.session.Session.add"><tt class="xref py py-func docutils literal"><span class="pre">add()</span></tt></a> is used to place instances in the session. For <em>transient</em> (i.e. brand new) instances, this will have the effect of an INSERT taking place for those instances upon the next flush. For instances which are <em>persistent</em> (i.e. were loaded by this session), they are already present and do not need to be added. Instances which are <em>detached</em> (i.e. have been removed from a session) may be re-associated with a session using this method:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">user1</span> <span class="o">=</span> <span class="n">User</span><span class="p">(</span><span class="n">name</span><span class="o">=</span><span class="s">'user1'</span><span class="p">)</span>
<span class="n">user2</span> <span class="o">=</span> <span class="n">User</span><span class="p">(</span><span class="n">name</span><span class="o">=</span><span class="s">'user2'</span><span class="p">)</span>
<span class="n">session</span><span class="o">.</span><span class="n">add</span><span class="p">(</span><span class="n">user1</span><span class="p">)</span>
<span class="n">session</span><span class="o">.</span><span class="n">add</span><span class="p">(</span><span class="n">user2</span><span class="p">)</span>
<span class="n">session</span><span class="o">.</span><span class="n">commit</span><span class="p">()</span> <span class="c"># write changes to the database</span></pre></div>
</div>
<p>To add a list of items to the session at once, use <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.session.Session.add_all" title="sqlalchemy.orm.session.Session.add_all"><tt class="xref py py-func docutils literal"><span class="pre">add_all()</span></tt></a>:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">session</span><span class="o">.</span><span class="n">add_all</span><span class="p">([</span><span class="n">item1</span><span class="p">,</span> <span class="n">item2</span><span class="p">,</span> <span class="n">item3</span><span class="p">])</span></pre></div>
</div>
<p>The <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.session.Session.add" title="sqlalchemy.orm.session.Session.add"><tt class="xref py py-func docutils literal"><span class="pre">add()</span></tt></a> operation <strong>cascades</strong> along the <tt class="docutils literal"><span class="pre">save-update</span></tt> cascade. For more details see the section <a class="reference internal" href="#unitofwork-cascades"><em>Cascades</em></a>.</p>
</div>
<div class="section" id="merging">
<h3>Merging<a class="headerlink" href="#merging" title="Permalink to this headline">¶</a></h3>
<p><a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.session.Session.merge" title="sqlalchemy.orm.session.Session.merge"><tt class="xref py py-func docutils literal"><span class="pre">merge()</span></tt></a> reconciles the current state of an instance and its associated children with existing data in the database, and returns a copy of the instance associated with the session. Usage is as follows:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">merged_object</span> <span class="o">=</span> <span class="n">session</span><span class="o">.</span><span class="n">merge</span><span class="p">(</span><span class="n">existing_object</span><span class="p">)</span></pre></div>
</div>
<p>When given an instance, it follows these steps:</p>
<blockquote>
<ul class="simple">
<li>It examines the primary key of the instance. If it’s present, it attempts to load an instance with that primary key (or pulls from the local identity map).</li>
<li>If there’s no primary key on the given instance, or the given primary key does not exist in the database, a new instance is created.</li>
<li>The state of the given instance is then copied onto the located/newly created instance.</li>
<li>The operation is cascaded to associated child items along the <tt class="docutils literal"><span class="pre">merge</span></tt> cascade. Note that all changes present on the given instance, including changes to collections, are merged.</li>
<li>The new instance is returned.</li>
</ul>
</blockquote>
<p>With <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.session.Session.merge" title="sqlalchemy.orm.session.Session.merge"><tt class="xref py py-func docutils literal"><span class="pre">merge()</span></tt></a>, the given instance is not placed within the session, and can be associated with a different session or detached. <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.session.Session.merge" title="sqlalchemy.orm.session.Session.merge"><tt class="xref py py-func docutils literal"><span class="pre">merge()</span></tt></a> is very useful for taking the state of any kind of object structure without regard for its origins or current session associations and placing that state within a session. Here’s two examples:</p>
<blockquote>
<ul class="simple">
<li>An application which reads an object structure from a file and wishes to save it to the database might parse the file, build up the structure, and then use <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.session.Session.merge" title="sqlalchemy.orm.session.Session.merge"><tt class="xref py py-func docutils literal"><span class="pre">merge()</span></tt></a> to save it to the database, ensuring that the data within the file is used to formulate the primary key of each element of the structure. Later, when the file has changed, the same process can be re-run, producing a slightly different object structure, which can then be <tt class="docutils literal"><span class="pre">merged</span></tt> in again, and the <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.session.Session" title="sqlalchemy.orm.session.Session"><tt class="xref py py-class docutils literal"><span class="pre">Session</span></tt></a> will automatically update the database to reflect those changes.</li>
<li>A web application stores mapped entities within an HTTP session object. When each request starts up, the serialized data can be merged into the session, so that the original entity may be safely shared among requests and threads.</li>
</ul>
</blockquote>
<p><a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.session.Session.merge" title="sqlalchemy.orm.session.Session.merge"><tt class="xref py py-func docutils literal"><span class="pre">merge()</span></tt></a> is frequently used by applications which implement their own second level caches. This refers to an application which uses an in memory dictionary, or an tool like Memcached to store objects over long running spans of time. When such an object needs to exist within a <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.session.Session" title="sqlalchemy.orm.session.Session"><tt class="xref py py-class docutils literal"><span class="pre">Session</span></tt></a>, <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.session.Session.merge" title="sqlalchemy.orm.session.Session.merge"><tt class="xref py py-func docutils literal"><span class="pre">merge()</span></tt></a> is a good choice since it leaves the original cached object untouched. For this use case, merge provides a keyword option called <tt class="docutils literal"><span class="pre">load=False</span></tt>. When this boolean flag is set to <tt class="xref docutils literal"><span class="pre">False</span></tt>, <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.session.Session.merge" title="sqlalchemy.orm.session.Session.merge"><tt class="xref py py-func docutils literal"><span class="pre">merge()</span></tt></a> will not issue any SQL to reconcile the given object against the current state of the database, thereby reducing query overhead. The limitation is that the given object and all of its children may not contain any pending changes, and it’s also of course possible that newer information in the database will not be present on the merged object, since no load is issued.</p>
</div>
<div class="section" id="deleting">
<h3>Deleting<a class="headerlink" href="#deleting" title="Permalink to this headline">¶</a></h3>
<p>The <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.session.Session.delete" title="sqlalchemy.orm.session.Session.delete"><tt class="xref py py-func docutils literal"><span class="pre">delete()</span></tt></a> method places an instance into the Session’s list of objects to be marked as deleted:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="c"># mark two objects to be deleted</span>
<span class="n">session</span><span class="o">.</span><span class="n">delete</span><span class="p">(</span><span class="n">obj1</span><span class="p">)</span>
<span class="n">session</span><span class="o">.</span><span class="n">delete</span><span class="p">(</span><span class="n">obj2</span><span class="p">)</span>
<span class="c"># commit (or flush)</span>
<span class="n">session</span><span class="o">.</span><span class="n">commit</span><span class="p">()</span></pre></div>
</div>
<p>The big gotcha with <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.session.Session.delete" title="sqlalchemy.orm.session.Session.delete"><tt class="xref py py-func docutils literal"><span class="pre">delete()</span></tt></a> is that <strong>nothing is removed from collections</strong>. Such as, if a <tt class="docutils literal"><span class="pre">User</span></tt> has a collection of three <tt class="docutils literal"><span class="pre">Addresses</span></tt>, deleting an <tt class="docutils literal"><span class="pre">Address</span></tt> will not remove it from <tt class="docutils literal"><span class="pre">user.addresses</span></tt>:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="n">address</span> <span class="o">=</span> <span class="n">user</span><span class="o">.</span><span class="n">addresses</span><span class="p">[</span><span class="mi">1</span><span class="p">]</span>
<span class="gp">>>> </span><span class="n">session</span><span class="o">.</span><span class="n">delete</span><span class="p">(</span><span class="n">address</span><span class="p">)</span>
<span class="gp">>>> </span><span class="n">session</span><span class="o">.</span><span class="n">flush</span><span class="p">()</span>
<span class="gp">>>> </span><span class="n">address</span> <span class="ow">in</span> <span class="n">user</span><span class="o">.</span><span class="n">addresses</span>
<span class="go">True</span></pre></div>
</div>
<p>The solution is to use proper cascading:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">mapper</span><span class="p">(</span><span class="n">User</span><span class="p">,</span> <span class="n">users_table</span><span class="p">,</span> <span class="n">properties</span><span class="o">=</span><span class="p">{</span>
<span class="s">'addresses'</span><span class="p">:</span><span class="n">relationship</span><span class="p">(</span><span class="n">Address</span><span class="p">,</span> <span class="n">cascade</span><span class="o">=</span><span class="s">"all, delete, delete-orphan"</span><span class="p">)</span>
<span class="p">})</span>
<span class="k">del</span> <span class="n">user</span><span class="o">.</span><span class="n">addresses</span><span class="p">[</span><span class="mi">1</span><span class="p">]</span>
<span class="n">session</span><span class="o">.</span><span class="n">flush</span><span class="p">()</span></pre></div>
</div>
<div class="section" id="deleting-based-on-filter-criterion">
<h4>Deleting based on Filter Criterion<a class="headerlink" href="#deleting-based-on-filter-criterion" title="Permalink to this headline">¶</a></h4>
<p>The caveat with <tt class="docutils literal"><span class="pre">Session.delete()</span></tt> is that you need to have an object handy already in order to delete. The Query includes a <a class="reference internal" href="reference/orm/query.html#sqlalchemy.orm.query.Query.delete" title="sqlalchemy.orm.query.Query.delete"><tt class="xref py py-func docutils literal"><span class="pre">delete()</span></tt></a> method which deletes based on filtering criteria:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">session</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="n">User</span><span class="p">)</span><span class="o">.</span><span class="n">filter</span><span class="p">(</span><span class="n">User</span><span class="o">.</span><span class="n">id</span><span class="o">==</span><span class="mi">7</span><span class="p">)</span><span class="o">.</span><span class="n">delete</span><span class="p">()</span></pre></div>
</div>
<p>The <tt class="docutils literal"><span class="pre">Query.delete()</span></tt> method includes functionality to “expire” objects already in the session which match the criteria. However it does have some caveats, including that “delete” and “delete-orphan” cascades won’t be fully expressed for collections which are already loaded. See the API docs for <a class="reference internal" href="reference/orm/query.html#sqlalchemy.orm.query.Query.delete" title="sqlalchemy.orm.query.Query.delete"><tt class="xref py py-meth docutils literal"><span class="pre">delete()</span></tt></a> for more details.</p>
</div>
</div>
<div class="section" id="flushing">
<h3>Flushing<a class="headerlink" href="#flushing" title="Permalink to this headline">¶</a></h3>
<p>When the <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.session.Session" title="sqlalchemy.orm.session.Session"><tt class="xref py py-class docutils literal"><span class="pre">Session</span></tt></a> is used with its default configuration, the flush step is nearly always done transparently. Specifically, the flush occurs before any individual <a class="reference internal" href="reference/orm/query.html#sqlalchemy.orm.query.Query" title="sqlalchemy.orm.query.Query"><tt class="xref py py-class docutils literal"><span class="pre">Query</span></tt></a> is issued, as well as within the <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.session.Session.commit" title="sqlalchemy.orm.session.Session.commit"><tt class="xref py py-func docutils literal"><span class="pre">commit()</span></tt></a> call before the transaction is committed. It also occurs before a SAVEPOINT is issued when <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.session.Session.begin_nested" title="sqlalchemy.orm.session.Session.begin_nested"><tt class="xref py py-func docutils literal"><span class="pre">begin_nested()</span></tt></a> is used.</p>
<p>Regardless of the autoflush setting, a flush can always be forced by issuing <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.session.Session.flush" title="sqlalchemy.orm.session.Session.flush"><tt class="xref py py-func docutils literal"><span class="pre">flush()</span></tt></a>:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">session</span><span class="o">.</span><span class="n">flush</span><span class="p">()</span></pre></div>
</div>
<p>The “flush-on-Query” aspect of the behavior can be disabled by constructing <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.sessionmaker" title="sqlalchemy.orm.sessionmaker"><tt class="xref py py-func docutils literal"><span class="pre">sessionmaker()</span></tt></a> with the flag <tt class="docutils literal"><span class="pre">autoflush=False</span></tt>:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">Session</span> <span class="o">=</span> <span class="n">sessionmaker</span><span class="p">(</span><span class="n">autoflush</span><span class="o">=</span><span class="bp">False</span><span class="p">)</span></pre></div>
</div>
<p>Additionally, autoflush can be temporarily disabled by setting the <tt class="docutils literal"><span class="pre">autoflush</span></tt> flag at any time:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">mysession</span> <span class="o">=</span> <span class="n">Session</span><span class="p">()</span>
<span class="n">mysession</span><span class="o">.</span><span class="n">autoflush</span> <span class="o">=</span> <span class="bp">False</span></pre></div>
</div>
<p>Some autoflush-disable recipes are available at <a class="reference external" href="http://www.sqlalchemy.org/trac/wiki/UsageRecipes/DisableAutoflush">DisableAutoFlush</a>.</p>
<p>The flush process <em>always</em> occurs within a transaction, even if the <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.session.Session" title="sqlalchemy.orm.session.Session"><tt class="xref py py-class docutils literal"><span class="pre">Session</span></tt></a> has been configured with <tt class="docutils literal"><span class="pre">autocommit=True</span></tt>, a setting that disables the session’s persistent transactional state. If no transaction is present, <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.session.Session.flush" title="sqlalchemy.orm.session.Session.flush"><tt class="xref py py-func docutils literal"><span class="pre">flush()</span></tt></a> creates its own transaction and commits it. Any failures during flush will always result in a rollback of whatever transaction is present. If the Session is not in <tt class="docutils literal"><span class="pre">autocommit=True</span></tt> mode, an explicit call to <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.session.Session.rollback" title="sqlalchemy.orm.session.Session.rollback"><tt class="xref py py-func docutils literal"><span class="pre">rollback()</span></tt></a> is required after a flush fails, even though the underlying transaction will have been rolled back already - this is so that the overall nesting pattern of so-called “subtransactions” is consistently maintained.</p>
</div>
<div class="section" id="committing">
<h3>Committing<a class="headerlink" href="#committing" title="Permalink to this headline">¶</a></h3>
<p><a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.session.Session.commit" title="sqlalchemy.orm.session.Session.commit"><tt class="xref py py-func docutils literal"><span class="pre">commit()</span></tt></a> is used to commit the current transaction. It always issues <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.session.Session.flush" title="sqlalchemy.orm.session.Session.flush"><tt class="xref py py-func docutils literal"><span class="pre">flush()</span></tt></a> beforehand to flush any remaining state to the database; this is independent of the “autoflush” setting. If no transaction is present, it raises an error. Note that the default behavior of the <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.session.Session" title="sqlalchemy.orm.session.Session"><tt class="xref py py-class docutils literal"><span class="pre">Session</span></tt></a> is that a transaction is always present; this behavior can be disabled by setting <tt class="docutils literal"><span class="pre">autocommit=True</span></tt>. In autocommit mode, a transaction can be initiated by calling the <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.session.Session.begin" title="sqlalchemy.orm.session.Session.begin"><tt class="xref py py-func docutils literal"><span class="pre">begin()</span></tt></a> method.</p>
<p>Another behavior of <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.session.Session.commit" title="sqlalchemy.orm.session.Session.commit"><tt class="xref py py-func docutils literal"><span class="pre">commit()</span></tt></a> is that by default it expires the state of all instances present after the commit is complete. This is so that when the instances are next accessed, either through attribute access or by them being present in a <a class="reference internal" href="reference/orm/query.html#sqlalchemy.orm.query.Query" title="sqlalchemy.orm.query.Query"><tt class="xref py py-class docutils literal"><span class="pre">Query</span></tt></a> result set, they receive the most recent state. To disable this behavior, configure <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.sessionmaker" title="sqlalchemy.orm.sessionmaker"><tt class="xref py py-func docutils literal"><span class="pre">sessionmaker()</span></tt></a> with <tt class="docutils literal"><span class="pre">expire_on_commit=False</span></tt>.</p>
<p>Normally, instances loaded into the <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.session.Session" title="sqlalchemy.orm.session.Session"><tt class="xref py py-class docutils literal"><span class="pre">Session</span></tt></a> are never changed by subsequent queries; the assumption is that the current transaction is isolated so the state most recently loaded is correct as long as the transaction continues. Setting <tt class="docutils literal"><span class="pre">autocommit=True</span></tt> works against this model to some degree since the <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.session.Session" title="sqlalchemy.orm.session.Session"><tt class="xref py py-class docutils literal"><span class="pre">Session</span></tt></a> behaves in exactly the same way with regard to attribute state, except no transaction is present.</p>
</div>
<div class="section" id="rolling-back">
<h3>Rolling Back<a class="headerlink" href="#rolling-back" title="Permalink to this headline">¶</a></h3>
<p><a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.session.Session.rollback" title="sqlalchemy.orm.session.Session.rollback"><tt class="xref py py-func docutils literal"><span class="pre">rollback()</span></tt></a> rolls back the current transaction. With a default configured session, the post-rollback state of the session is as follows:</p>
<blockquote>
<ul class="simple">
<li>All transactions are rolled back and all connections returned to the connection pool, unless the Session was bound directly to a Connection, in which case the connection is still maintained (but still rolled back).</li>
<li>Objects which were initially in the <em>pending</em> state when they were added to the <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.session.Session" title="sqlalchemy.orm.session.Session"><tt class="xref py py-class docutils literal"><span class="pre">Session</span></tt></a> within the lifespan of the transaction are expunged, corresponding to their INSERT statement being rolled back. The state of their attributes remains unchanged.</li>
<li>Objects which were marked as <em>deleted</em> within the lifespan of the transaction are promoted back to the <em>persistent</em> state, corresponding to their DELETE statement being rolled back. Note that if those objects were first <em>pending</em> within the transaction, that operation takes precedence instead.</li>
<li>All objects not expunged are fully expired.</li>
</ul>
</blockquote>
<p>With that state understood, the <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.session.Session" title="sqlalchemy.orm.session.Session"><tt class="xref py py-class docutils literal"><span class="pre">Session</span></tt></a> may safely continue usage after a rollback occurs.</p>
<p>When a <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.session.Session.flush" title="sqlalchemy.orm.session.Session.flush"><tt class="xref py py-func docutils literal"><span class="pre">flush()</span></tt></a> fails, typically for reasons like primary key, foreign key, or “not nullable” constraint violations, a <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.session.Session.rollback" title="sqlalchemy.orm.session.Session.rollback"><tt class="xref py py-func docutils literal"><span class="pre">rollback()</span></tt></a> is issued automatically (it’s currently not possible for a flush to continue after a partial failure). However, the flush process always uses its own transactional demarcator called a <em>subtransaction</em>, which is described more fully in the docstrings for <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.session.Session" title="sqlalchemy.orm.session.Session"><tt class="xref py py-class docutils literal"><span class="pre">Session</span></tt></a>. What it means here is that even though the database transaction has been rolled back, the end user must still issue <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.session.Session.rollback" title="sqlalchemy.orm.session.Session.rollback"><tt class="xref py py-func docutils literal"><span class="pre">rollback()</span></tt></a> to fully reset the state of the <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.session.Session" title="sqlalchemy.orm.session.Session"><tt class="xref py py-class docutils literal"><span class="pre">Session</span></tt></a>.</p>
</div>
<div class="section" id="expunging">
<h3>Expunging<a class="headerlink" href="#expunging" title="Permalink to this headline">¶</a></h3>
<p>Expunge removes an object from the Session, sending persistent instances to the detached state, and pending instances to the transient state:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="n">session</span><span class="o">.</span><span class="n">expunge</span><span class="p">(</span><span class="n">obj1</span><span class="p">)</span></pre></div>
</div>
<p>To remove all items, call <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.session.Session.expunge_all" title="sqlalchemy.orm.session.Session.expunge_all"><tt class="xref py py-func docutils literal"><span class="pre">expunge_all()</span></tt></a> (this method was formerly known as <tt class="docutils literal"><span class="pre">clear()</span></tt>).</p>
</div>
<div class="section" id="closing">
<h3>Closing<a class="headerlink" href="#closing" title="Permalink to this headline">¶</a></h3>
<p>The <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.session.Session.close" title="sqlalchemy.orm.session.Session.close"><tt class="xref py py-func docutils literal"><span class="pre">close()</span></tt></a> method issues a <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.session.Session.expunge_all" title="sqlalchemy.orm.session.Session.expunge_all"><tt class="xref py py-func docutils literal"><span class="pre">expunge_all()</span></tt></a>, and releases any transactional/connection resources. When connections are returned to the connection pool, transactional state is rolled back as well.</p>
</div>
<div class="section" id="refreshing-expiring">
<h3>Refreshing / Expiring<a class="headerlink" href="#refreshing-expiring" title="Permalink to this headline">¶</a></h3>
<p>To assist with the Session’s “sticky” behavior of instances which are present, individual objects can have all of their attributes immediately re-loaded from the database, or marked as “expired” which will cause a re-load to occur upon the next access of any of the object’s mapped attributes. Any changes marked on the object are discarded:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="c"># immediately re-load attributes on obj1, obj2</span>
<span class="n">session</span><span class="o">.</span><span class="n">refresh</span><span class="p">(</span><span class="n">obj1</span><span class="p">)</span>
<span class="n">session</span><span class="o">.</span><span class="n">refresh</span><span class="p">(</span><span class="n">obj2</span><span class="p">)</span>
<span class="c"># expire objects obj1, obj2, attributes will be reloaded</span>
<span class="c"># on the next access:</span>
<span class="n">session</span><span class="o">.</span><span class="n">expire</span><span class="p">(</span><span class="n">obj1</span><span class="p">)</span>
<span class="n">session</span><span class="o">.</span><span class="n">expire</span><span class="p">(</span><span class="n">obj2</span><span class="p">)</span></pre></div>
</div>
<p>When an expired object reloads, all non-deferred column-based attributes are loaded in one query. Current behavior for expired relationship-based attributes is that they load individually upon access - this behavior may be enhanced in a future release. When a refresh is invoked on an object, the ultimate operation is equivalent to a <tt class="xref py py-meth docutils literal"><span class="pre">Query.get()</span></tt>, so any relationships configured with eager loading should also load within the scope of the refresh operation.</p>
<p><a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.session.Session.refresh" title="sqlalchemy.orm.session.Session.refresh"><tt class="xref py py-func docutils literal"><span class="pre">refresh()</span></tt></a> and <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.session.Session.expire" title="sqlalchemy.orm.session.Session.expire"><tt class="xref py py-func docutils literal"><span class="pre">expire()</span></tt></a> also support being passed a list of individual attribute names in which to be refreshed. These names can refer to any attribute, column-based or relationship based:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="c"># immediately re-load the attributes 'hello', 'world' on obj1, obj2</span>
<span class="n">session</span><span class="o">.</span><span class="n">refresh</span><span class="p">(</span><span class="n">obj1</span><span class="p">,</span> <span class="p">[</span><span class="s">'hello'</span><span class="p">,</span> <span class="s">'world'</span><span class="p">])</span>
<span class="n">session</span><span class="o">.</span><span class="n">refresh</span><span class="p">(</span><span class="n">obj2</span><span class="p">,</span> <span class="p">[</span><span class="s">'hello'</span><span class="p">,</span> <span class="s">'world'</span><span class="p">])</span>
<span class="c"># expire the attributes 'hello', 'world' objects obj1, obj2, attributes will be reloaded</span>
<span class="c"># on the next access:</span>
<span class="n">session</span><span class="o">.</span><span class="n">expire</span><span class="p">(</span><span class="n">obj1</span><span class="p">,</span> <span class="p">[</span><span class="s">'hello'</span><span class="p">,</span> <span class="s">'world'</span><span class="p">])</span>
<span class="n">session</span><span class="o">.</span><span class="n">expire</span><span class="p">(</span><span class="n">obj2</span><span class="p">,</span> <span class="p">[</span><span class="s">'hello'</span><span class="p">,</span> <span class="s">'world'</span><span class="p">])</span></pre></div>
</div>
<p>The full contents of the session may be expired at once using <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.session.Session.expire_all" title="sqlalchemy.orm.session.Session.expire_all"><tt class="xref py py-func docutils literal"><span class="pre">expire_all()</span></tt></a>:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">session</span><span class="o">.</span><span class="n">expire_all</span><span class="p">()</span></pre></div>
</div>
<p><a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.session.Session.refresh" title="sqlalchemy.orm.session.Session.refresh"><tt class="xref py py-func docutils literal"><span class="pre">refresh()</span></tt></a> and <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.session.Session.expire" title="sqlalchemy.orm.session.Session.expire"><tt class="xref py py-func docutils literal"><span class="pre">expire()</span></tt></a> are usually not needed when working with a default-configured <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.session.Session" title="sqlalchemy.orm.session.Session"><tt class="xref py py-class docutils literal"><span class="pre">Session</span></tt></a>. The usual need is when an UPDATE or DELETE has been issued manually within the transaction using <tt class="docutils literal"><span class="pre">Session.execute()</span></tt>.</p>
</div>
<div class="section" id="session-attributes">
<h3>Session Attributes<a class="headerlink" href="#session-attributes" title="Permalink to this headline">¶</a></h3>
<p>The <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.session.Session" title="sqlalchemy.orm.session.Session"><tt class="xref py py-class docutils literal"><span class="pre">Session</span></tt></a> itself acts somewhat like a set-like collection. All items present may be accessed using the iterator interface:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="k">for</span> <span class="n">obj</span> <span class="ow">in</span> <span class="n">session</span><span class="p">:</span>
<span class="k">print</span> <span class="n">obj</span></pre></div>
</div>
<p>And presence may be tested for using regular “contains” semantics:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="k">if</span> <span class="n">obj</span> <span class="ow">in</span> <span class="n">session</span><span class="p">:</span>
<span class="k">print</span> <span class="s">"Object is present"</span></pre></div>
</div>
<p>The session is also keeping track of all newly created (i.e. pending) objects, all objects which have had changes since they were last loaded or saved (i.e. “dirty”), and everything that’s been marked as deleted:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="c"># pending objects recently added to the Session</span>
<span class="n">session</span><span class="o">.</span><span class="n">new</span>
<span class="c"># persistent objects which currently have changes detected</span>
<span class="c"># (this collection is now created on the fly each time the property is called)</span>
<span class="n">session</span><span class="o">.</span><span class="n">dirty</span>
<span class="c"># persistent objects that have been marked as deleted via session.delete(obj)</span>
<span class="n">session</span><span class="o">.</span><span class="n">deleted</span></pre></div>
</div>
<p>Note that objects within the session are by default <em>weakly referenced</em>. This means that when they are dereferenced in the outside application, they fall out of scope from within the <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.session.Session" title="sqlalchemy.orm.session.Session"><tt class="xref py py-class docutils literal"><span class="pre">Session</span></tt></a> as well and are subject to garbage collection by the Python interpreter. The exceptions to this include objects which are pending, objects which are marked as deleted, or persistent objects which have pending changes on them. After a full flush, these collections are all empty, and all objects are again weakly referenced. To disable the weak referencing behavior and force all objects within the session to remain until explicitly expunged, configure <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.sessionmaker" title="sqlalchemy.orm.sessionmaker"><tt class="xref py py-func docutils literal"><span class="pre">sessionmaker()</span></tt></a> with the <tt class="docutils literal"><span class="pre">weak_identity_map=False</span></tt> setting.</p>
</div>
</div>
<div class="section" id="cascades">
<span id="unitofwork-cascades"></span><h2>Cascades<a class="headerlink" href="#cascades" title="Permalink to this headline">¶</a></h2>
<p>Mappers support the concept of configurable <em>cascade</em> behavior on <a class="reference internal" href="reference/orm/mapping.html#sqlalchemy.orm.relationship" title="sqlalchemy.orm.relationship"><tt class="xref py py-func docutils literal"><span class="pre">relationship()</span></tt></a> constructs. This behavior controls how the Session should treat the instances that have a parent-child relationship with another instance that is operated upon by the Session. Cascade is indicated as a comma-separated list of string keywords, with the possible values <tt class="docutils literal"><span class="pre">all</span></tt>, <tt class="docutils literal"><span class="pre">delete</span></tt>, <tt class="docutils literal"><span class="pre">save-update</span></tt>, <tt class="docutils literal"><span class="pre">refresh-expire</span></tt>, <tt class="docutils literal"><span class="pre">merge</span></tt>, <tt class="docutils literal"><span class="pre">expunge</span></tt>, and <tt class="docutils literal"><span class="pre">delete-orphan</span></tt>.</p>
<p>Cascading is configured by setting the <tt class="docutils literal"><span class="pre">cascade</span></tt> keyword argument on a <a class="reference internal" href="reference/orm/mapping.html#sqlalchemy.orm.relationship" title="sqlalchemy.orm.relationship"><tt class="xref py py-func docutils literal"><span class="pre">relationship()</span></tt></a>:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">mapper</span><span class="p">(</span><span class="n">Order</span><span class="p">,</span> <span class="n">order_table</span><span class="p">,</span> <span class="n">properties</span><span class="o">=</span><span class="p">{</span>
<span class="s">'items'</span> <span class="p">:</span> <span class="n">relationship</span><span class="p">(</span><span class="n">Item</span><span class="p">,</span> <span class="n">items_table</span><span class="p">,</span> <span class="n">cascade</span><span class="o">=</span><span class="s">"all, delete-orphan"</span><span class="p">),</span>
<span class="s">'customer'</span> <span class="p">:</span> <span class="n">relationship</span><span class="p">(</span><span class="n">User</span><span class="p">,</span> <span class="n">users_table</span><span class="p">,</span> <span class="n">user_orders_table</span><span class="p">,</span> <span class="n">cascade</span><span class="o">=</span><span class="s">"save-update"</span><span class="p">),</span>
<span class="p">})</span></pre></div>
</div>
<p>The above mapper specifies two relationships, <tt class="docutils literal"><span class="pre">items</span></tt> and <tt class="docutils literal"><span class="pre">customer</span></tt>. The <tt class="docutils literal"><span class="pre">items</span></tt> relationship specifies “all, delete-orphan” as its <tt class="docutils literal"><span class="pre">cascade</span></tt> value, indicating that all <tt class="docutils literal"><span class="pre">add</span></tt>, <tt class="docutils literal"><span class="pre">merge</span></tt>, <tt class="docutils literal"><span class="pre">expunge</span></tt>, <tt class="docutils literal"><span class="pre">refresh</span></tt> <tt class="docutils literal"><span class="pre">delete</span></tt> and <tt class="docutils literal"><span class="pre">expire</span></tt> operations performed on a parent <tt class="docutils literal"><span class="pre">Order</span></tt> instance should also be performed on the child <tt class="docutils literal"><span class="pre">Item</span></tt> instances attached to it. The <tt class="docutils literal"><span class="pre">delete-orphan</span></tt> cascade value additionally indicates that if an <tt class="docutils literal"><span class="pre">Item</span></tt> instance is no longer associated with an <tt class="docutils literal"><span class="pre">Order</span></tt>, it should also be deleted. The “all, delete-orphan” cascade argument allows a so-called <em>lifecycle</em> relationship between an <tt class="docutils literal"><span class="pre">Order</span></tt> and an <tt class="docutils literal"><span class="pre">Item</span></tt> object.</p>
<p>The <tt class="docutils literal"><span class="pre">customer</span></tt> relationship specifies only the “save-update” cascade value, indicating most operations will not be cascaded from a parent <tt class="docutils literal"><span class="pre">Order</span></tt> instance to a child <tt class="docutils literal"><span class="pre">User</span></tt> instance except for the <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.session.Session.add" title="sqlalchemy.orm.session.Session.add"><tt class="xref py py-func docutils literal"><span class="pre">add()</span></tt></a> operation. “save-update” cascade indicates that an <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.session.Session.add" title="sqlalchemy.orm.session.Session.add"><tt class="xref py py-func docutils literal"><span class="pre">add()</span></tt></a> on the parent will cascade to all child items, and also that items added to a parent which is already present in a session will also be added to that same session. “save-update” cascade also cascades the <em>pending history</em> of a relationship()-based attribute, meaning that objects which were removed from a scalar or collection attribute whose changes have not yet been flushed are also placed into the new session - this so that foreign key clear operations and deletions will take place (new in 0.6).</p>
<p>Note that the <tt class="docutils literal"><span class="pre">delete-orphan</span></tt> cascade only functions for relationships where the target object can have a single parent at a time, meaning it is only appropriate for one-to-one or one-to-many relationships. For a <a class="reference internal" href="reference/orm/mapping.html#sqlalchemy.orm.relationship" title="sqlalchemy.orm.relationship"><tt class="xref py py-func docutils literal"><span class="pre">relationship()</span></tt></a> which establishes one-to-one via a local foreign key, i.e. a many-to-one that stores only a single parent, or one-to-one/one-to-many via a “secondary” (association) table, a warning will be issued if <tt class="docutils literal"><span class="pre">delete-orphan</span></tt> is configured. To disable this warning, also specify the <tt class="docutils literal"><span class="pre">single_parent=True</span></tt> flag on the relationship, which constrains objects to allow attachment to only one parent at a time.</p>
<p>The default value for <tt class="docutils literal"><span class="pre">cascade</span></tt> on <a class="reference internal" href="reference/orm/mapping.html#sqlalchemy.orm.relationship" title="sqlalchemy.orm.relationship"><tt class="xref py py-func docutils literal"><span class="pre">relationship()</span></tt></a> is <tt class="docutils literal"><span class="pre">save-update,</span> <span class="pre">merge</span></tt>.</p>
</div>
<div class="section" id="managing-transactions">
<span id="unitofwork-transaction"></span><h2>Managing Transactions<a class="headerlink" href="#managing-transactions" title="Permalink to this headline">¶</a></h2>
<p>The <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.session.Session" title="sqlalchemy.orm.session.Session"><tt class="xref py py-class docutils literal"><span class="pre">Session</span></tt></a> manages transactions across all engines associated with it. As the <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.session.Session" title="sqlalchemy.orm.session.Session"><tt class="xref py py-class docutils literal"><span class="pre">Session</span></tt></a> receives requests to execute SQL statements using a particular <a class="reference internal" href="reference/sqlalchemy/connections.html#sqlalchemy.engine.base.Engine" title="sqlalchemy.engine.base.Engine"><tt class="xref py py-class docutils literal"><span class="pre">Engine</span></tt></a> or <a class="reference internal" href="reference/sqlalchemy/connections.html#sqlalchemy.engine.base.Connection" title="sqlalchemy.engine.base.Connection"><tt class="xref py py-class docutils literal"><span class="pre">Connection</span></tt></a>, it adds each individual <a class="reference internal" href="reference/sqlalchemy/connections.html#sqlalchemy.engine.base.Engine" title="sqlalchemy.engine.base.Engine"><tt class="xref py py-class docutils literal"><span class="pre">Engine</span></tt></a> encountered to its transactional state and maintains an open connection for each one (note that a simple application normally has just one <a class="reference internal" href="reference/sqlalchemy/connections.html#sqlalchemy.engine.base.Engine" title="sqlalchemy.engine.base.Engine"><tt class="xref py py-class docutils literal"><span class="pre">Engine</span></tt></a>). At commit time, all unflushed data is flushed, and each individual transaction is committed. If the underlying databases support two-phase semantics, this may be used by the Session as well if two-phase transactions are enabled.</p>
<p>Normal operation ends the transactional state using the <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.session.Session.rollback" title="sqlalchemy.orm.session.Session.rollback"><tt class="xref py py-func docutils literal"><span class="pre">rollback()</span></tt></a> or <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.session.Session.commit" title="sqlalchemy.orm.session.Session.commit"><tt class="xref py py-func docutils literal"><span class="pre">commit()</span></tt></a> methods. After either is called, the <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.session.Session" title="sqlalchemy.orm.session.Session"><tt class="xref py py-class docutils literal"><span class="pre">Session</span></tt></a> starts a new transaction:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">Session</span> <span class="o">=</span> <span class="n">sessionmaker</span><span class="p">()</span>
<span class="n">session</span> <span class="o">=</span> <span class="n">Session</span><span class="p">()</span>
<span class="k">try</span><span class="p">:</span>
<span class="n">item1</span> <span class="o">=</span> <span class="n">session</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="n">Item</span><span class="p">)</span><span class="o">.</span><span class="n">get</span><span class="p">(</span><span class="mi">1</span><span class="p">)</span>
<span class="n">item2</span> <span class="o">=</span> <span class="n">session</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="n">Item</span><span class="p">)</span><span class="o">.</span><span class="n">get</span><span class="p">(</span><span class="mi">2</span><span class="p">)</span>
<span class="n">item1</span><span class="o">.</span><span class="n">foo</span> <span class="o">=</span> <span class="s">'bar'</span>
<span class="n">item2</span><span class="o">.</span><span class="n">bar</span> <span class="o">=</span> <span class="s">'foo'</span>
<span class="c"># commit- will immediately go into a new transaction afterwards</span>
<span class="n">session</span><span class="o">.</span><span class="n">commit</span><span class="p">()</span>
<span class="k">except</span><span class="p">:</span>
<span class="c"># rollback - will immediately go into a new transaction afterwards.</span>
<span class="n">session</span><span class="o">.</span><span class="n">rollback</span><span class="p">()</span></pre></div>
</div>
<p>A session which is configured with <tt class="docutils literal"><span class="pre">autocommit=True</span></tt> may be placed into a transaction using <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.session.Session.begin" title="sqlalchemy.orm.session.Session.begin"><tt class="xref py py-func docutils literal"><span class="pre">begin()</span></tt></a>. With an <tt class="docutils literal"><span class="pre">autocommit=True</span></tt> session that’s been placed into a transaction using <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.session.Session.begin" title="sqlalchemy.orm.session.Session.begin"><tt class="xref py py-func docutils literal"><span class="pre">begin()</span></tt></a>, the session releases all connection resources after a <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.session.Session.commit" title="sqlalchemy.orm.session.Session.commit"><tt class="xref py py-func docutils literal"><span class="pre">commit()</span></tt></a> or <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.session.Session.rollback" title="sqlalchemy.orm.session.Session.rollback"><tt class="xref py py-func docutils literal"><span class="pre">rollback()</span></tt></a> and remains transaction-less (with the exception of flushes) until the next <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.session.Session.begin" title="sqlalchemy.orm.session.Session.begin"><tt class="xref py py-func docutils literal"><span class="pre">begin()</span></tt></a> call:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">Session</span> <span class="o">=</span> <span class="n">sessionmaker</span><span class="p">(</span><span class="n">autocommit</span><span class="o">=</span><span class="bp">True</span><span class="p">)</span>
<span class="n">session</span> <span class="o">=</span> <span class="n">Session</span><span class="p">()</span>
<span class="n">session</span><span class="o">.</span><span class="n">begin</span><span class="p">()</span>
<span class="k">try</span><span class="p">:</span>
<span class="n">item1</span> <span class="o">=</span> <span class="n">session</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="n">Item</span><span class="p">)</span><span class="o">.</span><span class="n">get</span><span class="p">(</span><span class="mi">1</span><span class="p">)</span>
<span class="n">item2</span> <span class="o">=</span> <span class="n">session</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="n">Item</span><span class="p">)</span><span class="o">.</span><span class="n">get</span><span class="p">(</span><span class="mi">2</span><span class="p">)</span>
<span class="n">item1</span><span class="o">.</span><span class="n">foo</span> <span class="o">=</span> <span class="s">'bar'</span>
<span class="n">item2</span><span class="o">.</span><span class="n">bar</span> <span class="o">=</span> <span class="s">'foo'</span>
<span class="n">session</span><span class="o">.</span><span class="n">commit</span><span class="p">()</span>
<span class="k">except</span><span class="p">:</span>
<span class="n">session</span><span class="o">.</span><span class="n">rollback</span><span class="p">()</span>
<span class="k">raise</span></pre></div>
</div>
<p>The <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.session.Session.begin" title="sqlalchemy.orm.session.Session.begin"><tt class="xref py py-func docutils literal"><span class="pre">begin()</span></tt></a> method also returns a transactional token which is compatible with the Python 2.6 <tt class="docutils literal"><span class="pre">with</span></tt> statement:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">Session</span> <span class="o">=</span> <span class="n">sessionmaker</span><span class="p">(</span><span class="n">autocommit</span><span class="o">=</span><span class="bp">True</span><span class="p">)</span>
<span class="n">session</span> <span class="o">=</span> <span class="n">Session</span><span class="p">()</span>
<span class="k">with</span> <span class="n">session</span><span class="o">.</span><span class="n">begin</span><span class="p">():</span>
<span class="n">item1</span> <span class="o">=</span> <span class="n">session</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="n">Item</span><span class="p">)</span><span class="o">.</span><span class="n">get</span><span class="p">(</span><span class="mi">1</span><span class="p">)</span>
<span class="n">item2</span> <span class="o">=</span> <span class="n">session</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="n">Item</span><span class="p">)</span><span class="o">.</span><span class="n">get</span><span class="p">(</span><span class="mi">2</span><span class="p">)</span>
<span class="n">item1</span><span class="o">.</span><span class="n">foo</span> <span class="o">=</span> <span class="s">'bar'</span>
<span class="n">item2</span><span class="o">.</span><span class="n">bar</span> <span class="o">=</span> <span class="s">'foo'</span></pre></div>
</div>
<div class="section" id="using-savepoint">
<h3>Using SAVEPOINT<a class="headerlink" href="#using-savepoint" title="Permalink to this headline">¶</a></h3>
<p>SAVEPOINT transactions, if supported by the underlying engine, may be delineated using the <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.session.Session.begin_nested" title="sqlalchemy.orm.session.Session.begin_nested"><tt class="xref py py-func docutils literal"><span class="pre">begin_nested()</span></tt></a> method:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">Session</span> <span class="o">=</span> <span class="n">sessionmaker</span><span class="p">()</span>
<span class="n">session</span> <span class="o">=</span> <span class="n">Session</span><span class="p">()</span>
<span class="n">session</span><span class="o">.</span><span class="n">add</span><span class="p">(</span><span class="n">u1</span><span class="p">)</span>
<span class="n">session</span><span class="o">.</span><span class="n">add</span><span class="p">(</span><span class="n">u2</span><span class="p">)</span>
<span class="n">session</span><span class="o">.</span><span class="n">begin_nested</span><span class="p">()</span> <span class="c"># establish a savepoint</span>
<span class="n">session</span><span class="o">.</span><span class="n">add</span><span class="p">(</span><span class="n">u3</span><span class="p">)</span>
<span class="n">session</span><span class="o">.</span><span class="n">rollback</span><span class="p">()</span> <span class="c"># rolls back u3, keeps u1 and u2</span>
<span class="n">session</span><span class="o">.</span><span class="n">commit</span><span class="p">()</span> <span class="c"># commits u1 and u2</span></pre></div>
</div>
<p><a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.session.Session.begin_nested" title="sqlalchemy.orm.session.Session.begin_nested"><tt class="xref py py-func docutils literal"><span class="pre">begin_nested()</span></tt></a> may be called any number of times, which will issue a new SAVEPOINT with a unique identifier for each call. For each <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.session.Session.begin_nested" title="sqlalchemy.orm.session.Session.begin_nested"><tt class="xref py py-func docutils literal"><span class="pre">begin_nested()</span></tt></a> call, a corresponding <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.session.Session.rollback" title="sqlalchemy.orm.session.Session.rollback"><tt class="xref py py-func docutils literal"><span class="pre">rollback()</span></tt></a> or <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.session.Session.commit" title="sqlalchemy.orm.session.Session.commit"><tt class="xref py py-func docutils literal"><span class="pre">commit()</span></tt></a> must be issued.</p>
<p>When <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.session.Session.begin_nested" title="sqlalchemy.orm.session.Session.begin_nested"><tt class="xref py py-func docutils literal"><span class="pre">begin_nested()</span></tt></a> is called, a <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.session.Session.flush" title="sqlalchemy.orm.session.Session.flush"><tt class="xref py py-func docutils literal"><span class="pre">flush()</span></tt></a> is unconditionally issued (regardless of the <tt class="docutils literal"><span class="pre">autoflush</span></tt> setting). This is so that when a <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.session.Session.rollback" title="sqlalchemy.orm.session.Session.rollback"><tt class="xref py py-func docutils literal"><span class="pre">rollback()</span></tt></a> occurs, the full state of the session is expired, thus causing all subsequent attribute/instance access to reference the full state of the <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.session.Session" title="sqlalchemy.orm.session.Session"><tt class="xref py py-class docutils literal"><span class="pre">Session</span></tt></a> right before <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.session.Session.begin_nested" title="sqlalchemy.orm.session.Session.begin_nested"><tt class="xref py py-func docutils literal"><span class="pre">begin_nested()</span></tt></a> was called.</p>
</div>
<div class="section" id="enabling-two-phase-commit">
<h3>Enabling Two-Phase Commit<a class="headerlink" href="#enabling-two-phase-commit" title="Permalink to this headline">¶</a></h3>
<p>Finally, for MySQL, PostgreSQL, and soon Oracle as well, the session can be instructed to use two-phase commit semantics. This will coordinate the committing of transactions across databases so that the transaction is either committed or rolled back in all databases. You can also <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.session.Session.prepare" title="sqlalchemy.orm.session.Session.prepare"><tt class="xref py py-func docutils literal"><span class="pre">prepare()</span></tt></a> the session for interacting with transactions not managed by SQLAlchemy. To use two phase transactions set the flag <tt class="docutils literal"><span class="pre">twophase=True</span></tt> on the session:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">engine1</span> <span class="o">=</span> <span class="n">create_engine</span><span class="p">(</span><span class="s">'postgresql://db1'</span><span class="p">)</span>
<span class="n">engine2</span> <span class="o">=</span> <span class="n">create_engine</span><span class="p">(</span><span class="s">'postgresql://db2'</span><span class="p">)</span>
<span class="n">Session</span> <span class="o">=</span> <span class="n">sessionmaker</span><span class="p">(</span><span class="n">twophase</span><span class="o">=</span><span class="bp">True</span><span class="p">)</span>
<span class="c"># bind User operations to engine 1, Account operations to engine 2</span>
<span class="n">Session</span><span class="o">.</span><span class="n">configure</span><span class="p">(</span><span class="n">binds</span><span class="o">=</span><span class="p">{</span><span class="n">User</span><span class="p">:</span><span class="n">engine1</span><span class="p">,</span> <span class="n">Account</span><span class="p">:</span><span class="n">engine2</span><span class="p">})</span>
<span class="n">session</span> <span class="o">=</span> <span class="n">Session</span><span class="p">()</span>
<span class="c"># .... work with accounts and users</span>
<span class="c"># commit. session will issue a flush to all DBs, and a prepare step to all DBs,</span>
<span class="c"># before committing both transactions</span>
<span class="n">session</span><span class="o">.</span><span class="n">commit</span><span class="p">()</span></pre></div>
</div>
</div>
</div>
<div class="section" id="embedding-sql-insert-update-expressions-into-a-flush">
<h2>Embedding SQL Insert/Update Expressions into a Flush<a class="headerlink" href="#embedding-sql-insert-update-expressions-into-a-flush" title="Permalink to this headline">¶</a></h2>
<p>This feature allows the value of a database column to be set to a SQL expression instead of a literal value. It’s especially useful for atomic updates, calling stored procedures, etc. All you do is assign an expression to an attribute:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="k">class</span> <span class="nc">SomeClass</span><span class="p">(</span><span class="nb">object</span><span class="p">):</span>
<span class="k">pass</span>
<span class="n">mapper</span><span class="p">(</span><span class="n">SomeClass</span><span class="p">,</span> <span class="n">some_table</span><span class="p">)</span>
<span class="n">someobject</span> <span class="o">=</span> <span class="n">session</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="n">SomeClass</span><span class="p">)</span><span class="o">.</span><span class="n">get</span><span class="p">(</span><span class="mi">5</span><span class="p">)</span>
<span class="c"># set 'value' attribute to a SQL expression adding one</span>
<span class="n">someobject</span><span class="o">.</span><span class="n">value</span> <span class="o">=</span> <span class="n">some_table</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">value</span> <span class="o">+</span> <span class="mi">1</span>
<span class="c"># issues "UPDATE some_table SET value=value+1"</span>
<span class="n">session</span><span class="o">.</span><span class="n">commit</span><span class="p">()</span></pre></div>
</div>
<p>This technique works both for INSERT and UPDATE statements. After the flush/commit operation, the <tt class="docutils literal"><span class="pre">value</span></tt> attribute on <tt class="docutils literal"><span class="pre">someobject</span></tt> above is expired, so that when next accessed the newly generated value will be loaded from the database.</p>
</div>
<div class="section" id="using-sql-expressions-with-sessions">
<h2>Using SQL Expressions with Sessions<a class="headerlink" href="#using-sql-expressions-with-sessions" title="Permalink to this headline">¶</a></h2>
<p>SQL expressions and strings can be executed via the <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.session.Session" title="sqlalchemy.orm.session.Session"><tt class="xref py py-class docutils literal"><span class="pre">Session</span></tt></a> within its transactional context. This is most easily accomplished using the <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.session.Session.execute" title="sqlalchemy.orm.session.Session.execute"><tt class="xref py py-func docutils literal"><span class="pre">execute()</span></tt></a> method, which returns a <a class="reference internal" href="reference/sqlalchemy/connections.html#sqlalchemy.engine.base.ResultProxy" title="sqlalchemy.engine.base.ResultProxy"><tt class="xref py py-class docutils literal"><span class="pre">ResultProxy</span></tt></a> in the same manner as an <a class="reference internal" href="reference/sqlalchemy/connections.html#sqlalchemy.engine.base.Engine" title="sqlalchemy.engine.base.Engine"><tt class="xref py py-class docutils literal"><span class="pre">Engine</span></tt></a> or <a class="reference internal" href="reference/sqlalchemy/connections.html#sqlalchemy.engine.base.Connection" title="sqlalchemy.engine.base.Connection"><tt class="xref py py-class docutils literal"><span class="pre">Connection</span></tt></a>:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">Session</span> <span class="o">=</span> <span class="n">sessionmaker</span><span class="p">(</span><span class="n">bind</span><span class="o">=</span><span class="n">engine</span><span class="p">)</span>
<span class="n">session</span> <span class="o">=</span> <span class="n">Session</span><span class="p">()</span>
<span class="c"># execute a string statement</span>
<span class="n">result</span> <span class="o">=</span> <span class="n">session</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="s">"select * from table where id=:id"</span><span class="p">,</span> <span class="p">{</span><span class="s">'id'</span><span class="p">:</span><span class="mi">7</span><span class="p">})</span>
<span class="c"># execute a SQL expression construct</span>
<span class="n">result</span> <span class="o">=</span> <span class="n">session</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="n">select</span><span class="p">([</span><span class="n">mytable</span><span class="p">])</span><span class="o">.</span><span class="n">where</span><span class="p">(</span><span class="n">mytable</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">id</span><span class="o">==</span><span class="mi">7</span><span class="p">))</span></pre></div>
</div>
<p>The current <a class="reference internal" href="reference/sqlalchemy/connections.html#sqlalchemy.engine.base.Connection" title="sqlalchemy.engine.base.Connection"><tt class="xref py py-class docutils literal"><span class="pre">Connection</span></tt></a> held by the <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.session.Session" title="sqlalchemy.orm.session.Session"><tt class="xref py py-class docutils literal"><span class="pre">Session</span></tt></a> is accessible using the <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.session.Session.connection" title="sqlalchemy.orm.session.Session.connection"><tt class="xref py py-func docutils literal"><span class="pre">connection()</span></tt></a> method:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">connection</span> <span class="o">=</span> <span class="n">session</span><span class="o">.</span><span class="n">connection</span><span class="p">()</span></pre></div>
</div>
<p>The examples above deal with a <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.session.Session" title="sqlalchemy.orm.session.Session"><tt class="xref py py-class docutils literal"><span class="pre">Session</span></tt></a> that’s bound to a single <a class="reference internal" href="reference/sqlalchemy/connections.html#sqlalchemy.engine.base.Engine" title="sqlalchemy.engine.base.Engine"><tt class="xref py py-class docutils literal"><span class="pre">Engine</span></tt></a> or <a class="reference internal" href="reference/sqlalchemy/connections.html#sqlalchemy.engine.base.Connection" title="sqlalchemy.engine.base.Connection"><tt class="xref py py-class docutils literal"><span class="pre">Connection</span></tt></a>. To execute statements using a <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.session.Session" title="sqlalchemy.orm.session.Session"><tt class="xref py py-class docutils literal"><span class="pre">Session</span></tt></a> which is bound either to multiple engines, or none at all (i.e. relies upon bound metadata), both <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.session.Session.execute" title="sqlalchemy.orm.session.Session.execute"><tt class="xref py py-func docutils literal"><span class="pre">execute()</span></tt></a> and <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.session.Session.connection" title="sqlalchemy.orm.session.Session.connection"><tt class="xref py py-func docutils literal"><span class="pre">connection()</span></tt></a> accept a <tt class="docutils literal"><span class="pre">mapper</span></tt> keyword argument, which is passed a mapped class or <a class="reference internal" href="reference/orm/mapping.html#sqlalchemy.orm.mapper.Mapper" title="sqlalchemy.orm.mapper.Mapper"><tt class="xref py py-class docutils literal"><span class="pre">Mapper</span></tt></a> instance, which is used to locate the proper context for the desired engine:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">Session</span> <span class="o">=</span> <span class="n">sessionmaker</span><span class="p">()</span>
<span class="n">session</span> <span class="o">=</span> <span class="n">Session</span><span class="p">()</span>
<span class="c"># need to specify mapper or class when executing</span>
<span class="n">result</span> <span class="o">=</span> <span class="n">session</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="s">"select * from table where id=:id"</span><span class="p">,</span> <span class="p">{</span><span class="s">'id'</span><span class="p">:</span><span class="mi">7</span><span class="p">},</span> <span class="n">mapper</span><span class="o">=</span><span class="n">MyMappedClass</span><span class="p">)</span>
<span class="n">result</span> <span class="o">=</span> <span class="n">session</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="n">select</span><span class="p">([</span><span class="n">mytable</span><span class="p">],</span> <span class="n">mytable</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">id</span><span class="o">==</span><span class="mi">7</span><span class="p">),</span> <span class="n">mapper</span><span class="o">=</span><span class="n">MyMappedClass</span><span class="p">)</span>
<span class="n">connection</span> <span class="o">=</span> <span class="n">session</span><span class="o">.</span><span class="n">connection</span><span class="p">(</span><span class="n">MyMappedClass</span><span class="p">)</span></pre></div>
</div>
</div>
<div class="section" id="joining-a-session-into-an-external-transaction">
<h2>Joining a Session into an External Transaction<a class="headerlink" href="#joining-a-session-into-an-external-transaction" title="Permalink to this headline">¶</a></h2>
<p>If a <a class="reference internal" href="reference/sqlalchemy/connections.html#sqlalchemy.engine.base.Connection" title="sqlalchemy.engine.base.Connection"><tt class="xref py py-class docutils literal"><span class="pre">Connection</span></tt></a> is being used which is already in a transactional state (i.e. has a <a class="reference internal" href="reference/sqlalchemy/connections.html#sqlalchemy.engine.base.Transaction" title="sqlalchemy.engine.base.Transaction"><tt class="xref py py-class docutils literal"><span class="pre">Transaction</span></tt></a>), a <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.session.Session" title="sqlalchemy.orm.session.Session"><tt class="xref py py-class docutils literal"><span class="pre">Session</span></tt></a> can be made to participate within that transaction by just binding the <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.session.Session" title="sqlalchemy.orm.session.Session"><tt class="xref py py-class docutils literal"><span class="pre">Session</span></tt></a> to that <a class="reference internal" href="reference/sqlalchemy/connections.html#sqlalchemy.engine.base.Connection" title="sqlalchemy.engine.base.Connection"><tt class="xref py py-class docutils literal"><span class="pre">Connection</span></tt></a>:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">Session</span> <span class="o">=</span> <span class="n">sessionmaker</span><span class="p">()</span>
<span class="c"># non-ORM connection + transaction</span>
<span class="n">conn</span> <span class="o">=</span> <span class="n">engine</span><span class="o">.</span><span class="n">connect</span><span class="p">()</span>
<span class="n">trans</span> <span class="o">=</span> <span class="n">conn</span><span class="o">.</span><span class="n">begin</span><span class="p">()</span>
<span class="c"># create a Session, bind to the connection</span>
<span class="n">session</span> <span class="o">=</span> <span class="n">Session</span><span class="p">(</span><span class="n">bind</span><span class="o">=</span><span class="n">conn</span><span class="p">)</span>
<span class="c"># ... work with session</span>
<span class="n">session</span><span class="o">.</span><span class="n">commit</span><span class="p">()</span> <span class="c"># commit the session</span>
<span class="n">session</span><span class="o">.</span><span class="n">close</span><span class="p">()</span> <span class="c"># close it out, prohibit further actions</span>
<span class="n">trans</span><span class="o">.</span><span class="n">commit</span><span class="p">()</span> <span class="c"># commit the actual transaction</span></pre></div>
</div>
<p>Note that above, we issue a <tt class="docutils literal"><span class="pre">commit()</span></tt> both on the <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.session.Session" title="sqlalchemy.orm.session.Session"><tt class="xref py py-class docutils literal"><span class="pre">Session</span></tt></a> as well as the <a class="reference internal" href="reference/sqlalchemy/connections.html#sqlalchemy.engine.base.Transaction" title="sqlalchemy.engine.base.Transaction"><tt class="xref py py-class docutils literal"><span class="pre">Transaction</span></tt></a>. This is an example of where we take advantage of <a class="reference internal" href="reference/sqlalchemy/connections.html#sqlalchemy.engine.base.Connection" title="sqlalchemy.engine.base.Connection"><tt class="xref py py-class docutils literal"><span class="pre">Connection</span></tt></a>‘s ability to maintain <em>subtransactions</em>, or nested begin/commit pairs. The <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.session.Session" title="sqlalchemy.orm.session.Session"><tt class="xref py py-class docutils literal"><span class="pre">Session</span></tt></a> is used exactly as though it were managing the transaction on its own; its <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.session.Session.commit" title="sqlalchemy.orm.session.Session.commit"><tt class="xref py py-func docutils literal"><span class="pre">commit()</span></tt></a> method issues its <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.session.Session.flush" title="sqlalchemy.orm.session.Session.flush"><tt class="xref py py-func docutils literal"><span class="pre">flush()</span></tt></a>, and commits the subtransaction. The subsequent transaction the <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.session.Session" title="sqlalchemy.orm.session.Session"><tt class="xref py py-class docutils literal"><span class="pre">Session</span></tt></a> starts after commit will not begin until it’s next used. Above we issue a <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.session.Session.close" title="sqlalchemy.orm.session.Session.close"><tt class="xref py py-func docutils literal"><span class="pre">close()</span></tt></a> to prevent this from occurring. Finally, the actual transaction is committed using <tt class="docutils literal"><span class="pre">Transaction.commit()</span></tt>.</p>
<p>When using the <tt class="docutils literal"><span class="pre">threadlocal</span></tt> engine context, the process above is simplified; the <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.session.Session" title="sqlalchemy.orm.session.Session"><tt class="xref py py-class docutils literal"><span class="pre">Session</span></tt></a> uses the same connection/transaction as everyone else in the current thread, whether or not you explicitly bind it:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">engine</span> <span class="o">=</span> <span class="n">create_engine</span><span class="p">(</span><span class="s">'postgresql://mydb'</span><span class="p">,</span> <span class="n">strategy</span><span class="o">=</span><span class="s">"threadlocal"</span><span class="p">)</span>
<span class="n">engine</span><span class="o">.</span><span class="n">begin</span><span class="p">()</span>
<span class="n">session</span> <span class="o">=</span> <span class="n">Session</span><span class="p">()</span> <span class="c"># session takes place in the transaction like everyone else</span>
<span class="c"># ... go nuts</span>
<span class="n">engine</span><span class="o">.</span><span class="n">commit</span><span class="p">()</span> <span class="c"># commit the transaction</span></pre></div>
</div>
</div>
<div class="section" id="contextual-thread-local-sessions">
<span id="unitofwork-contextual"></span><h2>Contextual/Thread-local Sessions<a class="headerlink" href="#contextual-thread-local-sessions" title="Permalink to this headline">¶</a></h2>
<p>A common need in applications, particularly those built around web frameworks, is the ability to “share” a <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.session.Session" title="sqlalchemy.orm.session.Session"><tt class="xref py py-class docutils literal"><span class="pre">Session</span></tt></a> object among disparate parts of an application, without needing to pass the object explicitly to all method and function calls. What you’re really looking for is some kind of “global” session object, or at least “global” to all the parts of an application which are tasked with servicing the current request. For this pattern, SQLAlchemy provides the ability to enhance the <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.session.Session" title="sqlalchemy.orm.session.Session"><tt class="xref py py-class docutils literal"><span class="pre">Session</span></tt></a> class generated by <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.sessionmaker" title="sqlalchemy.orm.sessionmaker"><tt class="xref py py-func docutils literal"><span class="pre">sessionmaker()</span></tt></a> to provide auto-contextualizing support. This means that whenever you create a <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.session.Session" title="sqlalchemy.orm.session.Session"><tt class="xref py py-class docutils literal"><span class="pre">Session</span></tt></a> instance with its constructor, you get an <em>existing</em> <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.session.Session" title="sqlalchemy.orm.session.Session"><tt class="xref py py-class docutils literal"><span class="pre">Session</span></tt></a> object which is bound to some “context”. By default, this context is the current thread. This feature is what previously was accomplished using the <tt class="docutils literal"><span class="pre">sessioncontext</span></tt> SQLAlchemy extension.</p>
<div class="section" id="creating-a-thread-local-context">
<h3>Creating a Thread-local Context<a class="headerlink" href="#creating-a-thread-local-context" title="Permalink to this headline">¶</a></h3>
<p>The <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.scoped_session" title="sqlalchemy.orm.scoped_session"><tt class="xref py py-func docutils literal"><span class="pre">scoped_session()</span></tt></a> function wraps around the <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.sessionmaker" title="sqlalchemy.orm.sessionmaker"><tt class="xref py py-func docutils literal"><span class="pre">sessionmaker()</span></tt></a> function, and produces an object which behaves the same as the <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.session.Session" title="sqlalchemy.orm.session.Session"><tt class="xref py py-class docutils literal"><span class="pre">Session</span></tt></a> subclass returned by <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.sessionmaker" title="sqlalchemy.orm.sessionmaker"><tt class="xref py py-func docutils literal"><span class="pre">sessionmaker()</span></tt></a>:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="kn">from</span> <span class="nn">sqlalchemy.orm</span> <span class="kn">import</span> <span class="n">scoped_session</span><span class="p">,</span> <span class="n">sessionmaker</span>
<span class="n">Session</span> <span class="o">=</span> <span class="n">scoped_session</span><span class="p">(</span><span class="n">sessionmaker</span><span class="p">())</span></pre></div>
</div>
<p>However, when you instantiate this <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.session.Session" title="sqlalchemy.orm.session.Session"><tt class="xref py py-class docutils literal"><span class="pre">Session</span></tt></a> “class”, in reality the object is pulled from a threadlocal variable, or if it doesn’t exist yet, it’s created using the underlying class generated by <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.sessionmaker" title="sqlalchemy.orm.sessionmaker"><tt class="xref py py-func docutils literal"><span class="pre">sessionmaker()</span></tt></a>:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="gp">>>> </span><span class="c"># call Session() the first time. the new Session instance is created.</span>
<span class="gp">>>> </span><span class="n">session</span> <span class="o">=</span> <span class="n">Session</span><span class="p">()</span>
<span class="gp">>>> </span><span class="c"># later, in the same application thread, someone else calls Session()</span>
<span class="gp">>>> </span><span class="n">session2</span> <span class="o">=</span> <span class="n">Session</span><span class="p">()</span>
<span class="gp">>>> </span><span class="c"># the two Session objects are *the same* object</span>
<span class="gp">>>> </span><span class="n">session</span> <span class="ow">is</span> <span class="n">session2</span>
<span class="go">True</span></pre></div>
</div>
<p>Since the <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.session.Session" title="sqlalchemy.orm.session.Session"><tt class="xref py py-class docutils literal"><span class="pre">Session()</span></tt></a> constructor now returns the same <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.session.Session" title="sqlalchemy.orm.session.Session"><tt class="xref py py-class docutils literal"><span class="pre">Session</span></tt></a> object every time within the current thread, the object returned by <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.scoped_session" title="sqlalchemy.orm.scoped_session"><tt class="xref py py-func docutils literal"><span class="pre">scoped_session()</span></tt></a> also implements most of the <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.session.Session" title="sqlalchemy.orm.session.Session"><tt class="xref py py-class docutils literal"><span class="pre">Session</span></tt></a> methods and properties at the “class” level, such that you don’t even need to instantiate <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.session.Session" title="sqlalchemy.orm.session.Session"><tt class="xref py py-class docutils literal"><span class="pre">Session()</span></tt></a>:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="c"># create some objects</span>
<span class="n">u1</span> <span class="o">=</span> <span class="n">User</span><span class="p">()</span>
<span class="n">u2</span> <span class="o">=</span> <span class="n">User</span><span class="p">()</span>
<span class="c"># save to the contextual session, without instantiating</span>
<span class="n">Session</span><span class="o">.</span><span class="n">add</span><span class="p">(</span><span class="n">u1</span><span class="p">)</span>
<span class="n">Session</span><span class="o">.</span><span class="n">add</span><span class="p">(</span><span class="n">u2</span><span class="p">)</span>
<span class="c"># view the "new" attribute</span>
<span class="k">assert</span> <span class="n">u1</span> <span class="ow">in</span> <span class="n">Session</span><span class="o">.</span><span class="n">new</span>
<span class="c"># commit changes</span>
<span class="n">Session</span><span class="o">.</span><span class="n">commit</span><span class="p">()</span></pre></div>
</div>
<p>The contextual session may be disposed of by calling <tt class="docutils literal"><span class="pre">Session.remove()</span></tt>:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="c"># remove current contextual session</span>
<span class="n">Session</span><span class="o">.</span><span class="n">remove</span><span class="p">()</span></pre></div>
</div>
<p>After <tt class="docutils literal"><span class="pre">remove()</span></tt> is called, the next operation with the contextual session will start a new <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.session.Session" title="sqlalchemy.orm.session.Session"><tt class="xref py py-class docutils literal"><span class="pre">Session</span></tt></a> for the current thread.</p>
</div>
<div class="section" id="lifespan-of-a-contextual-session">
<span id="session-lifespan"></span><h3>Lifespan of a Contextual Session<a class="headerlink" href="#lifespan-of-a-contextual-session" title="Permalink to this headline">¶</a></h3>
<p>A (really, really) common question is when does the contextual session get created, when does it get disposed ? We’ll consider a typical lifespan as used in a web application:</p>
<div class="highlight-python"><pre>Web Server Web Framework User-defined Controller Call
-------------- -------------- ------------------------------
web request ->
call controller -> # call Session(). this establishes a new,
# contextual Session.
session = Session()
# load some objects, save some changes
objects = session.query(MyClass).all()
# some other code calls Session, it's the
# same contextual session as "sess"
session2 = Session()
session2.add(foo)
session2.commit()
# generate content to be returned
return generate_content()
Session.remove() <-
web response <-</pre>
</div>
<p>The above example illustrates an explicit call to <tt class="docutils literal"><span class="pre">Session.remove()</span></tt>. This has the effect such that each web request starts fresh with a brand new session. When integrating with a web framework, there’s actually many options on how to proceed for this step:</p>
<ul class="simple">
<li>Session.remove() - this is the most cut and dry approach; the <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.session.Session" title="sqlalchemy.orm.session.Session"><tt class="xref py py-class docutils literal"><span class="pre">Session</span></tt></a> is thrown away, all of its transactional resources rolled back and connections checked back to the connection pool. A new <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.session.Session" title="sqlalchemy.orm.session.Session"><tt class="xref py py-class docutils literal"><span class="pre">Session</span></tt></a> will be used on the next request.</li>
<li>Session.close() - Similar to calling <tt class="docutils literal"><span class="pre">remove()</span></tt>, in that all objects are explicitly expunged, transactional resources are rolled back, connection resources checked back into the connection pool, except the actual <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.session.Session" title="sqlalchemy.orm.session.Session"><tt class="xref py py-class docutils literal"><span class="pre">Session</span></tt></a> object hangs around. It doesn’t make too much difference here unless the start of the web request would like to pass specific options to the initial construction of <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.session.Session" title="sqlalchemy.orm.session.Session"><tt class="xref py py-class docutils literal"><span class="pre">Session()</span></tt></a>, such as a specific <a class="reference internal" href="reference/sqlalchemy/connections.html#sqlalchemy.engine.base.Engine" title="sqlalchemy.engine.base.Engine"><tt class="xref py py-class docutils literal"><span class="pre">Engine</span></tt></a> to bind to.</li>
<li>Session.commit() - In this case, the behavior is that any remaining changes pending are flushed, and the transaction is committed; connection resources are returned to the connection pool. The full state of the session is expired, so that when the next web request is started, all data will be reloaded. In reality, the contents of the <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.session.Session" title="sqlalchemy.orm.session.Session"><tt class="xref py py-class docutils literal"><span class="pre">Session</span></tt></a> are weakly referenced anyway so its likely that it will be empty on the next request in any case.</li>
<li>Session.rollback() - Similar to calling commit, except we assume that the user would have called commit explicitly if that was desired; the <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.session.Session.rollback" title="sqlalchemy.orm.session.Session.rollback"><tt class="xref py py-func docutils literal"><span class="pre">rollback()</span></tt></a> ensures that no transactional state remains, returns connections to the connection pool, and expires all data, in the case that the request was aborted and did not roll back itself.</li>
<li>do nothing - this is a valid option as well. The controller code is responsible for doing one of the above steps at the end of the request.</li>
</ul>
<p>Scoped Session API docs: <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.scoped_session" title="sqlalchemy.orm.scoped_session"><tt class="xref py py-func docutils literal"><span class="pre">sqlalchemy.orm.scoped_session()</span></tt></a></p>
</div>
</div>
<div class="section" id="partitioning-strategies">
<span id="session-partitioning"></span><h2>Partitioning Strategies<a class="headerlink" href="#partitioning-strategies" title="Permalink to this headline">¶</a></h2>
<div class="section" id="vertical-partitioning">
<h3>Vertical Partitioning<a class="headerlink" href="#vertical-partitioning" title="Permalink to this headline">¶</a></h3>
<p>Vertical partitioning places different kinds of objects, or different tables, across multiple databases:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">engine1</span> <span class="o">=</span> <span class="n">create_engine</span><span class="p">(</span><span class="s">'postgresql://db1'</span><span class="p">)</span>
<span class="n">engine2</span> <span class="o">=</span> <span class="n">create_engine</span><span class="p">(</span><span class="s">'postgresql://db2'</span><span class="p">)</span>
<span class="n">Session</span> <span class="o">=</span> <span class="n">sessionmaker</span><span class="p">(</span><span class="n">twophase</span><span class="o">=</span><span class="bp">True</span><span class="p">)</span>
<span class="c"># bind User operations to engine 1, Account operations to engine 2</span>
<span class="n">Session</span><span class="o">.</span><span class="n">configure</span><span class="p">(</span><span class="n">binds</span><span class="o">=</span><span class="p">{</span><span class="n">User</span><span class="p">:</span><span class="n">engine1</span><span class="p">,</span> <span class="n">Account</span><span class="p">:</span><span class="n">engine2</span><span class="p">})</span>
<span class="n">session</span> <span class="o">=</span> <span class="n">Session</span><span class="p">()</span></pre></div>
</div>
</div>
<div class="section" id="horizontal-partitioning">
<h3>Horizontal Partitioning<a class="headerlink" href="#horizontal-partitioning" title="Permalink to this headline">¶</a></h3>
<p>Horizontal partitioning partitions the rows of a single table (or a set of tables) across multiple databases.</p>
<p>See the “sharding” example in <a class="reference external" href="http://www.sqlalchemy.org/trac/browser/sqlalchemy/trunk/examples/sharding/attribute_shard.py">attribute_shard.py</a></p>
</div>
</div>
<div class="section" id="extending-session">
<h2>Extending Session<a class="headerlink" href="#extending-session" title="Permalink to this headline">¶</a></h2>
<p>Extending the session can be achieved through subclassing as well as through a simple extension class, which resembles the style of <a class="reference internal" href="mappers.html#extending-mapper"><em>Extending Mapper</em></a> called <a class="reference internal" href="reference/orm/interfaces.html#sqlalchemy.orm.interfaces.SessionExtension" title="sqlalchemy.orm.interfaces.SessionExtension"><tt class="xref py py-class docutils literal"><span class="pre">SessionExtension</span></tt></a>. See the docstrings for more information on this class’ methods.</p>
<p>Basic usage is similar to <a class="reference internal" href="reference/orm/interfaces.html#sqlalchemy.orm.interfaces.MapperExtension" title="sqlalchemy.orm.interfaces.MapperExtension"><tt class="xref py py-class docutils literal"><span class="pre">MapperExtension</span></tt></a>:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="k">class</span> <span class="nc">MySessionExtension</span><span class="p">(</span><span class="n">SessionExtension</span><span class="p">):</span>
<span class="k">def</span> <span class="nf">before_commit</span><span class="p">(</span><span class="bp">self</span><span class="p">,</span> <span class="n">session</span><span class="p">):</span>
<span class="k">print</span> <span class="s">"before commit!"</span>
<span class="n">Session</span> <span class="o">=</span> <span class="n">sessionmaker</span><span class="p">(</span><span class="n">extension</span><span class="o">=</span><span class="n">MySessionExtension</span><span class="p">())</span></pre></div>
</div>
<p>or with <a class="reference internal" href="reference/orm/sessions.html#sqlalchemy.orm.create_session" title="sqlalchemy.orm.create_session"><tt class="xref py py-func docutils literal"><span class="pre">create_session()</span></tt></a>:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">session</span> <span class="o">=</span> <span class="n">create_session</span><span class="p">(</span><span class="n">extension</span><span class="o">=</span><span class="n">MySessionExtension</span><span class="p">())</span></pre></div>
</div>
<p>The same <a class="reference internal" href="reference/orm/interfaces.html#sqlalchemy.orm.interfaces.SessionExtension" title="sqlalchemy.orm.interfaces.SessionExtension"><tt class="xref py py-class docutils literal"><span class="pre">SessionExtension</span></tt></a> instance can be used with any number of sessions.</p>
</div>
</div>
</div>
</div>
<div class="bottomnav">
<div class="prevnext">
Previous:
<a href="mappers.html" title="previous chapter">Mapper Configuration</a>
Next:
<a href="dbengine.html" title="next chapter">Database Engines</a>
</div>
<div class="doc_copyright">
© Copyright 2007, 2008, 2009, 2010, the SQLAlchemy authors and contributors.
Created using <a href="http://sphinx.pocoo.org/">Sphinx</a> 1.0b2+.
</div>
</div>
</body>
</html>
|
