Release: 1.4.0b1 | Release Date: November 2, 2020

SQLAlchemy 1.4 Documentation

《PostgreSQL》

Support for the PostgreSQL database.

数据库接口支持

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)
        NOT NULL,
    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 NOT NULL,
    data VARCHAR,
    PRIMARY KEY (id)
)

服务器端光标

服务器端游标支持可用于psycopg2、asyncpg方言,也可用于其他方言。

服务器端游标通过使用 Connection.execution_options.stream_results 连接执行选项:

with engine.connect() as conn:
    result = conn.execution_options(stream_resuls=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 执行选项。

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

邮报 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=True, 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=True,
...                       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允许指定 运算符类 对于索引的每一列(请参见http://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-Tree、哈希、GIST和GIN,以及用户创建自己的索引的能力(请参见http://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数据库就会隐式地创建一个惟一索引。检查桌子时使用 Inspector , the Inspector.get_indexes() 以及 Inspector.get_unique_constraints() 将清楚地报告这两个构造;对于索引,键 duplicates_constraint 如果被检测为镜像约束,则将出现在索引项中。执行反射时使用 Table(..., autoload=True) ,唯一索引是 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

表值、行和元组对象

行类型

内置支持渲染 ROW 目前还不可用,但是 tuple_() 可能被用来代替它。另一种选择是使用 sqlalchemy.func 发电机 func.ROW ::

table.select().where(
    tuple_(table.c.id, table.c.fk) > (1,2)
).where(func.ROW(table.c.id, table.c.fk) < func.ROW(3, 7))

将生成行比较::

SELECT *
FROM table
WHERE (id, fk) > (1, 2)
AND ROW(id, fk) < ROW(3, 7)

表格类型

PostgreSQL还支持将表作为参数传递给函数。这在sqlalchemy中还不可用,但是 literal_column() 可以使用表名为的函数替换它:

select('*').select_from(func.my_function(literal_column('my_table')))

将生成SQL::

SELECT *
FROM my_function(my_table)

数组类型

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数组,我们需要呈现适当的CAST。当前的psycopg2驱动程序可以正确地容纳结果集,而无需任何特殊步骤。

在 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数据类型

与所有的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)

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

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

这个方法是遗留的,在这里是为了向后兼容。

参见

any_()

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

的同义词 Comparator.all() 方法。

这个方法是遗留的,在这里是为了向后兼容。

参见

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表达式的键的超集/包含键。

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

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

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

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 sqlalchemy.dialects.postgresql.JSONB (sqlalchemy.dialects.postgresql.json.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表达式的键的超集/包含键。

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

JSONB.Comparator 的别名

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

提供PostgreSQL货币类型。

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对象。

所有DBAPI上可能不支持UUID类型。已知它在psycopg2上工作,而不是pg8000。

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中列出的测距仪提供了功能。 `postgres 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中列出的测距仪提供了功能。 `postgres documentation`_ _用于范围函数和运算符。它由中提供的所有范围类型使用 postgres 方言,可能用于您自己创建的任何范围类型。

Postgres文档表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。

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)

表级排除约束。

定义排除约束,如中所述 `postgres 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 . 此属性提供此行中的所有列都是可引用的。

参见

冲突时插入(向上插入) - 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: http://pypi.python.org/pypi/psycopg2/

Connecting

Connect String:

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

psycopg2连接参数

psycopg2接受的特定关键字参数 create_engine() 是:

  • use_native_unicode :启用每个连接使用psycopg2“本机Unicode”模式。默认为true。

  • isolation_level :此选项适用于所有PostgreSQL方言,包括 AUTOCOMMIT 使用psycopg2方言时的隔离级别。

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

  • executemany_modeexecutemany_batch_page_sizeexecutemany_values_page_size :允许使用psycopg2扩展优化“executemany”类型的查询。有关详细信息,请参阅下面的参考部分。

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快速执行助手

现代版本的psycopg2包括一个被称为 Fast Execution Helpers ,在基准测试中显示,它可以将psycopg2的executemany()性能(主要是INSERT语句)提高多个数量级。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

默认情况下,psycopg2驱动程序使用 psycopg2.extensions.UNICODE 扩展名,这样DBAPI直接接收并返回所有字符串作为python unicode对象—sqlAlchemy不做任何更改地传递这些值。这里的psycopg2将根据当前的“客户端编码”设置对字符串值进行编码/解码;默认情况下,这是 postgresql.conf 文件,通常默认为 SQL_ASCII . 通常,可以将其更改为 utf8 ,作为更有用的默认值:

# postgresql.conf file

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

影响客户端编码的第二种方法是在本地psycopg2中设置它。sqlAlchemy将调用psycopg2 psycopg2:connection.set_client_encoding() 基于传递给的值的所有新连接的方法 create_engine() 使用 client_encoding 参数::

# set_client_encoding() setting;
# works for *all* PostgreSQL versions
engine = create_engine("postgresql://user:pass@host/dbname",
                       client_encoding='utf8')

这将覆盖PostgreSQL客户机配置中指定的编码。以这种方式使用参数时,psycopg2驱动程序将发出 SET client_encoding TO 'utf8' 在连接上显式,并在所有PostgreSQL版本中工作。

请注意 client_encoding 传递到的设置 create_engine()不一样 正如最近增加的 client_encoding 参数现在由libpq直接支持。当 client_encoding 直接传递给 psycopg2.connect() ,并使用 create_engine.connect_args 参数::

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

# using the query string is equivalent
engine = create_engine("postgresql://user:pass@host/dbname?client_encoding=utf8")

以上参数仅在PostgreSQL 9.1版本中添加到libpq中,因此使用前面的方法更好地支持跨版本。

禁用本机Unicode

也可以指示sqlAlchemy跳过psycopg2的用法。 UNICODE 扩展和使用自己的Unicode编码/解码服务,这些服务通常只保留给那些不完全支持Unicode的DBAPI。经过 use_native_unicode=Falsecreate_engine() 将禁用 psycopg2.extensions. UNICODE . sqlAlchemy将使用 create_engine() encoding 参数,默认为 utf-8 . 由于大多数DBAPI现在完全支持Unicode,SQLAlchemy自己的Unicode编码/解码功能正逐渐过时。

绑定参数样式

psycopg2方言的默认参数样式是“pyformat”,其中使用 %(paramname)s 风格。此格式有一个限制,即它不适应实际包含百分号或括号符号的参数名称的异常情况;由于SQLAlchemy在许多情况下根据列的名称生成绑定参数名称,因此列名称中存在这些字符可能会导致问题。

有两种方法可以解决 Column 它的名称中包含这些字符之一。一是具体说明 Column.key 对于具有此类名称的列:

measurement = Table('measurement', metadata,
    Column('Size (meters)', Integer, key='size_meters')
)

上面的插入语句,例如 measurement.insert() 将使用 size_meters 作为参数名和SQL表达式,例如 measurement.c.size_meters > 10 将从 size_meters 也是关键。

在 1.0.0 版更改: -SQL表达式将使用 Column.key 作为在SQL表达式中创建匿名绑定参数时的命名源;以前,此行为仅适用于 Table.insert()Table.update() 参数名称。

另一种解决方案是使用位置格式;psycopg2允许使用“format”参数样式,可以将其传递给 create_engine.paramstyle ::

engine = create_engine(
    'postgresql://scott:tiger@localhost:5432/test', paramstyle='format')

使用上面的引擎,而不是像这样的语句:

INSERT INTO measurement ("Size (meters)") VALUES (%(Size (meters))s)
{'Size (meters)': 1}

相反,我们看到:

INSERT INTO measurement ("Size (meters)") VALUES (%s)
(1, )

在上面的位置,字典样式转换为具有位置样式的元组。

交易

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

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

PycPopg2CFFI

Support for the PostgreSQL database via the psycopg2cffi driver.

DBAPI

Documentation and download information (if applicable) for psycopg2cffi is available at: http://pypi.python.org/pypi/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: http://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: http://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