2.0 Changelog


no release date


  • [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 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.

    This change is also backported to: 1.4.27

    References: #7274

  • [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


  • [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.

    This change is also backported to: 1.4.27

    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.

    This change is also backported to: 1.4.27

    References: #7272


  • [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


  • [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] [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.

    This change is also backported to: 1.4.27

    References: #7284

  • [postgresql] [dialect]

    Added support for psycopg dialect supporting both sync and async execution. This dialect is available under the postgresql+psycopg name for both the create_engine() and create_async_engine() engine-creation functions.

    References: #6842


  • [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.

    This change is also backported to: 1.4.27

    References: #7281


  • [bug] [setup]

    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

  • [bug] [regression] [types]

    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.

    References: #7319