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/base.py
2# Copyright (C) 2005-2024 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
12 :name: SQLite
13 :full_support: 3.36.0
14 :normal_support: 3.12+
15 :best_effort: 3.7.16+
17.. _sqlite_datetime:
19Date and Time Types
20-------------------
22SQLite does not have built-in DATE, TIME, or DATETIME types, and pysqlite does
23not provide out of the box functionality for translating values between Python
24`datetime` objects and a SQLite-supported format. SQLAlchemy's own
25:class:`~sqlalchemy.types.DateTime` and related types provide date formatting
26and parsing functionality when SQLite is used. The implementation classes are
27:class:`_sqlite.DATETIME`, :class:`_sqlite.DATE` and :class:`_sqlite.TIME`.
28These types represent dates and times as ISO formatted strings, which also
29nicely support ordering. There's no reliance on typical "libc" internals for
30these functions so historical dates are fully supported.
32Ensuring Text affinity
33^^^^^^^^^^^^^^^^^^^^^^
35The DDL rendered for these types is the standard ``DATE``, ``TIME``
36and ``DATETIME`` indicators. However, custom storage formats can also be
37applied to these types. When the
38storage format is detected as containing no alpha characters, the DDL for
39these types is rendered as ``DATE_CHAR``, ``TIME_CHAR``, and ``DATETIME_CHAR``,
40so that the column continues to have textual affinity.
42.. seealso::
44 `Type Affinity <https://www.sqlite.org/datatype3.html#affinity>`_ -
45 in the SQLite documentation
47.. _sqlite_autoincrement:
49SQLite Auto Incrementing Behavior
50----------------------------------
52Background on SQLite's autoincrement is at: https://sqlite.org/autoinc.html
54Key concepts:
56* SQLite has an implicit "auto increment" feature that takes place for any
57 non-composite primary-key column that is specifically created using
58 "INTEGER PRIMARY KEY" for the type + primary key.
60* SQLite also has an explicit "AUTOINCREMENT" keyword, that is **not**
61 equivalent to the implicit autoincrement feature; this keyword is not
62 recommended for general use. SQLAlchemy does not render this keyword
63 unless a special SQLite-specific directive is used (see below). However,
64 it still requires that the column's type is named "INTEGER".
66Using the AUTOINCREMENT Keyword
67^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
69To specifically render the AUTOINCREMENT keyword on the primary key column
70when rendering DDL, add the flag ``sqlite_autoincrement=True`` to the Table
71construct::
73 Table('sometable', metadata,
74 Column('id', Integer, primary_key=True),
75 sqlite_autoincrement=True)
77Allowing autoincrement behavior SQLAlchemy types other than Integer/INTEGER
78^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
80SQLite's typing model is based on naming conventions. Among other things, this
81means that any type name which contains the substring ``"INT"`` will be
82determined to be of "integer affinity". A type named ``"BIGINT"``,
83``"SPECIAL_INT"`` or even ``"XYZINTQPR"``, will be considered by SQLite to be
84of "integer" affinity. However, **the SQLite autoincrement feature, whether
85implicitly or explicitly enabled, requires that the name of the column's type
86is exactly the string "INTEGER"**. Therefore, if an application uses a type
87like :class:`.BigInteger` for a primary key, on SQLite this type will need to
88be rendered as the name ``"INTEGER"`` when emitting the initial ``CREATE
89TABLE`` statement in order for the autoincrement behavior to be available.
91One approach to achieve this is to use :class:`.Integer` on SQLite
92only using :meth:`.TypeEngine.with_variant`::
94 table = Table(
95 "my_table", metadata,
96 Column("id", BigInteger().with_variant(Integer, "sqlite"), primary_key=True)
97 )
99Another is to use a subclass of :class:`.BigInteger` that overrides its DDL
100name to be ``INTEGER`` when compiled against SQLite::
102 from sqlalchemy import BigInteger
103 from sqlalchemy.ext.compiler import compiles
105 class SLBigInteger(BigInteger):
106 pass
108 @compiles(SLBigInteger, 'sqlite')
109 def bi_c(element, compiler, **kw):
110 return "INTEGER"
112 @compiles(SLBigInteger)
113 def bi_c(element, compiler, **kw):
114 return compiler.visit_BIGINT(element, **kw)
117 table = Table(
118 "my_table", metadata,
119 Column("id", SLBigInteger(), primary_key=True)
120 )
122.. seealso::
124 :meth:`.TypeEngine.with_variant`
126 :ref:`sqlalchemy.ext.compiler_toplevel`
128 `Datatypes In SQLite Version 3 <https://sqlite.org/datatype3.html>`_
130.. _sqlite_concurrency:
132Database Locking Behavior / Concurrency
133---------------------------------------
135SQLite is not designed for a high level of write concurrency. The database
136itself, being a file, is locked completely during write operations within
137transactions, meaning exactly one "connection" (in reality a file handle)
138has exclusive access to the database during this period - all other
139"connections" will be blocked during this time.
141The Python DBAPI specification also calls for a connection model that is
142always in a transaction; there is no ``connection.begin()`` method,
143only ``connection.commit()`` and ``connection.rollback()``, upon which a
144new transaction is to be begun immediately. This may seem to imply
145that the SQLite driver would in theory allow only a single filehandle on a
146particular database file at any time; however, there are several
147factors both within SQLite itself as well as within the pysqlite driver
148which loosen this restriction significantly.
150However, no matter what locking modes are used, SQLite will still always
151lock the database file once a transaction is started and DML (e.g. INSERT,
152UPDATE, DELETE) has at least been emitted, and this will block
153other transactions at least at the point that they also attempt to emit DML.
154By default, the length of time on this block is very short before it times out
155with an error.
157This behavior becomes more critical when used in conjunction with the
158SQLAlchemy ORM. SQLAlchemy's :class:`.Session` object by default runs
159within a transaction, and with its autoflush model, may emit DML preceding
160any SELECT statement. This may lead to a SQLite database that locks
161more quickly than is expected. The locking mode of SQLite and the pysqlite
162driver can be manipulated to some degree, however it should be noted that
163achieving a high degree of write-concurrency with SQLite is a losing battle.
165For more information on SQLite's lack of write concurrency by design, please
166see
167`Situations Where Another RDBMS May Work Better - High Concurrency
168<https://www.sqlite.org/whentouse.html>`_ near the bottom of the page.
170The following subsections introduce areas that are impacted by SQLite's
171file-based architecture and additionally will usually require workarounds to
172work when using the pysqlite driver.
174.. _sqlite_isolation_level:
176Transaction Isolation Level / Autocommit
177----------------------------------------
179SQLite supports "transaction isolation" in a non-standard way, along two
180axes. One is that of the
181`PRAGMA read_uncommitted <https://www.sqlite.org/pragma.html#pragma_read_uncommitted>`_
182instruction. This setting can essentially switch SQLite between its
183default mode of ``SERIALIZABLE`` isolation, and a "dirty read" isolation
184mode normally referred to as ``READ UNCOMMITTED``.
186SQLAlchemy ties into this PRAGMA statement using the
187:paramref:`_sa.create_engine.isolation_level` parameter of
188:func:`_sa.create_engine`.
189Valid values for this parameter when used with SQLite are ``"SERIALIZABLE"``
190and ``"READ UNCOMMITTED"`` corresponding to a value of 0 and 1, respectively.
191SQLite defaults to ``SERIALIZABLE``, however its behavior is impacted by
192the pysqlite driver's default behavior.
194When using the pysqlite driver, the ``"AUTOCOMMIT"`` isolation level is also
195available, which will alter the pysqlite connection using the ``.isolation_level``
196attribute on the DBAPI connection and set it to None for the duration
197of the setting.
199.. versionadded:: 1.3.16 added support for SQLite AUTOCOMMIT isolation level
200 when using the pysqlite / sqlite3 SQLite driver.
203The other axis along which SQLite's transactional locking is impacted is
204via the nature of the ``BEGIN`` statement used. The three varieties
205are "deferred", "immediate", and "exclusive", as described at
206`BEGIN TRANSACTION <https://sqlite.org/lang_transaction.html>`_. A straight
207``BEGIN`` statement uses the "deferred" mode, where the database file is
208not locked until the first read or write operation, and read access remains
209open to other transactions until the first write operation. But again,
210it is critical to note that the pysqlite driver interferes with this behavior
211by *not even emitting BEGIN* until the first write operation.
213.. warning::
215 SQLite's transactional scope is impacted by unresolved
216 issues in the pysqlite driver, which defers BEGIN statements to a greater
217 degree than is often feasible. See the section :ref:`pysqlite_serializable`
218 or :ref:`aiosqlite_serializable` for techniques to work around this behavior.
220.. seealso::
222 :ref:`dbapi_autocommit`
224INSERT/UPDATE/DELETE...RETURNING
225---------------------------------
227The SQLite dialect supports SQLite 3.35's ``INSERT|UPDATE|DELETE..RETURNING``
228syntax. ``INSERT..RETURNING`` may be used
229automatically in some cases in order to fetch newly generated identifiers in
230place of the traditional approach of using ``cursor.lastrowid``, however
231``cursor.lastrowid`` is currently still preferred for simple single-statement
232cases for its better performance.
234To specify an explicit ``RETURNING`` clause, use the
235:meth:`._UpdateBase.returning` method on a per-statement basis::
237 # INSERT..RETURNING
238 result = connection.execute(
239 table.insert().
240 values(name='foo').
241 returning(table.c.col1, table.c.col2)
242 )
243 print(result.all())
245 # UPDATE..RETURNING
246 result = connection.execute(
247 table.update().
248 where(table.c.name=='foo').
249 values(name='bar').
250 returning(table.c.col1, table.c.col2)
251 )
252 print(result.all())
254 # DELETE..RETURNING
255 result = connection.execute(
256 table.delete().
257 where(table.c.name=='foo').
258 returning(table.c.col1, table.c.col2)
259 )
260 print(result.all())
262.. versionadded:: 2.0 Added support for SQLite RETURNING
264SAVEPOINT Support
265----------------------------
267SQLite supports SAVEPOINTs, which only function once a transaction is
268begun. SQLAlchemy's SAVEPOINT support is available using the
269:meth:`_engine.Connection.begin_nested` method at the Core level, and
270:meth:`.Session.begin_nested` at the ORM level. However, SAVEPOINTs
271won't work at all with pysqlite unless workarounds are taken.
273.. warning::
275 SQLite's SAVEPOINT feature is impacted by unresolved
276 issues in the pysqlite and aiosqlite drivers, which defer BEGIN statements
277 to a greater degree than is often feasible. See the sections
278 :ref:`pysqlite_serializable` and :ref:`aiosqlite_serializable`
279 for techniques to work around this behavior.
281Transactional DDL
282----------------------------
284The SQLite database supports transactional :term:`DDL` as well.
285In this case, the pysqlite driver is not only failing to start transactions,
286it also is ending any existing transaction when DDL is detected, so again,
287workarounds are required.
289.. warning::
291 SQLite's transactional DDL is impacted by unresolved issues
292 in the pysqlite driver, which fails to emit BEGIN and additionally
293 forces a COMMIT to cancel any transaction when DDL is encountered.
294 See the section :ref:`pysqlite_serializable`
295 for techniques to work around this behavior.
297.. _sqlite_foreign_keys:
299Foreign Key Support
300-------------------
302SQLite supports FOREIGN KEY syntax when emitting CREATE statements for tables,
303however by default these constraints have no effect on the operation of the
304table.
306Constraint checking on SQLite has three prerequisites:
308* At least version 3.6.19 of SQLite must be in use
309* The SQLite library must be compiled *without* the SQLITE_OMIT_FOREIGN_KEY
310 or SQLITE_OMIT_TRIGGER symbols enabled.
311* The ``PRAGMA foreign_keys = ON`` statement must be emitted on all
312 connections before use -- including the initial call to
313 :meth:`sqlalchemy.schema.MetaData.create_all`.
315SQLAlchemy allows for the ``PRAGMA`` statement to be emitted automatically for
316new connections through the usage of events::
318 from sqlalchemy.engine import Engine
319 from sqlalchemy import event
321 @event.listens_for(Engine, "connect")
322 def set_sqlite_pragma(dbapi_connection, connection_record):
323 cursor = dbapi_connection.cursor()
324 cursor.execute("PRAGMA foreign_keys=ON")
325 cursor.close()
327.. warning::
329 When SQLite foreign keys are enabled, it is **not possible**
330 to emit CREATE or DROP statements for tables that contain
331 mutually-dependent foreign key constraints;
332 to emit the DDL for these tables requires that ALTER TABLE be used to
333 create or drop these constraints separately, for which SQLite has
334 no support.
336.. seealso::
338 `SQLite Foreign Key Support <https://www.sqlite.org/foreignkeys.html>`_
339 - on the SQLite web site.
341 :ref:`event_toplevel` - SQLAlchemy event API.
343 :ref:`use_alter` - more information on SQLAlchemy's facilities for handling
344 mutually-dependent foreign key constraints.
346.. _sqlite_on_conflict_ddl:
348ON CONFLICT support for constraints
349-----------------------------------
351.. seealso:: This section describes the :term:`DDL` version of "ON CONFLICT" for
352 SQLite, which occurs within a CREATE TABLE statement. For "ON CONFLICT" as
353 applied to an INSERT statement, see :ref:`sqlite_on_conflict_insert`.
355SQLite supports a non-standard DDL clause known as ON CONFLICT which can be applied
356to primary key, unique, check, and not null constraints. In DDL, it is
357rendered either within the "CONSTRAINT" clause or within the column definition
358itself depending on the location of the target constraint. To render this
359clause within DDL, the extension parameter ``sqlite_on_conflict`` can be
360specified with a string conflict resolution algorithm within the
361:class:`.PrimaryKeyConstraint`, :class:`.UniqueConstraint`,
362:class:`.CheckConstraint` objects. Within the :class:`_schema.Column` object,
363there
364are individual parameters ``sqlite_on_conflict_not_null``,
365``sqlite_on_conflict_primary_key``, ``sqlite_on_conflict_unique`` which each
366correspond to the three types of relevant constraint types that can be
367indicated from a :class:`_schema.Column` object.
369.. seealso::
371 `ON CONFLICT <https://www.sqlite.org/lang_conflict.html>`_ - in the SQLite
372 documentation
374.. versionadded:: 1.3
377The ``sqlite_on_conflict`` parameters accept a string argument which is just
378the resolution name to be chosen, which on SQLite can be one of ROLLBACK,
379ABORT, FAIL, IGNORE, and REPLACE. For example, to add a UNIQUE constraint
380that specifies the IGNORE algorithm::
382 some_table = Table(
383 'some_table', metadata,
384 Column('id', Integer, primary_key=True),
385 Column('data', Integer),
386 UniqueConstraint('id', 'data', sqlite_on_conflict='IGNORE')
387 )
389The above renders CREATE TABLE DDL as::
391 CREATE TABLE some_table (
392 id INTEGER NOT NULL,
393 data INTEGER,
394 PRIMARY KEY (id),
395 UNIQUE (id, data) ON CONFLICT IGNORE
396 )
399When using the :paramref:`_schema.Column.unique`
400flag to add a UNIQUE constraint
401to a single column, the ``sqlite_on_conflict_unique`` parameter can
402be added to the :class:`_schema.Column` as well, which will be added to the
403UNIQUE constraint in the DDL::
405 some_table = Table(
406 'some_table', metadata,
407 Column('id', Integer, primary_key=True),
408 Column('data', Integer, unique=True,
409 sqlite_on_conflict_unique='IGNORE')
410 )
412rendering::
414 CREATE TABLE some_table (
415 id INTEGER NOT NULL,
416 data INTEGER,
417 PRIMARY KEY (id),
418 UNIQUE (data) ON CONFLICT IGNORE
419 )
421To apply the FAIL algorithm for a NOT NULL constraint,
422``sqlite_on_conflict_not_null`` is used::
424 some_table = Table(
425 'some_table', metadata,
426 Column('id', Integer, primary_key=True),
427 Column('data', Integer, nullable=False,
428 sqlite_on_conflict_not_null='FAIL')
429 )
431this renders the column inline ON CONFLICT phrase::
433 CREATE TABLE some_table (
434 id INTEGER NOT NULL,
435 data INTEGER NOT NULL ON CONFLICT FAIL,
436 PRIMARY KEY (id)
437 )
440Similarly, for an inline primary key, use ``sqlite_on_conflict_primary_key``::
442 some_table = Table(
443 'some_table', metadata,
444 Column('id', Integer, primary_key=True,
445 sqlite_on_conflict_primary_key='FAIL')
446 )
448SQLAlchemy renders the PRIMARY KEY constraint separately, so the conflict
449resolution algorithm is applied to the constraint itself::
451 CREATE TABLE some_table (
452 id INTEGER NOT NULL,
453 PRIMARY KEY (id) ON CONFLICT FAIL
454 )
456.. _sqlite_on_conflict_insert:
458INSERT...ON CONFLICT (Upsert)
459-----------------------------------
461.. seealso:: This section describes the :term:`DML` version of "ON CONFLICT" for
462 SQLite, which occurs within an INSERT statement. For "ON CONFLICT" as
463 applied to a CREATE TABLE statement, see :ref:`sqlite_on_conflict_ddl`.
465From version 3.24.0 onwards, SQLite supports "upserts" (update or insert)
466of rows into a table via the ``ON CONFLICT`` clause of the ``INSERT``
467statement. A candidate row will only be inserted if that row does not violate
468any unique or primary key constraints. In the case of a unique constraint violation, a
469secondary action can occur which can be either "DO UPDATE", indicating that
470the data in the target row should be updated, or "DO NOTHING", which indicates
471to silently skip this row.
473Conflicts are determined using columns that are part of existing unique
474constraints and indexes. These constraints are identified by stating the
475columns and conditions that comprise the indexes.
477SQLAlchemy provides ``ON CONFLICT`` support via the SQLite-specific
478:func:`_sqlite.insert()` function, which provides
479the generative methods :meth:`_sqlite.Insert.on_conflict_do_update`
480and :meth:`_sqlite.Insert.on_conflict_do_nothing`:
482.. sourcecode:: pycon+sql
484 >>> from sqlalchemy.dialects.sqlite import insert
486 >>> insert_stmt = insert(my_table).values(
487 ... id='some_existing_id',
488 ... data='inserted value')
490 >>> do_update_stmt = insert_stmt.on_conflict_do_update(
491 ... index_elements=['id'],
492 ... set_=dict(data='updated value')
493 ... )
495 >>> print(do_update_stmt)
496 {printsql}INSERT INTO my_table (id, data) VALUES (?, ?)
497 ON CONFLICT (id) DO UPDATE SET data = ?{stop}
499 >>> do_nothing_stmt = insert_stmt.on_conflict_do_nothing(
500 ... index_elements=['id']
501 ... )
503 >>> print(do_nothing_stmt)
504 {printsql}INSERT INTO my_table (id, data) VALUES (?, ?)
505 ON CONFLICT (id) DO NOTHING
507.. versionadded:: 1.4
509.. seealso::
511 `Upsert
512 <https://sqlite.org/lang_UPSERT.html>`_
513 - in the SQLite documentation.
516Specifying the Target
517^^^^^^^^^^^^^^^^^^^^^
519Both methods supply the "target" of the conflict using column inference:
521* The :paramref:`_sqlite.Insert.on_conflict_do_update.index_elements` argument
522 specifies a sequence containing string column names, :class:`_schema.Column`
523 objects, and/or SQL expression elements, which would identify a unique index
524 or unique constraint.
526* When using :paramref:`_sqlite.Insert.on_conflict_do_update.index_elements`
527 to infer an index, a partial index can be inferred by also specifying the
528 :paramref:`_sqlite.Insert.on_conflict_do_update.index_where` parameter:
530 .. sourcecode:: pycon+sql
532 >>> stmt = insert(my_table).values(user_email='a@b.com', data='inserted data')
534 >>> do_update_stmt = stmt.on_conflict_do_update(
535 ... index_elements=[my_table.c.user_email],
536 ... index_where=my_table.c.user_email.like('%@gmail.com'),
537 ... set_=dict(data=stmt.excluded.data)
538 ... )
540 >>> print(do_update_stmt)
541 {printsql}INSERT INTO my_table (data, user_email) VALUES (?, ?)
542 ON CONFLICT (user_email)
543 WHERE user_email LIKE '%@gmail.com'
544 DO UPDATE SET data = excluded.data
546The SET Clause
547^^^^^^^^^^^^^^^
549``ON CONFLICT...DO UPDATE`` is used to perform an update of the already
550existing row, using any combination of new values as well as values
551from the proposed insertion. These values are specified using the
552:paramref:`_sqlite.Insert.on_conflict_do_update.set_` parameter. This
553parameter accepts a dictionary which consists of direct values
554for UPDATE:
556.. sourcecode:: pycon+sql
558 >>> stmt = insert(my_table).values(id='some_id', data='inserted value')
560 >>> do_update_stmt = stmt.on_conflict_do_update(
561 ... index_elements=['id'],
562 ... set_=dict(data='updated value')
563 ... )
565 >>> print(do_update_stmt)
566 {printsql}INSERT INTO my_table (id, data) VALUES (?, ?)
567 ON CONFLICT (id) DO UPDATE SET data = ?
569.. warning::
571 The :meth:`_sqlite.Insert.on_conflict_do_update` method does **not** take
572 into account Python-side default UPDATE values or generation functions,
573 e.g. those specified using :paramref:`_schema.Column.onupdate`. These
574 values will not be exercised for an ON CONFLICT style of UPDATE, unless
575 they are manually specified in the
576 :paramref:`_sqlite.Insert.on_conflict_do_update.set_` dictionary.
578Updating using the Excluded INSERT Values
579^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
581In order to refer to the proposed insertion row, the special alias
582:attr:`~.sqlite.Insert.excluded` is available as an attribute on
583the :class:`_sqlite.Insert` object; this object creates an "excluded." prefix
584on a column, that informs the DO UPDATE to update the row with the value that
585would have been inserted had the constraint not failed:
587.. sourcecode:: pycon+sql
589 >>> stmt = insert(my_table).values(
590 ... id='some_id',
591 ... data='inserted value',
592 ... author='jlh'
593 ... )
595 >>> do_update_stmt = stmt.on_conflict_do_update(
596 ... index_elements=['id'],
597 ... set_=dict(data='updated value', author=stmt.excluded.author)
598 ... )
600 >>> print(do_update_stmt)
601 {printsql}INSERT INTO my_table (id, data, author) VALUES (?, ?, ?)
602 ON CONFLICT (id) DO UPDATE SET data = ?, author = excluded.author
604Additional WHERE Criteria
605^^^^^^^^^^^^^^^^^^^^^^^^^
607The :meth:`_sqlite.Insert.on_conflict_do_update` method also accepts
608a WHERE clause using the :paramref:`_sqlite.Insert.on_conflict_do_update.where`
609parameter, which will limit those rows which receive an UPDATE:
611.. sourcecode:: pycon+sql
613 >>> stmt = insert(my_table).values(
614 ... id='some_id',
615 ... data='inserted value',
616 ... author='jlh'
617 ... )
619 >>> on_update_stmt = stmt.on_conflict_do_update(
620 ... index_elements=['id'],
621 ... set_=dict(data='updated value', author=stmt.excluded.author),
622 ... where=(my_table.c.status == 2)
623 ... )
624 >>> print(on_update_stmt)
625 {printsql}INSERT INTO my_table (id, data, author) VALUES (?, ?, ?)
626 ON CONFLICT (id) DO UPDATE SET data = ?, author = excluded.author
627 WHERE my_table.status = ?
630Skipping Rows with DO NOTHING
631^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
633``ON CONFLICT`` may be used to skip inserting a row entirely
634if any conflict with a unique constraint occurs; below this is illustrated
635using the :meth:`_sqlite.Insert.on_conflict_do_nothing` method:
637.. sourcecode:: pycon+sql
639 >>> stmt = insert(my_table).values(id='some_id', data='inserted value')
640 >>> stmt = stmt.on_conflict_do_nothing(index_elements=['id'])
641 >>> print(stmt)
642 {printsql}INSERT INTO my_table (id, data) VALUES (?, ?) ON CONFLICT (id) DO NOTHING
645If ``DO NOTHING`` is used without specifying any columns or constraint,
646it has the effect of skipping the INSERT for any unique violation which
647occurs:
649.. sourcecode:: pycon+sql
651 >>> stmt = insert(my_table).values(id='some_id', data='inserted value')
652 >>> stmt = stmt.on_conflict_do_nothing()
653 >>> print(stmt)
654 {printsql}INSERT INTO my_table (id, data) VALUES (?, ?) ON CONFLICT DO NOTHING
656.. _sqlite_type_reflection:
658Type Reflection
659---------------
661SQLite types are unlike those of most other database backends, in that
662the string name of the type usually does not correspond to a "type" in a
663one-to-one fashion. Instead, SQLite links per-column typing behavior
664to one of five so-called "type affinities" based on a string matching
665pattern for the type.
667SQLAlchemy's reflection process, when inspecting types, uses a simple
668lookup table to link the keywords returned to provided SQLAlchemy types.
669This lookup table is present within the SQLite dialect as it is for all
670other dialects. However, the SQLite dialect has a different "fallback"
671routine for when a particular type name is not located in the lookup map;
672it instead implements the SQLite "type affinity" scheme located at
673https://www.sqlite.org/datatype3.html section 2.1.
675The provided typemap will make direct associations from an exact string
676name match for the following types:
678:class:`_types.BIGINT`, :class:`_types.BLOB`,
679:class:`_types.BOOLEAN`, :class:`_types.BOOLEAN`,
680:class:`_types.CHAR`, :class:`_types.DATE`,
681:class:`_types.DATETIME`, :class:`_types.FLOAT`,
682:class:`_types.DECIMAL`, :class:`_types.FLOAT`,
683:class:`_types.INTEGER`, :class:`_types.INTEGER`,
684:class:`_types.NUMERIC`, :class:`_types.REAL`,
685:class:`_types.SMALLINT`, :class:`_types.TEXT`,
686:class:`_types.TIME`, :class:`_types.TIMESTAMP`,
687:class:`_types.VARCHAR`, :class:`_types.NVARCHAR`,
688:class:`_types.NCHAR`
690When a type name does not match one of the above types, the "type affinity"
691lookup is used instead:
693* :class:`_types.INTEGER` is returned if the type name includes the
694 string ``INT``
695* :class:`_types.TEXT` is returned if the type name includes the
696 string ``CHAR``, ``CLOB`` or ``TEXT``
697* :class:`_types.NullType` is returned if the type name includes the
698 string ``BLOB``
699* :class:`_types.REAL` is returned if the type name includes the string
700 ``REAL``, ``FLOA`` or ``DOUB``.
701* Otherwise, the :class:`_types.NUMERIC` type is used.
703.. _sqlite_partial_index:
705Partial Indexes
706---------------
708A partial index, e.g. one which uses a WHERE clause, can be specified
709with the DDL system using the argument ``sqlite_where``::
711 tbl = Table('testtbl', m, Column('data', Integer))
712 idx = Index('test_idx1', tbl.c.data,
713 sqlite_where=and_(tbl.c.data > 5, tbl.c.data < 10))
715The index will be rendered at create time as::
717 CREATE INDEX test_idx1 ON testtbl (data)
718 WHERE data > 5 AND data < 10
720.. _sqlite_dotted_column_names:
722Dotted Column Names
723-------------------
725Using table or column names that explicitly have periods in them is
726**not recommended**. While this is generally a bad idea for relational
727databases in general, as the dot is a syntactically significant character,
728the SQLite driver up until version **3.10.0** of SQLite has a bug which
729requires that SQLAlchemy filter out these dots in result sets.
731The bug, entirely outside of SQLAlchemy, can be illustrated thusly::
733 import sqlite3
735 assert sqlite3.sqlite_version_info < (3, 10, 0), "bug is fixed in this version"
737 conn = sqlite3.connect(":memory:")
738 cursor = conn.cursor()
740 cursor.execute("create table x (a integer, b integer)")
741 cursor.execute("insert into x (a, b) values (1, 1)")
742 cursor.execute("insert into x (a, b) values (2, 2)")
744 cursor.execute("select x.a, x.b from x")
745 assert [c[0] for c in cursor.description] == ['a', 'b']
747 cursor.execute('''
748 select x.a, x.b from x where a=1
749 union
750 select x.a, x.b from x where a=2
751 ''')
752 assert [c[0] for c in cursor.description] == ['a', 'b'], \
753 [c[0] for c in cursor.description]
755The second assertion fails::
757 Traceback (most recent call last):
758 File "test.py", line 19, in <module>
759 [c[0] for c in cursor.description]
760 AssertionError: ['x.a', 'x.b']
762Where above, the driver incorrectly reports the names of the columns
763including the name of the table, which is entirely inconsistent vs.
764when the UNION is not present.
766SQLAlchemy relies upon column names being predictable in how they match
767to the original statement, so the SQLAlchemy dialect has no choice but
768to filter these out::
771 from sqlalchemy import create_engine
773 eng = create_engine("sqlite://")
774 conn = eng.connect()
776 conn.exec_driver_sql("create table x (a integer, b integer)")
777 conn.exec_driver_sql("insert into x (a, b) values (1, 1)")
778 conn.exec_driver_sql("insert into x (a, b) values (2, 2)")
780 result = conn.exec_driver_sql("select x.a, x.b from x")
781 assert result.keys() == ["a", "b"]
783 result = conn.exec_driver_sql('''
784 select x.a, x.b from x where a=1
785 union
786 select x.a, x.b from x where a=2
787 ''')
788 assert result.keys() == ["a", "b"]
790Note that above, even though SQLAlchemy filters out the dots, *both
791names are still addressable*::
793 >>> row = result.first()
794 >>> row["a"]
795 1
796 >>> row["x.a"]
797 1
798 >>> row["b"]
799 1
800 >>> row["x.b"]
801 1
803Therefore, the workaround applied by SQLAlchemy only impacts
804:meth:`_engine.CursorResult.keys` and :meth:`.Row.keys()` in the public API. In
805the very specific case where an application is forced to use column names that
806contain dots, and the functionality of :meth:`_engine.CursorResult.keys` and
807:meth:`.Row.keys()` is required to return these dotted names unmodified,
808the ``sqlite_raw_colnames`` execution option may be provided, either on a
809per-:class:`_engine.Connection` basis::
811 result = conn.execution_options(sqlite_raw_colnames=True).exec_driver_sql('''
812 select x.a, x.b from x where a=1
813 union
814 select x.a, x.b from x where a=2
815 ''')
816 assert result.keys() == ["x.a", "x.b"]
818or on a per-:class:`_engine.Engine` basis::
820 engine = create_engine("sqlite://", execution_options={"sqlite_raw_colnames": True})
822When using the per-:class:`_engine.Engine` execution option, note that
823**Core and ORM queries that use UNION may not function properly**.
825SQLite-specific table options
826-----------------------------
828One option for CREATE TABLE is supported directly by the SQLite
829dialect in conjunction with the :class:`_schema.Table` construct:
831* ``WITHOUT ROWID``::
833 Table("some_table", metadata, ..., sqlite_with_rowid=False)
835.. seealso::
837 `SQLite CREATE TABLE options
838 <https://www.sqlite.org/lang_createtable.html>`_
841.. _sqlite_include_internal:
843Reflecting internal schema tables
844----------------------------------
846Reflection methods that return lists of tables will omit so-called
847"SQLite internal schema object" names, which are considered by SQLite
848as any object name that is prefixed with ``sqlite_``. An example of
849such an object is the ``sqlite_sequence`` table that's generated when
850the ``AUTOINCREMENT`` column parameter is used. In order to return
851these objects, the parameter ``sqlite_include_internal=True`` may be
852passed to methods such as :meth:`_schema.MetaData.reflect` or
853:meth:`.Inspector.get_table_names`.
855.. versionadded:: 2.0 Added the ``sqlite_include_internal=True`` parameter.
856 Previously, these tables were not ignored by SQLAlchemy reflection
857 methods.
859.. note::
861 The ``sqlite_include_internal`` parameter does not refer to the
862 "system" tables that are present in schemas such as ``sqlite_master``.
864.. seealso::
866 `SQLite Internal Schema Objects <https://www.sqlite.org/fileformat2.html#intschema>`_ - in the SQLite
867 documentation.
869""" # noqa
870from __future__ import annotations
872import datetime
873import numbers
874import re
875from typing import Optional
877from .json import JSON
878from .json import JSONIndexType
879from .json import JSONPathType
880from ... import exc
881from ... import schema as sa_schema
882from ... import sql
883from ... import text
884from ... import types as sqltypes
885from ... import util
886from ...engine import default
887from ...engine import processors
888from ...engine import reflection
889from ...engine.reflection import ReflectionDefaults
890from ...sql import coercions
891from ...sql import ColumnElement
892from ...sql import compiler
893from ...sql import elements
894from ...sql import roles
895from ...sql import schema
896from ...types import BLOB # noqa
897from ...types import BOOLEAN # noqa
898from ...types import CHAR # noqa
899from ...types import DECIMAL # noqa
900from ...types import FLOAT # noqa
901from ...types import INTEGER # noqa
902from ...types import NUMERIC # noqa
903from ...types import REAL # noqa
904from ...types import SMALLINT # noqa
905from ...types import TEXT # noqa
906from ...types import TIMESTAMP # noqa
907from ...types import VARCHAR # noqa
910class _SQliteJson(JSON):
911 def result_processor(self, dialect, coltype):
912 default_processor = super().result_processor(dialect, coltype)
914 def process(value):
915 try:
916 return default_processor(value)
917 except TypeError:
918 if isinstance(value, numbers.Number):
919 return value
920 else:
921 raise
923 return process
926class _DateTimeMixin:
927 _reg = None
928 _storage_format = None
930 def __init__(self, storage_format=None, regexp=None, **kw):
931 super().__init__(**kw)
932 if regexp is not None:
933 self._reg = re.compile(regexp)
934 if storage_format is not None:
935 self._storage_format = storage_format
937 @property
938 def format_is_text_affinity(self):
939 """return True if the storage format will automatically imply
940 a TEXT affinity.
942 If the storage format contains no non-numeric characters,
943 it will imply a NUMERIC storage format on SQLite; in this case,
944 the type will generate its DDL as DATE_CHAR, DATETIME_CHAR,
945 TIME_CHAR.
947 """
948 spec = self._storage_format % {
949 "year": 0,
950 "month": 0,
951 "day": 0,
952 "hour": 0,
953 "minute": 0,
954 "second": 0,
955 "microsecond": 0,
956 }
957 return bool(re.search(r"[^0-9]", spec))
959 def adapt(self, cls, **kw):
960 if issubclass(cls, _DateTimeMixin):
961 if self._storage_format:
962 kw["storage_format"] = self._storage_format
963 if self._reg:
964 kw["regexp"] = self._reg
965 return super().adapt(cls, **kw)
967 def literal_processor(self, dialect):
968 bp = self.bind_processor(dialect)
970 def process(value):
971 return "'%s'" % bp(value)
973 return process
976class DATETIME(_DateTimeMixin, sqltypes.DateTime):
977 r"""Represent a Python datetime object in SQLite using a string.
979 The default string storage format is::
981 "%(year)04d-%(month)02d-%(day)02d %(hour)02d:%(minute)02d:%(second)02d.%(microsecond)06d"
983 e.g.::
985 2021-03-15 12:05:57.105542
987 The incoming storage format is by default parsed using the
988 Python ``datetime.fromisoformat()`` function.
990 .. versionchanged:: 2.0 ``datetime.fromisoformat()`` is used for default
991 datetime string parsing.
993 The storage format can be customized to some degree using the
994 ``storage_format`` and ``regexp`` parameters, such as::
996 import re
997 from sqlalchemy.dialects.sqlite import DATETIME
999 dt = DATETIME(storage_format="%(year)04d/%(month)02d/%(day)02d "
1000 "%(hour)02d:%(minute)02d:%(second)02d",
1001 regexp=r"(\d+)/(\d+)/(\d+) (\d+)-(\d+)-(\d+)"
1002 )
1004 :param storage_format: format string which will be applied to the dict
1005 with keys year, month, day, hour, minute, second, and microsecond.
1007 :param regexp: regular expression which will be applied to incoming result
1008 rows, replacing the use of ``datetime.fromisoformat()`` to parse incoming
1009 strings. If the regexp contains named groups, the resulting match dict is
1010 applied to the Python datetime() constructor as keyword arguments.
1011 Otherwise, if positional groups are used, the datetime() constructor
1012 is called with positional arguments via
1013 ``*map(int, match_obj.groups(0))``.
1015 """ # noqa
1017 _storage_format = (
1018 "%(year)04d-%(month)02d-%(day)02d "
1019 "%(hour)02d:%(minute)02d:%(second)02d.%(microsecond)06d"
1020 )
1022 def __init__(self, *args, **kwargs):
1023 truncate_microseconds = kwargs.pop("truncate_microseconds", False)
1024 super().__init__(*args, **kwargs)
1025 if truncate_microseconds:
1026 assert "storage_format" not in kwargs, (
1027 "You can specify only "
1028 "one of truncate_microseconds or storage_format."
1029 )
1030 assert "regexp" not in kwargs, (
1031 "You can specify only one of "
1032 "truncate_microseconds or regexp."
1033 )
1034 self._storage_format = (
1035 "%(year)04d-%(month)02d-%(day)02d "
1036 "%(hour)02d:%(minute)02d:%(second)02d"
1037 )
1039 def bind_processor(self, dialect):
1040 datetime_datetime = datetime.datetime
1041 datetime_date = datetime.date
1042 format_ = self._storage_format
1044 def process(value):
1045 if value is None:
1046 return None
1047 elif isinstance(value, datetime_datetime):
1048 return format_ % {
1049 "year": value.year,
1050 "month": value.month,
1051 "day": value.day,
1052 "hour": value.hour,
1053 "minute": value.minute,
1054 "second": value.second,
1055 "microsecond": value.microsecond,
1056 }
1057 elif isinstance(value, datetime_date):
1058 return format_ % {
1059 "year": value.year,
1060 "month": value.month,
1061 "day": value.day,
1062 "hour": 0,
1063 "minute": 0,
1064 "second": 0,
1065 "microsecond": 0,
1066 }
1067 else:
1068 raise TypeError(
1069 "SQLite DateTime type only accepts Python "
1070 "datetime and date objects as input."
1071 )
1073 return process
1075 def result_processor(self, dialect, coltype):
1076 if self._reg:
1077 return processors.str_to_datetime_processor_factory(
1078 self._reg, datetime.datetime
1079 )
1080 else:
1081 return processors.str_to_datetime
1084class DATE(_DateTimeMixin, sqltypes.Date):
1085 r"""Represent a Python date object in SQLite using a string.
1087 The default string storage format is::
1089 "%(year)04d-%(month)02d-%(day)02d"
1091 e.g.::
1093 2011-03-15
1095 The incoming storage format is by default parsed using the
1096 Python ``date.fromisoformat()`` function.
1098 .. versionchanged:: 2.0 ``date.fromisoformat()`` is used for default
1099 date string parsing.
1102 The storage format can be customized to some degree using the
1103 ``storage_format`` and ``regexp`` parameters, such as::
1105 import re
1106 from sqlalchemy.dialects.sqlite import DATE
1108 d = DATE(
1109 storage_format="%(month)02d/%(day)02d/%(year)04d",
1110 regexp=re.compile("(?P<month>\d+)/(?P<day>\d+)/(?P<year>\d+)")
1111 )
1113 :param storage_format: format string which will be applied to the
1114 dict with keys year, month, and day.
1116 :param regexp: regular expression which will be applied to
1117 incoming result rows, replacing the use of ``date.fromisoformat()`` to
1118 parse incoming strings. If the regexp contains named groups, the resulting
1119 match dict is applied to the Python date() constructor as keyword
1120 arguments. Otherwise, if positional groups are used, the date()
1121 constructor is called with positional arguments via
1122 ``*map(int, match_obj.groups(0))``.
1124 """
1126 _storage_format = "%(year)04d-%(month)02d-%(day)02d"
1128 def bind_processor(self, dialect):
1129 datetime_date = datetime.date
1130 format_ = self._storage_format
1132 def process(value):
1133 if value is None:
1134 return None
1135 elif isinstance(value, datetime_date):
1136 return format_ % {
1137 "year": value.year,
1138 "month": value.month,
1139 "day": value.day,
1140 }
1141 else:
1142 raise TypeError(
1143 "SQLite Date type only accepts Python "
1144 "date objects as input."
1145 )
1147 return process
1149 def result_processor(self, dialect, coltype):
1150 if self._reg:
1151 return processors.str_to_datetime_processor_factory(
1152 self._reg, datetime.date
1153 )
1154 else:
1155 return processors.str_to_date
1158class TIME(_DateTimeMixin, sqltypes.Time):
1159 r"""Represent a Python time object in SQLite using a string.
1161 The default string storage format is::
1163 "%(hour)02d:%(minute)02d:%(second)02d.%(microsecond)06d"
1165 e.g.::
1167 12:05:57.10558
1169 The incoming storage format is by default parsed using the
1170 Python ``time.fromisoformat()`` function.
1172 .. versionchanged:: 2.0 ``time.fromisoformat()`` is used for default
1173 time string parsing.
1175 The storage format can be customized to some degree using the
1176 ``storage_format`` and ``regexp`` parameters, such as::
1178 import re
1179 from sqlalchemy.dialects.sqlite import TIME
1181 t = TIME(storage_format="%(hour)02d-%(minute)02d-"
1182 "%(second)02d-%(microsecond)06d",
1183 regexp=re.compile("(\d+)-(\d+)-(\d+)-(?:-(\d+))?")
1184 )
1186 :param storage_format: format string which will be applied to the dict
1187 with keys hour, minute, second, and microsecond.
1189 :param regexp: regular expression which will be applied to incoming result
1190 rows, replacing the use of ``datetime.fromisoformat()`` to parse incoming
1191 strings. If the regexp contains named groups, the resulting match dict is
1192 applied to the Python time() constructor as keyword arguments. Otherwise,
1193 if positional groups are used, the time() constructor is called with
1194 positional arguments via ``*map(int, match_obj.groups(0))``.
1196 """
1198 _storage_format = "%(hour)02d:%(minute)02d:%(second)02d.%(microsecond)06d"
1200 def __init__(self, *args, **kwargs):
1201 truncate_microseconds = kwargs.pop("truncate_microseconds", False)
1202 super().__init__(*args, **kwargs)
1203 if truncate_microseconds:
1204 assert "storage_format" not in kwargs, (
1205 "You can specify only "
1206 "one of truncate_microseconds or storage_format."
1207 )
1208 assert "regexp" not in kwargs, (
1209 "You can specify only one of "
1210 "truncate_microseconds or regexp."
1211 )
1212 self._storage_format = "%(hour)02d:%(minute)02d:%(second)02d"
1214 def bind_processor(self, dialect):
1215 datetime_time = datetime.time
1216 format_ = self._storage_format
1218 def process(value):
1219 if value is None:
1220 return None
1221 elif isinstance(value, datetime_time):
1222 return format_ % {
1223 "hour": value.hour,
1224 "minute": value.minute,
1225 "second": value.second,
1226 "microsecond": value.microsecond,
1227 }
1228 else:
1229 raise TypeError(
1230 "SQLite Time type only accepts Python "
1231 "time objects as input."
1232 )
1234 return process
1236 def result_processor(self, dialect, coltype):
1237 if self._reg:
1238 return processors.str_to_datetime_processor_factory(
1239 self._reg, datetime.time
1240 )
1241 else:
1242 return processors.str_to_time
1245colspecs = {
1246 sqltypes.Date: DATE,
1247 sqltypes.DateTime: DATETIME,
1248 sqltypes.JSON: _SQliteJson,
1249 sqltypes.JSON.JSONIndexType: JSONIndexType,
1250 sqltypes.JSON.JSONPathType: JSONPathType,
1251 sqltypes.Time: TIME,
1252}
1254ischema_names = {
1255 "BIGINT": sqltypes.BIGINT,
1256 "BLOB": sqltypes.BLOB,
1257 "BOOL": sqltypes.BOOLEAN,
1258 "BOOLEAN": sqltypes.BOOLEAN,
1259 "CHAR": sqltypes.CHAR,
1260 "DATE": sqltypes.DATE,
1261 "DATE_CHAR": sqltypes.DATE,
1262 "DATETIME": sqltypes.DATETIME,
1263 "DATETIME_CHAR": sqltypes.DATETIME,
1264 "DOUBLE": sqltypes.DOUBLE,
1265 "DECIMAL": sqltypes.DECIMAL,
1266 "FLOAT": sqltypes.FLOAT,
1267 "INT": sqltypes.INTEGER,
1268 "INTEGER": sqltypes.INTEGER,
1269 "JSON": JSON,
1270 "NUMERIC": sqltypes.NUMERIC,
1271 "REAL": sqltypes.REAL,
1272 "SMALLINT": sqltypes.SMALLINT,
1273 "TEXT": sqltypes.TEXT,
1274 "TIME": sqltypes.TIME,
1275 "TIME_CHAR": sqltypes.TIME,
1276 "TIMESTAMP": sqltypes.TIMESTAMP,
1277 "VARCHAR": sqltypes.VARCHAR,
1278 "NVARCHAR": sqltypes.NVARCHAR,
1279 "NCHAR": sqltypes.NCHAR,
1280}
1283class SQLiteCompiler(compiler.SQLCompiler):
1284 extract_map = util.update_copy(
1285 compiler.SQLCompiler.extract_map,
1286 {
1287 "month": "%m",
1288 "day": "%d",
1289 "year": "%Y",
1290 "second": "%S",
1291 "hour": "%H",
1292 "doy": "%j",
1293 "minute": "%M",
1294 "epoch": "%s",
1295 "dow": "%w",
1296 "week": "%W",
1297 },
1298 )
1300 def visit_truediv_binary(self, binary, operator, **kw):
1301 return (
1302 self.process(binary.left, **kw)
1303 + " / "
1304 + "(%s + 0.0)" % self.process(binary.right, **kw)
1305 )
1307 def visit_now_func(self, fn, **kw):
1308 return "CURRENT_TIMESTAMP"
1310 def visit_localtimestamp_func(self, func, **kw):
1311 return 'DATETIME(CURRENT_TIMESTAMP, "localtime")'
1313 def visit_true(self, expr, **kw):
1314 return "1"
1316 def visit_false(self, expr, **kw):
1317 return "0"
1319 def visit_char_length_func(self, fn, **kw):
1320 return "length%s" % self.function_argspec(fn)
1322 def visit_aggregate_strings_func(self, fn, **kw):
1323 return "group_concat%s" % self.function_argspec(fn)
1325 def visit_cast(self, cast, **kwargs):
1326 if self.dialect.supports_cast:
1327 return super().visit_cast(cast, **kwargs)
1328 else:
1329 return self.process(cast.clause, **kwargs)
1331 def visit_extract(self, extract, **kw):
1332 try:
1333 return "CAST(STRFTIME('%s', %s) AS INTEGER)" % (
1334 self.extract_map[extract.field],
1335 self.process(extract.expr, **kw),
1336 )
1337 except KeyError as err:
1338 raise exc.CompileError(
1339 "%s is not a valid extract argument." % extract.field
1340 ) from err
1342 def returning_clause(
1343 self,
1344 stmt,
1345 returning_cols,
1346 *,
1347 populate_result_map,
1348 **kw,
1349 ):
1350 kw["include_table"] = False
1351 return super().returning_clause(
1352 stmt, returning_cols, populate_result_map=populate_result_map, **kw
1353 )
1355 def limit_clause(self, select, **kw):
1356 text = ""
1357 if select._limit_clause is not None:
1358 text += "\n LIMIT " + self.process(select._limit_clause, **kw)
1359 if select._offset_clause is not None:
1360 if select._limit_clause is None:
1361 text += "\n LIMIT " + self.process(sql.literal(-1))
1362 text += " OFFSET " + self.process(select._offset_clause, **kw)
1363 else:
1364 text += " OFFSET " + self.process(sql.literal(0), **kw)
1365 return text
1367 def for_update_clause(self, select, **kw):
1368 # sqlite has no "FOR UPDATE" AFAICT
1369 return ""
1371 def update_from_clause(
1372 self, update_stmt, from_table, extra_froms, from_hints, **kw
1373 ):
1374 kw["asfrom"] = True
1375 return "FROM " + ", ".join(
1376 t._compiler_dispatch(self, fromhints=from_hints, **kw)
1377 for t in extra_froms
1378 )
1380 def visit_is_distinct_from_binary(self, binary, operator, **kw):
1381 return "%s IS NOT %s" % (
1382 self.process(binary.left),
1383 self.process(binary.right),
1384 )
1386 def visit_is_not_distinct_from_binary(self, binary, operator, **kw):
1387 return "%s IS %s" % (
1388 self.process(binary.left),
1389 self.process(binary.right),
1390 )
1392 def visit_json_getitem_op_binary(self, binary, operator, **kw):
1393 if binary.type._type_affinity is sqltypes.JSON:
1394 expr = "JSON_QUOTE(JSON_EXTRACT(%s, %s))"
1395 else:
1396 expr = "JSON_EXTRACT(%s, %s)"
1398 return expr % (
1399 self.process(binary.left, **kw),
1400 self.process(binary.right, **kw),
1401 )
1403 def visit_json_path_getitem_op_binary(self, binary, operator, **kw):
1404 if binary.type._type_affinity is sqltypes.JSON:
1405 expr = "JSON_QUOTE(JSON_EXTRACT(%s, %s))"
1406 else:
1407 expr = "JSON_EXTRACT(%s, %s)"
1409 return expr % (
1410 self.process(binary.left, **kw),
1411 self.process(binary.right, **kw),
1412 )
1414 def visit_empty_set_op_expr(self, type_, expand_op, **kw):
1415 # slightly old SQLite versions don't seem to be able to handle
1416 # the empty set impl
1417 return self.visit_empty_set_expr(type_)
1419 def visit_empty_set_expr(self, element_types, **kw):
1420 return "SELECT %s FROM (SELECT %s) WHERE 1!=1" % (
1421 ", ".join("1" for type_ in element_types or [INTEGER()]),
1422 ", ".join("1" for type_ in element_types or [INTEGER()]),
1423 )
1425 def visit_regexp_match_op_binary(self, binary, operator, **kw):
1426 return self._generate_generic_binary(binary, " REGEXP ", **kw)
1428 def visit_not_regexp_match_op_binary(self, binary, operator, **kw):
1429 return self._generate_generic_binary(binary, " NOT REGEXP ", **kw)
1431 def _on_conflict_target(self, clause, **kw):
1432 if clause.constraint_target is not None:
1433 target_text = "(%s)" % clause.constraint_target
1434 elif clause.inferred_target_elements is not None:
1435 target_text = "(%s)" % ", ".join(
1436 (
1437 self.preparer.quote(c)
1438 if isinstance(c, str)
1439 else self.process(c, include_table=False, use_schema=False)
1440 )
1441 for c in clause.inferred_target_elements
1442 )
1443 if clause.inferred_target_whereclause is not None:
1444 target_text += " WHERE %s" % self.process(
1445 clause.inferred_target_whereclause,
1446 include_table=False,
1447 use_schema=False,
1448 literal_binds=True,
1449 )
1451 else:
1452 target_text = ""
1454 return target_text
1456 def visit_on_conflict_do_nothing(self, on_conflict, **kw):
1457 target_text = self._on_conflict_target(on_conflict, **kw)
1459 if target_text:
1460 return "ON CONFLICT %s DO NOTHING" % target_text
1461 else:
1462 return "ON CONFLICT DO NOTHING"
1464 def visit_on_conflict_do_update(self, on_conflict, **kw):
1465 clause = on_conflict
1467 target_text = self._on_conflict_target(on_conflict, **kw)
1469 action_set_ops = []
1471 set_parameters = dict(clause.update_values_to_set)
1472 # create a list of column assignment clauses as tuples
1474 insert_statement = self.stack[-1]["selectable"]
1475 cols = insert_statement.table.c
1476 for c in cols:
1477 col_key = c.key
1479 if col_key in set_parameters:
1480 value = set_parameters.pop(col_key)
1481 elif c in set_parameters:
1482 value = set_parameters.pop(c)
1483 else:
1484 continue
1486 if coercions._is_literal(value):
1487 value = elements.BindParameter(None, value, type_=c.type)
1489 else:
1490 if (
1491 isinstance(value, elements.BindParameter)
1492 and value.type._isnull
1493 ):
1494 value = value._clone()
1495 value.type = c.type
1496 value_text = self.process(value.self_group(), use_schema=False)
1498 key_text = self.preparer.quote(c.name)
1499 action_set_ops.append("%s = %s" % (key_text, value_text))
1501 # check for names that don't match columns
1502 if set_parameters:
1503 util.warn(
1504 "Additional column names not matching "
1505 "any column keys in table '%s': %s"
1506 % (
1507 self.current_executable.table.name,
1508 (", ".join("'%s'" % c for c in set_parameters)),
1509 )
1510 )
1511 for k, v in set_parameters.items():
1512 key_text = (
1513 self.preparer.quote(k)
1514 if isinstance(k, str)
1515 else self.process(k, use_schema=False)
1516 )
1517 value_text = self.process(
1518 coercions.expect(roles.ExpressionElementRole, v),
1519 use_schema=False,
1520 )
1521 action_set_ops.append("%s = %s" % (key_text, value_text))
1523 action_text = ", ".join(action_set_ops)
1524 if clause.update_whereclause is not None:
1525 action_text += " WHERE %s" % self.process(
1526 clause.update_whereclause, include_table=True, use_schema=False
1527 )
1529 return "ON CONFLICT %s DO UPDATE SET %s" % (target_text, action_text)
1532class SQLiteDDLCompiler(compiler.DDLCompiler):
1533 def get_column_specification(self, column, **kwargs):
1534 coltype = self.dialect.type_compiler_instance.process(
1535 column.type, type_expression=column
1536 )
1537 colspec = self.preparer.format_column(column) + " " + coltype
1538 default = self.get_column_default_string(column)
1539 if default is not None:
1540 if isinstance(column.server_default.arg, ColumnElement):
1541 default = "(" + default + ")"
1542 colspec += " DEFAULT " + default
1544 if not column.nullable:
1545 colspec += " NOT NULL"
1547 on_conflict_clause = column.dialect_options["sqlite"][
1548 "on_conflict_not_null"
1549 ]
1550 if on_conflict_clause is not None:
1551 colspec += " ON CONFLICT " + on_conflict_clause
1553 if column.primary_key:
1554 if (
1555 column.autoincrement is True
1556 and len(column.table.primary_key.columns) != 1
1557 ):
1558 raise exc.CompileError(
1559 "SQLite does not support autoincrement for "
1560 "composite primary keys"
1561 )
1563 if (
1564 column.table.dialect_options["sqlite"]["autoincrement"]
1565 and len(column.table.primary_key.columns) == 1
1566 and issubclass(column.type._type_affinity, sqltypes.Integer)
1567 and not column.foreign_keys
1568 ):
1569 colspec += " PRIMARY KEY"
1571 on_conflict_clause = column.dialect_options["sqlite"][
1572 "on_conflict_primary_key"
1573 ]
1574 if on_conflict_clause is not None:
1575 colspec += " ON CONFLICT " + on_conflict_clause
1577 colspec += " AUTOINCREMENT"
1579 if column.computed is not None:
1580 colspec += " " + self.process(column.computed)
1582 return colspec
1584 def visit_primary_key_constraint(self, constraint, **kw):
1585 # for columns with sqlite_autoincrement=True,
1586 # the PRIMARY KEY constraint can only be inline
1587 # with the column itself.
1588 if len(constraint.columns) == 1:
1589 c = list(constraint)[0]
1590 if (
1591 c.primary_key
1592 and c.table.dialect_options["sqlite"]["autoincrement"]
1593 and issubclass(c.type._type_affinity, sqltypes.Integer)
1594 and not c.foreign_keys
1595 ):
1596 return None
1598 text = super().visit_primary_key_constraint(constraint)
1600 on_conflict_clause = constraint.dialect_options["sqlite"][
1601 "on_conflict"
1602 ]
1603 if on_conflict_clause is None and len(constraint.columns) == 1:
1604 on_conflict_clause = list(constraint)[0].dialect_options["sqlite"][
1605 "on_conflict_primary_key"
1606 ]
1608 if on_conflict_clause is not None:
1609 text += " ON CONFLICT " + on_conflict_clause
1611 return text
1613 def visit_unique_constraint(self, constraint, **kw):
1614 text = super().visit_unique_constraint(constraint)
1616 on_conflict_clause = constraint.dialect_options["sqlite"][
1617 "on_conflict"
1618 ]
1619 if on_conflict_clause is None and len(constraint.columns) == 1:
1620 col1 = list(constraint)[0]
1621 if isinstance(col1, schema.SchemaItem):
1622 on_conflict_clause = list(constraint)[0].dialect_options[
1623 "sqlite"
1624 ]["on_conflict_unique"]
1626 if on_conflict_clause is not None:
1627 text += " ON CONFLICT " + on_conflict_clause
1629 return text
1631 def visit_check_constraint(self, constraint, **kw):
1632 text = super().visit_check_constraint(constraint)
1634 on_conflict_clause = constraint.dialect_options["sqlite"][
1635 "on_conflict"
1636 ]
1638 if on_conflict_clause is not None:
1639 text += " ON CONFLICT " + on_conflict_clause
1641 return text
1643 def visit_column_check_constraint(self, constraint, **kw):
1644 text = super().visit_column_check_constraint(constraint)
1646 if constraint.dialect_options["sqlite"]["on_conflict"] is not None:
1647 raise exc.CompileError(
1648 "SQLite does not support on conflict clause for "
1649 "column check constraint"
1650 )
1652 return text
1654 def visit_foreign_key_constraint(self, constraint, **kw):
1655 local_table = constraint.elements[0].parent.table
1656 remote_table = constraint.elements[0].column.table
1658 if local_table.schema != remote_table.schema:
1659 return None
1660 else:
1661 return super().visit_foreign_key_constraint(constraint)
1663 def define_constraint_remote_table(self, constraint, table, preparer):
1664 """Format the remote table clause of a CREATE CONSTRAINT clause."""
1666 return preparer.format_table(table, use_schema=False)
1668 def visit_create_index(
1669 self, create, include_schema=False, include_table_schema=True, **kw
1670 ):
1671 index = create.element
1672 self._verify_index_table(index)
1673 preparer = self.preparer
1674 text = "CREATE "
1675 if index.unique:
1676 text += "UNIQUE "
1678 text += "INDEX "
1680 if create.if_not_exists:
1681 text += "IF NOT EXISTS "
1683 text += "%s ON %s (%s)" % (
1684 self._prepared_index_name(index, include_schema=True),
1685 preparer.format_table(index.table, use_schema=False),
1686 ", ".join(
1687 self.sql_compiler.process(
1688 expr, include_table=False, literal_binds=True
1689 )
1690 for expr in index.expressions
1691 ),
1692 )
1694 whereclause = index.dialect_options["sqlite"]["where"]
1695 if whereclause is not None:
1696 where_compiled = self.sql_compiler.process(
1697 whereclause, include_table=False, literal_binds=True
1698 )
1699 text += " WHERE " + where_compiled
1701 return text
1703 def post_create_table(self, table):
1704 if table.dialect_options["sqlite"]["with_rowid"] is False:
1705 return "\n WITHOUT ROWID"
1706 return ""
1709class SQLiteTypeCompiler(compiler.GenericTypeCompiler):
1710 def visit_large_binary(self, type_, **kw):
1711 return self.visit_BLOB(type_)
1713 def visit_DATETIME(self, type_, **kw):
1714 if (
1715 not isinstance(type_, _DateTimeMixin)
1716 or type_.format_is_text_affinity
1717 ):
1718 return super().visit_DATETIME(type_)
1719 else:
1720 return "DATETIME_CHAR"
1722 def visit_DATE(self, type_, **kw):
1723 if (
1724 not isinstance(type_, _DateTimeMixin)
1725 or type_.format_is_text_affinity
1726 ):
1727 return super().visit_DATE(type_)
1728 else:
1729 return "DATE_CHAR"
1731 def visit_TIME(self, type_, **kw):
1732 if (
1733 not isinstance(type_, _DateTimeMixin)
1734 or type_.format_is_text_affinity
1735 ):
1736 return super().visit_TIME(type_)
1737 else:
1738 return "TIME_CHAR"
1740 def visit_JSON(self, type_, **kw):
1741 # note this name provides NUMERIC affinity, not TEXT.
1742 # should not be an issue unless the JSON value consists of a single
1743 # numeric value. JSONTEXT can be used if this case is required.
1744 return "JSON"
1747class SQLiteIdentifierPreparer(compiler.IdentifierPreparer):
1748 reserved_words = {
1749 "add",
1750 "after",
1751 "all",
1752 "alter",
1753 "analyze",
1754 "and",
1755 "as",
1756 "asc",
1757 "attach",
1758 "autoincrement",
1759 "before",
1760 "begin",
1761 "between",
1762 "by",
1763 "cascade",
1764 "case",
1765 "cast",
1766 "check",
1767 "collate",
1768 "column",
1769 "commit",
1770 "conflict",
1771 "constraint",
1772 "create",
1773 "cross",
1774 "current_date",
1775 "current_time",
1776 "current_timestamp",
1777 "database",
1778 "default",
1779 "deferrable",
1780 "deferred",
1781 "delete",
1782 "desc",
1783 "detach",
1784 "distinct",
1785 "drop",
1786 "each",
1787 "else",
1788 "end",
1789 "escape",
1790 "except",
1791 "exclusive",
1792 "exists",
1793 "explain",
1794 "false",
1795 "fail",
1796 "for",
1797 "foreign",
1798 "from",
1799 "full",
1800 "glob",
1801 "group",
1802 "having",
1803 "if",
1804 "ignore",
1805 "immediate",
1806 "in",
1807 "index",
1808 "indexed",
1809 "initially",
1810 "inner",
1811 "insert",
1812 "instead",
1813 "intersect",
1814 "into",
1815 "is",
1816 "isnull",
1817 "join",
1818 "key",
1819 "left",
1820 "like",
1821 "limit",
1822 "match",
1823 "natural",
1824 "not",
1825 "notnull",
1826 "null",
1827 "of",
1828 "offset",
1829 "on",
1830 "or",
1831 "order",
1832 "outer",
1833 "plan",
1834 "pragma",
1835 "primary",
1836 "query",
1837 "raise",
1838 "references",
1839 "reindex",
1840 "rename",
1841 "replace",
1842 "restrict",
1843 "right",
1844 "rollback",
1845 "row",
1846 "select",
1847 "set",
1848 "table",
1849 "temp",
1850 "temporary",
1851 "then",
1852 "to",
1853 "transaction",
1854 "trigger",
1855 "true",
1856 "union",
1857 "unique",
1858 "update",
1859 "using",
1860 "vacuum",
1861 "values",
1862 "view",
1863 "virtual",
1864 "when",
1865 "where",
1866 }
1869class SQLiteExecutionContext(default.DefaultExecutionContext):
1870 @util.memoized_property
1871 def _preserve_raw_colnames(self):
1872 return (
1873 not self.dialect._broken_dotted_colnames
1874 or self.execution_options.get("sqlite_raw_colnames", False)
1875 )
1877 def _translate_colname(self, colname):
1878 # TODO: detect SQLite version 3.10.0 or greater;
1879 # see [ticket:3633]
1881 # adjust for dotted column names. SQLite
1882 # in the case of UNION may store col names as
1883 # "tablename.colname", or if using an attached database,
1884 # "database.tablename.colname", in cursor.description
1885 if not self._preserve_raw_colnames and "." in colname:
1886 return colname.split(".")[-1], colname
1887 else:
1888 return colname, None
1891class SQLiteDialect(default.DefaultDialect):
1892 name = "sqlite"
1893 supports_alter = False
1895 # SQlite supports "DEFAULT VALUES" but *does not* support
1896 # "VALUES (DEFAULT)"
1897 supports_default_values = True
1898 supports_default_metavalue = False
1900 # sqlite issue:
1901 # https://github.com/python/cpython/issues/93421
1902 # note this parameter is no longer used by the ORM or default dialect
1903 # see #9414
1904 supports_sane_rowcount_returning = False
1906 supports_empty_insert = False
1907 supports_cast = True
1908 supports_multivalues_insert = True
1909 use_insertmanyvalues = True
1910 tuple_in_values = True
1911 supports_statement_cache = True
1912 insert_null_pk_still_autoincrements = True
1913 insert_returning = True
1914 update_returning = True
1915 update_returning_multifrom = True
1916 delete_returning = True
1917 update_returning_multifrom = True
1919 supports_default_metavalue = True
1920 """dialect supports INSERT... VALUES (DEFAULT) syntax"""
1922 default_metavalue_token = "NULL"
1923 """for INSERT... VALUES (DEFAULT) syntax, the token to put in the
1924 parenthesis."""
1926 default_paramstyle = "qmark"
1927 execution_ctx_cls = SQLiteExecutionContext
1928 statement_compiler = SQLiteCompiler
1929 ddl_compiler = SQLiteDDLCompiler
1930 type_compiler_cls = SQLiteTypeCompiler
1931 preparer = SQLiteIdentifierPreparer
1932 ischema_names = ischema_names
1933 colspecs = colspecs
1935 construct_arguments = [
1936 (
1937 sa_schema.Table,
1938 {
1939 "autoincrement": False,
1940 "with_rowid": True,
1941 },
1942 ),
1943 (sa_schema.Index, {"where": None}),
1944 (
1945 sa_schema.Column,
1946 {
1947 "on_conflict_primary_key": None,
1948 "on_conflict_not_null": None,
1949 "on_conflict_unique": None,
1950 },
1951 ),
1952 (sa_schema.Constraint, {"on_conflict": None}),
1953 ]
1955 _broken_fk_pragma_quotes = False
1956 _broken_dotted_colnames = False
1958 @util.deprecated_params(
1959 _json_serializer=(
1960 "1.3.7",
1961 "The _json_serializer argument to the SQLite dialect has "
1962 "been renamed to the correct name of json_serializer. The old "
1963 "argument name will be removed in a future release.",
1964 ),
1965 _json_deserializer=(
1966 "1.3.7",
1967 "The _json_deserializer argument to the SQLite dialect has "
1968 "been renamed to the correct name of json_deserializer. The old "
1969 "argument name will be removed in a future release.",
1970 ),
1971 )
1972 def __init__(
1973 self,
1974 native_datetime=False,
1975 json_serializer=None,
1976 json_deserializer=None,
1977 _json_serializer=None,
1978 _json_deserializer=None,
1979 **kwargs,
1980 ):
1981 default.DefaultDialect.__init__(self, **kwargs)
1983 if _json_serializer:
1984 json_serializer = _json_serializer
1985 if _json_deserializer:
1986 json_deserializer = _json_deserializer
1987 self._json_serializer = json_serializer
1988 self._json_deserializer = json_deserializer
1990 # this flag used by pysqlite dialect, and perhaps others in the
1991 # future, to indicate the driver is handling date/timestamp
1992 # conversions (and perhaps datetime/time as well on some hypothetical
1993 # driver ?)
1994 self.native_datetime = native_datetime
1996 if self.dbapi is not None:
1997 if self.dbapi.sqlite_version_info < (3, 7, 16):
1998 util.warn(
1999 "SQLite version %s is older than 3.7.16, and will not "
2000 "support right nested joins, as are sometimes used in "
2001 "more complex ORM scenarios. SQLAlchemy 1.4 and above "
2002 "no longer tries to rewrite these joins."
2003 % (self.dbapi.sqlite_version_info,)
2004 )
2006 # NOTE: python 3.7 on fedora for me has SQLite 3.34.1. These
2007 # version checks are getting very stale.
2008 self._broken_dotted_colnames = self.dbapi.sqlite_version_info < (
2009 3,
2010 10,
2011 0,
2012 )
2013 self.supports_default_values = self.dbapi.sqlite_version_info >= (
2014 3,
2015 3,
2016 8,
2017 )
2018 self.supports_cast = self.dbapi.sqlite_version_info >= (3, 2, 3)
2019 self.supports_multivalues_insert = (
2020 # https://www.sqlite.org/releaselog/3_7_11.html
2021 self.dbapi.sqlite_version_info
2022 >= (3, 7, 11)
2023 )
2024 # see https://www.sqlalchemy.org/trac/ticket/2568
2025 # as well as https://www.sqlite.org/src/info/600482d161
2026 self._broken_fk_pragma_quotes = self.dbapi.sqlite_version_info < (
2027 3,
2028 6,
2029 14,
2030 )
2032 if self.dbapi.sqlite_version_info < (3, 35) or util.pypy:
2033 self.update_returning = self.delete_returning = (
2034 self.insert_returning
2035 ) = False
2037 if self.dbapi.sqlite_version_info < (3, 32, 0):
2038 # https://www.sqlite.org/limits.html
2039 self.insertmanyvalues_max_parameters = 999
2041 _isolation_lookup = util.immutabledict(
2042 {"READ UNCOMMITTED": 1, "SERIALIZABLE": 0}
2043 )
2045 def get_isolation_level_values(self, dbapi_connection):
2046 return list(self._isolation_lookup)
2048 def set_isolation_level(self, dbapi_connection, level):
2049 isolation_level = self._isolation_lookup[level]
2051 cursor = dbapi_connection.cursor()
2052 cursor.execute(f"PRAGMA read_uncommitted = {isolation_level}")
2053 cursor.close()
2055 def get_isolation_level(self, dbapi_connection):
2056 cursor = dbapi_connection.cursor()
2057 cursor.execute("PRAGMA read_uncommitted")
2058 res = cursor.fetchone()
2059 if res:
2060 value = res[0]
2061 else:
2062 # https://www.sqlite.org/changes.html#version_3_3_3
2063 # "Optional READ UNCOMMITTED isolation (instead of the
2064 # default isolation level of SERIALIZABLE) and
2065 # table level locking when database connections
2066 # share a common cache.""
2067 # pre-SQLite 3.3.0 default to 0
2068 value = 0
2069 cursor.close()
2070 if value == 0:
2071 return "SERIALIZABLE"
2072 elif value == 1:
2073 return "READ UNCOMMITTED"
2074 else:
2075 assert False, "Unknown isolation level %s" % value
2077 @reflection.cache
2078 def get_schema_names(self, connection, **kw):
2079 s = "PRAGMA database_list"
2080 dl = connection.exec_driver_sql(s)
2082 return [db[1] for db in dl if db[1] != "temp"]
2084 def _format_schema(self, schema, table_name):
2085 if schema is not None:
2086 qschema = self.identifier_preparer.quote_identifier(schema)
2087 name = f"{qschema}.{table_name}"
2088 else:
2089 name = table_name
2090 return name
2092 def _sqlite_main_query(
2093 self,
2094 table: str,
2095 type_: str,
2096 schema: Optional[str],
2097 sqlite_include_internal: bool,
2098 ):
2099 main = self._format_schema(schema, table)
2100 if not sqlite_include_internal:
2101 filter_table = " AND name NOT LIKE 'sqlite~_%' ESCAPE '~'"
2102 else:
2103 filter_table = ""
2104 query = (
2105 f"SELECT name FROM {main} "
2106 f"WHERE type='{type_}'{filter_table} "
2107 "ORDER BY name"
2108 )
2109 return query
2111 @reflection.cache
2112 def get_table_names(
2113 self, connection, schema=None, sqlite_include_internal=False, **kw
2114 ):
2115 query = self._sqlite_main_query(
2116 "sqlite_master", "table", schema, sqlite_include_internal
2117 )
2118 names = connection.exec_driver_sql(query).scalars().all()
2119 return names
2121 @reflection.cache
2122 def get_temp_table_names(
2123 self, connection, sqlite_include_internal=False, **kw
2124 ):
2125 query = self._sqlite_main_query(
2126 "sqlite_temp_master", "table", None, sqlite_include_internal
2127 )
2128 names = connection.exec_driver_sql(query).scalars().all()
2129 return names
2131 @reflection.cache
2132 def get_temp_view_names(
2133 self, connection, sqlite_include_internal=False, **kw
2134 ):
2135 query = self._sqlite_main_query(
2136 "sqlite_temp_master", "view", None, sqlite_include_internal
2137 )
2138 names = connection.exec_driver_sql(query).scalars().all()
2139 return names
2141 @reflection.cache
2142 def has_table(self, connection, table_name, schema=None, **kw):
2143 self._ensure_has_table_connection(connection)
2145 if schema is not None and schema not in self.get_schema_names(
2146 connection, **kw
2147 ):
2148 return False
2150 info = self._get_table_pragma(
2151 connection, "table_info", table_name, schema=schema
2152 )
2153 return bool(info)
2155 def _get_default_schema_name(self, connection):
2156 return "main"
2158 @reflection.cache
2159 def get_view_names(
2160 self, connection, schema=None, sqlite_include_internal=False, **kw
2161 ):
2162 query = self._sqlite_main_query(
2163 "sqlite_master", "view", schema, sqlite_include_internal
2164 )
2165 names = connection.exec_driver_sql(query).scalars().all()
2166 return names
2168 @reflection.cache
2169 def get_view_definition(self, connection, view_name, schema=None, **kw):
2170 if schema is not None:
2171 qschema = self.identifier_preparer.quote_identifier(schema)
2172 master = f"{qschema}.sqlite_master"
2173 s = ("SELECT sql FROM %s WHERE name = ? AND type='view'") % (
2174 master,
2175 )
2176 rs = connection.exec_driver_sql(s, (view_name,))
2177 else:
2178 try:
2179 s = (
2180 "SELECT sql FROM "
2181 " (SELECT * FROM sqlite_master UNION ALL "
2182 " SELECT * FROM sqlite_temp_master) "
2183 "WHERE name = ? "
2184 "AND type='view'"
2185 )
2186 rs = connection.exec_driver_sql(s, (view_name,))
2187 except exc.DBAPIError:
2188 s = (
2189 "SELECT sql FROM sqlite_master WHERE name = ? "
2190 "AND type='view'"
2191 )
2192 rs = connection.exec_driver_sql(s, (view_name,))
2194 result = rs.fetchall()
2195 if result:
2196 return result[0].sql
2197 else:
2198 raise exc.NoSuchTableError(
2199 f"{schema}.{view_name}" if schema else view_name
2200 )
2202 @reflection.cache
2203 def get_columns(self, connection, table_name, schema=None, **kw):
2204 pragma = "table_info"
2205 # computed columns are threaded as hidden, they require table_xinfo
2206 if self.server_version_info >= (3, 31):
2207 pragma = "table_xinfo"
2208 info = self._get_table_pragma(
2209 connection, pragma, table_name, schema=schema
2210 )
2211 columns = []
2212 tablesql = None
2213 for row in info:
2214 name = row[1]
2215 type_ = row[2].upper()
2216 nullable = not row[3]
2217 default = row[4]
2218 primary_key = row[5]
2219 hidden = row[6] if pragma == "table_xinfo" else 0
2221 # hidden has value 0 for normal columns, 1 for hidden columns,
2222 # 2 for computed virtual columns and 3 for computed stored columns
2223 # https://www.sqlite.org/src/info/069351b85f9a706f60d3e98fbc8aaf40c374356b967c0464aede30ead3d9d18b
2224 if hidden == 1:
2225 continue
2227 generated = bool(hidden)
2228 persisted = hidden == 3
2230 if tablesql is None and generated:
2231 tablesql = self._get_table_sql(
2232 connection, table_name, schema, **kw
2233 )
2235 columns.append(
2236 self._get_column_info(
2237 name,
2238 type_,
2239 nullable,
2240 default,
2241 primary_key,
2242 generated,
2243 persisted,
2244 tablesql,
2245 )
2246 )
2247 if columns:
2248 return columns
2249 elif not self.has_table(connection, table_name, schema):
2250 raise exc.NoSuchTableError(
2251 f"{schema}.{table_name}" if schema else table_name
2252 )
2253 else:
2254 return ReflectionDefaults.columns()
2256 def _get_column_info(
2257 self,
2258 name,
2259 type_,
2260 nullable,
2261 default,
2262 primary_key,
2263 generated,
2264 persisted,
2265 tablesql,
2266 ):
2267 if generated:
2268 # the type of a column "cc INTEGER GENERATED ALWAYS AS (1 + 42)"
2269 # somehow is "INTEGER GENERATED ALWAYS"
2270 type_ = re.sub("generated", "", type_, flags=re.IGNORECASE)
2271 type_ = re.sub("always", "", type_, flags=re.IGNORECASE).strip()
2273 coltype = self._resolve_type_affinity(type_)
2275 if default is not None:
2276 default = str(default)
2278 colspec = {
2279 "name": name,
2280 "type": coltype,
2281 "nullable": nullable,
2282 "default": default,
2283 "primary_key": primary_key,
2284 }
2285 if generated:
2286 sqltext = ""
2287 if tablesql:
2288 pattern = r"[^,]*\s+AS\s+\(([^,]*)\)\s*(?:virtual|stored)?"
2289 match = re.search(
2290 re.escape(name) + pattern, tablesql, re.IGNORECASE
2291 )
2292 if match:
2293 sqltext = match.group(1)
2294 colspec["computed"] = {"sqltext": sqltext, "persisted": persisted}
2295 return colspec
2297 def _resolve_type_affinity(self, type_):
2298 """Return a data type from a reflected column, using affinity rules.
2300 SQLite's goal for universal compatibility introduces some complexity
2301 during reflection, as a column's defined type might not actually be a
2302 type that SQLite understands - or indeed, my not be defined *at all*.
2303 Internally, SQLite handles this with a 'data type affinity' for each
2304 column definition, mapping to one of 'TEXT', 'NUMERIC', 'INTEGER',
2305 'REAL', or 'NONE' (raw bits). The algorithm that determines this is
2306 listed in https://www.sqlite.org/datatype3.html section 2.1.
2308 This method allows SQLAlchemy to support that algorithm, while still
2309 providing access to smarter reflection utilities by recognizing
2310 column definitions that SQLite only supports through affinity (like
2311 DATE and DOUBLE).
2313 """
2314 match = re.match(r"([\w ]+)(\(.*?\))?", type_)
2315 if match:
2316 coltype = match.group(1)
2317 args = match.group(2)
2318 else:
2319 coltype = ""
2320 args = ""
2322 if coltype in self.ischema_names:
2323 coltype = self.ischema_names[coltype]
2324 elif "INT" in coltype:
2325 coltype = sqltypes.INTEGER
2326 elif "CHAR" in coltype or "CLOB" in coltype or "TEXT" in coltype:
2327 coltype = sqltypes.TEXT
2328 elif "BLOB" in coltype or not coltype:
2329 coltype = sqltypes.NullType
2330 elif "REAL" in coltype or "FLOA" in coltype or "DOUB" in coltype:
2331 coltype = sqltypes.REAL
2332 else:
2333 coltype = sqltypes.NUMERIC
2335 if args is not None:
2336 args = re.findall(r"(\d+)", args)
2337 try:
2338 coltype = coltype(*[int(a) for a in args])
2339 except TypeError:
2340 util.warn(
2341 "Could not instantiate type %s with "
2342 "reflected arguments %s; using no arguments."
2343 % (coltype, args)
2344 )
2345 coltype = coltype()
2346 else:
2347 coltype = coltype()
2349 return coltype
2351 @reflection.cache
2352 def get_pk_constraint(self, connection, table_name, schema=None, **kw):
2353 constraint_name = None
2354 table_data = self._get_table_sql(connection, table_name, schema=schema)
2355 if table_data:
2356 PK_PATTERN = r"CONSTRAINT (\w+) PRIMARY KEY"
2357 result = re.search(PK_PATTERN, table_data, re.I)
2358 constraint_name = result.group(1) if result else None
2360 cols = self.get_columns(connection, table_name, schema, **kw)
2361 # consider only pk columns. This also avoids sorting the cached
2362 # value returned by get_columns
2363 cols = [col for col in cols if col.get("primary_key", 0) > 0]
2364 cols.sort(key=lambda col: col.get("primary_key"))
2365 pkeys = [col["name"] for col in cols]
2367 if pkeys:
2368 return {"constrained_columns": pkeys, "name": constraint_name}
2369 else:
2370 return ReflectionDefaults.pk_constraint()
2372 @reflection.cache
2373 def get_foreign_keys(self, connection, table_name, schema=None, **kw):
2374 # sqlite makes this *extremely difficult*.
2375 # First, use the pragma to get the actual FKs.
2376 pragma_fks = self._get_table_pragma(
2377 connection, "foreign_key_list", table_name, schema=schema
2378 )
2380 fks = {}
2382 for row in pragma_fks:
2383 (numerical_id, rtbl, lcol, rcol) = (row[0], row[2], row[3], row[4])
2385 if not rcol:
2386 # no referred column, which means it was not named in the
2387 # original DDL. The referred columns of the foreign key
2388 # constraint are therefore the primary key of the referred
2389 # table.
2390 try:
2391 referred_pk = self.get_pk_constraint(
2392 connection, rtbl, schema=schema, **kw
2393 )
2394 referred_columns = referred_pk["constrained_columns"]
2395 except exc.NoSuchTableError:
2396 # ignore not existing parents
2397 referred_columns = []
2398 else:
2399 # note we use this list only if this is the first column
2400 # in the constraint. for subsequent columns we ignore the
2401 # list and append "rcol" if present.
2402 referred_columns = []
2404 if self._broken_fk_pragma_quotes:
2405 rtbl = re.sub(r"^[\"\[`\']|[\"\]`\']$", "", rtbl)
2407 if numerical_id in fks:
2408 fk = fks[numerical_id]
2409 else:
2410 fk = fks[numerical_id] = {
2411 "name": None,
2412 "constrained_columns": [],
2413 "referred_schema": schema,
2414 "referred_table": rtbl,
2415 "referred_columns": referred_columns,
2416 "options": {},
2417 }
2418 fks[numerical_id] = fk
2420 fk["constrained_columns"].append(lcol)
2422 if rcol:
2423 fk["referred_columns"].append(rcol)
2425 def fk_sig(constrained_columns, referred_table, referred_columns):
2426 return (
2427 tuple(constrained_columns)
2428 + (referred_table,)
2429 + tuple(referred_columns)
2430 )
2432 # then, parse the actual SQL and attempt to find DDL that matches
2433 # the names as well. SQLite saves the DDL in whatever format
2434 # it was typed in as, so need to be liberal here.
2436 keys_by_signature = {
2437 fk_sig(
2438 fk["constrained_columns"],
2439 fk["referred_table"],
2440 fk["referred_columns"],
2441 ): fk
2442 for fk in fks.values()
2443 }
2445 table_data = self._get_table_sql(connection, table_name, schema=schema)
2447 def parse_fks():
2448 if table_data is None:
2449 # system tables, etc.
2450 return
2452 # note that we already have the FKs from PRAGMA above. This whole
2453 # regexp thing is trying to locate additional detail about the
2454 # FKs, namely the name of the constraint and other options.
2455 # so parsing the columns is really about matching it up to what
2456 # we already have.
2457 FK_PATTERN = (
2458 r"(?:CONSTRAINT (\w+) +)?"
2459 r"FOREIGN KEY *\( *(.+?) *\) +"
2460 r'REFERENCES +(?:(?:"(.+?)")|([a-z0-9_]+)) *\( *((?:(?:"[^"]+"|[a-z0-9_]+) *(?:, *)?)+)\) *' # noqa: E501
2461 r"((?:ON (?:DELETE|UPDATE) "
2462 r"(?:SET NULL|SET DEFAULT|CASCADE|RESTRICT|NO ACTION) *)*)"
2463 r"((?:NOT +)?DEFERRABLE)?"
2464 r"(?: +INITIALLY +(DEFERRED|IMMEDIATE))?"
2465 )
2466 for match in re.finditer(FK_PATTERN, table_data, re.I):
2467 (
2468 constraint_name,
2469 constrained_columns,
2470 referred_quoted_name,
2471 referred_name,
2472 referred_columns,
2473 onupdatedelete,
2474 deferrable,
2475 initially,
2476 ) = match.group(1, 2, 3, 4, 5, 6, 7, 8)
2477 constrained_columns = list(
2478 self._find_cols_in_sig(constrained_columns)
2479 )
2480 if not referred_columns:
2481 referred_columns = constrained_columns
2482 else:
2483 referred_columns = list(
2484 self._find_cols_in_sig(referred_columns)
2485 )
2486 referred_name = referred_quoted_name or referred_name
2487 options = {}
2489 for token in re.split(r" *\bON\b *", onupdatedelete.upper()):
2490 if token.startswith("DELETE"):
2491 ondelete = token[6:].strip()
2492 if ondelete and ondelete != "NO ACTION":
2493 options["ondelete"] = ondelete
2494 elif token.startswith("UPDATE"):
2495 onupdate = token[6:].strip()
2496 if onupdate and onupdate != "NO ACTION":
2497 options["onupdate"] = onupdate
2499 if deferrable:
2500 options["deferrable"] = "NOT" not in deferrable.upper()
2501 if initially:
2502 options["initially"] = initially.upper()
2504 yield (
2505 constraint_name,
2506 constrained_columns,
2507 referred_name,
2508 referred_columns,
2509 options,
2510 )
2512 fkeys = []
2514 for (
2515 constraint_name,
2516 constrained_columns,
2517 referred_name,
2518 referred_columns,
2519 options,
2520 ) in parse_fks():
2521 sig = fk_sig(constrained_columns, referred_name, referred_columns)
2522 if sig not in keys_by_signature:
2523 util.warn(
2524 "WARNING: SQL-parsed foreign key constraint "
2525 "'%s' could not be located in PRAGMA "
2526 "foreign_keys for table %s" % (sig, table_name)
2527 )
2528 continue
2529 key = keys_by_signature.pop(sig)
2530 key["name"] = constraint_name
2531 key["options"] = options
2532 fkeys.append(key)
2533 # assume the remainders are the unnamed, inline constraints, just
2534 # use them as is as it's extremely difficult to parse inline
2535 # constraints
2536 fkeys.extend(keys_by_signature.values())
2537 if fkeys:
2538 return fkeys
2539 else:
2540 return ReflectionDefaults.foreign_keys()
2542 def _find_cols_in_sig(self, sig):
2543 for match in re.finditer(r'(?:"(.+?)")|([a-z0-9_]+)', sig, re.I):
2544 yield match.group(1) or match.group(2)
2546 @reflection.cache
2547 def get_unique_constraints(
2548 self, connection, table_name, schema=None, **kw
2549 ):
2550 auto_index_by_sig = {}
2551 for idx in self.get_indexes(
2552 connection,
2553 table_name,
2554 schema=schema,
2555 include_auto_indexes=True,
2556 **kw,
2557 ):
2558 if not idx["name"].startswith("sqlite_autoindex"):
2559 continue
2560 sig = tuple(idx["column_names"])
2561 auto_index_by_sig[sig] = idx
2563 table_data = self._get_table_sql(
2564 connection, table_name, schema=schema, **kw
2565 )
2566 unique_constraints = []
2568 def parse_uqs():
2569 if table_data is None:
2570 return
2571 UNIQUE_PATTERN = r'(?:CONSTRAINT "?(.+?)"? +)?UNIQUE *\((.+?)\)'
2572 INLINE_UNIQUE_PATTERN = (
2573 r'(?:(".+?")|(?:[\[`])?([a-z0-9_]+)(?:[\]`])?) '
2574 r"+[a-z0-9_ ]+? +UNIQUE"
2575 )
2577 for match in re.finditer(UNIQUE_PATTERN, table_data, re.I):
2578 name, cols = match.group(1, 2)
2579 yield name, list(self._find_cols_in_sig(cols))
2581 # we need to match inlines as well, as we seek to differentiate
2582 # a UNIQUE constraint from a UNIQUE INDEX, even though these
2583 # are kind of the same thing :)
2584 for match in re.finditer(INLINE_UNIQUE_PATTERN, table_data, re.I):
2585 cols = list(
2586 self._find_cols_in_sig(match.group(1) or match.group(2))
2587 )
2588 yield None, cols
2590 for name, cols in parse_uqs():
2591 sig = tuple(cols)
2592 if sig in auto_index_by_sig:
2593 auto_index_by_sig.pop(sig)
2594 parsed_constraint = {"name": name, "column_names": cols}
2595 unique_constraints.append(parsed_constraint)
2596 # NOTE: auto_index_by_sig might not be empty here,
2597 # the PRIMARY KEY may have an entry.
2598 if unique_constraints:
2599 return unique_constraints
2600 else:
2601 return ReflectionDefaults.unique_constraints()
2603 @reflection.cache
2604 def get_check_constraints(self, connection, table_name, schema=None, **kw):
2605 table_data = self._get_table_sql(
2606 connection, table_name, schema=schema, **kw
2607 )
2609 CHECK_PATTERN = r"(?:CONSTRAINT (.+) +)?" r"CHECK *\( *(.+) *\),? *"
2610 cks = []
2611 # NOTE: we aren't using re.S here because we actually are
2612 # taking advantage of each CHECK constraint being all on one
2613 # line in the table definition in order to delineate. This
2614 # necessarily makes assumptions as to how the CREATE TABLE
2615 # was emitted.
2617 for match in re.finditer(CHECK_PATTERN, table_data or "", re.I):
2618 name = match.group(1)
2620 if name:
2621 name = re.sub(r'^"|"$', "", name)
2623 cks.append({"sqltext": match.group(2), "name": name})
2624 cks.sort(key=lambda d: d["name"] or "~") # sort None as last
2625 if cks:
2626 return cks
2627 else:
2628 return ReflectionDefaults.check_constraints()
2630 @reflection.cache
2631 def get_indexes(self, connection, table_name, schema=None, **kw):
2632 pragma_indexes = self._get_table_pragma(
2633 connection, "index_list", table_name, schema=schema
2634 )
2635 indexes = []
2637 # regular expression to extract the filter predicate of a partial
2638 # index. this could fail to extract the predicate correctly on
2639 # indexes created like
2640 # CREATE INDEX i ON t (col || ') where') WHERE col <> ''
2641 # but as this function does not support expression-based indexes
2642 # this case does not occur.
2643 partial_pred_re = re.compile(r"\)\s+where\s+(.+)", re.IGNORECASE)
2645 if schema:
2646 schema_expr = "%s." % self.identifier_preparer.quote_identifier(
2647 schema
2648 )
2649 else:
2650 schema_expr = ""
2652 include_auto_indexes = kw.pop("include_auto_indexes", False)
2653 for row in pragma_indexes:
2654 # ignore implicit primary key index.
2655 # https://www.mail-archive.com/sqlite-users@sqlite.org/msg30517.html
2656 if not include_auto_indexes and row[1].startswith(
2657 "sqlite_autoindex"
2658 ):
2659 continue
2660 indexes.append(
2661 dict(
2662 name=row[1],
2663 column_names=[],
2664 unique=row[2],
2665 dialect_options={},
2666 )
2667 )
2669 # check partial indexes
2670 if len(row) >= 5 and row[4]:
2671 s = (
2672 "SELECT sql FROM %(schema)ssqlite_master "
2673 "WHERE name = ? "
2674 "AND type = 'index'" % {"schema": schema_expr}
2675 )
2676 rs = connection.exec_driver_sql(s, (row[1],))
2677 index_sql = rs.scalar()
2678 predicate_match = partial_pred_re.search(index_sql)
2679 if predicate_match is None:
2680 # unless the regex is broken this case shouldn't happen
2681 # because we know this is a partial index, so the
2682 # definition sql should match the regex
2683 util.warn(
2684 "Failed to look up filter predicate of "
2685 "partial index %s" % row[1]
2686 )
2687 else:
2688 predicate = predicate_match.group(1)
2689 indexes[-1]["dialect_options"]["sqlite_where"] = text(
2690 predicate
2691 )
2693 # loop thru unique indexes to get the column names.
2694 for idx in list(indexes):
2695 pragma_index = self._get_table_pragma(
2696 connection, "index_info", idx["name"], schema=schema
2697 )
2699 for row in pragma_index:
2700 if row[2] is None:
2701 util.warn(
2702 "Skipped unsupported reflection of "
2703 "expression-based index %s" % idx["name"]
2704 )
2705 indexes.remove(idx)
2706 break
2707 else:
2708 idx["column_names"].append(row[2])
2710 indexes.sort(key=lambda d: d["name"] or "~") # sort None as last
2711 if indexes:
2712 return indexes
2713 elif not self.has_table(connection, table_name, schema):
2714 raise exc.NoSuchTableError(
2715 f"{schema}.{table_name}" if schema else table_name
2716 )
2717 else:
2718 return ReflectionDefaults.indexes()
2720 def _is_sys_table(self, table_name):
2721 return table_name in {
2722 "sqlite_schema",
2723 "sqlite_master",
2724 "sqlite_temp_schema",
2725 "sqlite_temp_master",
2726 }
2728 @reflection.cache
2729 def _get_table_sql(self, connection, table_name, schema=None, **kw):
2730 if schema:
2731 schema_expr = "%s." % (
2732 self.identifier_preparer.quote_identifier(schema)
2733 )
2734 else:
2735 schema_expr = ""
2736 try:
2737 s = (
2738 "SELECT sql FROM "
2739 " (SELECT * FROM %(schema)ssqlite_master UNION ALL "
2740 " SELECT * FROM %(schema)ssqlite_temp_master) "
2741 "WHERE name = ? "
2742 "AND type in ('table', 'view')" % {"schema": schema_expr}
2743 )
2744 rs = connection.exec_driver_sql(s, (table_name,))
2745 except exc.DBAPIError:
2746 s = (
2747 "SELECT sql FROM %(schema)ssqlite_master "
2748 "WHERE name = ? "
2749 "AND type in ('table', 'view')" % {"schema": schema_expr}
2750 )
2751 rs = connection.exec_driver_sql(s, (table_name,))
2752 value = rs.scalar()
2753 if value is None and not self._is_sys_table(table_name):
2754 raise exc.NoSuchTableError(f"{schema_expr}{table_name}")
2755 return value
2757 def _get_table_pragma(self, connection, pragma, table_name, schema=None):
2758 quote = self.identifier_preparer.quote_identifier
2759 if schema is not None:
2760 statements = [f"PRAGMA {quote(schema)}."]
2761 else:
2762 # because PRAGMA looks in all attached databases if no schema
2763 # given, need to specify "main" schema, however since we want
2764 # 'temp' tables in the same namespace as 'main', need to run
2765 # the PRAGMA twice
2766 statements = ["PRAGMA main.", "PRAGMA temp."]
2768 qtable = quote(table_name)
2769 for statement in statements:
2770 statement = f"{statement}{pragma}({qtable})"
2771 cursor = connection.exec_driver_sql(statement)
2772 if not cursor._soft_closed:
2773 # work around SQLite issue whereby cursor.description
2774 # is blank when PRAGMA returns no rows:
2775 # https://www.sqlite.org/cvstrac/tktview?tn=1884
2776 result = cursor.fetchall()
2777 else:
2778 result = []
2779 if result:
2780 return result
2781 else:
2782 return []