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
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
|
<!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>
Database Engines
— 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 Meta Data" href="metadata.html" />
<link rel="prev" title="Using the Session" href="session.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/dbengine.txt">view source)</div>
</div>
<div class="navbanner">
<a class="totoc" href="index.html">Table of Contents</a>
»
Database Engines
<div class="prevnext">
Previous:
<a href="session.html" title="previous chapter">Using the Session</a>
Next:
<a href="metadata.html" title="next chapter">Database Meta Data</a>
</div>
<h2>
Database Engines
</h2>
</div>
<ul>
<li><a class="reference internal" href="#">Database Engines</a><ul>
<li><a class="reference internal" href="#supported-databases">Supported Databases</a></li>
<li><a class="reference internal" href="#create-engine-url-arguments">create_engine() URL Arguments</a><ul>
<li><a class="reference internal" href="#custom-dbapi-connect-arguments">Custom DBAPI connect() arguments</a></li>
</ul>
</li>
<li><a class="reference internal" href="#database-engine-options">Database Engine Options</a></li>
<li><a class="reference internal" href="#more-on-connections">More On Connections</a></li>
<li><a class="reference internal" href="#using-transactions-with-connection">Using Transactions with Connection</a><ul>
<li><a class="reference internal" href="#understanding-autocommit">Understanding Autocommit</a></li>
</ul>
</li>
<li><a class="reference internal" href="#connectionless-execution-implicit-execution">Connectionless Execution, Implicit Execution</a><ul>
<li><a class="reference internal" href="#using-the-threadlocal-execution-strategy">Using the Threadlocal Execution Strategy</a></li>
</ul>
</li>
<li><a class="reference internal" href="#configuring-logging">Configuring Logging</a></li>
</ul>
</li>
</ul>
<div class="clearboth"></div>
</div>
<div class="document">
<div class="body">
<div class="section" id="database-engines">
<span id="engines-toplevel"></span><h1>Database Engines<a class="headerlink" href="#database-engines" title="Permalink to this headline">¶</a></h1>
<p>The <strong>Engine</strong> is the starting point for any SQLAlchemy application. It’s “home base” for the actual database and its DBAPI, delivered to the SQLAlchemy application through a connection pool and a <strong>Dialect</strong>, which describes how to talk to a specific kind of database/DBAPI combination.</p>
<p>The general structure is this:</p>
<div class="highlight-python"><pre> +-----------+ __________
/---| Pool |---\ (__________)
+-------------+ / +-----------+ \ +--------+ | |
connect() <--| Engine |---x x----| DBAPI |---| database |
+-------------+ \ +-----------+ / +--------+ | |
\---| Dialect |---/ |__________|
+-----------+ (__________)</pre>
</div>
<p>Where above, a <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> references both a <a class="reference internal" href="reference/sqlalchemy/connections.html#sqlalchemy.engine.base.Dialect" title="sqlalchemy.engine.base.Dialect"><tt class="xref py py-class docutils literal"><span class="pre">Dialect</span></tt></a> and <a class="reference internal" href="reference/sqlalchemy/pooling.html#sqlalchemy.pool.Pool" title="sqlalchemy.pool.Pool"><tt class="xref py py-class docutils literal"><span class="pre">Pool</span></tt></a>, which together interpret the DBAPI’s module functions as well as the behavior of the database.</p>
<p>Creating an engine is just a matter of issuing a single call, <tt class="xref py py-func docutils literal"><span class="pre">create_engine()</span></tt>:</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://scott:tiger@localhost:5432/mydatabase'</span><span class="p">)</span></pre></div>
</div>
<p>The above engine invokes the <tt class="docutils literal"><span class="pre">postgresql</span></tt> dialect and a connection pool which references <tt class="docutils literal"><span class="pre">localhost:5432</span></tt>.</p>
<p>Note that the appropriate usage of <tt class="xref py py-func docutils literal"><span class="pre">create_engine()</span></tt> is once per particular configuration, held globally for the lifetime of a single application process (not including child processes via <tt class="docutils literal"><span class="pre">fork()</span></tt> - these would require a new engine). 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> manages connections on behalf of the process and is intended to be called upon in a concurrent fashion. Creating engines for each particular operation is not the intended usage.</p>
<p>The engine can be used directly to issue SQL to the database. The most generic way is to use connections, which you get via the <tt class="docutils literal"><span class="pre">connect()</span></tt> method:</p>
<div class="highlight-python"><div class="highlight"><pre><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="n">result</span> <span class="o">=</span> <span class="n">connection</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="s">"select username from users"</span><span class="p">)</span>
<span class="k">for</span> <span class="n">row</span> <span class="ow">in</span> <span class="n">result</span><span class="p">:</span>
<span class="k">print</span> <span class="s">"username:"</span><span class="p">,</span> <span class="n">row</span><span class="p">[</span><span class="s">'username'</span><span class="p">]</span>
<span class="n">connection</span><span class="o">.</span><span class="n">close</span><span class="p">()</span></pre></div>
</div>
<p>The connection is an instance 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>, which is a <strong>proxy</strong> object for an actual DBAPI connection. The returned result is an instance of <tt class="xref py py-class docutils literal"><span class="pre">ResultProxy</span></tt>, which acts very much like a DBAPI cursor.</p>
<p>When you say <tt class="docutils literal"><span class="pre">engine.connect()</span></tt>, a new <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 is created, and a DBAPI connection is retrieved from the connection pool. Later, when you call <tt class="docutils literal"><span class="pre">connection.close()</span></tt>, the DBAPI connection is returned to the pool; nothing is actually “closed” from the perspective of the database.</p>
<p>To execute some SQL more quickly, you can skip the <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> part and just say:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">result</span> <span class="o">=</span> <span class="n">engine</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="s">"select username from users"</span><span class="p">)</span>
<span class="k">for</span> <span class="n">row</span> <span class="ow">in</span> <span class="n">result</span><span class="p">:</span>
<span class="k">print</span> <span class="s">"username:"</span><span class="p">,</span> <span class="n">row</span><span class="p">[</span><span class="s">'username'</span><span class="p">]</span>
<span class="n">result</span><span class="o">.</span><span class="n">close</span><span class="p">()</span></pre></div>
</div>
<p>Where above, the <tt class="docutils literal"><span class="pre">execute()</span></tt> method on the <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> does the <tt class="docutils literal"><span class="pre">connect()</span></tt> part for you, and returns the <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> directly. The actual <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 <em>inside</em> the <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>, waiting for you to finish reading the result. In this case, when you <tt class="docutils literal"><span class="pre">close()</span></tt> the <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>, the underlying <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 closed, which returns the DBAPI connection to the pool.</p>
<p>To summarize the above two examples, when you use 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> object, it’s known as <strong>explicit execution</strong>. When you don’t see the <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, but you still use the <tt class="docutils literal"><span class="pre">execute()</span></tt> method on the <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>, it’s called <strong>explicit, connectionless execution</strong>. A third variant of execution also exists called <strong>implicit execution</strong>; this will be described later.</p>
<p>The <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> can do a lot more than what we illustrated above; SQL strings are only its most rudimentary function. Later chapters will describe how “constructed SQL” expressions can be used with engines; in many cases, you don’t have to deal with the <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 all after it’s created. The Object Relational Mapper (ORM), an optional feature of SQLAlchemy, also uses the <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> in order to get at connections; that’s also a case where you can often create the engine once, and then forget about it.</p>
<div class="section" id="supported-databases">
<span id="supported-dbapis"></span><h2>Supported Databases<a class="headerlink" href="#supported-databases" title="Permalink to this headline">¶</a></h2>
<p>SQLAlchemy includes many <a class="reference internal" href="reference/sqlalchemy/connections.html#sqlalchemy.engine.base.Dialect" title="sqlalchemy.engine.base.Dialect"><tt class="xref py py-class docutils literal"><span class="pre">Dialect</span></tt></a> implementations for various
backends; each is described as its own package in the <a class="reference internal" href="reference/dialects/index.html"><em>sqlalchemy.dialects</em></a> package. A
SQLAlchemy dialect always requires that an appropriate DBAPI driver is installed.</p>
<p>The table below summarizes the state of DBAPI support in SQLAlchemy 0.6. The values
translate as:</p>
<ul class="simple">
<li>yes / Python platform - The SQLAlchemy dialect is mostly or fully operational on the target platform.</li>
<li>yes / OS platform - The DBAPI supports that platform.</li>
<li>no / Python platform - The DBAPI does not support that platform, or there is no SQLAlchemy dialect support.</li>
<li>no / OS platform - The DBAPI does not support that platform.</li>
<li>partial - the DBAPI is partially usable on the target platform but has major unresolved issues.</li>
<li>development - a development version of the dialect exists, but is not yet usable.</li>
<li>thirdparty - the dialect itself is maintained by a third party, who should be consulted for
information on current support.</li>
<li>* - indicates the given DBAPI is the “default” for SQLAlchemy, i.e. when just the database name is specified</li>
</ul>
<table border="1" class="docutils">
<colgroup>
<col width="22%" />
<col width="24%" />
<col width="10%" />
<col width="10%" />
<col width="10%" />
<col width="15%" />
<col width="11%" />
</colgroup>
<thead valign="bottom">
<tr><th class="head">Driver</th>
<th class="head">Connect string</th>
<th class="head">Py2K</th>
<th class="head">Py3K</th>
<th class="head">Jython</th>
<th class="head">Unix</th>
<th class="head">Windows</th>
</tr>
</thead>
<tbody valign="top">
<tr><td><strong>DB2/Informix IDS</strong></td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
</tr>
<tr><td><a class="reference external" href="http://code.google.com/p/ibm-db/">ibm-db</a></td>
<td>thirdparty</td>
<td>thirdparty</td>
<td>thirdparty</td>
<td>thirdparty</td>
<td>thirdparty</td>
<td>thirdparty</td>
</tr>
<tr><td><strong>Firebird</strong></td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
</tr>
<tr><td><a class="reference external" href="http://firebirdsql.org/index.php?op=devel&sub=python">kinterbasdb</a></td>
<td><tt class="docutils literal"><span class="pre">firebird+kinterbasdb</span></tt>*</td>
<td>yes</td>
<td>development</td>
<td>no</td>
<td>yes</td>
<td>yes</td>
</tr>
<tr><td><strong>Informix</strong></td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
</tr>
<tr><td><a class="reference external" href="http://informixdb.sourceforge.net/">informixdb</a></td>
<td><tt class="docutils literal"><span class="pre">informix+informixdb</span></tt>*</td>
<td>development</td>
<td>development</td>
<td>no</td>
<td>unknown</td>
<td>unknown</td>
</tr>
<tr><td><strong>MaxDB</strong></td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
</tr>
<tr><td><a class="reference external" href="http://www.sapdb.org/sapdbapi.html">sapdb</a></td>
<td><tt class="docutils literal"><span class="pre">maxdb+sapdb</span></tt>*</td>
<td>development</td>
<td>development</td>
<td>no</td>
<td>yes</td>
<td>unknown</td>
</tr>
<tr><td><strong>Microsoft Access</strong></td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
</tr>
<tr><td><a class="reference external" href="http://code.google.com/p/pyodbc/">pyodbc</a></td>
<td><tt class="docutils literal"><span class="pre">access+pyodbc</span></tt>*</td>
<td>development</td>
<td>development</td>
<td>no</td>
<td>unknown</td>
<td>yes</td>
</tr>
<tr><td><strong>Microsoft SQL Server</strong></td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
</tr>
<tr><td><a class="reference external" href="http://adodbapi.sourceforge.net/">adodbapi</a></td>
<td><tt class="docutils literal"><span class="pre">mssql+adodbapi</span></tt></td>
<td>development</td>
<td>development</td>
<td>no</td>
<td>no</td>
<td>yes</td>
</tr>
<tr><td><a class="reference external" href="http://jtds.sourceforge.net/">jTDS JDBC Driver</a></td>
<td><tt class="docutils literal"><span class="pre">mssql+zxjdbc</span></tt></td>
<td>no</td>
<td>no</td>
<td>development</td>
<td>yes</td>
<td>yes</td>
</tr>
<tr><td><a class="reference external" href="http://www.egenix.com/products/python/mxODBC/">mxodbc</a></td>
<td><tt class="docutils literal"><span class="pre">mssql+mxodbc</span></tt></td>
<td>yes</td>
<td>development</td>
<td>no</td>
<td>yes with <a class="reference external" href="http://www.freetds.org/">FreeTDS</a></td>
<td>yes</td>
</tr>
<tr><td><a class="reference external" href="http://code.google.com/p/pyodbc/">pyodbc</a></td>
<td><tt class="docutils literal"><span class="pre">mssql+pyodbc</span></tt>*</td>
<td>yes</td>
<td>development</td>
<td>no</td>
<td>yes with <a class="reference external" href="http://www.freetds.org/">FreeTDS</a></td>
<td>yes</td>
</tr>
<tr><td><a class="reference external" href="http://code.google.com/p/pymssql/">pymssql</a></td>
<td><tt class="docutils literal"><span class="pre">mssql+pymssql</span></tt></td>
<td>yes</td>
<td>development</td>
<td>no</td>
<td>yes</td>
<td>yes</td>
</tr>
<tr><td><strong>MySQL</strong></td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
</tr>
<tr><td><a class="reference external" href="http://dev.mysql.com/downloads/connector/j/">MySQL Connector/J</a></td>
<td><tt class="docutils literal"><span class="pre">mysql+zxjdbc</span></tt></td>
<td>no</td>
<td>no</td>
<td>yes</td>
<td>yes</td>
<td>yes</td>
</tr>
<tr><td><a class="reference external" href="https://launchpad.net/myconnpy">MySQL Connector/Python</a></td>
<td><tt class="docutils literal"><span class="pre">mysql+mysqlconnector</span></tt></td>
<td>yes</td>
<td>partial</td>
<td>no</td>
<td>yes</td>
<td>yes</td>
</tr>
<tr><td><a class="reference external" href="http://sourceforge.net/projects/mysql-python">mysql-python</a></td>
<td><tt class="docutils literal"><span class="pre">mysql+mysqldb</span></tt>*</td>
<td>yes</td>
<td>development</td>
<td>no</td>
<td>yes</td>
<td>yes</td>
</tr>
<tr><td><a class="reference external" href="http://packages.python.org/oursql/">OurSQL</a></td>
<td><tt class="docutils literal"><span class="pre">mysql+oursql</span></tt></td>
<td>yes</td>
<td>partial</td>
<td>no</td>
<td>yes</td>
<td>yes</td>
</tr>
<tr><td><strong>Oracle</strong></td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
</tr>
<tr><td><a class="reference external" href="http://cx-oracle.sourceforge.net/">cx_oracle</a></td>
<td><tt class="docutils literal"><span class="pre">oracle+cx_oracle</span></tt>*</td>
<td>yes</td>
<td>development</td>
<td>no</td>
<td>yes</td>
<td>yes</td>
</tr>
<tr><td><a class="reference external" href="http://www.oracle.com/technology/software/tech/java/sqlj_jdbc/index.html">Oracle JDBC Driver</a></td>
<td><tt class="docutils literal"><span class="pre">oracle+zxjdbc</span></tt></td>
<td>no</td>
<td>no</td>
<td>yes</td>
<td>yes</td>
<td>yes</td>
</tr>
<tr><td><strong>Postgresql</strong></td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
</tr>
<tr><td><a class="reference external" href="http://pybrary.net/pg8000/">pg8000</a></td>
<td><tt class="docutils literal"><span class="pre">postgresql+pg8000</span></tt></td>
<td>yes</td>
<td>yes</td>
<td>no</td>
<td>yes</td>
<td>yes</td>
</tr>
<tr><td><a class="reference external" href="http://jdbc.postgresql.org/">PostgreSQL JDBC Driver</a></td>
<td><tt class="docutils literal"><span class="pre">postgresql+zxjdbc</span></tt></td>
<td>no</td>
<td>no</td>
<td>yes</td>
<td>yes</td>
<td>yes</td>
</tr>
<tr><td><a class="reference external" href="http://www.initd.org/">psycopg2</a></td>
<td><tt class="docutils literal"><span class="pre">postgresql+psycopg2</span></tt>*</td>
<td>yes</td>
<td>development</td>
<td>no</td>
<td>yes</td>
<td>yes</td>
</tr>
<tr><td><a class="reference external" href="http://python.projects.postgresql.org/">pypostgresql</a></td>
<td><tt class="docutils literal"><span class="pre">postgresql+pypostgresql</span></tt></td>
<td>no</td>
<td>yes</td>
<td>no</td>
<td>yes</td>
<td>yes</td>
</tr>
<tr><td><strong>SQLite</strong></td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
</tr>
<tr><td><a class="reference external" href="http://pypi.python.org/pypi/pysqlite/">pysqlite</a></td>
<td><tt class="docutils literal"><span class="pre">sqlite+pysqlite</span></tt>*</td>
<td>yes</td>
<td>yes</td>
<td>no</td>
<td>yes</td>
<td>yes</td>
</tr>
<tr><td><a class="reference external" href="http://docs.python.org/library/sqlite3.html">sqlite3</a></td>
<td><tt class="docutils literal"><span class="pre">sqlite+pysqlite</span></tt>*</td>
<td>yes</td>
<td>yes</td>
<td>no</td>
<td>yes</td>
<td>yes</td>
</tr>
<tr><td><strong>Sybase ASE</strong></td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
</tr>
<tr><td><a class="reference external" href="http://www.egenix.com/products/python/mxODBC/">mxodbc</a></td>
<td><tt class="docutils literal"><span class="pre">sybase+mxodbc</span></tt></td>
<td>development</td>
<td>development</td>
<td>no</td>
<td>yes</td>
<td>yes</td>
</tr>
<tr><td><a class="reference external" href="http://code.google.com/p/pyodbc/">pyodbc</a></td>
<td><tt class="docutils literal"><span class="pre">sybase+pyodbc</span></tt>*</td>
<td>partial</td>
<td>development</td>
<td>no</td>
<td>unknown</td>
<td>unknown</td>
</tr>
<tr><td><a class="reference external" href="http://python-sybase.sourceforge.net/">python-sybase</a></td>
<td><tt class="docutils literal"><span class="pre">sybase+pysybase</span></tt></td>
<td>partial</td>
<td>development</td>
<td>no</td>
<td>yes</td>
<td>yes</td>
</tr>
</tbody>
</table>
<p>Further detail on dialects is available at <a class="reference internal" href="reference/dialects/index.html"><em>sqlalchemy.dialects</em></a> as well as additional notes on the wiki at <a class="reference external" href="http://www.sqlalchemy.org/trac/wiki/DatabaseNotes">Database Notes</a></p>
</div>
<div class="section" id="create-engine-url-arguments">
<h2>create_engine() URL Arguments<a class="headerlink" href="#create-engine-url-arguments" title="Permalink to this headline">¶</a></h2>
<p>SQLAlchemy indicates the source of an Engine strictly via <a class="reference external" href="http://rfc.net/rfc1738.html">RFC-1738</a> style URLs, combined with optional keyword arguments to specify options for the Engine. The form of the URL is:</p>
<blockquote>
dialect+driver://username:password@host:port/database</blockquote>
<p>Dialect names include the identifying name of the SQLAlchemy dialect which include <tt class="docutils literal"><span class="pre">sqlite</span></tt>, <tt class="docutils literal"><span class="pre">mysql</span></tt>, <tt class="docutils literal"><span class="pre">postgresql</span></tt>, <tt class="docutils literal"><span class="pre">oracle</span></tt>, <tt class="docutils literal"><span class="pre">mssql</span></tt>, and <tt class="docutils literal"><span class="pre">firebird</span></tt>. The drivername is the name of the DBAPI to be used to connect to the database using all lowercase letters. If not specified, a “default” DBAPI will be imported if available - this default is typically the most widely known driver available for that backend (i.e. cx_oracle, pysqlite/sqlite3, psycopg2, mysqldb). For Jython connections, specify the <cite>zxjdbc</cite> driver, which is the JDBC-DBAPI bridge included with Jython.</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="c"># postgresql - psycopg2 is the default driver.</span>
<span class="n">pg_db</span> <span class="o">=</span> <span class="n">create_engine</span><span class="p">(</span><span class="s">'postgresql://scott:tiger@localhost/mydatabase'</span><span class="p">)</span>
<span class="n">pg_db</span> <span class="o">=</span> <span class="n">create_engine</span><span class="p">(</span><span class="s">'postgresql+psycopg2://scott:tiger@localhost/mydatabase'</span><span class="p">)</span>
<span class="n">pg_db</span> <span class="o">=</span> <span class="n">create_engine</span><span class="p">(</span><span class="s">'postgresql+pg8000://scott:tiger@localhost/mydatabase'</span><span class="p">)</span>
<span class="n">pg_db</span> <span class="o">=</span> <span class="n">create_engine</span><span class="p">(</span><span class="s">'postgresql+pypostgresql://scott:tiger@localhost/mydatabase'</span><span class="p">)</span>
<span class="c"># postgresql on Jython</span>
<span class="n">pg_db</span> <span class="o">=</span> <span class="n">create_engine</span><span class="p">(</span><span class="s">'postgresql+zxjdbc://scott:tiger@localhost/mydatabase'</span><span class="p">)</span>
<span class="c"># mysql - MySQLdb (mysql-python) is the default driver</span>
<span class="n">mysql_db</span> <span class="o">=</span> <span class="n">create_engine</span><span class="p">(</span><span class="s">'mysql://scott:tiger@localhost/foo'</span><span class="p">)</span>
<span class="n">mysql_db</span> <span class="o">=</span> <span class="n">create_engine</span><span class="p">(</span><span class="s">'mysql+mysqldb://scott:tiger@localhost/foo'</span><span class="p">)</span>
<span class="c"># mysql on Jython</span>
<span class="n">mysql_db</span> <span class="o">=</span> <span class="n">create_engine</span><span class="p">(</span><span class="s">'mysql+zxjdbc://localhost/foo'</span><span class="p">)</span>
<span class="c"># mysql with pyodbc (buggy)</span>
<span class="n">mysql_db</span> <span class="o">=</span> <span class="n">create_engine</span><span class="p">(</span><span class="s">'mysql+pyodbc://scott:tiger@some_dsn'</span><span class="p">)</span>
<span class="c"># oracle - cx_oracle is the default driver</span>
<span class="n">oracle_db</span> <span class="o">=</span> <span class="n">create_engine</span><span class="p">(</span><span class="s">'oracle://scott:tiger@127.0.0.1:1521/sidname'</span><span class="p">)</span>
<span class="c"># oracle via TNS name</span>
<span class="n">oracle_db</span> <span class="o">=</span> <span class="n">create_engine</span><span class="p">(</span><span class="s">'oracle+cx_oracle://scott:tiger@tnsname'</span><span class="p">)</span>
<span class="c"># mssql using ODBC datasource names. PyODBC is the default driver.</span>
<span class="n">mssql_db</span> <span class="o">=</span> <span class="n">create_engine</span><span class="p">(</span><span class="s">'mssql://mydsn'</span><span class="p">)</span>
<span class="n">mssql_db</span> <span class="o">=</span> <span class="n">create_engine</span><span class="p">(</span><span class="s">'mssql+pyodbc://mydsn'</span><span class="p">)</span>
<span class="n">mssql_db</span> <span class="o">=</span> <span class="n">create_engine</span><span class="p">(</span><span class="s">'mssql+adodbapi://mydsn'</span><span class="p">)</span>
<span class="n">mssql_db</span> <span class="o">=</span> <span class="n">create_engine</span><span class="p">(</span><span class="s">'mssql+pyodbc://username:password@mydsn'</span><span class="p">)</span></pre></div>
</div>
<p>SQLite connects to file based databases. The same URL format is used, omitting the hostname, and using the “file” portion as the filename of the database. This has the effect of four slashes being present for an absolute file path:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="c"># sqlite://<nohostname>/<path></span>
<span class="c"># where <path> is relative:</span>
<span class="n">sqlite_db</span> <span class="o">=</span> <span class="n">create_engine</span><span class="p">(</span><span class="s">'sqlite:///foo.db'</span><span class="p">)</span>
<span class="c"># or absolute, starting with a slash:</span>
<span class="n">sqlite_db</span> <span class="o">=</span> <span class="n">create_engine</span><span class="p">(</span><span class="s">'sqlite:////absolute/path/to/foo.db'</span><span class="p">)</span></pre></div>
</div>
<p>To use a SQLite <tt class="docutils literal"><span class="pre">:memory:</span></tt> database, specify an empty URL:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">sqlite_memory_db</span> <span class="o">=</span> <span class="n">create_engine</span><span class="p">(</span><span class="s">'sqlite://'</span><span class="p">)</span></pre></div>
</div>
<p>The <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> will ask the connection pool for a connection when the <tt class="docutils literal"><span class="pre">connect()</span></tt> or <tt class="docutils literal"><span class="pre">execute()</span></tt> methods are called. The default connection pool, <a class="reference internal" href="reference/sqlalchemy/pooling.html#sqlalchemy.pool.QueuePool" title="sqlalchemy.pool.QueuePool"><tt class="xref py py-class docutils literal"><span class="pre">QueuePool</span></tt></a>, as well as the default connection pool used with SQLite, <a class="reference internal" href="reference/sqlalchemy/pooling.html#sqlalchemy.pool.SingletonThreadPool" title="sqlalchemy.pool.SingletonThreadPool"><tt class="xref py py-class docutils literal"><span class="pre">SingletonThreadPool</span></tt></a>, will open connections to the database on an as-needed basis. As concurrent statements are executed, <a class="reference internal" href="reference/sqlalchemy/pooling.html#sqlalchemy.pool.QueuePool" title="sqlalchemy.pool.QueuePool"><tt class="xref py py-class docutils literal"><span class="pre">QueuePool</span></tt></a> will grow its pool of connections to a default size of five, and will allow a default “overflow” of ten. Since the <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> is essentially “home base” for the connection pool, it follows that you should keep 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> per database established within an application, rather than creating a new one for each connection.</p>
<div class="section" id="custom-dbapi-connect-arguments">
<h3>Custom DBAPI connect() arguments<a class="headerlink" href="#custom-dbapi-connect-arguments" title="Permalink to this headline">¶</a></h3>
<p>Custom arguments used when issuing the <tt class="docutils literal"><span class="pre">connect()</span></tt> call to the underlying DBAPI may be issued in three distinct ways. String-based arguments can be passed directly from the URL string as query arguments:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="n">db</span> <span class="o">=</span> <span class="n">create_engine</span><span class="p">(</span><span class="s">'postgresql://scott:tiger@localhost/test?argument1=foo&argument2=bar'</span><span class="p">)</span></pre></div>
</div>
<p>If SQLAlchemy’s database connector is aware of a particular query argument, it may convert its type from string to its proper type.</p>
<p><a class="reference internal" href="reference/sqlalchemy/connections.html#sqlalchemy.create_engine" title="sqlalchemy.create_engine"><tt class="xref py py-func docutils literal"><span class="pre">create_engine()</span></tt></a> also takes an argument <tt class="docutils literal"><span class="pre">connect_args</span></tt> which is an additional dictionary that will be passed to <tt class="docutils literal"><span class="pre">connect()</span></tt>. This can be used when arguments of a type other than string are required, and SQLAlchemy’s database connector has no type conversion logic present for that parameter:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="n">db</span> <span class="o">=</span> <span class="n">create_engine</span><span class="p">(</span><span class="s">'postgresql://scott:tiger@localhost/test'</span><span class="p">,</span> <span class="n">connect_args</span> <span class="o">=</span> <span class="p">{</span><span class="s">'argument1'</span><span class="p">:</span><span class="mi">17</span><span class="p">,</span> <span class="s">'argument2'</span><span class="p">:</span><span class="s">'bar'</span><span class="p">})</span></pre></div>
</div>
<p>The most customizable connection method of all is to pass a <tt class="docutils literal"><span class="pre">creator</span></tt> argument, which specifies a callable that returns a DBAPI connection:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="k">def</span> <span class="nf">connect</span><span class="p">():</span>
<span class="k">return</span> <span class="n">psycopg</span><span class="o">.</span><span class="n">connect</span><span class="p">(</span><span class="n">user</span><span class="o">=</span><span class="s">'scott'</span><span class="p">,</span> <span class="n">host</span><span class="o">=</span><span class="s">'localhost'</span><span class="p">)</span>
<span class="n">db</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="n">creator</span><span class="o">=</span><span class="n">connect</span><span class="p">)</span></pre></div>
</div>
</div>
</div>
<div class="section" id="database-engine-options">
<span id="create-engine-args"></span><h2>Database Engine Options<a class="headerlink" href="#database-engine-options" title="Permalink to this headline">¶</a></h2>
<p>Keyword options can also be specified to <a class="reference internal" href="reference/sqlalchemy/connections.html#sqlalchemy.create_engine" title="sqlalchemy.create_engine"><tt class="xref py py-func docutils literal"><span class="pre">create_engine()</span></tt></a>, following the string URL as follows:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="n">db</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="n">encoding</span><span class="o">=</span><span class="s">'latin1'</span><span class="p">,</span> <span class="n">echo</span><span class="o">=</span><span class="bp">True</span><span class="p">)</span></pre></div>
</div>
<p>Options common to all database dialects are described at <a class="reference internal" href="reference/sqlalchemy/connections.html#sqlalchemy.create_engine" title="sqlalchemy.create_engine"><tt class="xref py py-func docutils literal"><span class="pre">create_engine()</span></tt></a>.</p>
</div>
<div class="section" id="more-on-connections">
<h2>More On Connections<a class="headerlink" href="#more-on-connections" title="Permalink to this headline">¶</a></h2>
<p>Recall from the beginning of this section that the Engine provides a <tt class="docutils literal"><span class="pre">connect()</span></tt> method which returns 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> object. <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 a <em>proxy</em> object which maintains a reference to a DBAPI connection instance. The <tt class="docutils literal"><span class="pre">close()</span></tt> method on <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> does not actually close the DBAPI connection, but instead returns it to the connection pool referenced by the <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>. <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> will also automatically return its resources to the connection pool when the object is garbage collected, i.e. its <tt class="docutils literal"><span class="pre">__del__()</span></tt> method is called. When using the standard C implementation of Python, this method is usually called immediately as soon as the object is dereferenced. With other Python implementations such as Jython, this is not so guaranteed.</p>
<p>The <tt class="docutils literal"><span class="pre">execute()</span></tt> methods on both <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> can also receive SQL clause constructs as well:</p>
<div class="highlight-python"><div class="highlight"><pre><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="n">result</span> <span class="o">=</span> <span class="n">connection</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">table1</span><span class="p">],</span> <span class="n">table1</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">col1</span><span class="o">==</span><span class="mi">5</span><span class="p">))</span>
<span class="k">for</span> <span class="n">row</span> <span class="ow">in</span> <span class="n">result</span><span class="p">:</span>
<span class="k">print</span> <span class="n">row</span><span class="p">[</span><span class="s">'col1'</span><span class="p">],</span> <span class="n">row</span><span class="p">[</span><span class="s">'col2'</span><span class="p">]</span>
<span class="n">connection</span><span class="o">.</span><span class="n">close</span><span class="p">()</span></pre></div>
</div>
<p>The above SQL construct is known as a <tt class="docutils literal"><span class="pre">select()</span></tt>. The full range of SQL constructs available are described in <a class="reference internal" href="sqlexpression.html"><em>SQL Expression Language Tutorial</em></a>.</p>
<p>Both <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> and <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> fulfill an interface known as <a class="reference internal" href="reference/sqlalchemy/connections.html#sqlalchemy.engine.base.Connectable" title="sqlalchemy.engine.base.Connectable"><tt class="xref py py-class docutils literal"><span class="pre">Connectable</span></tt></a> which specifies common functionality between the two objects, namely being able to call <tt class="docutils literal"><span class="pre">connect()</span></tt> to return 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> object (<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> just returns itself), and being able to call <tt class="docutils literal"><span class="pre">execute()</span></tt> to get a result set. Following this, most SQLAlchemy functions and objects which accept 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> as a parameter or attribute with which to execute SQL will also accept 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>. This argument is named <tt class="docutils literal"><span class="pre">bind</span></tt>:</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">'sqlite:///:memory:'</span><span class="p">)</span>
<span class="c"># specify some Table metadata</span>
<span class="n">metadata</span> <span class="o">=</span> <span class="n">MetaData</span><span class="p">()</span>
<span class="n">table</span> <span class="o">=</span> <span class="n">Table</span><span class="p">(</span><span class="s">'sometable'</span><span class="p">,</span> <span class="n">metadata</span><span class="p">,</span> <span class="n">Column</span><span class="p">(</span><span class="s">'col1'</span><span class="p">,</span> <span class="n">Integer</span><span class="p">))</span>
<span class="c"># create the table with the Engine</span>
<span class="n">table</span><span class="o">.</span><span class="n">create</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"># drop the table with a Connection off the Engine</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="n">table</span><span class="o">.</span><span class="n">drop</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>
<p id="index-0">Connection facts:</p>
<ul class="simple">
<li>the Connection object is <strong>not thread-safe</strong>. While a Connection can be shared among threads using properly synchronized access, this is also not recommended as many DBAPIs have issues with, if not outright disallow, sharing of connection state between threads.</li>
<li>The Connection object represents a single dbapi connection checked out from the connection pool. In this state, the connection pool has no affect upon the connection, including its expiration or timeout state. For the connection pool to properly manage connections, <strong>connections should be returned to the connection pool (i.e. ``connection.close()``) whenever the connection is not in use</strong>. If your application has a need for management of multiple connections or is otherwise long running (this includes all web applications, threaded or not), don’t hold a single connection open at the module level.</li>
</ul>
</div>
<div class="section" id="using-transactions-with-connection">
<h2>Using Transactions with Connection<a class="headerlink" href="#using-transactions-with-connection" title="Permalink to this headline">¶</a></h2>
<p>The <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 provides a <tt class="docutils literal"><span class="pre">begin()</span></tt> method which returns 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> object. This object is usually used within a try/except clause so that it is guaranteed to <tt class="docutils literal"><span class="pre">rollback()</span></tt> or <tt class="docutils literal"><span class="pre">commit()</span></tt>:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">trans</span> <span class="o">=</span> <span class="n">connection</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">r1</span> <span class="o">=</span> <span class="n">connection</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="n">table1</span><span class="o">.</span><span class="n">select</span><span class="p">())</span>
<span class="n">connection</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="n">table1</span><span class="o">.</span><span class="n">insert</span><span class="p">(),</span> <span class="n">col1</span><span class="o">=</span><span class="mi">7</span><span class="p">,</span> <span class="n">col2</span><span class="o">=</span><span class="s">'this is some data'</span><span class="p">)</span>
<span class="n">trans</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">trans</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/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> object also handles “nested” behavior by keeping track of the outermost begin/commit pair. In this example, two functions both issue a transaction on a Connection, but only the outermost Transaction object actually takes effect when it is committed.</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="c"># method_a starts a transaction and calls method_b</span>
<span class="k">def</span> <span class="nf">method_a</span><span class="p">(</span><span class="n">connection</span><span class="p">):</span>
<span class="n">trans</span> <span class="o">=</span> <span class="n">connection</span><span class="o">.</span><span class="n">begin</span><span class="p">()</span> <span class="c"># open a transaction</span>
<span class="k">try</span><span class="p">:</span>
<span class="n">method_b</span><span class="p">(</span><span class="n">connection</span><span class="p">)</span>
<span class="n">trans</span><span class="o">.</span><span class="n">commit</span><span class="p">()</span> <span class="c"># transaction is committed here</span>
<span class="k">except</span><span class="p">:</span>
<span class="n">trans</span><span class="o">.</span><span class="n">rollback</span><span class="p">()</span> <span class="c"># this rolls back the transaction unconditionally</span>
<span class="k">raise</span>
<span class="c"># method_b also starts a transaction</span>
<span class="k">def</span> <span class="nf">method_b</span><span class="p">(</span><span class="n">connection</span><span class="p">):</span>
<span class="n">trans</span> <span class="o">=</span> <span class="n">connection</span><span class="o">.</span><span class="n">begin</span><span class="p">()</span> <span class="c"># open a transaction - this runs in the context of method_a's transaction</span>
<span class="k">try</span><span class="p">:</span>
<span class="n">connection</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="s">"insert into mytable values ('bat', 'lala')"</span><span class="p">)</span>
<span class="n">connection</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="n">mytable</span><span class="o">.</span><span class="n">insert</span><span class="p">(),</span> <span class="n">col1</span><span class="o">=</span><span class="s">'bat'</span><span class="p">,</span> <span class="n">col2</span><span class="o">=</span><span class="s">'lala'</span><span class="p">)</span>
<span class="n">trans</span><span class="o">.</span><span class="n">commit</span><span class="p">()</span> <span class="c"># transaction is not committed yet</span>
<span class="k">except</span><span class="p">:</span>
<span class="n">trans</span><span class="o">.</span><span class="n">rollback</span><span class="p">()</span> <span class="c"># this rolls back the transaction unconditionally</span>
<span class="k">raise</span>
<span class="c"># open a Connection and call method_a</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">method_a</span><span class="p">(</span><span class="n">conn</span><span class="p">)</span>
<span class="n">conn</span><span class="o">.</span><span class="n">close</span><span class="p">()</span></pre></div>
</div>
<p>Above, <tt class="docutils literal"><span class="pre">method_a</span></tt> is called first, which calls <tt class="docutils literal"><span class="pre">connection.begin()</span></tt>. Then it calls <tt class="docutils literal"><span class="pre">method_b</span></tt>. When <tt class="docutils literal"><span class="pre">method_b</span></tt> calls <tt class="docutils literal"><span class="pre">connection.begin()</span></tt>, it just increments a counter that is decremented when it calls <tt class="docutils literal"><span class="pre">commit()</span></tt>. If either <tt class="docutils literal"><span class="pre">method_a</span></tt> or <tt class="docutils literal"><span class="pre">method_b</span></tt> calls <tt class="docutils literal"><span class="pre">rollback()</span></tt>, the whole transaction is rolled back. The transaction is not committed until <tt class="docutils literal"><span class="pre">method_a</span></tt> calls the <tt class="docutils literal"><span class="pre">commit()</span></tt> method. This “nesting” behavior allows the creation of functions which “guarantee” that a transaction will be used if one was not already available, but will automatically participate in an enclosing transaction if one exists.</p>
<p>Note that SQLAlchemy’s Object Relational Mapper also provides a way to control transaction scope at a higher level; this is described in <a class="reference internal" href="session.html#unitofwork-transaction"><em>Managing Transactions</em></a>.</p>
<p id="index-1">Transaction Facts:</p>
<ul class="simple">
<li>the Transaction object, just like its parent Connection, is <strong>not thread-safe</strong>.</li>
</ul>
<div class="section" id="understanding-autocommit">
<h3>Understanding Autocommit<a class="headerlink" href="#understanding-autocommit" title="Permalink to this headline">¶</a></h3>
<p>The above transaction example illustrates how to use <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> so that several executions can take part in the same transaction. What happens when we issue an INSERT, UPDATE or DELETE call without using <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>? The answer is <strong>autocommit</strong>. While many DBAPIs implement a flag called <tt class="docutils literal"><span class="pre">autocommit</span></tt>, the current SQLAlchemy behavior is such that it implements its own autocommit. This is achieved by detecting statements which represent data-changing operations, i.e. INSERT, UPDATE, DELETE, etc., and then issuing a COMMIT automatically if no transaction is in progress. The detection is based on compiled statement attributes, or in the case of a text-only statement via regular expressions.</p>
<div class="highlight-python+sql"><div class="highlight"><pre><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">conn</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="s">"INSERT INTO users VALUES (1, 'john')"</span><span class="p">)</span> <span class="c"># autocommits</span></pre></div>
</div>
</div>
</div>
<div class="section" id="connectionless-execution-implicit-execution">
<span id="dbengine-implicit"></span><h2>Connectionless Execution, Implicit Execution<a class="headerlink" href="#connectionless-execution-implicit-execution" title="Permalink to this headline">¶</a></h2>
<p>Recall from the first section we mentioned executing with and without 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>. <tt class="docutils literal"><span class="pre">Connectionless</span></tt> execution refers to calling the <tt class="docutils literal"><span class="pre">execute()</span></tt> method on an object which is not 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>, which could be on the <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> itself, or could be a constructed SQL object. When we say “implicit”, we mean that we are calling the <tt class="docutils literal"><span class="pre">execute()</span></tt> method on an object which is neither 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> nor 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> object; this can only be used with constructed SQL objects which have their own <tt class="docutils literal"><span class="pre">execute()</span></tt> method, and can be “bound” to 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>. A description of “constructed SQL objects” may be found in <a class="reference internal" href="sqlexpression.html"><em>SQL Expression Language Tutorial</em></a>.</p>
<p>A summary of all three methods follows below. First, assume the usage of the following <a class="reference internal" href="reference/sqlalchemy/schema.html#sqlalchemy.schema.MetaData" title="sqlalchemy.schema.MetaData"><tt class="xref py py-class docutils literal"><span class="pre">MetaData</span></tt></a> and <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; while we haven’t yet introduced these concepts, for now you only need to know that we are representing a database table, and are creating an “executable” SQL construct which issues a statement to the database. These objects are described in <a class="reference internal" href="metadata.html"><em>Database Meta Data</em></a>.</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="n">meta</span> <span class="o">=</span> <span class="n">MetaData</span><span class="p">()</span>
<span class="n">users_table</span> <span class="o">=</span> <span class="n">Table</span><span class="p">(</span><span class="s">'users'</span><span class="p">,</span> <span class="n">meta</span><span class="p">,</span>
<span class="n">Column</span><span class="p">(</span><span class="s">'id'</span><span class="p">,</span> <span class="n">Integer</span><span class="p">,</span> <span class="n">primary_key</span><span class="o">=</span><span class="bp">True</span><span class="p">),</span>
<span class="n">Column</span><span class="p">(</span><span class="s">'name'</span><span class="p">,</span> <span class="n">String</span><span class="p">(</span><span class="mi">50</span><span class="p">))</span>
<span class="p">)</span></pre></div>
</div>
<p>Explicit execution delivers the SQL text or constructed SQL expression to the <tt class="docutils literal"><span class="pre">execute()</span></tt> method 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>:</p>
<div class="highlight-python+sql"><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">'sqlite:///file.db'</span><span class="p">)</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="n">result</span> <span class="o">=</span> <span class="n">connection</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="n">users_table</span><span class="o">.</span><span class="n">select</span><span class="p">())</span>
<span class="k">for</span> <span class="n">row</span> <span class="ow">in</span> <span class="n">result</span><span class="p">:</span>
<span class="c"># ....</span>
<span class="n">connection</span><span class="o">.</span><span class="n">close</span><span class="p">()</span></pre></div>
</div>
<p>Explicit, connectionless execution delivers the expression to the <tt class="docutils literal"><span class="pre">execute()</span></tt> method 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>:</p>
<div class="highlight-python+sql"><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">'sqlite:///file.db'</span><span class="p">)</span>
<span class="n">result</span> <span class="o">=</span> <span class="n">engine</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="n">users_table</span><span class="o">.</span><span class="n">select</span><span class="p">())</span>
<span class="k">for</span> <span class="n">row</span> <span class="ow">in</span> <span class="n">result</span><span class="p">:</span>
<span class="c"># ....</span>
<span class="n">result</span><span class="o">.</span><span class="n">close</span><span class="p">()</span></pre></div>
</div>
<p>Implicit execution is also connectionless, and calls the <tt class="docutils literal"><span class="pre">execute()</span></tt> method on the expression itself, utilizing the fact that either 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> has been <em>bound</em> to the expression object (binding is discussed further in the next section, <a class="reference internal" href="metadata.html"><em>Database Meta Data</em></a>):</p>
<div class="highlight-python+sql"><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">'sqlite:///file.db'</span><span class="p">)</span>
<span class="n">meta</span><span class="o">.</span><span class="n">bind</span> <span class="o">=</span> <span class="n">engine</span>
<span class="n">result</span> <span class="o">=</span> <span class="n">users_table</span><span class="o">.</span><span class="n">select</span><span class="p">()</span><span class="o">.</span><span class="n">execute</span><span class="p">()</span>
<span class="k">for</span> <span class="n">row</span> <span class="ow">in</span> <span class="n">result</span><span class="p">:</span>
<span class="c"># ....</span>
<span class="n">result</span><span class="o">.</span><span class="n">close</span><span class="p">()</span></pre></div>
</div>
<p>In both “connectionless” examples, the <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 created behind the scenes; the <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> returned by the <tt class="docutils literal"><span class="pre">execute()</span></tt> call references the <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> used to issue the SQL statement. When we issue <tt class="docutils literal"><span class="pre">close()</span></tt> on the <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>, or if the result set object falls out of scope and is garbage collected, the underlying <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 closed for us, resulting in the DBAPI connection being returned to the pool.</p>
<div class="section" id="using-the-threadlocal-execution-strategy">
<span id="threadlocal-strategy"></span><h3>Using the Threadlocal Execution Strategy<a class="headerlink" href="#using-the-threadlocal-execution-strategy" title="Permalink to this headline">¶</a></h3>
<p>The “threadlocal” engine strategy is used by non-ORM applications which wish to bind a transaction to the current thread, such that all parts of the application can participate in that transaction implicitly without the need to explicitly reference 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>. “threadlocal” is designed for a very specific pattern of use, and is not appropriate unless this very specfic pattern, described below, is what’s desired. It has <strong>no impact</strong> on the “thread safety” of SQLAlchemy components or one’s application. It also should not be used when using an ORM <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, 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> itself represents an ongoing transaction and itself handles the job of maintaining connection and transactional resources.</p>
<p>Enabling <tt class="docutils literal"><span class="pre">threadlocal</span></tt> is achieved as follows:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="n">db</span> <span class="o">=</span> <span class="n">create_engine</span><span class="p">(</span><span class="s">'mysql://localhost/test'</span><span class="p">,</span> <span class="n">strategy</span><span class="o">=</span><span class="s">'threadlocal'</span><span class="p">)</span></pre></div>
</div>
<p>When the engine above is used in a “connectionless” style, meaning <tt class="docutils literal"><span class="pre">engine.execute()</span></tt> is called, a DBAPI connection is retrieved from the connection pool and then associated with the current thread. Subsequent operations on the <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> while the DBAPI connection remains checked out will make use of the <em>same</em> DBAPI connection object. The connection stays allocated until all returned <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> objects are closed, which occurs for a particular <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> after all pending results are fetched, or immediately for an operation which returns no rows (such as an INSERT).</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="c"># execute one statement and receive results. r1 now references a DBAPI connection resource.</span>
<span class="n">r1</span> <span class="o">=</span> <span class="n">db</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="s">"select * from table1"</span><span class="p">)</span>
<span class="c"># execute a second statement and receive results. r2 now references the *same* resource as r1</span>
<span class="n">r2</span> <span class="o">=</span> <span class="n">db</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="s">"select * from table2"</span><span class="p">)</span>
<span class="c"># fetch a row on r1 (assume more results are pending)</span>
<span class="n">row1</span> <span class="o">=</span> <span class="n">r1</span><span class="o">.</span><span class="n">fetchone</span><span class="p">()</span>
<span class="c"># fetch a row on r2 (same)</span>
<span class="n">row2</span> <span class="o">=</span> <span class="n">r2</span><span class="o">.</span><span class="n">fetchone</span><span class="p">()</span>
<span class="c"># close r1. the connection is still held by r2.</span>
<span class="n">r1</span><span class="o">.</span><span class="n">close</span><span class="p">()</span>
<span class="c"># close r2. with no more references to the underlying connection resources, they</span>
<span class="c"># are returned to the pool.</span>
<span class="n">r2</span><span class="o">.</span><span class="n">close</span><span class="p">()</span></pre></div>
</div>
<p>The above example does not illustrate any pattern that is particularly useful, as it is not a frequent occurence that two execute/result fetching operations “leapfrog” one another. There is a slight savings of connection pool checkout overhead between the two operations, and an implicit sharing of the same transactional context, but since there is no explicitly declared transaction, this association is short lived.</p>
<p>The real usage of “threadlocal” comes when we want several operations to occur within the scope of a shared transaction. The <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> now has <tt class="docutils literal"><span class="pre">begin()</span></tt>, <tt class="docutils literal"><span class="pre">commit()</span></tt> and <tt class="docutils literal"><span class="pre">rollback()</span></tt> methods which will retrieve a connection resource from the pool and establish a new transaction, maintaining the connection against the current thread until the transaction is committed or rolled back:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="n">db</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">call_operation1</span><span class="p">()</span>
<span class="n">call_operation2</span><span class="p">()</span>
<span class="n">db</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">db</span><span class="o">.</span><span class="n">rollback</span><span class="p">()</span></pre></div>
</div>
<p><tt class="docutils literal"><span class="pre">call_operation1()</span></tt> and <tt class="docutils literal"><span class="pre">call_operation2()</span></tt> can make use of the <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> as a global variable, using the “connectionless” execution style, and their operations will participate in the same transaction:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="k">def</span> <span class="nf">call_operation1</span><span class="p">():</span>
<span class="n">engine</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="s">"insert into users values (?, ?)"</span><span class="p">,</span> <span class="mi">1</span><span class="p">,</span> <span class="s">"john"</span><span class="p">)</span>
<span class="k">def</span> <span class="nf">call_operation2</span><span class="p">():</span>
<span class="n">users</span><span class="o">.</span><span class="n">update</span><span class="p">(</span><span class="n">users</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">user_id</span><span class="o">==</span><span class="mi">5</span><span class="p">)</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="n">name</span><span class="o">=</span><span class="s">'ed'</span><span class="p">)</span></pre></div>
</div>
<p>When using threadlocal, operations that do call upon the <tt class="docutils literal"><span class="pre">engine.connect()</span></tt> method will receive 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> that is <strong>outside</strong> the scope of the transaction. This can be used for operations such as logging the status of an operation regardless of transaction success:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="n">db</span><span class="o">.</span><span class="n">begin</span><span class="p">()</span>
<span class="n">conn</span> <span class="o">=</span> <span class="n">db</span><span class="o">.</span><span class="n">connect</span><span class="p">()</span>
<span class="k">try</span><span class="p">:</span>
<span class="n">conn</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="n">log_table</span><span class="o">.</span><span class="n">insert</span><span class="p">(),</span> <span class="n">message</span><span class="o">=</span><span class="s">"Operation started"</span><span class="p">)</span>
<span class="n">call_operation1</span><span class="p">()</span>
<span class="n">call_operation2</span><span class="p">()</span>
<span class="n">db</span><span class="o">.</span><span class="n">commit</span><span class="p">()</span>
<span class="n">conn</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="n">log_table</span><span class="o">.</span><span class="n">insert</span><span class="p">(),</span> <span class="n">message</span><span class="o">=</span><span class="s">"Operation succeeded"</span><span class="p">)</span>
<span class="k">except</span><span class="p">:</span>
<span class="n">db</span><span class="o">.</span><span class="n">rollback</span><span class="p">()</span>
<span class="n">conn</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="n">log_table</span><span class="o">.</span><span class="n">insert</span><span class="p">(),</span> <span class="n">message</span><span class="o">=</span><span class="s">"Operation failed"</span><span class="p">)</span>
<span class="k">finally</span><span class="p">:</span>
<span class="n">conn</span><span class="o">.</span><span class="n">close</span><span class="p">()</span></pre></div>
</div>
<p>Functions which are written to use an explicit <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, but wish to participate in the threadlocal transaction, can receive their <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 from the <tt class="docutils literal"><span class="pre">contextual_connect()</span></tt> method, which returns 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> that is <strong>inside</strong> the scope of the transaction:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="n">conn</span> <span class="o">=</span> <span class="n">db</span><span class="o">.</span><span class="n">contextual_connect</span><span class="p">()</span>
<span class="n">call_operation3</span><span class="p">(</span><span class="n">conn</span><span class="p">)</span>
<span class="n">conn</span><span class="o">.</span><span class="n">close</span><span class="p">()</span></pre></div>
</div>
<p>Calling <tt class="docutils literal"><span class="pre">close()</span></tt> on the “contextual” connection does not release the connection resources to the pool if other resources are making use of it. A resource-counting mechanism is employed so that the connection is released back to the pool only when all users of that connection, including the transaction established by <tt class="docutils literal"><span class="pre">engine.begin()</span></tt>, have been completed.</p>
<p>So remember - if you’re not sure if you need to use <tt class="docutils literal"><span class="pre">strategy="threadlocal"</span></tt> or not, the answer is <strong>no</strong> ! It’s driven by a specific programming pattern that is generally not the norm.</p>
</div>
</div>
<div class="section" id="configuring-logging">
<span id="dbengine-logging"></span><h2>Configuring Logging<a class="headerlink" href="#configuring-logging" title="Permalink to this headline">¶</a></h2>
<p>Python’s standard <a class="reference external" href="http://www.python.org/doc/lib/module-logging.html">logging</a> module is used to implement informational and debug log output with SQLAlchemy. This allows SQLAlchemy’s logging to integrate in a standard way with other applications and libraries. The <tt class="docutils literal"><span class="pre">echo</span></tt> and <tt class="docutils literal"><span class="pre">echo_pool</span></tt> flags that are present on <a class="reference internal" href="reference/sqlalchemy/connections.html#sqlalchemy.create_engine" title="sqlalchemy.create_engine"><tt class="xref py py-func docutils literal"><span class="pre">create_engine()</span></tt></a>, as well as the <tt class="docutils literal"><span class="pre">echo_uow</span></tt> flag used 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>, all interact with regular loggers.</p>
<p>This section assumes familiarity with the above linked logging module. All logging performed by SQLAlchemy exists underneath the <tt class="docutils literal"><span class="pre">sqlalchemy</span></tt> namespace, as used by <tt class="docutils literal"><span class="pre">logging.getLogger('sqlalchemy')</span></tt>. When logging has been configured (i.e. such as via <tt class="docutils literal"><span class="pre">logging.basicConfig()</span></tt>), the general namespace of SA loggers that can be turned on is as follows:</p>
<ul>
<li><p class="first"><tt class="docutils literal"><span class="pre">sqlalchemy.engine</span></tt> - controls SQL echoing. set to <tt class="docutils literal"><span class="pre">logging.INFO</span></tt> for SQL query output, <tt class="docutils literal"><span class="pre">logging.DEBUG</span></tt> for query + result set output.</p>
</li>
<li><p class="first"><tt class="docutils literal"><span class="pre">sqlalchemy.dialects</span></tt> - controls custom logging for SQL dialects. See the documentation of individual dialects for details.</p>
</li>
<li><p class="first"><tt class="docutils literal"><span class="pre">sqlalchemy.pool</span></tt> - controls connection pool logging. set to <tt class="docutils literal"><span class="pre">logging.INFO</span></tt> or lower to log connection pool checkouts/checkins.</p>
</li>
<li><dl class="first docutils">
<dt><tt class="docutils literal"><span class="pre">sqlalchemy.orm</span></tt> - controls logging of various ORM functions. set to <tt class="docutils literal"><span class="pre">logging.INFO</span></tt> for configurational logging as well as unit of work dumps, <tt class="docutils literal"><span class="pre">logging.DEBUG</span></tt> for extensive logging during query and flush() operations. Subcategories of <tt class="docutils literal"><span class="pre">sqlalchemy.orm</span></tt> include:</dt>
<dd><ul class="first last simple">
<li><tt class="docutils literal"><span class="pre">sqlalchemy.orm.attributes</span></tt> - logs certain instrumented attribute operations, such as triggered callables</li>
<li><tt class="docutils literal"><span class="pre">sqlalchemy.orm.mapper</span></tt> - logs Mapper configuration and operations</li>
<li><tt class="docutils literal"><span class="pre">sqlalchemy.orm.unitofwork</span></tt> - logs flush() operations, including dependency sort graphs and other operations</li>
<li><tt class="docutils literal"><span class="pre">sqlalchemy.orm.strategies</span></tt> - logs relationship loader operations (i.e. lazy and eager loads)</li>
<li><tt class="docutils literal"><span class="pre">sqlalchemy.orm.sync</span></tt> - logs synchronization of attributes from parent to child instances during a flush()</li>
</ul>
</dd>
</dl>
</li>
</ul>
<p>For example, to log SQL queries as well as unit of work debugging:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="kn">import</span> <span class="nn">logging</span>
<span class="n">logging</span><span class="o">.</span><span class="n">basicConfig</span><span class="p">()</span>
<span class="n">logging</span><span class="o">.</span><span class="n">getLogger</span><span class="p">(</span><span class="s">'sqlalchemy.engine'</span><span class="p">)</span><span class="o">.</span><span class="n">setLevel</span><span class="p">(</span><span class="n">logging</span><span class="o">.</span><span class="n">INFO</span><span class="p">)</span>
<span class="n">logging</span><span class="o">.</span><span class="n">getLogger</span><span class="p">(</span><span class="s">'sqlalchemy.orm.unitofwork'</span><span class="p">)</span><span class="o">.</span><span class="n">setLevel</span><span class="p">(</span><span class="n">logging</span><span class="o">.</span><span class="n">DEBUG</span><span class="p">)</span></pre></div>
</div>
<p>By default, the log level is set to <tt class="docutils literal"><span class="pre">logging.ERROR</span></tt> within the entire <tt class="docutils literal"><span class="pre">sqlalchemy</span></tt> namespace so that no log operations occur, even within an application that has logging enabled otherwise.</p>
<p>The <tt class="docutils literal"><span class="pre">echo</span></tt> flags present as keyword arguments to <a class="reference internal" href="reference/sqlalchemy/connections.html#sqlalchemy.create_engine" title="sqlalchemy.create_engine"><tt class="xref py py-func docutils literal"><span class="pre">create_engine()</span></tt></a> and others as well as the <tt class="docutils literal"><span class="pre">echo</span></tt> property on <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>, when set to <tt class="xref docutils literal"><span class="pre">True</span></tt>, will first attempt to ensure that logging is enabled. Unfortunately, the <tt class="docutils literal"><span class="pre">logging</span></tt> module provides no way of determining if output has already been configured (note we are referring to if a logging configuration has been set up, not just that the logging level is set). For this reason, any <tt class="docutils literal"><span class="pre">echo=True</span></tt> flags will result in a call to <tt class="docutils literal"><span class="pre">logging.basicConfig()</span></tt> using sys.stdout as the destination. It also sets up a default format using the level name, timestamp, and logger name. Note that this configuration has the affect of being configured <strong>in addition</strong> to any existing logger configurations. Therefore, <strong>when using Python logging, ensure all echo flags are set to False at all times</strong>, to avoid getting duplicate log lines.</p>
<p>The logger name of instance such 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/pooling.html#sqlalchemy.pool.Pool" title="sqlalchemy.pool.Pool"><tt class="xref py py-class docutils literal"><span class="pre">Pool</span></tt></a> defaults to using a truncated hex identifier string. To set this to a specific name, use the “logging_name” and “pool_logging_name” keyword arguments with <a class="reference internal" href="reference/sqlalchemy/connections.html#sqlalchemy.create_engine" title="sqlalchemy.create_engine"><tt class="xref py py-func docutils literal"><span class="pre">sqlalchemy.create_engine()</span></tt></a>.</p>
</div>
</div>
</div>
</div>
<div class="bottomnav">
<div class="prevnext">
Previous:
<a href="session.html" title="previous chapter">Using the Session</a>
Next:
<a href="metadata.html" title="next chapter">Database Meta Data</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>
|
