Changelog
### 1.4.46
```
:released: January 3, 2023
.. change::
:tags: bug, engine
:tickets: 8974
:versions: 2.0.0rc1
Fixed a long-standing race condition in the connection pool which could
occur under eventlet/gevent monkeypatching schemes in conjunction with the
use of eventlet/gevent ``Timeout`` conditions, where a connection pool
checkout that's interrupted due to the timeout would fail to clean up the
failed state, causing the underlying connection record and sometimes the
database connection itself to "leak", leaving the pool in an invalid state
with unreachable entries. This issue was first identified and fixed in
SQLAlchemy 1.2 for :ticket:`4225`, however the failure modes detected in
that fix failed to accommodate for ``BaseException``, rather than
``Exception``, which prevented eventlet/gevent ``Timeout`` from being
caught. In addition, a block within initial pool connect has also been
identified and hardened with a ``BaseException`` -> "clean failed connect"
block to accommodate for the same condition in this location.
Big thanks to Github user niklaus for their tenacious efforts in
identifying and describing this intricate issue.
.. change::
:tags: bug, postgresql
:tickets: 9023
:versions: 2.0.0rc1
Fixed bug where the PostgreSQL
:paramref:`_postgresql.Insert.on_conflict_do_update.constraint` parameter
would accept an :class:`.Index` object, however would not expand this index
out into its individual index expressions, instead rendering its name in an
ON CONFLICT ON CONSTRAINT clause, which is not accepted by PostgreSQL; the
"constraint name" form only accepts unique or exclude constraint names. The
parameter continues to accept the index but now expands it out into its
component expressions for the render.
.. change::
:tags: bug, general
:tickets: 8995
:versions: 2.0.0rc1
Fixed regression where the base compat module was calling upon
``platform.architecture()`` in order to detect some system properties,
which results in an over-broad system call against the system-level
``file`` call that is unavailable under some circumstances, including
within some secure environment configurations.
.. change::
:tags: usecase, postgresql
:tickets: 8393
:versions: 2.0.0b5
Added the PostgreSQL type ``MACADDR8``.
Pull request courtesy of Asim Farooq.
.. change::
:tags: bug, sqlite
:tickets: 8969
:versions: 2.0.0b5
Fixed regression caused by new support for reflection of partial indexes on
SQLite added in 1.4.45 for :ticket:`8804`, where the ``index_list`` pragma
command in very old versions of SQLite (possibly prior to 3.8.9) does not
return the current expected number of columns, leading to exceptions raised
when reflecting tables and indexes.
.. change::
:tags: bug, tests
:versions: 2.0.0rc1
Fixed issue in tox.ini file where changes in the tox 4.0 series to the
format of "passenv" caused tox to not function correctly, in particular
raising an error as of tox 4.0.6.
.. change::
:tags: bug, tests
:tickets: 9002
:versions: 2.0.0rc1
Added new exclusion rule for third party dialects called
``unusual_column_name_characters``, which can be "closed" for third party
dialects that don't support column names with unusual characters such as
dots, slashes, or percent signs in them, even if the name is properly
quoted.
.. change::
:tags: bug, sql
:tickets: 9009
:versions: 2.0.0b5
Added parameter
:paramref:`.FunctionElement.column_valued.joins_implicitly`, which is
useful in preventing the "cartesian product" warning when making use of
table-valued or column-valued functions. This parameter was already
introduced for :meth:`.FunctionElement.table_valued` in :ticket:`7845`,
however it failed to be added for :meth:`.FunctionElement.column_valued`
as well.
.. change::
:tags: change, general
:tickets: 8983
A new deprecation "uber warning" is now emitted at runtime the
first time any SQLAlchemy 2.0 deprecation warning would normally be
emitted, but the ``SQLALCHEMY_WARN_20`` environment variable is not set.
The warning emits only once at most, before setting a boolean to prevent
it from emitting a second time.
This deprecation warning intends to notify users who may not have set an
appropriate constraint in their requirements files to block against a
surprise SQLAlchemy 2.0 upgrade and also alert that the SQLAlchemy 2.0
upgrade process is available, as the first full 2.0 release is expected
very soon. The deprecation warning can be silenced by setting the
environment variable ``SQLALCHEMY_SILENCE_UBER_WARNING`` to ``"1"``.
.. seealso::
:ref:`migration_20_toplevel`
.. change::
:tags: bug, orm
:tickets: 9033
:versions: 2.0.0rc1
Fixed issue in the internal SQL traversal for DML statements like
:class:`_dml.Update` and :class:`_dml.Delete` which would cause among other
potential issues, a specific issue using lambda statements with the ORM
update/delete feature.
.. change::
:tags: bug, sql
:tickets: 8989
:versions: 2.0.0b5
Fixed bug where SQL compilation would fail (assertion fail in 2.0, NoneType
error in 1.4) when using an expression whose type included
:meth:`_types.TypeEngine.bind_expression`, in the context of an "expanding"
(i.e. "IN") parameter in conjunction with the ``literal_binds`` compiler
parameter.
.. change::
:tags: bug, sql
:tickets: 9029
:versions: 2.0.0rc1
Fixed issue in lambda SQL feature where the calculated type of a literal
value would not take into account the type coercion rules of the "compared
to type", leading to a lack of typing information for SQL expressions, such
as comparisons to :class:`_types.JSON` elements and similar.
.. changelog::
```
### 1.4.45
```
:released: December 10, 2022
.. change::
:tags: bug, orm
:tickets: 8862
:versions: 2.0.0rc1
Fixed bug where :meth:`_orm.Session.merge` would fail to preserve the
current loaded contents of relationship attributes that were indicated with
the :paramref:`_orm.relationship.viewonly` parameter, thus defeating
strategies that use :meth:`_orm.Session.merge` to pull fully loaded objects
from caches and other similar techniques. In a related change, fixed issue
where an object that contains a loaded relationship that was nonetheless
configured as ``lazy='raise'`` on the mapping would fail when passed to
:meth:`_orm.Session.merge`; checks for "raise" are now suspended within
the merge process assuming the :paramref:`_orm.Session.merge.load`
parameter remains at its default of ``True``.
Overall, this is a behavioral adjustment to a change introduced in the 1.4
series as of :ticket:`4994`, which took "merge" out of the set of cascades
applied by default to "viewonly" relationships. As "viewonly" relationships
aren't persisted under any circumstances, allowing their contents to
transfer during "merge" does not impact the persistence behavior of the
target object. This allows :meth:`_orm.Session.merge` to correctly suit one
of its use cases, that of adding objects to a :class:`.Session` that were
loaded elsewhere, often for the purposes of restoring from a cache.
.. change::
:tags: bug, orm
:tickets: 8881
:versions: 2.0.0rc1
Fixed issues in :func:`_orm.with_expression` where expressions that were
composed of columns that were referenced from the enclosing SELECT would
not render correct SQL in some contexts, in the case where the expression
had a label name that matched the attribute which used
:func:`_orm.query_expression`, even when :func:`_orm.query_expression` had
no default expression. For the moment, if the :func:`_orm.query_expression`
does have a default expression, that label name is still used for that
default, and an additional label with the same name will continue to be
ignored. Overall, this case is pretty thorny so further adjustments might
be warranted.
.. change::
:tags: bug, sqlite
:tickets: 8866
Backported a fix for SQLite reflection of unique constraints in attached
schemas, released in 2.0 as a small part of :ticket:`4379`. Previously,
unique constraints in attached schemas would be ignored by SQLite
reflection. Pull request courtesy Michael Gorven.
.. change::
:tags: bug, asyncio
:tickets: 8952
:versions: 2.0.0rc1
Removed non-functional ``merge()`` method from
:class:`_asyncio.AsyncResult`. This method has never worked and was
included with :class:`_asyncio.AsyncResult` in error.
.. change::
:tags: bug, oracle
:tickets: 8708
:versions: 2.0.0b4
Continued fixes for Oracle fix :ticket:`8708` released in 1.4.43 where
bound parameter names that start with underscores, which are disallowed by
Oracle, were still not being properly escaped in all circumstances.
.. change::
:tags: bug, postgresql
:tickets: 8748
:versions: 2.0.0rc1
Made an adjustment to how the PostgreSQL dialect considers column types
when it reflects columns from a table, to accommodate for alternative
backends which may return NULL from the PG ``format_type()`` function.
.. change::
:tags: usecase, sqlite
:tickets: 8903
:versions: 2.0.0rc1
Added support for the SQLite backend to reflect the "DEFERRABLE" and
"INITIALLY" keywords which may be present on a foreign key construct. Pull
request courtesy Michael Gorven.
.. change::
:tags: usecase, sql
:tickets: 8800
:versions: 2.0.0rc1
An informative re-raise is now thrown in the case where any "literal
bindparam" render operation fails, indicating the value itself and
the datatype in use, to assist in debugging when literal params
are being rendered in a statement.
.. change::
:tags: usecase, sqlite
:tickets: 8804
:versions: 2.0.0rc1
Added support for reflection of expression-oriented WHERE criteria included
in indexes on the SQLite dialect, in a manner similar to that of the
PostgreSQL dialect. Pull request courtesy Tobias Pfeiffer.
.. change::
:tags: bug, sql
:tickets: 8827
:versions: 2.0.0rc1
Fixed a series of issues regarding the position and sometimes the identity
of rendered bound parameters, such as those used for SQLite, asyncpg,
MySQL, Oracle and others. Some compiled forms would not maintain the order
of parameters correctly, such as the PostgreSQL ``regexp_replace()``
function, the "nesting" feature of the :class:`.CTE` construct first
introduced in :ticket:`4123`, and selectable tables formed by using the
:meth:`.FunctionElement.column_valued` method with Oracle.
.. change::
:tags: bug, oracle
:tickets: 8945
:versions: 2.0.0rc1
Fixed issue in Oracle compiler where the syntax for
:meth:`.FunctionElement.column_valued` was incorrect, rendering the name
``COLUMN_VALUE`` without qualifying the source table correctly.
.. change::
:tags: bug, engine
:tickets: 8963
:versions: 2.0.0rc1
Fixed issue where :meth:`_engine.Result.freeze` method would not work for
textual SQL using either :func:`_sql.text` or
:meth:`_engine.Connection.exec_driver_sql`.
.. changelog::
```
### 1.4.44
```
:released: November 12, 2022
.. change::
:tags: bug, sql
:tickets: 8790
:versions: 2.0.0b4
Fixed critical memory issue identified in cache key generation, where for
very large and complex ORM statements that make use of lots of ORM aliases
with subqueries, cache key generation could produce excessively large keys
that were orders of magnitude bigger than the statement itself. Much thanks
to Rollo Konig Brock for their very patient, long term help in finally
identifying this issue.
.. change::
:tags: bug, postgresql, mssql
:tickets: 8770
:versions: 2.0.0b4
For the PostgreSQL and SQL Server dialects only, adjusted the compiler so
that when rendering column expressions in the RETURNING clause, the "non
anon" label that's used in SELECT statements is suggested for SQL
expression elements that generate a label; the primary example is a SQL
function that may be emitting as part of the column's type, where the label
name should match the column's name by default. This restores a not-well
defined behavior that had changed in version 1.4.21 due to :ticket:`6718`,
:ticket:`6710`. The Oracle dialect has a different RETURNING implementation
and was not affected by this issue. Version 2.0 features an across the
board change for its widely expanded support of RETURNING on other
backends.
.. change::
:tags: bug, oracle
Fixed issue in the Oracle dialect where an INSERT statement that used
``insert(some_table).values(...).returning(some_table)`` against a full
:class:`.Table` object at once would fail to execute, raising an exception.
.. change::
:tags: bug, tests
:tickets: 8793
:versions: 2.0.0b4
Fixed issue where the ``--disable-asyncio`` parameter to the test suite
would fail to not actually run greenlet tests and would also not prevent
the suite from using a "wrapping" greenlet for the whole suite. This
parameter now ensures that no greenlet or asyncio use will occur within the
entire run when set.
.. change::
:tags: bug, tests
Adjusted the test suite which tests the Mypy plugin to accommodate for
changes in Mypy 0.990 regarding how it handles message output, which affect
how sys.path is interpreted when determining if notes and errors should be
printed for particular files. The change broke the test suite as the files
within the test directory itself no longer produced messaging when run
under the mypy API.
.. changelog::
```
### 1.4.43
```
:released: November 4, 2022
.. change::
:tags: bug, orm
:tickets: 8738
:versions: 2.0.0b3
Fixed issue in joined eager loading where an assertion fail would occur
with a particular combination of outer/inner joined eager loads, when
eager loading across three mappers where the middle mapper was
an inherited subclass mapper.
.. change::
:tags: bug, oracle
:tickets: 8708
:versions: 2.0.0b3
Fixed issue where bound parameter names, including those automatically
derived from similarly-named database columns, which contained characters
that normally require quoting with Oracle would not be escaped when using
"expanding parameters" with the Oracle dialect, causing execution errors.
The usual "quoting" for bound parameters used by the Oracle dialect is not
used with the "expanding parameters" architecture, so escaping for a large
range of characters is used instead, now using a list of characters/escapes
that are specific to Oracle.
.. change::
:tags: bug, orm
:tickets: 8721
:versions: 2.0.0b3
Fixed bug involving :class:`.Select` constructs, where combinations of
:meth:`.Select.select_from` with :meth:`.Select.join`, as well as when
using :meth:`.Select.join_from`, would cause the
:func:`_orm.with_loader_criteria` feature as well as the IN criteria needed
for single-table inheritance queries to not render, in cases where the
columns clause of the query did not explicitly include the left-hand side
entity of the JOIN. The correct entity is now transferred to the
:class:`.Join` object that's generated internally, so that the criteria
against the left side entity is correctly added.
.. change::
:tags: bug, mssql
:tickets: 8714
:versions: 2.0.0b3
Fixed issue with :meth:`.Inspector.has_table`, which when used against a
temporary table with the SQL Server dialect would fail on some Azure
variants, due to an unnecessary information schema query that is not
supported on those server versions. Pull request courtesy Mike Barry.
.. change::
:tags: bug, orm
:tickets: 8711
:versions: 2.0.0b3
An informative exception is now raised when the
:func:`_orm.with_loader_criteria` option is used as a loader option added
to a specific "loader path", such as when using it within
:meth:`.Load.options`. This use is not supported as
:func:`_orm.with_loader_criteria` is only intended to be used as a top
level loader option. Previously, an internal error would be generated.
.. change::
:tags: bug, oracle
:tickets: 8744
:versions: 2.0.0b3
Fixed issue where the ``nls_session_parameters`` view queried on first
connect in order to get the default decimal point character may not be
available depending on Oracle connection modes, and would therefore raise
an error. The approach to detecting decimal char has been simplified to
test a decimal value directly, instead of reading system views, which
works on any backend / driver.
.. change::
:tags: bug, orm
:tickets: 8753
:versions: 2.0.0b3
Improved "dictionary mode" for :meth:`_orm.Session.get` so that synonym
names which refer to primary key attribute names may be indicated in the
named dictionary.
.. change::
:tags: bug, engine, regression
:tickets: 8717
:versions: 2.0.0b3
Fixed issue where the :meth:`.PoolEvents.reset` event hook would not be be
called in all cases when a :class:`_engine.Connection` were closed and was
in the process of returning its DBAPI connection to the connection pool.
The scenario was when the :class:`_engine.Connection` had already emitted
``.rollback()`` on its DBAPI connection within the process of returning
the connection to the pool, where it would then instruct the connection
pool to forego doing its own "reset" to save on the additional method
call. However, this prevented custom pool reset schemes from being
used within this hook, as such hooks by definition are doing more than
just calling ``.rollback()``, and need to be invoked under all
circumstances. This was a regression that appeared in version 1.4.
For version 1.4, the :meth:`.PoolEvents.checkin` remains viable as an
alternate event hook to use for custom "reset" implementations. Version 2.0
will feature an improved version of :meth:`.PoolEvents.reset` which is
called for additional scenarios such as termination of asyncio connections,
and is also passed contextual information about the reset, to allow for
"custom connection reset" schemes which can respond to different reset
scenarios in different ways.
.. change::
:tags: bug, orm
:tickets: 8704
:versions: 2.0.0b3
Fixed issue where "selectin_polymorphic" loading for inheritance mappers
would not function correctly if the :paramref:`_orm.Mapper.polymorphic_on`
parameter referred to a SQL expression that was not directly mapped on the
class.
.. change::
:tags: bug, orm
:tickets: 8710
:versions: 2.0.0b3
Fixed issue where the underlying DBAPI cursor would not be closed when
using the :class:`_orm.Query` object as an iterator, if a user-defined exception
case were raised within the iteration process, thereby causing the iterator
to be closed by the Python interpreter. When using
:meth:`_orm.Query.yield_per` to create server-side cursors, this would lead
to the usual MySQL-related issues with server side cursors out of sync,
and without direct access to the :class:`.Result` object, end-user code
could not access the cursor in order to close it.
To resolve, a catch for ``GeneratorExit`` is applied within the iterator
method, which will close the result object in those cases when the
iterator were interrupted, and by definition will be closed by the
Python interpreter.
As part of this change as implemented for the 1.4 series, ensured that
``.close()`` methods are available on all :class:`.Result` implementations
including :class:`.ScalarResult`, :class:`.MappingResult`. The 2.0
version of this change also includes new context manager patterns for use
with :class:`.Result` classes.
.. change::
:tags: bug, engine
:tickets: 8710
Ensured all :class:`.Result` objects include a :meth:`.Result.close` method
as well as a :attr:`.Result.closed` attribute, including on
:class:`.ScalarResult` and :class:`.MappingResult`.
.. change::
:tags: bug, mssql, reflection
:tickets: 8700
:versions: 2.0.0b3
Fixed issue with :meth:`.Inspector.has_table`, which when used against a
view with the SQL Server dialect would erroneously return ``False``, due to
a regression in the 1.4 series which removed support for this on SQL
Server. The issue is not present in the 2.0 series which uses a different
reflection architecture. Test support is added to ensure ``has_table()``
remains working per spec re: views.
.. change::
:tags: bug, sql
:tickets: 8724
:versions: 2.0.0b3
Fixed issue which prevented the :func:`_sql.literal_column` construct from
working properly within the context of a :class:`.Select` construct as well
as other potential places where "anonymized labels" might be generated, if
the literal expression contained characters which could interfere with
format strings, such as open parenthesis, due to an implementation detail
of the "anonymous label" structure.
.. changelog::
```
### 1.4.42
```
: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 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::
```
### 1.4.41
```
: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::
```
### 1.4.40
```
: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::
```
### 1.4.39
```
: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::
```
### 1.4.38
```
: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::
```
### 1.4.37
```
: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::
```
### 1.4.36
```
:released: April 26, 2022
.. change::
:tags: bug, mysql, regression
:tickets: 7871
Fixed a regression in the untested MySQL PyODBC dialect caused by the fix
for :ticket:`7518` in version 1.4.32 where an argument was being propagated
incorrectly upon first connect, leading to a ``TypeError``.
.. change::
:tags: bug, orm, regression
:tickets: 7936
Fixed regression where the change made for :ticket:`7861`, released in
version 1.4.33, that brought the :class:`_sql.Insert` construct to be partially
recognized as an ORM-enabled statement did not properly transfer the
correct mapper / mapped table state to the :class:`.Session`, causing the
:meth:`.Session.get_bind` method to fail for a :class:`.Session` that was
bound to engines and/or connections using the :paramref:`.Session.binds`
parameter.
.. change::
:tags: bug, engine
:tickets: 7875
Fixed a memory leak in the C extensions which could occur when calling upon
named members of :class:`.Row` when the member does not exist under Python
3; in particular this could occur during NumPy transformations when it
attempts to call members such as ``.__array__``, but the issue was
surrounding any ``AttributeError`` thrown by the :class:`.Row` object. This
issue does not apply to version 2.0 which has already transitioned to
Cython. Thanks much to Sebastian Berg for identifying the problem.
.. change::
:tags: bug, postgresql
:tickets: 6515
Fixed bug in :class:`_sqltypes.ARRAY` datatype in combination with :class:`.Enum` on
PostgreSQL where using the ``.any()`` or ``.all()`` methods to render SQL
ANY() or ALL(), given members of the Python enumeration as arguments, would
produce a type adaptation failure on all drivers.
.. change::
:tags: bug, postgresql
:tickets: 7943
Implemented :attr:`_postgresql.UUID.python_type` attribute for the
PostgreSQL :class:`_postgresql.UUID` type object. The attribute will return
either ``str`` or ``uuid.UUID`` based on the
:paramref:`_postgresql.UUID.as_uuid` parameter setting. Previously, this
attribute was unimplemented. Pull request courtesy Alex Grönholm.
.. change::
:tags: bug, tests
:tickets: 7919
For third party dialects, repaired a missing requirement for the
``SimpleUpdateDeleteTest`` suite test which was not checking for a working
"rowcount" function on the target dialect.
.. change::
:tags: bug, postgresql
:tickets: 7930
Fixed an issue in the psycopg2 dialect when using the
:paramref:`_sa.create_engine.pool_pre_ping` parameter which would cause
user-configured ``AUTOCOMMIT`` isolation level to be inadvertently reset by
the "ping" handler.
.. change::
:tags: bug, asyncio
:tickets: 7937
Repaired handling of ``contextvar.ContextVar`` objects inside of async
adapted event handlers. Previously, values applied to a ``ContextVar``
would not be propagated in the specific case of calling upon awaitables
inside of non-awaitable code.
.. change::
:tags: bug, engine
:tickets: 7953
Added a warning regarding a bug which exists in the :meth:`_result.Result.columns`
method when passing 0 for the index in conjunction with a :class:`_result.Result`
that will return a single ORM entity, which indicates that the current
behavior of :meth:`_result.Result.columns` is broken in this case as the
:class:`_result.Result` object will yield scalar values and not :class:`.Row`
objects. The issue will be fixed in 2.0, which would be a
backwards-incompatible change for code that relies on the current broken
behavior. Code which wants to receive a collection of scalar values should
use the :meth:`_result.Result.scalars` method, which will return a new
:class:`.ScalarResult` object that yields non-row scalar objects.
.. change::
:tags: bug, schema
:tickets: 7958
Fixed bug where :class:`.ForeignKeyConstraint` naming conventions using the
``referred_column_0`` naming convention key would not work if the foreign
key constraint were set up as a :class:`.ForeignKey` object rather than an
explicit :class:`.ForeignKeyConstraint` object. As this change makes use of
a backport of some fixes from version 2.0, an additional little-known
feature that has likely been broken for many years is also fixed which is
that a :class:`.ForeignKey` object may refer to a referred table by name of
the table alone without using a column name, if the name of the referent
column is the same as that of the referred column.
The ``referred_column_0`` naming convention key was previously not tested
with the :class:`.ForeignKey` object, only :class:`.ForeignKeyConstraint`,
and this bug reveals that the feature has never worked correctly unless
:class:`.ForeignKeyConstraint` is used for all FK constraints. This bug
traces back to the original introduction of the feature introduced for
:ticket:`3989`.
.. change::
:tags: bug, orm, declarative
:tickets: 7900
Modified the :class:`.DeclarativeMeta` metaclass to pass ``cls.__dict__``
into the declarative scanning process to look for attributes, rather than
the separate dictionary passed to the type's ``__init__()`` method. This
allows user-defined base classes that add attributes within an
``__init_subclass__()`` to work as expected, as ``__init_subclass__()`` can
only affect the ``cls.__dict__`` itself and not the other dictionary. This
is technically a regression from 1.3 where ``__dict__`` was being used.
.. changelog::
```
### 1.4.35
```
:released: April 6, 2022
.. change::
:tags: bug, sql
:tickets: 7890
Fixed bug in newly implemented
:paramref:`.FunctionElement.table_valued.joins_implicitly` feature where
the parameter would not automatically propagate from the original
:class:`.TableValuedAlias` object to the secondary object produced when
calling upon :meth:`.TableValuedAlias.render_derived` or
:meth:`.TableValuedAlias.alias`.
Additionally repaired these issues in :class:`.TableValuedAlias`:
* repaired a potential memory issue which could occur when
repeatedly calling :meth:`.TableValuedAlias.render_derived` against
successive copies of the same object (for .alias(), we currently
have to still continue chaining from the previous element. not sure
if this can be improved but this is standard behavior for .alias()
elsewhere)
* repaired issue where the individual element types would be lost when
calling upon :meth:`.TableValuedAlias.render_derived` or
:meth:`.TableValuedAlias.alias`.
.. change::
:tags: bug, sql, regression
:tickets: 7903
Fixed regression caused by :ticket:`7823` which impacted the caching
system, such that bound parameters that had been "cloned" within ORM
operations, such as polymorphic loading, would in some cases not acquire
their correct execution-time value leading to incorrect bind values being
rendered.
.. changelog::
```
### 1.4.34
```
:released: March 31, 2022
.. change::
:tags: bug, orm, regression
:tickets: 7878
Fixed regression caused by :ticket:`7861` where invoking an
:class:`_sql.Insert` construct which contained ORM entities directly via
:meth:`_orm.Session.execute` would fail.
.. change::
:tags: bug, postgresql
:tickets: 7880
Scaled back a fix made for :ticket:`6581` where "executemany values" mode
for psycopg2 were disabled for all "ON CONFLICT" styles of INSERT, to
not apply to the "ON CONFLICT DO NOTHING" clause, which does not include
any parameters and is safe for "executemany values" mode. "ON CONFLICT
DO UPDATE" is still blocked from "executemany values" as there may
be additional parameters in the DO UPDATE clause that cannot be batched
(which is the original issue fixed by :ticket:`6581`).
.. changelog::
```
### 1.4.33
```
:released: March 31, 2022
.. change::
:tags: bug, engine
:tickets: 7853
Further clarified connection-level logging to indicate the BEGIN, ROLLBACK
and COMMIT log messages do not actually indicate a real transaction when
the AUTOCOMMIT isolation level is in use; messaging has been extended to
include the BEGIN message itself, and the messaging has also been fixed to
accommodate when the :class:`_engine.Engine` level
:paramref:`_sa.create_engine.isolation_level` parameter was used directly.
.. change::
:tags: bug, mssql, regression
:tickets: 7812
Fixed regression caused by :ticket:`7160` where FK reflection in
conjunction with a low compatibility level setting (compatibility level 80:
SQL Server 2000) causes an "Ambiguous column name" error. Patch courtesy
Lin-Your.
.. change::
:tags: usecase, schema
:tickets: 7860
Added support so that the :paramref:`.Table.to_metadata.referred_schema_fn`
callable passed to :meth:`.Table.to_metadata` may return the value
:attr:`.BLANK_SCHEMA` to indicate that the referenced foreign key should be
reset to None. The :attr:`.RETAIN_SCHEMA` symbol may also be returned from
this function to indicate "no change", which will behave the same as
``None`` currently does which also indicates no change.
.. change::
:tags: bug, sqlite, reflection
:tickets: 5463
Fixed bug where the name of CHECK constraints under SQLite would not be
reflected if the name were created using quotes, as is the case when the
name uses mixed case or special characters.
.. change::
:tags: bug, orm, regression
:tickets: 7868
Fixed regression in "dynamic" loader strategy where the
:meth:`_orm.Query.filter_by` method would not be given an appropriate
entity to filter from, in the case where a "secondary" table were present
in the relationship being queried and the mapping were against something
complex such as a "with polymorphic".
.. change::
:tags: bug, orm
:tickets: 7801
Fixed bug where :func:`_orm.composite` attributes would not work in
conjunction with the :func:`_orm.selectin_polymorphic` loader strategy for
joined table inheritance.
.. change::
:tags: bug, orm, performance
:tickets: 7823
Improvements in memory usage by the ORM, removing a significant set of
intermediary expression objects that are typically stored when a copy of an
expression object is created. These clones have been greatly reduced,
reducing the number of total expression objects stored in memory by
ORM mappings by about 30%.
.. change::
:tags: usecase, orm
:tickets: 7805
Added
pyup-bot
commented
1 year ago
This PR updates SQLAlchemy from 1.2.12 to 1.4.46.
Changelog
### 1.4.46 ``` :released: January 3, 2023 .. change:: :tags: bug, engine :tickets: 8974 :versions: 2.0.0rc1 Fixed a long-standing race condition in the connection pool which could occur under eventlet/gevent monkeypatching schemes in conjunction with the use of eventlet/gevent ``Timeout`` conditions, where a connection pool checkout that's interrupted due to the timeout would fail to clean up the failed state, causing the underlying connection record and sometimes the database connection itself to "leak", leaving the pool in an invalid state with unreachable entries. This issue was first identified and fixed in SQLAlchemy 1.2 for :ticket:`4225`, however the failure modes detected in that fix failed to accommodate for ``BaseException``, rather than ``Exception``, which prevented eventlet/gevent ``Timeout`` from being caught. In addition, a block within initial pool connect has also been identified and hardened with a ``BaseException`` -> "clean failed connect" block to accommodate for the same condition in this location. Big thanks to Github user niklaus for their tenacious efforts in identifying and describing this intricate issue. .. change:: :tags: bug, postgresql :tickets: 9023 :versions: 2.0.0rc1 Fixed bug where the PostgreSQL :paramref:`_postgresql.Insert.on_conflict_do_update.constraint` parameter would accept an :class:`.Index` object, however would not expand this index out into its individual index expressions, instead rendering its name in an ON CONFLICT ON CONSTRAINT clause, which is not accepted by PostgreSQL; the "constraint name" form only accepts unique or exclude constraint names. The parameter continues to accept the index but now expands it out into its component expressions for the render. .. change:: :tags: bug, general :tickets: 8995 :versions: 2.0.0rc1 Fixed regression where the base compat module was calling upon ``platform.architecture()`` in order to detect some system properties, which results in an over-broad system call against the system-level ``file`` call that is unavailable under some circumstances, including within some secure environment configurations. .. change:: :tags: usecase, postgresql :tickets: 8393 :versions: 2.0.0b5 Added the PostgreSQL type ``MACADDR8``. Pull request courtesy of Asim Farooq. .. change:: :tags: bug, sqlite :tickets: 8969 :versions: 2.0.0b5 Fixed regression caused by new support for reflection of partial indexes on SQLite added in 1.4.45 for :ticket:`8804`, where the ``index_list`` pragma command in very old versions of SQLite (possibly prior to 3.8.9) does not return the current expected number of columns, leading to exceptions raised when reflecting tables and indexes. .. change:: :tags: bug, tests :versions: 2.0.0rc1 Fixed issue in tox.ini file where changes in the tox 4.0 series to the format of "passenv" caused tox to not function correctly, in particular raising an error as of tox 4.0.6. .. change:: :tags: bug, tests :tickets: 9002 :versions: 2.0.0rc1 Added new exclusion rule for third party dialects called ``unusual_column_name_characters``, which can be "closed" for third party dialects that don't support column names with unusual characters such as dots, slashes, or percent signs in them, even if the name is properly quoted. .. change:: :tags: bug, sql :tickets: 9009 :versions: 2.0.0b5 Added parameter :paramref:`.FunctionElement.column_valued.joins_implicitly`, which is useful in preventing the "cartesian product" warning when making use of table-valued or column-valued functions. This parameter was already introduced for :meth:`.FunctionElement.table_valued` in :ticket:`7845`, however it failed to be added for :meth:`.FunctionElement.column_valued` as well. .. change:: :tags: change, general :tickets: 8983 A new deprecation "uber warning" is now emitted at runtime the first time any SQLAlchemy 2.0 deprecation warning would normally be emitted, but the ``SQLALCHEMY_WARN_20`` environment variable is not set. The warning emits only once at most, before setting a boolean to prevent it from emitting a second time. This deprecation warning intends to notify users who may not have set an appropriate constraint in their requirements files to block against a surprise SQLAlchemy 2.0 upgrade and also alert that the SQLAlchemy 2.0 upgrade process is available, as the first full 2.0 release is expected very soon. The deprecation warning can be silenced by setting the environment variable ``SQLALCHEMY_SILENCE_UBER_WARNING`` to ``"1"``. .. seealso:: :ref:`migration_20_toplevel` .. change:: :tags: bug, orm :tickets: 9033 :versions: 2.0.0rc1 Fixed issue in the internal SQL traversal for DML statements like :class:`_dml.Update` and :class:`_dml.Delete` which would cause among other potential issues, a specific issue using lambda statements with the ORM update/delete feature. .. change:: :tags: bug, sql :tickets: 8989 :versions: 2.0.0b5 Fixed bug where SQL compilation would fail (assertion fail in 2.0, NoneType error in 1.4) when using an expression whose type included :meth:`_types.TypeEngine.bind_expression`, in the context of an "expanding" (i.e. "IN") parameter in conjunction with the ``literal_binds`` compiler parameter. .. change:: :tags: bug, sql :tickets: 9029 :versions: 2.0.0rc1 Fixed issue in lambda SQL feature where the calculated type of a literal value would not take into account the type coercion rules of the "compared to type", leading to a lack of typing information for SQL expressions, such as comparisons to :class:`_types.JSON` elements and similar. .. changelog:: ``` ### 1.4.45 ``` :released: December 10, 2022 .. change:: :tags: bug, orm :tickets: 8862 :versions: 2.0.0rc1 Fixed bug where :meth:`_orm.Session.merge` would fail to preserve the current loaded contents of relationship attributes that were indicated with the :paramref:`_orm.relationship.viewonly` parameter, thus defeating strategies that use :meth:`_orm.Session.merge` to pull fully loaded objects from caches and other similar techniques. In a related change, fixed issue where an object that contains a loaded relationship that was nonetheless configured as ``lazy='raise'`` on the mapping would fail when passed to :meth:`_orm.Session.merge`; checks for "raise" are now suspended within the merge process assuming the :paramref:`_orm.Session.merge.load` parameter remains at its default of ``True``. Overall, this is a behavioral adjustment to a change introduced in the 1.4 series as of :ticket:`4994`, which took "merge" out of the set of cascades applied by default to "viewonly" relationships. As "viewonly" relationships aren't persisted under any circumstances, allowing their contents to transfer during "merge" does not impact the persistence behavior of the target object. This allows :meth:`_orm.Session.merge` to correctly suit one of its use cases, that of adding objects to a :class:`.Session` that were loaded elsewhere, often for the purposes of restoring from a cache. .. change:: :tags: bug, orm :tickets: 8881 :versions: 2.0.0rc1 Fixed issues in :func:`_orm.with_expression` where expressions that were composed of columns that were referenced from the enclosing SELECT would not render correct SQL in some contexts, in the case where the expression had a label name that matched the attribute which used :func:`_orm.query_expression`, even when :func:`_orm.query_expression` had no default expression. For the moment, if the :func:`_orm.query_expression` does have a default expression, that label name is still used for that default, and an additional label with the same name will continue to be ignored. Overall, this case is pretty thorny so further adjustments might be warranted. .. change:: :tags: bug, sqlite :tickets: 8866 Backported a fix for SQLite reflection of unique constraints in attached schemas, released in 2.0 as a small part of :ticket:`4379`. Previously, unique constraints in attached schemas would be ignored by SQLite reflection. Pull request courtesy Michael Gorven. .. change:: :tags: bug, asyncio :tickets: 8952 :versions: 2.0.0rc1 Removed non-functional ``merge()`` method from :class:`_asyncio.AsyncResult`. This method has never worked and was included with :class:`_asyncio.AsyncResult` in error. .. change:: :tags: bug, oracle :tickets: 8708 :versions: 2.0.0b4 Continued fixes for Oracle fix :ticket:`8708` released in 1.4.43 where bound parameter names that start with underscores, which are disallowed by Oracle, were still not being properly escaped in all circumstances. .. change:: :tags: bug, postgresql :tickets: 8748 :versions: 2.0.0rc1 Made an adjustment to how the PostgreSQL dialect considers column types when it reflects columns from a table, to accommodate for alternative backends which may return NULL from the PG ``format_type()`` function. .. change:: :tags: usecase, sqlite :tickets: 8903 :versions: 2.0.0rc1 Added support for the SQLite backend to reflect the "DEFERRABLE" and "INITIALLY" keywords which may be present on a foreign key construct. Pull request courtesy Michael Gorven. .. change:: :tags: usecase, sql :tickets: 8800 :versions: 2.0.0rc1 An informative re-raise is now thrown in the case where any "literal bindparam" render operation fails, indicating the value itself and the datatype in use, to assist in debugging when literal params are being rendered in a statement. .. change:: :tags: usecase, sqlite :tickets: 8804 :versions: 2.0.0rc1 Added support for reflection of expression-oriented WHERE criteria included in indexes on the SQLite dialect, in a manner similar to that of the PostgreSQL dialect. Pull request courtesy Tobias Pfeiffer. .. change:: :tags: bug, sql :tickets: 8827 :versions: 2.0.0rc1 Fixed a series of issues regarding the position and sometimes the identity of rendered bound parameters, such as those used for SQLite, asyncpg, MySQL, Oracle and others. Some compiled forms would not maintain the order of parameters correctly, such as the PostgreSQL ``regexp_replace()`` function, the "nesting" feature of the :class:`.CTE` construct first introduced in :ticket:`4123`, and selectable tables formed by using the :meth:`.FunctionElement.column_valued` method with Oracle. .. change:: :tags: bug, oracle :tickets: 8945 :versions: 2.0.0rc1 Fixed issue in Oracle compiler where the syntax for :meth:`.FunctionElement.column_valued` was incorrect, rendering the name ``COLUMN_VALUE`` without qualifying the source table correctly. .. change:: :tags: bug, engine :tickets: 8963 :versions: 2.0.0rc1 Fixed issue where :meth:`_engine.Result.freeze` method would not work for textual SQL using either :func:`_sql.text` or :meth:`_engine.Connection.exec_driver_sql`. .. changelog:: ``` ### 1.4.44 ``` :released: November 12, 2022 .. change:: :tags: bug, sql :tickets: 8790 :versions: 2.0.0b4 Fixed critical memory issue identified in cache key generation, where for very large and complex ORM statements that make use of lots of ORM aliases with subqueries, cache key generation could produce excessively large keys that were orders of magnitude bigger than the statement itself. Much thanks to Rollo Konig Brock for their very patient, long term help in finally identifying this issue. .. change:: :tags: bug, postgresql, mssql :tickets: 8770 :versions: 2.0.0b4 For the PostgreSQL and SQL Server dialects only, adjusted the compiler so that when rendering column expressions in the RETURNING clause, the "non anon" label that's used in SELECT statements is suggested for SQL expression elements that generate a label; the primary example is a SQL function that may be emitting as part of the column's type, where the label name should match the column's name by default. This restores a not-well defined behavior that had changed in version 1.4.21 due to :ticket:`6718`, :ticket:`6710`. The Oracle dialect has a different RETURNING implementation and was not affected by this issue. Version 2.0 features an across the board change for its widely expanded support of RETURNING on other backends. .. change:: :tags: bug, oracle Fixed issue in the Oracle dialect where an INSERT statement that used ``insert(some_table).values(...).returning(some_table)`` against a full :class:`.Table` object at once would fail to execute, raising an exception. .. change:: :tags: bug, tests :tickets: 8793 :versions: 2.0.0b4 Fixed issue where the ``--disable-asyncio`` parameter to the test suite would fail to not actually run greenlet tests and would also not prevent the suite from using a "wrapping" greenlet for the whole suite. This parameter now ensures that no greenlet or asyncio use will occur within the entire run when set. .. change:: :tags: bug, tests Adjusted the test suite which tests the Mypy plugin to accommodate for changes in Mypy 0.990 regarding how it handles message output, which affect how sys.path is interpreted when determining if notes and errors should be printed for particular files. The change broke the test suite as the files within the test directory itself no longer produced messaging when run under the mypy API. .. changelog:: ``` ### 1.4.43 ``` :released: November 4, 2022 .. change:: :tags: bug, orm :tickets: 8738 :versions: 2.0.0b3 Fixed issue in joined eager loading where an assertion fail would occur with a particular combination of outer/inner joined eager loads, when eager loading across three mappers where the middle mapper was an inherited subclass mapper. .. change:: :tags: bug, oracle :tickets: 8708 :versions: 2.0.0b3 Fixed issue where bound parameter names, including those automatically derived from similarly-named database columns, which contained characters that normally require quoting with Oracle would not be escaped when using "expanding parameters" with the Oracle dialect, causing execution errors. The usual "quoting" for bound parameters used by the Oracle dialect is not used with the "expanding parameters" architecture, so escaping for a large range of characters is used instead, now using a list of characters/escapes that are specific to Oracle. .. change:: :tags: bug, orm :tickets: 8721 :versions: 2.0.0b3 Fixed bug involving :class:`.Select` constructs, where combinations of :meth:`.Select.select_from` with :meth:`.Select.join`, as well as when using :meth:`.Select.join_from`, would cause the :func:`_orm.with_loader_criteria` feature as well as the IN criteria needed for single-table inheritance queries to not render, in cases where the columns clause of the query did not explicitly include the left-hand side entity of the JOIN. The correct entity is now transferred to the :class:`.Join` object that's generated internally, so that the criteria against the left side entity is correctly added. .. change:: :tags: bug, mssql :tickets: 8714 :versions: 2.0.0b3 Fixed issue with :meth:`.Inspector.has_table`, which when used against a temporary table with the SQL Server dialect would fail on some Azure variants, due to an unnecessary information schema query that is not supported on those server versions. Pull request courtesy Mike Barry. .. change:: :tags: bug, orm :tickets: 8711 :versions: 2.0.0b3 An informative exception is now raised when the :func:`_orm.with_loader_criteria` option is used as a loader option added to a specific "loader path", such as when using it within :meth:`.Load.options`. This use is not supported as :func:`_orm.with_loader_criteria` is only intended to be used as a top level loader option. Previously, an internal error would be generated. .. change:: :tags: bug, oracle :tickets: 8744 :versions: 2.0.0b3 Fixed issue where the ``nls_session_parameters`` view queried on first connect in order to get the default decimal point character may not be available depending on Oracle connection modes, and would therefore raise an error. The approach to detecting decimal char has been simplified to test a decimal value directly, instead of reading system views, which works on any backend / driver. .. change:: :tags: bug, orm :tickets: 8753 :versions: 2.0.0b3 Improved "dictionary mode" for :meth:`_orm.Session.get` so that synonym names which refer to primary key attribute names may be indicated in the named dictionary. .. change:: :tags: bug, engine, regression :tickets: 8717 :versions: 2.0.0b3 Fixed issue where the :meth:`.PoolEvents.reset` event hook would not be be called in all cases when a :class:`_engine.Connection` were closed and was in the process of returning its DBAPI connection to the connection pool. The scenario was when the :class:`_engine.Connection` had already emitted ``.rollback()`` on its DBAPI connection within the process of returning the connection to the pool, where it would then instruct the connection pool to forego doing its own "reset" to save on the additional method call. However, this prevented custom pool reset schemes from being used within this hook, as such hooks by definition are doing more than just calling ``.rollback()``, and need to be invoked under all circumstances. This was a regression that appeared in version 1.4. For version 1.4, the :meth:`.PoolEvents.checkin` remains viable as an alternate event hook to use for custom "reset" implementations. Version 2.0 will feature an improved version of :meth:`.PoolEvents.reset` which is called for additional scenarios such as termination of asyncio connections, and is also passed contextual information about the reset, to allow for "custom connection reset" schemes which can respond to different reset scenarios in different ways. .. change:: :tags: bug, orm :tickets: 8704 :versions: 2.0.0b3 Fixed issue where "selectin_polymorphic" loading for inheritance mappers would not function correctly if the :paramref:`_orm.Mapper.polymorphic_on` parameter referred to a SQL expression that was not directly mapped on the class. .. change:: :tags: bug, orm :tickets: 8710 :versions: 2.0.0b3 Fixed issue where the underlying DBAPI cursor would not be closed when using the :class:`_orm.Query` object as an iterator, if a user-defined exception case were raised within the iteration process, thereby causing the iterator to be closed by the Python interpreter. When using :meth:`_orm.Query.yield_per` to create server-side cursors, this would lead to the usual MySQL-related issues with server side cursors out of sync, and without direct access to the :class:`.Result` object, end-user code could not access the cursor in order to close it. To resolve, a catch for ``GeneratorExit`` is applied within the iterator method, which will close the result object in those cases when the iterator were interrupted, and by definition will be closed by the Python interpreter. As part of this change as implemented for the 1.4 series, ensured that ``.close()`` methods are available on all :class:`.Result` implementations including :class:`.ScalarResult`, :class:`.MappingResult`. The 2.0 version of this change also includes new context manager patterns for use with :class:`.Result` classes. .. change:: :tags: bug, engine :tickets: 8710 Ensured all :class:`.Result` objects include a :meth:`.Result.close` method as well as a :attr:`.Result.closed` attribute, including on :class:`.ScalarResult` and :class:`.MappingResult`. .. change:: :tags: bug, mssql, reflection :tickets: 8700 :versions: 2.0.0b3 Fixed issue with :meth:`.Inspector.has_table`, which when used against a view with the SQL Server dialect would erroneously return ``False``, due to a regression in the 1.4 series which removed support for this on SQL Server. The issue is not present in the 2.0 series which uses a different reflection architecture. Test support is added to ensure ``has_table()`` remains working per spec re: views. .. change:: :tags: bug, sql :tickets: 8724 :versions: 2.0.0b3 Fixed issue which prevented the :func:`_sql.literal_column` construct from working properly within the context of a :class:`.Select` construct as well as other potential places where "anonymized labels" might be generated, if the literal expression contained characters which could interfere with format strings, such as open parenthesis, due to an implementation detail of the "anonymous label" structure. .. changelog:: ``` ### 1.4.42 ``` :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 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:: ``` ### 1.4.41 ``` :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:: ``` ### 1.4.40 ``` :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:: ``` ### 1.4.39 ``` :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:: ``` ### 1.4.38 ``` :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:: ``` ### 1.4.37 ``` :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:: ``` ### 1.4.36 ``` :released: April 26, 2022 .. change:: :tags: bug, mysql, regression :tickets: 7871 Fixed a regression in the untested MySQL PyODBC dialect caused by the fix for :ticket:`7518` in version 1.4.32 where an argument was being propagated incorrectly upon first connect, leading to a ``TypeError``. .. change:: :tags: bug, orm, regression :tickets: 7936 Fixed regression where the change made for :ticket:`7861`, released in version 1.4.33, that brought the :class:`_sql.Insert` construct to be partially recognized as an ORM-enabled statement did not properly transfer the correct mapper / mapped table state to the :class:`.Session`, causing the :meth:`.Session.get_bind` method to fail for a :class:`.Session` that was bound to engines and/or connections using the :paramref:`.Session.binds` parameter. .. change:: :tags: bug, engine :tickets: 7875 Fixed a memory leak in the C extensions which could occur when calling upon named members of :class:`.Row` when the member does not exist under Python 3; in particular this could occur during NumPy transformations when it attempts to call members such as ``.__array__``, but the issue was surrounding any ``AttributeError`` thrown by the :class:`.Row` object. This issue does not apply to version 2.0 which has already transitioned to Cython. Thanks much to Sebastian Berg for identifying the problem. .. change:: :tags: bug, postgresql :tickets: 6515 Fixed bug in :class:`_sqltypes.ARRAY` datatype in combination with :class:`.Enum` on PostgreSQL where using the ``.any()`` or ``.all()`` methods to render SQL ANY() or ALL(), given members of the Python enumeration as arguments, would produce a type adaptation failure on all drivers. .. change:: :tags: bug, postgresql :tickets: 7943 Implemented :attr:`_postgresql.UUID.python_type` attribute for the PostgreSQL :class:`_postgresql.UUID` type object. The attribute will return either ``str`` or ``uuid.UUID`` based on the :paramref:`_postgresql.UUID.as_uuid` parameter setting. Previously, this attribute was unimplemented. Pull request courtesy Alex Grönholm. .. change:: :tags: bug, tests :tickets: 7919 For third party dialects, repaired a missing requirement for the ``SimpleUpdateDeleteTest`` suite test which was not checking for a working "rowcount" function on the target dialect. .. change:: :tags: bug, postgresql :tickets: 7930 Fixed an issue in the psycopg2 dialect when using the :paramref:`_sa.create_engine.pool_pre_ping` parameter which would cause user-configured ``AUTOCOMMIT`` isolation level to be inadvertently reset by the "ping" handler. .. change:: :tags: bug, asyncio :tickets: 7937 Repaired handling of ``contextvar.ContextVar`` objects inside of async adapted event handlers. Previously, values applied to a ``ContextVar`` would not be propagated in the specific case of calling upon awaitables inside of non-awaitable code. .. change:: :tags: bug, engine :tickets: 7953 Added a warning regarding a bug which exists in the :meth:`_result.Result.columns` method when passing 0 for the index in conjunction with a :class:`_result.Result` that will return a single ORM entity, which indicates that the current behavior of :meth:`_result.Result.columns` is broken in this case as the :class:`_result.Result` object will yield scalar values and not :class:`.Row` objects. The issue will be fixed in 2.0, which would be a backwards-incompatible change for code that relies on the current broken behavior. Code which wants to receive a collection of scalar values should use the :meth:`_result.Result.scalars` method, which will return a new :class:`.ScalarResult` object that yields non-row scalar objects. .. change:: :tags: bug, schema :tickets: 7958 Fixed bug where :class:`.ForeignKeyConstraint` naming conventions using the ``referred_column_0`` naming convention key would not work if the foreign key constraint were set up as a :class:`.ForeignKey` object rather than an explicit :class:`.ForeignKeyConstraint` object. As this change makes use of a backport of some fixes from version 2.0, an additional little-known feature that has likely been broken for many years is also fixed which is that a :class:`.ForeignKey` object may refer to a referred table by name of the table alone without using a column name, if the name of the referent column is the same as that of the referred column. The ``referred_column_0`` naming convention key was previously not tested with the :class:`.ForeignKey` object, only :class:`.ForeignKeyConstraint`, and this bug reveals that the feature has never worked correctly unless :class:`.ForeignKeyConstraint` is used for all FK constraints. This bug traces back to the original introduction of the feature introduced for :ticket:`3989`. .. change:: :tags: bug, orm, declarative :tickets: 7900 Modified the :class:`.DeclarativeMeta` metaclass to pass ``cls.__dict__`` into the declarative scanning process to look for attributes, rather than the separate dictionary passed to the type's ``__init__()`` method. This allows user-defined base classes that add attributes within an ``__init_subclass__()`` to work as expected, as ``__init_subclass__()`` can only affect the ``cls.__dict__`` itself and not the other dictionary. This is technically a regression from 1.3 where ``__dict__`` was being used. .. changelog:: ``` ### 1.4.35 ``` :released: April 6, 2022 .. change:: :tags: bug, sql :tickets: 7890 Fixed bug in newly implemented :paramref:`.FunctionElement.table_valued.joins_implicitly` feature where the parameter would not automatically propagate from the original :class:`.TableValuedAlias` object to the secondary object produced when calling upon :meth:`.TableValuedAlias.render_derived` or :meth:`.TableValuedAlias.alias`. Additionally repaired these issues in :class:`.TableValuedAlias`: * repaired a potential memory issue which could occur when repeatedly calling :meth:`.TableValuedAlias.render_derived` against successive copies of the same object (for .alias(), we currently have to still continue chaining from the previous element. not sure if this can be improved but this is standard behavior for .alias() elsewhere) * repaired issue where the individual element types would be lost when calling upon :meth:`.TableValuedAlias.render_derived` or :meth:`.TableValuedAlias.alias`. .. change:: :tags: bug, sql, regression :tickets: 7903 Fixed regression caused by :ticket:`7823` which impacted the caching system, such that bound parameters that had been "cloned" within ORM operations, such as polymorphic loading, would in some cases not acquire their correct execution-time value leading to incorrect bind values being rendered. .. changelog:: ``` ### 1.4.34 ``` :released: March 31, 2022 .. change:: :tags: bug, orm, regression :tickets: 7878 Fixed regression caused by :ticket:`7861` where invoking an :class:`_sql.Insert` construct which contained ORM entities directly via :meth:`_orm.Session.execute` would fail. .. change:: :tags: bug, postgresql :tickets: 7880 Scaled back a fix made for :ticket:`6581` where "executemany values" mode for psycopg2 were disabled for all "ON CONFLICT" styles of INSERT, to not apply to the "ON CONFLICT DO NOTHING" clause, which does not include any parameters and is safe for "executemany values" mode. "ON CONFLICT DO UPDATE" is still blocked from "executemany values" as there may be additional parameters in the DO UPDATE clause that cannot be batched (which is the original issue fixed by :ticket:`6581`). .. changelog:: ``` ### 1.4.33 ``` :released: March 31, 2022 .. change:: :tags: bug, engine :tickets: 7853 Further clarified connection-level logging to indicate the BEGIN, ROLLBACK and COMMIT log messages do not actually indicate a real transaction when the AUTOCOMMIT isolation level is in use; messaging has been extended to include the BEGIN message itself, and the messaging has also been fixed to accommodate when the :class:`_engine.Engine` level :paramref:`_sa.create_engine.isolation_level` parameter was used directly. .. change:: :tags: bug, mssql, regression :tickets: 7812 Fixed regression caused by :ticket:`7160` where FK reflection in conjunction with a low compatibility level setting (compatibility level 80: SQL Server 2000) causes an "Ambiguous column name" error. Patch courtesy Lin-Your. .. change:: :tags: usecase, schema :tickets: 7860 Added support so that the :paramref:`.Table.to_metadata.referred_schema_fn` callable passed to :meth:`.Table.to_metadata` may return the value :attr:`.BLANK_SCHEMA` to indicate that the referenced foreign key should be reset to None. The :attr:`.RETAIN_SCHEMA` symbol may also be returned from this function to indicate "no change", which will behave the same as ``None`` currently does which also indicates no change. .. change:: :tags: bug, sqlite, reflection :tickets: 5463 Fixed bug where the name of CHECK constraints under SQLite would not be reflected if the name were created using quotes, as is the case when the name uses mixed case or special characters. .. change:: :tags: bug, orm, regression :tickets: 7868 Fixed regression in "dynamic" loader strategy where the :meth:`_orm.Query.filter_by` method would not be given an appropriate entity to filter from, in the case where a "secondary" table were present in the relationship being queried and the mapping were against something complex such as a "with polymorphic". .. change:: :tags: bug, orm :tickets: 7801 Fixed bug where :func:`_orm.composite` attributes would not work in conjunction with the :func:`_orm.selectin_polymorphic` loader strategy for joined table inheritance. .. change:: :tags: bug, orm, performance :tickets: 7823 Improvements in memory usage by the ORM, removing a significant set of intermediary expression objects that are typically stored when a copy of an expression object is created. These clones have been greatly reduced, reducing the number of total expression objects stored in memory by ORM mappings by about 30%. .. change:: :tags: usecase, orm :tickets: 7805 Added