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
|
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>
SQLite
—
SQLAlchemy 0.9 Documentation
</title>
<!-- begin iterate through SQLA + sphinx environment css_files -->
<link rel="stylesheet" href="../_static/pygments.css" type="text/css" />
<link rel="stylesheet" href="../_static/docs.css" type="text/css" />
<link rel="stylesheet" href="../_static/sphinx_paramlinks.css" type="text/css" />
<link rel="stylesheet" href="../_static/changelog.css" type="text/css" />
<!-- end iterate through SQLA + sphinx environment css_files -->
<!-- begin layout.mako headers -->
<script type="text/javascript">
var DOCUMENTATION_OPTIONS = {
URL_ROOT: '../',
VERSION: '0.9.8',
COLLAPSE_MODINDEX: false,
FILE_SUFFIX: '.html'
};
</script>
<!-- begin iterate through sphinx environment script_files -->
<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>
<!-- end iterate through sphinx environment script_files -->
<script type="text/javascript" src="../_static/detectmobile.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="copyright" title="Copyright" href="../copyright.html" />
<link rel="top" title="SQLAlchemy 0.9 Documentation" href="../index.html" />
<link rel="up" title="Dialects" href="index.html" />
<link rel="next" title="Sybase" href="sybase.html" />
<link rel="prev" title="PostgreSQL" href="postgresql.html" />
<!-- end layout.mako headers -->
</head>
<body>
<div id="docs-container">
<div id="docs-top-navigation-container" class="body-background">
<div id="docs-header">
<div id="docs-version-header">
Release: <span class="version-num">0.9.8</span> | Release Date: October 13, 2014
</div>
<h1>SQLAlchemy 0.9 Documentation</h1>
</div>
</div>
<div id="docs-body-container">
<div id="fixed-sidebar" class="withsidebar">
<div id="docs-sidebar-popout">
<h3><a href="../index.html">SQLAlchemy 0.9 Documentation</a></h3>
<p id="sidebar-paginate">
<a href="index.html" title="Dialects">Up</a> |
<a href="postgresql.html" title="PostgreSQL">Prev</a> |
<a href="sybase.html" title="Sybase">Next</a>
</p>
<p id="sidebar-topnav">
<a href="../index.html">Contents</a> |
<a href="../genindex.html">Index</a>
</p>
<div id="sidebar-search">
<form class="search" action="../search.html" method="get">
<input type="text" name="q" size="12" /> <input type="submit" value="Search" />
<input type="hidden" name="check_keywords" value="yes" />
<input type="hidden" name="area" value="default" />
</form>
</div>
</div>
<div id="docs-sidebar">
<h3><a href="#">
SQLite
</a></h3>
<ul>
<li><a class="reference internal" href="#">SQLite</a><ul>
<li><a class="reference internal" href="#dialect-sqlite">Support for the SQLite database.</a></li>
<li><a class="reference internal" href="#date-and-time-types">Date and Time Types</a></li>
<li><a class="reference internal" href="#sqlite-auto-incrementing-behavior">SQLite Auto Incrementing Behavior</a></li>
<li><a class="reference internal" href="#database-locking-behavior-concurrency">Database Locking Behavior / Concurrency</a><ul>
<li><a class="reference internal" href="#transaction-isolation-level">Transaction Isolation Level</a></li>
<li><a class="reference internal" href="#savepoint-support">SAVEPOINT Support</a></li>
<li><a class="reference internal" href="#transactional-ddl">Transactional DDL</a></li>
</ul>
</li>
<li><a class="reference internal" href="#foreign-key-support">Foreign Key Support</a></li>
<li><a class="reference internal" href="#type-reflection">Type Reflection</a></li>
<li><a class="reference internal" href="#sqlite-data-types">SQLite Data Types</a></li>
<li><a class="reference internal" href="#module-sqlalchemy.dialects.sqlite.pysqlite">Pysqlite</a><ul>
<li><a class="reference internal" href="#dialect-sqlite-pysqlite-url">DBAPI</a></li>
<li><a class="reference internal" href="#dialect-sqlite-pysqlite-connect">Connecting</a></li>
<li><a class="reference internal" href="#driver">Driver</a></li>
<li><a class="reference internal" href="#connect-strings">Connect Strings</a></li>
<li><a class="reference internal" href="#compatibility-with-sqlite3-native-date-and-datetime-types">Compatibility with sqlite3 “native” date and datetime types</a></li>
<li><a class="reference internal" href="#threading-pooling-behavior">Threading/Pooling Behavior</a><ul>
<li><a class="reference internal" href="#using-a-memory-database-in-multiple-threads">Using a Memory Database in Multiple Threads</a></li>
<li><a class="reference internal" href="#using-temporary-tables-with-sqlite">Using Temporary Tables with SQLite</a></li>
</ul>
</li>
<li><a class="reference internal" href="#unicode">Unicode</a></li>
<li><a class="reference internal" href="#serializable-isolation-savepoints-transactional-ddl">Serializable isolation / Savepoints / Transactional DDL</a></li>
</ul>
</li>
</ul>
</li>
</ul>
</div>
</div>
<div id="docs-body" class="withsidebar" >
<div class="section" id="module-sqlalchemy.dialects.sqlite.base">
<span id="sqlite"></span><span id="sqlite-toplevel"></span><h1>SQLite<a class="headerlink" href="#module-sqlalchemy.dialects.sqlite.base" title="Permalink to this headline">¶</a></h1>
<div class="section" id="dialect-sqlite">
<p>Support for the SQLite database.</p>
<h2>DBAPI Support<a class="headerlink" href="#dialect-sqlite" title="Permalink to this headline">¶</a></h2>
<p>The following dialect/DBAPI options are available. Please refer to individual DBAPI sections for connect information.<ul class="simple">
<li><a class="reference external" href="#module-sqlalchemy.dialects.sqlite.pysqlite">pysqlite</a></li>
</ul>
</p>
</div>
<div class="section" id="date-and-time-types">
<h2>Date and Time Types<a class="headerlink" href="#date-and-time-types" title="Permalink to this headline">¶</a></h2>
<p>SQLite does not have built-in DATE, TIME, or DATETIME types, and pysqlite does
not provide out of the box functionality for translating values between Python
<cite>datetime</cite> objects and a SQLite-supported format. SQLAlchemy’s own
<a class="reference internal" href="../core/types.html#sqlalchemy.types.DateTime" title="sqlalchemy.types.DateTime"><tt class="xref py py-class docutils literal"><span class="pre">DateTime</span></tt></a> and related types provide date formatting
and parsing functionality when SQlite is used. The implementation classes are
<a class="reference internal" href="#sqlalchemy.dialects.sqlite.DATETIME" title="sqlalchemy.dialects.sqlite.DATETIME"><tt class="xref py py-class docutils literal"><span class="pre">DATETIME</span></tt></a>, <a class="reference internal" href="#sqlalchemy.dialects.sqlite.DATE" title="sqlalchemy.dialects.sqlite.DATE"><tt class="xref py py-class docutils literal"><span class="pre">DATE</span></tt></a> and <a class="reference internal" href="#sqlalchemy.dialects.sqlite.TIME" title="sqlalchemy.dialects.sqlite.TIME"><tt class="xref py py-class docutils literal"><span class="pre">TIME</span></tt></a>.
These types represent dates and times as ISO formatted strings, which also
nicely support ordering. There’s no reliance on typical “libc” internals for
these functions so historical dates are fully supported.</p>
</div>
<div class="section" id="sqlite-auto-incrementing-behavior">
<span id="sqlite-autoincrement"></span><h2>SQLite Auto Incrementing Behavior<a class="headerlink" href="#sqlite-auto-incrementing-behavior" title="Permalink to this headline">¶</a></h2>
<p>Background on SQLite’s autoincrement is at: <a class="reference external" href="http://sqlite.org/autoinc.html">http://sqlite.org/autoinc.html</a></p>
<p>Two things to note:</p>
<ul class="simple">
<li>The AUTOINCREMENT keyword is <strong>not</strong> required for SQLite tables to
generate primary key values automatically. AUTOINCREMENT only means that the
algorithm used to generate ROWID values should be slightly different.</li>
<li>SQLite does <strong>not</strong> generate primary key (i.e. ROWID) values, even for
one column, if the table has a composite (i.e. multi-column) primary key.
This is regardless of the AUTOINCREMENT keyword being present or not.</li>
</ul>
<p>To specifically render the AUTOINCREMENT keyword on the primary key column
when rendering DDL, add the flag <tt class="docutils literal"><span class="pre">sqlite_autoincrement=True</span></tt> to the Table
construct:</p>
<div class="highlight-python"><div class="highlight"><pre><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">'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">sqlite_autoincrement</span><span class="o">=</span><span class="bp">True</span><span class="p">)</span></pre></div>
</div>
</div>
<div class="section" id="database-locking-behavior-concurrency">
<span id="sqlite-concurrency"></span><h2>Database Locking Behavior / Concurrency<a class="headerlink" href="#database-locking-behavior-concurrency" title="Permalink to this headline">¶</a></h2>
<p>SQLite is not designed for a high level of write concurrency. The database
itself, being a file, is locked completely during write operations within
transactions, meaning exactly one “connection” (in reality a file handle)
has exclusive access to the database during this period - all other
“connections” will be blocked during this time.</p>
<p>The Python DBAPI specification also calls for a connection model that is
always in a transaction; there is no <tt class="docutils literal"><span class="pre">connection.begin()</span></tt> method,
only <tt class="docutils literal"><span class="pre">connection.commit()</span></tt> and <tt class="docutils literal"><span class="pre">connection.rollback()</span></tt>, upon which a
new transaction is to be begun immediately. This may seem to imply
that the SQLite driver would in theory allow only a single filehandle on a
particular database file at any time; however, there are several
factors both within SQlite itself as well as within the pysqlite driver
which loosen this restriction significantly.</p>
<p>However, no matter what locking modes are used, SQLite will still always
lock the database file once a transaction is started and DML (e.g. INSERT,
UPDATE, DELETE) has at least been emitted, and this will block
other transactions at least at the point that they also attempt to emit DML.
By default, the length of time on this block is very short before it times out
with an error.</p>
<p>This behavior becomes more critical when used in conjunction with the
SQLAlchemy ORM. SQLAlchemy’s <a class="reference internal" href="../orm/session.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 by default runs
within a transaction, and with its autoflush model, may emit DML preceding
any SELECT statement. This may lead to a SQLite database that locks
more quickly than is expected. The locking mode of SQLite and the pysqlite
driver can be manipulated to some degree, however it should be noted that
achieving a high degree of write-concurrency with SQLite is a losing battle.</p>
<p>For more information on SQLite’s lack of write concurrency by design, please
see
<a class="reference external" href="http://www.sqlite.org/whentouse.html">Situations Where Another RDBMS May Work Better - High Concurrency</a> near the bottom of the page.</p>
<p>The following subsections introduce areas that are impacted by SQLite’s
file-based architecture and additionally will usually require workarounds to
work when using the pysqlite driver.</p>
<div class="section" id="transaction-isolation-level">
<h3>Transaction Isolation Level<a class="headerlink" href="#transaction-isolation-level" title="Permalink to this headline">¶</a></h3>
<p>SQLite supports “transaction isolation” in a non-standard way, along two
axes. One is that of the <a class="reference external" href="http://www.sqlite.org/pragma.html#pragma_read_uncommitted">PRAGMA read_uncommitted</a>
instruction. This setting can essentially switch SQLite between its
default mode of <tt class="docutils literal"><span class="pre">SERIALIZABLE</span></tt> isolation, and a “dirty read” isolation
mode normally referred to as <tt class="docutils literal"><span class="pre">READ</span> <span class="pre">UNCOMMITTED</span></tt>.</p>
<p>SQLAlchemy ties into this PRAGMA statement using the
<a class="reference internal" href="../core/engines.html#sqlalchemy.create_engine.params.isolation_level" title="sqlalchemy.create_engine"><tt class="xref py py-paramref docutils literal"><span class="pre">create_engine.isolation_level</span></tt></a> parameter of <a class="reference internal" href="../core/engines.html#sqlalchemy.create_engine" title="sqlalchemy.create_engine"><tt class="xref py py-func docutils literal"><span class="pre">create_engine()</span></tt></a>.
Valid values for this parameter when used with SQLite are <tt class="docutils literal"><span class="pre">"SERIALIZABLE"</span></tt>
and <tt class="docutils literal"><span class="pre">"READ</span> <span class="pre">UNCOMMITTED"</span></tt> corresponding to a value of 0 and 1, respectively.
SQLite defaults to <tt class="docutils literal"><span class="pre">SERIALIZABLE</span></tt>, however its behavior is impacted by
the pysqlite driver’s default behavior.</p>
<p>The other axis along which SQLite’s transactional locking is impacted is
via the nature of the <tt class="docutils literal"><span class="pre">BEGIN</span></tt> statement used. The three varieties
are “deferred”, “immediate”, and “exclusive”, as described at
<a class="reference external" href="http://sqlite.org/lang_transaction.html">BEGIN TRANSACTION</a>. A straight
<tt class="docutils literal"><span class="pre">BEGIN</span></tt> statement uses the “deferred” mode, where the the database file is
not locked until the first read or write operation, and read access remains
open to other transactions until the first write operation. But again,
it is critical to note that the pysqlite driver interferes with this behavior
by <em>not even emitting BEGIN</em> until the first write operation.</p>
<div class="admonition warning">
<p class="first admonition-title">Warning</p>
<p class="last">SQLite’s transactional scope is impacted by unresolved
issues in the pysqlite driver, which defers BEGIN statements to a greater
degree than is often feasible. See the section <a class="reference internal" href="#pysqlite-serializable"><em>Serializable isolation / Savepoints / Transactional DDL</em></a>
for techniques to work around this behavior.</p>
</div>
</div>
<div class="section" id="savepoint-support">
<h3>SAVEPOINT Support<a class="headerlink" href="#savepoint-support" title="Permalink to this headline">¶</a></h3>
<p>SQLite supports SAVEPOINTs, which only function once a transaction is
begun. SQLAlchemy’s SAVEPOINT support is available using the
<a class="reference internal" href="../core/connections.html#sqlalchemy.engine.Connection.begin_nested" title="sqlalchemy.engine.Connection.begin_nested"><tt class="xref py py-meth docutils literal"><span class="pre">Connection.begin_nested()</span></tt></a> method at the Core level, and
<a class="reference internal" href="../orm/session.html#sqlalchemy.orm.session.Session.begin_nested" title="sqlalchemy.orm.session.Session.begin_nested"><tt class="xref py py-meth docutils literal"><span class="pre">Session.begin_nested()</span></tt></a> at the ORM level. However, SAVEPOINTs
won’t work at all with pysqlite unless workarounds are taken.</p>
<div class="admonition warning">
<p class="first admonition-title">Warning</p>
<p class="last">SQLite’s SAVEPOINT feature is impacted by unresolved
issues in the pysqlite driver, which defers BEGIN statements to a greater
degree than is often feasible. See the section <a class="reference internal" href="#pysqlite-serializable"><em>Serializable isolation / Savepoints / Transactional DDL</em></a>
for techniques to work around this behavior.</p>
</div>
</div>
<div class="section" id="transactional-ddl">
<h3>Transactional DDL<a class="headerlink" href="#transactional-ddl" title="Permalink to this headline">¶</a></h3>
<p>The SQLite database supports transactional <a class="reference internal" href="../glossary.html#term-ddl"><em class="xref std std-term">DDL</em></a> as well.
In this case, the pysqlite driver is not only failing to start transactions,
it also is ending any existing transction when DDL is detected, so again,
workarounds are required.</p>
<div class="admonition warning">
<p class="first admonition-title">Warning</p>
<p class="last">SQLite’s transactional DDL is impacted by unresolved issues
in the pysqlite driver, which fails to emit BEGIN and additionally
forces a COMMIT to cancel any transaction when DDL is encountered.
See the section <a class="reference internal" href="#pysqlite-serializable"><em>Serializable isolation / Savepoints / Transactional DDL</em></a>
for techniques to work around this behavior.</p>
</div>
</div>
</div>
<div class="section" id="foreign-key-support">
<span id="sqlite-foreign-keys"></span><h2>Foreign Key Support<a class="headerlink" href="#foreign-key-support" title="Permalink to this headline">¶</a></h2>
<p>SQLite supports FOREIGN KEY syntax when emitting CREATE statements for tables,
however by default these constraints have no effect on the operation of the
table.</p>
<p>Constraint checking on SQLite has three prerequisites:</p>
<ul class="simple">
<li>At least version 3.6.19 of SQLite must be in use</li>
<li>The SQLite library must be compiled <em>without</em> the SQLITE_OMIT_FOREIGN_KEY
or SQLITE_OMIT_TRIGGER symbols enabled.</li>
<li>The <tt class="docutils literal"><span class="pre">PRAGMA</span> <span class="pre">foreign_keys</span> <span class="pre">=</span> <span class="pre">ON</span></tt> statement must be emitted on all
connections before use.</li>
</ul>
<p>SQLAlchemy allows for the <tt class="docutils literal"><span class="pre">PRAGMA</span></tt> statement to be emitted automatically for
new connections through the usage of events:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="kn">from</span> <span class="nn">sqlalchemy.engine</span> <span class="kn">import</span> <span class="n">Engine</span>
<span class="kn">from</span> <span class="nn">sqlalchemy</span> <span class="kn">import</span> <span class="n">event</span>
<span class="nd">@event.listens_for</span><span class="p">(</span><span class="n">Engine</span><span class="p">,</span> <span class="s">"connect"</span><span class="p">)</span>
<span class="k">def</span> <span class="nf">set_sqlite_pragma</span><span class="p">(</span><span class="n">dbapi_connection</span><span class="p">,</span> <span class="n">connection_record</span><span class="p">):</span>
<span class="n">cursor</span> <span class="o">=</span> <span class="n">dbapi_connection</span><span class="o">.</span><span class="n">cursor</span><span class="p">()</span>
<span class="n">cursor</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="s">"PRAGMA foreign_keys=ON"</span><span class="p">)</span>
<span class="n">cursor</span><span class="o">.</span><span class="n">close</span><span class="p">()</span></pre></div>
</div>
<div class="admonition seealso">
<p class="first admonition-title">See also</p>
<p><a class="reference external" href="http://www.sqlite.org/foreignkeys.html">SQLite Foreign Key Support</a>
- on the SQLite web site.</p>
<p class="last"><a class="reference internal" href="../core/event.html"><em>Events</em></a> - SQLAlchemy event API.</p>
</div>
</div>
<div class="section" id="type-reflection">
<span id="sqlite-type-reflection"></span><h2>Type Reflection<a class="headerlink" href="#type-reflection" title="Permalink to this headline">¶</a></h2>
<p>SQLite types are unlike those of most other database backends, in that
the string name of the type usually does not correspond to a “type” in a
one-to-one fashion. Instead, SQLite links per-column typing behavior
to one of five so-called “type affinities” based on a string matching
pattern for the type.</p>
<p>SQLAlchemy’s reflection process, when inspecting types, uses a simple
lookup table to link the keywords returned to provided SQLAlchemy types.
This lookup table is present within the SQLite dialect as it is for all
other dialects. However, the SQLite dialect has a different “fallback”
routine for when a particular type name is not located in the lookup map;
it instead implements the SQLite “type affinity” scheme located at
<a class="reference external" href="http://www.sqlite.org/datatype3.html">http://www.sqlite.org/datatype3.html</a> section 2.1.</p>
<p>The provided typemap will make direct associations from an exact string
name match for the following types:</p>
<p><a class="reference internal" href="../core/types.html#sqlalchemy.types.BIGINT" title="sqlalchemy.types.BIGINT"><tt class="xref py py-class docutils literal"><span class="pre">BIGINT</span></tt></a>, <a class="reference internal" href="../core/types.html#sqlalchemy.types.BLOB" title="sqlalchemy.types.BLOB"><tt class="xref py py-class docutils literal"><span class="pre">BLOB</span></tt></a>,
<a class="reference internal" href="../core/types.html#sqlalchemy.types.BOOLEAN" title="sqlalchemy.types.BOOLEAN"><tt class="xref py py-class docutils literal"><span class="pre">BOOLEAN</span></tt></a>, <a class="reference internal" href="../core/types.html#sqlalchemy.types.BOOLEAN" title="sqlalchemy.types.BOOLEAN"><tt class="xref py py-class docutils literal"><span class="pre">BOOLEAN</span></tt></a>,
<a class="reference internal" href="../core/types.html#sqlalchemy.types.CHAR" title="sqlalchemy.types.CHAR"><tt class="xref py py-class docutils literal"><span class="pre">CHAR</span></tt></a>, <a class="reference internal" href="../core/types.html#sqlalchemy.types.DATE" title="sqlalchemy.types.DATE"><tt class="xref py py-class docutils literal"><span class="pre">DATE</span></tt></a>,
<a class="reference internal" href="../core/types.html#sqlalchemy.types.DATETIME" title="sqlalchemy.types.DATETIME"><tt class="xref py py-class docutils literal"><span class="pre">DATETIME</span></tt></a>, <a class="reference internal" href="../core/types.html#sqlalchemy.types.FLOAT" title="sqlalchemy.types.FLOAT"><tt class="xref py py-class docutils literal"><span class="pre">FLOAT</span></tt></a>,
<a class="reference internal" href="../core/types.html#sqlalchemy.types.DECIMAL" title="sqlalchemy.types.DECIMAL"><tt class="xref py py-class docutils literal"><span class="pre">DECIMAL</span></tt></a>, <a class="reference internal" href="../core/types.html#sqlalchemy.types.FLOAT" title="sqlalchemy.types.FLOAT"><tt class="xref py py-class docutils literal"><span class="pre">FLOAT</span></tt></a>,
<a class="reference internal" href="../core/types.html#sqlalchemy.types.INTEGER" title="sqlalchemy.types.INTEGER"><tt class="xref py py-class docutils literal"><span class="pre">INTEGER</span></tt></a>, <a class="reference internal" href="../core/types.html#sqlalchemy.types.INTEGER" title="sqlalchemy.types.INTEGER"><tt class="xref py py-class docutils literal"><span class="pre">INTEGER</span></tt></a>,
<a class="reference internal" href="../core/types.html#sqlalchemy.types.NUMERIC" title="sqlalchemy.types.NUMERIC"><tt class="xref py py-class docutils literal"><span class="pre">NUMERIC</span></tt></a>, <a class="reference internal" href="../core/types.html#sqlalchemy.types.REAL" title="sqlalchemy.types.REAL"><tt class="xref py py-class docutils literal"><span class="pre">REAL</span></tt></a>,
<a class="reference internal" href="../core/types.html#sqlalchemy.types.SMALLINT" title="sqlalchemy.types.SMALLINT"><tt class="xref py py-class docutils literal"><span class="pre">SMALLINT</span></tt></a>, <a class="reference internal" href="../core/types.html#sqlalchemy.types.TEXT" title="sqlalchemy.types.TEXT"><tt class="xref py py-class docutils literal"><span class="pre">TEXT</span></tt></a>,
<a class="reference internal" href="../core/types.html#sqlalchemy.types.TIME" title="sqlalchemy.types.TIME"><tt class="xref py py-class docutils literal"><span class="pre">TIME</span></tt></a>, <a class="reference internal" href="../core/types.html#sqlalchemy.types.TIMESTAMP" title="sqlalchemy.types.TIMESTAMP"><tt class="xref py py-class docutils literal"><span class="pre">TIMESTAMP</span></tt></a>,
<a class="reference internal" href="../core/types.html#sqlalchemy.types.VARCHAR" title="sqlalchemy.types.VARCHAR"><tt class="xref py py-class docutils literal"><span class="pre">VARCHAR</span></tt></a>, <a class="reference internal" href="../core/types.html#sqlalchemy.types.NVARCHAR" title="sqlalchemy.types.NVARCHAR"><tt class="xref py py-class docutils literal"><span class="pre">NVARCHAR</span></tt></a>,
<a class="reference internal" href="../core/types.html#sqlalchemy.types.NCHAR" title="sqlalchemy.types.NCHAR"><tt class="xref py py-class docutils literal"><span class="pre">NCHAR</span></tt></a></p>
<p>When a type name does not match one of the above types, the “type affinity”
lookup is used instead:</p>
<ul class="simple">
<li><a class="reference internal" href="../core/types.html#sqlalchemy.types.INTEGER" title="sqlalchemy.types.INTEGER"><tt class="xref py py-class docutils literal"><span class="pre">INTEGER</span></tt></a> is returned if the type name includes the
string <tt class="docutils literal"><span class="pre">INT</span></tt></li>
<li><a class="reference internal" href="../core/types.html#sqlalchemy.types.TEXT" title="sqlalchemy.types.TEXT"><tt class="xref py py-class docutils literal"><span class="pre">TEXT</span></tt></a> is returned if the type name includes the
string <tt class="docutils literal"><span class="pre">CHAR</span></tt>, <tt class="docutils literal"><span class="pre">CLOB</span></tt> or <tt class="docutils literal"><span class="pre">TEXT</span></tt></li>
<li><a class="reference internal" href="../core/types.html#sqlalchemy.types.NullType" title="sqlalchemy.types.NullType"><tt class="xref py py-class docutils literal"><span class="pre">NullType</span></tt></a> is returned if the type name includes the
string <tt class="docutils literal"><span class="pre">BLOB</span></tt></li>
<li><a class="reference internal" href="../core/types.html#sqlalchemy.types.REAL" title="sqlalchemy.types.REAL"><tt class="xref py py-class docutils literal"><span class="pre">REAL</span></tt></a> is returned if the type name includes the string
<tt class="docutils literal"><span class="pre">REAL</span></tt>, <tt class="docutils literal"><span class="pre">FLOA</span></tt> or <tt class="docutils literal"><span class="pre">DOUB</span></tt>.</li>
<li>Otherwise, the <a class="reference internal" href="../core/types.html#sqlalchemy.types.NUMERIC" title="sqlalchemy.types.NUMERIC"><tt class="xref py py-class docutils literal"><span class="pre">NUMERIC</span></tt></a> type is used.</li>
</ul>
<div class="versionadded">
<p><span>New in version 0.9.3: </span>Support for SQLite type affinity rules when reflecting
columns.</p>
</div>
</div>
<div class="section" id="sqlite-data-types">
<h2>SQLite Data Types<a class="headerlink" href="#sqlite-data-types" title="Permalink to this headline">¶</a></h2>
<p>As with all SQLAlchemy dialects, all UPPERCASE types that are known to be
valid with SQLite are importable from the top level dialect, whether
they originate from <a class="reference internal" href="../core/types.html#module-sqlalchemy.types" title="sqlalchemy.types"><tt class="xref py py-mod docutils literal"><span class="pre">sqlalchemy.types</span></tt></a> or from the local dialect:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="kn">from</span> <span class="nn">sqlalchemy.dialects.sqlite</span> <span class="kn">import</span> \
<span class="n">BLOB</span><span class="p">,</span> <span class="n">BOOLEAN</span><span class="p">,</span> <span class="n">CHAR</span><span class="p">,</span> <span class="n">DATE</span><span class="p">,</span> <span class="n">DATETIME</span><span class="p">,</span> <span class="n">DECIMAL</span><span class="p">,</span> <span class="n">FLOAT</span><span class="p">,</span> \
<span class="n">INTEGER</span><span class="p">,</span> <span class="n">NUMERIC</span><span class="p">,</span> <span class="n">SMALLINT</span><span class="p">,</span> <span class="n">TEXT</span><span class="p">,</span> <span class="n">TIME</span><span class="p">,</span> <span class="n">TIMESTAMP</span><span class="p">,</span> \
<span class="n">VARCHAR</span></pre></div>
</div>
<span class="target" id="module-sqlalchemy.dialects.sqlite"></span><dl class="class">
<dt id="sqlalchemy.dialects.sqlite.DATETIME">
<em class="property">class </em><tt class="descclassname">sqlalchemy.dialects.sqlite.</tt><tt class="descname">DATETIME</tt><big>(</big><em>*args</em>, <em>**kwargs</em><big>)</big><a class="headerlink" href="#sqlalchemy.dialects.sqlite.DATETIME" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <tt class="xref py py-class docutils literal"><span class="pre">sqlalchemy.dialects.sqlite.base._DateTimeMixin</span></tt>, <a class="reference internal" href="../core/types.html#sqlalchemy.types.DateTime" title="sqlalchemy.types.DateTime"><tt class="xref py py-class docutils literal"><span class="pre">sqlalchemy.types.DateTime</span></tt></a></p>
<p>Represent a Python datetime object in SQLite using a string.</p>
<p>The default string storage format is:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="s">"</span><span class="si">%(year)04d</span><span class="s">-</span><span class="si">%(month)02d</span><span class="s">-</span><span class="si">%(day)02d</span><span class="s"> </span><span class="si">%(hour)02d</span><span class="s">:</span><span class="si">%(min)02d</span><span class="s">:</span><span class="si">%(second)02d</span><span class="s">.</span><span class="si">%(microsecond)06d</span><span class="s">"</span></pre></div>
</div>
<p>e.g.:</p>
<div class="highlight-python"><pre>2011-03-15 12:05:57.10558</pre>
</div>
<p>The storage format can be customized to some degree using the
<tt class="docutils literal"><span class="pre">storage_format</span></tt> and <tt class="docutils literal"><span class="pre">regexp</span></tt> parameters, such as:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="kn">import</span> <span class="nn">re</span>
<span class="kn">from</span> <span class="nn">sqlalchemy.dialects.sqlite</span> <span class="kn">import</span> <span class="n">DATETIME</span>
<span class="n">dt</span> <span class="o">=</span> <span class="n">DATETIME</span><span class="p">(</span>
<span class="n">storage_format</span><span class="o">=</span><span class="s">"</span><span class="si">%(year)04d</span><span class="s">/</span><span class="si">%(month)02d</span><span class="s">/</span><span class="si">%(day)02d</span><span class="s"> </span><span class="si">%(hour)02d</span><span class="s">:</span><span class="si">%(min)02d</span><span class="s">:</span><span class="si">%(second)02d</span><span class="s">"</span><span class="p">,</span>
<span class="n">regexp</span><span class="o">=</span><span class="s">r"(\d+)/(\d+)/(\d+) (\d+)-(\d+)-(\d+)"</span>
<span class="p">)</span></pre></div>
</div>
<table class="docutils field-list" frame="void" rules="none">
<col class="field-name" />
<col class="field-body" />
<tbody valign="top">
<tr class="field-odd field"><th class="field-name">Parameters:</th><td class="field-body"><ul class="first last simple">
<li><span class="target" id="sqlalchemy.dialects.sqlite.DATETIME.params.storage_format"></span><strong>storage_format</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.sqlite.DATETIME.params.storage_format">¶</a> – format string which will be applied to the dict
with keys year, month, day, hour, minute, second, and microsecond.</li>
<li><span class="target" id="sqlalchemy.dialects.sqlite.DATETIME.params.regexp"></span><strong>regexp</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.sqlite.DATETIME.params.regexp">¶</a> – regular expression which will be applied to incoming result
rows. If the regexp contains named groups, the resulting match dict is
applied to the Python datetime() constructor as keyword arguments.
Otherwise, if positional groups are used, the datetime() constructor
is called with positional arguments via
<tt class="docutils literal"><span class="pre">*map(int,</span> <span class="pre">match_obj.groups(0))</span></tt>.</li>
</ul>
</td>
</tr>
</tbody>
</table>
</dd></dl>
<dl class="class">
<dt id="sqlalchemy.dialects.sqlite.DATE">
<em class="property">class </em><tt class="descclassname">sqlalchemy.dialects.sqlite.</tt><tt class="descname">DATE</tt><big>(</big><em>storage_format=None</em>, <em>regexp=None</em>, <em>**kw</em><big>)</big><a class="headerlink" href="#sqlalchemy.dialects.sqlite.DATE" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <tt class="xref py py-class docutils literal"><span class="pre">sqlalchemy.dialects.sqlite.base._DateTimeMixin</span></tt>, <a class="reference internal" href="../core/types.html#sqlalchemy.types.Date" title="sqlalchemy.types.Date"><tt class="xref py py-class docutils literal"><span class="pre">sqlalchemy.types.Date</span></tt></a></p>
<p>Represent a Python date object in SQLite using a string.</p>
<p>The default string storage format is:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="s">"</span><span class="si">%(year)04d</span><span class="s">-</span><span class="si">%(month)02d</span><span class="s">-</span><span class="si">%(day)02d</span><span class="s">"</span></pre></div>
</div>
<p>e.g.:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="mi">2011</span><span class="o">-</span><span class="mo">03</span><span class="o">-</span><span class="mi">15</span></pre></div>
</div>
<p>The storage format can be customized to some degree using the
<tt class="docutils literal"><span class="pre">storage_format</span></tt> and <tt class="docutils literal"><span class="pre">regexp</span></tt> parameters, such as:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="kn">import</span> <span class="nn">re</span>
<span class="kn">from</span> <span class="nn">sqlalchemy.dialects.sqlite</span> <span class="kn">import</span> <span class="n">DATE</span>
<span class="n">d</span> <span class="o">=</span> <span class="n">DATE</span><span class="p">(</span>
<span class="n">storage_format</span><span class="o">=</span><span class="s">"</span><span class="si">%(month)02d</span><span class="s">/</span><span class="si">%(day)02d</span><span class="s">/</span><span class="si">%(year)04d</span><span class="s">"</span><span class="p">,</span>
<span class="n">regexp</span><span class="o">=</span><span class="n">re</span><span class="o">.</span><span class="n">compile</span><span class="p">(</span><span class="s">"(?P<month>\d+)/(?P<day>\d+)/(?P<year>\d+)"</span><span class="p">)</span>
<span class="p">)</span></pre></div>
</div>
<table class="docutils field-list" frame="void" rules="none">
<col class="field-name" />
<col class="field-body" />
<tbody valign="top">
<tr class="field-odd field"><th class="field-name">Parameters:</th><td class="field-body"><ul class="first last simple">
<li><span class="target" id="sqlalchemy.dialects.sqlite.DATE.params.storage_format"></span><strong>storage_format</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.sqlite.DATE.params.storage_format">¶</a> – format string which will be applied to the
dict with keys year, month, and day.</li>
<li><span class="target" id="sqlalchemy.dialects.sqlite.DATE.params.regexp"></span><strong>regexp</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.sqlite.DATE.params.regexp">¶</a> – regular expression which will be applied to
incoming result rows. If the regexp contains named groups, the
resulting match dict is applied to the Python date() constructor
as keyword arguments. Otherwise, if positional groups are used, the
date() constructor is called with positional arguments via
<tt class="docutils literal"><span class="pre">*map(int,</span> <span class="pre">match_obj.groups(0))</span></tt>.</li>
</ul>
</td>
</tr>
</tbody>
</table>
</dd></dl>
<dl class="class">
<dt id="sqlalchemy.dialects.sqlite.TIME">
<em class="property">class </em><tt class="descclassname">sqlalchemy.dialects.sqlite.</tt><tt class="descname">TIME</tt><big>(</big><em>*args</em>, <em>**kwargs</em><big>)</big><a class="headerlink" href="#sqlalchemy.dialects.sqlite.TIME" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <tt class="xref py py-class docutils literal"><span class="pre">sqlalchemy.dialects.sqlite.base._DateTimeMixin</span></tt>, <a class="reference internal" href="../core/types.html#sqlalchemy.types.Time" title="sqlalchemy.types.Time"><tt class="xref py py-class docutils literal"><span class="pre">sqlalchemy.types.Time</span></tt></a></p>
<p>Represent a Python time object in SQLite using a string.</p>
<p>The default string storage format is:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="s">"</span><span class="si">%(hour)02d</span><span class="s">:</span><span class="si">%(minute)02d</span><span class="s">:</span><span class="si">%(second)02d</span><span class="s">.</span><span class="si">%(microsecond)06d</span><span class="s">"</span></pre></div>
</div>
<p>e.g.:</p>
<div class="highlight-python"><pre>12:05:57.10558</pre>
</div>
<p>The storage format can be customized to some degree using the
<tt class="docutils literal"><span class="pre">storage_format</span></tt> and <tt class="docutils literal"><span class="pre">regexp</span></tt> parameters, such as:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="kn">import</span> <span class="nn">re</span>
<span class="kn">from</span> <span class="nn">sqlalchemy.dialects.sqlite</span> <span class="kn">import</span> <span class="n">TIME</span>
<span class="n">t</span> <span class="o">=</span> <span class="n">TIME</span><span class="p">(</span>
<span class="n">storage_format</span><span class="o">=</span><span class="s">"</span><span class="si">%(hour)02d</span><span class="s">-</span><span class="si">%(minute)02d</span><span class="s">-</span><span class="si">%(second)02d</span><span class="s">-</span><span class="si">%(microsecond)06d</span><span class="s">"</span><span class="p">,</span>
<span class="n">regexp</span><span class="o">=</span><span class="n">re</span><span class="o">.</span><span class="n">compile</span><span class="p">(</span><span class="s">"(\d+)-(\d+)-(\d+)-(?:-(\d+))?"</span><span class="p">)</span>
<span class="p">)</span></pre></div>
</div>
<table class="docutils field-list" frame="void" rules="none">
<col class="field-name" />
<col class="field-body" />
<tbody valign="top">
<tr class="field-odd field"><th class="field-name">Parameters:</th><td class="field-body"><ul class="first last simple">
<li><span class="target" id="sqlalchemy.dialects.sqlite.TIME.params.storage_format"></span><strong>storage_format</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.sqlite.TIME.params.storage_format">¶</a> – format string which will be applied to the dict
with keys hour, minute, second, and microsecond.</li>
<li><span class="target" id="sqlalchemy.dialects.sqlite.TIME.params.regexp"></span><strong>regexp</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.sqlite.TIME.params.regexp">¶</a> – regular expression which will be applied to incoming result
rows. If the regexp contains named groups, the resulting match dict is
applied to the Python time() constructor as keyword arguments. Otherwise,
if positional groups are used, the time() constructor is called with
positional arguments via <tt class="docutils literal"><span class="pre">*map(int,</span> <span class="pre">match_obj.groups(0))</span></tt>.</li>
</ul>
</td>
</tr>
</tbody>
</table>
</dd></dl>
</div>
<div class="section" id="module-sqlalchemy.dialects.sqlite.pysqlite">
<span id="pysqlite"></span><h2>Pysqlite<a class="headerlink" href="#module-sqlalchemy.dialects.sqlite.pysqlite" title="Permalink to this headline">¶</a></h2>
<p>Support for the SQLite database via the pysqlite driver.<p>Note that <tt class="docutils literal"><span class="pre">pysqlite</span></tt> is the same driver as the <tt class="docutils literal"><span class="pre">sqlite3</span></tt>
module included with the Python distribution.</p>
</p>
<div class="section" id="dialect-sqlite-pysqlite-url">
<h3>DBAPI<a class="headerlink" href="#dialect-sqlite-pysqlite-url" title="Permalink to this headline">¶</a></h3>
<p>Documentation and download information (if applicable) for pysqlite is available at:
<a class="reference external" href="http://docs.python.org/library/sqlite3.html">http://docs.python.org/library/sqlite3.html</a></p>
</div>
<div class="section" id="dialect-sqlite-pysqlite-connect">
<h3>Connecting<a class="headerlink" href="#dialect-sqlite-pysqlite-connect" title="Permalink to this headline">¶</a></h3>
<p>Connect String:<div class="highlight-python"><pre>sqlite+pysqlite:///file_path</pre>
</div>
</p>
</div>
<div class="section" id="driver">
<h3>Driver<a class="headerlink" href="#driver" title="Permalink to this headline">¶</a></h3>
<p>When using Python 2.5 and above, the built in <tt class="docutils literal"><span class="pre">sqlite3</span></tt> driver is
already installed and no additional installation is needed. Otherwise,
the <tt class="docutils literal"><span class="pre">pysqlite2</span></tt> driver needs to be present. This is the same driver as
<tt class="docutils literal"><span class="pre">sqlite3</span></tt>, just with a different name.</p>
<p>The <tt class="docutils literal"><span class="pre">pysqlite2</span></tt> driver will be loaded first, and if not found, <tt class="docutils literal"><span class="pre">sqlite3</span></tt>
is loaded. This allows an explicitly installed pysqlite driver to take
precedence over the built in one. As with all dialects, a specific
DBAPI module may be provided to <a class="reference internal" href="../core/engines.html#sqlalchemy.create_engine" title="sqlalchemy.create_engine"><tt class="xref py py-func docutils literal"><span class="pre">create_engine()</span></tt></a> to control
this explicitly:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="kn">from</span> <span class="nn">sqlite3</span> <span class="kn">import</span> <span class="n">dbapi2</span> <span class="k">as</span> <span class="n">sqlite</span>
<span class="n">e</span> <span class="o">=</span> <span class="n">create_engine</span><span class="p">(</span><span class="s">'sqlite+pysqlite:///file.db'</span><span class="p">,</span> <span class="n">module</span><span class="o">=</span><span class="n">sqlite</span><span class="p">)</span></pre></div>
</div>
</div>
<div class="section" id="connect-strings">
<h3>Connect Strings<a class="headerlink" href="#connect-strings" title="Permalink to this headline">¶</a></h3>
<p>The file specification for the SQLite database is taken as the “database”
portion of the URL. Note that the format of a SQLAlchemy url is:</p>
<div class="highlight-python"><pre>driver://user:pass@host/database</pre>
</div>
<p>This means that the actual filename to be used starts with the characters to
the <strong>right</strong> of the third slash. So connecting to a relative filepath
looks like:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="c"># relative path</span>
<span class="n">e</span> <span class="o">=</span> <span class="n">create_engine</span><span class="p">(</span><span class="s">'sqlite:///path/to/database.db'</span><span class="p">)</span></pre></div>
</div>
<p>An absolute path, which is denoted by starting with a slash, means you
need <strong>four</strong> slashes:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="c"># absolute path</span>
<span class="n">e</span> <span class="o">=</span> <span class="n">create_engine</span><span class="p">(</span><span class="s">'sqlite:////path/to/database.db'</span><span class="p">)</span></pre></div>
</div>
<p>To use a Windows path, regular drive specifications and backslashes can be
used. Double backslashes are probably needed:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="c"># absolute path on Windows</span>
<span class="n">e</span> <span class="o">=</span> <span class="n">create_engine</span><span class="p">(</span><span class="s">'sqlite:///C:</span><span class="se">\\</span><span class="s">path</span><span class="se">\\</span><span class="s">to</span><span class="se">\\</span><span class="s">database.db'</span><span class="p">)</span></pre></div>
</div>
<p>The sqlite <tt class="docutils literal"><span class="pre">:memory:</span></tt> identifier is the default if no filepath is
present. Specify <tt class="docutils literal"><span class="pre">sqlite://</span></tt> and nothing else:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="c"># in-memory database</span>
<span class="n">e</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>
</div>
<div class="section" id="compatibility-with-sqlite3-native-date-and-datetime-types">
<h3>Compatibility with sqlite3 “native” date and datetime types<a class="headerlink" href="#compatibility-with-sqlite3-native-date-and-datetime-types" title="Permalink to this headline">¶</a></h3>
<p>The pysqlite driver includes the sqlite3.PARSE_DECLTYPES and
sqlite3.PARSE_COLNAMES options, which have the effect of any column
or expression explicitly cast as “date” or “timestamp” will be converted
to a Python date or datetime object. The date and datetime types provided
with the pysqlite dialect are not currently compatible with these options,
since they render the ISO date/datetime including microseconds, which
pysqlite’s driver does not. Additionally, SQLAlchemy does not at
this time automatically render the “cast” syntax required for the
freestanding functions “current_timestamp” and “current_date” to return
datetime/date types natively. Unfortunately, pysqlite
does not provide the standard DBAPI types in <tt class="docutils literal"><span class="pre">cursor.description</span></tt>,
leaving SQLAlchemy with no way to detect these types on the fly
without expensive per-row type checks.</p>
<p>Keeping in mind that pysqlite’s parsing option is not recommended,
nor should be necessary, for use with SQLAlchemy, usage of PARSE_DECLTYPES
can be forced if one configures “native_datetime=True” on create_engine():</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://'</span><span class="p">,</span>
<span class="n">connect_args</span><span class="o">=</span><span class="p">{</span><span class="s">'detect_types'</span><span class="p">:</span>
<span class="n">sqlite3</span><span class="o">.</span><span class="n">PARSE_DECLTYPES</span><span class="o">|</span><span class="n">sqlite3</span><span class="o">.</span><span class="n">PARSE_COLNAMES</span><span class="p">},</span>
<span class="n">native_datetime</span><span class="o">=</span><span class="bp">True</span>
<span class="p">)</span></pre></div>
</div>
<p>With this flag enabled, the DATE and TIMESTAMP types (but note - not the
DATETIME or TIME types...confused yet ?) will not perform any bind parameter
or result processing. Execution of “func.current_date()” will return a string.
“func.current_timestamp()” is registered as returning a DATETIME type in
SQLAlchemy, so this function still receives SQLAlchemy-level result
processing.</p>
</div>
<div class="section" id="threading-pooling-behavior">
<span id="pysqlite-threading-pooling"></span><h3>Threading/Pooling Behavior<a class="headerlink" href="#threading-pooling-behavior" title="Permalink to this headline">¶</a></h3>
<p>Pysqlite’s default behavior is to prohibit the usage of a single connection
in more than one thread. This is originally intended to work with older
versions of SQLite that did not support multithreaded operation under
various circumstances. In particular, older SQLite versions
did not allow a <tt class="docutils literal"><span class="pre">:memory:</span></tt> database to be used in multiple threads
under any circumstances.</p>
<p>Pysqlite does include a now-undocumented flag known as
<tt class="docutils literal"><span class="pre">check_same_thread</span></tt> which will disable this check, however note that
pysqlite connections are still not safe to use in concurrently in multiple
threads. In particular, any statement execution calls would need to be
externally mutexed, as Pysqlite does not provide for thread-safe propagation
of error messages among other things. So while even <tt class="docutils literal"><span class="pre">:memory:</span></tt> databases
can be shared among threads in modern SQLite, Pysqlite doesn’t provide enough
thread-safety to make this usage worth it.</p>
<p>SQLAlchemy sets up pooling to work with Pysqlite’s default behavior:</p>
<ul>
<li><p class="first">When a <tt class="docutils literal"><span class="pre">:memory:</span></tt> SQLite database is specified, the dialect by default
will use <a class="reference internal" href="../core/pooling.html#sqlalchemy.pool.SingletonThreadPool" title="sqlalchemy.pool.SingletonThreadPool"><tt class="xref py py-class docutils literal"><span class="pre">SingletonThreadPool</span></tt></a>. This pool maintains a single
connection per thread, so that all access to the engine within the current
thread use the same <tt class="docutils literal"><span class="pre">:memory:</span></tt> database - other threads would access a
different <tt class="docutils literal"><span class="pre">:memory:</span></tt> database.</p>
</li>
<li><p class="first">When a file-based database is specified, the dialect will use
<a class="reference internal" href="../core/pooling.html#sqlalchemy.pool.NullPool" title="sqlalchemy.pool.NullPool"><tt class="xref py py-class docutils literal"><span class="pre">NullPool</span></tt></a> as the source of connections. This pool closes and
discards connections which are returned to the pool immediately. SQLite
file-based connections have extremely low overhead, so pooling is not
necessary. The scheme also prevents a connection from being used again in
a different thread and works best with SQLite’s coarse-grained file locking.</p>
<div class="versionchanged">
<p><span>Changed in version 0.7: </span>Default selection of <a class="reference internal" href="../core/pooling.html#sqlalchemy.pool.NullPool" title="sqlalchemy.pool.NullPool"><tt class="xref py py-class docutils literal"><span class="pre">NullPool</span></tt></a> for SQLite file-based databases.
Previous versions select <a class="reference internal" href="../core/pooling.html#sqlalchemy.pool.SingletonThreadPool" title="sqlalchemy.pool.SingletonThreadPool"><tt class="xref py py-class docutils literal"><span class="pre">SingletonThreadPool</span></tt></a> by
default for all SQLite databases.</p>
</div>
</li>
</ul>
<div class="section" id="using-a-memory-database-in-multiple-threads">
<h4>Using a Memory Database in Multiple Threads<a class="headerlink" href="#using-a-memory-database-in-multiple-threads" title="Permalink to this headline">¶</a></h4>
<p>To use a <tt class="docutils literal"><span class="pre">:memory:</span></tt> database in a multithreaded scenario, the same
connection object must be shared among threads, since the database exists
only within the scope of that connection. The
<a class="reference internal" href="../core/pooling.html#sqlalchemy.pool.StaticPool" title="sqlalchemy.pool.StaticPool"><tt class="xref py py-class docutils literal"><span class="pre">StaticPool</span></tt></a> implementation will maintain a single connection
globally, and the <tt class="docutils literal"><span class="pre">check_same_thread</span></tt> flag can be passed to Pysqlite
as <tt class="docutils literal"><span class="pre">False</span></tt>:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="kn">from</span> <span class="nn">sqlalchemy.pool</span> <span class="kn">import</span> <span class="n">StaticPool</span>
<span class="n">engine</span> <span class="o">=</span> <span class="n">create_engine</span><span class="p">(</span><span class="s">'sqlite://'</span><span class="p">,</span>
<span class="n">connect_args</span><span class="o">=</span><span class="p">{</span><span class="s">'check_same_thread'</span><span class="p">:</span><span class="bp">False</span><span class="p">},</span>
<span class="n">poolclass</span><span class="o">=</span><span class="n">StaticPool</span><span class="p">)</span></pre></div>
</div>
<p>Note that using a <tt class="docutils literal"><span class="pre">:memory:</span></tt> database in multiple threads requires a recent
version of SQLite.</p>
</div>
<div class="section" id="using-temporary-tables-with-sqlite">
<h4>Using Temporary Tables with SQLite<a class="headerlink" href="#using-temporary-tables-with-sqlite" title="Permalink to this headline">¶</a></h4>
<p>Due to the way SQLite deals with temporary tables, if you wish to use a
temporary table in a file-based SQLite database across multiple checkouts
from the connection pool, such as when using an ORM <a class="reference internal" href="../orm/session.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> where
the temporary table should continue to remain after <a class="reference internal" href="../orm/session.html#sqlalchemy.orm.session.Session.commit" title="sqlalchemy.orm.session.Session.commit"><tt class="xref py py-meth docutils literal"><span class="pre">Session.commit()</span></tt></a> or
<a class="reference internal" href="../orm/session.html#sqlalchemy.orm.session.Session.rollback" title="sqlalchemy.orm.session.Session.rollback"><tt class="xref py py-meth docutils literal"><span class="pre">Session.rollback()</span></tt></a> is called, a pool which maintains a single
connection must be used. Use <a class="reference internal" href="../core/pooling.html#sqlalchemy.pool.SingletonThreadPool" title="sqlalchemy.pool.SingletonThreadPool"><tt class="xref py py-class docutils literal"><span class="pre">SingletonThreadPool</span></tt></a> if the scope is
only needed within the current thread, or <a class="reference internal" href="../core/pooling.html#sqlalchemy.pool.StaticPool" title="sqlalchemy.pool.StaticPool"><tt class="xref py py-class docutils literal"><span class="pre">StaticPool</span></tt></a> is scope is
needed within multiple threads for this case:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="c"># maintain the same connection per thread</span>
<span class="kn">from</span> <span class="nn">sqlalchemy.pool</span> <span class="kn">import</span> <span class="n">SingletonThreadPool</span>
<span class="n">engine</span> <span class="o">=</span> <span class="n">create_engine</span><span class="p">(</span><span class="s">'sqlite:///mydb.db'</span><span class="p">,</span>
<span class="n">poolclass</span><span class="o">=</span><span class="n">SingletonThreadPool</span><span class="p">)</span>
<span class="c"># maintain the same connection across all threads</span>
<span class="kn">from</span> <span class="nn">sqlalchemy.pool</span> <span class="kn">import</span> <span class="n">StaticPool</span>
<span class="n">engine</span> <span class="o">=</span> <span class="n">create_engine</span><span class="p">(</span><span class="s">'sqlite:///mydb.db'</span><span class="p">,</span>
<span class="n">poolclass</span><span class="o">=</span><span class="n">StaticPool</span><span class="p">)</span></pre></div>
</div>
<p>Note that <a class="reference internal" href="../core/pooling.html#sqlalchemy.pool.SingletonThreadPool" title="sqlalchemy.pool.SingletonThreadPool"><tt class="xref py py-class docutils literal"><span class="pre">SingletonThreadPool</span></tt></a> should be configured for the number
of threads that are to be used; beyond that number, connections will be
closed out in a non deterministic way.</p>
</div>
</div>
<div class="section" id="unicode">
<h3>Unicode<a class="headerlink" href="#unicode" title="Permalink to this headline">¶</a></h3>
<p>The pysqlite driver only returns Python <tt class="docutils literal"><span class="pre">unicode</span></tt> objects in result sets,
never plain strings, and accommodates <tt class="docutils literal"><span class="pre">unicode</span></tt> objects within bound
parameter values in all cases. Regardless of the SQLAlchemy string type in
use, string-based result values will by Python <tt class="docutils literal"><span class="pre">unicode</span></tt> in Python 2.
The <a class="reference internal" href="../core/types.html#sqlalchemy.types.Unicode" title="sqlalchemy.types.Unicode"><tt class="xref py py-class docutils literal"><span class="pre">Unicode</span></tt></a> type should still be used to indicate those columns that
require unicode, however, so that non-<tt class="docutils literal"><span class="pre">unicode</span></tt> values passed inadvertently
will emit a warning. Pysqlite will emit an error if a non-<tt class="docutils literal"><span class="pre">unicode</span></tt> string
is passed containing non-ASCII characters.</p>
</div>
<div class="section" id="serializable-isolation-savepoints-transactional-ddl">
<span id="pysqlite-serializable"></span><h3>Serializable isolation / Savepoints / Transactional DDL<a class="headerlink" href="#serializable-isolation-savepoints-transactional-ddl" title="Permalink to this headline">¶</a></h3>
<p>In the section <a class="reference internal" href="#sqlite-concurrency"><em>Database Locking Behavior / Concurrency</em></a>, we refer to the pysqlite
driver’s assortment of issues that prevent several features of SQLite
from working correctly. The pysqlite DBAPI driver has several
long-standing bugs which impact the correctness of its transactional
behavior. In its default mode of operation, SQLite features such as
SERIALIZABLE isolation, transactional DDL, and SAVEPOINT support are
non-functional, and in order to use these features, workarounds must
be taken.</p>
<p>The issue is essentially that the driver attempts to second-guess the user’s
intent, failing to start transactions and sometimes ending them prematurely, in
an effort to minimize the SQLite databases’s file locking behavior, even
though SQLite itself uses “shared” locks for read-only activities.</p>
<p>SQLAlchemy chooses to not alter this behavior by default, as it is the
long-expected behavior of the pysqlite driver; if and when the pysqlite
driver attempts to repair these issues, that will be more of a driver towards
defaults for SQLAlchemy.</p>
<p>The good news is that with a few events, we can implement transactional
support fully, by disabling pysqlite’s feature entirely and emitting BEGIN
ourselves. This is achieved using two event listeners:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="kn">from</span> <span class="nn">sqlalchemy</span> <span class="kn">import</span> <span class="n">create_engine</span><span class="p">,</span> <span class="n">event</span>
<span class="n">engine</span> <span class="o">=</span> <span class="n">create_engine</span><span class="p">(</span><span class="s">"sqlite:///myfile.db"</span><span class="p">)</span>
<span class="nd">@event.listens_for</span><span class="p">(</span><span class="n">engine</span><span class="p">,</span> <span class="s">"connect"</span><span class="p">)</span>
<span class="k">def</span> <span class="nf">do_connect</span><span class="p">(</span><span class="n">dbapi_connection</span><span class="p">,</span> <span class="n">connection_record</span><span class="p">):</span>
<span class="c"># disable pysqlite's emitting of the BEGIN statement entirely.</span>
<span class="c"># also stops it from emitting COMMIT before any DDL.</span>
<span class="n">dbapi_connection</span><span class="o">.</span><span class="n">isolation_level</span> <span class="o">=</span> <span class="bp">None</span>
<span class="nd">@event.listens_for</span><span class="p">(</span><span class="n">engine</span><span class="p">,</span> <span class="s">"begin"</span><span class="p">)</span>
<span class="k">def</span> <span class="nf">do_begin</span><span class="p">(</span><span class="n">conn</span><span class="p">):</span>
<span class="c"># emit our own BEGIN</span>
<span class="n">conn</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="s">"BEGIN"</span><span class="p">)</span></pre></div>
</div>
<p>Above, we intercept a new pysqlite connection and disable any transactional
integration. Then, at the point at which SQLAlchemy knows that transaction
scope is to begin, we emit <tt class="docutils literal"><span class="pre">"BEGIN"</span></tt> ourselves.</p>
<p>When we take control of <tt class="docutils literal"><span class="pre">"BEGIN"</span></tt>, we can also control directly SQLite’s
locking modes, introduced at <a class="reference external" href="http://sqlite.org/lang_transaction.html">BEGIN TRANSACTION</a>,
by adding the desired locking mode to our <tt class="docutils literal"><span class="pre">"BEGIN"</span></tt>:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="nd">@event.listens_for</span><span class="p">(</span><span class="n">engine</span><span class="p">,</span> <span class="s">"begin"</span><span class="p">)</span>
<span class="k">def</span> <span class="nf">do_begin</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">execute</span><span class="p">(</span><span class="s">"BEGIN EXCLUSIVE"</span><span class="p">)</span></pre></div>
</div>
<div class="admonition seealso">
<p class="first admonition-title">See also</p>
<p><a class="reference external" href="http://sqlite.org/lang_transaction.html">BEGIN TRANSACTION</a> - on the SQLite site</p>
<p><a class="reference external" href="http://bugs.python.org/issue9924">sqlite3 SELECT does not BEGIN a transaction</a> - on the Python bug tracker</p>
<p class="last"><a class="reference external" href="http://bugs.python.org/issue10740">sqlite3 module breaks transactions and potentially corrupts data</a> - on the Python bug tracker</p>
</div>
</div>
</div>
</div>
</div>
</div>
<div id="docs-bottom-navigation" class="docs-navigation-links">
Previous:
<a href="postgresql.html" title="previous chapter">PostgreSQL</a>
Next:
<a href="sybase.html" title="next chapter">Sybase</a>
<div id="docs-copyright">
© <a href="../copyright.html">Copyright</a> 2007-2014, the SQLAlchemy authors and contributors.
Created using <a href="http://sphinx.pocoo.org/">Sphinx</a> 1.2b1.
</div>
</div>
</div>
</body>
</html>
|
