Sqlalchemy

:released: October 16, 2022

.. change::
:tags: bug, asyncio
:tickets: 8516

Improved implementation of ``asyncio.shield()`` used in context managers as
added in :ticket:`8145`, such that the "close" operation is enclosed within
an ``asyncio.Task`` which is then strongly referenced as the operation
proceeds. This is per Python documentation indicating that the task is
otherwise not strongly referenced.

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

The :paramref:`_orm.Session.execute.bind_arguments` dictionary is no longer
mutated when passed to :meth:`_orm.Session.execute` and similar; instead,
it's copied to an internal dictionary for state changes. Among other
things, this fixes and issue where the "clause" passed to the
:meth:`_orm.Session.get_bind` method would be incorrectly referring to the
:class:`_sql.Select` construct used for the "fetch" synchronization
strategy, when the actual query being emitted was a :class:`_dml.Delete` or
:class:`_dml.Update`. This would interfere with recipes for "routing
sessions".

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

A warning is emitted in ORM configurations when an explicit
:func:`_orm.remote` annotation is applied to columns that are local to the
immediate mapped class, when the referenced class does not include any of
the same table columns. Ideally this would raise an error at some point as
it's not correct from a mapping point of view.

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

A warning is emitted when attempting to configure a mapped class within an
inheritance hierarchy where the mapper is not given any polymorphic
identity, however there is a polymorphic discriminator column assigned.
Such classes should be abstract if they never intend to load directly.


.. change::
:tags: bug, mssql, regression
:tickets: 8525

Fixed yet another regression in SQL Server isolation level fetch (see
:ticket:`8231`, :ticket:`8475`), this time with "Microsoft Dynamics CRM
Database via Azure Active Directory", which apparently lacks the
``system_views`` view entirely. Error catching has been extended that under
no circumstances will this method ever fail, provided database connectivity
is present.

.. change::
:tags: orm, bug, regression
:tickets: 8569

Fixed regression for 1.4 in :func:`_orm.contains_eager` where the "wrap in
subquery" logic of :func:`_orm.joinedload` would be inadvertently triggered
for use of the :func:`_orm.contains_eager` function with similar statements
(e.g. those that use ``distinct()``, ``limit()`` or ``offset()``), which
would then lead to secondary issues with queries that used some
combinations of SQL label names and aliasing. This "wrapping" is not
appropriate for :func:`_orm.contains_eager` which has always had the
contract that the user-defined SQL statement is unmodified with the
exception of adding the appropriate columns to be fetched.

.. change::
:tags: bug, orm, regression
:tickets: 8507

Fixed regression where using ORM update() with synchronize_session='fetch'
would fail due to the use of evaluators that are now used to determine the
in-Python value for expressions in the SET clause when refreshing
objects; if the evaluators make use of math operators against non-numeric
values such as PostgreSQL JSONB, the non-evaluable condition would fail to
be detected correctly. The evaluator now limits the use of math mutation
operators to numeric types only, with the exception of "+" that continues
to work for strings as well. SQLAlchemy 2.0 may alter this further by
fetching the SET values completely rather than using evaluation.

.. change::
:tags: usecase, postgresql
:tickets: 8574

:class:`_postgresql.aggregate_order_by` now supports cache generation.

.. change::
:tags: bug, mysql
:tickets: 8588

Adjusted the regular expression used to match "CREATE VIEW" when
testing for views to work more flexibly, no longer requiring the
special keyword "ALGORITHM" in the middle, which was intended to be
optional but was not working correctly. The change allows view reflection
to work more completely on MySQL-compatible variants such as StarRocks.
Pull request courtesy John Bodley.

.. change::
:tags: bug, engine
:tickets: 8536

Fixed issue where mixing "*" with additional explicitly-named column
expressions within the columns clause of a :func:`_sql.select` construct
would cause result-column targeting to sometimes consider the label name or
other non-repeated names to be an ambiguous target.

.. changelog::

:released: September 6, 2022

.. change::
:tags: bug, sql
:tickets: 8441

Fixed issue where use of the :func:`_sql.table` construct, passing a string
for the :paramref:`_sql.table.schema` parameter, would fail to take the
"schema" string into account when producing a cache key, thus leading to
caching collisions if multiple, same-named :func:`_sql.table` constructs
with different schemas were used.


.. change::
:tags: bug, events, orm
:tickets: 8467

Fixed event listening issue where event listeners added to a superclass
would be lost if a subclass were created which then had its own listeners
associated. The practical example is that of the :class:`.sessionmaker`
class created after events have been associated with the
:class:`_orm.Session` class.

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

Hardened the cache key strategy for the :func:`_orm.aliased` and
:func:`_orm.with_polymorphic` constructs. While no issue involving actual
statements being cached can easily be demonstrated (if at all), these two
constructs were not including enough of what makes them unique in their
cache keys for caching on the aliased construct alone to be accurate.

.. change::
:tags: bug, orm, regression
:tickets: 8456

Fixed regression appearing in the 1.4 series where a joined-inheritance
query placed as a subquery within an enclosing query for that same entity
would fail to render the JOIN correctly for the inner query. The issue
manifested in two different ways prior and subsequent to version 1.4.18
(related issue :ticket:`6595`), in one case rendering JOIN twice, in the
other losing the JOIN entirely. To resolve, the conditions under which
"polymorphic loading" are applied have been scaled back to not be invoked
for simple joined inheritance queries.

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

Fixed issue in :mod:`sqlalchemy.ext.mutable` extension where collection
links to the parent object would be lost if the object were merged with
:meth:`.Session.merge` while also passing :paramref:`.Session.merge.load`
as False.

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

Fixed issue involving :func:`_orm.with_loader_criteria` where a closure
variable used as bound parameter value within the lambda would not carry
forward correctly into additional relationship loaders such as
:func:`_orm.selectinload` and :func:`_orm.lazyload` after the statement
were cached, using the stale originally-cached value instead.


.. change::
:tags: bug, mssql, regression
:tickets: 8475

Fixed regression caused by the fix for :ticket:`8231` released in 1.4.40
where connection would fail if the user did not have permission to query
the ``dm_exec_sessions`` or ``dm_pdw_nodes_exec_sessions`` system views
when trying to determine the current transaction isolation level.

.. change::
:tags: bug, asyncio
:tickets: 8419

Integrated support for asyncpg's ``terminate()`` method call for cases
where the connection pool is recycling a possibly timed-out connection,
where a connection is being garbage collected that wasn't gracefully
closed, as well as when the connection has been invalidated. This allows
asyncpg to abandon the connection without waiting for a response that may
incur long timeouts.

.. changelog::

:released: August 8, 2022

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

Fixed issue where referencing a CTE multiple times in conjunction with a
polymorphic SELECT could result in multiple "clones" of the same CTE being
constructed, which would then trigger these two CTEs as duplicates. To
resolve, the two CTEs are deep-compared when this occurs to ensure that
they are equivalent, then are treated as equivalent.


.. change::
:tags: bug, orm, declarative
:tickets: 8190

Fixed issue where a hierarchy of classes set up as an abstract or mixin
declarative classes could not declare standalone columns on a superclass
that would then be copied correctly to a :class:`_orm.declared_attr`
callable that wanted to make use of them on a descendant class.

.. change::
:tags: bug, types
:tickets: 7249

Fixed issue where :class:`.TypeDecorator` would not correctly proxy the
``__getitem__()`` operator when decorating the :class:`_types.ARRAY`
datatype, without explicit workarounds.

.. change::
:tags: bug, asyncio
:tickets: 8145

Added ``asyncio.shield()`` to the connection and session release process
specifically within the ``__aexit__()`` context manager exit, when using
:class:`.AsyncConnection` or :class:`.AsyncSession` as a context manager
that releases the object when the context manager is complete. This appears
to help with task cancellation when using alternate concurrency libraries
such as ``anyio``, ``uvloop`` that otherwise don't provide an async context
for the connection pool to release the connection properly during task
cancellation.



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

Fixed issue in psycopg2 dialect where the "multiple hosts" feature
implemented for :ticket:`4392`, where multiple ``host:port`` pairs could be
passed in the query string as
``?host=host1:port1&host=host2:port2&host=host3:port3`` was not implemented
correctly, as it did not propagate the "port" parameter appropriately.
Connections that didn't use a different "port" likely worked without issue,
and connections that had "port" for some of the entries may have
incorrectly passed on that hostname. The format is now corrected to pass
hosts/ports appropriately.

As part of this change, maintained support for another multihost style that
worked unintentionally, which is comma-separated
``?host=h1,h2,h3&port=p1,p2,p3``. This format is more consistent with
libpq's query-string format, whereas the previous format is inspired by a
different aspect of libpq's URI format but is not quite the same thing.

If the two styles are mixed together, an error is raised as this is
ambiguous.

.. change::
:tags: bug, sql
:tickets: 8253

Adjusted the SQL compilation for string containment functions
``.contains()``, ``.startswith()``, ``.endswith()`` to force the use of the
string concatenation operator, rather than relying upon the overload of the
addition operator, so that non-standard use of these operators with for
example bytestrings still produces string concatenation operators.


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

A :func:`_sql.select` construct that is passed a sole '*' argument for
``SELECT *``, either via string, :func:`_sql.text`, or
:func:`_sql.literal_column`, will be interpreted as a Core-level SQL
statement rather than as an ORM level statement. This is so that the ``*``,
when expanded to match any number of columns, will result in all columns
returned in the result. the ORM- level interpretation of
:func:`_sql.select` needs to know the names and types of all ORM columns up
front which can't be achieved when ``'*'`` is used.

If ``'*`` is used amongst other expressions simultaneously with an ORM
statement, an error is raised as this can't be interpreted correctly by the
ORM.

.. change::
:tags: bug, mssql
:tickets: 8210

Fixed issues that prevented the new usage patterns for using DML with ORM
objects presented at :ref:`orm_dml_returning_objects` from working
correctly with the SQL Server pyodbc dialect.


.. change::
:tags: bug, mssql
:tickets: 8231

Fixed issue where the SQL Server dialect's query for the current isolation
level would fail on Azure Synapse Analytics, due to the way in which this
database handles transaction rollbacks after an error has occurred. The
initial query has been modified to no longer rely upon catching an error
when attempting to detect the appropriate system view. Additionally, to
better support this database's very specific "rollback" behavior,
implemented new parameter ``ignore_no_transaction_on_rollback`` indicating
that a rollback should ignore Azure Synapse error 'No corresponding
transaction found. (111214)', which is raised if no transaction is present
in conflict with the Python DBAPI.

Initial patch and valuable debugging assistance courtesy of ww2406.

.. seealso::

:ref:`azure_synapse_ignore_no_transaction_on_rollback`

.. change::
:tags: bug, mypy
:tickets: 8196

Fixed a crash of the mypy plugin when using a lambda as a Column
default. Pull request courtesy of tchapi.


.. change::
:tags: usecase, engine

Implemented new :paramref:`_engine.Connection.execution_options.yield_per`
execution option for :class:`_engine.Connection` in Core, to mirror that of
the same :ref:`yield_per <orm_queryguide_yield_per>` option available in
the ORM. The option sets both the
:paramref:`_engine.Connection.execution_options.stream_results` option at
the same time as invoking :meth:`_engine.Result.yield_per`, to provide the
most common streaming result configuration which also mirrors that of the
ORM use case in its usage pattern.

.. seealso::

:ref:`engine_stream_results` - revised documentation


.. change::
:tags: bug, engine

Fixed bug in :class:`_engine.Result` where the usage of a buffered result
strategy would not be used if the dialect in use did not support an
explicit "server side cursor" setting, when using
:paramref:`_engine.Connection.execution_options.stream_results`. This is in
error as DBAPIs such as that of SQLite and Oracle already use a
non-buffered result fetching scheme, which still benefits from usage of
partial result fetching. The "buffered" strategy is now used in all
cases where :paramref:`_engine.Connection.execution_options.stream_results`
is set.


.. change::
:tags: bug, engine
:tickets: 8199

Added :meth:`.FilterResult.yield_per` so that result implementations
such as :class:`.MappingResult`, :class:`.ScalarResult` and
:class:`.AsyncResult` have access to this method.

.. changelog::

:released: June 24, 2022

.. change::
:tags: bug, orm, regression
:tickets: 8133

Fixed regression caused by :ticket:`8133` where the pickle format for
mutable attributes was changed, without a fallback to recognize the old
format, causing in-place upgrades of SQLAlchemy to no longer be able to
read pickled data from previous versions. A check plus a fallback for the
old format is now in place.

.. changelog::

:released: June 23, 2022

.. change::
:tags: bug, orm, regression
:tickets: 8162

Fixed regression caused by :ticket:`8064` where a particular check for
column correspondence was made too liberal, resulting in incorrect
rendering for some ORM subqueries such as those using
:meth:`.PropComparator.has` or :meth:`.PropComparator.any` in conjunction
with joined-inheritance queries that also use legacy aliasing features.

.. change::
:tags: bug, engine
:tickets: 8115

Repaired a deprecation warning class decorator that was preventing key
objects such as :class:`_engine.Connection` from having a proper
``__weakref__`` attribute, causing operations like Python standard library
``inspect.getmembers()`` to fail.


.. change::
:tags: bug, sql
:tickets: 8098

Fixed multiple observed race conditions related to :func:`.lambda_stmt`,
including an initial "dogpile" issue when a new Python code object is
initially analyzed among multiple simultaneous threads which created both a
performance issue as well as some internal corruption of state.
Additionally repaired observed race condition which could occur when
"cloning" an expression construct that is also in the process of being
compiled or otherwise accessed in a different thread due to memoized
attributes altering the ``__dict__`` while iterated, for Python versions
prior to 3.10; in particular the lambda SQL construct is sensitive to this
as it holds onto a single statement object persistently. The iteration has
been refined to use ``dict.copy()`` with or without an additional iteration
instead.

.. change::
:tags: bug, sql
:tickets: 8084

Enhanced the mechanism of :class:`.Cast` and other "wrapping"
column constructs to more fully preserve a wrapped :class:`.Label`
construct, including that the label name will be preserved in the
``.c`` collection of a :class:`.Subquery`. The label was already
able to render in the SQL correctly on the outside of the construct
which it was wrapped inside.

.. change::
:tags: bug, orm, sql
:tickets: 8091

Fixed an issue where :meth:`_sql.GenerativeSelect.fetch` would not
be applied when executing a statement using the ORM.

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

Fixed issue where a :func:`_orm.with_loader_criteria` option could not be
pickled, as is necessary when it is carried along for propagation to lazy
loaders in conjunction with a caching scheme. Currently, the only form that
is supported as picklable is to pass the "where criteria" as a fixed
module-level callable function that produces a SQL expression. An ad-hoc
"lambda" can't be pickled, and a SQL expression object is usually not fully
picklable directly.


.. change::
:tags: bug, schema
:tickets: 8100, 8101

Fixed bugs involving the :paramref:`.Table.include_columns` and the
:paramref:`.Table.resolve_fks` parameters on :class:`.Table`; these
little-used parameters were apparently not working for columns that refer
to foreign key constraints.

In the first case, not-included columns that refer to foreign keys would
still attempt to create a :class:`.ForeignKey` object, producing errors
when attempting to resolve the columns for the foreign key constraint
within reflection; foreign key constraints that refer to skipped columns
are now omitted from the table reflection process in the same way as
occurs for :class:`.Index` and :class:`.UniqueConstraint` objects with the
same conditions. No warning is produced however, as we likely want to
remove the include_columns warnings for all constraints in 2.0.

In the latter case, the production of table aliases or subqueries would
fail on an FK related table not found despite the presence of
``resolve_fks=False``; the logic has been repaired so that if a related
table is not found, the :class:`.ForeignKey` object is still proxied to the
aliased table or subquery (these :class:`.ForeignKey` objects are normally
used in the production of join conditions), but it is sent with a flag that
it's not resolvable. The aliased table / subquery will then work normally,
with the exception that it cannot be used to generate a join condition
automatically, as the foreign key information is missing. This was already
the behavior for such foreign key constraints produced using non-reflection
methods, such as joining :class:`.Table` objects from different
:class:`.MetaData` collections.

.. change::
:tags: bug, sql
:tickets: 8113

Adjusted the fix made for :ticket:`8056` which adjusted the escaping of
bound parameter names with special characters such that the escaped names
were translated after the SQL compilation step, which broke a published
recipe on the FAQ illustrating how to merge parameter names into the string
output of a compiled SQL string. The change restores the escaped names that
come from ``compiled.params`` and adds a conditional parameter to
:meth:`.SQLCompiler.construct_params` named ``escape_names`` that defaults
to ``True``, restoring the old behavior by default.

.. change::
:tags: bug, schema, mssql
:tickets: 8111

Fixed issue where :class:`.Table` objects that made use of IDENTITY columns
with a :class:`.Numeric` datatype would produce errors when attempting to
reconcile the "autoincrement" column, preventing construction of the
:class:`.Column` from using the :paramref:`.Column.autoincrement` parameter
as well as emitting errors when attempting to invoke an :class:`_dml.Insert`
construct.


.. change::
:tags: bug, extensions
:tickets: 8133

Fixed bug in :class:`.Mutable` where pickling and unpickling of an ORM
mapped instance would not correctly restore state for mappings that
contained multiple :class:`.Mutable`-enabled attributes.

.. changelog::

:released: May 31, 2022

.. change::
:tags: bug, mssql
:tickets: 8062

Fix issue where a password with a leading "{" would result in login failure.

.. change::
:tags: bug, sql, postgresql, sqlite
:tickets: 8014

Fixed bug where the PostgreSQL
:meth:`_postgresql.Insert.on_conflict_do_update` method and the SQLite
:meth:`_sqlite.Insert.on_conflict_do_update` method would both fail to
correctly accommodate a column with a separate ".key" when specifying the
column using its key name in the dictionary passed to
:paramref:`_postgresql.Insert.on_conflict_do_update.set_`, as well as if
the :attr:`_postgresql.Insert.excluded` collection were used as the
dictionary directly.

.. change::
:tags: bug, sql
:tickets: 8073

An informative error is raised for the use case where
:meth:`_dml.Insert.from_select` is being passed a "compound select" object such
as a UNION, yet the INSERT statement needs to append additional columns to
support Python-side or explicit SQL defaults from the table metadata. In
this case a subquery of the compound object should be passed.

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

Fixed issue where using a :func:`_orm.column_property` construct containing
a subquery against an already-mapped column attribute would not correctly
apply ORM-compilation behaviors to the subquery, including that the "IN"
expression added for a single-table inherits expression would fail to be
included.

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

Fixed issue where ORM results would apply incorrect key names to the
returned :class:`.Row` objects in the case where the set of columns to be
selected were changed, such as when using
:meth:`.Select.with_only_columns`.

.. change::
:tags: bug, mysql
:tickets: 7966

Further adjustments to the MySQL PyODBC dialect to allow for complete
connectivity, which was previously still not working despite fixes in
:ticket:`7871`.

.. change::
:tags: bug, sql
:tickets: 7979

Fixed an issue where using :func:`.bindparam` with no explicit data or type
given could be coerced into the incorrect type when used in expressions
such as when using :meth:`_types.ARRAY.Comparator.any` and
:meth:`_types.ARRAY.Comparator.all`.


.. change::
:tags: bug, oracle
:tickets: 8053

Fixed SQL compiler issue where the "bind processing" function for a bound
parameter would not be correctly applied to a bound value if the bound
parameter's name were "escaped". Concretely, this applies, among other
cases, to Oracle when a :class:`.Column` has a name that itself requires
quoting, such that the quoting-required name is then used for the bound
parameters generated within DML statements, and the datatype in use
requires bind processing, such as the :class:`.Enum` datatype.

.. change::
:tags: bug, mssql, reflection
:tickets: 8035

Explicitly specify the collation when reflecting table columns using
MSSQL to prevent "collation conflict" errors.

.. change::
:tags: bug, orm, oracle, postgresql
:tickets: 8056

Fixed bug, likely a regression from 1.3, where usage of column names that
require bound parameter escaping, more concretely when using Oracle with
column names that require quoting such as those that start with an
underscore, or in less common cases with some PostgreSQL drivers when using
column names that contain percent signs, would cause the ORM versioning
feature to not work correctly if the versioning column itself had such a
name, as the ORM assumes certain bound parameter naming conventions that
were being interfered with via the quotes. This issue is related to
:ticket:`8053` and essentially revises the approach towards fixing this,
revising the original issue :ticket:`5653` that created the initial
implementation for generalized bound-parameter name quoting.

.. change::
:tags: bug, mysql
:tickets: 8036

Added disconnect code for MySQL error 4031, introduced in MySQL >= 8.0.24,
indicating connection idle timeout exceeded. In particular this repairs an
issue where pre-ping could not reconnect on a timed-out connection. Pull
request courtesy valievkarim.

.. change::
:tags: bug, sql
:tickets: 8018

An informative error is raised if two individual :class:`.BindParameter`
objects share the same name, yet one is used within an "expanding" context
(typically an IN expression) and the other is not; mixing the same name in
these two different styles of usage is not supported and typically the
``expanding=True`` parameter should be set on the parameters that are to
receive list values outside of IN expressions (where ``expanding`` is set
by default).

.. change::
:tags: bug, engine, tests
:tickets: 8019

Fixed issue where support for logging "stacklevel" implemented in
:ticket:`7612` required adjustment to work with recently released Python
3.11.0b1, also repairs the unit tests which tested this feature.

.. change::
:tags: usecase, oracle
:tickets: 8066

Added two new error codes for Oracle disconnect handling to support early
testing of the new "python-oracledb" driver released by Oracle.

.. changelog::