Release: 1.4.25 | Release Date: September 22, 2021

SQLAlchemy 1.4 Documentation

方言

《PostgreSQL》

Support for the PostgreSQL database.

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

支持的PostgreSQL版本

Support type

Versions

Fully tested in CI

9.6, 10, 11, 12, 13

Normal support

9.6+

Best effort

8+

DBAPI Support

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

序列/序列/标识

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

设置只读/可延迟

大多数PostgreSQL方言支持设置事务的“只读”和“可延迟”特性,这是对隔离级别设置的补充。通过传递 postgresql_readonlypostgresql_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_readonlypostgresql_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 方言级别选项被接受 TableMetaData.reflect() .

参见

The Schema Search Path -在PostgreSQL网站上。

INSERT/UPDATE...RETURNING

方言支持第8.2页 INSERT..RETURNINGUPDATE..RETURNINGDELETE..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构造,例如 UniqueConstraintPrimaryKeyConstraintIndexExcludeConstraint . 在这种用法中,如果约束有名称,则直接使用它。否则,如果约束未命名,则将使用推理,其中表达式和约束的可选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

仅从…

方言支持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_concurrentlyIndex 结构:

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 当检测到它正在镜像 UniqueConstraintTable.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())
Object Name Description

PGInspector

class sqlalchemy.dialects.postgresql.base.PGInspector(bind)
method sqlalchemy.dialects.postgresql.base.PGInspector.get_enums(schema=None)

返回枚举对象列表。

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

  • name-枚举的名称

  • schema-枚举的架构名称。

  • 可见-布尔值,无论此枚举在默认搜索路径中是否可见。

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

参数

schema -- 架构名称。如果没有,则使用默认架构(通常为“public”)。也可以设置为“*”以指示所有架构的加载枚举。

1.0.0 新版功能.

method sqlalchemy.dialects.postgresql.base.PGInspector.get_foreign_table_names(schema=None)

返回外部表名列表。

行为类似于 Inspector.get_table_names() ,但列表仅限于报告 relkind 价值 f .

1.0.0 新版功能.

method sqlalchemy.dialects.postgresql.base.PGInspector.get_table_oid(table_name, schema=None)

返回给定表名的OID。

method sqlalchemy.dialects.postgresql.base.PGInspector.get_view_names(schema=None, include=('plain', 'materialized'))

返回所有视图名称 schema .

参数
  • schema -- 可选,从非默认架构中检索名称。对于特殊报价,使用 quoted_name .

  • include -- 指定要返回的视图类型。作为字符串值(对于单个类型)或元组(对于任意数量的类型)传递。默认为 ('plain', 'materialized') . …添加的版本:1.1

PostgreSQL表选项

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大量使用现代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 新版功能.

列值函数

与表值函数类似,列值函数出现在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

行类型

对呈现 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支持将表作为参数传递给函数,它将该函数称为“记录”类型。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方言支持数组,既支持多维列类型,也支持数组文字:

JSON类型

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

HStury型

支持PostgreSQL hstore类型和hstore文本:

枚举类型

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

将枚举与数组一起使用

目前后端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聚合顺序。

例如。::

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.1 新版功能.

在 1.2.13 版更改: -order by参数可以是多个术语

参见

array_agg

class sqlalchemy.dialects.postgresql.array(clauses, **kw)

PostgreSQL数组文本。

这用于在SQL表达式中生成数组文本,例如:

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(item_type, as_tuple=False, dimensions=None, zero_indexes=False)

PostgreSQL数组类型。

在 1.1 版更改: 这个 ARRAY 类型现在是核心的子类 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])

这个 ARRAY 可能不支持所有PostgreSQL DBAPIS上的类型;目前已知它仅在PSycopg2上工作。

另外, ARRAY 类型不直接与 ENUM 类型。有关解决方法,请参见 将枚举与数组一起使用 .

参见

ARRAY -基数组类型

array -生成文本数组值。

class Comparator(expr)

为定义比较操作 ARRAY .

请注意,这些操作是对基地提供的操作的补充 Comparator 类,包括 Comparator.any()Comparator.all() .

类签名

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

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

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

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

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

此操作员可能会忽略kwargs,但它是API一致性所必需的。

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

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

method sqlalchemy.dialects.postgresql.ARRAY.__init__(item_type, as_tuple=False, dimensions=None, zero_indexes=False)

构造数组。

例如。::

Column('myarray', ARRAY(Integer))

论点是:

参数
  • item_type -- 此数组项的数据类型。注意维数在这里是不相关的,所以多维数组 INTEGER[][] ,构造为 ARRAY(Integer) ,而不是 ARRAY(ARRAY(Integer)) 或者这样。

  • as_tuple=False -- 指定是否应将返回结果从列表转换为元组。默认情况下,dbapis(如psycopg2)返回列表。当返回元组时,结果是可哈希的。

  • dimensions -- 如果非“无”,则数组将采用固定数量的维度。这将导致为此数组发出的DDL包含括号子句的确切数目。 [] 同时也将优化该类型的整体性能。请注意,pg数组总是隐式地“无维度”,这意味着无论如何声明,它们都可以存储任意数量的维度。

  • zero_indexes=False -- 如果为true,则索引值将在基于python零的索引和基于postgresql一的索引之间转换,例如,在传递到数据库之前,将向所有索引值添加一个值。…添加的版本:0.9.5

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

PostgreSQL特定形式的 array_agg ,确保返回类型为 ARRAY 而不是平原 ARRAY ,除非 type_ 通过。

1.1 新版功能.

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

数组级别的同义词 Comparator.any() 方法。有关详细信息,请参阅该方法。

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

数组级别的同义词 Comparator.all() 方法。有关详细信息,请参阅该方法。

class sqlalchemy.dialects.postgresql.BIT(length=None, varying=False)
class sqlalchemy.dialects.postgresql.BYTEA(length=None)
method sqlalchemy.dialects.postgresql.BYTEA.__init__(length=None)

inherited from the sqlalchemy.types.LargeBinary.__init__ method of LargeBinary

构造一个大二进制类型。

参数

length -- 可选,用于ddl语句中的列的长度,用于接受长度的二进制类型,例如mysql blob类型。

class sqlalchemy.dialects.postgresql.CIDR
class sqlalchemy.dialects.postgresql.DOUBLE_PRECISION(precision=None, asdecimal=False, decimal_return_scale=None)
method sqlalchemy.dialects.postgresql.DOUBLE_PRECISION.__init__(precision=None, asdecimal=False, decimal_return_scale=None)

inherited from the sqlalchemy.types.Float.__init__ method of Float

构造一个浮点。

参数
  • precision -- 用于DDL的数字精度 CREATE TABLE .

  • asdecimal -- 与…相同的标志 Numeric ,但默认为 False . 请注意,将此标志设置为 True 导致浮点转换。

  • decimal_return_scale -- 从浮点转换为python小数时使用的默认小数位数。由于小数点不准确,浮点值通常要长得多,而且大多数浮点数据库类型没有“小数位数”的概念,因此默认情况下,浮点类型在转换时查找前十位小数。指定此值将覆盖该长度。注意,如果没有另外指定,mysql float类型(包括“scale”)将使用“scale”作为decimal_return_scale的默认值。…版本已添加::0.9.0

class sqlalchemy.dialects.postgresql.ENUM(*enums, **kw)

PostgreSQL枚举类型。

这是 Enum 包括对PG的支持 CREATE TYPEDROP TYPE .

当内置类型 Enum 使用和 Enum.native_enum 标志的默认值为true,PostgreSQL后端将使用 ENUM 键入作为实现,因此将使用特殊的创建/删除规则。

枚举的创建/删除行为必然是复杂的,因为枚举类型与父表之间的关系很糟糕,因为它可能只由一个表“拥有”,也可能在多个表之间共享。

使用时 EnumENUM 以“内联”方式, CREATE TYPEDROP TYPE 发出的时间与 Table.create()Table.drop() 方法被调用::

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

要在多个表之间使用通用的枚举类型,最佳做法是声明 EnumENUM 独立,并将其与 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)
)

使用此模式时,必须在创建的单个表的级别上小心。在不指定的情况下发出创建表 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)

使用元数据级枚举类型时,如果调用元数据范围的create/drop,则始终创建并删除该类型::

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

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

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

在 1.0.0 版更改: 邮报 ENUM 类型现在在创建/删除方面的行为更加严格。将仅在元数据级别(而不是表级别)创建和删除元数据级别枚举类型,但 table.create(checkfirst=True) . 这个 table.drop() 调用现在将为表级枚举类型发出一个DROP类型。

类签名

class sqlalchemy.dialects.postgresql.ENUM (sqlalchemy.types.NativeForEmulated, sqlalchemy.types.Enum)

method sqlalchemy.dialects.postgresql.ENUM.__init__(*enums, **kw)

构建一个 ENUM .

参数与 Enum ,也包括以下参数。

参数

create_type -- 默认为true。表明 CREATE TYPE 在有选择地检查类型是否存在后,在创建父表时应发出;此外, DROP TYPE 在删除表时调用。什么时候? False ,将不执行任何检查 CREATE TYPEDROP TYPE 发出,除非 ENUM.create()ENUM.drop() 直接调用。设置为 False 在不访问实际数据库的情况下调用SQL文件的创建方案时非常有用 ENUM.create()ENUM.drop() 方法可用于向目标绑定发出SQL。

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

发出 CREATE TYPE 为此 ENUM .

如果底层方言不支持PostgreSQL创建类型,则不执行任何操作。

参数
  • bind -- 可连接的 EngineConnection 或类似对象以发出SQL。

  • checkfirst -- 如果 True ,在创建之前,将首先对pg目录执行查询,以查看类型是否已经不存在。

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

发出 DROP TYPE 为此 ENUM .

如果底层方言不支持PostgreSQL Drop类型,则不执行任何操作。

参数
  • bind -- 可连接的 EngineConnection 或类似对象以发出SQL。

  • checkfirst -- 如果 True ,将首先对pg目录执行查询,以在删除之前查看类型是否实际存在。

class sqlalchemy.dialects.postgresql.HSTORE(text_type=None)

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

对于与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() 功能。

class Comparator(expr)

为定义比较操作 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表达式的键的超集/包含键。

此操作员可能会忽略kwargs,但它是API一致性所必需的。

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

布尔表达式。测试键是否存在非空值。注意,键可能是一个sqla表达式。

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

hstore表达式。返回已删除给定密钥的hstore的内容。注意,键可能是一个sqla表达式。

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

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

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

布尔表达式。测试JSONB中是否存在任何密钥

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

布尔表达式。测试是否存在密钥。注意,键可能是一个sqla表达式。

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

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

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

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

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

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

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

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

method sqlalchemy.dialects.postgresql.HSTORE.__init__(text_type=None)

构建新的 HSTORE .

参数

text_type -- 应用于索引值的类型。默认为 Text . …添加的版本:1.1.0

method sqlalchemy.dialects.postgresql.HSTORE.bind_processor(dialect)

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

返回一个callable,该callable将接收一个bind参数值作为唯一的位置参数,并返回一个要发送到db-api的值。

如果不需要处理,则该方法应返回 None .

参数

dialect -- 方言实例正在使用中。

attribute sqlalchemy.dialects.postgresql.HSTORE.comparator_factory

alias of sqlalchemy.dialects.postgresql.hstore.HSTORE.Comparator

method sqlalchemy.dialects.postgresql.HSTORE.result_processor(dialect, coltype)

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

返回一个可调用的,它将接收作为唯一位置参数的结果行列值,并返回一个值以返回给用户。

如果不需要处理,则该方法应返回 None .

参数
  • dialect -- 方言实例正在使用中。

  • coltype -- 在cursor.description中接收到dbapi coltype参数。

class sqlalchemy.dialects.postgresql.hstore(*args, **kwargs)

使用PostgreSQL在SQL表达式中构造一个hstore值 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 数据类型。

attribute sqlalchemy.dialects.postgresql.hstore.type

alias of sqlalchemy.dialects.postgresql.hstore.HSTORE

class sqlalchemy.dialects.postgresql.INET
class sqlalchemy.dialects.postgresql.INTERVAL(precision=None, fields=None)

PostgreSQL间隔类型。

类签名

class sqlalchemy.dialects.postgresql.INTERVAL (sqlalchemy.types.NativeForEmulated, sqlalchemy.types._AbstractInterval)

method sqlalchemy.dialects.postgresql.INTERVAL.__init__(precision=None, fields=None)

构造一个间隔。

参数
  • precision -- 可选整数精度值

  • fields -- 字符串字段说明符。允许限制字段的存储,例如 "YEAR""MONTH""DAY TO HOUR" 等。添加的版本:1.2

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

表示PostgreSQL JSON类型。

JSON 每当基地 JSON 数据类型用于PostgreSQL后端,但是 JSON datatype不为PostgreSQL特定的比较方法提供Python访问器,例如 Comparator.astext() ;另外,使用PostgreSQL JSONB , the 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'

在 1.1 版更改: 这个 ColumnElement.cast() JSON对象上的运算符现在要求 Comparator.astext 如果强制转换仅从文本字符串起作用,则显式调用修饰符。

索引操作返回类型默认为的表达式对象 JSON 默认情况下,这样可以对结果类型调用更多面向JSON的指令。

自定义序列化程序和反序列化程序是在方言级别指定的,即使用 create_engine() . 原因是,当使用psycopg2时,DBAPI只允许在每个光标或每个连接级别使用序列化程序。例如。::

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

当使用psycopg2方言时,json_反序列化程序使用 psycopg2.extras.register_default_json .

参见

JSON -核心级JSON类型

JSONB

在 1.1 版更改: JSON 现在是PostgreSQL特有的新的专门化 JSON 类型。

class Comparator(expr)

为定义比较操作 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)
method sqlalchemy.dialects.postgresql.JSON.__init__(none_as_null=False, astext_type=None)

构建一个 JSON 类型。

参数
  • none_as_null -- 如果为真,则保持该值 None 作为SQL空值,而不是 null . 请注意,当此标志为false时, null() 构造仍可以用于持久化空值::from sqlAlchemy import null conn.execute(table.insert(),data=null())。。版本已更改::0.9.8-已添加 none_as_nullnull() 现在支持以保持空值。…参阅: JSON.NULL

  • astext_type -- 用于的类型 Comparator.astext 索引属性上的访问器。默认为 Text . …添加的版本:1.1

attribute sqlalchemy.dialects.postgresql.JSON.comparator_factory

alias of sqlalchemy.dialects.postgresql.json.JSON.Comparator

class sqlalchemy.dialects.postgresql.JSONB(none_as_null=False, astext_type=None)

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

JSON 类型 JSONB 当与ORM一起使用时,类型不检测就地更改,除非 sqlalchemy.ext.mutable 使用扩展名。

自定义序列化程序和反序列化程序与共享 JSON 类,使用 json_serializerjson_deserializer 关键字参数。必须在方言级别使用 create_engine() . 使用psycopg2时,序列化程序与使用 psycopg2.extras.register_default_jsonb 在每个连接的基础上,以相同的方式 psycopg2.extras.register_default_json 用于用JSON类型注册这些处理程序。

0.9.7 新版功能.

参见

JSON

class Comparator(expr)

为定义比较操作 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.has_all(other)

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

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

布尔表达式。测试JSONB中是否存在任何密钥

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

布尔表达式。测试是否存在密钥。注意,键可能是一个sqla表达式。

attribute sqlalchemy.dialects.postgresql.JSONB.comparator_factory

alias of sqlalchemy.dialects.postgresql.json.JSONB.Comparator

class sqlalchemy.dialects.postgresql.MACADDR
class sqlalchemy.dialects.postgresql.MONEY

提供PostgreSQL货币类型。

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

因此,可能更可取的做法是使用以下命令提供到基于数字的货币数据类型的转换 TypeDecorator ::

import re
import decimal
from sqlalchemy import TypeDecorator

class NumericMoney(TypeDecorator):
    impl = MONEY

    def process_result_value(self, value: Any, dialect: Any) -> 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() 方法如下:

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

提供PostgreSQL OID类型。

0.9.5 新版功能.

class sqlalchemy.dialects.postgresql.REAL(precision=None, asdecimal=False, decimal_return_scale=None)

SQL实数类型。

method sqlalchemy.dialects.postgresql.REAL.__init__(precision=None, asdecimal=False, decimal_return_scale=None)

inherited from the sqlalchemy.types.Float.__init__ method of Float

构造一个浮点。

参数
  • precision -- 用于DDL的数字精度 CREATE TABLE .

  • asdecimal -- 与…相同的标志 Numeric ,但默认为 False . 请注意,将此标志设置为 True 导致浮点转换。

  • decimal_return_scale -- 从浮点转换为python小数时使用的默认小数位数。由于小数点不准确,浮点值通常要长得多,而且大多数浮点数据库类型没有“小数位数”的概念,因此默认情况下,浮点类型在转换时查找前十位小数。指定此值将覆盖该长度。注意,如果没有另外指定,mysql float类型(包括“scale”)将使用“scale”作为decimal_return_scale的默认值。…版本已添加::0.9.0

class sqlalchemy.dialects.postgresql.REGCLASS

提供PostgreSQL RegClass类型。

1.2.7 新版功能.

class sqlalchemy.dialects.postgresql.TSVECTOR

这个 TSVECTOR 类型实现PostgreSQL文本搜索类型tsvector。

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

0.9.0 新版功能.

参见

全文搜索

class sqlalchemy.dialects.postgresql.UUID(as_uuid=False)

PostgreSQL UUID类型。

表示uuid列类型,将数据解释为dbapi本机返回的或python uuid对象。

目前已知UUID类型可以在SQLAlChemy支持的主要DBAPI驱动程序中工作,包括mental copg2、pg8000和asyncpg。对其他DBAPI驱动程序的支持可能不完整或不存在。

method sqlalchemy.dialects.postgresql.UUID.__init__(as_uuid=False)

构造UUID类型。

参数

as_uuid=False -- 如果为true,则值将被解释为python uuid对象,通过dbapi转换为字符串或从字符串转换为字符串。

范围类型

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

表示PostgreSQL Int4Range类型。

class sqlalchemy.dialects.postgresql.INT8RANGE

表示PostgreSQL Int8Range类型。

class sqlalchemy.dialects.postgresql.NUMRANGE

表示PostgreSQL NumRange类型。

class sqlalchemy.dialects.postgresql.DATERANGE

表示PostgreSQL日期范围类型。

class sqlalchemy.dialects.postgresql.TSRANGE

表示PostgreSQL tsrange类型。

class sqlalchemy.dialects.postgresql.TSTZRANGE

表示PostgreSQL TSZrange类型。

上述类型的大部分功能都来自以下混音器:

class sqlalchemy.dialects.postgresql.ranges.RangeOperators

此混合提供了范围运算符的功能,这些运算符在的表9-44中列出 `PostgreSQL documentation`_ _表示范围函数和运算符。中提供的所有范围类型都使用它。 postgres 方言,并且可能用于您自己创建的任何范围类型。

PostgreSQL文档表9-45中列出的范围函数不提供额外支持。对于这些,正常的 func() 对象,则应使用。

class comparator_factory(expr)

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

类签名

class sqlalchemy.dialects.postgresql.ranges.RangeOperators.comparator_factory (sqlalchemy.types.Comparator)

method sqlalchemy.dialects.postgresql.ranges.RangeOperators.comparator_factory.__ne__(other)

布尔表达式。如果两个范围不相等,则返回“真”

method sqlalchemy.dialects.postgresql.ranges.RangeOperators.comparator_factory.adjacent_to(other)

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

method sqlalchemy.dialects.postgresql.ranges.RangeOperators.comparator_factory.contained_by(other)

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

method sqlalchemy.dialects.postgresql.ranges.RangeOperators.comparator_factory.contains(other, **kw)

布尔表达式。如果列中包含右操作数(可以是元素或范围),则返回true。

此操作员可能会忽略kwargs,但它是API一致性所必需的。

method sqlalchemy.dialects.postgresql.ranges.RangeOperators.comparator_factory.not_extend_left_of(other)

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

method sqlalchemy.dialects.postgresql.ranges.RangeOperators.comparator_factory.not_extend_right_of(other)

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

method sqlalchemy.dialects.postgresql.ranges.RangeOperators.comparator_factory.overlaps(other)

布尔表达式。如果列与右侧操作数重叠(具有相同的点),则返回true。

method sqlalchemy.dialects.postgresql.ranges.RangeOperators.comparator_factory.strictly_left_of(other)

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

method sqlalchemy.dialects.postgresql.ranges.RangeOperators.comparator_factory.strictly_right_of(other)

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

警告

范围类型DDL支持应该与任何PostgreSQL DBAPI驱动程序一起工作,但是返回的数据类型可能会有所不同。如果您正在使用 psycopg2 ,建议在使用这些列类型之前升级到2.5版或更高版本。

在实例化使用这些列类型的模型时,您应该传递您用于该列类型的DBAPI驱动程序所期望的任何数据类型。为了 psycopg2 这些是 psycopg2.extras.NumericRangepsycopg2.extras.DateRangepsycopg2.extras.DateTimeRangepsycopg2.extras.DateTimeTZRange 或者你注册的班级 psycopg2.extras.register_range .

例如:

from psycopg2.extras import DateTimeRange
from sqlalchemy.dialects.postgresql import TSRANGE

class RoomBooking(Base):

    __tablename__ = 'room_booking'

    room = Column(Integer(), primary_key=True)
    during = Column(TSRANGE())

booking = RoomBooking(
    room=101,
    during=DateTimeRange(datetime(2013, 3, 23), None)
)

PostgreSQL约束类型

SQLAlchemy支持PostgreSQL通过 ExcludeConstraint 班级:

Object Name Description

ExcludeConstraint

表级排除约束。

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

表级排除约束。

定义排除约束,如 `PostgreSQL documentation`_ _.

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'}
    )
)
参数
  • *elements -- 形式的两个元组的序列 (column, operator) 其中“column”是SQL表达式元素或原始SQL字符串,通常是 Column 对象,“operator”是包含要使用的运算符的字符串。以便在 Column 对象不可用,在确保任何必要的引用规则生效的同时,临时 Columncolumn() 应使用对象。

  • name -- 可选,此约束的数据库内名称。

  • deferrable -- 可选布尔值。如果设置了,则在为该约束发出DDL时发出“可延迟”或“不可延迟”。

  • initially -- 可选字符串。如果已设置,则在为此约束发出DDL时最初发出<value>。

  • using -- 可选字符串。如果设置,则在为此约束发出DDL时使用<index_method>发出。默认为“gist”。

  • where -- 可选的SQL表达式构造或文本SQL字符串。如果设置,则在为此约束发出ddl时发出where<predicate>。…警告:: ExcludeConstraint.where 参数 ExcludeConstraint 可以作为python字符串参数传递,该参数将被视为 可信SQL文本 并按规定呈现。 不要将不受信任的输入传递给此参数 .

  • ops -- 可选字典。用于定义元素的运算符类;其工作方式与 postgresql_ops 参数指定给 Index 构造。。版本添加::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构造

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)

构建一个 Insert 对象。

此文档继承自 sqlalchemy.sql.expression.insert() ;这个构造器, sqlalchemy.dialects.postgresql.insert() ,创建一个 sqlalchemy.dialects.postgresql.Insert 对象。有关描述此子类的其他详细信息,请参见该类。

例如。::

from sqlalchemy import insert

stmt = (
    insert(user_table).
    values(name='username', fullname='Full Username')
)

类似功能可通过 TableClause.insert() 方法对 Table .

参数
  • table -- TableClause 这是插入的主题。

  • values -- 要插入的值集合;请参见 Insert.values() 有关此处允许格式的说明。完全可以省略;a Insert 构造还将根据传递给的参数在执行时动态地呈现values子句。 Connection.execute() . .. 已弃用::1.4 insert.values 参数将在SQLAlchemy 2.0中删除。请参考 Insert.values() 方法。

  • inline -- 如果为True,则不会尝试检索要在语句中提供的SQL生成的默认值;特别是,这允许SQL表达式在语句中以“内联”方式呈现,而无需预先执行它们;对于支持“返回”的后端,这将关闭语句的“隐式返回”功能。。已弃用::1.4 insert.inline 参数将在SQLAlchemy 2.0中删除。请使用 Insert.inline() 方法。

如果两者 Insert.values 并且存在编译时绑定参数,编译时绑定参数重写 Insert.values 以每键为基础。

里面的钥匙 Insert.values 要么可以 Column 对象或其字符串标识符。每个键可引用以下其中一个:

  • 文字数据值(即字符串、数字等);

  • 列对象;

  • 选择语句。

如果A SELECT 指定了引用此语句的语句 INSERT 语句的表,该语句将与 INSERT 语句。

参见

插入表达式 -SQL表达式教程

插入、更新和删除 -SQL表达式教程

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

PostgreSQL特定的insert实现。

为pg特定语法添加方法,如on conflict。

这个 Insert 对象是使用 sqlalchemy.dialects.postgresql.insert() 功能。

1.1 新版功能.

attribute sqlalchemy.dialects.postgresql.Insert.excluded

提供 excluded 冲突语句的命名空间

pg的on conflict子句允许引用将要插入的行,称为 excluded . 此属性提供此行中的所有列都是可引用的。

小技巧

这个 Insert.excluded 属性是 ColumnCollection ,它提供的接口与 Table.c 有关集合的说明,请参阅 访问表和列 。使用此集合,可以像属性一样访问普通名称(例如 stmt.excluded.some_column ),但是应该使用索引访问来访问特殊名称和字典方法名称,例如 stmt.excluded["column name"]stmt.excluded["values"] 。请参阅的文档字符串 ColumnCollection 了解更多的例子。

参见

冲突时插入(向上插入) - example of how to use Insert.excluded

method sqlalchemy.dialects.postgresql.Insert.on_conflict_do_nothing(constraint=None, index_elements=None, index_where=None)

为on conflict子句指定不做任何操作。

这个 constraintindex_elements 参数是可选的,但只能指定其中一个。

参数
  • constraint -- 表上唯一约束或排除约束的名称,或者约束对象本身(如果它具有.name属性)。

  • index_elements -- 由字符串列名组成的序列, Column 对象或其他将用于推断目标索引的列表达式对象。

  • index_where -- 可用于推断条件目标索引的附加Where条件。…添加的版本:1.1

method sqlalchemy.dialects.postgresql.Insert.on_conflict_do_update(constraint=None, index_elements=None, index_where=None, set_=None, where=None)

为on conflict子句指定do update set操作。

要么 constraintindex_elements 参数是必需的,但只能指定其中一个。

参数
  • constraint -- 表上唯一约束或排除约束的名称,或者约束对象本身(如果它具有.name属性)。

  • index_elements -- 由字符串列名组成的序列, Column 对象或其他将用于推断目标索引的列表达式对象。

  • index_where -- 可用于推断条件目标索引的附加Where条件。

  • set_ -- 字典或其他映射对象,其中键是目标表中列的名称,或者 Column 对象或其他ORM映射列与目标表的列相匹配,并将表达式或文本指定为值 SET 要采取的行动。。版本添加::1.4 Insert.on_conflict_do_update.set_ 参数支持 Column 来自目标的对象 Table 作为钥匙。。警告::这本字典有 not 考虑到python指定的默认更新值或生成函数,例如使用 Column.onupdate . 除非在 Insert.on_conflict_do_update.set_ 字典。

  • where -- 可选参数。如果存在,则可以是文本SQL字符串或 WHERE 限制受影响的行的子句 DO UPDATE SET . 行不符合 WHERE 不会更新条件(有效地 DO NOTHING 对于那些行)。…添加的版本:1.1

PycPcG2

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() ,并包括以下内容:

小技巧

上述关键字参数为 方言 关键字参数,这意味着它们将作为显式关键字参数传递给 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")

指定多个回退主机

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

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

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

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

空的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_sizeexecutemany_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=Falsecreate_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

通知日志

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_hstoreFalse 如下:

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

异步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_fallbackcreate_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不解码 jsonjsonb 类型并将其作为字符串返回。SQLAlchemy为设置默认类型解码器 jsonjsonb 使用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 新版功能.

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。

Previous: 方言 Next: MySQL和Mariadb