Changelog
### 1.4.10
```
:released: April 20, 2021
.. change::
:tags: bug, declarative, regression
:tickets: 6291
Fixed :func:`_declarative.instrument_declarative` that called
a non existing registry method.
.. change::
:tags: bug, orm
:tickets: 6320
Fixed bug in new :func:`_orm.with_loader_criteria` feature where using a
mixin class with :func:`_orm.declared_attr` on an attribute that were
accessed inside the custom lambda would emit a warning regarding using an
unmapped declared attr, when the lambda callable were first initialized.
This warning is now prevented using special instrumentation for this
lambda initialization step.
.. change::
:tags: usecase, schema, mssql
:tickets: 6306
The :paramref:`_types.DateTime.timezone` parameter when set to ``True``
will now make use of the ``DATETIMEOFFSET`` column type with SQL Server
when used to emit DDL, rather than ``DATETIME`` where the flag was silently
ignored.
.. change::
:tags: orm, bug, regression
:tickets: 6326
Fixed additional regression caused by the "eagerloaders on refresh" feature
added in :ticket:`1763` where the refresh operation historically would set
``populate_existing``, which given the new feature now overwrites pending
changes on eagerly loaded objects when autoflush is false. The
populate_existing flag has been turned off for this case and a more
specific method used to ensure the correct attributes refreshed.
.. change::
:tags: bug, orm, result
:tickets: 6299
Fixed an issue when using 2.0 style execution that prevented using
:meth:`_result.Result.scalar_one` or
:meth:`_result.Result.scalar_one_or_none` after calling
:meth:`_result.Result.unique`, for the case where the ORM is returning a
single-element row in any case.
.. change::
:tags: bug, sql
:tickets: 6327
Fixed issue in SQL compiler where the bound parameters set up for a
:class:`.Values` construct wouldn't be positionally tracked correctly if
inside of a :class:`_sql.CTE`, affecting database drivers that support
VALUES + ctes and use positional parameters such as SQL Server in
particular as well as asyncpg. The fix also repairs support for
compiler flags such as ``literal_binds``.
.. change::
:tags: bug, schema
:tickets: 6287
Fixed issue where :func:`_functions.next_value` was not deriving its type
from the corresponding :class:`_schema.Sequence`, instead hardcoded to
:class:`_types.Integer`. The specific numeric type is now used.
.. change::
:tags: bug, mypy
:tickets: 6255
Fixed issue where mypy plugin would not correctly interpret an explicit
:class:`_orm.Mapped` annotation in conjunction with a
:func:`_orm.relationship` that refers to a class by string name; the
correct annotation would be downgraded to a less specific one leading to
typing errors.
.. change::
:tags: bug, sql
:tickets: 6256
Repaired and solidified issues regarding custom functions and other
arbitrary expression constructs which within SQLAlchemy's column labeling
mechanics would seek to use ``str(obj)`` to get a string representation to
use as an anonymous column name in the ``.c`` collection of a subquery.
This is a very legacy behavior that performs poorly and leads to lots of
issues, so has been revised to no longer perform any compilation by
establishing specific methods on :class:`.FunctionElement` to handle this
case, as SQL functions are the only use case that it came into play. An
effect of this behavior is that an unlabeled column expression with no
derivable name will be given an arbitrary label starting with the prefix
``"_no_label"`` in the ``.c`` collection of a subquery; these were
previously being represented either as the generic stringification of that
expression, or as an internal symbol.
.. change::
:tags: usecase, orm
:ticketS: 6301
Altered some of the behavior repaired in :ticket:`6232` where the
``immediateload`` loader strategy no longer goes into recursive loops; the
modification is that an eager load (joinedload, selectinload, or
subqueryload) from A->bs->B which then states ``immediateload`` for a
simple manytoone B->a->A that's in the identity map will populate the B->A,
so that this attribute is back-populated when the collection of A/A.bs are
loaded. This allows the objects to be functional when detached.
.. changelog::
```
### 1.4.9
```
:released: April 17, 2021
.. change::
:tags: bug, sql, regression
:tickets: 6290
Fixed regression where an empty in statement on a tuple would result
in an error when compiled with the option ``literal_binds=True``.
.. change::
:tags: bug, regression, orm, performance, sql
:tickets: 6304
Fixed a critical performance issue where the traversal of a
:func:`_sql.select` construct would traverse a repetitive product of the
represented FROM clauses as they were each referred towards by columns in
the columns clause; for a series of nested subqueries with lots of columns
this could cause a large delay and significant memory growth. This
traversal is used by a wide variety of SQL and ORM functions, including by
the ORM :class:`_orm.Session` when it's configured to have
"table-per-bind", which while this is not a common use case, it seems to be
what Flask-SQLAlchemy is hardcoded as using, so the issue impacts
Flask-SQLAlchemy users. The traversal has been repaired to uniqify on FROM
clauses which was effectively what would happen implicitly with the pre-1.4
architecture.
.. change::
:tags: bug, postgresql, sql, regression
:tickets: 6303
Fixed an argument error in the default and PostgreSQL compilers that
would interfere with an UPDATE..FROM or DELETE..FROM..USING statement
that was then SELECTed from as a CTE.
.. change::
:tags: bug, orm, regression
:tickets: 6272
Fixed regression where an attribute that is mapped to a
:func:`_orm.synonym` could not be used in column loader options such as
:func:`_orm.load_only`.
.. change::
:tags: usecase, orm
:tickets: 6267
Established support for :func:`_orm.synoynm` in conjunction with
hybrid property, assocaitionproxy is set up completely, including that
synonyms can be established linking to these constructs which work
fully. This is a behavior that was semi-explicitly disallowed previously,
however since it did not fail in every scenario, explicit support
for assoc proxy and hybrids has been added.
.. changelog::
```
### 1.4.8
```
:released: April 15, 2021
.. change::
:tags: change, mypy
Updated Mypy plugin to only use the public plugin interface of the
semantic analyzer.
.. change::
:tags: bug, mssql, regression
:tickets: 6265
Fixed an additional regression in the same area as that of :ticket:`6173`,
:ticket:`6184`, where using a value of 0 for OFFSET in conjunction with
LIMIT with SQL Server would create a statement using "TOP", as was the
behavior in 1.3, however due to caching would then fail to respond
accordingly to other values of OFFSET. If the "0" wasn't first, then it
would be fine. For the fix, the "TOP" syntax is now only emitted if the
OFFSET value is omitted entirely, that is, :meth:`_sql.Select.offset` is
not used. Note that this change now requires that if the "with_ties" or
"percent" modifiers are used, the statement can't specify an OFFSET of
zero, it now needs to be omitted entirely.
.. change::
:tags: bug, engine
The :meth:`_engine.Dialect.has_table` method now raises an informative
exception if a non-Connection is passed to it, as this incorrect behavior
seems to be common. This method is not intended for external use outside
of a dialect. Please use the :meth:`.Inspector.has_table` method
or for cross-compatibility with older SQLAlchemy versions, the
:meth:`_engine.Engine.has_table` method.
.. change::
:tags: bug, regression, sql
:tickets: 6249
Fixed regression where the :class:`_sql.BindParameter` object would not
properly render for an IN expression (i.e. using the "post compile" feature
in 1.4) if the object were copied from either an internal cloning
operation, or from a pickle operation, and the parameter name contained
spaces or other special characters.
.. change::
:tags: bug, mypy
:tickets: 6205
Revised the fix for ``OrderingList`` from version 1.4.7 which was testing
against the incorrect API.
.. change::
:tags: bug, asyncio
:tickets: 6220
Fix typo that prevented setting the ``bind`` attribute of an
:class:`_asyncio.AsyncSession` to the correct value.
.. change::
:tags: feature, sql
:tickets: 3314
The tuple returned by :attr:`.CursorResult.inserted_primary_key` is now a
:class:`_result.Row` object with a named tuple interface on top of the
existing tuple interface.
.. change::
:tags: bug, regression, sql, sqlite
:tickets: 6254
Fixed regression where the introduction of the INSERT syntax "INSERT...
VALUES (DEFAULT)" was not supported on some backends that do however
support "INSERT..DEFAULT VALUES", including SQLite. The two syntaxes are
now each individually supported or non-supported for each dialect, for
example MySQL supports "VALUES (DEFAULT)" but not "DEFAULT VALUES".
Support for Oracle has also been enabled.
.. change::
:tags: bug, regression, orm
:tickets: 6259
Fixed a cache leak involving the :func:`_orm.with_expression` loader
option, where the given SQL expression would not be correctly considered as
part of the cache key.
Additionally, fixed regression involving the corresponding
:func:`_orm.query_expression` feature. While the bug technically exists in
1.3 as well, it was not exposed until 1.4. The "default expr" value of
``null()`` would be rendered when not needed, and additionally was also not
adapted correctly when the ORM rewrites statements such as when using
joined eager loading. The fix ensures "singleton" expressions like ``NULL``
and ``true`` aren't "adapted" to refer to columns in ORM statements, and
additionally ensures that a :func:`_orm.query_expression` with no default
expression doesn't render in the statement if a
:func:`_orm.with_expression` isn't used.
.. change::
:tags: bug, orm
:tickets: 6252
Fixed issue in the new feature of :meth:`_orm.Session.refresh` introduced
by :ticket:`1763` where eagerly loaded relationships are also refreshed,
where the ``lazy="raise"`` and ``lazy="raise_on_sql"`` loader strategies
would interfere with the :func:`_orm.immediateload` loader strategy, thus
breaking the feature for relationships that were loaded with
:func:`_orm.selectinload`, :func:`_orm.subqueryload` as well.
.. changelog::
```
### 1.4.7
```
:released: April 9, 2021
.. change::
:tags: bug, sql, regression
:tickets: 6222
Enhanced the "expanding" feature used for :meth:`_sql.ColumnOperators.in_`
operations to infer the type of expression from the right hand list of
elements, if the left hand side does not have any explicit type set up.
This allows the expression to support stringification among other things.
In 1.3, "expanding" was not automatically used for
:meth:`_sql.ColumnOperators.in_` expressions, so in that sense this change
fixes a behavioral regression.
.. change::
:tags: bug, mypy
Fixed issue in Mypy plugin where the plugin wasn’t inferring the correct
type for columns of subclasses that don’t directly descend from
``TypeEngine``, in particular that of ``TypeDecorator`` and
``UserDefinedType``.
.. change::
:tags: bug, orm, regression
:tickets: 6221
Fixed regression where the :func:`_orm.subqueryload` loader strategy would
fail to correctly accommodate sub-options, such as a :func:`_orm.defer`
option on a column, if the "path" of the subqueryload were more than one
level deep.
.. change::
:tags: bug, sql
Fixed the "stringify" compiler to support a basic stringification
of a "multirow" INSERT statement, i.e. one with multiple tuples
following the VALUES keyword.
.. change::
:tags: bug, orm, regression
:tickets: 6211
Fixed regression where the :func:`_orm.merge_frozen_result` function relied
upon by the dogpile.caching example was not included in tests and began
failing due to incorrect internal arguments.
.. change::
:tags: bug, engine, regression
:tickets: 6218
Fixed up the behavior of the :class:`_result.Row` object when dictionary
access is used upon it, meaning converting to a dict via ``dict(row)`` or
accessing members using strings or other objects i.e. ``row["some_key"]``
works as it would with a dictionary, rather than raising ``TypeError`` as
would be the case with a tuple, whether or not the C extensions are in
place. This was originally supposed to emit a 2.0 deprecation warning for
the "non-future" case using :class:`_result.LegacyRow`, and was to raise
``TypeError`` for the "future" :class:`_result.Row` class. However, the C
version of :class:`_result.Row` was failing to raise this ``TypeError``,
and to complicate matters, the :meth:`_orm.Session.execute` method now
returns :class:`_result.Row` in all cases to maintain consistency with the
ORM result case, so users who didn't have C extensions installed would
see different behavior in this one case for existing pre-1.4 style
code.
Therefore, in order to soften the overall upgrade scheme as most users have
not been exposed to the more strict behavior of :class:`_result.Row` up
through 1.4.6, :class:`_result.LegacyRow` and :class:`_result.Row` both
provide for string-key access as well as support for ``dict(row)``, in all
cases emitting the 2.0 deprecation warning when ``SQLALCHEMY_WARN_20`` is
enabled. The :class:`_result.Row` object still uses tuple-like behavior for
``__contains__``, which is probably the only noticeable behavioral change
compared to :class:`_result.LegacyRow`, other than the removal of
dictionary-style methods ``values()`` and ``items()``.
.. change::
:tags: bug, regression, orm
:tickets: 6233
Fixed critical regression where the :class:`_orm.Session` could fail to
"autobegin" a new transaction when a flush occurred without an existing
transaction in place, implicitly placing the :class:`_orm.Session` into
legacy autocommit mode which commit the transaction. The
:class:`_orm.Session` now has a check that will prevent this condition from
occurring, in addition to repairing the flush issue.
Additionally, scaled back part of the change made as part of :ticket:`5226`
which can run autoflush during an unexpire operation, to not actually
do this in the case of a :class:`_orm.Session` using legacy
:paramref:`_orm.Session.autocommit` mode, as this incurs a commit within
a refresh operation.
.. change::
:tags: change, tests
Added a new flag to :class:`.DefaultDialect` called ``supports_schemas``;
third party dialects may set this flag to ``False`` to disable SQLAlchemy's
schema-level tests when running the test suite for a third party dialect.
.. change::
:tags: bug, regression, schema
:tickets: 6216
Fixed regression where usage of a token in the
:paramref:`_engine.Connection.execution_options.schema_translate_map`
dictionary which contained special characters such as braces would fail to
be substituted properly. Use of square bracket characters ``[]`` is now
explicitly disallowed as these are used as a delimiter character in the
current implementation.
.. change::
:tags: bug, regression, orm
:tickets: 6215
Fixed regression where the ORM compilation scheme would assume the function
name of a hybrid property would be the same as the attribute name in such a
way that an ``AttributeError`` would be raised, when it would attempt to
determine the correct name for each element in a result tuple. A similar
issue exists in 1.3 but only impacts the names of tuple rows. The fix here
adds a check that the hybrid's function name is actually present in the
``__dict__`` of the class or its superclasses before assigning this name;
otherwise, the hybrid is considered to be "unnamed" and ORM result tuples
will use the naming scheme of the underlying expression.
.. change::
:tags: bug, orm, regression
:tickets: 6232
Fixed critical regression caused by the new feature added as part of
:ticket:`1763`, eager loaders are invoked on unexpire operations. The new
feature makes use of the "immediateload" eager loader strategy as a
substitute for a collection loading strategy, which unlike the other
"post-load" strategies was not accommodating for recursive invocations
between mutually-dependent relationships, leading to recursion overflow
errors.
.. changelog::
```
### 1.4.6
```
:released: April 6, 2021
.. change::
:tags: bug, sql, regression, oracle, mssql
:tickets: 6202
Fixed further regressions in the same area as that of :ticket:`6173` released in
1.4.5, where a "postcompile" parameter, again most typically those used for
LIMIT/OFFSET rendering in Oracle and SQL Server, would fail to be processed
correctly if the same parameter rendered in multiple places in the
statement.
.. change::
:tags: bug, orm, regression
:tickets: 6203
Fixed regression where a deprecated form of :meth:`_orm.Query.join` were
used, passing a series of entities to join from without any ON clause in a
single :meth:`_orm.Query.join` call, would fail to function correctly.
.. change::
:tags: bug, mypy
:tickets: 6147
Applied a series of refactorings and fixes to accommodate for Mypy
"incremental" mode across multiple files, which previously was not taken
into account. In this mode the Mypy plugin has to accommodate Python
datatypes expressed in other files coming in with less information than
they have on a direct run.
Additionally, a new decorator :func:`_orm.declarative_mixin` is added,
which is necessary for the Mypy plugin to be able to definifitely identify
a Declarative mixin class that is otherwise not used inside a particular
Python file.
.. seealso::
:ref:`mypy_declarative_mixins`
.. change::
:tags: bug, mypy
:tickets: 6205
Fixed issue where the Mypy plugin would fail to interpret the
"collection_class" of a relationship if it were a callable and not a class.
Also improved type matching and error reporting for collection-oriented
relationships.
.. change::
:tags: bug, sql
:tickets: 6204
Executing a :class:`_sql.Subquery` using :meth:`_engine.Connection.execute`
is deprecated and will emit a deprecation warning; this use case was an
oversight that should have been removed from 1.4. The operation will now
execute the underlying :class:`_sql.Select` object directly for backwards
compatibility. Similarly, the :class:`_sql.CTE` class is also not
appropriate for execution. In 1.3, attempting to execute a CTE would result
in an invalid "blank" SQL statement being executed; since this use case was
not working it now raises :class:`_exc.ObjectNotExecutableError`.
Previously, 1.4 was attempting to execute the CTE as a statement however it
was working only erratically.
.. change::
:tags: bug, regression, orm
:tickets: 6206
Fixed critical regression where the :meth:`_orm.Query.yield_per` method in
the ORM would set up the internal :class:`_engine.Result` to yield chunks
at a time, however made use of the new :meth:`_engine.Result.unique` method
which uniques across the entire result. This would lead to lost rows since
the ORM is using ``id(obj)`` as the uniquing function, which leads to
repeated identifiers for new objects as already-seen objects are garbage
collected. 1.3's behavior here was to "unique" across each chunk, which
does not actually produce "uniqued" results when results are yielded in
chunks. As the :meth:`_orm.Query.yield_per` method is already explicitly
disallowed when joined eager loading is in place, which is the primary
rationale for the "uniquing" feature, the "uniquing" feature is now turned
off entirely when :meth:`_orm.Query.yield_per` is used.
This regression only applies to the legacy :class:`_orm.Query` object; when
using :term:`2.0 style` execution, "uniquing" is not automatically applied.
To prevent the issue from arising from explicit use of
:meth:`_engine.Result.unique`, an error is now raised if rows are fetched
from a "uniqued" ORM-level :class:`_engine.Result` if any
:ref:`yield per <orm_queryguide_yield_per>` API is also in use, as the
purpose of ``yield_per`` is to allow for arbitrarily large numbers of rows,
which cannot be uniqued in memory without growing the number of entries to
fit the complete result size.
.. change::
:tags: usecase, asyncio, postgresql
:tickets: 6199
Added accessors ``.sqlstate`` and synonym ``.pgcode`` to the ``.orig``
attribute of the SQLAlchemy exception class raised by the asyncpg DBAPI
adapter, that is, the intermediary exception object that wraps on top of
that raised by the asyncpg library itself, but below the level of the
SQLAlchemy dialect.
.. changelog::
```
### 1.4.5
```
:released: April 2, 2021
.. change::
:tags: bug, sql, postgresql
:tickets: 6183
Fixed bug in new :meth:`_functions.FunctionElement.render_derived` feature
where column names rendered out explicitly in the alias SQL would not have
proper quoting applied for case sensitive names and other non-alphanumeric
names.
.. change::
:tags: bug, regression, orm
:tickets: 6172
Fixed regression where the :func:`_orm.joinedload` loader strategy would
not successfully joinedload to a mapper that is mapper against a
:class:`.CTE` construct.
.. change::
:tags: bug, regression, sql
:tickets: 6181
Fixed regression where use of the :meth:`.Operators.in_` method with a
:class:`_sql.Select` object against a non-table-bound column would produce
an ``AttributeError``, or more generally using a :class:`_sql.ScalarSelect`
that has no datatype in a binary expression would produce invalid state.
.. change::
:tags: bug, mypy
:tickets: sqlalchemy/sqlalchemy2-stubs/14
Fixed issue in mypy plugin where newly added support for
:func:`_orm.as_declarative` needed to more fully add the
``DeclarativeMeta`` class to the mypy interpreter's state so that it does
not result in a name not found error; additionally improves how global
names are setup for the plugin including the ``Mapped`` name.
.. change::
:tags: bug, mysql, regression
:tickets: 6163
Fixed regression in the MySQL dialect where the reflection query used to
detect if a table exists would fail on very old MySQL 5.0 and 5.1 versions.
.. change::
:tags: bug, sql
:tickets: 6184
Added a new flag to the :class:`_engine.Dialect` class called
:attr:`_engine.Dialect.supports_statement_cache`. This flag now needs to be present
directly on a dialect class in order for SQLAlchemy's
:ref:`query cache <sql_caching>` to take effect for that dialect. The
rationale is based on discovered issues such as :ticket:`6173` revealing
that dialects which hardcode literal values from the compiled statement,
often the numerical parameters used for LIMIT / OFFSET, will not be
compatible with caching until these dialects are revised to use the
parameters present in the statement only. For third party dialects where
this flag is not applied, the SQL logging will show the message "dialect
does not support caching", indicating the dialect should seek to apply this
flag once they have verified that no per-statement literal values are being
rendered within the compilation phase.
.. seealso::
:ref:`engine_thirdparty_caching`
.. change::
:tags: bug, postgresql
:tickets: 6099
Fixed typo in the fix for :ticket:`6099` released in 1.4.4 that completely
prevented this change from working correctly, i.e. the error message did not match
what was actually emitted by pg8000.
.. change::
:tags: bug, orm, regression
:tickets: 6171
Scaled back the warning message added in :ticket:`5171` to not warn for
overlapping columns in an inheritance scenario where a particular
relationship is local to a subclass and therefore does not represent an
overlap.
.. change::
:tags: bug, regression, oracle
:tickets: 6173
Fixed critical regression where the Oracle compiler would not maintain the
correct parameter values in the LIMIT/OFFSET for a select due to a caching
issue.
.. change::
:tags: bug, postgresql
:tickets: 6170
Fixed issue where the PostgreSQL :class:`.PGInspector`, when generated
against an :class:`_engine.Engine`, would fail for ``.get_enums()``,
``.get_view_names()``, ``.get_foreign_table_names()`` and
``.get_table_oid()`` when used against a "future" style engine and not the
connection directly.
.. change::
:tags: bug, schema
:tickets: 6146
Introduce a new parameter :paramref:`_types.Enum.omit_aliases` in
:class:`_types.Enum` type allow filtering aliases when using a pep435 Enum.
Previous versions of SQLAlchemy kept aliases in all cases, creating
database enum type with additional states, meaning that they were treated
as different values in the db. For backward compatibility this flag
defaults to ``False`` in the 1.4 series, but will be switched to ``True``
in a future version. A deprecation warning is raise if this flag is not
specified and the passed enum contains aliases.
.. change::
:tags: bug, mssql
:tickets: 6163
Fixed a regression in MSSQL 2012+ that prevented the order by clause
to be rendered when ``offset=0`` is used in a subquery.
.. change::
:tags: bug, asyncio
:tickets: 6166
Fixed issue where the asyncio extension could not be loaded
if running Python 3.6 with the backport library of
``contextvars`` installed.
.. changelog::
```
### 1.4.4
```
:released: March 30, 2021
.. change::
:tags: bug, misc
Adjusted the usage of the ``importlib_metadata`` library for loading
setuptools entrypoints in order to accommodate for some deprecation
changes.
.. change::
:tags: bug, postgresql
:tickets: 6099
Modified the ``is_disconnect()`` handler for the pg8000 dialect, which now
accommodates for a new ``InterfaceError`` emitted by pg8000 1.19.0. Pull
request courtesy Hamdi Burak Usul.
.. change::
:tags: bug, orm
:tickets: 6139
Fixed critical issue in the new :meth:`_orm.PropComparator.and_` feature
where loader strategies that emit secondary SELECT statements such as
:func:`_orm.selectinload` and :func:`_orm.lazyload` would fail to
accommodate for bound parameters in the user-defined criteria in terms of
the current statement being executed, as opposed to the cached statement,
causing stale bound values to be used.
This also adds a warning for the case where an object that uses
:func:`_orm.lazyload` in conjunction with :meth:`_orm.PropComparator.and_`
is attempted to be serialized; the loader criteria cannot reliably
be serialized and deserialized and eager loading should be used for this
case.
.. change::
:tags: bug, engine
:tickets: 6138
Repair wrong arguments to exception handling method
in CursorResult.
.. change::
:tags: bug, regression, orm
:tickets: 6144
Fixed missing method :meth:`_orm.Session.get` from the
:class:`_orm.ScopedSession` interface.
.. change::
:tags: usecase, engine
:tickets: 6155
Modified the context manager used by :class:`_engine.Transaction` so that
an "already detached" warning is not emitted by the ending of the context
manager itself, if the transaction were already manually rolled back inside
the block. This applies to regular transactions, savepoint transactions,
and legacy "marker" transactions. A warning is still emitted if the
``.rollback()`` method is called explicitly more than once.
.. changelog::
```
### 1.4.3
```
:released: March 25, 2021
.. change::
:tags: bug, orm
:tickets: 6069
Fixed a bug where python 2.7.5 (default on CentOS 7) wasn't able to import
sqlalchemy, because on this version of Python ``exec "statement"`` and
``exec("statement")`` do not behave the same way. The compatibility
``exec_()`` function was used instead.
.. change::
:tags: sqlite, feature, asyncio
:tickets: 5920
Added support for the aiosqlite database driver for use with the
SQLAlchemy asyncio extension.
.. seealso::
:ref:`aiosqlite`
.. change::
:tags: bug, regression, orm, declarative
:tickets: 6128
Fixed regression where the ``.metadata`` attribute on a per class level
would not be honored, breaking the use case of per-class-hierarchy
:class:`.schema.MetaData` for abstract declarative classes and mixins.
.. seealso::
:ref:`declarative_metadata`
.. change::
:tags: bug, mypy
Added support for the Mypy extension to correctly interpret a declarative
base class that's generated using the :func:`_orm.as_declarative` function
as well as the :meth:`_orm.registry.as_declarative_base` method.
.. change::
:tags: bug, mypy
:tickets: 6109
Fixed bug in Mypy plugin where the Python type detection
for the :class:`_types.Boolean` column type would produce
an exception; additionally implemented support for :class:`_types.Enum`,
including detection of a string-based enum vs. use of Python ``enum.Enum``.
.. change::
:tags: bug, reflection, postgresql
:tickets: 6129
Fixed reflection of identity columns in tables with mixed case names
in PostgreSQL.
.. change::
:tags: bug, sqlite, regression
:tickets: 5848
Repaired the ``pysqlcipher`` dialect to connect correctly which had
regressed in 1.4, and added test + CI support to maintain the driver
in working condition. The dialect now imports the ``sqlcipher3`` module
for Python 3 by default before falling back to ``pysqlcipher3`` which
is documented as now being unmaintained.
.. seealso::
:ref:`pysqlcipher`
.. change::
:tags: bug, orm
:tickets: 6060
Fixed bug where ORM queries using a correlated subquery in conjunction with
:func:`_orm.column_property` would fail to correlate correctly to an
enclosing subquery or to a CTE when :meth:`_sql.Select.correlate_except`
were used in the property to control correlation, in cases where the
subquery contained the same selectables as ones within the correlated
subquery that were intended to not be correlated.
.. change::
:tags: bug, orm
:tickets: 6131
Fixed bug where combinations of the new "relationship with criteria"
feature could fail in conjunction with features that make use of the new
"lambda SQL" feature, including loader strategies such as selectinload and
lazyload, for more complicated scenarios such as polymorphic loading.
.. change::
:tags: bug, orm
:tickets: 6124
Repaired support so that the :meth:`_sql.ClauseElement.params` method can
work correctly with a :class:`_sql.Select` object that includes joins
across ORM relationship structures, which is a new feature in 1.4.
.. change::
:tags: bug, engine, regression
:tickets: 6119
Restored the :class:`_engine.ResultProxy` name back to the
``sqlalchemy.engine`` namespace. This name refers to the
:class:`_engine.LegacyCursorResult` object.
.. change::
:tags: bug, orm
:tickets: 6115
Fixed issue where a "removed in 2.0" warning were generated internally by
the relationship loader mechanics.
.. changelog::
```
### 1.4.2
```
:released: March 19, 2021
.. change::
:tags: bug, orm, dataclasses
:tickets: 6093
Fixed issue in new ORM dataclasses functionality where dataclass fields on
an abstract base or mixin that contained column or other mapping constructs
would not be mapped if they also included a "default" key within the
dataclasses.field() object.
.. change::
:tags: bug, regression, orm
:tickets: 6088
Fixed regression where the :attr:`_orm.Query.selectable` accessor, which is
a synonym for :meth:`_orm.Query.__clause_element__`, got removed, it's now
restored.
.. change::
:tags: bug, engine, regression
Restored top level import for ``sqlalchemy.engine.reflection``. This
ensures that the base :class:`_reflection.Inspector` class is properly
registered so that :func:`_sa.inspect` works for third party dialects that
don't otherwise import this package.
.. change::
:tags: bug, regression, orm
:tickets: 6086
Fixed regression where use of an unnamed SQL expression such as a SQL
function would raise a column targeting error if the query itself were
using joinedload for an entity and was also being wrapped in a subquery by
the joinedload eager loading process.
.. change::
:tags: bug, orm, regression
:tickets: 6092
Fixed regression where the :meth:`_orm.Query.filter_by` method would fail
to locate the correct source entity if the :meth:`_orm.Query.join` method
had been used targeting an entity without any kind of ON clause.
.. change::
:tags: postgresql, usecase
:tickets: 6982
Rename the column name used by a reflection query that used
a reserved word in some postgresql compatible databases.
.. change::
:tags: usecase, orm, dataclasses
:tickets: 6100
Added support for the :class:`_orm.declared_attr` object to work in the
context of dataclass fields.
.. seealso::
:ref:`orm_declarative_dataclasses_mixin`
.. change::
:tags: bug, sql, regression
:tickets: 6101
Fixed issue where using a ``func`` that includes dotted packagenames would
fail to be cacheable by the SQL caching system due to a Python list of
names that needed to be a tuple.
.. change::
:tags: bug, regression, orm
:tickets: 6095
Fixed regression where the SQL compilation of a :class:`.Function` would
not work correctly if the object had been "annotated", which is an internal
memoization process used mostly by the ORM. In particular it could affect
ORM lazy loads which make greater use of this feature in 1.4.
.. change::
:tags: bug, sql, regression
:tickets: 6097
Fixed regression in the :func:`_sql.case` construct, where the "dictionary"
form of argument specification failed to work correctly if it were passed
positionally, rather than as a "whens" keyword argument.
.. change::
:tags: bug, orm
:tickets: 6090
Fixed regression where the :class:`.ConcreteBase` would fail to map at all
when a mapped column name overlapped with the discriminator column name,
producing an assertion error. The use case here did not function correctly
in 1.3 as the polymorphic union would produce a query that ignored the
discriminator column entirely, while emitting duplicate column warnings. As
1.4's architecture cannot easily reproduce this essentially broken behavior
of 1.3 at the ``select()`` level right now, the use case now raises an
informative error message instructing the user to use the
``.ConcreteBase._concrete_discriminator_name`` attribute to resolve the
conflict. To assist with this configuration,
``.ConcreteBase._concrete_discriminator_name`` may be placed on the base
class only where it will be automatically used by subclasses; previously
this was not the case.
.. change::
:tags: bug, mypy
:tickets: sqlalchemy/sqlalchemy2-stubs/2
Fixed issue in MyPy extension which crashed on detecting the type of a
:class:`.Column` if the type were given with a module prefix like
``sa.Integer()``.
.. changelog::
```
### 1.4.1
```
:released: March 17, 2021
.. change::
:tags: bug, orm, regression
:tickets: 6066
Fixed regression where producing a Core expression construct such as
:func:`_sql.select` using ORM entities would eagerly configure the mappers,
in an effort to maintain compatibility with the :class:`_orm.Query` object
which necessarily does this to support many backref-related legacy cases.
However, core :func:`_sql.select` constructs are also used in mapper
configurations and such, and to that degree this eager configuration is
more of an inconvenience, so eager configure has been disabled for the
:func:`_sql.select` and other Core constructs in the absence of ORM loading
types of functions such as :class:`_orm.Load`.
The change maintains the behavior of :class:`_orm.Query` so that backwards
compatibility is maintained. However, when using a :func:`_sql.select` in
conjunction with ORM entities, a "backref" that isn't explicitly placed on
one of the classes until mapper configure time won't be available unless
:func:`_orm.configure_mappers` or the newer :func:`_orm.registry.configure`
has been called elsewhere. Prefer using
:paramref:`_orm.relationship.back_populates` for more explicit relationship
configuration which does not have the eager configure requirement.
.. change::
:tags: bug, mssql, regression
:tickets: 6058
Fixed regression where a new setinputsizes() API that's available for
pyodbc was enabled, which is apparently incompatible with pyodbc's
fast_executemany() mode in the absence of more accurate typing information,
which as of yet is not fully implemented or tested. The pyodbc dialect and
connector has been modified so that setinputsizes() is not used at all
unless the parameter ``use_setinputsizes`` is passed to the dialect, e.g.
via :func:`_sa.create_engine`, at which point its behavior can be
customized using the :meth:`.DialectEvents.do_setinputsizes` hook.
.. seealso::
:ref:`mssql_pyodbc_setinputsizes`
.. change::
:tags: bug, orm, regression
:tickets: 6055
Fixed a critical regression in the relationship lazy loader where the SQL
criteria used to fetch a related many-to-one object could go stale in
relation to other memoized structures within the loader if the mapper had
configuration changes, such as can occur when mappers are late configured
or configured on demand, producing a comparison to None and returning no
object. Huge thanks to Alan Hamlett for their help tracking this down late
into the night.
.. change::
:tags: bug, regression
:tickets: 6068
Added back ``items`` and ``values`` to ``ColumnCollection`` class.
The regression was introduced while adding support for duplicate
columns in from clauses and selectable in ticket 4753.
.. change::
:tags: bug, engine, regression
:tickets: 6074
The Python ``namedtuple()`` has the behavior such that the names ``count``
and ``index`` will be served as tuple values if the named tuple includes
those names; if they are absent, then their behavior as methods of
``collections.abc.Sequence`` is maintained. Therefore the
:class:`_result.Row` and :class:`_result.LegacyRow` classes have been fixed
so that they work in this same way, maintaining the expected behavior for
database rows that have columns named "index" or "count".
.. change::
:tags: bug, orm, regression
:tickets: 6076
Fixed regression where the :meth:`_orm.Query.exists` method would fail to
create an expression if the entity list of the :class:`_orm.Query` were
an arbitrary SQL column expression.
.. change::
:tags: bug, orm, regression
:tickets: 6052
Fixed regression where calling upon :meth:`_orm.Query.count` in conjunction
with a loader option such as :func:`_orm.joinedload` would fail to ignore
the loader option. This is a behavior that has always been very specific to
the :meth:`_orm.Query.count` method; an error is normally raised if a given
:class:`_orm.Query` has options that don't apply to what it is returning.
.. change::
:tags: bug, orm, declarative, regression
:tickets: 6054
Fixed bug where user-mapped classes that contained an attribute named
"registry" would cause conflicts with the new registry-based mapping system
when using :class:`.DeclarativeMeta`. While the attribute remains
something that can be set explicitly on a declarative base to be
consumed by the metaclass, once located it is placed under a private
class variable so it does not conflict with future subclasses that use
the same name for other purposes.
.. change::
:tags: bug, orm, regression
:tickets: 6067
Fixed regression in :meth:`_orm.Session.identity_key`, including that the
method and related methods were not covered by any unit test as well as
that the method contained a typo preventing it from functioning correctly.
.. changelog::
```
### 1.4.0
```
:released: March 15, 2021
.. change::
:tags: bug, mssql
:tickets: 5919
Fix a reflection error for MSSQL 2005 introduced by the reflection of
filtered indexes.
.. change::
:tags: feature, mypy
:tickets: 4609
Rudimentary and experimental support for Mypy has been added in the form of
a new plugin, which itself depends on new typing stubs for SQLAlchemy. The
plugin allows declarative mappings in their standard form to both be
compatible with Mypy as well as to provide typing support for mapped
classes and instances.
.. seealso::
:ref:`mypy_toplevel`
.. change::
:tags: bug, sql
:tickets: 6016
Fixed bug where the "percent escaping" feature that occurs with dialects
that use the "format" or "pyformat" bound parameter styles was not enabled
for the :meth:`_sql.Operators.op` and :class:`_sql.custom_op` constructs,
for custom operators that use percent signs. The percent sign will now be
automatically doubled based on the paramstyle as necessary.
.. change::
:tags: bug, regression, sql
:tickets: 5979
Fixed regression where the "unsupported compilation error" for unknown
datatypes would fail to raise correctly.
.. change::
:tags: ext, usecase
:tickets: 5942
Add new parameter
:paramref:`_automap.AutomapBase.prepare.reflection_options`
to allow passing of :meth:`_schema.MetaData.reflect` options like ``only``
or dialect-specific reflection options like ``oracle_resolve_synonyms``.
.. change::
:tags: change, sql
Altered the compilation for the :class:`.CTE` construct so that a string is
returned representing the inner SELECT statement if the :class:`.CTE` is
stringified directly, outside of the context of an enclosing SELECT; This
is the same behavior of :meth:`_sql.FromClause.alias` and
:meth:`_sql.Select.subquery`. Previously, a blank string would be
returned as the CTE is normally placed above a SELECT after that SELECT has
been generated, which is generally misleading when debugging.
.. change::
:tags: bug, orm
:tickets: 5981
Fixed regression where the :paramref:`_orm.relationship.query_class`
parameter stopped being functional for "dynamic" relationships. The
``AppenderQuery`` remains dependent on the legacy :class:`_orm.Query`
class; users are encouraged to migrate from the use of "dynamic"
relationships to using :func:`_orm.with_parent` instead.
.. change::
:tags: bug, orm, regression
:tickets: 6003
Fixed regression where :meth:`_orm.Query.join` would produce no effect if
the query itself as well as the join target were against a
:class:`_schema.Table` object, rather than a mapped class. This was part of
a more systemic issue where the legacy ORM query compiler would not be
correctly used from a :class:`_orm.Query` if the statement produced had not
ORM entities present within it.
.. change::
:tags: bug, regression, sql
:tickets: 6008
Fixed regression where usage of the standalone :func:`_sql.distinct()` used
in the form of being directly SELECTed would fail to be locatable in the
result set by column identity, which is how the ORM locates columns. While
standalone :func:`_sql.distinct()` is not oriented towards being directly
SELECTed (use :meth:`_sql.select.distinct` for a regular
``SELECT DISTINCT..``) , it was usable to a limited extent in this way
previously (but wouldn't work in subqueries, for example). The column
targeting for unary expressions such as "DISTINCT <col>" has been improved
so that this case works again, and an additional improvement has been made
so that usage of this form in a subquery at least generates valid SQL which
was not the case previously.
The change additionally enhances the ability to target elements in
``row._mapping`` based on SQL expression objects in ORM-enabled
SELECT statements, including whether the statement was invoked by
``connection.execute()`` or ``session.execute()``.
.. change::
:tags: bug, orm, asyncio
:tickets: 5998
The API for :meth:`_asyncio.AsyncSession.delete` is now an awaitable;
this method cascades along relationships which must be loaded in a
similar manner as the :meth:`_asyncio.AsyncSession.merge` method.
.. change::
:tags: usecase, postgresql, mysql, asyncio
:tickets: 5967
Added an ``asyncio.Lock()`` within SQLAlchemy's emulated DBAPI cursor,
local to the connection, for the asyncpg and aiomysql dialects for the
scope of the ``cursor.execute()`` and ``cursor.executemany()`` methods. The
rationale is to prevent failures and corruption for the case where the
connection is used in multiple awaitables at once.
While this use case can also occur with threaded code and non-asyncio
dialects, we anticipate this kind of use will be more common under asyncio,
as the asyncio API is encouraging of such use. It's definitely better to
use a distinct connection per concurrent awaitable however as concurrency
will not be achieved otherwise.
For the asyncpg dialect, this is so that the space between
the call to ``prepare()`` and ``fetch()`` is prevented from allowing
concurrent executions on the connection from causing interface error
exceptions, as well as preventing race conditions when starting a new
transaction. Other PostgreSQL DBAPIs are threadsafe at the connection level
so this intends to provide a similar behavior, outside the realm of server
side cursors.
For the aiomysql dialect, the mutex will provide safety such that
the statement execution and the result set fetch, which are two distinct
steps at the connection level, won't get corrupted by concurrent
executions on the same connection.
.. change::
:tags: bug, engine
:tickets: 6002
Improved engine logging to note ROLLBACK and COMMIT which is logged while
the DBAPI driver is in AUTOCOMMIT mode. These ROLLBACK/COMMIT are library
level and do not have any effect when AUTOCOMMIT is in effect, however it's
still worthwhile to log as these indicate where SQLAlchemy sees the
"transaction" demarcation.
.. change::
:tags: bug, regression, engine
:tickets: 6004
Fixed a regression where the "reset agent" of the connection pool wasn't
really being utilized by the :class:`_engine.Connection` when it were
closed, and also leading to a double-rollback scenario that was somewhat
wasteful. The newer architecture of the engine has been updated so that
the connection pool "reset-on-return" logic will be skipped when the
:class:`_engine.Connection` explicitly closes out the transaction before
returning the pool to the connection.
.. change::
:tags: bug, schema
:tickets: 5953
Deprecated all schema-level ``.copy()`` methods and renamed to
``_copy()``. These are not standard Python "copy()" methods as they
typically rely upon being instantiated within particular contexts
which are passed to the method as optional keyword arguments. The
:meth:`_schema.Table.tometadata` method is the public API that provides
copying for :class:`_schema.Table` objects.
.. change::
:tags: bug, ext
:tickets: 6020
The ``sqlalchemy.ext.mutable`` extension now tracks the "parents"
collection using the :class:`.InstanceState` associated with objects,
rather than the object itself. The latter approach required that the object
be hashable so that it can be inside of a ``WeakKeyDictionary``, which goes
against the behavioral contract of the ORM overall which is that ORM mapped
objects do not need to provide any particular kind of ``__hash__()`` method
and that unhashable objects are supported.
.. change::
:tags: bug, orm
:tickets: 5984
The unit of work process now turns off all "lazy='raise'" behavior
altogether when a flush is proceeding. While there are areas where the UOW
is sometimes loading things that aren't ultimately needed, the lazy="raise"
strategy is not helpful here as the user often does not have much control
or visibility into the flush process.
.. changelog::
```
### 1.4.0b3
```
:released: March 15, 2021
:released: February 15, 2021
.. change::
:tags: bug, orm
:tickets: 5933
Fixed issue in new 1.4/2.0 style ORM queries where a statement-level label
style would not be preserved in the keys used by result rows; this has been
applied to all combinations of Core/ORM columns / session vs. connection
etc. so that the linkage from statement to result row is the same in all
cases. As part of this change, the labeling of column expressions
in rows has been improved to retain the original name of the ORM
attribute even if used in a subquery.
.. change::
:tags: bug, sql
:tickets: 5924
Fixed bug where the "cartesian product" assertion was not correctly
accommodating for joins between tables that relied upon the use of LATERAL
to connect from a subquery to another subquery in the enclosing context.
.. change::
:tags: bug, sql
:tickets: 5934
Fixed 1.4 regression where the :meth:`_functions.Function.in_` method was
not covered by tests and failed to function properly in all cases.
.. change::
:tags: bug, engine, postgresql
:tickets: 5941
Continued with the improvement made as part of :ticket:`5653` to further
support bound parameter names, including those generated against column
names, for names that include colons, parenthesis, and question marks, as
well as improved test support, so that bound parameter names even if they
are auto-derived from column names should have no problem including for
parenthesis in psycopg2's "pyformat" style.
As part of this change, the format used by the asyncpg DBAPI adapter (which
is local to SQLAlchemy's asyncpg dialect) has been changed from using
"qmark" paramstyle to "format", as there is a standard and internally
supported SQL string escaping style for names that use percent signs with
"format" style (i.e. to double percent signs), as opposed to names that use
question marks with "qmark" style (where an escaping system is not defined
by pep-249 or Python).
.. seealso::
:ref:`change_5941`
.. change::
:tags: sql, usecase, postgresql, sqlite
:tickets: 5939
Enhance ``set_`` keyword of :class:`.OnConflictDoUpdate` to accept a
:class:`.ColumnCollection`, such as the ``.c.`` collection from a
:class:`Selectable`, or the ``.excluded`` contextual object.
.. change::
:tags: feature, orm
The ORM used in :term:`2.0 style` can now return ORM objects from the rows
returned by an UPDATE..RETURNING or INSERT..RETURNING statement, by
supplying the construct to :meth:`_sql.Select.from_statement` in an ORM
context.
.. seealso::
:ref:`orm_dml_returning_objects`
.. change::
:tags: bug, sql
:tickets: 5935
Fixed regression where use of an arbitrary iterable with the
:func:`_sql.select` function was not working, outside of plain lists. The
forwards/backwards compatibility logic here now checks for a wider range of
incoming "iterable" types including that a ``.c`` collection from a
selectable can be passed directly. Pull request compliments of Oliver Rice.
.. changelog::
```
### 1.4.0b2
```
:released: March 15, 2021
:released: February 3, 2021
.. change::
:tags: usecase, sql
:tickets: 5695
Multiple calls to "returning", e.g. :meth:`_sql.Insert.returning`,
may now be chained to add new columns to the RETURNING clause.
.. change::
:tags: bug, asyncio
:tickets: 5615
Adjusted the greenlet integration, which provides support for Python asyncio
in SQLAlchemy, to accommodate for the handling of Python ``contextvars``
(introduced in Python 3.7) for ``greenlet`` versions greater than 0.4.17.
Greenlet version 0.4.17 added automatic handling of contextvars in a
backwards-incompatible way; we've coordinated with the greenlet authors to
add a preferred API for this in versions subsequent to 0.4.17 which is now
supported by SQLAlchemy's greenlet integration. For greenlet versions prior
to 0.4.17 no behavioral change is needed, version 0.4.17 itself is blocked
from the dependencies.
.. change::
:tags: bug, engine, sqlite
:tickets: 5845
Fixed bug in the 2.0 "future" version of :class:`.Engine` where emitting
SQL during the :meth:`.EngineEvents.begin` event hook would cause a
re-entrant (recursive) condition due to autobegin, affecting among other
things the recipe documented for SQLite to allow for savepoints and
serializable isolation support.
.. change::
:tags: bug, orm, regression
:tickets: 5845
Fixed issue in new :class:`_orm.Session` similar to that of the
:class:`_engine.Connection` where the new "autobegin" logic could be
tripped into a re-entrant (recursive) state if SQL were executed within the
:meth:`.SessionEvents.after_transaction_create` event hook.
.. change::
:tags: sql
:tickets: 4757
Replace :meth:`_orm.Query.with_labels` and
:meth:`_sql.GenerativeSelect.apply_labels` with explicit getters and
setters :meth:`_sql.GenerativeSelect.get_label_style` and
:meth:`_sql.GenerativeSelect.set_label_style` to accommodate the three
supported label styles: :data:`_sql.LABEL_STYLE_DISAMBIGUATE_ONLY`,
:data:`_sql.LABEL_STYLE_TABLENAME_PLUS_COL`, and
:data:`_sql.LABEL_STYLE_NONE`.
In addition, for Core and "future style" ORM queries,
``LABEL_STYLE_DISAMBIGUATE_ONLY`` is now the default label style. This
style differs from the existing "no labels" style in that labeling is
applied in the case of column name conflicts; with ``LABEL_STYLE_NONE``, a
duplicate column name is not accessible via name in any case.
For cases where labeling is significant, namely that the ``.c`` collection
of a subquery is able to refer to all columns unambiguously, the behavior
of ``LABEL_STYLE_DISAMBIGUATE_ONLY`` is now sufficient for all
SQLAlchemy features across Core and ORM which involve this behavior.
Result set rows since SQLAlchemy 1.0 are usually aligned with column
constructs positionally.
For legacy ORM queries using :class:`_query.Query`, the table-plus-column
names labeling style applied by ``LABEL_STYLE_TABLENAME_PLUS_COL``
continues to be used so that existing test suites and logging facilities
see no change in behavior by default.
.. change::
:tags: bug, orm, unitofwork
:tickets: 5735
Improved the unit of work topological sorting system such that the
toplogical sort is now deterministic based on the sorting of the input set,
which itself is now sorted at the level of mappers, so that the same inputs
of affected mappers should produce the same output every time, among
mappers / tables that don't have any dependency on each other. This further
reduces the chance of deadlocks as can be observed in a flush that UPDATEs
among multiple, unrelated tables such that row locks are generated.
.. change::
:tags: changed, orm
:tickets: 5897
Mapper "configuration", which occurs within the
:func:`_orm.configure_mappers` function, is now organized to be on a
per-registry basis. This allows for example the mappers within a certain
declarative base to be configured, but not those of another base that is
also present in memory. The goal is to provide a means of reducing
application startup time by only running the "configure" process for sets
of mappers that are needed. This also adds the
:meth:`_orm.registry.configure` method that will run configure for the
mappers local in a particular registry only.
.. change::
:tags: bug, orm
:tickets: 5702
Fixed regression where the :paramref:`.Bundle.single_entity` flag would
take effect for a :class:`.Bundle` even though it were not set.
Additionally, this flag is legacy as it only makes sense for the
:class:`_orm.Query` object and not 2.0 style execution. a deprecation
warning is emitted when used with new-style execution.
.. change::
:tags: bug, sql
:tickets: 5858
Fixed issue in new :meth:`_sql.Select.join` method where chaining from the
current JOIN wasn't looking at the right state, causing an expression like
"FROM a JOIN b <onclause>, b JOIN c <onclause>" rather than
"FROM a JOIN b <onclause> JOIN c <onclause>".
.. change::
:tags: usecase, sql
Added :meth:`_sql.Select.outerjoin_from` method to complement
:meth:`_sql.Select.join_from`.
.. change::
:tags: usecase, sql
:tickets: 5888
Adjusted the "literal_binds" feature of :class:`_sql.Compiler` to render
NULL for a bound parameter that has ``None`` as the value, either
explicitly passed or omitted. The previous error message "bind parameter
without a renderable value" is removed, and a missing or ``None`` value
will now render NULL in all cases. Previously, rendering of NULL was
starting to happen for DML statements due to internal refactorings, but was
not explicitly part of test coverage, which it now is.
While no error is raised, when the context is within that of a column
comparison, and the operator is not "IS"/"IS NOT", a warning is emitted
that this is not generally useful from a SQL perspective.
.. change::
:tags: bug, orm
:tickets: 5750
Fixed regression where creating an :class:`_orm.aliased` constru
pyup-bot
commented
3 years ago
This PR updates SQLAlchemy from 1.3.10 to 1.4.10.
Changelog
### 1.4.10 ``` :released: April 20, 2021 .. change:: :tags: bug, declarative, regression :tickets: 6291 Fixed :func:`_declarative.instrument_declarative` that called a non existing registry method. .. change:: :tags: bug, orm :tickets: 6320 Fixed bug in new :func:`_orm.with_loader_criteria` feature where using a mixin class with :func:`_orm.declared_attr` on an attribute that were accessed inside the custom lambda would emit a warning regarding using an unmapped declared attr, when the lambda callable were first initialized. This warning is now prevented using special instrumentation for this lambda initialization step. .. change:: :tags: usecase, schema, mssql :tickets: 6306 The :paramref:`_types.DateTime.timezone` parameter when set to ``True`` will now make use of the ``DATETIMEOFFSET`` column type with SQL Server when used to emit DDL, rather than ``DATETIME`` where the flag was silently ignored. .. change:: :tags: orm, bug, regression :tickets: 6326 Fixed additional regression caused by the "eagerloaders on refresh" feature added in :ticket:`1763` where the refresh operation historically would set ``populate_existing``, which given the new feature now overwrites pending changes on eagerly loaded objects when autoflush is false. The populate_existing flag has been turned off for this case and a more specific method used to ensure the correct attributes refreshed. .. change:: :tags: bug, orm, result :tickets: 6299 Fixed an issue when using 2.0 style execution that prevented using :meth:`_result.Result.scalar_one` or :meth:`_result.Result.scalar_one_or_none` after calling :meth:`_result.Result.unique`, for the case where the ORM is returning a single-element row in any case. .. change:: :tags: bug, sql :tickets: 6327 Fixed issue in SQL compiler where the bound parameters set up for a :class:`.Values` construct wouldn't be positionally tracked correctly if inside of a :class:`_sql.CTE`, affecting database drivers that support VALUES + ctes and use positional parameters such as SQL Server in particular as well as asyncpg. The fix also repairs support for compiler flags such as ``literal_binds``. .. change:: :tags: bug, schema :tickets: 6287 Fixed issue where :func:`_functions.next_value` was not deriving its type from the corresponding :class:`_schema.Sequence`, instead hardcoded to :class:`_types.Integer`. The specific numeric type is now used. .. change:: :tags: bug, mypy :tickets: 6255 Fixed issue where mypy plugin would not correctly interpret an explicit :class:`_orm.Mapped` annotation in conjunction with a :func:`_orm.relationship` that refers to a class by string name; the correct annotation would be downgraded to a less specific one leading to typing errors. .. change:: :tags: bug, sql :tickets: 6256 Repaired and solidified issues regarding custom functions and other arbitrary expression constructs which within SQLAlchemy's column labeling mechanics would seek to use ``str(obj)`` to get a string representation to use as an anonymous column name in the ``.c`` collection of a subquery. This is a very legacy behavior that performs poorly and leads to lots of issues, so has been revised to no longer perform any compilation by establishing specific methods on :class:`.FunctionElement` to handle this case, as SQL functions are the only use case that it came into play. An effect of this behavior is that an unlabeled column expression with no derivable name will be given an arbitrary label starting with the prefix ``"_no_label"`` in the ``.c`` collection of a subquery; these were previously being represented either as the generic stringification of that expression, or as an internal symbol. .. change:: :tags: usecase, orm :ticketS: 6301 Altered some of the behavior repaired in :ticket:`6232` where the ``immediateload`` loader strategy no longer goes into recursive loops; the modification is that an eager load (joinedload, selectinload, or subqueryload) from A->bs->B which then states ``immediateload`` for a simple manytoone B->a->A that's in the identity map will populate the B->A, so that this attribute is back-populated when the collection of A/A.bs are loaded. This allows the objects to be functional when detached. .. changelog:: ``` ### 1.4.9 ``` :released: April 17, 2021 .. change:: :tags: bug, sql, regression :tickets: 6290 Fixed regression where an empty in statement on a tuple would result in an error when compiled with the option ``literal_binds=True``. .. change:: :tags: bug, regression, orm, performance, sql :tickets: 6304 Fixed a critical performance issue where the traversal of a :func:`_sql.select` construct would traverse a repetitive product of the represented FROM clauses as they were each referred towards by columns in the columns clause; for a series of nested subqueries with lots of columns this could cause a large delay and significant memory growth. This traversal is used by a wide variety of SQL and ORM functions, including by the ORM :class:`_orm.Session` when it's configured to have "table-per-bind", which while this is not a common use case, it seems to be what Flask-SQLAlchemy is hardcoded as using, so the issue impacts Flask-SQLAlchemy users. The traversal has been repaired to uniqify on FROM clauses which was effectively what would happen implicitly with the pre-1.4 architecture. .. change:: :tags: bug, postgresql, sql, regression :tickets: 6303 Fixed an argument error in the default and PostgreSQL compilers that would interfere with an UPDATE..FROM or DELETE..FROM..USING statement that was then SELECTed from as a CTE. .. change:: :tags: bug, orm, regression :tickets: 6272 Fixed regression where an attribute that is mapped to a :func:`_orm.synonym` could not be used in column loader options such as :func:`_orm.load_only`. .. change:: :tags: usecase, orm :tickets: 6267 Established support for :func:`_orm.synoynm` in conjunction with hybrid property, assocaitionproxy is set up completely, including that synonyms can be established linking to these constructs which work fully. This is a behavior that was semi-explicitly disallowed previously, however since it did not fail in every scenario, explicit support for assoc proxy and hybrids has been added. .. changelog:: ``` ### 1.4.8 ``` :released: April 15, 2021 .. change:: :tags: change, mypy Updated Mypy plugin to only use the public plugin interface of the semantic analyzer. .. change:: :tags: bug, mssql, regression :tickets: 6265 Fixed an additional regression in the same area as that of :ticket:`6173`, :ticket:`6184`, where using a value of 0 for OFFSET in conjunction with LIMIT with SQL Server would create a statement using "TOP", as was the behavior in 1.3, however due to caching would then fail to respond accordingly to other values of OFFSET. If the "0" wasn't first, then it would be fine. For the fix, the "TOP" syntax is now only emitted if the OFFSET value is omitted entirely, that is, :meth:`_sql.Select.offset` is not used. Note that this change now requires that if the "with_ties" or "percent" modifiers are used, the statement can't specify an OFFSET of zero, it now needs to be omitted entirely. .. change:: :tags: bug, engine The :meth:`_engine.Dialect.has_table` method now raises an informative exception if a non-Connection is passed to it, as this incorrect behavior seems to be common. This method is not intended for external use outside of a dialect. Please use the :meth:`.Inspector.has_table` method or for cross-compatibility with older SQLAlchemy versions, the :meth:`_engine.Engine.has_table` method. .. change:: :tags: bug, regression, sql :tickets: 6249 Fixed regression where the :class:`_sql.BindParameter` object would not properly render for an IN expression (i.e. using the "post compile" feature in 1.4) if the object were copied from either an internal cloning operation, or from a pickle operation, and the parameter name contained spaces or other special characters. .. change:: :tags: bug, mypy :tickets: 6205 Revised the fix for ``OrderingList`` from version 1.4.7 which was testing against the incorrect API. .. change:: :tags: bug, asyncio :tickets: 6220 Fix typo that prevented setting the ``bind`` attribute of an :class:`_asyncio.AsyncSession` to the correct value. .. change:: :tags: feature, sql :tickets: 3314 The tuple returned by :attr:`.CursorResult.inserted_primary_key` is now a :class:`_result.Row` object with a named tuple interface on top of the existing tuple interface. .. change:: :tags: bug, regression, sql, sqlite :tickets: 6254 Fixed regression where the introduction of the INSERT syntax "INSERT... VALUES (DEFAULT)" was not supported on some backends that do however support "INSERT..DEFAULT VALUES", including SQLite. The two syntaxes are now each individually supported or non-supported for each dialect, for example MySQL supports "VALUES (DEFAULT)" but not "DEFAULT VALUES". Support for Oracle has also been enabled. .. change:: :tags: bug, regression, orm :tickets: 6259 Fixed a cache leak involving the :func:`_orm.with_expression` loader option, where the given SQL expression would not be correctly considered as part of the cache key. Additionally, fixed regression involving the corresponding :func:`_orm.query_expression` feature. While the bug technically exists in 1.3 as well, it was not exposed until 1.4. The "default expr" value of ``null()`` would be rendered when not needed, and additionally was also not adapted correctly when the ORM rewrites statements such as when using joined eager loading. The fix ensures "singleton" expressions like ``NULL`` and ``true`` aren't "adapted" to refer to columns in ORM statements, and additionally ensures that a :func:`_orm.query_expression` with no default expression doesn't render in the statement if a :func:`_orm.with_expression` isn't used. .. change:: :tags: bug, orm :tickets: 6252 Fixed issue in the new feature of :meth:`_orm.Session.refresh` introduced by :ticket:`1763` where eagerly loaded relationships are also refreshed, where the ``lazy="raise"`` and ``lazy="raise_on_sql"`` loader strategies would interfere with the :func:`_orm.immediateload` loader strategy, thus breaking the feature for relationships that were loaded with :func:`_orm.selectinload`, :func:`_orm.subqueryload` as well. .. changelog:: ``` ### 1.4.7 ``` :released: April 9, 2021 .. change:: :tags: bug, sql, regression :tickets: 6222 Enhanced the "expanding" feature used for :meth:`_sql.ColumnOperators.in_` operations to infer the type of expression from the right hand list of elements, if the left hand side does not have any explicit type set up. This allows the expression to support stringification among other things. In 1.3, "expanding" was not automatically used for :meth:`_sql.ColumnOperators.in_` expressions, so in that sense this change fixes a behavioral regression. .. change:: :tags: bug, mypy Fixed issue in Mypy plugin where the plugin wasn’t inferring the correct type for columns of subclasses that don’t directly descend from ``TypeEngine``, in particular that of ``TypeDecorator`` and ``UserDefinedType``. .. change:: :tags: bug, orm, regression :tickets: 6221 Fixed regression where the :func:`_orm.subqueryload` loader strategy would fail to correctly accommodate sub-options, such as a :func:`_orm.defer` option on a column, if the "path" of the subqueryload were more than one level deep. .. change:: :tags: bug, sql Fixed the "stringify" compiler to support a basic stringification of a "multirow" INSERT statement, i.e. one with multiple tuples following the VALUES keyword. .. change:: :tags: bug, orm, regression :tickets: 6211 Fixed regression where the :func:`_orm.merge_frozen_result` function relied upon by the dogpile.caching example was not included in tests and began failing due to incorrect internal arguments. .. change:: :tags: bug, engine, regression :tickets: 6218 Fixed up the behavior of the :class:`_result.Row` object when dictionary access is used upon it, meaning converting to a dict via ``dict(row)`` or accessing members using strings or other objects i.e. ``row["some_key"]`` works as it would with a dictionary, rather than raising ``TypeError`` as would be the case with a tuple, whether or not the C extensions are in place. This was originally supposed to emit a 2.0 deprecation warning for the "non-future" case using :class:`_result.LegacyRow`, and was to raise ``TypeError`` for the "future" :class:`_result.Row` class. However, the C version of :class:`_result.Row` was failing to raise this ``TypeError``, and to complicate matters, the :meth:`_orm.Session.execute` method now returns :class:`_result.Row` in all cases to maintain consistency with the ORM result case, so users who didn't have C extensions installed would see different behavior in this one case for existing pre-1.4 style code. Therefore, in order to soften the overall upgrade scheme as most users have not been exposed to the more strict behavior of :class:`_result.Row` up through 1.4.6, :class:`_result.LegacyRow` and :class:`_result.Row` both provide for string-key access as well as support for ``dict(row)``, in all cases emitting the 2.0 deprecation warning when ``SQLALCHEMY_WARN_20`` is enabled. The :class:`_result.Row` object still uses tuple-like behavior for ``__contains__``, which is probably the only noticeable behavioral change compared to :class:`_result.LegacyRow`, other than the removal of dictionary-style methods ``values()`` and ``items()``. .. change:: :tags: bug, regression, orm :tickets: 6233 Fixed critical regression where the :class:`_orm.Session` could fail to "autobegin" a new transaction when a flush occurred without an existing transaction in place, implicitly placing the :class:`_orm.Session` into legacy autocommit mode which commit the transaction. The :class:`_orm.Session` now has a check that will prevent this condition from occurring, in addition to repairing the flush issue. Additionally, scaled back part of the change made as part of :ticket:`5226` which can run autoflush during an unexpire operation, to not actually do this in the case of a :class:`_orm.Session` using legacy :paramref:`_orm.Session.autocommit` mode, as this incurs a commit within a refresh operation. .. change:: :tags: change, tests Added a new flag to :class:`.DefaultDialect` called ``supports_schemas``; third party dialects may set this flag to ``False`` to disable SQLAlchemy's schema-level tests when running the test suite for a third party dialect. .. change:: :tags: bug, regression, schema :tickets: 6216 Fixed regression where usage of a token in the :paramref:`_engine.Connection.execution_options.schema_translate_map` dictionary which contained special characters such as braces would fail to be substituted properly. Use of square bracket characters ``[]`` is now explicitly disallowed as these are used as a delimiter character in the current implementation. .. change:: :tags: bug, regression, orm :tickets: 6215 Fixed regression where the ORM compilation scheme would assume the function name of a hybrid property would be the same as the attribute name in such a way that an ``AttributeError`` would be raised, when it would attempt to determine the correct name for each element in a result tuple. A similar issue exists in 1.3 but only impacts the names of tuple rows. The fix here adds a check that the hybrid's function name is actually present in the ``__dict__`` of the class or its superclasses before assigning this name; otherwise, the hybrid is considered to be "unnamed" and ORM result tuples will use the naming scheme of the underlying expression. .. change:: :tags: bug, orm, regression :tickets: 6232 Fixed critical regression caused by the new feature added as part of :ticket:`1763`, eager loaders are invoked on unexpire operations. The new feature makes use of the "immediateload" eager loader strategy as a substitute for a collection loading strategy, which unlike the other "post-load" strategies was not accommodating for recursive invocations between mutually-dependent relationships, leading to recursion overflow errors. .. changelog:: ``` ### 1.4.6 ``` :released: April 6, 2021 .. change:: :tags: bug, sql, regression, oracle, mssql :tickets: 6202 Fixed further regressions in the same area as that of :ticket:`6173` released in 1.4.5, where a "postcompile" parameter, again most typically those used for LIMIT/OFFSET rendering in Oracle and SQL Server, would fail to be processed correctly if the same parameter rendered in multiple places in the statement. .. change:: :tags: bug, orm, regression :tickets: 6203 Fixed regression where a deprecated form of :meth:`_orm.Query.join` were used, passing a series of entities to join from without any ON clause in a single :meth:`_orm.Query.join` call, would fail to function correctly. .. change:: :tags: bug, mypy :tickets: 6147 Applied a series of refactorings and fixes to accommodate for Mypy "incremental" mode across multiple files, which previously was not taken into account. In this mode the Mypy plugin has to accommodate Python datatypes expressed in other files coming in with less information than they have on a direct run. Additionally, a new decorator :func:`_orm.declarative_mixin` is added, which is necessary for the Mypy plugin to be able to definifitely identify a Declarative mixin class that is otherwise not used inside a particular Python file. .. seealso:: :ref:`mypy_declarative_mixins` .. change:: :tags: bug, mypy :tickets: 6205 Fixed issue where the Mypy plugin would fail to interpret the "collection_class" of a relationship if it were a callable and not a class. Also improved type matching and error reporting for collection-oriented relationships. .. change:: :tags: bug, sql :tickets: 6204 Executing a :class:`_sql.Subquery` using :meth:`_engine.Connection.execute` is deprecated and will emit a deprecation warning; this use case was an oversight that should have been removed from 1.4. The operation will now execute the underlying :class:`_sql.Select` object directly for backwards compatibility. Similarly, the :class:`_sql.CTE` class is also not appropriate for execution. In 1.3, attempting to execute a CTE would result in an invalid "blank" SQL statement being executed; since this use case was not working it now raises :class:`_exc.ObjectNotExecutableError`. Previously, 1.4 was attempting to execute the CTE as a statement however it was working only erratically. .. change:: :tags: bug, regression, orm :tickets: 6206 Fixed critical regression where the :meth:`_orm.Query.yield_per` method in the ORM would set up the internal :class:`_engine.Result` to yield chunks at a time, however made use of the new :meth:`_engine.Result.unique` method which uniques across the entire result. This would lead to lost rows since the ORM is using ``id(obj)`` as the uniquing function, which leads to repeated identifiers for new objects as already-seen objects are garbage collected. 1.3's behavior here was to "unique" across each chunk, which does not actually produce "uniqued" results when results are yielded in chunks. As the :meth:`_orm.Query.yield_per` method is already explicitly disallowed when joined eager loading is in place, which is the primary rationale for the "uniquing" feature, the "uniquing" feature is now turned off entirely when :meth:`_orm.Query.yield_per` is used. This regression only applies to the legacy :class:`_orm.Query` object; when using :term:`2.0 style` execution, "uniquing" is not automatically applied. To prevent the issue from arising from explicit use of :meth:`_engine.Result.unique`, an error is now raised if rows are fetched from a "uniqued" ORM-level :class:`_engine.Result` if any :ref:`yield per <orm_queryguide_yield_per>` API is also in use, as the purpose of ``yield_per`` is to allow for arbitrarily large numbers of rows, which cannot be uniqued in memory without growing the number of entries to fit the complete result size. .. change:: :tags: usecase, asyncio, postgresql :tickets: 6199 Added accessors ``.sqlstate`` and synonym ``.pgcode`` to the ``.orig`` attribute of the SQLAlchemy exception class raised by the asyncpg DBAPI adapter, that is, the intermediary exception object that wraps on top of that raised by the asyncpg library itself, but below the level of the SQLAlchemy dialect. .. changelog:: ``` ### 1.4.5 ``` :released: April 2, 2021 .. change:: :tags: bug, sql, postgresql :tickets: 6183 Fixed bug in new :meth:`_functions.FunctionElement.render_derived` feature where column names rendered out explicitly in the alias SQL would not have proper quoting applied for case sensitive names and other non-alphanumeric names. .. change:: :tags: bug, regression, orm :tickets: 6172 Fixed regression where the :func:`_orm.joinedload` loader strategy would not successfully joinedload to a mapper that is mapper against a :class:`.CTE` construct. .. change:: :tags: bug, regression, sql :tickets: 6181 Fixed regression where use of the :meth:`.Operators.in_` method with a :class:`_sql.Select` object against a non-table-bound column would produce an ``AttributeError``, or more generally using a :class:`_sql.ScalarSelect` that has no datatype in a binary expression would produce invalid state. .. change:: :tags: bug, mypy :tickets: sqlalchemy/sqlalchemy2-stubs/14 Fixed issue in mypy plugin where newly added support for :func:`_orm.as_declarative` needed to more fully add the ``DeclarativeMeta`` class to the mypy interpreter's state so that it does not result in a name not found error; additionally improves how global names are setup for the plugin including the ``Mapped`` name. .. change:: :tags: bug, mysql, regression :tickets: 6163 Fixed regression in the MySQL dialect where the reflection query used to detect if a table exists would fail on very old MySQL 5.0 and 5.1 versions. .. change:: :tags: bug, sql :tickets: 6184 Added a new flag to the :class:`_engine.Dialect` class called :attr:`_engine.Dialect.supports_statement_cache`. This flag now needs to be present directly on a dialect class in order for SQLAlchemy's :ref:`query cache <sql_caching>` to take effect for that dialect. The rationale is based on discovered issues such as :ticket:`6173` revealing that dialects which hardcode literal values from the compiled statement, often the numerical parameters used for LIMIT / OFFSET, will not be compatible with caching until these dialects are revised to use the parameters present in the statement only. For third party dialects where this flag is not applied, the SQL logging will show the message "dialect does not support caching", indicating the dialect should seek to apply this flag once they have verified that no per-statement literal values are being rendered within the compilation phase. .. seealso:: :ref:`engine_thirdparty_caching` .. change:: :tags: bug, postgresql :tickets: 6099 Fixed typo in the fix for :ticket:`6099` released in 1.4.4 that completely prevented this change from working correctly, i.e. the error message did not match what was actually emitted by pg8000. .. change:: :tags: bug, orm, regression :tickets: 6171 Scaled back the warning message added in :ticket:`5171` to not warn for overlapping columns in an inheritance scenario where a particular relationship is local to a subclass and therefore does not represent an overlap. .. change:: :tags: bug, regression, oracle :tickets: 6173 Fixed critical regression where the Oracle compiler would not maintain the correct parameter values in the LIMIT/OFFSET for a select due to a caching issue. .. change:: :tags: bug, postgresql :tickets: 6170 Fixed issue where the PostgreSQL :class:`.PGInspector`, when generated against an :class:`_engine.Engine`, would fail for ``.get_enums()``, ``.get_view_names()``, ``.get_foreign_table_names()`` and ``.get_table_oid()`` when used against a "future" style engine and not the connection directly. .. change:: :tags: bug, schema :tickets: 6146 Introduce a new parameter :paramref:`_types.Enum.omit_aliases` in :class:`_types.Enum` type allow filtering aliases when using a pep435 Enum. Previous versions of SQLAlchemy kept aliases in all cases, creating database enum type with additional states, meaning that they were treated as different values in the db. For backward compatibility this flag defaults to ``False`` in the 1.4 series, but will be switched to ``True`` in a future version. A deprecation warning is raise if this flag is not specified and the passed enum contains aliases. .. change:: :tags: bug, mssql :tickets: 6163 Fixed a regression in MSSQL 2012+ that prevented the order by clause to be rendered when ``offset=0`` is used in a subquery. .. change:: :tags: bug, asyncio :tickets: 6166 Fixed issue where the asyncio extension could not be loaded if running Python 3.6 with the backport library of ``contextvars`` installed. .. changelog:: ``` ### 1.4.4 ``` :released: March 30, 2021 .. change:: :tags: bug, misc Adjusted the usage of the ``importlib_metadata`` library for loading setuptools entrypoints in order to accommodate for some deprecation changes. .. change:: :tags: bug, postgresql :tickets: 6099 Modified the ``is_disconnect()`` handler for the pg8000 dialect, which now accommodates for a new ``InterfaceError`` emitted by pg8000 1.19.0. Pull request courtesy Hamdi Burak Usul. .. change:: :tags: bug, orm :tickets: 6139 Fixed critical issue in the new :meth:`_orm.PropComparator.and_` feature where loader strategies that emit secondary SELECT statements such as :func:`_orm.selectinload` and :func:`_orm.lazyload` would fail to accommodate for bound parameters in the user-defined criteria in terms of the current statement being executed, as opposed to the cached statement, causing stale bound values to be used. This also adds a warning for the case where an object that uses :func:`_orm.lazyload` in conjunction with :meth:`_orm.PropComparator.and_` is attempted to be serialized; the loader criteria cannot reliably be serialized and deserialized and eager loading should be used for this case. .. change:: :tags: bug, engine :tickets: 6138 Repair wrong arguments to exception handling method in CursorResult. .. change:: :tags: bug, regression, orm :tickets: 6144 Fixed missing method :meth:`_orm.Session.get` from the :class:`_orm.ScopedSession` interface. .. change:: :tags: usecase, engine :tickets: 6155 Modified the context manager used by :class:`_engine.Transaction` so that an "already detached" warning is not emitted by the ending of the context manager itself, if the transaction were already manually rolled back inside the block. This applies to regular transactions, savepoint transactions, and legacy "marker" transactions. A warning is still emitted if the ``.rollback()`` method is called explicitly more than once. .. changelog:: ``` ### 1.4.3 ``` :released: March 25, 2021 .. change:: :tags: bug, orm :tickets: 6069 Fixed a bug where python 2.7.5 (default on CentOS 7) wasn't able to import sqlalchemy, because on this version of Python ``exec "statement"`` and ``exec("statement")`` do not behave the same way. The compatibility ``exec_()`` function was used instead. .. change:: :tags: sqlite, feature, asyncio :tickets: 5920 Added support for the aiosqlite database driver for use with the SQLAlchemy asyncio extension. .. seealso:: :ref:`aiosqlite` .. change:: :tags: bug, regression, orm, declarative :tickets: 6128 Fixed regression where the ``.metadata`` attribute on a per class level would not be honored, breaking the use case of per-class-hierarchy :class:`.schema.MetaData` for abstract declarative classes and mixins. .. seealso:: :ref:`declarative_metadata` .. change:: :tags: bug, mypy Added support for the Mypy extension to correctly interpret a declarative base class that's generated using the :func:`_orm.as_declarative` function as well as the :meth:`_orm.registry.as_declarative_base` method. .. change:: :tags: bug, mypy :tickets: 6109 Fixed bug in Mypy plugin where the Python type detection for the :class:`_types.Boolean` column type would produce an exception; additionally implemented support for :class:`_types.Enum`, including detection of a string-based enum vs. use of Python ``enum.Enum``. .. change:: :tags: bug, reflection, postgresql :tickets: 6129 Fixed reflection of identity columns in tables with mixed case names in PostgreSQL. .. change:: :tags: bug, sqlite, regression :tickets: 5848 Repaired the ``pysqlcipher`` dialect to connect correctly which had regressed in 1.4, and added test + CI support to maintain the driver in working condition. The dialect now imports the ``sqlcipher3`` module for Python 3 by default before falling back to ``pysqlcipher3`` which is documented as now being unmaintained. .. seealso:: :ref:`pysqlcipher` .. change:: :tags: bug, orm :tickets: 6060 Fixed bug where ORM queries using a correlated subquery in conjunction with :func:`_orm.column_property` would fail to correlate correctly to an enclosing subquery or to a CTE when :meth:`_sql.Select.correlate_except` were used in the property to control correlation, in cases where the subquery contained the same selectables as ones within the correlated subquery that were intended to not be correlated. .. change:: :tags: bug, orm :tickets: 6131 Fixed bug where combinations of the new "relationship with criteria" feature could fail in conjunction with features that make use of the new "lambda SQL" feature, including loader strategies such as selectinload and lazyload, for more complicated scenarios such as polymorphic loading. .. change:: :tags: bug, orm :tickets: 6124 Repaired support so that the :meth:`_sql.ClauseElement.params` method can work correctly with a :class:`_sql.Select` object that includes joins across ORM relationship structures, which is a new feature in 1.4. .. change:: :tags: bug, engine, regression :tickets: 6119 Restored the :class:`_engine.ResultProxy` name back to the ``sqlalchemy.engine`` namespace. This name refers to the :class:`_engine.LegacyCursorResult` object. .. change:: :tags: bug, orm :tickets: 6115 Fixed issue where a "removed in 2.0" warning were generated internally by the relationship loader mechanics. .. changelog:: ``` ### 1.4.2 ``` :released: March 19, 2021 .. change:: :tags: bug, orm, dataclasses :tickets: 6093 Fixed issue in new ORM dataclasses functionality where dataclass fields on an abstract base or mixin that contained column or other mapping constructs would not be mapped if they also included a "default" key within the dataclasses.field() object. .. change:: :tags: bug, regression, orm :tickets: 6088 Fixed regression where the :attr:`_orm.Query.selectable` accessor, which is a synonym for :meth:`_orm.Query.__clause_element__`, got removed, it's now restored. .. change:: :tags: bug, engine, regression Restored top level import for ``sqlalchemy.engine.reflection``. This ensures that the base :class:`_reflection.Inspector` class is properly registered so that :func:`_sa.inspect` works for third party dialects that don't otherwise import this package. .. change:: :tags: bug, regression, orm :tickets: 6086 Fixed regression where use of an unnamed SQL expression such as a SQL function would raise a column targeting error if the query itself were using joinedload for an entity and was also being wrapped in a subquery by the joinedload eager loading process. .. change:: :tags: bug, orm, regression :tickets: 6092 Fixed regression where the :meth:`_orm.Query.filter_by` method would fail to locate the correct source entity if the :meth:`_orm.Query.join` method had been used targeting an entity without any kind of ON clause. .. change:: :tags: postgresql, usecase :tickets: 6982 Rename the column name used by a reflection query that used a reserved word in some postgresql compatible databases. .. change:: :tags: usecase, orm, dataclasses :tickets: 6100 Added support for the :class:`_orm.declared_attr` object to work in the context of dataclass fields. .. seealso:: :ref:`orm_declarative_dataclasses_mixin` .. change:: :tags: bug, sql, regression :tickets: 6101 Fixed issue where using a ``func`` that includes dotted packagenames would fail to be cacheable by the SQL caching system due to a Python list of names that needed to be a tuple. .. change:: :tags: bug, regression, orm :tickets: 6095 Fixed regression where the SQL compilation of a :class:`.Function` would not work correctly if the object had been "annotated", which is an internal memoization process used mostly by the ORM. In particular it could affect ORM lazy loads which make greater use of this feature in 1.4. .. change:: :tags: bug, sql, regression :tickets: 6097 Fixed regression in the :func:`_sql.case` construct, where the "dictionary" form of argument specification failed to work correctly if it were passed positionally, rather than as a "whens" keyword argument. .. change:: :tags: bug, orm :tickets: 6090 Fixed regression where the :class:`.ConcreteBase` would fail to map at all when a mapped column name overlapped with the discriminator column name, producing an assertion error. The use case here did not function correctly in 1.3 as the polymorphic union would produce a query that ignored the discriminator column entirely, while emitting duplicate column warnings. As 1.4's architecture cannot easily reproduce this essentially broken behavior of 1.3 at the ``select()`` level right now, the use case now raises an informative error message instructing the user to use the ``.ConcreteBase._concrete_discriminator_name`` attribute to resolve the conflict. To assist with this configuration, ``.ConcreteBase._concrete_discriminator_name`` may be placed on the base class only where it will be automatically used by subclasses; previously this was not the case. .. change:: :tags: bug, mypy :tickets: sqlalchemy/sqlalchemy2-stubs/2 Fixed issue in MyPy extension which crashed on detecting the type of a :class:`.Column` if the type were given with a module prefix like ``sa.Integer()``. .. changelog:: ``` ### 1.4.1 ``` :released: March 17, 2021 .. change:: :tags: bug, orm, regression :tickets: 6066 Fixed regression where producing a Core expression construct such as :func:`_sql.select` using ORM entities would eagerly configure the mappers, in an effort to maintain compatibility with the :class:`_orm.Query` object which necessarily does this to support many backref-related legacy cases. However, core :func:`_sql.select` constructs are also used in mapper configurations and such, and to that degree this eager configuration is more of an inconvenience, so eager configure has been disabled for the :func:`_sql.select` and other Core constructs in the absence of ORM loading types of functions such as :class:`_orm.Load`. The change maintains the behavior of :class:`_orm.Query` so that backwards compatibility is maintained. However, when using a :func:`_sql.select` in conjunction with ORM entities, a "backref" that isn't explicitly placed on one of the classes until mapper configure time won't be available unless :func:`_orm.configure_mappers` or the newer :func:`_orm.registry.configure` has been called elsewhere. Prefer using :paramref:`_orm.relationship.back_populates` for more explicit relationship configuration which does not have the eager configure requirement. .. change:: :tags: bug, mssql, regression :tickets: 6058 Fixed regression where a new setinputsizes() API that's available for pyodbc was enabled, which is apparently incompatible with pyodbc's fast_executemany() mode in the absence of more accurate typing information, which as of yet is not fully implemented or tested. The pyodbc dialect and connector has been modified so that setinputsizes() is not used at all unless the parameter ``use_setinputsizes`` is passed to the dialect, e.g. via :func:`_sa.create_engine`, at which point its behavior can be customized using the :meth:`.DialectEvents.do_setinputsizes` hook. .. seealso:: :ref:`mssql_pyodbc_setinputsizes` .. change:: :tags: bug, orm, regression :tickets: 6055 Fixed a critical regression in the relationship lazy loader where the SQL criteria used to fetch a related many-to-one object could go stale in relation to other memoized structures within the loader if the mapper had configuration changes, such as can occur when mappers are late configured or configured on demand, producing a comparison to None and returning no object. Huge thanks to Alan Hamlett for their help tracking this down late into the night. .. change:: :tags: bug, regression :tickets: 6068 Added back ``items`` and ``values`` to ``ColumnCollection`` class. The regression was introduced while adding support for duplicate columns in from clauses and selectable in ticket 4753. .. change:: :tags: bug, engine, regression :tickets: 6074 The Python ``namedtuple()`` has the behavior such that the names ``count`` and ``index`` will be served as tuple values if the named tuple includes those names; if they are absent, then their behavior as methods of ``collections.abc.Sequence`` is maintained. Therefore the :class:`_result.Row` and :class:`_result.LegacyRow` classes have been fixed so that they work in this same way, maintaining the expected behavior for database rows that have columns named "index" or "count". .. change:: :tags: bug, orm, regression :tickets: 6076 Fixed regression where the :meth:`_orm.Query.exists` method would fail to create an expression if the entity list of the :class:`_orm.Query` were an arbitrary SQL column expression. .. change:: :tags: bug, orm, regression :tickets: 6052 Fixed regression where calling upon :meth:`_orm.Query.count` in conjunction with a loader option such as :func:`_orm.joinedload` would fail to ignore the loader option. This is a behavior that has always been very specific to the :meth:`_orm.Query.count` method; an error is normally raised if a given :class:`_orm.Query` has options that don't apply to what it is returning. .. change:: :tags: bug, orm, declarative, regression :tickets: 6054 Fixed bug where user-mapped classes that contained an attribute named "registry" would cause conflicts with the new registry-based mapping system when using :class:`.DeclarativeMeta`. While the attribute remains something that can be set explicitly on a declarative base to be consumed by the metaclass, once located it is placed under a private class variable so it does not conflict with future subclasses that use the same name for other purposes. .. change:: :tags: bug, orm, regression :tickets: 6067 Fixed regression in :meth:`_orm.Session.identity_key`, including that the method and related methods were not covered by any unit test as well as that the method contained a typo preventing it from functioning correctly. .. changelog:: ``` ### 1.4.0 ``` :released: March 15, 2021 .. change:: :tags: bug, mssql :tickets: 5919 Fix a reflection error for MSSQL 2005 introduced by the reflection of filtered indexes. .. change:: :tags: feature, mypy :tickets: 4609 Rudimentary and experimental support for Mypy has been added in the form of a new plugin, which itself depends on new typing stubs for SQLAlchemy. The plugin allows declarative mappings in their standard form to both be compatible with Mypy as well as to provide typing support for mapped classes and instances. .. seealso:: :ref:`mypy_toplevel` .. change:: :tags: bug, sql :tickets: 6016 Fixed bug where the "percent escaping" feature that occurs with dialects that use the "format" or "pyformat" bound parameter styles was not enabled for the :meth:`_sql.Operators.op` and :class:`_sql.custom_op` constructs, for custom operators that use percent signs. The percent sign will now be automatically doubled based on the paramstyle as necessary. .. change:: :tags: bug, regression, sql :tickets: 5979 Fixed regression where the "unsupported compilation error" for unknown datatypes would fail to raise correctly. .. change:: :tags: ext, usecase :tickets: 5942 Add new parameter :paramref:`_automap.AutomapBase.prepare.reflection_options` to allow passing of :meth:`_schema.MetaData.reflect` options like ``only`` or dialect-specific reflection options like ``oracle_resolve_synonyms``. .. change:: :tags: change, sql Altered the compilation for the :class:`.CTE` construct so that a string is returned representing the inner SELECT statement if the :class:`.CTE` is stringified directly, outside of the context of an enclosing SELECT; This is the same behavior of :meth:`_sql.FromClause.alias` and :meth:`_sql.Select.subquery`. Previously, a blank string would be returned as the CTE is normally placed above a SELECT after that SELECT has been generated, which is generally misleading when debugging. .. change:: :tags: bug, orm :tickets: 5981 Fixed regression where the :paramref:`_orm.relationship.query_class` parameter stopped being functional for "dynamic" relationships. The ``AppenderQuery`` remains dependent on the legacy :class:`_orm.Query` class; users are encouraged to migrate from the use of "dynamic" relationships to using :func:`_orm.with_parent` instead. .. change:: :tags: bug, orm, regression :tickets: 6003 Fixed regression where :meth:`_orm.Query.join` would produce no effect if the query itself as well as the join target were against a :class:`_schema.Table` object, rather than a mapped class. This was part of a more systemic issue where the legacy ORM query compiler would not be correctly used from a :class:`_orm.Query` if the statement produced had not ORM entities present within it. .. change:: :tags: bug, regression, sql :tickets: 6008 Fixed regression where usage of the standalone :func:`_sql.distinct()` used in the form of being directly SELECTed would fail to be locatable in the result set by column identity, which is how the ORM locates columns. While standalone :func:`_sql.distinct()` is not oriented towards being directly SELECTed (use :meth:`_sql.select.distinct` for a regular ``SELECT DISTINCT..``) , it was usable to a limited extent in this way previously (but wouldn't work in subqueries, for example). The column targeting for unary expressions such as "DISTINCT <col>" has been improved so that this case works again, and an additional improvement has been made so that usage of this form in a subquery at least generates valid SQL which was not the case previously. The change additionally enhances the ability to target elements in ``row._mapping`` based on SQL expression objects in ORM-enabled SELECT statements, including whether the statement was invoked by ``connection.execute()`` or ``session.execute()``. .. change:: :tags: bug, orm, asyncio :tickets: 5998 The API for :meth:`_asyncio.AsyncSession.delete` is now an awaitable; this method cascades along relationships which must be loaded in a similar manner as the :meth:`_asyncio.AsyncSession.merge` method. .. change:: :tags: usecase, postgresql, mysql, asyncio :tickets: 5967 Added an ``asyncio.Lock()`` within SQLAlchemy's emulated DBAPI cursor, local to the connection, for the asyncpg and aiomysql dialects for the scope of the ``cursor.execute()`` and ``cursor.executemany()`` methods. The rationale is to prevent failures and corruption for the case where the connection is used in multiple awaitables at once. While this use case can also occur with threaded code and non-asyncio dialects, we anticipate this kind of use will be more common under asyncio, as the asyncio API is encouraging of such use. It's definitely better to use a distinct connection per concurrent awaitable however as concurrency will not be achieved otherwise. For the asyncpg dialect, this is so that the space between the call to ``prepare()`` and ``fetch()`` is prevented from allowing concurrent executions on the connection from causing interface error exceptions, as well as preventing race conditions when starting a new transaction. Other PostgreSQL DBAPIs are threadsafe at the connection level so this intends to provide a similar behavior, outside the realm of server side cursors. For the aiomysql dialect, the mutex will provide safety such that the statement execution and the result set fetch, which are two distinct steps at the connection level, won't get corrupted by concurrent executions on the same connection. .. change:: :tags: bug, engine :tickets: 6002 Improved engine logging to note ROLLBACK and COMMIT which is logged while the DBAPI driver is in AUTOCOMMIT mode. These ROLLBACK/COMMIT are library level and do not have any effect when AUTOCOMMIT is in effect, however it's still worthwhile to log as these indicate where SQLAlchemy sees the "transaction" demarcation. .. change:: :tags: bug, regression, engine :tickets: 6004 Fixed a regression where the "reset agent" of the connection pool wasn't really being utilized by the :class:`_engine.Connection` when it were closed, and also leading to a double-rollback scenario that was somewhat wasteful. The newer architecture of the engine has been updated so that the connection pool "reset-on-return" logic will be skipped when the :class:`_engine.Connection` explicitly closes out the transaction before returning the pool to the connection. .. change:: :tags: bug, schema :tickets: 5953 Deprecated all schema-level ``.copy()`` methods and renamed to ``_copy()``. These are not standard Python "copy()" methods as they typically rely upon being instantiated within particular contexts which are passed to the method as optional keyword arguments. The :meth:`_schema.Table.tometadata` method is the public API that provides copying for :class:`_schema.Table` objects. .. change:: :tags: bug, ext :tickets: 6020 The ``sqlalchemy.ext.mutable`` extension now tracks the "parents" collection using the :class:`.InstanceState` associated with objects, rather than the object itself. The latter approach required that the object be hashable so that it can be inside of a ``WeakKeyDictionary``, which goes against the behavioral contract of the ORM overall which is that ORM mapped objects do not need to provide any particular kind of ``__hash__()`` method and that unhashable objects are supported. .. change:: :tags: bug, orm :tickets: 5984 The unit of work process now turns off all "lazy='raise'" behavior altogether when a flush is proceeding. While there are areas where the UOW is sometimes loading things that aren't ultimately needed, the lazy="raise" strategy is not helpful here as the user often does not have much control or visibility into the flush process. .. changelog:: ``` ### 1.4.0b3 ``` :released: March 15, 2021 :released: February 15, 2021 .. change:: :tags: bug, orm :tickets: 5933 Fixed issue in new 1.4/2.0 style ORM queries where a statement-level label style would not be preserved in the keys used by result rows; this has been applied to all combinations of Core/ORM columns / session vs. connection etc. so that the linkage from statement to result row is the same in all cases. As part of this change, the labeling of column expressions in rows has been improved to retain the original name of the ORM attribute even if used in a subquery. .. change:: :tags: bug, sql :tickets: 5924 Fixed bug where the "cartesian product" assertion was not correctly accommodating for joins between tables that relied upon the use of LATERAL to connect from a subquery to another subquery in the enclosing context. .. change:: :tags: bug, sql :tickets: 5934 Fixed 1.4 regression where the :meth:`_functions.Function.in_` method was not covered by tests and failed to function properly in all cases. .. change:: :tags: bug, engine, postgresql :tickets: 5941 Continued with the improvement made as part of :ticket:`5653` to further support bound parameter names, including those generated against column names, for names that include colons, parenthesis, and question marks, as well as improved test support, so that bound parameter names even if they are auto-derived from column names should have no problem including for parenthesis in psycopg2's "pyformat" style. As part of this change, the format used by the asyncpg DBAPI adapter (which is local to SQLAlchemy's asyncpg dialect) has been changed from using "qmark" paramstyle to "format", as there is a standard and internally supported SQL string escaping style for names that use percent signs with "format" style (i.e. to double percent signs), as opposed to names that use question marks with "qmark" style (where an escaping system is not defined by pep-249 or Python). .. seealso:: :ref:`change_5941` .. change:: :tags: sql, usecase, postgresql, sqlite :tickets: 5939 Enhance ``set_`` keyword of :class:`.OnConflictDoUpdate` to accept a :class:`.ColumnCollection`, such as the ``.c.`` collection from a :class:`Selectable`, or the ``.excluded`` contextual object. .. change:: :tags: feature, orm The ORM used in :term:`2.0 style` can now return ORM objects from the rows returned by an UPDATE..RETURNING or INSERT..RETURNING statement, by supplying the construct to :meth:`_sql.Select.from_statement` in an ORM context. .. seealso:: :ref:`orm_dml_returning_objects` .. change:: :tags: bug, sql :tickets: 5935 Fixed regression where use of an arbitrary iterable with the :func:`_sql.select` function was not working, outside of plain lists. The forwards/backwards compatibility logic here now checks for a wider range of incoming "iterable" types including that a ``.c`` collection from a selectable can be passed directly. Pull request compliments of Oliver Rice. .. changelog:: ``` ### 1.4.0b2 ``` :released: March 15, 2021 :released: February 3, 2021 .. change:: :tags: usecase, sql :tickets: 5695 Multiple calls to "returning", e.g. :meth:`_sql.Insert.returning`, may now be chained to add new columns to the RETURNING clause. .. change:: :tags: bug, asyncio :tickets: 5615 Adjusted the greenlet integration, which provides support for Python asyncio in SQLAlchemy, to accommodate for the handling of Python ``contextvars`` (introduced in Python 3.7) for ``greenlet`` versions greater than 0.4.17. Greenlet version 0.4.17 added automatic handling of contextvars in a backwards-incompatible way; we've coordinated with the greenlet authors to add a preferred API for this in versions subsequent to 0.4.17 which is now supported by SQLAlchemy's greenlet integration. For greenlet versions prior to 0.4.17 no behavioral change is needed, version 0.4.17 itself is blocked from the dependencies. .. change:: :tags: bug, engine, sqlite :tickets: 5845 Fixed bug in the 2.0 "future" version of :class:`.Engine` where emitting SQL during the :meth:`.EngineEvents.begin` event hook would cause a re-entrant (recursive) condition due to autobegin, affecting among other things the recipe documented for SQLite to allow for savepoints and serializable isolation support. .. change:: :tags: bug, orm, regression :tickets: 5845 Fixed issue in new :class:`_orm.Session` similar to that of the :class:`_engine.Connection` where the new "autobegin" logic could be tripped into a re-entrant (recursive) state if SQL were executed within the :meth:`.SessionEvents.after_transaction_create` event hook. .. change:: :tags: sql :tickets: 4757 Replace :meth:`_orm.Query.with_labels` and :meth:`_sql.GenerativeSelect.apply_labels` with explicit getters and setters :meth:`_sql.GenerativeSelect.get_label_style` and :meth:`_sql.GenerativeSelect.set_label_style` to accommodate the three supported label styles: :data:`_sql.LABEL_STYLE_DISAMBIGUATE_ONLY`, :data:`_sql.LABEL_STYLE_TABLENAME_PLUS_COL`, and :data:`_sql.LABEL_STYLE_NONE`. In addition, for Core and "future style" ORM queries, ``LABEL_STYLE_DISAMBIGUATE_ONLY`` is now the default label style. This style differs from the existing "no labels" style in that labeling is applied in the case of column name conflicts; with ``LABEL_STYLE_NONE``, a duplicate column name is not accessible via name in any case. For cases where labeling is significant, namely that the ``.c`` collection of a subquery is able to refer to all columns unambiguously, the behavior of ``LABEL_STYLE_DISAMBIGUATE_ONLY`` is now sufficient for all SQLAlchemy features across Core and ORM which involve this behavior. Result set rows since SQLAlchemy 1.0 are usually aligned with column constructs positionally. For legacy ORM queries using :class:`_query.Query`, the table-plus-column names labeling style applied by ``LABEL_STYLE_TABLENAME_PLUS_COL`` continues to be used so that existing test suites and logging facilities see no change in behavior by default. .. change:: :tags: bug, orm, unitofwork :tickets: 5735 Improved the unit of work topological sorting system such that the toplogical sort is now deterministic based on the sorting of the input set, which itself is now sorted at the level of mappers, so that the same inputs of affected mappers should produce the same output every time, among mappers / tables that don't have any dependency on each other. This further reduces the chance of deadlocks as can be observed in a flush that UPDATEs among multiple, unrelated tables such that row locks are generated. .. change:: :tags: changed, orm :tickets: 5897 Mapper "configuration", which occurs within the :func:`_orm.configure_mappers` function, is now organized to be on a per-registry basis. This allows for example the mappers within a certain declarative base to be configured, but not those of another base that is also present in memory. The goal is to provide a means of reducing application startup time by only running the "configure" process for sets of mappers that are needed. This also adds the :meth:`_orm.registry.configure` method that will run configure for the mappers local in a particular registry only. .. change:: :tags: bug, orm :tickets: 5702 Fixed regression where the :paramref:`.Bundle.single_entity` flag would take effect for a :class:`.Bundle` even though it were not set. Additionally, this flag is legacy as it only makes sense for the :class:`_orm.Query` object and not 2.0 style execution. a deprecation warning is emitted when used with new-style execution. .. change:: :tags: bug, sql :tickets: 5858 Fixed issue in new :meth:`_sql.Select.join` method where chaining from the current JOIN wasn't looking at the right state, causing an expression like "FROM a JOIN b <onclause>, b JOIN c <onclause>" rather than "FROM a JOIN b <onclause> JOIN c <onclause>". .. change:: :tags: usecase, sql Added :meth:`_sql.Select.outerjoin_from` method to complement :meth:`_sql.Select.join_from`. .. change:: :tags: usecase, sql :tickets: 5888 Adjusted the "literal_binds" feature of :class:`_sql.Compiler` to render NULL for a bound parameter that has ``None`` as the value, either explicitly passed or omitted. The previous error message "bind parameter without a renderable value" is removed, and a missing or ``None`` value will now render NULL in all cases. Previously, rendering of NULL was starting to happen for DML statements due to internal refactorings, but was not explicitly part of test coverage, which it now is. While no error is raised, when the context is within that of a column comparison, and the operator is not "IS"/"IS NOT", a warning is emitted that this is not generally useful from a SQL perspective. .. change:: :tags: bug, orm :tickets: 5750 Fixed regression where creating an :class:`_orm.aliased` constru