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