PostgreSQL¶

Support for the PostgreSQL database.

The following table summarizes current support levels for database release versions.

**Supported PostgreSQL versions**¶
Support type	Versions
Supported version	9.6+
Best effort	9+

DBAPI Support¶

The following dialect/DBAPI options are available. Please refer to individual DBAPI sections for connect information.

psycopg2
psycopg (a.k.a. psycopg 3)
pg8000
asyncpg
psycopg2cffi

序列/SERIAL/IDENTITY¶

Sequences/SERIAL/IDENTITY

中文

PostgreSQL 支持序列（sequences），SQLAlchemy 将其作为整型主键列生成新主键值的默认方式。在创建表时，SQLAlchemy 会为整型主键列发出 SERIAL 数据类型，该类型在数据库端自动生成一个序列及其默认值。

要为主键生成指定名称的序列，可以使用 Sequence() 构造函数指定序列名称，如下所示:

Table(
    "sometable",
    metadata,
    Column(
        "id", Integer, Sequence("some_id_seq", start=1), primary_key=True
    ),
)

当 SQLAlchemy 执行一条单独的 INSERT 语句时，为了满足返回“最后插入的标识符”的需求，会在该语句中添加一个 RETURNING 子句，用于在语句执行完成后返回主键列。此 RETURNING 功能仅在 PostgreSQL 8.2 及更高版本中可用。作为后备方案，无论是显式指定还是通过 SERIAL 隐式生成的序列，都会在插入语句之前单独执行一次以获取主键值，然后再进行插入操作。需要注意的是，当使用“executemany”语义执行 insert() 构造时，将不会启用“最后插入标识符”功能：此时不会添加 RETURNING 子句，也不会预先执行序列。

英文

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 及以上版本的 IDENTITY 列¶

PostgreSQL 10 and above IDENTITY columns

中文

从 PostgreSQL 10 开始，新增了 IDENTITY 特性，可替代传统的 SERIAL 用法。在 Column 中使用 Identity 构造即可控制其行为:

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),
)

上述 Table 对象所生成的 CREATE TABLE 语句如下：

CREATE TABLE data (
    id INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 42 CYCLE),
    data VARCHAR,
    PRIMARY KEY (id)
)

在 1.4 版本发生变更: 在 Column 中新增 Identity 构造，以支持自动递增列的配置。

备注

在早期版本中，SQLAlchemy 并不内建对 IDENTITY 的渲染支持，可以通过以下编译钩子将 SERIAL 替换为 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

使用上述方法，诸如以下的表定义:

t = Table(
    "t", m, Column("id", Integer, primary_key=True), Column("data", String)
)

将在底层数据库中生成如下 SQL 语句：

CREATE TABLE t (
    id INT GENERATED BY DEFAULT AS IDENTITY,
    data VARCHAR,
    PRIMARY KEY (id)
)

英文

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)
)

在 1.4 版本发生变更: Added Identity construct in a Column to specify the option of an autoincrementing column.

备注

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

中文

服务器端游标支持适用于 psycopg2、asyncpg 方言，并且可能在其他方言中也可用。

服务器端游标是按每个语句启用的，可以通过使用 Connection.execution_options.stream_results 连接执行选项来启用:

with engine.connect() as conn:
    result = conn.execution_options(stream_results=True).execute(
        text("select * from table")
    )

请注意，某些类型的 SQL 语句可能不支持服务器端游标；通常，仅返回行的 SQL 语句应与此选项一起使用。

自 1.4 版本弃用: 方言级别的 server_side_cursors 标志已弃用，将在未来版本中移除。请使用 Connection.stream_results 执行选项来支持无缓冲游标。

参见

使用服务器端游标（又称流结果）

英文

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.

自 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

中文

大多数 SQLAlchemy 方言支持通过 create_engine.isolation_level 参数在 create_engine() 级别以及在 Connection 级别通过 Connection.execution_options.isolation_level 参数设置事务隔离级别。

对于 PostgreSQL 方言，此功能通过使用 DBAPI 特定的功能来实现，例如 psycopg2 的隔离级别标志，它将在 "BEGIN" 语句中内嵌隔离级别设置；对于没有直接支持的 DBAPI，则通过在 DBAPI 发出的 "BEGIN" 语句之前发出 SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL <level> 来实现。对于特殊的 AUTOCOMMIT 隔离级别，使用 DBAPI 特定的技术，通常是在 DBAPI 连接对象上设置 .autocommit 标志。

使用 create_engine() 设置隔离级别:

engine = create_engine(
    "postgresql+pg8000://scott:tiger@localhost/test",
    isolation_level="REPEATABLE READ",
)

使用每连接执行选项设置:

with engine.connect() as conn:
    conn = conn.execution_options(isolation_level="REPEATABLE READ")
    with conn.begin():
        ...  # work with transaction

还可以通过“子引擎”对象应用不同的隔离级别设置，这些对象与主 Engine 相关联。有关更多信息，请参见设置事务隔离级别（包括 DBAPI 自动提交）。

有效的 PostgreSQL 方言 isolation_level 值包括：

READ COMMITTED
READ UNCOMMITTED
REPEATABLE READ
SERIALIZABLE
AUTOCOMMIT

参见

设置事务隔离级别（包括 DBAPI 自动提交）

设置 READ ONLY / DEFERRABLE

Psycopg2 Transaction Isolation Level

pg8000 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 设置事务隔离级别（包括 DBAPI 自动提交） for background.

Valid values for isolation_level on most PostgreSQL dialects include:

READ COMMITTED
READ UNCOMMITTED
REPEATABLE READ
SERIALIZABLE
AUTOCOMMIT

参见

设置事务隔离级别（包括 DBAPI 自动提交）

设置 READ ONLY / DEFERRABLE

Psycopg2 Transaction Isolation Level

pg8000 Transaction Isolation Level

设置 READ ONLY / DEFERRABLE¶

Setting READ ONLY / DEFERRABLE

中文

大多数 PostgreSQL 方言支持设置事务的“只读”和“可推迟”特性，除了隔离级别设置之外。这两个属性可以与隔离级别一起设置，或者单独设置，方法是通过 Connection.execution_options() 传递 postgresql_readonly 和 postgresql_deferrable 标志。下面的示例演示了在设置“只读”和“可推迟”的同时，传递 "SERIALIZABLE" 隔离级别:

with engine.connect() as conn:
    conn = conn.execution_options(
        isolation_level="SERIALIZABLE",
        postgresql_readonly=True,
        postgresql_deferrable=True,
    )
    with conn.begin():
        ...  # work with transaction

请注意，某些 DBAPI（如 asyncpg）仅支持在 SERIALIZABLE 隔离级别下使用“只读”。

在 1.4 版本加入: 添加对 postgresql_readonly 和 postgresql_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.

在 1.4 版本加入: added support for the postgresql_readonly and postgresql_deferrable execution options.

连接池的临时表/资源重置¶

Temporary Table / Resource Reset for Connection Pooling

中文

SQLAlchemy Engine 对象使用的 QueuePool 连接池实现包括 reset on return 行为，当连接被返回到池中时，将调用 DBAPI 的 .rollback() 方法。虽然此回滚将清除上一个事务使用的即时状态，但它不会覆盖更广泛的会话级状态，包括临时表以及其他服务器状态，如预处理语句句柄和语句缓存。PostgreSQL 数据库包括多种命令，可用于重置这些状态，包括 DISCARD、RESET、DEALLOCATE 和 UNLISTEN。

要将这些命令之一作为执行重置的手段，可以使用 PoolEvents.reset() 事件钩子，如下所示。该实现将结束当前进行中的事务，并使用 CLOSE、 RESET 和 DISCARD 命令丢弃临时表；有关每个语句作用的背景，请参见 PostgreSQL 文档。

create_engine.pool_reset_on_return 参数设置为 None，以便自定义方案可以完全替换默认行为。自定义钩子实现无论如何都会调用 .rollback()，因为通常需要确保 DBAPI 对提交/回滚的跟踪与事务的状态一致:

from sqlalchemy import create_engine
from sqlalchemy import event

postgresql_engine = create_engine(
    "postgresql+pyscopg2://scott:tiger@hostname/dbname",
    # 禁用默认的重置返回方案
    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")

    # 确保 DBAPI 本身知道连接已被重置
    dbapi_connection.rollback()

在 2.0.0b3 版本发生变更: 添加了更多的状态参数到 PoolEvents.reset() 事件，并确保该事件在所有“重置”事件中被调用，因此它可以作为自定义“重置”处理程序的合适位置。之前使用 PoolEvents.checkin() 处理程序的方案仍然可以使用。

参见

返回时重置 - 在连接池文档中

英文

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()

在 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.

参见

返回时重置 - in the 连接池 documentation

连接时设置备用搜索路径¶

Setting Alternate Search Paths on Connect

中文

PostgreSQL 的 search_path 变量指的是在 SQL 语句中引用特定表或其他对象时，会隐式引用的 schema 名称列表。如下一节远程模式表自检和 PostgreSQL search_path 中详细介绍，SQLAlchemy 通常围绕将该变量保持在其默认值 public 的概念进行组织，然而，为了在使用连接时自动设置为任意名称或多个名称，可以通过以下事件处理程序为池中的所有连接调用 “SET SESSION search_path” 命令，如为新连接设置默认架构所讨论的那样:

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

之所以需要使用 .autocommit DBAPI 属性，是为了确保当调用 SET SESSION search_path 指令时，它会在事务的作用域之外执行，因此在 DBAPI 连接回滚时不会被撤销。

参见

为新连接设置默认架构 - 在使用元数据描述数据库文档中

英文

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 远程模式表自检和 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 为新连接设置默认架构:

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.

参见

为新连接设置默认架构 - in the 使用元数据描述数据库 documentation

远程模式表自检和 PostgreSQL search_path¶

Remote-Schema Table Introspection and PostgreSQL search_path

中文

总结最佳实践

保持 search_path 变量设置为默认的 public，并且不包括其他 schema 名称。确保用于连接的用户名不与远程 schema 名称匹配，或者确保从 search_path 中移除 "$user" 标记。对于其他 schema 名称，应在 Table 定义中明确指定它们。或者，postgresql_ignore_search_path 选项将导致所有反射的 Table 对象设置 Table.schema 属性。

PostgreSQL 方言可以从任何 schema 反射表，如反射来自其他架构的表所述。

在所有情况下，SQLAlchemy 在反射表时的第一步是 确定当前数据库连接的默认 schema。它通过 PostgreSQL 的 current_schema() 函数来实现，如下所示，使用 PostgreSQL 客户端会话（即使用 psql 工具）:

test=> select current_schema();
current_schema
----------------
public
(1 row)

如上所示，在 PostgreSQL 的简单安装中，默认的 schema 名称是 public。

然而，如果您的数据库用户名 与某个 schema 名称匹配 ，PostgreSQL 的默认行为是 将该名称作为默认 schema。以下是我们使用用户名 scott 登录并创建名为 scott 的 schema 时， 默认 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)

current_schema() 的行为来源于 PostgreSQL 的 search_path 变量，如现代版本的 PostgreSQL 中默认设置:

test=> show search_path;
search_path
-----------------
"$user", public
(1 row)

在上述示例中，"$user" 变量会将当前用户名作为默认 schema，如果存在的话。否则，使用 public。

当反射 Table 对象时，如果它存在于 current_schema() 函数指示的 schema 中，则该表的 ``.schema`` 属性将赋值为 Python 的 “None” 值。否则，.schema 属性将赋值为该 schema 的字符串名称。

对于这些 Table 对象通过外键约束所引用的表，在该远程 schema 名称也属于当前 search_path 的情况下，必须决定如何在这些远程表中表示 .schema。

默认情况下，PostgreSQL 方言模仿 PostgreSQL 自己的 pg_get_constraintdef() 内建过程的行为。此函数返回某个外键约束的示例定义，当引用的 schema 名称也在 PostgreSQL schema search path 中时，会省略该 schema 名称。下面的交互演示了这一行为:

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';
                pg_get_constraintdef
---------------------------------------------------
FOREIGN KEY (referred_id) REFERENCES referred(id)
(1 row)

如上所示，我们创建了 referred 表作为 test_schema 远程 schema 的成员，然而当我们将 test_schema 添加到 PG search_path 并请求 pg_get_constraintdef() 返回 FOREIGN KEY 语法时，test_schema 并未包含在该函数的输出中。

另一方面，如果我们将 search_path 设置回典型的默认值 public:

test=> SET search_path TO public;
SET

相同的查询对 pg_get_constraintdef() 返回的结果现在包含完整的 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';
                        pg_get_constraintdef
---------------------------------------------------------------
FOREIGN KEY (referred_id) REFERENCES test_schema.referred(id)
(1 row)

SQLAlchemy 默认情况下会使用 pg_get_constraintdef() 返回的值来确定远程 schema 名称。也就是说，如果我们的 search_path 设置为包括 test_schema，并且我们以如下方式调用表反射过程:

>>> 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>

上述过程会将 referred 表作为没有 schema 的名称存储:

>>> metadata_obj.tables["referred"].schema is None

True

若要改变反射的行为，以便不管 search_path 设置如何，仍保持引用的 schema 名称，请使用 postgresql_ignore_search_path 选项，该选项可以作为方言特定的参数指定给 Table 以及 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>

现在，我们将 test_schema.referred 存储为带有 schema 资格的名称:

>>> metadata_obj.tables["test_schema.referred"].schema
'test_schema'

参见

架构限定反射与默认架构的交互 - 从后端无关的角度讨论该问题

The Schema Search Path - 在 PostgreSQL 网站上。

英文

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 反射来自其他架构的表.

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'

参见

架构限定反射与默认架构的交互 - discussion of the issue from a backend-agnostic perspective

The Schema Search Path - on the PostgreSQL website.

INSERT/UPDATE…RETURNING¶

INSERT/UPDATE…RETURNING

中文

该方言支持 PostgreSQL 8.2 的 INSERT..RETURNING、UPDATE..RETURNING 和 DELETE..RETURNING 语法。默认情况下，INSERT..RETURNING 用于单行插入语句，以便获取新生成的主键标识符。要指定显式的 RETURNING 子句，可以在每个语句基础上使用 _UpdateBase.returning() 方法:

# 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())

英文

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())

全文搜索¶

Full Text Search

中文

PostgreSQL 的全文搜索系统可通过 func 命名空间结合 Operators.bool_op() 方法使用自定义操作符来实现。对于某些具有跨数据库兼容性的简单场景，也可以使用 Operators.match() 操作符。

英文

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.

使用 `match()` 进行简单的纯文本匹配¶

Simple plain text matching with match()

中文

Operators.match() 操作符提供了跨平台兼容的简单文本匹配功能。对于 PostgreSQL 后端，它被硬编码为使用 @@ 操作符结合 PostgreSQL 的 plainto_tsquery() 函数生成表达式。

在 PostgreSQL 方言中，以下表达式:

select(sometable.c.text.match("search string"))

将生成如下 SQL：

SELECT text @@ plainto_tsquery('search string') FROM table

如上所示，将普通字符串传递给 Operators.match() 时会自动使用 plainto_tsquery() 来指定 tsquery 的类型。这为 Operators.match() 提供了基本的数据库兼容性。

在 2.0 版本发生变更: PostgreSQL 方言在使用 Operators.match() 时默认使用的 tsquery 生成函数改为 plainto_tsquery()。

若要生成与 1.4 版本中相同的 SQL，可使用如下形式:

from sqlalchemy import func

select(sometable.c.text.bool_op("@@")(func.to_tsquery("search string")))

生成的 SQL 如下：

SELECT text @@ to_tsquery('search string') FROM table

英文

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.

在 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

直接使用 PostgreSQL 全文函数和运算符¶

Using PostgreSQL full text functions and operators directly

中文

除了简单使用 Operators.match() 外，更复杂的文本搜索操作可通过 func 命名空间来生成 PostgreSQL 的全文搜索函数，结合 Operators.bool_op() 生成任意布尔操作符。

例如，以下查询:

select(func.to_tsquery("cat").bool_op("@>")(func.to_tsquery("cat & rat")))

将生成如下 SQL：

SELECT to_tsquery('cat') @> to_tsquery('cat & rat')

TSVECTOR 类型可用于显式 CAST 操作:

from sqlalchemy.dialects.postgresql import TSVECTOR
from sqlalchemy import select, cast

select(cast("some text", TSVECTOR))

生成的 SQL 等价于：

SELECT CAST('some text' AS TSVECTOR) AS anon_1

PostgreSQL 方言扩展了 func 命名空间，为大多数全文搜索函数设置了正确的参数与返回类型。这些函数在使用 sqlalchemy.sql.expression.func 命名空间时会自动启用，前提是已导入 sqlalchemy.dialects.postgresql 包，或已使用 postgresql 方言调用了 create_engine()。这些函数包括：

to_tsvector
to_tsquery
plainto_tsquery
phraseto_tsquery
websearch_to_tsquery
ts_headline

英文

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:

to_tsvector
to_tsquery
plainto_tsquery
phraseto_tsquery
websearch_to_tsquery
ts_headline

使用 `match()` 或自定义运算符指定“regconfig”¶

Specifying the “regconfig” with match() or custom operators

中文

PostgreSQL 的 plainto_tsquery() 函数支持一个可选的 “regconfig” 参数，该参数用于指定 PostgreSQL 使用某个预计算的 GIN 或 GiST 索引来执行搜索。当使用 Operators.match() 时，可以通过 postgresql_regconfig 参数传递此额外参数，例如:

select(mytable.c.id).where(
    mytable.c.title.match("somestring", postgresql_regconfig="english")
)

将生成如下 SQL：

SELECT mytable.id FROM mytable
WHERE mytable.title @@ plainto_tsquery('english', 'somestring')

当使用 func 进行其他 PostgreSQL 搜索函数调用时，可将 “regconfig” 参数作为第一个参数直接传递:

select(mytable.c.id).where(
    func.to_tsvector("english", mytable.c.title).bool_op("@@")(
        func.to_tsquery("english", "somestring")
    )
)

生成的 SQL 等价于：

SELECT mytable.id FROM mytable
WHERE to_tsvector('english', mytable.title) @@
    to_tsquery('english', 'somestring')

建议使用 PostgreSQL 的 EXPLAIN ANALYZE... 工具，以确保你通过 SQLAlchemy 生成的查询能够充分利用为全文搜索创建的索引。

参见

Full Text Search - PostgreSQL 官方文档中的全文搜索章节

英文

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.

参见

Full Text Search - in the PostgreSQL documentation

FROM ONLY …¶

FROM ONLY …

中文

该方言支持 PostgreSQL 的 ONLY 关键字，用于在继承结构中仅针对某个特定的表。这可用于生成 SELECT ... FROM ONLY、UPDATE ONLY ... 以及 DELETE FROM ONLY ... 的语法形式。它通过 SQLAlchemy 的 hint 机制实现:

# 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")

英文

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 特定的索引选项¶

PostgreSQL-Specific Index Options

中文

若干扩展功能可用于 PostgreSQL 方言下的 Index 构造。

英文

Several extensions to the Index construct are available, specific to the PostgreSQL dialect.

覆盖索引¶

Covering Indexes

中文

postgresql_include 选项可为指定列名渲染 INCLUDE(colname) 语法:

Index("my_index", table.c.x, postgresql_include=["y"])

上述代码将渲染为： CREATE INDEX my_index ON table (x) INCLUDE (y)

请注意，此功能需要 PostgreSQL 11 或更高版本。

在 1.4 版本加入.

英文

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.

在 1.4 版本加入.

部分索引¶

Partial Indexes

中文

部分索引（Partial Index）可为索引定义添加条件，使该索引仅应用于部分行。可通过在 Index 上使用 postgresql_where 关键字参数指定:

Index("my_index", my_table.c.id, postgresql_where=my_table.c.value > 10)

英文

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 允许为索引的每个列指定 *操作符类*（operator class），详见： https://www.postgresql.org/docs/current/interactive/indexes-opclass.html。 Index 构造可通过 postgresql_ops 关键字参数来指定这些操作符类:

Index(
    "my_index",
    my_table.c.id,
    my_table.c.data,
    postgresql_ops={"data": "text_pattern_ops", "id": "int4_ops"},
)

请注意，postgresql_ops 字典中的键为 Column 的 “key” 名称，即用于通过 Table 的 .c 集合访问该列的名称，该名称可配置为不同于数据库中实际定义的列名。

如果要对函数调用等复杂 SQL 表达式使用 postgresql_ops，则必须为其指定一个标签（label），并在字典中以该标签名称为键指定操作符类，例如:

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"},
)

操作符类同样适用于 ExcludeConstraint 构造，可通过 ExcludeConstraint.ops 参数指定。详情请参阅该参数的说明。

英文

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.

索引类型¶

Index Types

中文

PostgreSQL 提供了多种索引类型：B-Tree、Hash、GiST 和 GIN，此外还支持用户自定义索引类型（参见 https://www.postgresql.org/docs/current/static/indexes-types.html）。这些类型可通过在 Index 上使用 postgresql_using 关键字参数指定:

Index("my_index", my_table.c.data, postgresql_using="gin")

传递给该关键字参数的值将直接传递至底层的 CREATE INDEX 命令，因此它必须是适用于当前 PostgreSQL 版本的有效索引类型。

英文

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 允许为索引设置存储参数。可用的存储参数取决于索引所使用的方法。可通过在 Index 上使用 postgresql_with 关键字参数指定这些参数:

Index("my_index", my_table.c.data, postgresql_with={"fillfactor": 50})

PostgreSQL 支持指定索引所在的表空间。可通过在 Index 上使用 postgresql_tablespace 关键字参数进行设置:

Index("my_index", my_table.c.data, postgresql_tablespace="my_tablespace")

注意， Table 也支持相同的选项。

英文

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

中文

PostgreSQL 的 CONCURRENTLY 索引选项可以通过在 Index 构造中传入 postgresql_concurrently 标志启用:

tbl = Table("testtbl", m, Column("data", Integer))

idx1 = Index("test_idx1", tbl.c.data, postgresql_concurrently=True)

上述索引构造在检测到 PostgreSQL 8.2 或更高版本时，或在无连接方言下，将生成如下 CREATE INDEX 的 DDL 语句：

CREATE INDEX CONCURRENTLY test_idx1 ON testtbl (data)

在 DROP INDEX 场景中，当检测到 PostgreSQL 9.2 或更高版本，或在无连接方言下，将生成如下语句：

DROP INDEX CONCURRENTLY test_idx1

使用 CONCURRENTLY 时，PostgreSQL 要求该语句必须在事务块之外执行。而 Python DBAPI 即使对单条语句也默认存在事务，因此要使用该构造，必须启用 DBAPI 的 “自动提交”（autocommit）模式:

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)

参见

事务隔离级别

英文

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)

参见

事务隔离级别

PostgreSQL 索引反射¶

PostgreSQL Index Reflection

中文

当使用 UNIQUE CONSTRAINT 构造时，PostgreSQL 数据库会隐式地创建一个 UNIQUE INDEX。在使用 Inspector 对表结构进行检查时，Inspector.get_indexes() 与 Inspector.get_unique_constraints() 会将这两个构造分别返回；如果某个索引被检测为与某个约束相对应，则其条目中将包含键 duplicates_constraint。在使用 Table(..., autoload_with=engine) 进行结构反射时，若某个 UNIQUE INDEX 被识别为与 Table.constraints 中的某个 UniqueConstraint 匹配，则该索引将不会出现在 Table.indexes 中。

英文

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

中文

用于 PostgreSQL 后端的 Inspector 实际是 PGInspector 的实例，后者提供了额外的方法:

from sqlalchemy import create_engine, inspect

engine = create_engine("postgresql+psycopg2://localhost/test")
insp = inspect(engine)  # 将会是 PGInspector 实例

print(insp.get_enums())

英文

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
PGInspector

class sqlalchemy.dialects.postgresql.base.PGInspector¶

Members

get_domains(), get_enums(), get_foreign_table_names(), get_table_oid(), has_type()

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 and check with the constraint text.

参数:: schema¶ – schema name. If None, the default schema (typically ‘public’) is used. May also be set to '*' to indicate load domains for all schemas.

在 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.

参数:: 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 a relkind value of f.

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.

参数:

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.

参数:

type_name¶ – the type to check.
schema¶ – schema name. If None, the default schema (typically ‘public’) is used. May also be set to '*' to check in all schemas.

在 2.0 版本加入.

PostgreSQL 表选项¶

PostgreSQL Table Options

中文

PostgreSQL 方言在结合 Table 构造使用时，支持多种 CREATE TABLE 选项：

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)",
)

TABLESPACE:

Table("some_table", metadata, ..., postgresql_tablespace="some_tablespace")

上述选项在 Index 构造中也可用。

USING:

Table("some_table", metadata, ..., postgresql_using="heap")

在 2.0.26 版本加入.

WITH OIDS:

Table("some_table", metadata, ..., postgresql_with_oids=True)

WITHOUT OIDS:

Table("some_table", metadata, ..., postgresql_with_oids=False)

参见

PostgreSQL CREATE TABLE options - 参见 PostgreSQL 文档。

英文

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)",
)

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")

在 2.0.26 版本加入.

WITH OIDS:

Table("some_table", metadata, ..., postgresql_with_oids=True)

WITHOUT OIDS:

Table("some_table", metadata, ..., postgresql_with_oids=False)

参见

PostgreSQL CREATE TABLE options - in the PostgreSQL documentation.

PostgreSQL 约束选项¶

PostgreSQL Constraint Options

中文

下列选项可与特定约束构造配合使用，由 PostgreSQL 方言支持：

NOT VALID：该选项适用于 CHECK 和 FOREIGN KEY 约束，在通过 ALTER TABLE 向已存在的表添加约束时使用，其效果是跳过已有数据行的扫描。

在使用如 Alembic 这类渲染 ALTER TABLE 构造的 SQL 迁移工具时，可以在创建约束的操作中，通过额外的关键字参数 postgresql_not_valid 指定该选项，例如如下 Alembic 示例:
```
def update():
    op.create_foreign_key(
        "fk_user_address",
        "address",
        "user",
        ["user_id"],
        ["id"],
        postgresql_not_valid=True,
    )
```
该关键字最终会被 CheckConstraint、ForeignKeyConstraint 与 ForeignKey 构造直接接收；在使用如 Alembic 等工具时，方言特有的关键字参数将从迁移操作指令中传递给这些构造:
```
CheckConstraint("some_field IS NOT NULL", postgresql_not_valid=True)

ForeignKeyConstraint(
    ["some_id"], ["some_table.some_id"], postgresql_not_valid=True
)
```
在 1.4.32 版本加入.

参见

PostgreSQL ALTER TABLE options - 参见 PostgreSQL 文档。

含外键 ON DELETE SET 操作的列列表：适用于 ForeignKey 和 ForeignKeyConstraint。参数 ForeignKey.ondelete 在 PostgreSQL 后端中可以接受一个列名字符串列表（置于括号中），结合 SET NULL 或 SET DEFAULT，从而限制哪些列会受到该动作的影响:

fktable = Table(
    "fktable",
    metadata,
    Column("tid", Integer),
    Column("id", Integer),
    Column("fk_id_del_set_null", Integer),
    ForeignKeyConstraint(
        columns=["tid", "fk_id_del_set_null"],
        refcolumns=[pktable.c.tid, pktable.c.id],
        ondelete="SET NULL (fk_id_del_set_null)",
    ),
)

在 2.0.40 版本加入.

英文

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 and ForeignKey constructs; when using a tool like Alembic, dialect-specific keyword arguments are passed through to these constructs from the migration operation directives:
```
CheckConstraint("some_field IS NOT NULL", postgresql_not_valid=True)

ForeignKeyConstraint(
    ["some_id"], ["some_table.some_id"], postgresql_not_valid=True
)
```
在 1.4.32 版本加入.

参见

PostgreSQL ALTER TABLE options - in the PostgreSQL documentation.

Column list with foreign key ON DELETE SET actions: This applies to ForeignKey and ForeignKeyConstraint, the ForeignKey.ondelete parameter will accept on the PostgreSQL backend only a string list of column names inside parenthesis, following the SET NULL or SET DEFAULT phrases, which will limit the set of columns that are subject to the action:

fktable = Table(
    "fktable",
    metadata,
    Column("tid", Integer),
    Column("id", Integer),
    Column("fk_id_del_set_null", Integer),
    ForeignKeyConstraint(
        columns=["tid", "fk_id_del_set_null"],
        refcolumns=[pktable.c.tid, pktable.c.id],
        ondelete="SET NULL (fk_id_del_set_null)",
    ),
)

在 2.0.40 版本加入.

表值、表值和列值函数、行和元组对象¶

Table values, Table and Column valued functions, Row and Tuple objects

中文

PostgreSQL 广泛使用现代 SQL 表达形式，如表值函数（table-valued functions）、表和行作为值。这些构造在 PostgreSQL 对复杂数据类型（如 JSON、ARRAY 及其他数据类型）的支持中被广泛应用。SQLAlchemy 的 SQL 表达语言原生支持大多数表值和行值的表达形式。

英文

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

中文

许多 PostgreSQL 内置函数被设计为在 SELECT 语句的 FROM 子句中使用，并可返回单行或多行结果。例如大量 JSON 函数如 json_array_elements()、json_object_keys()、json_each_text()、json_each()、json_to_record()、json_populate_recordset() 等都采用此形式。在 SQLAlchemy 中，这类 SQL 函数调用形式可通过 FunctionElement.table_valued() 方法与从 func 命名空间生成的 Function 对象配合使用。

以下示例摘自 PostgreSQL 官方文档：

json_each()：

>>> from sqlalchemy importselect,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_1

json_populate_record()：

>>> from sqlalchemy importselect,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 x

json_to_record() —— 该形式使用 PostgreSQL 特有的派生列语法，我们可使用带类型的 column() 元素来构建。FunctionElement.table_valued() 方法会生成一个 TableValuedAlias 构造，而 TableValuedAlias.render_derived() 方法设置派生列定义：

>>> from sqlalchemy importselect,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 —— 作为 SQL 标准的一部分，WITH ORDINALITY 为函数输出添加一个序号列，PostgreSQL 中仅部分函数（如 unnest() 和 generate_series()）支持此语法。FunctionElement.table_valued() 方法接受一个关键字参数 with_ordinality，其值为用于“序号”列的字符串名称：

>>> from sqlalchemy importselect,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)

在 1.4.0b2 版本加入.

参见

表值函数 - 参见 SQLAlchemy 统一教程中的相关章节。

英文

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 importselect,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_1

json_populate_record():

>>> from sqlalchemy importselect,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 x

json_to_record() - this form uses a PostgreSQL specific form of derived columns in the alias, where we may make use of column() elements with types to produce them. The FunctionElement.table_valued() method produces a TableValuedAlias construct, and the method TableValuedAlias.render_derived() method sets up the derived columns specification:

>>> from sqlalchemy importselect,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 including unnest() and generate_series(). The FunctionElement.table_valued() method accepts a keyword parameter with_ordinality for this purpose, which accepts the string name that will be applied to the “ordinality” column:
```
>>> 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)
```

在 1.4.0b2 版本加入.

参见

表值函数 - in the SQLAlchemy 统一教程

列值函数¶

Column Valued Functions

中文

与表值函数类似，列值函数也出现在 FROM 子句中，但它会以单个标量值的形式呈现到 SELECT 子句的列中。PostgreSQL 中的函数如 json_array_elements()、unnest() 和 generate_series() 可以使用此形式。列值函数可通过 FunctionElement 的 FunctionElement.column_valued() 方法使用：

json_array_elements()：

>>> from sqlalchemy importselect,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 x

unnest() —— 若要生成 PostgreSQL 的 ARRAY 字面量，可使用 array() 构造器：

>>> from sqlalchemy.dialects.postgresql importarray>>> from sqlalchemy importselect,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_1

当然，该函数也可以用于现有表绑定的列，该列的类型为 ARRAY：

>>> from sqlalchemy importtable,column,ARRAY,Integer>>> from sqlalchemy importselect,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

参见

列值函数 - 表值函数作为标量列 - 位于 SQLAlchemy 统一教程

英文

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 importselect,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 x

unnest() - in order to generate a PostgreSQL ARRAY literal, the array() construct may be used:

>>> from sqlalchemy.dialects.postgresql importarray>>> from sqlalchemy importselect,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_1

The function can of course be used against an existing table-bound column that’s of type ARRAY:

>>> from sqlalchemy importtable,column,ARRAY,Integer>>> from sqlalchemy importselect,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

参见

列值函数 - 表值函数作为标量列 - in the SQLAlchemy 统一教程

行类型¶

Row Types

中文

内建的 ROW 渲染支持可通过 func.ROW 和 sqlalchemy.func 命名空间模拟，或通过 tuple_() 构造器实现：

>>> from sqlalchemy importtable,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)

参见

PostgreSQL 行构造器（Row Constructors）

PostgreSQL 行构造器比较（Row Constructor Comparison）

英文

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 importtable,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)

参见

PostgreSQL Row Constructors

PostgreSQL Row Constructor Comparison

传递给函数的表类型¶

Table Types passed to Functions

中文

PostgreSQL 支持将一个表作为函数的参数传递，这种类型被称为 “record” 类型。SQLAlchemy 中的 FromClause 对象（如 Table ）通过 FromClause.table_valued() 方法支持这种特殊形式。这个方法类似于 FunctionElement.table_valued()，但列的集合由该 FromClause 本身预先定义：

>>> from sqlalchemy importtable,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

在 1.4.0b2 版本加入.

英文

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 importtable,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

在 1.4.0b2 版本加入.

ARRAY 类型¶

ARRAY Types

中文

PostgreSQL方言支持数组，既可以作为多维列类型，也可以作为数组文字：

ARRAY - ARRAY数据类型
array - 数组文字
array_agg() - ARRAY_AGG SQL函数
aggregate_order_by - PG的ORDER BY聚合函数语法的助手类。

英文

The PostgreSQL dialect supports arrays, both as multidimensional column types as well as array literals:

ARRAY - ARRAY datatype
array - array literal
array_agg() - ARRAY_AGG SQL function
aggregate_order_by - helper for PG’s ORDER BY aggregate function syntax.

JSON 类型¶

JSON Types

中文

PostgreSQL方言支持JSON和JSONB数据类型，包括psycopg2的原生支持以及所有PostgreSQL特殊运算符的支持：

英文

The PostgreSQL dialect supports both JSON and JSONB datatypes, including psycopg2’s native support and support for all of PostgreSQL’s special operators:

JSON
JSONB
JSONPATH

HSTORE 类型¶

HSTORE Type

中文

PostgreSQL的HSTORE类型以及hstore文字都受到支持：

HSTORE - HSTORE数据类型
hstore - hstore文字

英文

The PostgreSQL HSTORE type as well as hstore literals are supported:

HSTORE - HSTORE datatype
hstore - hstore literal

ENUM 类型¶

ENUM Types

中文

PostgreSQL具有一个独立创建的TYPE结构，用于实现枚举类型。这种方法在SQLAlchemy方面引入了在何时应该创建和删除此类型的显著复杂性。类型对象也是一个独立的可反映实体。应参考以下章节：

ENUM - ENUM的DDL和类型支持。
PGInspector.get_enums() - 检索当前ENUM类型的列表
ENUM.create() , ENUM.drop() - ENUM的单独CREATE和DROP命令。

英文

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 types
ENUM.create() , ENUM.drop() - individual CREATE and DROP commands for ENUM.

将 ENUM 与 ARRAY 结合使用¶

Using ENUM with ARRAY

中文

ENUM和ARRAY的组合目前不直接受后端DBAPI的支持。在SQLAlchemy 1.3.17之前，需要一个特殊的解决方法来允许这种组合工作，如下所述。

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

例如:

Table(
    "mydata",
    metadata,
    Column("id", Integer, primary_key=True),
    Column("data", ArrayOfEnum(ENUM("a", "b", "c", name="myenum"))),
)

这种类型不包含为内置类型，因为它与在新版本中突然决定直接支持ENUM的ARRAY的DBAPI不兼容。

英文

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.

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.

将 JSON/JSONB 与 ARRAY 结合使用¶

Using JSON/JSONB with ARRAY

中文

类似于使用ENUM，在SQLAlchemy 1.3.17之前，对于JSON/JSONB的ARRAY，我们需要渲染适当的CAST。当前的psycopg2驱动程序正确处理结果集，无需任何特殊步骤。

class CastingArray(ARRAY):
    def bind_expression(self, bindvalue):
        return sa.cast(bindvalue, self)

例如:

Table(
    "mydata",
    metadata,
    Column("id", Integer, primary_key=True),
    Column("data", CastingArray(JSONB)),
)

英文

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.

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范围和多范围类型支持psycopg、pg8000和asyncpg方言；psycopg2方言仅支持范围类型。

在 2.0.17 版本加入: 为pg8000方言添加了范围和多范围支持。需要pg8000 1.29.8或更高版本。

传递到数据库的数据值可以作为字符串值传递，也可以使用 Range 数据对象传递。

在 2.0 版本加入: 添加了用于指示范围的后端无关的 Range 对象。不再公开特定于``psycopg2``的范围类，仅由该特定方言内部使用。

例如，使用 TSRANGE 数据类型的完全类型化模型示例:

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)

要表示上述``during``列的数据，Range 类型是一个简单的数据类，将表示范围的边界。下面说明了如何将一行插入到上述``room_booking``表中:

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()

从任何范围列中选择也将返回 Range 对象，如下所示:

from sqlalchemy import select

with Session(engine) as session:
    for row in session.execute(select(RoomBooking.during)):
        print(row)

可用的范围数据类型如下：

英文

PostgreSQL range and multirange types are supported for the psycopg, pg8000 and asyncpg dialects; the psycopg2 dialect supports the range types only.

在 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.

在 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:

INT4RANGE
INT8RANGE
NUMRANGE
DATERANGE
TSRANGE
TSTZRANGE

Object Name	Description
Range	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.

参数:

lower¶ – Lower bound value, or None
upper¶ – Upper bound value, or None
bounds¶ – keyword-only, optional string value that is one of "()", "[)", "(]", "[]". Defaults to "[)".
empty¶ – keyword-only, optional bool indicating this is an “empty” range

在 2.0 版本加入.

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.

在 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在PostgreSQL 14及以上版本中受支持。SQLAlchemy的多范围数据类型处理 Range 类型的列表。

Multiranges仅支持psycopg、asyncpg和pg8000方言。SQLAlchemy的默认 postgresql 方言psycopg2不支持多范围数据类型。

在 2.0 版本加入: 添加了对MULTIRANGE数据类型的支持。SQLAlchemy将多范围值表示为 Range 对象的列表。

在 2.0.17 版本加入: 为pg8000方言添加了多范围支持。需要pg8000 1.29.8或更高版本。

在 2.0.26 版本加入: 添加了 MultiRange 序列。

下面的示例说明了 TSMULTIRANGE 数据类型的使用:

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)

说明插入和选择记录:

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}")

备注

在上述示例中，ORM处理的 Range 类型列表不会自动检测特定列表值的原地更改；要使用ORM更新列表值，可以重新分配一个新列表给该属性，或者使用 MutableList 类型修改器。有关背景信息，请参见章节突变追踪。

英文

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.

在 2.0 版本加入: Added support for MULTIRANGE datatypes. SQLAlchemy represents a multirange value as a list of Range objects.

在 2.0.17 版本加入: Added multirange support for the pg8000 dialect. pg8000 1.29.8 or greater is required.

在 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}")

备注

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 突变追踪 for background.

使用多范围序列推断多范围类型¶

Use of a MultiRange sequence to infer the multirange type

中文

在不指定类型的情况下使用多范围作为文字时，可以使用实用程序 MultiRange 序列:

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(2023, 3, 31)),
                    Range(datetime(2023, 7, 1), datetime(2023, 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}")

使用简单的 list 而不是 MultiRange 将需要手动将文字值的类型设置为适当的多范围类型。

在 2.0.26 版本加入: MultiRange 序列添加。

可用的多范围数据类型如下：

INT4MULTIRANGE
INT8MULTIRANGE
NUMMULTIRANGE
DATEMULTIRANGE
TSMULTIRANGE
TSTZMULTIRANGE

英文

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.

在 2.0.26 版本加入: MultiRange sequence added.

The available multirange datatypes are as follows:

INT4MULTIRANGE
INT8MULTIRANGE
NUMMULTIRANGE
DATEMULTIRANGE
TSMULTIRANGE
TSTZMULTIRANGE

网络数据类型¶

Network Data Types

中文

包含的网络数据类型有 INET，CIDR，MACADDR。

对于 INET 和 CIDR 数据类型，有条件支持这些数据类型发送和检索Python ipaddress 对象，包括 ipaddress.IPv4Network， ipaddress.IPv6Network ， ipaddress.IPv4Address ， ipaddress.IPv6Address 。此支持目前是 DBAPI本身的默认行为，并且因DBAPI而异。SQLAlchemy尚未实现自己的网络地址转换逻辑。

psycopg 和 asyncpg 完全支持这些数据类型；默认情况下，来自 ipaddress 系列的对象会在行中返回。
psycopg2 方言仅发送和接收字符串。
pg8000 方言支持 INET 数据类型的 ipaddress.IPv4Address 和 ipaddress.IPv6Address 对象，但对 CIDR 类型使用字符串。

要 将所有上述DBAPI标准化为只返回字符串，请使用 native_inet_types 参数，并传递值 False

e = create_engine(
    "postgresql+psycopg://scott:tiger@host/dbname", native_inet_types=False
)

使用上述参数， psycopg、 asyncpg 和 pg8000 方言将禁用DBAPI对这些类型的适配，并仅返回字符串，匹配旧的 psycopg2 方言的行为。

该参数也可以设置为 True ，此时对于那些不支持或尚未完全支持将行转换为Python ipaddress 数据类型的后端（目前是psycopg2和pg8000），将引发 NotImplementedError 。

英文

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 and ipaddress.IPv6Address objects for the INET datatype, but uses strings for CIDR 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).

在 2.0.18 版本加入: - added the native_inet_types parameter.

PostgreSQL 数据类型¶

PostgreSQL Data Types

中文

与所有SQLAlchemy方言一样，所有已知对PostgreSQL有效的UPPERCASE类型都可以从顶级方言中导入，无论它们是来自 sqlalchemy.types 还是来自本地方言:

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,
)

特定于PostgreSQL或具有PostgreSQL特定构造参数的类型如下：

英文

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
AbstractMultiRange	Base for PostgreSQL MULTIRANGE types.
AbstractRange	Base class for single and multi Range SQL types.
AbstractSingleRange	Base for PostgreSQL RANGE types.
ARRAY	PostgreSQL ARRAY type.
BIT
BYTEA
CIDR
CITEXT	Provide the PostgreSQL CITEXT type.
DATEMULTIRANGE	Represent the PostgreSQL DATEMULTIRANGE type.
DATERANGE	Represent the PostgreSQL DATERANGE type.
DOMAIN	Represent the DOMAIN PostgreSQL type.
ENUM	PostgreSQL ENUM type.
HSTORE	Represent the PostgreSQL HSTORE type.
INET
INT4MULTIRANGE	Represent the PostgreSQL INT4MULTIRANGE type.
INT4RANGE	Represent the PostgreSQL INT4RANGE type.
INT8MULTIRANGE	Represent the PostgreSQL INT8MULTIRANGE type.
INT8RANGE	Represent the PostgreSQL INT8RANGE type.
INTERVAL	PostgreSQL INTERVAL type.
JSON	Represent the PostgreSQL JSON type.
JSONB	Represent the PostgreSQL JSONB type.
JSONPATH	JSON Path Type.
MACADDR
MACADDR8
MONEY	Provide the PostgreSQL MONEY type.
MultiRange	Represents a multirange sequence.
NUMMULTIRANGE	Represent the PostgreSQL NUMMULTIRANGE type.
NUMRANGE	Represent the PostgreSQL NUMRANGE type.
OID	Provide the PostgreSQL OID type.
REGCLASS	Provide the PostgreSQL REGCLASS type.
REGCONFIG	Provide the PostgreSQL REGCONFIG type.
TIME	PostgreSQL TIME type.
TIMESTAMP	Provide the PostgreSQL TIMESTAMP type.
TSMULTIRANGE	Represent the PostgreSQL TSRANGE type.
TSQUERY	Provide the PostgreSQL TSQUERY type.
TSRANGE	Represent the PostgreSQL TSRANGE type.
TSTZMULTIRANGE	Represent the PostgreSQL TSTZRANGE type.
TSTZRANGE	Represent the PostgreSQL TSTZRANGE type.
TSVECTOR	The `TSVECTOR` type implements the PostgreSQL text search type TSVECTOR.

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.

class sqlalchemy.dialects.postgresql.AbstractSingleRange¶

Base for PostgreSQL RANGE types.

These are types that return a single Range object.

参见

PostgreSQL range functions

Class signature

class sqlalchemy.dialects.postgresql.AbstractSingleRange (sqlalchemy.dialects.postgresql.ranges.AbstractRange)

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 core 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 ARRAY type provides all operations defined on the core ARRAY type, including support for “dimensions”, indexed access, and simple matching such as Comparator.any() and Comparator.all(). ARRAY class also provides PostgreSQL-specific methods for containment operations, including Comparator.contains() Comparator.contained_by(), and Comparator.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 the ENUM type. For a workaround, see the special type at 将 ENUM 与 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, the sqlalchemy.ext.mutable extension must be used, using the MutableList 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.

参见

ARRAY - base array type

array - produces a literal array value.

Members

__init__(), contains(), contained_by(), overlap()

Class signature

class sqlalchemy.dialects.postgresql.ARRAY (sqlalchemy.types.ARRAY)

method sqlalchemy.dialects.postgresql.ARRAY.__init__(item_type: _TypeEngineArgument[_T], as_tuple: bool = False, dimensions: int | None = None, zero_indexes: bool = False)¶

Construct an ARRAY.

E.g.:

Column("myarray", ARRAY(Integer))

Arguments are:

参数:

item_type¶ – The data type of items of this array. Note that dimensionality is irrelevant here, so multi-dimensional arrays like INTEGER[][], are constructed as ARRAY(Integer), not as ARRAY(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, including Comparator.any() and Comparator.all().

Class signature

class sqlalchemy.dialects.postgresql.ARRAY.Comparator (sqlalchemy.types.Comparator)

method sqlalchemy.dialects.postgresql.ARRAY.Comparator.contains(other: typing_Any, **kwargs: typing_Any) → ColumnElement[bool]¶

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: typing_Any) → ColumnElement[bool]¶: 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: typing_Any) → ColumnElement[bool]¶: Boolean expression. Test if array has elements in common with an argument array expression.

class sqlalchemy.dialects.postgresql.BIT¶: Class signature

class sqlalchemy.dialects.postgresql.BIT (sqlalchemy.types.TypeEngine)

class sqlalchemy.dialects.postgresql.BYTEA¶

Members

__init__()

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 of LargeBinary

Construct a LargeBinary type.

参数:: 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.

class sqlalchemy.dialects.postgresql.CIDR¶: Class signature

class sqlalchemy.dialects.postgresql.CIDR (sqlalchemy.dialects.postgresql.types._NetworkAddressTypeMixin, sqlalchemy.types.TypeEngine)

class sqlalchemy.dialects.postgresql.CITEXT¶

Provide the PostgreSQL CITEXT type.

在 2.0.7 版本加入.

Members

__init__()

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 of String

Create a string-holding type.

参数:

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 a length for use in DDL, and will raise an exception when the CREATE TABLE DDL is issued if a VARCHAR 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_1
```
备注

In most cases, the Unicode or UnicodeText datatypes should be used for a Column that expects to store non-ascii data. These datatypes will ensure that the correct types are used on the database.

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

在 2.0 版本加入.

Members

__init__(), create(), drop()

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.

参数:

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 this DOMAIN will be directly associated
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.

method sqlalchemy.dialects.postgresql.DOMAIN.create(bind, checkfirst=True, **kw)¶

inherited from the NamedType.create() method of NamedType

Emit CREATE DDL for this type.

参数:

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 of NamedType

Emit DROP DDL for this type.

参数:

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.

class sqlalchemy.dialects.postgresql.DOUBLE_PRECISION

The SQL DOUBLE PRECISION type.

在 2.0 版本加入.

参见

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 of Float

Construct a Float.

参数:

precision¶ –
the numeric precision for use in DDL CREATE TABLE. Backends should attempt to ensure this precision indicates a number of digits for the generic Float datatype.
备注

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-specific FLOAT datatype and specify the FLOAT.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, use TypeEngine.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 to False. Note that setting this flag to True 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.

class sqlalchemy.dialects.postgresql.ENUM¶

PostgreSQL ENUM type.

This is a subclass of Enum which includes support for PG’s CREATE TYPE and DROP TYPE.

When the builtin type Enum is used and the Enum.native_enum flag is left at its default of True, the PostgreSQL backend will use a 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 Enum or 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 Enum or 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)

Members

__init__(), create(), drop()

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.

参数:: 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 ENUM.create() or ENUM.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 ENUM.create() and ENUM.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 this ENUM.

If the underlying dialect does not support PostgreSQL CREATE TYPE, no action is taken.

参数:

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 this ENUM.

If the underlying dialect does not support PostgreSQL DROP TYPE, no action is taken.

参数:

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.

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 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.

参见

hstore - render the PostgreSQL hstore() 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 sqlalchemy.dialects.postgresql.HSTORE.__init__(text_type=None)¶

Construct a new HSTORE.

参数:: text_type¶ – the type that should be used for indexed values. Defaults to Text.

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.

备注

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 alternate TypeEngine.bind_processor() method, unless subclassing the UserDefinedType class explicitly.

To provide alternate behavior for TypeEngine.bind_processor(), implement a TypeDecorator class and provide an implementation of TypeDecorator.process_bind_param().

参见

增强现有类型

参数:: dialect¶ – Dialect instance in use.

attribute sqlalchemy.dialects.postgresql.HSTORE.comparator_factory¶: 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.

备注

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 alternate TypeEngine.result_processor() method, unless subclassing the UserDefinedType class explicitly.

To provide alternate behavior for TypeEngine.result_processor(), implement a TypeDecorator class and provide an implementation of TypeDecorator.process_result_value().

参见

增强现有类型

参数:

dialect¶ – Dialect instance in use.
coltype¶ – DBAPI coltype argument received in cursor.description.

class sqlalchemy.dialects.postgresql.INET¶: Class signature

class sqlalchemy.dialects.postgresql.INET (sqlalchemy.dialects.postgresql.types._NetworkAddressTypeMixin, sqlalchemy.types.TypeEngine)

class sqlalchemy.dialects.postgresql.INTERVAL¶

PostgreSQL INTERVAL type.

Members

__init__()

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.

参数:

precision¶ – optional integer precision value
fields¶ – string fields specifier. allows storage of fields to be limited, such as "YEAR", "MONTH", "DAY TO HOUR", etc.

class sqlalchemy.dialects.postgresql.JSON¶

Represent the PostgreSQL JSON type.

JSON is used automatically whenever the base JSON datatype is used against a PostgreSQL backend, however base JSON datatype does not provide Python accessors for PostgreSQL-specific comparison methods such as Comparator.astext(); additionally, to use PostgreSQL JSONB, the JSONB datatype should be used explicitly.

参见

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.

参见

JSON - Core level JSON type

JSONB

Members

astext, __init__(), comparator_factory, render_bind_cast

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)

参见

ColumnElement.cast()

method sqlalchemy.dialects.postgresql.JSON.__init__(none_as_null: bool = False, astext_type: TypeEngine[str] | None = None)¶

Construct a JSON type.

参数:

none_as_null¶ –
if True, persist the value None as a SQL NULL value, not the JSON encoding of null. Note that when this flag is False, the null() construct can still be used to persist a NULL value:
```
from sqlalchemy import null

conn.execute(table.insert(), {"data": null()})
```
参见

JSON.NULL
astext_type¶ – the type to use for the Comparator.astext accessor on indexed attributes. Defaults to Text.

attribute sqlalchemy.dialects.postgresql.JSON.comparator_factory¶: 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.

在 2.0 版本加入.

参见

BindTyping

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 by JSON, including the same behaviors for indexing operations. It also adds additional operators specific to JSONB, including Comparator.has_key(), Comparator.has_all(), Comparator.has_any(), Comparator.contains(), Comparator.contained_by(), Comparator.delete_path(), Comparator.path_exists() and Comparator.path_match().

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.

参见

JSON

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: Any) → ColumnElement[bool]¶: 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: Any, **kwargs: Any) → ColumnElement[bool]¶

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: List[str] | _pg_array[str]) → ColumnElement[JSONB]¶

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().

在 2.0 版本加入.

method sqlalchemy.dialects.postgresql.JSONB.Comparator.has_all(other: Any) → ColumnElement[bool]¶: Boolean expression. Test for presence of all keys in jsonb (equivalent of the ?& operator)

method sqlalchemy.dialects.postgresql.JSONB.Comparator.has_any(other: Any) → ColumnElement[bool]¶: Boolean expression. Test for presence of any key in jsonb (equivalent of the ?| operator)

method sqlalchemy.dialects.postgresql.JSONB.Comparator.has_key(other: Any) → ColumnElement[bool]¶: 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: Any) → ColumnElement[bool]¶: Boolean expression. Test for presence of item given by the argument JSONPath expression (equivalent of the @? operator).

在 2.0 版本加入.

method sqlalchemy.dialects.postgresql.JSONB.Comparator.path_match(other: Any) → ColumnElement[bool]¶

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.

在 2.0 版本加入.

attribute sqlalchemy.dialects.postgresql.JSONB.comparator_factory¶: 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 or jsonb_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.dialects.postgresql.types._NetworkAddressTypeMixin, sqlalchemy.types.TypeEngine)

class sqlalchemy.dialects.postgresql.MACADDR8¶: Class signature

class sqlalchemy.dialects.postgresql.MACADDR8 (sqlalchemy.dialects.postgresql.types._NetworkAddressTypeMixin, 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())

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.

参见

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 of Float

Construct a Float.

参数:

precision¶ –
the numeric precision for use in DDL CREATE TABLE. Backends should attempt to ensure this precision indicates a number of digits for the generic Float datatype.
备注

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-specific FLOAT datatype and specify the FLOAT.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, use TypeEngine.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 to False. Note that setting this flag to True 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.

class sqlalchemy.dialects.postgresql.REGCONFIG¶: Provide the PostgreSQL REGCONFIG type.

在 2.0.0rc1 版本加入.

Class signature

class sqlalchemy.dialects.postgresql.REGCONFIG (sqlalchemy.types.TypeEngine)

class sqlalchemy.dialects.postgresql.REGCLASS¶: Provide the PostgreSQL REGCLASS type.

Class signature

class sqlalchemy.dialects.postgresql.REGCLASS (sqlalchemy.types.TypeEngine)

class sqlalchemy.dialects.postgresql.TIMESTAMP¶

Provide the PostgreSQL TIMESTAMP type.

Members

__init__()

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.

参数:

timezone¶ – boolean value if timezone present, default False
precision¶ –
optional integer precision value

在 1.4 版本加入.

class sqlalchemy.dialects.postgresql.TIME¶

PostgreSQL TIME type.

Members

__init__()

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.

参数:

timezone¶ – boolean value if timezone present, default False
precision¶ –
optional integer precision value

在 1.4 版本加入.

class sqlalchemy.dialects.postgresql.TSQUERY¶: Provide the PostgreSQL TSQUERY type.

在 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.

参见

全文搜索

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 of UUID.

The UUID datatype only works on databases that have a SQL datatype named UUID. 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’s UNIQUEIDENTIFIER datatype, use the Uuid datatype.

在 2.0 版本加入.

参见

Uuid

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.

参数:

as_uuid=True¶ –

if True, values will be interpreted as Python uuid objects, converting to/from string via the DBAPI.

在 2.0 版本发生变更: as_uuid now defaults to True.

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 signature

class sqlalchemy.dialects.postgresql.INT4MULTIRANGE (sqlalchemy.dialects.postgresql.ranges.AbstractMultiRange)

class sqlalchemy.dialects.postgresql.INT8MULTIRANGE¶: Represent the PostgreSQL INT8MULTIRANGE type.

Class signature

class sqlalchemy.dialects.postgresql.INT8MULTIRANGE (sqlalchemy.dialects.postgresql.ranges.AbstractMultiRange)

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 signature

class sqlalchemy.dialects.postgresql.DATEMULTIRANGE (sqlalchemy.dialects.postgresql.ranges.AbstractMultiRange)

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 signature

class sqlalchemy.dialects.postgresql.TSTZMULTIRANGE (sqlalchemy.dialects.postgresql.ranges.AbstractMultiRange)

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)])))

在 2.0.26 版本加入.

参见

使用多范围序列推断多范围类型.

Class signature

class sqlalchemy.dialects.postgresql.MultiRange (builtins.list, typing.Generic)

PostgreSQL SQL 元素和函数¶

PostgreSQL SQL Elements and Functions

Object Name	Description
aggregate_order_by	Represent a PostgreSQL aggregate order by expression.
All(other, arrexpr[, operator])	A synonym for the ARRAY-level `Comparator.all()` method. See that method for details.
Any(other, arrexpr[, operator])	A synonym for the ARRAY-level `Comparator.any()` method. See that method for details.
array	A PostgreSQL ARRAY literal.
array_agg(arg, *kw)	PostgreSQL-specific form of `array_agg`, ensures return type is `ARRAY` and not the plain `ARRAY`, unless an explicit `type_` is passed.
distinct_on(*expr)	apply a DISTINCT_ON to a SELECT statement
hstore	Construct an hstore value within a SQL expression using the PostgreSQL `hstore()` function.
phraseto_tsquery	The PostgreSQL `phraseto_tsquery` SQL function.
plainto_tsquery	The PostgreSQL `plainto_tsquery` SQL function.
to_tsquery	The PostgreSQL `to_tsquery` SQL function.
to_tsvector	The PostgreSQL `to_tsvector` SQL function.
ts_headline	The PostgreSQL `ts_headline` SQL function.
websearch_to_tsquery	The PostgreSQL `websearch_to_tsquery` SQL function.

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;

参见

array_agg

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 datatype ARRAY. The “inner” type of the array is inferred from the values present, unless the array.type_ keyword argument is passed:

array(["foo", "bar"], type_=CHAR)

When constructing an empty array, the array.type_ argument is particularly important as PostgreSQL server typically requires a cast to be rendered for the inner type in order to render an empty array. SQLAlchemy’s compilation for the empty array will produce this cast so that:

stmt = array([], type_=Integer)
print(stmt.compile(dialect=postgresql.dialect()))

Produces:

ARRAY[]::INTEGER[]

As required by PostgreSQL for empty arrays.

在 2.0.40 版本加入: added support to render empty PostgreSQL array literals with a required cast.

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

参见

ARRAY

Members

__init__()

Class signature

class sqlalchemy.dialects.postgresql.array (sqlalchemy.sql.expression.ExpressionClauseList)

method sqlalchemy.dialects.postgresql.array.__init__(clauses: Iterable[_T], *, type_: _TypeEngineArgument[_T] | None = None, **kw: typing_Any)¶

Construct an ARRAY literal.

参数:

clauses¶ – iterable, such as a list, containing elements to be rendered in the array
type_¶ – optional type. If omitted, the type is inferred from the contents of the array.

function sqlalchemy.dialects.postgresql.array_agg(*arg, **kw)¶: PostgreSQL-specific form of array_agg, ensures return type is ARRAY and not the plain ARRAY, unless an explicit type_ is passed.

function sqlalchemy.dialects.postgresql.Any(other: typing_Any, arrexpr: _ColumnExpressionArgument[_T], operator: OperatorType = <built-in function eq>) → ColumnElement[bool]¶: A synonym for the ARRAY-level Comparator.any() method. See that method for details.

function sqlalchemy.dialects.postgresql.All(other: typing_Any, arrexpr: _ColumnExpressionArgument[_T], operator: OperatorType = <built-in function eq>) → ColumnElement[bool]¶: 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"]),
    )
)

参见

HSTORE - the PostgreSQL HSTORE datatype.

Members

inherit_cache, type

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 to False, 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.

参见

为自定义构造启用缓存支持 - General guideslines for setting the HasCacheKey.inherit_cache attribute for third-party or user defined SQL constructs.

attribute sqlalchemy.dialects.postgresql.hstore.type¶: HSTORE 的别名

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 of TSVECTOR.

Assuming the PostgreSQL dialect has been imported, either by invoking from sqlalchemy.dialects import postgresql, or by creating a PostgreSQL engine using create_engine("postgresql..."), to_tsvector will be used automatically when invoking sqlalchemy.func.to_tsvector(), ensuring the correct argument and return type handlers are used at compile and execution time.

在 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 of TSQUERY.

Assuming the PostgreSQL dialect has been imported, either by invoking from sqlalchemy.dialects import postgresql, or by creating a PostgreSQL engine using create_engine("postgresql..."), to_tsquery will be used automatically when invoking sqlalchemy.func.to_tsquery(), ensuring the correct argument and return type handlers are used at compile and execution time.

在 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 of TSQUERY.

Assuming the PostgreSQL dialect has been imported, either by invoking from sqlalchemy.dialects import postgresql, or by creating a PostgreSQL engine using create_engine("postgresql..."), plainto_tsquery will be used automatically when invoking sqlalchemy.func.plainto_tsquery(), ensuring the correct argument and return type handlers are used at compile and execution time.

在 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 of TSQUERY.

Assuming the PostgreSQL dialect has been imported, either by invoking from sqlalchemy.dialects import postgresql, or by creating a PostgreSQL engine using create_engine("postgresql..."), phraseto_tsquery will be used automatically when invoking sqlalchemy.func.phraseto_tsquery(), ensuring the correct argument and return type handlers are used at compile and execution time.

在 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 of TSQUERY.

Assuming the PostgreSQL dialect has been imported, either by invoking from sqlalchemy.dialects import postgresql, or by creating a PostgreSQL engine using create_engine("postgresql..."), websearch_to_tsquery will be used automatically when invoking sqlalchemy.func.websearch_to_tsquery(), ensuring the correct argument and return type handlers are used at compile and execution time.

在 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 of TEXT.

Assuming the PostgreSQL dialect has been imported, either by invoking from sqlalchemy.dialects import postgresql, or by creating a PostgreSQL engine using create_engine("postgresql..."), ts_headline will be used automatically when invoking sqlalchemy.func.ts_headline(), ensuring the correct argument and return type handlers are used at compile and execution time.

在 2.0.0rc1 版本加入.

Class signature

class sqlalchemy.dialects.postgresql.ts_headline (sqlalchemy.dialects.postgresql.ext._regconfig_fn)

function sqlalchemy.dialects.postgresql.distinct_on(*expr: _ColumnExpressionArgument[Any]) → DistinctOnClause¶

apply a DISTINCT_ON to a SELECT statement

e.g.:

stmt = select(tbl).ext(distinct_on(t.c.some_col))

this supersedes the previous approach of using select(tbl).distinct(t.c.some_col)) to apply a similar construct.

在 2.1 版本加入.

PostgreSQL 约束类型¶

PostgreSQL Constraint Types

中文

SQLAlchemy通过 ExcludeConstraint 类支持PostgreSQL EXCLUDE约束：

class sqlalchemy.dialects.postgresql.ExcludeConstraint¶

A table-level EXCLUDE constraint.

Defines an EXCLUDE constraint as described in the PostgreSQL documentation.

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 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",
        ops={"group": "my_operator_class"},
    )
)

The exclude constraint defined in this example requires the btree_gist extension, that can be created using the command CREATE EXTENSION btree_gist;.

参数:

*elements¶ –
A sequence of two tuples of the form (column, operator) where “column” is either a Column 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-hoc Column or column() object should be used. The column may also be a string SQL expression when passed as literal_column() or text()
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.

警告

The ExcludeConstraint.where argument to ExcludeConstraint 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.

参见

运算符类 - general description of how PostgreSQL operator classes are specified.

例如:

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 supports PostgreSQL EXCLUDE constraints via the ExcludeConstraint class:

class sqlalchemy.dialects.postgresql.ExcludeConstraint

A table-level EXCLUDE constraint.

Defines an EXCLUDE constraint as described in the PostgreSQL documentation.

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 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",
        ops={"group": "my_operator_class"},
    )
)

The exclude constraint defined in this example requires the btree_gist extension, that can be created using the command CREATE EXTENSION btree_gist;.

参数:

*elements¶ –
A sequence of two tuples of the form (column, operator) where “column” is either a Column 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-hoc Column or column() object should be used. The column may also be a string SQL expression when passed as literal_column() or text()
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.

警告

The ExcludeConstraint.where argument to ExcludeConstraint 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.

参见

运算符类 - general description of how PostgreSQL operator classes are specified.

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 构造¶

PostgreSQL DML Constructs

Object Name	Description
insert(table)	Construct a PostgreSQL-specific variant `Insert` construct.
Insert	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 a sqlalchemy.dialects.postgresql.Insert. This class is based on the dialect-agnostic Insert construct which may be constructed using the insert() function in SQLAlchemy Core.

The Insert construct includes additional methods Insert.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 the sqlalchemy.dialects.postgresql.insert() function.

Members

excluded, inherit_cache, on_conflict_do_nothing(), on_conflict_do_update()

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 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.

小技巧

The Insert.excluded attribute is an instance of ColumnCollection, which provides an interface the same as that of the Table.c collection described at 访问表和列. 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 as stmt.excluded["column name"] or stmt.excluded["values"]. See the docstring for ColumnCollection for further examples.

参见

INSERT…ON CONFLICT（更新插入） - example of how to use Insert.excluded

attribute sqlalchemy.dialects.postgresql.Insert.inherit_cache: bool | None = True¶

Indicate if this HasCacheKey instance should make use of the cache key generation scheme used by its immediate superclass.

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.

参见

为自定义构造启用缓存支持 - General guideslines for setting the HasCacheKey.inherit_cache attribute for third-party or user defined SQL constructs.

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.

参数:

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.

参见

INSERT…ON CONFLICT（更新插入）

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.

参数:

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 the SET actions to take.

在 1.4 版本加入: The Insert.on_conflict_do_update.set_ parameter supports Column objects from the target Table as keys.

警告

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 the Insert.on_conflict_do_update.set_ dictionary.
where¶ – Optional argument. An expression object representing a WHERE clause that restricts the rows affected by DO UPDATE SET. Rows not meeting the WHERE condition will not be updated (effectively a DO NOTHING for those rows).

参见

INSERT…ON CONFLICT（更新插入）

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 the AUTOCOMMIT 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 modern Connection.execution_options.isolation_level execution option, detailed at 设置事务隔离级别（包括 DBAPI 自动提交）.

参见

Psycopg2 Transaction Isolation Level

设置事务隔离级别（包括 DBAPI 自动提交）
client_encoding: sets the client encoding in a libpq-agnostic way, using psycopg2’s set_client_encoding() method.

参见

Unicode with Psycopg2
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.

参见

Psycopg2 Fast Execution Helpers

小技巧

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 自定义 DBAPI connect() 参数/连接例程.

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"
)

警告

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.

参见

PQconnectdbParams

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"
)

在 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.

参见

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.

参见

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 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. Defaults to False, meaning cursors are buffered by default.
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 BufferedRowCursorResult, and if omitted the buffer will grow to ultimately store 1000 rows at a time.

在 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 语句的“插入多个值”行为.

在 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, assuming create_engine.use_insertmanyvalues is left at its default value of True. 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, assuming create_engine.use_insertmanyvalues is left at its default value of True. Then, psycopg2’s execute_batch() handler is used for qualifying UPDATE and DELETE statements when executed with multiple parameter sets. When using this mode, the CursorResult.rowcount attribute will not contain a value for executemany-style executions against UPDATE and DELETE statements.

在 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,
)

参见

INSERT 语句的“插入多个值”行为 - background on “insertmanyvalues”

发送多个参数 - 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 underlying libpq PostgreSQL client library:
```
engine = create_engine(
    "postgresql+psycopg2://user:pass@host/dbname?client_encoding=utf8"
)
```
Alternatively, the above client_encoding value may be passed using create_engine.connect_args for programmatic establishment with libpq:
```
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 to create_engine():
```
engine = create_engine(
    "postgresql+psycopg2://user:pass@host/dbname", client_encoding="utf8"
)
```
小技巧

The above client_encoding parameter admittedly is very similar in appearance to usage of the parameter within the create_engine.connect_args dictionary; the difference above is that the parameter is consumed by psycopg2 and is passed to the database connection using SET client_encoding TO 'utf8'; in the previously mentioned style, the parameter is instead passed through psycopg2 and consumed by the libpq 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 事务隔离级别, 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

参见

事务隔离级别

pg8000 Transaction Isolation Level

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)

参见

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 the HSTORE 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 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.

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() with postgresql+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() with postgresql+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"
)

参见

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},
)

参见

Client-side-binding cursors

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...]

在 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

参见

事务隔离级别

Psycopg2 Transaction Isolation Level

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"
)

在 1.4 版本加入.

备注

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.

在 2.0.18 版本加入.

参见

Specifying multiple fallback hosts

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"
)

在 1.4.0b2 版本加入: Added prepared_statement_cache_size for asyncpg.

警告

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()}__",
    },
)

参见

https://github.com/MagicStack/asyncpg/issues/837

https://github.com/sqlalchemy/sqlalchemy/issues/6467

警告

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"}},
)

参见

https://github.com/MagicStack/asyncpg/issues/727

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.

参见

sqlalchemy.dialects.postgresql.psycopg2

PostgreSQL¶

DBAPI Support¶

序列/SERIAL/IDENTITY¶

PostgreSQL 10 及以上版本的 IDENTITY 列¶

服务器端游标¶

事务隔离级别¶

设置 READ ONLY / DEFERRABLE¶

连接池的临时表/资源重置¶

连接时设置备用搜索路径¶

远程模式表自检和 PostgreSQL search_path¶

INSERT/UPDATE…RETURNING¶

INSERT…ON CONFLICT（更新插入）¶

指定目标¶

SET 子句¶

使用排除的 INSERT 值进行更新¶

附加 WHERE 条件¶

使用 DO NOTHING 跳过行¶

全文搜索¶

使用 match() 进行简单的纯文本匹配¶

直接使用 PostgreSQL 全文函数和运算符¶

使用 match() 或自定义运算符指定“regconfig”¶

FROM ONLY …¶

PostgreSQL 特定的索引选项¶

覆盖索引¶

部分索引¶

运算符类¶

索引类型¶

索引存储参数¶

索引并发¶

PostgreSQL 索引反射¶

特殊反射选项¶

PostgreSQL 表选项¶

PostgreSQL 约束选项¶

表值、表值和列值函数、行和元组对象¶

表值函数¶

列值函数¶

行类型¶

传递给函数的表类型¶

ARRAY 类型¶

JSON 类型¶

HSTORE 类型¶

ENUM 类型¶

将 ENUM 与 ARRAY 结合使用¶

将 JSON/JSONB 与 ARRAY 结合使用¶

范围和多范围类型¶

多范围¶

使用多范围序列推断多范围类型¶

网络数据类型¶

PostgreSQL 数据类型¶

PostgreSQL SQL 元素和函数¶

PostgreSQL 约束类型¶

PostgreSQL DML 构造¶

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¶

DBAPI¶

Connecting¶

Using a different Cursor class¶

pg8000¶

DBAPI¶

Connecting¶

Unicode¶

SSL Connections¶

pg8000 Transaction Isolation Level¶

asyncpg¶

DBAPI¶

Connecting¶

使用 `match()` 进行简单的纯文本匹配¶

使用 `match()` 或自定义运算符指定“regconfig”¶