codekingpro/portable-devtools
115k
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
8
9
10r"""
11.. dialect:: sqlite
12 :name: SQLite
13 :full_support: 3.36.0
14 :normal_support: 3.12+
15 :best_effort: 3.7.16+
16
17.. _sqlite_datetime:
18
19Date and Time Types
20-------------------
21
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.
31
32Ensuring Text affinity
33^^^^^^^^^^^^^^^^^^^^^^
34
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.
41
42.. seealso::
43
44 `Type Affinity <https://www.sqlite.org/datatype3.html#affinity>`_ -
45 in the SQLite documentation
46
47.. _sqlite_autoincrement:
48
49SQLite Auto Incrementing Behavior
50----------------------------------
51
52Background on SQLite's autoincrement is at: https://sqlite.org/autoinc.html
53
54Key concepts:
55
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.
59
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".
65
66Using the AUTOINCREMENT Keyword
67^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
68
69To specifically render the AUTOINCREMENT keyword on the primary key column
70when rendering DDL, add the flag ``sqlite_autoincrement=True`` to the Table
71construct::
72
73 Table('sometable', metadata,
74 Column('id', Integer, primary_key=True),
75 sqlite_autoincrement=True)
76
77Allowing autoincrement behavior SQLAlchemy types other than Integer/INTEGER
78^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
79
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.
90
91One approach to achieve this is to use :class:`.Integer` on SQLite
92only using :meth:`.TypeEngine.with_variant`::
93
94 table = Table(
95 "my_table", metadata,
96 Column("id", BigInteger().with_variant(Integer, "sqlite"), primary_key=True)
97 )
98
99Another is to use a subclass of :class:`.BigInteger` that overrides its DDL
100name to be ``INTEGER`` when compiled against SQLite::
101
102 from sqlalchemy import BigInteger
103 from sqlalchemy.ext.compiler import compiles
104
105 class SLBigInteger(BigInteger):
106 pass
107
108 @compiles(SLBigInteger, 'sqlite')
109 def bi_c(element, compiler, **kw):
110 return "INTEGER"
111
112 @compiles(SLBigInteger)
113 def bi_c(element, compiler, **kw):
114 return compiler.visit_BIGINT(element, **kw)
115
116
117 table = Table(
118 "my_table", metadata,
119 Column("id", SLBigInteger(), primary_key=True)
120 )
121
122.. seealso::
123
124 :meth:`.TypeEngine.with_variant`
125
126 :ref:`sqlalchemy.ext.compiler_toplevel`
127
128 `Datatypes In SQLite Version 3 <https://sqlite.org/datatype3.html>`_
129
130.. _sqlite_concurrency:
131
132Database Locking Behavior / Concurrency
133---------------------------------------
134
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.
140
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.
149
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.
156
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.
164
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.
169
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.
173
174.. _sqlite_isolation_level:
175
176Transaction Isolation Level / Autocommit
177----------------------------------------
178
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``.
185
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.
193
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.
198
199.. versionadded:: 1.3.16 added support for SQLite AUTOCOMMIT isolation level
200 when using the pysqlite / sqlite3 SQLite driver.
201
202
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.
212
213.. warning::
214
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.
219
220.. seealso::
221
222 :ref:`dbapi_autocommit`
223
224INSERT/UPDATE/DELETE...RETURNING
225---------------------------------
226
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.
233
234To specify an explicit ``RETURNING`` clause, use the
235:meth:`._UpdateBase.returning` method on a per-statement basis::
236
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())
244
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())
253
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())
261
262.. versionadded:: 2.0 Added support for SQLite RETURNING
263
264SAVEPOINT Support
265----------------------------
266
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.
272
273.. warning::
274
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.
280
281Transactional DDL
282----------------------------
283
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.
288
289.. warning::
290
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.
296
297.. _sqlite_foreign_keys:
298
299Foreign Key Support
300-------------------
301
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.
305
306Constraint checking on SQLite has three prerequisites:
307
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`.
314
315SQLAlchemy allows for the ``PRAGMA`` statement to be emitted automatically for
316new connections through the usage of events::
317
318 from sqlalchemy.engine import Engine
319 from sqlalchemy import event
320
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()
326
327.. warning::
328
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.
335
336.. seealso::
337
338 `SQLite Foreign Key Support <https://www.sqlite.org/foreignkeys.html>`_
339 - on the SQLite web site.
340
341 :ref:`event_toplevel` - SQLAlchemy event API.
342
343 :ref:`use_alter` - more information on SQLAlchemy's facilities for handling
344 mutually-dependent foreign key constraints.
345
346.. _sqlite_on_conflict_ddl:
347
348ON CONFLICT support for constraints
349-----------------------------------
350
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`.
354
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.
368
369.. seealso::
370
371 `ON CONFLICT <https://www.sqlite.org/lang_conflict.html>`_ - in the SQLite
372 documentation
373
374.. versionadded:: 1.3
375
376
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::
381
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 )
388
389The above renders CREATE TABLE DDL as::
390
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 )
397
398
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::
404
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 )
411
412rendering::
413
414 CREATE TABLE some_table (
415 id INTEGER NOT NULL,
416 data INTEGER,
417 PRIMARY KEY (id),
418 UNIQUE (data) ON CONFLICT IGNORE
419 )
420
421To apply the FAIL algorithm for a NOT NULL constraint,
422``sqlite_on_conflict_not_null`` is used::
423
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 )
430
431this renders the column inline ON CONFLICT phrase::
432
433 CREATE TABLE some_table (
434 id INTEGER NOT NULL,
435 data INTEGER NOT NULL ON CONFLICT FAIL,
436 PRIMARY KEY (id)
437 )
438
439
440Similarly, for an inline primary key, use ``sqlite_on_conflict_primary_key``::
441
442 some_table = Table(
443 'some_table', metadata,
444 Column('id', Integer, primary_key=True,
445 sqlite_on_conflict_primary_key='FAIL')
446 )
447
448SQLAlchemy renders the PRIMARY KEY constraint separately, so the conflict
449resolution algorithm is applied to the constraint itself::
450
451 CREATE TABLE some_table (
452 id INTEGER NOT NULL,
453 PRIMARY KEY (id) ON CONFLICT FAIL
454 )
455
456.. _sqlite_on_conflict_insert:
457
458INSERT...ON CONFLICT (Upsert)
459-----------------------------------
460
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`.
464
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.
472
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.
476
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`:
481
482.. sourcecode:: pycon+sql
483
484 >>> from sqlalchemy.dialects.sqlite import insert
485
486 >>> insert_stmt = insert(my_table).values(
487 ... id='some_existing_id',
488 ... data='inserted value')
489
490 >>> do_update_stmt = insert_stmt.on_conflict_do_update(
491 ... index_elements=['id'],
492 ... set_=dict(data='updated value')
493 ... )
494
495 >>> print(do_update_stmt)
496 {printsql}INSERT INTO my_table (id, data) VALUES (?, ?)
497 ON CONFLICT (id) DO UPDATE SET data = ?{stop}
498
499 >>> do_nothing_stmt = insert_stmt.on_conflict_do_nothing(
500 ... index_elements=['id']
501 ... )
502
503 >>> print(do_nothing_stmt)
504 {printsql}INSERT INTO my_table (id, data) VALUES (?, ?)
505 ON CONFLICT (id) DO NOTHING
506
507.. versionadded:: 1.4
508
509.. seealso::
510
511 `Upsert
512 <https://sqlite.org/lang_UPSERT.html>`_
513 - in the SQLite documentation.
514
515
516Specifying the Target
517^^^^^^^^^^^^^^^^^^^^^
518
519Both methods supply the "target" of the conflict using column inference:
520
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.
525
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:
529
530 .. sourcecode:: pycon+sql
531
532 >>> stmt = insert(my_table).values(user_email='a@b.com', data='inserted data')
533
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 ... )
539
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
545
546The SET Clause
547^^^^^^^^^^^^^^^
548
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:
555
556.. sourcecode:: pycon+sql
557
558 >>> stmt = insert(my_table).values(id='some_id', data='inserted value')
559
560 >>> do_update_stmt = stmt.on_conflict_do_update(
561 ... index_elements=['id'],
562 ... set_=dict(data='updated value')
563 ... )
564
565 >>> print(do_update_stmt)
566 {printsql}INSERT INTO my_table (id, data) VALUES (?, ?)
567 ON CONFLICT (id) DO UPDATE SET data = ?
568
569.. warning::
570
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.
577
578Updating using the Excluded INSERT Values
579^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
580
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:
586
587.. sourcecode:: pycon+sql
588
589 >>> stmt = insert(my_table).values(
590 ... id='some_id',
591 ... data='inserted value',
592 ... author='jlh'
593 ... )
594
595 >>> do_update_stmt = stmt.on_conflict_do_update(
596 ... index_elements=['id'],
597 ... set_=dict(data='updated value', author=stmt.excluded.author)
598 ... )
599
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
603
604Additional WHERE Criteria
605^^^^^^^^^^^^^^^^^^^^^^^^^
606
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:
610
611.. sourcecode:: pycon+sql
612
613 >>> stmt = insert(my_table).values(
614 ... id='some_id',
615 ... data='inserted value',
616 ... author='jlh'
617 ... )
618
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 = ?
628
629
630Skipping Rows with DO NOTHING
631^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
632
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:
636
637.. sourcecode:: pycon+sql
638
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
643
644
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:
648
649.. sourcecode:: pycon+sql
650
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
655
656.. _sqlite_type_reflection:
657
658Type Reflection
659---------------
660
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.
666
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.
674
675The provided typemap will make direct associations from an exact string
676name match for the following types:
677
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`
689
690When a type name does not match one of the above types, the "type affinity"
691lookup is used instead:
692
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.
702
703.. _sqlite_partial_index:
704
705Partial Indexes
706---------------
707
708A partial index, e.g. one which uses a WHERE clause, can be specified
709with the DDL system using the argument ``sqlite_where``::
710
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))
714
715The index will be rendered at create time as::
716
717 CREATE INDEX test_idx1 ON testtbl (data)
718 WHERE data > 5 AND data < 10
719
720.. _sqlite_dotted_column_names:
721
722Dotted Column Names
723-------------------
724
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.
730
731The bug, entirely outside of SQLAlchemy, can be illustrated thusly::
732
733 import sqlite3
734
735 assert sqlite3.sqlite_version_info < (3, 10, 0), "bug is fixed in this version"
736
737 conn = sqlite3.connect(":memory:")
738 cursor = conn.cursor()
739
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)")
743
744 cursor.execute("select x.a, x.b from x")
745 assert [c[0] for c in cursor.description] == ['a', 'b']
746
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]
754
755The second assertion fails::
756
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']
761
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.
765
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::
769
770
771 from sqlalchemy import create_engine
772
773 eng = create_engine("sqlite://")
774 conn = eng.connect()
775
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)")
779
780 result = conn.exec_driver_sql("select x.a, x.b from x")
781 assert result.keys() == ["a", "b"]
782
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"]
789
790Note that above, even though SQLAlchemy filters out the dots, *both
791names are still addressable*::
792
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
802
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::
810
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"]
817
818or on a per-:class:`_engine.Engine` basis::
819
820 engine = create_engine("sqlite://", execution_options={"sqlite_raw_colnames": True})
821
822When using the per-:class:`_engine.Engine` execution option, note that
823**Core and ORM queries that use UNION may not function properly**.
824
825SQLite-specific table options
826-----------------------------
827
828One option for CREATE TABLE is supported directly by the SQLite
829dialect in conjunction with the :class:`_schema.Table` construct:
830
831* ``WITHOUT ROWID``::
832
833 Table("some_table", metadata, ..., sqlite_with_rowid=False)
834
835.. seealso::
836
837 `SQLite CREATE TABLE options
838 <https://www.sqlite.org/lang_createtable.html>`_
839
840
841.. _sqlite_include_internal:
842
843Reflecting internal schema tables
844----------------------------------
845
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`.
854
855.. versionadded:: 2.0 Added the ``sqlite_include_internal=True`` parameter.
856 Previously, these tables were not ignored by SQLAlchemy reflection
857 methods.
858
859.. note::
860
861 The ``sqlite_include_internal`` parameter does not refer to the
862 "system" tables that are present in schemas such as ``sqlite_master``.
863
864.. seealso::
865
866 `SQLite Internal Schema Objects <https://www.sqlite.org/fileformat2.html#intschema>`_ - in the SQLite
867 documentation.
868
869""" # noqa
870from __future__ import annotations
871
872import datetime
873import numbers
874import re
875from typing import Optional
876
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
908
909
910class _SQliteJson(JSON):
911 def result_processor(self, dialect, coltype):
912 default_processor = super().result_processor(dialect, coltype)
913
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
922
923 return process
924
925
926class _DateTimeMixin:
927 _reg = None
928 _storage_format = None
929
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
936
937 @property
938 def format_is_text_affinity(self):
939 """return True if the storage format will automatically imply
940 a TEXT affinity.
941
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.
946
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))
958
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)
966
967 def literal_processor(self, dialect):
968 bp = self.bind_processor(dialect)
969
970 def process(value):
971 return "'%s'" % bp(value)
972
973 return process
974
975
976class DATETIME(_DateTimeMixin, sqltypes.DateTime):
977 r"""Represent a Python datetime object in SQLite using a string.
978
979 The default string storage format is::
980
981 "%(year)04d-%(month)02d-%(day)02d %(hour)02d:%(minute)02d:%(second)02d.%(microsecond)06d"
982
983 e.g.::
984
985 2021-03-15 12:05:57.105542
986
987 The incoming storage format is by default parsed using the
988 Python ``datetime.fromisoformat()`` function.
989
990 .. versionchanged:: 2.0 ``datetime.fromisoformat()`` is used for default
991 datetime string parsing.
992
993 The storage format can be customized to some degree using the
994 ``storage_format`` and ``regexp`` parameters, such as::
995
996 import re
997 from sqlalchemy.dialects.sqlite import DATETIME
998
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 )
1003
1004 :param storage_format: format string which will be applied to the dict
1005 with keys year, month, day, hour, minute, second, and microsecond.
1006
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))``.
1014
1015 """ # noqa
1016
1017 _storage_format = (
1018 "%(year)04d-%(month)02d-%(day)02d "
1019 "%(hour)02d:%(minute)02d:%(second)02d.%(microsecond)06d"
1020 )
1021
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 )
1038
1039 def bind_processor(self, dialect):
1040 datetime_datetime = datetime.datetime
1041 datetime_date = datetime.date
1042 format_ = self._storage_format
1043
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 )
1072
1073 return process
1074
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
1082
1083
1084class DATE(_DateTimeMixin, sqltypes.Date):
1085 r"""Represent a Python date object in SQLite using a string.
1086
1087 The default string storage format is::
1088
1089 "%(year)04d-%(month)02d-%(day)02d"
1090
1091 e.g.::
1092
1093 2011-03-15
1094
1095 The incoming storage format is by default parsed using the
1096 Python ``date.fromisoformat()`` function.
1097
1098 .. versionchanged:: 2.0 ``date.fromisoformat()`` is used for default
1099 date string parsing.
1100
1101
1102 The storage format can be customized to some degree using the
1103 ``storage_format`` and ``regexp`` parameters, such as::
1104
1105 import re
1106 from sqlalchemy.dialects.sqlite import DATE
1107
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 )
1112
1113 :param storage_format: format string which will be applied to the
1114 dict with keys year, month, and day.
1115
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))``.
1123
1124 """
1125
1126 _storage_format = "%(year)04d-%(month)02d-%(day)02d"
1127
1128 def bind_processor(self, dialect):
1129 datetime_date = datetime.date
1130 format_ = self._storage_format
1131
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 )
1146
1147 return process
1148
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
1156
1157
1158class TIME(_DateTimeMixin, sqltypes.Time):
1159 r"""Represent a Python time object in SQLite using a string.
1160
1161 The default string storage format is::
1162
1163 "%(hour)02d:%(minute)02d:%(second)02d.%(microsecond)06d"
1164
1165 e.g.::
1166
1167 12:05:57.10558
1168
1169 The incoming storage format is by default parsed using the
1170 Python ``time.fromisoformat()`` function.
1171
1172 .. versionchanged:: 2.0 ``time.fromisoformat()`` is used for default
1173 time string parsing.
1174
1175 The storage format can be customized to some degree using the
1176 ``storage_format`` and ``regexp`` parameters, such as::
1177
1178 import re
1179 from sqlalchemy.dialects.sqlite import TIME
1180
1181 t = TIME(storage_format="%(hour)02d-%(minute)02d-"
1182 "%(second)02d-%(microsecond)06d",
1183 regexp=re.compile("(\d+)-(\d+)-(\d+)-(?:-(\d+))?")
1184 )
1185
1186 :param storage_format: format string which will be applied to the dict
1187 with keys hour, minute, second, and microsecond.
1188
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))``.
1195
1196 """
1197
1198 _storage_format = "%(hour)02d:%(minute)02d:%(second)02d.%(microsecond)06d"
1199
1200 def __init__(self, *args, **kwargs):