Sqlalchemy

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

:released: April 30, 2023

.. change::
:tags: bug, mysql, mariadb
:tickets: 9722

Fixed issues regarding reflection of comments for :class:`_schema.Table`
and :class:`_schema.Column` objects, where the comments contained control
characters such as newlines. Additional testing support for these
characters as well as extended Unicode characters in table and column
comments (the latter of which aren't supported by MySQL/MariaDB) added to
testing overall.

.. changelog::

:released: April 26, 2023

.. change::
:tags: bug, engine, regression
:tickets: 9682

Fixed regression which prevented the :attr:`_engine.URL.normalized_query`
attribute of :class:`_engine.URL` from functioning.

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

Fixed critical regression caused by :ticket:`9618`, which modified the
architecture of the :term:`insertmanyvalues` feature for 2.0.10, which
caused floating point values to lose all decimal places when being inserted
using the insertmanyvalues feature with either the psycopg2 or psycopg
drivers.


.. change::
:tags: bug, mssql

Implemented the :class:`_sqltypes.Double` type for SQL Server, where it
will render ``DOUBLE PRECISION`` at DDL time. This is implemented using
a new MSSQL datatype :class:`_mssql.DOUBLE_PRECISION` which also may
be used directly.


.. change::
:tags: bug, oracle

Fixed issue in Oracle dialects where ``Decimal`` returning types such as
:class:`_sqltypes.Numeric` would return floating point values, rather than
``Decimal`` objects, when these columns were used in the
:meth:`_dml.Insert.returning` clause to return INSERTed values.

.. change::
:tags: bug, orm
:tickets: 9583, 9595

Fixed 2.0 regression where use of :func:`_sql.bindparam()` inside of
:meth:`_dml.Insert.values` would fail to be interpreted correctly when
executing the :class:`_dml.Insert` statement using the ORM
:class:`_orm.Session`, due to the new
:ref:`ORM-enabled insert feature <orm_queryguide_bulk_insert>` not
implementing this use case.

.. change::
:tags: usecase, orm
:tickets: 9583, 9595

The :ref:`ORM bulk INSERT and UPDATE <orm_expression_update_delete>`
features now add these capabilities:

* The requirement that extra parameters aren't passed when using ORM
INSERT using the "orm" dml_strategy setting is lifted.
* The requirement that additional WHERE criteria is not passed when using
ORM UPDATE using the "bulk" dml_strategy setting is lifted. Note that
in this case, the check for expected row count is turned off.

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

Added support for slice access with :class:`.ColumnCollection`, e.g.
``table.c[0:5]``, ``subquery.c[:-1]`` etc. Slice access returns a sub
:class:`.ColumnCollection` in the same way as passing a tuple of keys. This
is a natural continuation of the key-tuple access added for :ticket:`8285`,
where it appears to be an oversight that the slice access use case was
omitted.

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

Improved typing of :class:`_engine.RowMapping` to indicate that it
support also :class:`_schema.Column` as index objects, not only
string names. Pull request courtesy Andy Freeland.

.. change::
:tags: engine, performance
:tickets: 9678, 9680

A series of performance enhancements to :class:`_engine.Row`:

* ``__getattr__`` performance of the row's "named tuple" interface has
been improved; within this change, the :class:`_engine.Row`
implementation has been streamlined, removing constructs and logic
that were specific to the 1.4 and prior series of SQLAlchemy.
As part of this change, the serialization format of :class:`_engine.Row`
has been modified slightly, however rows which were pickled with previous
SQLAlchemy 2.0 releases will be recognized within the new format.
Pull request courtesy J. Nick Koston.

* Improved row processing performance for "binary" datatypes by making the
"bytes" handler conditional on a per driver basis. As a result, the
"bytes" result handler has been removed for nearly all drivers other than
psycopg2, all of which in modern forms support returning Python "bytes"
directly. Pull request courtesy J. Nick Koston.

* Additional refactorings inside of :class:`_engine.Row` to improve
performance by Federico Caselli.




.. changelog::