PostgreSQL

Support for the PostgreSQL database.

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

序列/序列/标识

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

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

Table('sometable', metadata,
        Column('id', Integer, Sequence('some_id_seq'), primary_key=True)
    )

当sqlAlchemy发出单个insert语句时，为了满足“last insert identifier”可用的约定，在insert语句中添加了一个返回子句，指定在语句完成后应返回主键列。返回功能仅在使用PostgreSQL 8.2或更高版本时发生。作为回退方法，序列，无论是通过 SERIAL ，是预先独立执行的，返回的值将在随后的插入中使用。注意，当 insert() 使用“ExecuteMany”语义执行构造，不应用“Last Inserted Identifier”功能；不会发出返回子句，在这种情况下也不会预先执行序列。

若要强制使用默认返回，请指定标志 implicit_returning=False 到 create_engine() .

PostgreSQL 10及以上标识列

postgresql10及更高版本有一个新的标识特性，它取代了SERIAL的使用。这个 Identity 在a中构造 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 data (
    id INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 42 CYCLE),
    data VARCHAR,
    PRIMARY KEY (id)
)

在 1.4 版更改: 补充 Identity 在a中构造 Column 指定自动递增列的选项。

注解

早期版本的SQLAlchemy没有内置的标识呈现支持，可以使用以下编译钩子将出现的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 版后已移除: 方言级别的服务器u side_cursors标志已弃用，将在将来的版本中删除。请使用 Connection.stream_results 无缓冲游标支持的执行选项。

参见

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

事务隔离级别

大多数SQLAlchemy方言支持使用 create_engine.execution_options 参数 create_engine() 水平，在 Connection 通过 Connection.execution_options.isolation_level 参数。

对于PostgreSQL方言，此功能可以通过使用DBAPI特定的特性来工作，例如psycopg2的隔离级别标志，它将嵌入与 "BEGIN" 语句，或者对于没有直接支持的dbapi，通过发出 SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL <level> 领先于 "BEGIN" 由DBAPI发出的语句。对于特殊的AUTOCOMMIT隔离级别，使用特定于DBAPI的技术，这通常是 .autocommit DBAPI连接对象上的标志。

要设置隔离级别，请使用 create_engine() ：：

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

使用每个连接执行选项进行设置：

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

的有效值 isolation_level 大多数PostgreSQL方言包括：

• READ COMMITTED

• READ UNCOMMITTED

• REPEATABLE READ

• SERIALIZABLE

• AUTOCOMMIT

参见

设置只读/可延迟

设置事务隔离级别，包括DBAPI Autocommit

psycopg2事务隔离级别

PG8000事务隔离级别

设置只读/可延迟

大多数PostgreSQL方言支持设置事务的“只读”和“可延迟”特性，这是对隔离级别设置的补充。通过传递 postgresql_readonly 和 postgresql_deferrable 用标记 Connection.execution_options() . 下面的示例演示如何传递 "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）只支持具有可序列化隔离的“readonly”。

1.4 新版功能: 增加了对 postgresql_readonly 和 postgresql_deferrable 执行选项。

在连接上设置备用搜索路径

PostgreSQL search_path 变量是指在SQL语句中引用特定表或其他对象时将隐式引用的模式名列表。如下一节所述 远程模式表自省和PostgreSQL搜索路径 ，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连接具有回滚时不会恢复。

参见

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

远程模式表自省和PostgreSQL搜索路径

TL；DR； 保持 search_path 变量设置为默认值 public 命名模式 其他 比 public 明确在 Table 定义。

PostgreSQL方言可以反映任何模式中的表。这个 Table.schema 参数，或者也可以选择 MetaData.reflect.schema 参数确定将在哪个架构中搜索表。“倒影” Table 对象在所有情况下都将保留此属性 .schema 属性。但是，对于这些表， Table 对象通过外键约束引用，则必须决定 .schema 如果该远程架构名称也是当前 PostgreSQL search path 。

默认情况下，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://scott:tiger@localhost/test")
>>> with engine.connect() as conn:
...     conn.execute(text("SET search_path TO test_schema, public"))
...     meta = MetaData()
...     referring = Table('referring', meta,
...                       autoload_with=conn)
...
<sqlalchemy.engine.result.CursorResult object at 0x101612ed0>

上述流程将交付给 MetaData.tables 收集 referred 表命名 没有 模式：

>>> meta.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"))
...     meta = MetaData()
...     referring = Table('referring', meta,
...                       autoload_with=conn,
...                       postgresql_ignore_search_path=True)
...
<sqlalchemy.engine.result.CursorResult object at 0x1016126d0>

我们现在就要 test_schema.referred 存储为架构限定：：

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

注意 在所有情况下 “默认”模式始终反映为 None . PostgreSQL上的“默认”模式是PostgreSQL返回的模式。 current_schema() 功能。在典型的PostgreSQL安装中，这是 public . 所以一个表引用了 public （即默认）模式将始终具有 .schema 属性设置为 None .

0.9.2 新版功能: 增加了 postgresql_ignore_search_path 方言级别选项被接受 Table 和 MetaData.reflect() .

参见

The Schema Search Path -在PostgreSQL网站上。

INSERT/UPDATE...RETURNING

方言支持第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())

冲突时插入（向上插入）

从9.5版开始，PostgreSQL允许通过 ON CONFLICT 条款 INSERT 语句。只有当候选行不违反任何唯一约束时，才会插入该行。在违反唯一约束的情况下，可能会发生第二个操作，该操作可以是“do update”（执行更新），表示应更新目标行中的数据，也可以是“do nothing”（不执行任何操作），表示静默跳过此行。

使用现有的唯一约束和索引确定冲突。可以使用DDL中所述的名称来标识这些约束，也可以通过声明组成索引的列和条件来推断这些约束。

SQLAlchemy提供 ON CONFLICT 通过特定于PostgreSQL的支持 insert() 函数，提供生成方法 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

1.1 新版功能.

参见

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='a@b.com', data='inserted data')
  >>> stmt = stmt.on_conflict_do_update(
  ...     index_elements=[my_table.c.user_email],
  ...     index_where=my_table.c.user_email.like('%@gmail.com'),
  ...     set_=dict(data=stmt.excluded.data)
  ... )
  >>> print(stmt)
  INSERT INTO my_table (data, user_email)
  VALUES (%(data)s, %(user_email)s) ON CONFLICT (user_email)
  WHERE user_email LIKE %(user_email_1)s DO UPDATE SET data = excluded.data
  

• 这个 Insert.on_conflict_do_update.constraint 参数用于直接指定索引，而不是推断索引。它可以是唯一约束、主键约束或索引的名称：

  >>> 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 使用 Table.primary_key 属性：

  >>> 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_ 参数。此参数接受由直接值组成的字典进行更新：

>>> 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() 方法做 not 考虑Python端的默认更新值或生成函数，例如使用 Column.onupdate . 除非在 Insert.on_conflict_do_update.set_ 字典。

使用排除的插入值更新

为了引用建议的插入行，特殊别名 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() 方法还接受使用 Insert.on_conflict_do_update.where 参数，该参数将限制接收更新的行：

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

不执行任何操作跳过行

ON CONFLICT 如果与unique或exclusion约束发生任何冲突，则可用于完全跳过插入行；下面使用 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 在未指定任何列或约束的情况下使用，则对于发生的任何唯一或排除约束冲突，它将跳过插入操作：

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

全文搜索

SQLAlChemy使PostgreSQL变得可用 @@ 运算符通过 ColumnElement.match() 方法对任何文本列表达式执行。

在PostgreSQL方言中，如下所示的表达式：

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

将发送到数据库：：

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

各种其他PostgreSQL文本搜索功能，例如 to_tsquery() ， to_tsvector() ，以及 plainto_tsquery() 通过显式使用标准SQLAlChemy func 构造。

例如：：

select(func.to_tsvector('fat cats ate rats').match('cat & rat'))

发射相当于：

SELECT to_tsvector('fat cats ate rats') @@ to_tsquery('cat & rat')

这个 TSVECTOR 类型不能提供显式强制转换：：

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

小技巧

请务必记住，PostgreSQL中的文本搜索功能强大但很复杂，建议SQLAlChemy用户参考有关的PostgreSQL文档 Full Text Search 。

它们之间有重要的区别 to_tsquery 和 plainto_tsquery 其中最重要的是 to_tsquery 需要写入PostgreSQL自己规范的特殊格式的“querytext”，而 plainto_tsquery 需要将未格式化的文本转换为 to_tsquery 兼容的查询文本。这意味着输入到 .match() 在PostgreSQL下，可能与的输入不兼容 .match() 在另一个数据库后端。建议支持多个后端的SQLAlChemy用户小心实现他们的用法 .match() 来解决这些限制。

PostgreSQL中的全文搜索受以下组合的影响：PostgreSQL设置 default_text_search_config , the regconfig 用于构建GIN/GIST索引，以及 regconfig 在查询期间可选传入。

当对已经预先计算了GIN或GIST索引（在全文搜索中很常见）的列执行全文搜索时，可能需要显式传递特定的PostgreSQL regconfig 值以确保查询规划器使用索引，并且不会根据需要重新计算列。

为了提供这种明确的查询计划，或者使用不同的搜索策略，可以使用 match 方法接受 postgresql_regconfig 关键字参数：

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

发射相当于：

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

你也可以通过 'regconfig' 价值 to_tsvector() 作为初始参数的命令：

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

生成等价于以下内容的语句：

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

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

仅从…

方言支持PostgreSQL的唯一关键字，只针对继承层次结构中的特定表。这可用于生产 SELECT ... FROM ONLY, UPDATE ONLY ..., and 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://www.postgresql.org/docs/8.3/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树、哈希、GIST和GIN，并允许用户创建自己的索引(请参阅https://www.postgresql.org/docs/8.3/static/indexes-types.html).可以在 Index 使用 postgresql_using 关键字参数：：

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

传递给关键字参数的值将简单地传递给底层的create index命令，因此 must 为您的PostgreSQL版本提供有效的索引类型。

索引存储参数

PostgreSQL允许对索引设置存储参数。可用的存储参数取决于索引使用的索引方法。可以在上指定存储参数 Index 使用 postgresql_with 关键字参数：

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

1.0.6 新版功能.

PostgreSQL允许定义创建索引的表空间。可以在上指定表空间 Index 使用 postgresql_tablespace 关键字参数：

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

1.1 新版功能.

请注意，相同的选项在上可用 Table 也。

同时具有的索引

传递标志同时支持PostgreSQL索引选项 postgresql_concurrently 到 Index 结构：

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

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

如果检测到PostgreSQL 8.2或更高版本，或者检测到无连接方言，上述索引结构将为create index呈现DDL，如下所示：

CREATE INDEX CONCURRENTLY test_idx1 ON testtbl (data)

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

DROP INDEX CONCURRENTLY test_idx1

1.1 新版功能: 支持同时删除索引。只有在连接上检测到足够高的PostgreSQL版本（或对于无连接方言）时，才会发出concurrent关键字。

同时使用时，PostgreSQL数据库要求在事务块之外调用该语句。python dbapi强制即使对于单个语句，也存在事务，因此要使用此构造，必须使用dbapi的“autocommit”模式：

metadata = MetaData()
table = Table(
    "foo", metadata,
    Column("id", String))
index = Index(
    "foo_idx", table.c.id, postgresql_concurrently=True)

with engine.connect() as conn:
    with conn.execution_options(isolation_level='AUTOCOMMIT'):
        table.create(conn)

参见

事务隔离级别

PostgreSQL索引反射

每当使用UNIQUE约束构造时，PostgreSQL数据库都会隐式创建一个UNIQUE索引。使用检查表格时 Inspector ，即 Inspector.get_indexes() 以及 Inspector.get_unique_constraints() 将清楚地报告这两个结构；在索引的情况下，键 duplicates_constraint 如果检测到它在镜像约束，则它将出现在索引条目中。使用执行反射时 Table(..., autoload_with=engine) ，唯一索引为 not 返回的时间 Table.indexes 当检测到它正在镜像 UniqueConstraint 在 Table.constraints 收藏。

在 1.0.0 版更改: - Table reflection now includes UniqueConstraint objects present in the Table.constraints collection; the PostgreSQL backend will no longer include a "mirrored" Index construct in Table.indexes if it is detected as corresponding to a unique constraint.

特殊反射选项

这个 Inspector 用于PostgreSQL后端的是 PGInspector ，提供了其他方法：

from sqlalchemy import create_engine, inspect

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

print(insp.get_enums())

class sqlalchemy.dialects.postgresql.base.PGInspector(bind)

PostgreSQL方言与 Table 构建：

• TABLESPACE ：：

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

  上述选项也可在 Index 构造。

• ON COMMIT ：：

  Table("some_table", metadata, ..., postgresql_on_commit='PRESERVE ROWS')

• WITH OIDS ：：

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

• WITHOUT OIDS ：：

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

• INHERITS ：：

  Table("some_table", metadata, ..., postgresql_inherits="some_supertable")
  
  Table("some_table", metadata, ..., postgresql_inherits=("t1", "t2", ...))
  
  .. versionadded:: 1.0.0

• PARTITION BY ：：

  Table("some_table", metadata, ...,
        postgresql_partition_by='LIST (part_column)')
  
  .. versionadded:: 1.2.6

参见

PostgreSQL CREATE TABLE options

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

PostgreSQL大量使用现代SQL形式，如表值函数、表和行作为值。这些构造通常用作PostgreSQL对复杂数据类型(如JSON、数组和其他数据类型)支持的一部分。SQLAlChemy的SQL表达式语言具有对大多数表值和行值表单的本机支持。

表值函数

许多PostgreSQL内置函数旨在用于SELECT语句的FROM子句，并且能够返回表行或表行集合。PostgreSQL的大部分JSON函数，例如 json_array_elements() ， json_object_keys() ， json_each_text() ， json_each() ， json_to_record() ， json_populate_recordset() 使用这些表格。SQLAlChemy中的这些SQL函数调用窗体类可以使用 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")
  ... )
  >>> 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

1.4.0b2 新版功能.

参见

表值函数 - in the SQLAlchemy 1.4/2.0教程

列值函数

与表值函数类似，列值函数出现在FROM子句中，但会将自身作为单个标量值传递给COLUMNS子句。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() 构造可用于：：

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

参见

列值函数-作为标量列的表值函数 - in the SQLAlchemy 1.4/2.0教程

行类型

对呈现 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 Row Constructors

PostgreSQL Row Constructor Comparison

传递给函数的表类型

PostgreSQL支持将表作为参数传递给函数，它将该函数称为“记录”类型。SQL炼金术 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_agg() -数组agg sql函数

• aggregate_order_by -pg的order by aggregate函数语法助手。

JSON类型

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

• JSON

• JSONB

HStury型

支持PostgreSQL hstore类型和hstore文本：

• HSTORE -hstore数据类型

• hstore -hstore文本

枚举类型

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

• ENUM -对枚举的DDL和键入支持。

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

• ENUM.create() ， ENUM.drop() -用于枚举的单个创建和删除命令。

将枚举与数组一起使用

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

在 1.3.17 版更改: 枚举和数组的组合现在由SQLAlchemy的实现直接处理，而不需要任何变通方法。

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

)

此类型不作为内置类型包括在内，因为它与突然决定在新版本中直接支持枚举数组的DBAPI不兼容。

在数组中使用json/jsonb

与使用ENUM类似，在SQLAlChemy 1.3.17之前，对于JSON/JSONB数组，我们需要呈现适当的强制转换。当前的mental copg2驱动程序无需任何特殊步骤即可正确地适应结果集。

在 1.3.17 版更改: JSON/JSONB和ARRAY的组合现在由SQLAlchemy的实现直接处理，而不需要任何变通方法。

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数据类型和自定义SQL结构

与所有的sqlAlchemy方言一样，已知对postgresql有效的所有大写类型都可以从顶级方言导入，无论它们是否源自 sqlalchemy.types 或者来自当地方言：

from sqlalchemy.dialects.postgresql import \
    ARRAY, BIGINT, BIT, BOOLEAN, BYTEA, CHAR, CIDR, DATE, \
    DOUBLE_PRECISION, ENUM, FLOAT, HSTORE, INET, INTEGER, \
    INTERVAL, JSON, JSONB, MACADDR, MONEY, NUMERIC, OID, REAL, SMALLINT, TEXT, \
    TIME, TIMESTAMP, UUID, VARCHAR, INT4RANGE, INT8RANGE, NUMRANGE, \
    DATERANGE, TSRANGE, TSTZRANGE, TSVECTOR

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

Object Name	Description
aggregate_order_by	按表达式表示PostgreSQL聚合顺序。
All(other, arrexpr[, operator])	数组级别的同义词 Comparator.all() 方法。有关详细信息，请参阅该方法。
Any(other, arrexpr[, operator])	数组级别的同义词 Comparator.any() 方法。有关详细信息，请参阅该方法。
array	PostgreSQL数组文本。
ARRAY	PostgreSQL数组类型。
array_agg(*arg, **kw)	PostgreSQL特定形式的 array_agg ，确保返回类型为 ARRAY 而不是平原 ARRAY ，除非 type_ 通过。
BIT
BYTEA
CIDR
DOUBLE_PRECISION
ENUM	PostgreSQL枚举类型。
HSTORE	表示PostgreSQL hstore类型。
hstore	使用PostgreSQL在SQL表达式中构造一个hstore值 hstore() 功能。
INET
INTERVAL	PostgreSQL间隔类型。
JSON	表示PostgreSQL JSON类型。
JSONB	表示PostgreSQL JSONB类型。
MACADDR
MONEY	提供PostgreSQL货币类型。
OID	提供PostgreSQL OID类型。
REAL	SQL实数类型。
REGCLASS	提供PostgreSQL RegClass类型。
TSVECTOR	这个 TSVECTOR 类型实现PostgreSQL文本搜索类型tsvector。
UUID	PostgreSQL UUID类型。

class sqlalchemy.dialects.postgresql.aggregate_order_by(target, *order_by)

PostgreSQL 9.2以后版本中的新范围列类型由以下类型提供：

Object Name	Description
DATERANGE	表示PostgreSQL日期范围类型。
INT4RANGE	表示PostgreSQL Int4Range类型。
INT8RANGE	表示PostgreSQL Int8Range类型。
NUMRANGE	表示PostgreSQL NumRange类型。
RangeOperators	此混合提供了范围运算符的功能，这些运算符在的表9-44中列出 `PostgreSQL documentation`_ _表示范围函数和运算符。中提供的所有范围类型都使用它。 postgres 方言，并且可能用于您自己创建的任何范围类型。
TSRANGE	表示PostgreSQL tsrange类型。
TSTZRANGE	表示PostgreSQL TSZrange类型。

class sqlalchemy.dialects.postgresql.INT4RANGE

SQLAlchemy支持PostgreSQL通过 ExcludeConstraint 班级：

Object Name	Description
ExcludeConstraint	表级排除约束。

class sqlalchemy.dialects.postgresql.ExcludeConstraint(*elements, **kw)

Object Name	Description
insert(table[, values, inline, bind, ...], **dialect_kw)	构建一个 Insert 对象。
Insert	PostgreSQL特定的insert实现。

function sqlalchemy.dialects.postgresql.insert(table, values=None, inline=False, bind=None, prefixes=None, returning=None, return_defaults=False, **dialect_kw)

Support for the PostgreSQL database via the psycopg2 driver.

DBAPI

Documentation and download information (if applicable) for psycopg2 is available at: https://pypi.org/project/psycopg2/

Connecting

Connect String:

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

psycopg2连接参数

可以将特定于SQLAlChemy mental copg2方言的关键字参数传递给 create_engine() ，并包括以下内容：

• isolation_level ：此选项适用于所有PostgreSQL方言，包括 AUTOCOMMIT 使用mental copg2方言时的隔离级别。此选项设置 默认设置 连接的隔离级别，该级别是在连接池化之前在连接到数据库时立即设置的。这个选项通常会被更现代的 Connection.execution_options.isolation_level 执行选项，详情请参见 设置事务隔离级别，包括DBAPI Autocommit 。

  参见

  psycopg2事务隔离级别

  设置事务隔离级别，包括DBAPI Autocommit

• client_encoding ：使用psycopg2以libpq不可知方式设置客户端编码 set_client_encoding() 方法。

  参见

  带psycopg2的Unicode

• use_native_unicode ：仅在Python2下，可以将其设置为false以禁用mental copg2的原生Unicode支持。

  参见

  禁用本机Unicode

• executemany_mode ， executemany_batch_page_size ， executemany_values_page_size ：允许使用mental copg2扩展来优化“Executemany”样式的查询。有关详细信息，请参阅下面的参考部分。

  参见

  Psycopg2快速执行助手

小技巧

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

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

这些不应与 DBAPI 连接参数，这些参数作为 create_engine.connect_args 字典和/或在URL查询字符串中传递，如部分所述 自定义DBAPI connect（）参数/on connect例程 。

SSL连接

mental copg2模块有一个名为的连接参数 sslmode 用于控制其有关安全(SSL)连接的行为。默认值为 sslmode=prefer ；它将尝试SSL连接，如果失败，它将回退到未加密的连接。 sslmode=require 可用于确保仅建立安全连接。有关可用的更多选项，请参阅mental copg2/libpq文档。

请注意， sslmode 是特定于mental copg2的，因此它包含在连接URI：：

engine = sa.create_engine(
    "postgresql+psycopg2://scott:tiger@192.168.0.199:5432/test?sslmode=require"
)

Unix域连接

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

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

默认情况下，使用的套接字文件是连接到 /tmp 或者在构建PostgreSQL时指定的任何套接字目录。可以通过将路径名传递给psycopg2来重写此值，方法是使用 host 作为附加关键字参数：：

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

参见

PQconnectdbParams

指定多个回退主机

psycopg2支持连接字符串中的多个连接点。当 host 参数在URL的查询部分被多次使用，SQLAlchemy将创建一个字符串，其中包含主机和端口信息，以进行连接：

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

然后尝试连接到每个主机，直到连接成功或所有连接都不成功，在这种情况下会引发错误。

1.3.20 新版功能: 支持PostgreSQL连接字符串中的多个主机。

参见

PQConnString

空的DSN连接/环境变量连接

psycopg2 dbapi可以通过向libpq客户机库传递一个空的dsn连接到postgresql，默认情况下，libpq客户机库指示连接到为“trust”连接打开的localhost postgresql数据库。可以使用一组特定的环境变量进一步定制这种行为，这些环境变量的前缀为 PG_... ，由消耗 libpq 替换连接字符串的任何或所有元素。

对于此表单，可以不使用初始方案以外的任何元素传递URL:：

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

在上面的表单中，一个空白的“dsn”字符串被传递给 psycopg2.connect() 函数，它依次表示传递给libpq的空DSN。

1.3.2 新版功能: 支持与psycopg2的无参数连接。

参见

Environment Variables -关于如何使用的PostgreSQL文档 PG_... 连接的环境变量。

每个语句/连接执行选项

与一起使用时，将遵循以下特定于DBAPI的选项 Connection.execution_options() ， Executable.execution_options() ， Query.execution_options() ，除了那些不特定于DBAPIS的：

• isolation_level -设置事务隔离级别 Connection （只能在连接上设置，不能在语句或查询上设置）。见 psycopg2事务隔离级别 .

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

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

  在 1.4 版更改: 这个 max_row_buffer 大小现在可以大于1000，缓冲区将增长到该大小。

Psycopg2快速执行助手

现代版本的mental copg2包含一个称为 Fast Execution Helpers 在基准测试中已经显示，主要使用INSERT语句，可以将mental copg2的ecutemany()性能提高数个数量级。SQLAlChemy在内部将这些扩展用于 executemany() 样式调用，它对应于要传递到的参数列表 Connection.execute() 如中所述 multiple parameter sets 。ORM也尽可能在内部使用此模式。

psycopg2端的两个可用扩展是 execute_values() 和 execute_batch() 功能。psycopg2方言默认使用 execute_values() 所有符合条件的INSERT语句的扩展。

在 1.4 版更改: psycopg2方言现在默认为一种新模式 "values_only" 对于 executemany_mode ，它允许INSERT语句的性能得到一个数量级的改进，但不包括UPDATE和DELETE语句的“批处理”模式，这将删除 cursor.rowcount 正常工作。

这些扩展的使用由 executemany_mode 可传递给的标志 create_engine() ：：

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

可能的选择 executemany_mode 包括：

• values_only -这是默认值。psycopg2 execute_values（）扩展用于限定INSERT语句，它重写INSERT以包含多个values子句，以便可以用一个语句插入多个参数集。

  1.4 新版功能: 补充 "values_only" 设置为 executemany_mode 现在也是默认值。

• None -不使用psycopg2扩展，通常 cursor.executemany() 方法在调用具有多个参数集的语句时使用。

• 'batch' -用途 psycopg2.extras.execute_batch 对于所有符合条件的INSERT、UPDATE和DELETE语句，以便SQL查询的多个副本，每个副本对应于传递给 executemany() ，联接到一个由分号分隔的SQL字符串中。使用此模式时 CursorResult.rowcount 属性将不包含executemany样式执行的值。

• 'values_plus_batch' - execute_values 用于限定INSERT语句， execute_batch 用于更新和删除。使用此模式时 CursorResult.rowcount 属性将不包含针对UPDATE和DELETE语句执行executemany样式的值。

“限定语句”是指正在执行的语句必须是一个核心 insert() ， update() 或 delete() 构造，而不是纯文本SQL字符串或使用 text() . 使用ORM时，ORM刷新过程使用的所有insert/update/delete语句都是限定的。

“值”和“批处理”策略的“页面大小”可以通过使用 executemany_batch_page_size 和 executemany_values_page_size 发动机参数。它们控制在每次执行中应表示多少个参数集。“values”页面大小默认为1000，这与psycopg2的默认值不同。“批处理”页面大小默认为100。将新值传递给 create_engine() ：：

engine = create_engine(
    "postgresql+psycopg2://scott:tiger@host/dbname",
    executemany_mode='values',
    executemany_values_page_size=10000, executemany_batch_page_size=500)

在 1.4 版更改: 默认值为 executemany_values_page_size 现在是1000，从100增加到了1000。

参见

执行多个语句 -关于使用的一般信息 Connection 对象以这样的方式执行语句，以便使用DBAPI .executemany() 方法。

带psycopg2的Unicode

mental copg2 DBAPI驱动程序透明地支持Unicode数据。仅在Python2下，SQLAlChemy mental copg2方言将启用 psycopg2.extensions.UNICODE 默认情况下扩展，以确保正确处理Unicode；在Python3下，这是mental copg2的默认行为。

可以通过以下方式控制mental copg2方言的客户端字符编码：

• 对于PostgreSQL 9.1和更高版本， client_encoding 参数可以在数据库URL中传递；此参数由基础 libpq PostgreSQL客户端库：：

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

  或者，上面的 client_encoding 值可以使用 create_engine.connect_args 对于使用 libpq ：：

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

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

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

  小技巧

  以上内容 client_encoding 诚然，参数在外观上与 create_engine.connect_args 字典；上面的不同之处在于，参数由mental copg2使用，并使用以下命令传递给数据库连接 SET client_encoding TO 'utf8' ；在前面提到的样式中，该参数改为通过mental copg2传递，并由 libpq 类库。

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

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

禁用本机Unicode

仅在Python2下，还可以指示SQLAlChemy跳过mental copg2的使用 UNICODE 扩展，而不是使用它自己的Unicode编码/解码服务，这些服务通常只为那些不完全直接支持Unicode的DBAPI保留。通过 use_native_unicode=False 至 create_engine() 将禁用使用 psycopg2.extensions. UNICODE 。SQLAlChemy会改为在传入时将数据自身编码为Python字节字符串，并在返回时强制从字节开始，使用 create_engine() encoding 参数，该参数默认为 utf-8 。SQLAlChemy自己的Unicode编码/解码功能正在逐渐过时，因为大多数DBAPI现在完全支持Unicode。

交易

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

psycopg2事务隔离级别

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

psycopg2方言支持隔离级别的这些常量：

• READ COMMITTED

• READ UNCOMMITTED

• REPEATABLE READ

• SERIALIZABLE

• AUTOCOMMIT

参见

事务隔离级别

PG8000事务隔离级别

通知日志

psycopg2方言将通过 sqlalchemy.dialects.postgresql 记录器。当此记录器设置为 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网站上

HStury型

这个 psycopg2 DBAPI包括对hstore类型的本机处理编组的扩展。当使用psycopg2.4或更高版本时，sqlAlchemy psycopg2方言将默认启用此扩展，并且检测到目标数据库设置了hstore类型以供使用。换句话说，当方言进行第一次连接时，将执行如下顺序：

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

2. 如果 use_native_hstore 标志默认为 True 我们发现 HSTORE 有可用的OID， psycopg2.extensions.register_hstore() 对所有连接调用扩展。

这个 register_hstore() 扩展具有以下效果 接受所有python字典作为参数，而不管SQL中目标列的类型如何。 . 该扩展将字典转换为文本hstore表达式。如果不需要此行为，请通过设置禁用hstore扩展 use_native_hstore 到 False 如下：

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

这个 HSTORE 类型是 仍然支持 当 psycopg2.extensions.register_hstore() 未使用扩展名。这仅仅意味着在参数端和结果端，python字典和hstore字符串格式之间的强制将发生在sqlachemy自己的编组逻辑中，而不是 psycopg2 这可能更有效。

PG8000

Support for the PostgreSQL database via the pg8000 driver.

DBAPI

Documentation and download information (if applicable) for pg8000 is available at: https://pypi.org/project/pg8000/

Connecting

Connect String:

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

在 1.4 版更改: pg8000方言已经针对1.16.6和更高版本进行了更新，并且再次成为SQLAlchemy与完整功能支持的持续集成的一部分。

统一码

PG8000将使用PostgreSQL对它和服务器之间的字符串值进行编码/解码。 client_encoding 参数；默认情况下，这是 postgresql.conf 文件，通常默认为 SQL_ASCII . 通常，可以将其更改为 utf-8 ，作为更有用的默认值：

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

这个 client_encoding 可以通过执行SQL来重写会话：

将客户机编码设置为“utf8”；

SQLAlchemy将根据传递给的值对所有新连接执行此SQL create_engine() 使用 client_encoding 参数：：

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:tiger@192.168.0.199/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:tiger@192.168.0.199/test",
    connect_args={"ssl_context": ssl_context},
)

PG8000事务隔离级别

PG8000方言提供的隔离级别设置与 psycopg2 方言：

• READ COMMITTED

• READ UNCOMMITTED

• REPEATABLE READ

• SERIALIZABLE

• AUTOCOMMIT

参见

事务隔离级别

psycopg2事务隔离级别

异步PG

Support for the PostgreSQL database via the asyncpg driver.

DBAPI

Documentation and download information (if applicable) for asyncpg is available at: https://magicstack.github.io/asyncpg/

Connecting

Connect String:

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

asyncpg方言是SQLAlchemy的第一个Python异步方言。

使用特殊的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")

方言也可以作为“同步”方言在 create_engine() 函数，它将把“await”调用传递到特殊事件循环中。这种操作方式是 有限使用 仅用于特殊测试场景。可以通过添加SQLAlchemy特定的标志来启用该模式 async_fallback 与 create_engine() ：：

# for testing purposes only; do not use in production!
engine = create_engine("postgresql+asyncpg://user:pass@hostname/dbname?async_fallback=true")

1.4 新版功能.

注解

默认情况下，asyncpg不解码 json 和 jsonb 类型并将其作为字符串返回。SQLAlchemy为设置默认类型解码器 json 和 jsonb 使用python内置的类型 json.loads 功能。可以通过设置属性来更改所使用的json实现 json_deserializer 使用创建引擎时 create_engine() 或 create_async_engine() .

预准备语句高速缓存

Asyncpg SQLAlChemy方言利用 asyncpg.connection.prepare() 用于所有语句。准备好的语句对象在构造之后被缓存，这似乎可以使语句调用的性能提高10%或更多。缓存是基于每个DBAPI连接的，这意味着预准备语句的主存储在连接池内的DBAPI连接池中。此高速缓存的大小默认为每个DBAPI连接100条语句，并且可以使用 prepared_statement_cache_size DBAPI参数(请注意，虽然此参数由SQLAlChemy实现，但它是异步方言的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 新版功能: 已添加 prepared_statement_cache_size 用于异步cpg。

警告

这个 asyncpg 数据库驱动程序必须对PostgreSQL类型的OID使用缓存，当自定义PostgreSQL数据类型(如 ENUM 通过DDL操作更改对象。此外，当DDL已经发送到PostgreSQL数据库时，如上所述由SQLAlChemy的驱动程序可选地缓存的预准备语句本身也可能变得“陈旧”，这将修改特定预准备语句中涉及的表或其他对象。

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

PycPopg2CFFI

Support for the PostgreSQL database via the psycopg2cffi driver.

DBAPI

Documentation and download information (if applicable) for psycopg2cffi is available at: https://pypi.org/project/psycopg2cffi/

Connecting

Connect String:

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

psycopg2cffi 是对 psycopg2 ，对C层使用CFFI。这使得它适合用于例如pypy。文件依据 psycopg2 .

1.0.0 新版功能.

参见

sqlalchemy.dialects.postgresql.psycopg2

PostPostgreSQL

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

DBAPI

Documentation and download information (if applicable) for py-postgresql is available at: https://python.projects.pgfoundry.org/

连接

Connect String:

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

注解

Pypostgresql方言是 未作为SQLAlchemy持续集成的一部分进行测试 可能还有未解决的问题。推荐的PostgreSQL驱动程序是psycopg2。

1.4 版后已移除: py postgresql DBAPI已弃用，将在将来的版本中删除。此DBAPI已被位于的外部版本取代 external-dialect. 请使用外部版本或受支持的dbapi之一连接到PostgreSQL。

吡格列克

Support for the PostgreSQL database via the pygresql driver.

DBAPI

Documentation and download information (if applicable) for pygresql is available at: https://www.pygresql.org/

Connecting

Connect String:

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

注解

Pygresql方言是 未作为SQLAlchemy持续集成的一部分进行测试 可能还有未解决的问题。推荐的PostgreSQL方言是psycopg2。

1.4 版后已移除: pygresql DBAPI已弃用，将在将来的版本中删除。请使用受支持的dbapi之一连接到PostgreSQL。