SqlAlchemy 2.0 中文文档（五十九）

原文：docs.sqlalchemy.org/en/20/contents.html

1.4 更新日志

原文：docs.sqlalchemy.org/en/20/changelog/changelog_14.html

本文详细介绍了 1.4 版本中进行的单个问题级别的更改。有关 1.4 中的新内容的叙述性概述，请参阅 SQLAlchemy 1.4 有什么新功能？。

1.4.53

无发布日期

1.4.52

发布日期：2024 年 3 月 4 日

orm

• [orm] [bug] 修复了 ORM 中 with_loader_criteria() 不会应用到 Select.join() 的 bug，其中 ON 子句被给定为普通的 SQL 比较，而不是作为关系目标或类似的东西。 这是在 2.0 版本中修复的同一问题的回溯，针对 2.0.22。 参考：#10365

1.4.51

发布日期：2024 年 1 月 2 日

orm

• [orm] [bug] 改进了首次在版本 0.9.8 中实施的修复项，该修复项最初在 #3208 中发布，其中声明性内部使用的类的注册表可能在个别映射类在同时进行垃圾回收而新的映射类正在构造时出现竞态条件的情况下，发生某些测试套件配置或动态类创建环境中可能发生的情况。除了已经添加的弱引用检查外，还首先复制正在迭代的项目列表，以避免“在迭代时更改列表”错误。拉取请求由 Yilei Yang 提供。 参考：#10782

asyncio

• [asyncio] [bug] 修复了异步连接池的关键问题，其中调用 AsyncEngine.dispose() 会产生一个新的连接池，但没有完全重新建立使用 asyncio 兼容互斥锁的情况，导致在使用并发功能如 asyncio.gather() 时，在 asyncio 上下文中使用普通的 threading.Lock() 导致死锁。 参考：#10813

mysql

• [mysql] [bug] 修复了在使用旧于 1.0 版本的 PyMySQL 的池预先 ping 时由修复票据 #10492 引入的回归问题。 参考：#10650

1.4.50

发布日期：2023 年 10 月 29 日

orm

• [orm] [bug] 修复了某些形式的 ORM “注解” 无法对使用 Select.join() 进行关系目标的子查询进行的问题。这些注解在特殊情况下使用子查询时使用，例如在 PropComparator.and_() 和其他 ORM 特定情况下。 参考资料：#10223

sql

• [sql] [错误] 修复了在某些情况下，使用 literal_execute=True 时多次使用相同的绑定参数会由于迭代问题导致渲染错误值的问题。 参考资料：#10142
• [sql] [错误] 修复了对 Column 或其他 ColumnElement 的反序列化失败无法恢复正确的“比较器”对象的基本问题，该对象用于生成特定于类型对象的 SQL 表达式。 参考资料：#10213

模式

• [模式] [错误] 修改了仅对 Oracle 后端有效的 Identity.order 参数的渲染，该参数是 Sequence 和 Identity 的一部分，不再对诸如 PostgreSQL 等其他后端有效。未来的版本将重新命名 Identity.order、Sequence.order 和 Identity.on_null 参数，使用 Oracle 特定的名称，废弃旧名称，这些参数仅适用于 Oracle。 参考资料：#10207

mysql

• [mysql] [用例] 更新了 aiomysql 方言，因为该方言似乎又得到了维护。将其重新添加到使用版本 0.2.0 进行 ci 测试。
• [mysql] [错误] 修复了 MySQL“预先 ping”例程中的新不兼容性，其中传递给connection.ping()的False参数，旨在禁用不需要的“自动重新连接”功能，在 MySQL 驱动程序和后端中已弃用，并且正在为某些版本的 MySQL 原生客户端驱动程序生成警告。对于 mysqlclient，它已被删除，而对于 PyMySQL 和基于 PyMySQL 的驱动程序，该参数将在某个时候被弃用和删除，因此 API 内省用于未来防范这些不同的删除阶段。 参考：#10492

mssql

• [mssql] [bug] [reflection] 修复了对具有大型身份起始值（超过 18 位数）的 bigint 列的身份列反射将失败的问题。 参考：#10504

1.4.49

发布日期：2023 年 7 月 5 日

platform

• [platform] [usecase] 改进兼容性以完全与 Python 3.12 兼容

sql

• [sql] [bug] 修复了使用“flags”时ColumnOperators.regexp_match()无法生成“稳定”缓存密钥的问题，即缓存密钥每次都会更改，导致缓存污染。对于带有标志和实际替换表达式的ColumnOperators.regexp_replace()也存在相同的问题。现在，标志被表示为固定的修改器字符串，呈现为安全字符串，而不是绑定参数，并且替换表达式在“二进制”元素的主要部分内建立，以便生成适当的缓存密钥。 请注意，作为此更改的一部分，ColumnOperators.regexp_match.flags和ColumnOperators.regexp_replace.flags已修改为仅呈现为文字字符串，而以前它们呈现为完整的 SQL 表达式，通常是绑定参数。这些参数应始终作为纯 Python 字符串传递，而不是作为 SQL 表达式构造；不应该预期在实践中使用 SQL 表达式构造用于此参数，因此这是一个不兼容的更改。 此更改还修改了生成的表达式的内部结构，用于带有或不带有标志的ColumnOperators.regexp_replace()以及带有标志的ColumnOperators.regexp_match()。第三方方言可能已经实现了自己的正则表达式实现（在搜索中找不到这样的方言，所以预计影响较小），它们需要调整结构的遍历以适应。 参考：#10042
• [sql] [bug] 修复了一个在主要是内部使用的CacheKey结构中的问题，其中__ne__()运算符没有被正确实现，导致比较CacheKey实例时得到荒谬的结果。

extensions

• [extensions] [bug] 修复了与 mypy 1.4 一起使用的 mypy 插件中的问题。

1.4.48

发布日期：2023 年 4 月 30 日

orm

• [orm] [bug] 修复了一个关键的缓存问题，其中aliased()和hybrid_property()表达式组合的组合将导致缓存键不匹配，从而导致缓存键保留了实际的aliased()对象，同时又不匹配等价构造的缓存键，填满了缓存。 参考：#9728
• [orm] [bug] 修复了一个 bug，在各种 ORM 特定的 getter 函数（比如ORMExecuteState.is_column_load、ORMExecuteState.is_relationship_load、ORMExecuteState.loader_strategy_path等）中，如果 SQL 语句本身是“复合选择”（如 UNION），则会抛出AttributeError。 参考：#9634
• [orm] [bug] 修复了当使用“关联到别名类”的功能并且在加载器中指定了一个递归的急加载器，比如 lazy="selectinload"，与另一个急加载器在相反的一侧结合使用时可能出现的无限循环。循环检查已修复以包括别名类关系。 引用：#9590

1.4.47

发布日期：2023 年 3 月 18 日

sql

• [sql] [bug] 修复了使用Update.values()方法中与列相同名称的bindparam()的错误/回归，以及Insert.values()方法中与列相同名称的bindparam()的错误/回归，仅在 2.0 版本中会在某些情况下静默地失败，不会遵守呈现参数的 SQL 表达式，而是用同名的新参数替换表达式并丢弃 SQL 表达式的任何其他元素，比如 SQL 函数等。具体情况是针对 ORM 实体而不是普通的Table 实例构建的语句，但如果语句在使用时被调用了一个Session或一个Connection，则会发生。 Update 的一部分问题既出现在 2.0 版本中又出现在 1.4 版本中，并且被回溯到 1.4 版本。 引用：#9075
• [sql] [bug] 修复了CreateSchema和DropSchemaDDL 构造的字符串化，当没有方言时会导致 AttributeError。 引用：#7664
• [sql] [bug] 修复了临界的 SQL 缓存问题，其中使用 Operators.op() 自定义运算符函数不会生成适当的缓存键，导致 SQL 缓存的效果降低。 引用：#9506

mypy

• [mypy] [bug] 调整了 mypy 插件，以适应在使用 SQLAlchemy 1.4 时可能进行的一些针对问题#236 sqlalchemy2-stubs 的更改。这些更改在 SQLAlchemy 2.0 内保持同步。这些更改还与旧版本的 sqlalchemy2-stubs 兼容。
• [mypy] [bug] 修复了 mypy 插件中的崩溃，该崩溃可能发生在 1.4 和 2.0 版本上，如果使用一个装饰器来引用一个表达式中的装饰器（例如@Backend.mapper_registry.mapped），该表达式具有两个以上的组件，则会发生崩溃。现在会忽略这种情况；使用插件时，装饰器表达式需要是两个组件（即@reg.mapped）。 参考文献：#9102

postgresql

• [postgresql] [bug] 添加了对 asyncpg 方言的支持，以在可用时返回cursor.rowcount值以用于 SELECT 语句。虽然这不是cursor.rowcount的典型用法，但是其他 PostgreSQL 方言通常提供此值。拉取请求由 Michael Gorven 提供。 参考文献：#9048

mysql

• [mysql] [usecase] 添加了对 MySQL 索引反射的支持，以正确反映以前被忽略的mysql_length字典。 参考文献：#9047

mssql

• [mssql] [bug] 修复了一个 bug，在此 bug 中，使用方括号给出的模式名称，但名称内没有点，例如Table.schema的参数，将不会在 SQL Server 方言的上下文中解释为解释为标记定界符的文档化行为，首次在#2626 中添加，当在反射操作中引用模式名称时。有关#2626 行为的最初假设是，只有在存在点时，方括号的特殊解释才是重要的，但是在实践中，由于这些不是常规或定界标识符中的有效字符，因此在所有 SQL 渲染操作中都不包括方括号作为标识符名称的一部分。拉取请求由 Shan 提供。 参考文献：#9133

oracle

• [oracle] [bug] 将ROWID添加到反射类型中，因为此类型可能在“CREATE TABLE”语句中使用。 参考文献：#5047

1.4.46

发布日期：2023 年 1 月 3 日

一般

• [general] [change] 现在，当SQLALCHEMY_WARN_20环境变量未设置时，首次发出任何 SQLAlchemy 2.0 弃用警告时，将发出新的弃用“超级警告”。警告至多发出一次，然后设置一个布尔值以防止其再次发出。 此弃用警告旨在通知未在其要求文件中设置适当约束的用户，阻止对意外 SQLAlchemy 2.0 升级的惊喜，并提醒 SQLAlchemy 2.0 升级流程已经可用，因为预计很快将发布第一个完整的 2.0 版本。可以通过设置环境变量 SQLALCHEMY_SILENCE_UBER_WARNING 为 "1" 来关闭弃用警告。 另请参阅 SQLAlchemy 2.0 - 主要迁移指南 参考：#8983
• [general] [bug] 修复了基本兼容模块调用 platform.architecture() 来检测某些系统属性的问题，这导致针对一些情况下不可用的系统级 file 调用发生了过度广泛的系统调用，包括在某些安全环境配置中。 参考：#8995

orm

• [orm] [bug] 修复了用于 DML 语句（如 Update 和 Delete）的内部 SQL 遍历中的问题，该问题可能会导致与 ORM 更新/删除功能一起使用 lambda 语句时出现特定问题，以及其他潜在问题。 参考：#9033

engine

• [engine] [bug] 修复了连接池中长期存在的竞态条件，该条件可能在使用 eventlet/gevent 的猴子补丁方案以及使用 eventlet/gevent Timeout 条件时发生，并且连接池检出由于超时而中断时未能清理失败状态，导致底层连接记录以及有时数据库连接本身“泄漏”，使得连接池处于无效状态，其中某些条目无法访问。该问题首次在 SQLAlchemy 1.2 中被识别并修复，针对 #4225，然而，在该修复中检测到的故障模式未能适应于 BaseException，而不是 Exception，这导致无法捕获 eventlet/gevent Timeout。此外，还发现并加固了初始连接池连接中的一个块，使用了 BaseException -> “清理失败连接” 块，以适应此位置中的相同条件。非常感谢 Github 用户 @niklaus 在识别和描述此复杂问题方面的坚持努力。 参考：#8974

sql

• [sql] [bug] 添加了参数 FunctionElement.column_valued.joins_implicitly, 这在使用表值或列值函数时防止“笛卡尔积”警告时非常有用。此参数已经为 FunctionElement.table_valued() 在 #7845 中引入，但未能为 FunctionElement.column_valued() 添加。 参考：#9009
• [sql] [错误] 修复了 SQL 编译失败的 bug（2.0 中的断言失败，1.4 中的 NoneType 错误），当使用的表达式的类型包括 TypeEngine.bind_expression()，在与 literal_binds 编译器参数一起使用时处于“扩展”（即“IN”）参数的上下文中时。 参考：#8989
• [sql] [错误] 修复了 lambda SQL 功能中的问题，其中文字值的计算类型不会考虑“比较类型”的类型强制转换规则，导致 SQL 表达式（例如与JSON元素的比较等）缺乏类型信息。 参考：#9029

postgresql

• [postgresql] [用例] 添加了 PostgreSQL 类型 MACADDR8。Pull 请求由 Asim Farooq 提供。 参考：#8393
• [postgresql] [错误] 修复了 PostgreSQL Insert.on_conflict_do_update.constraint 参数接受 Index 对象的 bug，然而，它不会将此索引展开为其各个索引表达式，而是将其名称渲染到 ON CONFLICT ON CONSTRAINT 子句中，这不被 PostgreSQL 接受；“约束名”形式只接受唯一或排除约束名。该参数仍然接受索引，但现在将其展开为其组成表达式以进行渲染。 参考：#9023

sqlite

• [sqlite] [错误] 修复了 1.4.45 中对 SQLite 部分索引的反射支持引起的回归问题，该问题是由于早期版本的 SQLite（可能是 3.8.9 之前的版本）中的index_list pragma 命令未返回当前预期的列数，导致在反射表和索引时引发异常。 参考：#8969

测试

• [测试] [错误] 修复了 tox.ini 文件中的问题，其中 tox 4.0 系列对“passenv”的格式进行了更改，导致 tox 无法正确运行，特别是在 tox 4.0.6 中引发错误。
• [测试] [错误] 为第三方方言添加了新的排除规则，称为unusual_column_name_characters，可以关闭不支持带有点、斜杠或百分号等特殊字符的列名的第三方方言，即使名称已经正确引用。 参考：#9002

1.4.45

发布日期：2022 年 12 月 10 日

orm

• [orm] [错误] 修复了Session.merge()无法保留使用relationship.viewonly参数指示的关系属性的当前加载内容的错误，从而破坏了使用Session.merge()从缓存和其他类似技术中拉取完全加载的对象的策略。在相关变更中，修复了一个问题，即包含已加载的关系但在映射上仍配置为lazy='raise'的对象在传递给Session.merge()时会失败；在合并过程中暂停了对“raise”的检查，假定Session.merge.load参数保持其默认值True。 总的来说，这是对 1.4 系列中引入的一项变更的行为调整，截至#4994，该变更将“merge”从默认应用于“viewonly”关系的级联集中移除。由于“viewonly”关系在任何情况下都不会持久化，允许它们的内容在“merge”期间传输不会影响目标对象的持久化行为。这使得Session.merge()能够正确地满足其中一个用例，即向Session中添加在其他地方加载的对象，通常是为了从缓存中恢复。 参考：#8862
• [orm] [错误] 修复了with_expression()中的问题，在这种情况下，由从封闭 SELECT 引用的列组成的表达式在某些情境下不会正确渲染 SQL，即使表达式具有与使用query_expression()的属性匹配的标签名称，即使query_expression()没有默认表达式。目前，如果query_expression()确实有默认表达式，那个标签名称仍然用于该默认表达式，并且具有相同名称的额外标签将继续被忽略。总的来说，这种情况相当棘手，可能需要进一步调整。 参考：#8881

engine

• [engine] [错误] 修复了Result.freeze()方法无法用于使用text()或Connection.exec_driver_sql()的文本 SQL 的问题。 参考：#8963

sql

• [sql] [用例] 现在，在任何“文字绑定参数”渲染操作失败的情况下，会抛出一个信息性的重新引发，指示值本身和正在使用的数据类型，以帮助调试在语句中渲染文字参数时出现的��题。 参考：#8800
• [sql] [错误] 修复了关于渲染绑定参数位置和有时身份的一系列问题，例如用于 SQLite、asyncpg、MySQL、Oracle 等的参数。一些编译形式无法正确维护参数的顺序，例如 PostgreSQL 的regexp_replace()函数，首次引入于#4123的CTE构造的“嵌套”功能，以及使用 Oracle 的FunctionElement.column_valued()方法形成的可选择表。 参考：#8827

asyncio

• [asyncio] [错误] 从 AsyncResult 中删除了无效的 merge() 方法。此方法从未起作用，并且错误地包含在 AsyncResult 中。 参考：#8952

postgresql

• [postgresql] [bug] 调整了 PostgreSQL 方言在从表中反射列时考虑列类型的方式，以适应可能从 PG format_type() 函数返回 NULL 的替代后端。 参考：#8748

sqlite

• [sqlite] [usecase] 添加了对 SQLite 后端反映“DEFERRABLE”和“INITIALLY”关键字的支持，这些关键字可能存在于外键构造中。感谢 Michael Gorven 的贡献。 参考：#8903
• [sqlite] [usecase] 添加了对 SQLite 方言中包含在索引中的基于表达式的 WHERE 条件的反射支持，类似于 PostgreSQL 方言的方式。感谢 Tobias Pfeiffer 的贡献。 参考：#8804
• [sqlite] [bug] 回溯了一个关于 SQLite 反射附加模式中唯一约束的修复，作为 #4379 的一小部分在 2.0 中发布。以前，附加模式中的唯一约束会被 SQLite 反射忽略。感谢 Michael Gorven 的贡献。 参考：#8866

oracle

• [oracle] [bug] 继续修复 Oracle 修复 #8708，在 1.4.43 中发布，其中以下划线开头的绑定参数名称（Oracle 不允许）仍未在所有情况下正确转义。 参考：#8708
• [oracle] [bug] 修复了 Oracle 编译器中 FunctionElement.column_valued() 语法不正确的问题，未正确限定源表，导致名称 COLUMN_VALUE 不正确。 参考：#8945

1.4.44

发布日期：2022 年 11 月 12 日

sql

• [sql] [bug] 修复了缓存键生成中识别到的关键内存问题，对于使用大量带有子查询的 ORM 别名的非常大且复杂的 ORM 语句，缓存键生成可能会产生比语句本身大几个数量级的过大键。非常感谢 Rollo Konig Brock 在最终识别此问题方面的非常耐心和长期帮助。 参考：#8790

postgresql

• [postgresql] [bug] [mssql] 仅针对 PostgreSQL 和 SQL Server 方言，调整了编译器，以便在渲染 RETURNING 子句中的列表达式时，对于生成标签的 SQL 表达式元素建议使用“非匿名”标签，主要示例是 SQL 函数，可能作为列的类型的一部分发出，其中标签名称应默认与列的名称匹配。这恢复了在版本 1.4.21 中由于#6718，#6710引起的一个未明确定义的行为变更。Oracle 方言具有不同的 RETURNING 实现，并不受此问题影响。版本 2.0 对其他后端的广泛扩展的 RETURNING 支持进行了全面更改。 参考：#8770

oracle

• [oracle] [bug] 修复了 Oracle 方言中的问题，其中针对完整的Table对象一次性使用 insert(some_table).values(...).returning(some_table) 的 INSERT 语句会失败执行，引发异常。

测试

• [tests] [bug] 修复了测试套件中使用 --disable-asyncio 参数时的问题，该参数实际上未能运行 greenlet 测试，并且也未能阻止套件在整个运行过程中使用“包装” greenlet。设置此参数后，确保整个运行过程中不会发生任何 greenlet 或 asyncio 使用。 参考：#8793
• [tests] [bug] 调整了测试套件，测试 Mypy 插件，以适应 Mypy 0.990 中如何处理消息输出的更改，这影响了确定是否应为特定文件打印注释和错误时 sys.path 的解释。该更改使测试套件中的文件在使用 mypy API 运行时不再产生消息，从而破坏了测试套件。

1.4.43

发布日期：2022 年 11 月 4 日

orm

• [orm] [bug] 修复了连接的急切加载中的问题，在其中，当急切加载跨越三个映射器时，具有特定外部/内部连接的急切加载组合时，会发生断言失败。 参考：#8738
• [orm] [bug] 修复了涉及 Select 构造的错误，其中 Select.select_from() 与 Select.join() 的组合，以及使用 Select.join_from() 时，如果查询的列子句未明确包括 JOIN 的左侧实体，则会导致 with_loader_criteria() 功能以及单表继承查询所需的 IN 条件不会呈现。现在正确的实体被传递给内部生成的 Join 对象，以便正确添加与左侧实体的条件。 引用：#8721
• [orm] [bug] 当在特定“加载器路径”中添加到加载器选项时引发了一个信息性异常，例如在 Load.options() 中使用它时，现在会引发一个信息性异常。此使用不受支持，因为 with_loader_criteria() 仅用作顶级加载器选项。以前，会生成内部错误。 引用：#8711
• [orm] [bug] 为 Session.get() 改进了“字典模式”，以便于在命名字典中指示指向主键属性名称的同义词名。 引用：#8753
• [orm] [bug] 修复了继承映射器中“selectin_polymorphic”加载的问题，如果 Mapper.polymorphic_on 参数引用的 SQL 表达式不直接映射到类上，则此问题将无法正确工作。 引用：#8704
• [orm] [bug] 修复了在使用Query对象作为迭代器时，如果在迭代过程中引发了用户定义的异常情况，则底层的 DBAPI 游标不会被关闭的问题，从而导致迭代器被 Python 解释器关闭。当使用Query.yield_per()创建服务器端游标时，这将导致通常与服务器端游标不同步相关的 MySQL 问题，并且由于无法直接访问Result对象，最终用户代码无法访问游标以关闭它。 为了解决这个问题，在迭代器方法中应用了对GeneratorExit的捕获，当迭代器被中断时，将关闭结果对象，并且根据定义将被 Python 解释器关闭。 作为 1.4 系列实施的这一变更的一部分，确保了所有Result实现都提供了.close()方法，包括ScalarResult、MappingResult。2.0 版本的这一变更还包括了用于与Result类一起使用的新上下文管理器模式。 参考：#8710

引擎

• [engine] [bug] [regression] 修复了当Connection关闭并正在将其 DBAPI 连接返回到连接池时，在某些情况下不会调用PoolEvents.reset()事件钩子的问题。 情况是当Connection在将连接返回到池的过程中已经发出了.rollback()时，然后会指示连接池放弃执行自己的“重置”以节省额外的方法调用。然而，这阻止了在此钩子中使用自定义池重置方案，因为这样的钩子定义上做的不仅仅是调用.rollback()，并且需要在所有情况下被调用。这是在 1.4 版本中出现的一个退化。 对于版本 1.4，PoolEvents.checkin()仍然可以作为用于自定义“重置”实现的替代事件钩子。版本 2.0 将提供一个改进版本的PoolEvents.reset()，它被用于其他情况，例如终止 asyncio 连接，并且还传递了有关重置的上下文信息，以允许“自定义连接重置”方案以不同的方式响应不同的重置情况。 参考：#8717
• [engine] [bug] 确保所有Result对象都包括Result.close()方法以及Result.closed属性，包括ScalarResult和MappingResult。 参考：#8710

sql

• [sql] [bug] 修复了一个问题，该问题阻止了literal_column()构造在Select构造上正常工作，以及在其他潜在的可能生成“匿名标签”的地方，如果字面表达式包含可能干扰格式字符串的字符，例如括号，由于“匿名标签”结构的实现细节。 参考：#8724

mssql

• [mssql] [bug] 修复了使用Inspector.has_table()针对带有 SQL Server 方言的临时表时会在某些 Azure 变体上失败的问题，因为不支持在这些服务器版本上的不必要的信息模式查询。来自 Mike Barry 的拉取请求。 参考：#8714
• [mssql] [bug] [reflection] 修复了针对带有 SQL Server 方言的视图使用时的Inspector.has_table()的问题，由于 1.4 系列中去除了对 SQL Server 的此支持而导致错误返回False。此问题在使用不同反射架构的 2.0 系列中不存在。添加了测试支持以确保has_table()保持符合视图的规范。 引用：#8700

oracle

• [oracle] [bug] 修复了一个问题，即包含通常需要在 Oracle 中用引号引用的字符的绑定参数名称，包括从同名数据库列自动派生的参数名称，在使用 Oracle 方言的“expanding parameters”时不会被转义，从而导致执行错误。 Oracle 方言使用的绑定参数的通常“引用”不适用于“expanding parameters”架构，因此现在使用一系列特定于 Oracle 的字符/转义来转义。 引用：#8708
• [oracle] [bug] 修复了一个问题，即在第一次连接时查询nls_session_parameters视图以获取默认的小数点字符可能不可用，具体取决于 Oracle 连接模式，因此可能会引发错误。检测小数字符的方法已经简化为直接测试一个小数值，而不是读取系统视图，这在任何后端/驱动程序上都有效。 引用：#8744

1.4.42

发布日期：2022 年 10 月 16 日

orm

• [orm] [bug] 当传递给Session.execute()等方法时，Session.execute.bind_arguments 字典不再发生变化；相反，它会被复制到内部字典以进行状态更改。在其他方面，这修复了一个问题，即传递给Session.get_bind()方法的“clause”将错误地引用了用于“fetch”同步策略的Select构造，而实际发出的查询是Delete或Update。这会干扰“路由会话”的处理方法。 引用：#8614
• [orm] [bug] 在 ORM 配置中当将映射类的本地列应用于任何不包含相同表列的引用类时，会发出警告，这时应用了显式的remote() 注解。理想情况下，这在某个时候会引发错误，因为从映射的角度来看这是不正确的。 参考文献：#7094
• [orm] [bug] 当尝试配置一个在继承层次结构中的映射类时，其中映射器没有给定任何多态标识时会发出警告，但是却分配了一个多态鉴别器列。这样的类如果从不打算直接加载，则应该是抽象的。 参考文献：#7545
• [orm] [bug] [regression] 在contains_eager()中修复了 1.4 版本的回归问题，在其中 joinedload() 的“包装成子查询”逻辑会意外地触发使用 contains_eager() 函数的类似语句（例如使用 distinct()、limit() 或 offset() 的语句），然后会导致使用一些 SQL 标签名称和别名的查询的次要问题。这种“包装”对于 contains_eager() 并不合适，因为它一直遵循的合同是用户定义的 SQL 语句未经修改，只是添加了适当的列以被提取。 参考文献：#8569
• [orm] [bug] [regression] 修复了使用同步会话=’fetch’的 ORM update() 会因为在刷新对象时使用评估器来确定 SET 子句中的表达式的 Python 值而失败的回归问题；如果评估器针对非数值值（例如 PostgreSQL JSONB）使用数学运算符，那么评估不可用的条件将无法正确检测。评估器现在只对数值类型限制了数学突变运算符的使用，异常是“+”继续对字符串起作用。SQLAlchemy 2.0 可能会通过完全获取 SET 值而不是使用评估来进一步更改此设置。 参考文献：#8507

engine

• [engine] [bug] 修复了在 select() 构造的列子句中混合使用“*”和额外显式命名列表达式会导致结果列定位有时将标签名称或其他非重复名称视为模糊目标的问题。 参考文献：#8536

asyncio

• [asyncio] [bug] 改进了 asyncio.shield() 在上下文管理器中的实现，如在 #8145 中所添加的，使得“关闭”操作被封装在一个 asyncio.Task 中，然后在操作进行时被强引用。这符合 Python 文档中指出的任务否则不会被强引用的情况。 参考文献：#8516

postgresql

• [postgresql] [usecase] aggregate_order_by 现在支持缓存生成。 参考文献：#8574

mysql

• [mysql] [bug] 调整了在测试视图时用于匹配“CREATE VIEW”的正则表达式，以更灵活地工作，不再需要中间的特殊关键字“ALGORITHM”，这原本是可选的，但却不能正确工作。此更改允许视图反射在诸如 StarRocks 这样的兼容 MySQL 的变体上更完整地工作。拉取请求由 John Bodley 提供。 参考文献：#8588

mssql

• [mssql] [bug] [regression] 修复了 SQL Server 隔离级别获取中的另一个回归问题（见 #8231, #8475），这次是关于“Microsoft Dynamics CRM 数据库通过 Azure Active Directory”，显然完全缺少 system_views 视图。错误捕获已扩展，保证这种方法绝对不会失败，只要有数据库连接存在。 参考文献：#8525

1.4.41

发布日期：2022 年 9 月 6 日

orm

• [orm] [bug] [events] 修复了一个事件监听问题，即当事件监听器添加到一个超类时，如果创建了一个子类，那么子类自己的监听器就会丢失。一个实际的例子是在为 Session 类关联事件之后创建的 sessionmaker 类。 参考文献：#8467
• [orm] [bug] 加强了aliased()和with_polymorphic()构造的缓存键策略。虽然很难（如果有的话）演示涉及实际语句被缓存的问题，但是这两个构造在其缓存键中未包含足够使它们在缓存时独特的内容，以使仅对别名构造进行缓存是准确的。 参考：#8401
• [orm] [bug] [regression] 修复了在 1.4 系列中出现的回归问题，其中作为子查询放置的继承查询在该实体的封闭查询中会失败以正确渲染 JOIN。这个问题在 1.4.18 版本之前和之后的两种不同情况下表现出来（相关问题 #6595），在一种情况下会渲染两次 JOIN，而在另一种情况下会完全丢失 JOIN。为了解决这个问题，已经缩减了应用“多态加载”的条件，不再为简单的继承查询调用它。 参考：#8456
• [orm] [bug] 修复了sqlalchemy.ext.mutable扩展中的问题，如果对象在通过 Session.merge() 合并时同时传递 Session.merge.load 为 False，则会丢失到父对象的集合链接。 参考：#8446
• [orm] [bug] 修复了涉及with_loader_criteria()的问题，其中在 lambda 内使用的闭包变量作为绑定参数值在语句被缓存后不会正确传递到额外的关系加载器（如selectinload()和lazyload()），而是使用过时的原始缓存值。 参考：#8399

sql

• [sql] [bug] 修复了使用 table() 构造时，将字符串传递给 table.schema 参数时，未考虑“schema”字符串在生成缓存键时，导致如果使用了具有不同模式的多个同名 table() 构造，可能会导致缓存冲突的问题。 参考：#8441

asyncio

• [asyncio] [bug] 集成了对 asyncpg 的 terminate() 方法调用的支持，用于在连接池回收可能超时的连接、在垃圾回收未正常关闭连接时以及连接已失效时。这允许 asyncpg 放弃连接而无需等待可能导致长时间超时的响应。 参考：#8419

mssql

• [mssql] [bug] [regression] 修复了在 1.4.40 中发布的 #8231 修复���起的回归，当用户没有权限查询 dm_exec_sessions 或 dm_pdw_nodes_exec_sessions 系统视图以确定当前事务隔离级别时，连接会失败。 参考：#8475

1.4.40

发布日期：2022 年 8 月 8 日

orm

• [orm] [bug] 修复了在多次引用 CTE 与多态 SELECT 结合时可能导致构建多个相同 CTE 的“克隆体”，然后触发这两个 CTE 作为重复项的问题。为了解决这个问题，在发生这种情况时，这两个 CTE 会进行深度比较以确保它们是等价的，然后被视为等价。 参考：#8357
• [orm] [bug] 通过传递唯一的 SELECT * 参数（通过字符串、text() 或 literal_column()）给 select() 构造，将被解释为核心级别的 SQL 语句，而不是 ORM 级别的语句。这样，当 * 扩展以匹配任意数量的列时，将返回结果中的所有列。ORM 级别的 select() 的解释需要提前知道所有 ORM 列的名称和类型，而当使用 '*' 时无法实现。 如果在 ORM 语句中同时使用'*和其他表达式，会引发错误，因为 ORM 无法正确解释这种情况。 参考：#8235

orm 声明式

• [orm] [declarative] [bug] 修复了一个问题，即将一系列设置为抽象或混合声明类的类层次结构上的独立列声明在超类上，然后正确复制到declared_attr可调用的类上，该类希望在后代类上使用它们。 参考：#8190

引擎

• [engine] [usecase] 在核心中为Connection实现了新的Connection.execution_options.yield_per执行选项，以模仿 ORM 中相同的 yield_per 选项。该选项同时设置Connection.execution_options.stream_results选项，并调用Result.yield_per()，以提供最常见的流式结果配置，也与 ORM 使用情况中的使用模式相一致。 另请参阅 使用服务器端游标（即流式结果） - 修订文档
• [engine] [bug] 修复了Result中的错误，当使用缓冲结果策略时，如果使用的方言不支持显式的“服务器端游标”设置，则不会使用Connection.execution_options.stream_results。这是一个错误，因为像 SQLite 和 Oracle 这样的 DBAPI 已经使用了非缓冲结果获取方案，仍然受益于部分结果获取的使用。在设置Connection.execution_options.stream_results的所有情况下现在都使用“缓冲”策略。
• [engine] [bug] 添加了 FilterResult.yield_per()，以便结果实现（如 MappingResult、ScalarResult 和 AsyncResult）可以访问此方法。 参考：#8199

sql

• [sql] [bug] 调整了字符串包含函数 .contains()、.startswith()、.endswith() 的 SQL 编译，强制使用字符串连接运算符，而不是依赖于加法运算符的重载，以便非标准的用法，例如字节字符串仍然生成字符串连接运算符。 参考：#8253

mypy

• [mypy] [bug] 修复了使用 lambda 作为 Column 默认值时 mypy 插件崩溃的问题。感谢 tchapi 提交的拉取请求。 参考：#8196

asyncio

• [asyncio] [bug] 在使用 AsyncConnection 或 AsyncSession 作为上下文管理器释放对象时，特别是在 __aexit__() 上下文管理器退出时，将 asyncio.shield() 添加到连接和会话释放过程中。 当上下文管理器完成时，这似乎有助于使用其他并发库（如 anyio、uvloop）时取消任务时正确释放连接池中的连接。 参考：#8145

postgresql

• [postgresql] [bug] 修复了 psycopg2 方言中的问题，在实现了 #4392 的“多主机”功能时，可以将多个 host:port 对作为查询字符串传递，例如 ?host=host1:port1&host=host2:port2&host=host3:port3，但没有正确实现，因为它没有适当地传播“port”参数。 没有使用不同“port”的连接可能正常工作，而具有某些条目的“port”可能会不正确地传递该主机名。 现在已更正格式以正确传递主机/端口。 作为这一变化的一部分，保持了另一种意外有效的多主机样式的支持，即以逗号分隔的?host=h1,h2,h3&port=p1,p2,p3。这种格式更符合 libpq 的查询字符串格式，而以前的格式受到 libpq URI 格式的不同方面的启发，但并不完全相同。 如果两种样式混合在一起，会引发错误，因为这是模棱两可的。 参考：#4392

mssql

• [mssql] [bug] 修复了与 SQL Server pyodbc 方言一起使用 ORM 对象的新使用模式在 使用 INSERT、UPDATE 和 ON CONFLICT（即 upsert）返回 ORM 对象时无法正确工作的问题。 参考：#8210
• [mssql] [bug] 修复了在 Azure Synapse Analytics 上 SQL Server 方言的当前隔离级别查询失败的问题，这是由于这个数据库在错误发生后处理事务回滚的方式。已修改初始查询，不再依赖于在检测适当的系统视图时捕获错误。此外，为了更好地支持这个数据库的非常特定的“回滚”行为，实现了新参数ignore_no_transaction_on_rollback，指示回滚应忽略 Azure Synapse 错误‘No corresponding transaction found. (111214)’，如果没有与 Python DBAPI 冲突的事务存在。 初版补丁和宝贵的调试协助由@ww2406 提供。 另见 避免 Azure Synapse Analytics 上的事务相关异常 参考：#8231

杂项

• [bug] [types] 修复了TypeDecorator在装饰ARRAY数据类型时，不会正确代理__getitem__()运算符的问题，没有明确的解决方法。 参考：#7249

1.4.39

发布日期：2022 年 6 月 24 日

orm

• [orm] [bug] [regression] 修复了由#8133引起的回归，其中可变属性的 pickle 格式被更改，而没有回退到识别旧格式，导致 SQLAlchemy 的就地升级无法再读取来自旧版本的 pickled 数据。现在增加了检查以及对旧格式的回退。 参考：#8133

1.4.38

发布日期：2022 年 6 月 23 日

orm

• [orm] [bug] [regression] 修复了由#8064引起的回归，其中对列对应性的特定检查过于宽松，导致一些 ORM 子查询的渲染不正确，例如那些使用PropComparator.has()或PropComparator.any()与使用传统别名功能的联接继承查询。 参考：#8162
• [orm] [bug] [sql] 修复了在使用 ORM 执行语句时，GenerativeSelect.fetch()未被应用的问题。 参考：#8091
• [orm] [bug] 修复了一个问题，即with_loader_criteria()选项无法被 pickle，这在与缓存方案一起传播到懒加载器时是必要的。目前，唯一支持的可 pickle 形式是将“where criteria”作为一个固定的模块级可调用函数传递，该函数生成一个 SQL 表达式。一个临时的“lambda”无法被 pickle，而一个 SQL 表达式对象通常不能直接完全 pickle。 参考：#8109

engine

• [engine] [bug] 修复了一个阻止诸如Connection等关键对象具有正确__weakref__属性的弃用警告类装饰器，导致像 Python 标准库inspect.getmembers()之类的操作失败。 参考：#8115

sql

• [sql] [bug] 修复了与lambda_stmt()相关的多个观察到的竞争条件，包括在多个同时线程中首次分析新的 Python 代码对象时出现的初始“dogpile”问题，这导致了性能问题以及一些内部状态的损坏。此外，修复了观察到的竞争条件，当在不同线程中编译或访问正在被克隆的表达式构造时可能发生，因为 Python 版本在 3.10 之前的版本中，由于记忆化属性在迭代时改变__dict__，特别是 lambda SQL 构造对此很敏感，因为它持久地保留一个单一的语句对象。迭代已经被改进为使用dict.copy()，无论是否有额外的迭代。 参考：#8098
• [sql] [bug] 加强了Cast和其他“包装”列构造的机制，以更完全地保留被包装的Label构造，包括标签名称将在Subquery的.c集合中被保留。该标签已经能够在它被包装在内部的构造外正确地呈现 SQL。 参考：#8084
• [sql] [错误] 调整了对#8056所做的修复，该修复调整了具有特殊字符的绑定参数名称的转义方式，以便在 SQL 编译步骤之后翻译转义名称，这破坏了 FAQ 中一个已发布的示例，该示例说明了如何将参数名称合并到编译后的 SQL 字符串的输出中。该更改恢复了来自compiled.params的转义名称，并向SQLCompiler.construct_params()添加了一个条件参数，命名为escape_names，默认为True，以默认情况下恢复旧行为。 参考资料：#8113

模式

• [模式] [错误] 修复了涉及Table.include_columns和Table.resolve_fks参数的错误；这些很少使用的参数显然无法为引用外键约束的列工作。 在第一种情况下，引用外键的未包含列仍然会尝试创建一个ForeignKey对象，在尝试解析外键约束的列时会产生错误；引用被跳过的列的外键约束现在与具有相同条件的Index和UniqueConstraint对象一样在表反射过程中被省略了。但是不会产生警告，因为我们可能希望在 2.0 中删除所有约束的 include_columns 警告。 在后一种情况下，如果找不到与 FK 相关的表，则会在resolve_fks=False的情况下无法生成表别名或子查询；逻辑已被修复，因此如果未找到相关表，则ForeignKey对象仍会被代理到别名表或子查询（这些ForeignKey对象通常用于生成连接条件），但会发送一个标志，表明它是不可解析的。然后，别名表/子查询将正常工作，唯一的例外是它无法自动生成连接条件，因为缺少外键信息。这已经是这种外键约束的行为，该约束是使用非反射方法产生的，例如从不同的MetaData集合中连接Table对象。 参考：#8100, #8101
• [模式] [错误] [mssql] 修复了一个问题，当Table对象使用带有Numeric数据类型的 IDENTITY 列时，尝试调解“autoincrement”列时会产生错误，阻止了使用Column.autoincrement参数来构造Column的构造，并在尝试调用Insert构造时发出错误。 参考：#8111

扩展

• [扩展] [错误] 修复了在Mutable中的错误，在其中对包含多个Mutable启用属性的映射实例进行 pickling 和 unpickling 时，将不会正确恢复状态。 参考：#8133

1.4.37

发布日期：2022 年 5 月 31 日

orm

• [orm] [错误] 修复了使用column_property()构造包含子查询的问题，该子查询针对已映射的列属性将不正确应用 ORM 编译行为，包括针对单表继承表达式添加的“IN”表达式将无法包含的问题。 参考：#8064
• [orm] [bug] 修复了一个问题，在 ORM 结果中，在选择的列集更改时，例如使用 Select.with_only_columns() 时，会向返回的 Row 对象应用不正确的键名。 参考：#8001
• [orm] [bug] [oracle] [postgresql] 修复了一个 bug，很可能是从 1.3 版本开始的回归，当使用需要绑定参数转义的列名时（更具体地说，当在使用 Oracle 时列名需要引用时，例如以下划线开头的列名，或在某些情况下使用某些 PostgreSQL 驱动程序时，当使用包含百分号的列名时），如果版本控制列本身具有此类名称，则 ORM 版本控制功能将无法正常工作，因为 ORM 假定存在某些绑定参数命名约定，这些约定通过引号受到干扰。此问题与 #8053 相关，并且基本上修改了修复此问题的方法，修改了最初为广义绑定参数名称引用创建初始实现的原始问题 #5653。 参考：#8056

引擎

• [engine] [bug] [tests] 修复了在支持记录“stacklevel”日志时实施的问题，在 #7612 中，需要调整以使其与最近发布的 Python 3.11.0b1 版本一起使用，还修复了测试此功能的单元测试。 参考：#8019

SQL

• [sql] [bug] [postgresql] [sqlite] 修复了一个 bug，在 PostgreSQL 的 Insert.on_conflict_do_update() 方法和 SQLite 的 Insert.on_conflict_do_update() 方法中，当使用字典传递给 Insert.on_conflict_do_update.set_ 时，如果通过其键名指定列时失败，以及如果直接使用 Insert.excluded 集合作为字典时也会失败，尤其是当列有单独的“.key”时。 参考：#8014
• [sql] [bug] 当Insert.from_select()传递一个“复合选择”对象，如 UNION，但 INSERT 语句需要附加额外的列以支持来自表元数据的 Python 端或显式 SQL 默认值时，将引发一个信息性错误。在这种情况下，应传递复合对象的子查询。 引用：#8073
• [sql] [bug] 修复了使用bindparam()没有明确给出数据或类型时，可能会在表达式中被强制转换为不正确类型的问题，例如当使用Comparator.any()和Comparator.all()时。 引用：#7979
• [sql] [bug] 如果两个单独的BindParameter对象共享相同的名称，但一个在“扩展”上下文中使用（通常是 IN 表达式），另一个则不是，则会引发一个信息性错误；在这两种不同风格的用法中混合使用相同的名称不受支持，通常应在要在 IN 表达式之外接收列表值的参数上设置expanding=True参数（默认情况下设置expanding）。 引用：#8018

mysql

• [mysql] [bug] 进一步调整 MySQL PyODBC 方言，以允许完全的连接性，尽管在#7871中已经修复，但之前仍然无法正常工作。 引用：#7966
• [mysql] [bug] 为 MySQL 错误 4031 添加了断开连接代码，该错误在 MySQL >= 8.0.24 中引入，表示连接空闲超时已超过。特别是，这修复了一个问题，即预先 ping 不能在超时连接上重新连接的问题。拉取请求由 valievkarim 提供。 引用：#8036

mssql

• [mssql] [bug] 修复了以“{”开头的密码导致登录失败的问题。 引用：#8062
• [mssql] [bug] [reflection] 在使用 MSSQL 反射表列时明确指定排序规则，以防止“排序规则冲突”错误。 引用：#8035

oracle

• [oracle] [usecase] 为 Oracle 断开处理添加了两个新的错误代码，以支持 Oracle 发布的新“python-oracledb”驱动程序的早期测试。 引用：#8066
• [oracle] [bug] 修复了 SQL 编译器问题，即如果绑定参数的名称被“转义”，则绑定参数的“绑定处理”函数不会正确应用于绑定值。具体来说，这适用于 Oracle 等情况，当Column的名称本身需要引号引用时，因此在 DML 语句中生成的绑定参数使用需要绑定处理的数据类型时，引号引用的名称将用于绑定参数。 参考：#8053

1.4.36

发布日期：2022 年 4 月 26 日

orm

• [orm] [bug] [regression] 修复了回归问题，即在版本 1.4.33 中发布的为#7861所做的更改，使Insert构造部分被识别为 ORM 启用的语句，未正确传递正确的映射器/映射表状态给Session，导致绑定到使用Session.binds参数绑定到引擎和/或连接的Session的Session.get_bind()方法失败。 参考：#7936

orm 声明式

• [orm] [declarative] [bug] 修改了DeclarativeMeta元类，将cls.__dict__传递到声明扫描过程中以查找属性，而不是传递给类型的__init__()方法的单独字典。这允许用户定义的基类在__init_subclass__()中添加属性时按预期工作，因为__init_subclass__()只能影响cls.__dict__本身，而不能影响其他字典。从技术上讲，这是从 1.3 版本中使用__dict__的退化。 参考：#7900

引擎

• [engine] [bug] 修复了 C 扩展中的内存泄漏问题，当在 Python 3 中调用Row的命名成员时，如果成员不存在，可能会发生内存泄漏；特别是在 NumPy 转换期间尝试调用.__array__等成员时，但问题围绕着Row对象抛出的任何AttributeError。这个问题不适用于已经过渡到 Cython 的 2.0 版本。非常感谢 Sebastian Berg 发现了这个问题。 参考：#7875
• [引擎] [错误] 添加了一个关于Result.columns()方法中存在的 bug 的警告，当在与将返回单个 ORM 实体的Result一起传递 0 时，这表明Result.columns()方法的当前行为在这种情况下是错误的，因为Result对象将产生标量值而不是Row对象。这个问题将在 2.0 中修复，这将是一个向后不兼容的更改，对于依赖于当前错误行为的代码来说，这是一个向后不兼容的更改。想要接收标量值集合的代码应该使用Result.scalars()方法，它将返回一个新的ScalarResult对象，该对象产生非行标量对象。 参考：#7953

模式

• [模式] [错误] 修复了一个 bug，当使用referred_column_0命名约定键设置外键约束时，如果外键约束是作为一个ForeignKey对象而不是一个明确的ForeignKeyConstraint对象来设置时，命名约定将无效。由于此更改使用了一些从版本 2.0 中回退的修复的特性，还修复了一个很可能已经存在多年的、不为人所知的特性，即一个ForeignKey对象可以仅通过表的名称而不使用列名来引用被引用的表，如果被引用列的名称与被引用列的名称相同的话。 先前未对 ForeignKey 对象进行测试的 referred_column_0 命名约定键，仅对 ForeignKeyConstraint 进行了测试，此 bug 揭示了除非对所有 FK 约束使用 ForeignKeyConstraint，否则该功能从未正确工作。此 bug 可追溯到为 #3989 引入该功能。 参考：#7958

asyncio

• [asyncio] [错误修复] 修复了异步适配事件处理程序中 contextvar.ContextVar 对象的处理。先前，在非可等待代码中调用可等待项时，不会传播应用于 ContextVar 的值。 参考：#7937

postgresql

• [postgresql] [错误修复] 修复了在 PostgreSQL 上使用 ARRAY 数据类型与 Enum 结合时的 bug，其中使用 .any() 或 .all() 方法以 Python 枚举成员作为参数来渲染 SQL 的 ANY() 或 ALL()，会导致所有驱动程序的类型适配失败。 参考：#6515
• [postgresql] [错误修复] 为 PostgreSQL UUID 类型对象实现了 UUID.python_type 属性。该属性将根据 UUID.as_uuid 参数设置返回 str 或 uuid.UUID。此前，该属性未实现。感谢 Alex Grönholm 的拉取请求。 参考：#7943
• [postgresql] [错误修复] 修复了在使用 create_engine.pool_pre_ping 参数时，psycopg2 方言中的问题，该问题会导致用户配置的 AUTOCOMMIT 隔离级别被“ping”处理程序意外重置。 参考：#7930

mysql

• [mysql] [错误修复] [回归] 修复了未经测试的 MySQL PyODBC 方言中的一个回归，该回归是由于版本 1.4.32 中对 #7518 的修复引起的，导致在首次连接时错误地传播了一个参数，从而导致 TypeError。 参考：#7871

测试

• [测试] [错误修复] 对于第三方方言，修复了对 SimpleUpdateDeleteTest 套件测试的一个缺失要求，该套件测试未检查目标方言上的“rowcount”函数是否有效。 参考：#7919

1.4.35

发布日期：2022 年 4 月 6 日

sql

• [sql] [bug] 修复了新实现的FunctionElement.table_valued.joins_implicitly功能中的错误，即参数不会自动从原始TableValuedAlias对象传播到调用TableValuedAlias.render_derived()或TableValuedAlias.alias()时生成的次要对象。 另外还修复了TableValuedAlias中的这些问题：
  • 修复了可能发生的内存问题，即在连续对同一对象的副本调用TableValuedAlias.render_derived()时可能发生的问题（对于.alias()，我们目前仍然必须继续从前一个元素链接。不确定是否可以改进，但这是.alias()在其他地方的标准行为）
  • 修复了调用TableValuedAlias.render_derived()或TableValuedAlias.alias()时个别元素类型丢失的问题。

  参考：#7890

• [sql] [bug] [regression] 修复了由#7823引起的回归问题，影响了缓存系统，使得在 ORM 操作中“克隆”绑定参数的情况下，例如多态加载，在某些情况下可能不会获得正确的执行时值，导致渲染不正确的绑定值。 参考：#7903

1.4.34

发布日期：2022 年 3 月 31 日

orm

• [orm] [bug] [regression] 修复了由#7861引起的回归问题，即通过Session.execute()直接调用包含 ORM 实体的Insert构造会失败的问题。 参考：#7878

postgresql

• [postgresql] [bug] 为了修复#6581中的问题，缩减了对 psycopg2 的“executemany values”模式的禁用，不再适用于所有“ON CONFLICT”风格的 INSERT，以避免应用于“ON CONFLICT DO NOTHING”子句，该子句不包含任何参数，对于“executemany values”模式是安全的。“ON CONFLICT DO UPDATE”仍然被阻止使用“executemany values”，因为在 DO UPDATE 子句中可能有无法批量处理的额外参数（这是#6581修复的原始问题）。 参考：#7880

1.4.33

发布日期：2022 年 3 月 31 日

orm

• [orm] [usecase] 添加了with_polymorphic.adapt_on_names到with_polymorphic()函数，允许针对将仅根据列名适应到原始映射可选择项的替代可选择项进行多态加载（通常使用具体映射）。 参考：#7805
• [orm] [usecase] 添加了新属性UpdateBase.returning_column_descriptions和UpdateBase.entity_description，允许检查作为Insert、Update或Delete构造的 ORM 属性和实体。对于仅限于 Core 的可选择项，现在还实现了Select.column_descriptions访问器。 参考：#7861
• [orm] [performance] [bug] ORM 在内存使用方面有所改进，删除了一组重要的中间表达式对象，这些对象通常在创建表达式对象的副本时存储。这些克隆对象已大大减少，将 ORM 映射中存储的总表达式对象数量减少约 30%。 参考：#7823
• [orm] [bug] [regression] 修复了“动态”加载策略中的回归，其中Query.filter_by()方法在查询的关系中存在“次要”表且映射针对复杂内容（如“with polymorphic”）时，不会给出适当的实体进行过滤。 参考：#7868
• [orm] [错误] 修复了composite()属性与连接表继承的selectin_polymorphic()加载策略不兼容的错误。 参考：#7801
• [orm] [错误] 修复了selectin_polymorphic()加载选项在没有固定“polymorphic_on”列的连接继承映射器上无法工作的问题。此外，还增加了对此结构的更广泛用法模式的测试支持。 参考：#7799
• [orm] [错误] 修复了with_loader_criteria()函数中的错误，其中加载条件不会应用于在父对象的刷新操作范围内调用的连接急加载。 参考：#7862
• [orm] [错误] 修复了Mapper在映射到UNION时过于激进地减少用户定义的Mapper.primary_key参数的错误，其中对于某些 SELECT 条目，两列本质上是等效的，但在另一个条目中，它们不是，例如在递归 CTE 中。这里的逻辑已更改为接受给定的用户定义 PK，其中列将与映射的可选择相关联，但不再“减少”，因为这种启发式无法适应所有情况。 参考：#7842

引擎

• [引擎] [用例] 添加了新参数Engine.dispose.close，默认为 True。当为 False 时，引擎处理不会触及旧池中的连接，只是丢弃池并替换它。这种用例是为了当原始池从父进程传输时，父进程可以继续使用这些连接。 另请参阅 使用连接池与多进程或 os.fork() - 修订文档 参考：#7815, #7877
• [引擎] [错误] 进一步澄清连接级别的日志记录，以指示当使用 AUTOCOMMIT 隔离级别时，BEGIN、ROLLBACK 和 COMMIT 日志消息实际上并不表示真实的事务；消息已扩展以包括 BEGIN 消息本身，并且消息还已被修复以适应直接使用 Engine 级别的 create_engine.isolation_level 参数时的情况。 参考：#7853

sql

• [sql] [用例] 添加了新参数 FunctionElement.table_valued.joins_implicitly, 用于 FunctionElement.table_valued() 结构。此参数指示提供的表值函数将自动与引用的表执行隐式连接。这实际上禁用了“from linting”功能，例如由于存在此参数而触发的“笛卡尔积”警告。可用于函数，如 func.json_each()。 参考：#7845
• [sql] [错误] bindparam.literal_execute 参数现在参与了 bindparam() 的缓存生成，因为它更改了编译器生成的 sql 字符串。以前使用了正确的绑定值，但是在相同查询的后续执行中会忽略 literal_execute。 参考：#7876
• [sql] [错误] [回归] 修复了由 #7760 引起的回归，其中 TextualSelect 的新功能未在编译器中完全实现，导致与 CTE 和文本语句结合时出现“INSERT FROM SELECT”和“INSERT…ON CONFLICT”等复合 INSERT 结构的问题。 参考：#7798

模式

• [模式] [用例] 添加了支持，使得传递给Table.to_metadata()的Table.to_metadata.referred_schema_fn可调用对象可以返回值BLANK_SCHEMA以指示参考外键应重置为 None。该函数还可以返回RETAIN_SCHEMA符号以指示“无更改”，这将与当前行为相同，当前行为也表示无更改。 参考资料：#7860

sqlite

• [sqlite] [bug] [reflection] 修复了在 SQLite 下 CHECK 约束的名称不会反映的错误，如果使用引号创建名称，则会出现这种情况，这种情况下名称使用混合大小写或特殊字符。 参考资料：#5463

mssql

• [mssql] [bug] [regression] 修复了由#7160引起的回归，当 FK 反射与低兼容性级别设置（兼容性级别 80：SQL Server 2000）一起导致“模糊列名”错误。修补程序由@Lin-Your 提供。 参考资料：#7812

杂项

• [bug] [ext] 改进了association_proxy()构造尝试在类级别访问目标属性且此访问失败时引发的错误消息。这里的特定用例是当代理到不包括工作类级实现的混合属性时。 参考资料：#7827

1.4.32

发布日期：2022 年 3 月 6 日

orm

• [orm] [bug] [regression] 修复了 ORM 异常的回归，当 INSERT 默默失败未真正插入行（例如来自触发器）时，由于运行时提前引发的异常由于缺少主键值而不会到达，因此引发了一个不具信息的异常而不是正确的异常。对于 1.4 及以上版本，在此情况下添加了一个新的FlushError，它比 1.3 的以前的“空标识”异常更早地被引发，因为实际 INSERT 的行数不符合预期的情况在 1.4 中是一个更关键的情况，因为它阻止了多个对象的批处理正确工作。这与新提取的主键值被提取为 NULL 的情况不同，后者继续引发现有的“空标识”异常。 参考：#7594
• [ORM] [错误] 修复了在relationship()中使用完全合格路径的类名的问题，尽管其中包含了不正确的路径标记名称，但不是第一个标记，但是失败不会引发具有信息的错误，而是在稍后的步骤中随机失败。 参考：#7697

引擎

• [引擎] [错误] 调整了关键的 SQLAlchemy 组件的日志记录，包括Engine，Connection以建立适当的堆栈级别参数，这样当在自定义日志格式化程序中使用 Python 日志标记funcName和lineno时，将报告正确的信息，这在过滤日志输出时可能很有用；在 Python 3.8 及以上版本上支持。感谢 Markus Gerstel 的贡献的拉取请求。 参考：#7612

SQL

• [SQL] [错误] 修复了由于字符串格式错误而导致值为元组的错误消息失败的问题，包括对不支持的文字值和无效的布尔值的编译。 参考：#7721
• [SQL] [错误] [MySQL] 修复了 MySQL SET 数据类型以及通用 Enum 数据类型中的问题，在这些类型的__repr__()方法不会在字符串输出中呈现所有可选参数，影响这些类型在 Alembic 自动生成中的使用。MySQL 的拉取请求由 Yuki Nishimine 提供。 参考：#7598, #7720, #7789
• [SQL] [错误] 现在，如果指定了Enum.length参数而没有同时指定Enum.native_enum为 False，Enum数据类型会发出警告，因为在这种情况下参数将被静默忽略，尽管Enum数据类型仍会在没有原生 ENUM 数据类型的后端（如 SQLite）上渲染 VARCHAR DDL。这种行为在未来的发布中可能会更改，以便无论“native_enum”设置如何，“length”都会适用于所有非本机“enum”类型。
• [sql] [bug] 修复了当在TextualSelect实例上调用HasCTE.add_cte()方法时，SQL 编译器未进行适配的问题。此修复还将更多“SELECT”类似的编译器行为添加到TextualSelect中，包括可容纳 DML CTEs（如 UPDATE 和 INSERT）。 参考：#7760

asyncio

• [asyncio] [bug] 修复了在某些事件监听类别中未为异步引擎引发描述性错误消息的问题，应该是同步引擎实例。
• [asyncio] [bug] 修复了当使用不兼容同步风格Result对象的流式结果执行选项Connection.execution_options.stream_results时，AsyncSession.execute()方法未引发信息丰富的异常的问题。在这种情况下，现在会引发异常，就像在与AsyncConnection.execute()方法一起使用Connection.execution_options.stream_results选项时已引发异常一样。 此外，为了提高与状态敏感的数据库驱动程序（如 asyncmy）的稳定性，当出现此错误条件时，游标现在会被关闭；以前在 asyncmy 方言中，连接会进入无效状态，服务器端结果仍未消耗。 参考：#7667

postgresql

• [postgresql] [usecase] 添加了对 PostgreSQL NOT VALID 短语的编译器支持，用于渲染 CheckConstraint、ForeignKeyConstraint 和 ForeignKey 架构构造的 DDL。感谢 Gilbert Gilb 的拉取请求。 另请参阅 PostgreSQL 约束选项 参考：#7600

mysql

• [mysql] [bug] [regression] 由 #7518 引起的回归问题，其中将语法“SHOW VARIABLES”更改为“SELECT @@”破坏了与早于 5.6 版本的 MySQL 版本（包括早期 5.0 发行版）的兼容性。虽然这些是非常旧的 MySQL 版本，但并未计划更改兼容性，因此已恢复了特定于版本的逻辑，以便在 MySQL 服务器版本 < 5.6 时回退到“SHOW VARIABLES”。 参考：#7518

mariadb

• [mariadb] [bug] [regression] 修复了 mariadbconnector 方言中的回归问题，截至 mariadb 连接器 1.0.10，DBAPI 不再预缓冲 cursor.lastrowid，导致在使用 ORM 插入对象时出错，同时导致 CursorResult.inserted_primary_key 属性不可用。该方言现在主动获取此值，以适用的情况。 参考：#7738

sqlite

• [sqlite] [usecase] 添加了对反映 SQLite 内联唯一约束的支持，其中列名使用 SQLite 的“转义引号” [] 或 ```py, which are discarded by the database when producing the column name. References: #7736
• [sqlite] [bug] Fixed issue where SQLite unique constraint reflection would fail to detect a column-inline UNIQUE constraint where the column name had an underscore in its name. References: #7736

oracle

• [oracle] [bug] Fixed issue in Oracle dialect where using a column name that requires quoting when written as a bound parameter, such as "_id", would not correctly track a Python generated default value due to the bound-parameter rewriting missing this value, causing an Oracle error to be raised. References: #7676
• [oracle] [bug] [regression] Added support to parse “DPI” error codes from cx_Oracle exception objects such as DPI-1080 and DPI-1010, both of which now indicate a disconnect scenario as of cx_Oracle 8.3. References: #7748

tests

• [tests] [bug] Improvements to the test suite’s integration with pytest such that the “warnings” plugin, if manually enabled, will not interfere with the test suite, such that third parties can enable the warnings plugin or make use of the -W parameter and SQLAlchemy’s test suite will continue to pass. Additionally, modernized the detection of the “pytest-xdist” plugin so that plugins can be globally disabled using PYTEST_DISABLE_PLUGIN_AUTOLOAD=1 without breaking the test suite if xdist were still installed. Warning filters that promote deprecation warnings to errors are now localized to SQLAlchemy-specific warnings, or within SQLAlchemy-specific sources for general Python deprecation warnings, so that non-SQLAlchemy deprecation warnings emitted from pytest plugins should also not impact the test suite. References: #7599
• [tests] [bug] Made corrections to the default pytest configuration regarding how test discovery is configured, to fix issue where the test suite would not configure warnings correctly and also attempt to load example suites as tests, in the specific case where the SQLAlchemy checkout were located in an absolute path that had a super-directory named “test”. References: #7045

1.4.31

Released: January 20, 2022

orm

• [orm] [bug] Fixed issue in Session.bulk_save_objects() where the sorting that takes place when the preserve_order parameter is set to False would sort partially on Mapper objects, which is rejected in Python 3.11. References: #7591

postgresql

• [postgresql] [bug] [regression] Fixed regression where the change in #7148 to repair ENUM handling in PostgreSQL broke the use case of an empty ARRAY of ENUM, preventing rows that contained an empty array from being handled correctly when fetching results. References: #7590

mysql

• [mysql] [bug] [regression] Fixed regression in asyncmy dialect caused by #7567 where removal of the PyMySQL dependency broke binary columns, due to the asyncmy dialect not being properly included within CI tests. References: #7593

mssql

• [mssql] Added support for FILESTREAM when using VARBINARY(max) in MSSQL. See also VARBINARY.filestream References: #7243

1.4.30

Released: January 19, 2022

orm

• [orm] [bug] Fixed issue in joined-inheritance load of additional attributes functionality in deep multi-level inheritance where an intermediary table that contained no columns would not be included in the tables joined, instead linking those tables to their primary key identifiers. While this works fine, it nonetheless in 1.4 began producing the cartesian product compiler warning. The logic has been changed so that these intermediary tables are included regardless. While this does include additional tables in the query that are not technically necessary, this only occurs for the highly unusual case of deep 3+ level inheritance with intermediary tables that have no non primary key columns, potential performance impact is therefore expected to be negligible. References: #7507
• [orm] [bug] Fixed issue where calling upon registry.map_imperatively() more than once for the same class would produce an unexpected error, rather than an informative error that the target class is already mapped. This behavior differed from that of the mapper() function which does report an informative message already. References: #7579
• [orm] [bug] [asyncio] Added missing method AsyncSession.invalidate() to the AsyncSession class. References: #7524
• [orm] [bug] [regression] Fixed regression which appeared in 1.4.23 which could cause loader options to be mis-handled in some cases, in particular when using joined table inheritance in combination with the polymorphic_load="selectin" option as well as relationship lazy loading, leading to a TypeError. References: #7557
• [orm] [bug] [regression] Fixed ORM regression where calling the aliased() function against an existing aliased() construct would fail to produce correct SQL if the existing construct were against a fixed table. The fix allows that the original aliased() construct is disregarded if it were only against a table that’s now being replaced. It also allows for correct behavior when constructing a aliased() without a selectable argument against a aliased() that’s against a subuquery, to create an alias of that subquery (i.e. to change its name). The nesting behavior of aliased() remains in place for the case where the outer aliased() object is against a subquery which in turn refers to the inner aliased() object. This is a relatively new 1.4 feature that helps to suit use cases that were previously served by the deprecated Query.from_self() method. References: #7576
• [orm] [bug] Fixed issue where Select.correlate_except() method, when passed either the None value or no arguments, would not correlate any elements when used in an ORM context (that is, passing ORM entities as FROM clauses), rather than causing all FROM elements to be considered as “correlated” in the same way which occurs when using Core-only constructs. References: #7514
• [orm] [bug] [regression] Fixed regression from 1.3 where the “subqueryload” loader strategy would fail with a stack trace if used against a query that made use of Query.from_statement() or Select.from_statement(). As subqueryload requires modifying the original statement, it’s not compatible with the “from_statement” use case, especially for statements made against the text() construct. The behavior now is equivalent to that of 1.3 and previously, which is that the loader strategy silently degrades to not be used for such statements, typically falling back to using the lazyload strategy. References: #7505

sql

• [sql] [bug] [postgresql] Added additional rule to the system that determines TypeEngine implementations from Python literals to apply a second level of adjustment to the type, so that a Python datetime with or without tzinfo can set the timezone=True parameter on the returned DateTime object, as well as Time. This helps with some round-trip scenarios on type-sensitive PostgreSQL dialects such as asyncpg, psycopg3 (2.0 only). References: #7537
• [sql] [bug] Added an informative error message when a method object is passed to a SQL construct. Previously, when such a callable were passed, as is a common typographical error when dealing with method-chained SQL constructs, they were interpreted as “lambda SQL” targets to be invoked at compilation time, which would lead to silent failures. As this feature was not intended to be used with methods, method objects are now rejected. References: #7032

mypy

• [mypy] [bug] Fixed Mypy crash when running id daemon mode caused by a missing attribute on an internal mypy Var instance. References: #7321

asyncio

• [asyncio] [usecase] Added new method AdaptedConnection.run_async() to the DBAPI connection interface used by asyncio drivers, which allows methods to be called against the underlying “driver” connection directly within a sync-style function where the await keyword can’t be used, such as within SQLAlchemy event handler functions. The method is analogous to the AsyncConnection.run_sync() method which translates async-style calls to sync-style. The method is useful for things like connection-pool on-connect handlers that need to invoke awaitable methods on the driver connection when it’s first created. See also Using awaitable-only driver methods in connection pool and other events References: #7580

postgresql

• [postgresql] [usecase] Added string rendering to the UUID datatype, so that stringifying a statement with “literal_binds” that uses this type will render an appropriate string value for the PostgreSQL backend. Pull request courtesy José Duarte. References: #7561
• [postgresql] [bug] [asyncpg] Improved support for asyncpg handling of TIME WITH TIMEZONE, which was not fully implemented. References: #7537
• [postgresql] [bug] [mssql] [reflection] Fixed reflection of covering indexes to report include_columns as part of the dialect_options entry in the reflected index dictionary, thereby enabling round trips from reflection->create to be complete. Included columns continue to also be present under the include_columns key for backwards compatibility. References: #7382
• [postgresql] [bug] Fixed handling of array of enum values which require escape characters. References: #7418

mysql

• [mysql] [change] Replace SHOW VARIABLES LIKE statement with equivalent SELECT @@variable in MySQL and MariaDB dialect initialization. This should avoid mutex contention caused by SHOW VARIABLES, improving initialization performance. References: #7518
• [mysql] [bug] Removed unnecessary dependency on PyMySQL from the asyncmy dialect. Pull request courtesy long2ice. References: #7567

1.4.29

Released: December 22, 2021

orm

• [orm] [usecase] Added Session.get.execution_options parameter which was previously missing from the Session.get() method. References: #7410
• [orm] [bug] Fixed issue in new “loader criteria” method PropComparator.and_() where usage with a loader strategy like selectinload() against a column that was a member of the .c. collection of a subquery object, where the subquery would be dynamically added to the FROM clause of the statement, would be subject to stale parameter values within the subquery in the SQL statement cache, as the process used by the loader strategy to replace the parameters at execution time would fail to accommodate the subquery when received in this form. References: #7489
• [orm] [bug] Fixed recursion overflow which could occur within ORM statement compilation when using either the with_loader_criteria() feature or the the PropComparator.and_() method within a loader strategy in conjunction with a subquery which referred to the same entity being altered by the criteria option, or loaded by the loader strategy. A check for coming across the same loader criteria option in a recursive fashion has been added to accommodate for this scenario. References: #7491
• [orm] [bug] [mypy] Fixed issue where the __class_getitem__() method of the generated declarative base class by as_declarative() would lead to inaccessible class attributes such as __table__, for cases where a Generic[T] style typing declaration were used in the class hierarchy. This is in continuation from the basic addition of __class_getitem__() in #7368. Pull request courtesy Kai Mueller. References: #7368, #7462
• [orm] [bug] [regression] Fixed caching-related issue where the use of a loader option of the form lazyload(aliased(A).bs).joinedload(B.cs) would fail to result in the joinedload being invoked for runs subsequent to the query being cached, due to a mismatch for the options / object path applied to the objects loaded for a query with a lead entity that used aliased(). References: #7447

engine

• [engine] [bug] Corrected the error message for the AttributeError that’s raised when attempting to write to an attribute on the Row class, which is immutable. The previous message claimed the column didn’t exist which is misleading. References: #7432
• [engine] [bug] [regression] Fixed regression in the make_url() function used to parse URL strings where the query string parsing would go into a recursion overflow if a Python 2 u'' string were used. References: #7446

mypy

• [mypy] [bug] Fixed mypy regression where the release of mypy 0.930 added additional internal checks to the format of “named types”, requiring that they be fully qualified and locatable. This broke the mypy plugin for SQLAlchemy, raising an assertion error, as there was use of symbols such as __builtins__ and other un-locatable or unqualified names that previously had not raised any assertions. References: #7496

asyncio

• [asyncio] [usecase] Added async_engine_config() function to create an async engine from a configuration dict. This otherwise behaves the same as engine_from_config(). References: #7301

mariadb

• [mariadb] [bug] Corrected the error classes inspected for the “is_disconnect” check for the mariadbconnector dialect, which was failing for disconnects that occurred due to common MySQL/MariaDB error codes such as 2006; the DBAPI appears to currently use the mariadb.InterfaceError exception class for disconnect errors such as error code 2006, which has been added to the list of classes checked. References: #7457

tests

• [tests] [bug] [regression] Fixed a regression in the test suite where the test called CompareAndCopyTest::test_all_present would fail on some platforms due to additional testing artifacts being detected. Pull request courtesy Nils Philippsen. References: #7450

1.4.28

Released: December 9, 2021

platform

• [platform] [bug] Python 3.10 has deprecated “distutils” in favor of explicit use of “setuptools” in PEP 632; SQLAlchemy’s setup.py has replaced imports accordingly. However, since setuptools itself only recently added the replacement symbols mentioned in pep-632 as of November of 2021 in version 59.0.1, setup.py still has fallback imports to distutils, as SQLAlchemy 1.4 does not have a hard setuptools versioning requirement at this time. SQLAlchemy 2.0 is expected to use a full PEP 517 installation layout which will indicate appropriate setuptools versioning up front. References: #7311

orm

• [orm] [bug] [ext] Fixed issue where the internal cloning used by the PropComparator.any() method on a relationship() in the case where the related class also makes use of ORM polymorphic loading, would fail if a hybrid property on the related, polymorphic class were used within the criteria for the any() operation. References: #7425
• [orm] [bug] [mypy] Fixed issue where the as_declarative() decorator and similar functions used to generate the declarative base class would not copy the __class_getitem__() method from a given superclass, which prevented the use of pep-484 generics in conjunction with the Base class. Pull request courtesy Kai Mueller. References: #7368
• [orm] [bug] [regression] Fixed ORM regression where the new behavior of “eager loaders run on unexpire” added in #1763 would lead to loader option errors being raised inappropriately for the case where a single Query or Select were used to load multiple kinds of entities, along with loader options that apply to just one of those kinds of entity like a joinedload(), and later the objects would be refreshed from expiration, where the loader options would attempt to be applied to the mismatched object type and then raise an exception. The check for this mismatch now bypasses raising an error for this case. References: #7318
• [orm] [bug] User defined ORM options, such as those illustrated in the dogpile.caching example which subclass UserDefinedOption, by definition are handled on every statement execution and do not need to be considered as part of the cache key for the statement. Caching of the base ExecutableOption class has been modified so that it is no longer a HasCacheKey subclass directly, so that the presence of user defined option objects will not have the unwanted side effect of disabling statement caching. Only ORM specific loader and criteria options, which are all internal to SQLAlchemy, now participate within the caching system. References: #7394
• [orm] [bug] Fixed issue where mappings that made use of synonym() and potentially other kinds of “proxy” attributes would not in all cases successfully generate a cache key for their SQL statements, leading to degraded performance for those statements. References: #7394
• [orm] [bug] Fixed issue where a list mapped with relationship() would go into an endless loop if in-place added to itself, i.e. the += operator were used, as well as if .extend() were given the same list. References: #7389
• [orm] [bug] Fixed issue where if an exception occurred when the Session were to close the connection within the Session.commit() method, when using a context manager for Session.begin() , it would attempt a rollback which would not be possible as the Session was in between where the transaction is committed and the connection is then to be returned to the pool, raising the exception “this sessiontransaction is in the committed state”. This exception can occur mostly in an asyncio context where CancelledError can be raised. References: #7388
• [orm] [deprecated] Deprecated an undocumented loader option syntax ".*", which appears to be no different than passing a single asterisk, and will emit a deprecation warning if used. This syntax may have been intended for something but there is currently no need for it. References: #4390

engine

• [engine] [usecase] Added support for copy() and deepcopy() to the URL class. Pull request courtesy Tom Ritchford. References: #7400

sql

• [sql] [usecase] ”Compound select” methods like Select.union(), Select.intersect_all() etc. now accept *other as an argument rather than other to allow for multiple additional SELECTs to be compounded with the parent statement at once. In particular, the change as applied to CTE.union() and CTE.union_all() now allow for a so-called “non-linear CTE” to be created with the CTE construct, whereas previously there was no way to have more than two CTE sub-elements in a UNION together while still correctly calling upon the CTE in recursive fashion. Pull request courtesy Eric Masseran. References: #7259
• [sql] [usecase] Support multiple clause elements in the Exists.where() method, unifying the api with the one presented by a normal select() construct. References: #7386
• [sql] [bug] [regression] Extended the TypeDecorator.cache_ok attribute and corresponding warning message if this flag is not defined, a behavior first established for TypeDecorator as part of #6436, to also take place for UserDefinedType, by generalizing the flag and associated caching logic to a new common base for these two types, ExternalType to create UserDefinedType.cache_ok. The change means any current UserDefinedType will now cause SQL statement caching to no longer take place for statements which make use of the datatype, along with a warning being emitted, unless the class defines the UserDefinedType.cache_ok flag as True. If the datatype cannot form a deterministic, hashable cache key derived from its arguments, the attribute may be set to False which will continue to keep caching disabled but will suppress the warning. In particular, custom datatypes currently used in packages such as SQLAlchemy-utils will need to implement this flag. The issue was observed as a result of a SQLAlchemy-utils datatype that is not currently cacheable. See also ExternalType.cache_ok References: #7319
• [sql] [bug] Custom SQL elements, third party dialects, custom or third party datatypes will all generate consistent warnings when they do not clearly opt in or out of SQL statement caching, which is achieved by setting the appropriate attributes on each type of class. The warning links to documentation sections which indicate the appropriate approach for each type of object in order for caching to be enabled. References: #7394
• [sql] [bug] Fixed missing caching directives for a few lesser used classes in SQL Core which would cause [no key] to be logged for elements which made use of these. References: #7394

mypy

• [mypy] [bug] Fixed Mypy crash which would occur when using Mypy plugin against code which made use of declared_attr methods for non-mapped names like __mapper_args__, __table_args__, or other dunder names, as the plugin would try to interpret these as mapped attributes which would then be later mis-handled. As part of this change, the decorated function is still converted by the plugin into a generic assignment statement (e.g. __mapper_args__: Any) so that the argument signature can continue to be annotated in the same way one would for any other @classmethod without Mypy complaining about the wrong argument type for a method that isn’t explicitly @classmethod. References: #7321

postgresql

• [postgresql] [bug] Fixed missing caching directives for hstore and array constructs which would cause [no key] to be logged for these elements. References: #7394

tests

• [tests] [bug] Implemented support for the test suite to run correctly under Pytest 7. Previously, only Pytest 6.x was supported for Python 3, however the version was not pinned on the upper bound in tox.ini. Pytest is not pinned in tox.ini to be lower than version 8 so that SQLAlchemy versions released with the current codebase will be able to be tested under tox without changes to the environment. Much thanks to the Pytest developers for their help with this issue.

1.4.27

Released: November 11, 2021

orm

• [orm] [bug] Fixed bug in “relationship to aliased class” feature introduced at Relationship to Aliased Class where it was not possible to create a loader strategy option targeting an attribute on the target using the aliased() construct directly in a second loader option, such as selectinload(A.aliased_bs).joinedload(aliased_b.cs), without explicitly qualifying using PropComparator.of_type() on the preceding element of the path. Additionally, targeting the non-aliased class directly would be accepted (inappropriately), but would silently fail, such as selectinload(A.aliased_bs).joinedload(B.cs); this now raises an error referring to the typing mismatch. References: #7224
• [orm] [bug] All Result objects will now consistently raise ResourceClosedError if they are used after a hard close, which includes the “hard close” that occurs after calling “single row or value” methods like Result.first() and Result.scalar(). This was already the behavior of the most common class of result objects returned for Core statement executions, i.e. those based on CursorResult, so this behavior is not new. However, the change has been extended to properly accommodate for the ORM “filtering” result objects returned when using 2.0 style ORM queries, which would previously behave in “soft closed” style of returning empty results, or wouldn’t actually “soft close” at all and would continue yielding from the underlying cursor. As part of this change, also added Result.close() to the base Result class and implemented it for the filtered result implementations that are used by the ORM, so that it is possible to call the CursorResult.close() method on the underlying CursorResult when the yield_per execution option is in use to close a server side cursor before remaining ORM results have been fetched. This was again already available for Core result sets but the change makes it available for 2.0 style ORM results as well. References: #7274
• [orm] [bug] [regression] Fixed 1.4 regression where Query.filter_by() would not function correctly on a Query that was produced from Query.union(), Query.from_self() or similar. References: #7239
• [orm] [bug] Fixed issue where deferred polymorphic loading of attributes from a joined-table inheritance subclass would fail to populate the attribute correctly if the load_only() option were used to originally exclude that attribute, in the case where the load_only were descending from a relationship loader option. The fix allows that other valid options such as defer(..., raiseload=True) etc. still function as expected. References: #7304
• [orm] [bug] [regression] Fixed 1.4 regression where Query.filter_by() would not function correctly when Query.join() were joined to an entity which made use of PropComparator.of_type() to specify an aliased version of the target entity. The issue also applies to future style ORM queries constructed with select(). References: #7244

engine

• [engine] [bug] Fixed issue in future Connection object where the Connection.execute() method would not accept a non-dict mapping object, such as SQLAlchemy’s own RowMapping or other abc.collections.Mapping object as a parameter dictionary. References: #7291
• [engine] [bug] [regression] Fixed regression where the CursorResult.fetchmany() method would fail to autoclose a server-side cursor (i.e. when stream_results or yield_per is in use, either Core or ORM oriented results) when the results were fully exhausted. References: #7274
• [engine] [bug] Fixed issue in future Engine where calling upon Engine.begin() and entering the context manager would not close the connection if the actual BEGIN operation failed for some reason, such as an event handler raising an exception; this use case failed to be tested for the future version of the engine. Note that the “future” context managers which handle begin() blocks in Core and ORM don’t actually run the “BEGIN” operation until the context managers are actually entered. This is different from the legacy version which runs the “BEGIN” operation up front. References: #7272

sql

• [sql] [usecase] Added TupleType to the top level sqlalchemy import namespace.
• [sql] [bug] [regression] Fixed regression where the row objects returned for ORM queries, which are now the normal Row objects, would not be interpreted by the ColumnOperators.in_() operator as tuple values to be broken out into individual bound parameters, and would instead pass them as single values to the driver leading to failures. The change to the “expanding IN” system now accommodates for the expression already being of type TupleType and treats values accordingly if so. In the uncommon case of using “tuple-in” with an untyped statement such as a textual statement with no typing information, a tuple value is detected for values that implement collections.abc.Sequence, but that are not str or bytes, as always when testing for Sequence. References: #7292
• [sql] [bug] Fixed issue where using the feature of using a string label for ordering or grouping described at Ordering or Grouping by a Label would fail to function correctly if used on a CTE construct, when the CTE were embedded inside of an enclosing Select statement that itself was set up as a scalar subquery. References: #7269
• [sql] [bug] [regression] Fixed regression where the text() construct would no longer be accepted as a target case in the “whens” list within a case() construct. The regression appears related to an attempt to guard against some forms of literal values that were considered to be ambiguous when passed here; however, there’s no reason the target cases shouldn’t be interpreted as open-ended SQL expressions just like anywhere else, and a literal string or tuple will be converted to a bound parameter as would be the case elsewhere. References: #7287

schema

• [schema] [bug] Fixed issue in Table where the Table.implicit_returning parameter would not be accommodated correctly when passed along with Table.extend_existing to augment an existing Table. References: #7295

postgresql

• [postgresql] [usecase] [asyncpg] Added overridable methods PGDialect_asyncpg.setup_asyncpg_json_codec and PGDialect_asyncpg.setup_asyncpg_jsonb_codec codec, which handle the required task of registering JSON/JSONB codecs for these datatypes when using asyncpg. The change is that methods are broken out as individual, overridable methods to support third party dialects that need to alter or disable how these particular codecs are set up. References: #7284
• [postgresql] [bug] [asyncpg] Changed the asyncpg dialect to bind the Float type to the “float” PostgreSQL type instead of “numeric” so that the value float(inf) can be accommodated. Added test suite support for persistence of the “inf” value. References: #7283
• [postgresql] [pg8000] Improve array handling when using PostgreSQL with the pg8000 dialect. References: #7167

mysql

• [mysql] [bug] [mariadb] Reorganized the list of reserved words into two separate lists, one for MySQL and one for MariaDB, so that these diverging sets of words can be managed more accurately; adjusted the MySQL/MariaDB dialect to switch among these lists based on either explicitly configured or server-version-detected “MySQL” or “MariaDB” backend. Added all current reserved words through MySQL 8 and current MariaDB versions including recently added keywords like “lead” . Pull request courtesy Kevin Kirsche. References: #7167
• [mysql] [bug] Fixed issue in MySQL Insert.on_duplicate_key_update() which would render the wrong column name when an expression were used in a VALUES expression. Pull request courtesy Cristian Sabaila. References: #7281

mssql

• [mssql] [bug] Adjusted the compiler’s generation of “post compile” symbols including those used for “expanding IN” as well as for the “schema translate map” to not be based directly on plain bracketed strings with underscores, as this conflicts directly with SQL Server’s quoting format of also using brackets, which produces false matches when the compiler replaces “post compile” and “schema translate” symbols. The issue created easy to reproduce examples both with the Inspector.get_schema_names() method when used in conjunction with the Connection.execution_options.schema_translate_map feature, as well in the unlikely case that a symbol overlapping with the internal name “POSTCOMPILE” would be used with a feature like “expanding in”. References: #7300

1.4.26

Released: October 19, 2021

orm

• [orm] [bug] Improved the exception message generated when configuring a mapping with joined table inheritance where the two tables either have no foreign key relationships set up, or where they have multiple foreign key relationships set up. The message is now ORM specific and includes context that the Mapper.inherit_condition parameter may be needed particularly for the ambiguous foreign keys case.
• [orm] [bug] Fixed issue with with_loader_criteria() feature where ON criteria would not be added to a JOIN for a query of the form select(A).join(B), stating a target while making use of an implicit ON clause. References: #7189
• [orm] [bug] Fixed bug where the ORM “plugin”, necessary for features such as with_loader_criteria() to work correctly, would not be applied to a select() which queried from an ORM column expression if it made use of the ColumnElement.label() modifier. References: #7205
• [orm] [bug] Add missing methods added in #6991 to scoped_session and async_scoped_session(). References: #7103
• [orm] [bug] An extra layer of warning messages has been added to the functionality of Query.join() and the ORM version of Select.join(), where a few places where “automatic aliasing” continues to occur will now be called out as a pattern to avoid, mostly specific to the area of joined table inheritance where classes that share common base tables are being joined together without using explicit aliases. One case emits a legacy warning for a pattern that’s not recommended, the other case is fully deprecated. The automatic aliasing within ORM join() which occurs for overlapping mapped tables does not work consistently with all APIs such as contains_eager(), and rather than continue to try to make these use cases work everywhere, replacing with a more user-explicit pattern is clearer, less prone to bugs and simplifies SQLAlchemy’s internals further. The warnings include links to the errors.rst page where each pattern is demonstrated along with the recommended pattern to fix. See also An alias is being generated automatically for raw clauseelement An alias is being generated automatically due to overlapping tables References: #6972, #6974
• [orm] [bug] Fixed bug where iterating a Result from a Session after that Session were closed would partially attach objects to that session in an essentially invalid state. It now raises an exception with a link to new documentation if an un-buffered result is iterated from a Session that was closed or otherwise had the Session.expunge_all() method called after that Result was generated. The prebuffer_rows execution option, as is used automatically by the asyncio extension for client-side result sets, may be used to produce a Result where the ORM objects are prebuffered, and in this case iterating the result will produce a series of detached objects. See also Object cannot be converted to ‘persistent’ state, as this identity map is no longer valid. References: #7128
• [orm] [bug] Related to #7153, fixed an issue where result column lookups would fail for “adapted” SELECT statements that selected for “constant” value expressions most typically the NULL expression, as would occur in such places as joined eager loading in conjunction with limit/offset. This was overall a regression due to issue #6259 which removed all “adaption” for constants like NULL, “true”, and “false” when rewriting expressions in a SQL statement, but this broke the case where the same adaption logic were used to resolve the constant to a labeled expression for the purposes of result set targeting. References: #7154
• [orm] [bug] [regression] Fixed regression where ORM loaded objects could not be pickled in cases where loader options making use of "*" were used in certain combinations, such as combining the joinedload() loader strategy with raiseload('*') of sub-elements. References: #7134
• [orm] [bug] [regression] Fixed regression where the use of a hybrid_property attribute or a mapped composite() attribute as a key passed to the Update.values() method for an ORM-enabled Update statement, as well as when using it via the legacy Query.update() method, would be processed for incoming ORM/hybrid/composite values within the compilation stage of the UPDATE statement, which meant that in those cases where caching occurred, subsequent invocations of the same statement would no longer receive the correct values. This would include not only hybrids that use the hybrid_property.update_expression() method, but any use of a plain hybrid attribute as well. For composites, the issue instead caused a non-repeatable cache key to be generated, which would break caching and could fill up the statement cache with repeated statements. The Update construct now handles the processing of key/value pairs passed to Update.values() and Update.ordered_values() up front when the construct is first generated, before the cache key has been generated so that the key/value pairs are processed each time, and so that the cache key is generated against the individual column/value pairs that will ultimately be used in the statement. References: #7209
• [orm] Passing a Query object to Session.execute() is not the intended use of this object, and will now raise a deprecation warning. References: #6284

examples

• [examples] [bug] Repaired the examples in examples/versioned_rows to use SQLAlchemy 1.4 APIs correctly; these examples had been missed when API changes like removing “passive” from Session.is_modified() were made as well as the SessionEvents.do_orm_execute() event hook were added. References: #7169

engine

• [engine] [bug] Fixed issue where the deprecation warning for the URL constructor which indicates that the URL.create() method should be used would not emit if a full positional argument list of seven arguments were passed; additionally, validation of URL arguments will now occur if the constructor is called in this way, which was being skipped previously. References: #7130
• [engine] [bug] [postgresql] The Inspector.reflect_table() method now supports reflecting tables that do not have user defined columns. This allows MetaData.reflect() to properly complete reflection on databases that contain such tables. Currently, only PostgreSQL is known to support such a construct among the common database backends. References: #3247
• [engine] [bug] Implemented proper __reduce__() methods for all SQLAlchemy exception objects to ensure they all support clean round trips when pickling, as exception objects are often serialized for the purposes of various debugging tools. References: #7077

sql

• [sql] [bug] Fixed issue where SQL queries using the FunctionElement.within_group() construct could not be pickled, typically when using the sqlalchemy.ext.serializer extension but also for general generic pickling. References: #6520
• [sql] [bug] Repaired issue in new HasCTE.cte.nesting parameter introduced with #4123 where a recursive CTE using HasCTE.cte.recursive in typical conjunction with UNION would not compile correctly. Additionally makes some adjustments so that the CTE construct creates a correct cache key. Pull request courtesy Eric Masseran. References: #4123
• [sql] [bug] Account for the table.schema parameter passed to the table() construct, such that it is taken into account when accessing the TableClause.fullname attribute. References: #7061
• [sql] [bug] Fixed an inconsistency in the ColumnOperators.any_() / ColumnOperators.all_() functions / methods where the special behavior these functions have of “flipping” the expression such that the “ANY” / “ALL” expression is always on the right side would not function if the comparison were against the None value, that is, “column.any_() == None” should produce the same SQL expression as “null() == column.any_()”. Added more docs to clarify this as well, plus mentions that any_() / all_() generally supersede the ARRAY version “any()” / “all()”. References: #7140
• [sql] [bug] [regression] Fixed issue where “expanding IN” would fail to function correctly with datatypes that use the TypeEngine.bind_expression() method, where the method would need to be applied to each element of the IN expression rather than the overall IN expression itself. References: #7177
• [sql] [bug] Adjusted the “column disambiguation” logic that’s new in 1.4, where the same expression repeated gets an “extra anonymous” label, so that the logic more aggressively deduplicates those labels when the repeated element is the same Python expression object each time, as occurs in cases like when using “singleton” values like null(). This is based on the observation that at least some databases (e.g. MySQL, but not SQLite) will raise an error if the same label is repeated inside of a subquery. References: #7153

mypy

• [mypy] [bug] Fixed issue in mypy plugin to improve upon some issues detecting Enum() SQL types containing custom Python enumeration classes. Pull request courtesy Hiroshi Ogawa. References: #6435

postgresql

• [postgresql] [bug] Added a “disconnect” condition for the “SSL SYSCALL error: Bad address” error message as reported by psycopg2. Pull request courtesy Zeke Brechtel. References: #5387
• [postgresql] [bug] [regression] Fixed issue where IN expressions against a series of array elements, as can be done with PostgreSQL, would fail to function correctly due to multiple issues within the “expanding IN” feature of SQLAlchemy Core that was standardized in version 1.4. The psycopg2 dialect now makes use of the TypeEngine.bind_expression() method with ARRAY to portably apply the correct casts to elements. The asyncpg dialect was not affected by this issue as it applies bind-level casts at the driver level rather than at the compiler level. References: #7177

mysql

• [mysql] [bug] [mariadb] Fixes to accommodate for the MariaDB 10.6 series, including backwards incompatible changes in both the mariadb-connector Python driver (supported on SQLAlchemy 1.4 only) as well as the native 10.6 client libraries that are used automatically by the mysqlclient DBAPI (applies to both 1.3 and 1.4). The “utf8mb3” encoding symbol is now reported by these client libraries when the encoding is stated as “utf8”, leading to lookup and encoding errors within the MySQL dialect that does not expect this symbol. Updates to both the MySQL base library to accommodate for this utf8mb3 symbol being reported as well as to the test suite. Thanks to Georg Richter for support. This change is also backported to: 1.3.25 References: #7115, #7136
• [mysql] [bug] Fixed issue in MySQL match() construct where passing a clause expression such as bindparam() or other SQL expression for the “against” parameter would fail. Pull request courtesy Anton Kovalevich. References: #7144
• [mysql] [bug] Fixed installation issue where the sqlalchemy.dialects.mysql module would not be importable if “greenlet” were not installed. References: #7204

mssql

• [mssql] [usecase] Added reflection support for SQL Server foreign key options, including “ON UPDATE” and “ON DELETE” values of “CASCADE” and “SET NULL”.
• [mssql] [bug] Fixed issue with Inspector.get_foreign_keys() where foreign keys were omitted if they were established against a unique index instead of a unique constraint. References: #7160
• [mssql] [bug] Fixed issue with Inspector.has_table() where it would return False if a local temp table with the same name from a different session happened to be returned first when querying tempdb. This is a continuation of #6910 which accounted for the temp table existing only in the alternate session and not the current one. References: #7168
• [mssql] [bug] [regression] Fixed bug in SQL Server DATETIMEOFFSET datatype where the ODBC implementation would not generate the correct DDL, for cases where the type were converted using the dialect.type_descriptor() method, the usage of which is illustrated in some documented examples for TypeDecorator, though not necessary for most datatypes. Regression was introduced by #6366. As part of this change, the full list of SQL Server date types have been amended to return a “dialect impl” that generates the same DDL name as the supertype. References: #7129

1.4.25

Released: September 22, 2021

platform

• [platform] [bug] [regression] Fixed regression due to #7024 where the reorganization of the “platform machine” names used by the greenlet dependency mis-spelled “aarch64” and additionally omitted uppercase “AMD64” as is needed for Windows machines. Pull request courtesy James Dow. References: #7024

1.4.24

Released: September 22, 2021

platform

• [platform] [bug] Further adjusted the “greenlet” package specifier in setup.cfg to use a long chain of “or” expressions, so that the comparison of platform_machine to a specific identifier matches only the complete string. References: #7024

orm

• [orm] [usecase] Added loader options to Session.merge() and AsyncSession.merge() via a new Session.merge.options parameter, which will apply the given loader options to the get() used internally by merge, allowing eager loading of relationships etc. to be applied when the merge process loads a new object. Pull request courtesy Daniel Stone. References: #6955
• [orm] [bug] [regression] Fixed ORM issue where column expressions passed to query() or ORM-enabled select() would be deduplicated on the identity of the object, such as a phrase like select(A.id, null(), null()) would produce only one “NULL” expression, which previously was not the case in 1.3. However, the change also allows for ORM expressions to render as given as well, such as select(A.data, A.data) will produce a result row with two columns. References: #6979
• [orm] [bug] [regression] Fixed issue in recently repaired Query.with_entities() method where the flag that determines automatic uniquing for legacy ORM Query objects only would be set to True inappropriately in cases where the with_entities() call would be setting the Query to return column-only rows, which are not uniqued. References: #6924

engine

• [engine] [usecase] [asyncio] Improve the interface used by adapted drivers, like the asyncio ones, to access the actual connection object returned by the driver. The _ConnectionFairy object has two new attributes:
  • _ConnectionFairy.dbapi_connection always represents a DBAPI compatible object. For pep-249 drivers, this is the DBAPI connection as it always has been, previously accessed under the .connection attribute. For asyncio drivers that SQLAlchemy adapts into a pep-249 interface, the returned object will normally be a SQLAlchemy adaption object called AdaptedConnection.
  • _ConnectionFairy.driver_connection always represents the actual connection object maintained by the third party pep-249 DBAPI or async driver in use. For standard pep-249 DBAPIs, this will always be the same object as that of the dbapi_connection. For an asyncio driver, it will be the underlying asyncio-only connection object.

  The .connection attribute remains available and is now a legacy alias of .dbapi_connection. See also How do I get at the raw DBAPI connection when using an Engine? References: #6832

• [engine] [usecase] [orm] Added new methods Session.scalars(), Connection.scalars(), AsyncSession.scalars() and AsyncSession.stream_scalars(), which provide a short cut to the use case of receiving a row-oriented Result object and converting it to a ScalarResult object via the Result.scalars() method, to return a list of values rather than a list of rows. The new methods are analogous to the long existing Session.scalar() and Connection.scalar() methods used to return a single value from the first row only. Pull request courtesy Miguel Grinberg. References: #6990
• [engine] [bug] [regression] Fixed issue where the ability of the ConnectionEvents.before_execute() method to alter the SQL statement object passed, returning the new object to be invoked, was inadvertently removed. This behavior has been restored. References: #6913
• [engine] [bug] Ensure that str() is called on the an URL.create.password argument, allowing usage of objects that implement the __str__() method as password attributes. Also clarified that one such object is not appropriate to dynamically change the password for each database connection; the approaches at Generating dynamic authentication tokens should be used instead. References: #6958
• [engine] [bug] Fixed issue in URL where validation of “drivername” would not appropriately respond to the None value where a string were expected. References: #6983
• [engine] [bug] [postgresql] Fixed issue where an engine that had create_engine.implicit_returning set to False would fail to function when PostgreSQL’s “fast insertmany” feature were used in conjunction with a Sequence, as well as if any kind of “executemany” with “return_defaults()” were used in conjunction with a Sequence. Note that PostgreSQL “fast insertmany” uses “RETURNING” by definition, when the SQL statement is passed to the driver; overall, the create_engine.implicit_returning flag is legacy and has no real use in modern SQLAlchemy, and will be deprecated in a separate change. References: #6963

sql

• [sql] [usecase] Added new parameter HasCTE.cte.nesting to the CTE constructor and HasCTE.cte() method, which flags the CTE as one which should remain nested within an enclosing CTE, rather than being moved to the top level of the outermost SELECT. While in the vast majority of cases there is no difference in SQL functionality, users have identified various edge-cases where true nesting of CTE constructs is desirable. Much thanks to Eric Masseran for lots of work on this intricate feature. References: #4123
• [sql] [bug] Implemented missing methods in FunctionElement which, while unused, would lead pylint to report them as unimplemented abstract methods. References: #7052
• [sql] [bug] Fixed a two issues where combinations of select() and join() when adapted to form a copy of the element would not completely copy the state of all column objects associated with subqueries. A key problem this caused is that usage of the ClauseElement.params() method (which should probably be moved into a legacy category as it is inefficient and error prone) would leave copies of the old BindParameter objects around, leading to issues in correctly setting the parameters at execution time. References: #7055
• [sql] [bug] Fixed issue related to new HasCTE.add_cte() feature where pairing two “INSERT…FROM SELECT” statements simultaneously would lose track of the two independent SELECT statements, leading to the wrong SQL. References: #7036
• [sql] [bug] Fixed issue where using ORM column expressions as keys in the list of dictionaries passed to Insert.values() for “multi-valued insert” would not be processed correctly into the correct column expressions. References: #7060

mypy

• [mypy] [bug] Fixed issue where mypy plugin would crash when interpreting a query_expression() construct. References: #6950
• [mypy] [bug] Fixed issue in mypy plugin where columns on a mixin would not be correctly interpreted if the mapped class relied upon a __tablename__ routine that came from a superclass. References: #6937

asyncio

• [asyncio] [feature] [mysql] Added initial support for the asyncmy asyncio database driver for MySQL and MariaDB. This driver is very new, however appears to be the only current alternative to the aiomysql driver which currently appears to be unmaintained and is not working with current Python versions. Much thanks to long2ice for the pull request for this dialect. See also asyncmy References: #6993
• [asyncio] [usecase] The AsyncSession now supports overriding which Session it uses as the proxied instance. A custom Session class can be passed using the AsyncSession.sync_session_class parameter or by subclassing the AsyncSession and specifying a custom AsyncSession.sync_session_class. References: #6746
• [asyncio] [bug] Fixed a bug in AsyncSession.execute() and AsyncSession.stream() that required execution_options to be an instance of immutabledict when defined. It now correctly accepts any mapping. References: #6943
• [asyncio] [bug] Added missing **kw arguments to the AsyncSession.connection() method.
• [asyncio] [bug] Deprecate usage of scoped_session with asyncio drivers. When using Asyncio the async_scoped_session should be used instead. References: #6746

postgresql

• [postgresql] [bug] Qualify version() call to avoid shadowing issues if a different search path is configured by the user. References: #6912
• [postgresql] [bug] The ENUM datatype is PostgreSQL-native and therefore should not be used with the native_enum=False flag. This flag is now ignored if passed to the ENUM datatype and a warning is emitted; previously the flag would cause the type object to fail to function correctly. References: #6106

sqlite

• [sqlite] [bug] Fixed bug where the error message for SQLite invalid isolation level on the pysqlite driver would fail to indicate that “AUTOCOMMIT” is one of the valid isolation levels.

mssql

• [mssql] [bug] [reflection] Fixed an issue where sqlalchemy.engine.reflection.has_table() returned True for local temporary tables that actually belonged to a different SQL Server session (connection). An extra check is now performed to ensure that the temp table detected is in fact owned by the current session. References: #6910

oracle

• [oracle] [performance] [bug] Added a CAST(VARCHAR2(128)) to the “table name”, “owner”, and other DDL-name parameters as used in reflection queries against Oracle system views such as ALL_TABLES, ALL_TAB_CONSTRAINTS, etc to better enable indexing to take place against these columns, as they previously would be implicitly handled as NVARCHAR2 due to Python’s use of Unicode for strings; these columns are documented in all Oracle versions as being VARCHAR2 with lengths varying from 30 to 128 characters depending on server version. Additionally, test support has been enabled for Unicode-named DDL structures against Oracle databases. References: #4486

1.4.23

Released: August 18, 2021

general

• [general] [bug] The setup requirements have been modified such greenlet is a default requirement only for those platforms that are well known for greenlet to be installable and for which there is already a pre-built binary on pypi; the current list is x86_64 aarch64 ppc64le amd64 win32. For other platforms, greenlet will not install by default, which should enable installation and test suite running of SQLAlchemy 1.4 on platforms that don’t support greenlet, excluding any asyncio features. In order to install with the greenlet dependency included on a machine architecture outside of the above list, the [asyncio] extra may be included by running pip install sqlalchemy[asyncio] which will then attempt to install greenlet. Additionally, the test suite has been repaired so that tests can complete fully when greenlet is not installed, with appropriate skips for asyncio-related tests. References: #6136

orm

• [orm] [usecase] Added new attribute Select.columns_clause_froms that will retrieve the FROM list implied by the columns clause of the Select statement. This differs from the old Select.froms collection in that it does not perform any ORM compilation steps, which necessarily deannotate the FROM elements and do things like compute joinedloads etc., which makes it not an appropriate candidate for the Select.select_from() method. Additionally adds a new parameter Select.with_only_columns.maintain_column_froms that transfers this collection to Select.select_from() before replacing the columns collection. In addition, the Select.froms is renamed to Select.get_final_froms(), to stress that this collection is not a simple accessor and is instead calculated given the full state of the object, which can be an expensive call when used in an ORM context. Additionally fixes a regression involving the with_only_columns() function to support applying criteria to column elements that were replaced with either Select.with_only_columns() or Query.with_entities() , which had broken as part of #6503 released in 1.4.19. References: #6808
• [orm] [bug] [sql] Fixed issue where a bound parameter object that was “cloned” would cause a name conflict in the compiler, if more than one clone of this parameter were used at the same time in a single statement. This could occur in particular with things like ORM single table inheritance queries that indicated the same “discriminator” value multiple times in one query. References: #6824
• [orm] [bug] Fixed issue in loader strategies where the use of the Load.options() method, particularly when nesting multiple calls, would generate an overly long and more importantly non-deterministic cache key, leading to very large cache keys which were also not allowing efficient cache usage, both in terms of total memory used as well as number of entries used in the cache itself. References: #6869
• [orm] [bug] Revised the means by which the ORMExecuteState.user_defined_options accessor receives UserDefinedOption and related option objects from the context, with particular emphasis on the “selectinload” on the loader strategy where this previously was not working; other strategies did not have this problem. The objects that are associated with the current query being executed, and not that of a query being cached, are now propagated unconditionally. This essentially separates them out from the “loader strategy” options which are explicitly associated with the compiled state of a query and need to be used in relation to the cached query. The effect of this fix is that a user-defined option, such as those used by the dogpile.caching example as well as for other recipes such as defining a “shard id” for the horizontal sharing extension, will be correctly propagated to eager and lazy loaders regardless of whether a cached query was ultimately invoked. References: #6887
• [orm] [bug] Fixed issue where the unit of work would internally use a 2.0-deprecated SQL expression form, emitting a deprecation warning when SQLALCHEMY_WARN_20 were enabled. References: #6812
• [orm] [bug] Fixed issue in selectinload() where use of the new PropComparator.and_() feature within options that were nested more than one level deep would fail to update bound parameter values that were in the nested criteria, as a side effect of SQL statement caching. References: #6881
• [orm] [bug] Adjusted ORM loader internals to no longer use the “lambda caching” system that was added in 1.4, as well as repaired one location that was still using the previous “baked query” system for a query. The lambda caching system remains an effective way to reduce the overhead of building up queries that have relatively fixed usage patterns. In the case of loader strategies, the queries used are responsible for moving through lots of arbitrary options and criteria, which is both generated and sometimes consumed by end-user code, that make the lambda cache concept not any more efficient than not using it, at the cost of more complexity. In particular the problems noted by #6881 and #6887 are made are made considerably less complicated by removing this feature internally. References: #6079, #6889
• [orm] [bug] Fixed an issue where the Bundle construct would not create proper cache keys, leading to inefficient use of the query cache. This had some impact on the “selectinload” strategy and was identified as part of #6889. References: #6889

sql

• [sql] [bug] Fix issue in CTE where new HasCTE.add_cte() method added in version 1.4.21 / #6752 failed to function correctly for “compound select” structures such as union(), union_all(), except(), etc. Pull request courtesy Eric Masseran. References: #6752
• [sql] [bug] Fixed an issue in the CacheKey.to_offline_string() method used by the dogpile.caching example where attempting to create a proper cache key from the special “lambda” query generated by the lazy loader would fail to include the parameter values, leading to an incorrect cache key. References: #6858
• [sql] [bug] Adjusted the “from linter” warning feature to accommodate for a chain of joins more than one level deep where the ON clauses don’t explicitly match up the targets, such as an expression such as “ON TRUE”. This mode of use is intended to cancel the cartesian product warning simply by the fact that there’s a JOIN from “a to b”, which was not working for the case where the chain of joins had more than one element. References: #6886
• [sql] [bug] Fixed issue in lambda caching system where an element of a query that produces no cache key, like a custom option or clause element, would still populate the expression in the “lambda cache” inappropriately.

schema

• [schema] [enum] Unify behaviour Enum in native and non-native implementations regarding the accepted values for an enum with aliased elements. When Enum.omit_aliases is False all values, alias included, are accepted as valid values. When Enum.omit_aliases is True only non aliased values are accepted as valid values. References: #6146

mypy

• [mypy] [usecase] Added support for SQLAlchemy classes to be defined in user code using “generic class” syntax as defined by sqlalchemy2-stubs, e.g. Column[String], without the need for qualifying these constructs within a TYPE_CHECKING block by implementing the Python special method __class_getitem__(), which allows this syntax to pass without error at runtime. References: #6759, #6804

postgresql

• [postgresql] [bug] Added the “is_comparison” flag to the PostgreSQL “overlaps”, “contained_by”, “contains” operators, so that they work in relevant ORM contexts as well as in conjunction with the “from linter” feature. References: #6886

mssql

• [mssql] [bug] [sql] Fixed issue where the literal_binds compiler flag, as used externally to render bound parameters inline, would fail to work when used with a certain class of parameters known as “literal_execute”, which covers things like LIMIT and OFFSET values for dialects where the drivers don’t allow a bound parameter, such as SQL Server’s “TOP” clause. The issue locally seemed to affect only the MSSQL dialect. References: #6863

misc

• [bug] [ext] Fixed issue where the horizontal sharding extension would not correctly accommodate for a plain textual SQL statement passed to Session.execute(). References: #6816

1.4.22

Released: July 21, 2021

orm

• [orm] [bug] Fixed issue in new Table.table_valued() method where the resulting TableValuedColumn construct would not respond correctly to alias adaptation as is used throughout the ORM, such as for eager loading, polymorphic loading, etc. References: #6775
• [orm] [bug] Fixed issue where usage of the Result.unique() method with an ORM result that included column expressions with unhashable types, such as JSON or ARRAY using non-tuples would silently fall back to using the id() function, rather than raising an error. This now raises an error when the Result.unique() method is used in a 2.0 style ORM query. Additionally, hashability is assumed to be True for result values of unknown type, such as often happens when using SQL functions of unknown return type; if values are truly not hashable then the hash() itself will raise. For legacy ORM queries, since the legacy Query object uniquifies in all cases, the old rules remain in place, which is to use id() for result values of unknown type as this legacy uniquing is mostly for the purpose of uniquing ORM entities and not column values. References: #6769
• [orm] [bug] Fixed an issue where clearing of mappers during things like test suite teardowns could cause a “dictionary changed size” warning during garbage collection, due to iteration of a weak-referencing dictionary. A list() has been applied to prevent concurrent GC from affecting this operation. References: #6771
• [orm] [bug] [regression] Fixed critical caching issue where the ORM’s persistence feature using INSERT…RETURNING would cache an incorrect query when mixing the “bulk save” and standard “flush” forms of INSERT. References: #6793

engine

• [engine] [bug] Added some guards against KeyError in the event system to accommodate the case that the interpreter is shutting down at the same time Engine.dispose() is being called, which would cause stack trace warnings. References: #6740

sql

• [sql] [bug] Fixed issue where use of the case.whens parameter passing a dictionary positionally and not as a keyword argument would emit a 2.0 deprecation warning, referring to the deprecation of passing a list positionally. The dictionary format of “whens”, passed positionally, is still supported and was accidentally marked as deprecated. References: #6786
• [sql] [bug] Fixed issue where type-specific bound parameter handlers would not be called upon in the case of using the Insert.values() method with the Python None value; in particular, this would be noticed when using the JSON datatype as well as related PostgreSQL specific types such as JSONB which would fail to encode the Python None value into JSON null, however the issue was generalized to any bound parameter handler in conjunction with this specific method of Insert. References: #6770

1.4.21

Released: July 14, 2021

orm

• [orm] [usecase] Modified the approach used for history tracking of scalar object relationships that are not many-to-one, i.e. one-to-one relationships that would otherwise be one-to-many. When replacing a one-to-one value, the “old” value that would be replaced is no longer loaded immediately, and is instead handled during the flush process. This eliminates an historically troublesome lazy load that otherwise often occurs when assigning to a one-to-one attribute, and is particularly troublesome when using “lazy=’raise’” as well as asyncio use cases. This change does cause a behavioral change within the AttributeEvents.set() event, which is nonetheless currently documented, which is that the event applied to such a one-to-one attribute will no longer receive the “old” parameter if it is unloaded and the relationship.active_history flag is not set. As is documented in AttributeEvents.set(), if the event handler needs to receive the “old” value when the event fires off, the active_history flag must be established either with the event listener or with the relationship. This is already the behavior with other kinds of attributes such as many-to-one and column value references. The change additionally will defer updating a backref on the “old” value in the less common case that the “old” value is locally present in the session, but isn’t loaded on the relationship in question, until the next flush occurs. If this causes an issue, again the normal relationship.active_history flag can be set to True on the relationship. References: #6708
• [orm] [bug] [regression] Fixed regression caused in 1.4.19 due to #6503 and related involving Query.with_entities() where the new structure used would be inappropriately transferred to an enclosing Query when making use of set operations such as Query.union(), causing the JOIN instructions within to be applied to the outside query as well. References: #6698
• [orm] [bug] [regression] Fixed regression which appeared in version 1.4.3 due to #6060 where rules that limit ORM adaptation of derived selectables interfered with other ORM-adaptation based cases, in this case when applying adaptations for a with_polymorphic() against a mapping which uses a column_property() which in turn makes use of a scalar select that includes a aliased() object of the mapped table. References: #6762
• [orm] [regression] Fixed ORM regression where ad-hoc label names generated for hybrid properties and potentially other similar types of ORM-enabled expressions would usually be propagated outwards through subqueries, allowing the name to be retained in the final keys of the result set even when selecting from subqueries. Additional state is now tracked in this case that isn’t lost when a hybrid is selected out of a Core select / subquery. References: #6718

sql

• [sql] [usecase] Added new method HasCTE.add_cte() to each of the select(), insert(), update() and delete() constructs. This method will add the given CTE as an “independent” CTE of the statement, meaning it renders in the WITH clause above the statement unconditionally even if it is not otherwise referenced in the primary statement. This is a popular use case on the PostgreSQL database where a CTE is used for a DML statement that runs against database rows independently of the primary statement. References: #6752
• [sql] [bug] Fixed issue in CTE constructs where a recursive CTE that referred to a SELECT that has duplicate column names, which are typically deduplicated using labeling logic in 1.4, would fail to refer to the deduplicated label name correctly within the WITH clause. References: #6710
• [sql] [bug] [regression] Fixed regression where the tablesample() construct would fail to be executable when constructed given a floating-point sampling value not embedded within a SQL function. References: #6735

postgresql

• [postgresql] [bug] Fixed issue in Insert.on_conflict_do_nothing() and Insert.on_conflict_do_update() where the name of a unique constraint passed as the constraint parameter would not be properly truncated for length if it were based on a naming convention that generated a too-long name for the PostgreSQL max identifier length of 63 characters, in the same way which occurs within a CREATE TABLE statement. References: #6755
• [postgresql] [bug] Fixed issue where the PostgreSQL ENUM datatype as embedded in the ARRAY datatype would fail to emit correctly in create/drop when the schema_translate_map feature were also in use. Additionally repairs a related issue where the same schema_translate_map feature would not work for the ENUM datatype in combination with a CAST, that’s also intrinsic to how the ARRAY(ENUM) combination works on the PostgreSQL dialect. References: #6739
• [postgresql] [bug] Fixed issue in Insert.on_conflict_do_nothing() and Insert.on_conflict_do_update() where the name of a unique constraint passed as the constraint parameter would not be properly quoted if it contained characters which required quoting. References: #6696

mssql

• [mssql] [bug] [regression] Fixed regression where the special dotted-schema name handling for the SQL Server dialect would not function correctly if the dotted schema name were used within the schema_translate_map feature. References: #6697

1.4.20

Released: June 28, 2021

orm

• [orm] [bug] [regression] Fixed regression in ORM regarding an internal reconstitution step for the with_polymorphic() construct, when the user-facing object is garbage collected as the query is processed. The reconstitution was not ensuring the sub-entities for the “polymorphic” case were handled, leading to an AttributeError. References: #6680
• [orm] [bug] [regression] Adjusted Query.union() and similar set operations to be correctly compatible with the new capabilities just added in #6661, with SQLAlchemy 1.4.19, such that the SELECT statements rendered as elements of the UNION or other set operation will include directly mapped columns that are mapped as deferred; this both fixes a regression involving unions with multiple levels of nesting that would produce a column mismatch, and also allows the undefer() option to be used at the top level of such a Query without having to apply the option to each of the elements within the UNION. References: #6678
• [orm] [bug] Adjusted the check in the mapper for a callable object that is used as a @validates validator function or a @reconstructor reconstruction function, to check for “callable” more liberally such as to accommodate objects based on fundamental attributes like __func__ and __call__, rather than testing for MethodType / FunctionType, allowing things like cython functions to work properly. Pull request courtesy Miłosz Stypiński. References: #6538

engine

• [engine] [bug] Fixed an issue in the C extension for the Row class which could lead to a memory leak in the unlikely case of a Row object which referred to an ORM object that then was mutated to refer back to the Row itself, creating a cycle. The Python C APIs for tracking GC cycles has been added to the native Row implementation to accommodate for this case. References: #5348
• [engine] [bug] Fixed old issue where a select() made against the token “*”, which then yielded exactly one column, would fail to correctly organize the cursor.description column name into the keys of the result object. References: #6665

sql

• [sql] [usecase] Add a impl parameter to PickleType constructor, allowing any arbitrary type to be used in place of the default implementation of LargeBinary. Pull request courtesy jason3gb. References: #6646
• [sql] [bug] [orm] Fixed the class hierarchy for the Sequence and the more general DefaultGenerator base, as these are “executable” as statements they need to include Executable in their hierarchy, not just StatementRole as was applied arbitrarily to Sequence previously. The fix allows Sequence to work in all .execute() methods including with Session.execute() which was not working in the case that a SessionEvents.do_orm_execute() handler was also established. References: #6668

schema

• [schema] [bug] Fixed issue where passing None for the value of Table.prefixes would not store an empty list, but rather the constant None, which may be unexpected by third party dialects. The issue is revealed by a usage in recent versions of Alembic that are passing None for this value. Pull request courtesy Kai Mueller. References: #6685

mysql

• [mysql] [usecase] Made a small adjustment in the table reflection feature of the MySQL dialect to accommodate for alternate MySQL-oriented databases such as TiDB which include their own “comment” directives at the end of a constraint directive within “CREATE TABLE” where the format doesn’t have the additional space character after the comment, in this case the TiDB “clustered index” feature. Pull request courtesy Daniël van Eeden. References: #6659

misc

• [bug] [ext] [regression] Fixed regression in sqlalchemy.ext.automap extension such that the use case of creating an explicit mapped class to a table that is also the relationship.secondary element of a relationship() that automap will be generating would emit the “overlaps” warnings introduced in 1.4 and discussed at relationship X will copy column Q to column P, which conflicts with relationship(s): ‘Y’. While generating this case from automap is still subject to the same caveats mentioned in the ‘overlaps’ warning, since automap is primarily intended for more ad-hoc use cases, the condition triggering the warning is disabled when a many-to-many relationship with this specific pattern is generated. References: #6679

1.4.19

Released: June 22, 2021

orm

• [orm] [bug] [regression] Fixed further regressions in the same area as that of #6052 where loader options as well as invocations of methods like Query.join() would fail if the left side of the statement for which the option/join depends upon were replaced by using the Query.with_entities() method, or when using 2.0 style queries when using the Select.with_only_columns() method. A new set of state has been added to the objects which tracks the “left” entities that the options / join were made against which is memoized when the lead entities are changed. References: #6253, #6503
• [orm] [bug] Refined the behavior of ORM subquery rendering with regards to deferred columns and column properties to be more compatible with that of 1.3 while also providing for 1.4’s newer features. As a subquery in 1.4 does not make use of loader options, including undefer(), a subquery that is against an ORM entity with deferred attributes will now render those deferred attributes that refer directly to mapped table columns, as these are needed in the outer SELECT if that outer SELECT makes use of these columns; however a deferred attribute that refers to a composed SQL expression as we normally do with column_property() will not be part of the subquery, as these can be selected explicitly if needed in the subquery. If the entity is being SELECTed from this subquery, the column expression can still render on “the outside” in terms of the derived subquery columns. This produces essentially the same behavior as when working with 1.3. However in this case the fix has to also make sure that the .selected_columns collection of an ORM-enabled select() also follows these rules, which in particular allows recursive CTEs to render correctly in this scenario, which were previously failing to render correctly due to this issue. References: #6661

sql

• [sql] [bug] Fixed issue in CTE constructs mostly relevant to ORM use cases where a recursive CTE against “anonymous” labels such as those seen in ORM column_property() mappings would render in the WITH RECURSIVE xyz(...) section as their raw internal label and not a cleanly anonymized name. References: #6663

mypy

• [mypy] [bug] Fixed issue in mypy plugin where class info for a custom declarative base would not be handled correctly on a cached mypy pass, leading to an AssertionError being raised. References: #6476

asyncio

• [asyncio] [usecase] Implemented async_scoped_session to address some asyncio-related incompatibilities between scoped_session and AsyncSession, in which some methods (notably the async_scoped_session.remove() method) should be used with the await keyword. See also Using asyncio scoped session References: #6583
• [asyncio] [bug] [postgresql] Fixed bug in asyncio implementation where the greenlet adaptation system failed to propagate BaseException subclasses, most notably including asyncio.CancelledError, to the exception handling logic used by the engine to invalidate and clean up the connection, thus preventing connections from being correctly disposed when a task was cancelled. References: #6652

postgresql

• [postgresql] [bug] [oracle] Fixed issue where the INTERVAL datatype on PostgreSQL and Oracle would produce an AttributeError when used in the context of a comparison operation against a timedelta() object. Pull request courtesy MajorDallas. References: #6649
• [postgresql] [bug] Fixed issue where the pool “pre ping” feature would implicitly start a transaction, which would then interfere with custom transactional flags such as PostgreSQL’s “read only” mode when used with the psycopg2 driver. References: #6621

mysql

• [mysql] [usecase] Added new construct match, which provides for the full range of MySQL’s MATCH operator including multiple column support and modifiers. Pull request courtesy Anton Kovalevich. See also match References: #6132

mssql

• [mssql] [change] Made improvements to the server version regexp used by the pymssql dialect to prevent a regexp overflow in case of an invalid version string. References: #6253, #6503
• [mssql] [bug] Fixed bug where the “schema_translate_map” feature would fail to function correctly in conjunction with an INSERT into a table that has an IDENTITY column, where the value of the IDENTITY column were specified in the values of the INSERT thus triggering SQLAlchemy’s feature of setting IDENTITY INSERT to “on”; it’s in this directive where the schema translate map would fail to be honored. References: #6658

1.4.18

Released: June 10, 2021

orm

• [orm] [performance] [bug] [regression] Fixed regression involving how the ORM would resolve a given mapped column to a result row, where under cases such as joined eager loading, a slightly more expensive “fallback” could take place to set up this resolution due to some logic that was removed since 1.3. The issue could also cause deprecation warnings involving column resolution to be emitted when using a 1.4 style query with joined eager loading. References: #6596
• [orm] [bug] Clarified the current purpose of the relationship.bake_queries flag, which in 1.4 is to enable or disable “lambda caching” of statements within the “lazyload” and “selectinload” loader strategies; this is separate from the more foundational SQL query cache that is used for most statements. Additionally, the lazy loader no longer uses its own cache for many-to-one SQL queries, which was an implementation quirk that doesn’t exist for any other loader scenario. Finally, the “lru cache” warning that the lazyloader and selectinloader strategies could emit when handling a wide array of class/relationship combinations has been removed; based on analysis of some end-user cases, this warning doesn’t suggest any significant issue. While setting bake_queries=False for such a relationship will remove this cache from being used, there’s no particular performance gain in this case as using no caching vs. using a cache that needs to refresh often likely still wins out on the caching being used side. References: #6072, #6487
• [orm] [bug] [regression] Adjusted the means by which classes such as scoped_session and AsyncSession are generated from the base Session class, such that custom Session subclasses such as that used by Flask-SQLAlchemy don’t need to implement positional arguments when they call into the superclass method, and can continue using the same argument styles as in previous releases. References: #6285
• [orm] [bug] [regression] Fixed issue where query production for joinedload against a complex left hand side involving joined-table inheritance could fail to produce a correct query, due to a clause adaption issue. References: #6595
• [orm] [bug] Fixed issue in experimental “select ORM objects from INSERT/UPDATE” use case where an error was raised if the statement were against a single-table-inheritance subclass. References: #6591
• [orm] [bug] The warning that’s emitted for relationship() when multiple relationships would overlap with each other as far as foreign key attributes written towards, now includes the specific “overlaps” argument to use for each warning in order to silence the warning without changing the mapping. References: #6400

asyncio

• [asyncio] [usecase] Implemented a new registry architecture that allows the Async version of an object, like AsyncSession, AsyncConnection, etc., to be locatable given the proxied “sync” object, i.e. Session, Connection. Previously, to the degree such lookup functions were used, an Async object would be re-created each time, which was less than ideal as the identity and state of the “async” object would not be preserved across calls. From there, new helper functions async_object_session(), async_session() as well as a new InstanceState attribute InstanceState.async_session have been added, which are used to retrieve the original AsyncSession associated with an ORM mapped object, a Session associated with an AsyncSession, and an AsyncSession associated with an InstanceState, respectively. This patch also implements new methods AsyncSession.in_nested_transaction(), AsyncSession.get_transaction(), AsyncSession.get_nested_transaction(). References: #6319
• [asyncio] [bug] Fixed an issue that presented itself when using the NullPool or the StaticPool with an async engine. This mostly affected the aiosqlite dialect. References: #6575
• [asyncio] [bug] Added asyncio.exceptions.TimeoutError, asyncio.exceptions.CancelledError as so-called “exit exceptions”, a class of exceptions that include things like GreenletExit and KeyboardInterrupt, which are considered to be events that warrant considering a DBAPI connection to be in an unusable state where it should be recycled. References: #6592

postgresql

• [postgresql] [bug] [regression] Fixed regression where using the PostgreSQL “INSERT…ON CONFLICT” structure would fail to work with the psycopg2 driver if it were used in an “executemany” context along with bound parameters in the “SET” clause, due to the implicit use of the psycopg2 fast execution helpers which are not appropriate for this style of INSERT statement; as these helpers are the default in 1.4 this is effectively a regression. Additional checks to exclude this kind of statement from that particular extension have been added. References: #6581

sqlite

• [sqlite] [bug] Add note regarding encryption-related pragmas for pysqlcipher passed in the url. This change is also backported to: 1.3.25 References: #6589
• [sqlite] [bug] [regression] The fix for pysqlcipher released in version 1.4.3 #5848 was unfortunately non-working, in that the new on_connect_url hook was erroneously not receiving a URL object under normal usage of create_engine() and instead received a string that was unhandled; the test suite failed to fully set up the actual conditions under which this hook is called. This has been fixed. References: #6586

1.4.17

Released: May 29, 2021

orm

• [orm] [bug] [regression] Fixed regression caused by just-released performance fix mentioned in #6550 where a query.join() to a relationship could produce an AttributeError if the query were made against non-ORM structures only, a fairly unusual calling pattern. References: #6558

1.4.16

Released: May 28, 2021

general

• [general] [bug] Resolved various deprecation warnings which were appearing as of Python version 3.10.0b1. References: #6540, #6543

orm

• [orm] [bug] Fixed issue when using relationship.cascade_backrefs parameter set to False, which per cascade_backrefs behavior deprecated for removal in 2.0 is set to become the standard behavior in SQLAlchemy 2.0, where adding the item to a collection that uniquifies, such as set or dict would fail to fire a cascade event if the object were already associated in that collection via the backref. This fix represents a fundamental change in the collection mechanics by introducing a new event state which can fire off for a collection mutation even if there is no net change on the collection; the action is now suited using a new event hook AttributeEvents.append_wo_mutation(). References: #6471
• [orm] [bug] [regression] Fixed regression involving clause adaption of labeled ORM compound elements, such as single-table inheritance discriminator expressions with conditionals or CASE expressions, which could cause aliased expressions such as those used in ORM join / joinedload operations to not be adapted correctly, such as referring to the wrong table in the ON clause in a join. This change also improves a performance bump that was located within the process of invoking Select.join() given an ORM attribute as a target. References: #6550
• [orm] [bug] [regression] Fixed regression where the full combination of joined inheritance, global with_polymorphic, self-referential relationship and joined loading would fail to be able to produce a query with the scope of lazy loads and object refresh operations that also attempted to render the joined loader. References: #6495
• [orm] [bug] Enhanced the bind resolution rules for Session.execute() so that when a non-ORM statement such as an insert() construct nonetheless is built against ORM objects, to the greatest degree possible the ORM entity will be used to resolve the bind, such as for a Session that has a bind map set up on a common superclass without specific mappers or tables named in the map. References: #6484

engine

• [engine] [bug] Fixed issue where an @ sign in the database portion of a URL would not be interpreted correctly if the URL also had a username:password section. References: #6482
• [engine] [bug] Fixed a long-standing issue with URL where query parameters following the question mark would not be parsed correctly if the URL did not contain a database portion with a backslash. References: #6329

sql

• [sql] [bug] [regression] Fixed regression in dynamic loader strategy and relationship() overall where the relationship.order_by parameter were stored as a mutable list, which could then be mutated when combined with additional “order_by” methods used against the dynamic query object, causing the ORDER BY criteria to continue to grow repetitively. References: #6549

mssql

• [mssql] [usecase] Implemented support for a CTE construct to be used directly as the target of a delete() construct, i.e. “WITH … AS cte DELETE FROM cte”. This appears to be a useful feature of SQL Server. References: #6464

misc

• [bug] [ext] Fixed a deprecation warning that was emitted when using automap_base() without passing an existing Base. References: #6529
• [bug] [pep484] Remove pep484 types from the code. Current effort is around the stub package, and having typing in two places makes thing worse, since the types in the SQLAlchemy source were usually outdated compared to the version in the stubs. References: #6461
• [bug] [ext] [regression] Fixed regression in the sqlalchemy.ext.instrumentation extension that prevented instrumentation disposal from working completely. This fix includes both a 1.4 regression fix as well as a fix for a related issue that existed in 1.3 also. As part of this change, the sqlalchemy.ext.instrumentation.InstrumentationManager class now has a new method unregister(), which replaces the previous method dispose(), which was not called as of version 1.4. References: #6390

1.4.15

Released: May 11, 2021

general

• [general] [feature] A new approach has been applied to the warnings system in SQLAlchemy to accurately predict the appropriate stack level for each warning dynamically. This allows evaluating the source of SQLAlchemy-generated warnings and deprecation warnings to be more straightforward as the warning will indicate the source line within end-user code, rather than from an arbitrary level within SQLAlchemy’s own source code. References: #6241

orm

• [orm] [bug] [regression] Fixed additional regression caused by “eager loaders run on unexpire” feature #1763 where the feature would run for a contains_eager() eagerload option in the case that the contains_eager() were chained to an additional eager loader option, which would then produce an incorrect query as the original query-bound join criteria were no longer present. References: #6449
• [orm] [bug] Fixed issue in subquery loader strategy which prevented caching from working correctly. This would have been seen in the logs as a “generated” message instead of “cached” for all subqueryload SQL emitted, which by saturating the cache with new keys would degrade overall performance; it also would produce “LRU size alert” warnings. References: #6459

sql

• [sql] [bug] Adjusted the logic added as part of #6397 in 1.4.12 so that internal mutation of the BindParameter object occurs within the clause construction phase as it did before, rather than in the compilation phase. In the latter case, the mutation still produced side effects against the incoming construct and additionally could potentially interfere with other internal mutation routines. References: #6460

mysql

• [mysql] [bug] [documentation] Added support for the ssl_check_hostname= parameter in mysql connection URIs and updated the mysql dialect documentation regarding secure connections. Original pull request courtesy of Jerry Zhao. References: #5397

1.4.14

Released: May 6, 2021

orm

• [orm] [bug] [regression] Fixed regression involving lazy='dynamic' loader in conjunction with a detached object. The previous behavior was that the dynamic loader upon calling methods like .all() returns empty lists for detached objects without error, this has been restored; however a warning is now emitted as this is not the correct result. Other dynamic loader scenarios correctly raise DetachedInstanceError. References: #6426

engine

• [engine] [usecase] [orm] Applied consistent behavior to the use case of calling .commit() or .rollback() inside of an existing .begin() context manager, with the addition of potentially emitting SQL within the block subsequent to the commit or rollback. This change continues upon the change first added in #6155 where the use case of calling “rollback” inside of a .begin() contextmanager block was proposed:
  • calling .commit() or .rollback() will now be allowed without error or warning within all scopes, including that of legacy and future Engine, ORM Session, asyncio AsyncEngine. Previously, the Session disallowed this.
  • The remaining scope of the context manager is then closed; when the block ends, a check is emitted to see if the transaction was already ended, and if so the block returns without action.
  • It will now raise an error if subsequent SQL of any kind is emitted within the block, after .commit() or .rollback() is called. The block should be closed as the state of the executable object would otherwise be undefined in this state.

  References: #6288

• [engine] [bug] [regression] Established a deprecation path for calling upon the CursorResult.keys() method for a statement that returns no rows to provide support for legacy patterns used by the “records” package as well as any other non-migrated applications. Previously, this would raise ResourceClosedException unconditionally in the same way as it does when attempting to fetch rows. While this is the correct behavior going forward, the LegacyCursorResult object will now in this case return an empty list for .keys() as it did in 1.3, while also emitting a 2.0 deprecation warning. The _cursor.CursorResult, used when using a 2.0-style “future” engine, will continue to raise as it does now. References: #6427

sql

• [sql] [bug] [regression] Fixed regression caused by the “empty in” change just made in #6397 1.4.12 where the expression needs to be parenthesized for the “not in” use case, otherwise the condition will interfere with the other filtering criteria. References: #6428
• [sql] [bug] [regression] The TypeDecorator class will now emit a warning when used in SQL compilation with caching unless the .cache_ok flag is set to True or False. A new class-level attribute TypeDecorator.cache_ok may be set which will be used as an indication that all the parameters passed to the object are safe to be used as a cache key if set to True, False means they are not. References: #6436

1.4.13

Released: May 3, 2021

orm

• [orm] [bug] [regression] Fixed regression in selectinload loader strategy that would cause it to cache its internal state incorrectly when handling relationships that join across more than one column, such as when using a composite foreign key. The invalid caching would then cause other unrelated loader operations to fail. References: #6410
• [orm] [bug] [regression] Fixed regression where Query.filter_by() would not work if the lead entity were a SQL function or other expression derived from the primary entity in question, rather than a simple entity or column of that entity. Additionally, improved the behavior of Select.filter_by() overall to work with column expressions even in a non-ORM context. References: #6414
• [orm] [bug] [regression] Fixed regression where using selectinload() and subqueryload() to load a two-level-deep path would lead to an attribute error. References: #6419
• [orm] [bug] [regression] Fixed regression where using the noload() loader strategy in conjunction with a “dynamic” relationship would lead to an attribute error as the noload strategy would attempt to apply itself to the dynamic loader. References: #6420

engine

• [engine] [bug] [regression] Restored a legacy transactional behavior that was inadvertently removed from the Connection as it was never tested as a known use case in previous versions, where calling upon the Connection.begin_nested() method, when no transaction is present, does not create a SAVEPOINT at all and instead starts an outer transaction, returning a RootTransaction object instead of a NestedTransaction object. This RootTransaction then will emit a real COMMIT on the database connection when committed. Previously, the 2.0 style behavior was present in all cases that would autobegin a transaction but not commit it, which is a behavioral change. When using a 2.0 style connection object, the behavior is unchanged from previous 1.4 versions; calling Connection.begin_nested() will “autobegin” the outer transaction if not already present, and then as instructed emit a SAVEPOINT, returning the NestedTransaction object. The outer transaction is committed by calling upon Connection.commit(), as is “commit-as-you-go” style usage. In non-“future” mode, while the old behavior is restored, it also emits a 2.0 deprecation warning as this is a legacy behavior. References: #6408

asyncio

• [asyncio] [bug] [regression] Fixed a regression introduced by #6337 that would create an asyncio.Lock which could be attached to the wrong loop when instantiating the async engine before any asyncio loop was started, leading to an asyncio error message when attempting to use the engine under certain circumstances. References: #6409

postgresql

• [postgresql] [usecase] Add support for server side cursors in the pg8000 dialect for PostgreSQL. This allows use of the Connection.execution_options.stream_results option. References: #6198

1.4.12

Released: April 29, 2021

orm

• [orm] [bug] Fixed issue in Session.bulk_save_objects() when used with persistent objects which would fail to track the primary key of mappings where the column name of the primary key were different than the attribute name. This change is also backported to: 1.3.25 References: #6392
• [orm] [bug] [caching] [regression] Fixed critical regression where bound parameter tracking as used in the SQL caching system could fail to track all parameters for the case where the same SQL expression containing a parameter were used in an ORM-related query using a feature such as class inheritance, which was then embedded in an enclosing expression which would make use of that same expression multiple times, such as a UNION. The ORM would individually copy the individual SELECT statements as part of compilation with class inheritance, which then embedded in the enclosing statement would fail to accommodate for all parameters. The logic that tracks this condition has been adjusted to work for multiple copies of a parameter. References: #6391
• [orm] [bug] Fixed two distinct issues mostly affecting hybrid_property, which would come into play under common mis-configuration scenarios that were silently ignored in 1.3, and now failed in 1.4, where the “expression” implementation would return a non ClauseElement such as a boolean value. For both issues, 1.3’s behavior was to silently ignore the mis-configuration and ultimately attempt to interpret the value as a SQL expression, which would lead to an incorrect query.
  • Fixed issue regarding interaction of the attribute system with hybrid_property, where if the __clause_element__() method of the attribute returned a non-ClauseElement object, an internal AttributeError would lead the attribute to return the expression function on the hybrid_property itself, as the attribute error was against the name .expression which would invoke the __getattr__() method as a fallback. This now raises explicitly. In 1.3 the non-ClauseElement was returned directly.
  • Fixed issue in SQL argument coercions system where passing the wrong kind of object to methods that expect column expressions would fail if the object were altogether not a SQLAlchemy object, such as a Python function, in cases where the object were not just coerced into a bound value. Again 1.3 did not have a comprehensive argument coercion system so this case would also pass silently.

  References: #6350

• [orm] [bug] Fixed issue where using a Select as a subquery in an ORM context would modify the Select in place to disable eagerloads on that object, which would then cause that same Select to not eagerload if it were then re-used in a top-level execution context. References: #6378
• [orm] [bug] [regression] Fixed issue where the new autobegin behavior failed to “autobegin” in the case where an existing persistent object has an attribute change, which would then impact the behavior of Session.rollback() in that no snapshot was created to be rolled back. The “attribute modify” mechanics have been updated to ensure “autobegin”, which does not perform any database work, does occur when persistent attributes change in the same manner as when Session.add() is called. This is a regression as in 1.3, the rollback() method always had a transaction to roll back and would expire every time. References: #6359, #6360
• [orm] [bug] [regression] Fixed regression in ORM where using hybrid property to indicate an expression from a different entity would confuse the column-labeling logic in the ORM and attempt to derive the name of the hybrid from that other class, leading to an attribute error. The owning class of the hybrid attribute is now tracked along with the name. References: #6386
• [orm] [bug] [regression] Fixed regression in hybrid_property where a hybrid against a SQL function would generate an AttributeError when attempting to generate an entry for the .c collection of a subquery in some cases; among other things this would impact its use in cases like that of Query.count(). References: #6401
• [orm] [bug] [dataclasses] Adjusted the declarative scan for dataclasses so that the inheritance behavior of declared_attr() established on a mixin, when using the new form of having it inside of a dataclasses.field() construct and not actually a descriptor attribute on the class, correctly accommodates the case when the target class to be mapped is a subclass of an existing mapped class which has already mapped that declared_attr(), and therefore should not be re-applied to this class. References: #6346
• [orm] [bug] Fixed an issue with the (deprecated in 1.4) ForeignKeyConstraint.copy() method that caused an error when invoked with the schema argument. References: #6353

engine

• [engine] [bug] Fixed issue where usage of an explicit Sequence would produce inconsistent “inline” behavior for an Insert construct that includes multiple values phrases; the first seq would be inline but subsequent ones would be “pre-execute”, leading to inconsistent sequence ordering. The sequence expressions are now fully inline. References: #6361

sql

• [sql] [bug] Revised the “EMPTY IN” expression to no longer rely upon using a subquery, as this was causing some compatibility and performance problems. The new approach for selected databases takes advantage of using a NULL-returning IN expression combined with the usual “1 != 1” or “1 = 1” expression appended by AND or OR. The expression is now the default for all backends other than SQLite, which still had some compatibility issues regarding tuple “IN” for older SQLite versions. Third party dialects can still override how the “empty set” expression renders by implementing a new compiler method def visit_empty_set_op_expr(self, type_, expand_op), which takes precedence over the existing def visit_empty_set_expr(self, element_types) which remains in place. References: #6258, #6397
• [sql] [bug] [regression] Fixed regression where usage of the text() construct inside the columns clause of a Select construct, which is better handled by using a literal_column() construct, would nonetheless prevent constructs like union() from working correctly. Other use cases, such as constructing subuqeries, continue to work the same as in prior versions where the text() construct is silently omitted from the collection of exported columns. Also repairs similar use within the ORM. References: #6343
• [sql] [bug] [regression] Fixed regression involving legacy methods such as Select.append_column() where internal assertions would fail. References: #6261
• [sql] [bug] [regression] Fixed regression caused by #5395 where tuning back the check for sequences in select() now caused failures when doing 2.0-style querying with a mapped class that also happens to have an __iter__() method. Tuned the check some more to accommodate this as well as some other interesting __iter__() scenarios. References: #6300

schema

• [schema] [bug] [mariadb] [mysql] [oracle] [postgresql] Ensure that the MySQL and MariaDB dialect ignore the Identity construct while rendering the AUTO_INCREMENT keyword in a create table. The Oracle and PostgreSQL compiler was updated to not render Identity if the database version does not support it (Oracle < 12 and PostgreSQL < 10). Previously it was rendered regardless of the database version. References: #6338

postgresql

• [postgresql] [bug] Fixed very old issue where the Enum datatype would not inherit the MetaData.schema parameter of a MetaData object when that object were passed to the Enum using Enum.metadata. References: #6373

sqlite

• [sqlite] [usecase] Default to using SingletonThreadPool for in-memory SQLite databases created using URI filenames. Previously the default pool used was the NullPool that precented sharing the same database between multiple engines. References: #6379

mssql

• [mssql] [bug] [schema] Add TypeEngine.as_generic() support for sqlalchemy.dialects.mysql.BIT columns, mapping them to Boolean. References: #6345
• [mssql] [bug] [regression] Fixed regression caused by #6306 which added support for DateTime(timezone=True), where the previous behavior of the pyodbc driver of implicitly dropping the tzinfo from a timezone-aware date when INSERTing into a timezone-naive DATETIME column were lost, leading to a SQL Server error when inserting timezone-aware datetime objects into timezone-native database columns. References: #6366

1.4.11

Released: April 21, 2021

orm declarative

• [orm] [declarative] [bug] [regression] Fixed regression where recent changes to support Python dataclasses had the inadvertent effect that an ORM mapped class could not successfully override the __new__() method. References: #6331

engine

• [engine] [bug] [regression] Fixed critical regression caused by the change in #5497 where the connection pool “init” phase no longer occurred within mutexed isolation, allowing other threads to proceed with the dialect uninitialized, which could then impact the compilation of SQL statements. References: #6337

1.4.10

Released: April 20, 2021

orm

• [orm] [usecase] Altered some of the behavior repaired in #6232 where the immediateload loader strategy no longer goes into recursive loops; the modification is that an eager load (joinedload, selectinload, or subqueryload) from A->bs->B which then states immediateload for a simple manytoone B->a->A that’s in the identity map will populate the B->A, so that this attribute is back-populated when the collection of A/A.bs are loaded. This allows the objects to be functional when detached.
• [orm] [bug] Fixed bug in new with_loader_criteria() feature where using a mixin class with declared_attr() on an attribute that were accessed inside the custom lambda would emit a warning regarding using an unmapped declared attr, when the lambda callable were first initialized. This warning is now prevented using special instrumentation for this lambda initialization step. References: #6320
• [orm] [bug] [regression] Fixed additional regression caused by the “eagerloaders on refresh” feature added in #1763 where the refresh operation historically would set populate_existing, which given the new feature now overwrites pending changes on eagerly loaded objects when autoflush is false. The populate_existing flag has been turned off for this case and a more specific method used to ensure the correct attributes refreshed. References: #6326
• [orm] [bug] [result] Fixed an issue when using 2.0 style execution that prevented using Result.scalar_one() or Result.scalar_one_or_none() after calling Result.unique(), for the case where the ORM is returning a single-element row in any case. References: #6299

sql

• [sql] [bug] Fixed issue in SQL compiler where the bound parameters set up for a Values construct wouldn’t be positionally tracked correctly if inside of a CTE, affecting database drivers that support VALUES + ctes and use positional parameters such as SQL Server in particular as well as asyncpg. The fix also repairs support for compiler flags such as literal_binds. References: #6327
• [sql] [bug] Repaired and solidified issues regarding custom functions and other arbitrary expression constructs which within SQLAlchemy’s column labeling mechanics would seek to use str(obj) to get a string representation to use as an anonymous column name in the .c collection of a subquery. This is a very legacy behavior that performs poorly and leads to lots of issues, so has been revised to no longer perform any compilation by establishing specific methods on FunctionElement to handle this case, as SQL functions are the only use case that it came into play. An effect of this behavior is that an unlabeled column expression with no derivable name will be given an arbitrary label starting with the prefix "_no_label" in the .c collection of a subquery; these were previously being represented either as the generic stringification of that expression, or as an internal symbol. References: #6256

schema

• [schema] [bug] Fixed issue where next_value() was not deriving its type from the corresponding Sequence, instead hardcoded to Integer. The specific numeric type is now used. References: #6287

mypy

• [mypy] [bug] Fixed issue where mypy plugin would not correctly interpret an explicit Mapped annotation in conjunction with a relationship() that refers to a class by string name; the correct annotation would be downgraded to a less specific one leading to typing errors. References: #6255

mssql

• [mssql] [usecase] The DateTime.timezone parameter when set to True will now make use of the DATETIMEOFFSET column type with SQL Server when used to emit DDL, rather than DATETIME where the flag was silently ignored. References: #6306

misc

• [bug] [declarative] [regression] Fixed instrument_declarative() that called a non existing registry method. References: #6291

1.4.9

Released: April 17, 2021

orm

• [orm] [usecase] Established support for synoynm() in conjunction with hybrid property, assocaitionproxy is set up completely, including that synonyms can be established linking to these constructs which work fully. This is a behavior that was semi-explicitly disallowed previously, however since it did not fail in every scenario, explicit support for assoc proxy and hybrids has been added. References: #6267
• [orm] [performance] [bug] [regression] [sql] Fixed a critical performance issue where the traversal of a select() construct would traverse a repetitive product of the represented FROM clauses as they were each referenced by columns in the columns clause; for a series of nested subqueries with lots of columns this could cause a large delay and significant memory growth. This traversal is used by a wide variety of SQL and ORM functions, including by the ORM Session when it’s configured to have “table-per-bind”, which while this is not a common use case, it seems to be what Flask-SQLAlchemy is hardcoded as using, so the issue impacts Flask-SQLAlchemy users. The traversal has been repaired to uniqify on FROM clauses which was effectively what would happen implicitly with the pre-1.4 architecture. References: #6304
• [orm] [bug] [regression] Fixed regression where an attribute that is mapped to a synonym() could not be used in column loader options such as load_only(). References: #6272

sql

• [sql] [bug] [regression] Fixed regression where an empty in statement on a tuple would result in an error when compiled with the option literal_binds=True. References: #6290

postgresql

• [postgresql] [bug] [regression] [sql] Fixed an argument error in the default and PostgreSQL compilers that would interfere with an UPDATE…FROM or DELETE…FROM…USING statement that was then SELECTed from as a CTE. References: #6303

1.4.8

Released: April 15, 2021

orm

• [orm] [bug] [regression] Fixed a cache leak involving the with_expression() loader option, where the given SQL expression would not be correctly considered as part of the cache key. Additionally, fixed regression involving the corresponding query_expression() feature. While the bug technically exists in 1.3 as well, it was not exposed until 1.4. The “default expr” value of null() would be rendered when not needed, and additionally was also not adapted correctly when the ORM rewrites statements such as when using joined eager loading. The fix ensures “singleton” expressions like NULL and true aren’t “adapted” to refer to columns in ORM statements, and additionally ensures that a query_expression() with no default expression doesn’t render in the statement if a with_expression() isn’t used. References: #6259
• [orm] [bug] Fixed issue in the new feature of Session.refresh() introduced by #1763 where eagerly loaded relationships are also refreshed, where the lazy="raise" and lazy="raise_on_sql" loader strategies would interfere with the immediateload() loader strategy, thus breaking the feature for relationships that were loaded with selectinload(), subqueryload() as well. References: #6252

engine

• [engine] [bug] The Dialect.has_table() method now raises an informative exception if a non-Connection is passed to it, as this incorrect behavior seems to be common. This method is not intended for external use outside of a dialect. Please use the Inspector.has_table() method or for cross-compatibility with older SQLAlchemy versions, the Engine.has_table() method.

sql

• [sql] [feature] The tuple returned by CursorResult.inserted_primary_key is now a Row object with a named tuple interface on top of the existing tuple interface. References: #3314
• [sql] [bug] [regression] Fixed regression where the BindParameter object would not properly render for an IN expression (i.e. using the “post compile” feature in 1.4) if the object were copied from either an internal cloning operation, or from a pickle operation, and the parameter name contained spaces or other special characters. References: #6249
• [sql] [bug] [regression] [sqlite] Fixed regression where the introduction of the INSERT syntax “INSERT… VALUES (DEFAULT)” was not supported on some backends that do however support “INSERT…DEFAULT VALUES”, including SQLite. The two syntaxes are now each individually supported or non-supported for each dialect, for example MySQL supports “VALUES (DEFAULT)” but not “DEFAULT VALUES”. Support for Oracle has also been enabled. References: #6254

mypy

• [mypy] [change] Updated Mypy plugin to only use the public plugin interface of the semantic analyzer.
• [mypy] [bug] Revised the fix for OrderingList from version 1.4.7 which was testing against the incorrect API. References: #6205

asyncio

• [asyncio] [bug] Fix typo that prevented setting the bind attribute of an AsyncSession to the correct value. References: #6220

mssql

• [mssql] [bug] [regression] Fixed an additional regression in the same area as that of #6173, #6184, where using a value of 0 for OFFSET in conjunction with LIMIT with SQL Server would create a statement using “TOP”, as was the behavior in 1.3, however due to caching would then fail to respond accordingly to other values of OFFSET. If the “0” wasn’t first, then it would be fine. For the fix, the “TOP” syntax is now only emitted if the OFFSET value is omitted entirely, that is, Select.offset() is not used. Note that this change now requires that if the “with_ties” or “percent” modifiers are used, the statement can’t specify an OFFSET of zero, it now needs to be omitted entirely. References: #6265

1.4.7

Released: April 9, 2021

orm

• [orm] [bug] [regression] Fixed regression where the subqueryload() loader strategy would fail to correctly accommodate sub-options, such as a defer() option on a column, if the “path” of the subqueryload were more than one level deep. References: #6221
• [orm] [bug] [regression] Fixed regression where the merge_frozen_result() function relied upon by the dogpile.caching example was not included in tests and began failing due to incorrect internal arguments. References: #6211
• [orm] [bug] [regression] Fixed critical regression where the Session could fail to “autobegin” a new transaction when a flush occurred without an existing transaction in place, implicitly placing the Session into legacy autocommit mode which commit the transaction. The Session now has a check that will prevent this condition from occurring, in addition to repairing the flush issue. Additionally, scaled back part of the change made as part of #5226 which can run autoflush during an unexpire operation, to not actually do this in the case of a Session using legacy Session.autocommit mode, as this incurs a commit within a refresh operation. References: #6233
• [orm] [bug] [regression] Fixed regression where the ORM compilation scheme would assume the function name of a hybrid property would be the same as the attribute name in such a way that an AttributeError would be raised, when it would attempt to determine the correct name for each element in a result tuple. A similar issue exists in 1.3 but only impacts the names of tuple rows. The fix here adds a check that the hybrid’s function name is actually present in the __dict__ of the class or its superclasses before assigning this name; otherwise, the hybrid is considered to be “unnamed” and ORM result tuples will use the naming scheme of the underlying expression. References: #6215
• [orm] [bug] [regression] Fixed critical regression caused by the new feature added as part of #1763, eager loaders are invoked on unexpire operations. The new feature makes use of the “immediateload” eager loader strategy as a substitute for a collection loading strategy, which unlike the other “post-load” strategies was not accommodating for recursive invocations between mutually-dependent relationships, leading to recursion overflow errors. References: #6232

engine

• [engine] [bug] [regression] Fixed up the behavior of the Row object when dictionary access is used upon it, meaning converting to a dict via dict(row) or accessing members using strings or other objects i.e. row["some_key"] works as it would with a dictionary, rather than raising TypeError as would be the case with a tuple, whether or not the C extensions are in place. This was originally supposed to emit a 2.0 deprecation warning for the “non-future” case using LegacyRow, and was to raise TypeError for the “future” Row class. However, the C version of Row was failing to raise this TypeError, and to complicate matters, the Session.execute() method now returns Row in all cases to maintain consistency with the ORM result case, so users who didn’t have C extensions installed would see different behavior in this one case for existing pre-1.4 style code. Therefore, in order to soften the overall upgrade scheme as most users have not been exposed to the more strict behavior of Row up through 1.4.6, LegacyRow and Row both provide for string-key access as well as support for dict(row), in all cases emitting the 2.0 deprecation warning when SQLALCHEMY_WARN_20 is enabled. The Row object still uses tuple-like behavior for __contains__, which is probably the only noticeable behavioral change compared to LegacyRow, other than the removal of dictionary-style methods values() and items(). References: #6218

sql

• [sql] [bug] [regression] Enhanced the “expanding” feature used for ColumnOperators.in_() operations to infer the type of expression from the right hand list of elements, if the left hand side does not have any explicit type set up. This allows the expression to support stringification among other things. In 1.3, “expanding” was not automatically used for ColumnOperators.in_() expressions, so in that sense this change fixes a behavioral regression. References: #6222
• [sql] [bug] Fixed the “stringify” compiler to support a basic stringification of a “multirow” INSERT statement, i.e. one with multiple tuples following the VALUES keyword.

schema

• [schema] [bug] [regression] Fixed regression where usage of a token in the Connection.execution_options.schema_translate_map dictionary which contained special characters such as braces would fail to be substituted properly. Use of square bracket characters [] is now explicitly disallowed as these are used as a delimiter character in the current implementation. References: #6216

mypy

• [mypy] [bug] Fixed issue in Mypy plugin where the plugin wasn’t inferring the correct type for columns of subclasses that don’t directly descend from TypeEngine, in particular that of TypeDecorator and UserDefinedType.

tests

• [tests] [change] Added a new flag to DefaultDialect called supports_schemas; third party dialects may set this flag to False to disable SQLAlchemy’s schema-level tests when running the test suite for a third party dialect.

1.4.6

Released: April 6, 2021

orm

• [orm] [bug] [regression] Fixed regression where a deprecated form of Query.join() were used, passing a series of entities to join from without any ON clause in a single Query.join() call, would fail to function correctly. References: #6203
• [orm] [bug] [regression] Fixed critical regression where the Query.yield_per() method in the ORM would set up the internal Result to yield chunks at a time, however made use of the new Result.unique() method which uniques across the entire result. This would lead to lost rows since the ORM is using id(obj) as the uniquing function, which leads to repeated identifiers for new objects as already-seen objects are garbage collected. 1.3’s behavior here was to “unique” across each chunk, which does not actually produce “uniqued” results when results are yielded in chunks. As the Query.yield_per() method is already explicitly disallowed when joined eager loading is in place, which is the primary rationale for the “uniquing” feature, the “uniquing” feature is now turned off entirely when Query.yield_per() is used. This regression only applies to the legacy Query object; when using 2.0 style execution, “uniquing” is not automatically applied. To prevent the issue from arising from explicit use of Result.unique(), an error is now raised if rows are fetched from a “uniqued” ORM-level Result if any yield per API is also in use, as the purpose of yield_per is to allow for arbitrarily large numbers of rows, which cannot be uniqued in memory without growing the number of entries to fit the complete result size. References: #6206

sql

• [sql] [bug] [mssql] [oracle] [regression] Fixed further regressions in the same area as that of #6173 released in 1.4.5, where a “postcompile” parameter, again most typically those used for LIMIT/OFFSET rendering in Oracle and SQL Server, would fail to be processed correctly if the same parameter rendered in multiple places in the statement. References: #6202
• [sql] [bug] Executing a Subquery using Connection.execute() is deprecated and will emit a deprecation warning; this use case was an oversight that should have been removed from 1.4. The operation will now execute the underlying Select object directly for backwards compatibility. Similarly, the CTE class is also not appropriate for execution. In 1.3, attempting to execute a CTE would result in an invalid “blank” SQL statement being executed; since this use case was not working it now raises ObjectNotExecutableError. Previously, 1.4 was attempting to execute the CTE as a statement however it was working only erratically. References: #6204

schema

• [schema] [bug] The Table object now raises an informative error message if it is instantiated without passing at least the Table.name and Table.metadata arguments positionally. Previously, if these were passed as keyword arguments, the object would silently fail to initialize correctly. This change is also backported to: 1.3.25 References: #6135

mypy

• [mypy] [bug] Applied a series of refactorings and fixes to accommodate for Mypy “incremental” mode across multiple files, which previously was not taken into account. In this mode the Mypy plugin has to accommodate Python datatypes expressed in other files coming in with less information than they have on a direct run. Additionally, a new decorator declarative_mixin() is added, which is necessary for the Mypy plugin to be able to definifitely identify a Declarative mixin class that is otherwise not used inside a particular Python file. See also Using @declared_attr and Declarative Mixins References: #6147
• [mypy] [bug] Fixed issue where the Mypy plugin would fail to interpret the “collection_class” of a relationship if it were a callable and not a class. Also improved type matching and error reporting for collection-oriented relationships. References: #6205

asyncio

• [asyncio] [usecase] [postgresql] Added accessors .sqlstate and synonym .pgcode to the .orig attribute of the SQLAlchemy exception class raised by the asyncpg DBAPI adapter, that is, the intermediary exception object that wraps on top of that raised by the asyncpg library itself, but below the level of the SQLAlchemy dialect. References: #6199

1.4.5

Released: April 2, 2021

orm

• [orm] [bug] [regression] Fixed regression where the joinedload() loader strategy would not successfully joinedload to a mapper that is mapper against a CTE construct. References: #6172
• [orm] [bug] [regression] Scaled back the warning message added in #5171 to not warn for overlapping columns in an inheritance scenario where a particular relationship is local to a subclass and therefore does not represent an overlap. References: #6171

sql

• [sql] [bug] [postgresql] Fixed bug in new FunctionElement.render_derived() feature where column names rendered out explicitly in the alias SQL would not have proper quoting applied for case sensitive names and other non-alphanumeric names. References: #6183
• [sql] [bug] [regression] Fixed regression where use of the Operators.in_() method with a Select object against a non-table-bound column would produce an AttributeError, or more generally using a ScalarSelect that has no datatype in a binary expression would produce invalid state. References: #6181
• [sql] [bug] Added a new flag to the Dialect class called Dialect.supports_statement_cache. This flag now needs to be present directly on a dialect class in order for SQLAlchemy’s query cache to take effect for that dialect. The rationale is based on discovered issues such as #6173 revealing that dialects which hardcode literal values from the compiled statement, often the numerical parameters used for LIMIT / OFFSET, will not be compatible with caching until these dialects are revised to use the parameters present in the statement only. For third party dialects where this flag is not applied, the SQL logging will show the message “dialect does not support caching”, indicating the dialect should seek to apply this flag once they have verified that no per-statement literal values are being rendered within the compilation phase. See also Caching for Third Party Dialects References: #6184

schema

• [schema] [bug] Introduce a new parameter Enum.omit_aliases in Enum type allow filtering aliases when using a pep435 Enum. Previous versions of SQLAlchemy kept aliases in all cases, creating database enum type with additional states, meaning that they were treated as different values in the db. For backward compatibility this flag defaults to False in the 1.4 series, but will be switched to True in a future version. A deprecation warning is raise if this flag is not specified and the passed enum contains aliases. References: #6146

mypy

• [mypy] [bug] Fixed issue in mypy plugin where newly added support for as_declarative() needed to more fully add the DeclarativeMeta class to the mypy interpreter’s state so that it does not result in a name not found error; additionally improves how global names are setup for the plugin including the Mapped name. References: #sqlalchemy/sqlalchemy2-stubs/#14

asyncio

• [asyncio] [bug] Fixed issue where the asyncio extension could not be loaded if running Python 3.6 with the backport library of contextvars installed. References: #6166

postgresql

• [postgresql] [bug] [regression] Fixed regression caused by #6023 where the PostgreSQL cast operator applied to elements within an ARRAY when using psycopg2 would fail to use the correct type in the case that the datatype were also embedded within an instance of the Variant adapter. Additionally, repairs support for the correct CREATE TYPE to be emitted when using a Variant(ARRAY(some_schema_type)). This change is also backported to: 1.3.25 References: #6182
• [postgresql] [bug] Fixed typo in the fix for #6099 released in 1.4.4 that completely prevented this change from working correctly, i.e. the error message did not match what was actually emitted by pg8000. References: #6099
• [postgresql] [bug] Fixed issue where the PostgreSQL PGInspector, when generated against an Engine, would fail for .get_enums(), .get_view_names(), .get_foreign_table_names() and .get_table_oid() when used against a “future” style engine and not the connection directly. References: #6170

mysql

• [mysql] [bug] [regression] Fixed regression in the MySQL dialect where the reflection query used to detect if a table exists would fail on very old MySQL 5.0 and 5.1 versions. References: #6163

mssql

• [mssql] [bug] Fixed a regression in MSSQL 2012+ that prevented the order by clause to be rendered when offset=0 is used in a subquery. References: #6163

oracle

• [oracle] [bug] [regression] Fixed critical regression where the Oracle compiler would not maintain the correct parameter values in the LIMIT/OFFSET for a select due to a caching issue. References: #6173

1.4.4

Released: March 30, 2021

orm

• [orm] [bug] Fixed critical issue in the new PropComparator.and_() feature where loader strategies that emit secondary SELECT statements such as selectinload() and lazyload() would fail to accommodate for bound parameters in the user-defined criteria in terms of the current statement being executed, as opposed to the cached statement, causing stale bound values to be used. This also adds a warning for the case where an object that uses lazyload() in conjunction with PropComparator.and_() is attempted to be serialized; the loader criteria cannot reliably be serialized and deserialized and eager loading should be used for this case. References: #6139
• [orm] [bug] [regression] Fixed missing method Session.get() from the ScopedSession interface. References: #6144

engine

• [engine] [usecase] Modified the context manager used by Transaction so that an “already detached” warning is not emitted by the ending of the context manager itself, if the transaction were already manually rolled back inside the block. This applies to regular transactions, savepoint transactions, and legacy “marker” transactions. A warning is still emitted if the .rollback() method is called explicitly more than once. References: #6155
• [engine] [bug] Repair wrong arguments to exception handling method in CursorResult. References: #6138

postgresql

• [postgresql] [bug] [reflection] Fixed issue in PostgreSQL reflection where a column expressing “NOT NULL” will supersede the nullability of a corresponding domain. This change is also backported to: 1.3.24 References: #6161
• [postgresql] [bug] Modified the is_disconnect() handler for the pg8000 dialect, which now accommodates for a new InterfaceError emitted by pg8000 1.19.0. Pull request courtesy Hamdi Burak Usul. References: #6099

misc

• [misc] [bug] Adjusted the usage of the importlib_metadata library for loading setuptools entrypoints in order to accommodate for some deprecation changes.

1.4.3

Released: March 25, 2021

orm

• [orm] [bug] Fixed a bug where python 2.7.5 (default on CentOS 7) wasn’t able to import sqlalchemy, because on this version of Python exec "statement" and exec("statement") do not behave the same way. The compatibility exec_() function was used instead. References: #6069
• [orm] [bug] Fixed bug where ORM queries using a correlated subquery in conjunction with column_property() would fail to correlate correctly to an enclosing subquery or to a CTE when Select.correlate_except() were used in the property to control correlation, in cases where the subquery contained the same selectables as ones within the correlated subquery that were intended to not be correlated. References: #6060
• [orm] [bug] Fixed bug where combinations of the new “relationship with criteria” feature could fail in conjunction with features that make use of the new “lambda SQL” feature, including loader strategies such as selectinload and lazyload, for more complicated scenarios such as polymorphic loading. References: #6131
• [orm] [bug] Repaired support so that the ClauseElement.params() method can work correctly with a Select object that includes joins across ORM relationship structures, which is a new feature in 1.4. References: #6124
• [orm] [bug] Fixed issue where a “removed in 2.0” warning were generated internally by the relationship loader mechanics. References: #6115

orm declarative

• [orm] [declarative] [bug] [regression] Fixed regression where the .metadata attribute on a per class level would not be honored, breaking the use case of per-class-hierarchy MetaData for abstract declarative classes and mixins. See also metadata References: #6128

engine

• [engine] [bug] [regression] Restored the ResultProxy name back to the sqlalchemy.engine namespace. This name refers to the LegacyCursorResult object. References: #6119

schema

• [schema] [bug] Adjusted the logic that emits DROP statements for Sequence objects among the dropping of multiple tables, such that all Sequence objects are dropped after all tables, even if the given Sequence is related only to a Table object and not directly to the overall MetaData object. The use case supports the same Sequence being associated with more than one Table at a time. This change is also backported to: 1.3.24 References: #6071

mypy

• [mypy] [bug] Added support for the Mypy extension to correctly interpret a declarative base class that’s generated using the as_declarative() function as well as the registry.as_declarative_base() method.
• [mypy] [bug] Fixed bug in Mypy plugin where the Python type detection for the Boolean column type would produce an exception; additionally implemented support for Enum, including detection of a string-based enum vs. use of Python enum.Enum. References: #6109

postgresql

• [postgresql] [bug] [types] Adjusted the psycopg2 dialect to emit an explicit PostgreSQL-style cast for bound parameters that contain ARRAY elements. This allows the full range of datatypes to function correctly within arrays. The asyncpg dialect already generated these internal casts in the final statement. This also includes support for array slice updates as well as the PostgreSQL-specific ARRAY.contains() method. This change is also backported to: 1.3.24 References: #6023
• [postgresql] [bug] [reflection] Fixed reflection of identity columns in tables with mixed case names in PostgreSQL. References: #6129

sqlite

• [sqlite] [feature] [asyncio] Added support for the aiosqlite database driver for use with the SQLAlchemy asyncio extension. See also Aiosqlite References: #5920
• [sqlite] [bug] [regression] Repaired the pysqlcipher dialect to connect correctly which had regressed in 1.4, and added test + CI support to maintain the driver in working condition. The dialect now imports the sqlcipher3 module for Python 3 by default before falling back to pysqlcipher3 which is documented as now being unmaintained. See also Pysqlcipher References: #5848

1.4.2

Released: March 19, 2021

orm

• [orm] [usecase] [dataclasses] Added support for the declared_attr object to work in the context of dataclass fields. See also Using Declarative Mixins with pre-existing dataclasses References: #6100
• [orm] [bug] [dataclasses] Fixed issue in new ORM dataclasses functionality where dataclass fields on an abstract base or mixin that contained column or other mapping constructs would not be mapped if they also included a “default” key within the dataclasses.field() object. References: #6093
• [orm] [bug] [regression] Fixed regression where the Query.selectable accessor, which is a synonym for Query.__clause_element__(), got removed, it’s now restored. References: #6088
• [orm] [bug] [regression] Fixed regression where use of an unnamed SQL expression such as a SQL function would raise a column targeting error if the query itself were using joinedload for an entity and was also being wrapped in a subquery by the joinedload eager loading process. References: #6086
• [orm] [bug] [regression] Fixed regression where the Query.filter_by() method would fail to locate the correct source entity if the Query.join() method had been used targeting an entity without any kind of ON clause. References: #6092
• [orm] [bug] [regression] Fixed regression where the SQL compilation of a Function would not work correctly if the object had been “annotated”, which is an internal memoization process used mostly by the ORM. In particular it could affect ORM lazy loads which make greater use of this feature in 1.4. References: #6095
• [orm] [bug] Fixed regression where the ConcreteBase would fail to map at all when a mapped column name overlapped with the discriminator column name, producing an assertion error. The use case here did not function correctly in 1.3 as the polymorphic union would produce a query that ignored the discriminator column entirely, while emitting duplicate column warnings. As 1.4’s architecture cannot easily reproduce this essentially broken behavior of 1.3 at the select() level right now, the use case now raises an informative error message instructing the user to use the .ConcreteBase._concrete_discriminator_name attribute to resolve the conflict. To assist with this configuration, .ConcreteBase._concrete_discriminator_name may be placed on the base class only where it will be automatically used by subclasses; previously this was not the case. References: #6090

engine

• [engine] [bug] [regression] Restored top level import for sqlalchemy.engine.reflection. This ensures that the base Inspector class is properly registered so that inspect() works for third party dialects that don’t otherwise import this package.

sql

• [sql] [bug] [regression] Fixed issue where using a func that includes dotted packagenames would fail to be cacheable by the SQL caching system due to a Python list of names that needed to be a tuple. References: #6101
• [sql] [bug] [regression] Fixed regression in the case() construct, where the “dictionary” form of argument specification failed to work correctly if it were passed positionally, rather than as a “whens” keyword argument. References: #6097

mypy

• [mypy] [bug] Fixed issue in MyPy extension which crashed on detecting the type of a Column if the type were given with a module prefix like sa.Integer(). References: #sqlalchemy/sqlalchemy2-stubs/2

postgresql

• [postgresql] [usecase] Rename the column name used by a reflection query that used a reserved word in some postgresql compatible databases. References: #6982

1.4.1

Released: March 17, 2021

orm

• [orm] [bug] [regression] Fixed regression where producing a Core expression construct such as select() using ORM entities would eagerly configure the mappers, in an effort to maintain compatibility with the Query object which necessarily does this to support many backref-related legacy cases. However, core select() constructs are also used in mapper configurations and such, and to that degree this eager configuration is more of an inconvenience, so eager configure has been disabled for the select() and other Core constructs in the absence of ORM loading types of functions such as Load. The change maintains the behavior of Query so that backwards compatibility is maintained. However, when using a select() in conjunction with ORM entities, a “backref” that isn’t explicitly placed on one of the classes until mapper configure time won’t be available unless configure_mappers() or the newer configure() has been called elsewhere. Prefer using relationship.back_populates for more explicit relationship configuration which does not have the eager configure requirement. References: #6066
• [orm] [bug] [regression] Fixed a critical regression in the relationship lazy loader where the SQL criteria used to fetch a related many-to-one object could go stale in relation to other memoized structures within the loader if the mapper had configuration changes, such as can occur when mappers are late configured or configured on demand, producing a comparison to None and returning no object. Huge thanks to Alan Hamlett for their help tracking this down late into the night. References: #6055
• [orm] [bug] [regression] Fixed regression where the Query.exists() method would fail to create an expression if the entity list of the Query were an arbitrary SQL column expression. References: #6076
• [orm] [bug] [regression] Fixed regression where calling upon Query.count() in conjunction with a loader option such as joinedload() would fail to ignore the loader option. This is a behavior that has always been very specific to the Query.count() method; an error is normally raised if a given Query has options that don’t apply to what it is returning. References: #6052
• [orm] [bug] [regression] Fixed regression in Session.identity_key(), including that the method and related methods were not covered by any unit test as well as that the method contained a typo preventing it from functioning correctly. References: #6067

orm declarative

• [orm] [declarative] [bug] [regression] Fixed bug where user-mapped classes that contained an attribute named “registry” would cause conflicts with the new registry-based mapping system when using DeclarativeMeta. While the attribute remains something that can be set explicitly on a declarative base to be consumed by the metaclass, once located it is placed under a private class variable so it does not conflict with future subclasses that use the same name for other purposes. References: #6054

engine

• [engine] [bug] [regression] The Python namedtuple() has the behavior such that the names count and index will be served as tuple values if the named tuple includes those names; if they are absent, then their behavior as methods of collections.abc.Sequence is maintained. Therefore the Row and LegacyRow classes have been fixed so that they work in this same way, maintaining the expected behavior for database rows that have columns named “index” or “count”. References: #6074

mssql

• [mssql] [bug] [regression] Fixed regression where a new setinputsizes() API that’s available for pyodbc was enabled, which is apparently incompatible with pyodbc’s fast_executemany() mode in the absence of more accurate typing information, which as of yet is not fully implemented or tested. The pyodbc dialect and connector has been modified so that setinputsizes() is not used at all unless the parameter use_setinputsizes is passed to the dialect, e.g. via create_engine(), at which point its behavior can be customized using the DialectEvents.do_setinputsizes() hook. See also Setinputsizes Support References: #6058

misc

• [bug] [regression] Added back items and values to ColumnCollection class. The regression was introduced while adding support for duplicate columns in from clauses and selectable in ticket #4753. References: #6068

1.4.0

Released: March 15, 2021

orm

• [orm] [bug] Removed very old warning that states that passive_deletes is not intended for many-to-one relationships. While it is likely that in many cases placing this parameter on a many-to-one relationship is not what was intended, there are use cases where delete cascade may want to be disallowed following from such a relationship. This change is also backported to: 1.3.24 References: #5983
• [orm] [bug] Fixed issue where the process of joining two tables could fail if one of the tables had an unrelated, unresolvable foreign key constraint which would raise NoReferenceError within the join process, which nonetheless could be bypassed to allow the join to complete. The logic which tested the exception for significance within the process would make assumptions about the construct which would fail. This change is also backported to: 1.3.24 References: #5952
• [orm] [bug] Fixed issue where the MutableComposite construct could be placed into an invalid state when the parent object was already loaded, and then covered by a subsequent query, due to the composite properties’ refresh handler replacing the object with a new one not handled by the mutable extension. This change is also backported to: 1.3.24 References: #6001
• [orm] [bug] Fixed regression where the relationship.query_class parameter stopped being functional for “dynamic” relationships. The AppenderQuery remains dependent on the legacy Query class; users are encouraged to migrate from the use of “dynamic” relationships to using with_parent() instead. References: #5981
• [orm] [bug] [regression] Fixed regression where Query.join() would produce no effect if the query itself as well as the join target were against a Table object, rather than a mapped class. This was part of a more systemic issue where the legacy ORM query compiler would not be correctly used from a Query if the statement produced had not ORM entities present within it. References: #6003
• [orm] [bug] [asyncio] The API for AsyncSession.delete() is now an awaitable; this method cascades along relationships which must be loaded in a similar manner as the AsyncSession.merge() method. References: #5998
• [orm] [bug] The unit of work process now turns off all “lazy=’raise’” behavior altogether when a flush is proceeding. While there are areas where the UOW is sometimes loading things that aren’t ultimately needed, the lazy=”raise” strategy is not helpful here as the user often does not have much control or visibility into the flush process. References: #5984

engine

• [engine] [bug] Fixed bug where the “schema_translate_map” feature failed to be taken into account for the use case of direct execution of DefaultGenerator objects such as sequences, which included the case where they were “pre-executed” in order to generate primary key values when implicit_returning was disabled. This change is also backported to: 1.3.24 References: #5929
• [engine] [bug] Improved engine logging to note ROLLBACK and COMMIT which is logged while the DBAPI driver is in AUTOCOMMIT mode. These ROLLBACK/COMMIT are library level and do not have any effect when AUTOCOMMIT is in effect, however it’s still worthwhile to log as these indicate where SQLAlchemy sees the “transaction” demarcation. References: #6002
• [engine] [bug] [regression] Fixed a regression where the “reset agent” of the connection pool wasn’t really being utilized by the Connection when it were closed, and also leading to a double-rollback scenario that was somewhat wasteful. The newer architecture of the engine has been updated so that the connection pool “reset-on-return” logic will be skipped when the Connection explicitly closes out the transaction before returning the pool to the connection. References: #6004

sql

• [sql] [change] Altered the compilation for the CTE construct so that a string is returned representing the inner SELECT statement if the CTE is stringified directly, outside of the context of an enclosing SELECT; This is the same behavior of FromClause.alias() and Select.subquery(). Previously, a blank string would be returned as the CTE is normally placed above a SELECT after that SELECT has been generated, which is generally misleading when debugging.
• [sql] [bug] Fixed bug where the “percent escaping” feature that occurs with dialects that use the “format” or “pyformat” bound parameter styles was not enabled for the Operators.op() and custom_op constructs, for custom operators that use percent signs. The percent sign will now be automatically doubled based on the paramstyle as necessary. References: #6016
• [sql] [bug] [regression] Fixed regression where the “unsupported compilation error” for unknown datatypes would fail to raise correctly. References: #5979
• [sql] [bug] [regression] Fixed regression where usage of the standalone distinct() used in the form of being directly SELECTed would fail to be locatable in the result set by column identity, which is how the ORM locates columns. While standalone distinct() is not oriented towards being directly SELECTed (use select.distinct() for a regular SELECT DISTINCT..) , it was usable to a limited extent in this way previously (but wouldn’t work in subqueries, for example). The column targeting for unary expressions such as “DISTINCT ” has been improved so that this case works again, and an additional improvement has been made so that usage of this form in a subquery at least generates valid SQL which was not the case previously. The change additionally enhances the ability to target elements in row._mapping based on SQL expression objects in ORM-enabled SELECT statements, including whether the statement was invoked by connection.execute() or session.execute(). References: #6008

schema

• [schema] [bug] [sqlite] Fixed issue where the CHECK constraint generated by Boolean or Enum would fail to render the naming convention correctly after the first compilation, due to an unintended change of state within the name given to the constraint. This issue was first introduced in 0.9 in the fix for issue #3067, and the fix revises the approach taken at that time which appears to have been more involved than what was needed. This change is also backported to: 1.3.24 References: #6007
• [schema] [bug] Repaired / implemented support for primary key constraint naming conventions that use column names/keys/etc as part of the convention. In particular, this includes that the PrimaryKeyConstraint object that’s automatically associated with a Table will update its name as new primary key Column objects are added to the table and then to the constraint. Internal failure modes related to this constraint construction process including no columns present, no name present or blank name present are now accommodated. This change is also backported to: 1.3.24 References: #5919
• [schema] [bug] Deprecated all schema-level .copy() methods and renamed to _copy(). These are not standard Python “copy()” methods as they typically rely upon being instantiated within particular contexts which are passed to the method as optional keyword arguments. The Table.tometadata() method is the public API that provides copying for Table objects. References: #5953

mypy

• [mypy] [feature] Rudimentary and experimental support for Mypy has been added in the form of a new plugin, which itself depends on new typing stubs for SQLAlchemy. The plugin allows declarative mappings in their standard form to both be compatible with Mypy as well as to provide typing support for mapped classes and instances. See also Mypy / Pep-484 Support for ORM Mappings References: #4609

postgresql

• [postgresql] [usecase] [asyncio] [mysql] Added an asyncio.Lock() within SQLAlchemy’s emulated DBAPI cursor, local to the connection, for the asyncpg and aiomysql dialects for the scope of the cursor.execute() and cursor.executemany() methods. The rationale is to prevent failures and corruption for the case where the connection is used in multiple awaitables at once. While this use case can also occur with threaded code and non-asyncio dialects, we anticipate this kind of use will be more common under asyncio, as the asyncio API is encouraging of such use. It’s definitely better to use a distinct connection per concurrent awaitable however as concurrency will not be achieved otherwise. For the asyncpg dialect, this is so that the space between the call to prepare() and fetch() is prevented from allowing concurrent executions on the connection from causing interface error exceptions, as well as preventing race conditions when starting a new transaction. Other PostgreSQL DBAPIs are threadsafe at the connection level so this intends to provide a similar behavior, outside the realm of server side cursors. For the aiomysql dialect, the mutex will provide safety such that the statement execution and the result set fetch, which are two distinct steps at the connection level, won’t get corrupted by concurrent executions on the same connection. References: #5967
• [postgresql] [bug] Fixed issue where using aggregate_order_by would return ARRAY(NullType) under certain conditions, interfering with the ability of the result object to return data correctly. This change is also backported to: 1.3.24 References: #5989

mssql

• [mssql] [bug] Fix a reflection error for MSSQL 2005 introduced by the reflection of filtered indexes. References: #5919

misc

• [usecase] [ext] Add new parameter AutomapBase.prepare.reflection_options to allow passing of MetaData.reflect() options like only or dialect-specific reflection options like oracle_resolve_synonyms. References: #5942
• [bug] [ext] The sqlalchemy.ext.mutable extension now tracks the “parents” collection using the InstanceState associated with objects, rather than the object itself. The latter approach required that the object be hashable so that it can be inside of a WeakKeyDictionary, which goes against the behavioral contract of the ORM overall which is that ORM mapped objects do not need to provide any particular kind of __hash__() method and that unhashable objects are supported. References: #6020

1.4.0b3

Released: February 15, 2021

orm

• [orm] [feature] The ORM used in 2.0 style can now return ORM objects from the rows returned by an UPDATE…RETURNING or INSERT…RETURNING statement, by supplying the construct to Select.from_statement() in an ORM context. See also Using INSERT, UPDATE and ON CONFLICT (i.e. upsert) to return ORM Objects
• [orm] [bug] Fixed issue in new 1.4/2.0 style ORM queries where a statement-level label style would not be preserved in the keys used by result rows; this has been applied to all combinations of Core/ORM columns / session vs. connection etc. so that the linkage from statement to result row is the same in all cases. As part of this change, the labeling of column expressions in rows has been improved to retain the original name of the ORM attribute even if used in a subquery. References: #5933

engine

• [engine] [bug] [postgresql] Continued with the improvement made as part of #5653 to further support bound parameter names, including those generated against column names, for names that include colons, parenthesis, and question marks, as well as improved test support, so that bound parameter names even if they are auto-derived from column names should have no problem including for parenthesis in psycopg2’s “pyformat” style. As part of this change, the format used by the asyncpg DBAPI adapter (which is local to SQLAlchemy’s asyncpg dialect) has been changed from using “qmark” paramstyle to “format”, as there is a standard and internally supported SQL string escaping style for names that use percent signs with “format” style (i.e. to double percent signs), as opposed to names that use question marks with “qmark” style (where an escaping system is not defined by pep-249 or Python). See also psycopg2 dialect no longer has limitations regarding bound parameter names References: #5941

sql

• [sql] [usecase] [postgresql] [sqlite] Enhance set_ keyword of OnConflictDoUpdate to accept a ColumnCollection, such as the .c. collection from a Selectable, or the .excluded contextual object. References: #5939
• [sql] [bug] Fixed bug where the “cartesian product” assertion was not correctly accommodating for joins between tables that relied upon the use of LATERAL to connect from a subquery to another subquery in the enclosing context. References: #5924
• [sql] [bug] Fixed 1.4 regression where the Function.in_() method was not covered by tests and failed to function properly in all cases. References: #5934
• [sql] [bug] Fixed regression where use of an arbitrary iterable with the select() function was not working, outside of plain lists. The forwards/backwards compatibility logic here now checks for a wider range of incoming “iterable” types including that a .c collection from a selectable can be passed directly. Pull request compliments of Oliver Rice. References: #5935

1.4.0b2

Released: February 3, 2021

general

• [general] [bug] Fixed a SQLite source file that had non-ascii characters inside of its docstring without a source encoding, introduced within the “INSERT…ON CONFLICT” feature, which would cause failures under Python 2.

platform

• [platform] [performance] Adjusted some elements related to internal class production at import time which added significant latency to the time spent to import the library vs. that of 1.3. The time is now about 20-30% slower than 1.3 instead of 200%. References: #5681

orm

• [orm] [usecase] Added ORMExecuteState.bind_mapper and ORMExecuteState.all_mappers accessors to ORMExecuteState event object, so that handlers can respond to the target mapper and/or mapped class or classes involved in an ORM statement execution.
• [orm] [usecase] [asyncio] Added AsyncSession.scalar(), AsyncSession.get() as well as support for sessionmaker.begin() to work as an async context manager with AsyncSession. Also added AsyncSession.in_transaction() accessor. References: #5796, #5797, #5802
• [orm] [changed] Mapper “configuration”, which occurs within the configure_mappers() function, is now organized to be on a per-registry basis. This allows for example the mappers within a certain declarative base to be configured, but not those of another base that is also present in memory. The goal is to provide a means of reducing application startup time by only running the “configure” process for sets of mappers that are needed. This also adds the registry.configure() method that will run configure for the mappers local in a particular registry only. References: #5897
• [orm] [bug] Added a comprehensive check and an informative error message for the case where a mapped class, or a string mapped class name, is passed to relationship.secondary. This is an extremely common error which warrants a clear message. Additionally, added a new rule to the class registry resolution such that with regards to the relationship.secondary parameter, if a mapped class and its table are of the identical string name, the Table will be favored when resolving this parameter. In all other cases, the class continues to be favored if a class and table share the identical name. This change is also backported to: 1.3.21 References: #5774
• [orm] [bug] Fixed bug involving the restore_load_context option of ORM events such as InstanceEvents.load() such that the flag would not be carried along to subclasses which were mapped after the event handler were first established. This change is also backported to: 1.3.21 References: #5737
• [orm] [bug] [regression] Fixed issue in new Session similar to that of the Connection where the new “autobegin” logic could be tripped into a re-entrant (recursive) state if SQL were executed within the SessionEvents.after_transaction_create() event hook. References: #5845
• [orm] [bug] [unitofwork] Improved the unit of work topological sorting system such that the toplogical sort is now deterministic based on the sorting of the input set, which itself is now sorted at the level of mappers, so that the same inputs of affected mappers should produce the same output every time, among mappers / tables that don’t have any dependency on each other. This further reduces the chance of deadlocks as can be observed in a flush that UPDATEs among multiple, unrelated tables such that row locks are generated. References: #5735
• [orm] [bug] Fixed regression where the Bundle.single_entity flag would take effect for a Bundle even though it were not set. Additionally, this flag is legacy as it only makes sense for the Query object and not 2.0 style execution. a deprecation warning is emitted when used with new-style execution. References: #5702
• [orm] [bug] Fixed regression where creating an aliased construct against a plain selectable and including a name would raise an assertionerror. References: #5750
• [orm] [bug] Related to the fixes for the lambda criteria system within Core, within the ORM implemented a variety of fixes for the with_loader_criteria() feature as well as the SessionEvents.do_orm_execute() event handler that is often used in conjunction [ticket:5760]:
  • fixed issue where with_loader_criteria() function would fail if the given entity or base included non-mapped mixins in its descending class hierarchy [ticket:5766]
  • The with_loader_criteria() feature is now unconditionally disabled for the case of ORM “refresh” operations, including loads of deferred or expired column attributes as well as for explicit operations like Session.refresh(). These loads are necessarily based on primary key identity where additional WHERE criteria is never appropriate. [ticket:5762]
  • Added new attribute ORMExecuteState.is_column_load to indicate that a SessionEvents.do_orm_execute() handler that a particular operation is a primary-key-directed column attribute load, where additional criteria should not be added. The with_loader_criteria() function as above ignores these in any case now. [ticket:5761]
  • Fixed issue where the ORMExecuteState.is_relationship_load attribute would not be set correctly for many lazy loads as well as all selectinloads. The flag is essential in order to test if options should be added to statements or if they would already have been propagated via relationship loads. [ticket:5764]

  References: #5760, #5761, #5762, #5764, #5766

• [orm] [bug] Fixed 1.4 regression where the use of Query.having() in conjunction with queries with internally adapted SQL elements (common in inheritance scenarios) would fail due to an incorrect function call. Pull request courtesy esoh. References: #5781
• [orm] [bug] Fixed an issue where the API to create a custom executable SQL construct using the sqlalchemy.ext.compiles extension according to the documentation that’s been up for many years would no longer function if only Executable, ClauseElement were used as the base classes, additional classes were needed if wanting to use Session.execute(). This has been resolved so that those extra classes aren’t needed.
• [orm] [bug] [regression] Fixed ORM unit of work regression where an errant “assert primary_key” statement interferes with primary key generation sequences that don’t actually consider the columns in the table to use a real primary key constraint, instead using Mapper.primary_key to establish certain columns as “primary”. References: #5867

orm declarative

• [orm] [declarative] [feature] Added an alternate resolution scheme to Declarative that will extract the SQLAlchemy column or mapped property from the “metadata” dictionary of a dataclasses.Field object. This allows full declarative mappings to be combined with dataclass fields. See also Mapping pre-existing dataclasses using Declarative-style fields References: #5745

engine

• [engine] [feature] Dialect-specific constructs such as Insert.on_conflict_do_update() can now stringify in-place without the need to specify an explicit dialect object. The constructs, when called upon for str(), print(), etc. now have internal direction to call upon their appropriate dialect rather than the “default”dialect which doesn’t know how to stringify these. The approach is also adapted to generic schema-level create/drop such as AddConstraint, which will adapt its stringify dialect to one indicated by the element within it, such as the ExcludeConstraint object.
• [engine] [feature] Added new execution option Connection.execution_options.logging_token. This option will add an additional per-message token to log messages generated by the Connection as it executes statements. This token is not part of the logger name itself (that part can be affected using the existing create_engine.logging_name parameter), so is appropriate for ad-hoc connection use without the side effect of creating many new loggers. The option can be set at the level of Connection or Engine. See also Setting Per-Connection / Sub-Engine Tokens References: #5911
• [engine] [bug] [sqlite] Fixed bug in the 2.0 “future” version of Engine where emitting SQL during the EngineEvents.begin() event hook would cause a re-entrant (recursive) condition due to autobegin, affecting among other things the recipe documented for SQLite to allow for savepoints and serializable isolation support. References: #5845
• [engine] [bug] [oracle] [postgresql] Adjusted the “setinputsizes” logic relied upon by the cx_Oracle, asyncpg and pg8000 dialects to support a TypeDecorator that includes an override the TypeDecorator.get_dbapi_type() method.
• [engine] [bug] Added the “future” keyword to the list of words that are known by the engine_from_config() function, so that the values “true” and “false” may be configured as “boolean” values when using a key such as sqlalchemy.future = true or sqlalchemy.future = false.

sql

• [sql] [feature] Implemented support for “table valued functions” along with additional syntaxes supported by PostgreSQL, one of the most commonly requested features. Table valued functions are SQL functions that return lists of values or rows, and are prevalent in PostgreSQL in the area of JSON functions, where the “table value” is commonly referred to as the “record” datatype. Table valued functions are also supported by Oracle and SQL Server. Features added include:
  • the FunctionElement.table_valued() modifier that creates a table-like selectable object from a SQL function
  • A TableValuedAlias construct that renders a SQL function as a named table
  • Support for PostgreSQL’s special “derived column” syntax that includes column names and sometimes datatypes, such as for the json_to_recordset function, using the TableValuedAlias.render_derived() method.
  • Support for PostgreSQL’s “WITH ORDINALITY” construct using the FunctionElement.table_valued.with_ordinality parameter
  • Support for selection FROM a SQL function as column-valued scalar, a syntax supported by PostgreSQL and Oracle, via the FunctionElement.column_valued() method
  • A way to SELECT a single column from a table-valued expression without using a FROM clause via the FunctionElement.scalar_table_valued() method.

  See also Table-Valued Functions - in the SQLAlchemy Unified Tutorial References: #3566

• [sql] [usecase] Multiple calls to “returning”, e.g. Insert.returning(), may now be chained to add new columns to the RETURNING clause. References: #5695
• [sql] [usecase] Added Select.outerjoin_from() method to complement Select.join_from().
• [sql] [usecase] Adjusted the “literal_binds” feature of Compiler to render NULL for a bound parameter that has None as the value, either explicitly passed or omitted. The previous error message “bind parameter without a renderable value” is removed, and a missing or None value will now render NULL in all cases. Previously, rendering of NULL was starting to happen for DML statements due to internal refactorings, but was not explicitly part of test coverage, which it now is. While no error is raised, when the context is within that of a column comparison, and the operator is not “IS”/”IS NOT”, a warning is emitted that this is not generally useful from a SQL perspective. References: #5888
• [sql] [bug] Fixed issue in new Select.join() method where chaining from the current JOIN wasn’t looking at the right state, causing an expression like “FROM a JOIN b , b JOIN c ” rather than “FROM a JOIN b JOIN c ”. References: #5858
• [sql] [bug] Deprecation warnings are emitted under “SQLALCHEMY_WARN_20” mode when passing a plain string to Session.execute(). References: #5754
• [sql] [bug] [orm] A wide variety of fixes to the “lambda SQL” feature introduced at Using Lambdas to add significant speed gains to statement production have been implemented based on user feedback, with an emphasis on its use within the with_loader_criteria() feature where it is most prominently used [ticket:5760]:
  • Fixed the issue where boolean True/False values, which were referred to in the closure variables of the lambda, would cause failures. [ticket:5763]
  • Repaired a non-working detection for Python functions embedded in the lambda that produce bound values; this case is likely not supportable so raises an informative error, where the function should be invoked outside the lambda itself. New documentation has been added to further detail this behavior. [ticket:5770]
  • The lambda system by default now rejects the use of non-SQL elements within the closure variables of the lambda entirely, where the error suggests the two options of either explicitly ignoring closure variables that are not SQL parameters, or specifying a specific set of values to be considered as part of the cache key based on hash value. This critically prevents the lambda system from assuming that arbitrary objects within the lambda’s closure are appropriate for caching while also refusing to ignore them by default, preventing the case where their state might not be constant and have an impact on the SQL construct produced. The error message is comprehensive and new documentation has been added to further detail this behavior. [ticket:5765]
  • Fixed support for the edge case where an in_() expression against a list of SQL elements, such as literal() objects, would fail to be accommodated correctly. [ticket:5768]

  References: #5760, #5763, #5765, #5768, #5770

• [sql] [bug] [mysql] [postgresql] [sqlite] An informative error message is now raised for a selected set of DML methods (currently all part of Insert constructs) if they are called a second time, which would implicitly cancel out the previous setting. The methods altered include: on_conflict_do_update, on_conflict_do_nothing (SQLite), on_conflict_do_update, on_conflict_do_nothing (PostgreSQL), on_duplicate_key_update (MySQL) References: #5169
• [sql] [bug] Fixed issue in new Values construct where passing tuples of objects would fall back to per-value type detection rather than making use of the Column objects passed directly to Values that tells SQLAlchemy what the expected type is. This would lead to issues for objects such as enumerations and numpy strings that are not actually necessary since the expected type is given. References: #5785
• [sql] [bug] Fixed issue where a RemovedIn20Warning would erroneously emit when the .bind attribute were accessed internally on objects, particularly when stringifying a SQL construct. References: #5717
• [sql] [bug] Properly render cycle=False and order=False as NO CYCLE and NO ORDER in Sequence and Identity objects. References: #5722
• [sql] Replace Query.with_labels() and GenerativeSelect.apply_labels() with explicit getters and setters GenerativeSelect.get_label_style() and GenerativeSelect.set_label_style() to accommodate the three supported label styles: LABEL_STYLE_DISAMBIGUATE_ONLY, LABEL_STYLE_TABLENAME_PLUS_COL, and LABEL_STYLE_NONE. In addition, for Core and “future style” ORM queries, LABEL_STYLE_DISAMBIGUATE_ONLY is now the default label style. This style differs from the existing “no labels” style in that labeling is applied in the case of column name conflicts; with LABEL_STYLE_NONE, a duplicate column name is not accessible via name in any case. For cases where labeling is significant, namely that the .c collection of a subquery is able to refer to all columns unambiguously, the behavior of LABEL_STYLE_DISAMBIGUATE_ONLY is now sufficient for all SQLAlchemy features across Core and ORM which involve this behavior. Result set