Support for the PostgreSQL database.
The following dialect/DBAPI options are available. Please refer to individual DBAPI sections for connect information.
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'), 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.
To force the usage of RETURNING by default off, specify the flag
implicit_returning=False
to create_engine()
.
PostgreSQL 10 has a new IDENTITY feature that supersedes the use of SERIAL. Built-in support for rendering of IDENTITY is not available yet, however the following compilation hook may be used 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 NOT NULL,
data VARCHAR,
PRIMARY KEY (id)
)
All PostgreSQL dialects support setting of transaction isolation level
both via a dialect-specific parameter
create_engine.isolation_level
accepted by create_engine()
,
as well as the Connection.execution_options.isolation_level
argument as passed to Connection.execution_options()
.
When using a non-psycopg2 dialect, this feature works by issuing the command
SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL <level>
for
each new connection. For the special AUTOCOMMIT isolation level,
DBAPI-specific techniques are used.
To set isolation level using create_engine()
:
engine = create_engine(
"postgresql+pg8000://scott:tiger@localhost/test",
isolation_level="READ UNCOMMITTED"
)
To set using per-connection execution options:
connection = engine.connect()
connection = connection.execution_options(
isolation_level="READ COMMITTED"
)
Valid values for isolation_level
include:
READ COMMITTED
READ UNCOMMITTED
REPEATABLE READ
SERIALIZABLE
AUTOCOMMIT
- on psycopg2 / pg8000 onlyTL;DR;: keep the search_path
variable set to its default of public
,
name schemas other than public
explicitly within Table
definitions.
The PostgreSQL dialect can reflect tables from any schema. The
Table.schema
argument, or alternatively the
MetaData.reflect.schema
argument determines which schema will
be searched for the table or tables. The reflected Table
objects
will in all cases retain this .schema
attribute as was specified.
However, 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
PostgreSQL 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
>>> engine = create_engine("postgresql://scott:tiger@localhost/test")
>>> with engine.connect() as conn:
... conn.execute("SET search_path TO test_schema, public")
... meta = MetaData()
... referring = Table('referring', meta,
... autoload=True, autoload_with=conn)
...
<sqlalchemy.engine.result.ResultProxy object at 0x101612ed0>
The above process would deliver to the MetaData.tables
collection
referred
table named without the schema:
>>> meta.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("SET search_path TO test_schema, public")
... meta = MetaData()
... referring = Table('referring', meta, autoload=True,
... autoload_with=conn,
... postgresql_ignore_search_path=True)
...
<sqlalchemy.engine.result.ResultProxy object at 0x1016126d0>
We will now have test_schema.referred
stored as schema-qualified:
>>> meta.tables['test_schema.referred'].schema
'test_schema'
Best Practices for PostgreSQL Schema reflection
The description of PostgreSQL schema reflection behavior is complex, and
is the product of many years of dealing with widely varied use cases and
user preferences. But in fact, there’s no need to understand any of it if
you just stick to the simplest use pattern: leave the search_path
set
to its default of public
only, never refer to the name public
as
an explicit schema name otherwise, and refer to all other schema names
explicitly when building up a Table
object. The options
described here are only for those users who can’t, or prefer not to, stay
within these guidelines.
Note that in all cases, the “default” schema is always reflected as
None
. The “default” schema on PostgreSQL is that which is returned by the
PostgreSQL current_schema()
function. On a typical PostgreSQL
installation, this is the name public
. So a table that refers to another
which is in the public
(i.e. default) schema will always have the
.schema
attribute set to None
.
New in version 0.9.2: Added the postgresql_ignore_search_path
dialect-level option accepted by Table
and
MetaData.reflect()
.
See also
The Schema Search Path - on the PostgreSQL website.
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()
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
postgresql.dml.insert()
function, which provides
the generative methods on_conflict_do_update()
and 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']
)
conn.execute(do_nothing_stmt)
do_update_stmt = insert_stmt.on_conflict_do_update(
constraint='pk_my_table',
set_=dict(data='updated value')
)
conn.execute(do_update_stmt)
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')
)
do_update_stmt = insert_stmt.on_conflict_do_update(
index_elements=[my_table.c.id],
set_=dict(data='updated value')
)
When using Insert.on_conflict_do_update.index_elements
to
infer an index, a partial index can be inferred by also specifying the
use the Insert.on_conflict_do_update.index_where
parameter:
from sqlalchemy.dialects.postgresql import insert
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)
)
conn.execute(stmt)
The 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')
)
do_update_stmt = insert_stmt.on_conflict_do_update(
constraint='my_table_pk',
set_=dict(data='updated value')
)
The Insert.on_conflict_do_update.constraint
argument may
also refer to a SQLAlchemy construct representing a constraint,
e.g. UniqueConstraint
, PrimaryKeyConstraint
,
Index
, or ExcludeConstraint
. In this use,
if the constraint has a name, it is used directly. Otherwise, if the
constraint is unnamed, then inference will be used, where the expressions
and optional WHERE clause of the constraint will be spelled out in the
construct. This use is especially convenient
to refer to the named or unnamed primary key of a Table
using the
Table.primary_key
attribute:
do_update_stmt = insert_stmt.on_conflict_do_update(
constraint=my_table.primary_key,
set_=dict(data='updated value')
)
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:
from sqlalchemy.dialects.postgresql import insert
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')
)
conn.execute(do_update_stmt)
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.
In order to refer to the proposed insertion row, the special alias
excluded
is available as an attribute on
the postgresql.dml.Insert
object; this object is a
ColumnCollection
which alias contains all columns of the target
table:
from sqlalchemy.dialects.postgresql import insert
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)
)
conn.execute(do_update_stmt)
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:
from sqlalchemy.dialects.postgresql import insert
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)
)
conn.execute(on_update_stmt)
ON CONFLICT
may also be used to skip inserting a row entirely
if any conflict with a unique or exclusion constraint occurs; below
this is illustrated using the
on_conflict_do_nothing()
method:
from sqlalchemy.dialects.postgresql import insert
stmt = insert(my_table).values(id='some_id', data='inserted value')
stmt = stmt.on_conflict_do_nothing(index_elements=['id'])
conn.execute(stmt)
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:
from sqlalchemy.dialects.postgresql import insert
stmt = insert(my_table).values(id='some_id', data='inserted value')
stmt = stmt.on_conflict_do_nothing()
conn.execute(stmt)
New in version 1.1: Added support for PostgreSQL ON CONFLICT clauses
See also
INSERT .. ON CONFLICT - in the PostgreSQL documentation.
SQLAlchemy makes available the PostgreSQL @@
operator via the
ColumnElement.match()
method on any textual column expression.
On a PostgreSQL dialect, an expression like the following:
select([sometable.c.text.match("search string")])
will emit to the database:
SELECT text @@ to_tsquery('search string') FROM table
The PostgreSQL text search functions such as to_tsquery()
and to_tsvector()
are available
explicitly using the standard func
construct. For example:
select([
func.to_tsvector('fat cats ate rats').match('cat & rat')
])
Emits the equivalent of:
SELECT to_tsvector('fat cats ate rats') @@ to_tsquery('cat & rat')
The postgresql.TSVECTOR
type can provide for explicit CAST:
from sqlalchemy.dialects.postgresql import TSVECTOR
from sqlalchemy import select, cast
select([cast("some text", TSVECTOR)])
produces a statement equivalent to:
SELECT CAST('some text' AS TSVECTOR) AS anon_1
Full Text Searches in PostgreSQL are influenced by a combination of: the
PostgreSQL setting of default_text_search_config
, the regconfig
used
to build the GIN/GiST indexes, and the regconfig
optionally passed in
during a query.
When performing a Full Text Search against a column that has a GIN or
GiST index that is already pre-computed (which is common on full text
searches) one may need to explicitly pass in a particular PostgreSQL
regconfig
value to ensure the query-planner utilizes the index and does
not re-compute the column on demand.
In order to provide for this explicit query planning, or to use different
search strategies, the match
method accepts a postgresql_regconfig
keyword argument:
select([mytable.c.id]).where(
mytable.c.title.match('somestring', postgresql_regconfig='english')
)
Emits the equivalent of:
SELECT mytable.id FROM mytable
WHERE mytable.title @@ to_tsquery('english', 'somestring')
One can also specifically pass in a ‘regconfig’ value to the
to_tsvector()
command as the initial argument:
select([mytable.c.id]).where(
func.to_tsvector('english', mytable.c.title )\
.match('somestring', postgresql_regconfig='english')
)
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.
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')
Several extensions to the Index
construct are available, specific
to the PostgreSQL dialect.
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)
PostgreSQL allows the specification of an operator class for each column of
an index (see
http://www.postgresql.org/docs/8.3/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
dictionary 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'
})
PostgreSQL provides several index types: B-Tree, Hash, GiST, and GIN, as well
as the ability for users to create their own (see
http://www.postgresql.org/docs/8.3/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.
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})
New in version 1.0.6.
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')
New in version 1.1.
Note that the same option is available on Table
as well.
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
New in version 1.1: support for CONCURRENTLY on DROP INDEX. The CONCURRENTLY keyword is now only emitted if a high enough version of PostgreSQL is detected on the connection (or for a connection-less dialect).
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
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=True)
, the UNIQUE INDEX is not returned
in Table.indexes
when it is detected as mirroring a
UniqueConstraint
in the Table.constraints
collection.
Changed in version 1.0.0: - Table
reflection now includes
UniqueConstraint
objects present in the Table.constraints
collection; the PostgreSQL backend will no longer include a “mirrored”
Index
construct in Table.indexes
if it is detected
as corresponding to a unique constraint.
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())
sqlalchemy.dialects.postgresql.base.
PGInspector
(conn)¶Bases: sqlalchemy.engine.reflection.Inspector
get_enums
(schema=None)¶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. |
---|
New in version 1.0.0.
get_foreign_table_names
(schema=None)¶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 a
relkind
value of f
.
New in version 1.0.0.
get_table_oid
(table_name, schema=None)¶Return the OID for the given table name.
get_view_names
(schema=None, include=('plain', 'materialized'))¶Return all view names in schema.
Parameters: |
|
---|
Several options for CREATE TABLE are supported directly by the PostgreSQL
dialect in conjunction with the Table
construct:
TABLESPACE
:
Table("some_table", metadata, ..., postgresql_tablespace='some_tablespace')
The above option is also available on the Index
construct.
ON COMMIT
:
Table("some_table", metadata, ..., postgresql_on_commit='PRESERVE ROWS')
WITH OIDS
:
Table("some_table", metadata, ..., postgresql_with_oids=True)
WITHOUT OIDS
:
Table("some_table", metadata, ..., postgresql_with_oids=False)
INHERITS
:
Table("some_table", metadata, ..., postgresql_inherits="some_supertable")
Table("some_table", metadata, ..., postgresql_inherits=("t1", "t2", ...))
.. versionadded:: 1.0.0
PARTITION BY
:
Table("some_table", metadata, ...,
postgresql_partition_by='LIST (part_column)')
.. versionadded:: 1.2.6
See also
The PostgreSQL dialect supports arrays, both as multidimensional column types as well as array literals:
postgresql.ARRAY
- ARRAY datatypepostgresql.array
- array literalpostgresql.array_agg()
- ARRAY_AGG SQL functionpostgresql.aggregate_order_by
- helper for PG’s ORDER BY aggregate
function syntax.The PostgreSQL dialect supports both JSON and JSONB datatypes, including psycopg2’s native support and support for all of PostgreSQL’s special operators:
The PostgreSQL HSTORE type as well as hstore literals are supported:
postgresql.HSTORE
- HSTORE datatypepostgresql.hstore
- hstore literalPostgreSQL 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:
postgresql.ENUM
- DDL and typing support for ENUM.PGInspector.get_enums()
- retrieve a listing of current ENUM typespostgresql.ENUM.create()
, postgresql.ENUM.drop()
- individual
CREATE and DROP commands for ENUM.The combination of ENUM and ARRAY is not directly supported by backend
DBAPIs at this time. In order to send and receive an ARRAY of ENUM,
use the following workaround type, which decorates the
postgresql.ARRAY
datatype.
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.
Similar to using ENUM, for an ARRAY of JSON/JSONB we need to render the appropriate CAST, however current psycopg2 drivers seem to handle the result for ARRAY of JSON automatically, so the type is simpler:
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))
)
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, DATE, \
DOUBLE_PRECISION, ENUM, FLOAT, HSTORE, INET, INTEGER, \
INTERVAL, JSON, JSONB, MACADDR, MONEY, NUMERIC, OID, REAL, SMALLINT, TEXT, \
TIME, TIMESTAMP, UUID, VARCHAR, INT4RANGE, INT8RANGE, NUMRANGE, \
DATERANGE, TSRANGE, TSTZRANGE, TSVECTOR
Types which are specific to PostgreSQL, or have PostgreSQL-specific construction arguments, are as follows:
sqlalchemy.dialects.postgresql.
aggregate_order_by
(target, *order_by)¶Bases: sqlalchemy.sql.expression.ColumnElement
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;
New in version 1.1.
Changed in version 1.2.13: - the ORDER BY argument may be multiple terms
See also
sqlalchemy.dialects.postgresql.
array
(clauses, **kw)¶Bases: sqlalchemy.sql.expression.Tuple
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 datatype
ARRAY
. The “inner” type of the array is inferred from
the values present, unless the type_
keyword argument is passed:
array(['foo', 'bar'], type_=CHAR)
Multidimensional arrays are produced by nesting array
constructs.
The dimensionality of the final ARRAY
type is calculated by
recursively adding the dimensions of the inner ARRAY
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
sqlalchemy.dialects.postgresql.
ARRAY
(item_type, as_tuple=False, dimensions=None, zero_indexes=False)¶Bases: sqlalchemy.types.ARRAY
PostgreSQL ARRAY type.
Changed in version 1.1: The postgresql.ARRAY
type is now
a subclass of the core types.ARRAY
type.
The postgresql.ARRAY
type is constructed in the same way
as the core types.ARRAY
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 postgresql.ARRAY
type provides all operations defined on the
core types.ARRAY
type, including support for “dimensions”,
indexed access, and simple matching such as
types.ARRAY.Comparator.any()
and
types.ARRAY.Comparator.all()
. postgresql.ARRAY
class also
provides PostgreSQL-specific methods for containment operations, including
postgresql.ARRAY.Comparator.contains()
postgresql.ARRAY.Comparator.contained_by()
, and
postgresql.ARRAY.Comparator.overlap()
, e.g.:
mytable.c.data.contains([1, 2])
The postgresql.ARRAY
type may not be supported on all
PostgreSQL DBAPIs; it is currently known to work on psycopg2 only.
Additionally, the postgresql.ARRAY
type does not work directly in
conjunction with the ENUM
type. For a workaround, see the
special type at Using ENUM with ARRAY.
Comparator
(expr)¶Bases: sqlalchemy.types.Comparator
Define comparison operations for ARRAY
.
Note that these operations are in addition to those provided
by the base types.ARRAY.Comparator
class, including
types.ARRAY.Comparator.any()
and
types.ARRAY.Comparator.all()
.
contained_by
(other)¶Boolean expression. Test if elements are a proper subset of the elements of the argument array expression.
contains
(other, **kwargs)¶Boolean expression. Test if elements are a superset of the elements of the argument array expression.
overlap
(other)¶Boolean expression. Test if array has elements in common with an argument array expression.
__init__
(item_type, as_tuple=False, dimensions=None, zero_indexes=False)¶Construct an ARRAY.
E.g.:
Column('myarray', ARRAY(Integer))
Arguments are:
Parameters: |
|
---|
sqlalchemy.dialects.postgresql.
array_agg
(*arg, **kw)¶PostgreSQL-specific form of array_agg
, ensures
return type is postgresql.ARRAY
and not
the plain types.ARRAY
, unless an explicit type_
is passed.
New in version 1.1.
sqlalchemy.dialects.postgresql.
Any
(other, arrexpr, operator=<built-in function eq>)¶A synonym for the ARRAY.Comparator.any()
method.
This method is legacy and is here for backwards-compatibility.
See also
sqlalchemy.dialects.postgresql.
All
(other, arrexpr, operator=<built-in function eq>)¶A synonym for the ARRAY.Comparator.all()
method.
This method is legacy and is here for backwards-compatibility.
See also
sqlalchemy.dialects.postgresql.
BIT
(length=None, varying=False)¶Bases: sqlalchemy.types.TypeEngine
sqlalchemy.dialects.postgresql.
BYTEA
(length=None)¶Bases: sqlalchemy.types.LargeBinary
__init__
(length=None)¶__init__()
method of LargeBinary
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. |
---|
sqlalchemy.dialects.postgresql.
CIDR
¶Bases: sqlalchemy.types.TypeEngine
sqlalchemy.dialects.postgresql.
DOUBLE_PRECISION
(precision=None, asdecimal=False, decimal_return_scale=None)¶Bases: sqlalchemy.types.Float
__init__
(precision=None, asdecimal=False, decimal_return_scale=None)¶__init__()
method of Float
Construct a Float.
Parameters: |
|
---|
sqlalchemy.dialects.postgresql.
ENUM
(*enums, **kw)¶Bases: sqlalchemy.types.NativeForEmulated
, sqlalchemy.types.Enum
PostgreSQL ENUM type.
This is a subclass of types.Enum
which includes
support for PG’s CREATE TYPE
and DROP TYPE
.
When the builtin type types.Enum
is used and the
Enum.native_enum
flag is left at its default of
True, the PostgreSQL backend will use a postgresql.ENUM
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 types.Enum
or postgresql.ENUM
in an “inline” fashion, the CREATE TYPE
and DROP TYPE
is emitted
corresponding to when the Table.create()
and Table.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 types.Enum
or
postgresql.ENUM
independently, and associate it with the
MetaData
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 the ENUM
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)
Changed in version 1.0.0: The PostgreSQL postgresql.ENUM
type
now behaves more strictly with regards to CREATE/DROP. A metadata-level
ENUM type will only be created and dropped at the metadata level,
not the table level, with the exception of
table.create(checkfirst=True)
.
The table.drop()
call will now emit a DROP TYPE for a table-level
enumerated type.
__init__
(*enums, **kw)¶Construct an ENUM
.
Arguments are the same as that of
types.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
that DROP TYPE is called when the table
is dropped. When False , no check
will be performed and no CREATE TYPE
or DROP TYPE is emitted, unless
create()
or drop()
are called directly.
Setting to False is helpful
when invoking a creation scheme to a SQL file
without access to the actual database -
the create() and
drop() methods can
be used to emit SQL to a target bind. |
---|
create
(bind=None, checkfirst=True)¶Emit CREATE TYPE
for this
ENUM
.
If the underlying dialect does not support PostgreSQL CREATE TYPE, no action is taken.
Parameters: |
|
---|
drop
(bind=None, checkfirst=True)¶Emit DROP TYPE
for this
ENUM
.
If the underlying dialect does not support PostgreSQL DROP TYPE, no action is taken.
Parameters: |
|
---|
sqlalchemy.dialects.postgresql.
HSTORE
(text_type=None)¶Bases: sqlalchemy.types.Indexable
, sqlalchemy.types.Concatenable
, sqlalchemy.types.TypeEngine
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
HSTORE.comparator_factory
.
For usage with the SQLAlchemy ORM, it may be desirable to combine
the usage of HSTORE
with MutableDict
dictionary
now part of the sqlalchemy.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 PostgreSQL hstore()
function.
Comparator
(expr)¶Bases: sqlalchemy.types.Comparator
, sqlalchemy.types.Comparator
Define comparison operations for HSTORE
.
array
()¶Text array expression. Returns array of alternating keys and values.
contained_by
(other)¶Boolean expression. Test if keys are a proper subset of the keys of the argument jsonb expression.
contains
(other, **kwargs)¶Boolean expression. Test if keys (or array) are a superset of/contained the keys of the argument jsonb expression.
defined
(key)¶Boolean expression. Test for presence of a non-NULL value for the key. Note that the key may be a SQLA expression.
delete
(key)¶HStore expression. Returns the contents of this hstore with the given key deleted. Note that the key may be a SQLA expression.
has_all
(other)¶Boolean expression. Test for presence of all keys in jsonb
has_any
(other)¶Boolean expression. Test for presence of any key in jsonb
has_key
(other)¶Boolean expression. Test for presence of a key. Note that the key may be a SQLA expression.
keys
()¶Text array expression. Returns array of keys.
matrix
()¶Text array expression. Returns array of [key, value] pairs.
slice
(array)¶HStore expression. Returns a subset of an hstore defined by array of keys.
vals
()¶Text array expression. Returns array of values.
__init__
(text_type=None)¶Construct a new HSTORE
.
Parameters: | text_type¶ – the type that should be used for indexed values.
Defaults to New in version 1.1.0. |
---|
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
.
Parameters: | dialect¶ – Dialect instance in use. |
---|
comparator_factory
¶alias of HSTORE.Comparator
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
.
Parameters: |
---|
sqlalchemy.dialects.postgresql.
hstore
(*args, **kwargs)¶Bases: sqlalchemy.sql.functions.GenericFunction
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 PostgreSQL HSTORE
datatype.
sqlalchemy.dialects.postgresql.
INET
¶Bases: sqlalchemy.types.TypeEngine
sqlalchemy.dialects.postgresql.
INTERVAL
(precision=None, fields=None)¶Bases: sqlalchemy.types.NativeForEmulated
, sqlalchemy.types._AbstractInterval
PostgreSQL INTERVAL type.
The INTERVAL type may not be supported on all DBAPIs. It is known to work on psycopg2 and not pg8000 or zxjdbc.
sqlalchemy.dialects.postgresql.
JSON
(none_as_null=False, astext_type=None)¶Bases: sqlalchemy.types.JSON
Represent the PostgreSQL JSON type.
This type is a specialization of the Core-level types.JSON
type. Be sure to read the documentation for types.JSON
for
important tips regarding treatment of NULL values and ORM use.
Changed in version 1.1: postgresql.JSON
is now a PostgreSQL-
specific specialization of the new types.JSON
type.
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
JSON.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
JSON.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'
Changed in version 1.1: The ColumnElement.cast()
operator on
JSON objects now requires that the JSON.Comparator.astext
modifier be called explicitly, if the cast works only from a textual
string.
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://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
.
Comparator
(expr)¶Bases: sqlalchemy.types.Comparator
Define comparison operations for JSON
.
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
__init__
(none_as_null=False, astext_type=None)¶Construct a JSON
type.
Parameters: |
|
---|
comparator_factory
¶alias of JSON.Comparator
sqlalchemy.dialects.postgresql.
JSONB
(none_as_null=False, astext_type=None)¶Bases: sqlalchemy.dialects.postgresql.json.JSON
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 by
JSON
, including the same behaviors for indexing operations.
It also adds additional operators specific to JSONB, including
JSONB.Comparator.has_key()
, JSONB.Comparator.has_all()
,
JSONB.Comparator.has_any()
, JSONB.Comparator.contains()
,
and JSONB.Comparator.contained_by()
.
Like the JSON
type, the JSONB
type does not detect
in-place changes when used with the ORM, unless the
sqlalchemy.ext.mutable
extension is used.
Custom serializers and deserializers
are shared with the JSON
class, using the json_serializer
and json_deserializer
keyword arguments. These must be specified
at the dialect level using create_engine()
. When using
psycopg2, the serializers are associated with the jsonb type using
psycopg2.extras.register_default_jsonb
on a per-connection basis,
in the same way that psycopg2.extras.register_default_json
is used
to register these handlers with the json type.
New in version 0.9.7.
See also
Comparator
(expr)¶Bases: sqlalchemy.dialects.postgresql.json.Comparator
Define comparison operations for JSON
.
contained_by
(other)¶Boolean expression. Test if keys are a proper subset of the keys of the argument jsonb expression.
contains
(other, **kwargs)¶Boolean expression. Test if keys (or array) are a superset of/contained the keys of the argument jsonb expression.
has_all
(other)¶Boolean expression. Test for presence of all keys in jsonb
has_any
(other)¶Boolean expression. Test for presence of any key in jsonb
has_key
(other)¶Boolean expression. Test for presence of a key. Note that the key may be a SQLA expression.
comparator_factory
¶alias of JSONB.Comparator
sqlalchemy.dialects.postgresql.
MACADDR
¶Bases: sqlalchemy.types.TypeEngine
sqlalchemy.dialects.postgresql.
MONEY
¶Bases: sqlalchemy.types.TypeEngine
Provide the PostgreSQL MONEY type.
New in version 1.2.
sqlalchemy.dialects.postgresql.
OID
¶Bases: sqlalchemy.types.TypeEngine
Provide the PostgreSQL OID type.
New in version 0.9.5.
sqlalchemy.dialects.postgresql.
REAL
(precision=None, asdecimal=False, decimal_return_scale=None)¶Bases: sqlalchemy.types.Float
The SQL REAL type.
__init__
(precision=None, asdecimal=False, decimal_return_scale=None)¶__init__()
method of Float
Construct a Float.
Parameters: |
|
---|
sqlalchemy.dialects.postgresql.
REGCLASS
¶Bases: sqlalchemy.types.TypeEngine
Provide the PostgreSQL REGCLASS type.
New in version 1.2.7.
sqlalchemy.dialects.postgresql.
TSVECTOR
¶Bases: sqlalchemy.types.TypeEngine
The postgresql.TSVECTOR
type implements the PostgreSQL
text search type TSVECTOR.
It can be used to do full text queries on natural language documents.
New in version 0.9.0.
See also
sqlalchemy.dialects.postgresql.
UUID
(as_uuid=False)¶Bases: sqlalchemy.types.TypeEngine
PostgreSQL UUID type.
Represents the UUID column type, interpreting data either as natively returned by the DBAPI or as Python uuid objects.
The UUID type may not be supported on all DBAPIs. It is known to work on psycopg2 and not pg8000.
The new range column types found in PostgreSQL 9.2 onwards are catered for by the following types:
sqlalchemy.dialects.postgresql.
INT4RANGE
¶Bases: sqlalchemy.dialects.postgresql.ranges.RangeOperators
, sqlalchemy.types.TypeEngine
Represent the PostgreSQL INT4RANGE type.
sqlalchemy.dialects.postgresql.
INT8RANGE
¶Bases: sqlalchemy.dialects.postgresql.ranges.RangeOperators
, sqlalchemy.types.TypeEngine
Represent the PostgreSQL INT8RANGE type.
sqlalchemy.dialects.postgresql.
NUMRANGE
¶Bases: sqlalchemy.dialects.postgresql.ranges.RangeOperators
, sqlalchemy.types.TypeEngine
Represent the PostgreSQL NUMRANGE type.
sqlalchemy.dialects.postgresql.
DATERANGE
¶Bases: sqlalchemy.dialects.postgresql.ranges.RangeOperators
, sqlalchemy.types.TypeEngine
Represent the PostgreSQL DATERANGE type.
sqlalchemy.dialects.postgresql.
TSRANGE
¶Bases: sqlalchemy.dialects.postgresql.ranges.RangeOperators
, sqlalchemy.types.TypeEngine
Represent the PostgreSQL TSRANGE type.
sqlalchemy.dialects.postgresql.
TSTZRANGE
¶Bases: sqlalchemy.dialects.postgresql.ranges.RangeOperators
, sqlalchemy.types.TypeEngine
Represent the PostgreSQL TSTZRANGE type.
The types above get most of their functionality from the following mixin:
sqlalchemy.dialects.postgresql.ranges.
RangeOperators
¶This mixin provides functionality for the Range Operators
listed in Table 9-44 of the postgres documentation for Range
Functions and Operators. It is used by all the range types
provided in the postgres
dialect and can likely be used for
any range types you create yourself.
No extra support is provided for the Range Functions listed in
Table 9-45 of the postgres documentation. For these, the normal
func()
object should be used.
comparator_factory
(expr)¶Bases: sqlalchemy.types.Comparator
Define comparison operations for range types.
__ne__
(other)¶Boolean expression. Returns true if two ranges are not equal
adjacent_to
(other)¶Boolean expression. Returns true if the range in the column is adjacent to the range in the operand.
contained_by
(other)¶Boolean expression. Returns true if the column is contained within the right hand operand.
contains
(other, **kw)¶Boolean expression. Returns true if the right hand operand, which can be an element or a range, is contained within the column.
not_extend_left_of
(other)¶Boolean expression. Returns true if the range in the column does not extend left of the range in the operand.
not_extend_right_of
(other)¶Boolean expression. Returns true if the range in the column does not extend right of the range in the operand.
overlaps
(other)¶Boolean expression. Returns true if the column overlaps (has points in common with) the right hand operand.
strictly_left_of
(other)¶Boolean expression. Returns true if the column is strictly left of the right hand operand.
strictly_right_of
(other)¶Boolean expression. Returns true if the column is strictly right of the right hand operand.
Warning
The range type DDL support should work with any PostgreSQL DBAPI
driver, however the data types returned may vary. If you are using
psycopg2
, it’s recommended to upgrade to version 2.5 or later
before using these column types.
When instantiating models that use these column types, you should pass
whatever data type is expected by the DBAPI driver you’re using for
the column type. For psycopg2
these are
psycopg2.extras.NumericRange
,
psycopg2.extras.DateRange
,
psycopg2.extras.DateTimeRange
and
psycopg2.extras.DateTimeTZRange
or the class you’ve
registered with psycopg2.extras.register_range
.
For example:
from psycopg2.extras import DateTimeRange
from sqlalchemy.dialects.postgresql import TSRANGE
class RoomBooking(Base):
__tablename__ = 'room_booking'
room = Column(Integer(), primary_key=True)
during = Column(TSRANGE())
booking = RoomBooking(
room=101,
during=DateTimeRange(datetime(2013, 3, 23), None)
)
SQLAlchemy supports PostgreSQL EXCLUDE constraints via the
ExcludeConstraint
class:
sqlalchemy.dialects.postgresql.
ExcludeConstraint
(*elements, **kw)¶Bases: sqlalchemy.schema.ColumnCollectionConstraint
A table-level EXCLUDE constraint.
Defines an EXCLUDE constraint as described in the postgres documentation.
__init__
(*elements, **kw)¶Create an ExcludeConstraint
object.
E.g.:
const = ExcludeConstraint(
(Column('period'), '&&'),
(Column('group'), '='),
where=(Column('group') != 'some group')
)
The constraint is normally embedded into the Table
construct
directly, or added later using append_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'
)
)
Parameters: |
|
---|
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', '&&')),
)
sqlalchemy.dialects.postgresql.dml.
insert
(table, values=None, inline=False, bind=None, prefixes=None, returning=None, return_defaults=False, **dialect_kw)¶Construct a new Insert
object.
This constructor is mirrored as a public API function; see insert()
for a full usage and argument description.
sqlalchemy.dialects.postgresql.dml.
Insert
(table, values=None, inline=False, bind=None, prefixes=None, returning=None, return_defaults=False, **dialect_kw)¶Bases: sqlalchemy.sql.expression.Insert
PostgreSQL-specific implementation of INSERT.
Adds methods for PG-specific syntaxes such as ON CONFLICT.
New in version 1.1.
excluded
¶Provide the excluded
namespace for an ON CONFLICT statement
PG’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.
See also
INSERT…ON CONFLICT (Upsert) - example of how
to use Insert.excluded
on_conflict_do_nothing
(constraint=None, index_elements=None, index_where=None)¶Specifies a DO NOTHING action for ON CONFLICT clause.
The constraint
and index_elements
arguments
are optional, but only one of these can be specified.
Parameters: |
|
---|
See also
on_conflict_do_update
(constraint=None, index_elements=None, index_where=None, set_=None, where=None)¶Specifies a DO UPDATE SET action for ON CONFLICT clause.
Either the constraint
or index_elements
argument is
required, but only one of these can be specified.
Parameters: |
|
---|
See also
Support for the PostgreSQL database via the psycopg2 driver.
Documentation and download information (if applicable) for psycopg2 is available at: http://pypi.python.org/pypi/psycopg2/
Connect String:
postgresql+psycopg2://user:password@host:port/dbname[?key=value&key=value...]
psycopg2-specific keyword arguments which are accepted by
create_engine()
are:
server_side_cursors
: Enable the usage of “server side cursors” for SQL
statements which support this feature. What this essentially means from a
psycopg2 point of view is that the cursor is created using a name, e.g.
connection.cursor('some name')
, which has the effect that result rows
are not immediately pre-fetched and buffered after statement execution, but
are instead left on the server and only retrieved as needed. SQLAlchemy’s
ResultProxy
uses special row-buffering
behavior when this feature is enabled, such that groups of 100 rows at a
time are fetched over the wire to reduce conversational overhead.
Note that the Connection.execution_options.stream_results
execution option is a more targeted
way of enabling this mode on a per-execution basis.
use_native_unicode
: Enable the usage of Psycopg2 “native unicode” mode
per connection. True by default.
See also
isolation_level
: This option, available for all PostgreSQL dialects,
includes the AUTOCOMMIT
isolation level when using the psycopg2
dialect.
See also
client_encoding
: sets the client encoding in a libpq-agnostic way,
using psycopg2’s set_client_encoding()
method.
See also
executemany_mode
, executemany_batch_page_size
,
executemany_values_page_size
: Allows use of psycopg2
extensions for optimizing “executemany”-stye queries. See the referenced
section below for details.
See also
use_batch_mode
: this is the previous setting used to affect “executemany”
mode and is now deprecated.
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")
See also
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.
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 a Connection
(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.
If None
or not set, the server_side_cursors
option of the
Engine
is used.
max_row_buffer
- when using stream_results
, an integer value that
specifies the maximum number of rows to buffer at a time. This is
interpreted by the BufferedRowResultProxy
, and if omitted the
buffer will grow to ultimately store 1000 rows at a time.
New in version 1.0.6.
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 multiple orders of magnitude.
SQLAlchemy allows this extension to be used for all executemany()
style
calls invoked by an Engine
when used with multiple parameter
sets, which includes the use of this feature both by the
Core as well as by the ORM for inserts of objects with non-autogenerated
primary key values, by adding the executemany_mode
flag to
create_engine()
:
engine = create_engine(
"postgresql+psycopg2://scott:tiger@host/dbname",
executemany_mode='batch')
Changed in version 1.3.7: - the use_batch_mode
flag has been superseded
by a new parameter executemany_mode
which provides support both for
psycopg2’s execute_batch
helper as well as the execute_values
helper.
Possible options for executemany_mode
include:
None
- By default, psycopg2’s extensions are not used, and the usual
cursor.executemany()
method is used when invoking batches of statements.'batch'
- Uses psycopg2.extras.execute_batch
so that multiple copies
of a SQL query, each one corresponding to a parameter set passed to
executemany()
, are joined into a single SQL string separated by a
semicolon. This is the same behavior as was provided by the
use_batch_mode=True
flag.'values'
- For Core insert()
constructs only (including those
emitted by the ORM automatically), the psycopg2.extras.execute_values
extension is used so that multiple parameter sets are grouped into a single
INSERT statement and joined together with multiple VALUES expressions. This
method requires that the string text of the VALUES clause inside the
INSERT statement is manipulated, so is only supported with a compiled
insert()
construct where the format is predictable. For all other
constructs, including plain textual INSERT statements not rendered by the
SQLAlchemy expression language compiler, the
psycopg2.extras.execute_batch
method is used. It is therefore important
to note that “values” mode implies that “batch” mode is also used for
all statements for which “values” mode does not apply.For both strategies, the executemany_batch_page_size
and
executemany_values_page_size
arguments control how many parameter sets
should be represented in each execution. Because “values” mode implies a
fallback down to “batch” mode for non-INSERT statements, there are two
independent page size arguments. For each, the default value of None
means
to use psycopg2’s defaults, which at the time of this writing are quite low at
100. For the execute_values
method, a number as high as 10000 may prove
to be performant, whereas for execute_batch
, as the number represents
full statements repeated, a number closer to the default of 100 is likely
more appropriate:
engine = create_engine(
"postgresql+psycopg2://scott:tiger@host/dbname",
executemany_mode='values',
executemany_values_page_size=10000, executemany_batch_page_size=500)
See also
Executing Multiple Statements - General information on using the
Connection
object to execute statements in such a way as to make
use of the DBAPI .executemany()
method.
Changed in version 1.3.7: - Added support for
psycopg2.extras.execute_values
. The use_batch_mode
flag is
superseded by the executemany_mode
flag.
By default, the psycopg2 driver uses the psycopg2.extensions.UNICODE
extension, such that the DBAPI receives and returns all strings as Python
Unicode objects directly - SQLAlchemy passes these values through without
change. Psycopg2 here will encode/decode string values based on the
current “client encoding” setting; by default this is the value in
the postgresql.conf
file, which often defaults to SQL_ASCII
.
Typically, this can be changed to utf8
, as a more useful default:
# postgresql.conf file
# client_encoding = sql_ascii # actually, defaults to database
# encoding
client_encoding = utf8
A second way to affect the client encoding is to set it within Psycopg2
locally. SQLAlchemy will call psycopg2’s
psycopg2:connection.set_client_encoding()
method
on all new connections based on the value passed to
create_engine()
using the client_encoding
parameter:
# set_client_encoding() setting;
# works for *all* PostgreSQL versions
engine = create_engine("postgresql://user:pass@host/dbname",
client_encoding='utf8')
This overrides the encoding specified in the PostgreSQL client configuration.
When using the parameter in this way, the psycopg2 driver emits
SET client_encoding TO 'utf8'
on the connection explicitly, and works
in all PostgreSQL versions.
Note that the client_encoding
setting as passed to create_engine()
is not the same as the more recently added client_encoding
parameter
now supported by libpq directly. This is enabled when client_encoding
is passed directly to psycopg2.connect()
, and from SQLAlchemy is passed
using the create_engine.connect_args
parameter:
engine = create_engine(
"postgresql://user:pass@host/dbname",
connect_args={'client_encoding': 'utf8'})
# using the query string is equivalent
engine = create_engine("postgresql://user:pass@host/dbname?client_encoding=utf8")
The above parameter was only added to libpq as of version 9.1 of PostgreSQL, so using the previous method is better for cross-version support.
SQLAlchemy can also be instructed to skip the usage of the psycopg2
UNICODE
extension and to instead utilize its own unicode encode/decode
services, which are normally reserved only for those DBAPIs that don’t
fully support unicode directly. Passing use_native_unicode=False
to
create_engine()
will disable usage of psycopg2.extensions.UNICODE
.
SQLAlchemy will instead encode data itself into Python bytestrings on the way
in and coerce from bytes on the way back,
using the value of the create_engine()
encoding
parameter, which
defaults to utf-8
.
SQLAlchemy’s own unicode encode/decode functionality is steadily becoming
obsolete as most DBAPIs now support unicode fully.
The default parameter style for the psycopg2 dialect is “pyformat”, where
SQL is rendered using %(paramname)s
style. This format has the limitation
that it does not accommodate the unusual case of parameter names that
actually contain percent or parenthesis symbols; as SQLAlchemy in many cases
generates bound parameter names based on the name of a column, the presence
of these characters in a column name can lead to problems.
There are two solutions to the issue of a schema.Column
that contains
one of these characters in its name. One is to specify the
schema.Column.key
for columns that have such names:
measurement = Table('measurement', metadata,
Column('Size (meters)', Integer, key='size_meters')
)
Above, an INSERT statement such as measurement.insert()
will use
size_meters
as the parameter name, and a SQL expression such as
measurement.c.size_meters > 10
will derive the bound parameter name
from the size_meters
key as well.
Changed in version 1.0.0: - SQL expressions will use Column.key
as the source of naming when anonymous bound parameters are created
in SQL expressions; previously, this behavior only applied to
Table.insert()
and Table.update()
parameter names.
The other solution is to use a positional format; psycopg2 allows use of the
“format” paramstyle, which can be passed to
create_engine.paramstyle
:
engine = create_engine(
'postgresql://scott:tiger@localhost:5432/test', paramstyle='format')
With the above engine, instead of a statement like:
INSERT INTO measurement ("Size (meters)") VALUES (%(Size (meters))s)
{'Size (meters)': 1}
we instead see:
INSERT INTO measurement ("Size (meters)") VALUES (%s)
(1, )
Where above, the dictionary style is converted into a tuple with positional style.
The psycopg2 dialect fully supports SAVEPOINT and two-phase commit operations.
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
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
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:
psycopg2.extras.HstoreAdapter.get_oids()
.
If this function returns a list of HSTORE identifiers, we then determine
that the HSTORE
extension is present.
This function is skipped if the version of psycopg2 installed is
less than version 2.4.use_native_hstore
flag is at its default of True
, and
we’ve detected that HSTORE
oids are available, the
psycopg2.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.
Support for the PostgreSQL database via the pg8000 driver.
Documentation and download information (if applicable) for pg8000 is available at: https://pythonhosted.org/pg8000/
Connect String:
postgresql+pg8000://user:password@host:port/dbname[?key=value&key=value...]
Note
The pg8000 dialect is not tested as part of SQLAlchemy’s continuous integration and may have unresolved issues. The recommended PostgreSQL dialect is psycopg2.
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')
Support for the PostgreSQL database via the psycopg2cffi driver.
Documentation and download information (if applicable) for psycopg2cffi is available at: http://pypi.python.org/pypi/psycopg2cffi/
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
.
New in version 1.0.0.
Support for the PostgreSQL database via the py-postgresql driver.
Documentation and download information (if applicable) for py-postgresql is available at: http://python.projects.pgfoundry.org/
Connect String:
postgresql+pypostgresql://user:password@host:port/dbname[?key=value&key=value...]
Note
The pypostgresql dialect is not tested as part of SQLAlchemy’s continuous integration and may have unresolved issues. The recommended PostgreSQL driver is psycopg2.
Support for the PostgreSQL database via the pygresql driver.
Documentation and download information (if applicable) for pygresql is available at: http://www.pygresql.org/
Connect String:
postgresql+pygresql://user:password@host:port/dbname[?key=value&key=value...]
Note
The pygresql dialect is not tested as part of SQLAlchemy’s continuous integration and may have unresolved issues. The recommended PostgreSQL dialect is psycopg2.
Support for the PostgreSQL database via the zxJDBC for Jython driver.
Drivers for this database are available at: http://jdbc.postgresql.org/