U
|
¸ý°d± ã @ s d Z ddlmZ ddlZddlZddlZddlmZ ddlm Z ddlm
|
Z
|
ddlm Z d d
|
l m Z d d l mZ d d l mZ d d l mZ d dl mZ d dl mZ d dlmZ d dlmZ d dlmZ d dlmZ d dlmZ d dlm Z d dlm Z d dlm Z d dlm Z d d lmZ d dlm Z d dlm!Z! d dlm"Z" d d lm#Z# d d lm$Z$ d d lm%Z% d d lm&Z& d d lm'Z' d d!lm(Z( d d"lm)Z) d d#lm*Z* d d$lm+Z+ G d%d& d&e Z,G d'd( d(Z-G d)d* d*e-ej.Z/G d+d, d,e-ej0Z1G d-d. d.e-ej2Z3ej0e1ej.e/ej e,ej j
|
e
|
ej j e ej2e3iZ4ej5ej ej!ej!ej"ej1ej1ej/ej/ej6ej#ej$ej%ej%e ej&ej'ej(ej)ej3ej3ej*ej+ej7ej8d/Z9G d0d1 d1e j:Z;G d2d3 d3e j<Z=G d4d5 d5e j>Z?G d6d7 d7e j@ZAG d8d9 d9ejBZCG d:d; d;ejDZEdS )<a {
|
.. dialect:: sqlite
|
:name: SQLite
|
:full_support: 3.21, 3.28+
|
:normal_support: 3.12+
|
:best_effort: 3.7.16+
|
|
.. _sqlite_datetime:
|
|
Date and Time Types
|
-------------------
|
|
SQLite does not have built-in DATE, TIME, or DATETIME types, and pysqlite does
|
not provide out of the box functionality for translating values between Python
|
`datetime` objects and a SQLite-supported format. SQLAlchemy's own
|
:class:`~sqlalchemy.types.DateTime` and related types provide date formatting
|
and parsing functionality when SQLite is used. The implementation classes are
|
:class:`_sqlite.DATETIME`, :class:`_sqlite.DATE` and :class:`_sqlite.TIME`.
|
These types represent dates and times as ISO formatted strings, which also
|
nicely support ordering. There's no reliance on typical "libc" internals for
|
these functions so historical dates are fully supported.
|
|
Ensuring Text affinity
|
^^^^^^^^^^^^^^^^^^^^^^
|
|
The DDL rendered for these types is the standard ``DATE``, ``TIME``
|
and ``DATETIME`` indicators. However, custom storage formats can also be
|
applied to these types. When the
|
storage format is detected as containing no alpha characters, the DDL for
|
these types is rendered as ``DATE_CHAR``, ``TIME_CHAR``, and ``DATETIME_CHAR``,
|
so that the column continues to have textual affinity.
|
|
.. seealso::
|
|
`Type Affinity <https://www.sqlite.org/datatype3.html#affinity>`_ -
|
in the SQLite documentation
|
|
.. _sqlite_autoincrement:
|
|
SQLite Auto Incrementing Behavior
|
----------------------------------
|
|
Background on SQLite's autoincrement is at: https://sqlite.org/autoinc.html
|
|
Key concepts:
|
|
* SQLite has an implicit "auto increment" feature that takes place for any
|
non-composite primary-key column that is specifically created using
|
"INTEGER PRIMARY KEY" for the type + primary key.
|
|
* SQLite also has an explicit "AUTOINCREMENT" keyword, that is **not**
|
equivalent to the implicit autoincrement feature; this keyword is not
|
recommended for general use. SQLAlchemy does not render this keyword
|
unless a special SQLite-specific directive is used (see below). However,
|
it still requires that the column's type is named "INTEGER".
|
|
Using the AUTOINCREMENT Keyword
|
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
|
|
To specifically render the AUTOINCREMENT keyword on the primary key column
|
when rendering DDL, add the flag ``sqlite_autoincrement=True`` to the Table
|
construct::
|
|
Table('sometable', metadata,
|
Column('id', Integer, primary_key=True),
|
sqlite_autoincrement=True)
|
|
Allowing autoincrement behavior SQLAlchemy types other than Integer/INTEGER
|
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
|
|
SQLite's typing model is based on naming conventions. Among other things, this
|
means that any type name which contains the substring ``"INT"`` will be
|
determined to be of "integer affinity". A type named ``"BIGINT"``,
|
``"SPECIAL_INT"`` or even ``"XYZINTQPR"``, will be considered by SQLite to be
|
of "integer" affinity. However, **the SQLite autoincrement feature, whether
|
implicitly or explicitly enabled, requires that the name of the column's type
|
is exactly the string "INTEGER"**. Therefore, if an application uses a type
|
like :class:`.BigInteger` for a primary key, on SQLite this type will need to
|
be rendered as the name ``"INTEGER"`` when emitting the initial ``CREATE
|
TABLE`` statement in order for the autoincrement behavior to be available.
|
|
One approach to achieve this is to use :class:`.Integer` on SQLite
|
only using :meth:`.TypeEngine.with_variant`::
|
|
table = Table(
|
"my_table", metadata,
|
Column("id", BigInteger().with_variant(Integer, "sqlite"), primary_key=True)
|
)
|
|
Another is to use a subclass of :class:`.BigInteger` that overrides its DDL
|
name to be ``INTEGER`` when compiled against SQLite::
|
|
from sqlalchemy import BigInteger
|
from sqlalchemy.ext.compiler import compiles
|
|
class SLBigInteger(BigInteger):
|
pass
|
|
@compiles(SLBigInteger, 'sqlite')
|
def bi_c(element, compiler, **kw):
|
return "INTEGER"
|
|
@compiles(SLBigInteger)
|
def bi_c(element, compiler, **kw):
|
return compiler.visit_BIGINT(element, **kw)
|
|
|
table = Table(
|
"my_table", metadata,
|
Column("id", SLBigInteger(), primary_key=True)
|
)
|
|
.. seealso::
|
|
:meth:`.TypeEngine.with_variant`
|
|
:ref:`sqlalchemy.ext.compiler_toplevel`
|
|
`Datatypes In SQLite Version 3 <https://sqlite.org/datatype3.html>`_
|
|
.. _sqlite_concurrency:
|
|
Database Locking Behavior / Concurrency
|
---------------------------------------
|
|
SQLite is not designed for a high level of write concurrency. The database
|
itself, being a file, is locked completely during write operations within
|
transactions, meaning exactly one "connection" (in reality a file handle)
|
has exclusive access to the database during this period - all other
|
"connections" will be blocked during this time.
|
|
The Python DBAPI specification also calls for a connection model that is
|
always in a transaction; there is no ``connection.begin()`` method,
|
only ``connection.commit()`` and ``connection.rollback()``, upon which a
|
new transaction is to be begun immediately. This may seem to imply
|
that the SQLite driver would in theory allow only a single filehandle on a
|
particular database file at any time; however, there are several
|
factors both within SQLite itself as well as within the pysqlite driver
|
which loosen this restriction significantly.
|
|
However, no matter what locking modes are used, SQLite will still always
|
lock the database file once a transaction is started and DML (e.g. INSERT,
|
UPDATE, DELETE) has at least been emitted, and this will block
|
other transactions at least at the point that they also attempt to emit DML.
|
By default, the length of time on this block is very short before it times out
|
with an error.
|
|
This behavior becomes more critical when used in conjunction with the
|
SQLAlchemy ORM. SQLAlchemy's :class:`.Session` object by default runs
|
within a transaction, and with its autoflush model, may emit DML preceding
|
any SELECT statement. This may lead to a SQLite database that locks
|
more quickly than is expected. The locking mode of SQLite and the pysqlite
|
driver can be manipulated to some degree, however it should be noted that
|
achieving a high degree of write-concurrency with SQLite is a losing battle.
|
|
For more information on SQLite's lack of write concurrency by design, please
|
see
|
`Situations Where Another RDBMS May Work Better - High Concurrency
|
<https://www.sqlite.org/whentouse.html>`_ near the bottom of the page.
|
|
The following subsections introduce areas that are impacted by SQLite's
|
file-based architecture and additionally will usually require workarounds to
|
work when using the pysqlite driver.
|
|
.. _sqlite_isolation_level:
|
|
Transaction Isolation Level / Autocommit
|
----------------------------------------
|
|
SQLite supports "transaction isolation" in a non-standard way, along two
|
axes. One is that of the
|
`PRAGMA read_uncommitted <https://www.sqlite.org/pragma.html#pragma_read_uncommitted>`_
|
instruction. This setting can essentially switch SQLite between its
|
default mode of ``SERIALIZABLE`` isolation, and a "dirty read" isolation
|
mode normally referred to as ``READ UNCOMMITTED``.
|
|
SQLAlchemy ties into this PRAGMA statement using the
|
:paramref:`_sa.create_engine.isolation_level` parameter of
|
:func:`_sa.create_engine`.
|
Valid values for this parameter when used with SQLite are ``"SERIALIZABLE"``
|
and ``"READ UNCOMMITTED"`` corresponding to a value of 0 and 1, respectively.
|
SQLite defaults to ``SERIALIZABLE``, however its behavior is impacted by
|
the pysqlite driver's default behavior.
|
|
When using the pysqlite driver, the ``"AUTOCOMMIT"`` isolation level is also
|
available, which will alter the pysqlite connection using the ``.isolation_level``
|
attribute on the DBAPI connection and set it to None for the duration
|
of the setting.
|
|
.. versionadded:: 1.3.16 added support for SQLite AUTOCOMMIT isolation level
|
when using the pysqlite / sqlite3 SQLite driver.
|
|
|
The other axis along which SQLite's transactional locking is impacted is
|
via the nature of the ``BEGIN`` statement used. The three varieties
|
are "deferred", "immediate", and "exclusive", as described at
|
`BEGIN TRANSACTION <https://sqlite.org/lang_transaction.html>`_. A straight
|
``BEGIN`` statement uses the "deferred" mode, where the database file is
|
not locked until the first read or write operation, and read access remains
|
open to other transactions until the first write operation. But again,
|
it is critical to note that the pysqlite driver interferes with this behavior
|
by *not even emitting BEGIN* until the first write operation.
|
|
.. warning::
|
|
SQLite's transactional scope is impacted by unresolved
|
issues in the pysqlite driver, which defers BEGIN statements to a greater
|
degree than is often feasible. See the section :ref:`pysqlite_serializable`
|
for techniques to work around this behavior.
|
|
.. seealso::
|
|
:ref:`dbapi_autocommit`
|
|
INSERT/UPDATE/DELETE...RETURNING
|
---------------------------------
|
|
The SQLite dialect supports SQLite 3.35's ``INSERT|UPDATE|DELETE..RETURNING``
|
syntax. ``INSERT..RETURNING`` may be used
|
automatically in some cases in order to fetch newly generated identifiers in
|
place of the traditional approach of using ``cursor.lastrowid``, however
|
``cursor.lastrowid`` is currently still preferred for simple single-statement
|
cases for its better performance.
|
|
To specify an explicit ``RETURNING`` clause, use the
|
:meth:`._UpdateBase.returning` method on a per-statement basis::
|
|
# INSERT..RETURNING
|
result = connection.execute(
|
table.insert().
|
values(name='foo').
|
returning(table.c.col1, table.c.col2)
|
)
|
print(result.all())
|
|
# UPDATE..RETURNING
|
result = connection.execute(
|
table.update().
|
where(table.c.name=='foo').
|
values(name='bar').
|
returning(table.c.col1, table.c.col2)
|
)
|
print(result.all())
|
|
# DELETE..RETURNING
|
result = connection.execute(
|
table.delete().
|
where(table.c.name=='foo').
|
returning(table.c.col1, table.c.col2)
|
)
|
print(result.all())
|
|
.. versionadded:: 2.0 Added support for SQLite RETURNING
|
|
SAVEPOINT Support
|
----------------------------
|
|
SQLite supports SAVEPOINTs, which only function once a transaction is
|
begun. SQLAlchemy's SAVEPOINT support is available using the
|
:meth:`_engine.Connection.begin_nested` method at the Core level, and
|
:meth:`.Session.begin_nested` at the ORM level. However, SAVEPOINTs
|
won't work at all with pysqlite unless workarounds are taken.
|
|
.. warning::
|
|
SQLite's SAVEPOINT feature is impacted by unresolved
|
issues in the pysqlite driver, which defers BEGIN statements to a greater
|
degree than is often feasible. See the section :ref:`pysqlite_serializable`
|
for techniques to work around this behavior.
|
|
Transactional DDL
|
----------------------------
|
|
The SQLite database supports transactional :term:`DDL` as well.
|
In this case, the pysqlite driver is not only failing to start transactions,
|
it also is ending any existing transaction when DDL is detected, so again,
|
workarounds are required.
|
|
.. warning::
|
|
SQLite's transactional DDL is impacted by unresolved issues
|
in the pysqlite driver, which fails to emit BEGIN and additionally
|
forces a COMMIT to cancel any transaction when DDL is encountered.
|
See the section :ref:`pysqlite_serializable`
|
for techniques to work around this behavior.
|
|
.. _sqlite_foreign_keys:
|
|
Foreign Key Support
|
-------------------
|
|
SQLite supports FOREIGN KEY syntax when emitting CREATE statements for tables,
|
however by default these constraints have no effect on the operation of the
|
table.
|
|
Constraint checking on SQLite has three prerequisites:
|
|
* At least version 3.6.19 of SQLite must be in use
|
* The SQLite library must be compiled *without* the SQLITE_OMIT_FOREIGN_KEY
|
or SQLITE_OMIT_TRIGGER symbols enabled.
|
* The ``PRAGMA foreign_keys = ON`` statement must be emitted on all
|
connections before use -- including the initial call to
|
:meth:`sqlalchemy.schema.MetaData.create_all`.
|
|
SQLAlchemy allows for the ``PRAGMA`` statement to be emitted automatically for
|
new connections through the usage of events::
|
|
from sqlalchemy.engine import Engine
|
from sqlalchemy import event
|
|
@event.listens_for(Engine, "connect")
|
def set_sqlite_pragma(dbapi_connection, connection_record):
|
cursor = dbapi_connection.cursor()
|
cursor.execute("PRAGMA foreign_keys=ON")
|
cursor.close()
|
|
.. warning::
|
|
When SQLite foreign keys are enabled, it is **not possible**
|
to emit CREATE or DROP statements for tables that contain
|
mutually-dependent foreign key constraints;
|
to emit the DDL for these tables requires that ALTER TABLE be used to
|
create or drop these constraints separately, for which SQLite has
|
no support.
|
|
.. seealso::
|
|
`SQLite Foreign Key Support <https://www.sqlite.org/foreignkeys.html>`_
|
- on the SQLite web site.
|
|
:ref:`event_toplevel` - SQLAlchemy event API.
|
|
:ref:`use_alter` - more information on SQLAlchemy's facilities for handling
|
mutually-dependent foreign key constraints.
|
|
.. _sqlite_on_conflict_ddl:
|
|
ON CONFLICT support for constraints
|
-----------------------------------
|
|
.. seealso:: This section describes the :term:`DDL` version of "ON CONFLICT" for
|
SQLite, which occurs within a CREATE TABLE statement. For "ON CONFLICT" as
|
applied to an INSERT statement, see :ref:`sqlite_on_conflict_insert`.
|
|
SQLite supports a non-standard DDL clause known as ON CONFLICT which can be applied
|
to primary key, unique, check, and not null constraints. In DDL, it is
|
rendered either within the "CONSTRAINT" clause or within the column definition
|
itself depending on the location of the target constraint. To render this
|
clause within DDL, the extension parameter ``sqlite_on_conflict`` can be
|
specified with a string conflict resolution algorithm within the
|
:class:`.PrimaryKeyConstraint`, :class:`.UniqueConstraint`,
|
:class:`.CheckConstraint` objects. Within the :class:`_schema.Column` object,
|
there
|
are individual parameters ``sqlite_on_conflict_not_null``,
|
``sqlite_on_conflict_primary_key``, ``sqlite_on_conflict_unique`` which each
|
correspond to the three types of relevant constraint types that can be
|
indicated from a :class:`_schema.Column` object.
|
|
.. seealso::
|
|
`ON CONFLICT <https://www.sqlite.org/lang_conflict.html>`_ - in the SQLite
|
documentation
|
|
.. versionadded:: 1.3
|
|
|
The ``sqlite_on_conflict`` parameters accept a string argument which is just
|
the resolution name to be chosen, which on SQLite can be one of ROLLBACK,
|
ABORT, FAIL, IGNORE, and REPLACE. For example, to add a UNIQUE constraint
|
that specifies the IGNORE algorithm::
|
|
some_table = Table(
|
'some_table', metadata,
|
Column('id', Integer, primary_key=True),
|
Column('data', Integer),
|
UniqueConstraint('id', 'data', sqlite_on_conflict='IGNORE')
|
)
|
|
The above renders CREATE TABLE DDL as::
|
|
CREATE TABLE some_table (
|
id INTEGER NOT NULL,
|
data INTEGER,
|
PRIMARY KEY (id),
|
UNIQUE (id, data) ON CONFLICT IGNORE
|
)
|
|
|
When using the :paramref:`_schema.Column.unique`
|
flag to add a UNIQUE constraint
|
to a single column, the ``sqlite_on_conflict_unique`` parameter can
|
be added to the :class:`_schema.Column` as well, which will be added to the
|
UNIQUE constraint in the DDL::
|
|
some_table = Table(
|
'some_table', metadata,
|
Column('id', Integer, primary_key=True),
|
Column('data', Integer, unique=True,
|
sqlite_on_conflict_unique='IGNORE')
|
)
|
|
rendering::
|
|
CREATE TABLE some_table (
|
id INTEGER NOT NULL,
|
data INTEGER,
|
PRIMARY KEY (id),
|
UNIQUE (data) ON CONFLICT IGNORE
|
)
|
|
To apply the FAIL algorithm for a NOT NULL constraint,
|
``sqlite_on_conflict_not_null`` is used::
|
|
some_table = Table(
|
'some_table', metadata,
|
Column('id', Integer, primary_key=True),
|
Column('data', Integer, nullable=False,
|
sqlite_on_conflict_not_null='FAIL')
|
)
|
|
this renders the column inline ON CONFLICT phrase::
|
|
CREATE TABLE some_table (
|
id INTEGER NOT NULL,
|
data INTEGER NOT NULL ON CONFLICT FAIL,
|
PRIMARY KEY (id)
|
)
|
|
|
Similarly, for an inline primary key, use ``sqlite_on_conflict_primary_key``::
|
|
some_table = Table(
|
'some_table', metadata,
|
Column('id', Integer, primary_key=True,
|
sqlite_on_conflict_primary_key='FAIL')
|
)
|
|
SQLAlchemy renders the PRIMARY KEY constraint separately, so the conflict
|
resolution algorithm is applied to the constraint itself::
|
|
CREATE TABLE some_table (
|
id INTEGER NOT NULL,
|
PRIMARY KEY (id) ON CONFLICT FAIL
|
)
|
|
.. _sqlite_on_conflict_insert:
|
|
INSERT...ON CONFLICT (Upsert)
|
-----------------------------------
|
|
.. seealso:: This section describes the :term:`DML` version of "ON CONFLICT" for
|
SQLite, which occurs within an INSERT statement. For "ON CONFLICT" as
|
applied to a CREATE TABLE statement, see :ref:`sqlite_on_conflict_ddl`.
|
|
From version 3.24.0 onwards, SQLite supports "upserts" (update or insert)
|
of rows into a table via the ``ON CONFLICT`` clause of the ``INSERT``
|
statement. A candidate row will only be inserted if that row does not violate
|
any unique or primary key constraints. In the case of a unique constraint violation, a
|
secondary action can occur which can be either "DO UPDATE", indicating that
|
the data in the target row should be updated, or "DO NOTHING", which indicates
|
to silently skip this row.
|
|
Conflicts are determined using columns that are part of existing unique
|
constraints and indexes. These constraints are identified by stating the
|
columns and conditions that comprise the indexes.
|
|
SQLAlchemy provides ``ON CONFLICT`` support via the SQLite-specific
|
:func:`_sqlite.insert()` function, which provides
|
the generative methods :meth:`_sqlite.Insert.on_conflict_do_update`
|
and :meth:`_sqlite.Insert.on_conflict_do_nothing`:
|
|
.. sourcecode:: pycon+sql
|
|
>>> from sqlalchemy.dialects.sqlite import insert
|
|
>>> insert_stmt = insert(my_table).values(
|
... id='some_existing_id',
|
... data='inserted value')
|
|
>>> do_update_stmt = insert_stmt.on_conflict_do_update(
|
... index_elements=['id'],
|
... set_=dict(data='updated value')
|
... )
|
|
>>> print(do_update_stmt)
|
{printsql}INSERT INTO my_table (id, data) VALUES (?, ?)
|
ON CONFLICT (id) DO UPDATE SET data = ?{stop}
|
|
>>> do_nothing_stmt = insert_stmt.on_conflict_do_nothing(
|
... index_elements=['id']
|
... )
|
|
>>> print(do_nothing_stmt)
|
{printsql}INSERT INTO my_table (id, data) VALUES (?, ?)
|
ON CONFLICT (id) DO NOTHING
|
|
.. versionadded:: 1.4
|
|
.. seealso::
|
|
`Upsert
|
<https://sqlite.org/lang_UPSERT.html>`_
|
- in the SQLite documentation.
|
|
|
Specifying the Target
|
^^^^^^^^^^^^^^^^^^^^^
|
|
Both methods supply the "target" of the conflict using column inference:
|
|
* The :paramref:`_sqlite.Insert.on_conflict_do_update.index_elements` argument
|
specifies a sequence containing string column names, :class:`_schema.Column`
|
objects, and/or SQL expression elements, which would identify a unique index
|
or unique constraint.
|
|
* When using :paramref:`_sqlite.Insert.on_conflict_do_update.index_elements`
|
to infer an index, a partial index can be inferred by also specifying the
|
:paramref:`_sqlite.Insert.on_conflict_do_update.index_where` parameter:
|
|
.. sourcecode:: pycon+sql
|
|
>>> stmt = insert(my_table).values(user_email='a@b.com', data='inserted data')
|
|
>>> do_update_stmt = stmt.on_conflict_do_update(
|
... index_elements=[my_table.c.user_email],
|
... index_where=my_table.c.user_email.like('%@gmail.com'),
|
... set_=dict(data=stmt.excluded.data)
|
... )
|
|
>>> print(do_update_stmt)
|
{printsql}INSERT INTO my_table (data, user_email) VALUES (?, ?)
|
ON CONFLICT (user_email)
|
WHERE user_email LIKE '%@gmail.com'
|
DO UPDATE SET data = excluded.data
|
|
The SET Clause
|
^^^^^^^^^^^^^^^
|
|
``ON CONFLICT...DO UPDATE`` is used to perform an update of the already
|
existing row, using any combination of new values as well as values
|
from the proposed insertion. These values are specified using the
|
:paramref:`_sqlite.Insert.on_conflict_do_update.set_` parameter. This
|
parameter accepts a dictionary which consists of direct values
|
for UPDATE:
|
|
.. sourcecode:: pycon+sql
|
|
>>> stmt = insert(my_table).values(id='some_id', data='inserted value')
|
|
>>> do_update_stmt = stmt.on_conflict_do_update(
|
... index_elements=['id'],
|
... set_=dict(data='updated value')
|
... )
|
|
>>> print(do_update_stmt)
|
{printsql}INSERT INTO my_table (id, data) VALUES (?, ?)
|
ON CONFLICT (id) DO UPDATE SET data = ?
|
|
.. warning::
|
|
The :meth:`_sqlite.Insert.on_conflict_do_update` method does **not** take
|
into account Python-side default UPDATE values or generation functions,
|
e.g. those specified using :paramref:`_schema.Column.onupdate`. These
|
values will not be exercised for an ON CONFLICT style of UPDATE, unless
|
they are manually specified in the
|
:paramref:`_sqlite.Insert.on_conflict_do_update.set_` dictionary.
|
|
Updating using the Excluded INSERT Values
|
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
|
|
In order to refer to the proposed insertion row, the special alias
|
:attr:`~.sqlite.Insert.excluded` is available as an attribute on
|
the :class:`_sqlite.Insert` object; this object creates an "excluded." prefix
|
on a column, that informs the DO UPDATE to update the row with the value that
|
would have been inserted had the constraint not failed:
|
|
.. sourcecode:: pycon+sql
|
|
>>> stmt = insert(my_table).values(
|
... id='some_id',
|
... data='inserted value',
|
... author='jlh'
|
... )
|
|
>>> do_update_stmt = stmt.on_conflict_do_update(
|
... index_elements=['id'],
|
... set_=dict(data='updated value', author=stmt.excluded.author)
|
... )
|
|
>>> print(do_update_stmt)
|
{printsql}INSERT INTO my_table (id, data, author) VALUES (?, ?, ?)
|
ON CONFLICT (id) DO UPDATE SET data = ?, author = excluded.author
|
|
Additional WHERE Criteria
|
^^^^^^^^^^^^^^^^^^^^^^^^^
|
|
The :meth:`_sqlite.Insert.on_conflict_do_update` method also accepts
|
a WHERE clause using the :paramref:`_sqlite.Insert.on_conflict_do_update.where`
|
parameter, which will limit those rows which receive an UPDATE:
|
|
.. sourcecode:: pycon+sql
|
|
>>> stmt = insert(my_table).values(
|
... id='some_id',
|
... data='inserted value',
|
... author='jlh'
|
... )
|
|
>>> on_update_stmt = stmt.on_conflict_do_update(
|
... index_elements=['id'],
|
... set_=dict(data='updated value', author=stmt.excluded.author),
|
... where=(my_table.c.status == 2)
|
... )
|
>>> print(on_update_stmt)
|
{printsql}INSERT INTO my_table (id, data, author) VALUES (?, ?, ?)
|
ON CONFLICT (id) DO UPDATE SET data = ?, author = excluded.author
|
WHERE my_table.status = ?
|
|
|
Skipping Rows with DO NOTHING
|
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
|
|
``ON CONFLICT`` may be used to skip inserting a row entirely
|
if any conflict with a unique constraint occurs; below this is illustrated
|
using the :meth:`_sqlite.Insert.on_conflict_do_nothing` method:
|
|
.. sourcecode:: pycon+sql
|
|
>>> stmt = insert(my_table).values(id='some_id', data='inserted value')
|
>>> stmt = stmt.on_conflict_do_nothing(index_elements=['id'])
|
>>> print(stmt)
|
{printsql}INSERT INTO my_table (id, data) VALUES (?, ?) ON CONFLICT (id) DO NOTHING
|
|
|
If ``DO NOTHING`` is used without specifying any columns or constraint,
|
it has the effect of skipping the INSERT for any unique violation which
|
occurs:
|
|
.. sourcecode:: pycon+sql
|
|
>>> stmt = insert(my_table).values(id='some_id', data='inserted value')
|
>>> stmt = stmt.on_conflict_do_nothing()
|
>>> print(stmt)
|
{printsql}INSERT INTO my_table (id, data) VALUES (?, ?) ON CONFLICT DO NOTHING
|
|
.. _sqlite_type_reflection:
|
|
Type Reflection
|
---------------
|
|
SQLite types are unlike those of most other database backends, in that
|
the string name of the type usually does not correspond to a "type" in a
|
one-to-one fashion. Instead, SQLite links per-column typing behavior
|
to one of five so-called "type affinities" based on a string matching
|
pattern for the type.
|
|
SQLAlchemy's reflection process, when inspecting types, uses a simple
|
lookup table to link the keywords returned to provided SQLAlchemy types.
|
This lookup table is present within the SQLite dialect as it is for all
|
other dialects. However, the SQLite dialect has a different "fallback"
|
routine for when a particular type name is not located in the lookup map;
|
it instead implements the SQLite "type affinity" scheme located at
|
https://www.sqlite.org/datatype3.html section 2.1.
|
|
The provided typemap will make direct associations from an exact string
|
name match for the following types:
|
|
:class:`_types.BIGINT`, :class:`_types.BLOB`,
|
:class:`_types.BOOLEAN`, :class:`_types.BOOLEAN`,
|
:class:`_types.CHAR`, :class:`_types.DATE`,
|
:class:`_types.DATETIME`, :class:`_types.FLOAT`,
|
:class:`_types.DECIMAL`, :class:`_types.FLOAT`,
|
:class:`_types.INTEGER`, :class:`_types.INTEGER`,
|
:class:`_types.NUMERIC`, :class:`_types.REAL`,
|
:class:`_types.SMALLINT`, :class:`_types.TEXT`,
|
:class:`_types.TIME`, :class:`_types.TIMESTAMP`,
|
:class:`_types.VARCHAR`, :class:`_types.NVARCHAR`,
|
:class:`_types.NCHAR`
|
|
When a type name does not match one of the above types, the "type affinity"
|
lookup is used instead:
|
|
* :class:`_types.INTEGER` is returned if the type name includes the
|
string ``INT``
|
* :class:`_types.TEXT` is returned if the type name includes the
|
string ``CHAR``, ``CLOB`` or ``TEXT``
|
* :class:`_types.NullType` is returned if the type name includes the
|
string ``BLOB``
|
* :class:`_types.REAL` is returned if the type name includes the string
|
``REAL``, ``FLOA`` or ``DOUB``.
|
* Otherwise, the :class:`_types.NUMERIC` type is used.
|
|
.. _sqlite_partial_index:
|
|
Partial Indexes
|
---------------
|
|
A partial index, e.g. one which uses a WHERE clause, can be specified
|
with the DDL system using the argument ``sqlite_where``::
|
|
tbl = Table('testtbl', m, Column('data', Integer))
|
idx = Index('test_idx1', tbl.c.data,
|
sqlite_where=and_(tbl.c.data > 5, tbl.c.data < 10))
|
|
The index will be rendered at create time as::
|
|
CREATE INDEX test_idx1 ON testtbl (data)
|
WHERE data > 5 AND data < 10
|
|
.. _sqlite_dotted_column_names:
|
|
Dotted Column Names
|
-------------------
|
|
Using table or column names that explicitly have periods in them is
|
**not recommended**. While this is generally a bad idea for relational
|
databases in general, as the dot is a syntactically significant character,
|
the SQLite driver up until version **3.10.0** of SQLite has a bug which
|
requires that SQLAlchemy filter out these dots in result sets.
|
|
The bug, entirely outside of SQLAlchemy, can be illustrated thusly::
|
|
import sqlite3
|
|
assert sqlite3.sqlite_version_info < (3, 10, 0), "bug is fixed in this version"
|
|
conn = sqlite3.connect(":memory:")
|
cursor = conn.cursor()
|
|
cursor.execute("create table x (a integer, b integer)")
|
cursor.execute("insert into x (a, b) values (1, 1)")
|
cursor.execute("insert into x (a, b) values (2, 2)")
|
|
cursor.execute("select x.a, x.b from x")
|
assert [c[0] for c in cursor.description] == ['a', 'b']
|
|
cursor.execute('''
|
select x.a, x.b from x where a=1
|
union
|
select x.a, x.b from x where a=2
|
''')
|
assert [c[0] for c in cursor.description] == ['a', 'b'], \
|
[c[0] for c in cursor.description]
|
|
The second assertion fails::
|
|
Traceback (most recent call last):
|
File "test.py", line 19, in <module>
|
[c[0] for c in cursor.description]
|
AssertionError: ['x.a', 'x.b']
|
|
Where above, the driver incorrectly reports the names of the columns
|
including the name of the table, which is entirely inconsistent vs.
|
when the UNION is not present.
|
|
SQLAlchemy relies upon column names being predictable in how they match
|
to the original statement, so the SQLAlchemy dialect has no choice but
|
to filter these out::
|
|
|
from sqlalchemy import create_engine
|
|
eng = create_engine("sqlite://")
|
conn = eng.connect()
|
|
conn.exec_driver_sql("create table x (a integer, b integer)")
|
conn.exec_driver_sql("insert into x (a, b) values (1, 1)")
|
conn.exec_driver_sql("insert into x (a, b) values (2, 2)")
|
|
result = conn.exec_driver_sql("select x.a, x.b from x")
|
assert result.keys() == ["a", "b"]
|
|
result = conn.exec_driver_sql('''
|
select x.a, x.b from x where a=1
|
union
|
select x.a, x.b from x where a=2
|
''')
|
assert result.keys() == ["a", "b"]
|
|
Note that above, even though SQLAlchemy filters out the dots, *both
|
names are still addressable*::
|
|
>>> row = result.first()
|
>>> row["a"]
|
1
|
>>> row["x.a"]
|
1
|
>>> row["b"]
|
1
|
>>> row["x.b"]
|
1
|
|
Therefore, the workaround applied by SQLAlchemy only impacts
|
:meth:`_engine.CursorResult.keys` and :meth:`.Row.keys()` in the public API. In
|
the very specific case where an application is forced to use column names that
|
contain dots, and the functionality of :meth:`_engine.CursorResult.keys` and
|
:meth:`.Row.keys()` is required to return these dotted names unmodified,
|
the ``sqlite_raw_colnames`` execution option may be provided, either on a
|
per-:class:`_engine.Connection` basis::
|
|
result = conn.execution_options(sqlite_raw_colnames=True).exec_driver_sql('''
|
select x.a, x.b from x where a=1
|
union
|
select x.a, x.b from x where a=2
|
''')
|
assert result.keys() == ["x.a", "x.b"]
|
|
or on a per-:class:`_engine.Engine` basis::
|
|
engine = create_engine("sqlite://", execution_options={"sqlite_raw_colnames": True})
|
|
When using the per-:class:`_engine.Engine` execution option, note that
|
**Core and ORM queries that use UNION may not function properly**.
|
|
SQLite-specific table options
|
-----------------------------
|
|
One option for CREATE TABLE is supported directly by the SQLite
|
dialect in conjunction with the :class:`_schema.Table` construct:
|
|
* ``WITHOUT ROWID``::
|
|
Table("some_table", metadata, ..., sqlite_with_rowid=False)
|
|
.. seealso::
|
|
`SQLite CREATE TABLE options
|
<https://www.sqlite.org/lang_createtable.html>`_
|
|
|
.. _sqlite_include_internal:
|
|
Reflecting internal schema tables
|
----------------------------------
|
|
Reflection methods that return lists of tables will omit so-called
|
"SQLite internal schema object" names, which are referred towards by SQLite
|
as any object name that is prefixed with ``sqlite_``. An example of
|
such an object is the ``sqlite_sequence`` table that's generated when
|
the ``AUTOINCREMENT`` column parameter is used. In order to return
|
these objects, the parameter ``sqlite_include_internal=True`` may be
|
passed to methods such as :meth:`_schema.MetaData.reflect` or
|
:meth:`.Inspector.get_table_names`.
|
|
.. versionadded:: 2.0 Added the ``sqlite_include_internal=True`` parameter.
|
Previously, these tables were not ignored by SQLAlchemy reflection
|
methods.
|
|
.. note::
|
|
The ``sqlite_include_internal`` parameter does not refer to the
|
"system" tables that are present in schemas such as ``sqlite_master``.
|
|
.. seealso::
|
|
`SQLite Internal Schema Objects <https://www.sqlite.org/fileformat2.html#intschema>`_ - in the SQLite
|
documentation.
|
|
é )Ú annotationsN)ÚOptionalé )ÚJSON)Ú JSONIndexType)Ú JSONPathTypeé )Úexc©Úschema)Úsql)Útext)Útypes)Úutil)Údefault)Ú
|
processors)Ú
|
reflection)ÚReflectionDefaults)Ú coercions)Ú ColumnElement)Úcompiler)Úelements)Úroles)ÚBLOB)ÚBOOLEAN)ÚCHAR)ÚDECIMAL)ÚFLOAT)ÚINTEGER)ÚNUMERIC)ÚREAL)ÚSMALLINT)ÚTEXT)Ú TIMESTAMP)ÚVARCHARc s e Zd Z fddZ ZS )Ú _SQliteJsonc s t ||¡ fdd}|S )Nc s: z
|
| W S t k
|
r4 t| tjr.| Y S Y nX d S ©N)Ú TypeErrorÚ
|
isinstanceÚnumbersÚNumber©Úvalue©Zdefault_processor© úVd:\z\workplace\vscode\pyvenv\venv\Lib\site-packages\sqlalchemy/dialects/sqlite/base.pyÚprocess s
|
z-_SQliteJson.result_processor.<locals>.process)ÚsuperÚresult_processor)ÚselfÚdialectÚcoltyper0 ©Ú __class__r- r/ r2 s z _SQliteJson.result_processor)Ú__name__Ú
|
__module__Ú __qualname__r2 Ú __classcell__r. r. r6 r/ r% s r% c sF e Zd ZdZdZd
|
fdd Zedd Z fddZdd Z Z
|
S ) Ú_DateTimeMixinNc s4 t jf | |d k r"t |¡| _|d k r0|| _d S r& )r1 Ú__init__ÚreÚcompileÚ_regÚ_storage_format)r3 Ústorage_formatÚregexpÚkwr6 r. r/ r= ¡ s
|
z_DateTimeMixin.__init__c C s* | j dddddddd }tt d|¡S )a? return True if the storage format will automatically imply
|
a TEXT affinity.
|
|
If the storage format contains no non-numeric characters,
|
it will imply a NUMERIC storage format on SQLite; in this case,
|
the type will generate its DDL as DATE_CHAR, DATETIME_CHAR,
|
TIME_CHAR.
|
|
r ©ÚyearÚmonthÚdayÚhourÚminuteÚsecondÚ microsecondz[^0-9])rA Úboolr> Úsearch)r3 Úspecr. r. r/ Úformat_is_text_affinity¨ s ù z&_DateTimeMixin.format_is_text_affinityc s: t |tr*| jr| j|d< | jr*| j|d< t j|f|S )NrB rC )Ú
|
issubclassr< rA r@ r1 Úadapt)r3 ÚclsrD r6 r. r/ rR ¾ s
|
|
|
z_DateTimeMixin.adaptc s | |¡ fdd}|S )Nc s d | S )Nú'%s'r. r+ ©Zbpr. r/ r0 É s z1_DateTimeMixin.literal_processor.<locals>.process)Úbind_processor©r3 r4 r0 r. rU r/ Úliteral_processorÆ s
|
z _DateTimeMixin.literal_processor)NN) r8 r9 r: r@ rA r= ÚpropertyrP rR rX r; r. r. r6 r/ r< s
|
r< c s4 e Zd ZdZdZ fddZdd Zdd Z ZS ) ÚDATETIMEaû Represent a Python datetime object in SQLite using a string.
|
|
The default string storage format is::
|
|
"%(year)04d-%(month)02d-%(day)02d %(hour)02d:%(minute)02d:%(second)02d.%(microsecond)06d"
|
|
e.g.::
|
|
2021-03-15 12:05:57.105542
|
|
The incoming storage format is by default parsed using the
|
Python ``datetime.fromisoformat()`` function.
|
|
.. versionchanged:: 2.0 ``datetime.fromisoformat()`` is used for default
|
datetime string parsing.
|
|
The storage format can be customized to some degree using the
|
``storage_format`` and ``regexp`` parameters, such as::
|
|
import re
|
from sqlalchemy.dialects.sqlite import DATETIME
|
|
dt = DATETIME(storage_format="%(year)04d/%(month)02d/%(day)02d "
|
"%(hour)02d:%(minute)02d:%(second)02d",
|
regexp=r"(\d+)/(\d+)/(\d+) (\d+)-(\d+)-(\d+)"
|
)
|
|
:param storage_format: format string which will be applied to the dict
|
with keys year, month, day, hour, minute, second, and microsecond.
|
|
:param regexp: regular expression which will be applied to incoming result
|
rows, replacing the use of ``datetime.fromisoformat()`` to parse incoming
|
strings. If the regexp contains named groups, the resulting match dict is
|
applied to the Python datetime() constructor as keyword arguments.
|
Otherwise, if positional groups are used, the datetime() constructor
|
is called with positional arguments via
|
``*map(int, match_obj.groups(0))``.
|
|
zW%(year)04d-%(month)02d-%(day)02d %(hour)02d:%(minute)02d:%(second)02d.%(microsecond)06dc sH | dd¡}t j|| |rDd|ks.tdd|ks>tdd| _d S )NÚtruncate_microsecondsFrB úDYou can specify only one of truncate_microseconds or storage_format.rC ú<You can specify only one of truncate_microseconds or regexp.zE%(year)04d-%(month)02d-%(day)02d %(hour)02d:%(minute)02d:%(second)02d©Úpopr1 r= ÚAssertionErrorrA ©r3 ÚargsÚkwargsr[ r6 r. r/ r= ý s
|
ÿ
|
ÿÿzDATETIME.__init__c s& t j t j | j fdd}|S )Nc sp | d kr d S t | r<| j| j| j| j| j| j| jd S t | rd| j| j| jddddd S tdd S )NrE r zLSQLite DateTime type only accepts Python datetime and date objects as input.) r( rF rG rH rI rJ rK rL r' r+ ©Ú datetime_dateZdatetime_datetimeÚformat_r. r/ r0 s2
|
ù
|
ù
|
ÿz(DATETIME.bind_processor.<locals>.process©ÚdatetimeÚdaterA rW r. rd r/ rV s
|
zDATETIME.bind_processorc C s | j rt | j tj¡S tjS d S r& )r@ r Ú!str_to_datetime_processor_factoryrh Zstr_to_datetime©r3 r4 r5 r. r. r/ r2 2 s ÿzDATETIME.result_processor© r8 r9 r: Ú__doc__rA r= rV r2 r; r. r. r6 r/ rZ Ï s )ÿ $rZ c @ s$ e Zd ZdZdZdd Zdd ZdS )ÚDATEa@ Represent a Python date object in SQLite using a string.
|
|
The default string storage format is::
|
|
"%(year)04d-%(month)02d-%(day)02d"
|
|
e.g.::
|
|
2011-03-15
|
|
The incoming storage format is by default parsed using the
|
Python ``date.fromisoformat()`` function.
|
|
.. versionchanged:: 2.0 ``date.fromisoformat()`` is used for default
|
date string parsing.
|
|
|
The storage format can be customized to some degree using the
|
``storage_format`` and ``regexp`` parameters, such as::
|
|
import re
|
from sqlalchemy.dialects.sqlite import DATE
|
|
d = DATE(
|
storage_format="%(month)02d/%(day)02d/%(year)04d",
|
regexp=re.compile("(?P<month>\d+)/(?P<day>\d+)/(?P<year>\d+)")
|
)
|
|
:param storage_format: format string which will be applied to the
|
dict with keys year, month, and day.
|
|
:param regexp: regular expression which will be applied to
|
incoming result rows, replacing the use of ``date.fromisoformat()`` to
|
parse incoming strings. If the regexp contains named groups, the resulting
|
match dict is applied to the Python date() constructor as keyword
|
arguments. Otherwise, if positional groups are used, the date()
|
constructor is called with positional arguments via
|
``*map(int, match_obj.groups(0))``.
|
|
z %(year)04d-%(month)02d-%(day)02dc s t j | j fdd}|S )Nc s8 | d kr d S t | r,| j| j| jd S tdd S )N)rF rG rH z;SQLite Date type only accepts Python date objects as input.)r( rF rG rH r' r+ ©re rf r. r/ r0 k s
|
ýÿz$DATE.bind_processor.<locals>.processrg rW r. ro r/ rV g s zDATE.bind_processorc C s | j rt | j tj¡S tjS d S r& )r@ r rj rh ri Z str_to_daterk r. r. r/ r2 | s ÿzDATE.result_processorN)r8 r9 r: rm rA rV r2 r. r. r. r/ rn ; s )rn c s4 e Zd ZdZdZ fddZdd Zdd Z ZS ) ÚTIMEa Represent a Python time object in SQLite using a string.
|
|
The default string storage format is::
|
|
"%(hour)02d:%(minute)02d:%(second)02d.%(microsecond)06d"
|
|
e.g.::
|
|
12:05:57.10558
|
|
The incoming storage format is by default parsed using the
|
Python ``time.fromisoformat()`` function.
|
|
.. versionchanged:: 2.0 ``time.fromisoformat()`` is used for default
|
time string parsing.
|
|
The storage format can be customized to some degree using the
|
``storage_format`` and ``regexp`` parameters, such as::
|
|
import re
|
from sqlalchemy.dialects.sqlite import TIME
|
|
t = TIME(storage_format="%(hour)02d-%(minute)02d-"
|
"%(second)02d-%(microsecond)06d",
|
regexp=re.compile("(\d+)-(\d+)-(\d+)-(?:-(\d+))?")
|
)
|
|
:param storage_format: format string which will be applied to the dict
|
with keys hour, minute, second, and microsecond.
|
|
:param regexp: regular expression which will be applied to incoming result
|
rows, replacing the use of ``datetime.fromisoformat()`` to parse incoming
|
strings. If the regexp contains named groups, the resulting match dict is
|
applied to the Python time() constructor as keyword arguments. Otherwise,
|
if positional groups are used, the time() constructor is called with
|
positional arguments via ``*map(int, match_obj.groups(0))``.
|
|
z6%(hour)02d:%(minute)02d:%(second)02d.%(microsecond)06dc sH | dd¡}t j|| |rDd|ks.tdd|ks>tdd| _d S )Nr[ FrB r\ rC r] z$%(hour)02d:%(minute)02d:%(second)02dr^ ra r6 r. r/ r= ¯ s
|
ÿ
|
ÿz TIME.__init__c s t j | j fdd}|S )Nc s< | d kr d S t | r0| j| j| j| jd S tdd S )N)rI rJ rK rL z;SQLite Time type only accepts Python time objects as input.)r( rI rJ rK rL r' r+ ©Z datetime_timerf r. r/ r0 Á s
|
üÿz$TIME.bind_processor.<locals>.process)rh ÚtimerA rW r. rq r/ rV ½ s zTIME.bind_processorc C s | j rt | j tj¡S tjS d S r& )r@ r rj rh rr Z str_to_timerk r. r. r/ r2 Ó s ÿzTIME.result_processorrl r. r. r6 r/ rp
s
|
' rp )ÚBIGINTr ÚBOOLr r rn Ú DATE_CHARrZ Ú DATETIME_CHARÚDOUBLEr r ÚINTr r r r r! r" rp Ú TIME_CHARr# r$ ÚNVARCHARÚNCHARc sö e Zd Ze ejjddddddddd d
|
d
|
¡Zd d Zdd Z dd Z
|
dd Z dd Z dd Z fddZdd Z fd d Zd d Zd d! Zd"d# Zd$d% Zd&d' Zd(d) Zd*d+ Zd,d- Zd.d/ Zd0d1 Zd2d3 Zd4d5 Z d6d7 Z d8d9 Z Z S ):ÚSQLiteCompilerz%mz%dz%Yz%Sz%Hz%jz%Mz%sz%wz%W)
|
rG rH rF rK rI ZdoyrJ ÚepochZdowÚweekc K s( | j |jf|d d| j |jf| S )Nz / z
|
(%s + 0.0)©r0 ÚleftÚright©r3 ÚbinaryÚoperatorrD r. r. r/ Úvisit_truediv_binary s ÿþÿz#SQLiteCompiler.visit_truediv_binaryc K s dS )NZCURRENT_TIMESTAMPr. ©r3 ÚfnrD r. r. r/ Úvisit_now_func s z SQLiteCompiler.visit_now_funcc K s dS )Nz(DATETIME(CURRENT_TIMESTAMP, "localtime")r. )r3 ÚfuncrD r. r. r/ Úvisit_localtimestamp_func s z(SQLiteCompiler.visit_localtimestamp_funcc K s dS )NÚ1r. ©r3 ÚexprrD r. r. r/ Ú
|
visit_true s zSQLiteCompiler.visit_truec K s dS )NÚ0r. r r. r. r/ Ú visit_false# s zSQLiteCompiler.visit_falsec K s d| |¡ S )Nzlength%s)Zfunction_argspecr r. r. r/ Úvisit_char_length_func& s z%SQLiteCompiler.visit_char_length_funcc s, | j jrt j|f|S | j|jf|S d S r& )r4 Ú supports_castr1 Ú
|
visit_castr0 Úclause)r3 Úcastrc r6 r. r/ r ) s zSQLiteCompiler.visit_castc
|
K s\ z"d| j |j | j|jf|f W S tk
|
rV } zt d|j ¡|W 5 d }~X Y nX d S )Nz#CAST(STRFTIME('%s', %s) AS INTEGER)z#%s is not a valid extract argument.)Ú extract_mapÚfieldr0 r ÚKeyErrorr Ú CompileError)r3 ÚextractrD Úerrr. r. r/ Ú visit_extract/ s
|
þÿþz SQLiteCompiler.visit_extractc s" d|d< t j||fd|i|S )NFÚ include_tableÚpopulate_result_map)r1 Úreturning_clause)r3 ZstmtZreturning_colsr rD r6 r. r/ r : s ÿÿÿz SQLiteCompiler.returning_clausec K s d}|j d k r&|d| j|j f| 7 }|jd k rl|j d krR|d| t d¡¡ 7 }|d| j|jf| 7 }n |d| jt d¡f| 7 }|S )NÚ z
|
LIMIT éÿÿÿÿz OFFSET r )Z _limit_clauser0 Z_offset_clauser Úliteral)r3 ÚselectrD r r. r. r/ Ú limit_clauseG s
|
|
|
zSQLiteCompiler.limit_clausec K s dS )Nr r. )r3 r£ rD r. r. r/ Úfor_update_clauseS s z SQLiteCompiler.for_update_clausec s( dd< dd fdd|D ¡ S )NTZasfromzFROM ú, c 3 s$ | ] }|j fd iV qdS )Z fromhintsN)Z_compiler_dispatch)Ú.0Út©Ú
|
from_hintsrD r3 r. r/ Ú <genexpr>[ s ÿz4SQLiteCompiler.update_from_clause.<locals>.<genexpr>)Újoin)r3 Z update_stmtZ
|
from_tableZ extra_fromsrª rD r. r© r/ Úupdate_from_clauseW s þz!SQLiteCompiler.update_from_clausec K s d| |j¡| |j¡f S )Nz %s IS NOT %sr r r. r. r/ Ú visit_is_distinct_from_binary` s
|
|
þz,SQLiteCompiler.visit_is_distinct_from_binaryc K s d| |j¡| |j¡f S )Nz%s IS %sr r r. r. r/ Ú!visit_is_not_distinct_from_binaryf s
|
|
þz0SQLiteCompiler.visit_is_not_distinct_from_binaryc K s< |j jtjkrd}nd}|| j|jf|| j|jf|f S ©Nz JSON_QUOTE(JSON_EXTRACT(%s, %s))zJSON_EXTRACT(%s, %s)©ÚtypeÚ_type_affinityÚsqltypesr r0 r r ©r3 r r rD r r. r. r/ Ú visit_json_getitem_op_binaryl s þz+SQLiteCompiler.visit_json_getitem_op_binaryc K s< |j jtjkrd}nd}|| j|jf|| j|jf|f S r° r± rµ r. r. r/ Ú!visit_json_path_getitem_op_binaryw s þz0SQLiteCompiler.visit_json_path_getitem_op_binaryc K s
|
| |¡S r& )Úvisit_empty_set_expr)r3 Útype_Z expand_oprD r. r. r/ Úvisit_empty_set_op_expr s z&SQLiteCompiler.visit_empty_set_op_exprc K s< dd dd |pt gD ¡d dd |p0t gD ¡f S )Nz%SELECT %s FROM (SELECT %s) WHERE 1!=1r¦ c s s | ]
|
}d V qdS ©r Nr. ©r§ r¹ r. r. r/ r« s z6SQLiteCompiler.visit_empty_set_expr.<locals>.<genexpr>c s s | ]
|
}d V qdS r» r. r¼ r. r. r/ r« s )r¬ r )r3 Z element_typesrD r. r. r/ r¸ s þz#SQLiteCompiler.visit_empty_set_exprc K s | j |df|S )Nz REGEXP ©Z_generate_generic_binaryr r. r. r/ Ú visit_regexp_match_op_binary s z+SQLiteCompiler.visit_regexp_match_op_binaryc K s | j |df|S )Nz NOT REGEXP r½ r r. r. r/ Ú visit_not_regexp_match_op_binary s z/SQLiteCompiler.visit_not_regexp_match_op_binaryc sn |j d k rd|j }nT|jd k rfdd fdd|jD ¡ }|jd k rj|d j|jdddd 7 }nd }|S )
|
Nz(%s)r¦ c 3 s4 | ],}t |tr j |¡n j|d d dV qdS )F©r Ú
|
use_schemaN)r( ÚstrÚpreparerÚquoter0 ©r§ Úc©r3 r. r/ r« s ýÿz5SQLiteCompiler._on_conflict_target.<locals>.<genexpr>ú WHERE %sFT)r rÁ Ú literal_bindsr )Zconstraint_targetZinferred_target_elementsr¬ Zinferred_target_whereclauser0 )r3 r rD Ú target_textr. rÇ r/ Ú_on_conflict_target s
|
|
ú
|
|
ü z"SQLiteCompiler._on_conflict_targetc K s" | j |f|}|rd| S dS d S )NzON CONFLICT %s DO NOTHINGzON CONFLICT DO NOTHING)rË )r3 Ú on_conflictrD rÊ r. r. r/ Ú visit_on_conflict_do_nothing¬ s z+SQLiteCompiler.visit_on_conflict_do_nothingc K s² |}| j |f|}g }t|j}| jd d }|jj}|D ]¨} | j}
|
|
|
|krX| |
|
¡} n| |kr:| | ¡} nq:t | ¡rt
|
j d | | j d} n$t | t
|
j r°| j jr°| ¡ } | j | _ | j| ¡ dd} | j | j¡} | d| | f ¡ q:|rvt d| jjjd d d
|
|D ¡f ¡ | ¡ D ]Z\}}t |tr:| j |¡n | j|dd} | jt t j |¡dd} | d| | f ¡ qd |¡}|j d k r¦|d | j|j d dd 7 }d||f S )Nr¡ Z
|
selectable)r¹ F©rÁ z%s = %szFAdditional column names not matching any column keys in table '%s': %sr¦ c s s | ]}d | V qdS )rT Nr. rÅ r. r. r/ r« á s z=SQLiteCompiler.visit_on_conflict_do_update.<locals>.<genexpr>rÈ TrÀ z ON CONFLICT %s DO UPDATE SET %s) rË ÚdictZupdate_values_to_setÚstackÚtablerÆ Úkeyr_ r Z _is_literalr Z BindParameterr² r( Z_isnullZ_cloner0 Z
|
self_grouprà rÄ ÚnameÚappendr ÚwarnZcurrent_executabler¬ Úitemsr Úexpectr ZExpressionElementRoleZupdate_whereclause)r3 rÌ rD r rÊ Zaction_set_opsZset_parametersZinsert_statementÚcolsrÆ Zcol_keyr, Z
|
value_textZkey_textÚkÚvZ action_textr. r. r/ Úvisit_on_conflict_do_updateµ sd
|
|
|
ÿþþþÿÿ ý þ
|
ÿ
|
z*SQLiteCompiler.visit_on_conflict_do_update) r8 r9 r: r Z update_copyr Ú SQLCompilerr r
r r r r r r r r r¤ r¥ r r® r¯ r¶ r· rº r¸ r¾ r¿ rË rÍ rÛ r; r. r. r6 r/ r| sJ öþ r| c sn e Zd Zdd Z fddZ fddZ fddZ fd d
|
Z fd d Zd d Z dddZ
|
dd Z Z S )ÚSQLiteDDLCompilerc K sV | j jj|j|d}| j |¡d | }| |¡}|d k r`t|jj t
|
rTd| d }|d| 7 }|j s|d7 }|j d d }|d k r|d | 7 }|j r2|jd
|
kr¾t|jj jd kr¾t d ¡|jj d d r2t|jj jd kr2t|jjtjr2|js2|d7 }|j d d }|d k r*|d | 7 }|d7 }|jd k rR|d| |j¡ 7 }|S )N)Ztype_expressionú ú(ú)z DEFAULT z NOT NULLÚsqliteÚon_conflict_not_nullú ON CONFLICT Tr z@SQLite does not support autoincrement for composite primary keysÚ autoincrementz PRIMARY KEYÚon_conflict_primary_keyz AUTOINCREMENT)r4 Ztype_compiler_instancer0 r² rà Z format_columnZget_column_default_stringr( Zserver_defaultÚargr ÚnullableÚdialect_optionsÚ primary_keyrä ÚlenrÑ Úcolumnsr r rQ r³ r´ ÚIntegerÚ foreign_keysÚcomputed)r3 Úcolumnrc r5 Úcolspecr Úon_conflict_clauser. r. r/ Úget_column_specificationú sV ÿ
|
ÿ ÿþÿÿþýüÿ
|
z*SQLiteDDLCompiler.get_column_specificationc s¨ t |jdkrJt|d }|jrJ|jjd d rJt|jjt j
|
rJ|j sJd S t |¡}|jd d }|d krt |jdkrt|d jd d }|d k r¤|d| 7 }|S )Nr r rá rä rÌ rå rã )rê rë Úlistré rÑ rè rQ r² r³ r´ rì rí r1 Ú visit_primary_key_constraint)r3 Ú
|
constraintrD rÆ r rñ r6 r. r/ rô . s, ÿþýü ÿÿ z.SQLiteDDLCompiler.visit_primary_key_constraintc sv t |¡}|jd d }|d kr^t|jdkr^t|d }t|tjr^t|d jd d }|d k rr|d| 7 }|S )Nrá rÌ r r Úon_conflict_uniquerã ) r1 Úvisit_unique_constraintrè rê rë ró r( r Z
|
SchemaItem)r3 rõ rD r rñ Zcol1r6 r. r/ r÷ K s ÿ ÿþ z)SQLiteDDLCompiler.visit_unique_constraintc s2 t |¡}|jd d }|d k r.|d| 7 }|S )Nrá rÌ rã )r1 Úvisit_check_constraintrè )r3 rõ rD r rñ r6 r. r/ rø ] s ÿ z(SQLiteDDLCompiler.visit_check_constraintc s, t |¡}|jd d d k r(t d¡|S )Nrá rÌ zFSQLite does not support on conflict clause for column check constraint)r1 Ú visit_column_check_constraintrè r r )r3 rõ rD r r6 r. r/ rù i s ÿz/SQLiteDDLCompiler.visit_column_check_constraintc s< |j d jj}|j d jj}|j|jkr,d S t |¡S d S )Nr )r ÚparentrÑ rï r r1 Ú visit_foreign_key_constraint)r3 rõ rD Z local_tableZ remote_tabler6 r. r/ rû t s
|
z.SQLiteDDLCompiler.visit_foreign_key_constraintc C s |j |ddS )z=Format the remote table clause of a CREATE CONSTRAINT clause.FrÎ )Ú format_table)r3 rõ rÑ rà r. r. r/ Ú define_constraint_remote_table~ s z0SQLiteDDLCompiler.define_constraint_remote_tableFTc
|
s´ |j } |¡ j}d}|jr(|d7 }|d7 }|jr>|d7 }|d j|dd|j|jdd d
|
fd d |j D ¡f 7 }|j
|
d d }|d k r° j j |ddd} |d| 7 }|S )NzCREATE zUNIQUE zINDEX zIF NOT EXISTS z %s ON %s (%s)T)Úinclude_schemaFrÎ r¦ c 3 s | ]} j j|d ddV qdS )FT©r rÉ N)Ú sql_compilerr0 )r§ r rÇ r. r/ r« s ý ÿz7SQLiteDDLCompiler.visit_create_index.<locals>.<genexpr>rá Úwhererÿ z WHERE ) ÚelementZ_verify_index_tablerà ÚuniqueZ if_not_existsZ_prepared_index_namerü rÑ r¬ Z expressionsrè r r0 )
|
r3 Úcreaterþ Zinclude_table_schemarD Úindexrà r Z whereclauseZwhere_compiledr. rÇ r/ Úvisit_create_index s2
|
üý ÿ z$SQLiteDDLCompiler.visit_create_indexc C s |j d d dkrdS dS )Nrá Ú
|
with_rowidFz
|
WITHOUT ROWIDr )rè )r3 rÑ r. r. r/ Úpost_create_table¦ s z#SQLiteDDLCompiler.post_create_table)FT) r8 r9 r: rò rô r÷ rø rù rû rý r r r; r. r. r6 r/ rÝ ù s 4
|
ÿ
|
#rÝ c sD e Zd Zdd Z fddZ fddZ fddZd d
|
Z ZS ) ÚSQLiteTypeCompilerc K s
|
| |¡S r& )Z
|
visit_BLOB©r3 r¹ rD r. r. r/ Úvisit_large_binary s z%SQLiteTypeCompiler.visit_large_binaryc s$ t |tr|jr t |¡S dS d S )Nrv )r( r< rP r1 Úvisit_DATETIMEr
|
r6 r. r/ r ° s ÿþ z!SQLiteTypeCompiler.visit_DATETIMEc s$ t |tr|jr t |¡S dS d S )Nru )r( r< rP r1 Ú
|
visit_DATEr
|
r6 r. r/ r ¹ s ÿþ z SQLiteTypeCompiler.visit_DATEc s$ t |tr|jr t |¡S dS d S )Nry )r( r< rP r1 Ú
|
visit_TIMEr
|
r6 r. r/ r  s ÿþ z SQLiteTypeCompiler.visit_TIMEc K s dS )Nr r. r
|
r. r. r/ Ú
|
visit_JSONË s z SQLiteTypeCompiler.visit_JSON) r8 r9 r: r r r r r r; r. r. r6 r/ r ¬ s
|
r c u @ sú e Zd Zddddddddd d
|
d d d ddddddddddddddd d d d d d!d"d#d$d%d&d'd(d)d*d+d,d-d.d/d0d1d2d3d4d5d6d7d8d9d:d;d<d=d>d?d@dAdBdCdDdEdFdGdHdIdJdKdLdMdNdOdPdQdRdSdTdUdVdWdXdYdZd[d\d]d^d_d`dadbdcdddedfdgdhdidjdkdldmdndodpdqdrdsdtduhuZdvS )wÚSQLiteIdentifierPreparerÚaddÚafterÚallZalterZanalyzeÚandÚasZascÚattachrä ZbeforeÚbeginZbetweenZbyZcascadeZcaser ÚcheckZcollaterï ÚcommitÚconflictrõ r ZcrossZ current_dateÚ current_timeZcurrent_timestampZdatabaser Ú
|
deferrableÚdeferredÚdeleteÚdescÚdetachZdistinctZdropZeachÚelseÚendÚescapeÚexceptZ exclusiveÚexistsÚexplainÚfalseZfailÚforZforeignÚfromÚfullÚglobÚgroupZhavingÚifÚignoreZ immediateÚinr ZindexedÚ initiallyÚinnerÚinsertZinsteadZ intersectZintoÚisZisnullr¬ rÒ r ZlikeÚlimitÚmatchZnaturalÚnotZnotnullÚnullZofÚoffsetÚonÚorÚorderÚouterZplanÚpragmaZprimaryÚqueryÚraiseZ
|
referencesZreindexÚrenameÚreplaceZrestrictr ÚrollbackÚrowr£ ÚsetrÑ ÚtempÚ temporaryZthenÚtoZ transactionZtriggerÚtrueÚunionr ÚupdateZusingZvacuumÚvaluesÚviewZvirtualÚwhenr N)r8 r9 r: Zreserved_wordsr. r. r. r/ r Ò sì r c @ s" e Zd Zejdd Zdd ZdS )ÚSQLiteExecutionContextc C s | j j p| j dd¡S )NZsqlite_raw_colnamesF)r4 Ú_broken_dotted_colnamesZexecution_optionsÚgetrÇ r. r. r/ Ú_preserve_raw_colnamesM s
|
þz-SQLiteExecutionContext._preserve_raw_colnamesc C s, | j s d|kr | d¡d |fS |d fS d S )NÚ.r¡ )rQ Úsplit)r3 Zcolnamer. r. r/ Ú_translate_colnameT s z)SQLiteExecutionContext._translate_colnameN)r8 r9 r: r Zmemoized_propertyrQ rT r. r. r. r/ rN L s
|
rN c @ s& e Zd ZdZdZdZdZdZdZdZ dZ
|
dZ dZ dZ dZdZdZdZdZdZdZdZdZeZeZeZeZ e Z e Z e Z e!j"dddfe!j#ddife!j$dddd fe!j%d
|
difgZ&dZ'dZ(e)j*d d d dIddZ+e) ,ddd¡Z-dd Z.dd Z/dd Z0e1j2dd Z3dd Z4d d d d d d!d"Z5e1j2dJd#d$Z6e1j2dKd%d&Z7e1j2dLd'd(Z8e1j2dMd)d*Z9d+d, Z:e1j2dNd-d.Z;e1j2dOd/d0Z<e1j2dPd1d2Z=d3d4 Z>d5d6 Z?e1j2dQd7d8Z@e1j2dRd9d:ZAd;d< ZBe1j2dSd=d>ZCe1j2dTd?d@ZDe1j2dUdAdBZEdCdD ZFe1j2dVdEdFZGdWdGdHZHdS )XÚ SQLiteDialectrá FTÚNULLZqmark)rä r r N)rå râ rö rÌ )ú1.3.7z¨The _json_serializer argument to the SQLite dialect has been renamed to the correct name of json_serializer. The old argument name will be removed in a future release.)rW z¬The _json_deserializer argument to the SQLite dialect has been renamed to the correct name of json_deserializer. The old argument name will be removed in a future release.)Ú_json_serializerÚ_json_deserializerc K sÜ t jj| f| |r|}|r |}|| _|| _|| _| jd k rØ| jjdk r\t d| jjf ¡ | jjdk | _
|
| jjdk| _ | jjdk| _ | jjdk| _ | jjdk | _| jjdk s´tjrÆd | _ | _| _| jjd
|
k rØd | _d S ) N)r é é zÇSQLite version %s is older than 3.7.16, and will not support right nested joins, as are sometimes used in more complex ORM scenarios. SQLAlchemy 1.4 and above no longer tries to rewrite these joins.)r é
|
r )r r é )r é r )r rZ é )r é é )r é# F)r é r iç )r ÚDefaultDialectr= rX rY Únative_datetimeZdbapiZsqlite_version_infor rÕ rO Úsupports_default_valuesr Úsupports_multivalues_insertÚ_broken_fk_pragma_quotesÚpypyÚupdate_returningÚdelete_returningÚinsert_returningZ insertmanyvalues_max_parameters)r3 re Zjson_serializerZjson_deserializerrX rY rc r. r. r/ r= ¥ s<
|
üÿ
|
ÿþþ zSQLiteDialect.__init__r r )úREAD UNCOMMITTEDÚ SERIALIZABLEc C s
|
t | jS r& )ró Ú_isolation_lookup)r3 Údbapi_connectionr. r. r/ Úget_isolation_level_valuesü s z(SQLiteDialect.get_isolation_level_valuesc C s. | j | }| ¡ }| d| ¡ | ¡ d S )NzPRAGMA read_uncommitted = )ro ÚcursorÚexecuteÚclose)r3 rp ÚlevelZisolation_levelrr r. r. r/ Úset_isolation_levelÿ s
|
z!SQLiteDialect.set_isolation_levelc C s` | ¡ }| d¡ | ¡ }|r(|d }nd}| ¡ |dkr@dS |dkrLdS ds\td| d S )NzPRAGMA read_uncommittedr rn r rm FzUnknown isolation level %s)rr rs Zfetchonert r` )r3 rp rr Úresr, r. r. r/ Úget_isolation_level s
|
|
z!SQLiteDialect.get_isolation_levelc K s d}| |¡}dd |D S )NzPRAGMA database_listc S s g | ]}|d dkr|d qS )r rE r. )r§ Údbr. r. r/ Ú
|
<listcomp>! s z2SQLiteDialect.get_schema_names.<locals>.<listcomp>)Úexec_driver_sql)r3 Ú
|
connectionrD ÚsÚdlr. r. r/ Úget_schema_names s
|
z SQLiteDialect.get_schema_namesc C s, |d k r$| j |¡}| d| }n|}|S )NrR )Úidentifier_preparerÚquote_identifier)r3 r Ú
|
table_nameÚqschemarÓ r. r. r/ Ú_format_schema# s
|
z SQLiteDialect._format_schemar z Optional[str]rM )rÑ r¹ r Úsqlite_include_internalc C s6 | ||¡}|sd}nd}d| d| d| d}|S )Nz) AND name NOT LIKE 'sqlite~_%' ESCAPE '~'r zSELECT name FROM z WHERE type='ú'z ORDER BY name)r )r3 rÑ r¹ r r
ÚmainZ filter_tabler> r. r. r/ Ú_sqlite_main_query+ s ÿz SQLiteDialect._sqlite_main_queryc K s& | dd||¡}| |¡ ¡ ¡ }|S )NÚ sqlite_masterrÑ ©r r{ Zscalarsr ©r3 r| r r
rD r> Únamesr. r. r/ Úget_table_names> s ÿz SQLiteDialect.get_table_namesc K s& | ddd |¡}| |¡ ¡ ¡ }|S )NÚsqlite_temp_masterrÑ r ©r3 r| r
rD r> r r. r. r/ Úget_temp_table_namesH s ÿz"SQLiteDialect.get_temp_table_namesc K s& | ddd |¡}| |¡ ¡ ¡ }|S )Nr rL r r r. r. r/ Úget_temp_view_namesR s ÿz!SQLiteDialect.get_temp_view_namesc K sB | |¡ |d k r(|| j|f|kr(dS | j|d||d}t|S )NFÚ
|
table_infor
|
)Z _ensure_has_table_connectionr Ú_get_table_pragmarM )r3 r| r r rD Úinfor. r. r/ Ú has_table\ s
|
ÿÿ ÿzSQLiteDialect.has_tablec C s dS )Nr r. )r3 r| r. r. r/ Ú_get_default_schema_namej s z&SQLiteDialect._get_default_schema_namec K s& | dd||¡}| |¡ ¡ ¡ }|S )Nr rL r r r. r. r/ Úget_view_namesm s ÿz SQLiteDialect.get_view_namesc
|
K s® |d k r8| j |¡}| d}d|f }| ||f¡}n@zd}| ||f¡}W n( tjk
|
rv d}| ||f¡}Y nX | ¡ } | r| d jS t |r¤| d| n|¡d S )Nz.sqlite_masterz1SELECT sql FROM %s WHERE name = ? AND type='view'zzSELECT sql FROM (SELECT * FROM sqlite_master UNION ALL SELECT * FROM sqlite_temp_master) WHERE name = ? AND type='view'z<SELECT sql FROM sqlite_master WHERE name = ? AND type='view'r rR )r r r{ r Ú
|
DBAPIErrorÚfetchallr ÚNoSuchTableError)
|
r3 r| Z view_namer rD r Zmasterr} ÚrsÚresultr. r. r/ Úget_view_definitionw s*
|
ÿÿÿ
|
ÿz!SQLiteDialect.get_view_definitionc K s d}| j dkrd}| j||||d}g }d }|D ]} | d }
|
| d ¡ } | d } | d } | d }|dkrr| d
|
nd }|dkrq0t|}|dk}|d kr®|r®| j|||f|}| | |
|
| | | ||||¡¡ q0|rÖ|S | |||¡st |rü| d | n|¡nt
|
¡ S d S ) Nr )r é Z table_xinfor
|
r r^ r é é r` r rR ) Zserver_version_infor ÚupperrM Ú_get_table_sqlrÔ Ú_get_column_infor r r r rë )r3 r| r r rD r= r rë ÚtablesqlrC rÓ r¹ rç r ré ÚhiddenÚ generatedÚ persistedr. r. r/ Ú get_columns sb
|
ÿ
|
ÿÿøÿ ÿzSQLiteDialect.get_columnsc C s¤ |r0t jdd|t jd}t jdd|t jd ¡ }| |¡} |d k rJt|}|| |||d}
|
|r d} |rd} t t |¡| |t j¡} | r| d¡} | |d|
|
d < |
|
S )
|
Nr¦ r )ÚflagsÚalways)rÓ r² rç r ré z.[^,]*\s+AS\s+\(([^,]*)\)\s*(?:virtual|stored)?r )Úsqltextr§ rî ) r> ÚsubÚ
|
IGNORECASEÚstripÚ_resolve_type_affinityr rN r# r, )r3 rÓ r¹ rç r ré r¦ r§ r¤ r5 rð r« Úpatternr5 r. r. r/ r£ Ï s2
|
û ÿ
|
z SQLiteDialect._get_column_infoc C s t d|¡}|r&| d¡}| d¡}nd}d}|| jkrD| j| }njd|krTtj}nZd|ksld|ksld|krttj}n:d |ks|stj}n&d
|
|ks d |ks d |kr¨tj}ntj }|d k r t
|
d|¡}z|dd |D }W n. t k
|
r t d||f ¡ | }Y nX n| }|S )aZ Return a data type from a reflected column, using affinity rules.
|
|
SQLite's goal for universal compatibility introduces some complexity
|
during reflection, as a column's defined type might not actually be a
|
type that SQLite understands - or indeed, my not be defined *at all*.
|
Internally, SQLite handles this with a 'data type affinity' for each
|
column definition, mapping to one of 'TEXT', 'NUMERIC', 'INTEGER',
|
'REAL', or 'NONE' (raw bits). The algorithm that determines this is
|
listed in https://www.sqlite.org/datatype3.html section 2.1.
|
|
This method allows SQLAlchemy to support that algorithm, while still
|
providing access to smarter reflection utilities by recognizing
|
column definitions that SQLite only supports through affinity (like
|
DATE and DOUBLE).
|
|
z([\w ]+)(\(.*?\))?r r^ r rx r ZCLOBr" r r ZFLOAZDOUBNz(\d+)c S s g | ] }t |qS r. )Úint)r§ Úar. r. r/ rz " s z8SQLiteDialect._resolve_type_affinity.<locals>.<listcomp>zNCould not instantiate type %s with reflected arguments %s; using no arguments.)r> r5 r, Ú ischema_namesr´ r r" ZNullTyper r Úfindallr' r rÕ )r3 r¹ r5 r5 rb r. r. r/ r¯ ù s<
|
|
|
þÿz$SQLiteDialect._resolve_type_affinityc K s d }| j |||d}|r>d}t ||tj¡}|r:| d¡nd }| j|||f|} dd | D } | jdd d d d | D }
|
|
|
r|
|
|d
|
S t ¡ S d S ) Nr
|
z CONSTRAINT (\w+) PRIMARY KEYr c S s g | ]}| d d¡dkr|qS )ré r ©rP ©r§ Úcolr. r. r/ rz ; s z3SQLiteDialect.get_pk_constraint.<locals>.<listcomp>c S s
|
| d¡S )Nré rµ )r· r. r. r/ Ú<lambda>< ó z1SQLiteDialect.get_pk_constraint.<locals>.<lambda>©rÒ c S s g | ] }|d qS )rÓ r. r¶ r. r. r/ rz = s )Úconstrained_columnsrÓ ) r¢ r> rN ÚIr, r¨ Úsortr Z pk_constraint) r3 r| r r rD Úconstraint_nameÚ
|
table_dataZ
|
PK_PATTERNr rØ Zpkeysr. r. r/ Úget_pk_constraint/ s
|
z SQLiteDialect.get_pk_constraintc s j |d||d}i }|D ]Ú}|d |d |d |d f\}} }
|
} | sz$j|| fd|i|} | d } W q tjk
|
r g } Y qX ng } jr t d d
|
| ¡} ||kr²|| }n"d g || | i d }||< |||< |d |
|
¡ | r|d | ¡ qd d fdd| ¡ D }j |||dfdd}g }| D ]`\}}}} } ||| }||krvt
|
d||f ¡ q<| |¡}||d< ||d< | |¡ q<| | ¡ ¡ |r¶|S t ¡ S d S )NZforeign_key_listr
|
r r^ r r r r» z^[\"\[`\']|[\"\]`\']$r )rÓ r» Zreferred_schemaÚreferred_tableÚreferred_columnsÚoptionsr c S s t | |f t | S r& )Útuple©r» rÁ r r. r. r/ Úfk_sigy s ÿþÿz.SQLiteDialect.get_foreign_keys.<locals>.fk_sigc s& i | ] } |d |d |d |qS rÅ r. )r§ Úfk)rÆ r. r/ Ú
|
<dictcomp> s ûýz2SQLiteDialect.get_foreign_keys.<locals>.<dictcomp>c 3 s2 d kr d S d} t | t j¡D ]
|
}| dddddddd ¡\}}}}}}}} t |¡}|sf|}nt |¡}|pz|}i }
|
t d
|
| ¡ ¡D ]b} | d ¡rÄ| dd
¡ } | rò| d krò| |
|
d < q| d¡r| dd
¡ } | r| d kr| |
|
d< q|r
|
d| ¡ k|
|
d< | r | ¡ |
|
d< |||||
|
fV q d S )Nzö(?:CONSTRAINT (\w+) +)?FOREIGN KEY *\( *(.+?) *\) +REFERENCES +(?:(?:"(.+?)")|([a-z0-9_]+)) *\((.+?)\) *((?:ON (?:DELETE|UPDATE) (?:SET NULL|SET DEFAULT|CASCADE|RESTRICT|NO ACTION) *)*)((?:NOT +)?DEFERRABLE)?(?: +INITIALLY +(DEFERRED|IMMEDIATE))?r r^ r r r r` rZ r] z
|
*\bON\b *ÚDELETEz NO ACTIONÚondeleteZUPDATEÚonupdateZNOTr r0 )
|
r> Úfinditerr¼ r, ró Ú_find_cols_in_sigrS r¡ Ú
|
startswithr® )Z
|
FK_PATTERNr5 r¾ r» Zreferred_quoted_nameÚ referred_namer Zonupdatedeleter r0 rà ÚtokenrÊ rË ©r3 r¿ r. r/ Ú parse_fks sX ÿ
|
÷ÿÿ
|
|
|
|
ûz1SQLiteDialect.get_foreign_keys.<locals>.parse_fkszhWARNING: SQL-parsed foreign key constraint '%s' could not be located in PRAGMA foreign_keys for table %srÓ rà )r rÀ r r rh r> r¬ rÔ rK r¢ r rÕ r_ Úextendr rí )r3 r| r r rD Z
|
pragma_fksZfksrC Z numerical_idZrtblZlcolZrcolZ referred_pkr rÇ Zkeys_by_signaturerÒ Zfkeysr¾ r» rÏ rà ÚsigrÒ r. )rÆ r3 r¿ r/ Úget_foreign_keysD s ÿ$ ÿÿÿ
|
ú
|
ú ;ú
|
þÿ
|
z SQLiteDialect.get_foreign_keysc c s0 t d|t j¡D ]}| d¡p&| d¡V qd S )Nz(?:"(.+?)")|([a-z0-9_]+)r r^ )r> rÌ r¼ r, )r3 rÔ r5 r. r. r/ rÍ è s z SQLiteDialect._find_cols_in_sigc sÄ i } j ||f|dd|D ](}|d d¡s2q t|d }|||< q j||fd|i|g } fdd} | D ]6\}
|
} t| }||krx| |¡ |
|
| d } | | ¡ qx|r¸|S t ¡ S d S )
|
NT)r Úinclude_auto_indexesrÓ Úsqlite_autoindexÚ column_namesr c 3 s d kr d S d} d}t | t j¡D ](}| dd¡\}}|t |¡fV q$t |t j¡D ],}t | d¡pz| d¡¡}d |fV q^d S )Nz,(?:CONSTRAINT "?(.+?)"? +)?UNIQUE *\((.+?)\)zB(?:(".+?")|(?:[\[`])?([a-z0-9_]+)(?:[\]`])?) +[a-z0-9_ ]+? +UNIQUEr r^ )r> rÌ r¼ r, ró rÍ )ZUNIQUE_PATTERNZINLINE_UNIQUE_PATTERNr5 rÓ rØ rÑ r. r/ Ú parse_uqs
|
s ÿÿz7SQLiteDialect.get_unique_constraints.<locals>.parse_uqs)rÓ rØ )Ú get_indexesrÎ rÄ r¢ r_ rÔ r Úunique_constraints) r3 r| r r rD Zauto_index_by_sigÚidxrÔ rÛ rÙ rÓ rØ Zparsed_constraintr. rÑ r/ Úget_unique_constraintsì sB þüû
|
|
ÿÿÿ
|
|
z$SQLiteDialect.get_unique_constraintsc
|
K s | j ||fd|i|}d}g }t ||p,dtj¡D ]6}| d¡} | rTt dd| ¡} | | d¡| d¡ q4|jdd d
|
|r|S t ¡ S d S ) Nr z-(?:CONSTRAINT (.+) +)?CHECK *\( *(.+) *\),? *r r z^"|"$r^ )r« rÓ c S s | d p
|
dS ©NrÓ ú~r. ©Údr. r. r/ r¸ ;
|
r¹ z5SQLiteDialect.get_check_constraints.<locals>.<lambda>rº )
|
r¢ r> rÌ r¼ r, r¬ rÔ r½ r Zcheck_constraints)
|
r3 r| r r rD r¿ Z CHECK_PATTERNZcksr5 rÓ r. r. r/ Úget_check_constraints&
|
s& ÿÿÿ
|
z#SQLiteDialect.get_check_constraintsc K sÐ | j |d||d}g }t dtj¡}|r:d| j |¡ }nd}| dd¡} |D ]´}
|
| sf|
|
d d ¡rfqN| t |
|
d g |
|
d
|
i d ¡ t
|
|
|
d krN|
|
d rNdd|i } | | |
|
d f¡} | ¡ } | | ¡}|d krät d|
|
d ¡ qN| d¡}t||d d d< qNt|D ]l}| j |d|d |d}|D ]J}
|
|
|
d
|
d kr`t d|d ¡ | |¡ q n|d |
|
d
|
¡ q*q |jdd d |r|S | |||¡sÄt |r¼| d| n|¡nt ¡ S d S ) NZ
|
index_listr
|
z\)\s+where\s+(.+)ú%s.r rÖ Fr r× r^ )rÓ rØ r rè r r zISELECT sql FROM %(schema)ssqlite_master WHERE name = ? AND type = 'index'r z6Failed to look up filter predicate of partial index %sr¡ rè Z sqlite_whereÚ
|
index_inforÓ z;Skipped unsupported reflection of expression-based index %srØ c S s | d p
|
dS rÞ r. rà r. r. r/ r¸
|
r¹ z+SQLiteDialect.get_indexes.<locals>.<lambda>rº rR )r r> r? r r r r_ rÎ rÔ rÏ rê r{ ÚscalarrN r rÕ r, r ró Úremover½ r r r r Úindexes)r3 r| r r rD Zpragma_indexesrç Zpartial_pred_reÚ schema_exprrÖ rC r} r Z index_sqlZpredicate_matchÚ predicaterÜ Z pragma_indexr. r. r/ rÚ A
|
s ÿÿ ÿüÿ
|
þÿ
|
ÿÿ
|
ÿ ÿÿÿ
|
ÿzSQLiteDialect.get_indexesc C s |dkS )N> Úsqlite_temp_schemar Ú sqlite_schemar r. )r3 r r. r. r/ Ú _is_sys_table
|
s zSQLiteDialect._is_sys_tablec K s |rd| j |¡ }nd}z dd|i }| ||f¡}W n0 tjk
|
rh dd|i }| ||f¡}Y nX | ¡ }|d kr| |¡st | | ¡|S )Nrã r zSELECT sql FROM (SELECT * FROM %(schema)ssqlite_master UNION ALL SELECT * FROM %(schema)ssqlite_temp_master) WHERE name = ? AND type in ('table', 'view')r zTSELECT sql FROM %(schema)ssqlite_master WHERE name = ? AND type in ('table', 'view'))r r r{ r r rå rì r ) r3 r| r r rD rè r} r r, r. r. r/ r¢ £
|
s*
|
ÿüÿþÿz SQLiteDialect._get_table_sqlc C s | j j}|d k r$d|| dg}nddg}||}|D ]B}| | d| d}| |¡} | jsj| ¡ }
|
ng }
|
|
|
r8|
|
S q8g S )NzPRAGMA rR z PRAGMA main.z PRAGMA temp.rß rà )r r r{ Z _soft_closedr ) r3 r| r= r r rÄ Z
|
statementsZqtableZ statementrr r r. r. r/ r À
|
s
|
|
|
z SQLiteDialect._get_table_pragma)FNNNN)NF)F)F)N)NF)N)N)N)N)N)N)N)N)N)Ir8 r9 r: rÓ Zsupports_alterrf Zsupports_default_metavalueZ supports_sane_rowcount_returningZsupports_empty_insertr rg Zuse_insertmanyvaluesZtuple_in_valuesZsupports_statement_cacheZ#insert_null_pk_still_autoincrementsrl rj Zupdate_returning_multifromrk Zdefault_metavalue_tokenZdefault_paramstylerN Zexecution_ctx_clsr| Zstatement_compilerrÝ Z ddl_compilerr Ztype_compiler_clsr rà r³ ÚcolspecsÚ sa_schemaZTableZIndexZColumnZ
|
ConstraintZconstruct_argumentsrh rO r Zdeprecated_paramsr= Z immutabledictro rq rv rx r Úcacher r r r r r r r r r r¨ r£ r¯ rÀ rÕ rÍ rÝ râ rÚ rì r¢ r r. r. r. r/ rU b sÎ þþ ýþ ïù ú Eÿ
|
ÿ ÿ ÿ ÿ ! 5*6 $ ÿ 9 Y rU )Frm Ú
|
__future__r rh r) r> Útypingr Újsonr r r r r r rî r r r r´ r Zenginer r r Zengine.reflectionr r r r r r r r r r r r r r r! r" r# r$ r% r< ÚDateTimerZ ÚDatern ZTimerp rí rs rw rz r{ r³ rÜ r| Z DDLCompilerrÝ ZGenericTypeCompilerr ZIdentifierPreparerr ZDefaultExecutionContextrN rd rU r. r. r. r/ Ú<module>
|
s¼ a 2lJX ú
|
ç x 4&z
|
