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# dialects/sqlite/pysqlite.py
2# Copyright (C) 2005-2025 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: https://www.opensource.org/licenses/mit-license.php
7# mypy: ignore-errors
10r"""
11.. dialect:: sqlite+pysqlite
12 :name: pysqlite
13 :dbapi: sqlite3
14 :connectstring: sqlite+pysqlite:///file_path
15 :url: https://docs.python.org/library/sqlite3.html
17 Note that ``pysqlite`` is the same driver as the ``sqlite3``
18 module included with the Python distribution.
20Driver
21------
23The ``sqlite3`` Python DBAPI is standard on all modern Python versions;
24for cPython and Pypy, no additional installation is necessary.
27Connect Strings
28---------------
30The file specification for the SQLite database is taken as the "database"
31portion of the URL. Note that the format of a SQLAlchemy url is:
33.. sourcecode:: text
35 driver://user:pass@host/database
37This means that the actual filename to be used starts with the characters to
38the **right** of the third slash. So connecting to a relative filepath
39looks like::
41 # relative path
42 e = create_engine("sqlite:///path/to/database.db")
44An absolute path, which is denoted by starting with a slash, means you
45need **four** slashes::
47 # absolute path
48 e = create_engine("sqlite:////path/to/database.db")
50To use a Windows path, regular drive specifications and backslashes can be
51used. Double backslashes are probably needed::
53 # absolute path on Windows
54 e = create_engine("sqlite:///C:\\path\\to\\database.db")
56To use sqlite ``:memory:`` database specify it as the filename using
57``sqlite:///:memory:``. It's also the default if no filepath is
58present, specifying only ``sqlite://`` and nothing else::
60 # in-memory database (note three slashes)
61 e = create_engine("sqlite:///:memory:")
62 # also in-memory database
63 e2 = create_engine("sqlite://")
65.. _pysqlite_uri_connections:
67URI Connections
68^^^^^^^^^^^^^^^
70Modern versions of SQLite support an alternative system of connecting using a
71`driver level URI <https://www.sqlite.org/uri.html>`_, which has the advantage
72that additional driver-level arguments can be passed including options such as
73"read only". The Python sqlite3 driver supports this mode under modern Python
743 versions. The SQLAlchemy pysqlite driver supports this mode of use by
75specifying "uri=true" in the URL query string. The SQLite-level "URI" is kept
76as the "database" portion of the SQLAlchemy url (that is, following a slash)::
78 e = create_engine("sqlite:///file:path/to/database?mode=ro&uri=true")
80.. note:: The "uri=true" parameter must appear in the **query string**
81 of the URL. It will not currently work as expected if it is only
82 present in the :paramref:`_sa.create_engine.connect_args`
83 parameter dictionary.
85The logic reconciles the simultaneous presence of SQLAlchemy's query string and
86SQLite's query string by separating out the parameters that belong to the
87Python sqlite3 driver vs. those that belong to the SQLite URI. This is
88achieved through the use of a fixed list of parameters known to be accepted by
89the Python side of the driver. For example, to include a URL that indicates
90the Python sqlite3 "timeout" and "check_same_thread" parameters, along with the
91SQLite "mode" and "nolock" parameters, they can all be passed together on the
92query string::
94 e = create_engine(
95 "sqlite:///file:path/to/database?"
96 "check_same_thread=true&timeout=10&mode=ro&nolock=1&uri=true"
97 )
99Above, the pysqlite / sqlite3 DBAPI would be passed arguments as::
101 sqlite3.connect(
102 "file:path/to/database?mode=ro&nolock=1",
103 check_same_thread=True,
104 timeout=10,
105 uri=True,
106 )
108Regarding future parameters added to either the Python or native drivers. new
109parameter names added to the SQLite URI scheme should be automatically
110accommodated by this scheme. New parameter names added to the Python driver
111side can be accommodated by specifying them in the
112:paramref:`_sa.create_engine.connect_args` dictionary,
113until dialect support is
114added by SQLAlchemy. For the less likely case that the native SQLite driver
115adds a new parameter name that overlaps with one of the existing, known Python
116driver parameters (such as "timeout" perhaps), SQLAlchemy's dialect would
117require adjustment for the URL scheme to continue to support this.
119As is always the case for all SQLAlchemy dialects, the entire "URL" process
120can be bypassed in :func:`_sa.create_engine` through the use of the
121:paramref:`_sa.create_engine.creator`
122parameter which allows for a custom callable
123that creates a Python sqlite3 driver level connection directly.
125.. seealso::
127 `Uniform Resource Identifiers <https://www.sqlite.org/uri.html>`_ - in
128 the SQLite documentation
130.. _pysqlite_regexp:
132Regular Expression Support
133---------------------------
135.. versionadded:: 1.4
137Support for the :meth:`_sql.ColumnOperators.regexp_match` operator is provided
138using Python's re.search_ function. SQLite itself does not include a working
139regular expression operator; instead, it includes a non-implemented placeholder
140operator ``REGEXP`` that calls a user-defined function that must be provided.
142SQLAlchemy's implementation makes use of the pysqlite create_function_ hook
143as follows::
146 def regexp(a, b):
147 return re.search(a, b) is not None
150 sqlite_connection.create_function(
151 "regexp",
152 2,
153 regexp,
154 )
156There is currently no support for regular expression flags as a separate
157argument, as these are not supported by SQLite's REGEXP operator, however these
158may be included inline within the regular expression string. See `Python regular expressions`_ for
159details.
161.. seealso::
163 `Python regular expressions`_: Documentation for Python's regular expression syntax.
165.. _create_function: https://docs.python.org/3/library/sqlite3.html#sqlite3.Connection.create_function
167.. _re.search: https://docs.python.org/3/library/re.html#re.search
169.. _Python regular expressions: https://docs.python.org/3/library/re.html#re.search
173Compatibility with sqlite3 "native" date and datetime types
174-----------------------------------------------------------
176The pysqlite driver includes the sqlite3.PARSE_DECLTYPES and
177sqlite3.PARSE_COLNAMES options, which have the effect of any column
178or expression explicitly cast as "date" or "timestamp" will be converted
179to a Python date or datetime object. The date and datetime types provided
180with the pysqlite dialect are not currently compatible with these options,
181since they render the ISO date/datetime including microseconds, which
182pysqlite's driver does not. Additionally, SQLAlchemy does not at
183this time automatically render the "cast" syntax required for the
184freestanding functions "current_timestamp" and "current_date" to return
185datetime/date types natively. Unfortunately, pysqlite
186does not provide the standard DBAPI types in ``cursor.description``,
187leaving SQLAlchemy with no way to detect these types on the fly
188without expensive per-row type checks.
190Keeping in mind that pysqlite's parsing option is not recommended,
191nor should be necessary, for use with SQLAlchemy, usage of PARSE_DECLTYPES
192can be forced if one configures "native_datetime=True" on create_engine()::
194 engine = create_engine(
195 "sqlite://",
196 connect_args={
197 "detect_types": sqlite3.PARSE_DECLTYPES | sqlite3.PARSE_COLNAMES
198 },
199 native_datetime=True,
200 )
202With this flag enabled, the DATE and TIMESTAMP types (but note - not the
203DATETIME or TIME types...confused yet ?) will not perform any bind parameter
204or result processing. Execution of "func.current_date()" will return a string.
205"func.current_timestamp()" is registered as returning a DATETIME type in
206SQLAlchemy, so this function still receives SQLAlchemy-level result
207processing.
209.. _pysqlite_threading_pooling:
211Threading/Pooling Behavior
212---------------------------
214The ``sqlite3`` DBAPI by default prohibits the use of a particular connection
215in a thread which is not the one in which it was created. As SQLite has
216matured, it's behavior under multiple threads has improved, and even includes
217options for memory only databases to be used in multiple threads.
219The thread prohibition is known as "check same thread" and may be controlled
220using the ``sqlite3`` parameter ``check_same_thread``, which will disable or
221enable this check. SQLAlchemy's default behavior here is to set
222``check_same_thread`` to ``False`` automatically whenever a file-based database
223is in use, to establish compatibility with the default pool class
224:class:`.QueuePool`.
226The SQLAlchemy ``pysqlite`` DBAPI establishes the connection pool differently
227based on the kind of SQLite database that's requested:
229* When a ``:memory:`` SQLite database is specified, the dialect by default
230 will use :class:`.SingletonThreadPool`. This pool maintains a single
231 connection per thread, so that all access to the engine within the current
232 thread use the same ``:memory:`` database - other threads would access a
233 different ``:memory:`` database. The ``check_same_thread`` parameter
234 defaults to ``True``.
235* When a file-based database is specified, the dialect will use
236 :class:`.QueuePool` as the source of connections. at the same time,
237 the ``check_same_thread`` flag is set to False by default unless overridden.
239 .. versionchanged:: 2.0
241 SQLite file database engines now use :class:`.QueuePool` by default.
242 Previously, :class:`.NullPool` were used. The :class:`.NullPool` class
243 may be used by specifying it via the
244 :paramref:`_sa.create_engine.poolclass` parameter.
246Disabling Connection Pooling for File Databases
247^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
249Pooling may be disabled for a file based database by specifying the
250:class:`.NullPool` implementation for the :func:`_sa.create_engine.poolclass`
251parameter::
253 from sqlalchemy import NullPool
255 engine = create_engine("sqlite:///myfile.db", poolclass=NullPool)
257It's been observed that the :class:`.NullPool` implementation incurs an
258extremely small performance overhead for repeated checkouts due to the lack of
259connection re-use implemented by :class:`.QueuePool`. However, it still
260may be beneficial to use this class if the application is experiencing
261issues with files being locked.
263Using a Memory Database in Multiple Threads
264^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
266To use a ``:memory:`` database in a multithreaded scenario, the same
267connection object must be shared among threads, since the database exists
268only within the scope of that connection. The
269:class:`.StaticPool` implementation will maintain a single connection
270globally, and the ``check_same_thread`` flag can be passed to Pysqlite
271as ``False``::
273 from sqlalchemy.pool import StaticPool
275 engine = create_engine(
276 "sqlite://",
277 connect_args={"check_same_thread": False},
278 poolclass=StaticPool,
279 )
281Note that using a ``:memory:`` database in multiple threads requires a recent
282version of SQLite.
284Using Temporary Tables with SQLite
285^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
287Due to the way SQLite deals with temporary tables, if you wish to use a
288temporary table in a file-based SQLite database across multiple checkouts
289from the connection pool, such as when using an ORM :class:`.Session` where
290the temporary table should continue to remain after :meth:`.Session.commit` or
291:meth:`.Session.rollback` is called, a pool which maintains a single
292connection must be used. Use :class:`.SingletonThreadPool` if the scope is
293only needed within the current thread, or :class:`.StaticPool` is scope is
294needed within multiple threads for this case::
296 # maintain the same connection per thread
297 from sqlalchemy.pool import SingletonThreadPool
299 engine = create_engine("sqlite:///mydb.db", poolclass=SingletonThreadPool)
302 # maintain the same connection across all threads
303 from sqlalchemy.pool import StaticPool
305 engine = create_engine("sqlite:///mydb.db", poolclass=StaticPool)
307Note that :class:`.SingletonThreadPool` should be configured for the number
308of threads that are to be used; beyond that number, connections will be
309closed out in a non deterministic way.
312Dealing with Mixed String / Binary Columns
313------------------------------------------------------
315The SQLite database is weakly typed, and as such it is possible when using
316binary values, which in Python are represented as ``b'some string'``, that a
317particular SQLite database can have data values within different rows where
318some of them will be returned as a ``b''`` value by the Pysqlite driver, and
319others will be returned as Python strings, e.g. ``''`` values. This situation
320is not known to occur if the SQLAlchemy :class:`.LargeBinary` datatype is used
321consistently, however if a particular SQLite database has data that was
322inserted using the Pysqlite driver directly, or when using the SQLAlchemy
323:class:`.String` type which was later changed to :class:`.LargeBinary`, the
324table will not be consistently readable because SQLAlchemy's
325:class:`.LargeBinary` datatype does not handle strings so it has no way of
326"encoding" a value that is in string format.
328To deal with a SQLite table that has mixed string / binary data in the
329same column, use a custom type that will check each row individually::
331 from sqlalchemy import String
332 from sqlalchemy import TypeDecorator
335 class MixedBinary(TypeDecorator):
336 impl = String
337 cache_ok = True
339 def process_result_value(self, value, dialect):
340 if isinstance(value, str):
341 value = bytes(value, "utf-8")
342 elif value is not None:
343 value = bytes(value)
345 return value
347Then use the above ``MixedBinary`` datatype in the place where
348:class:`.LargeBinary` would normally be used.
350.. _pysqlite_serializable:
352Serializable isolation / Savepoints / Transactional DDL
353-------------------------------------------------------
355A newly revised version of this important section is now available
356at the top level of the SQLAlchemy SQLite documentation, in the section
357:ref:`sqlite_transactions`.
360.. _pysqlite_udfs:
362User-Defined Functions
363----------------------
365pysqlite supports a `create_function() <https://docs.python.org/3/library/sqlite3.html#sqlite3.Connection.create_function>`_
366method that allows us to create our own user-defined functions (UDFs) in Python and use them directly in SQLite queries.
367These functions are registered with a specific DBAPI Connection.
369SQLAlchemy uses connection pooling with file-based SQLite databases, so we need to ensure that the UDF is attached to the
370connection when it is created. That is accomplished with an event listener::
372 from sqlalchemy import create_engine
373 from sqlalchemy import event
374 from sqlalchemy import text
377 def udf():
378 return "udf-ok"
381 engine = create_engine("sqlite:///./db_file")
384 @event.listens_for(engine, "connect")
385 def connect(conn, rec):
386 conn.create_function("udf", 0, udf)
389 for i in range(5):
390 with engine.connect() as conn:
391 print(conn.scalar(text("SELECT UDF()")))
393""" # noqa
395import math
396import os
397import re
399from .base import DATE
400from .base import DATETIME
401from .base import SQLiteDialect
402from ... import exc
403from ... import pool
404from ... import types as sqltypes
405from ... import util
408class _SQLite_pysqliteTimeStamp(DATETIME):
409 def bind_processor(self, dialect):
410 if dialect.native_datetime:
411 return None
412 else:
413 return DATETIME.bind_processor(self, dialect)
415 def result_processor(self, dialect, coltype):
416 if dialect.native_datetime:
417 return None
418 else:
419 return DATETIME.result_processor(self, dialect, coltype)
422class _SQLite_pysqliteDate(DATE):
423 def bind_processor(self, dialect):
424 if dialect.native_datetime:
425 return None
426 else:
427 return DATE.bind_processor(self, dialect)
429 def result_processor(self, dialect, coltype):
430 if dialect.native_datetime:
431 return None
432 else:
433 return DATE.result_processor(self, dialect, coltype)
436class SQLiteDialect_pysqlite(SQLiteDialect):
437 default_paramstyle = "qmark"
438 supports_statement_cache = True
439 returns_native_bytes = True
441 colspecs = util.update_copy(
442 SQLiteDialect.colspecs,
443 {
444 sqltypes.Date: _SQLite_pysqliteDate,
445 sqltypes.TIMESTAMP: _SQLite_pysqliteTimeStamp,
446 },
447 )
449 description_encoding = None
451 driver = "pysqlite"
453 @classmethod
454 def import_dbapi(cls):
455 from sqlite3 import dbapi2 as sqlite
457 return sqlite
459 @classmethod
460 def _is_url_file_db(cls, url):
461 if (url.database and url.database != ":memory:") and (
462 url.query.get("mode", None) != "memory"
463 ):
464 return True
465 else:
466 return False
468 @classmethod
469 def get_pool_class(cls, url):
470 if cls._is_url_file_db(url):
471 return pool.QueuePool
472 else:
473 return pool.SingletonThreadPool
475 def _get_server_version_info(self, connection):
476 return self.dbapi.sqlite_version_info
478 _isolation_lookup = SQLiteDialect._isolation_lookup.union(
479 {
480 "AUTOCOMMIT": None,
481 }
482 )
484 def set_isolation_level(self, dbapi_connection, level):
485 if level == "AUTOCOMMIT":
486 dbapi_connection.isolation_level = None
487 else:
488 dbapi_connection.isolation_level = ""
489 return super().set_isolation_level(dbapi_connection, level)
491 def on_connect(self):
492 def regexp(a, b):
493 if b is None:
494 return None
495 return re.search(a, b) is not None
497 if self._get_server_version_info(None) >= (3, 9):
498 # sqlite must be greater than 3.8.3 for deterministic=True
499 # https://docs.python.org/3/library/sqlite3.html#sqlite3.Connection.create_function
500 # the check is more conservative since there were still issues
501 # with following 3.8 sqlite versions
502 create_func_kw = {"deterministic": True}
503 else:
504 create_func_kw = {}
506 def set_regexp(dbapi_connection):
507 dbapi_connection.create_function(
508 "regexp", 2, regexp, **create_func_kw
509 )
511 def floor_func(dbapi_connection):
512 # NOTE: floor is optionally present in sqlite 3.35+ , however
513 # as it is normally non-present we deliver floor() unconditionally
514 # for now.
515 # https://www.sqlite.org/lang_mathfunc.html
516 dbapi_connection.create_function(
517 "floor", 1, math.floor, **create_func_kw
518 )
520 fns = [set_regexp, floor_func]
522 def connect(conn):
523 for fn in fns:
524 fn(conn)
526 return connect
528 def create_connect_args(self, url):
529 if url.username or url.password or url.host or url.port:
530 raise exc.ArgumentError(
531 "Invalid SQLite URL: %s\n"
532 "Valid SQLite URL forms are:\n"
533 " sqlite:///:memory: (or, sqlite://)\n"
534 " sqlite:///relative/path/to/file.db\n"
535 " sqlite:////absolute/path/to/file.db" % (url,)
536 )
538 # theoretically, this list can be augmented, at least as far as
539 # parameter names accepted by sqlite3/pysqlite, using
540 # inspect.getfullargspec(). for the moment this seems like overkill
541 # as these parameters don't change very often, and as always,
542 # parameters passed to connect_args will always go to the
543 # sqlite3/pysqlite driver.
544 pysqlite_args = [
545 ("uri", bool),
546 ("timeout", float),
547 ("isolation_level", str),
548 ("detect_types", int),
549 ("check_same_thread", bool),
550 ("cached_statements", int),
551 ]
552 opts = url.query
553 pysqlite_opts = {}
554 for key, type_ in pysqlite_args:
555 util.coerce_kw_type(opts, key, type_, dest=pysqlite_opts)
557 if pysqlite_opts.get("uri", False):
558 uri_opts = dict(opts)
559 # here, we are actually separating the parameters that go to
560 # sqlite3/pysqlite vs. those that go the SQLite URI. What if
561 # two names conflict? again, this seems to be not the case right
562 # now, and in the case that new names are added to
563 # either side which overlap, again the sqlite3/pysqlite parameters
564 # can be passed through connect_args instead of in the URL.
565 # If SQLite native URIs add a parameter like "timeout" that
566 # we already have listed here for the python driver, then we need
567 # to adjust for that here.
568 for key, type_ in pysqlite_args:
569 uri_opts.pop(key, None)
570 filename = url.database
571 if uri_opts:
572 # sorting of keys is for unit test support
573 filename += "?" + (
574 "&".join(
575 "%s=%s" % (key, uri_opts[key])
576 for key in sorted(uri_opts)
577 )
578 )
579 else:
580 filename = url.database or ":memory:"
581 if filename != ":memory:":
582 filename = os.path.abspath(filename)
584 pysqlite_opts.setdefault(
585 "check_same_thread", not self._is_url_file_db(url)
586 )
588 return ([filename], pysqlite_opts)
590 def is_disconnect(self, e, connection, cursor):
591 return isinstance(
592 e, self.dbapi.ProgrammingError
593 ) and "Cannot operate on a closed database." in str(e)
596dialect = SQLiteDialect_pysqlite
599class _SQLiteDialect_pysqlite_numeric(SQLiteDialect_pysqlite):
600 """numeric dialect for testing only
602 internal use only. This dialect is **NOT** supported by SQLAlchemy
603 and may change at any time.
605 """
607 supports_statement_cache = True
608 default_paramstyle = "numeric"
609 driver = "pysqlite_numeric"
611 _first_bind = ":1"
612 _not_in_statement_regexp = None
614 def __init__(self, *arg, **kw):
615 kw.setdefault("paramstyle", "numeric")
616 super().__init__(*arg, **kw)
618 def create_connect_args(self, url):
619 arg, opts = super().create_connect_args(url)
620 opts["factory"] = self._fix_sqlite_issue_99953()
621 return arg, opts
623 def _fix_sqlite_issue_99953(self):
624 import sqlite3
626 first_bind = self._first_bind
627 if self._not_in_statement_regexp:
628 nis = self._not_in_statement_regexp
630 def _test_sql(sql):
631 m = nis.search(sql)
632 assert not m, f"Found {nis.pattern!r} in {sql!r}"
634 else:
636 def _test_sql(sql):
637 pass
639 def _numeric_param_as_dict(parameters):
640 if parameters:
641 assert isinstance(parameters, tuple)
642 return {
643 str(idx): value for idx, value in enumerate(parameters, 1)
644 }
645 else:
646 return ()
648 class SQLiteFix99953Cursor(sqlite3.Cursor):
649 def execute(self, sql, parameters=()):
650 _test_sql(sql)
651 if first_bind in sql:
652 parameters = _numeric_param_as_dict(parameters)
653 return super().execute(sql, parameters)
655 def executemany(self, sql, parameters):
656 _test_sql(sql)
657 if first_bind in sql:
658 parameters = [
659 _numeric_param_as_dict(p) for p in parameters
660 ]
661 return super().executemany(sql, parameters)
663 class SQLiteFix99953Connection(sqlite3.Connection):
664 def cursor(self, factory=None):
665 if factory is None:
666 factory = SQLiteFix99953Cursor
667 return super().cursor(factory=factory)
669 def execute(self, sql, parameters=()):
670 _test_sql(sql)
671 if first_bind in sql:
672 parameters = _numeric_param_as_dict(parameters)
673 return super().execute(sql, parameters)
675 def executemany(self, sql, parameters):
676 _test_sql(sql)
677 if first_bind in sql:
678 parameters = [
679 _numeric_param_as_dict(p) for p in parameters
680 ]
681 return super().executemany(sql, parameters)
683 return SQLiteFix99953Connection
686class _SQLiteDialect_pysqlite_dollar(_SQLiteDialect_pysqlite_numeric):
687 """numeric dialect that uses $ for testing only
689 internal use only. This dialect is **NOT** supported by SQLAlchemy
690 and may change at any time.
692 """
694 supports_statement_cache = True
695 default_paramstyle = "numeric_dollar"
696 driver = "pysqlite_dollar"
698 _first_bind = "$1"
699 _not_in_statement_regexp = re.compile(r"[^\d]:\d+")
701 def __init__(self, *arg, **kw):
702 kw.setdefault("paramstyle", "numeric_dollar")
703 super().__init__(*arg, **kw)