Sqlalchemy

: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::

: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::

: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::

: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::

: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
contained correlated subqueries that referred to the joined entities and
therefore also required "adaption" to aliased entities, would be excluded
from this adaption, producing the wrong ON clause for the joinedload.

.. change::
:tags: bug, postgresql
:tickets: 9773

Fixed apparently very old issue where the
:paramref:`_postgresql.ENUM.create_type` parameter, when set to its
non-default of ``False``, would not be propagated when the
:class:`_schema.Column` which it's a part of were copied, as is common when
using ORM Declarative mixins.

.. changelog::

:released: May 10, 2023

.. change::
:tags: usecase, asyncio
:tickets: 9731

Added a new helper mixin :class:`_asyncio.AsyncAttrs` that seeks to improve
the use of lazy-loader and other expired or deferred ORM attributes with
asyncio, providing a simple attribute accessor that provides an ``await``
interface to any ORM attribute, whether or not it needs to emit SQL.

.. seealso::

:class:`_asyncio.AsyncAttrs`

.. change::
:tags: bug, orm
:tickets: 9717

Fixed issue where ORM Annotated Declarative would not resolve forward
references correctly in all cases; in particular, when using
``from __future__ import annotations`` in combination with Pydantic
dataclasses.

.. change::
:tags: typing, sql
:tickets: 9656

Added type :data:`_sql.ColumnExpressionArgument` as a public-facing type
that indicates column-oriented arguments which are passed to SQLAlchemy
constructs, such as :meth:`_sql.Select.where`, :func:`_sql.and_` and
others. This may be used to add typing to end-user functions which call
these methods.

.. change::
:tags: bug, orm
:tickets: 9746

Fixed issue in new :ref:`orm_queryguide_upsert_returning` feature where the
``populate_existing`` execution option was not being propagated to the
loading option, preventing existing attributes from being refreshed
in-place.

.. change::
:tags: bug, sql

Fixed the base class for dialect-specific float/double types; Oracle
:class:`_oracle.BINARY_DOUBLE` now subclasses :class:`_sqltypes.Double`,
and internal types for :class:`_sqltypes.Float` for asyncpg and pg8000 now
correctly subclass :class:`_sqltypes.Float`.

.. change::
:tags: bug, ext
:tickets: 9676

Fixed issue in :class:`_mutable.Mutable` where event registration for ORM
mapped attributes would be called repeatedly for mapped inheritance
subclasses, leading to duplicate events being invoked in inheritance
hierarchies.

.. change::
:tags: bug, orm
:tickets: 9715

Fixed loader strategy pathing issues where eager loaders such as
:func:`_orm.joinedload` / :func:`_orm.selectinload` would fail to traverse
fully for many-levels deep following a load that had a
:func:`_orm.with_polymorphic` or similar construct as an interim member.

.. change::
:tags: usecase, sql
:tickets: 9721

Implemented the "cartesian product warning" for UPDATE and DELETE
statements, those which include multiple tables that are not correlated
together in some way.

.. change::
:tags: bug, sql

Fixed issue where :func:`_dml.update` construct that included multiple
tables and no VALUES clause would raise with an internal error. Current
behavior for :class:`_dml.Update` with no values is to generate a SQL
UPDATE statement with an empty "set" clause, so this has been made
consistent for this specific sub-case.

.. change::
:tags: oracle, reflection
:tickets: 9597

Added reflection support in the Oracle dialect to expression based indexes
and the ordering direction of index expressions.

.. change::
:tags: performance, schema
:tickets: 9597

Improved how table columns are added, avoiding unnecessary allocations,
significantly speeding up the creation of many table, like when reflecting
entire schemas.

.. change::
:tags: bug, typing
:tickets: 9762

Fixed typing for the :paramref:`_orm.Session.get.with_for_update` parameter
of :meth:`_orm.Session.get` and :meth:`_orm.Session.refresh` (as well as
corresponding methods on :class:`_asyncio.AsyncSession`) to accept boolean
``True`` and all other argument forms accepted by the parameter at runtime.

.. change::
:tags: bug, postgresql, regression
:tickets: 9739

Fixed another regression due to the "insertmanyvalues" change in 2.0.10 as
part of :ticket:`9618`, in a similar way as regression :ticket:`9701`, where
:class:`.LargeBinary` datatypes also need additional casts on when using the
asyncpg driver specifically in order to work with the new bulk INSERT
format.

.. change::
:tags: bug, orm
:tickets: 9630

Fixed issue in :func:`_orm.mapped_column` construct where the correct
warning for "column X named directly multiple times" would not be emitted
when ORM mapped attributes referred to the same :class:`_schema.Column`, if
the :func:`_orm.mapped_column` construct were involved, raising an internal
assertion instead.

.. change::
:tags: bug, asyncio

Fixed issue in semi-private ``await_only()`` and ``await_fallback()``
concurrency functions where the given awaitable would remain un-awaited if
the function threw a ``GreenletError``, which could cause "was not awaited"
warnings later on if the program continued. In this case, the given
awaitable is now cancelled before the exception is thrown.

.. changelog::