使用Engines和Connection ¶

Result.yield_per()

英文

To enable server side cursors without a specific partition size, the Connection.execution_options.stream_results option may be used, which like Connection.execution_options.yield_per may be called on the Connection object or the statement object.

When a Result object delivered using the Connection.execution_options.stream_results option is iterated directly, rows are fetched internally using a default buffering scheme that buffers first a small set of rows, then a larger and larger buffer on each fetch up to a pre-configured limit of 1000 rows. The maximum size of this buffer can be affected using the Connection.execution_options.max_row_buffer execution option:

with engine.connect() as conn:
    with conn.execution_options(stream_results=True, max_row_buffer=100).execute(
        text("select * from table")
    ) as result:
        for row in result:
            print(f"{row}")

While the Connection.execution_options.stream_results option may be combined with use of the Result.partitions() method, a specific partition size should be passed to Result.partitions() so that the entire result is not fetched. It is usually more straightforward to use the Connection.execution_options.yield_per option when setting up to use the Result.partitions() method.

参见

Connection.begin_twophase()

Result.yield_per()

模式名称的转换¶

Translation of Schema Names

中文

为了支持将一组通用表分布到多个 schema 的多租户应用程序，可以使用 Connection.execution_options.schema_translate_map 执行选项，在不修改表结构定义的前提下，将一组 Table 对象重定向到不同的 schema 名称下。

给定如下表结构：

user_table = Table(
    "user",
    metadata_obj,
    Column("id", Integer, primary_key=True),
    Column("name", String(50)),
)

此 Table 的 “schema” 由 Table.schema 属性定义，此处为 None。可以通过 Connection.execution_options.schema_translate_map 指定所有 schema 为 None 的 Table 对象实际渲染为 user_schema_one：

connection = engine.connect().execution_options(
    schema_translate_map={None: "user_schema_one"}
)

result = connection.execute(user_table.select())

上述代码将在数据库中生成如下 SQL：

SELECT user_schema_one.user.id, user_schema_one.user.name FROM
user_schema_one.user

也就是说，schema 名称被替换为我们设定的映射值。此映射可以指定任意数量的 schema 替换关系：

connection = engine.connect().execution_options(
    schema_translate_map={
        None: "user_schema_one",  # 无 schema 名 -> "user_schema_one"
        "special": "special_schema",  # schema="special" 替换为 "special_schema"
        "public": None,  # schema="public" 的 Table 对象将渲染为无 schema
    }
)

Connection.execution_options.schema_translate_map 参数会影响所有由 SQL 表达式语言生成的 DDL 和 SQL 构造，这些构造来源于 Table 或 Sequence 对象。 **不会**影响使用 text() 构造或通过 Connection.execute() 直接传入的字面 SQL 字符串。

该功能仅在 schema 名称直接来源于 Table 或 Sequence 对象时生效；不影响那些显式传入 schema 字符串的 API。按此逻辑，它适用于例如 MetaData.create_all() 或 MetaData.drop_all() 等方法中的 “可创建 / 可删除” 检查，也适用于基于 Table 对象的表反射。但它 不适用于 Inspector 对象中的操作，因为这些操作需要显式提供 schema 名称。

小技巧

要在 ORM 的 Session 中使用 schema 翻译功能，请在 Engine 层设置该选项，然后将该 engine 传入 Session。 Session 在每次事务中使用新的 Connection：

schema_engine = engine.execution_options(schema_translate_map={...})

session = Session(schema_engine)

...

警告

如果在没有扩展的情况下使用 ORM 的 Session， schema 翻译功能仅支持 每个 Session 使用一个 schema_translate_map。如果尝试为每条语句指定不同的 schema_translate_map，则 将无法生效，因为 ORM 的 Session 不会对单个对象考虑当前的 schema_translate 配置。

若要使用单个 Session 支持多个不同的 schema_translate_map 配置，可以使用水平分片扩展。参见示例水平分片。

英文

To support multi-tenancy applications that distribute common sets of tables into multiple schemas, the Connection.execution_options.schema_translate_map execution option may be used to repurpose a set of Table objects to render under different schema names without any changes.

Given a table:

user_table = Table(
    "user",
    metadata_obj,
    Column("id", Integer, primary_key=True),
    Column("name", String(50)),
)

The “schema” of this Table as defined by the Table.schema attribute is None. The Connection.execution_options.schema_translate_map can specify that all Table objects with a schema of None would instead render the schema as user_schema_one:

connection = engine.connect().execution_options(
    schema_translate_map={None: "user_schema_one"}
)

result = connection.execute(user_table.select())

The above code will invoke SQL on the database of the form:

SELECT user_schema_one.user.id, user_schema_one.user.name FROM
user_schema_one.user

That is, the schema name is substituted with our translated name. The map can specify any number of target->destination schemas:

connection = engine.connect().execution_options(
    schema_translate_map={
        None: "user_schema_one",  # no schema name -> "user_schema_one"
        "special": "special_schema",  # schema="special" becomes "special_schema"
        "public": None,  # Table objects with schema="public" will render with no schema
    }
)

The Connection.execution_options.schema_translate_map parameter affects all DDL and SQL constructs generated from the SQL expression language, as derived from the Table or Sequence objects. It does not impact literal string SQL used via the text() construct nor via plain strings passed to Connection.execute().

The feature takes effect only in those cases where the name of the schema is derived directly from that of a Table or Sequence; it does not impact methods where a string schema name is passed directly. By this pattern, it takes effect within the “can create” / “can drop” checks performed by methods such as MetaData.create_all() or MetaData.drop_all() are called, and it takes effect when using table reflection given a Table object. However it does not affect the operations present on the Inspector object, as the schema name is passed to these methods explicitly.

小技巧

To use the schema translation feature with the ORM Session, set this option at the level of the Engine, then pass that engine to the Session. The Session uses a new Connection for each transaction:

schema_engine = engine.execution_options(schema_translate_map={...})

session = Session(schema_engine)

...

警告

When using the ORM Session without extensions, the schema translate feature is only supported as a single schema translate map per Session. It will not work if different schema translate maps are given on a per-statement basis, as the ORM Session does not take current schema translate values into account for individual objects.

To use a single Session with multiple schema_translate_map configurations, the 水平分片 extension may be used. See the example at 水平分片.

引擎处置¶

Engine Disposal

中文

Engine 是一个连接池，这意味着在正常情况下，只要 Engine 对象仍然驻留在内存中，就会存在打开的数据库连接。当 Engine 被垃圾回收时，它的连接池将不再被引用，假设它的连接没有被借出，则连接池和连接也会被垃圾回收，最终关闭实际的数据库连接。然而，在其他情况下，Engine 将保留打开的数据库连接，假设它使用的是默认的 QueuePool 连接池实现。

Engine 通常应作为一个永久的组件，在应用程序的生命周期内建立并保持。它不应按连接基础创建和销毁；而应作为一个注册表，维护一个连接池以及数据库和 DBAPI 使用的配置信息，并且还会有一定程度的数据库资源的内部缓存。

但是，在许多情况下，完全关闭所有由 Engine 引用的连接资源是很有必要的。在这些情况下，通常不建议依赖 Python 的垃圾回收来完成此操作；而是应该显式调用 Engine.dispose() 方法来释放连接池，并用一个新的空池替换它。如果此时丢弃 Engine 并且不再使用它，那么它引用的所有 已检查入 的连接也会被完全关闭。

调用 Engine.dispose() 的有效使用案例包括：

当程序希望释放连接池中仍持有的连接，并且期望不再连接到该数据库。
当程序使用多处理或 fork() 时，且 Engine 对象被复制到子进程中，应调用 Engine.dispose()，以便引擎为该 fork 创建新的本地数据库连接。
在测试套件或多租户场景中，可能会创建和销毁许多临时的、短生命周期的 Engine 对象。

当连接处于借出状态时，它们不会在引擎被丢弃或垃圾回收时被丢弃，因为这些连接在应用程序的其他地方仍然有强引用。然而，在调用 Engine.dispose() 后，这些连接将不再与 Engine 相关联；当它们关闭时，它们将返回到它们现在成为孤立的连接池中，最终被垃圾回收。由于这个过程难以控制，强烈建议仅在所有借出连接已检查入或被移除池后调用 Engine.dispose()。

如果应用程序对 Engine 使用连接池的方式受到负面影响，可以选择完全禁用连接池。这样做通常只会对使用新连接的性能产生适度影响，并且每次检查入连接时都会完全关闭，而不会在内存中保持。有关如何禁用连接池的更多指导，请参见切换池实现。

参见

连接池

使用具有多处理或 os.fork() 的连接池

英文

The Engine refers to a connection pool, which means under normal circumstances, there are open database connections present while the Engine object is still resident in memory. When an Engine is garbage collected, its connection pool is no longer referred to by that Engine, and assuming none of its connections are still checked out, the pool and its connections will also be garbage collected, which has the effect of closing out the actual database connections as well. But otherwise, the Engine will hold onto open database connections assuming it uses the normally default pool implementation of QueuePool.

The Engine is intended to normally be a permanent fixture established up-front and maintained throughout the lifespan of an application. It is not intended to be created and disposed on a per-connection basis; it is instead a registry that maintains both a pool of connections as well as configurational information about the database and DBAPI in use, as well as some degree of internal caching of per-database resources.

However, there are many cases where it is desirable that all connection resources referred to by the Engine be completely closed out. It’s generally not a good idea to rely on Python garbage collection for this to occur for these cases; instead, the Engine can be explicitly disposed using the Engine.dispose() method. This disposes of the engine’s underlying connection pool and replaces it with a new one that’s empty. Provided that the Engine is discarded at this point and no longer used, all checked-in connections which it refers to will also be fully closed.

Valid use cases for calling Engine.dispose() include:

When a program wants to release any remaining checked-in connections held by the connection pool and expects to no longer be connected to that database at all for any future operations.
When a program uses multiprocessing or fork(), and an Engine object is copied to the child process, Engine.dispose() should be called so that the engine creates brand new database connections local to that fork. Database connections generally do not travel across process boundaries. Use the Engine.dispose.close parameter set to False in this case. See the section 使用具有多处理或 os.fork() 的连接池 for more background on this use case.
Within test suites or multitenancy scenarios where many ad-hoc, short-lived Engine objects may be created and disposed.

Connections that are checked out are not discarded when the engine is disposed or garbage collected, as these connections are still strongly referenced elsewhere by the application. However, after Engine.dispose() is called, those connections are no longer associated with that Engine; when they are closed, they will be returned to their now-orphaned connection pool which will ultimately be garbage collected, once all connections which refer to it are also no longer referenced anywhere. Since this process is not easy to control, it is strongly recommended that Engine.dispose() is called only after all checked out connections are checked in or otherwise de-associated from their pool.

An alternative for applications that are negatively impacted by the Engine object’s use of connection pooling is to disable pooling entirely. This typically incurs only a modest performance impact upon the use of new connections, and means that when a connection is checked in, it is entirely closed out and is not held in memory. See 切换池实现 for guidelines on how to disable pooling.

参见

连接池

使用具有多处理或 os.fork() 的连接池

注册新方言¶

Registering New Dialects

中文

sqlalchemy.create_engine() 函数调用通过 setuptools 条目定位给定的方言。这些条目可以通过 setup.py 脚本为第三方方言进行设置。例如，要创建一个新的方言 “foodialect://”，步骤如下：

创建一个名为 foodialect 的包。
该包应包含一个模块，该模块包含方言类，通常是 sqlalchemy.engine.default.DefaultDialect 的子类。在本示例中，我们假设它叫做 FooDialect，模块通过 foodialect.dialect 访问。
可以在 setup.cfg 中如下面所示建立条目：

[options.entry_points]
sqlalchemy.dialects =
    foodialect = foodialect.dialect:FooDialect

如果该方言在现有的 SQLAlchemy 支持的数据库上提供对特定 DBAPI 的支持，可以包括数据库限定名称。例如，如果 FooDialect 实际上是一个 MySQL 方言，则可以这样设置条目：

[options.entry_points]
sqlalchemy.dialects
    mysql.foodialect = foodialect.dialect:FooDialect

上述条目将通过 create_engine("mysql+foodialect://") 进行访问。

英文

The create_engine() function call locates the given dialect using setuptools entrypoints. These entry points can be established for third party dialects within the setup.py script. For example, to create a new dialect “foodialect://”, the steps are as follows:

Create a package called foodialect.
The package should have a module containing the dialect class, which is typically a subclass of sqlalchemy.engine.default.DefaultDialect. In this example let’s say it’s called FooDialect and its module is accessed via foodialect.dialect.

The entry point can be established in setup.cfg as follows:

[options.entry_points]
sqlalchemy.dialects =
    foodialect = foodialect.dialect:FooDialect

If the dialect is providing support for a particular DBAPI on top of an existing SQLAlchemy-supported database, the name can be given including a database-qualification. For example, if FooDialect were in fact a MySQL dialect, the entry point could be established like this:

[options.entry_points]
sqlalchemy.dialects
    mysql.foodialect = foodialect.dialect:FooDialect

The above entrypoint would then be accessed as create_engine("mysql+foodialect://").

在进程内注册方言¶

Registering Dialects In-Process

中文

SQLAlchemy 还允许在当前进程中注册一个方言，从而跳过单独安装的步骤。可以使用 register() 函数，如下所示:

from sqlalchemy.dialects import registry

registry.register("mysql.foodialect", "myapp.dialect", "MyMySQLDialect")

上述操作将响应 create_engine("mysql+foodialect://") 并加载来自 myapp.dialect 模块的 MyMySQLDialect 类。

英文

SQLAlchemy also allows a dialect to be registered within the current process, bypassing the need for separate installation. Use the register() function as follows:

from sqlalchemy.dialects import registry

registry.register("mysql.foodialect", "myapp.dialect", "MyMySQLDialect")

The above will respond to create_engine("mysql+foodialect://") and load the MyMySQLDialect class from the myapp.dialect module.

连接 / 引擎API¶

Connection / Engine API

Object Name	Description
Connection	Provides high-level functionality for a wrapped DB-API connection.
CreateEnginePlugin	A set of hooks intended to augment the construction of an `Engine` object based on entrypoint names in a URL.
Engine	Connects a `Pool` and `Dialect` together to provide a source of database connectivity and behavior.
ExceptionContext	Encapsulate information about an error condition in progress.
NestedTransaction	Represent a ‘nested’, or SAVEPOINT transaction.
RootTransaction	Represent the “root” transaction on a `Connection`.
Transaction	Represent a database transaction in progress.
TwoPhaseTransaction	Represent a two-phase transaction.

class sqlalchemy.engine.Connection¶

Provides high-level functionality for a wrapped DB-API connection.

The Connection object is procured by calling the Engine.connect() method of the Engine object, and provides services for execution of SQL statements as well as transaction control.

The Connection object is not thread-safe. While a Connection can be shared among threads using properly synchronized access, it is still possible that the underlying DBAPI connection may not support shared access between threads. Check the DBAPI documentation for details.

The Connection object represents a single DBAPI connection checked out from the connection pool. In this state, the connection pool has no affect upon the connection, including its expiration or timeout state. For the connection pool to properly manage connections, connections should be returned to the connection pool (i.e. connection.close()) whenever the connection is not in use.

Class signature

class sqlalchemy.engine.Connection (sqlalchemy.engine.interfaces.ConnectionEventsTarget, sqlalchemy.inspection.Inspectable)

method sqlalchemy.engine.Connection.__init__(engine: Engine, connection: PoolProxiedConnection | None = None, _has_events: bool | None = None, _allow_revalidate: bool = True, _allow_autobegin: bool = True)¶: Construct a new Connection.

method sqlalchemy.engine.Connection.begin() → RootTransaction¶

Begin a transaction prior to autobegin occurring.

E.g.:

with engine.connect() as conn:
    with conn.begin() as trans:
        conn.execute(table.insert(), {"username": "sandy"})

The returned object is an instance of RootTransaction. This object represents the “scope” of the transaction, which completes when either the Transaction.rollback() or Transaction.commit() method is called; the object also works as a context manager as illustrated above.

The Connection.begin() method begins a transaction that normally will be begun in any case when the connection is first used to execute a statement. The reason this method might be used would be to invoke the ConnectionEvents.begin() event at a specific time, or to organize code within the scope of a connection checkout in terms of context managed blocks, such as:

with engine.connect() as conn:
    with conn.begin():
        conn.execute(...)
        conn.execute(...)

    with conn.begin():
        conn.execute(...)
        conn.execute(...)

The above code is not fundamentally any different in its behavior than the following code which does not use Connection.begin(); the below style is known as “commit as you go” style:

with engine.connect() as conn:
    conn.execute(...)
    conn.execute(...)
    conn.commit()

    conn.execute(...)
    conn.execute(...)
    conn.commit()

From a database point of view, the Connection.begin() method does not emit any SQL or change the state of the underlying DBAPI connection in any way; the Python DBAPI does not have any concept of explicit transaction begin.

参见

使用事务和DBAPI - in the SQLAlchemy 统一教程

Connection.begin_nested() - use a SAVEPOINT

Connection.begin_twophase() - use a two phase /XID transaction

Engine.begin() - context manager available from Engine

method sqlalchemy.engine.Connection.begin_nested() → NestedTransaction¶

Begin a nested transaction (i.e. SAVEPOINT) and return a transaction handle that controls the scope of the SAVEPOINT.

E.g.:

with engine.begin() as connection:
    with connection.begin_nested():
        connection.execute(table.insert(), {"username": "sandy"})

The returned object is an instance of NestedTransaction, which includes transactional methods NestedTransaction.commit() and NestedTransaction.rollback(); for a nested transaction, these methods correspond to the operations “RELEASE SAVEPOINT <name>” and “ROLLBACK TO SAVEPOINT <name>”. The name of the savepoint is local to the NestedTransaction object and is generated automatically. Like any other Transaction, the NestedTransaction may be used as a context manager as illustrated above which will “release” or “rollback” corresponding to if the operation within the block were successful or raised an exception.

Nested transactions require SAVEPOINT support in the underlying database, else the behavior is undefined. SAVEPOINT is commonly used to run operations within a transaction that may fail, while continuing the outer transaction. E.g.:

from sqlalchemy import exc

with engine.begin() as connection:
    trans = connection.begin_nested()
    try:
        connection.execute(table.insert(), {"username": "sandy"})
        trans.commit()
    except exc.IntegrityError:  # catch for duplicate username
        trans.rollback()  # rollback to savepoint

    # outer transaction continues
    connection.execute(...)

If Connection.begin_nested() is called without first calling Connection.begin() or Engine.begin(), the Connection object will “autobegin” the outer transaction first. This outer transaction may be committed using “commit-as-you-go” style, e.g.:

with engine.connect() as connection:  # begin() wasn't called

    with connection.begin_nested():  # will auto-"begin()" first
        connection.execute(...)
    # savepoint is released

    connection.execute(...)

    # explicitly commit outer transaction
    connection.commit()

    # can continue working with connection here

在 2.0 版本发生变更: Connection.begin_nested() will now participate in the connection “autobegin” behavior that is new as of 2.0 / “future” style connections in 1.4.

参见

Connection.begin()

使用 SAVEPOINT - ORM support for SAVEPOINT

method sqlalchemy.engine.Connection.begin_twophase(xid: Any | None = None) → TwoPhaseTransaction¶

Begin a two-phase or XA transaction and return a transaction handle.

The returned object is an instance of TwoPhaseTransaction, which in addition to the methods provided by Transaction, also provides a TwoPhaseTransaction.prepare() method.

参数:: xid¶ – the two phase transaction id. If not supplied, a random id will be generated.

参见

Connection.begin()

method sqlalchemy.engine.Connection.close() → None¶

Close this Connection.

This results in a release of the underlying database resources, that is, the DBAPI connection referenced internally. The DBAPI connection is typically restored back to the connection-holding Pool referenced by the Engine that produced this Connection. Any transactional state present on the DBAPI connection is also unconditionally released via the DBAPI connection’s rollback() method, regardless of any Transaction object that may be outstanding with regards to this Connection.

This has the effect of also calling Connection.rollback() if any transaction is in place.

After Connection.close() is called, the Connection is permanently in a closed state, and will allow no further operations.

attribute sqlalchemy.engine.Connection.closed¶: Return True if this connection is closed.

method sqlalchemy.engine.Connection.commit() → None¶

Commit the transaction that is currently in progress.

This method commits the current transaction if one has been started. If no transaction was started, the method has no effect, assuming the connection is in a non-invalidated state.

A transaction is begun on a Connection automatically whenever a statement is first executed, or when the Connection.begin() method is called.

备注

The Connection.commit() method only acts upon the primary database transaction that is linked to the Connection object. It does not operate upon a SAVEPOINT that would have been invoked from the Connection.begin_nested() method; for control of a SAVEPOINT, call NestedTransaction.commit() on the NestedTransaction that is returned by the Connection.begin_nested() method itself.

attribute sqlalchemy.engine.Connection.connection¶

The underlying DB-API connection managed by this Connection.

This is a SQLAlchemy connection-pool proxied connection which then has the attribute _ConnectionFairy.dbapi_connection that refers to the actual driver connection.

参见

使用驱动程序 SQL 和原始 DBAPI 连接

attribute sqlalchemy.engine.Connection.default_isolation_level¶

The initial-connection time isolation level associated with the Dialect in use.

This value is independent of the Connection.execution_options.isolation_level and Engine.execution_options.isolation_level execution options, and is determined by the Dialect when the first connection is created, by performing a SQL query against the database for the current isolation level before any additional commands have been emitted.

Calling this accessor does not invoke any new SQL queries.

参见

Connection.get_isolation_level() - view current actual isolation level

create_engine.isolation_level - set per Engine isolation level

Connection.execution_options.isolation_level - set per Connection isolation level

method sqlalchemy.engine.Connection.detach() → None¶

Detach the underlying DB-API connection from its connection pool.

E.g.:

with engine.connect() as conn:
    conn.detach()
    conn.execute(text("SET search_path TO schema1, schema2"))

    # work with connection

# connection is fully closed (since we used "with:", can
# also call .close())

This Connection instance will remain usable. When closed (or exited from a context manager context as above), the DB-API connection will be literally closed and not returned to its originating pool.

This method can be used to insulate the rest of an application from a modified state on a connection (such as a transaction isolation level or similar).

method sqlalchemy.engine.Connection.exec_driver_sql(statement: str, parameters: _DBAPIAnyExecuteParams | None = None, execution_options: CoreExecuteOptionsParameter | None = None) → CursorResult[Unpack[TupleAny]]¶

Executes a string SQL statement on the DBAPI cursor directly, without any SQL compilation steps.

This can be used to pass any string directly to the cursor.execute() method of the DBAPI in use.

参数:

statement¶ – The statement str to be executed. Bound parameters must use the underlying DBAPI’s paramstyle, such as “qmark”, “pyformat”, “format”, etc.
parameters¶ – represent bound parameter values to be used in the execution. The format is one of: a dictionary of named parameters, a tuple of positional parameters, or a list containing either dictionaries or tuples for multiple-execute support.

返回:

a CursorResult.

E.g. multiple dictionaries:

conn.exec_driver_sql(
    "INSERT INTO table (id, value) VALUES (%(id)s, %(value)s)",
    [{"id": 1, "value": "v1"}, {"id": 2, "value": "v2"}],
)

Single dictionary:

conn.exec_driver_sql(
    "INSERT INTO table (id, value) VALUES (%(id)s, %(value)s)",
    dict(id=1, value="v1"),
)

Single tuple:

conn.exec_driver_sql(
    "INSERT INTO table (id, value) VALUES (?, ?)", (1, "v1")
)

备注

The Connection.exec_driver_sql() method does not participate in the ConnectionEvents.before_execute() and ConnectionEvents.after_execute() events. To intercept calls to Connection.exec_driver_sql(), use ConnectionEvents.before_cursor_execute() and ConnectionEvents.after_cursor_execute().

参见

PEP 249

method sqlalchemy.engine.Connection.execute(statement: Executable, parameters: _CoreAnyExecuteParams | None = None, *, execution_options: CoreExecuteOptionsParameter | None = None) → CursorResult[Unpack[TupleAny]]¶

Executes a SQL statement construct and returns a CursorResult.

参数:

statement¶ –
The statement to be executed. This is always an object that is in both the ClauseElement and Executable hierarchies, including:
- Select
- Insert, Update, Delete
- TextClause and TextualSelect
- DDL and objects which inherit from ExecutableDDLElement
parameters¶ – parameters which will be bound into the statement. This may be either a dictionary of parameter names to values, or a mutable sequence (e.g. a list) of dictionaries. When a list of dictionaries is passed, the underlying statement execution will make use of the DBAPI cursor.executemany() method. When a single dictionary is passed, the DBAPI cursor.execute() method will be used.
execution_options¶ – optional dictionary of execution options, which will be associated with the statement execution. This dictionary can provide a subset of the options that are accepted by Connection.execution_options().

返回:

a Result object.

method sqlalchemy.engine.Connection.execution_options(**opt: Any) → Connection¶

Set non-SQL options for the connection which take effect during execution.

This method modifies this Connection in-place; the return value is the same Connection object upon which the method is called. Note that this is in contrast to the behavior of the execution_options methods on other objects such as Engine.execution_options() and Executable.execution_options(). The rationale is that many such execution options necessarily modify the state of the base DBAPI connection in any case so there is no feasible means of keeping the effect of such an option localized to a “sub” connection.

在 2.0 版本发生变更: The Connection.execution_options() method, in contrast to other objects with this method, modifies the connection in-place without creating copy of it.

As discussed elsewhere, the Connection.execution_options() method accepts any arbitrary parameters including user defined names. All parameters given are consumable in a number of ways including by using the Connection.get_execution_options() method. See the examples at Executable.execution_options() and Engine.execution_options().

The keywords that are currently recognized by SQLAlchemy itself include all those listed under Executable.execution_options(), as well as others that are specific to Connection.

参数:

compiled_cache¶ –
Available on: Connection, Engine.

A dictionary where Compiled objects will be cached when the Connection compiles a clause expression into a Compiled object. This dictionary will supersede the statement cache that may be configured on the Engine itself. If set to None, caching is disabled, even if the engine has a configured cache size.

Note that the ORM makes use of its own “compiled” caches for some operations, including flush operations. The caching used by the ORM internally supersedes a cache dictionary specified here.
logging_token¶ –
Available on: Connection, Engine, Executable.

Adds the specified string token surrounded by brackets in log messages logged by the connection, i.e. the logging that’s enabled either via the create_engine.echo flag or via the logging.getLogger("sqlalchemy.engine") logger. This allows a per-connection or per-sub-engine token to be available which is useful for debugging concurrent connection scenarios.

在 1.4.0b2 版本加入.

参见

设置每个连接/子引擎令牌 - usage example

create_engine.logging_name - adds a name to the name used by the Python logger object itself.
isolation_level¶ –
Available on: Connection, Engine.

Set the transaction isolation level for the lifespan of this Connection object. Valid values include those string values accepted by the create_engine.isolation_level parameter passed to create_engine(). These levels are semi-database specific; see individual dialect documentation for valid levels.

The isolation level option applies the isolation level by emitting statements on the DBAPI connection, and necessarily affects the original Connection object overall. The isolation level will remain at the given setting until explicitly changed, or when the DBAPI connection itself is released to the connection pool, i.e. the Connection.close() method is called, at which time an event handler will emit additional statements on the DBAPI connection in order to revert the isolation level change.

备注

The isolation_level execution option may only be established before the Connection.begin() method is called, as well as before any SQL statements are emitted which would otherwise trigger “autobegin”, or directly after a call to Connection.commit() or Connection.rollback(). A database cannot change the isolation level on a transaction in progress.

备注

The isolation_level execution option is implicitly reset if the Connection is invalidated, e.g. via the Connection.invalidate() method, or if a disconnection error occurs. The new connection produced after the invalidation will not have the selected isolation level re-applied to it automatically.

参见

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

Connection.get_isolation_level() - view current actual level
no_parameters¶ –
Available on: Connection, Executable.

When True, if the final parameter list or dictionary is totally empty, will invoke the statement on the cursor as cursor.execute(statement), not passing the parameter collection at all. Some DBAPIs such as psycopg2 and mysql-python consider percent signs as significant only when parameters are present; this option allows code to generate SQL containing percent signs (and possibly other characters) that is neutral regarding whether it’s executed by the DBAPI or piped into a script that’s later invoked by command line tools.
stream_results¶ –
Available on: Connection, Executable.

Indicate to the dialect that results should be “streamed” and not pre-buffered, if possible. For backends such as PostgreSQL, MySQL and MariaDB, this indicates the use of a “server side cursor” as opposed to a client side cursor. Other backends such as that of Oracle Database may already use server side cursors by default.

The usage of Connection.execution_options.stream_results is usually combined with setting a fixed number of rows to to be fetched in batches, to allow for efficient iteration of database rows while at the same time not loading all result rows into memory at once; this can be configured on a Result object using the Result.yield_per() method, after execution has returned a new Result. If Result.yield_per() is not used, the Connection.execution_options.stream_results mode of operation will instead use a dynamically sized buffer which buffers sets of rows at a time, growing on each batch based on a fixed growth size up until a limit which may be configured using the Connection.execution_options.max_row_buffer parameter.

When using the ORM to fetch ORM mapped objects from a result, Result.yield_per() should always be used with Connection.execution_options.stream_results, so that the ORM does not fetch all rows into new ORM objects at once.

For typical use, the Connection.execution_options.yield_per execution option should be preferred, which sets up both Connection.execution_options.stream_results and Result.yield_per() at once. This option is supported both at a core level by Connection as well as by the ORM Session; the latter is described at 使用 Yield Per 获取大量结果集.

参见

使用服务器端游标（又称流结果） - background on Connection.execution_options.stream_results

Connection.execution_options.max_row_buffer

Connection.execution_options.yield_per

使用 Yield Per 获取大量结果集 - in the ORM 查询指南 describing the ORM version of yield_per
max_row_buffer¶ –
Available on: Connection, Executable. Sets a maximum buffer size to use when the Connection.execution_options.stream_results execution option is used on a backend that supports server side cursors. The default value if not specified is 1000.

参见

Connection.execution_options.stream_results

使用服务器端游标（又称流结果）
yield_per¶ –
Available on: Connection, Executable. Integer value applied which will set the Connection.execution_options.stream_results execution option and invoke Result.yield_per() automatically at once. Allows equivalent functionality as is present when using this parameter with the ORM.

在 1.4.40 版本加入.

参见

使用服务器端游标（又称流结果） - background and examples on using server side cursors with Core.

使用 Yield Per 获取大量结果集 - in the ORM 查询指南 describing the ORM version of yield_per
insertmanyvalues_page_size¶ –
Available on: Connection, Engine. Number of rows to format into an INSERT statement when the statement uses “insertmanyvalues” mode, which is a paged form of bulk insert that is used for many backends when using executemany execution typically in conjunction with RETURNING. Defaults to 1000. May also be modified on a per-engine basis using the create_engine.insertmanyvalues_page_size parameter.

在 2.0 版本加入.

参见

INSERT 语句的“插入多个值”行为
schema_translate_map¶ –
Available on: Connection, Engine, Executable.

A dictionary mapping schema names to schema names, that will be applied to the Table.schema element of each Table encountered when SQL or DDL expression elements are compiled into strings; the resulting schema name will be converted based on presence in the map of the original name.

参见

模式名称的转换
preserve_rowcount¶ –
Boolean; when True, the cursor.rowcount attribute will be unconditionally memoized within the result and made available via the CursorResult.rowcount attribute. Normally, this attribute is only preserved for UPDATE and DELETE statements. Using this option, the DBAPIs rowcount value can be accessed for other kinds of statements such as INSERT and SELECT, to the degree that the DBAPI supports these statements. See CursorResult.rowcount for notes regarding the behavior of this attribute.

在 2.0.28 版本加入.

参见

Engine.execution_options()

Executable.execution_options()

Connection.get_execution_options()

ORM 执行选项 - documentation on all ORM-specific execution options

参数:

driver_column_names¶ –

When True, the returned CursorResult will use the column names as written in cursor.description to set up the keys for the result set, including the names of columns for the Row object as well as the dictionary keys when using Row._mapping. On backends that use “name normalization” such as Oracle Database to correct for lower case names being converted to all uppercase, this behavior is turned off and the raw UPPERCASE names in cursor.description will be present.

在 2.1 版本加入.

method sqlalchemy.engine.Connection.get_execution_options() → _ExecuteOptions¶: Get the non-SQL options which will take effect during execution.

参见

Connection.execution_options()

method sqlalchemy.engine.Connection.get_isolation_level() → Literal['SERIALIZABLE', 'REPEATABLE READ', 'READ COMMITTED', 'READ UNCOMMITTED', 'AUTOCOMMIT']¶

Return the current actual isolation level that’s present on the database within the scope of this connection.

This attribute will perform a live SQL operation against the database in order to procure the current isolation level, so the value returned is the actual level on the underlying DBAPI connection regardless of how this state was set. This will be one of the four actual isolation modes READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE. It will not include the AUTOCOMMIT isolation level setting. Third party dialects may also feature additional isolation level settings.

备注

This method will not report on the AUTOCOMMIT isolation level, which is a separate dbapi setting that’s independent of actual isolation level. When AUTOCOMMIT is in use, the database connection still has a “traditional” isolation mode in effect, that is typically one of the four values READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE.

Compare to the Connection.default_isolation_level accessor which returns the isolation level that is present on the database at initial connection time.

参见

Connection.default_isolation_level - view default level

create_engine.isolation_level - set per Engine isolation level

Connection.execution_options.isolation_level - set per Connection isolation level

method sqlalchemy.engine.Connection.get_nested_transaction() → NestedTransaction | None¶: Return the current nested transaction in progress, if any.

在 1.4 版本加入.

method sqlalchemy.engine.Connection.get_transaction() → RootTransaction | None¶: Return the current root transaction in progress, if any.

在 1.4 版本加入.

method sqlalchemy.engine.Connection.in_nested_transaction() → bool¶: Return True if a transaction is in progress.

method sqlalchemy.engine.Connection.in_transaction() → bool¶: Return True if a transaction is in progress.

attribute sqlalchemy.engine.Connection.info¶

Info dictionary associated with the underlying DBAPI connection referred to by this Connection, allowing user-defined data to be associated with the connection.

The data here will follow along with the DBAPI connection including after it is returned to the connection pool and used again in subsequent instances of Connection.

method sqlalchemy.engine.Connection.invalidate(exception: BaseException | None = None) → None¶

Invalidate the underlying DBAPI connection associated with this Connection.

An attempt will be made to close the underlying DBAPI connection immediately; however if this operation fails, the error is logged but not raised. The connection is then discarded whether or not close() succeeded.

Upon the next use (where “use” typically means using the Connection.execute() method or similar), this Connection will attempt to procure a new DBAPI connection using the services of the Pool as a source of connectivity (e.g. a “reconnection”).

If a transaction was in progress (e.g. the Connection.begin() method has been called) when Connection.invalidate() method is called, at the DBAPI level all state associated with this transaction is lost, as the DBAPI connection is closed. The Connection will not allow a reconnection to proceed until the Transaction object is ended, by calling the Transaction.rollback() method; until that point, any attempt at continuing to use the Connection will raise an InvalidRequestError. This is to prevent applications from accidentally continuing an ongoing transactional operations despite the fact that the transaction has been lost due to an invalidation.

The Connection.invalidate() method, just like auto-invalidation, will at the connection pool level invoke the PoolEvents.invalidate() event.

参数:: exception¶ – an optional Exception instance that’s the reason for the invalidation. is passed along to event handlers and logging functions.

参见

更多关于失效的信息

attribute sqlalchemy.engine.Connection.invalidated¶

Return True if this connection was invalidated.

This does not indicate whether or not the connection was invalidated at the pool level, however

method sqlalchemy.engine.Connection.rollback() → None¶

Roll back the transaction that is currently in progress.

This method rolls back the current transaction if one has been started. If no transaction was started, the method has no effect. If a transaction was started and the connection is in an invalidated state, the transaction is cleared using this method.

A transaction is begun on a Connection automatically whenever a statement is first executed, or when the Connection.begin() method is called.

备注

The Connection.rollback() method only acts upon the primary database transaction that is linked to the Connection object. It does not operate upon a SAVEPOINT that would have been invoked from the Connection.begin_nested() method; for control of a SAVEPOINT, call NestedTransaction.rollback() on the NestedTransaction that is returned by the Connection.begin_nested() method itself.

method sqlalchemy.engine.Connection.scalar(statement: Executable, parameters: _CoreSingleExecuteParams | None = None, *, execution_options: CoreExecuteOptionsParameter | None = None) → Any¶

Executes a SQL statement construct and returns a scalar object.

This method is shorthand for invoking the Result.scalar() method after invoking the Connection.execute() method. Parameters are equivalent.

返回:: a scalar Python value representing the first column of the first row returned.

method sqlalchemy.engine.Connection.scalars(statement: Executable, parameters: _CoreAnyExecuteParams | None = None, *, execution_options: CoreExecuteOptionsParameter | None = None) → ScalarResult[Any]¶

Executes and returns a scalar result set, which yields scalar values from the first column of each row.

This method is equivalent to calling Connection.execute() to receive a Result object, then invoking the Result.scalars() method to produce a ScalarResult instance.

返回:: a ScalarResult

在 1.4.24 版本加入.

method sqlalchemy.engine.Connection.schema_for_object(obj: HasSchemaAttr) → str | None¶: Return the schema name for the given schema item taking into account current schema translate map.

class sqlalchemy.engine.CreateEnginePlugin¶

A set of hooks intended to augment the construction of an Engine object based on entrypoint names in a URL.

The purpose of CreateEnginePlugin is to allow third-party systems to apply engine, pool and dialect level event listeners without the need for the target application to be modified; instead, the plugin names can be added to the database URL. Target applications for CreateEnginePlugin include:

connection and SQL performance tools, e.g. which use events to track number of checkouts and/or time spent with statements
connectivity plugins such as proxies

A rudimentary CreateEnginePlugin that attaches a logger to an Engine object might look like:

import logging

from sqlalchemy.engine import CreateEnginePlugin
from sqlalchemy import event


class LogCursorEventsPlugin(CreateEnginePlugin):
    def __init__(self, url, kwargs):
        # consume the parameter "log_cursor_logging_name" from the
        # URL query
        logging_name = url.query.get(
            "log_cursor_logging_name", "log_cursor"
        )

        self.log = logging.getLogger(logging_name)

    def update_url(self, url):
        "update the URL to one that no longer includes our parameters"
        return url.difference_update_query(["log_cursor_logging_name"])

    def engine_created(self, engine):
        "attach an event listener after the new Engine is constructed"
        event.listen(engine, "before_cursor_execute", self._log_event)

    def _log_event(
        self,
        conn,
        cursor,
        statement,
        parameters,
        context,
        executemany,
    ):

        self.log.info("Plugin logged cursor event: %s", statement)

Plugins are registered using entry points in a similar way as that of dialects:

entry_points = {
    "sqlalchemy.plugins": [
        "log_cursor_plugin = myapp.plugins:LogCursorEventsPlugin"
    ]
}

A plugin that uses the above names would be invoked from a database URL as in:

from sqlalchemy import create_engine

engine = create_engine(
    "mysql+pymysql://scott:tiger@localhost/test?"
    "plugin=log_cursor_plugin&log_cursor_logging_name=mylogger"
)

The plugin URL parameter supports multiple instances, so that a URL may specify multiple plugins; they are loaded in the order stated in the URL:

engine = create_engine(
    "mysql+pymysql://scott:tiger@localhost/test?"
    "plugin=plugin_one&plugin=plugin_twp&plugin=plugin_three"
)

The plugin names may also be passed directly to create_engine() using the create_engine.plugins argument:

engine = create_engine(
    "mysql+pymysql://scott:tiger@localhost/test", plugins=["myplugin"]
)

A plugin may consume plugin-specific arguments from the URL object as well as the kwargs dictionary, which is the dictionary of arguments passed to the create_engine() call. “Consuming” these arguments includes that they must be removed when the plugin initializes, so that the arguments are not passed along to the Dialect constructor, where they will raise an ArgumentError because they are not known by the dialect.

As of version 1.4 of SQLAlchemy, arguments should continue to be consumed from the kwargs dictionary directly, by removing the values with a method such as dict.pop. Arguments from the URL object should be consumed by implementing the CreateEnginePlugin.update_url() method, returning a new copy of the URL with plugin-specific parameters removed:

class MyPlugin(CreateEnginePlugin):
    def __init__(self, url, kwargs):
        self.my_argument_one = url.query["my_argument_one"]
        self.my_argument_two = url.query["my_argument_two"]
        self.my_argument_three = kwargs.pop("my_argument_three", None)

    def update_url(self, url):
        return url.difference_update_query(
            ["my_argument_one", "my_argument_two"]
        )

Arguments like those illustrated above would be consumed from a create_engine() call such as:

from sqlalchemy import create_engine

engine = create_engine(
    "mysql+pymysql://scott:tiger@localhost/test?"
    "plugin=myplugin&my_argument_one=foo&my_argument_two=bar",
    my_argument_three="bat",
)

在 1.4 版本发生变更: The URL object is now immutable; a CreateEnginePlugin that needs to alter the URL should implement the newly added CreateEnginePlugin.update_url() method, which is invoked after the plugin is constructed.

For migration, construct the plugin in the following way, checking for the existence of the CreateEnginePlugin.update_url() method to detect which version is running:

class MyPlugin(CreateEnginePlugin):
    def __init__(self, url, kwargs):
        if hasattr(CreateEnginePlugin, "update_url"):
            # detect the 1.4 API
            self.my_argument_one = url.query["my_argument_one"]
            self.my_argument_two = url.query["my_argument_two"]
        else:
            # detect the 1.3 and earlier API - mutate the
            # URL directly
            self.my_argument_one = url.query.pop("my_argument_one")
            self.my_argument_two = url.query.pop("my_argument_two")

        self.my_argument_three = kwargs.pop("my_argument_three", None)

    def update_url(self, url):
        # this method is only called in the 1.4 version
        return url.difference_update_query(
            ["my_argument_one", "my_argument_two"]
        )

参见

The URL object is now immutable - overview of the URL change which also includes notes regarding CreateEnginePlugin.

Members

__init__(), engine_created(), handle_dialect_kwargs(), handle_pool_kwargs(), update_url()

When the engine creation process completes and produces the Engine object, it is again passed to the plugin via the CreateEnginePlugin.engine_created() hook. In this hook, additional changes can be made to the engine, most typically involving setup of events (e.g. those defined in Core 事件).

method sqlalchemy.engine.CreateEnginePlugin.__init__(url: URL, kwargs: Dict[str, Any])¶

Construct a new CreateEnginePlugin.

The plugin object is instantiated individually for each call to create_engine(). A single Engine will be passed to the CreateEnginePlugin.engine_created() method corresponding to this URL.

参数:

url¶ –
the URL object. The plugin may inspect the URL for arguments. Arguments used by the plugin should be removed, by returning an updated URL from the CreateEnginePlugin.update_url() method.

在 1.4 版本发生变更: The URL object is now immutable, so a CreateEnginePlugin that needs to alter the URL object should implement the CreateEnginePlugin.update_url() method.
kwargs¶ – The keyword arguments passed to create_engine().

method sqlalchemy.engine.CreateEnginePlugin.engine_created(engine: Engine) → None¶

Receive the Engine object when it is fully constructed.

The plugin may make additional changes to the engine, such as registering engine or connection pool events.

method sqlalchemy.engine.CreateEnginePlugin.handle_dialect_kwargs(dialect_cls: Type[Dialect], dialect_args: Dict[str, Any]) → None¶: parse and modify dialect kwargs

method sqlalchemy.engine.CreateEnginePlugin.handle_pool_kwargs(pool_cls: Type[Pool], pool_args: Dict[str, Any]) → None¶: parse and modify pool kwargs

method sqlalchemy.engine.CreateEnginePlugin.update_url(url: URL) → URL¶

Update the URL.

A new URL should be returned. This method is typically used to consume configuration arguments from the URL which must be removed, as they will not be recognized by the dialect. The URL.difference_update_query() method is available to remove these arguments. See the docstring at CreateEnginePlugin for an example.

在 1.4 版本加入.

class sqlalchemy.engine.Engine¶

Connects a Pool and Dialect together to provide a source of database connectivity and behavior.

An Engine object is instantiated publicly using the create_engine() function.

参见

Engine 配置

使用Engines和Connection

Members

begin(), clear_compiled_cache(), connect(), dispose(), driver, engine, execution_options(), get_execution_options(), name, raw_connection(), update_execution_options()

Class signature

class sqlalchemy.engine.Engine (sqlalchemy.engine.interfaces.ConnectionEventsTarget, sqlalchemy.log.Identified, sqlalchemy.inspection.Inspectable)

method sqlalchemy.engine.Engine.begin() → Iterator[Connection]¶

Return a context manager delivering a Connection with a Transaction established.

E.g.:

with engine.begin() as conn:
    conn.execute(text("insert into table (x, y, z) values (1, 2, 3)"))
    conn.execute(text("my_special_procedure(5)"))

Upon successful operation, the Transaction is committed. If an error is raised, the Transaction is rolled back.

参见

Engine.connect() - procure a Connection from an Engine.

Connection.begin() - start a Transaction for a particular Connection.

method sqlalchemy.engine.Engine.clear_compiled_cache() → None¶

Clear the compiled cache associated with the dialect.

This applies only to the built-in cache that is established via the create_engine.query_cache_size parameter. It will not impact any dictionary caches that were passed via the Connection.execution_options.compiled_cache parameter.

在 1.4 版本加入.

method sqlalchemy.engine.Engine.connect() → Connection¶

Return a new Connection object.

The Connection acts as a Python context manager, so the typical use of this method looks like:

with engine.connect() as connection:
    connection.execute(text("insert into table values ('foo')"))
    connection.commit()

Where above, after the block is completed, the connection is “closed” and its underlying DBAPI resources are returned to the connection pool. This also has the effect of rolling back any transaction that was explicitly begun or was begun via autobegin, and will emit the ConnectionEvents.rollback() event if one was started and is still in progress.

参见

Engine.begin()

method sqlalchemy.engine.Engine.dispose(close: bool = True) → None¶

Dispose of the connection pool used by this Engine.

A new connection pool is created immediately after the old one has been disposed. The previous connection pool is disposed either actively, by closing out all currently checked-in connections in that pool, or passively, by losing references to it but otherwise not closing any connections. The latter strategy is more appropriate for an initializer in a forked Python process.

参数:

close¶ –

if left at its default of True, has the effect of fully closing all currently checked in database connections. Connections that are still checked out will not be closed, however they will no longer be associated with this Engine, so when they are closed individually, eventually the Pool which they are associated with will be garbage collected and they will be closed out fully, if not already closed on checkin.

If set to False, the previous connection pool is de-referenced, and otherwise not touched in any way.

在 1.4.33 版本加入: Added the Engine.dispose.close parameter to allow the replacement of a connection pool in a child process without interfering with the connections used by the parent process.

参见

引擎处置

使用具有多处理或 os.fork() 的连接池

attribute sqlalchemy.engine.Engine.driver¶: Driver name of the Dialect in use by this Engine.

attribute sqlalchemy.engine.Engine.engine¶

Returns this Engine.

Used for legacy schemes that accept Connection / Engine objects within the same variable.

method sqlalchemy.engine.Engine.execution_options(**opt: Any) → OptionEngine¶

Return a new Engine that will provide Connection objects with the given execution options.

The returned Engine remains related to the original Engine in that it shares the same connection pool and other state:

The Pool used by the new Engine is the same instance. The Engine.dispose() method will replace the connection pool instance for the parent engine as well as this one.
Event listeners are “cascaded” - meaning, the new Engine inherits the events of the parent, and new events can be associated with the new Engine individually.
The logging configuration and logging_name is copied from the parent Engine.

The intent of the Engine.execution_options() method is to implement schemes where multiple Engine objects refer to the same connection pool, but are differentiated by options that affect some execution-level behavior for each engine. One such example is breaking into separate “reader” and “writer” Engine instances, where one Engine has a lower isolation level setting configured or is even transaction-disabled using “autocommit”. An example of this configuration is at 为单个引擎维护多个隔离级别.

Another example is one that uses a custom option shard_id which is consumed by an event to change the current schema on a database connection:

from sqlalchemy import event
from sqlalchemy.engine import Engine

primary_engine = create_engine("mysql+mysqldb://")
shard1 = primary_engine.execution_options(shard_id="shard1")
shard2 = primary_engine.execution_options(shard_id="shard2")

shards = {"default": "base", "shard_1": "db1", "shard_2": "db2"}


@event.listens_for(Engine, "before_cursor_execute")
def _switch_shard(conn, cursor, stmt, params, context, executemany):
    shard_id = conn.get_execution_options().get("shard_id", "default")
    current_shard = conn.info.get("current_shard", None)

    if current_shard != shard_id:
        cursor.execute("use %s" % shards[shard_id])
        conn.info["current_shard"] = shard_id

The above recipe illustrates two Engine objects that will each serve as factories for Connection objects that have pre-established “shard_id” execution options present. A ConnectionEvents.before_cursor_execute() event handler then interprets this execution option to emit a MySQL use statement to switch databases before a statement execution, while at the same time keeping track of which database we’ve established using the Connection.info dictionary.

参见

Connection.execution_options() - update execution options on a Connection object.

Engine.update_execution_options() - update the execution options for a given Engine in place.

Engine.get_execution_options()

method sqlalchemy.engine.Engine.get_execution_options() → _ExecuteOptions¶: Get the non-SQL options which will take effect during execution.

参见

Engine.execution_options()

attribute sqlalchemy.engine.Engine.name¶: String name of the Dialect in use by this Engine.

method sqlalchemy.engine.Engine.raw_connection() → PoolProxiedConnection¶

Return a “raw” DBAPI connection from the connection pool.

The returned object is a proxied version of the DBAPI connection object used by the underlying driver in use. The object will have all the same behavior as the real DBAPI connection, except that its close() method will result in the connection being returned to the pool, rather than being closed for real.

This method provides direct DBAPI connection access for special situations when the API provided by Connection is not needed. When a Connection object is already present, the DBAPI connection is available using the Connection.connection accessor.

参见

使用驱动程序 SQL 和原始 DBAPI 连接

method sqlalchemy.engine.Engine.update_execution_options(**opt: Any) → None¶

Update the default execution_options dictionary of this Engine.

The given keys/values in **opt are added to the default execution options that will be used for all connections. The initial contents of this dictionary can be sent via the execution_options parameter to create_engine().

参见

Connection.execution_options()

Engine.execution_options()

class sqlalchemy.engine.ExceptionContext¶

Encapsulate information about an error condition in progress.

Members

chained_exception, connection, cursor, dialect, engine, execution_context, invalidate_pool_on_disconnect, is_disconnect, is_pre_ping, original_exception, parameters, sqlalchemy_exception, statement

This object exists solely to be passed to the DialectEvents.handle_error() event, supporting an interface that can be extended without backwards-incompatibility.

attribute sqlalchemy.engine.ExceptionContext.chained_exception: BaseException | None¶

The exception that was returned by the previous handler in the exception chain, if any.

If present, this exception will be the one ultimately raised by SQLAlchemy unless a subsequent handler replaces it.

May be None.

attribute sqlalchemy.engine.ExceptionContext.connection: Connection | None¶

The Connection in use during the exception.

This member is present, except in the case of a failure when first connecting.

参见

ExceptionContext.engine

attribute sqlalchemy.engine.ExceptionContext.cursor: DBAPICursor | None¶

The DBAPI cursor object.

May be None.

attribute sqlalchemy.engine.ExceptionContext.dialect: Dialect¶

The Dialect in use.

This member is present for all invocations of the event hook.

在 2.0 版本加入.

attribute sqlalchemy.engine.ExceptionContext.engine: Engine | None¶

The Engine in use during the exception.

This member is present in all cases except for when handling an error within the connection pool “pre-ping” process.

attribute sqlalchemy.engine.ExceptionContext.execution_context: ExecutionContext | None¶

The ExecutionContext corresponding to the execution operation in progress.

This is present for statement execution operations, but not for operations such as transaction begin/end. It also is not present when the exception was raised before the ExecutionContext could be constructed.

Note that the ExceptionContext.statement and ExceptionContext.parameters members may represent a different value than that of the ExecutionContext, potentially in the case where a ConnectionEvents.before_cursor_execute() event or similar modified the statement/parameters to be sent.

May be None.

attribute sqlalchemy.engine.ExceptionContext.invalidate_pool_on_disconnect: bool¶

Represent whether all connections in the pool should be invalidated when a “disconnect” condition is in effect.

Setting this flag to False within the scope of the DialectEvents.handle_error() event will have the effect such that the full collection of connections in the pool will not be invalidated during a disconnect; only the current connection that is the subject of the error will actually be invalidated.

The purpose of this flag is for custom disconnect-handling schemes where the invalidation of other connections in the pool is to be performed based on other conditions, or even on a per-connection basis.

attribute sqlalchemy.engine.ExceptionContext.is_disconnect: bool¶

Represent whether the exception as occurred represents a “disconnect” condition.

This flag will always be True or False within the scope of the DialectEvents.handle_error() handler.

SQLAlchemy will defer to this flag in order to determine whether or not the connection should be invalidated subsequently. That is, by assigning to this flag, a “disconnect” event which then results in a connection and pool invalidation can be invoked or prevented by changing this flag.

备注

The pool “pre_ping” handler enabled using the create_engine.pool_pre_ping parameter does not consult this event before deciding if the “ping” returned false, as opposed to receiving an unhandled error. For this use case, the legacy recipe based on engine_connect() may be used. A future API allow more comprehensive customization of the “disconnect” detection mechanism across all functions.

attribute sqlalchemy.engine.ExceptionContext.is_pre_ping: bool¶: Indicates if this error is occurring within the “pre-ping” step performed when create_engine.pool_pre_ping is set to True. In this mode, the ExceptionContext.engine attribute will be None. The dialect in use is accessible via the ExceptionContext.dialect attribute.

在 2.0.5 版本加入.

attribute sqlalchemy.engine.ExceptionContext.original_exception: BaseException¶

The exception object which was caught.

This member is always present.

attribute sqlalchemy.engine.ExceptionContext.parameters: _DBAPIAnyExecuteParams | None¶

Parameter collection that was emitted directly to the DBAPI.

May be None.

attribute sqlalchemy.engine.ExceptionContext.sqlalchemy_exception: StatementError | None¶

The sqlalchemy.exc.StatementError which wraps the original, and will be raised if exception handling is not circumvented by the event.

May be None, as not all exception types are wrapped by SQLAlchemy. For DBAPI-level exceptions that subclass the dbapi’s Error class, this field will always be present.

attribute sqlalchemy.engine.ExceptionContext.statement: str | None¶

String SQL statement that was emitted directly to the DBAPI.

May be None.

class sqlalchemy.engine.NestedTransaction¶

Represent a ‘nested’, or SAVEPOINT transaction.

The NestedTransaction object is created by calling the Connection.begin_nested() method of Connection.

When using NestedTransaction, the semantics of “begin” / “commit” / “rollback” are as follows:

the “begin” operation corresponds to the “BEGIN SAVEPOINT” command, where the savepoint is given an explicit name that is part of the state of this object.
The NestedTransaction.commit() method corresponds to a “RELEASE SAVEPOINT” operation, using the savepoint identifier associated with this NestedTransaction.
The NestedTransaction.rollback() method corresponds to a “ROLLBACK TO SAVEPOINT” operation, using the savepoint identifier associated with this NestedTransaction.

The rationale for mimicking the semantics of an outer transaction in terms of savepoints so that code may deal with a “savepoint” transaction and an “outer” transaction in an agnostic way.

参见

使用 SAVEPOINT - ORM version of the SAVEPOINT API.

Members

close(), commit(), rollback()

Class signature

class sqlalchemy.engine.NestedTransaction (sqlalchemy.engine.Transaction)

method sqlalchemy.engine.NestedTransaction.close() → None¶

inherited from the Transaction.close() method of Transaction

Close this Transaction.

If this transaction is the base transaction in a begin/commit nesting, the transaction will rollback(). Otherwise, the method returns.

This is used to cancel a Transaction without affecting the scope of an enclosing transaction.

method sqlalchemy.engine.NestedTransaction.commit() → None¶

inherited from the Transaction.commit() method of Transaction

Commit this Transaction.

The implementation of this may vary based on the type of transaction in use:

For a simple database transaction (e.g. RootTransaction), it corresponds to a COMMIT.
For a NestedTransaction, it corresponds to a “RELEASE SAVEPOINT” operation.
For a TwoPhaseTransaction, DBAPI-specific methods for two phase transactions may be used.

method sqlalchemy.engine.NestedTransaction.rollback() → None¶

inherited from the Transaction.rollback() method of Transaction

Roll back this Transaction.

The implementation of this may vary based on the type of transaction in use:

For a simple database transaction (e.g. RootTransaction), it corresponds to a ROLLBACK.
For a NestedTransaction, it corresponds to a “ROLLBACK TO SAVEPOINT” operation.
For a TwoPhaseTransaction, DBAPI-specific methods for two phase transactions may be used.

class sqlalchemy.engine.RootTransaction¶

Represent the “root” transaction on a Connection.

This corresponds to the current “BEGIN/COMMIT/ROLLBACK” that’s occurring for the Connection. The RootTransaction is created by calling upon the Connection.begin() method, and remains associated with the Connection throughout its active span. The current RootTransaction in use is accessible via the Connection.get_transaction method of Connection.

In 2.0 style use, the Connection also employs “autobegin” behavior that will create a new RootTransaction whenever a connection in a non-transactional state is used to emit commands on the DBAPI connection. The scope of the RootTransaction in 2.0 style use can be controlled using the Connection.commit() and Connection.rollback() methods.

Members

close(), commit(), rollback()

Class signature

class sqlalchemy.engine.RootTransaction (sqlalchemy.engine.Transaction)

method sqlalchemy.engine.RootTransaction.close() → None¶

inherited from the Transaction.close() method of Transaction

Close this Transaction.

If this transaction is the base transaction in a begin/commit nesting, the transaction will rollback(). Otherwise, the method returns.

This is used to cancel a Transaction without affecting the scope of an enclosing transaction.

method sqlalchemy.engine.RootTransaction.commit() → None¶

inherited from the Transaction.commit() method of Transaction

Commit this Transaction.

The implementation of this may vary based on the type of transaction in use:

For a simple database transaction (e.g. RootTransaction), it corresponds to a COMMIT.
For a NestedTransaction, it corresponds to a “RELEASE SAVEPOINT” operation.
For a TwoPhaseTransaction, DBAPI-specific methods for two phase transactions may be used.

method sqlalchemy.engine.RootTransaction.rollback() → None¶

inherited from the Transaction.rollback() method of Transaction

Roll back this Transaction.

The implementation of this may vary based on the type of transaction in use:

For a simple database transaction (e.g. RootTransaction), it corresponds to a ROLLBACK.
For a NestedTransaction, it corresponds to a “ROLLBACK TO SAVEPOINT” operation.
For a TwoPhaseTransaction, DBAPI-specific methods for two phase transactions may be used.

class sqlalchemy.engine.Transaction¶

Represent a database transaction in progress.

The Transaction object is procured by calling the Connection.begin() method of Connection:

from sqlalchemy import create_engine

engine = create_engine("postgresql+psycopg2://scott:tiger@localhost/test")
connection = engine.connect()
trans = connection.begin()
connection.execute(text("insert into x (a, b) values (1, 2)"))
trans.commit()

The object provides rollback() and commit() methods in order to control transaction boundaries. It also implements a context manager interface so that the Python with statement can be used with the Connection.begin() method:

with connection.begin():
    connection.execute(text("insert into x (a, b) values (1, 2)"))

The Transaction object is not threadsafe.

Members

close(), commit(), rollback()

参见

Connection.begin()

Connection.begin_twophase()

Connection.begin_nested()

Class signature

class sqlalchemy.engine.Transaction (sqlalchemy.engine.util.TransactionalContext)

method sqlalchemy.engine.Transaction.close() → None¶

Close this Transaction.

If this transaction is the base transaction in a begin/commit nesting, the transaction will rollback(). Otherwise, the method returns.

This is used to cancel a Transaction without affecting the scope of an enclosing transaction.

method sqlalchemy.engine.Transaction.commit() → None¶

Commit this Transaction.

The implementation of this may vary based on the type of transaction in use:

For a simple database transaction (e.g. RootTransaction), it corresponds to a COMMIT.
For a NestedTransaction, it corresponds to a “RELEASE SAVEPOINT” operation.
For a TwoPhaseTransaction, DBAPI-specific methods for two phase transactions may be used.

method sqlalchemy.engine.Transaction.rollback() → None¶

Roll back this Transaction.

The implementation of this may vary based on the type of transaction in use:

For a simple database transaction (e.g. RootTransaction), it corresponds to a ROLLBACK.
For a NestedTransaction, it corresponds to a “ROLLBACK TO SAVEPOINT” operation.
For a TwoPhaseTransaction, DBAPI-specific methods for two phase transactions may be used.

class sqlalchemy.engine.TwoPhaseTransaction¶

Represent a two-phase transaction.

A new TwoPhaseTransaction object may be procured using the Connection.begin_twophase() method.

The interface is the same as that of Transaction with the addition of the prepare() method.

Members

close(), commit(), prepare(), rollback()

Class signature

class sqlalchemy.engine.TwoPhaseTransaction (sqlalchemy.engine.RootTransaction)

method sqlalchemy.engine.TwoPhaseTransaction.close() → None¶

inherited from the Transaction.close() method of Transaction

Close this Transaction.

If this transaction is the base transaction in a begin/commit nesting, the transaction will rollback(). Otherwise, the method returns.

This is used to cancel a Transaction without affecting the scope of an enclosing transaction.

method sqlalchemy.engine.TwoPhaseTransaction.commit() → None¶

inherited from the Transaction.commit() method of Transaction

Commit this Transaction.

The implementation of this may vary based on the type of transaction in use:

For a simple database transaction (e.g. RootTransaction), it corresponds to a COMMIT.
For a NestedTransaction, it corresponds to a “RELEASE SAVEPOINT” operation.
For a TwoPhaseTransaction, DBAPI-specific methods for two phase transactions may be used.

method sqlalchemy.engine.TwoPhaseTransaction.prepare() → None¶

Prepare this TwoPhaseTransaction.

After a PREPARE, the transaction can be committed.

method sqlalchemy.engine.TwoPhaseTransaction.rollback() → None¶

inherited from the Transaction.rollback() method of Transaction

Roll back this Transaction.

The implementation of this may vary based on the type of transaction in use:

For a simple database transaction (e.g. RootTransaction), it corresponds to a ROLLBACK.
For a NestedTransaction, it corresponds to a “ROLLBACK TO SAVEPOINT” operation.
For a TwoPhaseTransaction, DBAPI-specific methods for two phase transactions may be used.

结果集 API¶

Result Set API

Object Name	Description
ChunkedIteratorResult	An `IteratorResult` that works from an iterator-producing callable.
CursorResult	A Result that is representing state from a DBAPI cursor.
FilterResult	A wrapper for a `Result` that returns objects other than `Row` objects, such as dictionaries or scalar objects.
FrozenResult	Represents a `Result` object in a “frozen” state suitable for caching.
IteratorResult	A `Result` that gets data from a Python iterator of `Row` objects or similar row-like data.
MappingResult	A wrapper for a `Result` that returns dictionary values rather than `Row` values.
MergedResult	A `Result` that is merged from any number of `Result` objects.
Result	Represent a set of database results.
Row	Represent a single result row.
RowMapping	A `Mapping` that maps column names and objects to `Row` values.
ScalarResult	A wrapper for a `Result` that returns scalar values rather than `Row` values.
TupleResult	A `Result` that’s typed as returning plain Python tuples instead of rows.

class sqlalchemy.engine.ChunkedIteratorResult¶

An IteratorResult that works from an iterator-producing callable.

The given chunks argument is a function that is given a number of rows to return in each chunk, or None for all rows. The function should then return an un-consumed iterator of lists, each list of the requested size.

The function can be called at any time again, in which case it should continue from the same result set but adjust the chunk size as given.

在 1.4 版本加入.

Members

yield_per()

Class signature

class sqlalchemy.engine.ChunkedIteratorResult (sqlalchemy.engine.IteratorResult)

method sqlalchemy.engine.ChunkedIteratorResult.yield_per(num: int) → Self¶

Configure the row-fetching strategy to fetch num rows at a time.

This impacts the underlying behavior of the result when iterating over the result object, or otherwise making use of methods such as Result.fetchone() that return one row at a time. Data from the underlying cursor or other data source will be buffered up to this many rows in memory, and the buffered collection will then be yielded out one row at a time or as many rows are requested. Each time the buffer clears, it will be refreshed to this many rows or as many rows remain if fewer remain.

The Result.yield_per() method is generally used in conjunction with the Connection.execution_options.stream_results execution option, which will allow the database dialect in use to make use of a server side cursor, if the DBAPI supports a specific “server side cursor” mode separate from its default mode of operation.

小技巧

Consider using the Connection.execution_options.yield_per execution option, which will simultaneously set Connection.execution_options.stream_results to ensure the use of server side cursors, as well as automatically invoke the Result.yield_per() method to establish a fixed row buffer size at once.

The Connection.execution_options.yield_per execution option is available for ORM operations, with Session-oriented use described at 使用 Yield Per 获取大量结果集. The Core-only version which works with Connection is new as of SQLAlchemy 1.4.40.

在 1.4 版本加入.

参数:: num¶ – number of rows to fetch each time the buffer is refilled. If set to a value below 1, fetches all rows for the next buffer.

参见

使用服务器端游标（又称流结果） - describes Core behavior for Result.yield_per()

class sqlalchemy.engine.CursorResult¶

A Result that is representing state from a DBAPI cursor.

在 1.4 版本发生变更: The CursorResult` class replaces the previous ResultProxy interface. This classes are based on the Result calling API which provides an updated usage model and calling facade for SQLAlchemy Core and SQLAlchemy ORM.

Returns database rows via the Row class, which provides additional API features and behaviors on top of the raw data returned by the DBAPI. Through the use of filters such as the Result.scalars() method, other kinds of objects may also be returned.

参见

使用 SELECT 语句 - introductory material for accessing CursorResult and Row objects.

Class signature

class sqlalchemy.engine.CursorResult (sqlalchemy.engine.Result)

method sqlalchemy.engine.CursorResult.all() → Sequence[Row[Unpack[_Ts]]]¶

inherited from the Result.all() method of Result

Return all rows in a sequence.

Closes the result set after invocation. Subsequent invocations will return an empty sequence.

在 1.4 版本加入.

返回:: a sequence of Row objects.

参见

使用服务器端游标（又称流结果） - How to stream a large result set without loading it completely in python.

method sqlalchemy.engine.CursorResult.close() → Any¶

Close this CursorResult.

This closes out the underlying DBAPI cursor corresponding to the statement execution, if one is still present. Note that the DBAPI cursor is automatically released when the CursorResult exhausts all available rows. CursorResult.close() is generally an optional method except in the case when discarding a CursorResult that still has additional rows pending for fetch.

After this method is called, it is no longer valid to call upon the fetch methods, which will raise a ResourceClosedError on subsequent use.

参见

使用Engines和Connection

method sqlalchemy.engine.CursorResult.columns(*col_expressions: _KeyIndexType) → Self¶

inherited from the Result.columns() method of Result

Establish the columns that should be returned in each row.

This method may be used to limit the columns returned as well as to reorder them. The given list of expressions are normally a series of integers or string key names. They may also be appropriate ColumnElement objects which correspond to a given statement construct.

在 2.0 版本发生变更: Due to a bug in 1.4, the Result.columns() method had an incorrect behavior where calling upon the method with just one index would cause the Result object to yield scalar values rather than Row objects. In version 2.0, this behavior has been corrected such that calling upon Result.columns() with a single index will produce a Result object that continues to yield Row objects, which include only a single column.

E.g.:

statement = select(table.c.x, table.c.y, table.c.z)
result = connection.execute(statement)

for z, y in result.columns("z", "y"):
    ...

Example of using the column objects from the statement itself:

for z, y in result.columns(
    statement.selected_columns.c.z, statement.selected_columns.c.y
):
    ...

在 1.4 版本加入.

参数:: *col_expressions¶ – indicates columns to be returned. Elements may be integer row indexes, string column names, or appropriate ColumnElement objects corresponding to a select construct.
返回:: this Result object with the modifications given.

method sqlalchemy.engine.CursorResult.fetchall() → Sequence[Row[Unpack[_Ts]]]¶: inherited from the Result.fetchall() method of Result

A synonym for the Result.all() method.

method sqlalchemy.engine.CursorResult.fetchmany(size: int | None = None) → Sequence[Row[Unpack[_Ts]]]¶

inherited from the Result.fetchmany() method of Result

Fetch many rows.

When all rows are exhausted, returns an empty sequence.

This method is provided for backwards compatibility with SQLAlchemy 1.x.x.

To fetch rows in groups, use the Result.partitions() method.

返回:: a sequence of Row objects.

参见

ORM Query Unified with Core Select

method sqlalchemy.engine.CursorResult.fetchone() → Row[Unpack[_Ts]] | None¶

inherited from the Result.fetchone() method of Result

Fetch one row.

When all rows are exhausted, returns None.

This method is provided for backwards compatibility with SQLAlchemy 1.x.x.

To fetch the first row of a result only, use the Result.first() method. To iterate through all rows, iterate the Result object directly.

返回:: a Row object if no filters are applied, or None if no rows remain.

method sqlalchemy.engine.CursorResult.first() → Row[Unpack[_Ts]] | None¶

inherited from the Result.first() method of Result

Fetch the first row or None if no row is present.

Closes the result set and discards remaining rows.

备注

This method returns one row, e.g. tuple, by default. To return exactly one single scalar value, that is, the first column of the first row, use the Result.scalar() method, or combine Result.scalars() and Result.first().

Additionally, in contrast to the behavior of the legacy ORM Query.first() method, no limit is applied to the SQL query which was invoked to produce this Result; for a DBAPI driver that buffers results in memory before yielding rows, all rows will be sent to the Python process and all but the first row will be discarded.

参见

返回:: a Row object, or None if no rows remain.

参见

Result.scalar()

CursorResult.inserted_primary_key

method sqlalchemy.engine.CursorResult.freeze() → FrozenResult[Unpack[_Ts]]¶

inherited from the Result.freeze() method of Result

Return a callable object that will produce copies of this Result when invoked.

The callable object returned is an instance of FrozenResult.

This is used for result set caching. The method must be called on the result when it has been unconsumed, and calling the method will consume the result fully. When the FrozenResult is retrieved from a cache, it can be called any number of times where it will produce a new Result object each time against its stored set of rows.

参见

重新执行语句 - example usage within the ORM to implement a result-set cache.

attribute sqlalchemy.engine.CursorResult.inserted_primary_key¶

Return the primary key for the row just inserted.

The return value is a Row object representing a named tuple of primary key values in the order in which the primary key columns are configured in the source Table.

在 1.4.8 版本发生变更: - the CursorResult.inserted_primary_key value is now a named tuple via the Row class, rather than a plain tuple.

This accessor only applies to single row insert() constructs which did not explicitly specify Insert.returning(). Support for multirow inserts, while not yet available for most backends, would be accessed using the CursorResult.inserted_primary_key_rows accessor.

Note that primary key columns which specify a server_default clause, or otherwise do not qualify as “autoincrement” columns (see the notes at Column), and were generated using the database-side default, will appear in this list as None unless the backend supports “returning” and the insert statement executed with the “implicit returning” enabled.

Raises InvalidRequestError if the executed statement is not a compiled expression construct or is not an insert() construct.

attribute sqlalchemy.engine.CursorResult.inserted_primary_key_rows¶

Return the value of CursorResult.inserted_primary_key as a row contained within a list; some dialects may support a multiple row form as well.

备注

As indicated below, in current SQLAlchemy versions this accessor is only useful beyond what’s already supplied by CursorResult.inserted_primary_key when using the psycopg2 dialect. Future versions hope to generalize this feature to more dialects.

This accessor is added to support dialects that offer the feature that is currently implemented by the Psycopg2 Fast Execution Helpers feature, currently only the psycopg2 dialect, which provides for many rows to be INSERTed at once while still retaining the behavior of being able to return server-generated primary key values.

When using the psycopg2 dialect, or other dialects that may support “fast executemany” style inserts in upcoming releases : When invoking an INSERT statement while passing a list of rows as the second argument to Connection.execute(), this accessor will then provide a list of rows, where each row contains the primary key value for each row that was INSERTed.
When using all other dialects / backends that don’t yet support this feature: This accessor is only useful for single row INSERT statements, and returns the same information as that of the CursorResult.inserted_primary_key within a single-element list. When an INSERT statement is executed in conjunction with a list of rows to be INSERTed, the list will contain one row per row inserted in the statement, however it will contain None for any server-generated values.

Future releases of SQLAlchemy will further generalize the “fast execution helper” feature of psycopg2 to suit other dialects, thus allowing this accessor to be of more general use.

在 1.4 版本加入.

参见

attribute sqlalchemy.engine.CursorResult.is_insert¶

True if this CursorResult is the result of a executing an expression language compiled insert() construct.

When True, this implies that the inserted_primary_key attribute is accessible, assuming the statement did not include a user defined “returning” construct.

method sqlalchemy.engine.CursorResult.keys() → RMKeyView¶

inherited from the sqlalchemy.engine._WithKeys.keys method of sqlalchemy.engine._WithKeys

Return an iterable view which yields the string keys that would be represented by each Row.

The keys can represent the labels of the columns returned by a core statement or the names of the orm classes returned by an orm execution.

The view also can be tested for key containment using the Python in operator, which will test both for the string keys represented in the view, as well as for alternate keys such as column objects.

在 1.4 版本发生变更: a key view object is returned rather than a plain list.

method sqlalchemy.engine.CursorResult.last_inserted_params()¶

Return the collection of inserted parameters from this execution.

Raises InvalidRequestError if the executed statement is not a compiled expression construct or is not an insert() construct.

method sqlalchemy.engine.CursorResult.last_updated_params()¶

Return the collection of updated parameters from this execution.

Raises InvalidRequestError if the executed statement is not a compiled expression construct or is not an update() construct.

method sqlalchemy.engine.CursorResult.lastrow_has_defaults()¶

Return lastrow_has_defaults() from the underlying ExecutionContext.

See ExecutionContext for details.

attribute sqlalchemy.engine.CursorResult.lastrowid¶

Return the ‘lastrowid’ accessor on the DBAPI cursor.

This is a DBAPI specific method and is only functional for those backends which support it, for statements where it is appropriate. It’s behavior is not consistent across backends.

Usage of this method is normally unnecessary when using insert() expression constructs; the CursorResult.inserted_primary_key attribute provides a tuple of primary key values for a newly inserted row, regardless of database backend.

method sqlalchemy.engine.CursorResult.mappings() → MappingResult¶

inherited from the Result.mappings() method of Result

Apply a mappings filter to returned rows, returning an instance of MappingResult.

When this filter is applied, fetching rows will return RowMapping objects instead of Row objects.

在 1.4 版本加入.

返回:: a new MappingResult filtering object referring to this Result object.

method sqlalchemy.engine.CursorResult.merge(*others: Result[Unpack[Tuple[Any, ...]]]) → MergedResult[Unpack[Tuple[Any, ...]]]¶

Merge this Result with other compatible result objects.

The object returned is an instance of MergedResult, which will be composed of iterators from the given result objects.

The new result will use the metadata from this result object. The subsequent result objects must be against an identical set of result / cursor metadata, otherwise the behavior is undefined.

method sqlalchemy.engine.CursorResult.one() → Row[Unpack[_Ts]]¶

inherited from the Result.one() method of Result

Return exactly one row or raise an exception.

Raises NoResultFound if the result returns no rows, or MultipleResultsFound if multiple rows would be returned.

备注

This method returns one row, e.g. tuple, by default. To return exactly one single scalar value, that is, the first column of the first row, use the Result.scalar_one() method, or combine Result.scalars() and Result.one().

在 1.4 版本加入.

返回:: The first Row.
Raises:: MultipleResultsFound, NoResultFound

参见

Result.one_or_none()

Result.scalar_one()

method sqlalchemy.engine.CursorResult.one_or_none() → Row[Unpack[_Ts]] | None¶

inherited from the Result.one_or_none() method of Result

Return at most one result or raise an exception.

Returns None if the result has no rows. Raises MultipleResultsFound if multiple rows are returned.

在 1.4 版本加入.

返回:: The first Row or None if no row is available.
Raises:: MultipleResultsFound

参见

method sqlalchemy.engine.CursorResult.partitions(size: int | None = None) → Iterator[Sequence[Row[Unpack[_Ts]]]]¶

inherited from the Result.partitions() method of Result

Iterate through sub-lists of rows of the size given.

Each list will be of the size given, excluding the last list to be yielded, which may have a small number of rows. No empty lists will be yielded.

The result object is automatically closed when the iterator is fully consumed.

Note that the backend driver will usually buffer the entire result ahead of time unless the Connection.execution_options.stream_results execution option is used indicating that the driver should not pre-buffer results, if possible. Not all drivers support this option and the option is silently ignored for those who do not.

When using the ORM, the Result.partitions() method is typically more effective from a memory perspective when it is combined with use of the yield_per execution option, which instructs both the DBAPI driver to use server side cursors, if available, as well as instructs the ORM loading internals to only build a certain amount of ORM objects from a result at a time before yielding them out.

在 1.4 版本加入.

参数:: size¶ – indicate the maximum number of rows to be present in each list yielded. If None, makes use of the value set by the Result.yield_per(), method, if it were called, or the Connection.execution_options.yield_per execution option, which is equivalent in this regard. If yield_per weren’t set, it makes use of the Result.fetchmany() default, which may be backend specific and not well defined.
返回:: iterator of lists

参见

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

Connection.execution_options.preserve_rowcount

method sqlalchemy.engine.CursorResult.postfetch_cols()¶

Return postfetch_cols() from the underlying ExecutionContext.

See ExecutionContext for details.

Raises InvalidRequestError if the executed statement is not a compiled expression construct or is not an insert() or update() construct.

method sqlalchemy.engine.CursorResult.prefetch_cols()¶

Return prefetch_cols() from the underlying ExecutionContext.

See ExecutionContext for details.

Raises InvalidRequestError if the executed statement is not a compiled expression construct or is not an insert() or update() construct.

attribute sqlalchemy.engine.CursorResult.returned_defaults¶

Return the values of default columns that were fetched using the ValuesBase.return_defaults() feature.

The value is an instance of Row, or None if ValuesBase.return_defaults() was not used or if the backend does not support RETURNING.

参见

ValuesBase.return_defaults()

attribute sqlalchemy.engine.CursorResult.returned_defaults_rows¶

Return a list of rows each containing the values of default columns that were fetched using the ValuesBase.return_defaults() feature.

The return value is a list of Row objects.

在 1.4 版本加入.

attribute sqlalchemy.engine.CursorResult.returns_rows¶

True if this CursorResult returns zero or more rows.

I.e. if it is legal to call the methods CursorResult.fetchone(), CursorResult.fetchmany() CursorResult.fetchall().

Overall, the value of CursorResult.returns_rows should always be synonymous with whether or not the DBAPI cursor had a .description attribute, indicating the presence of result columns, noting that a cursor that returns zero rows still has a .description if a row-returning statement was emitted.

This attribute should be True for all results that are against SELECT statements, as well as for DML statements INSERT/UPDATE/DELETE that use RETURNING. For INSERT/UPDATE/DELETE statements that were not using RETURNING, the value will usually be False, however there are some dialect-specific exceptions to this, such as when using the MSSQL / pyodbc dialect a SELECT is emitted inline in order to retrieve an inserted primary key value.

attribute sqlalchemy.engine.CursorResult.rowcount¶

Return the ‘rowcount’ for this result.

The primary purpose of ‘rowcount’ is to report the number of rows matched by the WHERE criterion of an UPDATE or DELETE statement executed once (i.e. for a single parameter set), which may then be compared to the number of rows expected to be updated or deleted as a means of asserting data integrity.

This attribute is transferred from the cursor.rowcount attribute of the DBAPI before the cursor is closed, to support DBAPIs that don’t make this value available after cursor close. Some DBAPIs may offer meaningful values for other kinds of statements, such as INSERT and SELECT statements as well. In order to retrieve cursor.rowcount for these statements, set the Connection.execution_options.preserve_rowcount execution option to True, which will cause the cursor.rowcount value to be unconditionally memoized before any results are returned or the cursor is closed, regardless of statement type.

For cases where the DBAPI does not support rowcount for a particular kind of statement and/or execution, the returned value will be -1, which is delivered directly from the DBAPI and is part of PEP 249. All DBAPIs should support rowcount for single-parameter-set UPDATE and DELETE statements, however.

备注

Notes regarding CursorResult.rowcount:

This attribute returns the number of rows matched, which is not necessarily the same as the number of rows that were actually modified. For example, an UPDATE statement may have no net change on a given row if the SET values given are the same as those present in the row already. Such a row would be matched but not modified. On backends that feature both styles, such as MySQL, rowcount is configured to return the match count in all cases.
CursorResult.rowcount in the default case is only useful in conjunction with an UPDATE or DELETE statement, and only with a single set of parameters. For other kinds of statements, SQLAlchemy will not attempt to pre-memoize the value unless the Connection.execution_options.preserve_rowcount execution option is used. Note that contrary to PEP 249, many DBAPIs do not support rowcount values for statements that are not UPDATE or DELETE, particularly when rows are being returned which are not fully pre-buffered. DBAPIs that dont support rowcount for a particular kind of statement should return the value -1 for such statements.
CursorResult.rowcount may not be meaningful when executing a single statement with multiple parameter sets (i.e. an executemany). Most DBAPIs do not sum “rowcount” values across multiple parameter sets and will return -1 when accessed.
SQLAlchemy’s INSERT 语句的“插入多个值”行为 feature does support a correct population of CursorResult.rowcount when the Connection.execution_options.preserve_rowcount execution option is set to True.
Statements that use RETURNING may not support rowcount, returning a -1 value instead.

参见

从 UPDATE、DELETE 获取受影响的行数 - in the SQLAlchemy 统一教程

method sqlalchemy.engine.CursorResult.scalar() → Any¶

inherited from the Result.scalar() method of Result

Fetch the first column of the first row, and close the result set.

Returns None if there are no rows to fetch.

No validation is performed to test if additional rows remain.

After calling this method, the object is fully closed, e.g. the CursorResult.close() method will have been called.

返回:: a Python scalar value, or None if no rows remain.

method sqlalchemy.engine.CursorResult.scalar_one() → Any¶

inherited from the Result.scalar_one() method of Result

Return exactly one scalar result or raise an exception.

This is equivalent to calling Result.scalars() and then ScalarResult.one().

参见

ScalarResult.one()

ScalarResult.one_or_none()

method sqlalchemy.engine.CursorResult.scalar_one_or_none() → Any | None¶

inherited from the Result.scalar_one_or_none() method of Result

Return exactly one scalar result or None.

This is equivalent to calling Result.scalars() and then ScalarResult.one_or_none().

参见

CursorResult.splice_vertically()

method sqlalchemy.engine.CursorResult.scalars(index: _KeyIndexType = 0) → ScalarResult[Any]¶

inherited from the Result.scalars() method of Result

Return a ScalarResult filtering object which will return single elements rather than Row objects.

E.g.:

>>> result = conn.execute(text("select int_id from table"))
>>> result.scalars().all()
[1, 2, 3]

When results are fetched from the ScalarResult filtering object, the single column-row that would be returned by the Result is instead returned as the column’s value.

在 1.4 版本加入.

参数:: index¶ – integer or row key indicating the column to be fetched from each row, defaults to 0 indicating the first column.
返回:: a new ScalarResult filtering object referring to this Result object.

method sqlalchemy.engine.CursorResult.splice_horizontally(other)¶

Return a new CursorResult that “horizontally splices” together the rows of this CursorResult with that of another CursorResult.

小技巧

This method is for the benefit of the SQLAlchemy ORM and is not intended for general use.

“horizontally splices” means that for each row in the first and second result sets, a new row that concatenates the two rows together is produced, which then becomes the new row. The incoming CursorResult must have the identical number of rows. It is typically expected that the two result sets come from the same sort order as well, as the result rows are spliced together based on their position in the result.

The expected use case here is so that multiple INSERT..RETURNING statements (which definitely need to be sorted) against different tables can produce a single result that looks like a JOIN of those two tables.

E.g.:

r1 = connection.execute(
    users.insert().returning(
        users.c.user_name, users.c.user_id, sort_by_parameter_order=True
    ),
    user_values,
)

r2 = connection.execute(
    addresses.insert().returning(
        addresses.c.address_id,
        addresses.c.address,
        addresses.c.user_id,
        sort_by_parameter_order=True,
    ),
    address_values,
)

rows = r1.splice_horizontally(r2).all()
assert rows == [
    ("john", 1, 1, "foo@bar.com", 1),
    ("jack", 2, 2, "bar@bat.com", 2),
]

在 2.0 版本加入.

参见

method sqlalchemy.engine.CursorResult.splice_vertically(other)¶

Return a new CursorResult that “vertically splices”, i.e. “extends”, the rows of this CursorResult with that of another CursorResult.

小技巧

This method is for the benefit of the SQLAlchemy ORM and is not intended for general use.

“vertically splices” means the rows of the given result are appended to the rows of this cursor result. The incoming CursorResult must have rows that represent the identical list of columns in the identical order as they are in this CursorResult.

在 2.0 版本加入.

参见

CursorResult.splice_horizontally()

method sqlalchemy.engine.CursorResult.supports_sane_multi_rowcount()¶

Return supports_sane_multi_rowcount from the dialect.

See CursorResult.rowcount for background.

method sqlalchemy.engine.CursorResult.supports_sane_rowcount()¶

Return supports_sane_rowcount from the dialect.

See CursorResult.rowcount for background.

attribute sqlalchemy.engine.CursorResult.t¶

inherited from the Result.t attribute of Result

Apply a “typed tuple” typing filter to returned rows.

自 2.1.0 版本弃用: The Result.t method is deprecated, Row now behaves like a tuple and can unpack types directly.

The Result.t attribute is a synonym for calling the Result.tuples() method.

在 2.0 版本加入.

参见

Row 现在直接表示单个列类型，无需 Tuple - describes a migration path from this workaround for SQLAlchemy 2.1.

method sqlalchemy.engine.CursorResult.tuples() → TupleResult[Tuple[Unpack[_Ts]]]¶

inherited from the Result.tuples() method of Result

Apply a “typed tuple” typing filter to returned rows.

自 2.1.0 版本弃用: The Result.tuples() method is deprecated, Row now behaves like a tuple and can unpack types directly.

This method returns the same Result object at runtime, however annotates as returning a TupleResult object that will indicate to PEP 484 typing tools that plain typed Tuple instances are returned rather than rows. This allows tuple unpacking and __getitem__ access of Row objects to by typed, for those cases where the statement invoked itself included typing information.

在 2.0 版本加入.

返回:: the TupleResult type at typing time.

参见

Row 现在直接表示单个列类型，无需 Tuple - describes a migration path from this workaround for SQLAlchemy 2.1.

Result.t - shorter synonym

Row._t - Row version

method sqlalchemy.engine.CursorResult.unique(strategy: Callable[[Any], Any] | None = None) → Self¶

inherited from the Result.unique() method of Result

Apply unique filtering to the objects returned by this Result.

When this filter is applied with no arguments, the rows or objects returned will filtered such that each row is returned uniquely. The algorithm used to determine this uniqueness is by default the Python hashing identity of the whole tuple. In some cases a specialized per-entity hashing scheme may be used, such as when using the ORM, a scheme is applied which works against the primary key identity of returned objects.

The unique filter is applied after all other filters, which means if the columns returned have been refined using a method such as the Result.columns() or Result.scalars() method, the uniquing is applied to only the column or columns returned. This occurs regardless of the order in which these methods have been called upon the Result object.

The unique filter also changes the calculus used for methods like Result.fetchmany() and Result.partitions(). When using Result.unique(), these methods will continue to yield the number of rows or objects requested, after uniquing has been applied. However, this necessarily impacts the buffering behavior of the underlying cursor or datasource, such that multiple underlying calls to cursor.fetchmany() may be necessary in order to accumulate enough objects in order to provide a unique collection of the requested size.

参数:: strategy¶ – a callable that will be applied to rows or objects being iterated, which should return an object that represents the unique value of the row. A Python set() is used to store these identities. If not passed, a default uniqueness strategy is used which may have been assembled by the source of this Result object.

method sqlalchemy.engine.CursorResult.yield_per(num: int) → Self¶

Configure the row-fetching strategy to fetch num rows at a time.

小技巧

在 1.4 版本加入.

参数:: num¶ – number of rows to fetch each time the buffer is refilled. If set to a value below 1, fetches all rows for the next buffer.

参见

使用服务器端游标（又称流结果） - describes Core behavior for Result.yield_per()

class sqlalchemy.engine.FilterResult¶

A wrapper for a Result that returns objects other than Row objects, such as dictionaries or scalar objects.

FilterResult is the common base for additional result APIs including MappingResult, ScalarResult and AsyncResult.

Members

close(), closed, yield_per()

Class signature

class sqlalchemy.engine.FilterResult (sqlalchemy.engine.ResultInternal)

method sqlalchemy.engine.FilterResult.close() → None¶: Close this FilterResult.

在 1.4.43 版本加入.

attribute sqlalchemy.engine.FilterResult.closed¶: Return True if the underlying Result reports closed

在 1.4.43 版本加入.

method sqlalchemy.engine.FilterResult.yield_per(num: int) → Self¶

Configure the row-fetching strategy to fetch num rows at a time.

The FilterResult.yield_per() method is a pass through to the Result.yield_per() method. See that method’s documentation for usage notes.

在 1.4.40 版本加入: - added FilterResult.yield_per() so that the method is available on all result set implementations

参见

使用服务器端游标（又称流结果） - describes Core behavior for Result.yield_per()

class sqlalchemy.engine.FrozenResult¶

Represents a Result object in a “frozen” state suitable for caching.

The FrozenResult object is returned from the Result.freeze() method of any Result object.

A new iterable Result object is generated from a fixed set of data each time the FrozenResult is invoked as a callable:

result = connection.execute(query)

frozen = result.freeze()

unfrozen_result_one = frozen()

for row in unfrozen_result_one:
    print(row)

unfrozen_result_two = frozen()
rows = unfrozen_result_two.all()

# ... etc

在 1.4 版本加入.

参见

重新执行语句 - example usage within the ORM to implement a result-set cache.

merge_frozen_result() - ORM function to merge a frozen result back into a Session.

Class signature

class sqlalchemy.engine.FrozenResult (typing.Generic)

class sqlalchemy.engine.IteratorResult¶

A Result that gets data from a Python iterator of Row objects or similar row-like data.

在 1.4 版本加入.

Members

closed

Class signature

class sqlalchemy.engine.IteratorResult (sqlalchemy.engine.Result)

attribute sqlalchemy.engine.IteratorResult.closed¶: Return True if this IteratorResult has been closed

在 1.4.43 版本加入.

class sqlalchemy.engine.MergedResult¶

A Result that is merged from any number of Result objects.

Returned by the Result.merge() method.

在 1.4 版本加入.

Class signature

class sqlalchemy.engine.MergedResult (sqlalchemy.engine.IteratorResult)

class sqlalchemy.engine.Result¶

Represent a set of database results.

在 1.4 版本加入: The Result object provides a completely updated usage model and calling facade for SQLAlchemy Core and SQLAlchemy ORM. In Core, it forms the basis of the CursorResult object which replaces the previous ResultProxy interface. When using the ORM, a higher level object called ChunkedIteratorResult is normally used.

备注

In SQLAlchemy 1.4 and above, this object is used for ORM results returned by Session.execute(), which can yield instances of ORM mapped objects either individually or within tuple-like rows. Note that the Result object does not deduplicate instances or rows automatically as is the case with the legacy Query object. For in-Python de-duplication of instances or rows, use the Result.unique() modifier method.

参见

获取行 - in the SQLAlchemy 统一教程

Members

all(), close(), closed, columns(), fetchall(), fetchmany(), fetchone(), first(), freeze(), keys(), mappings(), merge(), one(), one_or_none(), partitions(), scalar(), scalar_one(), scalar_one_or_none(), scalars(), t, tuples(), unique(), yield_per()

Class signature

class sqlalchemy.engine.Result (sqlalchemy.engine._WithKeys, sqlalchemy.engine.ResultInternal)

method sqlalchemy.engine.Result.all() → Sequence[Row[Unpack[_Ts]]]¶

Return all rows in a sequence.

Closes the result set after invocation. Subsequent invocations will return an empty sequence.

在 1.4 版本加入.

返回:: a sequence of Row objects.

参见

使用服务器端游标（又称流结果） - How to stream a large result set without loading it completely in python.

method sqlalchemy.engine.Result.close() → None¶

close this Result.

The behavior of this method is implementation specific, and is not implemented by default. The method should generally end the resources in use by the result object and also cause any subsequent iteration or row fetching to raise ResourceClosedError.

在 1.4.27 版本加入: - .close() was previously not generally available for all Result classes, instead only being available on the CursorResult returned for Core statement executions. As most other result objects, namely the ones used by the ORM, are proxying a CursorResult in any case, this allows the underlying cursor result to be closed from the outside facade for the case when the ORM query is using the yield_per execution option where it does not immediately exhaust and autoclose the database cursor.

attribute sqlalchemy.engine.Result.closed¶: return True if this Result reports .closed

在 1.4.43 版本加入.

method sqlalchemy.engine.Result.columns(*col_expressions: _KeyIndexType) → Self¶

Establish the columns that should be returned in each row.

E.g.:

statement = select(table.c.x, table.c.y, table.c.z)
result = connection.execute(statement)

for z, y in result.columns("z", "y"):
    ...

Example of using the column objects from the statement itself:

for z, y in result.columns(
    statement.selected_columns.c.z, statement.selected_columns.c.y
):
    ...

在 1.4 版本加入.

参数:: *col_expressions¶ – indicates columns to be returned. Elements may be integer row indexes, string column names, or appropriate ColumnElement objects corresponding to a select construct.
返回:: this Result object with the modifications given.

method sqlalchemy.engine.Result.fetchall() → Sequence[Row[Unpack[_Ts]]]¶: A synonym for the Result.all() method.

method sqlalchemy.engine.Result.fetchmany(size: int | None = None) → Sequence[Row[Unpack[_Ts]]]¶

Fetch many rows.

When all rows are exhausted, returns an empty sequence.

This method is provided for backwards compatibility with SQLAlchemy 1.x.x.

To fetch rows in groups, use the Result.partitions() method.

返回:: a sequence of Row objects.

参见

ORM Query Unified with Core Select

method sqlalchemy.engine.Result.fetchone() → Row[Unpack[_Ts]] | None¶

Fetch one row.

When all rows are exhausted, returns None.

This method is provided for backwards compatibility with SQLAlchemy 1.x.x.

To fetch the first row of a result only, use the Result.first() method. To iterate through all rows, iterate the Result object directly.

返回:: a Row object if no filters are applied, or None if no rows remain.

method sqlalchemy.engine.Result.first() → Row[Unpack[_Ts]] | None¶

Fetch the first row or None if no row is present.

Closes the result set and discards remaining rows.

备注

参见

返回:: a Row object, or None if no rows remain.

参见

Result.scalar()

method sqlalchemy.engine.Result.freeze() → FrozenResult[Unpack[_Ts]]¶

Return a callable object that will produce copies of this Result when invoked.

The callable object returned is an instance of FrozenResult.

参见

重新执行语句 - example usage within the ORM to implement a result-set cache.

method sqlalchemy.engine.Result.keys() → RMKeyView¶

inherited from the sqlalchemy.engine._WithKeys.keys method of sqlalchemy.engine._WithKeys

Return an iterable view which yields the string keys that would be represented by each Row.

The keys can represent the labels of the columns returned by a core statement or the names of the orm classes returned by an orm execution.

The view also can be tested for key containment using the Python in operator, which will test both for the string keys represented in the view, as well as for alternate keys such as column objects.

在 1.4 版本发生变更: a key view object is returned rather than a plain list.

method sqlalchemy.engine.Result.mappings() → MappingResult¶

Apply a mappings filter to returned rows, returning an instance of MappingResult.

When this filter is applied, fetching rows will return RowMapping objects instead of Row objects.

在 1.4 版本加入.

返回:: a new MappingResult filtering object referring to this Result object.

method sqlalchemy.engine.Result.merge(*others: Result[Unpack[Tuple[Any, ...]]]) → MergedResult[Unpack[Tuple[Any, ...]]]¶

Merge this Result with other compatible result objects.

The object returned is an instance of MergedResult, which will be composed of iterators from the given result objects.

The new result will use the metadata from this result object. The subsequent result objects must be against an identical set of result / cursor metadata, otherwise the behavior is undefined.

method sqlalchemy.engine.Result.one() → Row[Unpack[_Ts]]¶

Return exactly one row or raise an exception.

Raises NoResultFound if the result returns no rows, or MultipleResultsFound if multiple rows would be returned.

备注

在 1.4 版本加入.

返回:: The first Row.
Raises:: MultipleResultsFound, NoResultFound

参见

Result.one_or_none()

Result.scalar_one()

method sqlalchemy.engine.Result.one_or_none() → Row[Unpack[_Ts]] | None¶

Return at most one result or raise an exception.

Returns None if the result has no rows. Raises MultipleResultsFound if multiple rows are returned.

在 1.4 版本加入.

返回:: The first Row or None if no row is available.
Raises:: MultipleResultsFound

参见

method sqlalchemy.engine.Result.partitions(size: int | None = None) → Iterator[Sequence[Row[Unpack[_Ts]]]]¶

Iterate through sub-lists of rows of the size given.

Each list will be of the size given, excluding the last list to be yielded, which may have a small number of rows. No empty lists will be yielded.

The result object is automatically closed when the iterator is fully consumed.

在 1.4 版本加入.

参数:: size¶ – indicate the maximum number of rows to be present in each list yielded. If None, makes use of the value set by the Result.yield_per(), method, if it were called, or the Connection.execution_options.yield_per execution option, which is equivalent in this regard. If yield_per weren’t set, it makes use of the Result.fetchmany() default, which may be backend specific and not well defined.
返回:: iterator of lists

参见

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

method sqlalchemy.engine.Result.scalar() → Any¶

Fetch the first column of the first row, and close the result set.

Returns None if there are no rows to fetch.

No validation is performed to test if additional rows remain.

After calling this method, the object is fully closed, e.g. the CursorResult.close() method will have been called.

返回:: a Python scalar value, or None if no rows remain.

method sqlalchemy.engine.Result.scalar_one() → Any¶

Return exactly one scalar result or raise an exception.

This is equivalent to calling Result.scalars() and then ScalarResult.one().

参见

ScalarResult.one()

ScalarResult.one_or_none()

method sqlalchemy.engine.Result.scalar_one_or_none() → Any | None¶

Return exactly one scalar result or None.

This is equivalent to calling Result.scalars() and then ScalarResult.one_or_none().

参见

method sqlalchemy.engine.Result.scalars(index: _KeyIndexType = 0) → ScalarResult[Any]¶

Return a ScalarResult filtering object which will return single elements rather than Row objects.

E.g.:

>>> result = conn.execute(text("select int_id from table"))
>>> result.scalars().all()
[1, 2, 3]

When results are fetched from the ScalarResult filtering object, the single column-row that would be returned by the Result is instead returned as the column’s value.

在 1.4 版本加入.

参数:: index¶ – integer or row key indicating the column to be fetched from each row, defaults to 0 indicating the first column.
返回:: a new ScalarResult filtering object referring to this Result object.

attribute sqlalchemy.engine.Result.t¶

Apply a “typed tuple” typing filter to returned rows.

自 2.1.0 版本弃用: The Result.t method is deprecated, Row now behaves like a tuple and can unpack types directly.

The Result.t attribute is a synonym for calling the Result.tuples() method.

在 2.0 版本加入.

参见

Row 现在直接表示单个列类型，无需 Tuple - describes a migration path from this workaround for SQLAlchemy 2.1.

method sqlalchemy.engine.Result.tuples() → TupleResult[Tuple[Unpack[_Ts]]]¶

Apply a “typed tuple” typing filter to returned rows.

自 2.1.0 版本弃用: The Result.tuples() method is deprecated, Row now behaves like a tuple and can unpack types directly.

在 2.0 版本加入.

返回:: the TupleResult type at typing time.

参见

Row 现在直接表示单个列类型，无需 Tuple - describes a migration path from this workaround for SQLAlchemy 2.1.

Result.t - shorter synonym

Row._t - Row version

method sqlalchemy.engine.Result.unique(strategy: Callable[[Any], Any] | None = None) → Self¶

Apply unique filtering to the objects returned by this Result.

参数:: strategy¶ – a callable that will be applied to rows or objects being iterated, which should return an object that represents the unique value of the row. A Python set() is used to store these identities. If not passed, a default uniqueness strategy is used which may have been assembled by the source of this Result object.

method sqlalchemy.engine.Result.yield_per(num: int) → Self¶

Configure the row-fetching strategy to fetch num rows at a time.

小技巧

在 1.4 版本加入.

参数:: num¶ – number of rows to fetch each time the buffer is refilled. If set to a value below 1, fetches all rows for the next buffer.

参见

使用服务器端游标（又称流结果） - describes Core behavior for Result.yield_per()

class sqlalchemy.engine.ScalarResult¶

A wrapper for a Result that returns scalar values rather than Row values.

The ScalarResult object is acquired by calling the Result.scalars() method.

A special limitation of ScalarResult is that it has no fetchone() method; since the semantics of fetchone() are that the None value indicates no more results, this is not compatible with ScalarResult since there is no way to distinguish between None as a row value versus None as an indicator. Use next(result) to receive values individually.

Members

all(), close(), closed, fetchall(), fetchmany(), first(), one(), one_or_none(), partitions(), unique(), yield_per()

Class signature

class sqlalchemy.engine.ScalarResult (sqlalchemy.engine.FilterResult)

method sqlalchemy.engine.ScalarResult.all() → Sequence[_R]¶

Return all scalar values in a sequence.

Equivalent to Result.all() except that scalar values, rather than Row objects, are returned.

method sqlalchemy.engine.ScalarResult.close() → None¶: inherited from the FilterResult.close() method of FilterResult

Close this FilterResult.

在 1.4.43 版本加入.

attribute sqlalchemy.engine.ScalarResult.closed¶: inherited from the FilterResult.closed attribute of FilterResult

Return True if the underlying Result reports closed

在 1.4.43 版本加入.

method sqlalchemy.engine.ScalarResult.fetchall() → Sequence[_R]¶: A synonym for the ScalarResult.all() method.

method sqlalchemy.engine.ScalarResult.fetchmany(size: int | None = None) → Sequence[_R]¶

Fetch many objects.

Equivalent to Result.fetchmany() except that scalar values, rather than Row objects, are returned.

method sqlalchemy.engine.ScalarResult.first() → _R | None¶

Fetch the first object or None if no object is present.

Equivalent to Result.first() except that scalar values, rather than Row objects, are returned.

method sqlalchemy.engine.ScalarResult.one() → _R¶

Return exactly one object or raise an exception.

Equivalent to Result.one() except that scalar values, rather than Row objects, are returned.

method sqlalchemy.engine.ScalarResult.one_or_none() → _R | None¶

Return at most one object or raise an exception.

Equivalent to Result.one_or_none() except that scalar values, rather than Row objects, are returned.

method sqlalchemy.engine.ScalarResult.partitions(size: int | None = None) → Iterator[Sequence[_R]]¶

Iterate through sub-lists of elements of the size given.

Equivalent to Result.partitions() except that scalar values, rather than Row objects, are returned.

method sqlalchemy.engine.ScalarResult.unique(strategy: Callable[[Any], Any] | None = None) → Self¶

Apply unique filtering to the objects returned by this ScalarResult.

See Result.unique() for usage details.

method sqlalchemy.engine.ScalarResult.yield_per(num: int) → Self¶

inherited from the FilterResult.yield_per() method of FilterResult

Configure the row-fetching strategy to fetch num rows at a time.

The FilterResult.yield_per() method is a pass through to the Result.yield_per() method. See that method’s documentation for usage notes.

在 1.4.40 版本加入: - added FilterResult.yield_per() so that the method is available on all result set implementations

参见

使用服务器端游标（又称流结果） - describes Core behavior for Result.yield_per()

class sqlalchemy.engine.MappingResult¶

A wrapper for a Result that returns dictionary values rather than Row values.

The MappingResult object is acquired by calling the Result.mappings() method.

Members

all(), close(), closed, columns(), fetchall(), fetchmany(), fetchone(), first(), keys(), one(), one_or_none(), partitions(), unique(), yield_per()

Class signature

class sqlalchemy.engine.MappingResult (sqlalchemy.engine._WithKeys, sqlalchemy.engine.FilterResult)

method sqlalchemy.engine.MappingResult.all() → Sequence[RowMapping]¶

Return all scalar values in a sequence.

Equivalent to Result.all() except that RowMapping values, rather than Row objects, are returned.

method sqlalchemy.engine.MappingResult.close() → None¶: inherited from the FilterResult.close() method of FilterResult

Close this FilterResult.

在 1.4.43 版本加入.

attribute sqlalchemy.engine.MappingResult.closed¶: inherited from the FilterResult.closed attribute of FilterResult

Return True if the underlying Result reports closed

在 1.4.43 版本加入.

method sqlalchemy.engine.MappingResult.columns(*col_expressions: _KeyIndexType) → Self¶: Establish the columns that should be returned in each row.

method sqlalchemy.engine.MappingResult.fetchall() → Sequence[RowMapping]¶: A synonym for the MappingResult.all() method.

method sqlalchemy.engine.MappingResult.fetchmany(size: int | None = None) → Sequence[RowMapping]¶

Fetch many objects.

Equivalent to Result.fetchmany() except that RowMapping values, rather than Row objects, are returned.

method sqlalchemy.engine.MappingResult.fetchone() → RowMapping | None¶

Fetch one object.

Equivalent to Result.fetchone() except that RowMapping values, rather than Row objects, are returned.

method sqlalchemy.engine.MappingResult.first() → RowMapping | None¶

Fetch the first object or None if no object is present.

Equivalent to Result.first() except that RowMapping values, rather than Row objects, are returned.

method sqlalchemy.engine.MappingResult.keys() → RMKeyView¶

inherited from the sqlalchemy.engine._WithKeys.keys method of sqlalchemy.engine._WithKeys

Return an iterable view which yields the string keys that would be represented by each Row.

The keys can represent the labels of the columns returned by a core statement or the names of the orm classes returned by an orm execution.

The view also can be tested for key containment using the Python in operator, which will test both for the string keys represented in the view, as well as for alternate keys such as column objects.

在 1.4 版本发生变更: a key view object is returned rather than a plain list.

method sqlalchemy.engine.MappingResult.one() → RowMapping¶

Return exactly one object or raise an exception.

Equivalent to Result.one() except that RowMapping values, rather than Row objects, are returned.

method sqlalchemy.engine.MappingResult.one_or_none() → RowMapping | None¶

Return at most one object or raise an exception.

Equivalent to Result.one_or_none() except that RowMapping values, rather than Row objects, are returned.

method sqlalchemy.engine.MappingResult.partitions(size: int | None = None) → Iterator[Sequence[RowMapping]]¶

Iterate through sub-lists of elements of the size given.

Equivalent to Result.partitions() except that RowMapping values, rather than Row objects, are returned.

method sqlalchemy.engine.MappingResult.unique(strategy: Callable[[Any], Any] | None = None) → Self¶

Apply unique filtering to the objects returned by this MappingResult.

See Result.unique() for usage details.

method sqlalchemy.engine.MappingResult.yield_per(num: int) → Self¶

inherited from the FilterResult.yield_per() method of FilterResult

Configure the row-fetching strategy to fetch num rows at a time.

The FilterResult.yield_per() method is a pass through to the Result.yield_per() method. See that method’s documentation for usage notes.

在 1.4.40 版本加入: - added FilterResult.yield_per() so that the method is available on all result set implementations

参见

使用服务器端游标（又称流结果） - describes Core behavior for Result.yield_per()