Changelog
### 2.0.25
```
:released: January 2, 2024
.. change::
:tags: oracle, asyncio
:tickets: 10679
Added support for :ref:`oracledb` in asyncio mode, using the newly released
version of the ``oracledb`` DBAPI that includes asyncio support. For the
2.0 series, this is a preview release, where the current implementation
does not yet have include support for
:meth:`_asyncio.AsyncConnection.stream`. Improved support is planned for
the 2.1 release of SQLAlchemy.
.. change::
:tags: bug, orm
:tickets: 10800
Fixed issue where when making use of the
:paramref:`_orm.relationship.post_update` feature at the same time as using
a mapper version_id_col could lead to a situation where the second UPDATE
statement emitted by the post-update feature would fail to make use of the
correct version identifier, assuming an UPDATE was already emitted in that
flush which had already bumped the version counter.
.. change::
:tags: bug, typing
:tickets: 10801, 10818
Fixed regressions caused by typing added to the ``sqlalchemy.sql.functions``
module in version 2.0.24, as part of :ticket:`6810`:
* Further enhancements to pep-484 typing to allow SQL functions from
:attr:`_sql.func` derived elements to work more effectively with ORM-mapped
attributes (:ticket:`10801`)
* Fixed the argument types passed to functions so that literal expressions
like strings and ints are again interpreted correctly (:ticket:`10818`)
.. change::
:tags: usecase, orm
:tickets: 10807
Added preliminary support for Python 3.12 pep-695 type alias structures,
when resolving custom type maps for ORM Annotated Declarative mappings.
.. change::
:tags: bug, orm
:tickets: 10815
Fixed issue where ORM Annotated Declarative would mis-interpret the left
hand side of a relationship without any collection specified as
uselist=True if the left type were given as a class and not a string,
without using future-style annotations.
.. change::
:tags: bug, sql
:tickets: 10817
Improved compilation of :func:`_sql.any_` / :func:`_sql.all_` in the
context of a negation of boolean comparison, will now render ``NOT (expr)``
rather than reversing the equality operator to not equals, allowing
finer-grained control of negations for these non-typical operators.
.. changelog::
```
### 2.0.24
```
:released: December 28, 2023
.. change::
:tags: bug, orm
:tickets: 10597
Fixed issue where use of :func:`_orm.foreign` annotation on a
non-initialized :func:`_orm.mapped_column` construct would produce an
expression without a type, which was then not updated at initialization
time of the actual column, leading to issues such as relationships not
determining ``use_get`` appropriately.
.. change::
:tags: bug, schema
:tickets: 10654
Fixed issue where error reporting for unexpected schema item when creating
objects like :class:`_schema.Table` would incorrectly handle an argument
that was itself passed as a tuple, leading to a formatting error. The
error message has been modernized to use f-strings.
.. change::
:tags: bug, engine
:tickets: 10662
Fixed URL-encoding of the username and password components of
:class:`.engine.URL` objects when converting them to string using the
:meth:`_engine.URL.render_as_string` method, by using Python standard
library ``urllib.parse.quote`` while allowing for plus signs and spaces to
remain unchanged as supported by SQLAlchemy's non-standard URL parsing,
rather than the legacy home-grown routine from many years ago. Pull request
courtesy of Xavier NUNN.
.. change::
:tags: bug, orm
:tickets: 10668
Improved the error message produced when the unit of work process sets the
value of a primary key column to NULL due to a related object with a
dependency rule on that column being deleted, to include not just the
destination object and column name but also the source column from which
the NULL value is originating. Pull request courtesy Jan Vollmer.
.. change::
:tags: bug, postgresql
:tickets: 10717
Adjusted the asyncpg dialect such that when the ``terminate()`` method is
used to discard an invalidated connection, the dialect will first attempt
to gracefully close the connection using ``.close()`` with a timeout, if
the operation is proceeding within an async event loop context only. This
allows the asyncpg driver to attend to finalizing a ``TimeoutError``
including being able to close a long-running query server side, which
otherwise can keep running after the program has exited.
.. change::
:tags: bug, orm
:tickets: 10732
Modified the ``__init_subclass__()`` method used by
:class:`_orm.MappedAsDataclass`, :class:`_orm.DeclarativeBase` and
:class:`_orm.DeclarativeBaseNoMeta` to accept arbitrary ``**kw`` and to
propagate them to the ``super()`` call, allowing greater flexibility in
arranging custom superclasses and mixins which make use of
``__init_subclass__()`` keyword arguments. Pull request courtesy Michael
Oliver.
.. change::
:tags: bug, tests
:tickets: 10747
Improvements to the test suite to further harden its ability to run
when Python ``greenlet`` is not installed. There is now a tox
target that includes the token "nogreenlet" that will run the suite
with greenlet not installed (note that it still temporarily installs
greenlet as part of the tox config, however).
.. change::
:tags: bug, sql
:tickets: 10753
Fixed issue in stringify for SQL elements, where a specific dialect is not
passed, where a dialect-specific element such as the PostgreSQL "on
conflict do update" construct is encountered and then fails to provide for
a stringify dialect with the appropriate state to render the construct,
leading to internal errors.
.. change::
:tags: bug, sql
Fixed issue where stringifying or compiling a :class:`.CTE` that was
against a DML construct such as an :func:`_sql.insert` construct would fail
to stringify, due to a mis-detection that the statement overall is an
INSERT, leading to internal errors.
.. change::
:tags: bug, orm
:tickets: 10776
Ensured the use case of :class:`.Bundle` objects used in the
``returning()`` portion of ORM-enabled INSERT, UPDATE and DELETE statements
is tested and works fully. This was never explicitly implemented or
tested previously and did not work correctly in the 1.4 series; in the 2.0
series, ORM UPDATE/DELETE with WHERE criteria was missing an implementation
method preventing :class:`.Bundle` objects from working.
.. change::
:tags: bug, orm
:tickets: 10784
Fixed 2.0 regression in :class:`.MutableList` where a routine that detects
sequences would not correctly filter out string or bytes instances, making
it impossible to assign a string value to a specific index (while
non-sequence values would work fine).
.. change::
:tags: change, asyncio
The ``async_fallback`` dialect argument is now deprecated, and will be
removed in SQLAlchemy 2.1. This flag has not been used for SQLAlchemy's
test suite for some time. asyncio dialects can still run in a synchronous
style by running code within a greenlet using :func:`_util.greenlet_spawn`.
.. change::
:tags: bug, typing
:tickets: 6810
Completed pep-484 typing for the ``sqlalchemy.sql.functions`` module.
:func:`_sql.select` constructs made against ``func`` elements should now
have filled-in return types.
.. changelog::
```
### 2.0.23
```
:released: November 2, 2023
.. change::
:tags: bug, oracle
:tickets: 10509
Fixed issue in :class:`.Interval` datatype where the Oracle implementation
was not being used for DDL generation, leading to the ``day_precision`` and
``second_precision`` parameters to be ignored, despite being supported by
this dialect. Pull request courtesy Indivar.
.. change::
:tags: bug, orm
:tickets: 10516
Fixed issue where the ``__allow_unmapped__`` directive failed to allow for
legacy :class:`.Column` / :func:`.deferred` mappings that nonetheless had
annotations such as ``Any`` or a specific type without ``Mapped[]`` as
their type, without errors related to locating the attribute name.
.. change::
:tags: bug, mariadb
:tickets: 10056
Adjusted the MySQL / MariaDB dialects to default a generated column to NULL
when using MariaDB, if :paramref:`_schema.Column.nullable` was not
specified with an explicit ``True`` or ``False`` value, as MariaDB does not
support the "NOT NULL" phrase with a generated column. Pull request
courtesy Indivar.
.. change::
:tags: bug, mariadb, regression
:tickets: 10505
Established a workaround for what seems to be an intrinsic issue across
MySQL/MariaDB drivers where a RETURNING result for DELETE DML which returns
no rows using SQLAlchemy's "empty IN" criteria fails to provide a
cursor.description, which then yields result that returns no rows,
leading to regressions for the ORM that in the 2.0 series uses RETURNING
for bulk DELETE statements for the "synchronize session" feature. To
resolve, when the specific case of "no description when RETURNING was
given" is detected, an "empty result" with a correct cursor description is
generated and used in place of the non-working cursor.
.. change::
:tags: bug, orm
:tickets: 10570
Fixed caching bug where using the :func:`_orm.with_expression` construct in
conjunction with loader options :func:`_orm.selectinload`,
:func:`_orm.lazyload` would fail to substitute bound parameter values
correctly on subsequent caching runs.
.. change::
:tags: usecase, mssql
:tickets: 6521
Added support for the ``aioodbc`` driver implemented for SQL Server,
which builds on top of the pyodbc and general aio* dialect architecture.
.. seealso::
:ref:`mssql_aioodbc` - in the SQL Server dialect documentation.
.. change::
:tags: bug, sql
:tickets: 10535
Added compiler-level None/NULL handling for the "literal processors" of all
datatypes that include literal processing, that is, where a value is
rendered inline within a SQL statement rather than as a bound parameter,
for all those types that do not feature explicit "null value" handling.
Previously this behavior was undefined and inconsistent.
.. change::
:tags: usecase, orm
:tickets: 10575
Implemented the :paramref:`_orm.Session.bulk_insert_mappings.render_nulls`
parameter for new style bulk ORM inserts, allowing ``render_nulls=True`` as
an execution option. This allows for bulk ORM inserts with a mixture of
``None`` values in the parameter dictionaries to use a single batch of rows
for a given set of dicationary keys, rather than breaking up into batches
that omit the NULL columns from each INSERT.
.. seealso::
:ref:`orm_queryguide_insert_null_params`
.. change::
:tags: bug, postgresql
:tickets: 10479
Fixed 2.0 regression caused by :ticket:`7744` where chains of expressions
involving PostgreSQL JSON operators combined with other operators such as
string concatenation would lose correct parenthesization, due to an
implementation detail specific to the PostgreSQL dialect.
.. change::
:tags: bug, postgresql
:tickets: 10532
Fixed SQL handling for "insertmanyvalues" when using the
:class:`.postgresql.BIT` datatype with the asyncpg backend. The
:class:`.postgresql.BIT` on asyncpg apparently requires the use of an
asyncpg-specific ``BitString`` type which is currently exposed when using
this DBAPI, making it incompatible with other PostgreSQL DBAPIs that all
work with plain bitstrings here. A future fix in version 2.1 will
normalize this datatype across all PG backends. Pull request courtesy
Sören Oldag.
.. change::
:tags: usecase, sql
:tickets: 9737
Implemented "literal value processing" for the :class:`.Interval` datatype
for both the PostgreSQL and Oracle dialects, allowing literal rendering of
interval values. Pull request courtesy Indivar Mishra.
.. change::
:tags: bug, oracle
:tickets: 10470
Fixed issue where the cx_Oracle dialect claimed to support a lower
cx_Oracle version (7.x) than was actually supported in practice within the
2.0 series of SQLAlchemy. The dialect imports symbols that are only in
cx_Oracle 8 or higher, so runtime dialect checks as well as setup.cfg
requirements have been updated to reflect this compatibility.
.. change::
:tags: sql
Removed unused placeholder method :meth:`.TypeEngine.compare_against_backend`
This method was used by very old versions of Alembic.
See https://github.com/sqlalchemy/alembic/issues/1293 for details.
.. change::
:tags: bug, orm
:tickets: 10472
Fixed bug in ORM annotated declarative where using a ``ClassVar`` that
nonetheless referred in some way to an ORM mapped class name would fail to
be interpreted as a ``ClassVar`` that's not mapped.
.. change::
:tags: bug, asyncio
:tickets: 10421
Fixed bug with method :meth:`_asyncio.AsyncSession.close_all`
that was not working correctly.
Also added function :func:`_asyncio.close_all_sessions` that's
the equivalent of :func:`_orm.close_all_sessions`.
Pull request courtesy of Bryan不可思议.
.. changelog::
```
### 2.0.22
```
:released: October 12, 2023
.. change::
:tags: bug, orm
:tickets: 10369, 10046
Fixed a wide range of :func:`_orm.mapped_column` parameters that were not
being transferred when using the :func:`_orm.mapped_column` object inside
of a pep-593 ``Annotated`` object, including
:paramref:`_orm.mapped_column.sort_order`,
:paramref:`_orm.mapped_column.deferred`,
:paramref:`_orm.mapped_column.autoincrement`,
:paramref:`_orm.mapped_column.system`, :paramref:`_orm.mapped_column.info`
etc.
Additionally, it remains not supported to have dataclass arguments, such as
:paramref:`_orm.mapped_column.kw_only`,
:paramref:`_orm.mapped_column.default_factory` etc. indicated within the
:func:`_orm.mapped_column` received by ``Annotated``, as this is not
supported with pep-681 Dataclass Transforms. A warning is now emitted when
these parameters are used within ``Annotated`` in this way (and they
continue to be ignored).
.. change::
:tags: bug, orm
:tickets: 10459
Fixed issue where calling :meth:`_engine.Result.unique` with a new-style
:func:`.select` query in the ORM, where one or more columns yields values
that are of "unknown hashability", typically when using JSON functions like
``func.json_build_object()`` without providing a type, would fail
internally when the returned values were not actually hashable. The
behavior is repaired to test the objects as they are received for
hashability in this case, raising an informative error message if not. Note
that for values of "known unhashability", such as when the
:class:`_types.JSON` or :class:`_types.ARRAY` types are used directly, an
informative error message was already raised.
The "hashabiltiy testing" fix here is applied to legacy :class:`.Query` as
well, however in the legacy case, :meth:`_engine.Result.unique` is used for
nearly all queries, so no new warning is emitted here; the legacy behavior
of falling back to using ``id()`` in this case is maintained, with the
improvement that an unknown type that turns out to be hashable will now be
uniqufied, whereas previously it would not.
.. change::
:tags: bug, orm
:tickets: 10453
Fixed regression in recently revised "insertmanyvalues" feature (likely
issue :ticket:`9618`) where the ORM would inadvertently attempt to
interpret a non-RETURNING result as one with RETURNING, in the case where
the ``implicit_returning=False`` parameter were applied to the mapped
:class:`.Table`, indicating that "insertmanyvalues" cannot be used if the
primary key values are not provided.
.. change::
:tags: bug, engine
Fixed issue within some dialects where the dialect could incorrectly return
an empty result set for an INSERT statement that does not actually return
rows at all, due to artfacts from pre- or post-fetching the primary key of
the row or rows still being present. Affected dialects included asyncpg,
all mssql dialects.
.. change::
:tags: bug, typing
:tickets: 10451
Fixed typing issue where the argument list passed to :class:`.Values` was
too-restrictively tied to ``List`` rather than ``Sequence``. Pull request
courtesy Iuri de Silvio.
.. change::
:tags: bug, orm
:tickets: 10365
Fixed bug where ORM :func:`_orm.with_loader_criteria` would not apply
itself to a :meth:`_sql.Select.join` where the ON clause were given as a
plain SQL comparison, rather than as a relationship target or similar.
.. change::
:tags: bug, sql
:tickets: 10408
Fixed issue where referring to a FROM entry in the SET clause of an UPDATE
statement would not include it in the FROM clause of the UPDATE statement,
if that entry were nowhere else in the statement; this occurs currently for
CTEs that were added using :meth:`.Update.add_cte` to provide the desired
CTE at the top of the statement.
.. change::
:tags: bug, mariadb
:tickets: 10396
Modified the mariadb-connector driver to pre-load the ``cursor.rowcount``
value for all queries, to suit tools such as Pandas that hardcode to
calling :attr:`.Result.rowcount` in this way. SQLAlchemy normally pre-loads
``cursor.rowcount`` only for UPDATE/DELETE statements and otherwise passes
through to the DBAPI where it can return -1 if no value is available.
However, mariadb-connector does not support invoking ``cursor.rowcount``
after the cursor itself is closed, raising an error instead. Generic test
support has been added to ensure all backends support the allowing
:attr:`.Result.rowcount` to succceed (that is, returning an integer
value with -1 for "not available") after the result is closed.
.. change::
:tags: bug, mariadb
Additional fixes for the mariadb-connector dialect to support UUID data
values in the result in INSERT..RETURNING statements.
.. change::
:tags: bug, mssql
:tickets: 10458
Fixed bug where the rule that prevents ORDER BY from emitting within
subqueries on SQL Server was not being disabled in the case where the
:meth:`.select.fetch` method were used to limit rows in conjunction with
WITH TIES or PERCENT, preventing valid subqueries with TOP / ORDER BY from
being used.
.. change::
:tags: bug, sql
:tickets: 10443
Fixed 2.0 regression where the :class:`.DDL` construct would no longer
``__repr__()`` due to the removed ``on`` attribute not being accommodated.
Pull request courtesy Iuri de Silvio.
.. change::
:tags: orm, usecase
:tickets: 10202
Added method :meth:`_orm.Session.get_one` that behaves like
:meth:`_orm.Session.get` but raises an exception instead of returning
``None`` if no instance was found with the provided primary key.
Pull request courtesy of Carlos Sousa.
.. change::
:tags: asyncio, bug
Fixed the :paramref:`_asyncio.AsyncSession.get.execution_options` parameter
which was not being propagated to the underlying :class:`_orm.Session` and
was instead being ignored.
.. change::
:tags: bug, orm
:tickets: 10412
Fixed issue where :class:`.Mapped` symbols like :class:`.WriteOnlyMapped`
and :class:`.DynamicMapped` could not be correctly resolved when referenced
as an element of a sub-module in the given annotation, assuming
string-based or "future annotations" style annotations.
.. change::
:tags: bug, engine
:tickets: 10414
Fixed issue where under some garbage collection / exception scenarios the
connection pool's cleanup routine would raise an error due to an unexpected
set of state, which can be reproduced under specific conditions.
.. change::
:tags: bug, typing
Updates to the codebase to support Mypy 1.6.0.
.. change::
:tags: usecase, orm
:tickets: 7787
Added an option to permanently close sessions.
Set to ``False`` the new parameter :paramref:`_orm.Session.close_resets_only`
will prevent a :class:`_orm.Session` from performing any other
operation after :meth:`_orm.Session.close` has been called.
Added new method :meth:`_orm.Session.reset` that will reset a :class:`_orm.Session`
to its initial state. This is an alias of :meth:`_orm.Session.close`,
unless :paramref:`_orm.Session.close_resets_only` is set to ``False``.
.. change::
:tags: orm, bug
:tickets: 10385
Fixed issue with ``__allow_unmapped__`` declarative option
where types that were declared using collection types such as
``list[SomeClass]`` vs. the typing construct ``List[SomeClass]``
would fail to be recognized correctly. Pull request courtesy
Pascal Corpet.
.. changelog::
```
### 2.0.21
```
:released: September 18, 2023
.. change::
:tags: bug, sql
:tickets: 9610
Adjusted the operator precedence for the string concatenation operator to
be equal to that of string matching operators, such as
:meth:`.ColumnElement.like`, :meth:`.ColumnElement.regexp_match`,
:meth:`.ColumnElement.match`, etc., as well as plain ``==`` which has the
same precedence as string comparison operators, so that parenthesis will be
applied to a string concatenation expression that follows a string match
operator. This provides for backends such as PostgreSQL where the "regexp
match" operator is apparently of higher precedence than the string
concatenation operator.
.. change::
:tags: bug, sql
:tickets: 10342
Qualified the use of ``hashlib.md5()`` within the DDL compiler, which is
used to generate deterministic four-character suffixes for long index and
constraint names in DDL statements, to include the Python 3.9+
``usedforsecurity=False`` parameter so that Python interpreters built for
restricted environments such as FIPS do not consider this call to be
related to security concerns.
.. change::
:tags: bug, postgresql
:tickets: 10226
Fixed regression which appeared in 2.0 due to :ticket:`8491` where the
revised "ping" used for PostgreSQL dialects when the
:paramref:`_sa.create_engine.pool_pre_ping` parameter is in use would
interfere with the use of asyncpg with PGBouncer "transaction" mode, as the
multiple PostgreSQL commands emitted by asnycpg could be broken out among
multiple connections leading to errors, due to the lack of any transaction
around this newly revised "ping". The ping is now invoked within a
transaction, in the same way that is implicit with all other backends that
are based on the pep-249 DBAPI; this guarantees that the series of PG
commands sent by asyncpg for this command are invoked on the same backend
connection without it jumping to a different connection mid-command. The
transaction is not used if the asyncpg dialect is used in "AUTOCOMMIT"
mode, which remains incompatible with pgbouncer transaction mode.
.. change::
:tags: bug, orm
:tickets: 10279
Adjusted the ORM's interpretation of the "target" entity used within
:class:`.Update` and :class:`.Delete` to not interfere with the target
"from" object passed to the statement, such as when passing an ORM-mapped
:class:`_orm.aliased` construct that should be maintained within a phrase
like "UPDATE FROM". Cases like ORM session synchonize using "SELECT"
statements such as with MySQL/ MariaDB will still have issues with
UPDATE/DELETE of this form so it's best to disable synchonize_session when
using DML statements of this type.
.. change::
:tags: bug, orm
:tickets: 10348
Added new capability to the :func:`_orm.selectin_polymorphic` loader option
which allows other loader options to be bundled as siblings, referring to
one of its subclasses, within the sub-options of parent loader option.
Previously, this pattern was only supported if the
:func:`_orm.selectin_polymorphic` were at the top level of the options for
the query. See new documentation section for example.
As part of this change, improved the behavior of the
:meth:`_orm.Load.selectin_polymorphic` method / loader strategy so that the
subclass load does not load most already-loaded columns from the parent
table, when the option is used against a class that is already being
relationship-loaded. Previously, the logic to load only the subclass
columns worked only for a top level class load.
.. seealso::
:ref:`polymorphic_selectin_as_loader_option_target_plus_opts`
.. change::
:tags: bug, typing
:tickets: 10264, 9284
Fixed regression introduced in 2.0.20 via :ticket:`9600` fix which
attempted to add more formal typing to
:paramref:`_schema.MetaData.naming_convention`. This change prevented basic
naming convention dictionaries from passing typing and has been adjusted so
that a plain dictionary of strings for keys as well as dictionaries that
use constraint types as keys or a mix of both, are again accepted.
As part of this change, lesser used forms of the naming convention
dictionary are also typed, including that it currently allows for
``Constraint`` type objects as keys as well.
.. change::
:tags: usecase, typing
:tickets: 10288
Made the contained type for :class:`.Mapped` covariant; this is to allow
greater flexibility for end-user typing scenarios, such as the use of
protocols to represent particular mapped class structures that are passed
to other functions. As part of this change, the contained type was also
made covariant for dependent and related types such as
:class:`_orm.base.SQLORMOperations`, :class:`_orm.WriteOnlyMapped`, and
:class:`_sql.SQLColumnExpression`. Pull request courtesy Roméo Després.
.. change::
:tags: bug, engine
:tickets: 10275
Fixed a series of reflection issues affecting the PostgreSQL,
MySQL/MariaDB, and SQLite dialects when reflecting foreign key constraints
where the target column contained parenthesis in one or both of the table
name or column name.
.. change::
:tags: bug, sql
:tickets: 10280
The :class:`.Values` construct will now automatically create a proxy (i.e.
a copy) of a :class:`_sql.column` if the column were already associated
with an existing FROM clause. This allows that an expression like
``values_obj.c.colname`` will produce the correct FROM clause even in the
case that ``colname`` was passed as a :class:`_sql.column` that was already
used with a previous :class:`.Values` or other table construct.
Originally this was considered to be a candidate for an error condition,
however it's likely this pattern is already in widespread use so it's
now added to support.
.. change::
:tags: bug, setup
:tickets: 10321
Fixed very old issue where the full extent of SQLAlchemy modules, including
``sqlalchemy.testing.fixtures``, could not be imported outside of a pytest
run. This suits inspection utilities such as ``pkgutil`` that attempt to
import all installed modules in all packages.
.. change::
:tags: usecase, sql
:tickets: 10269
Adjusted the :class:`_types.Enum` datatype to accept an argument of
``None`` for the :paramref:`_types.Enum.length` parameter, resulting in a
VARCHAR or other textual type with no length in the resulting DDL. This
allows for new elements of any length to be added to the type after it
exists in the schema. Pull request courtesy Eugene Toder.
.. change::
:tags: bug, typing
:tickets: 9878
Fixed the type annotation for ``__class_getitem__()`` as applied to the
``Visitable`` class at the base of expression constructs to accept ``Any``
for a key, rather than ``str``, which helps with some IDEs such as PyCharm
when attempting to write typing annotations for SQL constructs which
include generic selectors. Pull request courtesy Jordan Macdonald.
.. change::
:tags: bug, typing
:tickets: 10353
Repaired the core "SQL element" class ``SQLCoreOperations`` to support the
``__hash__()`` method from a typing perspective, as objects like
:class:`.Column` and ORM :class:`.InstrumentedAttribute` are hashable and
are used as dictionary keys in the public API for the :class:`_dml.Update`
and :class:`_dml.Insert` constructs. Previously, type checkers were not
aware the root SQL element was hashable.
.. change::
:tags: bug, typing
:tickets: 10337
Fixed typing issue with :meth:`_sql.Existing.select_from` that
prevented its use with ORM classes.
.. change::
:tags: usecase, sql
:tickets: 9873
Added new generic SQL function :class:`_functions.aggregate_strings`, which
accepts a SQL expression and a decimeter, concatenating strings on multiple
rows into a single aggregate value. The function is compiled on a
per-backend basis, into functions such as ``group_concat(),``
``string_agg()``, or ``LISTAGG()``.
Pull request courtesy Joshua Morris.
.. change::
:tags: typing, bug
:tickets: 10131
Update type annotations for ORM loading options, restricting them to accept
only `"*"` instead of any string for string arguments. Pull request
courtesy Janek Nouvertné.
.. changelog::
```
### 2.0.20
```
:released: August 15, 2023
.. change::
:tags: bug, orm
:tickets: 10169
Fixed issue where the ORM's generation of a SELECT from a joined
inheritance model with same-named columns in superclass and subclass would
somehow not send the correct list of column names to the :class:`.CTE`
construct, when the RECURSIVE column list were generated.
.. change::
:tags: bug, typing
:tickets: 9185
Typing improvements:
* :class:`.CursorResult` is returned for some forms of
:meth:`_orm.Session.execute` where DML without RETURNING is used
* fixed type for :paramref:`_orm.Query.with_for_update.of` parameter within
:meth:`_orm.Query.with_for_update`
* improvements to ``_DMLColumnArgument`` type used by some DML methods to
pass column expressions
* Add overload to :func:`_sql.literal` so that it is inferred that the
return type is ``BindParameter[NullType]`` where
:paramref:`_sql.literal.type_` param is None
* Add overloads to :meth:`_sql.ColumnElement.op` so that the inferred
type when :paramref:`_sql.ColumnElement.op.return_type` is not provided
is ``Callable[[Any], BinaryExpression[Any]]``
* Add missing overload to :meth:`_sql.ColumnElement.__add__`
Pull request courtesy Mehdi Gmira.
.. change::
:tags: usecase, orm
:tickets: 10192
Implemented the "RETURNING '*'" use case for ORM enabled DML statements.
This will render in as many cases as possible and return the unfiltered
result set, however is not supported for multi-parameter "ORM bulk INSERT"
statements that have specific column rendering requirements.
.. change::
:tags: bug, typing
:tickets: 10182
Fixed issue in :class:`_orm.Session` and :class:`_asyncio.AsyncSession`
methods such as :meth:`_orm.Session.connection` where the
:paramref:`_orm.Session.connection.execution_options` parameter were
hardcoded to an internal type that is not user-facing.
.. change::
:tags: orm, bug
:tickets: 10231
Fixed fairly major issue where execution options passed to
:meth:`_orm.Session.execute`, as well as execution options local to the ORM
executed statement itself, would not be propagated along to eager loaders
such as that of :func:`_orm.selectinload`, :func:`_orm.immediateload`, and
:meth:`_orm.subqueryload`, making it impossible to do things such as
disabling the cache for a single statement or using
``schema_translate_map`` for a single statement, as well as the use of
user-custom execution options. A change has been made where **all**
user-facing execution options present for :meth:`_orm.Session.execute` will
be propagated along to additional loaders.
As part of this change, the warning for "excessively deep" eager loaders
leading to caching being disabled can be silenced on a per-statement
basis by sending ``execution_options={"compiled_cache": None}`` to
:meth:`_orm.Session.execute`, which will disable caching for the full
series of statements within that scope.
.. change::
:tags: usecase, asyncio
:tickets: 9698
Added new methods :meth:`_asyncio.AsyncConnection.aclose` as a synonym for
:meth:`_asyncio.AsyncConnection.close` and
:meth:`_asyncio.AsyncSession.aclose` as a synonym for
:meth:`_asyncio.AsyncSession.close` to the
:class:`_asyncio.AsyncConnection` and :class:`_asyncio.AsyncSession`
objects, to provide compatibility with Python standard library
``contextlib.aclosing`` construct. Pull request courtesy Grigoriev Semyon.
.. change::
:tags: bug, orm
:tickets: 10124
Fixed issue where internal cloning used by the ORM for expressions like
:meth:`_orm.relationship.Comparator.any` to produce correlated EXISTS
constructs would interfere with the "cartesian product warning" feature of
the SQL compiler, leading the SQL compiler to warn when all elements of the
statement were correctly joined.
.. change::
:tags: orm, bug
:tickets: 10139
Fixed issue where the ``lazy="immediateload"`` loader strategy would place
an internal loading token into the ORM mapped attribute under circumstances
where the load should not occur, such as in a recursive self-referential
load. As part of this change, the ``lazy="immediateload"`` strategy now
honors the :paramref:`_orm.relationship.join_depth` parameter for
self-referential eager loads in the same way as that of other eager
loaders, where leaving it unset or set at zero will lead to a
self-referential immediateload not occurring, setting it to a value of one
or greater will immediateload up until that given depth.
.. change::
:tags: bug, orm
:tickets: 10175
Fixed issue where dictionary-based collections such as
:func:`_orm.attribute_keyed_dict` did not fully pickle/unpickle correctly,
leading to issues when attempting to mutate such a collection after
unpickling.
.. change::
:tags: bug, orm
:tickets: 10125
Fixed issue where chaining :func:`_orm.load_only` or other wildcard use of
:func:`_orm.defer` from another eager loader using a :func:`_orm.aliased`
against a joined inheritance subclass would fail to take effect for columns
local to the superclass.
.. change::
:tags: bug, orm
:tickets: 10167
Fixed issue where an ORM-enabled :func:`_sql.select` construct would not
render any CTEs added only via the :meth:`_sql.Select.add_cte` method that
were not otherwise referenced in the statement.
.. change::
:tags: bug, examples
The dogpile_caching examples have been updated for 2.0 style queries.
Within the "caching query" logic itself there is one conditional added to
differentiate between ``Query`` and ``select()`` when performing an
invalidation operation.
.. change::
:tags: typing, usecase
:tickets: 10173
Added new typing only utility functions :func:`.Nullable` and
:func:`.NotNullable` to type a column or ORM class as, respectively,
nullable or not nullable.
These function are no-op at runtime, returning the input unchanged.
.. change::
:tags: bug, engine
:tickets: 10147
Fixed critical issue where setting
:paramref:`_sa.create_engine.isolation_level` to ``AUTOCOMMIT`` (as opposed
to using the :meth:`_engine.Engine.execution_options` method) would fail to
restore "autocommit" to a pooled connection if an alternate isolation level
were temporarily selected using
:paramref:`_engine.Connection.execution_options.isolation_level`.
.. changelog::
```
### 2.0.19
```
:released: July 15, 2023
.. change::
:tags: bug, orm
:tickets: 10089
Fixed issue where setting a relationship collection directly, where an
object in the new collection were already present, would not trigger a
cascade event for that object, leading to it not being added to the
:class:`_orm.Session` if it were not already present. This is similar in
nature to :ticket:`6471` and is a more apparent issue due to the removal of
``cascade_backrefs`` in the 2.0 series. The
:meth:`_orm.AttributeEvents.append_wo_mutation` event added as part of
:ticket:`6471` is now also emitted for existing members of a collection
that are present in a bulk set of that same collection.
.. change::
:tags: bug, engine
:tickets: 10093
Renamed :attr:`_result.Row.t` and :meth:`_result.Row.tuple` to
:attr:`_result.Row._t` and :meth:`_result.Row._tuple`; this is to suit the
policy that all methods and pre-defined attributes on :class:`.Row` should
be in the style of Python standard library ``namedtuple`` where all fixed
names have a leading underscore, to avoid name conflicts with existing
column names. The previous method/attribute is now deprecated and will
emit a deprecation warning.
.. change::
:tags: bug, postgresql
:tickets: 10069
Fixed regression caused by improvements to PostgreSQL URL parsing in
:ticket:`10004` where "host" query string arguments that had colons in
them, to support various third party proxy servers and/or dialects, would
not parse correctly as these were evaluted as ``host:port`` combinations.
Parsing has been updated to consider a colon as indicating a ``host:port``
value only if the hostname contains only alphanumeric characters with dots
or dashes only (e.g. no slashes), followed by exactly one colon followed by
an all-integer token of zero or more integers. In all other cases, the
full string is taken as a host.
.. change::
:tags: bug, engine
:tickets: 10079
Added detection for non-string, non-:class:`_engine.URL` objects to the
:func:`_engine.make_url` function, allowing ``ArgumentError`` to be thrown
immediately, rather than causing failures later on. Special logic ensures
that mock forms of :class:`_engine.URL` are allowed through. Pull request
courtesy Grigoriev Semyon.
.. change::
:tags: bug, orm
:tickets: 10090
Fixed issue where objects that were associated with an unloaded collection
via backref, but were not merged into the :class:`_orm.Session` due to the
removal of ``cascade_backrefs`` in the 2.0 series, would not emit a warning
that these objects were not being included in a flush, even though they
were pending members of the collection; in other such cases, a warning is
emitted when a collection being flushed contains non-attached objects which
will be essentially discarded. The addition of the warning for
backref-pending collection members establishes greater consistency with
collections that may be present or non-present and possibly flushed or not
flushed at different times based on different relationship loading
strategies.
.. change::
:tags: bug, postgresql
:tickets: 10096
Fixed issue where comparisons to the :class:`_postgresql.CITEXT` datatype
would cast the right side to ``VARCHAR``, leading to the right side not
being interpreted as a ``CITEXT`` datatype, for the asyncpg, psycopg3 and
pg80000 dialects. This led to the :class:`_postgresql.CITEXT` type being
essentially unusable for practical use; this is now fixed and the test
suite has been corrected to properly assert that expressions are rendered
correctly.
.. change::
:tags: bug, orm, regression
:tickets: 10098
Fixed additional regression caused by :ticket:`9805` where more aggressive
propagation of the "ORM" flag on statements could lead to an internal
attribute error when embedding an ORM :class:`.Query` construct that
nonetheless contained no ORM entities within a Core SQL statement, in this
case ORM-enabled UPDATE and DELETE statements.
.. changelog::
```
### 2.0.18
```
:released: July 5, 2023
.. change::
:tags: usecase, typing
:tickets: 10054
Improved typing when using standalone operator functions from
``sqlalchemy.sql.operators`` such as ``sqlalchemy.sql.operators.eq``.
.. change::
:tags: usecase, mariadb, reflection
:tickets: 10028
Allowed reflecting :class:`_types.UUID` columns from MariaDB. This allows
Alembic to properly detect the type of such columns in existing MariaDB
databases.
.. change::
:tags: bug, postgresql
:tickets: 9945
Added new parameter ``native_inet_types=False`` to all PostgreSQL
dialects, which indicates converters used by the DBAPI to
convert rows from PostgreSQL :class:`.INET` and :class:`.CIDR` columns
into Python ``ipaddress`` datatypes should be disabled, returning strings
instead. This allows code written to work with strings for these datatypes
to be migrated to asyncpg, psycopg, or pg8000 without code changes
other than adding this parameter to the :func:`_sa.create_engine`
or :func:`_asyncio.create_async_engine` function call.
.. seealso::
:ref:`postgresql_network_datatypes`
.. change::
:tags: usecase, extensions
:tickets: 10013
Added new option to :func:`.association_proxy`
:paramref:`.association_proxy.create_on_none_assignment`; when an
association proxy which refers to a scalar relationship is assigned the
value ``None``, and the referenced object is not present, a new object is
created via the creator. This was apparently an undefined behavior in the
1.2 series that was silently removed.
.. change::
:tags: bug, typing
:tickets: 10061
Fixed some of the typing within the :func:`_orm.aliased` construct to
correctly accept a :class:`.Table` object that's been aliased with
:meth:`.Table.alias`, as well as general support for :class:`.FromClause`
objects to be passed as the "selectable" argument, since this is all
supported.
.. change::
:tags: bug, engine
:tickets: 10025
Adjusted the :paramref:`_sa.create_engine.schema_translate_map` feature
such that **all** schema names in the statement are now tokenized,
regardless of whether or not a specific name is in the immediate schema
translate map given, and to fallback to substituting the original name when
the key is not in the actual schema translate map at execution time. These
two changes allow for repeated use of a compiled object with schema
schema_translate_maps that include or dont include various keys on each
run, allowing cached SQL constructs to continue to function at runtime when
schema translate maps with different sets of keys are used each time. In
addition, added detection of schema_translate_map dictionaries which gain
or lose a ``None`` key across calls for the same statement, which affects
compilation of the statement and is not compatible with caching; an
exception is raised for these scenarios.
.. change::
:tags: bug, mssql, sql
:tickets: 9932
Fixed issue where performing :class:`.Cast` to a string type with an
explicit collation would render the COLLATE clause inside the CAST
function, which resulted in a syntax error.
.. change::
:tags: usecase, mssql
:tickets: 7340
Added support for creation and reflection of COLUMNSTORE
indexes in MSSQL dialect. Can be specified on indexes
specifying ``mssql_columnstore=True``.
.. change::
:tags: usecase, postgresql
:tickets: 10004
Added multi-host support for the asyncpg dialect. General improvements and
error checking added to the PostgreSQL URL routines for the "multihost" use
case added as well. Pull request courtesy Ilia Dmitriev.
.. seealso::
:ref:`asyncpg_multihost`
.. changelog::
```
### 2.0.17
```
:released: June 23, 2023
.. change::
:tags: usecase, postgresql
:tickets: 9965
The pg8000 dialect now supports RANGE and MULTIRANGE datatypes, using the
existing RANGE API described at :ref:`postgresql_ranges`. Range and
multirange types are supported in the pg8000 driver from version 1.29.8.
Pull request courtesy Tony Locke.
.. change::
:tags: bug, orm, regression
:tickets: 9870
Fixed regression in the 2.0 series where a query that used
:func:`.undefer_group` with :func:`_orm.selectinload` or
:func:`_orm.subqueryload` would raise an ``AttributeError``. Pull request
courtesy of Matthew Martin.
.. change::
:tags: bug, orm
:tickets: 9957
Fixed issue in ORM Annotated Declarative which prevented a
:class:`_orm.declared_attr` from being used on a mixin which did not return
a :class:`.Mapped` datatype, and instead returned a supplemental ORM
datatype such as :class:`.AssociationProxy`. The Declarative runtime would
erroneously try to interpret this annotation as needing to be
:class:`.Mapped` and raise an error.
.. change::
:tags: bug, orm, typing
:tickets: 9957
Fixed typing issue where using the :class:`.AssociationProxy` return type
from a :class:`_orm.declared_attr` function was disallowed.
.. change::
:tags: bug, orm, regression
:tickets: 9936
Fixed regression introduced in 2.0.16 by :ticket:`9879` where passing a
callable to the :paramref:`_orm.mapped_column.default` parameter of
:class:`_orm.mapped_column` while also setting ``init=False`` would
interpret this value as a Dataclass default value which would be assigned
directly to new instances of the object directly, bypassing the default
generator taking place as the :paramref:`_schema.Column.default`
value generator on the underlying :class:`_schema.Column`. This condition
is now detected so that the previous behavior is maintained, however a
deprecation warning for this ambiguous use is emitted; to populate the
default generator for a :class:`_schema.Column`, the
:paramref:`_orm.mapped_column.insert_default` parameter should be used,
which disambiguates from the :paramref:`_orm.mapped_column.default`
parameter whose name is fixed as per pep-681.
.. change::
:tags: bug, orm
:tickets: 9973
Additional hardening and documentation for the ORM :class:`_orm.Session`
"state change" system, which detects concurrent use of
:class:`_orm.Session` and :class:`_asyncio.AsyncSession` objects; an
additional check is added within the process to acquire connections from
the underlying engine, which is a critical section with regards to internal
connection management.
.. change::
:tags: bug, orm
:tickets: 10006
Fixed issue in ORM loader strategy logic which further allows for long
chains of :func:`_orm.contains_eager` loader options across complex
inheriting polymorphic / aliased / of_type() relationship chains to take
proper effect in queries.
.. change::
:tags: bug, orm, declarative
:tickets: 3532
A warning is emitted when an ORM :func:`_orm.relationship` and other
:class:`.MapperProperty` objects are assigned to two different class
attributes at once; only one of the attributes will be mapped. A warning
for this condition was already in place for :class:`_schema.Column` and
:class:`_orm.mapped_column` objects.
.. change::
:tags: bug, orm
:tickets: 9963
Fixed issue in support for the :class:`.Enum` datatype in the
:paramref:`_orm.registry.type_annotation_map` first added as part of
:ticket:`8859` where using a custom :class:`.Enum` with fixed configuration
in the map would fail to transfer the :paramref:`.Enum.name` parameter,
which among other issues would prevent PostgreSQL enums from working if the
enum values were passed as individual values. Logic has been updated so
that "name" is transferred over, but also that the default :class:`.Enum`
which is against the plain Python `enum.Enum` class or other "empty" enum
won't set a hardcoded name of ``"enum"`` either.
.. change::
:tags: bug, typing
:tickets: 9985
Fixed typing issue which prevented :class:`_orm.WriteOnlyMapped` and
:class:`_orm.DynamicMapped` attributes from being used fully within ORM
queries.
.. changelog::
```
### 2.0.16
```
:released: June 10, 2023
.. change::
:tags: usecase, postgresql, reflection
:tickets: 9838
Cast ``NAME`` columns to ``TEXT`` when using ``ARRAY_AGG`` in PostgreSQL
reflection. This seems to improve compatibility with some PostgreSQL
derivatives that may not support aggregations on the ``NAME`` type.
.. change::
:tags: bug, orm
:tickets: 9862
Fixed issue where :class:`.DeclarativeBaseNoMeta` declarative base class
would not function with non-mapped mixins or abstract classes, raising an
``AttributeError`` instead.
.. change::
:tags: usecase, orm
:tickets: 9828
Improved :meth:`.DeferredReflection.prepare` to accept arbitrary ``**kw``
arguments that are passed to :meth:`_schema.MetaData.reflect`, allowing use
cases such as reflection of views as well as dialect-specific arguments to
be passed. Additionally, modernized the
:paramref:`.DeferredReflection.prepare.bind` argument so that either an
:class:`.Engine` or :class:`.Connection` are accepted as the "bind"
argument.
.. change::
:tags: usecase, asyncio
:tickets: 8215
Added new :paramref:`_asyncio.create_async_engine.async_creator` parameter
to :func:`.create_async_engine`, which accomplishes the same purpose as the
:paramref:`.create_engine.creator` parameter of :func:`.create_engine`.
This is a no-argument callable that provides a new asyncio connection,
using the asyncio database driver directly. The
:func:`.create_async_engine` function will wrap the driver-level connection
in the appropriate structures. Pull request courtesy of Jack Wotherspoon.
.. change::
:tags: bug, orm, regression
:tickets: 9820
Fixed regression in the 2.0 series where the default value of
:paramref:`_orm.validates.include_backrefs` got changed to ``False`` for
the :func:`_orm.validates` function. This default is now restored to
``True``.
.. change::
:tags: bug, orm
:tickets: 9917
Fixed bug in new feature which allows a WHERE clause to be used in
conjunction with :ref:`orm_queryguide_bulk_update`, added in version 2.0.11
as part of :ticket:`9583`, where sending dictionaries that did not include
the primary key values for each row would run through the bulk process and
include "pk=NULL" for the rows, silently failing. An exception is now
raised if primary key values for bulk UPDATE are not supplied.
.. change::
:tags: bug, postgresql
:tickets: 9836
Use proper precedence on PostgreSQL specific operators, such as ``>``.
Previously the precedence was wrong, leading to wrong parenthesis when
rendering against and ``ANY`` or ``ALL`` construct.
.. change::
:tags: bug, orm, dataclasses
:tickets: 9879
Fixed an issue where generating dataclasses fields that specified a
``default`` value and set ``init=False`` would not work.
The dataclasses behavior in this case is to set the default
value on the class, that's not compatible with the descriptors used
by SQLAlchemy. To support this case the default is transformed to
a ``default_factory`` when generating the dataclass.
.. change::
:tags: bug, orm
:tickets: 9841
A deprecation warning is emitted whenever a property is added to a
:class:`_orm.Mapper` where an ORM mapped property were already configured,
or an attribute is already present on the class. Previously, there was a
non-deprecation warning for this case that did not emit consistently. The
logic for this warning has been improved so that it detects end-user
replacement of attribute while not having false positives for internal
Declarative and other cases where replacement of descriptors with new ones
is expected.
.. change::
:tags: bug, postgresql
:tickets: 9907
Fixed issue where the :paramref:`.ColumnOperators.like.escape` and similar
parameters did not allow an empty string as an argument that would be
passed through as the "escape" character; this is a supported syntax by
PostgreSQL. Pull requset courtesy Martin Caslavsky.
.. change::
:tags: bug, orm
:tickets: 9869
Improved the argument chacking on the
:paramref:`_orm.registry.map_imperatively.local_table` parameter of the
:meth:`_orm.registry.map_imperatively` method, ensuring only a
:class:`.Table` or other :class:`.FromClause` is passed, and not an
existing mapped class, which would lead to undefined behavior as the object
were further interpreted for a new mapping.
.. change::
:tags: usecase, postgresql
:tickets: 9041
Unified the custom PostgreSQL operator definitions, since they are
shared among multiple different data types.
.. change::
:tags: platform, usecase
Compatibility improvements allowing the complete test suite to pass
on Python 3.12.0b1.
.. change::
:tags: bug, orm
:tickets: 9913
The :attr:`_orm.InstanceState.unloaded_expirable` attribute is a synonym
for :attr:`_orm.InstanceState.unloaded`, and is now deprecated; this
attribute was always implementation-specific and should not have been
public.
.. change::
:tags: usecase, postgresql
:tickets: 8240
Added support for PostgreSQL 10 ``NULLS NOT DISTINCT`` feature of
unique indexes and unique constraint using the dialect option
``postgresql_nulls_not_distinct``.
Updated the reflection logic to also correctly take this option
into account.
Pull request courtesy of Pavel Siarchenia.
.. changelog::
```
### 2.0.15
```
:released: May 19, 2023
.. change::
:tags: bug, orm
:tickets: 9805
As more projects are using new-style "2.0" ORM querying, it's becoming
apparent that the conditional nature of "autoflush", being based on whether
or not the given statement refers to ORM entities, is becoming more of a
key behavior. Up until now, the "ORM" flag for a statement has been loosely
based around whether or not the statement returns rows that correspond to
ORM entities or columns; the original purpose of the "ORM" flag was to
enable ORM-entity fetching rules which apply post-processing to Core result
sets as well as ORM loader strategies to the statement. For statements
that don't build on rows that contain ORM entities, the "ORM" flag was
considered to be mostly unnecessary.
It still may be the case that "autoflush" would be better taking effect for
*all* usage of :meth:`_orm.Session.execute` and related methods, even for
purely Core SQL constructs. However, this still could impact legacy cases
where this is not expected and may be more of a 2.1 thing. For now however,
the rules for the "ORM-flag" have been opened up so that a statement that
includes ORM entities or attributes anywhere within, including in the WHERE
/ ORDER BY / GROUP BY clause alone, within scalar subqueries, etc. will
enable this flag. This will cause "autoflush" to occur for such statements
and also be visible via the :attr:`_orm.ORMExecuteState.is_orm_statement`
event-level attribute.
.. change::
:tags: bug, postgresql, regression
:tickets: 9808
Repaired the base :class:`.Uuid` datatype for the PostgreSQL dialect to
make full use of the PG-specific ``UUID`` dialect-specific datatype when
"native_uuid" is selected, so that PG driver behaviors are included. This
issue became apparent due to the insertmanyvalues improvement made as part
of :ticket:`9618`, where in a similar manner as that of :ticket:`9739`, the
asyncpg driver is very sensitive to datatype casts being present or not,
and the PostgreSQL driver-specific native ``UUID`` datatype must be invoked
when this generic type is used so that these casts take place.
.. changelog::
```
### 2.0.14
```
:released: May 18, 2023
.. change::
:tags: bug, sql
:tickets: 9772
Fixed issue in :func:`_sql.values` construct where an internal compilation
error would occur if the construct were used inside of a scalar subquery.
.. change::
:tags: usecase, sql
:tickets: 9752
Generalized the MSSQL :func:`_sql.try_cast` function into the
``sqlalchemy.`` import namespace so that it may be implemented by third
party dialects as well. Within SQLAlchemy, the :func:`_sql.try_cast`
function remains a SQL Server-only construct that will raise
:class:`.CompileError` if used with backends that don't support it.
:func:`_sql.try_cast` implements a CAST where un-castable conversions are
returned as NULL, instead of raising an error. Theoretically, the construct
could be implemented by third party dialects for Google BigQuery, DuckDB,
and Snowflake, and possibly others.
Pull request courtesy Nick Crews.
.. change::
:tags: bug, tests, pypy
:tickets: 9789
Fixed test that relied on the ``sys.getsizeof()`` function to not run on
pypy, where this function appears to have different behavior than it does
on cpython.
.. change::
:tags: bug, orm
:tickets: 9777
Modified the ``JoinedLoader`` implementation to use a simpler approach in
one particular area where it previously used a cached structure that would
be shared among threads. The rationale is to avoid a potential race
condition which is suspected of being the cause of a particular crash
that's been reported multiple times. The cached structure in question is
still ultimately "cached" via the compiled SQL cache, so a performance
degradation is not anticipated.
.. change::
:tags: bug, orm, regression
:tickets: 9767
Fixed regression where use of :func:`_dml.update` or :func:`_dml.delete`
within a :class:`_sql.CTE` construct, then used in a :func:`_sql.select`,
would raise a :class:`.CompileError` as a result of ORM related rules for
performing ORM-level update/delete statements.
.. change::
:tags: bug, orm
:tickets: 9766
Fixed issue in new ORM Annotated Declarative where using a
:class:`_schema.ForeignKey` (or other column-level constraint) inside of
:func:`_orm.mapped_column` which is then copied out to models via pep-593
``Annotated`` would apply duplicates of each constraint to the
:class:`_schema.Column` as produced in the target :class:`_schema.Table`,
leading to incorrect CREATE TABLE DDL as well as migration directives under
Alembic.
.. change::
:tags: bug, orm
:tickets: 9779
Fixed issue where using additional relationship criteria with the
:func:`_orm.joinedload` loader option, where the additional criteria itself
containe
pyup-bot
commented
9 months ago
This PR updates SQLAlchemy from 1.2.12 to 2.0.25.
Changelog
### 2.0.25 ``` :released: January 2, 2024 .. change:: :tags: oracle, asyncio :tickets: 10679 Added support for :ref:`oracledb` in asyncio mode, using the newly released version of the ``oracledb`` DBAPI that includes asyncio support. For the 2.0 series, this is a preview release, where the current implementation does not yet have include support for :meth:`_asyncio.AsyncConnection.stream`. Improved support is planned for the 2.1 release of SQLAlchemy. .. change:: :tags: bug, orm :tickets: 10800 Fixed issue where when making use of the :paramref:`_orm.relationship.post_update` feature at the same time as using a mapper version_id_col could lead to a situation where the second UPDATE statement emitted by the post-update feature would fail to make use of the correct version identifier, assuming an UPDATE was already emitted in that flush which had already bumped the version counter. .. change:: :tags: bug, typing :tickets: 10801, 10818 Fixed regressions caused by typing added to the ``sqlalchemy.sql.functions`` module in version 2.0.24, as part of :ticket:`6810`: * Further enhancements to pep-484 typing to allow SQL functions from :attr:`_sql.func` derived elements to work more effectively with ORM-mapped attributes (:ticket:`10801`) * Fixed the argument types passed to functions so that literal expressions like strings and ints are again interpreted correctly (:ticket:`10818`) .. change:: :tags: usecase, orm :tickets: 10807 Added preliminary support for Python 3.12 pep-695 type alias structures, when resolving custom type maps for ORM Annotated Declarative mappings. .. change:: :tags: bug, orm :tickets: 10815 Fixed issue where ORM Annotated Declarative would mis-interpret the left hand side of a relationship without any collection specified as uselist=True if the left type were given as a class and not a string, without using future-style annotations. .. change:: :tags: bug, sql :tickets: 10817 Improved compilation of :func:`_sql.any_` / :func:`_sql.all_` in the context of a negation of boolean comparison, will now render ``NOT (expr)`` rather than reversing the equality operator to not equals, allowing finer-grained control of negations for these non-typical operators. .. changelog:: ``` ### 2.0.24 ``` :released: December 28, 2023 .. change:: :tags: bug, orm :tickets: 10597 Fixed issue where use of :func:`_orm.foreign` annotation on a non-initialized :func:`_orm.mapped_column` construct would produce an expression without a type, which was then not updated at initialization time of the actual column, leading to issues such as relationships not determining ``use_get`` appropriately. .. change:: :tags: bug, schema :tickets: 10654 Fixed issue where error reporting for unexpected schema item when creating objects like :class:`_schema.Table` would incorrectly handle an argument that was itself passed as a tuple, leading to a formatting error. The error message has been modernized to use f-strings. .. change:: :tags: bug, engine :tickets: 10662 Fixed URL-encoding of the username and password components of :class:`.engine.URL` objects when converting them to string using the :meth:`_engine.URL.render_as_string` method, by using Python standard library ``urllib.parse.quote`` while allowing for plus signs and spaces to remain unchanged as supported by SQLAlchemy's non-standard URL parsing, rather than the legacy home-grown routine from many years ago. Pull request courtesy of Xavier NUNN. .. change:: :tags: bug, orm :tickets: 10668 Improved the error message produced when the unit of work process sets the value of a primary key column to NULL due to a related object with a dependency rule on that column being deleted, to include not just the destination object and column name but also the source column from which the NULL value is originating. Pull request courtesy Jan Vollmer. .. change:: :tags: bug, postgresql :tickets: 10717 Adjusted the asyncpg dialect such that when the ``terminate()`` method is used to discard an invalidated connection, the dialect will first attempt to gracefully close the connection using ``.close()`` with a timeout, if the operation is proceeding within an async event loop context only. This allows the asyncpg driver to attend to finalizing a ``TimeoutError`` including being able to close a long-running query server side, which otherwise can keep running after the program has exited. .. change:: :tags: bug, orm :tickets: 10732 Modified the ``__init_subclass__()`` method used by :class:`_orm.MappedAsDataclass`, :class:`_orm.DeclarativeBase` and :class:`_orm.DeclarativeBaseNoMeta` to accept arbitrary ``**kw`` and to propagate them to the ``super()`` call, allowing greater flexibility in arranging custom superclasses and mixins which make use of ``__init_subclass__()`` keyword arguments. Pull request courtesy Michael Oliver. .. change:: :tags: bug, tests :tickets: 10747 Improvements to the test suite to further harden its ability to run when Python ``greenlet`` is not installed. There is now a tox target that includes the token "nogreenlet" that will run the suite with greenlet not installed (note that it still temporarily installs greenlet as part of the tox config, however). .. change:: :tags: bug, sql :tickets: 10753 Fixed issue in stringify for SQL elements, where a specific dialect is not passed, where a dialect-specific element such as the PostgreSQL "on conflict do update" construct is encountered and then fails to provide for a stringify dialect with the appropriate state to render the construct, leading to internal errors. .. change:: :tags: bug, sql Fixed issue where stringifying or compiling a :class:`.CTE` that was against a DML construct such as an :func:`_sql.insert` construct would fail to stringify, due to a mis-detection that the statement overall is an INSERT, leading to internal errors. .. change:: :tags: bug, orm :tickets: 10776 Ensured the use case of :class:`.Bundle` objects used in the ``returning()`` portion of ORM-enabled INSERT, UPDATE and DELETE statements is tested and works fully. This was never explicitly implemented or tested previously and did not work correctly in the 1.4 series; in the 2.0 series, ORM UPDATE/DELETE with WHERE criteria was missing an implementation method preventing :class:`.Bundle` objects from working. .. change:: :tags: bug, orm :tickets: 10784 Fixed 2.0 regression in :class:`.MutableList` where a routine that detects sequences would not correctly filter out string or bytes instances, making it impossible to assign a string value to a specific index (while non-sequence values would work fine). .. change:: :tags: change, asyncio The ``async_fallback`` dialect argument is now deprecated, and will be removed in SQLAlchemy 2.1. This flag has not been used for SQLAlchemy's test suite for some time. asyncio dialects can still run in a synchronous style by running code within a greenlet using :func:`_util.greenlet_spawn`. .. change:: :tags: bug, typing :tickets: 6810 Completed pep-484 typing for the ``sqlalchemy.sql.functions`` module. :func:`_sql.select` constructs made against ``func`` elements should now have filled-in return types. .. changelog:: ``` ### 2.0.23 ``` :released: November 2, 2023 .. change:: :tags: bug, oracle :tickets: 10509 Fixed issue in :class:`.Interval` datatype where the Oracle implementation was not being used for DDL generation, leading to the ``day_precision`` and ``second_precision`` parameters to be ignored, despite being supported by this dialect. Pull request courtesy Indivar. .. change:: :tags: bug, orm :tickets: 10516 Fixed issue where the ``__allow_unmapped__`` directive failed to allow for legacy :class:`.Column` / :func:`.deferred` mappings that nonetheless had annotations such as ``Any`` or a specific type without ``Mapped[]`` as their type, without errors related to locating the attribute name. .. change:: :tags: bug, mariadb :tickets: 10056 Adjusted the MySQL / MariaDB dialects to default a generated column to NULL when using MariaDB, if :paramref:`_schema.Column.nullable` was not specified with an explicit ``True`` or ``False`` value, as MariaDB does not support the "NOT NULL" phrase with a generated column. Pull request courtesy Indivar. .. change:: :tags: bug, mariadb, regression :tickets: 10505 Established a workaround for what seems to be an intrinsic issue across MySQL/MariaDB drivers where a RETURNING result for DELETE DML which returns no rows using SQLAlchemy's "empty IN" criteria fails to provide a cursor.description, which then yields result that returns no rows, leading to regressions for the ORM that in the 2.0 series uses RETURNING for bulk DELETE statements for the "synchronize session" feature. To resolve, when the specific case of "no description when RETURNING was given" is detected, an "empty result" with a correct cursor description is generated and used in place of the non-working cursor. .. change:: :tags: bug, orm :tickets: 10570 Fixed caching bug where using the :func:`_orm.with_expression` construct in conjunction with loader options :func:`_orm.selectinload`, :func:`_orm.lazyload` would fail to substitute bound parameter values correctly on subsequent caching runs. .. change:: :tags: usecase, mssql :tickets: 6521 Added support for the ``aioodbc`` driver implemented for SQL Server, which builds on top of the pyodbc and general aio* dialect architecture. .. seealso:: :ref:`mssql_aioodbc` - in the SQL Server dialect documentation. .. change:: :tags: bug, sql :tickets: 10535 Added compiler-level None/NULL handling for the "literal processors" of all datatypes that include literal processing, that is, where a value is rendered inline within a SQL statement rather than as a bound parameter, for all those types that do not feature explicit "null value" handling. Previously this behavior was undefined and inconsistent. .. change:: :tags: usecase, orm :tickets: 10575 Implemented the :paramref:`_orm.Session.bulk_insert_mappings.render_nulls` parameter for new style bulk ORM inserts, allowing ``render_nulls=True`` as an execution option. This allows for bulk ORM inserts with a mixture of ``None`` values in the parameter dictionaries to use a single batch of rows for a given set of dicationary keys, rather than breaking up into batches that omit the NULL columns from each INSERT. .. seealso:: :ref:`orm_queryguide_insert_null_params` .. change:: :tags: bug, postgresql :tickets: 10479 Fixed 2.0 regression caused by :ticket:`7744` where chains of expressions involving PostgreSQL JSON operators combined with other operators such as string concatenation would lose correct parenthesization, due to an implementation detail specific to the PostgreSQL dialect. .. change:: :tags: bug, postgresql :tickets: 10532 Fixed SQL handling for "insertmanyvalues" when using the :class:`.postgresql.BIT` datatype with the asyncpg backend. The :class:`.postgresql.BIT` on asyncpg apparently requires the use of an asyncpg-specific ``BitString`` type which is currently exposed when using this DBAPI, making it incompatible with other PostgreSQL DBAPIs that all work with plain bitstrings here. A future fix in version 2.1 will normalize this datatype across all PG backends. Pull request courtesy Sören Oldag. .. change:: :tags: usecase, sql :tickets: 9737 Implemented "literal value processing" for the :class:`.Interval` datatype for both the PostgreSQL and Oracle dialects, allowing literal rendering of interval values. Pull request courtesy Indivar Mishra. .. change:: :tags: bug, oracle :tickets: 10470 Fixed issue where the cx_Oracle dialect claimed to support a lower cx_Oracle version (7.x) than was actually supported in practice within the 2.0 series of SQLAlchemy. The dialect imports symbols that are only in cx_Oracle 8 or higher, so runtime dialect checks as well as setup.cfg requirements have been updated to reflect this compatibility. .. change:: :tags: sql Removed unused placeholder method :meth:`.TypeEngine.compare_against_backend` This method was used by very old versions of Alembic. See https://github.com/sqlalchemy/alembic/issues/1293 for details. .. change:: :tags: bug, orm :tickets: 10472 Fixed bug in ORM annotated declarative where using a ``ClassVar`` that nonetheless referred in some way to an ORM mapped class name would fail to be interpreted as a ``ClassVar`` that's not mapped. .. change:: :tags: bug, asyncio :tickets: 10421 Fixed bug with method :meth:`_asyncio.AsyncSession.close_all` that was not working correctly. Also added function :func:`_asyncio.close_all_sessions` that's the equivalent of :func:`_orm.close_all_sessions`. Pull request courtesy of Bryan不可思议. .. changelog:: ``` ### 2.0.22 ``` :released: October 12, 2023 .. change:: :tags: bug, orm :tickets: 10369, 10046 Fixed a wide range of :func:`_orm.mapped_column` parameters that were not being transferred when using the :func:`_orm.mapped_column` object inside of a pep-593 ``Annotated`` object, including :paramref:`_orm.mapped_column.sort_order`, :paramref:`_orm.mapped_column.deferred`, :paramref:`_orm.mapped_column.autoincrement`, :paramref:`_orm.mapped_column.system`, :paramref:`_orm.mapped_column.info` etc. Additionally, it remains not supported to have dataclass arguments, such as :paramref:`_orm.mapped_column.kw_only`, :paramref:`_orm.mapped_column.default_factory` etc. indicated within the :func:`_orm.mapped_column` received by ``Annotated``, as this is not supported with pep-681 Dataclass Transforms. A warning is now emitted when these parameters are used within ``Annotated`` in this way (and they continue to be ignored). .. change:: :tags: bug, orm :tickets: 10459 Fixed issue where calling :meth:`_engine.Result.unique` with a new-style :func:`.select` query in the ORM, where one or more columns yields values that are of "unknown hashability", typically when using JSON functions like ``func.json_build_object()`` without providing a type, would fail internally when the returned values were not actually hashable. The behavior is repaired to test the objects as they are received for hashability in this case, raising an informative error message if not. Note that for values of "known unhashability", such as when the :class:`_types.JSON` or :class:`_types.ARRAY` types are used directly, an informative error message was already raised. The "hashabiltiy testing" fix here is applied to legacy :class:`.Query` as well, however in the legacy case, :meth:`_engine.Result.unique` is used for nearly all queries, so no new warning is emitted here; the legacy behavior of falling back to using ``id()`` in this case is maintained, with the improvement that an unknown type that turns out to be hashable will now be uniqufied, whereas previously it would not. .. change:: :tags: bug, orm :tickets: 10453 Fixed regression in recently revised "insertmanyvalues" feature (likely issue :ticket:`9618`) where the ORM would inadvertently attempt to interpret a non-RETURNING result as one with RETURNING, in the case where the ``implicit_returning=False`` parameter were applied to the mapped :class:`.Table`, indicating that "insertmanyvalues" cannot be used if the primary key values are not provided. .. change:: :tags: bug, engine Fixed issue within some dialects where the dialect could incorrectly return an empty result set for an INSERT statement that does not actually return rows at all, due to artfacts from pre- or post-fetching the primary key of the row or rows still being present. Affected dialects included asyncpg, all mssql dialects. .. change:: :tags: bug, typing :tickets: 10451 Fixed typing issue where the argument list passed to :class:`.Values` was too-restrictively tied to ``List`` rather than ``Sequence``. Pull request courtesy Iuri de Silvio. .. change:: :tags: bug, orm :tickets: 10365 Fixed bug where ORM :func:`_orm.with_loader_criteria` would not apply itself to a :meth:`_sql.Select.join` where the ON clause were given as a plain SQL comparison, rather than as a relationship target or similar. .. change:: :tags: bug, sql :tickets: 10408 Fixed issue where referring to a FROM entry in the SET clause of an UPDATE statement would not include it in the FROM clause of the UPDATE statement, if that entry were nowhere else in the statement; this occurs currently for CTEs that were added using :meth:`.Update.add_cte` to provide the desired CTE at the top of the statement. .. change:: :tags: bug, mariadb :tickets: 10396 Modified the mariadb-connector driver to pre-load the ``cursor.rowcount`` value for all queries, to suit tools such as Pandas that hardcode to calling :attr:`.Result.rowcount` in this way. SQLAlchemy normally pre-loads ``cursor.rowcount`` only for UPDATE/DELETE statements and otherwise passes through to the DBAPI where it can return -1 if no value is available. However, mariadb-connector does not support invoking ``cursor.rowcount`` after the cursor itself is closed, raising an error instead. Generic test support has been added to ensure all backends support the allowing :attr:`.Result.rowcount` to succceed (that is, returning an integer value with -1 for "not available") after the result is closed. .. change:: :tags: bug, mariadb Additional fixes for the mariadb-connector dialect to support UUID data values in the result in INSERT..RETURNING statements. .. change:: :tags: bug, mssql :tickets: 10458 Fixed bug where the rule that prevents ORDER BY from emitting within subqueries on SQL Server was not being disabled in the case where the :meth:`.select.fetch` method were used to limit rows in conjunction with WITH TIES or PERCENT, preventing valid subqueries with TOP / ORDER BY from being used. .. change:: :tags: bug, sql :tickets: 10443 Fixed 2.0 regression where the :class:`.DDL` construct would no longer ``__repr__()`` due to the removed ``on`` attribute not being accommodated. Pull request courtesy Iuri de Silvio. .. change:: :tags: orm, usecase :tickets: 10202 Added method :meth:`_orm.Session.get_one` that behaves like :meth:`_orm.Session.get` but raises an exception instead of returning ``None`` if no instance was found with the provided primary key. Pull request courtesy of Carlos Sousa. .. change:: :tags: asyncio, bug Fixed the :paramref:`_asyncio.AsyncSession.get.execution_options` parameter which was not being propagated to the underlying :class:`_orm.Session` and was instead being ignored. .. change:: :tags: bug, orm :tickets: 10412 Fixed issue where :class:`.Mapped` symbols like :class:`.WriteOnlyMapped` and :class:`.DynamicMapped` could not be correctly resolved when referenced as an element of a sub-module in the given annotation, assuming string-based or "future annotations" style annotations. .. change:: :tags: bug, engine :tickets: 10414 Fixed issue where under some garbage collection / exception scenarios the connection pool's cleanup routine would raise an error due to an unexpected set of state, which can be reproduced under specific conditions. .. change:: :tags: bug, typing Updates to the codebase to support Mypy 1.6.0. .. change:: :tags: usecase, orm :tickets: 7787 Added an option to permanently close sessions. Set to ``False`` the new parameter :paramref:`_orm.Session.close_resets_only` will prevent a :class:`_orm.Session` from performing any other operation after :meth:`_orm.Session.close` has been called. Added new method :meth:`_orm.Session.reset` that will reset a :class:`_orm.Session` to its initial state. This is an alias of :meth:`_orm.Session.close`, unless :paramref:`_orm.Session.close_resets_only` is set to ``False``. .. change:: :tags: orm, bug :tickets: 10385 Fixed issue with ``__allow_unmapped__`` declarative option where types that were declared using collection types such as ``list[SomeClass]`` vs. the typing construct ``List[SomeClass]`` would fail to be recognized correctly. Pull request courtesy Pascal Corpet. .. changelog:: ``` ### 2.0.21 ``` :released: September 18, 2023 .. change:: :tags: bug, sql :tickets: 9610 Adjusted the operator precedence for the string concatenation operator to be equal to that of string matching operators, such as :meth:`.ColumnElement.like`, :meth:`.ColumnElement.regexp_match`, :meth:`.ColumnElement.match`, etc., as well as plain ``==`` which has the same precedence as string comparison operators, so that parenthesis will be applied to a string concatenation expression that follows a string match operator. This provides for backends such as PostgreSQL where the "regexp match" operator is apparently of higher precedence than the string concatenation operator. .. change:: :tags: bug, sql :tickets: 10342 Qualified the use of ``hashlib.md5()`` within the DDL compiler, which is used to generate deterministic four-character suffixes for long index and constraint names in DDL statements, to include the Python 3.9+ ``usedforsecurity=False`` parameter so that Python interpreters built for restricted environments such as FIPS do not consider this call to be related to security concerns. .. change:: :tags: bug, postgresql :tickets: 10226 Fixed regression which appeared in 2.0 due to :ticket:`8491` where the revised "ping" used for PostgreSQL dialects when the :paramref:`_sa.create_engine.pool_pre_ping` parameter is in use would interfere with the use of asyncpg with PGBouncer "transaction" mode, as the multiple PostgreSQL commands emitted by asnycpg could be broken out among multiple connections leading to errors, due to the lack of any transaction around this newly revised "ping". The ping is now invoked within a transaction, in the same way that is implicit with all other backends that are based on the pep-249 DBAPI; this guarantees that the series of PG commands sent by asyncpg for this command are invoked on the same backend connection without it jumping to a different connection mid-command. The transaction is not used if the asyncpg dialect is used in "AUTOCOMMIT" mode, which remains incompatible with pgbouncer transaction mode. .. change:: :tags: bug, orm :tickets: 10279 Adjusted the ORM's interpretation of the "target" entity used within :class:`.Update` and :class:`.Delete` to not interfere with the target "from" object passed to the statement, such as when passing an ORM-mapped :class:`_orm.aliased` construct that should be maintained within a phrase like "UPDATE FROM". Cases like ORM session synchonize using "SELECT" statements such as with MySQL/ MariaDB will still have issues with UPDATE/DELETE of this form so it's best to disable synchonize_session when using DML statements of this type. .. change:: :tags: bug, orm :tickets: 10348 Added new capability to the :func:`_orm.selectin_polymorphic` loader option which allows other loader options to be bundled as siblings, referring to one of its subclasses, within the sub-options of parent loader option. Previously, this pattern was only supported if the :func:`_orm.selectin_polymorphic` were at the top level of the options for the query. See new documentation section for example. As part of this change, improved the behavior of the :meth:`_orm.Load.selectin_polymorphic` method / loader strategy so that the subclass load does not load most already-loaded columns from the parent table, when the option is used against a class that is already being relationship-loaded. Previously, the logic to load only the subclass columns worked only for a top level class load. .. seealso:: :ref:`polymorphic_selectin_as_loader_option_target_plus_opts` .. change:: :tags: bug, typing :tickets: 10264, 9284 Fixed regression introduced in 2.0.20 via :ticket:`9600` fix which attempted to add more formal typing to :paramref:`_schema.MetaData.naming_convention`. This change prevented basic naming convention dictionaries from passing typing and has been adjusted so that a plain dictionary of strings for keys as well as dictionaries that use constraint types as keys or a mix of both, are again accepted. As part of this change, lesser used forms of the naming convention dictionary are also typed, including that it currently allows for ``Constraint`` type objects as keys as well. .. change:: :tags: usecase, typing :tickets: 10288 Made the contained type for :class:`.Mapped` covariant; this is to allow greater flexibility for end-user typing scenarios, such as the use of protocols to represent particular mapped class structures that are passed to other functions. As part of this change, the contained type was also made covariant for dependent and related types such as :class:`_orm.base.SQLORMOperations`, :class:`_orm.WriteOnlyMapped`, and :class:`_sql.SQLColumnExpression`. Pull request courtesy Roméo Després. .. change:: :tags: bug, engine :tickets: 10275 Fixed a series of reflection issues affecting the PostgreSQL, MySQL/MariaDB, and SQLite dialects when reflecting foreign key constraints where the target column contained parenthesis in one or both of the table name or column name. .. change:: :tags: bug, sql :tickets: 10280 The :class:`.Values` construct will now automatically create a proxy (i.e. a copy) of a :class:`_sql.column` if the column were already associated with an existing FROM clause. This allows that an expression like ``values_obj.c.colname`` will produce the correct FROM clause even in the case that ``colname`` was passed as a :class:`_sql.column` that was already used with a previous :class:`.Values` or other table construct. Originally this was considered to be a candidate for an error condition, however it's likely this pattern is already in widespread use so it's now added to support. .. change:: :tags: bug, setup :tickets: 10321 Fixed very old issue where the full extent of SQLAlchemy modules, including ``sqlalchemy.testing.fixtures``, could not be imported outside of a pytest run. This suits inspection utilities such as ``pkgutil`` that attempt to import all installed modules in all packages. .. change:: :tags: usecase, sql :tickets: 10269 Adjusted the :class:`_types.Enum` datatype to accept an argument of ``None`` for the :paramref:`_types.Enum.length` parameter, resulting in a VARCHAR or other textual type with no length in the resulting DDL. This allows for new elements of any length to be added to the type after it exists in the schema. Pull request courtesy Eugene Toder. .. change:: :tags: bug, typing :tickets: 9878 Fixed the type annotation for ``__class_getitem__()`` as applied to the ``Visitable`` class at the base of expression constructs to accept ``Any`` for a key, rather than ``str``, which helps with some IDEs such as PyCharm when attempting to write typing annotations for SQL constructs which include generic selectors. Pull request courtesy Jordan Macdonald. .. change:: :tags: bug, typing :tickets: 10353 Repaired the core "SQL element" class ``SQLCoreOperations`` to support the ``__hash__()`` method from a typing perspective, as objects like :class:`.Column` and ORM :class:`.InstrumentedAttribute` are hashable and are used as dictionary keys in the public API for the :class:`_dml.Update` and :class:`_dml.Insert` constructs. Previously, type checkers were not aware the root SQL element was hashable. .. change:: :tags: bug, typing :tickets: 10337 Fixed typing issue with :meth:`_sql.Existing.select_from` that prevented its use with ORM classes. .. change:: :tags: usecase, sql :tickets: 9873 Added new generic SQL function :class:`_functions.aggregate_strings`, which accepts a SQL expression and a decimeter, concatenating strings on multiple rows into a single aggregate value. The function is compiled on a per-backend basis, into functions such as ``group_concat(),`` ``string_agg()``, or ``LISTAGG()``. Pull request courtesy Joshua Morris. .. change:: :tags: typing, bug :tickets: 10131 Update type annotations for ORM loading options, restricting them to accept only `"*"` instead of any string for string arguments. Pull request courtesy Janek Nouvertné. .. changelog:: ``` ### 2.0.20 ``` :released: August 15, 2023 .. change:: :tags: bug, orm :tickets: 10169 Fixed issue where the ORM's generation of a SELECT from a joined inheritance model with same-named columns in superclass and subclass would somehow not send the correct list of column names to the :class:`.CTE` construct, when the RECURSIVE column list were generated. .. change:: :tags: bug, typing :tickets: 9185 Typing improvements: * :class:`.CursorResult` is returned for some forms of :meth:`_orm.Session.execute` where DML without RETURNING is used * fixed type for :paramref:`_orm.Query.with_for_update.of` parameter within :meth:`_orm.Query.with_for_update` * improvements to ``_DMLColumnArgument`` type used by some DML methods to pass column expressions * Add overload to :func:`_sql.literal` so that it is inferred that the return type is ``BindParameter[NullType]`` where :paramref:`_sql.literal.type_` param is None * Add overloads to :meth:`_sql.ColumnElement.op` so that the inferred type when :paramref:`_sql.ColumnElement.op.return_type` is not provided is ``Callable[[Any], BinaryExpression[Any]]`` * Add missing overload to :meth:`_sql.ColumnElement.__add__` Pull request courtesy Mehdi Gmira. .. change:: :tags: usecase, orm :tickets: 10192 Implemented the "RETURNING '*'" use case for ORM enabled DML statements. This will render in as many cases as possible and return the unfiltered result set, however is not supported for multi-parameter "ORM bulk INSERT" statements that have specific column rendering requirements. .. change:: :tags: bug, typing :tickets: 10182 Fixed issue in :class:`_orm.Session` and :class:`_asyncio.AsyncSession` methods such as :meth:`_orm.Session.connection` where the :paramref:`_orm.Session.connection.execution_options` parameter were hardcoded to an internal type that is not user-facing. .. change:: :tags: orm, bug :tickets: 10231 Fixed fairly major issue where execution options passed to :meth:`_orm.Session.execute`, as well as execution options local to the ORM executed statement itself, would not be propagated along to eager loaders such as that of :func:`_orm.selectinload`, :func:`_orm.immediateload`, and :meth:`_orm.subqueryload`, making it impossible to do things such as disabling the cache for a single statement or using ``schema_translate_map`` for a single statement, as well as the use of user-custom execution options. A change has been made where **all** user-facing execution options present for :meth:`_orm.Session.execute` will be propagated along to additional loaders. As part of this change, the warning for "excessively deep" eager loaders leading to caching being disabled can be silenced on a per-statement basis by sending ``execution_options={"compiled_cache": None}`` to :meth:`_orm.Session.execute`, which will disable caching for the full series of statements within that scope. .. change:: :tags: usecase, asyncio :tickets: 9698 Added new methods :meth:`_asyncio.AsyncConnection.aclose` as a synonym for :meth:`_asyncio.AsyncConnection.close` and :meth:`_asyncio.AsyncSession.aclose` as a synonym for :meth:`_asyncio.AsyncSession.close` to the :class:`_asyncio.AsyncConnection` and :class:`_asyncio.AsyncSession` objects, to provide compatibility with Python standard library ``contextlib.aclosing`` construct. Pull request courtesy Grigoriev Semyon. .. change:: :tags: bug, orm :tickets: 10124 Fixed issue where internal cloning used by the ORM for expressions like :meth:`_orm.relationship.Comparator.any` to produce correlated EXISTS constructs would interfere with the "cartesian product warning" feature of the SQL compiler, leading the SQL compiler to warn when all elements of the statement were correctly joined. .. change:: :tags: orm, bug :tickets: 10139 Fixed issue where the ``lazy="immediateload"`` loader strategy would place an internal loading token into the ORM mapped attribute under circumstances where the load should not occur, such as in a recursive self-referential load. As part of this change, the ``lazy="immediateload"`` strategy now honors the :paramref:`_orm.relationship.join_depth` parameter for self-referential eager loads in the same way as that of other eager loaders, where leaving it unset or set at zero will lead to a self-referential immediateload not occurring, setting it to a value of one or greater will immediateload up until that given depth. .. change:: :tags: bug, orm :tickets: 10175 Fixed issue where dictionary-based collections such as :func:`_orm.attribute_keyed_dict` did not fully pickle/unpickle correctly, leading to issues when attempting to mutate such a collection after unpickling. .. change:: :tags: bug, orm :tickets: 10125 Fixed issue where chaining :func:`_orm.load_only` or other wildcard use of :func:`_orm.defer` from another eager loader using a :func:`_orm.aliased` against a joined inheritance subclass would fail to take effect for columns local to the superclass. .. change:: :tags: bug, orm :tickets: 10167 Fixed issue where an ORM-enabled :func:`_sql.select` construct would not render any CTEs added only via the :meth:`_sql.Select.add_cte` method that were not otherwise referenced in the statement. .. change:: :tags: bug, examples The dogpile_caching examples have been updated for 2.0 style queries. Within the "caching query" logic itself there is one conditional added to differentiate between ``Query`` and ``select()`` when performing an invalidation operation. .. change:: :tags: typing, usecase :tickets: 10173 Added new typing only utility functions :func:`.Nullable` and :func:`.NotNullable` to type a column or ORM class as, respectively, nullable or not nullable. These function are no-op at runtime, returning the input unchanged. .. change:: :tags: bug, engine :tickets: 10147 Fixed critical issue where setting :paramref:`_sa.create_engine.isolation_level` to ``AUTOCOMMIT`` (as opposed to using the :meth:`_engine.Engine.execution_options` method) would fail to restore "autocommit" to a pooled connection if an alternate isolation level were temporarily selected using :paramref:`_engine.Connection.execution_options.isolation_level`. .. changelog:: ``` ### 2.0.19 ``` :released: July 15, 2023 .. change:: :tags: bug, orm :tickets: 10089 Fixed issue where setting a relationship collection directly, where an object in the new collection were already present, would not trigger a cascade event for that object, leading to it not being added to the :class:`_orm.Session` if it were not already present. This is similar in nature to :ticket:`6471` and is a more apparent issue due to the removal of ``cascade_backrefs`` in the 2.0 series. The :meth:`_orm.AttributeEvents.append_wo_mutation` event added as part of :ticket:`6471` is now also emitted for existing members of a collection that are present in a bulk set of that same collection. .. change:: :tags: bug, engine :tickets: 10093 Renamed :attr:`_result.Row.t` and :meth:`_result.Row.tuple` to :attr:`_result.Row._t` and :meth:`_result.Row._tuple`; this is to suit the policy that all methods and pre-defined attributes on :class:`.Row` should be in the style of Python standard library ``namedtuple`` where all fixed names have a leading underscore, to avoid name conflicts with existing column names. The previous method/attribute is now deprecated and will emit a deprecation warning. .. change:: :tags: bug, postgresql :tickets: 10069 Fixed regression caused by improvements to PostgreSQL URL parsing in :ticket:`10004` where "host" query string arguments that had colons in them, to support various third party proxy servers and/or dialects, would not parse correctly as these were evaluted as ``host:port`` combinations. Parsing has been updated to consider a colon as indicating a ``host:port`` value only if the hostname contains only alphanumeric characters with dots or dashes only (e.g. no slashes), followed by exactly one colon followed by an all-integer token of zero or more integers. In all other cases, the full string is taken as a host. .. change:: :tags: bug, engine :tickets: 10079 Added detection for non-string, non-:class:`_engine.URL` objects to the :func:`_engine.make_url` function, allowing ``ArgumentError`` to be thrown immediately, rather than causing failures later on. Special logic ensures that mock forms of :class:`_engine.URL` are allowed through. Pull request courtesy Grigoriev Semyon. .. change:: :tags: bug, orm :tickets: 10090 Fixed issue where objects that were associated with an unloaded collection via backref, but were not merged into the :class:`_orm.Session` due to the removal of ``cascade_backrefs`` in the 2.0 series, would not emit a warning that these objects were not being included in a flush, even though they were pending members of the collection; in other such cases, a warning is emitted when a collection being flushed contains non-attached objects which will be essentially discarded. The addition of the warning for backref-pending collection members establishes greater consistency with collections that may be present or non-present and possibly flushed or not flushed at different times based on different relationship loading strategies. .. change:: :tags: bug, postgresql :tickets: 10096 Fixed issue where comparisons to the :class:`_postgresql.CITEXT` datatype would cast the right side to ``VARCHAR``, leading to the right side not being interpreted as a ``CITEXT`` datatype, for the asyncpg, psycopg3 and pg80000 dialects. This led to the :class:`_postgresql.CITEXT` type being essentially unusable for practical use; this is now fixed and the test suite has been corrected to properly assert that expressions are rendered correctly. .. change:: :tags: bug, orm, regression :tickets: 10098 Fixed additional regression caused by :ticket:`9805` where more aggressive propagation of the "ORM" flag on statements could lead to an internal attribute error when embedding an ORM :class:`.Query` construct that nonetheless contained no ORM entities within a Core SQL statement, in this case ORM-enabled UPDATE and DELETE statements. .. changelog:: ``` ### 2.0.18 ``` :released: July 5, 2023 .. change:: :tags: usecase, typing :tickets: 10054 Improved typing when using standalone operator functions from ``sqlalchemy.sql.operators`` such as ``sqlalchemy.sql.operators.eq``. .. change:: :tags: usecase, mariadb, reflection :tickets: 10028 Allowed reflecting :class:`_types.UUID` columns from MariaDB. This allows Alembic to properly detect the type of such columns in existing MariaDB databases. .. change:: :tags: bug, postgresql :tickets: 9945 Added new parameter ``native_inet_types=False`` to all PostgreSQL dialects, which indicates converters used by the DBAPI to convert rows from PostgreSQL :class:`.INET` and :class:`.CIDR` columns into Python ``ipaddress`` datatypes should be disabled, returning strings instead. This allows code written to work with strings for these datatypes to be migrated to asyncpg, psycopg, or pg8000 without code changes other than adding this parameter to the :func:`_sa.create_engine` or :func:`_asyncio.create_async_engine` function call. .. seealso:: :ref:`postgresql_network_datatypes` .. change:: :tags: usecase, extensions :tickets: 10013 Added new option to :func:`.association_proxy` :paramref:`.association_proxy.create_on_none_assignment`; when an association proxy which refers to a scalar relationship is assigned the value ``None``, and the referenced object is not present, a new object is created via the creator. This was apparently an undefined behavior in the 1.2 series that was silently removed. .. change:: :tags: bug, typing :tickets: 10061 Fixed some of the typing within the :func:`_orm.aliased` construct to correctly accept a :class:`.Table` object that's been aliased with :meth:`.Table.alias`, as well as general support for :class:`.FromClause` objects to be passed as the "selectable" argument, since this is all supported. .. change:: :tags: bug, engine :tickets: 10025 Adjusted the :paramref:`_sa.create_engine.schema_translate_map` feature such that **all** schema names in the statement are now tokenized, regardless of whether or not a specific name is in the immediate schema translate map given, and to fallback to substituting the original name when the key is not in the actual schema translate map at execution time. These two changes allow for repeated use of a compiled object with schema schema_translate_maps that include or dont include various keys on each run, allowing cached SQL constructs to continue to function at runtime when schema translate maps with different sets of keys are used each time. In addition, added detection of schema_translate_map dictionaries which gain or lose a ``None`` key across calls for the same statement, which affects compilation of the statement and is not compatible with caching; an exception is raised for these scenarios. .. change:: :tags: bug, mssql, sql :tickets: 9932 Fixed issue where performing :class:`.Cast` to a string type with an explicit collation would render the COLLATE clause inside the CAST function, which resulted in a syntax error. .. change:: :tags: usecase, mssql :tickets: 7340 Added support for creation and reflection of COLUMNSTORE indexes in MSSQL dialect. Can be specified on indexes specifying ``mssql_columnstore=True``. .. change:: :tags: usecase, postgresql :tickets: 10004 Added multi-host support for the asyncpg dialect. General improvements and error checking added to the PostgreSQL URL routines for the "multihost" use case added as well. Pull request courtesy Ilia Dmitriev. .. seealso:: :ref:`asyncpg_multihost` .. changelog:: ``` ### 2.0.17 ``` :released: June 23, 2023 .. change:: :tags: usecase, postgresql :tickets: 9965 The pg8000 dialect now supports RANGE and MULTIRANGE datatypes, using the existing RANGE API described at :ref:`postgresql_ranges`. Range and multirange types are supported in the pg8000 driver from version 1.29.8. Pull request courtesy Tony Locke. .. change:: :tags: bug, orm, regression :tickets: 9870 Fixed regression in the 2.0 series where a query that used :func:`.undefer_group` with :func:`_orm.selectinload` or :func:`_orm.subqueryload` would raise an ``AttributeError``. Pull request courtesy of Matthew Martin. .. change:: :tags: bug, orm :tickets: 9957 Fixed issue in ORM Annotated Declarative which prevented a :class:`_orm.declared_attr` from being used on a mixin which did not return a :class:`.Mapped` datatype, and instead returned a supplemental ORM datatype such as :class:`.AssociationProxy`. The Declarative runtime would erroneously try to interpret this annotation as needing to be :class:`.Mapped` and raise an error. .. change:: :tags: bug, orm, typing :tickets: 9957 Fixed typing issue where using the :class:`.AssociationProxy` return type from a :class:`_orm.declared_attr` function was disallowed. .. change:: :tags: bug, orm, regression :tickets: 9936 Fixed regression introduced in 2.0.16 by :ticket:`9879` where passing a callable to the :paramref:`_orm.mapped_column.default` parameter of :class:`_orm.mapped_column` while also setting ``init=False`` would interpret this value as a Dataclass default value which would be assigned directly to new instances of the object directly, bypassing the default generator taking place as the :paramref:`_schema.Column.default` value generator on the underlying :class:`_schema.Column`. This condition is now detected so that the previous behavior is maintained, however a deprecation warning for this ambiguous use is emitted; to populate the default generator for a :class:`_schema.Column`, the :paramref:`_orm.mapped_column.insert_default` parameter should be used, which disambiguates from the :paramref:`_orm.mapped_column.default` parameter whose name is fixed as per pep-681. .. change:: :tags: bug, orm :tickets: 9973 Additional hardening and documentation for the ORM :class:`_orm.Session` "state change" system, which detects concurrent use of :class:`_orm.Session` and :class:`_asyncio.AsyncSession` objects; an additional check is added within the process to acquire connections from the underlying engine, which is a critical section with regards to internal connection management. .. change:: :tags: bug, orm :tickets: 10006 Fixed issue in ORM loader strategy logic which further allows for long chains of :func:`_orm.contains_eager` loader options across complex inheriting polymorphic / aliased / of_type() relationship chains to take proper effect in queries. .. change:: :tags: bug, orm, declarative :tickets: 3532 A warning is emitted when an ORM :func:`_orm.relationship` and other :class:`.MapperProperty` objects are assigned to two different class attributes at once; only one of the attributes will be mapped. A warning for this condition was already in place for :class:`_schema.Column` and :class:`_orm.mapped_column` objects. .. change:: :tags: bug, orm :tickets: 9963 Fixed issue in support for the :class:`.Enum` datatype in the :paramref:`_orm.registry.type_annotation_map` first added as part of :ticket:`8859` where using a custom :class:`.Enum` with fixed configuration in the map would fail to transfer the :paramref:`.Enum.name` parameter, which among other issues would prevent PostgreSQL enums from working if the enum values were passed as individual values. Logic has been updated so that "name" is transferred over, but also that the default :class:`.Enum` which is against the plain Python `enum.Enum` class or other "empty" enum won't set a hardcoded name of ``"enum"`` either. .. change:: :tags: bug, typing :tickets: 9985 Fixed typing issue which prevented :class:`_orm.WriteOnlyMapped` and :class:`_orm.DynamicMapped` attributes from being used fully within ORM queries. .. changelog:: ``` ### 2.0.16 ``` :released: June 10, 2023 .. change:: :tags: usecase, postgresql, reflection :tickets: 9838 Cast ``NAME`` columns to ``TEXT`` when using ``ARRAY_AGG`` in PostgreSQL reflection. This seems to improve compatibility with some PostgreSQL derivatives that may not support aggregations on the ``NAME`` type. .. change:: :tags: bug, orm :tickets: 9862 Fixed issue where :class:`.DeclarativeBaseNoMeta` declarative base class would not function with non-mapped mixins or abstract classes, raising an ``AttributeError`` instead. .. change:: :tags: usecase, orm :tickets: 9828 Improved :meth:`.DeferredReflection.prepare` to accept arbitrary ``**kw`` arguments that are passed to :meth:`_schema.MetaData.reflect`, allowing use cases such as reflection of views as well as dialect-specific arguments to be passed. Additionally, modernized the :paramref:`.DeferredReflection.prepare.bind` argument so that either an :class:`.Engine` or :class:`.Connection` are accepted as the "bind" argument. .. change:: :tags: usecase, asyncio :tickets: 8215 Added new :paramref:`_asyncio.create_async_engine.async_creator` parameter to :func:`.create_async_engine`, which accomplishes the same purpose as the :paramref:`.create_engine.creator` parameter of :func:`.create_engine`. This is a no-argument callable that provides a new asyncio connection, using the asyncio database driver directly. The :func:`.create_async_engine` function will wrap the driver-level connection in the appropriate structures. Pull request courtesy of Jack Wotherspoon. .. change:: :tags: bug, orm, regression :tickets: 9820 Fixed regression in the 2.0 series where the default value of :paramref:`_orm.validates.include_backrefs` got changed to ``False`` for the :func:`_orm.validates` function. This default is now restored to ``True``. .. change:: :tags: bug, orm :tickets: 9917 Fixed bug in new feature which allows a WHERE clause to be used in conjunction with :ref:`orm_queryguide_bulk_update`, added in version 2.0.11 as part of :ticket:`9583`, where sending dictionaries that did not include the primary key values for each row would run through the bulk process and include "pk=NULL" for the rows, silently failing. An exception is now raised if primary key values for bulk UPDATE are not supplied. .. change:: :tags: bug, postgresql :tickets: 9836 Use proper precedence on PostgreSQL specific operators, such as ``>``. Previously the precedence was wrong, leading to wrong parenthesis when rendering against and ``ANY`` or ``ALL`` construct. .. change:: :tags: bug, orm, dataclasses :tickets: 9879 Fixed an issue where generating dataclasses fields that specified a ``default`` value and set ``init=False`` would not work. The dataclasses behavior in this case is to set the default value on the class, that's not compatible with the descriptors used by SQLAlchemy. To support this case the default is transformed to a ``default_factory`` when generating the dataclass. .. change:: :tags: bug, orm :tickets: 9841 A deprecation warning is emitted whenever a property is added to a :class:`_orm.Mapper` where an ORM mapped property were already configured, or an attribute is already present on the class. Previously, there was a non-deprecation warning for this case that did not emit consistently. The logic for this warning has been improved so that it detects end-user replacement of attribute while not having false positives for internal Declarative and other cases where replacement of descriptors with new ones is expected. .. change:: :tags: bug, postgresql :tickets: 9907 Fixed issue where the :paramref:`.ColumnOperators.like.escape` and similar parameters did not allow an empty string as an argument that would be passed through as the "escape" character; this is a supported syntax by PostgreSQL. Pull requset courtesy Martin Caslavsky. .. change:: :tags: bug, orm :tickets: 9869 Improved the argument chacking on the :paramref:`_orm.registry.map_imperatively.local_table` parameter of the :meth:`_orm.registry.map_imperatively` method, ensuring only a :class:`.Table` or other :class:`.FromClause` is passed, and not an existing mapped class, which would lead to undefined behavior as the object were further interpreted for a new mapping. .. change:: :tags: usecase, postgresql :tickets: 9041 Unified the custom PostgreSQL operator definitions, since they are shared among multiple different data types. .. change:: :tags: platform, usecase Compatibility improvements allowing the complete test suite to pass on Python 3.12.0b1. .. change:: :tags: bug, orm :tickets: 9913 The :attr:`_orm.InstanceState.unloaded_expirable` attribute is a synonym for :attr:`_orm.InstanceState.unloaded`, and is now deprecated; this attribute was always implementation-specific and should not have been public. .. change:: :tags: usecase, postgresql :tickets: 8240 Added support for PostgreSQL 10 ``NULLS NOT DISTINCT`` feature of unique indexes and unique constraint using the dialect option ``postgresql_nulls_not_distinct``. Updated the reflection logic to also correctly take this option into account. Pull request courtesy of Pavel Siarchenia. .. changelog:: ``` ### 2.0.15 ``` :released: May 19, 2023 .. change:: :tags: bug, orm :tickets: 9805 As more projects are using new-style "2.0" ORM querying, it's becoming apparent that the conditional nature of "autoflush", being based on whether or not the given statement refers to ORM entities, is becoming more of a key behavior. Up until now, the "ORM" flag for a statement has been loosely based around whether or not the statement returns rows that correspond to ORM entities or columns; the original purpose of the "ORM" flag was to enable ORM-entity fetching rules which apply post-processing to Core result sets as well as ORM loader strategies to the statement. For statements that don't build on rows that contain ORM entities, the "ORM" flag was considered to be mostly unnecessary. It still may be the case that "autoflush" would be better taking effect for *all* usage of :meth:`_orm.Session.execute` and related methods, even for purely Core SQL constructs. However, this still could impact legacy cases where this is not expected and may be more of a 2.1 thing. For now however, the rules for the "ORM-flag" have been opened up so that a statement that includes ORM entities or attributes anywhere within, including in the WHERE / ORDER BY / GROUP BY clause alone, within scalar subqueries, etc. will enable this flag. This will cause "autoflush" to occur for such statements and also be visible via the :attr:`_orm.ORMExecuteState.is_orm_statement` event-level attribute. .. change:: :tags: bug, postgresql, regression :tickets: 9808 Repaired the base :class:`.Uuid` datatype for the PostgreSQL dialect to make full use of the PG-specific ``UUID`` dialect-specific datatype when "native_uuid" is selected, so that PG driver behaviors are included. This issue became apparent due to the insertmanyvalues improvement made as part of :ticket:`9618`, where in a similar manner as that of :ticket:`9739`, the asyncpg driver is very sensitive to datatype casts being present or not, and the PostgreSQL driver-specific native ``UUID`` datatype must be invoked when this generic type is used so that these casts take place. .. changelog:: ``` ### 2.0.14 ``` :released: May 18, 2023 .. change:: :tags: bug, sql :tickets: 9772 Fixed issue in :func:`_sql.values` construct where an internal compilation error would occur if the construct were used inside of a scalar subquery. .. change:: :tags: usecase, sql :tickets: 9752 Generalized the MSSQL :func:`_sql.try_cast` function into the ``sqlalchemy.`` import namespace so that it may be implemented by third party dialects as well. Within SQLAlchemy, the :func:`_sql.try_cast` function remains a SQL Server-only construct that will raise :class:`.CompileError` if used with backends that don't support it. :func:`_sql.try_cast` implements a CAST where un-castable conversions are returned as NULL, instead of raising an error. Theoretically, the construct could be implemented by third party dialects for Google BigQuery, DuckDB, and Snowflake, and possibly others. Pull request courtesy Nick Crews. .. change:: :tags: bug, tests, pypy :tickets: 9789 Fixed test that relied on the ``sys.getsizeof()`` function to not run on pypy, where this function appears to have different behavior than it does on cpython. .. change:: :tags: bug, orm :tickets: 9777 Modified the ``JoinedLoader`` implementation to use a simpler approach in one particular area where it previously used a cached structure that would be shared among threads. The rationale is to avoid a potential race condition which is suspected of being the cause of a particular crash that's been reported multiple times. The cached structure in question is still ultimately "cached" via the compiled SQL cache, so a performance degradation is not anticipated. .. change:: :tags: bug, orm, regression :tickets: 9767 Fixed regression where use of :func:`_dml.update` or :func:`_dml.delete` within a :class:`_sql.CTE` construct, then used in a :func:`_sql.select`, would raise a :class:`.CompileError` as a result of ORM related rules for performing ORM-level update/delete statements. .. change:: :tags: bug, orm :tickets: 9766 Fixed issue in new ORM Annotated Declarative where using a :class:`_schema.ForeignKey` (or other column-level constraint) inside of :func:`_orm.mapped_column` which is then copied out to models via pep-593 ``Annotated`` would apply duplicates of each constraint to the :class:`_schema.Column` as produced in the target :class:`_schema.Table`, leading to incorrect CREATE TABLE DDL as well as migration directives under Alembic. .. change:: :tags: bug, orm :tickets: 9779 Fixed issue where using additional relationship criteria with the :func:`_orm.joinedload` loader option, where the additional criteria itself containe