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
394from __future__ import annotations
396import math
397import os
398import re
399from typing import cast
400from typing import Optional
401from typing import TYPE_CHECKING
402from typing import Union
404from .base import DATE
405from .base import DATETIME
406from .base import SQLiteDialect
407from ... import exc
408from ... import pool
409from ... import types as sqltypes
410from ... import util
412if TYPE_CHECKING:
413 from ...engine.interfaces import DBAPIConnection
414 from ...engine.interfaces import DBAPICursor
415 from ...engine.interfaces import DBAPIModule
416 from ...engine.url import URL
417 from ...pool.base import PoolProxiedConnection
420class _SQLite_pysqliteTimeStamp(DATETIME):
421 def bind_processor(self, dialect):
422 if dialect.native_datetime:
423 return None
424 else:
425 return DATETIME.bind_processor(self, dialect)
427 def result_processor(self, dialect, coltype):
428 if dialect.native_datetime:
429 return None
430 else:
431 return DATETIME.result_processor(self, dialect, coltype)
434class _SQLite_pysqliteDate(DATE):
435 def bind_processor(self, dialect):
436 if dialect.native_datetime:
437 return None
438 else:
439 return DATE.bind_processor(self, dialect)
441 def result_processor(self, dialect, coltype):
442 if dialect.native_datetime:
443 return None
444 else:
445 return DATE.result_processor(self, dialect, coltype)
448class SQLiteDialect_pysqlite(SQLiteDialect):
449 default_paramstyle = "qmark"
450 supports_statement_cache = True
451 returns_native_bytes = True
453 colspecs = util.update_copy(
454 SQLiteDialect.colspecs,
455 {
456 sqltypes.Date: _SQLite_pysqliteDate,
457 sqltypes.TIMESTAMP: _SQLite_pysqliteTimeStamp,
458 },
459 )
461 description_encoding = None
463 driver = "pysqlite"
465 @classmethod
466 def import_dbapi(cls):
467 from sqlite3 import dbapi2 as sqlite
469 return sqlite
471 @classmethod
472 def _is_url_file_db(cls, url: URL):
473 if (url.database and url.database != ":memory:") and (
474 url.query.get("mode", None) != "memory"
475 ):
476 return True
477 else:
478 return False
480 @classmethod
481 def get_pool_class(cls, url):
482 if cls._is_url_file_db(url):
483 return pool.QueuePool
484 else:
485 return pool.SingletonThreadPool
487 def _get_server_version_info(self, connection):
488 return self.dbapi.sqlite_version_info
490 _isolation_lookup = SQLiteDialect._isolation_lookup.union(
491 {
492 "AUTOCOMMIT": None,
493 }
494 )
496 def set_isolation_level(self, dbapi_connection, level):
497 if level == "AUTOCOMMIT":
498 dbapi_connection.isolation_level = None
499 else:
500 dbapi_connection.isolation_level = ""
501 return super().set_isolation_level(dbapi_connection, level)
503 def detect_autocommit_setting(self, dbapi_connection):
504 return dbapi_connection.isolation_level is None
506 def on_connect(self):
507 def regexp(a, b):
508 if b is None:
509 return None
510 return re.search(a, b) is not None
512 if self._get_server_version_info(None) >= (3, 9):
513 # sqlite must be greater than 3.8.3 for deterministic=True
514 # https://docs.python.org/3/library/sqlite3.html#sqlite3.Connection.create_function
515 # the check is more conservative since there were still issues
516 # with following 3.8 sqlite versions
517 create_func_kw = {"deterministic": True}
518 else:
519 create_func_kw = {}
521 def set_regexp(dbapi_connection):
522 dbapi_connection.create_function(
523 "regexp", 2, regexp, **create_func_kw
524 )
526 def floor_func(dbapi_connection):
527 # NOTE: floor is optionally present in sqlite 3.35+ , however
528 # as it is normally non-present we deliver floor() unconditionally
529 # for now.
530 # https://www.sqlite.org/lang_mathfunc.html
531 dbapi_connection.create_function(
532 "floor", 1, math.floor, **create_func_kw
533 )
535 fns = [set_regexp, floor_func]
537 def connect(conn):
538 for fn in fns:
539 fn(conn)
541 return connect
543 def create_connect_args(self, url):
544 if url.username or url.password or url.host or url.port:
545 raise exc.ArgumentError(
546 "Invalid SQLite URL: %s\n"
547 "Valid SQLite URL forms are:\n"
548 " sqlite:///:memory: (or, sqlite://)\n"
549 " sqlite:///relative/path/to/file.db\n"
550 " sqlite:////absolute/path/to/file.db" % (url,)
551 )
553 # theoretically, this list can be augmented, at least as far as
554 # parameter names accepted by sqlite3/pysqlite, using
555 # inspect.getfullargspec(). for the moment this seems like overkill
556 # as these parameters don't change very often, and as always,
557 # parameters passed to connect_args will always go to the
558 # sqlite3/pysqlite driver.
559 pysqlite_args = [
560 ("uri", bool),
561 ("timeout", float),
562 ("isolation_level", str),
563 ("detect_types", int),
564 ("check_same_thread", bool),
565 ("cached_statements", int),
566 ]
567 opts = url.query
568 pysqlite_opts = {}
569 for key, type_ in pysqlite_args:
570 util.coerce_kw_type(opts, key, type_, dest=pysqlite_opts)
572 if pysqlite_opts.get("uri", False):
573 uri_opts = dict(opts)
574 # here, we are actually separating the parameters that go to
575 # sqlite3/pysqlite vs. those that go the SQLite URI. What if
576 # two names conflict? again, this seems to be not the case right
577 # now, and in the case that new names are added to
578 # either side which overlap, again the sqlite3/pysqlite parameters
579 # can be passed through connect_args instead of in the URL.
580 # If SQLite native URIs add a parameter like "timeout" that
581 # we already have listed here for the python driver, then we need
582 # to adjust for that here.
583 for key, type_ in pysqlite_args:
584 uri_opts.pop(key, None)
585 filename = url.database
586 if uri_opts:
587 # sorting of keys is for unit test support
588 filename += "?" + (
589 "&".join(
590 "%s=%s" % (key, uri_opts[key])
591 for key in sorted(uri_opts)
592 )
593 )
594 else:
595 filename = url.database or ":memory:"
596 if filename != ":memory:":
597 filename = os.path.abspath(filename)
599 pysqlite_opts.setdefault(
600 "check_same_thread", not self._is_url_file_db(url)
601 )
603 return ([filename], pysqlite_opts)
605 def is_disconnect(
606 self,
607 e: DBAPIModule.Error,
608 connection: Optional[Union[PoolProxiedConnection, DBAPIConnection]],
609 cursor: Optional[DBAPICursor],
610 ) -> bool:
611 self.dbapi = cast("DBAPIModule", self.dbapi)
612 return isinstance(
613 e, self.dbapi.ProgrammingError
614 ) and "Cannot operate on a closed database." in str(e)
617dialect = SQLiteDialect_pysqlite
620class _SQLiteDialect_pysqlite_numeric(SQLiteDialect_pysqlite):
621 """numeric dialect for testing only
623 internal use only. This dialect is **NOT** supported by SQLAlchemy
624 and may change at any time.
626 """
628 supports_statement_cache = True
629 default_paramstyle = "numeric"
630 driver = "pysqlite_numeric"
632 _first_bind = ":1"
633 _not_in_statement_regexp = None
635 def __init__(self, *arg, **kw):
636 kw.setdefault("paramstyle", "numeric")
637 super().__init__(*arg, **kw)
639 def create_connect_args(self, url):
640 arg, opts = super().create_connect_args(url)
641 opts["factory"] = self._fix_sqlite_issue_99953()
642 return arg, opts
644 def _fix_sqlite_issue_99953(self):
645 import sqlite3
647 first_bind = self._first_bind
648 if self._not_in_statement_regexp:
649 nis = self._not_in_statement_regexp
651 def _test_sql(sql):
652 m = nis.search(sql)
653 assert not m, f"Found {nis.pattern!r} in {sql!r}"
655 else:
657 def _test_sql(sql):
658 pass
660 def _numeric_param_as_dict(parameters):
661 if parameters:
662 assert isinstance(parameters, tuple)
663 return {
664 str(idx): value for idx, value in enumerate(parameters, 1)
665 }
666 else:
667 return ()
669 class SQLiteFix99953Cursor(sqlite3.Cursor):
670 def execute(self, sql, parameters=()):
671 _test_sql(sql)
672 if first_bind in sql:
673 parameters = _numeric_param_as_dict(parameters)
674 return super().execute(sql, parameters)
676 def executemany(self, sql, parameters):
677 _test_sql(sql)
678 if first_bind in sql:
679 parameters = [
680 _numeric_param_as_dict(p) for p in parameters
681 ]
682 return super().executemany(sql, parameters)
684 class SQLiteFix99953Connection(sqlite3.Connection):
685 def cursor(self, factory=None):
686 if factory is None:
687 factory = SQLiteFix99953Cursor
688 return super().cursor(factory=factory)
690 def execute(self, sql, parameters=()):
691 _test_sql(sql)
692 if first_bind in sql:
693 parameters = _numeric_param_as_dict(parameters)
694 return super().execute(sql, parameters)
696 def executemany(self, sql, parameters):
697 _test_sql(sql)
698 if first_bind in sql:
699 parameters = [
700 _numeric_param_as_dict(p) for p in parameters
701 ]
702 return super().executemany(sql, parameters)
704 return SQLiteFix99953Connection
707class _SQLiteDialect_pysqlite_dollar(_SQLiteDialect_pysqlite_numeric):
708 """numeric dialect that uses $ for testing only
710 internal use only. This dialect is **NOT** supported by SQLAlchemy
711 and may change at any time.
713 """
715 supports_statement_cache = True
716 default_paramstyle = "numeric_dollar"
717 driver = "pysqlite_dollar"
719 _first_bind = "$1"
720 _not_in_statement_regexp = re.compile(r"[^\d]:\d+")
722 def __init__(self, *arg, **kw):
723 kw.setdefault("paramstyle", "numeric_dollar")
724 super().__init__(*arg, **kw)