U
|
¸ý°d¢ ã f @ sd d Z ddlmZ ddlmZ ddlmZ ddlZddlm Z ddlm
|
Z
|
ddlm Z dd lm 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
|
dlm Z d
|
dlm Z d
|
dlm Z d
|
dlm Z d
|
dlm Z d
|
dlm!Z! d
|
dl"m#Z# d
|
dl"m$Z$ d
|
dl"m%Z% d
|
d l"m&Z& d
|
d l"m'Z' d
|
d l"m(Z( d
|
d l"m)Z) d
|
d l"m*Z* d
|
d!l"m+Z+ d
|
d"l"m,Z, d
|
d#l"m-Z- d
|
d$l"m.Z. d
|
d%l"m/Z/ d
|
d&l"m0Z0 d
|
d'l"m1Z1 d
|
d(l"m2Z2 d
|
d)l"m3Z3 d
|
d*l"m4Z4 d
|
d+l"m5Z5 d
|
d,l"m6Z6 d
|
d-l"m7Z7 d
|
d.l"m8Z8 d
|
d/l"m9Z9 d
|
d0l"m:Z: d
|
d1l"m;Z; d2d3l m<Z< d2d4l m=Z= d2d5l m>Z> d2d6l m?Z? d2d7l m@Z@ d2d8lAmBZB d2d9lAmCZC d2d:lAmDZD d2d;lAmEZE d2d<lAmFZF d2d=lAmGZG d2d>lHmIZI d2d?l?mJZJ d2d@l?mKZK d2dAl?mLZL d2dBl?mMZM d2dCl?mNZN d2dDl?mOZO d2dEl?mPZP d2d7l?m@ZQ d2dFlRmSZS d2dGlTmUZU d2dHl"mVZV d2dIl"mWZW d2dJl"mXZX d2dKl"mYZY d2dLl"mZZZ d2dMl"m[Z[ d2dNl"m\Z\ d2dOl"m]Z] d2dPl"m^Z^ d2dQl"m_Z_ d2dRl"m`Z` d2dSl"maZa d2dTl"mbZb d2dUlcmdZd e edVejf¡ZgdWdXdYdZd[d\d]d^d_d`dadbdcdddedfdgdhdidjdkdldmdndodpdqdrdsdtdudvdwdxdydzd{d|d}d~ddddddd
ddddddddddddddddddddddddddd d¡d¢d£d¤d¥d¦d§d¨d©dªd«d¬dd®d¯d°d±d²d³d´dµd¶d·d¸d¹dºd»d¼hfZhePjiejiePjje+ePjke ePjljmejnePjlejleae6iZoejiejpejlejqejrejsejtejuejvejwejxejyejzej{ej|ej}e\eVe_ebeXePj~ePj~e`e]e[e^e*e(e)eae&e&e,e-e.e/e7eZe:e:e:e9e9eYe9e'eWe+e;d½2ZG d¾d¿ d¿eLjZG dÀdÁ dÁeLjZG dÂdà dÃeLjZ
G dÄdÅ dÅeLjZG dÆdÇ dÇedZG dÈdÉ dÉedZG dÊdË dËeZG dÌdÍ dÍeZG dÎdÏ dÏeGjZG dÐdÑ dÑeCjZG dÒdÓ dÓeBjZG dÔdÕ dÕeBjZG dÖd× d×eCjZdS )Øa.É
|
.. dialect:: postgresql
|
:name: PostgreSQL
|
:full_support: 9.6, 10, 11, 12, 13, 14
|
:normal_support: 9.6+
|
:best_effort: 9+
|
|
.. _postgresql_sequences:
|
|
Sequences/SERIAL/IDENTITY
|
-------------------------
|
|
PostgreSQL supports sequences, and SQLAlchemy uses these as the default means
|
of creating new primary key values for integer-based primary key columns. When
|
creating tables, SQLAlchemy will issue the ``SERIAL`` datatype for
|
integer-based primary key columns, which generates a sequence and server side
|
default corresponding to the column.
|
|
To specify a specific named sequence to be used for primary key generation,
|
use the :func:`~sqlalchemy.schema.Sequence` construct::
|
|
Table(
|
"sometable",
|
metadata,
|
Column(
|
"id", Integer, Sequence("some_id_seq", start=1), primary_key=True
|
)
|
)
|
|
When SQLAlchemy issues a single INSERT statement, to fulfill the contract of
|
having the "last insert identifier" available, a RETURNING clause is added to
|
the INSERT statement which specifies the primary key columns should be
|
returned after the statement completes. The RETURNING functionality only takes
|
place if PostgreSQL 8.2 or later is in use. As a fallback approach, the
|
sequence, whether specified explicitly or implicitly via ``SERIAL``, is
|
executed independently beforehand, the returned value to be used in the
|
subsequent insert. Note that when an
|
:func:`~sqlalchemy.sql.expression.insert()` construct is executed using
|
"executemany" semantics, the "last inserted identifier" functionality does not
|
apply; no RETURNING clause is emitted nor is the sequence pre-executed in this
|
case.
|
|
|
PostgreSQL 10 and above IDENTITY columns
|
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
|
|
PostgreSQL 10 and above have a new IDENTITY feature that supersedes the use
|
of SERIAL. The :class:`_schema.Identity` construct in a
|
:class:`_schema.Column` can be used to control its behavior::
|
|
from sqlalchemy import Table, Column, MetaData, Integer, Computed
|
|
metadata = MetaData()
|
|
data = Table(
|
"data",
|
metadata,
|
Column(
|
'id', Integer, Identity(start=42, cycle=True), primary_key=True
|
),
|
Column('data', String)
|
)
|
|
The CREATE TABLE for the above :class:`_schema.Table` object would be:
|
|
.. sourcecode:: sql
|
|
CREATE TABLE data (
|
id INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 42 CYCLE),
|
data VARCHAR,
|
PRIMARY KEY (id)
|
)
|
|
.. versionchanged:: 1.4 Added :class:`_schema.Identity` construct
|
in a :class:`_schema.Column` to specify the option of an autoincrementing
|
column.
|
|
.. note::
|
|
Previous versions of SQLAlchemy did not have built-in support for rendering
|
of IDENTITY, and could use the following compilation hook to replace
|
occurrences of SERIAL with IDENTITY::
|
|
from sqlalchemy.schema import CreateColumn
|
from sqlalchemy.ext.compiler import compiles
|
|
|
@compiles(CreateColumn, 'postgresql')
|
def use_identity(element, compiler, **kw):
|
text = compiler.visit_create_column(element, **kw)
|
text = text.replace(
|
"SERIAL", "INT GENERATED BY DEFAULT AS IDENTITY"
|
)
|
return text
|
|
Using the above, a table such as::
|
|
t = Table(
|
't', m,
|
Column('id', Integer, primary_key=True),
|
Column('data', String)
|
)
|
|
Will generate on the backing database as::
|
|
CREATE TABLE t (
|
id INT GENERATED BY DEFAULT AS IDENTITY,
|
data VARCHAR,
|
PRIMARY KEY (id)
|
)
|
|
.. _postgresql_ss_cursors:
|
|
Server Side Cursors
|
-------------------
|
|
Server-side cursor support is available for the psycopg2, asyncpg
|
dialects and may also be available in others.
|
|
Server side cursors are enabled on a per-statement basis by using the
|
:paramref:`.Connection.execution_options.stream_results` connection execution
|
option::
|
|
with engine.connect() as conn:
|
result = conn.execution_options(stream_results=True).execute(text("select * from table"))
|
|
Note that some kinds of SQL statements may not be supported with
|
server side cursors; generally, only SQL statements that return rows should be
|
used with this option.
|
|
.. deprecated:: 1.4 The dialect-level server_side_cursors flag is deprecated
|
and will be removed in a future release. Please use the
|
:paramref:`_engine.Connection.stream_results` execution option for
|
unbuffered cursor support.
|
|
.. seealso::
|
|
:ref:`engine_stream_results`
|
|
.. _postgresql_isolation_level:
|
|
Transaction Isolation Level
|
---------------------------
|
|
Most SQLAlchemy dialects support setting of transaction isolation level
|
using the :paramref:`_sa.create_engine.isolation_level` parameter
|
at the :func:`_sa.create_engine` level, and at the :class:`_engine.Connection`
|
level via the :paramref:`.Connection.execution_options.isolation_level`
|
parameter.
|
|
For PostgreSQL dialects, this feature works either by making use of the
|
DBAPI-specific features, such as psycopg2's isolation level flags which will
|
embed the isolation level setting inline with the ``"BEGIN"`` statement, or for
|
DBAPIs with no direct support by emitting ``SET SESSION CHARACTERISTICS AS
|
TRANSACTION ISOLATION LEVEL <level>`` ahead of the ``"BEGIN"`` statement
|
emitted by the DBAPI. For the special AUTOCOMMIT isolation level,
|
DBAPI-specific techniques are used which is typically an ``.autocommit``
|
flag on the DBAPI connection object.
|
|
To set isolation level using :func:`_sa.create_engine`::
|
|
engine = create_engine(
|
"postgresql+pg8000://scott:tiger@localhost/test",
|
isolation_level = "REPEATABLE READ"
|
)
|
|
To set using per-connection execution options::
|
|
with engine.connect() as conn:
|
conn = conn.execution_options(
|
isolation_level="REPEATABLE READ"
|
)
|
with conn.begin():
|
# ... work with transaction
|
|
There are also more options for isolation level configurations, such as
|
"sub-engine" objects linked to a main :class:`_engine.Engine` which each apply
|
different isolation level settings. See the discussion at
|
:ref:`dbapi_autocommit` for background.
|
|
Valid values for ``isolation_level`` on most PostgreSQL dialects include:
|
|
* ``READ COMMITTED``
|
* ``READ UNCOMMITTED``
|
* ``REPEATABLE READ``
|
* ``SERIALIZABLE``
|
* ``AUTOCOMMIT``
|
|
.. seealso::
|
|
:ref:`dbapi_autocommit`
|
|
:ref:`postgresql_readonly_deferrable`
|
|
:ref:`psycopg2_isolation_level`
|
|
:ref:`pg8000_isolation_level`
|
|
.. _postgresql_readonly_deferrable:
|
|
Setting READ ONLY / DEFERRABLE
|
------------------------------
|
|
Most PostgreSQL dialects support setting the "READ ONLY" and "DEFERRABLE"
|
characteristics of the transaction, which is in addition to the isolation level
|
setting. These two attributes can be established either in conjunction with or
|
independently of the isolation level by passing the ``postgresql_readonly`` and
|
``postgresql_deferrable`` flags with
|
:meth:`_engine.Connection.execution_options`. The example below illustrates
|
passing the ``"SERIALIZABLE"`` isolation level at the same time as setting
|
"READ ONLY" and "DEFERRABLE"::
|
|
with engine.connect() as conn:
|
conn = conn.execution_options(
|
isolation_level="SERIALIZABLE",
|
postgresql_readonly=True,
|
postgresql_deferrable=True
|
)
|
with conn.begin():
|
# ... work with transaction
|
|
Note that some DBAPIs such as asyncpg only support "readonly" with
|
SERIALIZABLE isolation.
|
|
.. versionadded:: 1.4 added support for the ``postgresql_readonly``
|
and ``postgresql_deferrable`` execution options.
|
|
.. _postgresql_reset_on_return:
|
|
Temporary Table / Resource Reset for Connection Pooling
|
-------------------------------------------------------
|
|
The :class:`.QueuePool` connection pool implementation used
|
by the SQLAlchemy :class:`.Engine` object includes
|
:ref:`reset on return <pool_reset_on_return>` behavior that will invoke
|
the DBAPI ``.rollback()`` method when connections are returned to the pool.
|
While this rollback will clear out the immediate state used by the previous
|
transaction, it does not cover a wider range of session-level state, including
|
temporary tables as well as other server state such as prepared statement
|
handles and statement caches. The PostgreSQL database includes a variety
|
of commands which may be used to reset this state, including
|
``DISCARD``, ``RESET``, ``DEALLOCATE``, and ``UNLISTEN``.
|
|
|
To install
|
one or more of these commands as the means of performing reset-on-return,
|
the :meth:`.PoolEvents.reset` event hook may be used, as demonstrated
|
in the example below. The implementation
|
will end transactions in progress as well as discard temporary tables
|
using the ``CLOSE``, ``RESET`` and ``DISCARD`` commands; see the PostgreSQL
|
documentation for background on what each of these statements do.
|
|
The :paramref:`_sa.create_engine.pool_reset_on_return` parameter
|
is set to ``None`` so that the custom scheme can replace the default behavior
|
completely. The custom hook implementation calls ``.rollback()`` in any case,
|
as it's usually important that the DBAPI's own tracking of commit/rollback
|
will remain consistent with the state of the transaction::
|
|
|
from sqlalchemy import create_engine
|
from sqlalchemy import event
|
|
postgresql_engine = create_engine(
|
"postgresql+pyscopg2://scott:tiger@hostname/dbname",
|
|
# disable default reset-on-return scheme
|
pool_reset_on_return=None,
|
)
|
|
|
@event.listens_for(postgresql_engine, "reset")
|
def _reset_postgresql(dbapi_connection, connection_record, reset_state):
|
if not reset_state.terminate_only:
|
dbapi_connection.execute("CLOSE ALL")
|
dbapi_connection.execute("RESET ALL")
|
dbapi_connection.execute("DISCARD TEMP")
|
|
# so that the DBAPI itself knows that the connection has been
|
# reset
|
dbapi_connection.rollback()
|
|
.. versionchanged:: 2.0.0b3 Added additional state arguments to
|
the :meth:`.PoolEvents.reset` event and additionally ensured the event
|
is invoked for all "reset" occurrences, so that it's appropriate
|
as a place for custom "reset" handlers. Previous schemes which
|
use the :meth:`.PoolEvents.checkin` handler remain usable as well.
|
|
.. seealso::
|
|
:ref:`pool_reset_on_return` - in the :ref:`pooling_toplevel` documentation
|
|
.. _postgresql_alternate_search_path:
|
|
Setting Alternate Search Paths on Connect
|
------------------------------------------
|
|
The PostgreSQL ``search_path`` variable refers to the list of schema names
|
that will be implicitly referred towards when a particular table or other
|
object is referenced in a SQL statement. As detailed in the next section
|
:ref:`postgresql_schema_reflection`, SQLAlchemy is generally organized around
|
the concept of keeping this variable at its default value of ``public``,
|
however, in order to have it set to any arbitrary name or names when connections
|
are used automatically, the "SET SESSION search_path" command may be invoked
|
for all connections in a pool using the following event handler, as discussed
|
at :ref:`schema_set_default_connections`::
|
|
from sqlalchemy import event
|
from sqlalchemy import create_engine
|
|
engine = create_engine("postgresql+psycopg2://scott:tiger@host/dbname")
|
|
@event.listens_for(engine, "connect", insert=True)
|
def set_search_path(dbapi_connection, connection_record):
|
existing_autocommit = dbapi_connection.autocommit
|
dbapi_connection.autocommit = True
|
cursor = dbapi_connection.cursor()
|
cursor.execute("SET SESSION search_path='%s'" % schema_name)
|
cursor.close()
|
dbapi_connection.autocommit = existing_autocommit
|
|
The reason the recipe is complicated by use of the ``.autocommit`` DBAPI
|
attribute is so that when the ``SET SESSION search_path`` directive is invoked,
|
it is invoked outside of the scope of any transaction and therefore will not
|
be reverted when the DBAPI connection has a rollback.
|
|
.. seealso::
|
|
:ref:`schema_set_default_connections` - in the :ref:`metadata_toplevel` documentation
|
|
|
|
|
.. _postgresql_schema_reflection:
|
|
Remote-Schema Table Introspection and PostgreSQL search_path
|
------------------------------------------------------------
|
|
.. admonition:: Section Best Practices Summarized
|
|
keep the ``search_path`` variable set to its default of ``public``, without
|
any other schema names. For other schema names, name these explicitly
|
within :class:`_schema.Table` definitions. Alternatively, the
|
``postgresql_ignore_search_path`` option will cause all reflected
|
:class:`_schema.Table` objects to have a :attr:`_schema.Table.schema`
|
attribute set up.
|
|
The PostgreSQL dialect can reflect tables from any schema, as outlined in
|
:ref:`metadata_reflection_schemas`.
|
|
With regards to tables which these :class:`_schema.Table`
|
objects refer to via foreign key constraint, a decision must be made as to how
|
the ``.schema`` is represented in those remote tables, in the case where that
|
remote schema name is also a member of the current
|
`PostgreSQL search path
|
<https://www.postgresql.org/docs/current/static/ddl-schemas.html#DDL-SCHEMAS-PATH>`_.
|
|
By default, the PostgreSQL dialect mimics the behavior encouraged by
|
PostgreSQL's own ``pg_get_constraintdef()`` builtin procedure. This function
|
returns a sample definition for a particular foreign key constraint,
|
omitting the referenced schema name from that definition when the name is
|
also in the PostgreSQL schema search path. The interaction below
|
illustrates this behavior::
|
|
test=> CREATE TABLE test_schema.referred(id INTEGER PRIMARY KEY);
|
CREATE TABLE
|
test=> CREATE TABLE referring(
|
test(> id INTEGER PRIMARY KEY,
|
test(> referred_id INTEGER REFERENCES test_schema.referred(id));
|
CREATE TABLE
|
test=> SET search_path TO public, test_schema;
|
test=> SELECT pg_catalog.pg_get_constraintdef(r.oid, true) FROM
|
test-> pg_catalog.pg_class c JOIN pg_catalog.pg_namespace n
|
test-> ON n.oid = c.relnamespace
|
test-> JOIN pg_catalog.pg_constraint r ON c.oid = r.conrelid
|
test-> WHERE c.relname='referring' AND r.contype = 'f'
|
test-> ;
|
pg_get_constraintdef
|
---------------------------------------------------
|
FOREIGN KEY (referred_id) REFERENCES referred(id)
|
(1 row)
|
|
Above, we created a table ``referred`` as a member of the remote schema
|
``test_schema``, however when we added ``test_schema`` to the
|
PG ``search_path`` and then asked ``pg_get_constraintdef()`` for the
|
``FOREIGN KEY`` syntax, ``test_schema`` was not included in the output of
|
the function.
|
|
On the other hand, if we set the search path back to the typical default
|
of ``public``::
|
|
test=> SET search_path TO public;
|
SET
|
|
The same query against ``pg_get_constraintdef()`` now returns the fully
|
schema-qualified name for us::
|
|
test=> SELECT pg_catalog.pg_get_constraintdef(r.oid, true) FROM
|
test-> pg_catalog.pg_class c JOIN pg_catalog.pg_namespace n
|
test-> ON n.oid = c.relnamespace
|
test-> JOIN pg_catalog.pg_constraint r ON c.oid = r.conrelid
|
test-> WHERE c.relname='referring' AND r.contype = 'f';
|
pg_get_constraintdef
|
---------------------------------------------------------------
|
FOREIGN KEY (referred_id) REFERENCES test_schema.referred(id)
|
(1 row)
|
|
SQLAlchemy will by default use the return value of ``pg_get_constraintdef()``
|
in order to determine the remote schema name. That is, if our ``search_path``
|
were set to include ``test_schema``, and we invoked a table
|
reflection process as follows::
|
|
>>> from sqlalchemy import Table, MetaData, create_engine, text
|
>>> engine = create_engine("postgresql+psycopg2://scott:tiger@localhost/test")
|
>>> with engine.connect() as conn:
|
... conn.execute(text("SET search_path TO test_schema, public"))
|
... metadata_obj = MetaData()
|
... referring = Table('referring', metadata_obj,
|
... autoload_with=conn)
|
...
|
<sqlalchemy.engine.result.CursorResult object at 0x101612ed0>
|
|
The above process would deliver to the :attr:`_schema.MetaData.tables`
|
collection
|
``referred`` table named **without** the schema::
|
|
>>> metadata_obj.tables['referred'].schema is None
|
True
|
|
To alter the behavior of reflection such that the referred schema is
|
maintained regardless of the ``search_path`` setting, use the
|
``postgresql_ignore_search_path`` option, which can be specified as a
|
dialect-specific argument to both :class:`_schema.Table` as well as
|
:meth:`_schema.MetaData.reflect`::
|
|
>>> with engine.connect() as conn:
|
... conn.execute(text("SET search_path TO test_schema, public"))
|
... metadata_obj = MetaData()
|
... referring = Table('referring', metadata_obj,
|
... autoload_with=conn,
|
... postgresql_ignore_search_path=True)
|
...
|
<sqlalchemy.engine.result.CursorResult object at 0x1016126d0>
|
|
We will now have ``test_schema.referred`` stored as schema-qualified::
|
|
>>> metadata_obj.tables['test_schema.referred'].schema
|
'test_schema'
|
|
.. sidebar:: Best Practices for PostgreSQL Schema reflection
|
|
The description of PostgreSQL schema reflection behavior is complex, and
|
is the product of many years of dealing with widely varied use cases and
|
user preferences. But in fact, there's no need to understand any of it if
|
you just stick to the simplest use pattern: leave the ``search_path`` set
|
to its default of ``public`` only, never refer to the name ``public`` as
|
an explicit schema name otherwise, and refer to all other schema names
|
explicitly when building up a :class:`_schema.Table` object. The options
|
described here are only for those users who can't, or prefer not to, stay
|
within these guidelines.
|
|
Note that **in all cases**, the "default" schema is always reflected as
|
``None``. The "default" schema on PostgreSQL is that which is returned by the
|
PostgreSQL ``current_schema()`` function. On a typical PostgreSQL
|
installation, this is the name ``public``. So a table that refers to another
|
which is in the ``public`` (i.e. default) schema will always have the
|
``.schema`` attribute set to ``None``.
|
|
.. seealso::
|
|
:ref:`reflection_schema_qualified_interaction` - discussion of the issue
|
from a backend-agnostic perspective
|
|
`The Schema Search Path
|
<https://www.postgresql.org/docs/current/static/ddl-schemas.html#DDL-SCHEMAS-PATH>`_
|
- on the PostgreSQL website.
|
|
INSERT/UPDATE...RETURNING
|
-------------------------
|
|
The dialect supports PG 8.2's ``INSERT..RETURNING``, ``UPDATE..RETURNING`` and
|
``DELETE..RETURNING`` syntaxes. ``INSERT..RETURNING`` is used by default
|
for single-row INSERT statements in order to fetch newly generated
|
primary key identifiers. To specify an explicit ``RETURNING`` clause,
|
use the :meth:`._UpdateBase.returning` method on a per-statement basis::
|
|
# INSERT..RETURNING
|
result = table.insert().returning(table.c.col1, table.c.col2).\
|
values(name='foo')
|
print(result.fetchall())
|
|
# UPDATE..RETURNING
|
result = table.update().returning(table.c.col1, table.c.col2).\
|
where(table.c.name=='foo').values(name='bar')
|
print(result.fetchall())
|
|
# DELETE..RETURNING
|
result = table.delete().returning(table.c.col1, table.c.col2).\
|
where(table.c.name=='foo')
|
print(result.fetchall())
|
|
.. _postgresql_insert_on_conflict:
|
|
INSERT...ON CONFLICT (Upsert)
|
------------------------------
|
|
Starting with version 9.5, PostgreSQL allows "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
|
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 existing unique constraints and indexes. These
|
constraints may be identified either using their name as stated in DDL,
|
or they may be inferred by stating the columns and conditions that comprise
|
the indexes.
|
|
SQLAlchemy provides ``ON CONFLICT`` support via the PostgreSQL-specific
|
:func:`_postgresql.insert()` function, which provides
|
the generative methods :meth:`_postgresql.Insert.on_conflict_do_update`
|
and :meth:`~.postgresql.Insert.on_conflict_do_nothing`:
|
|
.. sourcecode:: pycon+sql
|
|
>>> from sqlalchemy.dialects.postgresql import insert
|
>>> insert_stmt = insert(my_table).values(
|
... id='some_existing_id',
|
... data='inserted value')
|
>>> do_nothing_stmt = insert_stmt.on_conflict_do_nothing(
|
... index_elements=['id']
|
... )
|
>>> print(do_nothing_stmt)
|
{printsql}INSERT INTO my_table (id, data) VALUES (%(id)s, %(data)s)
|
ON CONFLICT (id) DO NOTHING
|
{stop}
|
|
>>> do_update_stmt = insert_stmt.on_conflict_do_update(
|
... constraint='pk_my_table',
|
... set_=dict(data='updated value')
|
... )
|
>>> print(do_update_stmt)
|
{printsql}INSERT INTO my_table (id, data) VALUES (%(id)s, %(data)s)
|
ON CONFLICT ON CONSTRAINT pk_my_table DO UPDATE SET data = %(param_1)s
|
|
.. seealso::
|
|
`INSERT .. ON CONFLICT
|
<https://www.postgresql.org/docs/current/static/sql-insert.html#SQL-ON-CONFLICT>`_
|
- in the PostgreSQL documentation.
|
|
Specifying the Target
|
^^^^^^^^^^^^^^^^^^^^^
|
|
Both methods supply the "target" of the conflict using either the
|
named constraint or by column inference:
|
|
* The :paramref:`_postgresql.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:
|
|
.. sourcecode:: pycon+sql
|
|
>>> 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 (%(id)s, %(data)s)
|
ON CONFLICT (id) DO UPDATE SET data = %(param_1)s
|
{stop}
|
|
>>> do_update_stmt = insert_stmt.on_conflict_do_update(
|
... index_elements=[my_table.c.id],
|
... set_=dict(data='updated value')
|
... )
|
>>> print(do_update_stmt)
|
{printsql}INSERT INTO my_table (id, data) VALUES (%(id)s, %(data)s)
|
ON CONFLICT (id) DO UPDATE SET data = %(param_1)s
|
|
* When using :paramref:`_postgresql.Insert.on_conflict_do_update.index_elements` to
|
infer an index, a partial index can be inferred by also specifying the
|
use the :paramref:`_postgresql.Insert.on_conflict_do_update.index_where` parameter:
|
|
.. sourcecode:: pycon+sql
|
|
>>> stmt = insert(my_table).values(user_email='a@b.com', data='inserted data')
|
>>> 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(stmt)
|
{printsql}INSERT INTO my_table (data, user_email)
|
VALUES (%(data)s, %(user_email)s) ON CONFLICT (user_email)
|
WHERE user_email LIKE %(user_email_1)s DO UPDATE SET data = excluded.data
|
|
* The :paramref:`_postgresql.Insert.on_conflict_do_update.constraint` argument is
|
used to specify an index directly rather than inferring it. This can be
|
the name of a UNIQUE constraint, a PRIMARY KEY constraint, or an INDEX:
|
|
.. sourcecode:: pycon+sql
|
|
>>> do_update_stmt = insert_stmt.on_conflict_do_update(
|
... constraint='my_table_idx_1',
|
... set_=dict(data='updated value')
|
... )
|
>>> print(do_update_stmt)
|
{printsql}INSERT INTO my_table (id, data) VALUES (%(id)s, %(data)s)
|
ON CONFLICT ON CONSTRAINT my_table_idx_1 DO UPDATE SET data = %(param_1)s
|
{stop}
|
|
>>> do_update_stmt = insert_stmt.on_conflict_do_update(
|
... constraint='my_table_pk',
|
... set_=dict(data='updated value')
|
... )
|
>>> print(do_update_stmt)
|
{printsql}INSERT INTO my_table (id, data) VALUES (%(id)s, %(data)s)
|
ON CONFLICT ON CONSTRAINT my_table_pk DO UPDATE SET data = %(param_1)s
|
{stop}
|
|
* The :paramref:`_postgresql.Insert.on_conflict_do_update.constraint` argument may
|
also refer to a SQLAlchemy construct representing a constraint,
|
e.g. :class:`.UniqueConstraint`, :class:`.PrimaryKeyConstraint`,
|
:class:`.Index`, or :class:`.ExcludeConstraint`. In this use,
|
if the constraint has a name, it is used directly. Otherwise, if the
|
constraint is unnamed, then inference will be used, where the expressions
|
and optional WHERE clause of the constraint will be spelled out in the
|
construct. This use is especially convenient
|
to refer to the named or unnamed primary key of a :class:`_schema.Table`
|
using the
|
:attr:`_schema.Table.primary_key` attribute:
|
|
.. sourcecode:: pycon+sql
|
|
>>> do_update_stmt = insert_stmt.on_conflict_do_update(
|
... constraint=my_table.primary_key,
|
... set_=dict(data='updated value')
|
... )
|
>>> print(do_update_stmt)
|
{printsql}INSERT INTO my_table (id, data) VALUES (%(id)s, %(data)s)
|
ON CONFLICT (id) DO UPDATE SET data = %(param_1)s
|
|
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:`_postgresql.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 (%(id)s, %(data)s)
|
ON CONFLICT (id) DO UPDATE SET data = %(param_1)s
|
|
.. warning::
|
|
The :meth:`_expression.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:`_postgresql.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:`~.postgresql.Insert.excluded` is available as an attribute on
|
the :class:`_postgresql.Insert` object; this object is a
|
:class:`_expression.ColumnCollection`
|
which alias contains all columns of the target
|
table:
|
|
.. 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 (%(id)s, %(data)s, %(author)s)
|
ON CONFLICT (id) DO UPDATE SET data = %(param_1)s, author = excluded.author
|
|
Additional WHERE Criteria
|
^^^^^^^^^^^^^^^^^^^^^^^^^
|
|
The :meth:`_expression.Insert.on_conflict_do_update` method also accepts
|
a WHERE clause using the :paramref:`_postgresql.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 (%(id)s, %(data)s, %(author)s)
|
ON CONFLICT (id) DO UPDATE SET data = %(param_1)s, author = excluded.author
|
WHERE my_table.status = %(status_1)s
|
|
Skipping Rows with DO NOTHING
|
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
|
|
``ON CONFLICT`` may be used to skip inserting a row entirely
|
if any conflict with a unique or exclusion constraint occurs; below
|
this is illustrated using the
|
:meth:`~.postgresql.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 (%(id)s, %(data)s)
|
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 or exclusion
|
constraint 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 (%(id)s, %(data)s)
|
ON CONFLICT DO NOTHING
|
|
.. _postgresql_match:
|
|
Full Text Search
|
----------------
|
|
PostgreSQL's full text search system is available through the use of the
|
:data:`.func` namespace, combined with the use of custom operators
|
via the :meth:`.Operators.bool_op` method. For simple cases with some
|
degree of cross-backend compatibility, the :meth:`.Operators.match` operator
|
may also be used.
|
|
.. _postgresql_simple_match:
|
|
Simple plain text matching with ``match()``
|
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
|
|
The :meth:`.Operators.match` operator provides for cross-compatible simple
|
text matching. For the PostgreSQL backend, it's hardcoded to generate
|
an expression using the ``@@`` operator in conjunction with the
|
``plainto_tsquery()`` PostgreSQL function.
|
|
On the PostgreSQL dialect, an expression like the following::
|
|
select(sometable.c.text.match("search string"))
|
|
would emit to the database::
|
|
SELECT text @@ plainto_tsquery('search string') FROM table
|
|
Above, passing a plain string to :meth:`.Operators.match` will automatically
|
make use of ``plainto_tsquery()`` to specify the type of tsquery. This
|
establishes basic database cross-compatibility for :meth:`.Operators.match`
|
with other backends.
|
|
.. versionchanged:: 2.0 The default tsquery generation function used by the
|
PostgreSQL dialect with :meth:`.Operators.match` is ``plainto_tsquery()``.
|
|
To render exactly what was rendered in 1.4, use the following form::
|
|
from sqlalchemy import func
|
|
select(
|
sometable.c.text.bool_op("@@")(func.to_tsquery("search string"))
|
)
|
|
Which would emit::
|
|
SELECT text @@ to_tsquery('search string') FROM table
|
|
Using PostgreSQL full text functions and operators directly
|
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
|
|
Text search operations beyond the simple use of :meth:`.Operators.match`
|
may make use of the :data:`.func` namespace to generate PostgreSQL full-text
|
functions, in combination with :meth:`.Operators.bool_op` to generate
|
any boolean operator.
|
|
For example, the query::
|
|
select(
|
func.to_tsquery('cat').bool_op("@>")(func.to_tsquery('cat & rat'))
|
)
|
|
would generate:
|
|
.. sourcecode:: sql
|
|
SELECT to_tsquery('cat') @> to_tsquery('cat & rat')
|
|
|
The :class:`_postgresql.TSVECTOR` type can provide for explicit CAST::
|
|
from sqlalchemy.dialects.postgresql import TSVECTOR
|
from sqlalchemy import select, cast
|
select(cast("some text", TSVECTOR))
|
|
produces a statement equivalent to::
|
|
SELECT CAST('some text' AS TSVECTOR) AS anon_1
|
|
The ``func`` namespace is augmented by the PostgreSQL dialect to set up
|
correct argument and return types for most full text search functions.
|
These functions are used automatically by the :attr:`_sql.func` namespace
|
assuming the ``sqlalchemy.dialects.postgresql`` package has been imported,
|
or :func:`_sa.create_engine` has been invoked using a ``postgresql``
|
dialect. These functions are documented at:
|
|
* :class:`_postgresql.to_tsvector`
|
* :class:`_postgresql.to_tsquery`
|
* :class:`_postgresql.plainto_tsquery`
|
* :class:`_postgresql.phraseto_tsquery`
|
* :class:`_postgresql.websearch_to_tsquery`
|
* :class:`_postgresql.ts_headline`
|
|
Specifying the "regconfig" with ``match()`` or custom operators
|
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
|
|
PostgreSQL's ``plainto_tsquery()`` function accepts an optional
|
"regconfig" argument that is used to instruct PostgreSQL to use a
|
particular pre-computed GIN or GiST index in order to perform the search.
|
When using :meth:`.Operators.match`, this additional parameter may be
|
specified using the ``postgresql_regconfig`` parameter, such as::
|
|
select(mytable.c.id).where(
|
mytable.c.title.match('somestring', postgresql_regconfig='english')
|
)
|
|
Which would emit::
|
|
SELECT mytable.id FROM mytable
|
WHERE mytable.title @@ plainto_tsquery('english', 'somestring')
|
|
When using other PostgreSQL search functions with :data:`.func`, the
|
"regconfig" parameter may be passed directly as the initial argument::
|
|
select(mytable.c.id).where(
|
func.to_tsvector("english", mytable.c.title).bool_op("@@")(
|
func.to_tsquery("english", "somestring")
|
)
|
)
|
|
produces a statement equivalent to::
|
|
SELECT mytable.id FROM mytable
|
WHERE to_tsvector('english', mytable.title) @@
|
to_tsquery('english', 'somestring')
|
|
It is recommended that you use the ``EXPLAIN ANALYZE...`` tool from
|
PostgreSQL to ensure that you are generating queries with SQLAlchemy that
|
take full advantage of any indexes you may have created for full text search.
|
|
.. seealso::
|
|
`Full Text Search <https://www.postgresql.org/docs/current/textsearch-controls.html>`_ - in the PostgreSQL documentation
|
|
|
FROM ONLY ...
|
-------------
|
|
The dialect supports PostgreSQL's ONLY keyword for targeting only a particular
|
table in an inheritance hierarchy. This can be used to produce the
|
``SELECT ... FROM ONLY``, ``UPDATE ONLY ...``, and ``DELETE FROM ONLY ...``
|
syntaxes. It uses SQLAlchemy's hints mechanism::
|
|
# SELECT ... FROM ONLY ...
|
result = table.select().with_hint(table, 'ONLY', 'postgresql')
|
print(result.fetchall())
|
|
# UPDATE ONLY ...
|
table.update(values=dict(foo='bar')).with_hint('ONLY',
|
dialect_name='postgresql')
|
|
# DELETE FROM ONLY ...
|
table.delete().with_hint('ONLY', dialect_name='postgresql')
|
|
|
.. _postgresql_indexes:
|
|
PostgreSQL-Specific Index Options
|
---------------------------------
|
|
Several extensions to the :class:`.Index` construct are available, specific
|
to the PostgreSQL dialect.
|
|
Covering Indexes
|
^^^^^^^^^^^^^^^^
|
|
The ``postgresql_include`` option renders INCLUDE(colname) for the given
|
string names::
|
|
Index("my_index", table.c.x, postgresql_include=['y'])
|
|
would render the index as ``CREATE INDEX my_index ON table (x) INCLUDE (y)``
|
|
Note that this feature requires PostgreSQL 11 or later.
|
|
.. versionadded:: 1.4
|
|
.. _postgresql_partial_indexes:
|
|
Partial Indexes
|
^^^^^^^^^^^^^^^
|
|
Partial indexes add criterion to the index definition so that the index is
|
applied to a subset of rows. These can be specified on :class:`.Index`
|
using the ``postgresql_where`` keyword argument::
|
|
Index('my_index', my_table.c.id, postgresql_where=my_table.c.value > 10)
|
|
.. _postgresql_operator_classes:
|
|
Operator Classes
|
^^^^^^^^^^^^^^^^
|
|
PostgreSQL allows the specification of an *operator class* for each column of
|
an index (see
|
https://www.postgresql.org/docs/current/interactive/indexes-opclass.html).
|
The :class:`.Index` construct allows these to be specified via the
|
``postgresql_ops`` keyword argument::
|
|
Index(
|
'my_index', my_table.c.id, my_table.c.data,
|
postgresql_ops={
|
'data': 'text_pattern_ops',
|
'id': 'int4_ops'
|
})
|
|
Note that the keys in the ``postgresql_ops`` dictionaries are the
|
"key" name of the :class:`_schema.Column`, i.e. the name used to access it from
|
the ``.c`` collection of :class:`_schema.Table`, which can be configured to be
|
different than the actual name of the column as expressed in the database.
|
|
If ``postgresql_ops`` is to be used against a complex SQL expression such
|
as a function call, then to apply to the column it must be given a label
|
that is identified in the dictionary by name, e.g.::
|
|
Index(
|
'my_index', my_table.c.id,
|
func.lower(my_table.c.data).label('data_lower'),
|
postgresql_ops={
|
'data_lower': 'text_pattern_ops',
|
'id': 'int4_ops'
|
})
|
|
Operator classes are also supported by the
|
:class:`_postgresql.ExcludeConstraint` construct using the
|
:paramref:`_postgresql.ExcludeConstraint.ops` parameter. See that parameter for
|
details.
|
|
.. versionadded:: 1.3.21 added support for operator classes with
|
:class:`_postgresql.ExcludeConstraint`.
|
|
|
Index Types
|
^^^^^^^^^^^
|
|
PostgreSQL provides several index types: B-Tree, Hash, GiST, and GIN, as well
|
as the ability for users to create their own (see
|
https://www.postgresql.org/docs/current/static/indexes-types.html). These can be
|
specified on :class:`.Index` using the ``postgresql_using`` keyword argument::
|
|
Index('my_index', my_table.c.data, postgresql_using='gin')
|
|
The value passed to the keyword argument will be simply passed through to the
|
underlying CREATE INDEX command, so it *must* be a valid index type for your
|
version of PostgreSQL.
|
|
.. _postgresql_index_storage:
|
|
Index Storage Parameters
|
^^^^^^^^^^^^^^^^^^^^^^^^
|
|
PostgreSQL allows storage parameters to be set on indexes. The storage
|
parameters available depend on the index method used by the index. Storage
|
parameters can be specified on :class:`.Index` using the ``postgresql_with``
|
keyword argument::
|
|
Index('my_index', my_table.c.data, postgresql_with={"fillfactor": 50})
|
|
PostgreSQL allows to define the tablespace in which to create the index.
|
The tablespace can be specified on :class:`.Index` using the
|
``postgresql_tablespace`` keyword argument::
|
|
Index('my_index', my_table.c.data, postgresql_tablespace='my_tablespace')
|
|
Note that the same option is available on :class:`_schema.Table` as well.
|
|
.. _postgresql_index_concurrently:
|
|
Indexes with CONCURRENTLY
|
^^^^^^^^^^^^^^^^^^^^^^^^^
|
|
The PostgreSQL index option CONCURRENTLY is supported by passing the
|
flag ``postgresql_concurrently`` to the :class:`.Index` construct::
|
|
tbl = Table('testtbl', m, Column('data', Integer))
|
|
idx1 = Index('test_idx1', tbl.c.data, postgresql_concurrently=True)
|
|
The above index construct will render DDL for CREATE INDEX, assuming
|
PostgreSQL 8.2 or higher is detected or for a connection-less dialect, as::
|
|
CREATE INDEX CONCURRENTLY test_idx1 ON testtbl (data)
|
|
For DROP INDEX, assuming PostgreSQL 9.2 or higher is detected or for
|
a connection-less dialect, it will emit::
|
|
DROP INDEX CONCURRENTLY test_idx1
|
|
When using CONCURRENTLY, the PostgreSQL database requires that the statement
|
be invoked outside of a transaction block. The Python DBAPI enforces that
|
even for a single statement, a transaction is present, so to use this
|
construct, the DBAPI's "autocommit" mode must be used::
|
|
metadata = MetaData()
|
table = Table(
|
"foo", metadata,
|
Column("id", String))
|
index = Index(
|
"foo_idx", table.c.id, postgresql_concurrently=True)
|
|
with engine.connect() as conn:
|
with conn.execution_options(isolation_level='AUTOCOMMIT'):
|
table.create(conn)
|
|
.. seealso::
|
|
:ref:`postgresql_isolation_level`
|
|
.. _postgresql_index_reflection:
|
|
PostgreSQL Index Reflection
|
---------------------------
|
|
The PostgreSQL database creates a UNIQUE INDEX implicitly whenever the
|
UNIQUE CONSTRAINT construct is used. When inspecting a table using
|
:class:`_reflection.Inspector`, the :meth:`_reflection.Inspector.get_indexes`
|
and the :meth:`_reflection.Inspector.get_unique_constraints`
|
will report on these
|
two constructs distinctly; in the case of the index, the key
|
``duplicates_constraint`` will be present in the index entry if it is
|
detected as mirroring a constraint. When performing reflection using
|
``Table(..., autoload_with=engine)``, the UNIQUE INDEX is **not** returned
|
in :attr:`_schema.Table.indexes` when it is detected as mirroring a
|
:class:`.UniqueConstraint` in the :attr:`_schema.Table.constraints` collection
|
.
|
|
Special Reflection Options
|
--------------------------
|
|
The :class:`_reflection.Inspector`
|
used for the PostgreSQL backend is an instance
|
of :class:`.PGInspector`, which offers additional methods::
|
|
from sqlalchemy import create_engine, inspect
|
|
engine = create_engine("postgresql+psycopg2://localhost/test")
|
insp = inspect(engine) # will be a PGInspector
|
|
print(insp.get_enums())
|
|
.. autoclass:: PGInspector
|
:members:
|
|
.. _postgresql_table_options:
|
|
PostgreSQL Table Options
|
------------------------
|
|
Several options for CREATE TABLE are supported directly by the PostgreSQL
|
dialect in conjunction with the :class:`_schema.Table` construct:
|
|
* ``TABLESPACE``::
|
|
Table("some_table", metadata, ..., postgresql_tablespace='some_tablespace')
|
|
The above option is also available on the :class:`.Index` construct.
|
|
* ``ON COMMIT``::
|
|
Table("some_table", metadata, ..., postgresql_on_commit='PRESERVE ROWS')
|
|
* ``WITH OIDS``::
|
|
Table("some_table", metadata, ..., postgresql_with_oids=True)
|
|
* ``WITHOUT OIDS``::
|
|
Table("some_table", metadata, ..., postgresql_with_oids=False)
|
|
* ``INHERITS``::
|
|
Table("some_table", metadata, ..., postgresql_inherits="some_supertable")
|
|
Table("some_table", metadata, ..., postgresql_inherits=("t1", "t2", ...))
|
|
* ``PARTITION BY``::
|
|
Table("some_table", metadata, ...,
|
postgresql_partition_by='LIST (part_column)')
|
|
.. versionadded:: 1.2.6
|
|
.. seealso::
|
|
`PostgreSQL CREATE TABLE options
|
<https://www.postgresql.org/docs/current/static/sql-createtable.html>`_ -
|
in the PostgreSQL documentation.
|
|
.. _postgresql_constraint_options:
|
|
PostgreSQL Constraint Options
|
-----------------------------
|
|
The following option(s) are supported by the PostgreSQL dialect in conjunction
|
with selected constraint constructs:
|
|
* ``NOT VALID``: This option applies towards CHECK and FOREIGN KEY constraints
|
when the constraint is being added to an existing table via ALTER TABLE,
|
and has the effect that existing rows are not scanned during the ALTER
|
operation against the constraint being added.
|
|
When using a SQL migration tool such as `Alembic <https://alembic.sqlalchemy.org>`_
|
that renders ALTER TABLE constructs, the ``postgresql_not_valid`` argument
|
may be specified as an additional keyword argument within the operation
|
that creates the constraint, as in the following Alembic example::
|
|
def update():
|
op.create_foreign_key(
|
"fk_user_address",
|
"address",
|
"user",
|
["user_id"],
|
["id"],
|
postgresql_not_valid=True
|
)
|
|
The keyword is ultimately accepted directly by the
|
:class:`_schema.CheckConstraint`, :class:`_schema.ForeignKeyConstraint`
|
and :class:`_schema.ForeignKey` constructs; when using a tool like
|
Alembic, dialect-specific keyword arguments are passed through to
|
these constructs from the migration operation directives::
|
|
CheckConstraint("some_field IS NOT NULL", postgresql_not_valid=True)
|
|
ForeignKeyConstraint(["some_id"], ["some_table.some_id"], postgresql_not_valid=True)
|
|
.. versionadded:: 1.4.32
|
|
.. seealso::
|
|
`PostgreSQL ALTER TABLE options
|
<https://www.postgresql.org/docs/current/static/sql-altertable.html>`_ -
|
in the PostgreSQL documentation.
|
|
.. _postgresql_table_valued_overview:
|
|
Table values, Table and Column valued functions, Row and Tuple objects
|
-----------------------------------------------------------------------
|
|
PostgreSQL makes great use of modern SQL forms such as table-valued functions,
|
tables and rows as values. These constructs are commonly used as part
|
of PostgreSQL's support for complex datatypes such as JSON, ARRAY, and other
|
datatypes. SQLAlchemy's SQL expression language has native support for
|
most table-valued and row-valued forms.
|
|
.. _postgresql_table_valued:
|
|
Table-Valued Functions
|
^^^^^^^^^^^^^^^^^^^^^^^
|
|
Many PostgreSQL built-in functions are intended to be used in the FROM clause
|
of a SELECT statement, and are capable of returning table rows or sets of table
|
rows. A large portion of PostgreSQL's JSON functions for example such as
|
``json_array_elements()``, ``json_object_keys()``, ``json_each_text()``,
|
``json_each()``, ``json_to_record()``, ``json_populate_recordset()`` use such
|
forms. These classes of SQL function calling forms in SQLAlchemy are available
|
using the :meth:`_functions.FunctionElement.table_valued` method in conjunction
|
with :class:`_functions.Function` objects generated from the :data:`_sql.func`
|
namespace.
|
|
Examples from PostgreSQL's reference documentation follow below:
|
|
* ``json_each()``:
|
|
.. sourcecode:: pycon+sql
|
|
>>> from sqlalchemy import select, func
|
>>> stmt = select(func.json_each('{"a":"foo", "b":"bar"}').table_valued("key", "value"))
|
>>> print(stmt)
|
{printsql}SELECT anon_1.key, anon_1.value
|
FROM json_each(:json_each_1) AS anon_1
|
|
* ``json_populate_record()``:
|
|
.. sourcecode:: pycon+sql
|
|
>>> from sqlalchemy import select, func, literal_column
|
>>> stmt = select(
|
... func.json_populate_record(
|
... literal_column("null::myrowtype"),
|
... '{"a":1,"b":2}'
|
... ).table_valued("a", "b", name="x")
|
... )
|
>>> print(stmt)
|
{printsql}SELECT x.a, x.b
|
FROM json_populate_record(null::myrowtype, :json_populate_record_1) AS x
|
|
* ``json_to_record()`` - this form uses a PostgreSQL specific form of derived
|
columns in the alias, where we may make use of :func:`_sql.column` elements with
|
types to produce them. The :meth:`_functions.FunctionElement.table_valued`
|
method produces a :class:`_sql.TableValuedAlias` construct, and the method
|
:meth:`_sql.TableValuedAlias.render_derived` method sets up the derived
|
columns specification:
|
|
.. sourcecode:: pycon+sql
|
|
>>> from sqlalchemy import select, func, column, Integer, Text
|
>>> stmt = select(
|
... func.json_to_record('{"a":1,"b":[1,2,3],"c":"bar"}').table_valued(
|
... column("a", Integer), column("b", Text), column("d", Text),
|
... ).render_derived(name="x", with_types=True)
|
... )
|
>>> print(stmt)
|
{printsql}SELECT x.a, x.b, x.d
|
FROM json_to_record(:json_to_record_1) AS x(a INTEGER, b TEXT, d TEXT)
|
|
* ``WITH ORDINALITY`` - part of the SQL standard, ``WITH ORDINALITY`` adds an
|
ordinal counter to the output of a function and is accepted by a limited set
|
of PostgreSQL functions including ``unnest()`` and ``generate_series()``. The
|
:meth:`_functions.FunctionElement.table_valued` method accepts a keyword
|
parameter ``with_ordinality`` for this purpose, which accepts the string name
|
that will be applied to the "ordinality" column:
|
|
.. sourcecode:: pycon+sql
|
|
>>> from sqlalchemy import select, func
|
>>> stmt = select(
|
... func.generate_series(4, 1, -1).
|
... table_valued("value", with_ordinality="ordinality").
|
... render_derived()
|
... )
|
>>> print(stmt)
|
{printsql}SELECT anon_1.value, anon_1.ordinality
|
FROM generate_series(:generate_series_1, :generate_series_2, :generate_series_3)
|
WITH ORDINALITY AS anon_1(value, ordinality)
|
|
.. versionadded:: 1.4.0b2
|
|
.. seealso::
|
|
:ref:`tutorial_functions_table_valued` - in the :ref:`unified_tutorial`
|
|
.. _postgresql_column_valued:
|
|
Column Valued Functions
|
^^^^^^^^^^^^^^^^^^^^^^^
|
|
Similar to the table valued function, a column valued function is present
|
in the FROM clause, but delivers itself to the columns clause as a single
|
scalar value. PostgreSQL functions such as ``json_array_elements()``,
|
``unnest()`` and ``generate_series()`` may use this form. Column valued functions are available using the
|
:meth:`_functions.FunctionElement.column_valued` method of :class:`_functions.FunctionElement`:
|
|
* ``json_array_elements()``:
|
|
.. sourcecode:: pycon+sql
|
|
>>> from sqlalchemy import select, func
|
>>> stmt = select(func.json_array_elements('["one", "two"]').column_valued("x"))
|
>>> print(stmt)
|
{printsql}SELECT x
|
FROM json_array_elements(:json_array_elements_1) AS x
|
|
* ``unnest()`` - in order to generate a PostgreSQL ARRAY literal, the
|
:func:`_postgresql.array` construct may be used:
|
|
.. sourcecode:: pycon+sql
|
|
>>> from sqlalchemy.dialects.postgresql import array
|
>>> from sqlalchemy import select, func
|
>>> stmt = select(func.unnest(array([1, 2])).column_valued())
|
>>> print(stmt)
|
{printsql}SELECT anon_1
|
FROM unnest(ARRAY[%(param_1)s, %(param_2)s]) AS anon_1
|
|
The function can of course be used against an existing table-bound column
|
that's of type :class:`_types.ARRAY`:
|
|
.. sourcecode:: pycon+sql
|
|
>>> from sqlalchemy import table, column, ARRAY, Integer
|
>>> from sqlalchemy import select, func
|
>>> t = table("t", column('value', ARRAY(Integer)))
|
>>> stmt = select(func.unnest(t.c.value).column_valued("unnested_value"))
|
>>> print(stmt)
|
{printsql}SELECT unnested_value
|
FROM unnest(t.value) AS unnested_value
|
|
.. seealso::
|
|
:ref:`tutorial_functions_column_valued` - in the :ref:`unified_tutorial`
|
|
|
Row Types
|
^^^^^^^^^
|
|
Built-in support for rendering a ``ROW`` may be approximated using
|
``func.ROW`` with the :attr:`_sa.func` namespace, or by using the
|
:func:`_sql.tuple_` construct:
|
|
.. sourcecode:: pycon+sql
|
|
>>> from sqlalchemy import table, column, func, tuple_
|
>>> t = table("t", column("id"), column("fk"))
|
>>> stmt = t.select().where(
|
... tuple_(t.c.id, t.c.fk) > (1,2)
|
... ).where(
|
... func.ROW(t.c.id, t.c.fk) < func.ROW(3, 7)
|
... )
|
>>> print(stmt)
|
{printsql}SELECT t.id, t.fk
|
FROM t
|
WHERE (t.id, t.fk) > (:param_1, :param_2) AND ROW(t.id, t.fk) < ROW(:ROW_1, :ROW_2)
|
|
.. seealso::
|
|
`PostgreSQL Row Constructors
|
<https://www.postgresql.org/docs/current/sql-expressions.html#SQL-SYNTAX-ROW-CONSTRUCTORS>`_
|
|
`PostgreSQL Row Constructor Comparison
|
<https://www.postgresql.org/docs/current/functions-comparisons.html#ROW-WISE-COMPARISON>`_
|
|
Table Types passed to Functions
|
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
|
|
PostgreSQL supports passing a table as an argument to a function, which it
|
refers towards as a "record" type. SQLAlchemy :class:`_sql.FromClause` objects
|
such as :class:`_schema.Table` support this special form using the
|
:meth:`_sql.FromClause.table_valued` method, which is comparable to the
|
:meth:`_functions.FunctionElement.table_valued` method except that the collection
|
of columns is already established by that of the :class:`_sql.FromClause`
|
itself:
|
|
.. sourcecode:: pycon+sql
|
|
>>> from sqlalchemy import table, column, func, select
|
>>> a = table( "a", column("id"), column("x"), column("y"))
|
>>> stmt = select(func.row_to_json(a.table_valued()))
|
>>> print(stmt)
|
{printsql}SELECT row_to_json(a) AS row_to_json_1
|
FROM a
|
|
.. versionadded:: 1.4.0b2
|
|
|
|
é )Ú annotations)Ú defaultdict)Ú lru_cacheN)ÚAny)ÚList)ÚOptional)ÚTupleé )Úarray)Úhstore)Újson)Ú
|
pg_catalog)Úranges)Ú _regconfig_fn)Úaggregate_order_by)ÚCreateDomainType)ÚCreateEnumType)ÚDOMAIN)ÚDropDomainType)Ú DropEnumType)ÚENUM)Ú NamedType)Ú_DECIMAL_TYPES)Ú _FLOAT_TYPES)Ú
|
_INT_TYPES)ÚBIT)ÚBYTEA)ÚCIDR)ÚCITEXT)ÚINET)ÚINTERVAL)ÚMACADDR)ÚMACADDR8)ÚMONEY)ÚOID)ÚPGBit)ÚPGCidr)ÚPGInet)Ú
|
PGInterval)Ú PGMacAddr)Ú
|
PGMacAddr8)ÚPGUuid)ÚREGCLASS)Ú REGCONFIG)ÚTIME)Ú TIMESTAMP)ÚTSVECTORé )Úexc)Úschema)Úselect)Úsql)Úutil)Úcharacteristics)Údefault)Ú
|
interfaces)Ú
|
ObjectKind)Ú ObjectScope)Ú
|
reflection)ÚReflectionDefaults)Ú bindparam)Ú coercions)Úcompiler)Úelements)Ú
|
expression)Úroles)Úsqltypes)Ú InsertmanyvaluesSentinelOpts)ÚInternalTraversal)ÚBIGINT)ÚBOOLEAN)ÚCHAR)ÚDATE)ÚDOUBLE_PRECISION)ÚFLOAT)ÚINTEGER)ÚNUMERIC)ÚREAL)ÚSMALLINT)ÚTEXT)ÚUUID)ÚVARCHAR)Ú TypedDictz ^(?:btree|hash|gist|gin|[\w_]+)$ÚallZanalyseZanalyzeÚandÚanyr
|
ÚasZascZ
|
asymmetricZbothÚcaseÚcastÚcheckZcollateÚcolumnÚ
|
constraintÚcreateZcurrent_catalogZ current_dateZ current_roleÚ current_timeZcurrent_timestampZ current_userr8 Ú
|
deferrableÚdescZdistinctZdoÚelseÚendÚexceptÚfalseÚfetchÚforZforeignÚfromZgrantÚgroupZhavingÚinÚ initiallyZ intersectZintoÚleadingÚlimitÚ localtimeZlocaltimestampÚnewÚnotÚnullÚofÚoffÚoffsetÚoldÚonÚonlyÚorÚorderZplacingZprimaryZ
|
referencesZ returningr4 Z session_userZsomeZ symmetricÚtableZthenÚtoZtrailingÚtrueÚunionÚuniqueÚuserÚusingZvariadicÚwhenÚwhereZwindowÚwithÚ authorizationZbetweenÚbinaryZcrossZcurrent_schemaÚfreezeÚfullZilikeÚinnerÚisZisnullÚjoinÚleftZlikeZnaturalZnotnullÚouterZoverÚoverlapsÚrightZsimilarÚverbose)2Ú_arrayr r ZjsonbZ int4rangeZ int8rangeZnumrangeZ daterangeZtsrangeZ tstzrangeZint4multirangeZint8multirangeZ nummultirangeZdatemultirangeZ tsmultirangeZtstzmultirangeÚintegerZbigintZsmallintzcharacter varyingÚ characterz"char"ÚnameÚtextÚnumericÚfloatÚrealZinetZcidrZcitextÚuuidÚbitú bit varyingZmacaddrZmacaddr8ZmoneyÚoidZregclassúdouble precisionÚ timestampútimestamp with time zoneútimestamp without time zoneútime with time zoneútime without time zoneÚdateÚtimeZbyteaÚbooleanÚintervalZtsvectorc s@ e Zd Zdd Zdd Zdd Zdd Zd d
|
Zd d Zd d Z dd Z
|
dd Z dd Z dd Z dLddZdMddZd d Zd d Zd d! Zd"d# Zd$d% Zd&d' Zd(d) Zd*d+ Zd,d- Zd.d/ Zd0d1 Z fd2d3Zd4d5 Z d6d7 Z d8d9 Z d:d; Z d<d= Z d>d? Z!d@dA Z"dBdC Z#dDdE Z$dFdG Z%dHdI Z&dJdK Z' Z(S )NÚ
|
PGCompilerc K s | j |f|S ©N©Ú_assert_pg_ts_ext©ÚselfÚelementÚkw© r® úZd:\z\workplace\vscode\pyvenv\venv\Lib\site-packages\sqlalchemy/dialects/postgresql/base.pyÚvisit_to_tsvector_func| s z!PGCompiler.visit_to_tsvector_funcc K s | j |f|S r§ r¨ rª r® r® r¯ Úvisit_to_tsquery_func s z PGCompiler.visit_to_tsquery_funcc K s | j |f|S r§ r¨ rª r® r® r¯ Úvisit_plainto_tsquery_func s z%PGCompiler.visit_plainto_tsquery_funcc K s | j |f|S r§ r¨ rª r® r® r¯ Úvisit_phraseto_tsquery_func
s z&PGCompiler.visit_phraseto_tsquery_funcc K s | j |f|S r§ r¨ rª r® r® r¯ Ú visit_websearch_to_tsquery_func s z*PGCompiler.visit_websearch_to_tsquery_funcc K s | j |f|S r§ r¨ rª r® r® r¯ Úvisit_ts_headline_func s z!PGCompiler.visit_ts_headline_funcc K s> t |ts&t d|j d|j d¡|j | j|f| S )NzCan't compile "zÙ()" full text search function construct that does not originate from the "sqlalchemy.dialects.postgresql" package. Please ensure "import sqlalchemy.dialects.postgresql" is called before constructing "sqlalchemy.func.zD()" to ensure registration of the correct argument and return types.)Ú
|
isinstancer r2 Ú CompileErrorr Zfunction_argspecrª r® r® r¯ r© s
|
|
ÿ
|
z PGCompiler._assert_pg_ts_extc C s0 |j tjkrtj}| d| jjj|| jd S )Nz::)Úidentifier_preparer)Ú_type_affinityrD ÚStringÚ
|
STRINGTYPEÚdialectÚtype_compiler_instanceÚprocessÚpreparer)r« Útype_Z
|
dbapi_typeÚsqltextr® r® r¯ Úrender_bind_cast£ s ÿÿzPGCompiler.render_bind_castc K s d| j |f| S )Nz ARRAY[%s])Zvisit_clauselistrª r® r® r¯ Ú visit_array® s zPGCompiler.visit_arrayc K s$ d| j |jf|| j |jf|f S )Nz%s:%s)r¾ ÚstartÚstoprª r® r® r¯ Ú visit_slice± s þzPGCompiler.visit_slicec K s | j |df|S )Nz # )Ú_generate_generic_binary©r« r
Úoperatorr r® r® r¯ Úvisit_bitwise_xor_op_binary· s z&PGCompiler.visit_bitwise_xor_op_binaryFc K sR |s2|j jtjk r2d|d< | jt ||j ¡f|S d|d< | j||sHdndf|S )NTÚ _cast_appliedÚeager_groupingz -> z ->> ©Útyper¹ rD ÚJSONr¾ r5 rZ rÇ ©r« r
rÉ rË r r® r® r¯ Ú visit_json_getitem_op_binaryº s ÿ þ
|
ÿÿz'PGCompiler.visit_json_getitem_op_binaryc K sR |s2|j jtjk r2d|d< | jt ||j ¡f|S d|d< | j||sHdndf|S )NTrË rÌ z #> z #>> rÍ rÐ r® r® r¯ Ú!visit_json_path_getitem_op_binaryÊ s ÿ þ
|
ÿÿz,PGCompiler.visit_json_path_getitem_op_binaryc K s$ d| j |jf|| j |jf|f S )Nz%s[%s])r¾ r r rÈ r® r® r¯ Úvisit_getitem_binaryÙ s þz PGCompiler.visit_getitem_binaryc K s$ d| j |jf|| j |jf|f S )Nz%s ORDER BY %s)r¾ ÚtargetÚorder_byrª r® r® r¯ Úvisit_aggregate_order_byß s þz#PGCompiler.visit_aggregate_order_byc K sl d|j krH| |j d tj¡}|rHd| j|jf||| j|jf|f S d| j|jf|| j|jf|f S )NZpostgresql_regconfigz %s @@ plainto_tsquery(%s, %s)z%s @@ plainto_tsquery(%s))Ú modifiersÚrender_literal_valuerD r» r¾ r r )r« r
rÉ r Z regconfigr® r® r¯ Úvisit_match_op_binaryå s
|
ÿýþz PGCompiler.visit_match_op_binaryc K s |j j| f|S r§ )r¬ Ú_compiler_dispatchrª r® r® r¯ Ú$visit_ilike_case_insensitive_operandõ s z/PGCompiler.visit_ilike_case_insensitive_operandc K sL |j dd ¡}d| j|jf|| j|jf|f |rFd| |tj¡ nd S )NÚescapez %s ILIKE %sú ESCAPE Ú ©r× Úgetr¾ r r rØ rD r» ©r« r
rÉ r rÜ r® r® r¯ Úvisit_ilike_op_binaryø s þÿúz PGCompiler.visit_ilike_op_binaryc K sL |j dd ¡}d| j|jf|| j|jf|f |rFd| |tj¡ nd S )NrÜ z%s NOT ILIKE %srÝ rÞ rß rá r® r® r¯ Úvisit_not_ilike_op_binary s þÿúz$PGCompiler.visit_not_ilike_op_binaryc C s |j d }|d kr&| j|d| f|S t|tjrP|jdkrP| j|d| f|S d| j|jf||| j|f|| j|jf|f S )NÚflagsz %s Úiz %s* z %s %s CONCAT('(?', %s, ')', %s)) r× rÇ r¶ rA Ú BindParameterÚvaluer¾ r r )r« Zbase_opr
rÉ r rä r® r® r¯ Ú _regexp_match s*
|
ÿÿ ÿÿ üzPGCompiler._regexp_matchc K s | d|||¡S )Nú~©rè rÈ r® r® r¯ Ú visit_regexp_match_op_binary s z'PGCompiler.visit_regexp_match_op_binaryc K s | d|||¡S )Nz!~rê rÈ r® r® r¯ Ú visit_not_regexp_match_op_binary# s z+PGCompiler.visit_not_regexp_match_op_binaryc K sr | j |jf|}| j |jf|}|jd }| j |jd f|}|d krTd|||f S d|||| j |f|f S d S )Nrä Ú replacementzREGEXP_REPLACE(%s, %s, %s)z REGEXP_REPLACE(%s, %s, %s, %s))r¾ r r r× )r« r
rÉ r ÚstringÚpatternrä rí r® r® r¯ Ú visit_regexp_replace_op_binary& s
|
ý üz)PGCompiler.visit_regexp_replace_op_binaryc s& dd fdd|pt gD ¡f S )NzSELECT %s WHERE 1!=1ú, c 3 s, | ]$}d j j |jr t n|¡ V qdS )zCAST(NULL AS %s)N)r¼ r½ r¾ Ú_isnullrM )Ú.0rÀ ©r« r® r¯ Ú <genexpr>> s üÿÿz2PGCompiler.visit_empty_set_expr.<locals>.<genexpr>)r rM )r« Z element_typesr r® rô r¯ Úvisit_empty_set_expr9 s
|
|
ûÿz PGCompiler.visit_empty_set_exprc s& t ||¡}| jjr"| dd¡}|S )Nú\z\\)ÚsuperrØ r¼ Ú_backslash_escapesÚreplace)r« rç rÀ ©Ú __class__r® r¯ rØ G s z PGCompiler.render_literal_valuec K s d| j |¡ S )Nz nextval('%s'))r¿ Úformat_sequence)r« Úseqr r® r® r¯ Úvisit_sequenceN s zPGCompiler.visit_sequencec K s^ d}|j d k r&|d| j|j f| 7 }|jd k rZ|j d krB|d7 }|d| j|jf| 7 }|S )NrÞ z
|
LIMIT z
|
LIMIT ALLz OFFSET )Z _limit_clauser¾ Ú_offset_clause©r« r4 r r r® r® r¯ Ú limit_clauseQ s
|
|
|
zPGCompiler.limit_clausec C s" | ¡ dkrt d| ¡d| S )NÚONLYzUnrecognized hint: %rzONLY )Úupperr2 r· )r« rÁ rz ÚhintZiscrudr® r® r¯ Úformat_from_hint_text[ s z PGCompiler.format_from_hint_textc sD |j s |jr<|jr6dd fdd|jD ¡ d S dS ndS d S )Nz DISTINCT ON (rñ c s g | ]}j |f qS r® ©r¾ ©ró Úcol©r r« r® r¯ Ú
|
<listcomp>h s ÿz4PGCompiler.get_select_precolumns.<locals>.<listcomp>z) z DISTINCT rÞ )Z _distinctZ _distinct_onr )r« r4 r r® r
|
r¯ Úget_select_precolumns` s þÿÿùÿ z PGCompiler.get_select_precolumnsc s¢ |j jr |j jrd}q.d}n|j jr*d}nd}|j jr~t ¡ }|j jD ]}| t |¡¡ qF|dd fdd|D ¡ 7 }|j j
|
r|d 7 }|j j r|d
|
7 }|S ) Nz FOR KEY SHAREz
|
FOR SHAREz FOR NO KEY UPDATEz FOR UPDATEz OF rñ c 3 s& | ] }j |fd dd V qdS )TF)ZashintÚ
|
use_schemaNr )ró rz r
|
r® r¯ rõ s ÿz/PGCompiler.for_update_clause.<locals>.<genexpr>z NOWAITz SKIP LOCKED) Z_for_update_argÚreadZ key_sharerr r6 Z
|
OrderedSetÚupdateÚsql_utilZsurface_selectables_onlyr ZnowaitZ skip_locked)r« r4 r ÚtmpZtablesÚcr® r
|
r¯ Úfor_update_clauset s& þ z PGCompiler.for_update_clausec K sp | j |jjd f|}| j |jjd f|}t|jjdkr`| j |jjd f|}d|||f S d||f S d S )Nr r é z SUBSTRING(%s FROM %s FOR %s)zSUBSTRING(%s FROM %s))r¾ ZclausesÚlen)r« Úfuncr ÚsrÄ Úlengthr® r® r¯ Úvisit_substring_func s z PGCompiler.visit_substring_funcc st |j d k r d j |j ¡ }nR|jd k rldd fdd|jD ¡ }|jd k rp|d j|jddd 7 }nd }|S )
|
NzON CONSTRAINT %sú(%s)rñ c 3 s4 | ],}t |tr j |¡n j|d d dV qdS )F©Ú include_tabler N)r¶ Ústrr¿ Úquoter¾ ©ró r rô r® r¯ rõ § s ýÿz1PGCompiler._on_conflict_target.<locals>.<genexpr>ú WHERE %sFr rÞ )Zconstraint_targetr¿ Ú#truncate_and_render_constraint_nameZinferred_target_elementsr Zinferred_target_whereclauser¾ )r« Úclauser Ú target_textr® rô r¯ Ú_on_conflict_target s&
|
ÿÿÿ
|
ú
|
|
ý z PGCompiler._on_conflict_targetc K s" | j |f|}|rd| S dS d S )NzON CONFLICT %s DO NOTHINGzON CONFLICT DO NOTHING)r$ )r« Ú on_conflictr r# r® r® r¯ Ú visit_on_conflict_do_nothingº s z'PGCompiler.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 )NéÿÿÿÿZ
|
selectable)rÀ F)r ú%s = %szFAdditional column names not matching any column keys in table '%s': %srñ c s s | ]}d | V qdS )z'%s'Nr® r r® r® r¯ rõ î s z9PGCompiler.visit_on_conflict_do_update.<locals>.<genexpr>r Tr z ON CONFLICT %s DO UPDATE SET %s) r$ ÚdictZupdate_values_to_setÚstackrz r ÚkeyÚpopr? Z _is_literalrA ræ rÎ r¶ rò Z_cloner¾ Ú
|
self_groupr¿ r r Úappendr6 ÚwarnZcurrent_executabler Úitemsr ÚexpectrC ZExpressionElementRoleZupdate_whereclause)r« r% r 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&PGCompiler.visit_on_conflict_do_updatec s( dd< dd fdd|D ¡ S )NTÚasfromzFROM rñ c 3 s$ | ] }|j fd iV qdS ©Z fromhintsN©rÚ ©ró Út©Ú
|
from_hintsr r« r® r¯ rõ s ÿz0PGCompiler.update_from_clause.<locals>.<genexpr>©r )r« Z update_stmtÚ
|
from_tableÚ extra_fromsr< r r® r; r¯ Úupdate_from_clause s þz PGCompiler.update_from_clausec s( dd< dd fdd|D ¡ S )z9Render the DELETE .. USING clause specific to PostgreSQL.Tr6 zUSING rñ c 3 s$ | ] }|j fd iV qdS r7 r8 r9 r; r® r¯ rõ s ÿz6PGCompiler.delete_extra_from_clause.<locals>.<genexpr>r= )r« Z delete_stmtr> r? r< r r® r; r¯ Údelete_extra_from_clause s þz#PGCompiler.delete_extra_from_clausec K sn d}|j d k r&|d| j|j f| 7 }|jd k rj|d| j|jf||jd rPdnd|jd r`dndf 7 }|S ) NrÞ z
|
OFFSET (%s) ROWSz
|
FETCH FIRST (%s)%s ROWS %sÚpercentz PERCENTZ with_tiesz WITH TIESr )r r¾ Z _fetch_clauseZ_fetch_clause_optionsr r® r® r¯ Ú fetch_clause s
|
ÿÿ
|
ÿûzPGCompiler.fetch_clause)F)F))Ú__name__Ú
|
__module__Ú __qualname__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ÿ r r r r r r$ r& r5 r@ rA rC Ú __classcell__r® r® rû r¯ r¦ { sN ÿ
|
ÿ
|
|
!C
|
r¦ c s¤ e Zd Zdd Zdd Z fddZ fddZd d
|
Zd d Zd d Z dd Z
|
dd Z dd Z dd Z dd Zdd Z fdd Zd d Zd d Zd!d" Z ZS )#Ú PGDDLCompilerc K sh | j |¡}|j | j¡}t|tjr,|j}|j d k o<| jj
|
}|j r¾||j j kr¾| jjsdt|tjs¾|s¾|jd kst|jtjr¾|jjr¾t|tjr|d7 }qüt|tjr´|d7 }qü|d7 }n>|d| jjj|j|| j d 7 }| |¡}|d k rü|d| 7 }|jd k r |d| |j¡ 7 }|r6|d| |j ¡ 7 }|jsN|sN|d7 }n|jrd|rd|d7 }|S ) Nz
|
BIGSERIALz SMALLSERIALz SERIALú )Ztype_expressionr¸ z DEFAULT z NOT NULLz NULL)r¿ Z format_columnrÎ Z dialect_implr¼ r¶ rD Z TypeDecoratorÚimplÚidentityÚsupports_identity_columnsÚ primary_keyrz Ú_autoincrement_columnÚsupports_smallserialZ SmallIntegerr8 r3 ÚSequenceÚoptionalZ
|
BigIntegerr½ r¾ Zget_column_default_stringÚcomputedÚnullable)r« r\ ÚkwargsZcolspecZ impl_typeZ has_identityr8 r® r® r¯ Úget_column_specification- sZ
|
þÿ
|
þü
|
ûù ÷ õ ô
|
|
|
ý
|
|
|
z&PGDDLCompiler.get_column_specificationc C s |j d d }|rdS dS )NÚ
|
postgresqlÚ not_validz
|
NOT VALIDrÞ )Údialect_options)r« r] rW r® r® r¯ Ú_define_constraint_validityc s z)PGDDLCompiler._define_constraint_validityc s` |j rBt|jd j}t|tjrBt|jtjrB|jj sBt
|
d¡t |¡}|| |¡7 }|S )Nr zPostgreSQL dialect cannot produce the CHECK constraint for ARRAY of non-native ENUM; please specify create_constraint=False on this Enum datatype.)Z _type_boundÚlistÚcolumnsrÎ r¶ rD ÚARRAYÚ item_typeÚEnumÚ native_enumr2 r· rø Úvisit_check_constraintrY )r« r] r Útypr rû r® r¯ r` g s
|
ÿ þýÿ z$PGDDLCompiler.visit_check_constraintc s t |¡}|| |¡7 }|S r§ )rø Ú visit_foreign_key_constraintrY )r« r] r r rû r® r¯ rb y s z*PGDDLCompiler.visit_foreign_key_constraintc s0 |j }d j |¡d fdd|jD ¡f S )NzCREATE TYPE %s AS ENUM (%s)rñ c 3 s$ | ] } j jt |¡d dV qdS )T©Ú literal_bindsN)Ú sql_compilerr¾ r5 Úliteral)ró Úerô r® r¯ rõ s ÿz7PGDDLCompiler.visit_create_enum_type.<locals>.<genexpr>)r¬ r¿ Ú format_typer Úenums)r« r^ r rÀ r® rô r¯ Úvisit_create_enum_type~ s
|
þþz$PGDDLCompiler.visit_create_enum_typec K s |j }d| j |¡ S )Nz DROP TYPE %s©r¬ r¿ rh )r« Údropr rÀ r® r® r¯ Úvisit_drop_enum_type s z"PGDDLCompiler.visit_drop_enum_typec K sê |j }g }|jd k r.| d| j |j¡ ¡ |jd k rT| |j¡}| d| ¡ |jd k r|| j |j¡}| d| ¡ |j r| d¡ |j
|
d k r¼| j j |j
|
ddd}| d| d ¡ d
|
| j |¡ d | j |j¡ d d |¡ S ) NzCOLLATE zDEFAULT z CONSTRAINT zNOT NULLFT©r rd zCHECK (ú)zCREATE DOMAIN z AS rI )r¬ Z collationr. r¿ r r8 Zrender_default_stringÚconstraint_namer! Únot_nullr[ re r¾ rh Ú type_compilerÚ data_typer )r« r^ r ÚdomainÚoptionsr8 r r[ r® r® r¯ Úvisit_create_domain_type s.
|
|
|
ÿ
|
|
ÿ,ÿz&PGDDLCompiler.visit_create_domain_typec K s |j }d| j |¡ S )Nz DROP DOMAIN rk )r« rl r rt r® r® r¯ Úvisit_drop_domain_typeª s z$PGDDLCompiler.visit_drop_domain_typec sÈ j |j ¡ d} jr(|d7 }|d7 }jjrR jd d }|rR|d7 }|jr`|d7 }|dj d d
|
j
|
¡f 7 } jd d }|r®|d j |t ¡ ¡ 7 } jd d |dd fdd jD ¡ 7 } jd d }|r& fdd|D }|dd fdd|D ¡ 7 } jd d }|rZ|dd dd | ¡ D ¡ 7 } jd d } | r|d | ¡ 7 } jd d }
|
|
|
d k rÄt tj|
|
¡}
|
jj|
|
d d d } |d | 7 }|S ) NzCREATE zUNIQUE zINDEX rV Ú concurrentlyú CONCURRENTLY zIF NOT EXISTS z %s ON %s F©Zinclude_schemar z USING %s Úopsr rñ c sX g | ]P}j jt|tjs"| ¡ n|d ddt|drN|j krNd |j nd qS )FTrn r+ rI rÞ )re r¾ r¶ rB Z ColumnClauser- Úhasattrr+ )ró Úexpr)r{ r« r® r¯ r Î s ô
|
ÿ
|
û ÿÿöz4PGDDLCompiler.visit_create_index.<locals>.<listcomp>Úincludec s& g | ] }t |tr jj| n|qS r® )r¶ r rz r r )Úindexr® r¯ r â s ÿz INCLUDE (%s)c s g | ]} |j¡qS r® )r r r ©r¿ r® r¯ r ç s r z
|
WITH (%s)c S s g | ] }d | qS )r( r® )ró Zstorage_parameterr® r® r¯ r î s ÿÚ
|
tablespacez TABLESPACE %sr Trn z WHERE )r¿ r¬ Z_verify_index_tabler~ r¼ Ú#_supports_create_index_concurrentlyrX Z if_not_existsÚ_prepared_index_nameÚ format_tablerz Úvalidate_sql_phraseÚ IDX_USINGÚlowerr Ú expressionsr0 r r? r1 rC ZDDLExpressionRolere r¾ ) r« r^ r r rx r Z includeclauseZ
|
inclusionsZ
|
withclauseÚtablespace_nameZ whereclauseZwhere_compiledr® )r r{ r¿ r« r¯ Úvisit_create_index® s
|
|
þÿÿ óÿÿ
|
þÿþÿÿ
|
ÿ ÿ z PGDDLCompiler.visit_create_indexc K sP |j }d}| jjr,|jd d }|r,|d7 }|jr:|d7 }|| j|dd7 }|S )Nz
|
DROP INDEX rV rx ry z
|
IF EXISTS Trz )r¬ r¼ Ú!_supports_drop_index_concurrentlyrX Z if_existsr )r« rl r r r rx r® r® r¯ Úvisit_drop_index s z PGDDLCompiler.visit_drop_indexc K sð d}|j d k r"|d| j |¡ 7 }g }d|d< d|d< |jD ]V\}}}| jj|f|t|drz|j|jkrzd|j|j nd }| d ||f ¡ q<|d
|
| j
|
|j t ¡ ¡ d |¡f 7 }|jd k rÞ|d | jj|jdd 7 }|| |¡7 }|S )NrÞ zCONSTRAINT %s Fr Trd r+ rI z
|
%s WITH %szEXCLUDE USING %s (%s)rñ z WHERE (%s)rc )r r¿ Úformat_constraintZ _render_exprsre r¾ r| r+ r{ r. r
r r r r r Z define_constraint_deferrability) r« r] r r rA r} r ÚopZexclude_elementr® r® r¯ Úvisit_exclude_constraint s>
|
|
ÿÿ
|
ÿý ÿü
|
|
ÿ
|
z&PGDDLCompiler.visit_exclude_constraintc sü g }|j d }| d¡}|d k rZt|ttfs4|f}| dd fdd|D ¡ d ¡ |d rt| d |d ¡ |d
|
d kr| d ¡ n|d
|
d kr¢| d¡ |d rÌ|d dd¡ ¡ }| d| ¡ |d rò|d }| d j
|
|¡ ¡ d |¡S )NrV Úinheritsz
|
INHERITS ( rñ c 3 s | ]} j |¡V qd S r§ )r¿ r )ró r rô r® r¯ rõ > s z2PGDDLCompiler.post_create_table.<locals>.<genexpr>z )Ú partition_byz
|
PARTITION BY %sÚ with_oidsTz
|
WITH OIDSFz
|
WITHOUT OIDSÚ on_commitÚ_rI z
|
ON COMMIT %sr z
|
TABLESPACE %srÞ ) rX rà r¶ rZ Útupler. r rú r r¿ r )r« rz Z
|
table_optsZpg_optsr Zon_commit_optionsr r® rô r¯ Úpost_create_table4 s8
|
|
ÿþÿ
|
ÿz PGDDLCompiler.post_create_tablec K s, |j dkrt d¡d| jj|jddd S )NFzPostrgreSQL computed columns do not support 'virtual' persistence; set the 'persisted' flag to None or True for PostgreSQL support.z GENERATED ALWAYS AS (%s) STOREDTrn )Ú persistedr2 r· re r¾ rÁ )r« Ú generatedr r® r® r¯ Úvisit_computed_columnV s
|
ÿ ÿz#PGDDLCompiler.visit_computed_columnc s< d }|j jd k r$d| j |j j¡ }t j|fd|i|S )Nz AS %sÚprefix)r¬ rs rr r¾ rø Úvisit_create_sequence)r« r^ r r rû r® r¯ r b s ÿz#PGDDLCompiler.visit_create_sequencec C sB |j }|jd kr"t d|d¡|jd kr>t d|d¡d S )Nz%Can't emit COMMENT ON for constraint z: it has no namez : it has no associated table)r¬ r r2 r· rz )r« Z ddl_instancer] r® r® r¯ Ú_can_comment_on_constraintk s
|
|
ÿ
|
|
ÿz(PGDDLCompiler._can_comment_on_constraintc K s@ | |¡ d| j |j¡| j |jj¡| j |jjt
|
¡ ¡f S )Nz$COMMENT ON CONSTRAINT %s ON %s IS %s) r r¿ r r¬ r rz re rØ ÚcommentrD rº )r« r^ r r® r® r¯ Ú visit_set_constraint_commentx s
|
ÿýz*PGDDLCompiler.visit_set_constraint_commentc K s, | |¡ d| j |j¡| j |jj¡f S )Nz&COMMENT ON CONSTRAINT %s ON %s IS NULL)r r¿ r r¬ r rz )r« rl r r® r® r¯ Ú visit_drop_constraint_comment s
|
|
þz+PGDDLCompiler.visit_drop_constraint_comment)rD rE rF rU rY r` rb rj rm rv rw r r r r r r r r r rG r® r® rû r¯ rH , s" 6 X "
|
rH c s e Zd Zdd Zdd Zdd Zdd Zd d
|
Zd d Zd d Z dd Z
|
dd Z dd Z dd Z dd Zdd Zdd Zd d Zd d Zd!d" Zd#d$ Zd%d& Zd'd( Zd)d* Zd+d, Zd-d. Zd/d0 Zd1d2 Zd3d4 Z d5d6 Z d7d8 Z d9d: Z d;d< Z d=d> Z!d?d@ Z" fdAdBZ#d^dDdEZ$d_dFdGZ%dHdI Z&dJdK Z'dLdM Z(dNdO Z) fdPdQZ*dRdS Z+dTdU Z,dVdW Z-dXdY Z.dZd[ Z/d\d] Z0 Z1S )`ÚPGTypeCompilerc K s dS )Nr0 r® ©r« rÀ r r® r® r¯ Úvisit_TSVECTOR s z PGTypeCompiler.visit_TSVECTORc K s dS )NZTSQUERYr® r¡ r® r® r¯ Ú visit_TSQUERY s z PGTypeCompiler.visit_TSQUERYc K s dS )Nr r® r¡ r® r® r¯ Ú
|
visit_INET s zPGTypeCompiler.visit_INETc K s dS )Nr r® r¡ r® r® r¯ Ú
|
visit_CIDR s zPGTypeCompiler.visit_CIDRc K s dS )Nr r® r¡ r® r® r¯ Ú visit_CITEXT s zPGTypeCompiler.visit_CITEXTc K s dS )Nr! r® r¡ r® r® r¯ Ú visit_MACADDR s z PGTypeCompiler.visit_MACADDRc K s dS )Nr" r® r¡ r® r® r¯ Úvisit_MACADDR8 s z PGTypeCompiler.visit_MACADDR8c K s dS )Nr# r® r¡ r® r® r¯ Ú visit_MONEY s zPGTypeCompiler.visit_MONEYc K s dS )Nr$ r® r¡ r® r® r¯ Ú visit_OID£ s zPGTypeCompiler.visit_OIDc K s dS )Nr- r® r¡ r® r® r¯ Úvisit_REGCONFIG¦ s z PGTypeCompiler.visit_REGCONFIGc K s dS )Nr, r® r¡ r® r® r¯ Úvisit_REGCLASS© s z PGTypeCompiler.visit_REGCLASSc K s |j s
|
dS dd|j i S d S )NrL zFLOAT(%(precision)s)Ú precision)r r¡ r® r® r¯ Ú visit_FLOAT¬ s zPGTypeCompiler.visit_FLOATc K s | j tf|S r§ )Zvisit_DOUBLE_PRECISIONrÎ r¡ r® r® r¯ Ú visit_double² s zPGTypeCompiler.visit_doublec K s dS )NrG r® r¡ r® r® r¯ Ú visit_BIGINTµ s zPGTypeCompiler.visit_BIGINTc K s dS )NÚHSTOREr® r¡ r® r® r¯ Ú visit_HSTORE¸ s zPGTypeCompiler.visit_HSTOREc K s dS )NrÏ r® r¡ r® r® r¯ Ú
|
visit_JSON» s zPGTypeCompiler.visit_JSONc K s dS )NÚJSONBr® r¡ r® r® r¯ Ú visit_JSONB¾ s zPGTypeCompiler.visit_JSONBc K s dS )NÚINT4MULTIRANGEr® r¡ r® r® r¯ Úvisit_INT4MULTIRANGEÁ s z#PGTypeCompiler.visit_INT4MULTIRANGEc K s dS )NÚINT8MULTIRANGEr® r¡ r® r® r¯ Úvisit_INT8MULTIRANGEÄ s z#PGTypeCompiler.visit_INT8MULTIRANGEc K s dS )NÚ NUMMULTIRANGEr® r¡ r® r® r¯ Úvisit_NUMMULTIRANGEÇ s z"PGTypeCompiler.visit_NUMMULTIRANGEc K s dS )NÚDATEMULTIRANGEr® r¡ r® r® r¯ Úvisit_DATEMULTIRANGEÊ s z#PGTypeCompiler.visit_DATEMULTIRANGEc K s dS )NÚ TSMULTIRANGEr® r¡ r® r® r¯ Úvisit_TSMULTIRANGEÍ s z!PGTypeCompiler.visit_TSMULTIRANGEc K s dS )NÚTSTZMULTIRANGEr® r¡ r® r® r¯ Úvisit_TSTZMULTIRANGEÐ s z#PGTypeCompiler.visit_TSTZMULTIRANGEc K s dS )NÚ INT4RANGEr® r¡ r® r® r¯ Úvisit_INT4RANGEÓ s z PGTypeCompiler.visit_INT4RANGEc K s dS )NÚ INT8RANGEr® r¡ r® r® r¯ Úvisit_INT8RANGEÖ s z PGTypeCompiler.visit_INT8RANGEc K s dS )NÚNUMRANGEr® r¡ r® r® r¯ Úvisit_NUMRANGEÙ s z PGTypeCompiler.visit_NUMRANGEc K s dS )NÚ DATERANGEr® r¡ r® r® r¯ Úvisit_DATERANGEÜ s z PGTypeCompiler.visit_DATERANGEc K s dS )NÚTSRANGEr® r¡ r® r® r¯ Ú visit_TSRANGEß s z PGTypeCompiler.visit_TSRANGEc K s dS )NÚ TSTZRANGEr® r¡ r® r® r¯ Úvisit_TSTZRANGEâ s z PGTypeCompiler.visit_TSTZRANGEc K s dS )NÚINTr® r¡ r® r® r¯ Úvisit_json_int_indexå s z#PGTypeCompiler.visit_json_int_indexc K s dS )NrQ r® r¡ r® r® r¯ Úvisit_json_str_indexè s z#PGTypeCompiler.visit_json_str_indexc K s | j |f|S r§ )Úvisit_TIMESTAMPr¡ r® r® r¯ Úvisit_datetimeë s z PGTypeCompiler.visit_datetimec s0 |j r| jjs t j|f|S | j|f|S d S r§ )r_ r¼ Úsupports_native_enumrø Ú
|
visit_enumÚ
|
visit_ENUMr¡ rû r® r¯ rÔ î s zPGTypeCompiler.visit_enumNc K s |d kr| j j}| |¡S r§ ©r¼ r¸ rh ©r« rÀ r¸ r r® r® r¯ rÕ ô s zPGTypeCompiler.visit_ENUMc K s |d kr| j j}| |¡S r§ rÖ r× r® r® r¯ Ú visit_DOMAINù s zPGTypeCompiler.visit_DOMAINc K s4 dt |dd d k r d|j nd|jr(dp*dd f S )NzTIMESTAMP%s %sr ú(%d)rÞ ÚWITHÚWITHOUTú
|
TIME ZONE©Úgetattrr Útimezoner¡ r® r® r¯ rÑ þ s ÿ üz PGTypeCompiler.visit_TIMESTAMPc K s4 dt |dd d k r d|j nd|jr(dp*dd f S )Nz TIME%s %sr rÙ rÞ rÚ rÛ rÜ rÝ r¡ r® r® r¯ Ú
|
visit_TIME
|
s ÿ üzPGTypeCompiler.visit_TIMEc K s8 d}|j d k r |d|j 7 }|jd k r4|d|j 7 }|S )Nr rI z (%d))Úfieldsr )r« rÀ r r r® r® r¯ Úvisit_INTERVAL
|
s
|
|
z PGTypeCompiler.visit_INTERVALc K s2 |j r$d}|jd k r.|d|j 7 }n
|
d|j }|S )Nz BIT VARYINGrÙ zBIT(%d))Úvaryingr )r« rÀ r Zcompiledr® r® r¯ Ú visit_BIT
|
s
|
|
zPGTypeCompiler.visit_BITc s( |j r| j|f|S t j|f|S d S r§ )Z native_uuidÚ
|
visit_UUIDrø Ú
|
visit_uuidr¡ rû r® r¯ ræ
|
s zPGTypeCompiler.visit_uuidc K s dS )NrR r® r¡ r® r® r¯ rå %
|
s zPGTypeCompiler.visit_UUIDc K s | j |f|S r§ )Ú visit_BYTEAr¡ r® r® r¯ Úvisit_large_binary(
|
s z!PGTypeCompiler.visit_large_binaryc K s dS )Nr r® r¡ r® r® r¯ rç +
|
s zPGTypeCompiler.visit_BYTEAc K s: | j |jf|}tjddd|jd k r*|jnd |ddS )Nz((?: COLLATE.*)?)$z%s\1ú[]r )Úcount)r¾ r] ÚreÚsubZ
|
dimensions)r« rÀ r r r® r® r¯ Ú visit_ARRAY.
|
s ÿþözPGTypeCompiler.visit_ARRAYc K s | j |f|S r§ )Úvisit_JSONPATHr¡ r® r® r¯ Úvisit_json_path=
|
s z PGTypeCompiler.visit_json_pathc K s dS )NÚJSONPATHr® r¡ r® r® r¯ rî @
|
s z PGTypeCompiler.visit_JSONPATH)N)N)2rD rE rF 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Ç rÉ rË rÍ rÏ rÐ rÒ rÔ rÕ rØ rÑ rà râ rä ræ rå rè rç rí rï rî rG r® r® rû r¯ r s\
|
|
r c @ s" e Zd ZeZdd ZdddZdS )ÚPGIdentifierPreparerc C s* |d | j kr&|dd
| j| j¡}|S )Nr r r' )Z initial_quoterú Zescape_to_quoteZ escape_quote)r« rç r® r® r¯ Ú_unquote_identifierG
|
s ÿz(PGIdentifierPreparer._unquote_identifierTc C s\ |j s t d|jj d¡| |j ¡}| |¡}| jsX|rX|d k rX| |¡ d| }|S )Nz PostgreSQL z type requires a name.Ú.) r r2 r· rü rD r Úschema_for_objectZ omit_schemaZ quote_schema)r« rÀ r r Úeffective_schemar® r® r¯ rh N
|
s ÿ
|
ÿþýz PGIdentifierPreparer.format_typeN)T)rD rE rF ÚRESERVED_WORDSZreserved_wordsrò rh r® r® r® r¯ rñ D
|
s rñ c @ s* e Zd ZU dZded< ded< ded< dS )ÚReflectedNamedTypez"Represents a reflected named type.r r r3 ÚboolÚvisibleN©rD rE rF Ú__doc__Ú__annotations__r® r® r® r¯ r÷ `
|
s
|
|
r÷ c @ s" e Zd ZU dZded< ded< dS )ÚReflectedDomainConstraintz2Represents a reflect check constraint of a domain.r r r[ Nrú r® r® r® r¯ rý k
|
s
|
rý c @ s2 e Zd ZU dZded< ded< ded< ded < d
|
S ) ÚReflectedDomainú Represents a reflected enum.r rÎ rø rS ú Optional[str]r8 z List[ReflectedDomainConstraint]Ú constraintsNrú r® r® r® r¯ rþ t
|
s
|
rþ c @ s e Zd ZU dZded< dS )Ú ReflectedEnumrÿ ú List[str]ÚlabelsNrú r® r® r® r¯ r
|
s
|
r c @ sv e Zd ZU ded< ddddddd Zddd
|
d d d Zdddd ddZd ddd ddZd dddddddZdS ) Ú PGInspectorÚ PGDialectr¼ Nr r Úint)Ú
|
table_namer3 Úreturnc
|
C s6 | ¡ $}| jj|||| jdW 5 Q R £ S Q R X dS )aQ Return the OID for the given table name.
|
|
:param table_name: string name of the table. For special quoting,
|
use :class:`.quoted_name`.
|
|
:param schema: string schema name; if omitted, uses the default schema
|
of the database connection. For special quoting,
|
use :class:`.quoted_name`.
|
|
©Ú
|
info_cacheN)Ú_operation_contextr¼ Ú get_table_oidr )r« r r3 Úconnr® r® r¯ r
|
s
|
ÿzPGInspector.get_table_oidzList[ReflectedDomain])r3 r c
|
C s4 | ¡ "}| jj||| jdW 5 Q R £ S Q R X dS )aÐ Return a list of DOMAIN objects.
|
|
Each member is a dictionary containing these fields:
|
|
* name - name of the domain
|
* schema - the schema name for the domain.
|
* visible - boolean, whether or not this domain is visible
|
in the default search path.
|
* type - the type defined by this domain.
|
* nullable - Indicates if this domain can be ``NULL``.
|
* default - The default value of the domain or ``None`` if the
|
domain has no default.
|
* constraints - A list of dict wit the constraint defined by this
|
domain. Each element constaints two keys: ``name`` of the
|
constraint and ``check`` with the constraint text.
|
|
:param schema: schema name. If None, the default schema
|
(typically 'public') is used. May also be set to ``'*'`` to
|
indicate load domains for all schemas.
|
|
.. versionadded:: 2.0
|
|
r
|
N)r r¼ Ú _load_domainsr ©r« r3 r r® r® r¯ Ú get_domains¢
|
s
|
ÿzPGInspector.get_domainszList[ReflectedEnum]c
|
C s4 | ¡ "}| jj||| jdW 5 Q R £ S Q R X dS )a. Return a list of ENUM objects.
|
|
Each member is a dictionary containing these fields:
|
|
* name - name of the enum
|
* schema - the schema name for the enum.
|
* visible - boolean, whether or not this enum is visible
|
in the default search path.
|
* labels - a list of string labels that apply to the enum.
|
|
:param schema: schema name. If None, the default schema
|
(typically 'public') is used. May also be set to ``'*'`` to
|
indicate load enums for all schemas.
|
|
r
|
N)r r¼ Ú _load_enumsr r r® r® r¯ Ú get_enumsÁ
|
s
|
ÿzPGInspector.get_enumsr c
|
C s4 | ¡ "}| jj||| jdW 5 Q R £ S Q R X dS )zøReturn a list of FOREIGN TABLE names.
|
|
Behavior is similar to that of
|
:meth:`_reflection.Inspector.get_table_names`,
|
except that the list is limited to those tables that report a
|
``relkind`` value of ``f``.
|
|
r
|
N)r r¼ Ú_get_foreign_table_namesr r r® r® r¯ Úget_foreign_table_namesÖ
|
s
|
ÿz#PGInspector.get_foreign_table_namesr rø )Ú type_namer3 r r c
|
K s6 | ¡ $}| jj|||| jdW 5 Q R £ S Q R X dS )aJ Return if the database has the specified type in the provided
|
schema.
|
|
:param type_name: the type to check.
|
:param schema: schema name. If None, the default schema
|
(typically 'public') is used. May also be set to ``'*'`` to
|
check in all schemas.
|
|
.. versionadded:: 2.0
|
|
r
|
N)r r¼ Úhas_typer )r« r r3 r r r® r® r¯ r æ
|
s
|
ÿzPGInspector.has_type)N)N)N)N)N) rD rE rF rü r r r r r r® r® r® r¯ r
|
s
|
ÿ ÿ ÿ ÿr c s$ e Zd Zdd Z fddZ ZS )ÚPGExecutionContextc C s | d| j |¡ |¡S )Nzselect nextval('%s'))Ú_execute_scalarr¸ rý )r« rþ rÀ r® r® r¯ Ú fire_sequenceû
|
s
|
ÿûz PGExecutionContext.fire_sequencec s2 |j r&||jjkr&|jr:|jjr:| d|jj |j¡S |jd ksX|jj r&|jj
|
r&z
|
|j }W nt t k
|
rÖ |jj }|j }|ddtddt|
}|ddtddt|
}d||f }| |_ }Y nX |jd k rò| j |j¡}nd }|d k rd||f }n
|
d|f }| ||j¡S t |¡S )Nz select %sr é z %s_%s_seqzselect nextval('"%s"."%s"')zselect nextval('"%s"'))rM rz rN Zserver_defaultZ has_argumentr ÚargrÎ r8 Z is_sequencerQ Z_postgresql_seq_nameÚAttributeErrorr Úmaxr Ú
|
connectionrô rø Úget_insert_default)r« r\ Zseq_nameÚtabr r rõ r2 rû r® r¯ r sB
|
ÿ
|
ÿÿ
|
|
ÿ
|
þ
|
z%PGExecutionContext.get_insert_default)rD rE rF r r rG r® r® rû r¯ r ú
|
s r c @ s( e Zd ZdZdd Zdd Zdd ZdS ) Ú"PGReadOnlyConnectionCharacteristicTc C s | |d¡ d S ©NF©Ú set_readonly©r« r¼ Ú
|
dbapi_connr® r® r¯ Úreset_characteristic6 s z7PGReadOnlyConnectionCharacteristic.reset_characteristicc C s | ||¡ d S r§ r$ ©r« r¼ r' rç r® r® r¯ Úset_characteristic9 s z5PGReadOnlyConnectionCharacteristic.set_characteristicc C s
|
| |¡S r§ )Ú get_readonlyr& r® r® r¯ Úget_characteristic< s z5PGReadOnlyConnectionCharacteristic.get_characteristicN©rD rE rF Z transactionalr( r* r, r® r® r® r¯ r" 1 s r" c @ s( e Zd ZdZdd Zdd Zdd ZdS ) Ú$PGDeferrableConnectionCharacteristicTc C s | |d¡ d S r# ©Úset_deferrabler& r® r® r¯ r( E s z9PGDeferrableConnectionCharacteristic.reset_characteristicc C s | ||¡ d S r§ r/ r) r® r® r¯ r* H s z7PGDeferrableConnectionCharacteristic.set_characteristicc C s
|
| |¡S r§ )Úget_deferrabler& r® r® r¯ r, K s z7PGDeferrableConnectionCharacteristic.get_characteristicNr- r® r® r® r¯ r. @ s r. c s e Zd ZdZdZdZdZdZej j
|
Z dZ dZ dZdZdZdZdZdZdZdZejejB ejB ZdZdZ dZ dZ dZ dZ dZ!dZ"e#Z#e$Z$e%Z&e'Z(e)Z*e+Z,e-Z.e/Z0dZ1dZ2dZ3dZ4dZ5e6j7j8Z8e8 9e: e; d¡Z8e<j=dddi di ddfe<j>ddddddd fe<j?d
|
dife<j@d
|
difgZAd ZBdZCdZDdZEdd d ZF fddZGdd ZHdd ZIdd ZJdd ZKdd ZLdd ZMd d ZNd d ZOd d! ZPdd"d#ZQdd$d%ZRd&d' ZSd(d) ZTeUjVd*d+ ZWdd,d-ZXdd.d/ZYeZ d0d1 Z[eUjVd d2d3Z\eUjVd¡d4d5Z]eUjVd¢d6d7Z^d8d9 Z_eUjVd£d:d;Z`eUjVd<d= Zad>d? ZbeUjVd¤d@dAZceUjVdBdC ZdeUjVd¥dDdEZeeUjVd¦dFdGZfeUjVd§dHdIZgeUjVd¨dJdKZheUjVd©dLdMZieUjVdªdNdOZjdPdQ ZkdRdS ZldTdUdVdWdXZmeUjVd«dYdZZneZ d[d\ Zod]d^ Zpd_d` ZqeZ dadb ZreU sdcetjufddetjvfdeetjwfdfetjwf¡dgdh Zxeyjzdidj Z{dkdl Z|eUjVd¬dmdnZ}dodp Z~eUjVddqdrZeZ dsdt Zeyjzdudv Zd®dwdxZeUjVd¯dydzZeyjzd{d| Zd}d~ Z
eUjVd°ddZdd ZeUjVd±ddZeZ d
d Zdd ZeUjVd²ddZeZ dd Zdd Zdd ZeZ dd ZeUjVd³ddZeZ dd ZeUjVd´ddZdd Z ZS )µr rV Té? FZpyformat)Zpostgresql_readonlyZpostgresql_deferrableN)r r~ r r{ rx r r )Zignore_search_pathr r r r r rW )Ú postgresql_ignore_search_pathc K s t jj| f| || _|| _d S r§ )r8 ÚDefaultDialectÚ__init__Z_json_deserializerZ_json_serializer)r« Zjson_serializerZjson_deserializerrT r® r® r¯ r5 ½ s zPGDialect.__init__c s> t |¡ | jdk| _| |¡ | jdk| _| jdk| _d S )N)é r ©é
|
)rø Ú
|
initializeÚserver_version_inforO Ú_set_backslash_escapesr rL ©r« r rû r® r¯ r9 Ã s
|
|
zPGDialect.initializec C s dS )N)Z SERIALIZABLEzREAD UNCOMMITTEDzREAD COMMITTEDzREPEATABLE READr® )r« r' r® r® r¯ Úget_isolation_level_valuesÑ s z$PGDialect.get_isolation_level_valuesc C s. | ¡ }| d| ¡ | d¡ | ¡ d S )Nz;SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL ZCOMMIT)ÚcursorÚexecuteÚclose)r« Údbapi_connectionÚlevelr> r® r® r¯ Úset_isolation_levelÛ s ÿ
|
z PGDialect.set_isolation_levelc C s. | ¡ }| d¡ | ¡ d }| ¡ | ¡ S )Nz show transaction isolation levelr )r> r? Zfetchoner@ r )r« rA r> Úvalr® r® r¯ Úget_isolation_levelä s
|
|
z PGDialect.get_isolation_levelc C s
|
t d S r§ ©ÚNotImplementedError©r« r rç r® r® r¯ r% ë s zPGDialect.set_readonlyc C s
|
t d S r§ rF r< r® r® r¯ r+ î s zPGDialect.get_readonlyc C s
|
t d S r§ rF rH r® r® r¯ r0 ñ s zPGDialect.set_deferrablec C s
|
t d S r§ rF r< r® r® r¯ r1 ô s zPGDialect.get_deferrablec C s | |j¡ d S r§ )Zdo_beginr ©r« r Úxidr® r® r¯ Údo_begin_twophase÷ s zPGDialect.do_begin_twophasec C s | d| ¡ d S )NzPREPARE TRANSACTION '%s')Úexec_driver_sqlrI r® r® r¯ Údo_prepare_twophaseú s z PGDialect.do_prepare_twophasec C sH |r8|r| d¡ | d| ¡ | d¡ | |j¡ n | |j¡ d S )NÚROLLBACKzROLLBACK PREPARED '%s'ÚBEGIN)rL Ú do_rollbackr ©r« r rJ Z is_preparedZrecoverr® r® r¯ Údo_rollback_twophaseý s
|
|
z PGDialect.do_rollback_twophasec C sH |r8|r| d¡ | d| ¡ | d¡ | |j¡ n | |j¡ d S )NrN zCOMMIT PREPARED '%s'rO )rL rP r Z do_commitrQ r® r® r¯ Údo_commit_twophase s
|
|
z PGDialect.do_commit_twophasec C s | t d¡¡ ¡ S )Nz!SELECT gid FROM pg_prepared_xacts)Úscalarsr5 r rU r< r® r® r¯ Údo_recover_twophase s ÿz PGDialect.do_recover_twophasec C s | d¡ ¡ S )Nzselect current_schema())rL Úscalarr< r® r® r¯ Ú_get_default_schema_name s z"PGDialect._get_default_schema_namec K s, t tjjj tjjj|k¡}t| |¡S r§ )r4 r Ú pg_namespacer Únspnamer rø rV )r« r r3 r Úqueryr® r® r¯ Ú
|
has_schema! s ÿzPGDialect.has_schemac C s¦ |d krt j}| t jt jjj|jjk¡}|tjkrH| |jj
|
dk¡}n |tj krd| |jj
|
dk¡}|d kr| t |jj¡t jjj dk¡}n| t jjj |k¡}|S )Nr: r )r Úpg_classr rX r r Ú relnamespacer; ÚDEFAULTr ZrelpersistenceÚ TEMPORARYZpg_table_is_visiblerY )r« rZ r3 ÚscopeÚpg_class_tabler® r® r¯ Ú _pg_class_filter_scope_schema( s" þ
|
|
ýz'PGDialect._pg_class_filter_scope_schemac C s& |d krt j}|jjt t |¡¡kS r§ )r r\ r Úrelkindr5 Úany_r r
|
)r« Úrelkindsra r® r® r¯ Ú_pg_class_relkind_conditionA s z%PGDialect._pg_class_relkind_conditionc C s> t tjjj tjjjtdk| tj¡¡}| j ||t
|
j dS )Nr ©r` ) r4 r r\ r Úrelnamer r> rf ÚRELKINDS_ALL_TABLE_LIKErb r; ÚANY)r« r3 rZ r® r® r¯ Ú_has_table_queryH s ÿþ ÿzPGDialect._has_table_queryc K s( | |¡ | |¡}t| |d|i¡S )Nr )Z _ensure_has_table_connectionrk rø rV )r« r r r3 r rZ r® r® r¯ Ú has_tableT s
|
|
zPGDialect.has_tablec K sJ t tjjj tjjjdktjjj|k¡}| j||tj d}t
|
| |¡S )NÚSrg ) r4 r r\ r rh r rc rb r; rj rø rV )r« r Z sequence_namer3 r rZ r® r® r¯ Ú has_sequenceZ s þ ÿzPGDialect.has_sequencec K s t tjjj tjtjjjtjjjk¡ tjjj|k¡}|d krd| t
|
tjjj¡tjjj dk¡}n |dkr| tjjj |k¡}t | |¡S ©Nr Ú*)r4 r Úpg_typer Útypnamer rX r Ú typnamespacer Úpg_type_is_visiblerY rø rV )r« r r r3 r rZ r® r® r¯ r e s" ÿý úÿ ýzPGDialect.has_typec C sF | d¡ ¡ }t d|¡}|s*td| tdd | ddd¡D S ) Nzselect pg_catalog.version()zQ.*(?:PostgreSQL|EnterpriseDB) (\d+)\.?(\d+)?(?:\.(\d+))?(?:\.\d+)?(?:devel|beta)?z,Could not determine version from string '%s'c S s g | ]}|d k rt |qS r§ )r ©ró Úxr® r® r¯ r s z6PGDialect._get_server_version_info.<locals>.<listcomp>r r r1 )rL rV rë ÚmatchÚAssertionErrorr ri )r« r r4 Úmr® r® r¯ Ú_get_server_version_info{ s ýÿz"PGDialect._get_server_version_infoc K sl t tjjj tjjj|k| tj¡¡}| j ||t
|
j d}| |¡}|dkrht |rb| d| n|¡|S )z$Fetch the oid for schema.table_name.rg Nró )r4 r r\ r r r rh rf ri rb r; rj rV r2 ÚNoSuchTableError)r« r r r3 r rZ Z table_oidr® r® r¯ r s" ÿþ ÿ
|
ÿzPGDialect.get_table_oidc K s: t tjjj tjjj d¡¡ tjjj¡}| |¡ ¡ S )Nzpg_%)
|
r4 r rX r rY r Znot_likerÕ rT rU )r« r r rZ r® r® r¯ Úget_schema_names s ÿþÿzPGDialect.get_schema_namesc C s8 t tjjj | |¡¡}| j|||d}| |¡ ¡ S ©Nrg )
|
r4 r r\ r rh r rf rb rT rU )r« r r3 re r` rZ r® r® r¯ Ú_get_relnames_for_relkinds¤ s
|
ÿz$PGDialect._get_relnames_for_relkindsc K s | j ||tjtjdS r} )r~ r ÚRELKINDS_TABLE_NO_FOREIGNr; r^ ©r« r r3 r r® r® r¯ Úget_table_names« s üzPGDialect.get_table_namesc K s | j |d tjtjdS )N)r3 re r` )r~ r r r; r_ )r« r r r® r® r¯ Úget_temp_table_names´ s üz PGDialect.get_temp_table_namesc K s | j ||dtjdS )N)Úf©re r` ©r~ r; rj r r® r® r¯ r ½ s ÿz"PGDialect._get_foreign_table_namesc K s | j ||tjtjdS r} )r~ r Ú RELKINDS_VIEWr; r^ r r® r® r¯ Úget_view_namesà s üzPGDialect.get_view_namesc K s | j ||tjtjdS r} )r~ r ÚRELKINDS_MAT_VIEWr; r^ r r® r® r¯ Úget_materialized_view_namesÌ s üz%PGDialect.get_materialized_view_namesc K s | j ||tjtjdS r} )r~ r r r; r_ r r® r® r¯ Úget_temp_view_namesÕ s úz PGDialect.get_temp_view_namesc K s | j ||dtjdS )N)rm r r
r r® r® r¯ Úget_sequence_namesà s ÿz PGDialect.get_sequence_namesc K s t t tjjj¡ tj¡ tjjj|k| tj
|
tj ¡¡}| j ||t jd}| |¡}|d kr~t |rv| d| n|¡n|S d S )Nrg ró )r4 r Zpg_get_viewdefr\ r r Ú select_fromr rh rf r r rb r; rj rV r2 r{ )r« r Z view_namer3 r rZ Úresr® r® r¯ Úget_view_definitionæ s( ÿ
|
ÿüÿ
|
ÿ
|
ÿz PGDialect.get_view_definitionc C sJ zt |||f W S tk
|
rD t |r8| d| n|¡d Y nX d S )Nró )r) ÚKeyErrorr2 r{ )r« Údatarz r3 r® r® r¯ Ú_value_or_raiseý s ÿþzPGDialect._value_or_raisec C s |rdd|ifS di fS d S )NTÚ filter_namesFr® )r« r r® r® r¯ Ú_prepare_filter_names s z PGDialect._prepare_filter_namesr: zTuple[str, ...])Úkindr c C sT |t jkrtjS d}t j|kr(|tj7 }t j|kr<|tj7 }t j|krP|tj 7 }|S )Nr® )
|
r: rj r ri ZTABLEZRELKINDS_TABLEZVIEWr ZMATERIALIZED_VIEWr )r« r re r® r® r¯ Ú_kind_to_relkinds s
|
|
|
|
|
|
|
zPGDialect._kind_to_relkindsc K s0 | j |f||gtjtjd|}| |||¡S ©N)r3 r r` r )Úget_multi_columnsr; rj r: r ©r« r r r3 r r r® r® r¯ Ú get_columns s ÿûúzPGDialect.get_columnsc
|
C s | j dkrtjjj d¡n t ¡ d¡}| j dkröttj
|
dtjjj dkdtj jj dtj jjdtj jjd tj jjd
|
tj jjd tj jj¡ tj ¡ tjjj d ktj jjt t t t t tjjjt¡t¡tjjj¡t¡t ¡k¡ tj¡ ¡ d ¡}nt ¡ d ¡}tt tj jj!tj jj"¡ tj ¡ tj jj"tjjjktj jj#tjjj$ktjjj%¡ tj¡ ¡ d¡}| &|¡}ttjjj d¡t 'tjjj(tjjj)¡ d¡|tjjj* d¡tj+jj, d¡tj-jj. d¡|| tj+¡ /tjt 0tj+jj1tjjjktjjj$dktjjj2 ¡¡ /tj-t 0tj-jj3tjjjktj-jj4tjjj$k¡¡ | 5|¡¡ 6tj+jj,tjjj$¡} | j7| ||d} |r| tj+jj, 8t9d¡¡} | S )N)é r r7 ÚalwaysÚarÄ Ú incrementZminvalueZmaxvalueÚcacheÚcyclerÞ Úidentity_optionsr8 r rh rq r r r rg r ):r: r Ú pg_attributer Z attgeneratedÚlabelr5 rq r4 r Zjson_build_objectZ attidentityZ pg_sequenceZseqstartZ seqincrementZseqminZseqmaxZseqcacheZseqcycler r ZseqrelidrZ Zpg_get_serial_sequenceÚattrelidr, rQ Úattnamer$ Z correlateZscalar_subqueryÚ pg_get_exprZ
|
pg_attrdefZadbinZadrelidZadnumÚattnumZ atthasdefr rh ZatttypidZ atttypmodZ
|
attnotnullr\ rh Úpg_descriptionÚ descriptionÚ outerjoinÚand_r Z attisdroppedÚobjoidÚobjsubidrf rÕ rb Úin_r> )
|
r« r3 Úhas_filter_namesr` r r rK r8 re rZ r® r® r¯ Ú_columns_query# sò ÿ ý
|
òÿî þûø
|
õ òÿé)×+Õÿ/þÿúÿÿôòðÿ
|
þýõ óÿ
|
üíÿÿýä#Ý% Ûÿ)ÿzPGDialect._columns_queryc K s | |¡\}}| ||||¡} | | |¡ ¡ }
|
dd | j|d| d¡dD } tdd | j|d| d¡dD } | |
|
| | |¡} | ¡ S )Nc S s0 i | ](}|d s |d |d fn|d f|qS )rù r3 r r® )ró Údr® r® r¯ Ú
|
<dictcomp> s ÿ z/PGDialect.get_multi_columns.<locals>.<dictcomp>rp r )r3 r c s s8 | ]0}|d r |d f|fn|d |d f|fV qdS )rù r r3 Nr® )ró Zrecr® r® r¯ rõ ¶ s þÿz.PGDialect.get_multi_columns.<locals>.<genexpr>)
|
r r¯ r? Úmappingsr rà r) r Ú_get_columns_infor0 )r« r r3 r r` r r r® ÚparamsrZ ÚrowsÚdomainsri r[ r® r® r¯ r ¤ s& ÿþ ÿü zPGDialect.get_multi_columnsc $ s t d¡ t d¡}t d¡}t d¡}t d¡} fdd} tt}
|
|D ]L} | d d krrt ¡ |
|
|| d f< qJ|
|
|| d f } | d
|
} | d }| d }| d }| d }| d krÄd}d }} d}n d}| d| ¡}| |\}}tt |¡}| d }|
|
| ¡}|r| d¡}|
|
| ¡}|rD| d¡rDt| | d¡¡}nd}i }|dkr|r|| d¡\}}t |t |f}nd}nú|dkrd}nê|dkr¢d}nÚ|dkrÌd|d< |rÆt ||d < d}n°|d kröd|d< |rðt ||d < d}n|d kr d|d < |rt |f}nd}n\| d ¡rlt d!|t j¡} |rNt ||d < | rb| d¡|d"< d }d}n|r|t |f}|| jkr| j| } qNn²||krâ|| } t} | d |d< | d# sÐ| d$ |d$< t| d% }qNnh||krB|| } | d& }| |\}}tt |¡}|o"| d' }| d r||s|| d }q|nd } qNq|| rt| ||} |rª| jd( | } n6|rt d)|f ¡ tj} nt d*||f ¡ tj} |d+krÊt||d,kd-} d }nd } d}!|d k rJt
|
d.|¡}"|"d k rJt| jtjrd}!d/|" d0¡krJ|d k rJ|" d¡d1| d/ |" d0¡ |" d2¡ }|| |||!p^|d k | d3 d4}#| d k r|| |#d5< |d k r||#d6< | |#¡ qJ|
|
S )7Nz\[\]$z\(.*\)z \(([\d,]+)\)z\((.*)\)ú\s*,\s*c s d| ¡| d¡fS )NrÞ ré )rì Úendswith)Úattype©Zarray_type_patternr® r¯ Ú_handle_array_typeÊ s
|
ýz7PGDialect._get_columns_info.<locals>._handle_array_typer r rh r8 r r Tzno format_type()FrÞ rq r r® r ú,r )é5 r )r r rß r )r r¡ r£ r rã r¥ z interval (.+)rá rù r3 r rÎ rS r z6PostgreSQL format_type() returned NULL for column '%s'z*Did not recognize type '%s' of column '%s')NrÞ ó )r ó s)rÁ r z(nextval\(')([^']+)('.*$)ró r z"%s"r1 r )r rÎ rS r8 Ú autoincrementr rR rK )rë Úcompiler rZ r= r[ rì r r6 Zquoted_token_parserÚsearchri Úsplitr Ú
|
startswithrw ÚIÚ ischema_namesr r/ rD ZNULLTYPEr) Ú
|
issubclassr¹ ZIntegerr. )$r« rµ r¶ ri r3 Zattype_patternZcharlen_patternZ args_patternZargs_split_patternr» r[ Úrow_dictZ
|
table_colsrh r8 r r rK Zno_format_typer¹ Zis_arrayZenum_or_domain_keyrS ZcharlenÚargsrT ÚprecZscaleZ field_matchZcoltypeÚenumrt rR rÀ rw Z column_infor® rº r¯ r³ Ã s*
|
|
|
|
|
|
þ
|
ÿ
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
ÿ
|
ÿÿÿÿ
|
ÿ
|
|
ÿþýüÿ ú
|
|
zPGDialect._get_columns_infoc C s^ | |¡}ttjjjtjjj | |¡¡}| j |||d}|rZ| tjjj
|
t d¡¡}|S )Nrg r ) r r4 r r\ r r rh r rf rb r r> )r« r3 r® r` r re Úoid_qr® r® r¯ Ú_table_oids_query s
|
ÿþÿzPGDialect._table_oids_queryr3 r r r` c K s2 | |¡\}}| ||||¡} | | |¡}
|
|
|
¡ S r§ )r rÍ r? rU ) r« r r3 r r` r r r® r´ rÌ Úresultr® r® r¯ Ú_get_table_oids s zPGDialect._get_table_oidsc C sV t tjjjtjjjtj tjjj ¡
|
d¡tj tjjj d¡
|
d¡tj jj tj tj jjtjjjk¡ tjjjtdktjjj td¡¡ d¡}t |jj|jj|jj |jjtjjj tj¡ |t tjjj |jj ktjjj |jjk¡¡ d¡}t |jjtj t |jj|jj¡
|
d¡|jjtj |jj ¡
|
d ¡ !|jj|jj¡ "|jj|jj¡S )
|
Nr¦ r ÚordÚcontypeÚoidsÚconÚattrr2 r¨ )#r4 r Ú pg_constraintr ÚconrelidÚconnamer5 r ÚunnestZconkeyr¢ Úgenerate_subscriptsr§ r¨ r© r« r r rÑ r> r ÚsubqueryrÐ r¡ r¤ r r rª r¦ r£ Ú array_aggr ÚminÚgroup_byrÕ )r« Úcon_sqÚattr_sqr® r® r¯ Ú_constraint_query¦ sx ÿ ÿþ÷ ÿóîìÿûù þöñÿÿþú ø ÷ÿzPGDialect._constraint_queryc k sØ | j |||||f|}t|} | rÔ| dd
}
|
g | dd
< | | jdd |
|
D |d¡} tt} | D ] \} }}}| | |||f¡ qd|
|
D ]F\} }| | d¡}|rÂ|D ]\}}}||||fV q¦q|d d d fV qq d S )Nr é¸ c S s g | ] }|d qS ©r r® ©ró Úrr® r® r¯ r î s z1PGDialect._reflect_constraint.<locals>.<listcomp>)rÒ rÑ r® )rÏ rZ r? rà r r. rà )r« r rÑ r3 r r` r r Ú
|
table_oidsÚbatchesÚbatchrÎ Ú result_by_oidr r2 rp r Z tablenameZfor_oidr] r® r® r¯ Ú_reflect_constraintà s4 ÿÿ þ z PGDialect._reflect_constraintc K s0 | j |f||gtjtjd|}| |||¡S r )Úget_multi_pk_constraintr; rj r: r r r® r® r¯ Úget_pk_constraintý s ÿûúzPGDialect.get_pk_constraintc s2 | j |d|||f|}tj fdd|D S )NÚpc 3 sD | ]<\}}}}|f|d k r4|d kr(g n|||dn fV qd S )N)Úconstrained_columnsr r r® )ró r r2 Zpk_namer ©r8 r3 r® r¯ rõ s
|
÷ûýøz4PGDialect.get_multi_pk_constraint.<locals>.<genexpr>)ré r= Z pk_constraint)r« r r3 r r` r r rÎ r® rî r¯ rê s ÿÿ õz!PGDialect.get_multi_pk_constraintc K s2 | j |f||g|tjtjd|}| |||¡S )N)r3 r r3 r` r )Úget_multi_foreign_keysr; rj r: r )r« r r r3 r3 r r r® r® r¯ Úget_foreign_keys! s ÿúù zPGDialect.get_foreign_keysc C s: t j d¡}t j d¡}| |¡}tt jjjt jjj t
|
j t jjj d ¡t t jjj d¡fd d|jjt jjj t j¡ t jt
|
t jjj t jjjkt jjjdk¡¡ ||jj t jjjk¡ ||jj|jj k¡ t jt jjjt jjj k¡ t jjjt jjj ¡ | |¡¡}| |||¡}|r6| t jjj t d¡¡}|S )NZcls_refZnsp_refT©Zelse_r r ) r r\ ÚaliasrX r r4 r rh rÕ r× r5 rY r Úis_notÚpg_get_constraintdefrY r§ r¨ r r© rª rÖ rÑ Z confrelidr] r« rÕ r rf rb r r> ) r« r3 r® r` r Z pg_class_refZpg_namespace_refre rZ r® r® r¯ Ú_foreing_key_query5 sf
|
ÿþù ñïÿ ýìä à#ÿÜ(×+Õÿ.ÿz PGDialect._foreing_key_queryc C s
|
t d¡S )Na/ FOREIGN KEY \((.*?)\) REFERENCES (?:(.*?)\.)?(.*?)\((.*?)\)[\s]?(MATCH (FULL|PARTIAL|SIMPLE)+)?[\s]?(ON UPDATE (CASCADE|RESTRICT|NO ACTION|SET NULL|SET DEFAULT)+)?[\s]?(ON DELETE (CASCADE|RESTRICT|NO ACTION|SET NULL|SET DEFAULT)+)?[\s]?(DEFERRABLE|NOT DEFERRABLE)?[\s]?(INITIALLY (DEFERRED|IMMEDIATE)+)?)rë rÁ rô r® r® r¯ Ú_fk_regex_patterno s ÿzPGDialect._fk_regex_patternc " s | j | |¡\}} | ||||¡}
|
| |
|
| ¡} | j} tt} tj}| D ]D\}}}}}|d krp| | ||f< qH| ||f }t
|
| |¡ ¡ }|\ }}}}}}}} }} } }} | d k rÂ| dkr¾dnd} fddt d|¡D }|rö|| j krð|}n|}n*|r |¡}n|d k r ||kr |} |¡} fddt d|¡D }d d
|
d | fd | fd | fd| fd|ffD } |||||| |d}!| |!¡ qH| ¡ S )NZ
|
DEFERRABLETFc s g | ]} |¡qS r® ©rò ru r r® r¯ r ¬ s ÿz4PGDialect.get_multi_foreign_keys.<locals>.<listcomp>r· c s g | ]} |¡qS r® r÷ ru r r® r¯ r à s ÿz\s*,\sc S s& i | ] \}}|d k r|dkr||qS )Nz NO ACTIONr® )ró r3 r4 r® r® r¯ r± Ç s
|
ø z4PGDialect.get_multi_foreign_keys.<locals>.<dictcomp>ÚonupdateÚondeleterk r` rw )r rí Úreferred_schemaÚreferred_tableÚreferred_columnsru r )r¸ r rõ r? rö r rZ r= Z foreign_keysrë r Úgroupsrà Zdefault_schema_namerò r. r0 )"r« r r3 r r` r r3 r r® r´ rZ rÎ ZFK_REGEXZfkeysr8 r r× ZcondefZ conschemar Z table_fksry rí rú rû rü r rw rø rù r` rk ru Zfkey_dr® r r¯ rï } s~
|
ò
|
|
þ
|
|
|
|
þûþ ù z PGDialect.get_multi_foreign_keysc K s0 | j |f||gtjtjd|}| |||¡S r )Úget_multi_indexesr; rj r: r r r® r® r¯ Ú get_indexesÞ s ÿûúzPGDialect.get_indexesc C s t j d¡}tt jjjt jjjtj
|
t jjj ¡ d¡tj t jjj d¡ d¡ t jjj t jjj td¡¡ d¡}t|jj|jj|jjtj|jjdkt |jj|jjd d¡ft t jjjt ¡d d
|
¡|jjdk d ¡ |¡ t jt t jjj|jjkt jjj |jjk¡¡ |jj td¡¡ d ¡}t|jjtj |jj¡tj t!|jj"|jj¡ d ¡tj t!|jj#|jj¡ d¡ $|jj¡ d¡}| j%dkr®t jjj&}nt '¡ d¡}tt jjj|jj( d¡t jjj)t j*jj+ ,d ¡ d¡t jjj-|jj.t j/jj0tjt jjj1 ,d ¡t 2t jjj1t jjj¡fd d d¡||jj3|jj4 t j¡ t jjj td¡t jjj ¡ 5|t jjj|jj6k¡ 5t j/|jj7t j/jj6k¡ |t jjj|jjk¡ t j*t t jjjt j*jj+kt jjjt j*jj8kt j*jj9t :t; <d¡¡k¡¡ =t jjj|jj(¡S )NZcls_idxr¦ r rÐ rÒ Úidxr Trñ r¬ Úis_exprZidx_attrrA Úelements_is_exprZidx_cols©é r Ú indnkeyattsÚ relname_indexÚhas_constraintÚfilter_definition)rì Úurv )>r r\ rò r4 Zpg_indexr Z
|
indexrelidÚindrelidr5 r rØ Zindkeyr¢ rÙ r Z indisprimaryr r> rÚ rÐ rY r¦ Zpg_get_indexdefrZ r¡ r¤ rQ r r© rª r£ rÜ rÛ r r¬ r rÝ r: r rq rh Ú indisuniquerÕ rÖ ró Ú indoptionÚ
|
reloptionsZpg_amÚamnameZindpredr¥ rA r r r ZrelamZconindidrÑ rd r r
|
rÕ )r« Zpg_class_indexZidx_sqrß Zcols_sqr r® r® r¯ Ú _index_queryê sþ ÿþü
|
ö ôÿ
|
|
ÿþ ö õ îìþè ã âÿ#ÿþÿþú
|
ö õÿ ÿþþø ÷
|
èæ
|
ã ß$Û(×,ÿÿÿûÓ6 ÊÿzPGDialect._index_queryc ! K s¾ | j |||||f|}tt}tj} t|}
|
|
|
r¶|
|
dd
} g |
|
dd
< | | jddd | D i¡ ¡ } tt} | D ]}| |d |¡ qv| D ] \}}|| kr´| |||f< q| | D ]ò}|d }|||f }|d }|d }|d
|
}|rHt ||krH||d
}|d |
}|d |
}t
|
d d ||d
D sTt n |}|}g }||d d}t |rdd t ||D |d< ||d< n||d< i }t|d D ]X\} } d} | d@ rØ| d7 } | d@ sê| d7 } n| d@ rê| d7 } | r¦| ||| < q¦|r||d< |d r ||d< i } |d rHtd d |d D | d < |d } | d krf|d | d!< |d" r||d" | d#< | jd$kr||d%< || d&< | r¦| |d'< | |¡ q¼qq,| ¡ S )(Nr rá rÒ c S s g | ] }|d qS râ r® rã r® r® r¯ r s z/PGDialect.get_multi_indexes.<locals>.<listcomp>r
|
r rA r r c s s | ] }| V qd S r§ r® )ró r r® r® r¯ rõ ¥ s ÿz.PGDialect.get_multi_indexes.<locals>.<genexpr>r )r r~ c S s g | ]\}}|rd n|qS r§ r® )ró r} r r® r® r¯ r ° s ÿÚ column_namesr r r® r )ra r )Z
|
nulls_last)Z nulls_firstZcolumn_sortingr Zduplicates_constraintr c S s g | ]}| d ¡qS )ú=)rà )ró Úoptionr® r® r¯ r Ð s Zpostgresql_withr ZbtreeZpostgresql_usingr Zpostgresql_wherer Zinclude_columnsZpostgresql_includerX )rÏ r rZ r= Úindexesr? r r² r. r rU rx rW ÚzipÚ enumerater) r: r0 )!r« r r3 r r` r r rå r r8 ræ rç rÎ rè rÈ r r ÚrowZ
|
index_nameZ table_indexesZ all_elementsZall_elements_is_exprr Zinc_colsZ idx_elementsZidx_elements_is_exprr ZsortingZ col_indexZ col_flagsZ col_sortingrX r r® r® r¯ rþ p s° ÿÿ ÿ ÿ
|
þ
|
ÿþ
|
|
|
|
|
|
|
|
ÿ
|
|
ÿ zPGDialect.get_multi_indexesc K s0 | j |f||gtjtjd|}| |||¡S r )Ú get_multi_unique_constraintsr; rj r: r r r® r® r¯ Úget_unique_constraintsè s ÿûúz PGDialect.get_unique_constraintsc K sr | j |d||||f|}tt}tj} |D ]>\}
|
} } } | d krN| |||
|
f< q*|||
|
f | | | d¡ q*| ¡ S )Nr )r r r )ré r rZ r= Zunique_constraintsr. r0 )r« r r3 r r` r r rÎ Zuniquesr8 r r2 Zcon_namer r® r® r¯ r ö s. ÿÿ ýÿz&PGDialect.get_multi_unique_constraintsc K s0 | j |||gftjtjd|}| |||¡S ©N)r` r )Úget_multi_table_commentr; rj r: r r r® r® r¯ Úget_table_comment s ýûúzPGDialect.get_table_commentc C s | |¡}ttjjjtjjj tj¡ tjt
|
tjjj tjjj ktjjjdk¡¡ | |¡¡}| |||¡}|r| tjjj td¡¡}|S )Nr r )r r4 r r\ r rh r§ r¨ r r© r5 rª r r« r¬ r rf rb r r> ©r« r3 r® r` r re rZ r® r® r¯ Ú_comment_query" s0
|
þüÿ ýù óÿÿzPGDialect._comment_queryc sD | |¡\}}| |||¡} | | |¡}
|
tj fdd|
|
D S )Nc 3 s0 | ](\}}|f|d k r d|in fV qd S )Nr r® )ró rz r rî r® r¯ rõ D s ýþz4PGDialect.get_multi_table_comment.<locals>.<genexpr>)r r r? r= Z table_comment) r« r r3 r r` r r r® r´ rZ rÎ r® rî r¯ r < s ûz!PGDialect.get_multi_table_commentc K s0 | j |||gftjtjd|}| |||¡S r )Úget_multi_check_constraintsr; rj r: r r r® r® r¯ Úget_check_constraintsL s ýûúz PGDialect.get_check_constraintsc C sð | |¡}ttjjjtjjjtj tjjj
|
d ¡t tjjj
|
d¡fd dtj jj tj¡ tjt tjjj
|
tjjjktjjjdk¡¡ tj tj jjtjjj
|
k¡ tjjjtjjj¡ | |¡¡}| |||¡}|rì| tjjj td¡¡}|S )NTrñ r r )r r4 r r\ r rh rÕ r× r5 rY r ró rô r§ r¨ r r© rª rÖ rÑ r« rÕ r rf rb r r> r r® r® r¯ Ú_check_constraint_queryX sT
|
ÿþù òðÿ ýíÿå à"Þÿ%ÿz!PGDialect._check_constraint_queryc K sò | |¡\}}| ||||¡} | | |¡}
|
tt} tj} |
|
D ]¬\} }}}|d krh|d krh| | || f< q<tjd|tj d}|st
|
d| ¡ d}n tj dtj d d| d¡¡}|||d}|rÖ| d ¡rÖd
|
d i|d < | || f |¡ q<| ¡ S ) Nz ^CHECK *\((.+)\)( NOT VALID)?$)rä z)Could not parse CHECK constraint text: %rrÞ z^[\s\n]*\((.+)\)[\s\n]*$z\1r )r rÁ r r rW TrX )r r r? r rZ r= Úcheck_constraintsrë rw ÚDOTALLr6 r/ rÁ rì ri r. r0 )r« r r3 r r` r r r® r´ rZ rÎ r! r8 r Z
|
check_nameÚsrcr ry rÁ Úentryr® r® r¯ r sJ ÿ ÿ ÿ þý z%PGDialect.get_multi_check_constraintsc C sL |d kr,| t tjjj¡tjjjdk¡}n |dkrH| tjjj|k¡}|S ro )r r rt rq r r rX rY )r« rZ r3 r® r® r¯ Ú_pg_type_filter_schema³ s ýz PGDialect._pg_type_filter_schemac C sä t tjjjtj ttjjj tjjj
|
¡ d¡ tjjj¡ d¡}t tjjj d¡t tjjj¡ d¡tjjj d¡|jj d¡ tjtjjjtjjjk¡ |tjjj|jjk¡ tjjjdk¡ tjjjtjjj¡}| ||¡S )Nr Zlbl_aggr rù r3 rg ) r4 r Zpg_enumr Z enumtypidr5 r rÛ r Z enumlabelZ enumsortorderr¢ rÝ rÚ rq rr rt r rX rY r r rs r© r ÚtyptyperÕ r% )r« r3 Z
|
lbl_agg_sqrZ r® r® r¯ Ú _enum_query¾ sN þÿûþ ÷
|
öÿÿ ú ÿö ò ð îÿzPGDialect._enum_queryc K sT | j s
|
g S | | |¡¡}g }|D ],\}}}}| ||||d krDg n|d¡ q"|S )N)r r3 rù r )rÓ r? r' r. ) r« r r3 r rÎ ri r rù r r® r® r¯ r ç s üÿzPGDialect._load_enumsc C s@ t tjjjtj t tjjj d¡¡
|
d¡tj tjjj ¡
|
d¡ tjjjdk¡ tjjj¡ d¡}t tjjj
|
d¡t tjjjtjjj¡
|
d¡tjjj
|
d¡tjjj
|
d ¡t tjjj ¡
|
d
|
¡tjjj
|
d ¡|jj|jj tjtjjj tjjj k¡ |tjjj |jjk¡ tjjj d k¡ tjjjtjjj¡}| ||¡S ) NTÚcondefsÚconnamesr Zdomain_constraintsr r¹ rS r8 rù r3 r° )!r4 r rÕ r Zcontypidr5 r rÛ rô r r¢ r× r rÝ rÚ rq rr rh Z typbasetypeZ typtypmodZ
|
typnotnullZ
|
typdefaultrt rX rY r( r) r rs r© r& rÕ r% )r« r3 rÞ rZ r® r® r¯ Ú _domain_queryú sj ÿÿüÿù ô óòÿþýÿóÿïê è æÿ zPGDialect._domain_queryc K s | | |¡¡}g }| ¡ D ] }t d|d ¡ d¡}g }|d rtt|d |d dd d} | D ]$\}
|
} | d d
|
} | |
|
| d ¡ qd|d |d |d ||d |d |d} | | ¡ q |S )Nz([^\(]+)r¹ r r) r( c S s | d S )Nr r® )r: r® r® r¯ Ú<lambda>= ó z)PGDialect._load_domains.<locals>.<lambda>)r+ é r' )r r[ r r3 rù rS r8 )r r3 rù rÎ rS r8 r ) r? r* r² rë r ri Úsortedr r. )r« r r3 r rÎ r¶ rt r¹ r Zsorted_constraintsr Zdef_r[ Z
|
domain_recr® r® r¯ r . s. þ ù zPGDialect._load_domainsc C s | d¡ ¡ }|dk| _d S )Nz show standard_conforming_stringsrs )rL rV rù )r« r Z
|
std_stringr® r® r¯ r; R s ÿz PGDialect._set_backslash_escapes)NN)TF)TF)N)N)N)N)N)N)N)N)N)N)N)N)N)N)N)NF)F)N)N)N)N)N)N)rD rE rF r Zsupports_statement_cacheZsupports_alterZmax_identifier_lengthZsupports_sane_rowcountr9 Z
|
BindTypingZ RENDER_CASTSZ bind_typingrÓ Zsupports_native_booleanZsupports_native_uuidrO Zsupports_sequencesZsequences_optionalZ"preexecute_autoincrement_sequencesZpostfetch_lastrowidZuse_insertmanyvaluesZreturns_native_bytesrE ZANY_AUTOINCREMENTZUSE_INSERT_FROM_SELECTZRENDER_SELECT_COL_CASTSZ"insertmanyvalues_implicit_sentinelZsupports_commentsZ supports_constraint_commentsZsupports_default_valuesZsupports_default_metavalueZsupports_empty_insertZsupports_multivalues_insertrL Zdefault_paramstylerÆ Úcolspecsr¦ Zstatement_compilerrH Z ddl_compilerr Ztype_compiler_clsrñ r¿ r Zexecution_ctx_clsr Z inspectorZupdate_returningZdelete_returningZinsert_returningZupdate_returning_multifromZdelete_returning_multifromr8 r4 Zconnection_characteristicsr} r" r. r3 ZIndexZTableZCheckConstraintZForeignKeyConstraintZconstruct_argumentsZreflection_optionsrù r r r5 r9 r= rC rE r% r+ r0 r1 rK rM rR rS rU rW r< r r[ rb rf r rk rl rn r rz r r| r~ r r r r r r r r r r r r r¯ r r³ rÍ Z flexi_cacherF Z dp_stringZdp_string_listZ dp_plain_objrÏ r6 Zmemoized_propertyrà ré rë rê rð rõ rö rï rÿ r rþ r r r r r r r r r% r' r r* r r; rG r® r® rû r¯ r O s ÿþÿÿþÿ ùþ úþ ÿþ ÿþâ&
|
|
ÿ
|
ÿ
|
|
ÿ
|
|
|
|
|
|
|
|
H
|
ü
|
|
9 û
|
9
|
ù
|
a
|
x ÿ
|
|
.,
|
(
|
3 #r )rû Ú
|
__future__r Ú collectionsr Ú functoolsr rë Útypingr r r r rÞ r
|
r r Z_hstorer Ú_jsonr r Ú_rangesÚextr r Z named_typesr r r r r r r Útypesr r r r r r r r r r! r" r# r$ r% r& r' r( r) r* r+ r, r- r. r/ r0 r2 r3 r4 r5 r6 Zenginer7 r8 r9 r: r; r< Zengine.reflectionr= r> r? r@ rA rB rC rD r Z sql.compilerrE Z sql.visitorsrF rG rH rI rJ rK rL rM rN rO rP rQ rR rS Z util.typingrT rÁ rÅ r rö r\ ZIntervalr^ rÏ Z JSONPathTyperð r/ r± r´ r rÄ rÆ rÈ rÊ rÌ r¶ r¸ rº r¼ r¾ rÀ rº rÆ Z SQLCompilerr¦ Z DDLCompilerrH ZGenericTypeCompilerr ZIdentifierPreparerrñ r÷ rý rþ r Z Inspectorr ZDefaultExecutionContextr ZConnectionCharacteristicr" r. r4 r r® r® r® r¯ Ú<module> s8 { j ú Î6 4 ` ; n7
|
ÿ
|
ÿ
|
