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.. versionadded:: 1.3.9
127.. seealso::
129 `Uniform Resource Identifiers <https://www.sqlite.org/uri.html>`_ - in
130 the SQLite documentation
132.. _pysqlite_regexp:
134Regular Expression Support
135---------------------------
137.. versionadded:: 1.4
139Support for the :meth:`_sql.ColumnOperators.regexp_match` operator is provided
140using Python's re.search_ function. SQLite itself does not include a working
141regular expression operator; instead, it includes a non-implemented placeholder
142operator ``REGEXP`` that calls a user-defined function that must be provided.
144SQLAlchemy's implementation makes use of the pysqlite create_function_ hook
145as follows::
148 def regexp(a, b):
149 return re.search(a, b) is not None
152 sqlite_connection.create_function(
153 "regexp",
154 2,
155 regexp,
156 )
158There is currently no support for regular expression flags as a separate
159argument, as these are not supported by SQLite's REGEXP operator, however these
160may be included inline within the regular expression string. See `Python regular expressions`_ for
161details.
163.. seealso::
165 `Python regular expressions`_: Documentation for Python's regular expression syntax.
167.. _create_function: https://docs.python.org/3/library/sqlite3.html#sqlite3.Connection.create_function
169.. _re.search: https://docs.python.org/3/library/re.html#re.search
171.. _Python regular expressions: https://docs.python.org/3/library/re.html#re.search
175Compatibility with sqlite3 "native" date and datetime types
176-----------------------------------------------------------
178The pysqlite driver includes the sqlite3.PARSE_DECLTYPES and
179sqlite3.PARSE_COLNAMES options, which have the effect of any column
180or expression explicitly cast as "date" or "timestamp" will be converted
181to a Python date or datetime object. The date and datetime types provided
182with the pysqlite dialect are not currently compatible with these options,
183since they render the ISO date/datetime including microseconds, which
184pysqlite's driver does not. Additionally, SQLAlchemy does not at
185this time automatically render the "cast" syntax required for the
186freestanding functions "current_timestamp" and "current_date" to return
187datetime/date types natively. Unfortunately, pysqlite
188does not provide the standard DBAPI types in ``cursor.description``,
189leaving SQLAlchemy with no way to detect these types on the fly
190without expensive per-row type checks.
192Keeping in mind that pysqlite's parsing option is not recommended,
193nor should be necessary, for use with SQLAlchemy, usage of PARSE_DECLTYPES
194can be forced if one configures "native_datetime=True" on create_engine()::
196 engine = create_engine(
197 "sqlite://",
198 connect_args={
199 "detect_types": sqlite3.PARSE_DECLTYPES | sqlite3.PARSE_COLNAMES
200 },
201 native_datetime=True,
202 )
204With this flag enabled, the DATE and TIMESTAMP types (but note - not the
205DATETIME or TIME types...confused yet ?) will not perform any bind parameter
206or result processing. Execution of "func.current_date()" will return a string.
207"func.current_timestamp()" is registered as returning a DATETIME type in
208SQLAlchemy, so this function still receives SQLAlchemy-level result
209processing.
211.. _pysqlite_threading_pooling:
213Threading/Pooling Behavior
214---------------------------
216The ``sqlite3`` DBAPI by default prohibits the use of a particular connection
217in a thread which is not the one in which it was created. As SQLite has
218matured, it's behavior under multiple threads has improved, and even includes
219options for memory only databases to be used in multiple threads.
221The thread prohibition is known as "check same thread" and may be controlled
222using the ``sqlite3`` parameter ``check_same_thread``, which will disable or
223enable this check. SQLAlchemy's default behavior here is to set
224``check_same_thread`` to ``False`` automatically whenever a file-based database
225is in use, to establish compatibility with the default pool class
226:class:`.QueuePool`.
228The SQLAlchemy ``pysqlite`` DBAPI establishes the connection pool differently
229based on the kind of SQLite database that's requested:
231* When a ``:memory:`` SQLite database is specified, the dialect by default
232 will use :class:`.SingletonThreadPool`. This pool maintains a single
233 connection per thread, so that all access to the engine within the current
234 thread use the same ``:memory:`` database - other threads would access a
235 different ``:memory:`` database. The ``check_same_thread`` parameter
236 defaults to ``True``.
237* When a file-based database is specified, the dialect will use
238 :class:`.QueuePool` as the source of connections. at the same time,
239 the ``check_same_thread`` flag is set to False by default unless overridden.
241 .. versionchanged:: 2.0
243 SQLite file database engines now use :class:`.QueuePool` by default.
244 Previously, :class:`.NullPool` were used. The :class:`.NullPool` class
245 may be used by specifying it via the
246 :paramref:`_sa.create_engine.poolclass` parameter.
248Disabling Connection Pooling for File Databases
249^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
251Pooling may be disabled for a file based database by specifying the
252:class:`.NullPool` implementation for the :func:`_sa.create_engine.poolclass`
253parameter::
255 from sqlalchemy import NullPool
257 engine = create_engine("sqlite:///myfile.db", poolclass=NullPool)
259It's been observed that the :class:`.NullPool` implementation incurs an
260extremely small performance overhead for repeated checkouts due to the lack of
261connection re-use implemented by :class:`.QueuePool`. However, it still
262may be beneficial to use this class if the application is experiencing
263issues with files being locked.
265Using a Memory Database in Multiple Threads
266^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
268To use a ``:memory:`` database in a multithreaded scenario, the same
269connection object must be shared among threads, since the database exists
270only within the scope of that connection. The
271:class:`.StaticPool` implementation will maintain a single connection
272globally, and the ``check_same_thread`` flag can be passed to Pysqlite
273as ``False``::
275 from sqlalchemy.pool import StaticPool
277 engine = create_engine(
278 "sqlite://",
279 connect_args={"check_same_thread": False},
280 poolclass=StaticPool,
281 )
283Note that using a ``:memory:`` database in multiple threads requires a recent
284version of SQLite.
286Using Temporary Tables with SQLite
287^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
289Due to the way SQLite deals with temporary tables, if you wish to use a
290temporary table in a file-based SQLite database across multiple checkouts
291from the connection pool, such as when using an ORM :class:`.Session` where
292the temporary table should continue to remain after :meth:`.Session.commit` or
293:meth:`.Session.rollback` is called, a pool which maintains a single
294connection must be used. Use :class:`.SingletonThreadPool` if the scope is
295only needed within the current thread, or :class:`.StaticPool` is scope is
296needed within multiple threads for this case::
298 # maintain the same connection per thread
299 from sqlalchemy.pool import SingletonThreadPool
301 engine = create_engine("sqlite:///mydb.db", poolclass=SingletonThreadPool)
304 # maintain the same connection across all threads
305 from sqlalchemy.pool import StaticPool
307 engine = create_engine("sqlite:///mydb.db", poolclass=StaticPool)
309Note that :class:`.SingletonThreadPool` should be configured for the number
310of threads that are to be used; beyond that number, connections will be
311closed out in a non deterministic way.
314Dealing with Mixed String / Binary Columns
315------------------------------------------------------
317The SQLite database is weakly typed, and as such it is possible when using
318binary values, which in Python are represented as ``b'some string'``, that a
319particular SQLite database can have data values within different rows where
320some of them will be returned as a ``b''`` value by the Pysqlite driver, and
321others will be returned as Python strings, e.g. ``''`` values. This situation
322is not known to occur if the SQLAlchemy :class:`.LargeBinary` datatype is used
323consistently, however if a particular SQLite database has data that was
324inserted using the Pysqlite driver directly, or when using the SQLAlchemy
325:class:`.String` type which was later changed to :class:`.LargeBinary`, the
326table will not be consistently readable because SQLAlchemy's
327:class:`.LargeBinary` datatype does not handle strings so it has no way of
328"encoding" a value that is in string format.
330To deal with a SQLite table that has mixed string / binary data in the
331same column, use a custom type that will check each row individually::
333 from sqlalchemy import String
334 from sqlalchemy import TypeDecorator
337 class MixedBinary(TypeDecorator):
338 impl = String
339 cache_ok = True
341 def process_result_value(self, value, dialect):
342 if isinstance(value, str):
343 value = bytes(value, "utf-8")
344 elif value is not None:
345 value = bytes(value)
347 return value
349Then use the above ``MixedBinary`` datatype in the place where
350:class:`.LargeBinary` would normally be used.
352.. _pysqlite_serializable:
354Serializable isolation / Savepoints / Transactional DDL
355-------------------------------------------------------
357A newly revised version of this important section is now available
358at the top level of the SQLAlchemy SQLite documentation, in the section
359:ref:`sqlite_transactions`.
362.. _pysqlite_udfs:
364User-Defined Functions
365----------------------
367pysqlite supports a `create_function() <https://docs.python.org/3/library/sqlite3.html#sqlite3.Connection.create_function>`_
368method that allows us to create our own user-defined functions (UDFs) in Python and use them directly in SQLite queries.
369These functions are registered with a specific DBAPI Connection.
371SQLAlchemy uses connection pooling with file-based SQLite databases, so we need to ensure that the UDF is attached to the
372connection when it is created. That is accomplished with an event listener::
374 from sqlalchemy import create_engine
375 from sqlalchemy import event
376 from sqlalchemy import text
379 def udf():
380 return "udf-ok"
383 engine = create_engine("sqlite:///./db_file")
386 @event.listens_for(engine, "connect")
387 def connect(conn, rec):
388 conn.create_function("udf", 0, udf)
391 for i in range(5):
392 with engine.connect() as conn:
393 print(conn.scalar(text("SELECT UDF()")))
395""" # noqa
396from __future__ import annotations
398import math
399import os
400import re
401from typing import cast
402from typing import Optional
403from typing import TYPE_CHECKING
404from typing import Union
406from .base import DATE
407from .base import DATETIME
408from .base import SQLiteDialect
409from ... import exc
410from ... import pool
411from ... import types as sqltypes
412from ... import util
414if TYPE_CHECKING:
415 from ...engine.interfaces import DBAPIConnection
416 from ...engine.interfaces import DBAPICursor
417 from ...engine.interfaces import DBAPIModule
418 from ...engine.url import URL
419 from ...pool.base import PoolProxiedConnection
422class _SQLite_pysqliteTimeStamp(DATETIME):
423 def bind_processor(self, dialect):
424 if dialect.native_datetime:
425 return None
426 else:
427 return DATETIME.bind_processor(self, dialect)
429 def result_processor(self, dialect, coltype):
430 if dialect.native_datetime:
431 return None
432 else:
433 return DATETIME.result_processor(self, dialect, coltype)
436class _SQLite_pysqliteDate(DATE):
437 def bind_processor(self, dialect):
438 if dialect.native_datetime:
439 return None
440 else:
441 return DATE.bind_processor(self, dialect)
443 def result_processor(self, dialect, coltype):
444 if dialect.native_datetime:
445 return None
446 else:
447 return DATE.result_processor(self, dialect, coltype)
450class SQLiteDialect_pysqlite(SQLiteDialect):
451 default_paramstyle = "qmark"
452 supports_statement_cache = True
453 returns_native_bytes = True
455 colspecs = util.update_copy(
456 SQLiteDialect.colspecs,
457 {
458 sqltypes.Date: _SQLite_pysqliteDate,
459 sqltypes.TIMESTAMP: _SQLite_pysqliteTimeStamp,
460 },
461 )
463 description_encoding = None
465 driver = "pysqlite"
467 @classmethod
468 def import_dbapi(cls):
469 from sqlite3 import dbapi2 as sqlite
471 return sqlite
473 @classmethod
474 def _is_url_file_db(cls, url: URL):
475 if (url.database and url.database != ":memory:") and (
476 url.query.get("mode", None) != "memory"
477 ):
478 return True
479 else:
480 return False
482 @classmethod
483 def get_pool_class(cls, url):
484 if cls._is_url_file_db(url):
485 return pool.QueuePool
486 else:
487 return pool.SingletonThreadPool
489 def _get_server_version_info(self, connection):
490 return self.dbapi.sqlite_version_info
492 _isolation_lookup = SQLiteDialect._isolation_lookup.union(
493 {
494 "AUTOCOMMIT": None,
495 }
496 )
498 def set_isolation_level(self, dbapi_connection, level):
499 if level == "AUTOCOMMIT":
500 dbapi_connection.isolation_level = None
501 else:
502 dbapi_connection.isolation_level = ""
503 return super().set_isolation_level(dbapi_connection, level)
505 def detect_autocommit_setting(self, dbapi_connection):
506 return dbapi_connection.isolation_level is None
508 def on_connect(self):
509 def regexp(a, b):
510 if b is None:
511 return None
512 return re.search(a, b) is not None
514 if util.py38 and self._get_server_version_info(None) >= (3, 9):
515 # sqlite must be greater than 3.8.3 for deterministic=True
516 # https://docs.python.org/3/library/sqlite3.html#sqlite3.Connection.create_function
517 # the check is more conservative since there were still issues
518 # with following 3.8 sqlite versions
519 create_func_kw = {"deterministic": True}
520 else:
521 create_func_kw = {}
523 def set_regexp(dbapi_connection):
524 dbapi_connection.create_function(
525 "regexp", 2, regexp, **create_func_kw
526 )
528 def floor_func(dbapi_connection):
529 # NOTE: floor is optionally present in sqlite 3.35+ , however
530 # as it is normally non-present we deliver floor() unconditionally
531 # for now.
532 # https://www.sqlite.org/lang_mathfunc.html
533 dbapi_connection.create_function(
534 "floor", 1, math.floor, **create_func_kw
535 )
537 fns = [set_regexp, floor_func]
539 def connect(conn):
540 for fn in fns:
541 fn(conn)
543 return connect
545 def create_connect_args(self, url):
546 if url.username or url.password or url.host or url.port:
547 raise exc.ArgumentError(
548 "Invalid SQLite URL: %s\n"
549 "Valid SQLite URL forms are:\n"
550 " sqlite:///:memory: (or, sqlite://)\n"
551 " sqlite:///relative/path/to/file.db\n"
552 " sqlite:////absolute/path/to/file.db" % (url,)
553 )
555 # theoretically, this list can be augmented, at least as far as
556 # parameter names accepted by sqlite3/pysqlite, using
557 # inspect.getfullargspec(). for the moment this seems like overkill
558 # as these parameters don't change very often, and as always,
559 # parameters passed to connect_args will always go to the
560 # sqlite3/pysqlite driver.
561 pysqlite_args = [
562 ("uri", bool),
563 ("timeout", float),
564 ("isolation_level", str),
565 ("detect_types", int),
566 ("check_same_thread", bool),
567 ("cached_statements", int),
568 ]
569 opts = url.query
570 pysqlite_opts = {}
571 for key, type_ in pysqlite_args:
572 util.coerce_kw_type(opts, key, type_, dest=pysqlite_opts)
574 if pysqlite_opts.get("uri", False):
575 uri_opts = dict(opts)
576 # here, we are actually separating the parameters that go to
577 # sqlite3/pysqlite vs. those that go the SQLite URI. What if
578 # two names conflict? again, this seems to be not the case right
579 # now, and in the case that new names are added to
580 # either side which overlap, again the sqlite3/pysqlite parameters
581 # can be passed through connect_args instead of in the URL.
582 # If SQLite native URIs add a parameter like "timeout" that
583 # we already have listed here for the python driver, then we need
584 # to adjust for that here.
585 for key, type_ in pysqlite_args:
586 uri_opts.pop(key, None)
587 filename = url.database
588 if uri_opts:
589 # sorting of keys is for unit test support
590 filename += "?" + (
591 "&".join(
592 "%s=%s" % (key, uri_opts[key])
593 for key in sorted(uri_opts)
594 )
595 )
596 else:
597 filename = url.database or ":memory:"
598 if filename != ":memory:":
599 filename = os.path.abspath(filename)
601 pysqlite_opts.setdefault(
602 "check_same_thread", not self._is_url_file_db(url)
603 )
605 return ([filename], pysqlite_opts)
607 def is_disconnect(
608 self,
609 e: DBAPIModule.Error,
610 connection: Optional[Union[PoolProxiedConnection, DBAPIConnection]],
611 cursor: Optional[DBAPICursor],
612 ) -> bool:
613 self.dbapi = cast("DBAPIModule", self.dbapi)
614 return isinstance(
615 e, self.dbapi.ProgrammingError
616 ) and "Cannot operate on a closed database." in str(e)
619dialect = SQLiteDialect_pysqlite
622class _SQLiteDialect_pysqlite_numeric(SQLiteDialect_pysqlite):
623 """numeric dialect for testing only
625 internal use only. This dialect is **NOT** supported by SQLAlchemy
626 and may change at any time.
628 """
630 supports_statement_cache = True
631 default_paramstyle = "numeric"
632 driver = "pysqlite_numeric"
634 _first_bind = ":1"
635 _not_in_statement_regexp = None
637 def __init__(self, *arg, **kw):
638 kw.setdefault("paramstyle", "numeric")
639 super().__init__(*arg, **kw)
641 def create_connect_args(self, url):
642 arg, opts = super().create_connect_args(url)
643 opts["factory"] = self._fix_sqlite_issue_99953()
644 return arg, opts
646 def _fix_sqlite_issue_99953(self):
647 import sqlite3
649 first_bind = self._first_bind
650 if self._not_in_statement_regexp:
651 nis = self._not_in_statement_regexp
653 def _test_sql(sql):
654 m = nis.search(sql)
655 assert not m, f"Found {nis.pattern!r} in {sql!r}"
657 else:
659 def _test_sql(sql):
660 pass
662 def _numeric_param_as_dict(parameters):
663 if parameters:
664 assert isinstance(parameters, tuple)
665 return {
666 str(idx): value for idx, value in enumerate(parameters, 1)
667 }
668 else:
669 return ()
671 class SQLiteFix99953Cursor(sqlite3.Cursor):
672 def execute(self, sql, parameters=()):
673 _test_sql(sql)
674 if first_bind in sql:
675 parameters = _numeric_param_as_dict(parameters)
676 return super().execute(sql, parameters)
678 def executemany(self, sql, parameters):
679 _test_sql(sql)
680 if first_bind in sql:
681 parameters = [
682 _numeric_param_as_dict(p) for p in parameters
683 ]
684 return super().executemany(sql, parameters)
686 class SQLiteFix99953Connection(sqlite3.Connection):
687 def cursor(self, factory=None):
688 if factory is None:
689 factory = SQLiteFix99953Cursor
690 return super().cursor(factory=factory)
692 def execute(self, sql, parameters=()):
693 _test_sql(sql)
694 if first_bind in sql:
695 parameters = _numeric_param_as_dict(parameters)
696 return super().execute(sql, parameters)
698 def executemany(self, sql, parameters):
699 _test_sql(sql)
700 if first_bind in sql:
701 parameters = [
702 _numeric_param_as_dict(p) for p in parameters
703 ]
704 return super().executemany(sql, parameters)
706 return SQLiteFix99953Connection
709class _SQLiteDialect_pysqlite_dollar(_SQLiteDialect_pysqlite_numeric):
710 """numeric dialect that uses $ for testing only
712 internal use only. This dialect is **NOT** supported by SQLAlchemy
713 and may change at any time.
715 """
717 supports_statement_cache = True
718 default_paramstyle = "numeric_dollar"
719 driver = "pysqlite_dollar"
721 _first_bind = "$1"
722 _not_in_statement_regexp = re.compile(r"[^\d]:\d+")
724 def __init__(self, *arg, **kw):
725 kw.setdefault("paramstyle", "numeric_dollar")
726 super().__init__(*arg, **kw)