PostgreSQL

支持 PostgreSQL 数据库。

下表总结了当前对数据库发布版本的支持级别。

支持的 PostgreSQL 版本

支持类型	版本
支持版本	9.6+
尽力而为	9+

DBAPI 支持

以下方言/DBAPI 选项可用。请参考各个 DBAPI 部分以获取连接信息。

• psycopg2

• psycopg (也称为 psycopg 3)

• pg8000

• asyncpg

• psycopg2cffi

序列/SERIAL/IDENTITY

PostgreSQL 支持序列，SQLAlchemy 使用这些序列作为为基于整数的主键列创建新主键值的默认方法。创建表时，SQLAlchemy 将为基于整数的主键列发出 SERIAL 数据类型，该类型会生成一个序列和与该列对应的服务器端默认值。

要指定用于主键生成的特定命名序列，请使用 Sequence() 结构

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

当 SQLAlchemy 发出一个 INSERT 语句时，为了满足具有“最后插入标识符”的合约，会在 INSERT 语句中添加一个 RETURNING 子句，该子句指定语句完成后应返回主键列。RETURNING 功能只有在使用 PostgreSQL 8.2 或更高版本时才会生效。作为备用方法，序列（无论是显式指定还是通过 SERIAL 隐式指定）都会事先独立执行，返回的值将在随后的插入中使用。请注意，当 insert() 结构使用“executemany”语义执行时，“最后插入标识符”功能不适用；在这种情况下不会发出 RETURNING 子句，也不会预先执行序列。

PostgreSQL 10 及更高版本 IDENTITY 列

PostgreSQL 10 及更高版本具有一个新的 IDENTITY 功能，它取代了 SERIAL 的使用。 Identity 结构在 Column 中可以使用，以控制其行为

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 中的变更: 在 Identity 结构中添加了 Column 以指定自增列的选项。

注意

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

将在后备数据库中生成为

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

服务器端游标

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 执行选项来支持非缓冲游标。

另请参阅

使用服务器端游标（也称为流式结果）

事务隔离级别

大多数 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 事务隔离级别

pg8000 事务隔离级别

设置 READ ONLY / DEFERRABLE

大多数 PostgreSQL 方言支持设置事务的“READ ONLY”和“DEFERRABLE”特性，这除了隔离级别设置。这两个属性可以通过使用 postgresql_readonly 和 postgresql_deferrable 标志与 Connection.execution_options() 一起设置，也可以独立设置。下面的示例说明了在设置“READ ONLY”和“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 隔离级别的“readonly”。

在版本 1.4 中添加： 添加了对 postgresql_readonly 和 postgresql_deferrable 执行选项的支持。

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

SQLAlchemy Engine 对象使用的 QueuePool 连接池实现包含 返回时重置 行为，这将调用 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",

    # 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 中更改： 向 PoolEvents.reset() 事件添加了其他状态参数，并额外确保为所有“重置”事件调用该事件，因此它适合作为自定义“重置”处理程序的位置。以前使用 PoolEvents.checkin() 处理程序的方案仍然可以使用。

另请参阅

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

连接时设置备用搜索路径

PostgreSQL search_path 变量是指当在 SQL 语句中引用特定表或其他对象时将隐式引用的模式名称列表。如下一节 远程模式表内省和 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 连接回滚时不会被恢复。

另请参阅

为新连接设置默认模式 - 在 使用 MetaData 描述数据库 文档中

远程模式表内省和 PostgreSQL search_path

最佳实践概述

将 search_path 变量设置为默认值 public，不包含其他模式名称。确保用于连接的用户名与远程模式不匹配，或确保 "$user" 令牌从 search_path 中 **移除**。对于其他模式名称，在 Table 定义中显式命名这些模式。或者，postgresql_ignore_search_path 选项将导致所有反射的 Table 对象具有一个设置为 Table.schema 属性。

PostgreSQL 方言可以反射来自任何模式的表，如 反射来自其他模式的表 中所述。

在所有情况下，SQLAlchemy 反射表时所做的第一件事是 **确定当前数据库连接的默认模式**。它使用 PostgreSQL current_schema() 函数来完成此操作，如下所示，使用 PostgreSQL 客户端会话（即使用 psql 工具）

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

上面我们看到，在 PostgreSQL 的纯安装中，默认模式名称是 public。

但是，如果您的数据库用户名 **与模式的名称匹配**，PostgreSQL 的默认行为是 **使用该名称作为默认模式**。下面，我们使用用户名 scott 登录。当我们创建一个名为 scott 的模式时，**它会隐式地更改默认模式**

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" 变量会将当前用户名注入为默认模式。否则，将使用 public。

当一个 Table 对象被反射时，如果它存在于 current_schema() 函数指示的模式中，**分配给 Table 的 “.schema” 属性的模式名称是 Python “None” 值**。否则，“.schema” 属性将被分配该模式的字符串名称。

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

默认情况下，PostgreSQL 方言会模仿 PostgreSQL 自身 pg_get_constraintdef() 内置过程所鼓励的行为。此函数返回特定外键约束的示例定义，当该名称也位于 PostgreSQL 模式搜索路径中时，会从该定义中省略引用的模式名称。下面的交互说明了这种行为

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)

上面，我们创建了一个名为 referred 的表作为远程模式 test_schema 的成员，但是当我们将 test_schema 添加到 PG search_path，然后向 pg_get_constraintdef() 请求 FOREIGN KEY 语法时，test_schema 没有包含在函数的输出中。

另一方面，如果我们将搜索路径设置回典型的默认值 public

test=> SET search_path TO public;
SET

对 pg_get_constraintdef() 的相同查询现在为我们返回完全限定的模式名称

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() 的返回值来确定远程模式名称。也就是说，如果我们的 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>

上述过程将向 MetaData.tables 集合提供名为 referred 的表，**没有** 模式

>>> metadata_obj.tables['referred'].schema is None
True

要更改反射的行为，使其无论 search_path 设置如何都维护引用的模式，请使用 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 存储为限定模式

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

PostgreSQL 模式反射的最佳实践

PostgreSQL 模式反射行为的描述很复杂，是多年来处理各种用例和用户偏好的结果。但实际上，如果您坚持使用最简单的使用模式，则无需了解其中任何内容：将 search_path 设置为其默认值 public，绝不要将 public 作为显式模式名称引用，并在构建 Table 对象时显式引用所有其他模式名称。此处描述的选项仅适用于无法或不愿遵循这些准则的用户。

另请参阅

限定模式反射与默认模式的交互 - 从后端无关的角度讨论这个问题

模式搜索路径 - 在 PostgreSQL 网站上。

INSERT/UPDATE…RETURNING

该方言支持 PG 8.2 的 INSERT..RETURNING、UPDATE..RETURNING 和 DELETE..RETURNING 语法。INSERT..RETURNING 默认情况下用于单行 INSERT 语句，以获取新生成的主键标识符。要指定显式的 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())

INSERT…ON CONFLICT (Upsert)

从 9.5 版本开始，PostgreSQL 允许通过 INSERT 语句的 ON CONFLICT 子句将行 “upsert” （更新或插入）到表中。候选行仅在该行不违反任何唯一约束时才被插入。在唯一约束违反的情况下，可能会发生辅助操作，该操作可以是 “DO UPDATE”，表示应更新目标行中的数据，也可以是 “DO NOTHING”，表示静默跳过该行。

冲突是使用现有的唯一约束和索引来确定的。这些约束可以使用它们在 DDL 中声明的名称来识别，或者可以通过声明构成索引的列和条件来推断。

SQLAlchemy 通过特定于 PostgreSQL 的 insert() 函数提供 ON CONFLICT 支持，该函数提供生成方法 Insert.on_conflict_do_update() 和 Insert.on_conflict_do_nothing()

>>> from sqlalchemy.dialects.postgresql import insert
>>> insert_stmt = insert(my_table).values(
...     id='some_existing_id',
...     data='inserted value')
>>> do_nothing_stmt = insert_stmt.on_conflict_do_nothing(
...     index_elements=['id']
... )
>>> print(do_nothing_stmt)
INSERT INTO my_table (id, data) VALUES (%(id)s, %(data)s)
ON CONFLICT (id) DO NOTHING
>>> do_update_stmt = insert_stmt.on_conflict_do_update(
...     constraint='pk_my_table',
...     set_=dict(data='updated value')
... )
>>> print(do_update_stmt)
INSERT INTO my_table (id, data) VALUES (%(id)s, %(data)s)
ON CONFLICT ON CONSTRAINT pk_my_table DO UPDATE SET data = %(param_1)s

另请参阅

INSERT .. ON CONFLICT - 在 PostgreSQL 文档中。

指定目标

两种方法都使用命名的约束或通过列推断来提供冲突的 “目标”

• Insert.on_conflict_do_update.index_elements 参数指定一个包含字符串列名称、Column 对象和/或 SQL 表达式元素的序列，这些元素将标识唯一索引

  >>> do_update_stmt = insert_stmt.on_conflict_do_update(
  ...     index_elements=['id'],
  ...     set_=dict(data='updated value')
  ... )
  >>> print(do_update_stmt)
  INSERT INTO my_table (id, data) VALUES (%(id)s, %(data)s)
  ON CONFLICT (id) DO UPDATE SET data = %(param_1)s
  >>> do_update_stmt = insert_stmt.on_conflict_do_update(
  ...     index_elements=[my_table.c.id],
  ...     set_=dict(data='updated value')
  ... )
  >>> print(do_update_stmt)
  INSERT INTO my_table (id, data) VALUES (%(id)s, %(data)s)
  ON CONFLICT (id) DO UPDATE SET data = %(param_1)s
  

• 当使用 Insert.on_conflict_do_update.index_elements 推断索引时，可以通过也指定使用 Insert.on_conflict_do_update.index_where 参数来推断部分索引

  >>> stmt = insert(my_table).values(user_email='[email protected]', data='inserted data')
  >>> stmt = stmt.on_conflict_do_update(
  ...     index_elements=[my_table.c.user_email],
  ...     index_where=my_table.c.user_email.like('%@gmail.com'),
  ...     set_=dict(data=stmt.excluded.data)
  ... )
  >>> print(stmt)
  INSERT INTO my_table (data, user_email)
  VALUES (%(data)s, %(user_email)s) ON CONFLICT (user_email)
  WHERE user_email LIKE %(user_email_1)s DO UPDATE SET data = excluded.data
  

• Insert.on_conflict_do_update.constraint 参数用于直接指定索引而不是推断它。这可以是 UNIQUE 约束、PRIMARY KEY 约束或 INDEX 的名称

  >>> do_update_stmt = insert_stmt.on_conflict_do_update(
  ...     constraint='my_table_idx_1',
  ...     set_=dict(data='updated value')
  ... )
  >>> print(do_update_stmt)
  INSERT INTO my_table (id, data) VALUES (%(id)s, %(data)s)
  ON CONFLICT ON CONSTRAINT my_table_idx_1 DO UPDATE SET data = %(param_1)s
  >>> do_update_stmt = insert_stmt.on_conflict_do_update(
  ...     constraint='my_table_pk',
  ...     set_=dict(data='updated value')
  ... )
  >>> print(do_update_stmt)
  INSERT INTO my_table (id, data) VALUES (%(id)s, %(data)s)
  ON CONFLICT ON CONSTRAINT my_table_pk DO UPDATE SET data = %(param_1)s
  

• 参数 Insert.on_conflict_do_update.constraint 也可以引用 SQLAlchemy 结构，表示约束，例如：UniqueConstraint，PrimaryKeyConstraint，Index 或者 ExcludeConstraint。在此使用情况下，如果约束有名称，则直接使用它。否则，如果约束没有名称，则会使用推断，其中约束的表达式和可选的 WHERE 子句将被拼写成构造。此用法特别方便，可以参考使用 Table.primary_key 属性的 Table 的已命名或未命名主键。

  >>> do_update_stmt = insert_stmt.on_conflict_do_update(
  ...     constraint=my_table.primary_key,
  ...     set_=dict(data='updated value')
  ... )
  >>> print(do_update_stmt)
  INSERT INTO my_table (id, data) VALUES (%(id)s, %(data)s)
  ON CONFLICT (id) DO UPDATE SET data = %(param_1)s
  

SET 子句

ON CONFLICT...DO UPDATE 用于执行对已存在行的更新，使用任何组合的新值以及来自提议插入的值。这些值使用 Insert.on_conflict_do_update.set_ 参数指定。此参数接受一个字典，该字典包含用于 UPDATE 的直接值。

>>> stmt = insert(my_table).values(id='some_id', data='inserted value')
>>> do_update_stmt = stmt.on_conflict_do_update(
...     index_elements=['id'],
...     set_=dict(data='updated value')
... )
>>> print(do_update_stmt)
INSERT INTO my_table (id, data) VALUES (%(id)s, %(data)s)
ON CONFLICT (id) DO UPDATE SET data = %(param_1)s

警告

方法 Insert.on_conflict_do_update() **不会** 考虑 Python 端默认 UPDATE 值或生成函数，例如使用 Column.onupdate 指定的函数。这些值不会在 ON CONFLICT 样式的 UPDATE 中执行，除非它们在 Insert.on_conflict_do_update.set_ 字典中手动指定。

使用排除的 INSERT 值更新

为了引用提议插入的行，可以使用特殊别名 Insert.excluded 作为 Insert 对象上的属性；此对象是一个 ColumnCollection，其别名包含目标表的所有列。

>>> stmt = insert(my_table).values(
...     id='some_id',
...     data='inserted value',
...     author='jlh'
... )
>>> do_update_stmt = stmt.on_conflict_do_update(
...     index_elements=['id'],
...     set_=dict(data='updated value', author=stmt.excluded.author)
... )
>>> print(do_update_stmt)
INSERT INTO my_table (id, data, author)
VALUES (%(id)s, %(data)s, %(author)s)
ON CONFLICT (id) DO UPDATE SET data = %(param_1)s, author = excluded.author

其他 WHERE 条件

方法 Insert.on_conflict_do_update() 还接受一个 WHERE 子句，使用 Insert.on_conflict_do_update.where 参数，这将限制接收 UPDATE 的行。

>>> stmt = insert(my_table).values(
...     id='some_id',
...     data='inserted value',
...     author='jlh'
... )
>>> on_update_stmt = stmt.on_conflict_do_update(
...     index_elements=['id'],
...     set_=dict(data='updated value', author=stmt.excluded.author),
...     where=(my_table.c.status == 2)
... )
>>> print(on_update_stmt)
INSERT INTO my_table (id, data, author)
VALUES (%(id)s, %(data)s, %(author)s)
ON CONFLICT (id) DO UPDATE SET data = %(param_1)s, author = excluded.author
WHERE my_table.status = %(status_1)s

使用 DO NOTHING 跳过行

ON CONFLICT 可用于在与唯一或排除约束发生冲突时完全跳过插入行；下面使用 Insert.on_conflict_do_nothing() 方法说明。

>>> stmt = insert(my_table).values(id='some_id', data='inserted value')
>>> stmt = stmt.on_conflict_do_nothing(index_elements=['id'])
>>> print(stmt)
INSERT INTO my_table (id, data) VALUES (%(id)s, %(data)s)
ON CONFLICT (id) DO NOTHING

如果使用 DO NOTHING 且未指定任何列或约束，则其效果是跳过针对发生的任何唯一或排除约束违规的 INSERT。

>>> stmt = insert(my_table).values(id='some_id', data='inserted value')
>>> stmt = stmt.on_conflict_do_nothing()
>>> print(stmt)
INSERT INTO my_table (id, data) VALUES (%(id)s, %(data)s)
ON CONFLICT DO NOTHING

全文搜索

PostgreSQL 的全文搜索系统可通过使用 func 命名空间，以及通过 Operators.bool_op() 方法使用自定义运算符来使用。

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

运算符 Operators.match() 提供跨兼容的简单文本匹配。对于 PostgreSQL 后端，它被硬编码为生成一个表达式，使用 @@ 运算符以及 plainto_tsquery() PostgreSQL 函数。

在 PostgreSQL 方言上，以下表达式

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

将发出到数据库

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 中完全渲染的内容，请使用以下形式

from sqlalchemy import func

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

这将发出

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

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

超越 Operators.match() 的简单使用的文本搜索操作可以使用 func 命名空间来生成 PostgreSQL 全文函数，并结合 Operators.bool_op() 来生成任何布尔运算符。

例如，查询

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

将生成

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

产生与以下语句等效的语句

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

命名空间 func 由 PostgreSQL 方言增强，为大多数全文搜索函数设置正确的参数和返回值类型。假设已导入 sqlalchemy.dialects.postgresql 包，或者使用 postgresql 方言调用了 create_engine()，则 sqlalchemy.sql.expression.func 命名空间会自动使用这些函数。这些函数在以下文档中进行了说明

• to_tsvector

• to_tsquery

• plainto_tsquery

• phraseto_tsquery

• websearch_to_tsquery

• ts_headline

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

PostgreSQL 的 plainto_tsquery() 函数接受一个可选的“regconfig”参数，用于指示 PostgreSQL 使用特定的预计算 GIN 或 GiST 索引来执行搜索。使用 Operators.match() 时，可以使用 postgresql_regconfig 参数指定此附加参数，例如

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

这将发出

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

产生与以下语句等效的语句

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

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

另请参阅

全文搜索 - 在 PostgreSQL 文档中

FROM ONLY …

该方言支持 PostgreSQL 的 ONLY 关键字，用于仅针对继承层次结构中的特定表。这可用于生成 SELECT ... FROM ONLY，UPDATE ONLY ... 和 DELETE FROM ONLY ... 语法。它使用 SQLAlchemy 的提示机制

# 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 的索引选项

可使用几种对 Index 结构的扩展，这些扩展特定于 PostgreSQL 方言。

覆盖索引

选项 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。

部分索引

部分索引在索引定义中添加标准，以便索引应用于行的子集。这些可以在 Index 上使用关键字参数 postgresql_where 指定

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

运算符类

PostgreSQL 允许为索引的每个列指定一个操作符类（参见 https://postgresql.ac.cn/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 的“键”名称，即从 .c 集合中访问它的名称 Table，它可以配置为与数据库中表达的列的实际名称不同。

如果 postgresql_ops 要用于复杂的 SQL 表达式（如函数调用），则要应用于该列，必须为其提供一个标签，该标签在字典中按名称标识，例如：

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 参数。有关详细信息，请参阅该参数。

版本 1.3.21 中的新增功能： 添加了对 ExcludeConstraint 操作符类的支持。

索引类型

PostgreSQL 提供了几种索引类型：B-Tree、Hash、GiST 和 GIN，以及用户创建自己的索引类型的能力（参见 https://postgresql.ac.cn/docs/current/static/indexes-types.html）。这些索引类型可以在 Index 上使用 postgresql_using 关键字参数指定。

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

传递给关键字参数的值将简单地传递给底层的 CREATE INDEX 命令，因此它必须是 PostgreSQL 版本的有效索引类型。

索引存储参数

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 上也提供相同的选项。

带有 CONCURRENTLY 的索引

PostgreSQL 索引选项 CONCURRENTLY 通过将标志 postgresql_concurrently 传递给 Index 结构来支持。

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

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

上面的索引结构将呈现 CREATE INDEX 的 DDL，假设检测到 PostgreSQL 8.2 或更高版本，或者对于无连接的方言，则为：

CREATE INDEX CONCURRENTLY test_idx1 ON testtbl (data)

对于 DROP INDEX，假设检测到 PostgreSQL 9.2 或更高版本，或者对于无连接的方言，它将发出：

DROP INDEX CONCURRENTLY test_idx1

当使用 CONCURRENTLY 时，PostgreSQL 数据库要求在事务块之外调用语句。Python DBAPI 即使对于单个语句，也强制存在事务，因此要使用此结构，必须使用 DBAPI 的“自动提交”模式。

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 数据库在使用 UNIQUE CONSTRAINT 结构时隐式创建 UNIQUE INDEX。当使用 Inspector 检查表时，Inspector.get_indexes() 和 Inspector.get_unique_constraints() 将分别报告这两个结构；在索引的情况下，如果检测到它镜像约束，则键 duplicates_constraint 将出现在索引条目中。当使用 Table(..., autoload_with=engine) 进行反射时，UNIQUE INDEX 在检测到它镜像 UniqueConstraint 时，在 Table.indexes 中不会返回 Table.constraints 集合中。

特殊反射选项

用于 PostgreSQL 后端的 Inspector 是 PGInspector 的实例，它提供了一些额外的 方法。

from sqlalchemy import create_engine, inspect

engine = create_engine("postgresql+psycopg2://127.0.0.1/test")
insp = inspect(engine)  # will be a PGInspector

print(insp.get_enums())

对象名称	描述
PGInspector

class sqlalchemy.dialects.postgresql.base.PGInspector

成员

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

类签名

class sqlalchemy.dialects.postgresql.base.PGInspector (sqlalchemy.engine.reflection.Inspector)

method sqlalchemy.dialects.postgresql.base.PGInspector.get_domains(schema: str | None = None) → List[ReflectedDomain]

返回一个 DOMAIN 对象列表。

每个成员都是一个包含以下字段的字典：

• name - 域的名称。

• schema - 域的模式名称。

• visible - 布尔值，指示该域是否在默认搜索路径中可见。

• type - 由该域定义的类型。

• nullable - 指示该域是否可以为 NULL。

• default - 域的默认值，如果域没有默认值，则为 None。

• constraints - 一个包含该域定义的约束的字典列表。每个元素都包含两个键：约束的 name 和包含约束文本的 check。

参数：:

schema – 模式名称。如果为 None，则使用默认模式（通常为‘public’）。也可以设置为 '*' 以指示加载所有模式的域。

版本 2.0 中的新增功能。

method sqlalchemy.dialects.postgresql.base.PGInspector.get_enums(schema: str | None = None) → List[ReflectedEnum]

返回 ENUM 对象列表。

每个成员都是一个包含以下字段的字典：

• name - 枚举的名称

• schema - 枚举的模式名称。

• visible - 布尔值，表示此枚举是否在默认搜索路径中可见。

• labels - 应用于枚举的字符串标签列表。

参数：:

schema – 模式名称。如果为 None，则使用默认模式（通常为 'public'）。也可以设置为 '*' 表示加载所有模式的枚举。

method sqlalchemy.dialects.postgresql.base.PGInspector.get_foreign_table_names(schema: str | None = None) → List[str]

返回 FOREIGN TABLE 名称列表。

行为类似于 Inspector.get_table_names()，只是列表仅限于那些报告 relkind 值为 f 的表。

method sqlalchemy.dialects.postgresql.base.PGInspector.get_table_oid(table_name: str, schema: str | None = None) → int

返回给定表名的 OID。

参数：:

• table_name – 表的字符串名称。对于特殊引用，请使用 quoted_name。

• schema – 字符串模式名称；如果省略，则使用数据库连接的默认模式。对于特殊引用，请使用 quoted_name。

method sqlalchemy.dialects.postgresql.base.PGInspector.has_type(type_name: str, schema: str | None = None, **kw: Any) → bool

返回数据库在提供的模式中是否具有指定的类型。

参数：:

• type_name – 要检查的类型。

• schema – 模式名称。如果为 None，则使用默认模式（通常为 'public'）。也可以设置为 '*' 表示检查所有模式。

版本 2.0 中的新增功能。

PostgreSQL 表选项

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)')
  
  .. versionadded:: 1.2.6

• TABLESPACE:

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

  上述选项也可用于 Index 结构。

• USING:

  Table("some_table", metadata, ..., postgresql_using='heap')
  
  .. versionadded:: 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 选项 - 在 PostgreSQL 文档中。

PostgreSQL 约束选项

以下选项由 PostgreSQL 方言与选定的约束结构一起支持

• NOT VALID：当通过 ALTER TABLE 将约束添加到现有表时，此选项适用于 CHECK 和 FOREIGN KEY 约束，并且具有在 ALTER 操作期间不会扫描现有行以检查添加的约束的效果。

  当使用像 Alembic 这样的 SQL 迁移工具来渲染 ALTER TABLE 结构时，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 选项 - 在 PostgreSQL 文档中。

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

PostgreSQL 广泛利用了现代 SQL 形式，例如表值函数、表和行作为值。这些结构通常用作 PostgreSQL 对 JSON、ARRAY 和其他数据类型等复杂数据类型的支持的一部分。SQLAlchemy 的 SQL 表达式语言对大多数表值和行值形式具有原生支持。

表值函数

许多 PostgreSQL 内置函数旨在用于 SELECT 语句的 FROM 子句中，并且能够返回表行或表行集。例如，PostgreSQL 的大部分 JSON 函数，例如 json_array_elements()、json_object_keys()、json_each_text()、json_each()、json_to_record()、json_populate_recordset() 都使用这种形式。这些类型的 SQL 函数调用形式在 SQLAlchemy 中可以使用 FunctionElement.table_valued() 方法与从 Function 对象生成的 func 命名空间结合使用。

以下是一些来自 PostgreSQL 参考文档的示例

• json_each():

  >>> from sqlalchemy import select, func
  >>> stmt = select(func.json_each('{"a":"foo", "b":"bar"}').table_valued("key", "value"))
  >>> print(stmt)
  SELECT anon_1.key, anon_1.value
  FROM json_each(:json_each_1) AS anon_1
  

• json_populate_record():

  >>> from sqlalchemy import select, func, literal_column
  >>> stmt = select(
  ...     func.json_populate_record(
  ...         literal_column("null::myrowtype"),
  ...         '{"a":1,"b":2}'
  ...     ).table_valued("a", "b", name="x")
  ... )
  >>> print(stmt)
  SELECT x.a, x.b
  FROM json_populate_record(null::myrowtype, :json_populate_record_1) AS x
  

• json_to_record() - 这种形式使用 PostgreSQL 特定的派生列别名形式，我们可以利用 column() 元素类型来生成它们。该 FunctionElement.table_valued() 方法产生一个 TableValuedAlias 结构，并且该方法 TableValuedAlias.render_derived() 方法设置了派生列规范

  >>> from sqlalchemy import select, func, column, Integer, Text
  >>> stmt = select(
  ...     func.json_to_record('{"a":1,"b":[1,2,3],"c":"bar"}').table_valued(
  ...         column("a", Integer), column("b", Text), column("d", Text),
  ...     ).render_derived(name="x", with_types=True)
  ... )
  >>> print(stmt)
  SELECT x.a, x.b, x.d
  FROM json_to_record(:json_to_record_1) AS x(a INTEGER, b TEXT, d TEXT)
  

• WITH ORDINALITY - 作为 SQL 标准的一部分，WITH ORDINALITY 向函数的输出添加一个序数计数器，并且被有限的 PostgreSQL 函数集接受，包括 unnest() 和 generate_series()。该 FunctionElement.table_valued() 方法接受一个关键字参数 with_ordinality，用于此目的，它接受将应用于“序数”列的字符串名称

  >>> 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 中的新增功能。

另请参阅

表值函数 - 在 SQLAlchemy 统一教程 中

列值函数

类似于表值函数，列值函数存在于 FROM 子句中，但将自身作为单个标量值传递给列子句。PostgreSQL 函数，如 json_array_elements()、unnest() 和 generate_series() 可能会使用这种形式。列值函数可以使用 FunctionElement.column_valued() 方法在 FunctionElement 上使用。

• json_array_elements():

  >>> from sqlalchemy import select, func
  >>> stmt = select(func.json_array_elements('["one", "two"]').column_valued("x"))
  >>> print(stmt)
  SELECT x
  FROM json_array_elements(:json_array_elements_1) AS x
  

• unnest() - 为了生成 PostgreSQL ARRAY 字面量，可以使用 array() 结构

  >>> from sqlalchemy.dialects.postgresql import array
  >>> from sqlalchemy import select, func
  >>> stmt = select(func.unnest(array([1, 2])).column_valued())
  >>> print(stmt)
  SELECT anon_1
  FROM unnest(ARRAY[%(param_1)s, %(param_2)s]) AS anon_1
  

  当然，该函数可以针对现有的表绑定列使用，该列的类型为 ARRAY

  >>> from sqlalchemy import table, column, ARRAY, Integer
  >>> from sqlalchemy import select, func
  >>> t = table("t", column('value', ARRAY(Integer)))
  >>> stmt = select(func.unnest(t.c.value).column_valued("unnested_value"))
  >>> print(stmt)
  SELECT unnested_value
  FROM unnest(t.value) AS unnested_value
  

另请参阅

列值函数 - 表值函数作为标量列 - 在 SQLAlchemy 统一教程 中

行类型

对渲染 ROW 的内置支持可以使用 func.ROW 与 sqlalchemy.func 命名空间一起使用，或者使用 tuple_() 结构

>>> from sqlalchemy import table, column, func, tuple_
>>> t = table("t", column("id"), column("fk"))
>>> stmt = t.select().where(
...     tuple_(t.c.id, t.c.fk) > (1,2)
... ).where(
...     func.ROW(t.c.id, t.c.fk) < func.ROW(3, 7)
... )
>>> print(stmt)
SELECT t.id, t.fk
FROM t
WHERE (t.id, t.fk) > (:param_1, :param_2) AND ROW(t.id, t.fk) < ROW(:ROW_1, :ROW_2)

另请参阅

PostgreSQL 行构造函数

PostgreSQL 行构造函数比较

传递给函数的表类型

PostgreSQL 支持将表作为参数传递给函数，这被称为“记录”类型。SQLAlchemy FromClause 对象，如 Table，使用 FromClause.table_valued() 方法支持这种特殊形式，它类似于 FunctionElement.table_valued() 方法，只是列的集合已经由 FromClause 本身建立

>>> from sqlalchemy import table, column, func, select
>>> a = table( "a", column("id"), column("x"), column("y"))
>>> stmt = select(func.row_to_json(a.table_valued()))
>>> print(stmt)
SELECT row_to_json(a) AS row_to_json_1
FROM a

版本 1.4.0b2 中的新增功能。

数组类型

PostgreSQL 方言支持数组，既作为多维列类型，也作为数组字面量

• ARRAY - ARRAY 数据类型

• array - 数组字面量

• array_agg() - ARRAY_AGG SQL 函数

• aggregate_order_by - 用于 PG 的 ORDER BY 聚合函数语法的助手。

JSON 类型

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

• JSON

• JSONB

• JSONPATH

HSTORE 类型

支持 PostgreSQL HSTORE 类型以及 hstore 字面量

• HSTORE - HSTORE 数据类型

• hstore - hstore 字面量

枚举类型

PostgreSQL 具有独立可创建的 TYPE 结构，用于实现枚举类型。这种方法在 SQLAlchemy 方面引入了相当大的复杂性，涉及何时应该创建和删除该类型。类型对象也是一个独立可反射的实体。应参考以下部分

• ENUM - ENUM 的 DDL 和类型支持。

• PGInspector.get_enums() - 检索当前 ENUM 类型的列表

• ENUM.create() , ENUM.drop() - ENUM 的单个 CREATE 和 DROP 命令。

将 ENUM 与 ARRAY 结合使用

目前，后端 DBAPI 不直接支持 ENUM 和 ARRAY 的组合。在 SQLAlchemy 1.3.17 之前，需要一种特殊的解决方法才能使这种组合正常工作，如下所述。

版本 1.3.17 中的更改： 现在，SQLAlchemy 的实现直接处理 ENUM 和 ARRAY 的组合，无需任何解决方法。

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

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

将 JSON/JSONB 与 ARRAY 结合使用

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

版本 1.3.17 中的更改： 现在，SQLAlchemy 的实现直接处理 JSON/JSONB 和 ARRAY 的组合，无需任何解决方法。

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

范围和多范围类型

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 类型是一个简单的 dataclass，它将表示范围的边界。下面说明将一行插入到上面的 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)

可用的范围数据类型如下

• INT4RANGE

• INT8RANGE

• NUMRANGE

• DATERANGE

• TSRANGE

• TSTZRANGE

对象名称	描述
范围	表示 PostgreSQL 范围。

class sqlalchemy.dialects.postgresql.Range

表示 PostgreSQL 范围。

例如。

r = Range(10, 50, bounds="()")

调用方式类似于 psycopg 和 psycopg2，部分原因是为了允许更轻松地从以前使用这些对象直接的 SQLAlchemy 版本迁移。

参数：:

• lower – 下界值，或 None

• upper – 上界值，或 None

• bounds – 仅限关键字，可选字符串值，为 "()"、"[)"、"(]"、"[]" 之一。默认为 "[)"。

• empty – 仅限关键字，可选 bool 值，指示这是一个“空”范围

版本 2.0 中的新增功能。

成员

__eq__()、adjacent_to()、contained_by()、contains()、difference()、intersection()、is_empty、isempty、lower、lower_inc、lower_inf、not_extend_left_of()、not_extend_right_of()、overlaps()、strictly_left_of()、strictly_right_of()、union()、upper、upper_inc、upper_inf

类签名

class sqlalchemy.dialects.postgresql.Range (typing.Generic)

method sqlalchemy.dialects.postgresql.Range.__eq__(other: Any) → bool

将此范围与 other 进行比较，同时考虑边界包含性，如果它们相等则返回 True。

method sqlalchemy.dialects.postgresql.Range.adjacent_to(other: Range[_T]) → bool

确定此范围是否与 other 相邻。

method sqlalchemy.dialects.postgresql.Range.contained_by(other: Range[_T]) → bool

确定此范围是否被 other 包含。

method sqlalchemy.dialects.postgresql.Range.contains(value: _T | Range[_T]) → bool

确定此范围是否包含 value。

method sqlalchemy.dialects.postgresql.Range.difference(other: Range[_T]) → Range[_T]

计算此范围与 other 之间的差。

如果这两个范围是“不相交”的，即既不相邻也不重叠，则会引发 ValueError 异常。

method sqlalchemy.dialects.postgresql.Range.intersection(other: Range[_T]) → Range[_T]

计算此范围与 other 的交集。

版本 2.0.10 中新增。

attribute sqlalchemy.dialects.postgresql.Range.is_empty

‘empty’ 属性的同义词。

attribute sqlalchemy.dialects.postgresql.Range.isempty

‘empty’ 属性的同义词。

attribute sqlalchemy.dialects.postgresql.Range.lower: _T | None

下界

attribute sqlalchemy.dialects.postgresql.Range.lower_inc

如果下界是包含的，则返回 True。

attribute sqlalchemy.dialects.postgresql.Range.lower_inf

如果此范围非空且下界是无穷大的，则返回 True。

method sqlalchemy.dialects.postgresql.Range.not_extend_left_of(other: Range[_T]) → bool

确定此范围是否不扩展到 other 的左侧。

method sqlalchemy.dialects.postgresql.Range.not_extend_right_of(other: Range[_T]) → bool

确定此范围是否不扩展到 other 的右侧。

method sqlalchemy.dialects.postgresql.Range.overlaps(other: Range[_T]) → bool

确定此范围是否与 other 重叠。

method sqlalchemy.dialects.postgresql.Range.strictly_left_of(other: Range[_T]) → bool

确定此范围是否完全位于 other 的左侧。

method sqlalchemy.dialects.postgresql.Range.strictly_right_of(other: Range[_T]) → bool

确定此范围是否完全位于 other 的右侧。

method sqlalchemy.dialects.postgresql.Range.union(other: Range[_T]) → Range[_T]

计算此范围与 other 的并集。

如果这两个范围是“不相交”的，即既不相邻也不重叠，则会引发 ValueError 异常。

attribute sqlalchemy.dialects.postgresql.Range.upper: _T | None

上限。

attribute sqlalchemy.dialects.postgresql.Range.upper_inc

如果上限是包含的，则返回 True。

attribute sqlalchemy.dialects.postgresql.Range.upper_inf

如果此范围是非空的且上限是无穷大，则返回 True。

多范围

PostgreSQL 14 及更高版本支持多范围。 SQLAlchemy 的多范围数据类型处理 Range 类型列表。

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

New in version 2.0: 添加了对 MULTIRANGE 数据类型的支持。 SQLAlchemy 将多范围值表示为 Range 对象列表。

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

New in version 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 类型修饰符。有关背景信息，请参见部分 变异跟踪。

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

在将多范围用作字面量而不指定类型时，可以使用实用程序 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(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}")

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

New in version 2.0.26: MultiRange 序列已添加。

可用的多范围数据类型如下所示。

• INT4MULTIRANGE

• INT8MULTIRANGE

• NUMMULTIRANGE

• DATEMULTIRANGE

• TSMULTIRANGE

• TSTZMULTIRANGE

网络数据类型

包含的网络数据类型是 INET、CIDR、MACADDR。

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

• **psycopg** 和 **asyncpg** 完全支持这些数据类型；来自 ipaddress 家族的对象默认情况下在行中返回。

• **psycopg2** 方言仅发送和接收字符串。

• **pg8000** 方言支持 ipaddress.IPv4Address 和 ipaddress.IPv6Address 对象以用于 INET 数据类型，但对 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。

New in version 2.0.18: - 添加了 native_inet_types 参数。

PostgreSQL 数据类型

与所有 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 特定构造参数的类型

对象名称	描述
AbstractMultiRange	PostgreSQL MULTIRANGE 类型的基类。
AbstractRange	单范围和多范围 SQL 类型的基类。
AbstractSingleRange	PostgreSQL RANGE 类型的基类。
ARRAY	PostgreSQL ARRAY 类型。
BIT
BYTEA
CIDR
CITEXT	提供 PostgreSQL CITEXT 类型。
DATEMULTIRANGE	表示 PostgreSQL DATEMULTIRANGE 类型。
DATERANGE	表示 PostgreSQL DATERANGE 类型。
DOMAIN	表示 DOMAIN PostgreSQL 类型。
ENUM	PostgreSQL ENUM 类型。
HSTORE	表示 PostgreSQL HSTORE 类型。
INET
INT4MULTIRANGE	表示 PostgreSQL INT4MULTIRANGE 类型。
INT4RANGE	表示 PostgreSQL INT4RANGE 类型。
INT8MULTIRANGE	表示 PostgreSQL INT8MULTIRANGE 类型。
INT8RANGE	表示 PostgreSQL INT8RANGE 类型。
INTERVAL	PostgreSQL INTERVAL 类型。
JSON	表示 PostgreSQL JSON 类型。
JSONB	表示 PostgreSQL JSONB 类型。
JSONPATH	JSON 路径类型。
MACADDR
MACADDR8
MONEY	提供 PostgreSQL MONEY 类型。
MultiRange	表示多范围序列。
NUMMULTIRANGE	表示 PostgreSQL NUMMULTIRANGE 类型。
NUMRANGE	表示 PostgreSQL NUMRANGE 类型。
OID	提供 PostgreSQL OID 类型。
REGCLASS	提供 PostgreSQL REGCLASS 类型。
REGCONFIG	提供 PostgreSQL REGCONFIG 类型。
TIME	PostgreSQL TIME 类型。
TIMESTAMP	提供 PostgreSQL TIMESTAMP 类型。
TSMULTIRANGE	表示 PostgreSQL TSRANGE 类型。
TSQUERY	提供 PostgreSQL TSQUERY 类型。
TSRANGE	表示 PostgreSQL TSRANGE 类型。
TSTZMULTIRANGE	表示 PostgreSQL TSTZRANGE 类型。
TSTZRANGE	表示 PostgreSQL TSTZRANGE 类型。
TSVECTOR	TSVECTOR 类型实现 PostgreSQL 文本搜索类型 TSVECTOR。

class sqlalchemy.dialects.postgresql.AbstractRange

单范围和多范围 SQL 类型的基类。

成员

adjacent_to(), contained_by(), contains(), difference(), intersection(), not_extend_left_of(), not_extend_right_of(), overlaps(), strictly_left_of(), strictly_right_of(), union()

类签名

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

class comparator_factory

为范围类型定义比较操作。

类签名

class sqlalchemy.dialects.postgresql.AbstractRange.comparator_factory (sqlalchemy.types.Comparator)

method sqlalchemy.dialects.postgresql.AbstractRange.comparator_factory.adjacent_to(other: Any) → ColumnElement[bool]

布尔表达式。如果列中的范围与操作数中的范围相邻，则返回 true。

method sqlalchemy.dialects.postgresql.AbstractRange.comparator_factory.contained_by(other: Any) → ColumnElement[bool]

布尔表达式。如果列包含在右手操作数中，则返回 true。

method sqlalchemy.dialects.postgresql.AbstractRange.comparator_factory.contains(other: Any, **kw: Any) → ColumnElement[bool]

布尔表达式。如果右手操作数（可以是元素或范围）包含在列中，则返回 true。

kwargs 可能被此运算符忽略，但 API 符合性需要它们。

method sqlalchemy.dialects.postgresql.AbstractRange.comparator_factory.difference(other: Any) → ColumnElement[bool]

范围表达式。返回两个范围的并集。如果生成的范围不是连续的，则会引发异常。

method sqlalchemy.dialects.postgresql.AbstractRange.comparator_factory.intersection(other: Any) → ColumnElement[Range[_T]]

范围表达式。返回两个范围的交集。如果生成的范围不是连续的，则会引发异常。

method sqlalchemy.dialects.postgresql.AbstractRange.comparator_factory.not_extend_left_of(other: Any) → ColumnElement[bool]

布尔表达式。如果列中的范围不向操作数中的范围的左侧扩展，则返回 true。

method sqlalchemy.dialects.postgresql.AbstractRange.comparator_factory.not_extend_right_of(other: Any) → ColumnElement[bool]

布尔表达式。如果列中的范围不向操作数中的范围的右侧扩展，则返回 true。

method sqlalchemy.dialects.postgresql.AbstractRange.comparator_factory.overlaps(other: Any) → ColumnElement[bool]

布尔表达式。如果列与右侧操作数重叠（具有公共点），则返回 True。

method sqlalchemy.dialects.postgresql.AbstractRange.comparator_factory.strictly_left_of(other: Any) → ColumnElement[bool]

布尔表达式。如果列严格位于右侧操作数的左侧，则返回 True。

method sqlalchemy.dialects.postgresql.AbstractRange.comparator_factory.strictly_right_of(other: Any) → ColumnElement[bool]

布尔表达式。如果列严格位于右侧操作数的右侧，则返回 True。

method sqlalchemy.dialects.postgresql.AbstractRange.comparator_factory.union(other: Any) → ColumnElement[bool]

范围表达式。返回两个范围的并集。如果生成的范围不是连续的，则会引发异常。

class sqlalchemy.dialects.postgresql.AbstractSingleRange

PostgreSQL RANGE 类型的基类。

这些类型返回单个 Range 对象。

另请参阅

PostgreSQL 范围函数

类签名

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

class sqlalchemy.dialects.postgresql.AbstractMultiRange

PostgreSQL MULTIRANGE 类型的基类。

这些类型返回 Range 对象的序列。

类签名

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

class sqlalchemy.dialects.postgresql.ARRAY

PostgreSQL ARRAY 类型。

ARRAY 类型以与核心 ARRAY 类型相同的方式构建；需要成员类型，如果类型要用于多个维度，则建议使用多个维度。

from sqlalchemy.dialects import postgresql

mytable = Table("mytable", metadata,
        Column("data", postgresql.ARRAY(Integer, dimensions=2))
    )

ARRAY 类型提供在核心 ARRAY 类型上定义的所有操作，包括对“维度”、索引访问和简单匹配的支持，例如 Comparator.any() 和 Comparator.all()。 ARRAY 类还为包含操作提供了 PostgreSQL 特定的方法，包括 Comparator.contains() Comparator.contained_by() 和 Comparator.overlap()，例如：

mytable.c.data.contains([1, 2])

索引访问默认情况下为从 1 开始的，以匹配 PostgreSQL 的索引访问；对于从 0 开始的索引访问，请设置 ARRAY.zero_indexes。

此外，ARRAY 类型不能直接与 ENUM 类型结合使用。有关解决方法，请参见 Using ENUM with ARRAY 中的特殊类型。

在使用 ORM 时检测 ARRAY 列中的更改

当与 SQLAlchemy ORM 一起使用时，ARRAY 类型不会检测对数组的原地修改。为了检测这些更改，必须使用 sqlalchemy.ext.mutable 扩展，使用 MutableList 类

from sqlalchemy.dialects.postgresql import ARRAY
from sqlalchemy.ext.mutable import MutableList

class SomeOrmClass(Base):
    # ...

    data = Column(MutableList.as_mutable(ARRAY(Integer)))

此扩展将允许对数组进行“原地”更改，例如 .append() 以产生工作单元将检测到的事件。请注意，对数组内部元素的更改（包括原地修改的子数组）不会被检测到。

或者，将新的数组值分配给替换旧数组值的 ORM 元素将始终触发更改事件。

另请参阅

ARRAY - 基本数组类型

array - 生成文字数组值。

成员

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

类签名

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

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

构造一个 ARRAY。

例如。

Column('myarray', ARRAY(Integer))

参数为

参数：:

• item_type – 数组中元素的数据类型。请注意，这里维度无关紧要，因此像 INTEGER[][] 这样的多维数组被构建为 ARRAY(Integer)，而不是 ARRAY(ARRAY(Integer)) 等。

• as_tuple=False – 指定是否将返回结果从列表转换为元组。像 psycopg2 这样的 DBAPI 默认返回列表。当返回元组时，结果是可散列的。

• dimensions – 如果不为 None，则 ARRAY 将假定固定数量的维度。这将导致为此 ARRAY 发出的 DDL 包含确切数量的方括号子句 []，并且还将优化类型的整体性能。请注意，PG 数组始终隐式为“无维度”，这意味着它们可以存储任何数量的维度，无论它们是如何声明的。

• zero_indexes=False – 当为 True 时，索引值将在 Python 零索引和 PostgreSQL 一索引之间转换，例如，值 1 将在传递到数据库之前添加到所有索引值。

class Comparator

定义 ARRAY 的比较操作。

请注意，这些操作是除了 Comparator 基类提供的操作之外的，包括 Comparator.any() 和 Comparator.all()。

类签名

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

method sqlalchemy.dialects.postgresql.ARRAY.Comparator.contains(other, **kwargs)

布尔表达式。测试元素是否为参数数组表达式的元素的超集。

kwargs 可能被此运算符忽略，但 API 符合性需要它们。

method sqlalchemy.dialects.postgresql.ARRAY.Comparator.contained_by(other)

布尔表达式。测试元素是否为参数数组表达式的元素的真子集。

method sqlalchemy.dialects.postgresql.ARRAY.Comparator.overlap(other)

布尔表达式。测试数组是否与参数数组表达式具有共同的元素。

class sqlalchemy.dialects.postgresql.BIT

类签名

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

class sqlalchemy.dialects.postgresql.BYTEA

成员

__init__()

类签名

class sqlalchemy.dialects.postgresql.BYTEA (sqlalchemy.types.LargeBinary)

method sqlalchemy.dialects.postgresql.BYTEA.__init__(length: int | None = None)

从 sqlalchemy.types.LargeBinary.__init__ 方法继承 LargeBinary

构造一个 LargeBinary 类型。

参数：:

length – 可选，用于 DDL 语句中列的长度，用于那些接受长度的二进制类型，例如 MySQL BLOB 类型。

class sqlalchemy.dialects.postgresql.CIDR

类签名

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

class sqlalchemy.dialects.postgresql.CITEXT

提供 PostgreSQL CITEXT 类型。

2.0.7 版新增。

成员

__init__()

类签名

class sqlalchemy.dialects.postgresql.CITEXT (sqlalchemy.types.TEXT)

method sqlalchemy.dialects.postgresql.CITEXT.__init__(length: int | None = None, collation: str | None = None)

从 sqlalchemy.types.String.__init__ 方法继承 String

创建一个保存字符串的类型。

参数：:

• length – 可选，用于 DDL 和 CAST 表达式中列的长度。如果不会发出任何 CREATE TABLE，可以安全地省略。某些数据库可能需要 length 用于 DDL，并且在发出 CREATE TABLE DDL 时，如果包含没有长度的 VARCHAR，则会引发异常。值是解释为字节还是字符取决于数据库。

• collation –

  可选，用于 DDL 和 CAST 表达式的列级排序规则。使用 SQLite、MySQL 和 PostgreSQL 支持的 COLLATE 关键字呈现。例如：

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

  注意

  在大多数情况下，应该为期望存储非 ASCII 数据的 Column 使用 Unicode 或 UnicodeText 数据类型。这些数据类型将确保在数据库上使用正确的类型。

class sqlalchemy.dialects.postgresql.DOMAIN

表示 DOMAIN PostgreSQL 类型。

域本质上是一种数据类型，带有可选约束，用于限制允许的值集。例如：

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

有关更多详细信息，请参阅 PostgreSQL 文档

版本 2.0 中的新增功能。

成员

__init__(), create(), drop()

类签名

类 sqlalchemy.dialects.postgresql.DOMAIN (sqlalchemy.dialects.postgresql.named_types.NamedType, sqlalchemy.types.SchemaType)

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

构造一个 DOMAIN。

参数：:

• name – 域的名称

• data_type – 域的底层数据类型。这可能包括数组说明符。

• collation – 域的可选排序规则。如果没有指定排序规则，则使用底层数据类型的默认排序规则。如果指定了 collation，则底层类型必须是可排序的。

• default – DEFAULT 子句为域数据类型的列指定一个默认值。默认值应为字符串或 text() 值。如果未指定默认值，则默认值为 null 值。

• constraint_name – 约束的可选名称。如果未指定，则后端会生成一个名称。

• not_null – 阻止此域的值为 null。默认情况下，域允许为 null。如果未指定，则不会发出可空性子句。

• check – CHECK 子句指定域值必须满足的完整性约束或测试。约束必须是产生布尔结果的表达式，该表达式可以使用关键字 VALUE 来引用正在测试的值。与 PostgreSQL 不同的是，SQLAlchemy 目前只允许一个 check 子句。

• schema – 可选的模式名称

• metadata – 可选的 MetaData 对象，该对象将与该 DOMAIN 直接关联

• create_type – 默认值为 True。表示在创建父表时，应在可选地检查类型是否存在后发出 CREATE TYPE；另外，在删除表时会调用 DROP TYPE。

方法 sqlalchemy.dialects.postgresql.DOMAIN.create(bind, checkfirst=True, **kw)

继承自 NamedType.create() 方法的 NamedType

为该类型发出 CREATE DDL。

参数：:

• bind – 一个可连接的 Engine、Connection 或类似对象来发出 SQL。

• checkfirst – 如果为 True，则会先对 PG 目录执行查询，以查看类型是否已存在，然后再创建。

方法 sqlalchemy.dialects.postgresql.DOMAIN.drop(bind, checkfirst=True, **kw)

继承自 NamedType.drop() 方法的 NamedType

为该类型发出 DROP DDL。

参数：:

• bind – 一个可连接的 Engine、Connection 或类似对象来发出 SQL。

• checkfirst – 如果为 True，则会先对 PG 目录执行查询，以查看类型是否确实存在，然后再删除。

类 sqlalchemy.dialects.postgresql.DOUBLE_PRECISION

SQL DOUBLE PRECISION 类型。

版本 2.0 中的新增功能。

另请参阅

Double - 基类型文档。

类签名

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

继承自 sqlalchemy.types.Float.__init__ 方法的 Float

构造一个 Float。

参数：:

• precision –

  用于 DDL CREATE TABLE 的数值精度。后端 **应该** 尝试确保此精度指示通用 Float 数据类型的数字位数。

  注意

  对于 Oracle 后端，当渲染 DDL 时，不支持 Float.precision 参数，因为 Oracle 不支持以十进制位数指定浮点数精度。相反，使用 Oracle 特定的 FLOAT 数据类型并指定 FLOAT.binary_precision 参数。这是 SQLAlchemy 2.0 版本的新功能。

  要创建数据库无关的 Float ，它为 Oracle 单独指定二进制精度，请使用 TypeEngine.with_variant() ，如下所示

  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 – 与 Numeric 的标志相同，但默认为 False 。请注意，将此标志设置为 True 将导致浮点数转换。

• decimal_return_scale – 从浮点数转换为 Python 小数时使用的默认比例。由于十进制精度问题，浮点数通常会长得多，而且大多数浮点数数据库类型没有“比例”的概念，因此默认情况下，浮点数类型在转换时会查找前十个十进制位。指定此值将覆盖该长度。请注意，MySQL 浮点数类型（包括“比例”）将使用“比例”作为 decimal_return_scale 的默认值，如果未另行指定。

class sqlalchemy.dialects.postgresql.ENUM

PostgreSQL ENUM 类型。

这是 Enum 的一个子类，它包括对 PG 的 CREATE TYPE 和 DROP TYPE 的支持。

当使用内置类型 Enum 且 Enum.native_enum 标志保留其默认值 True 时，PostgreSQL 后端将使用 ENUM 类型作为实现，因此将使用特殊的创建/删除规则。

ENUM 的创建/删除行为必然很复杂，因为 ENUM 类型与父表的关联关系很尴尬，因为它可能仅由单个表“拥有”，也可能由多个表共享。

当以“内联”方式使用 Enum 或 ENUM 时，将发出与 Table.create() 和 Table.drop() 方法调用时相对应的 CREATE TYPE 和 DROP TYPE 。

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

要在多个表之间使用通用枚举类型，最佳做法是独立声明 Enum 或 ENUM ，并将其与 MetaData 对象本身关联

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

当使用此模式时，在各个表创建级别上仍然必须注意。发出 CREATE TABLE 而不也指定 checkfirst=True 仍会导致问题

t1.create(engine) # will fail: no such type 'myenum'

如果我们指定 checkfirst=True ，各个表级创建操作将检查 ENUM 并创建（如果不存在）

# will check if enum exists, and emit CREATE TYPE if not
t1.create(engine, checkfirst=True)

当使用元数据级 ENUM 类型时，如果调用元数据范围的创建/删除，该类型将始终被创建和删除

metadata.create_all(engine)  # will emit CREATE TYPE
metadata.drop_all(engine)  # will emit DROP TYPE

该类型也可以直接创建和删除

my_enum.create(engine)
my_enum.drop(engine)

成员

__init__(), create(), drop()

类签名

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)

构造一个 ENUM 。

参数与 Enum 相同，但也包括以下参数。

参数：:

create_type – 默认为 True。表示当父表正在创建时，应该发出 CREATE TYPE ，在可选地检查类型是否存在后；此外，在表被删除时调用 DROP TYPE 。当 False 时，不会执行任何检查，并且不会发出 CREATE TYPE 或 DROP TYPE ，除非直接调用 ENUM.create() 或 ENUM.drop() 。当调用创建方案到没有访问实际数据库的 SQL 文件时，设置为 False 会很有帮助 - ENUM.create() 和 ENUM.drop() 方法可用于向目标绑定发出 SQL 。

method sqlalchemy.dialects.postgresql.ENUM.create(bind=None, checkfirst=True)

为该 ENUM 发出 CREATE TYPE。

如果底层方言不支持 PostgreSQL CREATE TYPE，则不执行任何操作。

参数：:

• bind – 一个可连接的 Engine，Connection 或类似对象来发出 SQL。

• checkfirst – 如果为 True，将首先执行针对 PG 目录的查询以查看类型是否已存在，然后才创建。

method sqlalchemy.dialects.postgresql.ENUM.drop(bind=None, checkfirst=True)

为该 ENUM 发出 DROP TYPE。

如果底层方言不支持 PostgreSQL DROP TYPE，则不执行任何操作。

参数：:

• bind – 一个可连接的 Engine，Connection 或类似对象来发出 SQL。

• checkfirst – 如果为 True，将首先执行针对 PG 目录的查询以查看类型是否实际存在，然后才删除。

class sqlalchemy.dialects.postgresql.HSTORE

表示 PostgreSQL HSTORE 类型。

HSTORE 类型存储包含字符串的字典，例如：

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 提供了广泛的操作，包括

• 索引操作

  data_table.c.data['some key'] == 'some value'

• 包含操作

  data_table.c.data.has_key('some key')
  
  data_table.c.data.has_all(['one', 'two', 'three'])

• 串联

  data_table.c.data + {"k1": "v1"}

有关特殊方法的完整列表，请参见 comparator_factory。

在使用 ORM 时检测 HSTORE 列的变化

为了与 SQLAlchemy ORM 一起使用，可能需要将 HSTORE 的使用与 MutableDict 字典（现在是 sqlalchemy.ext.mutable 扩展的一部分）结合使用。此扩展将允许对字典进行“就地”更改，例如向当前字典添加新键或替换/删除现有键，以产生工作单元将检测到的事件。

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

当未使用 sqlalchemy.ext.mutable 扩展时，ORM 不会收到对现有字典内容的任何更改的通知，除非该字典值被重新分配到 HSTORE 属性本身，从而生成更改事件。

另请参阅

hstore - 渲染 PostgreSQL hstore() 函数。

成员

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 sqlalchemy.dialects.postgresql.HSTORE (sqlalchemy.types.Indexable, sqlalchemy.types.Concatenable, sqlalchemy.types.TypeEngine)

class Comparator

为 HSTORE 定义比较操作。

类签名

class sqlalchemy.dialects.postgresql.HSTORE.Comparator (sqlalchemy.types.Comparator, sqlalchemy.types.Comparator)

method sqlalchemy.dialects.postgresql.HSTORE.Comparator.array()

文本数组表达式。返回交替键和值的数组。

method sqlalchemy.dialects.postgresql.HSTORE.Comparator.contained_by(other)

布尔表达式。测试键是否是参数 jsonb 表达式键的真子集。

method sqlalchemy.dialects.postgresql.HSTORE.Comparator.contains(other, **kwargs)

布尔表达式。测试键（或数组）是否是参数 jsonb 表达式键的超集/是否包含参数 jsonb 表达式键。

kwargs 可能被此运算符忽略，但 API 符合性需要它们。

method sqlalchemy.dialects.postgresql.HSTORE.Comparator.defined(key)

布尔表达式。测试键是否存在非 NULL 值。请注意，键可以是 SQLA 表达式。

method sqlalchemy.dialects.postgresql.HSTORE.Comparator.delete(key)

HStore 表达式。返回删除了给定键的此 hstore 的内容。请注意，键可以是 SQLA 表达式。

方法 sqlalchemy.dialects.postgresql.HSTORE.Comparator.has_all(other)

布尔表达式。测试 jsonb 中是否存在所有键。

方法 sqlalchemy.dialects.postgresql.HSTORE.Comparator.has_any(other)

布尔表达式。测试 jsonb 中是否存在任何键。

方法 sqlalchemy.dialects.postgresql.HSTORE.Comparator.has_key(other)

布尔表达式。测试键是否存在。请注意，键可能是一个 SQLA 表达式。

方法 sqlalchemy.dialects.postgresql.HSTORE.Comparator.keys()

文本数组表达式。返回键数组。

方法 sqlalchemy.dialects.postgresql.HSTORE.Comparator.matrix()

文本数组表达式。返回 [键，值] 对的数组。

方法 sqlalchemy.dialects.postgresql.HSTORE.Comparator.slice(array)

HStore 表达式。返回由键数组定义的 hstore 子集。

方法 sqlalchemy.dialects.postgresql.HSTORE.Comparator.vals()

文本数组表达式。返回值的数组。

方法 sqlalchemy.dialects.postgresql.HSTORE.__init__(text_type=None)

构造一个新的 HSTORE。

参数：:

text_type – 用于索引值的类型。默认为 Text。

方法 sqlalchemy.dialects.postgresql.HSTORE.bind_processor(dialect)

返回用于处理绑定值的转换函数。

返回一个可调用对象，该对象将接收绑定参数值作为唯一的 positional 参数，并返回一个要发送到 DB-API 的值。

如果不需要处理，该方法应该返回 None。

注意

此方法仅针对方言特定类型对象调用，该对象通常对使用的方言是私有的，与面向公众的对象不是同一个类型对象，这意味着不可能子类化 TypeEngine 类以提供一个替代的 TypeEngine.bind_processor() 方法，除非显式地子类化 UserDefinedType 类。

要为 TypeEngine.bind_processor() 提供替代行为，请实现一个 TypeDecorator 类并提供 TypeDecorator.process_bind_param() 的实现。

另请参阅

扩展现有类型

参数：:

dialect – 使用中的方言实例。

属性 sqlalchemy.dialects.postgresql.HSTORE.comparator_factory

Comparator 的别名。

属性 sqlalchemy.dialects.postgresql.HSTORE.hashable = False

标志，如果为 False，则表示此类型的 value 不可哈希。

ORM 在唯一化结果列表时使用。

方法 sqlalchemy.dialects.postgresql.HSTORE.result_processor(dialect, coltype)

返回用于处理结果行值的转换函数。

返回一个可调用对象，该对象将接收结果行列值作为唯一的 positional 参数，并返回一个要返回给用户的 value。

如果不需要处理，该方法应该返回 None。

注意

此方法仅针对方言特定类型对象调用，该对象通常对使用的方言是私有的，与面向公众的对象不是同一个类型对象，这意味着不可能子类化 TypeEngine 类以提供一个替代的 TypeEngine.result_processor() 方法，除非显式地子类化 UserDefinedType 类。

要为 TypeEngine.result_processor() 提供替代行为，请实现一个 TypeDecorator 类并提供 TypeDecorator.process_result_value() 的实现。

另请参阅

扩展现有类型

参数：:

• dialect – 使用中的方言实例。

• coltype – 在 cursor.description 中接收到的 DBAPI coltype 参数。

类 sqlalchemy.dialects.postgresql.INET

类签名

类 sqlalchemy.dialects.postgresql.INET (sqlalchemy.types.TypeEngine)

类 sqlalchemy.dialects.postgresql.INTERVAL

PostgreSQL INTERVAL 类型。

成员

__init__()

类签名

类 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

构建一个 INTERVAL。

参数：:

• precision – 可选的整数精度值

• fields –

  字符串字段指定符。允许存储字段的限制，例如 "YEAR"，"MONTH"，"DAY TO HOUR" 等。

  版本 1.2 中新增。

class sqlalchemy.dialects.postgresql.JSON

表示 PostgreSQL JSON 类型。

每当在 PostgreSQL 后端使用基本 JSON 数据类型时，JSON 会自动使用，但是基本 JSON 数据类型没有为 PostgreSQL 特定比较方法（如 Comparator.astext()）提供 Python 访问器；此外，要使用 PostgreSQL JSONB，应显式使用 JSONB 数据类型。

另请参阅

JSON - 通用跨平台 JSON 数据类型的核心文档。

PostgreSQL 版本的 JSON 提供的操作符包括

• 索引操作（-> 操作符）

  data_table.c.data['some key']
  
  data_table.c.data[5]

• 返回文本的索引操作（->> 操作符）

  data_table.c.data['some key'].astext == 'some value'

  请注意，等效功能可通过 Comparator.as_string 访问器获得。

• 带有 CAST 的索引操作（等效于 CAST(col ->> ['some key'] AS <type>))

  data_table.c.data['some key'].astext.cast(Integer) == 5

  请注意，等效功能可通过 Comparator.as_integer 和类似的访问器获得。

• 路径索引操作（#> 操作符）

  data_table.c.data[('key_1', 'key_2', 5, ..., 'key_n')]

• 返回文本的路径索引操作（#>> 操作符）

  data_table.c.data[('key_1', 'key_2', 5, ..., 'key_n')].astext == 'some value'

索引操作返回一个表达式对象，其类型默认情况下为 JSON，因此可以在结果类型上调用更多面向 JSON 的指令。

自定义序列化器和反序列化器在方言级别指定，即使用 create_engine()。这样做的原因是，当使用 psycopg2 时，DBAPI 仅允许在每个游标或每个连接级别进行序列化。例如

engine = create_engine("postgresql+psycopg2://scott:tiger@localhost/test",
                        json_serializer=my_serialize_fn,
                        json_deserializer=my_deserialize_fn
                )

当使用 psycopg2 方言时，json_deserializer 使用 psycopg2.extras.register_default_json 在数据库中注册。

另请参阅

JSON - 核心级别的 JSON 类型

JSONB

成员

astext，__init__()，comparator_factory，render_bind_cast

类签名

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

class Comparator

为 JSON 定义比较操作。

类签名

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

attribute sqlalchemy.dialects.postgresql.JSON.Comparator.astext

在索引表达式上，在 SQL 中呈现时使用“astext”（例如“->>”）转换。

例如。

select(data_table.c.data['some key'].astext)

另请参阅

ColumnElement.cast()

method sqlalchemy.dialects.postgresql.JSON.__init__(none_as_null=False, astext_type=None)

构建一个 JSON 类型。

参数：:

• none_as_null –

  如果为 True，则将值 None 作为 SQL NULL 值持久化，而不是 null 的 JSON 编码。请注意，当此标志为 False 时，null() 结构仍然可以用于持久化 NULL 值

  from sqlalchemy import null
  conn.execute(table.insert(), {"data": null()})

  另请参阅

  JSON.NULL

• astext_type – 在索引属性上用于 Comparator.astext 访问器的类型。默认为 Text。

attribute sqlalchemy.dialects.postgresql.JSON.comparator_factory

Comparator 的别名

attribute sqlalchemy.dialects.postgresql.JSON.render_bind_cast = True

为 BindTyping.RENDER_CASTS 模式呈现绑定转换。

如果为 True，则此类型（通常是方言级别的实现类型）向编译器发出信号，表明应在此类型的绑定参数周围呈现转换。

版本 2.0 中的新增功能。

另请参阅

BindTyping

class sqlalchemy.dialects.postgresql.JSONB

表示 PostgreSQL JSONB 类型。

JSONB 类型存储任意 JSONB 格式的数据，例如

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

该 JSONB 类型包含了所有由 JSON 提供的操作，包括索引操作的相同行为。它还增加了特定于 JSONB 的附加运算符，包括 Comparator.has_key()，Comparator.has_all()，Comparator.has_any()，Comparator.contains()，Comparator.contained_by()，Comparator.delete_path()，Comparator.path_exists() 和 Comparator.path_match().

与 JSON 类型一样，JSONB 类型在与 ORM 一起使用时不会检测就地更改，除非使用 sqlalchemy.ext.mutable 扩展。

自定义序列化器和反序列化器与 JSON 类共享，使用 json_serializer 和 json_deserializer 关键字参数。这些必须使用 create_engine() 在方言级别指定。当使用 psycopg2 时，序列化器使用 psycopg2.extras.register_default_jsonb 在每个连接的基础上与 jsonb 类型相关联，就像 psycopg2.extras.register_default_json 用于将这些处理程序与 json 类型注册一样。

另请参阅

JSON

成员

contained_by()，contains()，delete_path()，has_all()，has_any()，has_key()，path_exists()，path_match()，comparator_factory

类签名

class sqlalchemy.dialects.postgresql.JSONB (sqlalchemy.dialects.postgresql.json.JSON)

class Comparator

为 JSON 定义比较操作。

类签名

class sqlalchemy.dialects.postgresql.JSONB.Comparator (sqlalchemy.dialects.postgresql.json.Comparator)

method sqlalchemy.dialects.postgresql.JSONB.Comparator.contained_by(other)

布尔表达式。测试键是否为参数 jsonb 表达式键的真子集（等效于 <@ 运算符）。

method sqlalchemy.dialects.postgresql.JSONB.Comparator.contains(other, **kwargs)

布尔表达式。测试键（或数组）是否为参数 jsonb 表达式键的超集/包含（等效于 @> 运算符）。

kwargs 可能被此运算符忽略，但 API 符合性需要它们。

method sqlalchemy.dialects.postgresql.JSONB.Comparator.delete_path(array)

JSONB 表达式。删除参数数组中指定的字段或数组元素（等效于 #- 运算符）。

输入可以是字符串列表，这些字符串将被强制转换为 ARRAY 或 _postgres.array() 的实例。

版本 2.0 中的新增功能。

method sqlalchemy.dialects.postgresql.JSONB.Comparator.has_all(other)

布尔表达式。测试 jsonb 中所有键的存在（等效于 ?& 运算符）

method sqlalchemy.dialects.postgresql.JSONB.Comparator.has_any(other)

布尔表达式。测试 jsonb 中任何键的存在（等效于 ?| 运算符）

method sqlalchemy.dialects.postgresql.JSONB.Comparator.has_key(other)

布尔表达式。测试键的存在（等效于 ? 运算符）。请注意，键可以是 SQLA 表达式。

method sqlalchemy.dialects.postgresql.JSONB.Comparator.path_exists(other)

布尔表达式。测试参数 JSONPath 表达式给出的项目的是否存在（等效于 @? 运算符）。

版本 2.0 中的新增功能。

method sqlalchemy.dialects.postgresql.JSONB.Comparator.path_match(other)

布尔表达式。测试参数 JSONPath 表达式给出的 JSONPath 谓词是否匹配（等效于 @@ 运算符）。

只考虑结果的第一个项目。

版本 2.0 中的新增功能。

attribute sqlalchemy.dialects.postgresql.JSONB.comparator_factory

Comparator 的别名

class sqlalchemy.dialects.postgresql.JSONPATH

JSON 路径类型。

这通常需要在使用 json 搜索功能（例如 jsonb_path_query_array 或 jsonb_path_exists）时将文字值转换为 json 路径。

stmt = sa.select(
    sa.func.jsonb_path_query_array(
        table.c.jsonb_col, cast("$.address.id", JSONPATH)
    )
)

类签名

class sqlalchemy.dialects.postgresql.JSONPATH (sqlalchemy.dialects.postgresql.json.JSONPathType)

class sqlalchemy.dialects.postgresql.MACADDR

类签名

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

class sqlalchemy.dialects.postgresql.MACADDR8

类签名

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

class sqlalchemy.dialects.postgresql.MONEY

提供 PostgreSQL MONEY 类型。

根据驱动程序的不同，使用此类型的结果行可能会返回包含货币符号的字符串值。

因此，最好使用 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

或者，可以使用 TypeDecorator.column_expression() 方法将转换应用为 CAST，如下所示。

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

版本 1.2 中新增。

类签名

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

class sqlalchemy.dialects.postgresql.OID

提供 PostgreSQL OID 类型。

类签名

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

class sqlalchemy.dialects.postgresql.REAL

SQL REAL 类型。

另请参阅

Float - 基本类型的文档。

类签名

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)

继承自 sqlalchemy.types.Float.__init__ 方法的 Float

构造一个 Float。

参数：:

• precision –

  用于 DDL CREATE TABLE 的数值精度。后端 **应该** 尝试确保此精度指示通用 Float 数据类型的数字位数。

  注意

  对于 Oracle 后端，当渲染 DDL 时，不支持 Float.precision 参数，因为 Oracle 不支持以十进制位数指定浮点数精度。相反，使用 Oracle 特定的 FLOAT 数据类型并指定 FLOAT.binary_precision 参数。这是 SQLAlchemy 2.0 版本的新功能。

  要创建数据库无关的 Float ，它为 Oracle 单独指定二进制精度，请使用 TypeEngine.with_variant() ，如下所示

  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 – 与 Numeric 的标志相同，但默认值为 False。请注意，将此标志设置为 True 会导致浮点数转换。

• decimal_return_scale – 从浮点数转换为 Python 十进制数时使用的默认比例。由于小数精度，浮点数的值通常会长得多，而且大多数浮点数据库类型没有“比例”的概念，因此默认情况下，浮点类型在转换时会查找前十个小数位。指定此值将覆盖该长度。请注意，包含“比例”的 MySQL 浮点类型将使用“比例”作为 decimal_return_scale 的默认值（如果未另行指定）。

class sqlalchemy.dialects.postgresql.REGCONFIG

提供 PostgreSQL REGCONFIG 类型。

版本 2.0.0rc1 中的新增功能。

类签名

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

class sqlalchemy.dialects.postgresql.REGCLASS

提供 PostgreSQL REGCLASS 类型。

版本 1.2.7 中的新增功能。

类签名

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

class sqlalchemy.dialects.postgresql.TIMESTAMP

提供 PostgreSQL TIMESTAMP 类型。

成员

__init__()

类签名

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

method sqlalchemy.dialects.postgresql.TIMESTAMP.__init__(timezone: bool = False, precision: int | None = None) → None

构造一个 TIMESTAMP。

参数：:

• timezone – 如果存在时区，则为布尔值，默认值为 False

• precision –

  可选的整数精度值。

  新版功能，版本 1.4。

class sqlalchemy.dialects.postgresql.TIME

PostgreSQL TIME 类型。

成员

__init__()

类签名

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

method sqlalchemy.dialects.postgresql.TIME.__init__(timezone: bool = False, precision: int | None = None) → None

构造一个 TIME。

参数：:

• timezone – 如果存在时区，则为布尔值，默认值为 False

• precision –

  可选的整数精度值。

  新版功能，版本 1.4。

class sqlalchemy.dialects.postgresql.TSQUERY

提供 PostgreSQL TSQUERY 类型。

版本 2.0.0rc1 中的新增功能。

类签名

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

class sqlalchemy.dialects.postgresql.TSVECTOR

TSVECTOR 类型实现 PostgreSQL 文本搜索类型 TSVECTOR。

它可以用于对自然语言文档进行全文查询。

另请参阅

全文搜索

类签名

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

class sqlalchemy.dialects.postgresql.UUID

表示 SQL UUID 类型。

这是 Uuid 数据库不可知数据类型的 SQL 本地形式，并且向后兼容以前的仅 PostgreSQL 版本的 UUID。

UUID 数据类型仅适用于具有名为 UUID 的 SQL 数据类型的数据库。它不适用于没有这种确切命名类型的后端，包括 SQL Server。对于具有原生支持的与后端无关的 UUID 值，包括 SQL Server 的 UNIQUEIDENTIFIER 数据类型，请使用 Uuid 数据类型。

版本 2.0 中的新增功能。

另请参阅

Uuid

类签名

class sqlalchemy.dialects.postgresql.UUID (sqlalchemy.types.Uuid, sqlalchemy.types.NativeForEmulated)

method sqlalchemy.dialects.postgresql.UUID.__init__(as_uuid: bool = True)

构造一个 UUID 类型。

参数：:

as_uuid=True –

如果为 True，则值将被解释为 Python uuid 对象，通过 DBAPI 进行字符串转换。

class sqlalchemy.dialects.postgresql.INT4RANGE

表示 PostgreSQL INT4RANGE 类型。

类签名

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

class sqlalchemy.dialects.postgresql.INT8RANGE

表示 PostgreSQL INT8RANGE 类型。

类签名

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

class sqlalchemy.dialects.postgresql.NUMRANGE

表示 PostgreSQL NUMRANGE 类型。

类签名

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

class sqlalchemy.dialects.postgresql.DATERANGE

表示 PostgreSQL DATERANGE 类型。

类签名

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

class sqlalchemy.dialects.postgresql.TSRANGE

表示 PostgreSQL TSRANGE 类型。

类签名

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

class sqlalchemy.dialects.postgresql.TSTZRANGE

表示 PostgreSQL TSTZRANGE 类型。

类签名

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

class sqlalchemy.dialects.postgresql.INT4MULTIRANGE

表示 PostgreSQL INT4MULTIRANGE 类型。

类签名

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

class sqlalchemy.dialects.postgresql.INT8MULTIRANGE

表示 PostgreSQL INT8MULTIRANGE 类型。

类签名

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

class sqlalchemy.dialects.postgresql.NUMMULTIRANGE

表示 PostgreSQL NUMMULTIRANGE 类型。

类签名

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

class sqlalchemy.dialects.postgresql.DATEMULTIRANGE

表示 PostgreSQL DATEMULTIRANGE 类型。

类签名

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

class sqlalchemy.dialects.postgresql.TSMULTIRANGE

表示 PostgreSQL TSRANGE 类型。

类签名

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

class sqlalchemy.dialects.postgresql.TSTZMULTIRANGE

表示 PostgreSQL TSTZRANGE 类型。

类签名

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

class sqlalchemy.dialects.postgresql.MultiRange

表示多范围序列。

此列表子类是允许根据单一范围值自动推断正确的多范围 SQL 类型的一个实用程序。这在对文字多范围进行操作时非常有用。

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 sqlalchemy.dialects.postgresql.MultiRange (builtins.list, typing.Generic)

PostgreSQL SQL 元素和函数

对象名称	描述
aggregate_order_by	表示一个 PostgreSQL 聚合排序表达式。
All(other, arrexpr[, operator])	ARRAY 级别的 Comparator.all() 方法的同义词。有关详细信息，请参见该方法。
Any(other, arrexpr[, operator])	ARRAY 级别的 Comparator.any() 方法的同义词。有关详细信息，请参见该方法。
array	PostgreSQL ARRAY 字面量。
array_agg(*arg, **kw)	PostgreSQL 特定的 array_agg 形式，确保返回类型为 ARRAY 而不是普通的 ARRAY，除非传递了显式的 type_。
hstore	使用 PostgreSQL hstore() 函数在 SQL 表达式中构建 hstore 值。
phraseto_tsquery	PostgreSQL phraseto_tsquery SQL 函数。
plainto_tsquery	PostgreSQL plainto_tsquery SQL 函数。
to_tsquery	PostgreSQL to_tsquery SQL 函数。
to_tsvector	PostgreSQL to_tsvector SQL 函数。
ts_headline	PostgreSQL ts_headline SQL 函数。
websearch_to_tsquery	PostgreSQL websearch_to_tsquery SQL 函数。

class sqlalchemy.dialects.postgresql.aggregate_order_by

表示一个 PostgreSQL 聚合排序表达式。

例如。

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)

将表示表达式

SELECT array_agg(a ORDER BY b DESC) FROM table;

同样

expr = func.string_agg(
    table.c.a,
    aggregate_order_by(literal_column("','"), table.c.a)
)
stmt = select(expr)

将表示

SELECT string_agg(a, ',' ORDER BY a) FROM table;

在版本 1.2.13 中变更: - ORDER BY 参数可以是多个项

另请参阅

array_agg

类签名

class sqlalchemy.dialects.postgresql.aggregate_order_by (sqlalchemy.sql.expression.ColumnElement)

class sqlalchemy.dialects.postgresql.array

PostgreSQL ARRAY 字面量。

这用于在 SQL 表达式中生成 ARRAY 字面量，例如

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

生成 SQL

SELECT ARRAY[%(param_1)s, %(param_2)s] ||
    ARRAY[%(param_3)s, %(param_4)s, %(param_5)s]) AS anon_1

array 的实例始终具有数据类型 ARRAY。除非传递了 type_ 关键字参数，否则数组的“内部”类型将从存在的数值中推断。

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

多维数组是通过嵌套 array 结构生成的。最终 ARRAY 类型的维度是通过递归地添加内部 ARRAY 类型的维度来计算的。

stmt = select(
    array([
        array([1, 2]), array([3, 4]), array([column('q'), column('x')])
    ])
)
print(stmt.compile(dialect=postgresql.dialect()))

生成

SELECT ARRAY[ARRAY[%(param_1)s, %(param_2)s],
ARRAY[%(param_3)s, %(param_4)s], ARRAY[q, x]] AS anon_1

在版本 1.3.6 中添加: 添加了对多维数组字面量的支持。

另请参阅

ARRAY

类签名

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

function sqlalchemy.dialects.postgresql.array_agg(*arg, **kw)

PostgreSQL 特定的 array_agg 形式，确保返回类型为 ARRAY 而不是普通的 ARRAY，除非传递了显式的 type_。

function sqlalchemy.dialects.postgresql.Any(other, arrexpr, operator=<built-in function eq>)

ARRAY 级别的 Comparator.any() 方法的同义词。有关详细信息，请参见该方法。

function sqlalchemy.dialects.postgresql.All(other, arrexpr, operator=<built-in function eq>)

ARRAY 级别的 Comparator.all() 方法的同义词。有关详细信息，请参见该方法。

class sqlalchemy.dialects.postgresql.hstore

使用 PostgreSQL hstore() 函数在 SQL 表达式中构建 hstore 值。

hstore 函数接受一个或两个参数，如 PostgreSQL 文档中所述。

例如。

from sqlalchemy.dialects.postgresql import array, hstore

select(hstore('key1', 'value1'))

select(
    hstore(
        array(['key1', 'key2', 'key3']),
        array(['value1', 'value2', 'value3'])
    )
)

另请参阅

HSTORE - PostgreSQL HSTORE 数据类型。

成员

inherit_cache, type

类签名

class sqlalchemy.dialects.postgresql.hstore (sqlalchemy.sql.functions.GenericFunction)

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

指示此 HasCacheKey 实例是否应该使用其直接超类使用的缓存键生成方案。

该属性默认为 None，这意味着构造尚未考虑其是否适合参与缓存；在功能上等同于将该值设置为 False，只是还会发出警告。

如果与对象相对应的 SQL 不基于此类的本地属性（而不是其超类）而改变，则可以将此标志设置为特定类的 True。

另请参阅

为自定义构造启用缓存支持 - 为第三方或用户定义的 SQL 构造设置 HasCacheKey.inherit_cache 属性的一般指南。

attribute sqlalchemy.dialects.postgresql.hstore.type

HSTORE 的别名

class sqlalchemy.dialects.postgresql.to_tsvector

PostgreSQL to_tsvector SQL 函数。

此函数应用 REGCONFIG 参数的自动强制转换，以自动使用 REGCONFIG 数据类型，并应用 TSVECTOR 的返回类型。

假设已经导入 PostgreSQL 方言，无论是通过调用 from sqlalchemy.dialects import postgresql 还是通过使用 create_engine("postgresql...") 创建 PostgreSQL 引擎，to_tsvector 会在调用 sqlalchemy.func.to_tsvector() 时自动使用，确保在编译和执行时使用正确的参数和返回类型处理程序。

版本 2.0.0rc1 中的新增功能。

类签名

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

class sqlalchemy.dialects.postgresql.to_tsquery

PostgreSQL to_tsquery SQL 函数。

此函数会自动将 REGCONFIG 参数转换为使用 REGCONFIG 数据类型，并应用 TSQUERY 的返回类型。

假设已经导入 PostgreSQL 方言，无论是通过调用 from sqlalchemy.dialects import postgresql 还是通过使用 create_engine("postgresql...") 创建 PostgreSQL 引擎，to_tsquery 会在调用 sqlalchemy.func.to_tsquery() 时自动使用，确保在编译和执行时使用正确的参数和返回类型处理程序。

版本 2.0.0rc1 中的新增功能。

类签名

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

class sqlalchemy.dialects.postgresql.plainto_tsquery

PostgreSQL plainto_tsquery SQL 函数。

此函数会自动将 REGCONFIG 参数转换为使用 REGCONFIG 数据类型，并应用 TSQUERY 的返回类型。

假设已经导入 PostgreSQL 方言，无论是通过调用 from sqlalchemy.dialects import postgresql 还是通过使用 create_engine("postgresql...") 创建 PostgreSQL 引擎，plainto_tsquery 会在调用 sqlalchemy.func.plainto_tsquery() 时自动使用，确保在编译和执行时使用正确的参数和返回类型处理程序。

版本 2.0.0rc1 中的新增功能。

类签名

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

class sqlalchemy.dialects.postgresql.phraseto_tsquery

PostgreSQL phraseto_tsquery SQL 函数。

此函数会自动将 REGCONFIG 参数转换为使用 REGCONFIG 数据类型，并应用 TSQUERY 的返回类型。

假设已经导入 PostgreSQL 方言，无论是通过调用 from sqlalchemy.dialects import postgresql 还是通过使用 create_engine("postgresql...") 创建 PostgreSQL 引擎，phraseto_tsquery 会在调用 sqlalchemy.func.phraseto_tsquery() 时自动使用，确保在编译和执行时使用正确的参数和返回类型处理程序。

版本 2.0.0rc1 中的新增功能。

类签名

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

class sqlalchemy.dialects.postgresql.websearch_to_tsquery

PostgreSQL websearch_to_tsquery SQL 函数。

此函数会自动将 REGCONFIG 参数转换为使用 REGCONFIG 数据类型，并应用 TSQUERY 的返回类型。

假设已经导入 PostgreSQL 方言，无论是通过调用 from sqlalchemy.dialects import postgresql 还是通过使用 create_engine("postgresql...") 创建 PostgreSQL 引擎，websearch_to_tsquery 会在调用 sqlalchemy.func.websearch_to_tsquery() 时自动使用，确保在编译和执行时使用正确的参数和返回类型处理程序。

版本 2.0.0rc1 中的新增功能。

类签名

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

class sqlalchemy.dialects.postgresql.ts_headline

PostgreSQL ts_headline SQL 函数。

此函数会自动将 REGCONFIG 参数转换为使用 REGCONFIG 数据类型，并应用 TEXT 的返回类型。

假设已经导入 PostgreSQL 方言，无论是通过调用 from sqlalchemy.dialects import postgresql 还是通过使用 create_engine("postgresql...") 创建 PostgreSQL 引擎，ts_headline 会在调用 sqlalchemy.func.ts_headline() 时自动使用，确保在编译和执行时使用正确的参数和返回类型处理程序。

版本 2.0.0rc1 中的新增功能。

类签名

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

PostgreSQL 约束类型

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

对象名称	描述
ExcludeConstraint	表级别的 EXCLUDE 约束。

class sqlalchemy.dialects.postgresql.ExcludeConstraint

表级别的 EXCLUDE 约束。

定义 EXCLUDE 约束，如 PostgreSQL 文档 中所述。

成员

__init__()

类签名

class sqlalchemy.dialects.postgresql.ExcludeConstraint (sqlalchemy.schema.ColumnCollectionConstraint)

method sqlalchemy.dialects.postgresql.ExcludeConstraint.__init__(*elements, **kw)

创建一个 ExcludeConstraint 对象。

例如。

const = ExcludeConstraint(
    (Column('period'), '&&'),
    (Column('group'), '='),
    where=(Column('group') != 'some group'),
    ops={'group': 'my_operator_class'}
)

约束通常直接嵌入到 Table 结构中，或使用 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'}
    )
)

在本示例中定义的排除约束需要 btree_gist 扩展，可以使用命令 CREATE EXTENSION btree_gist; 创建。

参数：:

• *elements –

  一个包含两个元组的序列，元组格式为 (column, operator)，其中“column”是 Column 对象，或 SQL 表达式元素（例如 func.int8range(table.from, table.to)）或列名称作为字符串，而“operator”是一个包含要使用的运算符的字符串（例如 “&&” 或 “=”）。

  为了在 Column 对象不可用时指定列名，同时确保任何必要的引用规则生效，应该使用临时的 Column 或 column() 对象。当作为 literal_column() 或 text() 传递时，column 也可以是字符串 SQL 表达式。

• name – 可选，此约束的数据库内名称。

• deferrable – 可选布尔值。如果设置，在为该约束发出 DDL 时发出 DEFERRABLE 或 NOT DEFERRABLE。

• initially – 可选字符串。如果设置，在为该约束发出 DDL 时发出 INITIALLY <value>。

• using – 可选字符串。如果设置，在为该约束发出 DDL 时发出 USING <index_method>。默认值为 ‘gist’。

• where –

  可选 SQL 表达式构造或文字 SQL 字符串。如果设置，在为该约束发出 DDL 时发出 WHERE <predicate>。

  警告

  传入 ExcludeConstraint.where 参数到 ExcludeConstraint 可以作为 Python 字符串参数传递，它将被视为**受信任的 SQL 文本**并按原样呈现。**不要将不受信任的输入传递给此参数**。

• ops –

  可选字典。用于为元素定义运算符类；工作方式与指定给 Index 构造的 postgresql_ops 参数相同。

  版本 1.3.21 中的新增功能。

  另请参阅

  运算符类 - 如何指定 PostgreSQL 运算符类的总体描述。

例如

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

对象名称	描述
insert(table)	构造一个 PostgreSQL 特定变体 Insert 构造。
Insert	PostgreSQL 特定的 INSERT 实现。

function sqlalchemy.dialects.postgresql.insert(table: _DMLTableArgument) → Insert

构造一个 PostgreSQL 特定变体 Insert 构造。

函数 sqlalchemy.dialects.postgresql.insert() 创建一个 sqlalchemy.dialects.postgresql.Insert。此类基于与方言无关的 Insert 构造，可以使用 SQLAlchemy Core 中的 insert() 函数构造。

构造 Insert 包含附加方法 Insert.on_conflict_do_update()、Insert.on_conflict_do_nothing()。

class sqlalchemy.dialects.postgresql.Insert

PostgreSQL 特定的 INSERT 实现。

添加对 PG 特定语法（如 ON CONFLICT）的支持。

对象 Insert 使用函数 sqlalchemy.dialects.postgresql.insert() 创建。

成员

excluded, inherit_cache, on_conflict_do_nothing(), on_conflict_do_update()

类签名

class sqlalchemy.dialects.postgresql.Insert (sqlalchemy.sql.expression.Insert)

attribute sqlalchemy.dialects.postgresql.Insert.excluded

为 ON CONFLICT 语句提供 excluded 命名空间

PG 的 ON CONFLICT 子句允许引用将要插入的行，称为 excluded。此属性提供此行中的所有列以供引用。

提示

属性 Insert.excluded 是 ColumnCollection 的一个实例，它提供的接口与在 访问表和列 中描述的 Table.c 集合相同。使用此集合，普通名称可以像属性一样访问（例如 stmt.excluded.some_column），但特殊名称和字典方法名称应使用索引访问，例如 stmt.excluded["column name"] 或 stmt.excluded["values"]。有关更多示例，请参阅 ColumnCollection 的文档字符串。

另请参阅

INSERT…ON CONFLICT（更新） - 如何使用 Insert.excluded 的示例

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

指示此 HasCacheKey 实例是否应该使用其直接超类使用的缓存键生成方案。

该属性默认为 None，这意味着构造尚未考虑其是否适合参与缓存；在功能上等同于将该值设置为 False，只是还会发出警告。

如果与对象相对应的 SQL 不基于此类的本地属性（而不是其超类）而改变，则可以将此标志设置为特定类的 True。

另请参阅

为自定义构造启用缓存支持 - 为第三方或用户定义的 SQL 构造设置 HasCacheKey.inherit_cache 属性的一般指南。

方法 sqlalchemy.dialects.postgresql.Insert.on_conflict_do_nothing(constraint: _OnConflictConstraintT = None, index_elements: _OnConflictIndexElementsT = None, index_where: _OnConflictIndexWhereT = None) → Self

指定 ON CONFLICT 子句的 DO NOTHING 操作。

constraint 和 index_elements 参数是可选的，但只能指定其中一个。

参数：:

• constraint – 表上唯一约束或排除约束的名称，或者约束对象本身（如果它具有 .name 属性）。

• index_elements – 一个序列，包含字符串列名、Column 对象或其他列表达式对象，这些对象将用于推断目标索引。

• index_where – 可以用于推断条件目标索引的附加 WHERE 条件。

另请参阅

INSERT…ON CONFLICT (Upsert)

方法 sqlalchemy.dialects.postgresql.Insert.on_conflict_do_update(constraint: _OnConflictConstraintT = None, index_elements: _OnConflictIndexElementsT = None, index_where: _OnConflictIndexWhereT = None, set_: _OnConflictSetT = None, where: _OnConflictWhereT = None) → Self

指定 ON CONFLICT 子句的 DO UPDATE SET 操作。

constraint 或 index_elements 参数是必需的，但只能指定其中一个。

参数：:

• constraint – 表上唯一约束或排除约束的名称，或者约束对象本身（如果它具有 .name 属性）。

• index_elements – 一个序列，包含字符串列名、Column 对象或其他列表达式对象，这些对象将用于推断目标索引。

• index_where – 可以用于推断条件目标索引的附加 WHERE 条件。

• set_ –

  一个字典或其他映射对象，其中键是目标表中列的名称，或者 Column 对象或其他 ORM 映射的列，与目标表匹配，以及表达式或字面值作为值，指定要采取的 SET 操作。

  版本 1.4 中的新功能: The Insert.on_conflict_do_update.set_ 参数支持来自目标 Table 的 Column 对象作为键。

  警告

  这个字典不考虑 Python 指定的默认 UPDATE 值或生成函数，例如使用 Column.onupdate 指定的函数。这些值不会在 ON CONFLICT 样式的 UPDATE 中执行，除非它们在 Insert.on_conflict_do_update.set_ 字典中手动指定。

• where – 可选参数。如果存在，可以是文字 SQL 字符串或 WHERE 子句的可接受表达式，该子句限制 DO UPDATE SET 影响的行。不满足 WHERE 条件的行不会被更新（对这些行来说，实际上是 DO NOTHING）。

另请参阅

INSERT…ON CONFLICT (Upsert)

psycopg2

通过 psycopg2 驱动程序支持 PostgreSQL 数据库。

DBAPI

psycopg2 的文档和下载信息（如果适用）可在以下位置找到：https://pypi.ac.cn/project/psycopg2/

连接

连接字符串

postgresql+psycopg2://user:password@host:port/dbname[?key=value&key=value...]

psycopg2 连接参数

特定于 SQLAlchemy psycopg2 方言的关键字参数可以传递给 create_engine()，并包括以下内容

• isolation_level: 此选项适用于所有 PostgreSQL 方言，包括使用 psycopg2 方言时的 AUTOCOMMIT 隔离级别。此选项设置连接的默认隔离级别，该隔离级别在连接到数据库后立即设置，在连接被池化之前。此选项通常会被更现代的 Connection.execution_options.isolation_level 执行选项所取代，该选项在 设置事务隔离级别，包括 DBAPI Autocommit 中进行了详细介绍。

  另请参阅

  Psycopg2 事务隔离级别

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

• client_encoding: 使用 psycopg2 的 set_client_encoding() 方法以与 libpq 无关的方式设置客户端编码。

  另请参阅

  Psycopg2 中的 Unicode

• executemany_mode, executemany_batch_page_size, executemany_values_page_size: 允许使用 psycopg2 扩展来优化“executemany”风格的查询。有关详细信息，请参阅下面引用的部分。

  另请参阅

  Psycopg2 快速执行助手

提示

上述关键字参数是方言关键字参数，这意味着它们作为显式关键字参数传递给 create_engine()

engine = create_engine(
    "postgresql+psycopg2://scott:tiger@localhost/test",
    isolation_level="SERIALIZABLE",
)

不要将它们与DBAPI 连接参数混淆，DBAPI 连接参数作为 create_engine.connect_args 字典的一部分传递，或者在 URL 查询字符串中传递，如 自定义 DBAPI connect() 参数 / 连接时例程 部分所述。

SSL 连接

psycopg2 模块有一个名为 sslmode 的连接参数，用于控制其在安全（SSL）连接方面的行为。默认值为 sslmode=prefer；它将尝试建立 SSL 连接，如果失败，它将回退到未加密连接。可以使用 sslmode=require 来确保只建立安全连接。有关可用选项的更多信息，请参阅 psycopg2 / libpq 文档。

请注意，sslmode 是特定于 psycopg2 的，因此它包含在连接 URI 中

engine = sa.create_engine(
    "postgresql+psycopg2://scott:[email protected]:5432/test?sslmode=require"
)

Unix 域连接

psycopg2 支持通过 Unix 域连接进行连接。当 URL 的 host 部分被省略时，SQLAlchemy 会将 None 传递给 psycopg2，它指定 Unix 域通信而不是 TCP/IP 通信

create_engine("postgresql+psycopg2://user:password@/dbname")

默认情况下，用于连接到 Unix 域套接字的套接字文件位于 /tmp 中，或者是在构建 PostgreSQL 时指定的任何套接字目录。可以通过将路径名传递给 psycopg2 来覆盖此值，使用 host 作为额外的关键字参数

create_engine("postgresql+psycopg2://user:password@/dbname?host=/var/lib/postgresql")

警告

这里接受的格式允许在主 URL 中使用主机名，除了“host”查询字符串参数。**当使用此 URL 格式时，初始主机将被静默忽略**。也就是说，这个 URL

engine = create_engine("postgresql+psycopg2://user:password@myhost1/dbname?host=myhost2")

上面，主机名 myhost1 被**静默忽略并丢弃**。连接到的主机是 myhost2 主机。

这样做是为了与 PostgreSQL 自己的 URL 格式保持一定程度的兼容性，该格式经过测试表现相同，并且像 PifPaf 这样的工具在其中硬编码了两个主机名。

另请参阅

PQconnectdbParams

指定多个备用主机

psycopg2 在连接字符串中支持多个连接点。当 host 参数在 URL 的查询部分中被多次使用时，SQLAlchemy 将创建一个包含主机和端口信息的单个字符串，用于建立连接。令牌可以包含 host::port 或仅 host；在后一种情况下，默认端口由 libpq 选择。在下面的示例中，指定了三个主机连接，分别为 HostA::PortA、连接到默认端口的 HostB 和 HostC::PortC

create_engine(
    "postgresql+psycopg2://user:password@/dbname?host=HostA:PortA&host=HostB&host=HostC:PortC"
)

作为替代方案，也可以使用 libpq 查询字符串格式；它将 host 和 port 指定为单个查询字符串参数，并使用逗号分隔列表 - 可以通过在逗号分隔列表中指示空值来选择默认端口

create_engine(
    "postgresql+psycopg2://user:password@/dbname?host=HostA,HostB,HostC&port=PortA,,PortC"
)

无论使用哪种 URL 样式，都将根据可配置的策略尝试连接到每个主机，该策略可以使用 libpq target_session_attrs 参数进行配置。根据 libpq 的默认值是 any，它表示将尝试连接到每个主机，直到成功建立连接为止。其他策略包括 primary、prefer-standby 等。完整的列表由 PostgreSQL 在 libpq 连接字符串 中进行文档化。

例如，要使用 primary 策略指示两个主机

create_engine(
    "postgresql+psycopg2://user:password@/dbname?host=HostA:PortA&host=HostB&host=HostC:PortC&target_session_attrs=primary"
)

版本 1.4.40 中的变更： psycopg2 多主机格式中的端口规范已修复，以前在该上下文中无法正确解释端口。现在也支持 libpq 逗号分隔格式。

版本 1.3.20 中的新增： 支持 PostgreSQL 连接字符串中的多个主机。

另请参阅

libpq 连接字符串 - 请参考 libpq 文档中的这一部分以获取有关多主机支持的完整背景信息。

空 DSN 连接/环境变量连接

psycopg2 DBAPI 可以通过将空 DSN 传递给 libpq 客户端库来连接到 PostgreSQL，该库默认指示连接到一个为“信任”连接开放的本地 PostgreSQL 数据库。此行为可以使用一组特定的环境变量进行进一步定制，这些环境变量以 PG_... 为前缀，它们由 libpq 使用来代替连接字符串中的任何或所有元素。

对于此形式，URL 可以不包含任何元素，除了初始方案之外

engine = create_engine('postgresql+psycopg2://')

在上面的形式中，一个空白的“dsn”字符串被传递给 psycopg2.connect() 函数，该函数反过来代表一个传递给 libpq 的空 DSN。

版本 1.3.2 中的新增： 支持使用 psycopg2 的无参数连接。

另请参阅

环境变量 - PostgreSQL 文档介绍如何使用 PG_... 环境变量进行连接。

每个语句/连接执行选项

当与 Connection.execution_options()、Executable.execution_options()、Query.execution_options() 一起使用时，以下特定于 DBAPI 的选项会被尊重，此外还有那些不特定于 DBAPI 的选项

• isolation_level - 为 Connection 的生命周期设置事务隔离级别（只能在连接上设置，不能在语句或查询上设置）。请参见 Psycopg2 事务隔离级别。

• stream_results - 启用或禁用 psycopg2 服务器端游标的使用 - 此功能使用“命名”游标与特殊的处理结果方法结合使用，以便结果行不会被完全缓冲。默认值为 False，这意味着游标默认情况下会被缓冲。

• max_row_buffer - 使用 stream_results 时，一个整数值，指定一次缓冲的最大行数。这将被 BufferedRowCursorResult 解释，如果省略，缓冲区将增长到最终一次存储 1000 行。

  版本 1.4 中的变更： max_row_buffer 大小现在可以大于 1000，缓冲区将增长到该大小。

Psycopg2 快速执行助手

现代版本的 psycopg2 包含一个称为 快速执行助手 的功能，在基准测试中证明，它至少提高了 psycopg2 的 executemany() 性能一个数量级，主要是针对 INSERT 语句。

SQLAlchemy 实现了一种“插入多个值”处理程序的原生形式，它将重写一个单行 INSERT 语句，以适应扩展 VALUES 子句中的多个值；此处理程序等效于 psycopg2 的 execute_values() 处理程序；此功能及其配置的概述位于 “插入多个值” INSERT 语句的行为。

版本 2.0 中的新增： 用称为 insertmanyvalues 的原生 SQLAlchemy 机制替换了 psycopg2 的 execute_values() 快速执行助手。

psycopg2 方言保留了使用特定于 psycopg2 的 execute_batch() 功能的能力，尽管预计这不会是一个广泛使用的功能。可以使用 executemany_mode 标志启用此扩展，该标志可以传递给 create_engine()

engine = create_engine(
    "postgresql+psycopg2://scott:tiger@host/dbname",
    executemany_mode='values_plus_batch')

可能的 executemany_mode 选项包括

• values_only - 这是默认值。SQLAlchemy 的原生 insertmanyvalues 处理程序用于限定 INSERT 语句，假设 create_engine.use_insertmanyvalues 保持其默认值 True。此处理程序会重写简单的 INSERT 语句以包含多个 VALUES 子句，以便使用一个语句插入多个参数集。

• 'values_plus_batch'- SQLAlchemy 的原生 insertmanyvalues 处理程序用于限定 INSERT 语句，假设 create_engine.use_insertmanyvalues 保持其默认值 True。然后，psycopg2 的 execute_batch() 处理程序用于限定 UPDATE 和 DELETE 语句，这些语句在使用多个参数集执行时。当使用此模式时，CursorResult.rowcount 属性将不包含针对 UPDATE 和 DELETE 语句的 executemany 样式执行的值。

版本 2.0 中的变更: 从 psycopg2 executemany_mode 中删除了 'batch' 和 'None' 选项。现在通过 create_engine.use_insertmanyvalues 引擎级参数来配置 INSERT 语句的批处理控制。

术语“限定语句”是指正在执行的语句是 Core insert()、update() 或 delete() 结构，**而不是**纯文本 SQL 字符串或使用 text() 构造的字符串。它也**可能不是**诸如“ON CONFLICT” “upsert”语句之类的特殊“扩展”语句。在使用 ORM 时，ORM 刷新过程中使用的所有插入/更新/删除语句都是限定语句。

psycopg2 “batch” 策略的“页面大小”可以通过使用 executemany_batch_page_size 参数来影响，该参数默认为 100。

对于“insertmanyvalues”功能，可以使用 create_engine.insertmanyvalues_page_size 参数来控制页面大小，该参数默认为 1000。下面是修改这两个参数的示例。

engine = create_engine(
    "postgresql+psycopg2://scott:tiger@host/dbname",
    executemany_mode='values_plus_batch',
    insertmanyvalues_page_size=5000, executemany_batch_page_size=500)

另请参阅

“Insert Many Values” Behavior for INSERT statements - 关于“insertmanyvalues”的背景信息

Sending Multiple Parameters - 使用 Connection 对象以利用 DBAPI .executemany() 方法执行语句的一般信息。

Psycopg2 中的 Unicode

psycopg2 DBAPI 驱动程序透明地支持 Unicode 数据。

可以通过以下几种方式控制 psycopg2 方言的客户端字符编码

• 对于 PostgreSQL 9.1 及更高版本，可以在数据库 URL 中传递 client_encoding 参数；此参数由底层的 libpq PostgreSQL 客户端库使用

  engine = create_engine("postgresql+psycopg2://user:pass@host/dbname?client_encoding=utf8")

  或者，可以使用 create_engine.connect_args 通过编程方式与 libpq 建立连接，传递上述 client_encoding 值

  engine = create_engine(
      "postgresql+psycopg2://user:pass@host/dbname",
      connect_args={'client_encoding': 'utf8'}
  )

• 对于所有 PostgreSQL 版本，psycopg2 都支持在首次建立数据库连接时传递给数据库连接的客户端编码值。SQLAlchemy psycopg2 方言使用传递给 create_engine() 的 client_encoding 参数来支持这一点

  engine = create_engine(
      "postgresql+psycopg2://user:pass@host/dbname",
      client_encoding="utf8"
  )

  提示

  上面的 client_encoding 参数与在 create_engine.connect_args 字典中使用该参数的方式非常相似；上述区别在于该参数由 psycopg2 使用，并使用 SET client_encoding TO 'utf8' 传递给数据库连接；在前面提到的方式中，该参数通过 psycopg2 传递，并由 libpq 库使用。

• 使用 PostgreSQL 数据库设置客户端编码的常用方法是确保它在服务器端的 postgresql.conf 文件中进行配置；这是为始终以一种编码在所有数据库中使用的服务器设置编码的推荐方法

  # postgresql.conf file
  
  # client_encoding = sql_ascii # actually, defaults to database
                               # encoding
  client_encoding = utf8

事务

psycopg2 方言完全支持 SAVEPOINT 和两阶段提交操作。

Psycopg2 事务隔离级别

如 Transaction Isolation Level 中所述，所有 PostgreSQL 方言都支持通过传递给 create_engine() 的 isolation_level 参数以及 Connection.execution_options() 使用的 isolation_level 参数来设置事务隔离级别。在使用 psycopg2 方言时，这些选项会使用 psycopg2 的 set_isolation_level() 连接方法，而不是发出 PostgreSQL 指令；这是因为 psycopg2 的 API 级设置始终在任何情况下都将在每个事务的开始时发出。

psycopg2 方言支持以下隔离级别常量

• READ COMMITTED

• READ UNCOMMITTED

• REPEATABLE READ

• SERIALIZABLE

• AUTOCOMMIT

另请参阅

事务隔离级别

pg8000 事务隔离级别

NOTICE 日志记录

psycopg2 方言将通过 sqlalchemy.dialects.postgresql 记录器记录 PostgreSQL NOTICE 消息。当此记录器设置为 logging.INFO 级别时，将记录通知消息

import logging

logging.getLogger('sqlalchemy.dialects.postgresql').setLevel(logging.INFO)

上面假定日志记录在外部配置。如果不是这种情况，则必须使用诸如 logging.basicConfig() 之类的配置

import logging

logging.basicConfig()   # log messages to stdout
logging.getLogger('sqlalchemy.dialects.postgresql').setLevel(logging.INFO)

另请参阅

Logging HOWTO - 在 python.org 网站上

HSTORE 类型

psycopg2 DBAPI 包含一个扩展，用于本机处理 HSTORE 类型的编组。当使用 psycopg2 版本 2.4 或更高版本时，SQLAlchemy psycopg2 方言将默认启用此扩展，并且检测到目标数据库已设置 HSTORE 类型以供使用。换句话说，当方言建立第一个连接时，将执行以下类似的序列

1. 使用 psycopg2.extras.HstoreAdapter.get_oids() 请求可用的 HSTORE oid。如果此函数返回一个 HSTORE 标识符列表，那么我们将确定 HSTORE 扩展存在。如果安装的 psycopg2 版本低于 2.4 版本，则会跳过此函数。

2. 如果 use_native_hstore 标志处于其默认值 True，并且我们检测到 HSTORE oid 可用，则 psycopg2.extensions.register_hstore() 扩展将在所有连接中调用。

register_hstore() 扩展的效果是**所有 Python 字典都将被接受为参数，无论 SQL 中目标列的类型如何**。这些字典由此扩展转换为文本 HSTORE 表达式。如果不需要这种行为，请通过将 use_native_hstore 设置为 False 来禁用 hstore 扩展的使用，如下所示

engine = create_engine("postgresql+psycopg2://scott:tiger@localhost/test",
            use_native_hstore=False)

即使不使用 psycopg2.extensions.register_hstore() 扩展，仍然**支持** HSTORE 类型。它仅仅意味着在参数端和结果端，Python 字典和 HSTORE 字符串格式之间的强制转换将在 SQLAlchemy 自己的编组逻辑中进行，而不是在 psycopg2 中进行，这可能更高效。

psycopg

通过 psycopg（又名 psycopg 3）驱动程序支持 PostgreSQL 数据库。

DBAPI

psycopg（又名 psycopg 3）的文档和下载信息（如果适用）可从以下网站获得：https://pypi.ac.cn/project/psycopg/

连接

连接字符串

postgresql+psycopg://user:password@host:port/dbname[?key=value&key=value...]

psycopg 是版本 3 的 psycopg 数据库驱动的软件包和模块名称，以前称为 psycopg2。该驱动程序与它的 psycopg2 前身有很大不同，SQLAlchemy 通过完全独立的方言支持它；只要该软件包继续在现代 Python 版本中发挥作用，就预计会继续支持 psycopg2，并且它也是 postgresql:// 方言系列的默认方言。

SQLAlchemy psycopg 方言在同一个方言名称下提供了同步和异步实现。根据创建引擎的方式选择合适的版本

• 使用 postgresql+psycopg://... 调用 create_engine() 将自动选择同步版本，例如

  from sqlalchemy import create_engine
  sync_engine = create_engine("postgresql+psycopg://scott:tiger@localhost/test")

• 使用 create_async_engine() 和 postgresql+psycopg://... 会自动选择异步版本，例如：

  from sqlalchemy.ext.asyncio import create_async_engine
  asyncio_engine = create_async_engine("postgresql+psycopg://scott:tiger@localhost/test")

也可以使用 psycopg_async 后缀显式指定方言的异步版本，如：

from sqlalchemy.ext.asyncio import create_async_engine
asyncio_engine = create_async_engine("postgresql+psycopg_async://scott:tiger@localhost/test")

另请参阅

psycopg2 - SQLAlchemy 的 psycopg 方言在行为上与 psycopg2 方言非常相似。更详细的文档可以在那里找到。

使用不同的游标类

psycopg 和旧版本的 psycopg2 之间的一个区别是绑定参数的处理方式：psycopg2 会在客户端绑定参数，而 psycopg 默认会在服务器端绑定参数。

可以通过在创建引擎时将 cursor_factory 指定为 ClientCursor 来配置 psycopg 进行客户端绑定。

from psycopg import ClientCursor

client_side_engine = create_engine(
    "postgresql+psycopg://...",
    connect_args={"cursor_factory": ClientCursor},
)

类似地，在使用异步引擎时，可以指定 AsyncClientCursor。

from psycopg import AsyncClientCursor

client_side_engine = create_async_engine(
    "postgresql+psycopg://...",
    connect_args={"cursor_factory": AsyncClientCursor},
)

另请参阅

客户端绑定游标

pg8000

通过 pg8000 驱动程序支持 PostgreSQL 数据库。

DBAPI

pg8000 的文档和下载信息（如果适用）可在以下网站找到：https://pypi.ac.cn/project/pg8000/

连接

连接字符串

postgresql+pg8000://user:password@host:port/dbname[?key=value&key=value...]

版本 1.4 中变更: pg8000 方言已更新至 1.16.6 及更高版本，并已重新纳入 SQLAlchemy 的持续集成，具有完整的功能支持。

Unicode

pg8000 会使用 PostgreSQL 的 client_encoding 参数在自身和服务器之间对字符串值进行编码/解码；默认情况下，此参数的值来自 postgresql.conf 文件，该文件通常默认为 SQL_ASCII。通常情况下，可以将它更改为 utf-8，作为更实用的默认值。

#client_encoding = sql_ascii # actually, defaults to database
                             # encoding
client_encoding = utf8

可以通过执行以下 SQL 代码来覆盖会话的 client_encoding：

SET CLIENT_ENCODING TO ‘utf8’;

SQLAlchemy 会根据传递给 create_engine() 的 client_encoding 参数值，在所有新连接上执行此 SQL 代码。

engine = create_engine(
    "postgresql+pg8000://user:pass@host/dbname", client_encoding='utf8')

SSL 连接

pg8000 接受 Python SSLContext 对象，可以使用 create_engine.connect_args 字典指定该对象。

import ssl
ssl_context = ssl.create_default_context()
engine = sa.create_engine(
    "postgresql+pg8000://scott:[email protected]/test",
    connect_args={"ssl_context": ssl_context},
)

如果服务器使用自动生成的证书，该证书是自签名的，或者与主机名不匹配（从客户端的角度看），则可能还需要禁用主机名检查。

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:[email protected]/test",
    connect_args={"ssl_context": ssl_context},
)

pg8000 事务隔离级别

pg8000 方言提供与 psycopg2 方言相同的隔离级别设置。

• READ COMMITTED

• READ UNCOMMITTED

• REPEATABLE READ

• SERIALIZABLE

• AUTOCOMMIT

另请参阅

事务隔离级别

Psycopg2 事务隔离级别

asyncpg

通过 asyncpg 驱动程序支持 PostgreSQL 数据库。

DBAPI

asyncpg 的文档和下载信息（如果适用）可在以下网站找到：https://magicstack.github.io/asyncpg/

连接

连接字符串

postgresql+asyncpg://user:password@host:port/dbname[?key=value&key=value...]

asyncpg 方言是 SQLAlchemy 的第一个 Python asyncio 方言。

通过使用特殊的 asyncio 中介层，asyncpg 方言可以用作 SQLAlchemy asyncio 扩展包的后端。

此方言通常只应与 create_async_engine() 引擎创建函数一起使用。

from sqlalchemy.ext.asyncio import create_async_engine
engine = create_async_engine("postgresql+asyncpg://user:pass@hostname/dbname")

新版功能，版本 1.4。

注意

默认情况下，asyncpg 不会解码 json 和 jsonb 类型，而是将它们作为字符串返回。SQLAlchemy 使用 Python 内置的 json.loads 函数为 json 和 jsonb 类型设置默认类型解码器。可以通过在使用 create_engine() 或 create_async_engine() 创建引擎时设置 json_deserializer 属性来更改使用的 json 实现。

多主机连接

asyncpg 方言与 psycopg2 和 psycopg 方言一样，支持在同一连接中使用多个备用主机。语法相同，使用 host=<host>:<port> 组合作为额外的查询字符串参数；但是，没有默认端口，因此所有主机都必须包含完整的端口号，否则会引发异常。

engine = create_async_engine(
    "postgresql+asyncpg://user:password@/dbname?host=HostA:5432&host=HostB:5432&host=HostC:5432"
)

有关此语法的完整背景信息，请参阅 指定多个备用主机。

版本 2.0.18 中新增。

另请参阅

指定多个备用主机

预备语句缓存

asyncpg SQLAlchemy 方言对所有语句都使用 asyncpg.connection.prepare()。预备语句对象在构造后被缓存，这似乎可以提高语句调用性能 10% 或更多。缓存是在每个 DBAPI 连接的基础上进行的，这意味着预备语句的主要存储位置是在连接池中池化的 DBAPI 连接内。此缓存的大小默认为每个 DBAPI 连接 100 个语句，可以使用 prepared_statement_cache_size DBAPI 参数调整（请注意，虽然此参数是由 SQLAlchemy 实现的，但它属于 asyncpg 方言的 DBAPI 模拟部分，因此作为 DBAPI 参数处理，而不是方言参数）。

engine = create_async_engine("postgresql+asyncpg://user:pass@hostname/dbname?prepared_statement_cache_size=500")

要禁用预备语句缓存，请使用零值。

engine = create_async_engine("postgresql+asyncpg://user:pass@hostname/dbname?prepared_statement_cache_size=0")

版本 1.4.0b2 中新增: 为 asyncpg 添加了 prepared_statement_cache_size。

警告

asyncpg 数据库驱动程序必须为 PostgreSQL 类型 OID 使用缓存，这些缓存会在使用 DDL 操作更改自定义 PostgreSQL 数据类型（例如 ENUM 对象）时过时。此外，SQLAlchemy 驱动程序（如上所述）可选地缓存的预备语句本身，如果 DDL 已发出到 PostgreSQL 数据库，并且修改了预备语句中涉及的表格或其他对象，也可能变得“过时”。

SQLAlchemy asyncpg 方言会在本地连接上发出代表 DDL 的语句时，在其本地进程中使这些缓存失效，但这只能在单个 Python 进程/数据库引擎内控制。如果从其他数据库引擎和/或进程进行 DDL 更改，运行中的应用程序可能会遇到 asyncpg 异常 InvalidCachedStatementError 和/或 InternalServerError("cache lookup failed for type <oid>")，如果它引用了在先前结构上进行操作的池化数据库连接。SQLAlchemy asyncpg 方言会在驱动程序引发这些异常时，通过清除其内部缓存以及 asyncpg 驱动程序的缓存来恢复，但无法阻止这些异常在第一个地方被引发，如果缓存的预备语句或 asyncpg 类型缓存已过时，也无法重试语句，因为当这些错误发生时，PostgreSQL 事务将失效。

使用 PGBouncer 的预备语句名称

默认情况下，asyncpg 按数字顺序枚举预备语句，如果某个名称已被另一个预备语句占用，则会导致错误。如果您的应用程序使用数据库代理（例如 PgBouncer）来处理连接，则会出现此问题。一个可能的解决办法是使用动态预备语句名称，asyncpg 现在通过语句名称的可选 name 值支持此功能。这允许您生成自己的唯一名称，这些名称不会与现有名称冲突。为了实现这一点，您可以提供一个函数，该函数会在每次准备预备语句时被调用。

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

警告

在使用 PGBouncer 时，为了防止应用程序中累积不必要的预备语句，务必使用 NullPool 池类，并配置 PgBouncer 在返回连接时使用 DISCARD。DISCARD 命令用于释放 db 连接持有的资源，包括预备语句。如果没有适当的设置，预备语句会迅速累积，导致性能问题。

禁用 PostgreSQL JIT 以改进 ENUM 数据类型处理

Asyncpg 在使用 PostgreSQL ENUM 数据类型时存在一个 问题，即在创建新的数据库连接时，可能会发出一个代价高昂的查询以检索有关自定义类型的元数据，这已被证明会对性能造成负面影响。为了缓解此问题，可以使用此设置从客户端禁用 PostgreSQL 的“jit”设置，该设置传递给 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

通过 psycopg2cffi 驱动程序支持 PostgreSQL 数据库。

DBAPI

psycopg2cffi 的文档和下载信息（如果适用）可在以下位置获取：https://pypi.ac.cn/project/psycopg2cffi/

连接

连接字符串

postgresql+psycopg2cffi://user:password@host:port/dbname[?key=value&key=value...]

psycopg2cffi 是 psycopg2 的一个改编版本，使用 CFFI 作为 C 层。这使得它适合在例如 PyPy 中使用。文档与 psycopg2 相同。

另请参阅

sqlalchemy.dialects.postgresql.psycopg2