SQLAlchemy 2.0 - Major Migration Guide
SQLAlchemy 2.0 - 主要迁移指南 ¶

Note for Readers 读者须知

SQLAlchemy 2.0’s transition documents are separated into two documents - one which details major API shifts from the 1.x to 2.x series, and the other which details new features and behaviors relative to SQLAlchemy 1.4:
SQLAlchemy 2.0 的过渡文档分为两份文档 - 一份详细说明了从 1.x 到 2.x 系列的主要 API 变化,另一份详细说明了相对于 SQLAlchemy 1.4 的新特性和行为:

Readers who have already updated their 1.4 application to follow SQLAlchemy 2.0 engine and ORM conventions may navigate to What’s New in SQLAlchemy 2.0? for an overview of new features and capabilities.
已经更新其 1.4 应用程序以遵循 SQLAlchemy 2.0 引擎和 ORM 约定的读者可以导航至 SQLAlchemy 2.0 中的新增功能?以获取新功能和功能的概述。

About this document 关于此文档

This document describes changes between SQLAlchemy version 1.4 and SQLAlchemy version 2.0.
本文档描述了 SQLAlchemy 1.4 版本和 SQLAlchemy 2.0 版本之间的更改。

SQLAlchemy 2.0 presents a major shift for a wide variety of key SQLAlchemy usage patterns in both the Core and ORM components. The goal of this release is to make a slight readjustment in some of the most fundamental assumptions of SQLAlchemy since its early beginnings, and to deliver a newly streamlined usage model that is hoped to be significantly more minimalist and consistent between the Core and ORM components, as well as more capable. The move of Python to be Python 3 only as well as the emergence of gradual typing systems for Python 3 are the initial inspirations for this shift, as is the changing nature of the Python community which now includes not just hardcore database programmers but a vast new community of data scientists and students of many different disciplines.
SQLAlchemy 2.0 对核心和 ORM 组件中各种关键 SQLAlchemy 使用模式带来了重大转变。此版本的目的是对 SQLAlchemy 自早期开始以来一些最基本的假设进行微调,并提供一个新的简化使用模型,希望在核心和 ORM 组件之间更加简约和一致,并且功能更强大。Python 转向仅限 Python 3 以及 Python 3 逐渐输入系统的出现是这种转变的最初灵感,Python 社区的变化也是如此,其中不仅包括铁杆数据库程序员,还包括大量数据科学家和来自不同领域的学生。

SQLAlchemy started with Python 2.3 which had no context managers, no function decorators, Unicode as a second class feature, and a variety of other shortcomings that would be unknown today. The biggest changes in SQLAlchemy 2.0 are targeting the residual assumptions left over from this early period in SQLAlchemy’s development as well as the leftover artifacts resulting from the incremental introduction of key API features such as Query and Declarative. It also hopes standardize some newer capabilities that have proven to be very effective.
SQLAlchemy 最初使用 Python 2.3,该版本没有上下文管理器、没有函数装饰器、Unicode 作为二等特性,以及今天看来各种其他不足之处。SQLAlchemy 2.0 中最大的变化针对的是 SQLAlchemy 开发早期遗留下来的残余假设,以及 Query 和 Declarative 等关键 API 特性逐步引入所产生的遗留工件。它还希望标准化一些已被证明非常有效的较新功能。

The 1.4->2.0 Migration Path
1.4->2.0 迁移路径 ¶

The most prominent architectural features and API changes that are considered to be “SQLAlchemy 2.0” were in fact released as fully available within the 1.4 series, to provide for a clean upgrade path from the 1.x to the 2.x series as well as to serve as a beta platform for the features themselves. These changes include:
被认为是“SQLAlchemy 2.0”的最突出的架构特性和 API 更改实际上已在 1.4 系列中完全发布,以便为从 1.x 到 2.x 系列提供干净的升级路径,并作为这些特性的测试平台。这些更改包括:

The above bullets link to the description of these new paradigms as introduced in SQLAlchemy 1.4. in the What’s New in SQLAlchemy 1.4? document.
上述项目符号链接到 SQLAlchemy 1.4 中引入的这些新范例的描述,详见 SQLAlchemy 1.4 中的新增功能?文档。

For SQLAlchemy 2.0, all API features and behaviors that were marked as deprecated for 2.0 are now finalized; in particular, major APIs that are no longer present include:
对于 SQLAlchemy 2.0,所有标记为 2.0 弃用的 API 功能和行为现在已最终确定;特别是,不再存在的重大 API 包括:

The above bullets refer to the most prominent fully backwards-incompatible changes that are finalized in the 2.0 release. The migration path for applications to accommodate for these changes as well as others is framed as a transition path first into the 1.4 series of SQLAlchemy where the “future” APIs are available to provide for the “2.0” way of working, and then to the 2.0 series where the no-longer-used APIs above and others are removed.
上述要点涉及 2.0 版本中最终确定的最突出的完全向后不兼容的更改。应用程序适应这些更改以及其他更改的迁移路径首先被构建为过渡路径,进入 SQLAlchemy 的 1.4 系列,其中“未来”API 可用于提供“2.0”工作方式,然后进入 2.0 系列,其中上述不再使用的 API 和其他 API 已被移除。

The complete steps for this migration path are later in this document at 1.x -> 2.x Migration Overview.
有关此迁移路径的完整步骤,请参阅本文档后面的 1.x -> 2.x 迁移概述。

1.x -> 2.x Migration Overview
1.x -> 2.x 迁移概述 ¶

The SQLAlchemy 2.0 transition presents itself in the SQLAlchemy 1.4 release as a series of steps that allow an application of any size or complexity to be migrated to SQLAlchemy 2.0 using a gradual, iterative process. Lessons learned from the Python 2 to Python 3 transition have inspired a system that intends to as great a degree as possible to not require any “breaking” changes, or any change that would need to be made universally or not at all.
SQLAlchemy 2.0 过渡在 SQLAlchemy 1.4 版本中以一系列步骤的形式呈现,这些步骤允许通过渐进的迭代过程将任何规模或复杂程度的应用程序迁移到 SQLAlchemy 2.0。从 Python 2 到 Python 3 过渡中吸取的经验启发了一个系统,该系统旨在尽可能不进行任何“重大”更改,或任何需要普遍进行或根本不需要进行的更改。

As a means of both proving the 2.0 architecture as well as allowing a fully iterative transition environment, the entire scope of 2.0’s new APIs and features are present and available within the 1.4 series; this includes major new areas of functionality such as the SQL caching system, the new ORM statement execution model, new transactional paradigms for both ORM and Core, a new ORM declarative system that unifies classical and declarative mapping, support for Python dataclasses, and asyncio support for Core and ORM.
作为证明 2.0 架构以及允许完全迭代过渡环境的一种手段,2.0 的新 API 和功能的整个范围都存在于 1.4 系列中,并且可以在其中使用;这包括主要的新功能领域,例如 SQL 缓存系统、新的 ORM 语句执行模型、ORM 和 Core 的新事务范例、统一经典映射和声明映射的新 ORM 声明系统、对 Python 数据类的支持以及对 Core 和 ORM 的 asyncio 支持。

The steps to achieve 2.0 migration are in the following subsections; overall, the general strategy is that once an application runs on 1.4 with all warning flags turned on and does not emit any 2.0-deprecation warnings, it is now mostly cross-compatible with SQLAlchemy 2.0. Please note there may be additional API and behavioral changes that may behave differently when running against SQLAlchemy 2.0; always test code against an actual SQLAlchemy 2.0 release as the final step in migrating.
实现 2.0 迁移的步骤在以下小节中;总体而言,总体策略是,一旦应用程序在 1.4 上运行,所有警告标志都已打开,并且不会发出任何 2.0 弃用警告,它现在与 SQLAlchemy 2.0 大多是交叉兼容的。请注意,在针对 SQLAlchemy 2.0 运行时,可能会有其他 API 和行为更改,其行为可能不同;在迁移的最后一步中,始终针对实际的 SQLAlchemy 2.0 版本测试代码。

First Prerequisite, step one - A Working 1.3 Application
第一个前提条件,步骤一 - 一个可用的 1.3 应用程序 ¶

The first step is getting an existing application onto 1.4, in the case of a typical non trivial application, is to ensure it runs on SQLAlchemy 1.3 with no deprecation warnings. Release 1.4 does have a few changes linked to conditions that warn in previous version, including some warnings that were introduced in 1.3, in particular some changes to the behavior of the relationship.viewonly and relationship.sync_backref flags.
第一步是将现有应用程序升级到 1.4,对于一个典型的非平凡应用程序,需要确保它在 SQLAlchemy 1.3 上运行且没有弃用警告。1.4 版本确实有一些与以前版本中警告条件相关的更改,包括 1.3 中引入的一些警告,特别是对 relationship.viewonlyrelationship.sync_backref 标志的行为进行了一些更改。

For best results, the application should be able to run, or pass all of its tests, with the latest SQLAlchemy 1.3 release with no SQLAlchemy deprecation warnings; these are warnings emitted for the SADeprecationWarning class.
为了获得最佳结果,该应用程序应该能够运行或通过其所有测试,使用最新的 SQLAlchemy 1.3 版本,并且没有 SQLAlchemy 弃用警告;这些是针对 SADeprecationWarning 类发出的警告。

First Prerequisite, step two - A Working 1.4 Application
第一个前提条件,步骤 2 - 一个可用的 1.4 应用程序 ¶

Once the application is good to go on SQLAlchemy 1.3, the next step is to get it running on SQLAlchemy 1.4. In the vast majority of cases, applications should run without problems from SQLAlchemy 1.3 to 1.4. However, it’s always the case between any 1.x and 1.y release, APIs and behaviors have changed either subtly or in some cases a little less subtly, and the SQLAlchemy project always gets a good deal of regression reports for the first few months.
一旦应用程序在 SQLAlchemy 1.3 上运行良好,下一步就是让它在 SQLAlchemy 1.4 上运行。在绝大多数情况下,应用程序应该可以在 SQLAlchemy 1.3 到 1.4 之间无问题地运行。然而,在任何 1.x 和 1.y 版本之间,API 和行为都会发生细微的变化,在某些情况下变化会更大,而 SQLAlchemy 项目总会在最初几个月收到大量回归报告。

The 1.x->1.y release process usually has a few changes around the margins that are a little bit more dramatic and are based around use cases that are expected to be very seldom if at all used. For 1.4, the changes identified as being in this realm are as follows:
1.x->1.y 发布流程通常会有一些围绕边界的变化,这些变化会稍微更明显,并且基于预期极少使用(如果使用的话)的用例。对于 1.4,被识别为属于此领域的更改如下:

  • The URL object is now immutable - this impacts code that would be manipulating the URL object and may impact code that makes use of the CreateEnginePlugin extension point. This is an uncommon case but may affect in particular some test suites that are making use of special database provisioning logic. A github search for code that uses the relatively new and little-known CreateEnginePlugin class found two projects that were unaffected by the change.
    URL 对象现在不可变 - 这会影响操作 URL 对象的代码,并且可能会影响使用 CreateEnginePlugin 扩展点的代码。这是一个不常见的情况,但可能会影响使用特殊数据库配置逻辑的某些测试套件。对使用相对较新且鲜为人知的 CreateEnginePlugin 类的代码进行的 github 搜索发现两个不受此更改影响的项目。

  • A SELECT statement is no longer implicitly considered to be a FROM clause - this change may impact code that was somehow relying upon behavior that was mostly unusable in the Select construct, where it would create unnamed subqueries that were usually confusing and non-working. These subqueries would be rejected by most databases in any case as a name is usually required except on SQLite, however it is possible some applications will need to adjust some queries that are inadvertently relying upon this.
    SELECT 语句不再被隐式视为 FROM 子句 - 此更改可能会影响以某种方式依赖于 Select 构造中大部分不可用行为的代码,其中它会创建通常令人困惑且不起作用的未命名子查询。在任何情况下,大多数数据库都会拒绝这些子查询,因为通常需要一个名称,但 SQLite 除外,但是一些应用程序可能需要调整无意中依赖于此的一些查询。

  • select().join() and outerjoin() add JOIN criteria to the current query, rather than creating a subquery - somewhat related, the Select class featured .join() and .outerjoin() methods that implicitly created a subquery and then returned a Join construct, which again would be mostly useless and produced lots of confusion. The decision was made to move forward with the vastly more useful 2.0-style join-building approach where these methods now work the same way as the ORM Query.join() method.
    select().join() 和 outerjoin() 向当前查询添加 JOIN 标准,而不是创建子查询 - 有些相关, Select 类具有 .join().outerjoin() 方法,这些方法隐式创建子查询,然后返回 Join 构造,这在大多数情况下都是无用的,并且会产生大量混淆。决定采用更有用的 2.0 风格的连接构建方法,其中这些方法现在的工作方式与 ORM Query.join() 方法相同。

  • Many Core and ORM statement objects now perform much of their construction and validation in the compile phase - some error messages related to construction of a Query or Select may not be emitted until compilation / execution, rather than at construction time. This might impact some test suites that are testing against failure modes.
    许多核心和 ORM 语句对象现在在编译阶段执行大部分构造和验证 - 与构造 QuerySelect 相关的某些错误消息可能不会在构造时发出,而是在编译/执行时发出。这可能会影响针对故障模式进行测试的一些测试套件。

For the full overview of SQLAlchemy 1.4 changes, see the What’s New in SQLAlchemy 1.4? document.
有关 SQLAlchemy 1.4 更改的完整概述,请参阅 SQLAlchemy 1.4 中的新增功能?文档。

Migration to 2.0 Step One - Python 3 only (Python 3.7 minimum for 2.0 compatibility)
迁移到 2.0 第一步 - 仅限 Python 3(2.0 兼容性最低要求 Python 3.7)¶

SQLAlchemy 2.0 was first inspired by the fact that Python 2’s EOL was in 2020. SQLAlchemy is taking a longer period of time than other major projects to drop Python 2.7 support. However, in order to use SQLAlchemy 2.0, the application will need to be runnable on at least Python 3.7. SQLAlchemy 1.4 supports Python 3.6 or newer within the Python 3 series; throughout the 1.4 series, the application can remain running on Python 2.7 or on at least Python 3.6. Version 2.0 however starts at Python 3.7.
SQLAlchemy 2.0 最初的灵感来自于 Python 2 的 EOL 在 2020 年这一事实。SQLAlchemy 花费的时间比其他主要项目更长来放弃对 Python 2.7 的支持。但是,为了使用 SQLAlchemy 2.0,应用程序需要至少在 Python 3.7 上可运行。SQLAlchemy 1.4 在 Python 3 系列中支持 Python 3.6 或更高版本;在整个 1.4 系列中,应用程序可以在 Python 2.7 或至少 Python 3.6 上继续运行。但是,版本 2.0 从 Python 3.7 开始。

Migration to 2.0 Step Two - Turn on RemovedIn20Warnings
迁移到 2.0 步骤二 - 启用 RemovedIn20Warnings ¶

SQLAlchemy 1.4 features a conditional deprecation warning system inspired by the Python “-3” flag that would indicate legacy patterns in a running application. For SQLAlchemy 1.4, the RemovedIn20Warning deprecation class is emitted only when an environment variable SQLALCHEMY_WARN_20 is set to either of true or 1.
SQLAlchemy 1.4 具有受 Python “-3” 标志启发的条件弃用警告系统,该标志将指示正在运行的应用程序中的旧模式。对于 SQLAlchemy 1.4,仅当环境变量 SQLALCHEMY_WARN_20 设置为 true1 之一时,才会发出 RemovedIn20Warning 弃用类。

Given the example program below:
鉴于以下示例程序:

from sqlalchemy import column
from sqlalchemy import create_engine
from sqlalchemy import select
from sqlalchemy import table


engine = create_engine("sqlite://")

engine.execute("CREATE TABLE foo (id integer)")
engine.execute("INSERT INTO foo (id) VALUES (1)")


foo = table("foo", column("id"))
result = engine.execute(select([foo.c.id]))

print(result.fetchall())

The above program uses several patterns that many users will already identify as “legacy”, namely the use of the Engine.execute() method that’s part of the “connectionless execution” API. When we run the above program against 1.4, it returns a single line:
上述程序使用许多用户已经识别为“传统”的几个模式,即使用“无连接执行”API 的一部分 Engine.execute() 方法。当我们针对 1.4 运行上述程序时,它返回一行:

$ python test3.py
[(1,)]

To enable “2.0 deprecations mode”, we enable the SQLALCHEMY_WARN_20=1 variable, and additionally ensure that a warnings filter that will not suppress any warnings is selected:
为了启用“2.0 弃用模式”,我们启用 SQLALCHEMY_WARN_20=1 变量,并另外确保选择一个不会抑制任何警告的警告过滤器:

SQLALCHEMY_WARN_20=1 python -W always::DeprecationWarning test3.py

Since the reported warning location is not always in the correct place, locating the offending code may be difficult without the full stacktrace. This can be achieved by transforming the warnings to exceptions by specifying the error warning filter, using Python option -W error::DeprecationWarning.
由于报告的警告位置并不总是在正确的位置,因此在没有完整堆栈跟踪的情况下,找到有问题的代码可能很困难。可以通过指定 error 警告过滤器,使用 Python 选项 -W error::DeprecationWarning 将警告转换为异常来实现此目的。

With warnings turned on, our program now has a lot to say:
在启用警告后,我们的程序现在有很多话要说:

$ SQLALCHEMY_WARN_20=1 python2 -W always::DeprecationWarning test3.py
test3.py:9: RemovedIn20Warning: The Engine.execute() function/method is considered legacy as of the 1.x series of SQLAlchemy and will be removed in 2.0. All statement execution in SQLAlchemy 2.0 is performed by the Connection.execute() method of Connection, or in the ORM by the Session.execute() method of Session. (Background on SQLAlchemy 2.0 at: https://sqlalche.me/e/b8d9) (Background on SQLAlchemy 2.0 at: https://sqlalche.me/e/b8d9)
  engine.execute("CREATE TABLE foo (id integer)")
/home/classic/dev/sqlalchemy/lib/sqlalchemy/engine/base.py:2856: RemovedIn20Warning: Passing a string to Connection.execute() is deprecated and will be removed in version 2.0.  Use the text() construct, or the Connection.exec_driver_sql() method to invoke a driver-level SQL string. (Background on SQLAlchemy 2.0 at: https://sqlalche.me/e/b8d9)
  return connection.execute(statement, *multiparams, **params)
/home/classic/dev/sqlalchemy/lib/sqlalchemy/engine/base.py:1639: RemovedIn20Warning: The current statement is being autocommitted using implicit autocommit.Implicit autocommit will be removed in SQLAlchemy 2.0.   Use the .begin() method of Engine or Connection in order to use an explicit transaction for DML and DDL statements. (Background on SQLAlchemy 2.0 at: https://sqlalche.me/e/b8d9)
  self._commit_impl(autocommit=True)
test3.py:10: RemovedIn20Warning: The Engine.execute() function/method is considered legacy as of the 1.x series of SQLAlchemy and will be removed in 2.0. All statement execution in SQLAlchemy 2.0 is performed by the Connection.execute() method of Connection, or in the ORM by the Session.execute() method of Session. (Background on SQLAlchemy 2.0 at: https://sqlalche.me/e/b8d9) (Background on SQLAlchemy 2.0 at: https://sqlalche.me/e/b8d9)
  engine.execute("INSERT INTO foo (id) VALUES (1)")
/home/classic/dev/sqlalchemy/lib/sqlalchemy/engine/base.py:2856: RemovedIn20Warning: Passing a string to Connection.execute() is deprecated and will be removed in version 2.0.  Use the text() construct, or the Connection.exec_driver_sql() method to invoke a driver-level SQL string. (Background on SQLAlchemy 2.0 at: https://sqlalche.me/e/b8d9)
  return connection.execute(statement, *multiparams, **params)
/home/classic/dev/sqlalchemy/lib/sqlalchemy/engine/base.py:1639: RemovedIn20Warning: The current statement is being autocommitted using implicit autocommit.Implicit autocommit will be removed in SQLAlchemy 2.0.   Use the .begin() method of Engine or Connection in order to use an explicit transaction for DML and DDL statements. (Background on SQLAlchemy 2.0 at: https://sqlalche.me/e/b8d9)
  self._commit_impl(autocommit=True)
/home/classic/dev/sqlalchemy/lib/sqlalchemy/sql/selectable.py:4271: RemovedIn20Warning: The legacy calling style of select() is deprecated and will be removed in SQLAlchemy 2.0.  Please use the new calling style described at select(). (Background on SQLAlchemy 2.0 at: https://sqlalche.me/e/b8d9) (Background on SQLAlchemy 2.0 at: https://sqlalche.me/e/b8d9)
  return cls.create_legacy_select(*args, **kw)
test3.py:14: RemovedIn20Warning: The Engine.execute() function/method is considered legacy as of the 1.x series of SQLAlchemy and will be removed in 2.0. All statement execution in SQLAlchemy 2.0 is performed by the Connection.execute() method of Connection, or in the ORM by the Session.execute() method of Session. (Background on SQLAlchemy 2.0 at: https://sqlalche.me/e/b8d9) (Background on SQLAlchemy 2.0 at: https://sqlalche.me/e/b8d9)
  result = engine.execute(select([foo.c.id]))
[(1,)]

With the above guidance, we can migrate our program to use 2.0 styles, and as a bonus our program is much clearer:
有了上述指导,我们可以迁移我们的程序以使用 2.0 样式,并且作为奖励,我们的程序更加清晰:

from sqlalchemy import column
from sqlalchemy import create_engine
from sqlalchemy import select
from sqlalchemy import table
from sqlalchemy import text


engine = create_engine("sqlite://")

# don't rely on autocommit for DML and DDL
with engine.begin() as connection:
    # use connection.execute(), not engine.execute()
    # use the text() construct to execute textual SQL
    connection.execute(text("CREATE TABLE foo (id integer)"))
    connection.execute(text("INSERT INTO foo (id) VALUES (1)"))


foo = table("foo", column("id"))

with engine.connect() as connection:
    # use connection.execute(), not engine.execute()
    # select() now accepts column / table expressions positionally
    result = connection.execute(select(foo.c.id))

    print(result.fetchall())

The goal of “2.0 deprecations mode” is that a program which runs with no RemovedIn20Warning warnings with “2.0 deprecations mode” turned on is then ready to run in SQLAlchemy 2.0.
“2.0 弃用模式”的目标是,在启用“2.0 弃用模式”后,不带 RemovedIn20Warning 警告运行的程序即可在 SQLAlchemy 2.0 中运行。

Migration to 2.0 Step Three - Resolve all RemovedIn20Warnings
迁移到 2.0 步骤三 - 解决所有 RemovedIn20Warnings ¶

Code can be developed iteratively to resolve these warnings. Within the SQLAlchemy project itself, the approach taken is as follows:
代码可以迭代开发以解决这些警告。在 SQLAlchemy 项目本身中,采取的方法如下:

  1. enable the SQLALCHEMY_WARN_20=1 environment variable in the test suite, for SQLAlchemy this is in the tox.ini file
    在测试套件中启用 SQLALCHEMY_WARN_20=1 环境变量,对于 SQLAlchemy,这在 tox.ini 文件中

  2. Within the setup for the test suite, set up a series of warnings filters that will select for particular subsets of warnings to either raise an exception, or to be ignored (or logged). Work with just one subgroup of warnings at a time. Below, a warnings filter is configured for an application where the change to the Core level .execute() calls will be needed in order for all tests to pass, but all other 2.0-style warnings will be suppressed:
    在测试套件的设置中,设置一系列警告过滤器,这些过滤器将选择特定警告子集,以引发异常或被忽略(或记录)。一次只处理一个警告子组。下面,为应用程序配置了一个警告过滤器,其中需要对核心级别 .execute() 调用的更改才能使所有测试通过,但所有其他 2.0 样式警告都将被禁止:

    import warnings
    from sqlalchemy import exc
    
    # for warnings not included in regex-based filter below, just log
    warnings.filterwarnings("always", category=exc.RemovedIn20Warning)
    
    # for warnings related to execute() / scalar(), raise
    for msg in [
        r"The (?:Executable|Engine)\.(?:execute|scalar)\(\) function",
        r"The current statement is being autocommitted using implicit autocommit,",
        r"The connection.execute\(\) method in SQLAlchemy 2.0 will accept "
        "parameters as a single dictionary or a single sequence of "
        "dictionaries only.",
        r"The Connection.connect\(\) function/method is considered legacy",
        r".*DefaultGenerator.execute\(\)",
    ]:
        warnings.filterwarnings(
            "error",
            message=msg,
            category=exc.RemovedIn20Warning,
        )
  3. As each sub-category of warnings are resolved in the application, new warnings that are caught by the “always” filter can be added to the list of “errors” to be resolved.
    随着应用程序中每个子类别的警告得到解决,可以将“始终”过滤器捕获的新警告添加到要解决的“错误”列表中。

  4. Once no more warnings are emitted, the filter can be removed.
    一旦不再发出警告,就可以移除该过滤器。

Migration to 2.0 Step Four - Use the future flag on Engine
迁移到 2.0 第四步 - 在 Engine 上使用 future 标志 ¶

The Engine object features an updated transaction-level API in version 2.0. In 1.4, this new API is available by passing the flag future=True to the create_engine() function.
Engine 对象在 2.0 版本中具有更新的事务级 API。在 1.4 中,此新 API 可通过将标志 future=True 传递给 create_engine() 函数来使用。

When the create_engine.future flag is used, the Engine and Connection objects support the 2.0 API fully and not at all any legacy features, including the new argument format for Connection.execute(), the removal of “implicit autocommit”, string statements require the text() construct unless the Connection.exec_driver_sql() method is used, and connectionless execution from the Engine is removed.
当使用 create_engine.future 标志时, EngineConnection 对象完全支持 2.0 API,并且完全不支持任何旧功能,包括 Connection.execute() 的新参数格式,删除“隐式自动提交”,字符串语句需要 text() 构造,除非使用 Connection.exec_driver_sql() 方法,并且从 Engine 中删除无连接执行。

If all RemovedIn20Warning warnings have been resolved regarding use of the Engine and Connection, then the create_engine.future flag may be enabled and there should be no errors raised.
如果所有 RemovedIn20Warning 警告都已解决,关于使用 EngineConnection ,那么 create_engine.future 标志可以启用,并且不应该引发任何错误。

The new engine is described at Engine which delivers a new Connection object. In addition to the above changes, the, Connection object features Connection.commit() and Connection.rollback() methods, to support the new “commit-as-you-go” mode of operation:
新引擎在 Engine 中进行了描述,它提供了一个新的 Connection 对象。除了上述更改之外, Connection 对象具有 Connection.commit()Connection.rollback() 方法,以支持新的“边提交边进行”操作模式:

from sqlalchemy import create_engine

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

with engine.connect() as conn:
    conn.execute(text("insert into table (x) values (:some_x)"), {"some_x": 10})

    conn.commit()  # commit as you go

Migration to 2.0 Step Five - Use the future flag on Session
迁移到 2.0 第五步 - 在会话中使用 future 标志

The Session object also features an updated transaction/connection level API in version 2.0. This API is available in 1.4 using the Session.future flag on Session or on sessionmaker.
Session 对象在 2.0 版本中还具有更新的事务/连接级别 API。此 API 在 1.4 中使用 Session.future 标记在 Sessionsessionmaker 上可用。

The Session object supports “future” mode in place, and involves these changes:
Session 对象支持就地“未来”模式,涉及以下更改:

  1. The Session no longer supports “bound metadata” when it resolves the engine to be used for connectivity. This means that an Engine object must be passed to the constructor (this may be either a legacy or future style object).
    Session 在解析用于连接的引擎时不再支持“绑定元数据”。这意味着必须将 Engine 对象传递给构造函数(这可能是旧式或未来式对象)。

  2. The Session.begin.subtransactions flag is no longer supported.
    Session.begin.subtransactions 标志不再受支持。

  3. The Session.commit() method always emits a COMMIT to the database, rather than attempting to reconcile “subtransactions”.
    Session.commit() 方法始终向数据库发出 COMMIT,而不是尝试协调“子事务”。

  4. The Session.rollback() method always rolls back the full stack of transactions at once, rather than attempting to keep “subtransactions” in place.
    Session.rollback() 方法始终一次回滚整个事务堆栈,而不是尝试保留“子事务”。

The Session also supports more flexible creational patterns in 1.4 which are now closely matched to the patterns used by the Connection object. Highlights include that the Session may be used as a context manager:
Session 还支持 1.4 中更灵活的创建模式,这些模式现在与 Connection 对象使用的模式紧密匹配。重点包括 Session 可用作上下文管理器:

from sqlalchemy.orm import Session

with Session(engine) as session:
    session.add(MyObject())
    session.commit()

In addition, the sessionmaker object supports a sessionmaker.begin() context manager that will create a Session and begin /commit a transaction in one block:
此外, sessionmaker 对象支持一个 sessionmaker.begin() 上下文管理器,它将创建一个 Session 并在一个块中开始/提交一个事务:

from sqlalchemy.orm import sessionmaker

Session = sessionmaker(engine)

with Session.begin() as session:
    session.add(MyObject())

See the section Session-level vs. Engine level transaction control for a comparison of Session creational patterns compared to those of Connection.
有关 Session 创建模式与 Connection 创建模式的比较,请参阅会话级与引擎级事务控制部分。

Once the application passes all tests/ runs with SQLALCHEMY_WARN_20=1 and all exc.RemovedIn20Warning occurrences set to raise an error, the application is ready!.
一旦应用程序通过所有测试/运行,其中 SQLALCHEMY_WARN_20=1 和所有 exc.RemovedIn20Warning 出现时都会引发错误,则应用程序就准备好了!

The sections that follow will detail the specific changes to make for all major API modifications.
以下部分将详细说明对所有主要 API 修改所做的具体更改。

Migration to 2.0 Step Six - Add __allow_unmapped__ to explicitly typed ORM models
迁移到 2.0 步骤六 - 向显式类型化 ORM 模型添加 __allow_unmapped__

SQLAlchemy 2.0 has new support for runtime interpretation of PEP 484 typing annotations on ORM models. A requirement of these annotations is that they must make use of the Mapped generic container. Annotations which don’t use Mapped which link to constructs such as relationship() will raise errors in Python, as they suggest mis-configurations.
SQLAlchemy 2.0 对 ORM 模型上的 PEP 484 类型注释的运行时解释提供了新的支持。这些注释的要求是它们必须使用 Mapped 通用容器。不使用 Mapped 的注释(链接到诸如 relationship() 之类的构造)将在 Python 中引发错误,因为它们表明配置错误。

SQLAlchemy applications that use the Mypy plugin with explicit annotations that don’t use Mapped in their annotations are subject to these errors, as would occur in the example below:
使用 Mypy 插件的 SQLAlchemy 应用程序,其显式注释中不使用 Mapped ,会遇到这些错误,如下例所示:

Base = declarative_base()


class Foo(Base):
    __tablename__ = "foo"

    id: int = Column(Integer, primary_key=True)

    # will raise
    bars: List["Bar"] = relationship("Bar", back_populates="foo")


class Bar(Base):
    __tablename__ = "bar"

    id: int = Column(Integer, primary_key=True)
    foo_id = Column(ForeignKey("foo.id"))

    # will raise
    foo: Foo = relationship(Foo, back_populates="bars", cascade="all")

Above, the Foo.bars and Bar.foo relationship() declarations will raise an error at class construction time because they don’t use Mapped (by contrast, the annotations that use Column are ignored by 2.0, as these are able to be recognized as a legacy configuration style). To allow all annotations that don’t use Mapped to pass without error, the __allow_unmapped__ attribute may be used on the class or any subclasses, which will cause the annotations in these cases to be ignored completely by the new Declarative system.
在上面, Foo.barsBar.foo relationship() 声明将在类构造时间引发错误,因为它们不使用 Mapped (相比之下,使用 Column 的注释被 2.0 忽略,因为这些注释能够被识别为一种旧配置样式)。要允许所有不使用 Mapped 的注释通过而不报错,可以在类或任何子类上使用 __allow_unmapped__ 属性,这将导致这些情况下的注释被新的声明式系统完全忽略。

Note 注意

The __allow_unmapped__ directive applies only to the runtime behavior of the ORM. It does not affect the behavior of Mypy, and the above mapping as written still requires that the Mypy plugin be installed. For fully 2.0 style ORM models that will type correctly under Mypy without a plugin, follow the migration steps at Migrating an Existing Mapping.
__allow_unmapped__ 指令仅适用于 ORM 的运行时行为。它不会影响 Mypy 的行为,并且如上所述的映射仍然需要安装 Mypy 插件。对于在没有插件的情况下在 Mypy 下类型正确的完全 2.0 样式 ORM 模型,请按照迁移现有映射中的迁移步骤进行操作。

The example below illustrates the application of __allow_unmapped__ to the Declarative Base class, where it will take effect for all classes that descend from Base:
以下示例说明了将 __allow_unmapped__ 应用于声明性 Base 类,它将对从 Base 派生的所有类生效:

# qualify the base with __allow_unmapped__.  Can also be
# applied to classes directly if preferred
class Base:
    __allow_unmapped__ = True


Base = declarative_base(cls=Base)


# existing mapping proceeds, Declarative will ignore any annotations
# which don't include ``Mapped[]``
class Foo(Base):
    __tablename__ = "foo"

    id: int = Column(Integer, primary_key=True)

    bars: List["Bar"] = relationship("Bar", back_populates="foo")


class Bar(Base):
    __tablename__ = "bar"

    id: int = Column(Integer, primary_key=True)
    foo_id = Column(ForeignKey("foo.id"))

    foo: Foo = relationship(Foo, back_populates="bars", cascade="all")

Changed in version 2.0.0beta3: - improved the __allow_unmapped__ attribute support to allow for 1.4-style explicit annotated relationships that don’t use Mapped to remain usable.
在版本 2.0.0beta3 中更改:- 改进了 __allow_unmapped__ 属性支持,以允许使用不使用 Mapped 的 1.4 风格显式注释关系保持可用。

Migration to 2.0 Step Seven - Test against a SQLAlchemy 2.0 Release
迁移到 2.0 第七步 - 测试 SQLAlchemy 2.0 版本 ¶

As mentioned previously, SQLAlchemy 2.0 has additional API and behavioral changes that are intended to be backwards compatible, however may introduce some incompatibilities nonetheless. Therefore after the overall porting process is complete, the final step is to test against the most recent release of SQLAlchemy 2.0 to correct for any remaining issues that might be present.
如前所述,SQLAlchemy 2.0 具有其他 API 和行为更改,旨在向后兼容,但仍可能引入一些不兼容性。因此,在整个移植过程完成后,最后一步是针对 SQLAlchemy 2.0 的最新版本进行测试,以纠正可能存在的任何剩余问题。

The guide at What’s New in SQLAlchemy 2.0? provides an overview of new features and behaviors for SQLAlchemy 2.0 which extend beyond the base set of 1.4->2.0 API changes.
SQLAlchemy 2.0 中的新增功能指南提供了 SQLAlchemy 2.0 的新功能和行为的概述,这些功能和行为超出了 1.4->2.0 API 更改的基本集。

2.0 Migration - Core Connection / Transaction
2.0 迁移 - 核心连接/事务 ¶

Library-level (but not driver level) “Autocommit” removed from both Core and ORM
库级别(但不是驱动程序级别)“自动提交”已从 Core 和 ORM 中移除 ¶

Synopsis 简介

In SQLAlchemy 1.x, the following statements will automatically commit the underlying DBAPI transaction, but in SQLAlchemy 2.0 this will not occur:
在 SQLAlchemy 1.x 中,以下语句将自动提交底层 DBAPI 事务,但在 SQLAlchemy 2.0 中不会发生这种情况:

conn = engine.connect()

# won't autocommit in 2.0
conn.execute(some_table.insert().values(foo="bar"))

Nor will this autocommit:
也不会自动提交:

conn = engine.connect()

# won't autocommit in 2.0
conn.execute(text("INSERT INTO table (foo) VALUES ('bar')"))

The common workaround for custom DML that requires commit, the “autocommit” execution option, will be removed:
需要提交的自定义 DML 的常见解决方法,“autocommit” 执行选项将被移除:

conn = engine.connect()

# won't autocommit in 2.0
conn.execute(text("EXEC my_procedural_thing()").execution_options(autocommit=True))

Migration to 2.0 迁移到 2.0

The method that is cross-compatible with 1.x style and 2.0 style execution is to make use of the Connection.begin() method, or the Engine.begin() context manager:
与 1.x 样式和 2.0 样式执行交叉兼容的方法是使用 Connection.begin() 方法或 Engine.begin() 上下文管理器:

with engine.begin() as conn:
    conn.execute(some_table.insert().values(foo="bar"))
    conn.execute(some_other_table.insert().values(bat="hoho"))

with engine.connect() as conn:
    with conn.begin():
        conn.execute(some_table.insert().values(foo="bar"))
        conn.execute(some_other_table.insert().values(bat="hoho"))

with engine.begin() as conn:
    conn.execute(text("EXEC my_procedural_thing()"))

When using 2.0 style with the create_engine.future flag, “commit as you go” style may also be used, as the Connection features autobegin behavior, which takes place when a statement is first invoked in the absence of an explicit call to Connection.begin():
当使用带有 create_engine.future 标志的 2.0 样式时,也可以使用“边提交边进行”样式,因为 Connection 特性具有自动开始行为,当在没有显式调用 Connection.begin() 的情况下首次调用语句时,就会发生这种情况:

with engine.connect() as conn:
    conn.execute(some_table.insert().values(foo="bar"))
    conn.execute(some_other_table.insert().values(bat="hoho"))

    conn.commit()

When 2.0 deprecations mode is enabled, a warning will emit when the deprecated “autocommit” feature takes place, indicating those places where an explicit transaction should be noted.
当启用 2.0 弃用模式时,在弃用的“autocommit”功能发生时会发出警告,指示应注意显式事务的位置。

Discussion 讨论

SQLAlchemy’s first releases were at odds with the spirit of the Python DBAPI (PEP 249) in that it tried to hide PEP 249’s emphasis on “implicit begin” and “explicit commit” of transactions. Fifteen years later we now see this was essentially a mistake, as SQLAlchemy’s many patterns that attempt to “hide” the presence of a transaction make for a more complex API which works inconsistently and is extremely confusing to especially those users who are new to relational databases and ACID transactions in general. SQLAlchemy 2.0 will do away with all attempts to implicitly commit transactions, and usage patterns will always require that the user demarcate the “beginning” and the “end” of a transaction in some way, in the same way as reading or writing to a file in Python has a “beginning” and an “end”.
SQLAlchemy 的第一个版本与 Python DBAPI(PEP 249)的精神不符,因为它试图隐藏 PEP 249 对事务的“隐式开始”和“显式提交”的强调。十五年后,我们现在看到这本质上是一个错误,因为 SQLAlchemy 的许多试图“隐藏”事务存在的模式导致了一个更复杂的 API,该 API 工作不一致,并且对于那些不熟悉关系数据库和 ACID 事务的用户来说尤其令人困惑。SQLAlchemy 2.0 将取消所有隐式提交事务的尝试,并且使用模式将始终要求用户以某种方式划分事务的“开始”和“结束”,就像在 Python 中读写文件有“开始”和“结束”一样。

In the case of autocommit for a pure textual statement, there is actually a regular expression that parses every statement in order to detect autocommit! Not surprisingly, this regex is continuously failing to accommodate for various kinds of statements and stored procedures that imply a “write” to the database, leading to ongoing confusion as some statements produce results in the database and others don’t. By preventing the user from being aware of the transactional concept, we get a lot of bug reports on this one because users don’t understand that databases always use a transaction, whether or not some layer is autocommitting it.
对于纯文本语句的自动提交,实际上有一个正则表达式来解析每个语句以检测自动提交!毫不奇怪,此正则表达式持续无法适应暗示对数据库进行“写入”的各种类型的语句和存储过程,从而导致持续的混乱,因为某些语句在数据库中产生结果而另一些语句则没有。通过防止用户意识到事务概念,我们收到了很多关于此问题的错误报告,因为用户不理解数据库始终使用事务,无论是否有一些层正在自动提交它。

SQLAlchemy 2.0 will require that all database actions at every level be explicit as to how the transaction should be used. For the vast majority of Core use cases, it’s the pattern that is already recommended:
SQLAlchemy 2.0 将要求在每个级别的所有数据库操作都明确说明如何使用事务。对于绝大多数 Core 用例,这是已经推荐的模式:

with engine.begin() as conn:
    conn.execute(some_table.insert().values(foo="bar"))

For “commit as you go, or rollback instead” usage, which resembles how the Session is normally used today, the “future” version of Connection, which is the one that is returned from an Engine that was created using the create_engine.future flag, includes new Connection.commit() and Connection.rollback() methods, which act upon a transaction that is now begun automatically when a statement is first invoked:
对于“边提交边进行,或回滚”用法,它类似于 Session 在当今的常规用法, Connection 的“未来”版本(即使用 create_engine.future 标志创建的 Engine 返回的版本)包含新的 Connection.commit()Connection.rollback() 方法,这些方法作用于在首次调用语句时自动开始的事务:

# 1.4 / 2.0 code

from sqlalchemy import create_engine

engine = create_engine(..., future=True)

with engine.connect() as conn:
    conn.execute(some_table.insert().values(foo="bar"))
    conn.commit()

    conn.execute(text("some other SQL"))
    conn.rollback()

Above, the engine.connect() method will return a Connection that features autobegin, meaning the begin() event is emitted when the execute method is first used (note however that there is no actual “BEGIN” in the Python DBAPI). “autobegin” is a new pattern in SQLAlchemy 1.4 that is featured both by Connection as well as the ORM Session object; autobegin allows that the Connection.begin() method may be called explicitly when the object is first acquired, for schemes that wish to demarcate the beginning of the transaction, but if the method is not called, then it occurs implicitly when work is first done on the object.
在上面, engine.connect() 方法将返回一个具有自动开始功能的 Connection ,这意味着在首次使用 execute 方法时会发出 begin() 事件(但请注意,Python DBAPI 中没有实际的“BEGIN”)。“autobegin”是 SQLAlchemy 1.4 中的新模式, Connection 和 ORM Session 对象都具有该模式;autobegin 允许在首次获取对象时显式调用 Connection.begin() 方法,以用于希望限定事务开始的方案,但如果未调用该方法,则在首次对对象执行工作时会隐式发生。

The removal of “autocommit” is closely related to the removal of “connectionless” execution discussed at “Implicit” and “Connectionless” execution, “bound metadata” removed. All of these legacy patterns built up from the fact that Python did not have context managers or decorators when SQLAlchemy was first created, so there were no convenient idiomatic patterns for demarcating the use of a resource.
“autocommit” 的移除与在 “隐式” 和 “无连接” 执行中讨论的 “无连接” 执行的移除密切相关,“绑定元数据” 已移除。所有这些遗留模式都是基于这样一个事实:当 SQLAlchemy 首次创建时,Python 没有上下文管理器或装饰器,因此没有方便的惯用模式来界定资源的使用。

Driver-level autocommit remains available
驱动程序级自动提交仍然可用 ¶

True “autocommit” behavior is now widely available with most DBAPI implementations, and is supported by SQLAlchemy via the Connection.execution_options.isolation_level parameter as discussed at Setting Transaction Isolation Levels including DBAPI Autocommit. True autocommit is treated as an “isolation level” so that the structure of application code does not change when autocommit is used; the Connection.begin() context manager as well as methods like Connection.commit() may still be used, they are simply no-ops at the database driver level when DBAPI-level autocommit is turned on.
真正的“自动提交”行为现在已广泛用于大多数 DBAPI 实现,并通过 Connection.execution_options.isolation_level 参数(如在设置事务隔离级别(包括 DBAPI 自动提交)中所述)得到 SQLAlchemy 的支持。真正的自动提交被视为“隔离级别”,以便在使用自动提交时应用程序代码的结构不会发生改变; Connection.begin() 上下文管理器以及 Connection.commit() 等方法仍然可以使用,当 DBAPI 级别的自动提交被打开时,它们在数据库驱动程序级别只是无操作。

“Implicit” and “Connectionless” execution, “bound metadata” removed
“隐式”和“无连接”执行,“绑定元数据”已移除 ¶

Synopsis 概要

The ability to associate an Engine with a MetaData object, which then makes available a range of so-called “connectionless” execution patterns, is removed:
EngineMetaData 对象关联的能力,然后提供一系列所谓的“无连接”执行模式,已被移除:

from sqlalchemy import MetaData

metadata_obj = MetaData(bind=engine)  # no longer supported

metadata_obj.create_all()  # requires Engine or Connection

metadata_obj.reflect()  # requires Engine or Connection

t = Table("t", metadata_obj, autoload=True)  # use autoload_with=engine

result = engine.execute(t.select())  # no longer supported

result = t.select().execute()  # no longer supported

Migration to 2.0 迁移到 2.0

For schema level patterns, explicit use of an Engine or Connection is required. The Engine may still be used directly as the source of connectivity for a MetaData.create_all() operation or autoload operation. For executing statements, only the Connection object has a Connection.execute() method (in addition to the ORM-level Session.execute() method):
对于模式级别模式,需要明确使用 EngineConnection 。仍可直接将 Engine 用作 MetaData.create_all() 操作或自动加载操作的连接源。对于执行语句,只有 Connection 对象具有 Connection.execute() 方法(除了 ORM 级别的 Session.execute() 方法):

from sqlalchemy import MetaData

metadata_obj = MetaData()

# engine level:

# create tables
metadata_obj.create_all(engine)

# reflect all tables
metadata_obj.reflect(engine)

# reflect individual table
t = Table("t", metadata_obj, autoload_with=engine)


# connection level:


with engine.connect() as connection:
    # create tables, requires explicit begin and/or commit:
    with connection.begin():
        metadata_obj.create_all(connection)

    # reflect all tables
    metadata_obj.reflect(connection)

    # reflect individual table
    t = Table("t", metadata_obj, autoload_with=connection)

    # execute SQL statements
    result = connection.execute(t.select())

Discussion 讨论

The Core documentation has already standardized on the desired pattern here, so it is likely that most modern applications would not have to change much in any case, however there are likely many applications that still rely upon engine.execute() calls that will need to be adjusted.
核心文档已经在此标准化了所需的模式,因此大多数现代应用程序可能无论如何都不必进行太多更改,但是可能仍有许多应用程序依赖于需要调整的 engine.execute() 调用。

“Connectionless” execution refers to the still fairly popular pattern of invoking .execute() from the Engine:
“无连接”执行是指从 Engine 调用 .execute() 的仍然相当流行的模式:

result = engine.execute(some_statement)

The above operation implicitly procures a Connection object, and runs the .execute() method on it. While this appears to be a simple convenience feature, it has been shown to give rise to several issues:
上述操作隐式获取一个 Connection 对象,并运行其 .execute() 方法。虽然这看起来是一个简单的便利功能,但已证明它会引发几个问题:

  • Programs that feature extended strings of engine.execute() calls have become prevalent, overusing a feature that was intended to be seldom used and leading to inefficient non-transactional applications. New users are confused as to the difference between engine.execute() and connection.execute() and the nuance between these two approaches is often not understood.
    具有 engine.execute() 调用扩展字符串的程序变得普遍,过度使用本应很少使用的功能,并导致低效的非事务性应用程序。新用户对 engine.execute()connection.execute() 之间的区别感到困惑,并且通常不理解这两种方法之间的细微差别。

  • The feature relies upon the “application level autocommit” feature in order to make sense, which itself is also being removed as it is also inefficient and misleading.
    该特性依赖于“应用程序级别自动提交”特性才能有意义,而该特性本身也因效率低下且具有误导性而被移除。

  • In order to handle result sets, Engine.execute returns a result object with unconsumed cursor results. This cursor result necessarily still links to the DBAPI connection which remains in an open transaction, all of which is released once the result set has fully consumed the rows waiting within the cursor. This means that Engine.execute does not actually close out the connection resources that it claims to be managing when the call is complete. SQLAlchemy’s “autoclose” behavior is well-tuned enough that users don’t generally report any negative effects from this system, however it remains an overly implicit and inefficient system left over from SQLAlchemy’s earliest releases.
    为了处理结果集, Engine.execute 返回一个带有未消耗游标结果的结果对象。此游标结果必然仍然链接到保持在打开事务中的 DBAPI 连接,所有这些连接一旦结果集完全消耗了游标中等待的行,就会被释放。这意味着 Engine.execute 在调用完成后实际上并未关闭其声称在管理的连接资源。SQLAlchemy 的“自动关闭”行为经过充分调整,以至于用户通常不会报告此系统有任何负面影响,但它仍然是 SQLAlchemy 最早版本遗留下来的过于隐式且低效的系统。

The removal of “connectionless” execution then leads to the removal of an even more legacy pattern, that of “implicit, connectionless” execution:
“无连接”执行的移除随后导致了移除一个更传统的模式,即“隐式、无连接”执行:

result = some_statement.execute()

The above pattern has all the issues of “connectionless” execution, plus it relies upon the “bound metadata” pattern, which SQLAlchemy has tried to de-emphasize for many years. This was SQLAlchemy’s very first advertised usage model in version 0.1, which became obsolete almost immediately when the Connection object was introduced and later Python context managers provided a better pattern for using resources within a fixed scope.
上述模式具有“无连接”执行的所有问题,此外它还依赖于“绑定元数据”模式,而 SQLAlchemy 多年来一直试图淡化该模式。这是 SQLAlchemy 在 0.1 版中首次宣传的使用模型,当 Connection 对象引入后,它几乎立即变得过时,后来 Python 上下文管理器为在固定范围内使用资源提供了更好的模式。

With implicit execution removed, “bound metadata” itself also no longer has a purpose within this system. In modern use “bound metadata” tends to still be somewhat convenient for working within MetaData.create_all() calls as well as with Session objects, however having these functions receive an Engine explicitly provides for clearer application design.
随着隐式执行的移除,“绑定元数据”本身在这个系统中也不再有目的。在现代用法中,“绑定元数据”仍然在 MetaData.create_all() 调用以及 Session 对象中工作时比较方便,但是让这些函数接收一个 Engine 明确地提供了更清晰的应用程序设计。

Many Choices becomes One Choice
众多选择变为唯一选择 ¶

Overall, the above executional patterns were introduced in SQLAlchemy’s very first 0.1 release before the Connection object even existed. After many years of de-emphasizing these patterns, “implicit, connectionless” execution and “bound metadata” are no longer as widely used so in 2.0 we seek to finally reduce the number of choices for how to execute a statement in Core from “many choices”:
总体而言,上述执行模式在 SQLAlchemy 最早的 0.1 版本中引入,甚至在 Connection 对象存在之前。在多年来淡化这些模式后,“隐式、无连接”执行和“绑定元数据”不再被广泛使用,因此在 2.0 中,我们寻求最终减少在 Core 中执行语句的方式的选择数量,从“多种选择”中:

# many choices

# bound metadata?
metadata_obj = MetaData(engine)

# or not?
metadata_obj = MetaData()

# execute from engine?
result = engine.execute(stmt)

# or execute the statement itself (but only if you did
# "bound metadata" above, which means you can't get rid of "bound" if any
# part of your program uses this form)
result = stmt.execute()

# execute from connection, but it autocommits?
conn = engine.connect()
conn.execute(stmt)

# execute from connection, but autocommit isn't working, so use the special
# option?
conn.execution_options(autocommit=True).execute(stmt)

# or on the statement ?!
conn.execute(stmt.execution_options(autocommit=True))

# or execute from connection, and we use explicit transaction?
with conn.begin():
    conn.execute(stmt)

to “one choice”, where by “one choice” we mean “explicit connection with explicit transaction”; there are still a few ways to demarcate transaction blocks depending on need. The “one choice” is to procure a Connection and then to explicitly demarcate the transaction, in the case that the operation is a write operation:
“一个选择”,其中“一个选择”是指“显式连接与显式事务”;根据需要,仍然有几种方法来划分事务块。“一个选择”是获取 Connection ,然后在操作为写操作的情况下显式地划分事务:

# one choice - work with explicit connection, explicit transaction
# (there remain a few variants on how to demarcate the transaction)

# "begin once" - one transaction only per checkout
with engine.begin() as conn:
    result = conn.execute(stmt)

# "commit as you go" - zero or more commits per checkout
with engine.connect() as conn:
    result = conn.execute(stmt)
    conn.commit()

# "commit as you go" but with a transaction block instead of autobegin
with engine.connect() as conn:
    with conn.begin():
        result = conn.execute(stmt)

execute() method more strict, execution options are more prominent
execute() 方法更严格,执行选项更突出 ¶

Synopsis 简介

The argument patterns that may be used with the sqlalchemy.engine.Connection() execute method in SQLAlchemy 2.0 are highly simplified, removing many previously available argument patterns. The new API in the 1.4 series is described at sqlalchemy.engine.Connection(). The examples below illustrate the patterns that require modification:
在 SQLAlchemy 2.0 中,可与 sqlalchemy.engine.Connection() execute 方法一起使用的参数模式已高度简化,移除了许多以前可用的参数模式。1.4 系列中的新 API 在 sqlalchemy.engine.Connection() 中进行了描述。以下示例说明了需要修改的模式:

connection = engine.connect()

# direct string SQL not supported; use text() or exec_driver_sql() method
result = connection.execute("select * from table")

# positional parameters no longer supported, only named
# unless using exec_driver_sql()
result = connection.execute(table.insert(), ("x", "y", "z"))

# **kwargs no longer accepted, pass a single dictionary
result = connection.execute(table.insert(), x=10, y=5)

# multiple *args no longer accepted, pass a list
result = connection.execute(
    table.insert(), {"x": 10, "y": 5}, {"x": 15, "y": 12}, {"x": 9, "y": 8}
)

Migration to 2.0 迁移到 2.0

The new Connection.execute() method now accepts a subset of the argument styles that are accepted by the 1.x Connection.execute() method, so the following code is cross-compatible between 1.x and 2.0:
新的 Connection.execute() 方法现在接受 1.x Connection.execute() 方法接受的部分参数样式,因此以下代码在 1.x 和 2.0 之间是交叉兼容的:

connection = engine.connect()

from sqlalchemy import text

result = connection.execute(text("select * from table"))

# pass a single dictionary for single statement execution
result = connection.execute(table.insert(), {"x": 10, "y": 5})

# pass a list of dictionaries for executemany
result = connection.execute(
    table.insert(), [{"x": 10, "y": 5}, {"x": 15, "y": 12}, {"x": 9, "y": 8}]
)

Discussion 讨论

The use of *args and **kwargs has been removed both to remove the complexity of guessing what kind of arguments were passed to the method, as well as to make room for other options, namely the Connection.execute.execution_options dictionary that is now available to provide options on a per statement basis. The method is also modified so that its use pattern matches that of the Session.execute() method, which is a much more prominent API in 2.0 style.
*args**kwargs 的使用已被删除,既是为了消除猜测传递给方法的参数类型的复杂性,也是为了为其他选项腾出空间,即现在可用于按语句提供选项的 Connection.execute.execution_options 字典。该方法还经过修改,使其使用模式与 Session.execute() 方法的模式相匹配,后者是 2.0 样式中更突出的 API。

The removal of direct string SQL is to resolve an inconsistency between Connection.execute() and Session.execute(), where in the former case the string is passed to the driver raw, and in the latter case it is first converted to a text() construct. By allowing only text() this also limits the accepted parameter format to “named” and not “positional”. Finally, the string SQL use case is becoming more subject to scrutiny from a security perspective, and the text() construct has come to represent an explicit boundary into the textual SQL realm where attention to untrusted user input must be given.
删除直接字符串 SQL 是为了解决 Connection.execute()Session.execute() 之间的不一致,在第一种情况下,字符串被原样传递给驱动程序,而在第二种情况下,它首先被转换为 text() 构造。通过仅允许 text() ,这也将接受的参数格式限制为“命名”而不是“位置”。最后,从安全角度来看,字符串 SQL 用例正变得越来越容易受到审查,并且 text() 构造已经成为文本 SQL 领域中明确边界的代表,在该领域中必须注意不受信任的用户输入。

Result rows act like named tuples
结果行就像命名元组 ¶

Synopsis 简介

Version 1.4 introduces an all new Result object that in turn returns Row objects, which behave like named tuples when using “future” mode:
版本 1.4 引入了全新的 Result 对象,它反过来返回 Row 对象,在使用“future”模式时,这些对象的行为类似于命名元组:

engine = create_engine(..., future=True)  # using future mode

with engine.connect() as conn:
    result = conn.execute(text("select x, y from table"))

    row = result.first()  # suppose the row is (1, 2)

    "x" in row  # evaluates to False, in 1.x / future=False, this would be True

    1 in row  # evaluates to True, in 1.x / future=False, this would be False

Migration to 2.0 迁移到 2.0

Application code or test suites that are testing for a particular key being present in a row would need to test the row.keys() collection instead. This is however an unusual use case as a result row is typically used by code that already knows what columns are present within it.
应用程序代码或测试套件在测试行中是否存在特定键时,需要改为测试 row.keys() 集合。然而,这是一个不常见的用例,因为结果行通常由已经知道其中存在哪些列的代码使用。

Discussion 讨论

Already part of 1.4, the previous KeyedTuple class that was used when selecting rows from the Query object has been replaced by the Row class, which is the base of the same Row that comes back with Core statement results when using the create_engine.future flag with Engine (when the create_engine.future flag is not set, Core result sets use the LegacyRow subclass, which maintains backwards-compatible behaviors for the __contains__() method; ORM exclusively uses the Row class directly).
在 1.4 中,以前用于从 Query 对象中选择行的 KeyedTuple 类已被 Row 类替换,后者是使用 Engine 标记时 create_engine.future 返回的相同 Row 的基础(当未设置 create_engine.future 标记时,Core 结果集使用 LegacyRow 子类,该子类为 __contains__() 方法维护向后兼容的行为;ORM 直接使用 Row 类)。

This Row behaves like a named tuple, in that it acts as a sequence but also supports attribute name access, e.g. row.some_column. However, it also provides the previous “mapping” behavior via the special attribute row._mapping, which produces a Python mapping such that keyed access such as row["some_column"] can be used.
Row 的行为类似于命名元组,因为它充当序列,但也支持属性名称访问,例如 row.some_column 。但是,它还通过特殊属性 row._mapping 提供了先前的“映射”行为,该属性生成 Python 映射,以便可以使用键控访问,例如 row["some_column"]

In order to receive results as mappings up front, the mappings() modifier on the result can be used:
为了预先接收结果作为映射,可以使用结果上的 mappings() 修饰符:

from sqlalchemy.future.orm import Session

session = Session(some_engine)

result = session.execute(stmt)
for row in result.mappings():
    print("the user is: %s" % row["User"])

The Row class as used by the ORM also supports access via entity or attribute:
Row 类由 ORM 使用,还支持通过实体或属性进行访问:

from sqlalchemy.future import select

stmt = select(User, Address).join(User.addresses)

for row in session.execute(stmt).mappings():
    print("the user is: %s  the address is: %s" % (row[User], row[Address]))

2.0 Migration - Core Usage
2.0 迁移 - 核心用法 ¶

select() no longer accepts varied constructor arguments, columns are passed positionally
select() 不再接受不同的构造函数参数,列按位置传递 ¶

synopsis 概要

The select() construct as well as the related method FromClause.select() will no longer accept keyword arguments to build up elements such as the WHERE clause, FROM list and ORDER BY. The list of columns may now be sent positionally, rather than as a list. Additionally, the case() construct now accepts its WHEN criteria positionally, rather than as a list:
select() 构造以及相关方法 FromClause.select() 将不再接受关键字参数来构建元素,例如 WHERE 子句、FROM 列表和 ORDER BY。现在可以按位置发送列列表,而不是作为列表发送。此外, case() 构造现在按位置接受其 WHEN 条件,而不是作为列表接受:

# select_from / order_by keywords no longer supported
stmt = select([1], select_from=table, order_by=table.c.id)

# whereclause parameter no longer supported
stmt = select([table.c.x], table.c.id == 5)

# whereclause parameter no longer supported
stmt = table.select(table.c.id == 5)

# list emits a deprecation warning
stmt = select([table.c.x, table.c.y])

# list emits a deprecation warning
case_clause = case(
    [(table.c.x == 5, "five"), (table.c.x == 7, "seven")],
    else_="neither five nor seven",
)

Migration to 2.0 迁移到 2.0

Only the “generative” style of select() will be supported. The list of columns / tables to SELECT from should be passed positionally. The select() construct in SQLAlchemy 1.4 accepts both the legacy styles and the new styles using an auto-detection scheme, so the code below is cross-compatible with 1.4 and 2.0:
只有 select() 的“生成”样式将得到支持。应该按位置传递要从其进行 SELECT 的列/表的列表。SQLAlchemy 1.4 中的 select() 构造接受旧样式和使用自动检测方案的新样式,因此以下代码与 1.4 和 2.0 兼容:

# use generative methods
stmt = select(1).select_from(table).order_by(table.c.id)

# use generative methods
stmt = select(table).where(table.c.id == 5)

# use generative methods
stmt = table.select().where(table.c.id == 5)

# pass columns clause expressions positionally
stmt = select(table.c.x, table.c.y)

# case conditions passed positionally
case_clause = case(
    (table.c.x == 5, "five"), (table.c.x == 7, "seven"), else_="neither five nor seven"
)

Discussion 讨论

SQLAlchemy has for many years developed a convention for SQL constructs accepting an argument either as a list or as positional arguments. This convention states that structural elements, those that form the structure of a SQL statement, should be passed positionally. Conversely, data elements, those that form the parameterized data of a SQL statement, should be passed as lists. For many years, the select() construct could not participate in this convention smoothly because of the very legacy calling pattern where the “WHERE” clause would be passed positionally. SQLAlchemy 2.0 finally resolves this by changing the select() construct to only accept the “generative” style that has for many years been the only documented style in the Core tutorial.
多年来,SQLAlchemy 为接受参数(作为列表或位置参数)的 SQL 构造开发了一个约定。此约定规定,结构元素(形成 SQL 语句结构的元素)应按位置传递。相反,数据元素(形成 SQL 语句的参数化数据的元素)应作为列表传递。多年来, select() 构造无法顺利参与此约定,因为“WHERE”子句将按位置传递的非常传统的调用模式。SQLAlchemy 2.0 最终通过将 select() 构造更改为仅接受“生成”样式来解决此问题,多年来,此样式一直是核心教程中唯一记录的样式。

Examples of “structural” vs. “data” elements are as follows:
“结构”与“数据”元素的示例如下:

# table columns for CREATE TABLE - structural
table = Table("table", metadata_obj, Column("x", Integer), Column("y", Integer))

# columns in a SELECT statement - structural
stmt = select(table.c.x, table.c.y)

# literal elements in an IN clause - data
stmt = stmt.where(table.c.y.in_([1, 2, 3]))

insert/update/delete DML no longer accept keyword constructor arguments
insert/update/delete DML 不再接受关键字构造函数参数 ¶

Synopsis 简介

In a similar way as to the previous change to select(), the constructor arguments to insert(), update() and delete() other than the table argument are essentially removed:
与对 select() 的先前更改类似,除了表参数之外, insert()update()delete() 的构造函数参数基本上已删除:

# no longer supported
stmt = insert(table, values={"x": 10, "y": 15}, inline=True)

# no longer supported
stmt = insert(table, values={"x": 10, "y": 15}, returning=[table.c.x])

# no longer supported
stmt = table.delete(table.c.x > 15)

# no longer supported
stmt = table.update(table.c.x < 15, preserve_parameter_order=True).values(
    [(table.c.y, 20), (table.c.x, table.c.y + 10)]
)

Migration to 2.0 迁移到 2.0

The following examples illustrate generative method use for the above examples:
以下示例说明了生成方法对上述示例的使用:

# use generative methods, **kwargs OK for values()
stmt = insert(table).values(x=10, y=15).inline()

# use generative methods, dictionary also still  OK for values()
stmt = insert(table).values({"x": 10, "y": 15}).returning(table.c.x)

# use generative methods
stmt = table.delete().where(table.c.x > 15)

# use generative methods, ordered_values() replaces preserve_parameter_order
stmt = (
    table.update()
    .where(
        table.c.x < 15,
    )
    .ordered_values((table.c.y, 20), (table.c.x, table.c.y + 10))
)

Discussion 讨论

The API and internals is being simplified for the DML constructs in a similar manner as that of the select() construct.
API 和内部结构正在以类似于 select() 结构的方式简化为 DML 结构。

2.0 Migration - ORM Configuration
2.0 迁移 - ORM 配置 ¶

Declarative becomes a first class API
声明式成为一流的 API ¶

Synopsis 简介

The sqlalchemy.ext.declarative package is mostly, with some exceptions, moved to the sqlalchemy.orm package. The declarative_base() and declared_attr() functions are present without any behavioral changes. A new super-implementation of declarative_base() known as registry now serves as the top-level ORM configurational construct, which also provides for decorator-based declarative and new support for classical mappings that integrate with the declarative registry.
sqlalchemy.ext.declarative 包大部分(有一些例外)已移至 sqlalchemy.orm 包。 declarative_base()declared_attr() 函数存在,没有任何行为更改。一个新的 declarative_base() 超级实现(称为 registry )现在用作顶级 ORM 配置结构,它还提供基于装饰器的声明式和对与声明式注册表集成的经典映射的新支持。

Migration to 2.0 迁移到 2.0

Change imports: 更改导入:

from sqlalchemy.ext import declarative_base, declared_attr

To: 收件人:

from sqlalchemy.orm import declarative_base, declared_attr

Discussion 讨论

After ten years or so of popularity, the sqlalchemy.ext.declarative package is now integrated into the sqlalchemy.orm namespace, with the exception of the declarative “extension” classes which remain as Declarative extensions. The change is detailed further in the 1.4 migration guide at Declarative is now integrated into the ORM with new features.
在流行了大约十年之后, sqlalchemy.ext.declarative 包现在集成到 sqlalchemy.orm 命名空间中,除了声明式“扩展”类,它们仍然作为声明式扩展。在声明式现在集成到 ORM 中并具有新功能的 1.4 迁移指南中,对更改进行了进一步的详细说明。

See also 参见

ORM Mapped Class Overview - all new unified documentation for Declarative, classical mapping, dataclasses, attrs, etc.
ORM 映射类概述 - 针对声明式、经典映射、数据类、attrs 等的所有新统一文档。

Declarative is now integrated into the ORM with new features
声明式现在已集成到具有新功能的 ORM 中

The original “mapper()” function now a core element of Declarative, renamed
原“mapper()”函数现在是 Declarative 的核心元素,已重命名为¶

Synopsis 简介

The sqlalchemy.orm.mapper() standalone function moves behind the scenes to be invoked by higher level APIs. The new version of this function is the method registry.map_imperatively() taken from a registry object.
sqlalchemy.orm.mapper() 独立函数在后台移动,由更高级别的 API 调用。此函数的新版本是 registry 对象中的 registry.map_imperatively() 方法。

Migration to 2.0 迁移到 2.0

Code that works with classical mappings should change imports and code from:
使用经典映射的代码应更改导入和代码:

from sqlalchemy.orm import mapper


mapper(SomeClass, some_table, properties={"related": relationship(SomeRelatedClass)})

To work from a central registry object:
从一个中心 registry 对象工作:

from sqlalchemy.orm import registry

mapper_reg = registry()

mapper_reg.map_imperatively(
    SomeClass, some_table, properties={"related": relationship(SomeRelatedClass)}
)

The above registry is also the source for declarative mappings, and classical mappings now have access to this registry including string-based configuration on relationship():
上述 registry 也是声明式映射的源,现在经典映射可以访问此注册表,包括 relationship() 上基于字符串的配置:

from sqlalchemy.orm import registry

mapper_reg = registry()

Base = mapper_reg.generate_base()


class SomeRelatedClass(Base):
    __tablename__ = "related"

    # ...


mapper_reg.map_imperatively(
    SomeClass,
    some_table,
    properties={
        "related": relationship(
            "SomeRelatedClass",
            primaryjoin="SomeRelatedClass.related_id == SomeClass.id",
        )
    },
)

Discussion 讨论

By popular demand, “classical mapping” is staying around, however the new form of it is based off of the registry object and is available as registry.map_imperatively().
应广大需求,“经典映射”将继续保留,但其新形式基于 registry 对象,并作为 registry.map_imperatively() 提供。

In addition, the primary rationale used for “classical mapping” is that of keeping the Table setup distinct from the class. Declarative has always allowed this style using so-called hybrid declarative. However, to remove the base class requirement, a first class decorator form has been added.
此外,“经典映射”使用的主要原理是保持 Table 设置与类不同。声明式一直允许使用所谓的混合声明式来实现此样式。但是,为了移除基类要求,已添加了一个一级装饰器形式。

As yet another separate but related enhancement, support for Python dataclasses is added as well to both declarative decorator and classical mapping forms.
作为另一个独立但相关的增强功能,对 Python 数据类的支持也已添加到声明式装饰器和经典映射形式中。

See also 参见

ORM Mapped Class Overview - all new unified documentation for Declarative, classical mapping, dataclasses, attrs, etc.
ORM 映射类概述 - 所有新的统一文档,用于声明式、经典映射、数据类、attrs 等。

2.0 Migration - ORM Usage
2.0 迁移 - ORM 用法 ¶

The biggest visible change in SQLAlchemy 2.0 is the use of Session.execute() in conjunction with select() to run ORM queries, instead of using Session.query(). As mentioned elsewhere, there is no plan to actually remove the Session.query() API itself, as it is now implemented by using the new API internally it will remain as a legacy API, and both APIs can be used freely.
SQLAlchemy 2.0 中最大的可见变化是使用 Session.execute() 结合 select() 来运行 ORM 查询,而不是使用 Session.query() 。如其他地方所述,实际上并没有计划移除 Session.query() API 本身,因为它现在通过在内部使用新 API 来实现,它将保留为传统 API,并且可以自由使用这两个 API。

The table below provides an introduction to the general change in calling form with links to documentation for each technique presented. The individual migration notes are in the embedded sections following the table, and may include additional notes not summarized here.
下表提供了对调用形式的常规更改的介绍,其中包含指向每种所述技术的文档的链接。各个迁移注释位于表后面的嵌入式部分中,并且可能包括此处未总结的其他注释。

Overview of Major ORM Querying Patterns
主要 ORM 查询模式概述 ¶

1.x style form 1.x 样式表单

2.0 style form 2.0 样式表单

See Also 另请参阅

session.query(User).get(42)
session.get(User, 42)

ORM Query - get() method moves to Session
ORM 查询 - get() 方法移至会话

session.query(User).all()
session.execute(
  select(User)
).scalars().all()

# or

session.scalars(
  select(User)
).all()

ORM Query Unified with Core Select
ORM 查询与核心选择统一

Session.scalars() Result.scalars()

session.query(User).\
  filter_by(name="some user").\
  one()
session.execute(
  select(User).
  filter_by(name="some user")
).scalar_one()

ORM Query Unified with Core Select
ORM 查询与核心选择统一

Result.scalar_one()

session.query(User).\
  filter_by(name="some user").\
  first()
session.scalars(
  select(User).
  filter_by(name="some user").
  limit(1)
).first()

ORM Query Unified with Core Select
ORM 查询与核心选择统一

Result.first()

session.query(User).options(
  joinedload(User.addresses)
).all()
session.scalars(
  select(User).
  options(
    joinedload(User.addresses)
  )
).unique().all()

ORM Rows not uniquified by default
ORM 行默认情况下不唯一

session.query(User).\
  join(Address).\
  filter(
    Address.email == "e@sa.us"
  ).\
  all()
session.execute(
  select(User).
  join(Address).
  where(
    Address.email == "e@sa.us"
  )
).scalars().all()

ORM Query Unified with Core Select
ORM 查询与核心选择统一

Joins 连接

session.query(User).\
  from_statement(
    text("select * from users")
  ).\
  all()
session.scalars(
  select(User).
  from_statement(
    text("select * from users")
  )
).all()

Getting ORM Results from Textual Statements
从文本语句中获取 ORM 结果

session.query(User).\
  join(User.addresses).\
  options(
    contains_eager(User.addresses)
  ).\
  populate_existing().all()
session.execute(
  select(User)
  .join(User.addresses)
  .options(
    contains_eager(User.addresses)
  )
  .execution_options(
      populate_existing=True
  )
).scalars().all()

ORM Execution Options ORM 执行选项

Populate Existing 填充现有

session.query(User).\
  filter(User.name == "foo").\
  update(
    {"fullname": "Foo Bar"},
    synchronize_session="evaluate"
  )
session.execute(
  update(User)
  .where(User.name == "foo")
  .values(fullname="Foo Bar")
  .execution_options(
    synchronize_session="evaluate"
  )
)

ORM-Enabled INSERT, UPDATE, and DELETE statements
启用 ORM 的 INSERT、UPDATE 和 DELETE 语句

session.query(User).count()
session.scalar(
  select(func.count()).
  select_from(User)
)

# or

session.scalar(
  select(func.count(User.id))
)

Session.scalar()

ORM Query Unified with Core Select
ORM 查询与核心选择统一 ¶

Synopsis 简介

The Query object (as well as the BakedQuery and ShardedQuery extensions) become long term legacy objects, replaced by the direct usage of the select() construct in conjunction with the Session.execute() method. Results that are returned from Query in the form of lists of objects or tuples, or as scalar ORM objects are returned from Session.execute() uniformly as Result objects, which feature an interface consistent with that of Core execution.
Query 对象(以及 BakedQueryShardedQuery 扩展)成为长期遗留对象,由 select() 构造与 Session.execute() 方法结合的直接使用所取代。从 Query 以对象或元组列表形式返回的结果,或作为标量 ORM 对象从 Session.execute() 返回的结果统一作为 Result 对象返回,该对象具有与 Core 执行一致的接口。

Legacy code examples are illustrated below:
以下说明了旧代码示例:

session = Session(engine)

# becomes legacy use case
user = session.query(User).filter_by(name="some user").one()

# becomes legacy use case
user = session.query(User).filter_by(name="some user").first()

# becomes legacy use case
user = session.query(User).get(5)

# becomes legacy use case
for user in (
    session.query(User).join(User.addresses).filter(Address.email == "some@email.com")
):
    ...

# becomes legacy use case
users = session.query(User).options(joinedload(User.addresses)).order_by(User.id).all()

# becomes legacy use case
users = session.query(User).from_statement(text("select * from users")).all()

# etc

Migration to 2.0 迁移到 2.0

Because the vast majority of an ORM application is expected to make use of Query objects as well as that the Query interface being available does not impact the new interface, the object will stay around in 2.0 but will no longer be part of documentation nor will it be supported for the most part. The select() construct now suits both the Core and ORM use cases, which when invoked via the Session.execute() method will return ORM-oriented results, that is, ORM objects if that’s what was requested.
由于绝大多数 ORM 应用程序预计会使用 Query 对象,并且 Query 接口可用不会影响新接口,因此该对象将在 2.0 中保留,但不再是文档的一部分,并且在大多数情况下也不会受到支持。 select() 构造现在适用于 Core 和 ORM 用例,当通过 Session.execute() 方法调用时,将返回面向 ORM 的结果,即如果这是请求的内容,则返回 ORM 对象。

The Select() construct adds many new methods for compatibility with Query, including Select.filter() Select.filter_by(), newly reworked Select.join() and Select.outerjoin() methods, Select.options(), etc. Other more supplemental methods of Query such as Query.populate_existing() are implemented via execution options.
Select() 构造添加了许多新方法以兼容 Query ,包括 Select.filter() Select.filter_by() 、新修改的 Select.join()Select.outerjoin() 方法、 Select.options() 等。其他更多 Query 的补充方法(如 Query.populate_existing() )通过执行选项实现。

Return results are in terms of a Result object, the new version of the SQLAlchemy ResultProxy object, which also adds many new methods for compatibility with Query, including Result.one(), Result.all(), Result.first(), Result.one_or_none(), etc.
返回结果以 Result 对象的形式返回,这是 SQLAlchemy ResultProxy 对象的新版本,还添加了许多新方法以兼容 Query ,包括 Result.one()Result.all()Result.first()Result.one_or_none() 等。

The Result object however does require some different calling patterns, in that when first returned it will always return tuples and it will not deduplicate results in memory. In order to return single ORM objects the way Query does, the Result.scalars() modifier must be called first. In order to return uniqued objects, as is necessary when using joined eager loading, the Result.unique() modifier must be called first.
Result 对象确实需要一些不同的调用模式,因为当首次返回时,它总是返回元组,并且不会在内存中对结果进行重复数据删除。为了返回 Query 所做的单一 ORM 对象,必须首先调用 Result.scalars() 修饰符。为了返回唯一对象(在使用联接急加载时必须这样做),必须首先调用 Result.unique() 修饰符。

Documentation for all new features of select() including execution options, etc. are at ORM Querying Guide.
有关 select() 所有新功能的文档,包括执行选项等,请参阅 ORM 查询指南。

Below are some examples of how to migrate to select():
以下是迁移到 select() 的一些示例:

session = Session(engine)

user = session.execute(select(User).filter_by(name="some user")).scalar_one()

# for first(), no LIMIT is applied automatically; add limit(1) if LIMIT
# is desired on the query
user = (
    session.execute(select(User).filter_by(name="some user").limit(1)).scalars().first()
)

# get() moves to the Session directly
user = session.get(User, 5)

for user in session.execute(
    select(User).join(User.addresses).filter(Address.email == "some@email.case")
).scalars():
    ...

# when using joinedload() against collections, use unique() on the result
users = (
    session.execute(select(User).options(joinedload(User.addresses)).order_by(User.id))
    .unique()
    .all()
)

# select() has ORM-ish methods like from_statement() that only work
# if the statement is against ORM entities
users = (
    session.execute(select(User).from_statement(text("select * from users")))
    .scalars()
    .all()
)

Discussion 讨论

The fact that SQLAlchemy has both a select() construct as well as a separate Query object that features an extremely similar, but fundamentally incompatible interface is likely the greatest inconsistency in SQLAlchemy, one that arose as a result of small incremental additions over time that added up to two major APIs that are divergent.
SQLAlchemy 既有 select() 构造,又有单独的 Query 对象,该对象具有极其相似但从根本上不兼容的界面,这可能是 SQLAlchemy 中最大的不一致之处,这是随着时间的推移而增加的小增量添加的结果,最终形成了两个不同的主要 API。

In SQLAlchemy’s first releases, the Query object didn’t exist at all. The original idea was that the Mapper construct itself would be able to select rows, and that Table objects, not classes, would be used to create the various criteria in a Core-style approach. The Query came along some months / years into SQLAlchemy’s history as a user proposal for a new, “buildable” querying object originally called SelectResults was accepted. Concepts like a .where() method, which SelectResults called .filter(), were not present in SQLAlchemy previously, and the select() construct used only the “all-at-once” construction style that’s now deprecated at select() no longer accepts varied constructor arguments, columns are passed positionally.
在 SQLAlchemy 的第一个版本中, Query 对象根本不存在。最初的想法是 Mapper 构造本身能够选择行,并且 Table 对象(而不是类)将用于创建 Core 风格方法中的各种条件。在 SQLAlchemy 的历史中, Query 在几个月/几年后作为用户对一个新的“可构建”查询对象(最初称为 SelectResults )的建议而被接受。 .where() 方法之类的概念( SelectResults 称为 .filter() )以前在 SQLAlchemy 中不存在,并且 select() 构造仅使用“一次性”构造样式,该样式现在已弃用,select() 不再接受不同的构造函数参数,列按位置传递。

As the new approach took off, the object evolved into the Query object as new features such as being able to select individual columns, being able to select multiple entities at once, being able to build subqueries from a Query object rather than from a select object were added. The goal became that Query should have the full functionality of select in that it could be composed to build SELECT statements fully with no explicit use of select() needed. At the same time, select() had also evolved “generative” methods like Select.where() and Select.order_by().
随着新方法的推出,该对象演变为 Query 对象,因为添加了诸如能够选择单个列、能够一次选择多个实体、能够从 Query 对象而不是从 select 对象构建子查询等新功能。目标变成了 Query 应该具有 select 的全部功能,因为它可以被组合以完全构建 SELECT 语句,而无需显式使用 select() 。与此同时, select() 也演变出了“生成”方法,如 Select.where()Select.order_by()

In modern SQLAlchemy, this goal has been achieved and the two objects are now completely overlapping in functionality. The major challenge to unifying these objects was that the select() object needed to remain completely agnostic of the ORM. To achieve this, the vast majority of logic from Query has been moved into the SQL compile phase, where ORM-specific compiler plugins receive the Select construct and interpret its contents in terms of an ORM-style query, before passing off to the core-level compiler in order to create a SQL string. With the advent of the new SQL compilation caching system, the majority of this ORM logic is also cached.
在现代 SQLAlchemy 中,此目标已实现,并且这两个对象现在在功能上完全重叠。统一这些对象的主要挑战在于 select() 对象需要完全独立于 ORM。为了实现这一点, Query 中的绝大部分逻辑已被移至 SQL 编译阶段,其中特定于 ORM 的编译器插件接收 Select 构造并根据 ORM 风格的查询解释其内容,然后传递给核心级编译器以创建 SQL 字符串。随着新的 SQL 编译缓存系统的出现,此 ORM 逻辑的大部分也已缓存。

ORM Query - get() method moves to Session
ORM 查询 - get() 方法移至会话 ¶

Synopsis 简介

The Query.get() method remains for legacy purposes, but the primary interface is now the Session.get() method:
Query.get() 方法保留用于传统目的,但现在主要接口是 Session.get() 方法:

# legacy usage
user_obj = session.query(User).get(5)

Migration to 2.0 迁移到 2.0

In 1.4 / 2.0, the Session object adds a new Session.get() method:
在 1.4 / 2.0 中, Session 对象添加了一个新的 Session.get() 方法:

# 1.4 / 2.0 cross-compatible use
user_obj = session.get(User, 5)

Discussion 讨论

The Query object is to be a legacy object in 2.0, as ORM queries are now available using the select() object. As the Query.get() method defines a special interaction with the Session and does not necessarily even emit a query, it’s more appropriate that it be part of Session, where it is similar to other “identity” methods such as refresh and merge.
Query 对象在 2.0 中将成为一个传统对象,因为现在可以使用 select() 对象进行 ORM 查询。由于 Query.get() 方法定义了与 Session 的特殊交互,甚至不一定发出查询,因此将其作为 Session 的一部分更为合适,其中它类似于其他“标识”方法,例如 refreshmerge

SQLAlchemy originally included “get()” to resemble the Hibernate Session.load() method. As is so often the case, we got it slightly wrong as this method is really more about the Session than with writing a SQL query.
SQLAlchemy 最初包含“get()”以类似 Hibernate Session.load() 方法。正如经常发生的那样,我们对此方法理解略有偏差,因为此方法实际上更关注 Session 而不是编写 SQL 查询。

ORM Query - Joining / loading on relationships uses attributes, not strings
ORM 查询 - 连接/加载关系使用属性,而不是字符串 ¶

Synopsis 简介

This refers to patterns such as that of Query.join() as well as query options like joinedload() which currently accept a mixture of string attribute names or actual class attributes. The string forms will all be removed in 2.0:
这指的是 Query.join() 等模式以及 joinedload() 等查询选项,它们目前接受字符串属性名称或实际类属性的混合。字符串形式将在 2.0 中全部移除:

# string use removed
q = session.query(User).join("addresses")

# string use removed
q = session.query(User).options(joinedload("addresses"))

# string use removed
q = session.query(Address).filter(with_parent(u1, "addresses"))

Migration to 2.0 迁移到 2.0

Modern SQLAlchemy 1.x versions support the recommended technique which is to use mapped attributes:
现代 SQLAlchemy 1.x 版本支持推荐的技术,即使用映射属性:

# compatible with all modern SQLAlchemy versions

q = session.query(User).join(User.addresses)

q = session.query(User).options(joinedload(User.addresses))

q = session.query(Address).filter(with_parent(u1, User.addresses))

The same techniques apply to 2.0-style style use:
相同的技术适用于 2.0 风格的使用:

# SQLAlchemy 1.4 / 2.0 cross compatible use

stmt = select(User).join(User.addresses)
result = session.execute(stmt)

stmt = select(User).options(joinedload(User.addresses))
result = session.execute(stmt)

stmt = select(Address).where(with_parent(u1, User.addresses))
result = session.execute(stmt)

Discussion 讨论

The string calling form is ambiguous and requires that the internals do extra work to determine the appropriate path and retrieve the correct mapped property. By passing the ORM mapped attribute directly, not only is the necessary information passed up front, the attribute is also typed and is more potentially compatible with IDEs and pep-484 integrations.
字符串调用形式不明确,需要内部执行额外工作来确定适当的路径并检索正确的映射属性。通过直接传递 ORM 映射的属性,不仅可以预先传递必要的信息,而且该属性还具有类型,并且更有可能与 IDE 和 pep-484 集成兼容。

ORM Query - Chaining using lists of attributes, rather than individual calls, removed
ORM 查询 - 使用属性列表进行链接,而不是单独调用,已移除 ¶

Synopsis 简介

“Chained” forms of joining and loader options which accept multiple mapped attributes in a list will be removed:
接受列表中多个映射属性的“链接”形式的连接和加载程序选项将被移除:

# chaining removed
q = session.query(User).join("orders", "items", "keywords")

Migration to 2.0 迁移到 2.0

Use individual calls to Query.join() for 1.x /2.0 cross compatible use:
对 1.x /2.0 跨兼容使用,使用对 Query.join() 的单独调用:

q = session.query(User).join(User.orders).join(Order.items).join(Item.keywords)

For 2.0-style use, Select has the same behavior of Select.join(), and also features a new Select.join_from() method that allows an explicit left side:
对于 2.0 风格的使用, Select 具有与 Select.join() 相同的行为,并且还具有一个新的 Select.join_from() 方法,该方法允许显式左侧:

# 1.4 / 2.0 cross compatible

stmt = select(User).join(User.orders).join(Order.items).join(Item.keywords)
result = session.execute(stmt)

# join_from can also be helpful
stmt = select(User).join_from(User, Order).join_from(Order, Item, Order.items)
result = session.execute(stmt)

Discussion 讨论

Removing the chaining of attributes is in line with simplifying the calling interface of methods such as Select.join().
移除属性的链式调用符合简化 Select.join() 等方法的调用接口。

ORM Query - join(…, aliased=True), from_joinpoint removed
ORM 查询 - join(…, aliased=True),已移除 from_joinpoint

Synopsis 简介

The aliased=True option on Query.join() is removed, as is the from_joinpoint flag:
aliased=True 选项已从 Query.join() 中移除, from_joinpoint 标志也是如此:

# no longer supported
q = (
    session.query(Node)
    .join("children", aliased=True)
    .filter(Node.name == "some sub child")
    .join("children", from_joinpoint=True, aliased=True)
    .filter(Node.name == "some sub sub child")
)

Migration to 2.0 迁移到 2.0

Use explicit aliases instead:
使用显式别名:

n1 = aliased(Node)
n2 = aliased(Node)

q = (
    select(Node)
    .join(Node.children.of_type(n1))
    .where(n1.name == "some sub child")
    .join(n1.children.of_type(n2))
    .where(n2.name == "some sub child")
)

Discussion 讨论

The aliased=True option on Query.join() is another feature that seems to be almost never used, based on extensive code searches to find actual use of this feature. The internal complexity that the aliased=True flag requires is enormous, and will be going away in 2.0.
aliased=True 选项在 Query.join() 上是另一个似乎几乎从未使用过的功能,这是基于广泛的代码搜索以查找此功能的实际使用情况。 aliased=True 标志所需的内部复杂性非常大,并且将在 2.0 中消失。

Most users aren’t familiar with this flag, however it allows for automatic aliasing of elements along a join, which then applies automatic aliasing to filter conditions. The original use case was to assist in long chains of self-referential joins, as in the example shown above. However, the automatic adaption of the filter criteria is enormously complicated internally and almost never used in real world applications. The pattern also leads to issues such as if filter criteria need to be added at each link in the chain; the pattern then must use the from_joinpoint flag which SQLAlchemy developers could absolutely find no occurrence of this parameter ever being used in real world applications.
大多数用户不熟悉此标志,但它允许沿连接自动生成元素别名,然后将自动别名应用于筛选条件。最初的用例是帮助生成自引用连接的长链,如上所示的示例。但是,筛选条件的自动适应在内部非常复杂,在实际应用中几乎从未使用过。此模式还会导致问题,例如如果需要在链中的每个链接处添加筛选条件;那么该模式必须使用 from_joinpoint 标志,而 SQLAlchemy 开发人员绝对找不到此参数在实际应用中被使用的任何情况。

The aliased=True and from_joinpoint parameters were developed at a time when the Query object didn’t yet have good capabilities regarding joining along relationship attributes, functions like PropComparator.of_type() did not exist, and the aliased() construct itself didn’t exist early on.
aliased=Truefrom_joinpoint 参数是在 Query 对象在关系属性上还没有良好的连接能力、 PropComparator.of_type() 这样的函数还不存在,以及 aliased() 构造本身在早期还不存在的时候开发的。

Using DISTINCT with additional columns, but only select the entity
使用 DISTINCT 和其他列,但仅选择实体 ¶

Synopsis 简介

Query will automatically add columns in the ORDER BY when distinct is used. The following query will select from all User columns as well as “address.email_address” but only return User objects:
Query 在使用 distinct 时会自动在 ORDER BY 中添加列。以下查询将从所有 User 列以及“address.email_address”中进行选择,但仅返回 User 对象:

# 1.xx code

result = (
    session.query(User)
    .join(User.addresses)
    .distinct()
    .order_by(Address.email_address)
    .all()
)

In version 2.0, the “email_address” column will not be automatically added to the columns clause, and the above query will fail, since relational databases won’t allow you to ORDER BY “address.email_address” when using DISTINCT if it isn’t also in the columns clause.
在版本 2.0 中,“email_address” 列不会自动添加到 columns 子句,并且上面的查询将失败,因为如果关系数据库在使用 DISTINCT 时不在 columns 子句中,则不允许你按 “address.email_address” 排序。

Migration to 2.0 迁移到 2.0

In 2.0, the column must be added explicitly. To resolve the issue of only returning the main entity object, and not the extra column, use the Result.columns() method:
在 2.0 中,必须显式添加列。若要解决仅返回主实体对象而不返回额外列的问题,请使用 Result.columns() 方法:

# 1.4 / 2.0 code

stmt = (
    select(User, Address.email_address)
    .join(User.addresses)
    .distinct()
    .order_by(Address.email_address)
)

result = session.execute(stmt).columns(User).all()

Discussion 讨论

This case is an example of the limited flexibility of Query leading to the case where implicit, “magical” behavior needed to be added; the “email_address” column is implicitly added to the columns clause, then additional internal logic would omit that column from the actual results returned.
此案例是 Query 灵活度有限的一个示例,导致需要添加隐式、“神奇”行为的情况;“email_address” 列被隐式添加到列子句,然后其他内部逻辑会从返回的实际结果中省略该列。

The new approach simplifies the interaction and makes what’s going on explicit, while still making it possible to fulfill the original use case without inconvenience.
新方法简化了交互,并明确了正在发生的事情,同时仍然可以方便地实现原始用例。

Selecting from the query itself as a subquery, e.g. “from_self()”
从查询本身中选择作为子查询,例如“from_self()”¶

Synopsis 简介

The Query.from_self() method will be removed from Query:
Query.from_self() 方法将从 Query 中移除:

# from_self is removed
q = (
    session.query(User, Address.email_address)
    .join(User.addresses)
    .from_self(User)
    .order_by(Address.email_address)
)

Migration to 2.0 迁移到 2.0

The aliased() construct may be used to emit ORM queries against an entity that is in terms of any arbitrary selectable. It has been enhanced in version 1.4 to smoothly accommodate being used multiple times against the same subquery for different entities as well. This can be used in 1.x style with Query as below; note that since the final query wants to query in terms of both the User and Address entities, two separate aliased() constructs are created:
aliased() 构造可用于针对任何任意可选择项的实体发出 ORM 查询。它已在 1.4 版中得到增强,以便针对同一子查询平滑地适应多次用于不同实体。这可与 Query 一起用于 1.x 样式,如下所示;请注意,由于最终查询希望根据 UserAddress 实体进行查询,因此创建了两个单独的 aliased() 构造:

from sqlalchemy.orm import aliased

subq = session.query(User, Address.email_address).join(User.addresses).subquery()

ua = aliased(User, subq)

aa = aliased(Address, subq)

q = session.query(ua, aa).order_by(aa.email_address)

The same form may be used in 2.0 style:
2.0 样式中可以使用相同的表单:

from sqlalchemy.orm import aliased

subq = select(User, Address.email_address).join(User.addresses).subquery()

ua = aliased(User, subq)

aa = aliased(Address, subq)

stmt = select(ua, aa).order_by(aa.email_address)

result = session.execute(stmt)

Discussion 讨论

The Query.from_self() method is a very complicated method that is rarely used. The purpose of this method is to convert a Query into a subquery, then return a new Query which SELECTs from that subquery. The elaborate aspect of this method is that the returned query applies automatic translation of ORM entities and columns to be stated in the SELECT in terms of the subquery, as well as that it allows the entities and columns to be SELECTed from to be modified.
Query.from_self() 方法是一个很少使用的非常复杂的方法。此方法的目的是将 Query 转换为子查询,然后返回一个从该子查询中 SELECT 的新 Query 。此方法的复杂方面在于,返回的查询应用了 ORM 实体和列的自动转换,以便在 SELECT 中根据子查询进行表述,并且它允许修改要从中 SELECT 的实体和列。

Because Query.from_self() packs an intense amount of implicit translation into the SQL it produces, while it does allow a certain kind of pattern to be executed very succinctly, real world use of this method is infrequent as it is not simple to understand.
因为 Query.from_self() 将大量的隐式翻译打包到它生成的 SQL 中,虽然它确实允许以非常简洁的方式执行某种模式,但这种方法的实际使用并不频繁,因为它不容易理解。

The new approach makes use of the aliased() construct so that the ORM internals don’t need to guess which entities and columns should be adapted and in what way; in the example above, the ua and aa objects, both of which are AliasedClass instances, provide to the internals an unambiguous marker as to where the subquery should be referenced as well as what entity column or relationship is being considered for a given component of the query.
新方法利用 aliased() 构造,以便 ORM 内部无需猜测应以何种方式调整哪些实体和列;在上面的示例中, uaaa 对象(两者都是 AliasedClass 实例)为内部提供了一个明确的标记,指示应将子查询引用到何处,以及正在考虑查询的给定组件的哪个实体列或关系。

SQLAlchemy 1.4 also features an improved labeling style that no longer requires the use of long labels that include the table name in order to disambiguate columns of same names from different tables. In the above examples, even if our User and Address entities have overlapping column names, we can select from both entities at once without having to specify any particular labeling:
SQLAlchemy 1.4 还具有改进的标记样式,不再需要使用包含表名的长标记来消除来自不同表的同名列的歧义。在上述示例中,即使我们的 UserAddress 实体具有重叠的列名,我们也可以一次从这两个实体中进行选择,而无需指定任何特定标记:

# 1.4 / 2.0 code

subq = select(User, Address).join(User.addresses).subquery()

ua = aliased(User, subq)
aa = aliased(Address, subq)

stmt = select(ua, aa).order_by(aa.email_address)
result = session.execute(stmt)

The above query will disambiguate the .id column of User and Address, where Address.id is rendered and tracked as id_1:
上述查询将消除 .id 列的 UserAddress 的歧义,其中 Address.id 呈现并跟踪为 id_1

SELECT anon_1.id AS anon_1_id, anon_1.id_1 AS anon_1_id_1,
       anon_1.user_id AS anon_1_user_id,
       anon_1.email_address AS anon_1_email_address
FROM (
  SELECT "user".id AS id, address.id AS id_1,
  address.user_id AS user_id, address.email_address AS email_address
  FROM "user" JOIN address ON "user".id = address.user_id
) AS anon_1 ORDER BY anon_1.email_address

#5221

Selecting entities from alternative selectables; Query.select_entity_from()
从备选项中选择实体;Query.select_entity_from() ¶

Synopsis 简介

The Query.select_entity_from() method will be removed in 2.0:
Query.select_entity_from() 方法将在 2.0 中移除:

subquery = session.query(User).filter(User.id == 5).subquery()

user = session.query(User).select_entity_from(subquery).first()

Migration to 2.0 迁移到 2.0

As is the case described at Selecting from the query itself as a subquery, e.g. “from_self()”, the aliased() object provides a single place that operations like “select entity from a subquery” may be achieved. Using 1.x style:
正如在从查询本身中选择作为子查询中所述,例如“from_self()”, aliased() 对象提供了一个可以实现“从子查询中选择实体”等操作的单一位置。使用 1.x 样式:

from sqlalchemy.orm import aliased

subquery = session.query(User).filter(User.name.like("%somename%")).subquery()

ua = aliased(User, subquery)

user = session.query(ua).order_by(ua.id).first()

Using 2.0 style: 使用 2.0 样式:

from sqlalchemy.orm import aliased

subquery = select(User).where(User.name.like("%somename%")).subquery()

ua = aliased(User, subquery)

# note that LIMIT 1 is not automatically supplied, if needed
user = session.execute(select(ua).order_by(ua.id).limit(1)).scalars().first()

Discussion 讨论

The points here are basically the same as those discussed at Selecting from the query itself as a subquery, e.g. “from_self()”. The Query.select_from_entity() method was another way to instruct the query to load rows for a particular ORM mapped entity from an alternate selectable, which involved having the ORM apply automatic aliasing to that entity wherever it was used in the query later on, such as in the WHERE clause or ORDER BY. This intensely complex feature is seldom used in this way, where as was the case with Query.from_self(), it’s much easier to follow what’s going on when using an explicit aliased() object, both from a user point of view as well as how the internals of the SQLAlchemy ORM must handle it.
这里的要点基本上与在从查询本身中选择作为子查询(例如“from_self()”)中讨论的要点相同。 Query.select_from_entity() 方法是另一种指示查询从备用可选择项加载特定 ORM 映射实体的行的方法,其中涉及让 ORM 在稍后在查询中使用的任何地方(例如在 WHERE 子句或 ORDER BY 中)自动为该实体应用别名。这种极其复杂的功能很少以这种方式使用,就像 Query.from_self() 的情况一样,使用显式 aliased() 对象更容易理解正在发生的事情,无论是从用户角度还是从 SQLAlchemy ORM 的内部必须如何处理它的角度来看。

ORM Rows not uniquified by default
ORM 行默认情况下不唯一化 ¶

Synopsis 概要

ORM rows returned by session.execute(stmt) are no longer automatically “uniqued”. This will normally be a welcome change, except in the case where the “joined eager loading” loader strategy is used with collections:
session.execute(stmt) 返回的 ORM 行不再自动“唯一化”。这通常是一个受欢迎的更改,除非在集合中使用“联接急加载”加载器策略:

# In the legacy API, many rows each have the same User primary key, but
# only one User per primary key is returned
users = session.query(User).options(joinedload(User.addresses))

# In the new API, uniquing is available but not implicitly
# enabled
result = session.execute(select(User).options(joinedload(User.addresses)))

# this actually will raise an error to let the user know that
# uniquing should be applied
rows = result.all()

Migrating to 2.0 迁移到 2.0

When using a joined load of a collection, it’s required that the Result.unique() method is called. The ORM will actually set a default row handler that will raise an error if this is not done, to ensure that a joined eager load collection does not return duplicate rows while still maintaining explicitness:
当使用集合的联接加载时,需要调用 Result.unique() 方法。如果未执行此操作,ORM 实际上会设置一个默认行处理程序,该处理程序将引发错误,以确保联接急加载集合不会返回重复行,同时仍保持显式性:

# 1.4 / 2.0 code

stmt = select(User).options(joinedload(User.addresses))

# statement will raise if unique() is not used, due to joinedload()
# of a collection.  in all other cases, unique() is not needed.
# By stating unique() explicitly, confusion over discrepancies between
# number of objects/ rows returned vs. "SELECT COUNT(*)" is resolved
rows = session.execute(stmt).unique().all()

Discussion 讨论

The situation here is a little bit unusual, in that SQLAlchemy is requiring that a method be invoked that it is in fact entirely capable of doing automatically. The reason for requiring that the method be called is to ensure the developer is “opting in” to the use of the Result.unique() method, such that they will not be confused when a straight count of rows does not conflict with the count of records in the actual result set, which has been a long running source of user confusion and bug reports for many years. That the uniquifying is not happening in any other case by default will improve performance and also improve clarity in those cases where automatic uniquing was causing confusing results.
这里的情况有点不寻常,因为 SQLAlchemy 要求调用一个它实际上完全有能力自动执行的方法。要求调用该方法的原因是为了确保开发人员“选择加入”使用 Result.unique() 方法,这样当行数的直接计数与实际结果集中记录的计数不冲突时,他们就不会感到困惑,而这多年来一直是用户困惑和错误报告的长期根源。在任何其他情况下,默认情况下不发生唯一化将提高性能,并且在自动唯一化导致令人困惑的结果的情况下也会提高清晰度。

To the degree that having to call Result.unique() when joined eager load collections are used is inconvenient, in modern SQLAlchemy the selectinload() strategy presents a collection-oriented eager loader that is superior in most respects to joinedload() and should be preferred.
在使用连接的急加载集合时必须调用 Result.unique() 的程度不方便,在现代 SQLAlchemy 中, selectinload() 策略提供了一个面向集合的急加载器,它在大多数方面都优于 joinedload() ,并且应该是首选。

“Dynamic” relationship loaders superseded by “Write Only”
“动态”关系加载器被“只写”取代

Synopsis 简介

The lazy="dynamic" relationship loader strategy, discussed at Dynamic Relationship Loaders, makes use of the Query object which is legacy in 2.0. The “dynamic” relationship is not directly compatible with asyncio without workarounds, and additionally it does not fulfill its original purpose of preventing iteration of large collections as it has several behaviors where this iteration occurs implicitly.
在动态关系加载器中讨论的 lazy="dynamic" 关系加载器策略利用了 2.0 中的 Query 对象。如果没有解决方法,“动态”关系与 asyncio 不直接兼容,此外,它也没有实现其防止迭代大型集合的最初目的,因为它有几种行为,其中此迭代会隐式发生。

A new loader strategy known as lazy="write_only" is introduced, which through the WriteOnlyCollection collection class provides a very strict “no implicit iteration” API and additionally integrates with 2.0 style statement execution, supporting asyncio as well as direct integrations with the new ORM-enabled Bulk DML featureset.
引入了一种新的加载器策略,称为 lazy="write_only" ,它通过 WriteOnlyCollection 集合类提供非常严格的“无隐式迭代”API,此外还与 2.0 样式语句执行集成,支持 asyncio 以及与新的支持 ORM 的批量 DML 功能集直接集成。

At the same time, lazy="dynamic" remains fully supported in version 2.0; applications can delay migrating this particular pattern until they are fully on the 2.0 series.
同时, lazy="dynamic" 在 2.0 版本中仍然得到完全支持;应用程序可以延迟迁移此特定模式,直到它们完全使用 2.0 系列。

Migration to 2.0 迁移到 2.0

The new “write only” feature is only available in SQLAlchemy 2.0, and is not part of 1.4. At the same time, the lazy="dynamic" loader strategy remains fully supported in version 2.0, and even includes new pep-484 and annotated mapping support.
新的“仅写入”功能仅在 SQLAlchemy 2.0 中可用,不属于 1.4。同时, lazy="dynamic" 加载策略在 2.0 版本中仍然得到完全支持,甚至包括新的 pep-484 和注释映射支持。

Therefore the best strategy for migrating from “dynamic” is to wait until the application is fully running on 2.0, then migrate directly from AppenderQuery, which is the collection type used by the “dynamic” strategy, to WriteOnlyCollection, which is the collection type used by hte “write_only” strategy.
因此,从“dynamic”迁移的最佳策略是等到应用程序完全在 2.0 上运行,然后直接从 AppenderQuery 迁移到 WriteOnlyCollection ,这是“dynamic”策略使用的集合类型,而 WriteOnlyCollection 是“write_only”策略使用的集合类型。

Some techniques are available to use lazy="dynamic" under 1.4 in a more “2.0” style however. There are two ways to achieve 2.0 style querying that’s in terms of a specific relationship:
不过,有一些技术可用于以更“2.0”的风格在 1.4 中使用 lazy="dynamic" 。有两种方法可以实现 2.0 风格的查询,即针对特定关系:

  • Make use of the Query.statement attribute on an existing lazy="dynamic" relationship. We can use methods like Session.scalars() with the dynamic loader straight away as follows:
    利用现有 lazy="dynamic" 关系上的 Query.statement 属性。我们可以直接使用动态加载器中的 Session.scalars() 等方法,如下所示:

    class User(Base):
        __tablename__ = "user"
    
        posts = relationship(Post, lazy="dynamic")
    
    
    jack = session.get(User, 5)
    
    # filter Jack's blog posts
    posts = session.scalars(jack.posts.statement.where(Post.headline == "this is a post"))
  • Use the with_parent() function to construct a select() construct directly:
    使用 with_parent() 函数直接构造 select() 构造体:

    from sqlalchemy.orm import with_parent
    
    jack = session.get(User, 5)
    
    posts = session.scalars(
        select(Post)
        .where(with_parent(jack, User.posts))
        .where(Post.headline == "this is a post")
    )

Discussion 讨论

The original idea was that the with_parent() function should be sufficient, however continuing to make use of special attributes on the relationship itself remains appealing, and there’s no reason a 2.0 style construct can’t be made to work here as well.
最初的想法是 with_parent() 函数应该足够,但是继续使用关系本身的特殊属性仍然很有吸引力,并且没有理由不能让 2.0 样式的构造在这里也起作用。

The new “write_only” loader strategy provides a new kind of collection which does not support implicit iteration or item access. Instead, reading the contents of the collection is performed by calling upon its .select() method to help construct an appropriate SELECT statement. The collection also includes methods .insert(), .update(), .delete() which may be used to emit bulk DML statements for the items in the collection. In a manner similar to that of the “dynamic” feature, there are also methods .add(), .add_all() and .remove() which queue individual members for addition or removal using the unit of work process. An introduction to the new feature is as New “Write Only” relationship strategy supersedes “dynamic”.
新的“仅写”加载器策略提供了一种新型集合,不支持隐式迭代或项访问。相反,通过调用其 .select() 方法来读取集合的内容,以帮助构建适当的 SELECT 语句。该集合还包括方法 .insert().update().delete() ,可用于为集合中的项发出批量 DML 语句。与“动态”功能类似,还有方法 .add().add_all().remove() ,它们使用工作单元进程对各个成员排队以进行添加或删除。新功能的介绍是,新的“仅写”关系策略取代了“动态”。

Autocommit mode removed from Session; autobegin support added
Autocommit 模式已从会话中移除;添加了 autobegin 支持 ¶

Synopsis 简介

The Session will no longer support “autocommit” mode, that is, this pattern:
Session 将不再支持“自动提交”模式,即此模式:

from sqlalchemy.orm import Session

sess = Session(engine, autocommit=True)

# no transaction begun, but emits SQL, won't be supported
obj = sess.query(Class).first()


# session flushes in a transaction that it begins and
# commits, won't be supported
sess.flush()

Migration to 2.0 迁移到 2.0

The main reason a Session is used in “autocommit” mode is so that the Session.begin() method is available, so that framework integrations and event hooks can control when this event happens. In 1.4, the Session now features autobegin behavior which resolves this issue; the Session.begin() method may now be called:
Session 在“自动提交”模式下使用主要是因为 Session.begin() 方法可用,以便框架集成和事件挂钩可以控制此事件何时发生。在 1.4 中, Session 现在具有自动开始行为,可解决此问题;现在可以调用 Session.begin() 方法:

from sqlalchemy.orm import Session

sess = Session(engine)

sess.begin()  # begin explicitly; if not called, will autobegin
# when database access is needed

sess.add(obj)

sess.commit()

Discussion 讨论

The “autocommit” mode is another holdover from the first versions of SQLAlchemy. The flag has stayed around mostly in support of allowing explicit use of Session.begin(), which is now solved by 1.4, as well as to allow the use of “subtransactions”, which are also removed in 2.0.
“autocommit” 模式是 SQLAlchemy 早期版本中的另一个遗留模式。该标志主要保留下来是为了支持显式使用 Session.begin() ,该问题已在 1.4 中得到解决,还为了支持使用“子事务”,该功能也在 2.0 中被移除。

Session “subtransaction” behavior removed
会话“子事务”行为已移除 ¶

Synopsis 简介

The “subtransaction” pattern that was often used with autocommit mode is also deprecated in 1.4. This pattern allowed the use of the Session.begin() method when a transaction were already begun, resulting in a construct called a “subtransaction”, which was essentially a block that would prevent the Session.commit() method from actually committing.
在 1.4 中,通常与自动提交模式一起使用的“子事务”模式也已弃用。此模式允许在事务已开始时使用 Session.begin() 方法,从而导致一个称为“子事务”的构造,它本质上是一个阻止 Session.commit() 方法实际提交的块。

Migration to 2.0 迁移到 2.0

To provide backwards compatibility for applications that make use of this pattern, the following context manager or a similar implementation based on a decorator may be used:
为了向使用此模式的应用程序提供向后兼容性,可以使用以下上下文管理器或基于装饰器的类似实现:

import contextlib


@contextlib.contextmanager
def transaction(session):
    if not session.in_transaction():
        with session.begin():
            yield
    else:
        yield

The above context manager may be used in the same way the “subtransaction” flag works, such as in the following example:
上述上下文管理器可以像“子事务”标志一样使用,例如在以下示例中:

# method_a starts a transaction and calls method_b
def method_a(session):
    with transaction(session):
        method_b(session)


# method_b also starts a transaction, but when
# called from method_a participates in the ongoing
# transaction.
def method_b(session):
    with transaction(session):
        session.add(SomeObject("bat", "lala"))


Session = sessionmaker(engine)

# create a Session and call method_a
with Session() as session:
    method_a(session)

To compare towards the preferred idiomatic pattern, the begin block should be at the outermost level. This removes the need for individual functions or methods to be concerned with the details of transaction demarcation:
为了与首选的惯用模式进行比较,begin 块应处于最外层。这消除了各个函数或方法需要关注事务分隔的详细信息:

def method_a(session):
    method_b(session)


def method_b(session):
    session.add(SomeObject("bat", "lala"))


Session = sessionmaker(engine)

# create a Session and call method_a
with Session() as session:
    with session.begin():
        method_a(session)

Discussion 讨论

This pattern has been shown to be confusing in real world applications, and it is preferable for an application to ensure that the top-most level of database operations are performed with a single begin/commit pair.
这种模式已被证明在实际应用中会造成混淆,因此应用程序最好确保数据库操作的最顶层使用单个 begin/commit 对执行。

2.0 Migration - ORM Extension and Recipe Changes
2.0 迁移 - ORM 扩展和配方更改 ¶

Dogpile cache recipe and Horizontal Sharding uses new Session API
Dogpile 缓存配方和水平分片使用新的会话 API ¶

As the Query object becomes legacy, these two recipes which previously relied upon subclassing of the Query object now make use of the SessionEvents.do_orm_execute() hook. See the section Re-Executing Statements for an example.
随着 Query 对象成为传统,以前依赖于 Query 对象的子类的这两个配方现在使用 SessionEvents.do_orm_execute() 钩子。有关示例,请参阅重新执行语句部分。

Baked Query Extension Superseded by built-in caching
内置缓存取代了 Baked Query Extension ¶

The baked query extension is superseded by the built in caching system and is no longer used by the ORM internals.
烘焙查询扩展已被内置缓存系统取代,不再被 ORM 内部使用。

See SQL Compilation Caching for full background on the new caching system.
有关新缓存系统的完整背景信息,请参阅 SQL 编译缓存。

Asyncio Support Asyncio 支持 ¶

SQLAlchemy 1.4 includes asyncio support for both Core and ORM. The new API exclusively makes use of the “future” patterns noted above. See Asynchronous IO Support for Core and ORM for background.
SQLAlchemy 1.4 包含对 Core 和 ORM 的 asyncio 支持。新的 API 专用使用上面提到的“future”模式。有关背景信息,请参阅 Core 和 ORM 的异步 IO 支持。