Shortcuts on this page
r m x toggle line displays
j k next/prev highlighted chunk
0 (zero) top of page
1 (one) first highlighted chunk
1# sqlite/pysqlite.py
2# Copyright (C) 2005-2021 the SQLAlchemy authors and contributors
3# <see AUTHORS file>
4#
5# This module is part of SQLAlchemy and is released under
6# the MIT License: http://www.opensource.org/licenses/mit-license.php
8r"""
9.. dialect:: sqlite+pysqlite
10 :name: pysqlite
11 :dbapi: sqlite3
12 :connectstring: sqlite+pysqlite:///file_path
13 :url: http://docs.python.org/library/sqlite3.html
15 Note that ``pysqlite`` is the same driver as the ``sqlite3``
16 module included with the Python distribution.
18Driver
19------
21The ``sqlite3`` Python DBAPI is standard on all modern Python versions;
22for cPython and Pypy, no additional installation is necessary.
25Connect Strings
26---------------
28The file specification for the SQLite database is taken as the "database"
29portion of the URL. Note that the format of a SQLAlchemy url is::
31 driver://user:pass@host/database
33This means that the actual filename to be used starts with the characters to
34the **right** of the third slash. So connecting to a relative filepath
35looks like::
37 # relative path
38 e = create_engine('sqlite:///path/to/database.db')
40An absolute path, which is denoted by starting with a slash, means you
41need **four** slashes::
43 # absolute path
44 e = create_engine('sqlite:////path/to/database.db')
46To use a Windows path, regular drive specifications and backslashes can be
47used. Double backslashes are probably needed::
49 # absolute path on Windows
50 e = create_engine('sqlite:///C:\\path\\to\\database.db')
52The sqlite ``:memory:`` identifier is the default if no filepath is
53present. Specify ``sqlite://`` and nothing else::
55 # in-memory database
56 e = create_engine('sqlite://')
58.. _pysqlite_uri_connections:
60URI Connections
61^^^^^^^^^^^^^^^
63Modern versions of SQLite support an alternative system of connecting using a
64`driver level URI <https://www.sqlite.org/uri.html>`_, which has the advantage
65that additional driver-level arguments can be passed including options such as
66"read only". The Python sqlite3 driver supports this mode under modern Python
673 versions. The SQLAlchemy pysqlite driver supports this mode of use by
68specifing "uri=true" in the URL query string. The SQLite-level "URI" is kept
69as the "database" portion of the SQLAlchemy url (that is, following a slash)::
71 e = create_engine("sqlite:///file:path/to/database?mode=ro&uri=true")
73.. note:: The "uri=true" parameter must appear in the **query string**
74 of the URL. It will not currently work as expected if it is only
75 present in the :paramref:`_sa.create_engine.connect_args`
76 parameter dictionary.
78The logic reconciles the simultaneous presence of SQLAlchemy's query string and
79SQLite's query string by separating out the parameters that belong to the
80Python sqlite3 driver vs. those that belong to the SQLite URI. This is
81achieved through the use of a fixed list of parameters known to be accepted by
82the Python side of the driver. For example, to include a URL that indicates
83the Python sqlite3 "timeout" and "check_same_thread" parameters, along with the
84SQLite "mode" and "nolock" parameters, they can all be passed together on the
85query string::
87 e = create_engine(
88 "sqlite:///file:path/to/database?"
89 "check_same_thread=true&timeout=10&mode=ro&nolock=1&uri=true"
90 )
92Above, the pysqlite / sqlite3 DBAPI would be passed arguments as::
94 sqlite3.connect(
95 "file:path/to/database?mode=ro&nolock=1",
96 check_same_thread=True, timeout=10, uri=True
97 )
99Regarding future parameters added to either the Python or native drivers. new
100parameter names added to the SQLite URI scheme should be automatically
101accommodated by this scheme. New parameter names added to the Python driver
102side can be accommodated by specifying them in the
103:paramref:`_sa.create_engine.connect_args` dictionary,
104until dialect support is
105added by SQLAlchemy. For the less likely case that the native SQLite driver
106adds a new parameter name that overlaps with one of the existing, known Python
107driver parameters (such as "timeout" perhaps), SQLAlchemy's dialect would
108require adjustment for the URL scheme to continue to support this.
110As is always the case for all SQLAlchemy dialects, the entire "URL" process
111can be bypassed in :func:`_sa.create_engine` through the use of the
112:paramref:`_sa.create_engine.creator`
113parameter which allows for a custom callable
114that creates a Python sqlite3 driver level connection directly.
116.. versionadded:: 1.3.9
118.. seealso::
120 `Uniform Resource Identifiers <https://www.sqlite.org/uri.html>`_ - in
121 the SQLite documentation
123Compatibility with sqlite3 "native" date and datetime types
124-----------------------------------------------------------
126The pysqlite driver includes the sqlite3.PARSE_DECLTYPES and
127sqlite3.PARSE_COLNAMES options, which have the effect of any column
128or expression explicitly cast as "date" or "timestamp" will be converted
129to a Python date or datetime object. The date and datetime types provided
130with the pysqlite dialect are not currently compatible with these options,
131since they render the ISO date/datetime including microseconds, which
132pysqlite's driver does not. Additionally, SQLAlchemy does not at
133this time automatically render the "cast" syntax required for the
134freestanding functions "current_timestamp" and "current_date" to return
135datetime/date types natively. Unfortunately, pysqlite
136does not provide the standard DBAPI types in ``cursor.description``,
137leaving SQLAlchemy with no way to detect these types on the fly
138without expensive per-row type checks.
140Keeping in mind that pysqlite's parsing option is not recommended,
141nor should be necessary, for use with SQLAlchemy, usage of PARSE_DECLTYPES
142can be forced if one configures "native_datetime=True" on create_engine()::
144 engine = create_engine('sqlite://',
145 connect_args={'detect_types':
146 sqlite3.PARSE_DECLTYPES|sqlite3.PARSE_COLNAMES},
147 native_datetime=True
148 )
150With this flag enabled, the DATE and TIMESTAMP types (but note - not the
151DATETIME or TIME types...confused yet ?) will not perform any bind parameter
152or result processing. Execution of "func.current_date()" will return a string.
153"func.current_timestamp()" is registered as returning a DATETIME type in
154SQLAlchemy, so this function still receives SQLAlchemy-level result
155processing.
157.. _pysqlite_threading_pooling:
159Threading/Pooling Behavior
160---------------------------
162Pysqlite's default behavior is to prohibit the usage of a single connection
163in more than one thread. This is originally intended to work with older
164versions of SQLite that did not support multithreaded operation under
165various circumstances. In particular, older SQLite versions
166did not allow a ``:memory:`` database to be used in multiple threads
167under any circumstances.
169Pysqlite does include a now-undocumented flag known as
170``check_same_thread`` which will disable this check, however note that
171pysqlite connections are still not safe to use in concurrently in multiple
172threads. In particular, any statement execution calls would need to be
173externally mutexed, as Pysqlite does not provide for thread-safe propagation
174of error messages among other things. So while even ``:memory:`` databases
175can be shared among threads in modern SQLite, Pysqlite doesn't provide enough
176thread-safety to make this usage worth it.
178SQLAlchemy sets up pooling to work with Pysqlite's default behavior:
180* When a ``:memory:`` SQLite database is specified, the dialect by default
181 will use :class:`.SingletonThreadPool`. This pool maintains a single
182 connection per thread, so that all access to the engine within the current
183 thread use the same ``:memory:`` database - other threads would access a
184 different ``:memory:`` database.
185* When a file-based database is specified, the dialect will use
186 :class:`.NullPool` as the source of connections. This pool closes and
187 discards connections which are returned to the pool immediately. SQLite
188 file-based connections have extremely low overhead, so pooling is not
189 necessary. The scheme also prevents a connection from being used again in
190 a different thread and works best with SQLite's coarse-grained file locking.
192Using a Memory Database in Multiple Threads
193^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
195To use a ``:memory:`` database in a multithreaded scenario, the same
196connection object must be shared among threads, since the database exists
197only within the scope of that connection. The
198:class:`.StaticPool` implementation will maintain a single connection
199globally, and the ``check_same_thread`` flag can be passed to Pysqlite
200as ``False``::
202 from sqlalchemy.pool import StaticPool
203 engine = create_engine('sqlite://',
204 connect_args={'check_same_thread':False},
205 poolclass=StaticPool)
207Note that using a ``:memory:`` database in multiple threads requires a recent
208version of SQLite.
210Using Temporary Tables with SQLite
211^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
213Due to the way SQLite deals with temporary tables, if you wish to use a
214temporary table in a file-based SQLite database across multiple checkouts
215from the connection pool, such as when using an ORM :class:`.Session` where
216the temporary table should continue to remain after :meth:`.Session.commit` or
217:meth:`.Session.rollback` is called, a pool which maintains a single
218connection must be used. Use :class:`.SingletonThreadPool` if the scope is
219only needed within the current thread, or :class:`.StaticPool` is scope is
220needed within multiple threads for this case::
222 # maintain the same connection per thread
223 from sqlalchemy.pool import SingletonThreadPool
224 engine = create_engine('sqlite:///mydb.db',
225 poolclass=SingletonThreadPool)
228 # maintain the same connection across all threads
229 from sqlalchemy.pool import StaticPool
230 engine = create_engine('sqlite:///mydb.db',
231 poolclass=StaticPool)
233Note that :class:`.SingletonThreadPool` should be configured for the number
234of threads that are to be used; beyond that number, connections will be
235closed out in a non deterministic way.
237Unicode
238-------
240The pysqlite driver only returns Python ``unicode`` objects in result sets,
241never plain strings, and accommodates ``unicode`` objects within bound
242parameter values in all cases. Regardless of the SQLAlchemy string type in
243use, string-based result values will by Python ``unicode`` in Python 2.
244The :class:`.Unicode` type should still be used to indicate those columns that
245require unicode, however, so that non-``unicode`` values passed inadvertently
246will emit a warning. Pysqlite will emit an error if a non-``unicode`` string
247is passed containing non-ASCII characters.
249Dealing with Mixed String / Binary Columns in Python 3
250------------------------------------------------------
252The SQLite database is weakly typed, and as such it is possible when using
253binary values, which in Python 3 are represented as ``b'some string'``, that a
254particular SQLite database can have data values within different rows where
255some of them will be returned as a ``b''`` value by the Pysqlite driver, and
256others will be returned as Python strings, e.g. ``''`` values. This situation
257is not known to occur if the SQLAlchemy :class:`.LargeBinary` datatype is used
258consistently, however if a particular SQLite database has data that was
259inserted using the Pysqlite driver directly, or when using the SQLAlchemy
260:class:`.String` type which was later changed to :class:`.LargeBinary`, the
261table will not be consistently readable because SQLAlchemy's
262:class:`.LargeBinary` datatype does not handle strings so it has no way of
263"encoding" a value that is in string format.
265To deal with a SQLite table that has mixed string / binary data in the
266same column, use a custom type that will check each row individually::
268 # note this is Python 3 only
270 from sqlalchemy import String
271 from sqlalchemy import TypeDecorator
273 class MixedBinary(TypeDecorator):
274 impl = String
276 def process_result_value(self, value, dialect):
277 if isinstance(value, str):
278 value = bytes(value, 'utf-8')
279 elif value is not None:
280 value = bytes(value)
282 return value
284Then use the above ``MixedBinary`` datatype in the place where
285:class:`.LargeBinary` would normally be used.
287.. _pysqlite_serializable:
289Serializable isolation / Savepoints / Transactional DDL
290-------------------------------------------------------
292In the section :ref:`sqlite_concurrency`, we refer to the pysqlite
293driver's assortment of issues that prevent several features of SQLite
294from working correctly. The pysqlite DBAPI driver has several
295long-standing bugs which impact the correctness of its transactional
296behavior. In its default mode of operation, SQLite features such as
297SERIALIZABLE isolation, transactional DDL, and SAVEPOINT support are
298non-functional, and in order to use these features, workarounds must
299be taken.
301The issue is essentially that the driver attempts to second-guess the user's
302intent, failing to start transactions and sometimes ending them prematurely, in
303an effort to minimize the SQLite databases's file locking behavior, even
304though SQLite itself uses "shared" locks for read-only activities.
306SQLAlchemy chooses to not alter this behavior by default, as it is the
307long-expected behavior of the pysqlite driver; if and when the pysqlite
308driver attempts to repair these issues, that will be more of a driver towards
309defaults for SQLAlchemy.
311The good news is that with a few events, we can implement transactional
312support fully, by disabling pysqlite's feature entirely and emitting BEGIN
313ourselves. This is achieved using two event listeners::
315 from sqlalchemy import create_engine, event
317 engine = create_engine("sqlite:///myfile.db")
319 @event.listens_for(engine, "connect")
320 def do_connect(dbapi_connection, connection_record):
321 # disable pysqlite's emitting of the BEGIN statement entirely.
322 # also stops it from emitting COMMIT before any DDL.
323 dbapi_connection.isolation_level = None
325 @event.listens_for(engine, "begin")
326 def do_begin(conn):
327 # emit our own BEGIN
328 conn.execute("BEGIN")
330.. warning:: When using the above recipe, it is advised to not use the
331 :paramref:`.Connection.execution_options.isolation_level` setting on
332 :class:`_engine.Connection` and :func:`_sa.create_engine`
333 with the SQLite driver,
334 as this function necessarily will also alter the ".isolation_level" setting.
337Above, we intercept a new pysqlite connection and disable any transactional
338integration. Then, at the point at which SQLAlchemy knows that transaction
339scope is to begin, we emit ``"BEGIN"`` ourselves.
341When we take control of ``"BEGIN"``, we can also control directly SQLite's
342locking modes, introduced at
343`BEGIN TRANSACTION <http://sqlite.org/lang_transaction.html>`_,
344by adding the desired locking mode to our ``"BEGIN"``::
346 @event.listens_for(engine, "begin")
347 def do_begin(conn):
348 conn.execute("BEGIN EXCLUSIVE")
350.. seealso::
352 `BEGIN TRANSACTION <http://sqlite.org/lang_transaction.html>`_ -
353 on the SQLite site
355 `sqlite3 SELECT does not BEGIN a transaction <http://bugs.python.org/issue9924>`_ -
356 on the Python bug tracker
358 `sqlite3 module breaks transactions and potentially corrupts data <http://bugs.python.org/issue10740>`_ -
359 on the Python bug tracker
362""" # noqa
364import os
366from .base import DATE
367from .base import DATETIME
368from .base import SQLiteDialect
369from ... import exc
370from ... import pool
371from ... import types as sqltypes
372from ... import util
375class _SQLite_pysqliteTimeStamp(DATETIME):
376 def bind_processor(self, dialect):
377 if dialect.native_datetime:
378 return None
379 else:
380 return DATETIME.bind_processor(self, dialect)
382 def result_processor(self, dialect, coltype):
383 if dialect.native_datetime:
384 return None
385 else:
386 return DATETIME.result_processor(self, dialect, coltype)
389class _SQLite_pysqliteDate(DATE):
390 def bind_processor(self, dialect):
391 if dialect.native_datetime:
392 return None
393 else:
394 return DATE.bind_processor(self, dialect)
396 def result_processor(self, dialect, coltype):
397 if dialect.native_datetime:
398 return None
399 else:
400 return DATE.result_processor(self, dialect, coltype)
403class SQLiteDialect_pysqlite(SQLiteDialect):
404 default_paramstyle = "qmark"
406 colspecs = util.update_copy(
407 SQLiteDialect.colspecs,
408 {
409 sqltypes.Date: _SQLite_pysqliteDate,
410 sqltypes.TIMESTAMP: _SQLite_pysqliteTimeStamp,
411 },
412 )
414 if not util.py2k:
415 description_encoding = None
417 driver = "pysqlite"
419 @classmethod
420 def dbapi(cls):
421 if util.py2k:
422 try:
423 from pysqlite2 import dbapi2 as sqlite
424 except ImportError:
425 try:
426 from sqlite3 import dbapi2 as sqlite
427 except ImportError as e:
428 raise e
429 else:
430 from sqlite3 import dbapi2 as sqlite
431 return sqlite
433 @classmethod
434 def _is_url_file_db(cls, url):
435 if url.database and url.database != ":memory:":
436 return True
437 else:
438 return False
440 @classmethod
441 def get_pool_class(cls, url):
442 if cls._is_url_file_db(url):
443 return pool.NullPool
444 else:
445 return pool.SingletonThreadPool
447 def _get_server_version_info(self, connection):
448 return self.dbapi.sqlite_version_info
450 def set_isolation_level(self, connection, level):
451 if hasattr(connection, "connection"):
452 dbapi_connection = connection.connection
453 else:
454 dbapi_connection = connection
456 if level == "AUTOCOMMIT":
457 dbapi_connection.isolation_level = None
458 else:
459 dbapi_connection.isolation_level = ""
460 return super(SQLiteDialect_pysqlite, self).set_isolation_level(
461 connection, level
462 )
464 def create_connect_args(self, url):
465 if url.username or url.password or url.host or url.port:
466 raise exc.ArgumentError(
467 "Invalid SQLite URL: %s\n"
468 "Valid SQLite URL forms are:\n"
469 " sqlite:///:memory: (or, sqlite://)\n"
470 " sqlite:///relative/path/to/file.db\n"
471 " sqlite:////absolute/path/to/file.db" % (url,)
472 )
474 # theoretically, this list can be augmented, at least as far as
475 # parameter names accepted by sqlite3/pysqlite, using
476 # inspect.getfullargspec(). for the moment this seems like overkill
477 # as these parameters don't change very often, and as always,
478 # parameters passed to connect_args will always go to the
479 # sqlite3/pysqlite driver.
480 pysqlite_args = [
481 ("uri", bool),
482 ("timeout", float),
483 ("isolation_level", str),
484 ("detect_types", int),
485 ("check_same_thread", bool),
486 ("cached_statements", int),
487 ]
488 opts = url.query
489 pysqlite_opts = {}
490 for key, type_ in pysqlite_args:
491 util.coerce_kw_type(opts, key, type_, dest=pysqlite_opts)
493 if pysqlite_opts.get("uri", False):
494 uri_opts = opts.copy()
495 # here, we are actually separating the parameters that go to
496 # sqlite3/pysqlite vs. those that go the SQLite URI. What if
497 # two names conflict? again, this seems to be not the case right
498 # now, and in the case that new names are added to
499 # either side which overlap, again the sqlite3/pysqlite parameters
500 # can be passed through connect_args instead of in the URL.
501 # If SQLite native URIs add a parameter like "timeout" that
502 # we already have listed here for the python driver, then we need
503 # to adjust for that here.
504 for key, type_ in pysqlite_args:
505 uri_opts.pop(key, None)
506 filename = url.database
507 if uri_opts:
508 # sorting of keys is for unit test support
509 filename += "?" + (
510 "&".join(
511 "%s=%s" % (key, uri_opts[key])
512 for key in sorted(uri_opts)
513 )
514 )
515 else:
516 filename = url.database or ":memory:"
517 if filename != ":memory:":
518 filename = os.path.abspath(filename)
520 return ([filename], pysqlite_opts)
522 def is_disconnect(self, e, connection, cursor):
523 return isinstance(
524 e, self.dbapi.ProgrammingError
525 ) and "Cannot operate on a closed database." in str(e)
528dialect = SQLiteDialect_pysqlite