SQLAlchemy 2.0 Documentation
Dialects
- PostgreSQL¶
- Support for the PostgreSQL database.
- Sequences/SERIAL/IDENTITY
- Server Side Cursors
- Transaction Isolation Level
- Setting READ ONLY / DEFERRABLE
- Temporary Table / Resource Reset for Connection Pooling
- Setting Alternate Search Paths on Connect
- Remote-Schema Table Introspection and PostgreSQL search_path
- INSERT/UPDATE…RETURNING
- INSERT…ON CONFLICT (Upsert)
- Full Text Search
- FROM ONLY …
- PostgreSQL-Specific Index Options
- PostgreSQL Index Reflection
- Special Reflection Options
- PostgreSQL Table Options
- PostgreSQL Constraint Options
- Table values, Table and Column valued functions, Row and Tuple objects
- ARRAY Types
- JSON Types
- HSTORE Type
- ENUM Types
- Range and Multirange Types
Range
Range.__eq__()
Range.adjacent_to()
Range.contained_by()
Range.contains()
Range.difference()
Range.intersection()
Range.is_empty
Range.isempty
Range.lower
Range.lower_inc
Range.lower_inf
Range.not_extend_left_of()
Range.not_extend_right_of()
Range.overlaps()
Range.strictly_left_of()
Range.strictly_right_of()
Range.union()
Range.upper
Range.upper_inc
Range.upper_inf
- Multiranges
- Network Data Types
- PostgreSQL Data Types
AbstractRange
AbstractRange.comparator_factory
AbstractRange.comparator_factory.adjacent_to()
AbstractRange.comparator_factory.contained_by()
AbstractRange.comparator_factory.contains()
AbstractRange.comparator_factory.difference()
AbstractRange.comparator_factory.intersection()
AbstractRange.comparator_factory.not_extend_left_of()
AbstractRange.comparator_factory.not_extend_right_of()
AbstractRange.comparator_factory.overlaps()
AbstractRange.comparator_factory.strictly_left_of()
AbstractRange.comparator_factory.strictly_right_of()
AbstractRange.comparator_factory.union()
AbstractSingleRange
AbstractMultiRange
ARRAY
BIT
BYTEA
CIDR
CITEXT
DOMAIN
ENUM
HSTORE
HSTORE.Comparator
HSTORE.Comparator.array()
HSTORE.Comparator.contained_by()
HSTORE.Comparator.contains()
HSTORE.Comparator.defined()
HSTORE.Comparator.delete()
HSTORE.Comparator.has_all()
HSTORE.Comparator.has_any()
HSTORE.Comparator.has_key()
HSTORE.Comparator.keys()
HSTORE.Comparator.matrix()
HSTORE.Comparator.slice()
HSTORE.Comparator.vals()
HSTORE.__init__()
HSTORE.bind_processor()
HSTORE.comparator_factory
HSTORE.hashable
HSTORE.result_processor()
INET
INTERVAL
JSON
JSONB
JSONPATH
MACADDR
MACADDR8
MONEY
OID
REGCONFIG
REGCLASS
TIMESTAMP
TIME
TSQUERY
TSVECTOR
INT4RANGE
INT8RANGE
NUMRANGE
DATERANGE
TSRANGE
TSTZRANGE
INT4MULTIRANGE
INT8MULTIRANGE
NUMMULTIRANGE
DATEMULTIRANGE
TSMULTIRANGE
TSTZMULTIRANGE
MultiRange
- PostgreSQL SQL Elements and Functions
- PostgreSQL Constraint Types
- PostgreSQL DML Constructs
- psycopg2
- DBAPI
- Connecting
- psycopg2 Connect Arguments
- SSL Connections
- Unix Domain Connections
- Specifying multiple fallback hosts
- Empty DSN Connections / Environment Variable Connections
- Per-Statement/Connection Execution Options
- Psycopg2 Fast Execution Helpers
- Unicode with Psycopg2
- Transactions
- Psycopg2 Transaction Isolation Level
- NOTICE logging
- HSTORE type
- psycopg
- pg8000
- asyncpg
- psycopg2cffi
- MySQL and MariaDB
- SQLite
- Oracle
- Microsoft SQL Server
Project Versions
- Previous: Dialects
- Next: MySQL and MariaDB
- Up: Home
- On this page:
- PostgreSQL
- Support for the PostgreSQL database.
- Sequences/SERIAL/IDENTITY
- Server Side Cursors
- Transaction Isolation Level
- Setting READ ONLY / DEFERRABLE
- Temporary Table / Resource Reset for Connection Pooling
- Setting Alternate Search Paths on Connect
- Remote-Schema Table Introspection and PostgreSQL search_path
- INSERT/UPDATE…RETURNING
- INSERT…ON CONFLICT (Upsert)
- Full Text Search
- FROM ONLY …
- PostgreSQL-Specific Index Options
- PostgreSQL Index Reflection
- Special Reflection Options
- PostgreSQL Table Options
- PostgreSQL Constraint Options
- Table values, Table and Column valued functions, Row and Tuple objects
- ARRAY Types
- JSON Types
- HSTORE Type
- ENUM Types
- Range and Multirange Types
Range
Range.__eq__()
Range.adjacent_to()
Range.contained_by()
Range.contains()
Range.difference()
Range.intersection()
Range.is_empty
Range.isempty
Range.lower
Range.lower_inc
Range.lower_inf
Range.not_extend_left_of()
Range.not_extend_right_of()
Range.overlaps()
Range.strictly_left_of()
Range.strictly_right_of()
Range.union()
Range.upper
Range.upper_inc
Range.upper_inf
- Multiranges
- Network Data Types
- PostgreSQL Data Types
AbstractRange
AbstractRange.comparator_factory
AbstractRange.comparator_factory.adjacent_to()
AbstractRange.comparator_factory.contained_by()
AbstractRange.comparator_factory.contains()
AbstractRange.comparator_factory.difference()
AbstractRange.comparator_factory.intersection()
AbstractRange.comparator_factory.not_extend_left_of()
AbstractRange.comparator_factory.not_extend_right_of()
AbstractRange.comparator_factory.overlaps()
AbstractRange.comparator_factory.strictly_left_of()
AbstractRange.comparator_factory.strictly_right_of()
AbstractRange.comparator_factory.union()
AbstractSingleRange
AbstractMultiRange
ARRAY
BIT
BYTEA
CIDR
CITEXT
DOMAIN
ENUM
HSTORE
HSTORE.Comparator
HSTORE.Comparator.array()
HSTORE.Comparator.contained_by()
HSTORE.Comparator.contains()
HSTORE.Comparator.defined()
HSTORE.Comparator.delete()
HSTORE.Comparator.has_all()
HSTORE.Comparator.has_any()
HSTORE.Comparator.has_key()
HSTORE.Comparator.keys()
HSTORE.Comparator.matrix()
HSTORE.Comparator.slice()
HSTORE.Comparator.vals()
HSTORE.__init__()
HSTORE.bind_processor()
HSTORE.comparator_factory
HSTORE.hashable
HSTORE.result_processor()
INET
INTERVAL
JSON
JSONB
JSONPATH
MACADDR
MACADDR8
MONEY
OID
REGCONFIG
REGCLASS
TIMESTAMP
TIME
TSQUERY
TSVECTOR
INT4RANGE
INT8RANGE
NUMRANGE
DATERANGE
TSRANGE
TSTZRANGE
INT4MULTIRANGE
INT8MULTIRANGE
NUMMULTIRANGE
DATEMULTIRANGE
TSMULTIRANGE
TSTZMULTIRANGE
MultiRange
- PostgreSQL SQL Elements and Functions
- PostgreSQL Constraint Types
- PostgreSQL DML Constructs
- psycopg2
- DBAPI
- Connecting
- psycopg2 Connect Arguments
- SSL Connections
- Unix Domain Connections
- Specifying multiple fallback hosts
- Empty DSN Connections / Environment Variable Connections
- Per-Statement/Connection Execution Options
- Psycopg2 Fast Execution Helpers
- Unicode with Psycopg2
- Transactions
- Psycopg2 Transaction Isolation Level
- NOTICE logging
- HSTORE type
- psycopg
- pg8000
- asyncpg
- psycopg2cffi
PostgreSQL¶
Support for the PostgreSQL database.
The following table summarizes current support levels for database release versions.
Support type |
Versions |
---|---|
9.6+ |
|
9+ |
DBAPI Support¶
The following dialect/DBAPI options are available. Please refer to individual DBAPI sections for connect information.
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 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
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 Identity
construct in a
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 Table
object would be:
CREATE TABLE data (
id INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 42 CYCLE),
data VARCHAR,
PRIMARY KEY (id)
)
Changed in version 1.4: Added Identity
construct
in a 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)
)
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
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 since version 1.4: The dialect-level server_side_cursors flag is deprecated
and will be removed in a future release. Please use the
Connection.stream_results
execution option for
unbuffered cursor support.
Transaction Isolation Level¶
Most SQLAlchemy dialects support setting of transaction isolation level
using the create_engine.isolation_level
parameter
at the create_engine()
level, and at the Connection
level via the 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 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 Engine
which each apply
different isolation level settings. See the discussion at
Setting Transaction Isolation Levels including DBAPI Autocommit for background.
Valid values for isolation_level
on most PostgreSQL dialects include:
READ COMMITTED
READ UNCOMMITTED
REPEATABLE READ
SERIALIZABLE
AUTOCOMMIT
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
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.
New in version 1.4: added support for the postgresql_readonly
and postgresql_deferrable
execution options.
Temporary Table / Resource Reset for Connection Pooling¶
The QueuePool
connection pool implementation used
by the SQLAlchemy Engine
object includes
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 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 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()
Changed in version 2.0.0b3: Added additional state arguments to
the 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 PoolEvents.checkin()
handler remain usable as well.
See also
Reset On Return - in the Connection Pooling documentation
Setting Alternate Search Paths on Connect¶
The PostgreSQL search_path
variable refers to the list of schema names
that will be implicitly referenced when a particular table or other
object is referenced in a SQL statement. As detailed in the next section
Remote-Schema Table Introspection and PostgreSQL search_path, 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 Setting a Default Schema for New 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.
See also
Setting a Default Schema for New Connections - in the Describing Databases with MetaData documentation
Remote-Schema Table Introspection and PostgreSQL search_path¶
Section Best Practices Summarized
keep the search_path
variable set to its default of public
, without
any other schema names. Ensure the username used to connect does not
match remote schemas, or ensure the "$user"
token is removed from
search_path
. For other schema names, name these explicitly
within Table
definitions. Alternatively, the
postgresql_ignore_search_path
option will cause all reflected
Table
objects to have a Table.schema
attribute set up.
The PostgreSQL dialect can reflect tables from any schema, as outlined in Reflecting Tables from Other Schemas.
In all cases, the first thing SQLAlchemy does when reflecting tables is
to determine the default schema for the current database connection.
It does this using the PostgreSQL current_schema()
function, illustated below using a PostgreSQL client session (i.e. using
the psql
tool):
test=> select current_schema();
current_schema
----------------
public
(1 row)
Above we see that on a plain install of PostgreSQL, the default schema name
is the name public
.
However, if your database username matches the name of a schema, PostgreSQL’s
default is to then use that name as the default schema. Below, we log in
using the username scott
. When we create a schema named scott
, it
implicitly changes the default schema:
test=> select current_schema();
current_schema
----------------
public
(1 row)
test=> create schema scott;
CREATE SCHEMA
test=> select current_schema();
current_schema
----------------
scott
(1 row)
The behavior of current_schema()
is derived from the
PostgreSQL search path
variable search_path
, which in modern PostgreSQL versions defaults to this:
test=> show search_path;
search_path
-----------------
"$user", public
(1 row)
Where above, the "$user"
variable will inject the current username as the
default schema, if one exists. Otherwise, public
is used.
When a Table
object is reflected, if it is present in the
schema indicated by the current_schema()
function, the schema name assigned
to the “.schema” attribute of the Table is the Python “None” value. Otherwise, the
“.schema” attribute will be assigned the string name of that schema.
With regards to tables which these 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 search_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 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 Table
as well as
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'
See also
Interaction of Schema-qualified Reflection with the Default Schema - discussion of the issue from a backend-agnostic perspective
The Schema Search 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 _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())
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
insert()
function, which provides
the generative methods Insert.on_conflict_do_update()
and Insert.on_conflict_do_nothing()
:
>>> 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)
INSERT INTO my_table (id, data) VALUES (%(id)s, %(data)s)
ON CONFLICT (id) DO NOTHING
>>> do_update_stmt = insert_stmt.on_conflict_do_update(
... constraint="pk_my_table", set_=dict(data="updated value")
... )
>>> print(do_update_stmt)
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
See also
INSERT .. 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
Insert.on_conflict_do_update.index_elements
argument specifies a sequence containing string column names,Column
objects, and/or SQL expression elements, which would identify a unique index:>>> do_update_stmt = insert_stmt.on_conflict_do_update( ... index_elements=["id"], set_=dict(data="updated value") ... ) >>> print(do_update_stmt)
INSERT INTO my_table (id, data) VALUES (%(id)s, %(data)s) ON CONFLICT (id) DO UPDATE SET data = %(param_1)s>>> do_update_stmt = insert_stmt.on_conflict_do_update( ... index_elements=[my_table.c.id], set_=dict(data="updated value") ... ) >>> print(do_update_stmt)INSERT INTO my_table (id, data) VALUES (%(id)s, %(data)s) ON CONFLICT (id) DO UPDATE SET data = %(param_1)sWhen using
Insert.on_conflict_do_update.index_elements
to infer an index, a partial index can be inferred by also specifying the use theInsert.on_conflict_do_update.index_where
parameter:>>> 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)
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.dataThe
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:>>> do_update_stmt = insert_stmt.on_conflict_do_update( ... constraint="my_table_idx_1", set_=dict(data="updated value") ... ) >>> print(do_update_stmt)
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>>> do_update_stmt = insert_stmt.on_conflict_do_update( ... constraint="my_table_pk", set_=dict(data="updated value") ... ) >>> print(do_update_stmt)INSERT INTO my_table (id, data) VALUES (%(id)s, %(data)s) ON CONFLICT ON CONSTRAINT my_table_pk DO UPDATE SET data = %(param_1)sThe
Insert.on_conflict_do_update.constraint
argument may also refer to a SQLAlchemy construct representing a constraint, e.g.UniqueConstraint
,PrimaryKeyConstraint
,Index
, orExcludeConstraint
. 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 aTable
using theTable.primary_key
attribute:>>> do_update_stmt = insert_stmt.on_conflict_do_update( ... constraint=my_table.primary_key, set_=dict(data="updated value") ... ) >>> print(do_update_stmt)
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
Insert.on_conflict_do_update.set_
parameter. This
parameter accepts a dictionary which consists of direct values
for UPDATE:
>>> 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)
INSERT INTO my_table (id, data) VALUES (%(id)s, %(data)s)
ON CONFLICT (id) DO UPDATE SET data = %(param_1)s
Warning
The Insert.on_conflict_do_update()
method does not take into
account Python-side default UPDATE values or generation functions, e.g.
those specified using Column.onupdate
.
These values will not be exercised for an ON CONFLICT style of UPDATE,
unless they are manually specified in the
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
Insert.excluded
is available as an attribute on
the Insert
object; this object is a
ColumnCollection
which alias contains all columns of the target
table:
>>> 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)
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 Insert.on_conflict_do_update()
method also accepts
a WHERE clause using the Insert.on_conflict_do_update.where
parameter, which will limit those rows which receive an UPDATE:
>>> 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)
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
Insert.on_conflict_do_nothing()
method:
>>> stmt = insert(my_table).values(id="some_id", data="inserted value")
>>> stmt = stmt.on_conflict_do_nothing(index_elements=["id"])
>>> print(stmt)
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:
>>> stmt = insert(my_table).values(id="some_id", data="inserted value")
>>> stmt = stmt.on_conflict_do_nothing()
>>> print(stmt)
INSERT INTO my_table (id, data) VALUES (%(id)s, %(data)s)
ON CONFLICT DO NOTHING
Full Text Search¶
PostgreSQL’s full text search system is available through the use of the
func
namespace, combined with the use of custom operators
via the Operators.bool_op()
method. For simple cases with some
degree of cross-backend compatibility, the Operators.match()
operator
may also be used.
Simple plain text matching with match()
¶
The 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 Operators.match()
will automatically
make use of plainto_tsquery()
to specify the type of tsquery. This
establishes basic database cross-compatibility for Operators.match()
with other backends.
Changed in version 2.0: The default tsquery generation function used by the
PostgreSQL dialect with 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 Operators.match()
may make use of the func
namespace to generate PostgreSQL full-text
functions, in combination with 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:
SELECT to_tsquery('cat') @> to_tsquery('cat & rat')
The 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 sqlalchemy.sql.expression.func
namespace
assuming the sqlalchemy.dialects.postgresql
package has been imported,
or create_engine()
has been invoked using a postgresql
dialect. These functions are documented at:
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 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 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.
See also
Full Text Search - 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-Specific Index Options¶
Several extensions to the 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.
New in version 1.4.
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 Index
using the postgresql_where
keyword argument:
Index("my_index", my_table.c.id, postgresql_where=my_table.c.value > 10)
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 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 Column
, i.e. the name used to access it from
the .c
collection of 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
ExcludeConstraint
construct using the
ExcludeConstraint.ops
parameter. See that parameter for
details.
New in version 1.3.21: added support for operator classes with
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 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.
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 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 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 Table
as well.
Indexes with CONCURRENTLY¶
The PostgreSQL index option CONCURRENTLY is supported by passing the
flag postgresql_concurrently
to the 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)
See also
PostgreSQL Index Reflection¶
The PostgreSQL database creates a UNIQUE INDEX implicitly whenever the
UNIQUE CONSTRAINT construct is used. When inspecting a table using
Inspector
, the Inspector.get_indexes()
and the 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 Table.indexes
when it is detected as mirroring a
UniqueConstraint
in the Table.constraints
collection
.
Special Reflection Options¶
The Inspector
used for the PostgreSQL backend is an instance
of 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())
Object Name | Description |
---|---|
- class sqlalchemy.dialects.postgresql.base.PGInspector¶
-
Class signature
class
sqlalchemy.dialects.postgresql.base.PGInspector
(sqlalchemy.engine.reflection.Inspector
)-
method
sqlalchemy.dialects.postgresql.base.PGInspector.
get_domains(schema: str | None = None) → List[ReflectedDomain]¶ 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 andcheck
with the constraint text.
- Parameters:
schema¶ – schema name. If None, the default schema (typically ‘public’) is used. May also be set to
'*'
to indicate load domains for all schemas.
New in version 2.0.
-
method
sqlalchemy.dialects.postgresql.base.PGInspector.
get_enums(schema: str | None = None) → List[ReflectedEnum]¶ 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.
- Parameters:
schema¶ – schema name. If None, the default schema (typically ‘public’) is used. May also be set to
'*'
to indicate load enums for all schemas.
-
method
sqlalchemy.dialects.postgresql.base.PGInspector.
get_foreign_table_names(schema: str | None = None) → List[str]¶ Return a list of FOREIGN TABLE names.
Behavior is similar to that of
Inspector.get_table_names()
, except that the list is limited to those tables that report arelkind
value off
.
-
method
sqlalchemy.dialects.postgresql.base.PGInspector.
get_table_oid(table_name: str, schema: str | None = None) → int¶ Return the OID for the given table name.
- Parameters:
table_name¶ – string name of the table. For special quoting, use
quoted_name
.schema¶ – string schema name; if omitted, uses the default schema of the database connection. For special quoting, use
quoted_name
.
-
method
sqlalchemy.dialects.postgresql.base.PGInspector.
has_type(type_name: str, schema: str | None = None, **kw: Any) → bool¶ Return if the database has the specified type in the provided schema.
- Parameters:
New in version 2.0.
-
method
PostgreSQL Table Options¶
Several options for CREATE TABLE are supported directly by the PostgreSQL
dialect in conjunction with the Table
construct:
INHERITS
:Table("some_table", metadata, ..., postgresql_inherits="some_supertable") Table("some_table", metadata, ..., postgresql_inherits=("t1", "t2", ...))
ON COMMIT
:Table("some_table", metadata, ..., postgresql_on_commit="PRESERVE ROWS")
PARTITION BY
:Table( "some_table", metadata, ..., postgresql_partition_by="LIST (part_column)", )
New in version 1.2.6.
TABLESPACE
:Table("some_table", metadata, ..., postgresql_tablespace="some_tablespace")
The above option is also available on the
Index
construct.USING
:Table("some_table", metadata, ..., postgresql_using="heap")
New in version 2.0.26.
WITH OIDS
:Table("some_table", metadata, ..., postgresql_with_oids=True)
WITHOUT OIDS
:Table("some_table", metadata, ..., postgresql_with_oids=False)
See also
PostgreSQL CREATE TABLE options - in the PostgreSQL documentation.
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 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
CheckConstraint
,ForeignKeyConstraint
andForeignKey
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 )
New in version 1.4.32.
See also
PostgreSQL ALTER TABLE options - in the PostgreSQL documentation.
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.
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 FunctionElement.table_valued()
method in conjunction
with Function
objects generated from the func
namespace.
Examples from PostgreSQL’s reference documentation follow below:
json_each()
:>>> from sqlalchemy import select, func >>> stmt = select( ... func.json_each('{"a":"foo", "b":"bar"}').table_valued("key", "value") ... ) >>> print(stmt)
SELECT anon_1.key, anon_1.value FROM json_each(:json_each_1) AS anon_1json_populate_record()
:>>> 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)
SELECT x.a, x.b FROM json_populate_record(null::myrowtype, :json_populate_record_1) AS xjson_to_record()
- this form uses a PostgreSQL specific form of derived columns in the alias, where we may make use ofcolumn()
elements with types to produce them. TheFunctionElement.table_valued()
method produces aTableValuedAlias
construct, and the methodTableValuedAlias.render_derived()
method sets up the derived columns specification:>>> 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)
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 includingunnest()
andgenerate_series()
. TheFunctionElement.table_valued()
method accepts a keyword parameterwith_ordinality
for this purpose, which accepts the string name that will be applied to the “ordinality” column:>>> from sqlalchemy import select, func >>> stmt = select( ... func.generate_series(4, 1, -1) ... .table_valued("value", with_ordinality="ordinality") ... .render_derived() ... ) >>> print(stmt)
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)
New in version 1.4.0b2.
See also
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
FunctionElement.column_valued()
method of FunctionElement
:
json_array_elements()
:>>> from sqlalchemy import select, func >>> stmt = select( ... func.json_array_elements('["one", "two"]').column_valued("x") ... ) >>> print(stmt)
SELECT x FROM json_array_elements(:json_array_elements_1) AS xunnest()
- in order to generate a PostgreSQL ARRAY literal, thearray()
construct may be used:>>> from sqlalchemy.dialects.postgresql import array >>> from sqlalchemy import select, func >>> stmt = select(func.unnest(array([1, 2])).column_valued()) >>> print(stmt)
SELECT anon_1 FROM unnest(ARRAY[%(param_1)s, %(param_2)s]) AS anon_1The function can of course be used against an existing table-bound column that’s of type
ARRAY
:>>> 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)
SELECT unnested_value FROM unnest(t.value) AS unnested_value
Row Types¶
Built-in support for rendering a ROW
may be approximated using
func.ROW
with the sqlalchemy.func
namespace, or by using the
tuple_()
construct:
>>> 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)
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)
Table Types passed to Functions¶
PostgreSQL supports passing a table as an argument to a function, which is
known as a “record” type. SQLAlchemy FromClause
objects
such as Table
support this special form using the
FromClause.table_valued()
method, which is comparable to the
FunctionElement.table_valued()
method except that the collection
of columns is already established by that of the FromClause
itself:
>>> 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)
SELECT row_to_json(a) AS row_to_json_1
FROM a
New in version 1.4.0b2.
ARRAY Types¶
The PostgreSQL dialect supports arrays, both as multidimensional column types as well as array literals:
ARRAY
- ARRAY datatypearray
- array literalarray_agg()
- ARRAY_AGG SQL functionaggregate_order_by
- helper for PG’s ORDER BY aggregate function syntax.
JSON Types¶
The PostgreSQL dialect supports both JSON and JSONB datatypes, including psycopg2’s native support and support for all of PostgreSQL’s special operators:
HSTORE Type¶
The PostgreSQL HSTORE type as well as hstore literals are supported:
ENUM Types¶
PostgreSQL has an independently creatable TYPE structure which is used to implement an enumerated type. This approach introduces significant complexity on the SQLAlchemy side in terms of when this type should be CREATED and DROPPED. The type object is also an independently reflectable entity. The following sections should be consulted:
ENUM
- DDL and typing support for ENUM.PGInspector.get_enums()
- retrieve a listing of current ENUM typesENUM.create()
,ENUM.drop()
- individual CREATE and DROP commands for ENUM.
Using ENUM with ARRAY¶
The combination of ENUM and ARRAY is not directly supported by backend DBAPIs at this time. Prior to SQLAlchemy 1.3.17, a special workaround was needed in order to allow this combination to work, described below.
Changed in version 1.3.17: The combination of ENUM and ARRAY is now directly handled by SQLAlchemy’s implementation without any workarounds needed.
from sqlalchemy import TypeDecorator
from sqlalchemy.dialects.postgresql import ARRAY
class ArrayOfEnum(TypeDecorator):
impl = ARRAY
def bind_expression(self, bindvalue):
return sa.cast(bindvalue, self)
def result_processor(self, dialect, coltype):
super_rp = super(ArrayOfEnum, self).result_processor(dialect, coltype)
def handle_raw_string(value):
inner = re.match(r"^{(.*)}$", value).group(1)
return inner.split(",") if inner else []
def process(value):
if value is None:
return None
return super_rp(handle_raw_string(value))
return process
E.g.:
Table(
"mydata",
metadata,
Column("id", Integer, primary_key=True),
Column("data", ArrayOfEnum(ENUM("a", "b", "c", name="myenum"))),
)
This type is not included as a built-in type as it would be incompatible with a DBAPI that suddenly decides to support ARRAY of ENUM directly in a new version.
Using JSON/JSONB with ARRAY¶
Similar to using ENUM, prior to SQLAlchemy 1.3.17, for an ARRAY of JSON/JSONB we need to render the appropriate CAST. Current psycopg2 drivers accommodate the result set correctly without any special steps.
Changed in version 1.3.17: The combination of JSON/JSONB and ARRAY is now directly handled by SQLAlchemy’s implementation without any workarounds needed.
class CastingArray(ARRAY):
def bind_expression(self, bindvalue):
return sa.cast(bindvalue, self)
E.g.:
Table(
"mydata",
metadata,
Column("id", Integer, primary_key=True),
Column("data", CastingArray(JSONB)),
)
Range and Multirange Types¶
PostgreSQL range and multirange types are supported for the psycopg, pg8000 and asyncpg dialects; the psycopg2 dialect supports the range types only.
New in version 2.0.17: Added range and multirange support for the pg8000 dialect. pg8000 1.29.8 or greater is required.
Data values being passed to the database may be passed as string
values or by using the Range
data object.
New in version 2.0: Added the backend-agnostic Range
object used to indicate ranges. The psycopg2
-specific range classes
are no longer exposed and are only used internally by that particular
dialect.
E.g. an example of a fully typed model using the
TSRANGE
datatype:
from datetime import datetime
from sqlalchemy.dialects.postgresql import Range
from sqlalchemy.dialects.postgresql import TSRANGE
from sqlalchemy.orm import DeclarativeBase
from sqlalchemy.orm import Mapped
from sqlalchemy.orm import mapped_column
class Base(DeclarativeBase):
pass
class RoomBooking(Base):
__tablename__ = "room_booking"
id: Mapped[int] = mapped_column(primary_key=True)
room: Mapped[str]
during: Mapped[Range[datetime]] = mapped_column(TSRANGE)
To represent data for the during
column above, the Range
type is a simple dataclass that will represent the bounds of the range.
Below illustrates an INSERT of a row into the above room_booking
table:
from sqlalchemy import create_engine
from sqlalchemy.orm import Session
engine = create_engine("postgresql+psycopg://scott:tiger@pg14/dbname")
Base.metadata.create_all(engine)
with Session(engine) as session:
booking = RoomBooking(
room="101", during=Range(datetime(2013, 3, 23), datetime(2013, 3, 25))
)
session.add(booking)
session.commit()
Selecting from any range column will also return Range
objects as indicated:
from sqlalchemy import select
with Session(engine) as session:
for row in session.execute(select(RoomBooking.during)):
print(row)
The available range datatypes are as follows:
Object Name | Description |
---|---|
Represent a PostgreSQL range. |
- class sqlalchemy.dialects.postgresql.Range¶
Represent a PostgreSQL range.
E.g.:
r = Range(10, 50, bounds="()")
The calling style is similar to that of psycopg and psycopg2, in part to allow easier migration from previous SQLAlchemy versions that used these objects directly.
- Parameters:
New in version 2.0.
Members
__eq__(), adjacent_to(), contained_by(), contains(), difference(), intersection(), is_empty, isempty, lower, lower_inc, lower_inf, not_extend_left_of(), not_extend_right_of(), overlaps(), strictly_left_of(), strictly_right_of(), union(), upper, upper_inc, upper_inf
Class signature
class
sqlalchemy.dialects.postgresql.Range
(typing.Generic
)-
method
sqlalchemy.dialects.postgresql.Range.
__eq__(other: Any) → bool¶ Compare this range to the other taking into account bounds inclusivity, returning
True
if they are equal.
-
method
sqlalchemy.dialects.postgresql.Range.
adjacent_to(other: Range[_T]) → bool¶ Determine whether this range is adjacent to the other.
-
method
sqlalchemy.dialects.postgresql.Range.
contained_by(other: Range[_T]) → bool¶ Determine whether this range is a contained by other.
-
method
sqlalchemy.dialects.postgresql.Range.
contains(value: _T | Range[_T]) → bool¶ Determine whether this range contains value.
-
method
sqlalchemy.dialects.postgresql.Range.
difference(other: Range[_T]) → Range[_T]¶ Compute the difference between this range and the other.
This raises a
ValueError
exception if the two ranges are “disjunct”, that is neither adjacent nor overlapping.
-
method
sqlalchemy.dialects.postgresql.Range.
intersection(other: Range[_T]) → Range[_T]¶ Compute the intersection of this range with the other.
New in version 2.0.10.
-
attribute
sqlalchemy.dialects.postgresql.Range.
is_empty¶ A synonym for the ‘empty’ attribute.
-
attribute
sqlalchemy.dialects.postgresql.Range.
isempty¶ A synonym for the ‘empty’ attribute.
-
attribute
sqlalchemy.dialects.postgresql.Range.
lower: _T | None¶ the lower bound
-
attribute
sqlalchemy.dialects.postgresql.Range.
lower_inc¶ Return True if the lower bound is inclusive.
-
attribute
sqlalchemy.dialects.postgresql.Range.
lower_inf¶ Return True if this range is non-empty and lower bound is infinite.
-
method
sqlalchemy.dialects.postgresql.Range.
not_extend_left_of(other: Range[_T]) → bool¶ Determine whether this does not extend to the left of other.
-
method
sqlalchemy.dialects.postgresql.Range.
not_extend_right_of(other: Range[_T]) → bool¶ Determine whether this does not extend to the right of other.
-
method
sqlalchemy.dialects.postgresql.Range.
overlaps(other: Range[_T]) → bool¶ Determine whether this range overlaps with other.
-
method
sqlalchemy.dialects.postgresql.Range.
strictly_left_of(other: Range[_T]) → bool¶ Determine whether this range is completely to the left of other.
-
method
sqlalchemy.dialects.postgresql.Range.
strictly_right_of(other: Range[_T]) → bool¶ Determine whether this range is completely to the right of other.
-
method
sqlalchemy.dialects.postgresql.Range.
union(other: Range[_T]) → Range[_T]¶ Compute the union of this range with the other.
This raises a
ValueError
exception if the two ranges are “disjunct”, that is neither adjacent nor overlapping.
-
attribute
sqlalchemy.dialects.postgresql.Range.
upper: _T | None¶ the upper bound
-
attribute
sqlalchemy.dialects.postgresql.Range.
upper_inc¶ Return True if the upper bound is inclusive.
-
attribute
sqlalchemy.dialects.postgresql.Range.
upper_inf¶ Return True if this range is non-empty and the upper bound is infinite.
Multiranges¶
Multiranges are supported by PostgreSQL 14 and above. SQLAlchemy’s
multirange datatypes deal in lists of Range
types.
Multiranges are supported on the psycopg, asyncpg, and pg8000 dialects
only. The psycopg2 dialect, which is SQLAlchemy’s default postgresql
dialect, does not support multirange datatypes.
New in version 2.0: Added support for MULTIRANGE datatypes.
SQLAlchemy represents a multirange value as a list of
Range
objects.
New in version 2.0.17: Added multirange support for the pg8000 dialect. pg8000 1.29.8 or greater is required.
New in version 2.0.26: MultiRange
sequence added.
The example below illustrates use of the TSMULTIRANGE
datatype:
from datetime import datetime
from typing import List
from sqlalchemy.dialects.postgresql import Range
from sqlalchemy.dialects.postgresql import TSMULTIRANGE
from sqlalchemy.orm import DeclarativeBase
from sqlalchemy.orm import Mapped
from sqlalchemy.orm import mapped_column
class Base(DeclarativeBase):
pass
class EventCalendar(Base):
__tablename__ = "event_calendar"
id: Mapped[int] = mapped_column(primary_key=True)
event_name: Mapped[str]
added: Mapped[datetime]
in_session_periods: Mapped[List[Range[datetime]]] = mapped_column(TSMULTIRANGE)
Illustrating insertion and selecting of a record:
from sqlalchemy import create_engine
from sqlalchemy import select
from sqlalchemy.orm import Session
engine = create_engine("postgresql+psycopg://scott:tiger@pg14/test")
Base.metadata.create_all(engine)
with Session(engine) as session:
calendar = EventCalendar(
event_name="SQLAlchemy Tutorial Sessions",
in_session_periods=[
Range(datetime(2013, 3, 23), datetime(2013, 3, 25)),
Range(datetime(2013, 4, 12), datetime(2013, 4, 15)),
Range(datetime(2013, 5, 9), datetime(2013, 5, 12)),
],
)
session.add(calendar)
session.commit()
for multirange in session.scalars(select(EventCalendar.in_session_periods)):
for range_ in multirange:
print(f"Start: {range_.lower} End: {range_.upper}")
Note
In the above example, the list of Range
types
as handled by the ORM will not automatically detect in-place changes to
a particular list value; to update list values with the ORM, either re-assign
a new list to the attribute, or use the MutableList
type modifier. See the section Mutation Tracking for background.
Use of a MultiRange sequence to infer the multirange type¶
When using a multirange as a literal without specifying the type
the utility MultiRange
sequence can be used:
from sqlalchemy import literal
from sqlalchemy.dialects.postgresql import MultiRange
with Session(engine) as session:
stmt = select(EventCalendar).where(
EventCalendar.added.op("<@")(
MultiRange(
[
Range(datetime(2023, 1, 1), datetime(2013, 3, 31)),
Range(datetime(2023, 7, 1), datetime(2013, 9, 30)),
]
)
)
)
in_range = session.execute(stmt).all()
with engine.connect() as conn:
row = conn.scalar(select(literal(MultiRange([Range(2, 4)]))))
print(f"{row.lower} -> {row.upper}")
Using a simple list
instead of MultiRange
would require
manually setting the type of the literal value to the appropriate multirange type.
New in version 2.0.26: MultiRange
sequence added.
The available multirange datatypes are as follows:
Network Data Types¶
The included networking datatypes are INET
,
CIDR
, MACADDR
.
For INET
and CIDR
datatypes,
conditional support is available for these datatypes to send and retrieve
Python ipaddress
objects including ipaddress.IPv4Network
,
ipaddress.IPv6Network
, ipaddress.IPv4Address
,
ipaddress.IPv6Address
. This support is currently the default behavior of
the DBAPI itself, and varies per DBAPI. SQLAlchemy does not yet implement its
own network address conversion logic.
The psycopg and asyncpg support these datatypes fully; objects from the
ipaddress
family are returned in rows by default.The psycopg2 dialect only sends and receives strings.
The pg8000 dialect supports
ipaddress.IPv4Address
andipaddress.IPv6Address
objects for theINET
datatype, but uses strings forCIDR
types.
To normalize all the above DBAPIs to only return strings, use the
native_inet_types
parameter, passing a value of False
:
e = create_engine(
"postgresql+psycopg://scott:tiger@host/dbname", native_inet_types=False
)
With the above parameter, the psycopg
, asyncpg
and pg8000
dialects
will disable the DBAPI’s adaptation of these types and will return only strings,
matching the behavior of the older psycopg2
dialect.
The parameter may also be set to True
, where it will have the effect of
raising NotImplementedError
for those backends that don’t support, or
don’t yet fully support, conversion of rows to Python ipaddress
datatypes
(currently psycopg2 and pg8000).
New in version 2.0.18: - added the native_inet_types
parameter.
PostgreSQL Data Types¶
As with all SQLAlchemy dialects, all UPPERCASE types that are known to be
valid with PostgreSQL are importable from the top level dialect, whether
they originate from sqlalchemy.types
or from the local dialect:
from sqlalchemy.dialects.postgresql import (
ARRAY,
BIGINT,
BIT,
BOOLEAN,
BYTEA,
CHAR,
CIDR,
CITEXT,
DATE,
DATEMULTIRANGE,
DATERANGE,
DOMAIN,
DOUBLE_PRECISION,
ENUM,
FLOAT,
HSTORE,
INET,
INT4MULTIRANGE,
INT4RANGE,
INT8MULTIRANGE,
INT8RANGE,
INTEGER,
INTERVAL,
JSON,
JSONB,
JSONPATH,
MACADDR,
MACADDR8,
MONEY,
NUMERIC,
NUMMULTIRANGE,
NUMRANGE,
OID,
REAL,
REGCLASS,
REGCONFIG,
SMALLINT,
TEXT,
TIME,
TIMESTAMP,
TSMULTIRANGE,
TSQUERY,
TSRANGE,
TSTZMULTIRANGE,
TSTZRANGE,
TSVECTOR,
UUID,
VARCHAR,
)
Types which are specific to PostgreSQL, or have PostgreSQL-specific construction arguments, are as follows:
Object Name | Description |
---|---|
Base for PostgreSQL MULTIRANGE types. |
|
Base class for single and multi Range SQL types. |
|
Base for PostgreSQL RANGE types. |
|
PostgreSQL ARRAY type. |
|
Provide the PostgreSQL CITEXT type. |
|
Represent the PostgreSQL DATEMULTIRANGE type. |
|
Represent the PostgreSQL DATERANGE type. |
|
Represent the DOMAIN PostgreSQL type. |
|
PostgreSQL ENUM type. |
|
Represent the PostgreSQL HSTORE type. |
|
Represent the PostgreSQL INT4MULTIRANGE type. |
|
Represent the PostgreSQL INT4RANGE type. |
|
Represent the PostgreSQL INT8MULTIRANGE type. |
|
Represent the PostgreSQL INT8RANGE type. |
|
PostgreSQL INTERVAL type. |
|
Represent the PostgreSQL JSON type. |
|
Represent the PostgreSQL JSONB type. |
|
JSON Path Type. |
|
Provide the PostgreSQL MONEY type. |
|
Represents a multirange sequence. |
|
Represent the PostgreSQL NUMMULTIRANGE type. |
|
Represent the PostgreSQL NUMRANGE type. |
|
Provide the PostgreSQL OID type. |
|
Provide the PostgreSQL REGCLASS type. |
|
Provide the PostgreSQL REGCONFIG type. |
|
PostgreSQL TIME type. |
|
Provide the PostgreSQL TIMESTAMP type. |
|
Represent the PostgreSQL TSRANGE type. |
|
Provide the PostgreSQL TSQUERY type. |
|
Represent the PostgreSQL TSRANGE type. |
|
Represent the PostgreSQL TSTZRANGE type. |
|
Represent the PostgreSQL TSTZRANGE type. |
|
The |
- class sqlalchemy.dialects.postgresql.AbstractRange¶
Base class for single and multi Range SQL types.
Members
adjacent_to(), contained_by(), contains(), difference(), intersection(), not_extend_left_of(), not_extend_right_of(), overlaps(), strictly_left_of(), strictly_right_of(), union()
Class signature
class
sqlalchemy.dialects.postgresql.AbstractRange
(sqlalchemy.types.TypeEngine
)- class comparator_factory¶
Define comparison operations for range types.
Class signature
class
sqlalchemy.dialects.postgresql.AbstractRange.comparator_factory
(sqlalchemy.types.Comparator
)-
method
sqlalchemy.dialects.postgresql.AbstractRange.comparator_factory.
adjacent_to(other: Any) → ColumnElement[bool]¶ Boolean expression. Returns true if the range in the column is adjacent to the range in the operand.
-
method
sqlalchemy.dialects.postgresql.AbstractRange.comparator_factory.
contained_by(other: Any) → ColumnElement[bool]¶ Boolean expression. Returns true if the column is contained within the right hand operand.
-
method
sqlalchemy.dialects.postgresql.AbstractRange.comparator_factory.
contains(other: Any, **kw: Any) → ColumnElement[bool]¶ Boolean expression. Returns true if the right hand operand, which can be an element or a range, is contained within the column.
kwargs may be ignored by this operator but are required for API conformance.
-
method
sqlalchemy.dialects.postgresql.AbstractRange.comparator_factory.
difference(other: Any) → ColumnElement[bool]¶ Range expression. Returns the union of the two ranges. Will raise an exception if the resulting range is not contiguous.
-
method
sqlalchemy.dialects.postgresql.AbstractRange.comparator_factory.
intersection(other: Any) → ColumnElement[Range[_T]]¶ Range expression. Returns the intersection of the two ranges. Will raise an exception if the resulting range is not contiguous.
-
method
sqlalchemy.dialects.postgresql.AbstractRange.comparator_factory.
not_extend_left_of(other: Any) → ColumnElement[bool]¶ Boolean expression. Returns true if the range in the column does not extend left of the range in the operand.
-
method
sqlalchemy.dialects.postgresql.AbstractRange.comparator_factory.
not_extend_right_of(other: Any) → ColumnElement[bool]¶ Boolean expression. Returns true if the range in the column does not extend right of the range in the operand.
-
method
sqlalchemy.dialects.postgresql.AbstractRange.comparator_factory.
overlaps(other: Any) → ColumnElement[bool]¶ Boolean expression. Returns true if the column overlaps (has points in common with) the right hand operand.
-
method
sqlalchemy.dialects.postgresql.AbstractRange.comparator_factory.
strictly_left_of(other: Any) → ColumnElement[bool]¶ Boolean expression. Returns true if the column is strictly left of the right hand operand.
-
method
sqlalchemy.dialects.postgresql.AbstractRange.comparator_factory.
strictly_right_of(other: Any) → ColumnElement[bool]¶ Boolean expression. Returns true if the column is strictly right of the right hand operand.
-
method
sqlalchemy.dialects.postgresql.AbstractRange.comparator_factory.
union(other: Any) → ColumnElement[bool]¶ Range expression. Returns the union of the two ranges. Will raise an exception if the resulting range is not contiguous.
-
method
- class sqlalchemy.dialects.postgresql.AbstractSingleRange¶
Base for PostgreSQL RANGE types.
These are types that return a single
Range
object.See also
- class sqlalchemy.dialects.postgresql.AbstractMultiRange¶
Base for PostgreSQL MULTIRANGE types.
these are types that return a sequence of
Range
objects.Class signature
class
sqlalchemy.dialects.postgresql.AbstractMultiRange
(sqlalchemy.dialects.postgresql.ranges.AbstractRange
)
- class sqlalchemy.dialects.postgresql.ARRAY¶
PostgreSQL ARRAY type.
The
ARRAY
type is constructed in the same way as the coreARRAY
type; a member type is required, and a number of dimensions is recommended if the type is to be used for more than one dimension:from sqlalchemy.dialects import postgresql mytable = Table( "mytable", metadata, Column("data", postgresql.ARRAY(Integer, dimensions=2)), )
The
ARRAY
type provides all operations defined on the coreARRAY
type, including support for “dimensions”, indexed access, and simple matching such asComparator.any()
andComparator.all()
.ARRAY
class also provides PostgreSQL-specific methods for containment operations, includingComparator.contains()
Comparator.contained_by()
, andComparator.overlap()
, e.g.:mytable.c.data.contains([1, 2])
Indexed access is one-based by default, to match that of PostgreSQL; for zero-based indexed access, set
ARRAY.zero_indexes
.Additionally, the
ARRAY
type does not work directly in conjunction with theENUM
type. For a workaround, see the special type at Using ENUM with ARRAY.Detecting Changes in ARRAY columns when using the ORM
The
ARRAY
type, when used with the SQLAlchemy ORM, does not detect in-place mutations to the array. In order to detect these, thesqlalchemy.ext.mutable
extension must be used, using theMutableList
class:from sqlalchemy.dialects.postgresql import ARRAY from sqlalchemy.ext.mutable import MutableList class SomeOrmClass(Base): # ... data = Column(MutableList.as_mutable(ARRAY(Integer)))
This extension will allow “in-place” changes such to the array such as
.append()
to produce events which will be detected by the unit of work. Note that changes to elements inside the array, including subarrays that are mutated in place, are not detected.Alternatively, assigning a new array value to an ORM element that replaces the old one will always trigger a change event.
Members
Class signature
class
sqlalchemy.dialects.postgresql.ARRAY
(sqlalchemy.types.ARRAY
)-
method
sqlalchemy.dialects.postgresql.ARRAY.
__init__(item_type: _TypeEngineArgument[Any], as_tuple: bool = False, dimensions: int | None = None, zero_indexes: bool = False)¶ Construct an ARRAY.
E.g.:
Column("myarray", ARRAY(Integer))
Arguments are:
- Parameters:
item_type¶ – The data type of items of this array. Note that dimensionality is irrelevant here, so multi-dimensional arrays like
INTEGER[][]
, are constructed asARRAY(Integer)
, not asARRAY(ARRAY(Integer))
or such.as_tuple=False¶ – Specify whether return results should be converted to tuples from lists. DBAPIs such as psycopg2 return lists by default. When tuples are returned, the results are hashable.
dimensions¶ – if non-None, the ARRAY will assume a fixed number of dimensions. This will cause the DDL emitted for this ARRAY to include the exact number of bracket clauses
[]
, and will also optimize the performance of the type overall. Note that PG arrays are always implicitly “non-dimensioned”, meaning they can store any number of dimensions no matter how they were declared.zero_indexes=False¶ – when True, index values will be converted between Python zero-based and PostgreSQL one-based indexes, e.g. a value of one will be added to all index values before passing to the database.
- class Comparator¶
Define comparison operations for
ARRAY
.Note that these operations are in addition to those provided by the base
Comparator
class, includingComparator.any()
andComparator.all()
.Class signature
class
sqlalchemy.dialects.postgresql.ARRAY.Comparator
(sqlalchemy.types.Comparator
)-
method
sqlalchemy.dialects.postgresql.ARRAY.Comparator.
contains(other, **kwargs)¶ Boolean expression. Test if elements are a superset of the elements of the argument array expression.
kwargs may be ignored by this operator but are required for API conformance.
-
method
sqlalchemy.dialects.postgresql.ARRAY.Comparator.
contained_by(other)¶ Boolean expression. Test if elements are a proper subset of the elements of the argument array expression.
-
method
sqlalchemy.dialects.postgresql.ARRAY.Comparator.
overlap(other)¶ Boolean expression. Test if array has elements in common with an argument array expression.
-
method
-
method
- class sqlalchemy.dialects.postgresql.BIT¶
Class signature
class
sqlalchemy.dialects.postgresql.BIT
(sqlalchemy.types.TypeEngine
)
- class sqlalchemy.dialects.postgresql.BYTEA¶
Members
Class signature
class
sqlalchemy.dialects.postgresql.BYTEA
(sqlalchemy.types.LargeBinary
)-
method
sqlalchemy.dialects.postgresql.BYTEA.
__init__(length: int | None = None)¶ inherited from the
sqlalchemy.types.LargeBinary.__init__
method ofLargeBinary
Construct a LargeBinary type.
- Parameters:
length¶ – optional, a length for the column for use in DDL statements, for those binary types that accept a length, such as the MySQL BLOB type.
-
method
- class sqlalchemy.dialects.postgresql.CIDR¶
Class signature
class
sqlalchemy.dialects.postgresql.CIDR
(sqlalchemy.types.TypeEngine
)
- class sqlalchemy.dialects.postgresql.CITEXT¶
Provide the PostgreSQL CITEXT type.
New in version 2.0.7.
Members
Class signature
class
sqlalchemy.dialects.postgresql.CITEXT
(sqlalchemy.types.TEXT
)-
method
sqlalchemy.dialects.postgresql.CITEXT.
__init__(length: int | None = None, collation: str | None = None)¶ inherited from the
sqlalchemy.types.String.__init__
method ofString
Create a string-holding type.
- Parameters:
length¶ – optional, a length for the column for use in DDL and CAST expressions. May be safely omitted if no
CREATE TABLE
will be issued. Certain databases may require alength
for use in DDL, and will raise an exception when theCREATE TABLE
DDL is issued if aVARCHAR
with no length is included. Whether the value is interpreted as bytes or characters is database specific.collation¶ –
Optional, a column-level collation for use in DDL and CAST expressions. Renders using the COLLATE keyword supported by SQLite, MySQL, and PostgreSQL. E.g.:
>>> from sqlalchemy import cast, select, String >>> print(select(cast("some string", String(collation="utf8"))))
SELECT CAST(:param_1 AS VARCHAR COLLATE utf8) AS anon_1Note
In most cases, the
Unicode
orUnicodeText
datatypes should be used for aColumn
that expects to store non-ascii data. These datatypes will ensure that the correct types are used on the database.
-
method
- class sqlalchemy.dialects.postgresql.DOMAIN¶
Represent the DOMAIN PostgreSQL type.
A domain is essentially a data type with optional constraints that restrict the allowed set of values. E.g.:
PositiveInt = DOMAIN("pos_int", Integer, check="VALUE > 0", not_null=True) UsPostalCode = DOMAIN( "us_postal_code", Text, check="VALUE ~ '^\d{5}$' OR VALUE ~ '^\d{5}-\d{4}$'", )
See the PostgreSQL documentation for additional details
New in version 2.0.
Members
Class signature
class
sqlalchemy.dialects.postgresql.DOMAIN
(sqlalchemy.dialects.postgresql.named_types.NamedType
,sqlalchemy.types.SchemaType
)-
method
sqlalchemy.dialects.postgresql.DOMAIN.
__init__(name: str, data_type: _TypeEngineArgument[Any], *, collation: str | None = None, default: elements.TextClause | str | None = None, constraint_name: str | None = None, not_null: bool | None = None, check: elements.TextClause | str | None = None, create_type: bool = True, **kw: Any)¶ Construct a DOMAIN.
- Parameters:
name¶ – the name of the domain
data_type¶ – The underlying data type of the domain. This can include array specifiers.
collation¶ – An optional collation for the domain. If no collation is specified, the underlying data type’s default collation is used. The underlying type must be collatable if
collation
is specified.default¶ – The DEFAULT clause specifies a default value for columns of the domain data type. The default should be a string or a
text()
value. If no default value is specified, then the default value is the null value.constraint_name¶ – An optional name for a constraint. If not specified, the backend generates a name.
not_null¶ – Values of this domain are prevented from being null. By default domain are allowed to be null. If not specified no nullability clause will be emitted.
check¶ – CHECK clause specify integrity constraint or test which values of the domain must satisfy. A constraint must be an expression producing a Boolean result that can use the key word VALUE to refer to the value being tested. Differently from PostgreSQL, only a single check clause is currently allowed in SQLAlchemy.
schema¶ – optional schema name
metadata¶ – optional
MetaData
object which thisDOMAIN
will be directly associatedcreate_type¶ – Defaults to True. Indicates that
CREATE TYPE
should be emitted, after optionally checking for the presence of the type, when the parent table is being created; and additionally thatDROP TYPE
is called when the table is dropped.
-
method
sqlalchemy.dialects.postgresql.DOMAIN.
create(bind, checkfirst=True, **kw)¶ inherited from the
NamedType.create()
method ofNamedType
Emit
CREATE
DDL for this type.- Parameters:
bind¶ – a connectable
Engine
,Connection
, or similar object to emit SQL.checkfirst¶ – if
True
, a query against the PG catalog will be first performed to see if the type does not exist already before creating.
-
method
sqlalchemy.dialects.postgresql.DOMAIN.
drop(bind, checkfirst=True, **kw)¶ inherited from the
NamedType.drop()
method ofNamedType
Emit
DROP
DDL for this type.- Parameters:
bind¶ – a connectable
Engine
,Connection
, or similar object to emit SQL.checkfirst¶ – if
True
, a query against the PG catalog will be first performed to see if the type actually exists before dropping.
-
method
- class sqlalchemy.dialects.postgresql.DOUBLE_PRECISION
The SQL DOUBLE PRECISION type.
New in version 2.0.
See also
Double
- documentation for the base type.Class signature
class
sqlalchemy.dialects.postgresql.DOUBLE_PRECISION
(sqlalchemy.types.Double
)-
method
sqlalchemy.dialects.postgresql.DOUBLE_PRECISION.
__init__(precision: int | None = None, asdecimal: bool = False, decimal_return_scale: int | None = None) inherited from the
sqlalchemy.types.Float.__init__
method ofFloat
Construct a Float.
- Parameters:
precision¶ –
the numeric precision for use in DDL
CREATE TABLE
. Backends should attempt to ensure this precision indicates a number of digits for the genericFloat
datatype.Note
For the Oracle Database backend, the
Float.precision
parameter is not accepted when rendering DDL, as Oracle Database does not support float precision specified as a number of decimal places. Instead, use the Oracle Database-specificFLOAT
datatype and specify theFLOAT.binary_precision
parameter. This is new in version 2.0 of SQLAlchemy.To create a database agnostic
Float
that separately specifies binary precision for Oracle Database, useTypeEngine.with_variant()
as follows:from sqlalchemy import Column from sqlalchemy import Float from sqlalchemy.dialects import oracle Column( "float_data", Float(5).with_variant(oracle.FLOAT(binary_precision=16), "oracle"), )
asdecimal¶ – the same flag as that of
Numeric
, but defaults toFalse
. Note that setting this flag toTrue
results in floating point conversion.decimal_return_scale¶ – Default scale to use when converting from floats to Python decimals. Floating point values will typically be much longer due to decimal inaccuracy, and most floating point database types don’t have a notion of “scale”, so by default the float type looks for the first ten decimal places when converting. Specifying this value will override that length. Note that the MySQL float types, which do include “scale”, will use “scale” as the default for decimal_return_scale, if not otherwise specified.
-
method
- class sqlalchemy.dialects.postgresql.ENUM¶
PostgreSQL ENUM type.
This is a subclass of
Enum
which includes support for PG’sCREATE TYPE
andDROP TYPE
.When the builtin type
Enum
is used and theEnum.native_enum
flag is left at its default of True, the PostgreSQL backend will use aENUM
type as the implementation, so the special create/drop rules will be used.The create/drop behavior of ENUM is necessarily intricate, due to the awkward relationship the ENUM type has in relationship to the parent table, in that it may be “owned” by just a single table, or may be shared among many tables.
When using
Enum
orENUM
in an “inline” fashion, theCREATE TYPE
andDROP TYPE
is emitted corresponding to when theTable.create()
andTable.drop()
methods are called:table = Table( "sometable", metadata, Column("some_enum", ENUM("a", "b", "c", name="myenum")), ) table.create(engine) # will emit CREATE ENUM and CREATE TABLE table.drop(engine) # will emit DROP TABLE and DROP ENUM
To use a common enumerated type between multiple tables, the best practice is to declare the
Enum
orENUM
independently, and associate it with theMetaData
object itself:my_enum = ENUM("a", "b", "c", name="myenum", metadata=metadata) t1 = Table("sometable_one", metadata, Column("some_enum", myenum)) t2 = Table("sometable_two", metadata, Column("some_enum", myenum))
When this pattern is used, care must still be taken at the level of individual table creates. Emitting CREATE TABLE without also specifying
checkfirst=True
will still cause issues:t1.create(engine) # will fail: no such type 'myenum'
If we specify
checkfirst=True
, the individual table-level create operation will check for theENUM
and create if not exists:# will check if enum exists, and emit CREATE TYPE if not t1.create(engine, checkfirst=True)
When using a metadata-level ENUM type, the type will always be created and dropped if either the metadata-wide create/drop is called:
metadata.create_all(engine) # will emit CREATE TYPE metadata.drop_all(engine) # will emit DROP TYPE
The type can also be created and dropped directly:
my_enum.create(engine) my_enum.drop(engine)
Members
Class signature
class
sqlalchemy.dialects.postgresql.ENUM
(sqlalchemy.dialects.postgresql.named_types.NamedType
,sqlalchemy.types.NativeForEmulated
,sqlalchemy.types.Enum
)-
method
sqlalchemy.dialects.postgresql.ENUM.
__init__(*enums, name: str | _NoArg | None = _NoArg.NO_ARG, create_type: bool = True, **kw)¶ Construct an
ENUM
.Arguments are the same as that of
Enum
, but also including the following parameters.- Parameters:
create_type¶ – Defaults to True. Indicates that
CREATE TYPE
should be emitted, after optionally checking for the presence of the type, when the parent table is being created; and additionally thatDROP TYPE
is called when the table is dropped. WhenFalse
, no check will be performed and noCREATE TYPE
orDROP TYPE
is emitted, unlessENUM.create()
orENUM.drop()
are called directly. Setting toFalse
is helpful when invoking a creation scheme to a SQL file without access to the actual database - theENUM.create()
andENUM.drop()
methods can be used to emit SQL to a target bind.
-
method
sqlalchemy.dialects.postgresql.ENUM.
create(bind=None, checkfirst=True)¶ Emit
CREATE TYPE
for thisENUM
.If the underlying dialect does not support PostgreSQL CREATE TYPE, no action is taken.
- Parameters:
bind¶ – a connectable
Engine
,Connection
, or similar object to emit SQL.checkfirst¶ – if
True
, a query against the PG catalog will be first performed to see if the type does not exist already before creating.
-
method
sqlalchemy.dialects.postgresql.ENUM.
drop(bind=None, checkfirst=True)¶ Emit
DROP TYPE
for thisENUM
.If the underlying dialect does not support PostgreSQL DROP TYPE, no action is taken.
- Parameters:
bind¶ – a connectable
Engine
,Connection
, or similar object to emit SQL.checkfirst¶ – if
True
, a query against the PG catalog will be first performed to see if the type actually exists before dropping.
-
method
- class sqlalchemy.dialects.postgresql.HSTORE¶
Represent the PostgreSQL HSTORE type.
The
HSTORE
type stores dictionaries containing strings, e.g.:data_table = Table( "data_table", metadata, Column("id", Integer, primary_key=True), Column("data", HSTORE), ) with engine.connect() as conn: conn.execute( data_table.insert(), data={"key1": "value1", "key2": "value2"} )
HSTORE
provides for a wide range of operations, including:Index operations:
data_table.c.data["some key"] == "some value"
Containment operations:
data_table.c.data.has_key("some key") data_table.c.data.has_all(["one", "two", "three"])
Concatenation:
data_table.c.data + {"k1": "v1"}
For a full list of special methods see
comparator_factory
.Detecting Changes in HSTORE columns when using the ORM
For usage with the SQLAlchemy ORM, it may be desirable to combine the usage of
HSTORE
withMutableDict
dictionary now part of thesqlalchemy.ext.mutable
extension. This extension will allow “in-place” changes to the dictionary, e.g. addition of new keys or replacement/removal of existing keys to/from the current dictionary, to produce events which will be detected by the unit of work:from sqlalchemy.ext.mutable import MutableDict class MyClass(Base): __tablename__ = "data_table" id = Column(Integer, primary_key=True) data = Column(MutableDict.as_mutable(HSTORE)) my_object = session.query(MyClass).one() # in-place mutation, requires Mutable extension # in order for the ORM to detect my_object.data["some_key"] = "some value" session.commit()
When the
sqlalchemy.ext.mutable
extension is not used, the ORM will not be alerted to any changes to the contents of an existing dictionary, unless that dictionary value is re-assigned to the HSTORE-attribute itself, thus generating a change event.See also
hstore
- render the PostgreSQLhstore()
function.Members
array(), contained_by(), contains(), defined(), delete(), has_all(), has_any(), has_key(), keys(), matrix(), slice(), vals(), __init__(), bind_processor(), comparator_factory, hashable, result_processor()
Class signature
class
sqlalchemy.dialects.postgresql.HSTORE
(sqlalchemy.types.Indexable
,sqlalchemy.types.Concatenable
,sqlalchemy.types.TypeEngine
)- class Comparator¶
Define comparison operations for
HSTORE
.Class signature
class
sqlalchemy.dialects.postgresql.HSTORE.Comparator
(sqlalchemy.types.Comparator
,sqlalchemy.types.Comparator
)-
method
sqlalchemy.dialects.postgresql.HSTORE.Comparator.
array()¶ Text array expression. Returns array of alternating keys and values.
-
method
sqlalchemy.dialects.postgresql.HSTORE.Comparator.
contained_by(other)¶ Boolean expression. Test if keys are a proper subset of the keys of the argument jsonb expression.
-
method
sqlalchemy.dialects.postgresql.HSTORE.Comparator.
contains(other, **kwargs)¶ Boolean expression. Test if keys (or array) are a superset of/contained the keys of the argument jsonb expression.
kwargs may be ignored by this operator but are required for API conformance.
-
method
sqlalchemy.dialects.postgresql.HSTORE.Comparator.
defined(key)¶ Boolean expression. Test for presence of a non-NULL value for the key. Note that the key may be a SQLA expression.
-
method
sqlalchemy.dialects.postgresql.HSTORE.Comparator.
delete(key)¶ HStore expression. Returns the contents of this hstore with the given key deleted. Note that the key may be a SQLA expression.
-
method
sqlalchemy.dialects.postgresql.HSTORE.Comparator.
has_all(other)¶ Boolean expression. Test for presence of all keys in jsonb
-
method
sqlalchemy.dialects.postgresql.HSTORE.Comparator.
has_any(other)¶ Boolean expression. Test for presence of any key in jsonb
-
method
sqlalchemy.dialects.postgresql.HSTORE.Comparator.
has_key(other)¶ Boolean expression. Test for presence of a key. Note that the key may be a SQLA expression.
-
method
sqlalchemy.dialects.postgresql.HSTORE.Comparator.
keys()¶ Text array expression. Returns array of keys.
-
method
sqlalchemy.dialects.postgresql.HSTORE.Comparator.
matrix()¶ Text array expression. Returns array of [key, value] pairs.
-
method
sqlalchemy.dialects.postgresql.HSTORE.Comparator.
slice(array)¶ HStore expression. Returns a subset of an hstore defined by array of keys.
-
method
sqlalchemy.dialects.postgresql.HSTORE.Comparator.
vals()¶ Text array expression. Returns array of values.
-
method
-
method
sqlalchemy.dialects.postgresql.HSTORE.
__init__(text_type=None)¶ Construct a new
HSTORE
.
-
method
sqlalchemy.dialects.postgresql.HSTORE.
bind_processor(dialect)¶ Return a conversion function for processing bind values.
Returns a callable which will receive a bind parameter value as the sole positional argument and will return a value to send to the DB-API.
If processing is not necessary, the method should return
None
.Note
This method is only called relative to a dialect specific type object, which is often private to a dialect in use and is not the same type object as the public facing one, which means it’s not feasible to subclass a
TypeEngine
class in order to provide an alternateTypeEngine.bind_processor()
method, unless subclassing theUserDefinedType
class explicitly.To provide alternate behavior for
TypeEngine.bind_processor()
, implement aTypeDecorator
class and provide an implementation ofTypeDecorator.process_bind_param()
.See also
- Parameters:
dialect¶ – Dialect instance in use.
-
attribute
sqlalchemy.dialects.postgresql.HSTORE.
comparator_factory¶ alias of
Comparator
-
attribute
sqlalchemy.dialects.postgresql.HSTORE.
hashable = False¶ Flag, if False, means values from this type aren’t hashable.
Used by the ORM when uniquing result lists.
-
method
sqlalchemy.dialects.postgresql.HSTORE.
result_processor(dialect, coltype)¶ Return a conversion function for processing result row values.
Returns a callable which will receive a result row column value as the sole positional argument and will return a value to return to the user.
If processing is not necessary, the method should return
None
.Note
This method is only called relative to a dialect specific type object, which is often private to a dialect in use and is not the same type object as the public facing one, which means it’s not feasible to subclass a
TypeEngine
class in order to provide an alternateTypeEngine.result_processor()
method, unless subclassing theUserDefinedType
class explicitly.To provide alternate behavior for
TypeEngine.result_processor()
, implement aTypeDecorator
class and provide an implementation ofTypeDecorator.process_result_value()
.See also
- class sqlalchemy.dialects.postgresql.INET¶
Class signature
class
sqlalchemy.dialects.postgresql.INET
(sqlalchemy.types.TypeEngine
)
- class sqlalchemy.dialects.postgresql.INTERVAL¶
PostgreSQL INTERVAL type.
Members
Class signature
class
sqlalchemy.dialects.postgresql.INTERVAL
(sqlalchemy.types.NativeForEmulated
,sqlalchemy.types._AbstractInterval
)-
method
sqlalchemy.dialects.postgresql.INTERVAL.
__init__(precision: int | None = None, fields: str | None = None) → None¶ Construct an INTERVAL.
-
method
- class sqlalchemy.dialects.postgresql.JSON¶
Represent the PostgreSQL JSON type.
JSON
is used automatically whenever the baseJSON
datatype is used against a PostgreSQL backend, however baseJSON
datatype does not provide Python accessors for PostgreSQL-specific comparison methods such asComparator.astext()
; additionally, to use PostgreSQLJSONB
, theJSONB
datatype should be used explicitly.See also
JSON
- main documentation for the generic cross-platform JSON datatype.The operators provided by the PostgreSQL version of
JSON
include:Index operations (the
->
operator):data_table.c.data["some key"] data_table.c.data[5]
Index operations returning text (the
->>
operator):data_table.c.data["some key"].astext == "some value"
Note that equivalent functionality is available via the
Comparator.as_string
accessor.Index operations with CAST (equivalent to
CAST(col ->> ['some key'] AS <type>)
):data_table.c.data["some key"].astext.cast(Integer) == 5
Note that equivalent functionality is available via the
Comparator.as_integer
and similar accessors.Path index operations (the
#>
operator):data_table.c.data[("key_1", "key_2", 5, ..., "key_n")]
Path index operations returning text (the
#>>
operator):data_table.c.data[ ("key_1", "key_2", 5, ..., "key_n") ].astext == "some value"
Index operations return an expression object whose type defaults to
JSON
by default, so that further JSON-oriented instructions may be called upon the result type.Custom serializers and deserializers are specified at the dialect level, that is using
create_engine()
. The reason for this is that when using psycopg2, the DBAPI only allows serializers at the per-cursor or per-connection level. E.g.:engine = create_engine( "postgresql+psycopg2://scott:tiger@localhost/test", json_serializer=my_serialize_fn, json_deserializer=my_deserialize_fn, )
When using the psycopg2 dialect, the json_deserializer is registered against the database using
psycopg2.extras.register_default_json
.Members
Class signature
class
sqlalchemy.dialects.postgresql.JSON
(sqlalchemy.types.JSON
)- class Comparator¶
Define comparison operations for
JSON
.Class signature
class
sqlalchemy.dialects.postgresql.JSON.Comparator
(sqlalchemy.types.Comparator
)-
attribute
sqlalchemy.dialects.postgresql.JSON.Comparator.
astext¶ On an indexed expression, use the “astext” (e.g. “->>”) conversion when rendered in SQL.
E.g.:
select(data_table.c.data["some key"].astext)
See also
-
attribute
-
method
sqlalchemy.dialects.postgresql.JSON.
__init__(none_as_null=False, astext_type=None)¶ Construct a
JSON
type.- Parameters:
none_as_null¶ –
if True, persist the value
None
as a SQL NULL value, not the JSON encoding ofnull
. Note that when this flag is False, thenull()
construct can still be used to persist a NULL value:from sqlalchemy import null conn.execute(table.insert(), {"data": null()})
See also
astext_type¶ – the type to use for the
Comparator.astext
accessor on indexed attributes. Defaults toText
.
-
attribute
sqlalchemy.dialects.postgresql.JSON.
comparator_factory¶ alias of
Comparator
-
attribute
sqlalchemy.dialects.postgresql.JSON.
render_bind_cast = True¶ Render bind casts for
BindTyping.RENDER_CASTS
mode.If True, this type (usually a dialect level impl type) signals to the compiler that a cast should be rendered around a bound parameter for this type.
New in version 2.0.
See also
- class sqlalchemy.dialects.postgresql.JSONB¶
Represent the PostgreSQL JSONB type.
The
JSONB
type stores arbitrary JSONB format data, e.g.:data_table = Table( "data_table", metadata, Column("id", Integer, primary_key=True), Column("data", JSONB), ) with engine.connect() as conn: conn.execute( data_table.insert(), data={"key1": "value1", "key2": "value2"} )
The
JSONB
type includes all operations provided byJSON
, including the same behaviors for indexing operations. It also adds additional operators specific to JSONB, includingComparator.has_key()
,Comparator.has_all()
,Comparator.has_any()
,Comparator.contains()
,Comparator.contained_by()
,Comparator.delete_path()
,Comparator.path_exists()
andComparator.path_match()
.Like the
JSON
type, theJSONB
type does not detect in-place changes when used with the ORM, unless thesqlalchemy.ext.mutable
extension is used.Custom serializers and deserializers are shared with the
JSON
class, using thejson_serializer
andjson_deserializer
keyword arguments. These must be specified at the dialect level usingcreate_engine()
. When using psycopg2, the serializers are associated with the jsonb type usingpsycopg2.extras.register_default_jsonb
on a per-connection basis, in the same way thatpsycopg2.extras.register_default_json
is used to register these handlers with the json type.See also
Members
contained_by(), contains(), delete_path(), has_all(), has_any(), has_key(), path_exists(), path_match(), comparator_factory
Class signature
class
sqlalchemy.dialects.postgresql.JSONB
(sqlalchemy.dialects.postgresql.json.JSON
)- class Comparator¶
Define comparison operations for
JSON
.Class signature
class
sqlalchemy.dialects.postgresql.JSONB.Comparator
(sqlalchemy.dialects.postgresql.json.Comparator
)-
method
sqlalchemy.dialects.postgresql.JSONB.Comparator.
contained_by(other)¶ Boolean expression. Test if keys are a proper subset of the keys of the argument jsonb expression (equivalent of the
<@
operator).
-
method
sqlalchemy.dialects.postgresql.JSONB.Comparator.
contains(other, **kwargs)¶ Boolean expression. Test if keys (or array) are a superset of/contained the keys of the argument jsonb expression (equivalent of the
@>
operator).kwargs may be ignored by this operator but are required for API conformance.
-
method
sqlalchemy.dialects.postgresql.JSONB.Comparator.
delete_path(array)¶ JSONB expression. Deletes field or array element specified in the argument array (equivalent of the
#-
operator).The input may be a list of strings that will be coerced to an
ARRAY
or an instance of_postgres.array()
.New in version 2.0.
-
method
sqlalchemy.dialects.postgresql.JSONB.Comparator.
has_all(other)¶ Boolean expression. Test for presence of all keys in jsonb (equivalent of the
?&
operator)
-
method
sqlalchemy.dialects.postgresql.JSONB.Comparator.
has_any(other)¶ Boolean expression. Test for presence of any key in jsonb (equivalent of the
?|
operator)
-
method
sqlalchemy.dialects.postgresql.JSONB.Comparator.
has_key(other)¶ Boolean expression. Test for presence of a key (equivalent of the
?
operator). Note that the key may be a SQLA expression.
-
method
sqlalchemy.dialects.postgresql.JSONB.Comparator.
path_exists(other)¶ Boolean expression. Test for presence of item given by the argument JSONPath expression (equivalent of the
@?
operator).New in version 2.0.
-
method
sqlalchemy.dialects.postgresql.JSONB.Comparator.
path_match(other)¶ Boolean expression. Test if JSONPath predicate given by the argument JSONPath expression matches (equivalent of the
@@
operator).Only the first item of the result is taken into account.
New in version 2.0.
-
method
-
attribute
sqlalchemy.dialects.postgresql.JSONB.
comparator_factory¶ alias of
Comparator
- class sqlalchemy.dialects.postgresql.JSONPATH¶
JSON Path Type.
This is usually required to cast literal values to json path when using json search like function, such as
jsonb_path_query_array
orjsonb_path_exists
:stmt = sa.select( sa.func.jsonb_path_query_array( table.c.jsonb_col, cast("$.address.id", JSONPATH) ) )
Class signature
class
sqlalchemy.dialects.postgresql.JSONPATH
(sqlalchemy.dialects.postgresql.json.JSONPathType
)
- class sqlalchemy.dialects.postgresql.MACADDR¶
Class signature
class
sqlalchemy.dialects.postgresql.MACADDR
(sqlalchemy.types.TypeEngine
)
- class sqlalchemy.dialects.postgresql.MACADDR8¶
Class signature
class
sqlalchemy.dialects.postgresql.MACADDR8
(sqlalchemy.types.TypeEngine
)
- class sqlalchemy.dialects.postgresql.MONEY¶
Provide the PostgreSQL MONEY type.
Depending on driver, result rows using this type may return a string value which includes currency symbols.
For this reason, it may be preferable to provide conversion to a numerically-based currency datatype using
TypeDecorator
:import re import decimal from sqlalchemy import Dialect from sqlalchemy import TypeDecorator class NumericMoney(TypeDecorator): impl = MONEY def process_result_value(self, value: Any, dialect: Dialect) -> None: if value is not None: # adjust this for the currency and numeric m = re.match(r"\$([\d.]+)", value) if m: value = decimal.Decimal(m.group(1)) return value
Alternatively, the conversion may be applied as a CAST using the
TypeDecorator.column_expression()
method as follows:import decimal from sqlalchemy import cast from sqlalchemy import TypeDecorator class NumericMoney(TypeDecorator): impl = MONEY def column_expression(self, column: Any): return cast(column, Numeric())
New in version 1.2.
Class signature
class
sqlalchemy.dialects.postgresql.MONEY
(sqlalchemy.types.TypeEngine
)
- class sqlalchemy.dialects.postgresql.OID¶
Provide the PostgreSQL OID type.
Class signature
class
sqlalchemy.dialects.postgresql.OID
(sqlalchemy.types.TypeEngine
)
- class sqlalchemy.dialects.postgresql.REAL
The SQL REAL type.
See also
Float
- documentation for the base type.Class signature
class
sqlalchemy.dialects.postgresql.REAL
(sqlalchemy.types.Float
)-
method
sqlalchemy.dialects.postgresql.REAL.
__init__(precision: int | None = None, asdecimal: bool = False, decimal_return_scale: int | None = None) inherited from the
sqlalchemy.types.Float.__init__
method ofFloat
Construct a Float.
- Parameters:
precision¶ –
the numeric precision for use in DDL
CREATE TABLE
. Backends should attempt to ensure this precision indicates a number of digits for the genericFloat
datatype.Note
For the Oracle Database backend, the
Float.precision
parameter is not accepted when rendering DDL, as Oracle Database does not support float precision specified as a number of decimal places. Instead, use the Oracle Database-specificFLOAT
datatype and specify theFLOAT.binary_precision
parameter. This is new in version 2.0 of SQLAlchemy.To create a database agnostic
Float
that separately specifies binary precision for Oracle Database, useTypeEngine.with_variant()
as follows:from sqlalchemy import Column from sqlalchemy import Float from sqlalchemy.dialects import oracle Column( "float_data", Float(5).with_variant(oracle.FLOAT(binary_precision=16), "oracle"), )
asdecimal¶ – the same flag as that of
Numeric
, but defaults toFalse
. Note that setting this flag toTrue
results in floating point conversion.decimal_return_scale¶ – Default scale to use when converting from floats to Python decimals. Floating point values will typically be much longer due to decimal inaccuracy, and most floating point database types don’t have a notion of “scale”, so by default the float type looks for the first ten decimal places when converting. Specifying this value will override that length. Note that the MySQL float types, which do include “scale”, will use “scale” as the default for decimal_return_scale, if not otherwise specified.
-
method
- class sqlalchemy.dialects.postgresql.REGCONFIG¶
Provide the PostgreSQL REGCONFIG type.
New in version 2.0.0rc1.
Class signature
class
sqlalchemy.dialects.postgresql.REGCONFIG
(sqlalchemy.types.TypeEngine
)
- class sqlalchemy.dialects.postgresql.REGCLASS¶
Provide the PostgreSQL REGCLASS type.
New in version 1.2.7.
Class signature
class
sqlalchemy.dialects.postgresql.REGCLASS
(sqlalchemy.types.TypeEngine
)
- class sqlalchemy.dialects.postgresql.TIMESTAMP¶
Provide the PostgreSQL TIMESTAMP type.
Members
Class signature
class
sqlalchemy.dialects.postgresql.TIMESTAMP
(sqlalchemy.types.TIMESTAMP
)-
method
sqlalchemy.dialects.postgresql.TIMESTAMP.
__init__(timezone: bool = False, precision: int | None = None) → None¶ Construct a TIMESTAMP.
-
method
- class sqlalchemy.dialects.postgresql.TIME¶
PostgreSQL TIME type.
Members
Class signature
class
sqlalchemy.dialects.postgresql.TIME
(sqlalchemy.types.TIME
)-
method
sqlalchemy.dialects.postgresql.TIME.
__init__(timezone: bool = False, precision: int | None = None) → None¶ Construct a TIME.
-
method
- class sqlalchemy.dialects.postgresql.TSQUERY¶
Provide the PostgreSQL TSQUERY type.
New in version 2.0.0rc1.
Class signature
class
sqlalchemy.dialects.postgresql.TSQUERY
(sqlalchemy.types.TypeEngine
)
- class sqlalchemy.dialects.postgresql.TSVECTOR¶
The
TSVECTOR
type implements the PostgreSQL text search type TSVECTOR.It can be used to do full text queries on natural language documents.
See also
Class signature
class
sqlalchemy.dialects.postgresql.TSVECTOR
(sqlalchemy.types.TypeEngine
)
- class sqlalchemy.dialects.postgresql.UUID
Represent the SQL UUID type.
This is the SQL-native form of the
Uuid
database agnostic datatype, and is backwards compatible with the previous PostgreSQL-only version ofUUID
.The
UUID
datatype only works on databases that have a SQL datatype namedUUID
. It will not function for backends which don’t have this exact-named type, including SQL Server. For backend-agnostic UUID values with native support, including for SQL Server’sUNIQUEIDENTIFIER
datatype, use theUuid
datatype.New in version 2.0.
See also
Class signature
class
sqlalchemy.dialects.postgresql.UUID
(sqlalchemy.types.Uuid
,sqlalchemy.types.NativeForEmulated
)-
method
sqlalchemy.dialects.postgresql.UUID.
__init__(as_uuid: bool = True) Construct a
UUID
type.- Parameters:
as_uuid=True¶ –
if True, values will be interpreted as Python uuid objects, converting to/from string via the DBAPI.
-
method
- class sqlalchemy.dialects.postgresql.INT4RANGE¶
Represent the PostgreSQL INT4RANGE type.
Class signature
class
sqlalchemy.dialects.postgresql.INT4RANGE
(sqlalchemy.dialects.postgresql.ranges.AbstractSingleRange
)
- class sqlalchemy.dialects.postgresql.INT8RANGE¶
Represent the PostgreSQL INT8RANGE type.
Class signature
class
sqlalchemy.dialects.postgresql.INT8RANGE
(sqlalchemy.dialects.postgresql.ranges.AbstractSingleRange
)
- class sqlalchemy.dialects.postgresql.NUMRANGE¶
Represent the PostgreSQL NUMRANGE type.
Class signature
class
sqlalchemy.dialects.postgresql.NUMRANGE
(sqlalchemy.dialects.postgresql.ranges.AbstractSingleRange
)
- class sqlalchemy.dialects.postgresql.DATERANGE¶
Represent the PostgreSQL DATERANGE type.
Class signature
class
sqlalchemy.dialects.postgresql.DATERANGE
(sqlalchemy.dialects.postgresql.ranges.AbstractSingleRange
)
- class sqlalchemy.dialects.postgresql.TSRANGE¶
Represent the PostgreSQL TSRANGE type.
Class signature
class
sqlalchemy.dialects.postgresql.TSRANGE
(sqlalchemy.dialects.postgresql.ranges.AbstractSingleRange
)
- class sqlalchemy.dialects.postgresql.TSTZRANGE¶
Represent the PostgreSQL TSTZRANGE type.
Class signature
class
sqlalchemy.dialects.postgresql.TSTZRANGE
(sqlalchemy.dialects.postgresql.ranges.AbstractSingleRange
)
- class sqlalchemy.dialects.postgresql.INT4MULTIRANGE¶
Represent the PostgreSQL INT4MULTIRANGE type.
- class sqlalchemy.dialects.postgresql.INT8MULTIRANGE¶
Represent the PostgreSQL INT8MULTIRANGE type.
- class sqlalchemy.dialects.postgresql.NUMMULTIRANGE¶
Represent the PostgreSQL NUMMULTIRANGE type.
Class signature
class
sqlalchemy.dialects.postgresql.NUMMULTIRANGE
(sqlalchemy.dialects.postgresql.ranges.AbstractMultiRange
)
- class sqlalchemy.dialects.postgresql.DATEMULTIRANGE¶
Represent the PostgreSQL DATEMULTIRANGE type.
- class sqlalchemy.dialects.postgresql.TSMULTIRANGE¶
Represent the PostgreSQL TSRANGE type.
Class signature
class
sqlalchemy.dialects.postgresql.TSMULTIRANGE
(sqlalchemy.dialects.postgresql.ranges.AbstractMultiRange
)
- class sqlalchemy.dialects.postgresql.TSTZMULTIRANGE¶
Represent the PostgreSQL TSTZRANGE type.
- class sqlalchemy.dialects.postgresql.MultiRange¶
Represents a multirange sequence.
This list subclass is an utility to allow automatic type inference of the proper multi-range SQL type depending on the single range values. This is useful when operating on literal multi-ranges:
import sqlalchemy as sa from sqlalchemy.dialects.postgresql import MultiRange, Range value = literal(MultiRange([Range(2, 4)])) select(tbl).where(tbl.c.value.op("@")(MultiRange([Range(-3, 7)])))
New in version 2.0.26.
Class signature
class
sqlalchemy.dialects.postgresql.MultiRange
(builtins.list
,typing.Generic
)
PostgreSQL SQL Elements and Functions¶
Object Name | Description |
---|---|
Represent a PostgreSQL aggregate order by expression. |
|
All(other, arrexpr[, operator]) |
A synonym for the ARRAY-level |
Any(other, arrexpr[, operator]) |
A synonym for the ARRAY-level |
A PostgreSQL ARRAY literal. |
|
array_agg(*arg, **kw) |
PostgreSQL-specific form of |
Construct an hstore value within a SQL expression using the
PostgreSQL |
|
The PostgreSQL |
|
The PostgreSQL |
|
The PostgreSQL |
|
The PostgreSQL |
|
The PostgreSQL |
|
The PostgreSQL |
- class sqlalchemy.dialects.postgresql.aggregate_order_by¶
Represent a PostgreSQL aggregate order by expression.
E.g.:
from sqlalchemy.dialects.postgresql import aggregate_order_by expr = func.array_agg(aggregate_order_by(table.c.a, table.c.b.desc())) stmt = select(expr)
would represent the expression:
SELECT array_agg(a ORDER BY b DESC) FROM table;
Similarly:
expr = func.string_agg( table.c.a, aggregate_order_by(literal_column("','"), table.c.a) ) stmt = select(expr)
Would represent:
SELECT string_agg(a, ',' ORDER BY a) FROM table;
Changed in version 1.2.13: - the ORDER BY argument may be multiple terms
See also
Class signature
class
sqlalchemy.dialects.postgresql.aggregate_order_by
(sqlalchemy.sql.expression.ColumnElement
)
- class sqlalchemy.dialects.postgresql.array¶
A PostgreSQL ARRAY literal.
This is used to produce ARRAY literals in SQL expressions, e.g.:
from sqlalchemy.dialects.postgresql import array from sqlalchemy.dialects import postgresql from sqlalchemy import select, func stmt = select(array([1, 2]) + array([3, 4, 5])) print(stmt.compile(dialect=postgresql.dialect()))
Produces the SQL:
SELECT ARRAY[%(param_1)s, %(param_2)s] || ARRAY[%(param_3)s, %(param_4)s, %(param_5)s]) AS anon_1
An instance of
array
will always have the datatypeARRAY
. The “inner” type of the array is inferred from the values present, unless thetype_
keyword argument is passed:array(["foo", "bar"], type_=CHAR)
Multidimensional arrays are produced by nesting
array
constructs. The dimensionality of the finalARRAY
type is calculated by recursively adding the dimensions of the innerARRAY
type:stmt = select( array( [array([1, 2]), array([3, 4]), array([column("q"), column("x")])] ) ) print(stmt.compile(dialect=postgresql.dialect()))
Produces:
SELECT ARRAY[ ARRAY[%(param_1)s, %(param_2)s], ARRAY[%(param_3)s, %(param_4)s], ARRAY[q, x] ] AS anon_1
New in version 1.3.6: added support for multidimensional array literals
See also
Class signature
class
sqlalchemy.dialects.postgresql.array
(sqlalchemy.sql.expression.ExpressionClauseList
)
- function sqlalchemy.dialects.postgresql.array_agg(*arg, **kw)¶
PostgreSQL-specific form of
array_agg
, ensures return type isARRAY
and not the plainARRAY
, unless an explicittype_
is passed.
- function sqlalchemy.dialects.postgresql.Any(other, arrexpr, operator=<built-in function eq>)¶
A synonym for the ARRAY-level
Comparator.any()
method. See that method for details.
- function sqlalchemy.dialects.postgresql.All(other, arrexpr, operator=<built-in function eq>)¶
A synonym for the ARRAY-level
Comparator.all()
method. See that method for details.
- class sqlalchemy.dialects.postgresql.hstore¶
Construct an hstore value within a SQL expression using the PostgreSQL
hstore()
function.The
hstore
function accepts one or two arguments as described in the PostgreSQL documentation.E.g.:
from sqlalchemy.dialects.postgresql import array, hstore select(hstore("key1", "value1")) select( hstore( array(["key1", "key2", "key3"]), array(["value1", "value2", "value3"]), ) )
See also
HSTORE
- the PostgreSQLHSTORE
datatype.Members
Class signature
class
sqlalchemy.dialects.postgresql.hstore
(sqlalchemy.sql.functions.GenericFunction
)-
attribute
sqlalchemy.dialects.postgresql.hstore.
inherit_cache: bool | None = True¶ Indicate if this
HasCacheKey
instance should make use of the cache key generation scheme used by its immediate superclass.The attribute defaults to
None
, which indicates that a construct has not yet taken into account whether or not its appropriate for it to participate in caching; this is functionally equivalent to setting the value toFalse
, except that a warning is also emitted.This flag can be set to
True
on a particular class, if the SQL that corresponds to the object does not change based on attributes which are local to this class, and not its superclass.See also
Enabling Caching Support for Custom Constructs - General guideslines for setting the
HasCacheKey.inherit_cache
attribute for third-party or user defined SQL constructs.
-
attribute
sqlalchemy.dialects.postgresql.hstore.
type¶ alias of
HSTORE
-
attribute
- class sqlalchemy.dialects.postgresql.to_tsvector¶
The PostgreSQL
to_tsvector
SQL function.This function applies automatic casting of the REGCONFIG argument to use the
REGCONFIG
datatype automatically, and applies a return type ofTSVECTOR
.Assuming the PostgreSQL dialect has been imported, either by invoking
from sqlalchemy.dialects import postgresql
, or by creating a PostgreSQL engine usingcreate_engine("postgresql...")
,to_tsvector
will be used automatically when invokingsqlalchemy.func.to_tsvector()
, ensuring the correct argument and return type handlers are used at compile and execution time.New in version 2.0.0rc1.
Class signature
class
sqlalchemy.dialects.postgresql.to_tsvector
(sqlalchemy.dialects.postgresql.ext._regconfig_fn
)
- class sqlalchemy.dialects.postgresql.to_tsquery¶
The PostgreSQL
to_tsquery
SQL function.This function applies automatic casting of the REGCONFIG argument to use the
REGCONFIG
datatype automatically, and applies a return type ofTSQUERY
.Assuming the PostgreSQL dialect has been imported, either by invoking
from sqlalchemy.dialects import postgresql
, or by creating a PostgreSQL engine usingcreate_engine("postgresql...")
,to_tsquery
will be used automatically when invokingsqlalchemy.func.to_tsquery()
, ensuring the correct argument and return type handlers are used at compile and execution time.New in version 2.0.0rc1.
Class signature
class
sqlalchemy.dialects.postgresql.to_tsquery
(sqlalchemy.dialects.postgresql.ext._regconfig_fn
)
- class sqlalchemy.dialects.postgresql.plainto_tsquery¶
The PostgreSQL
plainto_tsquery
SQL function.This function applies automatic casting of the REGCONFIG argument to use the
REGCONFIG
datatype automatically, and applies a return type ofTSQUERY
.Assuming the PostgreSQL dialect has been imported, either by invoking
from sqlalchemy.dialects import postgresql
, or by creating a PostgreSQL engine usingcreate_engine("postgresql...")
,plainto_tsquery
will be used automatically when invokingsqlalchemy.func.plainto_tsquery()
, ensuring the correct argument and return type handlers are used at compile and execution time.New in version 2.0.0rc1.
Class signature
class
sqlalchemy.dialects.postgresql.plainto_tsquery
(sqlalchemy.dialects.postgresql.ext._regconfig_fn
)
- class sqlalchemy.dialects.postgresql.phraseto_tsquery¶
The PostgreSQL
phraseto_tsquery
SQL function.This function applies automatic casting of the REGCONFIG argument to use the
REGCONFIG
datatype automatically, and applies a return type ofTSQUERY
.Assuming the PostgreSQL dialect has been imported, either by invoking
from sqlalchemy.dialects import postgresql
, or by creating a PostgreSQL engine usingcreate_engine("postgresql...")
,phraseto_tsquery
will be used automatically when invokingsqlalchemy.func.phraseto_tsquery()
, ensuring the correct argument and return type handlers are used at compile and execution time.New in version 2.0.0rc1.
Class signature
class
sqlalchemy.dialects.postgresql.phraseto_tsquery
(sqlalchemy.dialects.postgresql.ext._regconfig_fn
)
- class sqlalchemy.dialects.postgresql.websearch_to_tsquery¶
The PostgreSQL
websearch_to_tsquery
SQL function.This function applies automatic casting of the REGCONFIG argument to use the
REGCONFIG
datatype automatically, and applies a return type ofTSQUERY
.Assuming the PostgreSQL dialect has been imported, either by invoking
from sqlalchemy.dialects import postgresql
, or by creating a PostgreSQL engine usingcreate_engine("postgresql...")
,websearch_to_tsquery
will be used automatically when invokingsqlalchemy.func.websearch_to_tsquery()
, ensuring the correct argument and return type handlers are used at compile and execution time.New in version 2.0.0rc1.
Class signature
class
sqlalchemy.dialects.postgresql.websearch_to_tsquery
(sqlalchemy.dialects.postgresql.ext._regconfig_fn
)
- class sqlalchemy.dialects.postgresql.ts_headline¶
The PostgreSQL
ts_headline
SQL function.This function applies automatic casting of the REGCONFIG argument to use the
REGCONFIG
datatype automatically, and applies a return type ofTEXT
.Assuming the PostgreSQL dialect has been imported, either by invoking
from sqlalchemy.dialects import postgresql
, or by creating a PostgreSQL engine usingcreate_engine("postgresql...")
,ts_headline
will be used automatically when invokingsqlalchemy.func.ts_headline()
, ensuring the correct argument and return type handlers are used at compile and execution time.New in version 2.0.0rc1.
Class signature
class
sqlalchemy.dialects.postgresql.ts_headline
(sqlalchemy.dialects.postgresql.ext._regconfig_fn
)
PostgreSQL Constraint Types¶
SQLAlchemy supports PostgreSQL EXCLUDE constraints via the
ExcludeConstraint
class:
Object Name | Description |
---|---|
A table-level EXCLUDE constraint. |
- class sqlalchemy.dialects.postgresql.ExcludeConstraint¶
A table-level EXCLUDE constraint.
Defines an EXCLUDE constraint as described in the PostgreSQL documentation.
Members
Class signature
class
sqlalchemy.dialects.postgresql.ExcludeConstraint
(sqlalchemy.schema.ColumnCollectionConstraint
)-
method
sqlalchemy.dialects.postgresql.ExcludeConstraint.
__init__(*elements, **kw)¶ Create an
ExcludeConstraint
object.E.g.:
const = ExcludeConstraint( (Column("period"), "&&"), (Column("group"), "="), where=(Column("group") != "some group"), ops={"group": "my_operator_class"}, )
The constraint is normally embedded into the
Table
construct directly, or added later usingappend_constraint()
:some_table = Table( "some_table", metadata, Column("id", Integer, primary_key=True), Column("period", TSRANGE()), Column("group", String), ) some_table.append_constraint( ExcludeConstraint( (some_table.c.period, "&&"), (some_table.c.group, "="), where=some_table.c.group != "some group", name="some_table_excl_const", ops={"group": "my_operator_class"}, ) )
The exclude constraint defined in this example requires the
btree_gist
extension, that can be created using the commandCREATE EXTENSION btree_gist;
.- Parameters:
*elements¶ –
A sequence of two tuples of the form
(column, operator)
where “column” is either aColumn
object, or a SQL expression element (e.g.func.int8range(table.from, table.to)
) or the name of a column as string, and “operator” is a string containing the operator to use (e.g. “&&” or “=”).In order to specify a column name when a
Column
object is not available, while ensuring that any necessary quoting rules take effect, an ad-hocColumn
orcolumn()
object should be used. Thecolumn
may also be a string SQL expression when passed asliteral_column()
ortext()
name¶ – Optional, the in-database name of this constraint.
deferrable¶ – Optional bool. If set, emit DEFERRABLE or NOT DEFERRABLE when issuing DDL for this constraint.
initially¶ – Optional string. If set, emit INITIALLY <value> when issuing DDL for this constraint.
using¶ – Optional string. If set, emit USING <index_method> when issuing DDL for this constraint. Defaults to ‘gist’.
where¶ –
Optional SQL expression construct or literal SQL string. If set, emit WHERE <predicate> when issuing DDL for this constraint.
Warning
The
ExcludeConstraint.where
argument toExcludeConstraint
can be passed as a Python string argument, which will be treated as trusted SQL text and rendered as given. DO NOT PASS UNTRUSTED INPUT TO THIS PARAMETER.ops¶ –
Optional dictionary. Used to define operator classes for the elements; works the same way as that of the postgresql_ops parameter specified to the
Index
construct.New in version 1.3.21.
See also
Operator Classes - general description of how PostgreSQL operator classes are specified.
-
method
For example:
from sqlalchemy.dialects.postgresql import ExcludeConstraint, TSRANGE
class RoomBooking(Base):
__tablename__ = "room_booking"
room = Column(Integer(), primary_key=True)
during = Column(TSRANGE())
__table_args__ = (ExcludeConstraint(("room", "="), ("during", "&&")),)
PostgreSQL DML Constructs¶
Object Name | Description |
---|---|
insert(table) |
Construct a PostgreSQL-specific variant |
PostgreSQL-specific implementation of INSERT. |
- function sqlalchemy.dialects.postgresql.insert(table: _DMLTableArgument) → Insert¶
Construct a PostgreSQL-specific variant
Insert
construct.The
sqlalchemy.dialects.postgresql.insert()
function creates asqlalchemy.dialects.postgresql.Insert
. This class is based on the dialect-agnosticInsert
construct which may be constructed using theinsert()
function in SQLAlchemy Core.The
Insert
construct includes additional methodsInsert.on_conflict_do_update()
,Insert.on_conflict_do_nothing()
.
- class sqlalchemy.dialects.postgresql.Insert¶
PostgreSQL-specific implementation of INSERT.
Adds methods for PG-specific syntaxes such as ON CONFLICT.
The
Insert
object is created using thesqlalchemy.dialects.postgresql.insert()
function.Class signature
class
sqlalchemy.dialects.postgresql.Insert
(sqlalchemy.sql.expression.Insert
)-
attribute
sqlalchemy.dialects.postgresql.Insert.
excluded¶ Provide the
excluded
namespace for an ON CONFLICT statementPG’s ON CONFLICT clause allows reference to the row that would be inserted, known as
excluded
. This attribute provides all columns in this row to be referenceable.Tip
The
Insert.excluded
attribute is an instance ofColumnCollection
, which provides an interface the same as that of theTable.c
collection described at Accessing Tables and Columns. With this collection, ordinary names are accessible like attributes (e.g.stmt.excluded.some_column
), but special names and dictionary method names should be accessed using indexed access, such asstmt.excluded["column name"]
orstmt.excluded["values"]
. See the docstring forColumnCollection
for further examples.See also
INSERT…ON CONFLICT (Upsert) - example of how to use
Insert.excluded
-
attribute
sqlalchemy.dialects.postgresql.Insert.
inherit_cache: bool | None = False¶ Indicate if this
HasCacheKey
instance should make use of the cache key generation scheme used by its immediate superclass.The attribute defaults to
None
, which indicates that a construct has not yet taken into account whether or not its appropriate for it to participate in caching; this is functionally equivalent to setting the value toFalse
, except that a warning is also emitted.This flag can be set to
True
on a particular class, if the SQL that corresponds to the object does not change based on attributes which are local to this class, and not its superclass.See also
Enabling Caching Support for Custom Constructs - General guideslines for setting the
HasCacheKey.inherit_cache
attribute for third-party or user defined SQL constructs.
-
method
sqlalchemy.dialects.postgresql.Insert.
on_conflict_do_nothing(constraint: _OnConflictConstraintT = None, index_elements: _OnConflictIndexElementsT = None, index_where: _OnConflictIndexWhereT = None) → Self¶ Specifies a DO NOTHING action for ON CONFLICT clause.
The
constraint
andindex_elements
arguments are optional, but only one of these can be specified.- Parameters:
constraint¶ – The name of a unique or exclusion constraint on the table, or the constraint object itself if it has a .name attribute.
index_elements¶ – A sequence consisting of string column names,
Column
objects, or other column expression objects that will be used to infer a target index.index_where¶ – Additional WHERE criterion that can be used to infer a conditional target index.
See also
-
method
sqlalchemy.dialects.postgresql.Insert.
on_conflict_do_update(constraint: _OnConflictConstraintT = None, index_elements: _OnConflictIndexElementsT = None, index_where: _OnConflictIndexWhereT = None, set_: _OnConflictSetT = None, where: _OnConflictWhereT = None) → Self¶ Specifies a DO UPDATE SET action for ON CONFLICT clause.
Either the
constraint
orindex_elements
argument is required, but only one of these can be specified.- Parameters:
constraint¶ – The name of a unique or exclusion constraint on the table, or the constraint object itself if it has a .name attribute.
index_elements¶ – A sequence consisting of string column names,
Column
objects, or other column expression objects that will be used to infer a target index.index_where¶ – Additional WHERE criterion that can be used to infer a conditional target index.
set_¶ –
A dictionary or other mapping object where the keys are either names of columns in the target table, or
Column
objects or other ORM-mapped columns matching that of the target table, and expressions or literals as values, specifying theSET
actions to take.New in version 1.4: The
Insert.on_conflict_do_update.set_
parameter supportsColumn
objects from the targetTable
as keys.Warning
This dictionary does not take into account Python-specified default UPDATE values or generation functions, e.g. those specified using
Column.onupdate
. These values will not be exercised for an ON CONFLICT style of UPDATE, unless they are manually specified in theInsert.on_conflict_do_update.set_
dictionary.where¶ – Optional argument. If present, can be a literal SQL string or an acceptable expression for a
WHERE
clause that restricts the rows affected byDO UPDATE SET
. Rows not meeting theWHERE
condition will not be updated (effectively aDO NOTHING
for those rows).
See also
-
attribute
psycopg2¶
Support for the PostgreSQL database via the psycopg2 driver.
DBAPI¶
Documentation and download information (if applicable) for psycopg2 is available at: https://pypi.org/project/psycopg2/
Connecting¶
Connect String:
postgresql+psycopg2://user:password@host:port/dbname[?key=value&key=value...]
psycopg2 Connect Arguments¶
Keyword arguments that are specific to the SQLAlchemy psycopg2 dialect
may be passed to create_engine()
, and include the following:
isolation_level
: This option, available for all PostgreSQL dialects, includes theAUTOCOMMIT
isolation level when using the psycopg2 dialect. This option sets the default isolation level for the connection that is set immediately upon connection to the database before the connection is pooled. This option is generally superseded by the more modernConnection.execution_options.isolation_level
execution option, detailed at Setting Transaction Isolation Levels including DBAPI Autocommit.client_encoding
: sets the client encoding in a libpq-agnostic way, using psycopg2’sset_client_encoding()
method.See also
executemany_mode
,executemany_batch_page_size
,executemany_values_page_size
: Allows use of psycopg2 extensions for optimizing “executemany”-style queries. See the referenced section below for details.See also
Tip
The above keyword arguments are dialect keyword arguments, meaning
that they are passed as explicit keyword arguments to create_engine()
:
engine = create_engine(
"postgresql+psycopg2://scott:tiger@localhost/test",
isolation_level="SERIALIZABLE",
)
These should not be confused with DBAPI connect arguments, which
are passed as part of the create_engine.connect_args
dictionary and/or are passed in the URL query string, as detailed in
the section Custom DBAPI connect() arguments / on-connect routines.
SSL Connections¶
The psycopg2 module has a connection argument named sslmode
for
controlling its behavior regarding secure (SSL) connections. The default is
sslmode=prefer
; it will attempt an SSL connection and if that fails it
will fall back to an unencrypted connection. sslmode=require
may be used
to ensure that only secure connections are established. Consult the
psycopg2 / libpq documentation for further options that are available.
Note that sslmode
is specific to psycopg2 so it is included in the
connection URI:
engine = sa.create_engine(
"postgresql+psycopg2://scott:tiger@192.168.0.199:5432/test?sslmode=require"
)
Unix Domain Connections¶
psycopg2 supports connecting via Unix domain connections. When the host
portion of the URL is omitted, SQLAlchemy passes None
to psycopg2,
which specifies Unix-domain communication rather than TCP/IP communication:
create_engine("postgresql+psycopg2://user:password@/dbname")
By default, the socket file used is to connect to a Unix-domain socket
in /tmp
, or whatever socket directory was specified when PostgreSQL
was built. This value can be overridden by passing a pathname to psycopg2,
using host
as an additional keyword argument:
create_engine(
"postgresql+psycopg2://user:password@/dbname?host=/var/lib/postgresql"
)
Warning
The format accepted here allows for a hostname in the main URL in addition to the “host” query string argument. When using this URL format, the initial host is silently ignored. That is, this URL:
engine = create_engine(
"postgresql+psycopg2://user:password@myhost1/dbname?host=myhost2"
)
Above, the hostname myhost1
is silently ignored and discarded. The
host which is connected is the myhost2
host.
This is to maintain some degree of compatibility with PostgreSQL’s own URL format which has been tested to behave the same way and for which tools like PifPaf hardcode two hostnames.
See also
Specifying multiple fallback hosts¶
psycopg2 supports multiple connection points in the connection string.
When the host
parameter is used multiple times in the query section of
the URL, SQLAlchemy will create a single string of the host and port
information provided to make the connections. Tokens may consist of
host::port
or just host
; in the latter case, the default port
is selected by libpq. In the example below, three host connections
are specified, for HostA::PortA
, HostB
connecting to the default port,
and HostC::PortC
:
create_engine(
"postgresql+psycopg2://user:password@/dbname?host=HostA:PortA&host=HostB&host=HostC:PortC"
)
As an alternative, libpq query string format also may be used; this specifies
host
and port
as single query string arguments with comma-separated
lists - the default port can be chosen by indicating an empty value
in the comma separated list:
create_engine(
"postgresql+psycopg2://user:password@/dbname?host=HostA,HostB,HostC&port=PortA,,PortC"
)
With either URL style, connections to each host is attempted based on a
configurable strategy, which may be configured using the libpq
target_session_attrs
parameter. Per libpq this defaults to any
which indicates a connection to each host is then attempted until a connection is successful.
Other strategies include primary
, prefer-standby
, etc. The complete
list is documented by PostgreSQL at
libpq connection strings.
For example, to indicate two hosts using the primary
strategy:
create_engine(
"postgresql+psycopg2://user:password@/dbname?host=HostA:PortA&host=HostB&host=HostC:PortC&target_session_attrs=primary"
)
Changed in version 1.4.40: Port specification in psycopg2 multiple host format is repaired, previously ports were not correctly interpreted in this context. libpq comma-separated format is also now supported.
New in version 1.3.20: Support for multiple hosts in PostgreSQL connection string.
See also
libpq connection strings - please refer to this section in the libpq documentation for complete background on multiple host support.
Empty DSN Connections / Environment Variable Connections¶
The psycopg2 DBAPI can connect to PostgreSQL by passing an empty DSN to the
libpq client library, which by default indicates to connect to a localhost
PostgreSQL database that is open for “trust” connections. This behavior can be
further tailored using a particular set of environment variables which are
prefixed with PG_...
, which are consumed by libpq
to take the place of
any or all elements of the connection string.
For this form, the URL can be passed without any elements other than the initial scheme:
engine = create_engine("postgresql+psycopg2://")
In the above form, a blank “dsn” string is passed to the psycopg2.connect()
function which in turn represents an empty DSN passed to libpq.
New in version 1.3.2: support for parameter-less connections with psycopg2.
See also
Environment Variables -
PostgreSQL documentation on how to use PG_...
environment variables for connections.
Per-Statement/Connection Execution Options¶
The following DBAPI-specific options are respected when used with
Connection.execution_options()
,
Executable.execution_options()
,
Query.execution_options()
,
in addition to those not specific to DBAPIs:
isolation_level
- Set the transaction isolation level for the lifespan of aConnection
(can only be set on a connection, not a statement or query). See Psycopg2 Transaction Isolation Level.stream_results
- Enable or disable usage of psycopg2 server side cursors - this feature makes use of “named” cursors in combination with special result handling methods so that result rows are not fully buffered. Defaults to False, meaning cursors are buffered by default.max_row_buffer
- when usingstream_results
, an integer value that specifies the maximum number of rows to buffer at a time. This is interpreted by theBufferedRowCursorResult
, and if omitted the buffer will grow to ultimately store 1000 rows at a time.Changed in version 1.4: The
max_row_buffer
size can now be greater than 1000, and the buffer will grow to that size.
Psycopg2 Fast Execution Helpers¶
Modern versions of psycopg2 include a feature known as Fast Execution Helpers , which have been shown in benchmarking to improve psycopg2’s executemany() performance, primarily with INSERT statements, by at least an order of magnitude.
SQLAlchemy implements a native form of the “insert many values”
handler that will rewrite a single-row INSERT statement to accommodate for
many values at once within an extended VALUES clause; this handler is
equivalent to psycopg2’s execute_values()
handler; an overview of this
feature and its configuration are at “Insert Many Values” Behavior for INSERT statements.
New in version 2.0: Replaced psycopg2’s execute_values()
fast execution
helper with a native SQLAlchemy mechanism known as
insertmanyvalues.
The psycopg2 dialect retains the ability to use the psycopg2-specific
execute_batch()
feature, although it is not expected that this is a widely
used feature. The use of this extension may be enabled using the
executemany_mode
flag which may be passed to create_engine()
:
engine = create_engine(
"postgresql+psycopg2://scott:tiger@host/dbname",
executemany_mode="values_plus_batch",
)
Possible options for executemany_mode
include:
values_only
- this is the default value. SQLAlchemy’s native insertmanyvalues handler is used for qualifying INSERT statements, assumingcreate_engine.use_insertmanyvalues
is left at its default value ofTrue
. This handler rewrites simple INSERT statements to include multiple VALUES clauses so that many parameter sets can be inserted with one statement.'values_plus_batch'
- SQLAlchemy’s native insertmanyvalues handler is used for qualifying INSERT statements, assumingcreate_engine.use_insertmanyvalues
is left at its default value ofTrue
. Then, psycopg2’sexecute_batch()
handler is used for qualifying UPDATE and DELETE statements when executed with multiple parameter sets. When using this mode, theCursorResult.rowcount
attribute will not contain a value for executemany-style executions against UPDATE and DELETE statements.
Changed in version 2.0: Removed the 'batch'
and 'None'
options
from psycopg2 executemany_mode
. Control over batching for INSERT
statements is now configured via the
create_engine.use_insertmanyvalues
engine-level parameter.
The term “qualifying statements” refers to the statement being executed
being a Core insert()
, update()
or delete()
construct, and not a plain textual SQL
string or one constructed using text()
. It also may not be
a special “extension” statement such as an “ON CONFLICT” “upsert” statement.
When using the ORM, all insert/update/delete statements used by the ORM flush process
are qualifying.
The “page size” for the psycopg2 “batch” strategy can be affected
by using the executemany_batch_page_size
parameter, which defaults to
100.
For the “insertmanyvalues” feature, the page size can be controlled using the
create_engine.insertmanyvalues_page_size
parameter,
which defaults to 1000. An example of modifying both parameters
is below:
engine = create_engine(
"postgresql+psycopg2://scott:tiger@host/dbname",
executemany_mode="values_plus_batch",
insertmanyvalues_page_size=5000,
executemany_batch_page_size=500,
)
See also
“Insert Many Values” Behavior for INSERT statements - background on “insertmanyvalues”
Sending Multiple Parameters - General information on using the
Connection
object to execute statements in such a way as to make
use of the DBAPI .executemany()
method.
Unicode with Psycopg2¶
The psycopg2 DBAPI driver supports Unicode data transparently.
The client character encoding can be controlled for the psycopg2 dialect in the following ways:
For PostgreSQL 9.1 and above, the
client_encoding
parameter may be passed in the database URL; this parameter is consumed by the underlyinglibpq
PostgreSQL client library:engine = create_engine( "postgresql+psycopg2://user:pass@host/dbname?client_encoding=utf8" )
Alternatively, the above
client_encoding
value may be passed usingcreate_engine.connect_args
for programmatic establishment withlibpq
:engine = create_engine( "postgresql+psycopg2://user:pass@host/dbname", connect_args={"client_encoding": "utf8"}, )
For all PostgreSQL versions, psycopg2 supports a client-side encoding value that will be passed to database connections when they are first established. The SQLAlchemy psycopg2 dialect supports this using the
client_encoding
parameter passed tocreate_engine()
:engine = create_engine( "postgresql+psycopg2://user:pass@host/dbname", client_encoding="utf8" )
Tip
The above
client_encoding
parameter admittedly is very similar in appearance to usage of the parameter within thecreate_engine.connect_args
dictionary; the difference above is that the parameter is consumed by psycopg2 and is passed to the database connection usingSET client_encoding TO 'utf8'
; in the previously mentioned style, the parameter is instead passed through psycopg2 and consumed by thelibpq
library.A common way to set up client encoding with PostgreSQL databases is to ensure it is configured within the server-side postgresql.conf file; this is the recommended way to set encoding for a server that is consistently of one encoding in all databases:
# postgresql.conf file # client_encoding = sql_ascii # actually, defaults to database # encoding client_encoding = utf8
Transactions¶
The psycopg2 dialect fully supports SAVEPOINT and two-phase commit operations.
Psycopg2 Transaction Isolation Level¶
As discussed in Transaction Isolation Level,
all PostgreSQL dialects support setting of transaction isolation level
both via the isolation_level
parameter passed to create_engine()
,
as well as the isolation_level
argument used by
Connection.execution_options()
. When using the psycopg2 dialect
, these
options make use of psycopg2’s set_isolation_level()
connection method,
rather than emitting a PostgreSQL directive; this is because psycopg2’s
API-level setting is always emitted at the start of each transaction in any
case.
The psycopg2 dialect supports these constants for isolation level:
READ COMMITTED
READ UNCOMMITTED
REPEATABLE READ
SERIALIZABLE
AUTOCOMMIT
NOTICE logging¶
The psycopg2 dialect will log PostgreSQL NOTICE messages
via the sqlalchemy.dialects.postgresql
logger. When this logger
is set to the logging.INFO
level, notice messages will be logged:
import logging
logging.getLogger("sqlalchemy.dialects.postgresql").setLevel(logging.INFO)
Above, it is assumed that logging is configured externally. If this is not
the case, configuration such as logging.basicConfig()
must be utilized:
import logging
logging.basicConfig() # log messages to stdout
logging.getLogger("sqlalchemy.dialects.postgresql").setLevel(logging.INFO)
See also
Logging HOWTO - on the python.org website
HSTORE type¶
The psycopg2
DBAPI includes an extension to natively handle marshalling of
the HSTORE type. The SQLAlchemy psycopg2 dialect will enable this extension
by default when psycopg2 version 2.4 or greater is used, and
it is detected that the target database has the HSTORE type set up for use.
In other words, when the dialect makes the first
connection, a sequence like the following is performed:
Request the available HSTORE oids using
psycopg2.extras.HstoreAdapter.get_oids()
. If this function returns a list of HSTORE identifiers, we then determine that theHSTORE
extension is present. This function is skipped if the version of psycopg2 installed is less than version 2.4.If the
use_native_hstore
flag is at its default ofTrue
, and we’ve detected thatHSTORE
oids are available, thepsycopg2.extensions.register_hstore()
extension is invoked for all connections.
The register_hstore()
extension has the effect of all Python
dictionaries being accepted as parameters regardless of the type of target
column in SQL. The dictionaries are converted by this extension into a
textual HSTORE expression. If this behavior is not desired, disable the
use of the hstore extension by setting use_native_hstore
to False
as
follows:
engine = create_engine(
"postgresql+psycopg2://scott:tiger@localhost/test",
use_native_hstore=False,
)
The HSTORE
type is still supported when the
psycopg2.extensions.register_hstore()
extension is not used. It merely
means that the coercion between Python dictionaries and the HSTORE
string format, on both the parameter side and the result side, will take
place within SQLAlchemy’s own marshalling logic, and not that of psycopg2
which may be more performant.
psycopg¶
Support for the PostgreSQL database via the psycopg (a.k.a. psycopg 3) driver.
DBAPI¶
Documentation and download information (if applicable) for psycopg (a.k.a. psycopg 3) is available at: https://pypi.org/project/psycopg/
Connecting¶
Connect String:
postgresql+psycopg://user:password@host:port/dbname[?key=value&key=value...]
psycopg
is the package and module name for version 3 of the psycopg
database driver, formerly known as psycopg2
. This driver is different
enough from its psycopg2
predecessor that SQLAlchemy supports it
via a totally separate dialect; support for psycopg2
is expected to remain
for as long as that package continues to function for modern Python versions,
and also remains the default dialect for the postgresql://
dialect
series.
The SQLAlchemy psycopg
dialect provides both a sync and an async
implementation under the same dialect name. The proper version is
selected depending on how the engine is created:
calling
create_engine()
withpostgresql+psycopg://...
will automatically select the sync version, e.g.:from sqlalchemy import create_engine sync_engine = create_engine( "postgresql+psycopg://scott:tiger@localhost/test" )
calling
create_async_engine()
withpostgresql+psycopg://...
will automatically select the async version, e.g.:from sqlalchemy.ext.asyncio import create_async_engine asyncio_engine = create_async_engine( "postgresql+psycopg://scott:tiger@localhost/test" )
The asyncio version of the dialect may also be specified explicitly using the
psycopg_async
suffix, as:
from sqlalchemy.ext.asyncio import create_async_engine
asyncio_engine = create_async_engine(
"postgresql+psycopg_async://scott:tiger@localhost/test"
)
See also
psycopg2 - The SQLAlchemy psycopg
dialect shares most of its behavior with the psycopg2
dialect.
Further documentation is available there.
Using a different Cursor class¶
One of the differences between psycopg
and the older psycopg2
is how bound parameters are handled: psycopg2
would bind them
client side, while psycopg
by default will bind them server side.
It’s possible to configure psycopg
to do client side binding by
specifying the cursor_factory
to be ClientCursor
when creating
the engine:
from psycopg import ClientCursor
client_side_engine = create_engine(
"postgresql+psycopg://...",
connect_args={"cursor_factory": ClientCursor},
)
Similarly when using an async engine the AsyncClientCursor
can be
specified:
from psycopg import AsyncClientCursor
client_side_engine = create_async_engine(
"postgresql+psycopg://...",
connect_args={"cursor_factory": AsyncClientCursor},
)
See also
pg8000¶
Support for the PostgreSQL database via the pg8000 driver.
DBAPI¶
Documentation and download information (if applicable) for pg8000 is available at: https://pypi.org/project/pg8000/
Connecting¶
Connect String:
postgresql+pg8000://user:password@host:port/dbname[?key=value&key=value...]
Changed in version 1.4: The pg8000 dialect has been updated for version 1.16.6 and higher, and is again part of SQLAlchemy’s continuous integration with full feature support.
Unicode¶
pg8000 will encode / decode string values between it and the server using the
PostgreSQL client_encoding
parameter; by default this is the value in
the postgresql.conf
file, which often defaults to SQL_ASCII
.
Typically, this can be changed to utf-8
, as a more useful default:
# client_encoding = sql_ascii # actually, defaults to database encoding
client_encoding = utf8
The client_encoding
can be overridden for a session by executing the SQL:
SET CLIENT_ENCODING TO 'utf8';
SQLAlchemy will execute this SQL on all new connections based on the value
passed to create_engine()
using the client_encoding
parameter:
engine = create_engine(
"postgresql+pg8000://user:pass@host/dbname", client_encoding="utf8"
)
SSL Connections¶
pg8000 accepts a Python SSLContext
object which may be specified using the
create_engine.connect_args
dictionary:
import ssl
ssl_context = ssl.create_default_context()
engine = sa.create_engine(
"postgresql+pg8000://scott:tiger@192.168.0.199/test",
connect_args={"ssl_context": ssl_context},
)
If the server uses an automatically-generated certificate that is self-signed or does not match the host name (as seen from the client), it may also be necessary to disable hostname checking:
import ssl
ssl_context = ssl.create_default_context()
ssl_context.check_hostname = False
ssl_context.verify_mode = ssl.CERT_NONE
engine = sa.create_engine(
"postgresql+pg8000://scott:tiger@192.168.0.199/test",
connect_args={"ssl_context": ssl_context},
)
pg8000 Transaction Isolation Level¶
The pg8000 dialect offers the same isolation level settings as that of the psycopg2 dialect:
READ COMMITTED
READ UNCOMMITTED
REPEATABLE READ
SERIALIZABLE
AUTOCOMMIT
asyncpg¶
Support for the PostgreSQL database via the asyncpg driver.
DBAPI¶
Documentation and download information (if applicable) for asyncpg is available at: https://magicstack.github.io/asyncpg/
Connecting¶
Connect String:
postgresql+asyncpg://user:password@host:port/dbname[?key=value&key=value...]
The asyncpg dialect is SQLAlchemy’s first Python asyncio dialect.
Using a special asyncio mediation layer, the asyncpg dialect is usable as the backend for the SQLAlchemy asyncio extension package.
This dialect should normally be used only with the
create_async_engine()
engine creation function:
from sqlalchemy.ext.asyncio import create_async_engine
engine = create_async_engine(
"postgresql+asyncpg://user:pass@hostname/dbname"
)
New in version 1.4.
Note
By default asyncpg does not decode the json
and jsonb
types and
returns them as strings. SQLAlchemy sets default type decoder for json
and jsonb
types using the python builtin json.loads
function.
The json implementation used can be changed by setting the attribute
json_deserializer
when creating the engine with
create_engine()
or create_async_engine()
.
Multihost Connections¶
The asyncpg dialect features support for multiple fallback hosts in the
same way as that of the psycopg2 and psycopg dialects. The
syntax is the same,
using host=<host>:<port>
combinations as additional query string arguments;
however, there is no default port, so all hosts must have a complete port number
present, otherwise an exception is raised:
engine = create_async_engine(
"postgresql+asyncpg://user:password@/dbname?host=HostA:5432&host=HostB:5432&host=HostC:5432"
)
For complete background on this syntax, see Specifying multiple fallback hosts.
New in version 2.0.18.
See also
Prepared Statement Cache¶
The asyncpg SQLAlchemy dialect makes use of asyncpg.connection.prepare()
for all statements. The prepared statement objects are cached after
construction which appears to grant a 10% or more performance improvement for
statement invocation. The cache is on a per-DBAPI connection basis, which
means that the primary storage for prepared statements is within DBAPI
connections pooled within the connection pool. The size of this cache
defaults to 100 statements per DBAPI connection and may be adjusted using the
prepared_statement_cache_size
DBAPI argument (note that while this argument
is implemented by SQLAlchemy, it is part of the DBAPI emulation portion of the
asyncpg dialect, therefore is handled as a DBAPI argument, not a dialect
argument):
engine = create_async_engine(
"postgresql+asyncpg://user:pass@hostname/dbname?prepared_statement_cache_size=500"
)
To disable the prepared statement cache, use a value of zero:
engine = create_async_engine(
"postgresql+asyncpg://user:pass@hostname/dbname?prepared_statement_cache_size=0"
)
New in version 1.4.0b2: Added prepared_statement_cache_size
for asyncpg.
Warning
The asyncpg
database driver necessarily uses caches for
PostgreSQL type OIDs, which become stale when custom PostgreSQL datatypes
such as ENUM
objects are changed via DDL operations. Additionally,
prepared statements themselves which are optionally cached by SQLAlchemy’s
driver as described above may also become “stale” when DDL has been emitted
to the PostgreSQL database which modifies the tables or other objects
involved in a particular prepared statement.
The SQLAlchemy asyncpg dialect will invalidate these caches within its local
process when statements that represent DDL are emitted on a local
connection, but this is only controllable within a single Python process /
database engine. If DDL changes are made from other database engines
and/or processes, a running application may encounter asyncpg exceptions
InvalidCachedStatementError
and/or InternalServerError("cache lookup
failed for type <oid>")
if it refers to pooled database connections which
operated upon the previous structures. The SQLAlchemy asyncpg dialect will
recover from these error cases when the driver raises these exceptions by
clearing its internal caches as well as those of the asyncpg driver in
response to them, but cannot prevent them from being raised in the first
place if the cached prepared statement or asyncpg type caches have gone
stale, nor can it retry the statement as the PostgreSQL transaction is
invalidated when these errors occur.
Prepared Statement Name with PGBouncer¶
By default, asyncpg enumerates prepared statements in numeric order, which
can lead to errors if a name has already been taken for another prepared
statement. This issue can arise if your application uses database proxies
such as PgBouncer to handle connections. One possible workaround is to
use dynamic prepared statement names, which asyncpg now supports through
an optional name
value for the statement name. This allows you to
generate your own unique names that won’t conflict with existing ones.
To achieve this, you can provide a function that will be called every time
a prepared statement is prepared:
from uuid import uuid4
engine = create_async_engine(
"postgresql+asyncpg://user:pass@somepgbouncer/dbname",
poolclass=NullPool,
connect_args={
"prepared_statement_name_func": lambda: f"__asyncpg_{uuid4()}__",
},
)
Warning
When using PGBouncer, to prevent a buildup of useless prepared statements in
your application, it’s important to use the NullPool
pool
class, and to configure PgBouncer to use DISCARD
when returning connections. The DISCARD command is used to release resources held by the db connection,
including prepared statements. Without proper setup, prepared statements can
accumulate quickly and cause performance issues.
Disabling the PostgreSQL JIT to improve ENUM datatype handling¶
Asyncpg has an issue when
using PostgreSQL ENUM datatypes, where upon the creation of new database
connections, an expensive query may be emitted in order to retrieve metadata
regarding custom types which has been shown to negatively affect performance.
To mitigate this issue, the PostgreSQL “jit” setting may be disabled from the
client using this setting passed to create_async_engine()
:
engine = create_async_engine(
"postgresql+asyncpg://user:password@localhost/tmp",
connect_args={"server_settings": {"jit": "off"}},
)
psycopg2cffi¶
Support for the PostgreSQL database via the psycopg2cffi driver.
DBAPI¶
Documentation and download information (if applicable) for psycopg2cffi is available at: https://pypi.org/project/psycopg2cffi/
Connecting¶
Connect String:
postgresql+psycopg2cffi://user:password@host:port/dbname[?key=value&key=value...]
psycopg2cffi
is an adaptation of psycopg2
, using CFFI for the C
layer. This makes it suitable for use in e.g. PyPy. Documentation
is as per psycopg2
.
flambé! the dragon and The Alchemist image designs created and generously donated by Rotem Yaari.
Created using Sphinx 7.2.6. Documentation last generated: Thu 12 Dec 2024 05:39:06 PM EST