NewYour coding agent can read the release notes before it upgrades.Set up the MCP server →
PyPI, #86 most downloaded on PyPI
Database Abstraction Library
Latest stable
2.1.8
Last release 12 days ago
04 Nov 2026
Ships fairly regularly
a new release about every 2 weeks
Nearly every release is documented
notes for 60 of the last 60 stable releases
3 versions withdrawn
withdrawn after publishing
21 years old
342 releases · first in 2006
Drag across the ruler to compare, or tap a release to open it.
…unwanted "automatic reconnect" feature, is being deprecated in MySQL drivers and backends, and is producing warnings for some versions of MySQL's nati…
Released: November 2, 2023
[orm] [usecase] Implemented the _orm.Session.bulk_insert_mappings.render_nulls
parameter for new style bulk ORM inserts, allowing render_nulls=True as
an execution option. This allows for bulk ORM inserts with a mixture of
None values in the parameter dictionaries to use a single batch of rows
for a given set of dicationary keys, rather than breaking up into batches
that omit the NULL columns from each INSERT.
References: #10575
[orm] [bug] Fixed issue where the __allow_unmapped__ directive failed to allow for
legacy Column / deferred() mappings that nonetheless had
annotations such as Any or a specific type without Mapped[] as
their type, without errors related to locating the attribute name.
References: #10516
[orm] [bug] Fixed caching bug where using the _orm.with_expression() construct in
conjunction with loader options _orm.selectinload(),
_orm.lazyload() would fail to substitute bound parameter values
correctly on subsequent caching runs.
References: #10570
[orm] [bug] Fixed bug in ORM annotated declarative where using a ClassVar that
nonetheless referred in some way to an ORM mapped class name would fail to
be interpreted as a ClassVar that's not mapped.
References: #10472
[sql] [usecase] Implemented "literal value processing" for the Interval datatype
for both the PostgreSQL and Oracle dialects, allowing literal rendering of
interval values. Pull request courtesy Indivar Mishra.
References: #9737
[sql] [bug] Fixed issue where using the same bound parameter more than once with
literal_execute=True in some combinations with other literal rendering
parameters would cause the wrong values to render due to an iteration
issue.
This change is also backported to: 1.4.50
References: #10142
[sql] [bug] Added compiler-level None/NULL handling for the "literal processors" of all datatypes that include literal processing, that is, where a value is rendered inline within a SQL statement rather than as a bound parameter, for all those types that do not feature explicit "null value" handling. Previously this behavior was undefined and inconsistent.
References: #10535
[sql] Removed unused placeholder method TypeEngine.compare_against_backend()
This method was used by very old versions of Alembic.
See https://github.com/sqlalchemy/alembic/issues/1293 for details.
[asyncio] [bug] Fixed bug with method _asyncio.AsyncSession.close_all()
that was not working correctly.
Also added function _asyncio.close_all_sessions() that's
the equivalent of _orm.close_all_sessions().
Pull request courtesy of Bryan不可思议.
References: #10421
[postgresql] [bug] Fixed 2.0 regression caused by #7744 where chains of expressions involving PostgreSQL JSON operators combined with other operators such as string concatenation would lose correct parenthesization, due to an implementation detail specific to the PostgreSQL dialect.
References: #10479
[postgresql] [bug] Fixed SQL handling for "insertmanyvalues" when using the
postgresql.BIT datatype with the asyncpg backend. The
postgresql.BIT on asyncpg apparently requires the use of an
asyncpg-specific BitString type which is currently exposed when using
this DBAPI, making it incompatible with other PostgreSQL DBAPIs that all
work with plain bitstrings here. A future fix in version 2.1 will
normalize this datatype across all PG backends. Pull request courtesy
Sören Oldag.
References: #10532
[mysql] [bug] Repaired a new incompatibility in the MySQL "pre-ping" routine where the
False argument passed to connection.ping(), which is intended to
disable an unwanted "automatic reconnect" feature, is being deprecated in
MySQL drivers and backends, and is producing warnings for some versions of
MySQL's native client drivers. It's removed for mysqlclient, whereas for
PyMySQL and drivers based on PyMySQL, the parameter will be deprecated and
removed at some point, so API introspection is used to future proof against
these various stages of removal.
This change is also backported to: 1.4.50
References: #10492
[mariadb] [bug] Adjusted the MySQL / MariaDB dialects to default a generated column to NULL
when using MariaDB, if _schema.Column.nullable was not
specified with an explicit True or False value, as MariaDB does not
support the "NOT NULL" phrase with a generated column. Pull request
courtesy Indivar.
References: #10056
[mariadb] [bug] [regression] Established a workaround for what seems to be an intrinsic issue across MySQL/MariaDB drivers where a RETURNING result for DELETE DML which returns no rows using SQLAlchemy's "empty IN" criteria fails to provide a cursor.description, which then yields result that returns no rows, leading to regressions for the ORM that in the 2.0 series uses RETURNING for bulk DELETE statements for the "synchronize session" feature. To resolve, when the specific case of "no description when RETURNING was given" is detected, an "empty result" with a correct cursor description is generated and used in place of the non-working cursor.
References: #10505
[mssql] [usecase] Added support for the aioodbc driver implemented for SQL Server,
which builds on top of the pyodbc and general aio* dialect architecture.
References: #6521
[mssql] [bug] [reflection] Fixed issue where identity column reflection would fail for a bigint column with a large identity start value (more than 18 digits).
This change is also backported to: 1.4.50
References: #10504
[oracle] [bug] Fixed issue in Interval datatype where the Oracle implementation
was not being used for DDL generation, leading to the day_precision and
second_precision parameters to be ignored, despite being supported by
this dialect. Pull request courtesy Indivar.
References: #10509
[oracle] [bug] Fixed issue where the cx_Oracle dialect claimed to support a lower cx_Oracle version (7.x) than was actually supported in practice within the 2.0 series of SQLAlchemy. The dialect imports symbols that are only in cx_Oracle 8 or higher, so runtime dialect checks as well as setup.cfg requirements have been updated to reflect this compatibility.
References: #10470
Your coding agent can read these notes before it upgrades. Set up the MCP server →
[orm] [usecase] Added method _orm.Session.get_one() that behaves like _orm.Session.get() but raises an exception instead of returning None if no insta
Released: October 12, 2023
[orm] [usecase] Added method _orm.Session.get_one() that behaves like
_orm.Session.get() but raises an exception instead of returning
None if no instance was found with the provided primary key.
Pull request courtesy of Carlos Sousa.
References: #10202
[orm] [usecase] Added an option to permanently close sessions.
Set to False the new parameter _orm.Session.close_resets_only
will prevent a _orm.Session from performing any other
operation after _orm.Session.close() has been called.
Added new method _orm.Session.reset() that will reset a _orm.Session
to its initial state. This is an alias of _orm.Session.close(),
unless _orm.Session.close_resets_only is set to False.
References: #7787
[orm] [bug] Fixed a wide range of _orm.mapped_column() parameters that were not
being transferred when using the _orm.mapped_column() object inside
of a pep-593 Annotated object, including
_orm.mapped_column.sort_order,
_orm.mapped_column.deferred,
_orm.mapped_column.autoincrement,
_orm.mapped_column.system, _orm.mapped_column.info
etc.
Additionally, it remains not supported to have dataclass arguments, such as
_orm.mapped_column.kw_only,
_orm.mapped_column.default_factory etc. indicated within the
_orm.mapped_column() received by Annotated, as this is not
supported with pep-681 Dataclass Transforms. A warning is now emitted when
these parameters are used within Annotated in this way (and they
continue to be ignored).
[orm] [bug] Fixed issue where calling _engine.Result.unique() with a new-style
select() query in the ORM, where one or more columns yields values
that are of "unknown hashability", typically when using JSON functions like
func.json_build_object() without providing a type, would fail
internally when the returned values were not actually hashable. The
behavior is repaired to test the objects as they are received for
hashability in this case, raising an informative error message if not. Note
that for values of "known unhashability", such as when the
_types.JSON or _types.ARRAY types are used directly, an
informative error message was already raised.
The "hashabiltiy testing" fix here is applied to legacy Query as
well, however in the legacy case, _engine.Result.unique() is used for
nearly all queries, so no new warning is emitted here; the legacy behavior
of falling back to using id() in this case is maintained, with the
improvement that an unknown type that turns out to be hashable will now be
uniqufied, whereas previously it would not.
References: #10459
[orm] [bug] Fixed regression in recently revised "insertmanyvalues" feature (likely
issue #9618) where the ORM would inadvertently attempt to
interpret a non-RETURNING result as one with RETURNING, in the case where
the implicit_returning=False parameter were applied to the mapped
Table, indicating that "insertmanyvalues" cannot be used if the
primary key values are not provided.
References: #10453
[orm] [bug] Fixed bug where ORM _orm.with_loader_criteria() would not apply
itself to a _sql.Select.join() where the ON clause were given as a
plain SQL comparison, rather than as a relationship target or similar.
References: #10365
[orm] [bug] Fixed issue where Mapped symbols like WriteOnlyMapped
and DynamicMapped could not be correctly resolved when referenced
as an element of a sub-module in the given annotation, assuming
string-based or "future annotations" style annotations.
References: #10412
[orm] [bug] Fixed issue with __allow_unmapped__ declarative option
where types that were declared using collection types such as
list[SomeClass] vs. the typing construct List[SomeClass]
would fail to be recognized correctly. Pull request courtesy
Pascal Corpet.
References: #10385
[engine] [bug] Fixed issue within some dialects where the dialect could incorrectly return an empty result set for an INSERT statement that does not actually return rows at all, due to artfacts from pre- or post-fetching the primary key of the row or rows still being present. Affected dialects included asyncpg, all mssql dialects.
[engine] [bug] Fixed issue where under some garbage collection / exception scenarios the connection pool's cleanup routine would raise an error due to an unexpected set of state, which can be reproduced under specific conditions.
References: #10414
[sql] [bug] Fixed issue where referring to a FROM entry in the SET clause of an UPDATE
statement would not include it in the FROM clause of the UPDATE statement,
if that entry were nowhere else in the statement; this occurs currently for
CTEs that were added using Update.add_cte() to provide the desired
CTE at the top of the statement.
References: #10408
[sql] [bug] Fixed 2.0 regression where the DDL construct would no longer
__repr__() due to the removed on attribute not being accommodated.
Pull request courtesy Iuri de Silvio.
References: #10443
[typing] [bug] Fixed typing issue where the argument list passed to Values was
too-restrictively tied to List rather than Sequence. Pull request
courtesy Iuri de Silvio.
References: #10451
[typing] [bug] Updates to the codebase to support Mypy 1.6.0.
_asyncio.AsyncSession.get.execution_options parameter
which was not being propagated to the underlying _orm.Session and
was instead being ignored.[mariadb] [bug] Modified the mariadb-connector driver to pre-load the cursor.rowcount
value for all queries, to suit tools such as Pandas that hardcode to
calling Result.rowcount in this way. SQLAlchemy normally pre-loads
cursor.rowcount only for UPDATE/DELETE statements and otherwise passes
through to the DBAPI where it can return -1 if no value is available.
However, mariadb-connector does not support invoking cursor.rowcount
after the cursor itself is closed, raising an error instead. Generic test
support has been added to ensure all backends support the allowing
Result.rowcount to succceed (that is, returning an integer
value with -1 for "not available") after the result is closed.
References: #10396
[mariadb] [bug] Additional fixes for the mariadb-connector dialect to support UUID data values in the result in INSERT..RETURNING statements.
[mssql] [bug] Fixed bug where the rule that prevents ORDER BY from emitting within
subqueries on SQL Server was not being disabled in the case where the
select.fetch() method were used to limit rows in conjunction with
WITH TIES or PERCENT, preventing valid subqueries with TOP / ORDER BY from
being used.
References: #10458
A future release will rename the Identity.order, Sequence.order and Identity.on_null parameters to Oracle-specific names, deprecating the old names, t…
Released: September 18, 2023
[orm] [bug] Adjusted the ORM's interpretation of the "target" entity used within
Update and Delete to not interfere with the target
"from" object passed to the statement, such as when passing an ORM-mapped
_orm.aliased construct that should be maintained within a phrase
like "UPDATE FROM". Cases like ORM session synchonize using "SELECT"
statements such as with MySQL/ MariaDB will still have issues with
UPDATE/DELETE of this form so it's best to disable synchonize_session when
using DML statements of this type.
References: #10279
[orm] [bug] Added new capability to the _orm.selectin_polymorphic() loader option
which allows other loader options to be bundled as siblings, referring to
one of its subclasses, within the sub-options of parent loader option.
Previously, this pattern was only supported if the
_orm.selectin_polymorphic() were at the top level of the options for
the query. See new documentation section for example.
As part of this change, improved the behavior of the
_orm.Load.selectin_polymorphic() method / loader strategy so that the
subclass load does not load most already-loaded columns from the parent
table, when the option is used against a class that is already being
relationship-loaded. Previously, the logic to load only the subclass
columns worked only for a top level class load.
References: #10348
[engine] [bug] Fixed a series of reflection issues affecting the PostgreSQL, MySQL/MariaDB, and SQLite dialects when reflecting foreign key constraints where the target column contained parenthesis in one or both of the table name or column name.
References: #10275
[sql] [usecase] Adjusted the _types.Enum datatype to accept an argument of
None for the _types.Enum.length parameter, resulting in a
VARCHAR or other textual type with no length in the resulting DDL. This
allows for new elements of any length to be added to the type after it
exists in the schema. Pull request courtesy Eugene Toder.
References: #10269
[sql] [usecase] Added new generic SQL function _functions.aggregate_strings, which
accepts a SQL expression and a decimeter, concatenating strings on multiple
rows into a single aggregate value. The function is compiled on a
per-backend basis, into functions such as group_concat(),
string_agg(), or LISTAGG().
Pull request courtesy Joshua Morris.
References: #9873
[sql] [bug] Adjusted the operator precedence for the string concatenation operator to
be equal to that of string matching operators, such as
ColumnElement.like(), ColumnElement.regexp_match(),
ColumnElement.match(), etc., as well as plain == which has the
same precedence as string comparison operators, so that parenthesis will be
applied to a string concatenation expression that follows a string match
operator. This provides for backends such as PostgreSQL where the "regexp
match" operator is apparently of higher precedence than the string
concatenation operator.
References: #9610
[sql] [bug] Qualified the use of hashlib.md5() within the DDL compiler, which is
used to generate deterministic four-character suffixes for long index and
constraint names in DDL statements, to include the Python 3.9+
usedforsecurity=False parameter so that Python interpreters built for
restricted environments such as FIPS do not consider this call to be
related to security concerns.
References: #10342
[sql] [bug] The Values construct will now automatically create a proxy (i.e.
a copy) of a _sql.column if the column were already associated
with an existing FROM clause. This allows that an expression like
values_obj.c.colname will produce the correct FROM clause even in the
case that colname was passed as a _sql.column that was already
used with a previous Values or other table construct.
Originally this was considered to be a candidate for an error condition,
however it's likely this pattern is already in widespread use so it's
now added to support.
References: #10280
[schema] [bug] Modified the rendering of the Oracle only Identity.order
parameter that's part of both Sequence and Identity to
only take place for the Oracle backend, and not other backends such as that
of PostgreSQL. A future release will rename the
Identity.order, Sequence.order and
Identity.on_null parameters to Oracle-specific names,
deprecating the old names, these parameters only apply to Oracle.
This change is also backported to: 1.4.50
References: #10207
[schema] [bug] Modified the rendering of the Oracle only Identity.order
parameter that's part of both Sequence and Identity to
only take place for the Oracle backend, and not other backends such as that
of PostgreSQL. A future release will rename the
Identity.order, Sequence.order and
Identity.on_null parameters to Oracle-specific names,
deprecating the old names, these parameters only apply to Oracle.
This change is also backported to: 1.4.50
References: #10207
[typing] [usecase] Made the contained type for Mapped covariant; this is to allow
greater flexibility for end-user typing scenarios, such as the use of
protocols to represent particular mapped class structures that are passed
to other functions. As part of this change, the contained type was also
made covariant for dependent and related types such as
_orm.base.SQLORMOperations, _orm.WriteOnlyMapped, and
_sql.SQLColumnExpression. Pull request courtesy Roméo Després.
References: #10288
[typing] [bug] Fixed regression introduced in 2.0.20 via #9600 fix which
attempted to add more formal typing to
_schema.MetaData.naming_convention. This change prevented basic
naming convention dictionaries from passing typing and has been adjusted so
that a plain dictionary of strings for keys as well as dictionaries that
use constraint types as keys or a mix of both, are again accepted.
As part of this change, lesser used forms of the naming convention
dictionary are also typed, including that it currently allows for
Constraint type objects as keys as well.
[typing] [bug] Fixed the type annotation for __class_getitem__() as applied to the
Visitable class at the base of expression constructs to accept Any
for a key, rather than str, which helps with some IDEs such as PyCharm
when attempting to write typing annotations for SQL constructs which
include generic selectors. Pull request courtesy Jordan Macdonald.
References: #9878
[typing] [bug] Repaired the core "SQL element" class SQLCoreOperations to support the
__hash__() method from a typing perspective, as objects like
Column and ORM InstrumentedAttribute are hashable and
are used as dictionary keys in the public API for the _dml.Update
and _dml.Insert constructs. Previously, type checkers were not
aware the root SQL element was hashable.
References: #10353
[typing] [bug] Fixed typing issue with _sql.Existing.select_from() that
prevented its use with ORM classes.
References: #10337
[typing] [bug] Update type annotations for ORM loading options, restricting them to accept only "*" instead of any string for string arguments. Pull request courtesy Janek Nouvertné.
References: #10131
[postgresql] [bug] Fixed regression which appeared in 2.0 due to #8491 where the
revised "ping" used for PostgreSQL dialects when the
_sa.create_engine.pool_pre_ping parameter is in use would
interfere with the use of asyncpg with PGBouncer "transaction" mode, as the
multiple PostgreSQL commands emitted by asnycpg could be broken out among
multiple connections leading to errors, due to the lack of any transaction
around this newly revised "ping". The ping is now invoked within a
transaction, in the same way that is implicit with all other backends that
are based on the pep-249 DBAPI; this guarantees that the series of PG
commands sent by asyncpg for this command are invoked on the same backend
connection without it jumping to a different connection mid-command. The
transaction is not used if the asyncpg dialect is used in "AUTOCOMMIT"
mode, which remains incompatible with pgbouncer transaction mode.
References: #10226
[bug] [setup] Fixed very old issue where the full extent of SQLAlchemy modules, including
sqlalchemy.testing.fixtures, could not be imported outside of a pytest
run. This suits inspection utilities such as pkgutil that attempt to
import all installed modules in all packages.
References: #10321
[orm] [usecase] Implemented the "RETURNING '*'" use case for ORM enabled DML statements. This will render in as many cases as possible and return the
Released: August 15, 2023
[orm] [usecase] Implemented the "RETURNING '*'" use case for ORM enabled DML statements. This will render in as many cases as possible and return the unfiltered result set, however is not supported for multi-parameter "ORM bulk INSERT" statements that have specific column rendering requirements.
References: #10192
[orm] [bug] Fixed fundamental issue which prevented some forms of ORM "annotations"
from taking place for subqueries which made use of _sql.Select.join()
against a relationship target. These annotations are used whenever a
subquery is used in special situations such as within
_orm.PropComparator.and_() and other ORM-specific scenarios.
This change is also backported to: 1.4.50
References: #10223
[orm] [bug] Fixed fundamental issue which prevented some forms of ORM "annotations"
from taking place for subqueries which made use of _sql.Select.join()
against a relationship target. These annotations are used whenever a
subquery is used in special situations such as within
_orm.PropComparator.and_() and other ORM-specific scenarios.
This change is also backported to: 1.4.50
References: #10223
[orm] [bug] Fixed issue where the ORM's generation of a SELECT from a joined
inheritance model with same-named columns in superclass and subclass would
somehow not send the correct list of column names to the CTE
construct, when the RECURSIVE column list were generated.
References: #10169
[orm] [bug] Fixed fairly major issue where execution options passed to
_orm.Session.execute(), as well as execution options local to the ORM
executed statement itself, would not be propagated along to eager loaders
such as that of _orm.selectinload(), _orm.immediateload(), and
_orm.subqueryload(), making it impossible to do things such as
disabling the cache for a single statement or using
schema_translate_map for a single statement, as well as the use of
user-custom execution options. A change has been made where all
user-facing execution options present for _orm.Session.execute() will
be propagated along to additional loaders.
As part of this change, the warning for "excessively deep" eager loaders
leading to caching being disabled can be silenced on a per-statement
basis by sending execution_options={"compiled_cache": None} to
_orm.Session.execute(), which will disable caching for the full
series of statements within that scope.
References: #10231
[orm] [bug] Fixed issue where internal cloning used by the ORM for expressions like
_orm.relationship.Comparator.any() to produce correlated EXISTS
constructs would interfere with the "cartesian product warning" feature of
the SQL compiler, leading the SQL compiler to warn when all elements of the
statement were correctly joined.
References: #10124
[orm] [bug] Fixed issue where the lazy="immediateload" loader strategy would place
an internal loading token into the ORM mapped attribute under circumstances
where the load should not occur, such as in a recursive self-referential
load. As part of this change, the lazy="immediateload" strategy now
honors the _orm.relationship.join_depth parameter for
self-referential eager loads in the same way as that of other eager
loaders, where leaving it unset or set at zero will lead to a
self-referential immediateload not occurring, setting it to a value of one
or greater will immediateload up until that given depth.
References: #10139
[orm] [bug] Fixed issue where dictionary-based collections such as
_orm.attribute_keyed_dict() did not fully pickle/unpickle correctly,
leading to issues when attempting to mutate such a collection after
unpickling.
References: #10175
[orm] [bug] Fixed issue where chaining _orm.load_only() or other wildcard use of
_orm.defer() from another eager loader using a _orm.aliased()
against a joined inheritance subclass would fail to take effect for columns
local to the superclass.
References: #10125
[orm] [bug] Fixed issue where an ORM-enabled _sql.select() construct would not
render any CTEs added only via the _sql.Select.add_cte() method that
were not otherwise referenced in the statement.
References: #10167
Query and select() when performing an
invalidation operation.[engine] [bug] Fixed critical issue where setting
_sa.create_engine.isolation_level to AUTOCOMMIT (as opposed
to using the _engine.Engine.execution_options() method) would fail to
restore "autocommit" to a pooled connection if an alternate isolation level
were temporarily selected using
_engine.Connection.execution_options.isolation_level.
References: #10147
[sql] [bug] Fixed issue where unpickling of a _schema.Column or other
_sql.ColumnElement would fail to restore the correct "comparator"
object, which is used to generate SQL expressions specific to the type
object.
This change is also backported to: 1.4.50
References: #10213
[sql] [bug] Fixed issue where unpickling of a _schema.Column or other
_sql.ColumnElement would fail to restore the correct "comparator"
object, which is used to generate SQL expressions specific to the type
object.
This change is also backported to: 1.4.50
References: #10213
[typing] [usecase] Added new typing only utility functions Nullable() and
NotNullable() to type a column or ORM class as, respectively,
nullable or not nullable.
These function are no-op at runtime, returning the input unchanged.
References: #10173
[typing] [bug] Typing improvements:
- `CursorResult` is returned for some forms of
`_orm.Session.execute()` where DML without RETURNING is used
- fixed type for `_orm.Query.with_for_update.of` parameter within
`_orm.Query.with_for_update()`
- improvements to `_DMLColumnArgument` type used by some DML methods to
pass column expressions
- Add overload to `_sql.literal()` so that it is inferred that the
return type is `BindParameter[NullType]` where
`_sql.literal.type_` param is None
- Add overloads to `_sql.ColumnElement.op()` so that the inferred
type when `_sql.ColumnElement.op.return_type` is not provided
is `Callable[[Any], BinaryExpression[Any]]`
- Add missing overload to `_sql.ColumnElement.__add__()`
Pull request courtesy Mehdi Gmira.
References: #9185
[typing] [bug] Fixed issue in _orm.Session and _asyncio.AsyncSession
methods such as _orm.Session.connection() where the
_orm.Session.connection.execution_options parameter were
hardcoded to an internal type that is not user-facing.
References: #10182
[asyncio] [usecase] Added new methods _asyncio.AsyncConnection.aclose() as a synonym for
_asyncio.AsyncConnection.close() and
_asyncio.AsyncSession.aclose() as a synonym for
_asyncio.AsyncSession.close() to the
_asyncio.AsyncConnection and _asyncio.AsyncSession
objects, to provide compatibility with Python standard library
@contextlib.aclosing construct. Pull request courtesy Grigoriev Semyon.
References: #9698
[mysql] [usecase] Updated aiomysql dialect since the dialect appears to be maintained again. Re-added to the ci testing using version 0.2.0.
This change is also backported to: 1.4.50
The previous method/attribute is now deprecated and will emit a deprecation warning.
Released: July 15, 2023
[orm] [bug] Fixed issue where setting a relationship collection directly, where an
object in the new collection were already present, would not trigger a
cascade event for that object, leading to it not being added to the
_orm.Session if it were not already present. This is similar in
nature to #6471 and is a more apparent issue due to the removal of
cascade_backrefs in the 2.0 series. The
_orm.AttributeEvents.append_wo_mutation() event added as part of
#6471 is now also emitted for existing members of a collection
that are present in a bulk set of that same collection.
References: #10089
[orm] [bug] Fixed issue where objects that were associated with an unloaded collection
via backref, but were not merged into the _orm.Session due to the
removal of cascade_backrefs in the 2.0 series, would not emit a warning
that these objects were not being included in a flush, even though they
were pending members of the collection; in other such cases, a warning is
emitted when a collection being flushed contains non-attached objects which
will be essentially discarded. The addition of the warning for
backref-pending collection members establishes greater consistency with
collections that may be present or non-present and possibly flushed or not
flushed at different times based on different relationship loading
strategies.
References: #10090
[orm] [bug] [regression] Fixed additional regression caused by #9805 where more aggressive
propagation of the "ORM" flag on statements could lead to an internal
attribute error when embedding an ORM Query construct that
nonetheless contained no ORM entities within a Core SQL statement, in this
case ORM-enabled UPDATE and DELETE statements.
References: #10098
[engine] [bug] Renamed _result.Row.t and _result.Row.tuple() to
_result.Row._t and _result.Row._tuple(); this is to suit the
policy that all methods and pre-defined attributes on Row should
be in the style of Python standard library namedtuple where all fixed
names have a leading underscore, to avoid name conflicts with existing
column names. The previous method/attribute is now deprecated and will
emit a deprecation warning.
References: #10093
[engine] [bug] Added detection for non-string, non-_engine.URL objects to the
_engine.make_url() function, allowing ArgumentError to be thrown
immediately, rather than causing failures later on. Special logic ensures
that mock forms of _engine.URL are allowed through. Pull request
courtesy Grigoriev Semyon.
References: #10079
[postgresql] [bug] Fixed regression caused by improvements to PostgreSQL URL parsing in
#10004 where "host" query string arguments that had colons in
them, to support various third party proxy servers and/or dialects, would
not parse correctly as these were evaluted as host:port combinations.
Parsing has been updated to consider a colon as indicating a host:port
value only if the hostname contains only alphanumeric characters with dots
or dashes only (e.g. no slashes), followed by exactly one colon followed by
an all-integer token of zero or more integers. In all other cases, the
full string is taken as a host.
References: #10069
[postgresql] [bug] Fixed issue where comparisons to the _postgresql.CITEXT datatype
would cast the right side to VARCHAR, leading to the right side not
being interpreted as a CITEXT datatype, for the asyncpg, psycopg3 and
pg80000 dialects. This led to the _postgresql.CITEXT type being
essentially unusable for practical use; this is now fixed and the test
suite has been corrected to properly assert that expressions are rendered
correctly.
References: #10096
[engine] [bug] Adjusted the _sa.create_engine.schema_translate_map feature such that all schema names in the statement are now tokenized, regardless o
Released: July 5, 2023
[engine] [bug] Adjusted the _sa.create_engine.schema_translate_map feature
such that all schema names in the statement are now tokenized,
regardless of whether or not a specific name is in the immediate schema
translate map given, and to fallback to substituting the original name when
the key is not in the actual schema translate map at execution time. These
two changes allow for repeated use of a compiled object with schema
schema_translate_maps that include or dont include various keys on each
run, allowing cached SQL constructs to continue to function at runtime when
schema translate maps with different sets of keys are used each time. In
addition, added detection of schema_translate_map dictionaries which gain
or lose a None key across calls for the same statement, which affects
compilation of the statement and is not compatible with caching; an
exception is raised for these scenarios.
References: #10025
[sql] [bug] Fixed issue where the _sql.ColumnOperators.regexp_match()
when using "flags" would not produce a "stable" cache key, that
is, the cache key would keep changing each time causing cache pollution.
The same issue existed for _sql.ColumnOperators.regexp_replace()
with both the flags and the actual replacement expression.
The flags are now represented as fixed modifier strings rendered as
safestrings rather than bound parameters, and the replacement
expression is established within the primary portion of the "binary"
element so that it generates an appropriate cache key.
Note that as part of this change, the
_sql.ColumnOperators.regexp_match.flags and
_sql.ColumnOperators.regexp_replace.flags have been modified to
render as literal strings only, whereas previously they were rendered as
full SQL expressions, typically bound parameters. These parameters should
always be passed as plain Python strings and not as SQL expression
constructs; it's not expected that SQL expression constructs were used in
practice for this parameter, so this is a backwards-incompatible change.
The change also modifies the internal structure of the expression
generated, for _sql.ColumnOperators.regexp_replace() with or without
flags, and for _sql.ColumnOperators.regexp_match() with flags. Third
party dialects which may have implemented regexp implementations of their
own (no such dialects could be located in a search, so impact is expected
to be low) would need to adjust the traversal of the structure to
accommodate.
This change is also backported to: 1.4.49
References: #10042
[sql] [bug] Fixed issue in mostly-internal CacheKey construct where the
__ne__() operator were not properly implemented, leading to nonsensical
results when comparing CacheKey instances to each other.
This change is also backported to: 1.4.49
[extensions] [usecase] Added new option to association_proxy()
association_proxy.create_on_none_assignment; when an
association proxy which refers to a scalar relationship is assigned the
value None, and the referenced object is not present, a new object is
created via the creator. This was apparently an undefined behavior in the
1.2 series that was silently removed.
References: #10013
[typing] [usecase] Improved typing when using standalone operator functions from
sqlalchemy.sql.operators such as sqlalchemy.sql.operators.eq.
References: #10054
[typing] [bug] Fixed some of the typing within the _orm.aliased() construct to
correctly accept a Table object that's been aliased with
Table.alias(), as well as general support for FromClause
objects to be passed as the "selectable" argument, since this is all
supported.
References: #10061
[postgresql] [usecase] Added multi-host support for the asyncpg dialect. General improvements and error checking added to the PostgreSQL URL routines for the "multihost" use case added as well. Pull request courtesy Ilia Dmitriev.
References: #10004
[postgresql] [bug] Added new parameter native_inet_types=False to all PostgreSQL
dialects, which indicates converters used by the DBAPI to
convert rows from PostgreSQL INET and CIDR columns
into Python ipaddress datatypes should be disabled, returning strings
instead. This allows code written to work with strings for these datatypes
to be migrated to asyncpg, psycopg, or pg8000 without code changes
other than adding this parameter to the _sa.create_engine()
or _asyncio.create_async_engine() function call.
References: #9945
[mariadb] [usecase] [reflection] Allowed reflecting _types.UUID columns from MariaDB. This allows
Alembic to properly detect the type of such columns in existing MariaDB
databases.
References: #10028
[mssql] [usecase] Added support for creation and reflection of COLUMNSTORE
indexes in MSSQL dialect. Can be specified on indexes
specifying mssql_columnstore=True.
References: #7340
[mssql] [bug] [sql] Fixed issue where performing Cast to a string type with an
explicit collation would render the COLLATE clause inside the CAST
function, which resulted in a syntax error.
References: #9932
This condition is now detected so that the previous behavior is maintained, however a deprecation warning for this ambiguous use is emitted; to popula…
Released: June 23, 2023
[orm] [bug] [regression] Fixed regression in the 2.0 series where a query that used
undefer_group() with _orm.selectinload() or
_orm.subqueryload() would raise an AttributeError. Pull request
courtesy of Matthew Martin.
References: #9870
[orm] [bug] Fixed issue in ORM Annotated Declarative which prevented a
_orm.declared_attr from being used on a mixin which did not return
a Mapped datatype, and instead returned a supplemental ORM
datatype such as AssociationProxy. The Declarative runtime would
erroneously try to interpret this annotation as needing to be
Mapped and raise an error.
References: #9957
[orm] [bug] [typing] Fixed typing issue where using the AssociationProxy return type
from a _orm.declared_attr function was disallowed.
References: #9957
[orm] [bug] [regression] Fixed regression introduced in 2.0.16 by #9879 where passing a
callable to the _orm.mapped_column.default parameter of
_orm.mapped_column while also setting init=False would
interpret this value as a Dataclass default value which would be assigned
directly to new instances of the object directly, bypassing the default
generator taking place as the _schema.Column.default
value generator on the underlying _schema.Column. This condition
is now detected so that the previous behavior is maintained, however a
deprecation warning for this ambiguous use is emitted; to populate the
default generator for a _schema.Column, the
_orm.mapped_column.insert_default parameter should be used,
which disambiguates from the _orm.mapped_column.default
parameter whose name is fixed as per pep-681.
References: #9936
[orm] [bug] Additional hardening and documentation for the ORM _orm.Session
"state change" system, which detects concurrent use of
_orm.Session and _asyncio.AsyncSession objects; an
additional check is added within the process to acquire connections from
the underlying engine, which is a critical section with regards to internal
connection management.
References: #9973
[orm] [bug] Fixed issue in ORM loader strategy logic which further allows for long
chains of _orm.contains_eager() loader options across complex
inheriting polymorphic / aliased / of_type() relationship chains to take
proper effect in queries.
References: #10006
[orm] [bug] Fixed issue in support for the Enum datatype in the
_orm.registry.type_annotation_map first added as part of
#8859 where using a custom Enum with fixed configuration
in the map would fail to transfer the Enum.name parameter,
which among other issues would prevent PostgreSQL enums from working if the
enum values were passed as individual values. Logic has been updated so
that "name" is transferred over, but also that the default Enum
which is against the plain Python enum.Enum class or other "empty" enum
won't set a hardcoded name of "enum" either.
References: #9963
[orm] [declarative] [bug] A warning is emitted when an ORM _orm.relationship() and other
MapperProperty objects are assigned to two different class
attributes at once; only one of the attributes will be mapped. A warning
for this condition was already in place for _schema.Column and
_orm.mapped_column objects.
References: #3532
[typing] [bug] Fixed typing issue which prevented _orm.WriteOnlyMapped and
_orm.DynamicMapped attributes from being used fully within ORM
queries.
References: #9985
[postgresql] [usecase] The pg8000 dialect now supports RANGE and MULTIRANGE datatypes, using the
existing RANGE API described at postgresql_ranges. Range and
multirange types are supported in the pg8000 driver from version 1.29.8.
Pull request courtesy Tony Locke.
References: #9965
[bug] [ext] Fixed issue in mypy plugin for use with mypy 1.4.
This change is also backported to: 1.4.49
[orm] [bug] A deprecation warning is emitted whenever a property is added to a _orm.Mapper where an ORM mapped property were already configured, or an…
Released: June 10, 2023
[orm] [usecase] Improved DeferredReflection.prepare() to accept arbitrary **kw
arguments that are passed to _schema.MetaData.reflect(), allowing use
cases such as reflection of views as well as dialect-specific arguments to
be passed. Additionally, modernized the
DeferredReflection.prepare.bind argument so that either an
Engine or Connection are accepted as the "bind"
argument.
References: #9828
[orm] [bug] Fixed issue where DeclarativeBaseNoMeta declarative base class
would not function with non-mapped mixins or abstract classes, raising an
AttributeError instead.
References: #9862
[orm] [bug] [regression] Fixed regression in the 2.0 series where the default value of
_orm.validates.include_backrefs got changed to False for
the _orm.validates() function. This default is now restored to
True.
References: #9820
[orm] [bug] Fixed bug in new feature which allows a WHERE clause to be used in
conjunction with orm_queryguide_bulk_update, added in version 2.0.11
as part of #9583, where sending dictionaries that did not include
the primary key values for each row would run through the bulk process and
include "pk=NULL" for the rows, silently failing. An exception is now
raised if primary key values for bulk UPDATE are not supplied.
References: #9917
[orm] [bug] [dataclasses] Fixed an issue where generating dataclasses fields that specified a
default value and set init=False would not work.
The dataclasses behavior in this case is to set the default
value on the class, that's not compatible with the descriptors used
by SQLAlchemy. To support this case the default is transformed to
a default_factory when generating the dataclass.
References: #9879
[orm] [bug] A deprecation warning is emitted whenever a property is added to a
_orm.Mapper where an ORM mapped property were already configured,
or an attribute is already present on the class. Previously, there was a
non-deprecation warning for this case that did not emit consistently. The
logic for this warning has been improved so that it detects end-user
replacement of attribute while not having false positives for internal
Declarative and other cases where replacement of descriptors with new ones
is expected.
References: #9841
[orm] [bug] Improved the argument chacking on the
_orm.registry.map_imperatively.local_table parameter of the
_orm.registry.map_imperatively() method, ensuring only a
Table or other FromClause is passed, and not an
existing mapped class, which would lead to undefined behavior as the object
were further interpreted for a new mapping.
References: #9869
[orm] [bug] The _orm.InstanceState.unloaded_expirable attribute is a synonym
for _orm.InstanceState.unloaded, and is now deprecated; this
attribute was always implementation-specific and should not have been
public.
References: #9913
[asyncio] [usecase] Added new _asyncio.create_async_engine.async_creator parameter
to create_async_engine(), which accomplishes the same purpose as the
create_engine.creator parameter of create_engine().
This is a no-argument callable that provides a new asyncio connection,
using the asyncio database driver directly. The
create_async_engine() function will wrap the driver-level connection
in the appropriate structures. Pull request curtesy of Jack Wotherspoon.
References: #8215
[postgresql] [usecase] [reflection] Cast NAME columns to TEXT when using ARRAY_AGG in PostgreSQL
reflection. This seems to improve compatibility with some PostgreSQL
derivatives that may not support aggregations on the NAME type.
References: #9838
[postgresql] [usecase] Unified the custom PostgreSQL operator definitions, since they are shared among multiple different data types.
References: #9041
[postgresql] [usecase] Added support for PostgreSQL 10 NULLS NOT DISTINCT feature of
unique indexes and unique constraint using the dialect option
postgresql_nulls_not_distinct.
Updated the reflection logic to also correctly take this option
into account.
Pull request courtesy of Pavel Siarchenia.
References: #8240
[postgresql] [bug] Use proper precedence on PostgreSQL specific operators, such as @>.
Previously the precedence was wrong, leading to wrong parenthesis when
rendering against and ANY or ALL construct.
References: #9836
[postgresql] [bug] Fixed issue where the ColumnOperators.like.escape and similar
parameters did not allow an empty string as an argument that would be
passed through as the "escape" character; this is a supported syntax by
PostgreSQL. Pull requset courtesy Martin Caslavsky.
References: #9907
[orm] [bug] As more projects are using new-style "2.0" ORM querying, it's becoming apparent that the conditional nature of "autoflush", being based on
Released: May 19, 2023
[orm] [bug] As more projects are using new-style "2.0" ORM querying, it's becoming apparent that the conditional nature of "autoflush", being based on whether or not the given statement refers to ORM entities, is becoming more of a key behavior. Up until now, the "ORM" flag for a statement has been loosely based around whether or not the statement returns rows that correspond to ORM entities or columns; the original purpose of the "ORM" flag was to enable ORM-entity fetching rules which apply post-processing to Core result sets as well as ORM loader strategies to the statement. For statements that don't build on rows that contain ORM entities, the "ORM" flag was considered to be mostly unnecessary.
It still may be the case that "autoflush" would be better taking effect for
all usage of _orm.Session.execute() and related methods, even for
purely Core SQL constructs. However, this still could impact legacy cases
where this is not expected and may be more of a 2.1 thing. For now however,
the rules for the "ORM-flag" have been opened up so that a statement that
includes ORM entities or attributes anywhere within, including in the WHERE
/ ORDER BY / GROUP BY clause alone, within scalar subqueries, etc. will
enable this flag. This will cause "autoflush" to occur for such statements
and also be visible via the _orm.ORMExecuteState.is_orm_statement
event-level attribute.
References: #9805
[postgresql] [bug] [regression] Repaired the base Uuid datatype for the PostgreSQL dialect to
make full use of the PG-specific UUID dialect-specific datatype when
"native_uuid" is selected, so that PG driver behaviors are included. This
issue became apparent due to the insertmanyvalues improvement made as part
of #9618, where in a similar manner as that of #9739, the
asyncpg driver is very sensitive to datatype casts being present or not,
and the PostgreSQL driver-specific native UUID datatype must be invoked
when this generic type is used so that these casts take place.
References: #9808
[orm] [bug] Modified the JoinedLoader implementation to use a simpler approach in one particular area where it previously used a cached structure that
Released: May 18, 2023
[orm] [bug] Modified the JoinedLoader implementation to use a simpler approach in
one particular area where it previously used a cached structure that would
be shared among threads. The rationale is to avoid a potential race
condition which is suspected of being the cause of a particular crash
that's been reported multiple times. The cached structure in question is
still ultimately "cached" via the compiled SQL cache, so a performance
degradation is not anticipated.
References: #9777
[orm] [bug] [regression] Fixed regression where use of _dml.update() or _dml.delete()
within a _sql.CTE construct, then used in a _sql.select(),
would raise a CompileError as a result of ORM related rules for
performing ORM-level update/delete statements.
References: #9767
[orm] [bug] Fixed issue in new ORM Annotated Declarative where using a
_schema.ForeignKey (or other column-level constraint) inside of
_orm.mapped_column() which is then copied out to models via pep-593
Annotated would apply duplicates of each constraint to the
_schema.Column as produced in the target _schema.Table,
leading to incorrect CREATE TABLE DDL as well as migration directives under
Alembic.
References: #9766
[orm] [bug] Fixed issue where using additional relationship criteria with the
_orm.joinedload() loader option, where the additional criteria itself
contained correlated subqueries that referred to the joined entities and
therefore also required "adaption" to aliased entities, would be excluded
from this adaption, producing the wrong ON clause for the joinedload.
References: #9779
[sql] [usecase] Generalized the MSSQL _sql.try_cast() function into the
sqlalchemy. import namespace so that it may be implemented by third
party dialects as well. Within SQLAlchemy, the _sql.try_cast()
function remains a SQL Server-only construct that will raise
CompileError if used with backends that don't support it.
_sql.try_cast() implements a CAST where un-castable conversions are
returned as NULL, instead of raising an error. Theoretically, the construct
could be implemented by third party dialects for Google BigQuery, DuckDB,
and Snowflake, and possibly others.
Pull request courtesy Nick Crews.
References: #9752
[sql] [bug] Fixed issue in _sql.values() construct where an internal compilation
error would occur if the construct were used inside of a scalar subquery.
References: #9772
[postgresql] [bug] Fixed apparently very old issue where the
_postgresql.ENUM.create_type parameter, when set to its
non-default of False, would not be propagated when the
_schema.Column which it's a part of were copied, as is common when
using ORM Declarative mixins.
References: #9773
[tests] [bug] [pypy] Fixed test that relied on the sys.getsizeof() function to not run on
pypy, where this function appears to have different behavior than it does
on cpython.
References: #9789
[orm] [bug] Fixed issue where ORM Annotated Declarative would not resolve forward references correctly in all cases; in particular, when using from __
Released: May 10, 2023
[orm] [bug] Fixed issue where ORM Annotated Declarative would not resolve forward
references correctly in all cases; in particular, when using
from __future__ import annotations in combination with Pydantic
dataclasses.
References: #9717
[orm] [bug] Fixed issue in new orm_queryguide_upsert_returning feature where the
populate_existing execution option was not being propagated to the
loading option, preventing existing attributes from being refreshed
in-place.
References: #9746
[orm] [bug] Fixed loader strategy pathing issues where eager loaders such as
_orm.joinedload() / _orm.selectinload() would fail to traverse
fully for many-levels deep following a load that had a
_orm.with_polymorphic() or similar construct as an interim member.
References: #9715
[orm] [bug] Fixed issue in _orm.mapped_column() construct where the correct
warning for "column X named directly multiple times" would not be emitted
when ORM mapped attributes referred to the same _schema.Column, if
the _orm.mapped_column() construct were involved, raising an internal
assertion instead.
References: #9630
[sql] [usecase] Implemented the "cartesian product warning" for UPDATE and DELETE statements, those which include multiple tables that are not correlated together in some way.
References: #9721
[sql] [bug] Fixed the base class for dialect-specific float/double types; Oracle
_oracle.BINARY_DOUBLE now subclasses _sqltypes.Double,
and internal types for _sqltypes.Float for asyncpg and pg8000 now
correctly subclass _sqltypes.Float.
[sql] [bug] Fixed issue where _dml.update() construct that included multiple
tables and no VALUES clause would raise with an internal error. Current
behavior for _dml.Update with no values is to generate a SQL
UPDATE statement with an empty "set" clause, so this has been made
consistent for this specific sub-case.
[schema] [performance] Improved how table columns are added, avoiding unnecessary allocations, significantly speeding up the creation of many table, like when reflecting entire schemas.
References: #9597
[typing] [bug] Fixed typing for the _orm.Session.get.with_for_update parameter
of _orm.Session.get() and _orm.Session.refresh() (as well as
corresponding methods on _asyncio.AsyncSession) to accept boolean
True and all other argument forms accepted by the parameter at runtime.
References: #9762
[typing] [sql] Added type :data:_sql.ColumnExpressionArgument as a public-facing type
that indicates column-oriented arguments which are passed to SQLAlchemy
constructs, such as _sql.Select.where(), _sql.and_() and
others. This may be used to add typing to end-user functions which call
these methods.
Unknown interpreted text role "data".
References: #9656
[asyncio] [usecase] Added a new helper mixin _asyncio.AsyncAttrs that seeks to improve
the use of lazy-loader and other expired or deferred ORM attributes with
asyncio, providing a simple attribute accessor that provides an await
interface to any ORM attribute, whether or not it needs to emit SQL.
References: #9731
[asyncio] [bug] Fixed issue in semi-private await_only() and await_fallback()
concurrency functions where the given awaitable would remain un-awaited if
the function threw a GreenletError, which could cause "was not awaited"
warnings later on if the program continued. In this case, the given
awaitable is now cancelled before the exception is thrown.
[postgresql] [bug] [regression] Fixed another regression due to the "insertmanyvalues" change in 2.0.10 as
part of #9618, in a similar way as regression #9701, where
LargeBinary datatypes also need additional casts on when using the
asyncpg driver specifically in order to work with the new bulk INSERT
format.
References: #9739
[oracle] [reflection] Added reflection support in the Oracle dialect to expression based indexes and the ordering direction of index expressions.
References: #9597
[bug] [ext] Fixed issue in _mutable.Mutable where event registration for ORM
mapped attributes would be called repeatedly for mapped inheritance
subclasses, leading to duplicate events being invoked in inheritance
hierarchies.
References: #9676
[orm] [bug] Fixed critical caching issue where the combination of _orm.aliased() and _hybrid.hybrid_property() expression compositions would cause a c
Released: April 30, 2023
[orm] [bug] Fixed critical caching issue where the combination of
_orm.aliased() and _hybrid.hybrid_property() expression
compositions would cause a cache key mismatch, leading to cache keys that
held onto the actual _orm.aliased() object while also not matching
that of equivalent constructs, filling up the cache.
This change is also backported to: 1.4.48
References: #9728
[mysql] [bug] [mariadb] Fixed issues regarding reflection of comments for _schema.Table
and _schema.Column objects, where the comments contained control
characters such as newlines. Additional testing support for these
characters as well as extended Unicode characters in table and column
comments (the latter of which aren't supported by MySQL/MariaDB) added to
testing overall.
References: #9722
[orm] [usecase] The ORM bulk INSERT and UPDATE features now add these capabilities:
# 2.0.11
Released: April 26, 2023 ## orm
[orm] [usecase] The ORM bulk INSERT and UPDATE <orm_expression_update_delete> features now add these capabilities:
The requirement that extra parameters aren't passed when using ORM INSERT using the "orm" dml_strategy setting is lifted.
The requirement that additional WHERE criteria is not passed when using ORM UPDATE using the "bulk" dml_strategy setting is lifted. Note that in this case, the check for expected row count is turned off.
References: [#9583](https://www.sqlalchemy.org/trac/ticket/9583), [#9595](https://www.sqlalchemy.org/trac/ticket/9595)
[orm] [bug] Fixed 2.0 regression where use of _sql.bindparam() inside of _dml.Insert.values() would fail to be interpreted correctly when executing the _dml.Insert statement using the ORM _orm.Session, due to the new ORM-enabled insert feature <orm_queryguide_bulk_insert> not implementing this use case.
References: [#9583](https://www.sqlalchemy.org/trac/ticket/9583), [#9595](https://www.sqlalchemy.org/trac/ticket/9595)
## engine
[engine] [performance] A series of performance enhancements to _engine.Row:
__getattr__ performance of the row's "named tuple" interface has been improved; within this change, the _engine.Row implementation has been streamlined, removing constructs and logic that were specific to the 1.4 and prior series of SQLAlchemy. As part of this change, the serialization format of _engine.Row has been modified slightly, however rows which were pickled with previous SQLAlchemy 2.0 releases will be recognized within the new format. Pull request courtesy J. Nick Koston.
Improved row processing performance for "binary" datatypes by making the "bytes" handler conditional on a per driver basis. As a result, the "bytes" result handler has been removed for nearly all drivers other than psycopg2, all of which in modern forms support returning Python "bytes" directly. Pull request courtesy J. Nick Koston.
Additional refactorings inside of _engine.Row to improve performance by Federico Caselli.
References: [#9678](https://www.sqlalchemy.org/trac/ticket/9678), [#9680](https://www.sqlalchemy.org/trac/ticket/9680)
[engine] [bug] [regression] Fixed regression which prevented the _engine.URL.normalized_query attribute of _engine.URL from functioning.
References: [#9682](https://www.sqlalchemy.org/trac/ticket/9682)
## sql
[sql] [usecase] Added support for slice access with ColumnCollection, e.g. table.c[0:5], subquery.c[:-1] etc. Slice access returns a sub ColumnCollection in the same way as passing a tuple of keys. This is a natural continuation of the key-tuple access added for [#8285](https://www.sqlalchemy.org/trac/ticket/8285), where it appears to be an oversight that the slice access use case was omitted.
References: [#8285](https://www.sqlalchemy.org/trac/ticket/8285)
## typing
[typing] [bug] Improved typing of _engine.RowMapping to indicate that it support also _schema.Column as index objects, not only string names. Pull request courtesy Andy Freeland.
References: [#9644](https://www.sqlalchemy.org/trac/ticket/9644)
## postgresql
[postgresql] [bug] [regression] Fixed critical regression caused by [#9618](https://www.sqlalchemy.org/trac/ticket/9618), which modified the architecture of the insertmanyvalues feature for 2.0.10, which caused floating point values to lose all decimal places when being inserted using the insertmanyvalues feature with either the psycopg2 or psycopg drivers.
Unknown interpreted text role "term".
References: [#9701](https://www.sqlalchemy.org/trac/ticket/9701)
## mssql
[mssql] [bug] Implemented the _sqltypes.Double type for SQL Server, where it will render DOUBLE PRECISION at DDL time. This is implemented using a new MSSQL datatype _mssql.DOUBLE_PRECISION which also may be used directly.
## oracle
[oracle] [bug] Fixed issue in Oracle dialects where Decimal returning types such as _sqltypes.Numeric would return floating point values, rather than Decimal objects, when these columns were used in the _dml.Insert.returning() clause to return INSERTed values.
The dataclass parameters for _orm.column_property() init, default, default_factory, kw_only are now deprecated; these fields don't apply to _orm.colum…
Released: April 21, 2023
[orm] [bug] Fixed bug where various ORM-specific getters such as
ORMExecuteState.is_column_load,
ORMExecuteState.is_relationship_load,
ORMExecuteState.loader_strategy_path etc. would throw an
AttributeError if the SQL statement itself were a "compound select"
such as a UNION.
This change is also backported to: 1.4.48
References: #9634
[orm] [bug] Fixed bug where various ORM-specific getters such as
ORMExecuteState.is_column_load,
ORMExecuteState.is_relationship_load,
ORMExecuteState.loader_strategy_path etc. would throw an
AttributeError if the SQL statement itself were a "compound select"
such as a UNION.
This change is also backported to: 1.4.48
References: #9634
[orm] [bug] Fixed issue where the _orm.declared_attr.directive() modifier was not
correctly honored for subclasses when applied to the __mapper_args__
special method name, as opposed to direct use of
_orm.declared_attr. The two constructs should have identical
runtime behaviors.
References: #9625
[orm] [bug] Made an improvement to the _orm.with_loader_criteria() loader option
to allow it to be indicated in the Executable.options() method of a
top-level statement that is not itself an ORM statement. Examples include
_sql.select() that's embedded in compound statements such as
_sql.union(), within an _dml.Insert.from_select() construct, as
well as within CTE expressions that are not ORM related at the top level.
References: #9635
[orm] [bug] Fixed bug in ORM bulk insert feature where additional unnecessary columns would be rendered in the INSERT statement if RETURNING of individual columns were requested.
References: #9685
[orm] [bug] Fixed bug in ORM Declarative Dataclasses where the
_orm.query_expression() and _orm.column_property()
constructs, which are documented as read-only constructs in the context of
a Declarative mapping, could not be used with a
_orm.MappedAsDataclass class without adding init=False, which
in the case of _orm.query_expression() was not possible as no
init parameter was included. These constructs have been modified from a
dataclass perspective to be assumed to be "read only", setting
init=False by default and no longer including them in the pep-681
constructor. The dataclass parameters for _orm.column_property()
init, default, default_factory, kw_only are now deprecated;
these fields don't apply to _orm.column_property() as used in a
Declarative dataclasses configuration where the construct would be
read-only. Also added read-specific parameter
_orm.query_expression.compare to
_orm.query_expression(); _orm.query_expression.repr
was already present.
References: #9628
[orm] [bug] Added missing _orm.mapped_column.active_history parameter
to _orm.mapped_column() construct.
[engine] [usecase] Added _sa.create_pool_from_url() and
_asyncio.create_async_pool_from_url() to create
a _pool.Pool instance from an input url passed as string
or _sa.URL.
References: #9613
[engine] [bug] Repaired a major shortcoming which was identified in the
engine_insertmanyvalues performance optimization feature first
introduced in the 2.0 series. This was a continuation of the change in
2.0.9 which disabled the SQL Server version of the feature due to a
reliance in the ORM on apparent row ordering that is not guaranteed to take
place. The fix applies new logic to all "insertmanyvalues" operations,
which takes effect when a new parameter
_dml.Insert.returning.sort_by_parameter_order on the
_dml.Insert.returning() or _dml.UpdateBase.return_defaults()
methods, that through a combination of alternate SQL forms, direct
correspondence of client side parameters, and in some cases downgrading to
running row-at-a-time, will apply sorting to each batch of returned rows
using correspondence to primary key or other unique values in each row
which can be correlated to the input data.
Performance impact is expected to be minimal as nearly all common primary key scenarios are suitable for parameter-ordered batching to be achieved for all backends other than SQLite, while "row-at-a-time" mode operates with a bare minimum of Python overhead compared to the very heavyweight approaches used in the 1.x series. For SQLite, there is no difference in performance when "row-at-a-time" mode is used.
It's anticipated that with an efficient "row-at-a-time" INSERT with RETURNING batching capability, the "insertmanyvalues" feature can be later be more easily generalized to third party backends that include RETURNING support but not necessarily easy ways to guarantee a correspondence with parameter order.
[typing] [bug] Added typing information for recently added operators
ColumnOperators.icontains(), ColumnOperators.istartswith(),
ColumnOperators.iendswith(), and bitwise operators
ColumnOperators.bitwise_and(), ColumnOperators.bitwise_or(),
ColumnOperators.bitwise_xor(), ColumnOperators.bitwise_not(),
ColumnOperators.bitwise_lshift()
ColumnOperators.bitwise_rshift(). Pull request courtesy Martijn
Pieters.
References: #9650
[typing] [bug] Updates to the codebase to pass typing with Mypy 1.2.0.
[typing] [bug] Fixed typing issue where _orm.PropComparator.and_() expressions would
not be correctly typed inside of loader options such as
_orm.selectinload().
References: #9669
[postgresql] [usecase] Added prepared_statement_name_func connection argument option in the
asyncpg dialect. This option allows passing a callable used to customize
the name of the prepared statement that will be created by the driver
when executing queries. Pull request courtesy Pavel Sirotkin.
References: #9608
[postgresql] [usecase] Add missing _postgresql.Range.intersection() method.
Pull request courtesy Yurii Karabas.
References: #9509
[postgresql] [bug] Restored the _postgresql.ENUM.name parameter as optional in the
signature for _postgresql.ENUM, as this is chosen automatically
from a given pep-435 Enum type.
References: #9611
[postgresql] [bug] Fixed issue where the comparison for _postgresql.ENUM against a
plain string would cast that right-hand side type as VARCHAR, which due to
more explicit casting added to dialects such as asyncpg would produce a
PostgreSQL type mismatch error.
References: #9621
[postgresql] [bug] Fixed issue that prevented reflection of expression based indexes with long expressions in PostgreSQL. The expression where erroneously truncated to the identifier length (that's 63 bytes by default).
References: #9615
[mssql] [bug] Restored the :term:insertmanyvalues feature for Microsoft SQL Server.
This feature was disabled in version 2.0.9 due to an apparent reliance
on the ordering of RETURNING that is not guaranteed. The architecture of
the "insertmanyvalues" feature has been reworked to accommodate for
specific organizations of INSERT statements and result row handling that
can guarantee the correspondence of returned rows to input records.
Unknown interpreted text role "term".
_sqltypes.Uuid datatype could not be used in
an INSERT..RETURNING clause with the Oracle dialect.[orm] [bug] Fixed endless loop which could occur when using "relationship to aliased class" feature and also indicating a recursive eager loader such
Released: April 5, 2023
[orm] [bug] Fixed endless loop which could occur when using "relationship to aliased
class" feature and also indicating a recursive eager loader such as
lazy="selectinload" in the loader, in combination with another eager
loader on the opposite side. The check for cycles has been fixed to include
aliased class relationships.
This change is also backported to: 1.4.48
References: #9590
[mariadb] [bug] Added row_number as reserved word in MariaDb.
References: #9588
[mssql] [bug] Due to a critical bug identified in SQL Server, the SQLAlchemy "insertmanyvalues" feature which allows fast INSERT of many rows while also supporting RETURNING unfortunately needs to be disabled for SQL Server. SQL Server is apparently unable to guarantee that the order of rows inserted matches the order in which they are sent back by OUTPUT inserted when table-valued rows are used with INSERT in conjunction with OUTPUT inserted. We are trying to see if Microsoft is able to confirm this undocumented behavior however there is no known workaround, other than it's not safe to use table-valued expressions with OUTPUT inserted for now.
References: #9603
[mssql] [bug] Changed the bulk INSERT strategy used for SQL Server "executemany" with
pyodbc when fast_executemany is set to True by using
fast_executemany / cursor.executemany() for bulk INSERT that does
not include RETURNING, restoring the same behavior as was used in
SQLAlchemy 1.4 when this parameter is set.
New performance details from end users have shown that fast_executemany
is still much faster for very large datasets as it uses ODBC commands that
can receive all rows in a single round trip, allowing for much larger
datasizes than the batches that can be sent by "insertmanyvalues"
as was implemented for SQL Server.
While this change was made such that "insertmanyvalues" continued to be
used for INSERT that includes RETURNING, as well as if fast_executemany
were not set, due to #9603, the "insertmanyvalues" strategy has
been disabled for SQL Server across the board in any case.
References: #9586
…not themselves part of a dataclass, now emit a deprecation warning as these fields will be ignored in a future release, as Python dataclasses behavior…
Released: March 31, 2023
[orm] [usecase] Exceptions such as TypeError and ValueError raised by Python
dataclasses when making use of the _orm.MappedAsDataclass mixin
class or _orm.registry.mapped_as_dataclass() decorator are now
wrapped within an InvalidRequestError wrapper along with
informative context about the error message, referring to the Python
dataclasses documentation as the authoritative source of background
information on the cause of the exception.
References: #9563
[orm] [bug] Fixed issue in ORM Annotated Declarative where using a recursive type (e.g. using a nested Dict type) would result in a recursion overflow in the ORM's annotation resolution logic, even if this datatype were not necessary to map the column.
References: #9553
[orm] [bug] Fixed issue where the _orm.mapped_column() construct would raise an
internal error if used on a Declarative mixin and included the
_orm.mapped_column.deferred parameter.
References: #9550
[orm] [bug] Expanded the warning emitted when a plain _sql.column() object is
present in a Declarative mapping to include any arbitrary SQL expression
that is not declared within an appropriate property type such as
_orm.column_property(), _orm.deferred(), etc. These attributes
are otherwise not mapped at all and remain unchanged within the class
dictionary. As it seems likely that such an expression is usually not
what's intended, this case now warns for all such otherwise ignored
expressions, rather than just the _sql.column() case.
References: #9537
[orm] [bug] Fixed regression where accessing the expression value of a hybrid property
on a class that was either unmapped or not-yet-mapped (such as calling upon
it within a _orm.declared_attr() method) would raise an internal
error, as an internal fetch for the parent class' mapper would fail and an
instruction for this failure to be ignored were inadvertently removed in
2.0.
References: #9519
[orm] [bug] Fields that are declared on Declarative Mixins and then combined with
classes that make use of _orm.MappedAsDataclass, where those mixin
fields are not themselves part of a dataclass, now emit a deprecation
warning as these fields will be ignored in a future release, as Python
dataclasses behavior is to ignore these fields. Type checkers will not see
these fields under pep-681.
References: #9350
[orm] [bug] Fixed issue where the _sql.BindParameter.render_literal_execute()
method would fail when called on a parameter that also had ORM annotations
associated with it. In practice, this would be observed as a failure of SQL
compilation when using some combinations of a dialect that uses "FETCH
FIRST" such as Oracle along with a _sql.Select construct that uses
_sql.Select.limit(), within some ORM contexts, including if the
statement were embedded within a relationship primaryjoin expression.
References: #9526
[orm] [bug] Towards maintaining consistency with unit-of-work changes made for
#5984 and #8862, both of which disable "lazy='raise'"
handling within _orm.Session processes that aren't triggered by
attribute access, the _orm.Session.delete() method will now also
disable "lazy='raise'" handling when it traverses relationship paths in
order to process the "delete" and "delete-orphan" cascade rules.
Previously, there was no easy way to generically call
_orm.Session.delete() on an object that had "lazy='raise'" set up
such that only the necessary relationships would be loaded. As
"lazy='raise'" is primarily intended to catch SQL loading that emits on
attribute access, _orm.Session.delete() is now made to behave like
other _orm.Session methods including _orm.Session.merge() as
well as _orm.Session.flush() along with autoflush.
References: #9549
[orm] [bug] Fixed issue where an annotation-only _orm.Mapped directive could
not be used in a Declarative mixin class, without that attribute attempting
to take effect for single- or joined-inheritance subclasses of mapped
classes that had already mapped that attribute on a superclass, producing
conflicting column errors and/or warnings.
References: #9564
[orm] [bug] [typing] Properly type _dml.Insert.from_select.names to accept
a list of string or columns or mapped attributes.
References: #9514
_orm.DeclarativeBase would fail to be mapped.
Additionally, repaired the given test suite so that the documented
instructions for running the example using Python unittest now work again.[typing] [bug] Fixed typing for _orm.deferred() and _orm.query_expression()
to work correctly with 2.0 style mappings.
References: #9536
[postgresql] [bug] Fixed critical regression in PostgreSQL dialects such as asyncpg which rely
upon explicit casts in SQL in order for datatypes to be passed to the
driver correctly, where a String datatype would be cast along
with the exact column length being compared, leading to implicit truncation
when comparing a VARCHAR of a smaller length to a string of greater
length regardless of operator in use (e.g. LIKE, MATCH, etc.). The
PostgreSQL dialect now omits the length from VARCHAR when rendering
these casts.
References: #9511
[mysql] [bug] Fixed issue where string datatypes such as _sqltypes.CHAR,
_sqltypes.VARCHAR, _sqltypes.TEXT, as well as binary
_sqltypes.BLOB, could not be produced with an explicit length of
zero, which has special meaning for MySQL. Pull request courtesy J. Nick
Koston.
References: #9544
[bug] [util] Implemented missing methods copy and pop in
OrderedSet class.
References: #9487
[sql] [bug] Fixed critical SQL caching issue where use of the _sql.Operators.op() custom operator function would not produce an appropriate cache key,
Released: March 18, 2023
[sql] [bug] Fixed critical SQL caching issue where use of the
_sql.Operators.op() custom operator function would not produce an appropriate
cache key, leading to reduce the effectiveness of the SQL cache.
This change is also backported to: 1.4.47
References: #9506
[typing] [bug] Fixed typing issue where _orm.composite() would not allow an
arbitrary callable as the source of the composite class.
References: #9502
[postgresql] [usecase] Added new PostgreSQL type _postgresql.CITEXT. Pull request
courtesy Julian David Rath.
References: #9416
[postgresql] [usecase] Modifications to the base PostgreSQL dialect to allow for better integration with the sqlalchemy-redshift third party dialect for SQLAlchemy 2.0. Pull request courtesy matthewgdv.
References: #9442
[orm] [bug] Fixed bug where the "active history" feature was not fully implemented for composite attributes, making it impossible to receive events th
Released: March 13, 2023
[orm] [bug] Fixed bug where the "active history" feature was not fully implemented for composite attributes, making it impossible to receive events that included the "old" value. This seems to have been the case with older SQLAlchemy versions as well, where "active_history" would be propagated to the underlying column-based attributes, but an event handler listening to the composite attribute itself would not be given the "old" value being replaced, even if the composite() were set up with active_history=True.
Additionally, fixed a regression that's local to 2.0 which disallowed
active_history on composite from being assigned to the impl with
attr.impl.active_history=True.
References: #9460
[orm] [bug] Fixed regression involving pickling of Python rows between the cython and
pure Python implementations of Row, which occurred as part of
refactoring code for version 2.0 with typing. A particular constant were
turned into a string based Enum for the pure Python version of
Row whereas the cython version continued to use an integer
constant, leading to deserialization failures.
References: #9418
[sql] [bug] [regression] Fixed regression where the fix for #8098, which was released in the 1.4 series and provided a layer of concurrency-safe checks for the lambda SQL API, included additional fixes in the patch that failed to be applied to the main branch. These additional fixes have been applied.
References: #9461
[sql] [bug] Fixed regression where the _sql.select() construct would not be able
to render if it were given no columns and then used in the context of an
EXISTS, raising an internal exception instead. While an empty "SELECT" is
not typically valid SQL, in the context of EXISTS databases such as
PostgreSQL allow it, and in any case the condition now no longer raises
an internal exception.
References: #9440
[typing] [bug] Fixed typing issue where ColumnElement.cast() did not allow a
TypeEngine argument independent of the type of the
ColumnElement itself, which is the purpose of
ColumnElement.cast().
References: #9451
[typing] [bug] Fixed issues to allow typing tests to pass under Mypy 1.1.1.
[oracle] [bug] Fixed reflection bug where Oracle "name normalize" would not work correctly
for reflection of symbols that are in the "PUBLIC" schema, such as
synonyms, meaning the PUBLIC name could not be indicated as lower case on
the Python side for the _schema.Table.schema argument. Using
uppercase "PUBLIC" would work, but would then lead to awkward SQL queries
including a quoted "PUBLIC" name as well as indexing the table under
uppercase "PUBLIC", which was inconsistent.
References: #9459
[orm] [bug] Added constructor arguments to the built-in mapping collection types including KeyFuncDict, _orm.attribute_keyed_dict(), _orm.column_keyed
Released: March 5, 2023
[orm] [bug] Added constructor arguments to the built-in mapping collection types
including KeyFuncDict, _orm.attribute_keyed_dict(),
_orm.column_keyed_dict() so that these dictionary types may be
constructed in place given the data up front; this provides further
compatibility with tools such as Python dataclasses .asdict() which
relies upon invoking these classes directly as ordinary dictionary classes.
References: #9418
[orm] [bug] [regression] Fixed multiple regressions due to #8372, involving
_orm.attribute_mapped_collection() (now called
_orm.attribute_keyed_dict()).
First, the collection was no longer usable with "key" attributes that were not themselves ordinary mapped attributes; attributes linked to descriptors and/or association proxy attributes have been fixed.
Second, if an event or other operation needed access to the "key" in order to populate the dictionary from an mapped attribute that was not loaded, this also would raise an error inappropriately, rather than trying to load the attribute as was the behavior in 1.4. This is also fixed.
For both cases, the behavior of #8372 has been expanded.
#8372 introduced an error that raises when the derived key that
would be used as a mapped dictionary key is effectively unassigned. In this
change, a warning only is emitted if the effective value of the ".key"
attribute is None, where it cannot be unambiguously determined if this
None was intentional or not. None will be not supported as mapped
collection dictionary keys going forward (as it typically refers to NULL
which means "unknown"). Setting
_orm.attribute_keyed_dict.ignore_unpopulated_attribute will now
cause such None keys to be ignored as well.
References: #9424
[orm] [bug] Identified that the sqlite and mssql+pyodbc dialects are now
compatible with the SQLAlchemy ORM's "versioned rows" feature, since
SQLAlchemy now computes rowcount for a RETURNING statement in this specific
case by counting the rows returned, rather than relying upon
cursor.rowcount. In particular, the ORM versioned rows use case
(documented at mapper_version_counter) should now be fully
supported with the SQL Server pyodbc dialect.
[orm] [bug] Added support for the _orm.Mapper.polymorphic_load parameter to
be applied to each mapper in an inheritance hierarchy more than one level
deep, allowing columns to load for all classes in the hierarchy that
indicate "selectin" using a single statement, rather than ignoring
elements on those intermediary classes that nonetheless indicate they also
would participate in "selectin" loading and were not part of the
base-most SELECT statement.
References: #9373
[orm] [bug] Continued the fix for #8853, allowing the _orm.Mapped
name to be fully qualified regardless of whether or not
from __annotations__ import future were present. This issue first fixed
in 2.0.0b3 confirmed that this case worked via the test suite, however the
test suite apparently was not testing the behavior for the name
_orm.Mapped not being locally present at all; string resolution
has been updated to ensure the _orm.Mapped symbol is locatable as
applies to how the ORM uses these functions.
[bug] [orm declarative] Fixed issue where new _orm.mapped_column.use_existing_column
feature would not work if the two same-named columns were mapped under
attribute names that were differently-named from an explicit name given to
the column itself. The attribute names can now be differently named when
using this parameter.
References: #9332
[engine] [performance] A small optimization to the Cython implementation of Result
using a cdef for a particular int value to avoid Python overhead. Pull
request courtesy Matus Valo.
References: #9343
[engine] [bug] Fixed bug where _engine.Row objects could not be reliably unpickled
across processes due to an accidental reliance on an unstable hash value.
References: #9423
[sql] [bug] [regression] Restore the nullslast() and nullsfirst() legacy functions
into the sqlalchemy import namespace. Previously, the newer
nulls_last() and nulls_first() functions were available, but
the legacy ones were inadvertently removed.
References: #9390
_schema.MetaData.schema
argument of _schema.MetaData is a string.[typing] [usecase] Exported the type returned by
_orm.scoped_session.query_property() using a new public type
orm.QueryPropertyDescriptor.
References: #9338
[typing] [bug] Fixed bug where the _engine.Connection.scalars() method was not typed
as allowing a multiple-parameters list, which is now supported using
insertmanyvalues operations.
[typing] [bug] Improved typing for the mapping passed to Insert.values() and
Update.values() to be more open-ended about collection type, by
indicating read-only Mapping instead of writeable Dict which would
error out on too limited of a key type.
References: #9376
[typing] [bug] Added missing init overload to the _types.Numeric type object so
that pep-484 type checkers may properly resolve the complete type, deriving
from the _types.Numeric.asdecimal parameter whether Decimal
or float objects will be represented.
References: #9391
[typing] [bug] Fixed typing bug where _sql.Select.from_statement() would not accept
_sql.text() or TextualSelect objects as a valid type.
Additionally repaired the TextClause.columns method to have a
return type, which was missing.
References: #9398
[typing] [bug] Fixed typing issue where _orm.with_polymorphic() would not
record the class type correctly.
References: #9340
[postgresql] [bug] Fixed issue in PostgreSQL _postgresql.ExcludeConstraint where
literal values were being compiled as bound parameters and not direct
inline values as is required for DDL.
References: #9349
[postgresql] [bug] Fixed issue where the PostgreSQL _postgresql.ExcludeConstraint
construct would not be copyable within operations such as
_schema.Table.to_metadata() as well as within some Alembic scenarios,
if the constraint contained textual expression elements.
References: #9401
[mysql] [bug] [postgresql] The support for pool ping listeners to receive exception events via the
DialectEvents.handle_error() event added in 2.0.0b1 for
#5648 failed to take into account dialect-specific ping routines
such as that of MySQL and PostgreSQL. The dialect feature has been reworked
so that all dialects participate within event handling. Additionally,
a new boolean element ExceptionContext.is_pre_ping is added
which identifies if this operation is occurring within the pre-ping
operation.
For this release, third party dialects which implement a custom
_engine.Dialect.do_ping() method can opt in to the newly improved
behavior by having their method no longer catch exceptions or check
exceptions for "is_disconnect", instead just propagating all exceptions
outwards. Checking the exception for "is_disconnect" is now done by an
enclosing method on the default dialect, which ensures that the event hook
is invoked for all exception scenarios before testing the exception as a
"disconnect" exception. If an existing do_ping() method continues to
catch exceptions and check "is_disconnect", it will continue to work as it
did previously, but handle_error hooks will not have access to the
exception if it isn't propagated outwards.
References: #5648
[sqlite] [bug] [regression] Fixed regression for SQLite connections where use of the deterministic
parameter when establishing database functions would fail for older SQLite
versions, those prior to version 3.8.3. The version checking logic has been
improved to accommodate for this case.
References: #9379
[mssql] [bug] Fixed issue in the new Uuid datatype which prevented it from
working with the pymssql driver. As pymssql seems to be maintained again,
restored testing support for pymssql.
References: #9414
[mssql] [bug] Tweaked the pymssql dialect to take better advantage of RETURNING for INSERT statements in order to retrieve last inserted primary key values, in the same way as occurs for the mssql+pyodbc dialect right now.
[bug] [ext] Fixed issue in automap where calling _automap.AutomapBase.prepare()
from a specific mapped class, rather than from the
_automap.AutomapBase directly, would not use the correct base
class when automap detected new tables, instead using the given class,
leading to mappers trying to configure inheritance. While one should
normally call _automap.AutomapBase.prepare() from the base in any
case, it shouldn't misbehave that badly when called from a subclass.
References: #9367
[bug] [ext] [regression] Fixed regression caused by typing added to sqlalchemy.ext.mutable for
#8667, where the semantics of the .pop() method changed such
that the method was non-working. Pull request courtesy Nils Philippsen.
References: #9380
Nothing published for this version
[orm] [usecase] The _orm.Session.refresh() method will now immediately load a relationship-bound attribute that is explicitly named within the _orm.Se
Released: February 17, 2023
[orm] [usecase] The _orm.Session.refresh() method will now immediately load a
relationship-bound attribute that is explicitly named within the
_orm.Session.refresh.attribute_names collection even if it is
currently linked to the "select" loader, which normally is a "lazy" loader
that does not fire off during a refresh. The "lazy loader" strategy will
now detect that the operation is specifically a user-initiated
_orm.Session.refresh() operation which named this attribute
explicitly, and will then call upon the "immediateload" strategy to
actually emit SQL to load the attribute. This should be helpful in
particular for some asyncio situations where the loading of an unloaded
lazy-loaded attribute must be forced, without using the actual lazy-loading
attribute pattern not supported in asyncio.
References: #9298
[orm] [bug] [regression] Fixed regression introduced in version 2.0.2 due to #9217 where
using DML RETURNING statements, as well as
_sql.Select.from_statement() constructs as was "fixed" in
#9217, in conjunction with ORM mapped classes that used
expressions such as with _orm.column_property(), would lead to an
internal error within Core where it would attempt to match the expression
by name. The fix repairs the Core issue, and also adjusts the fix in
#9217 to not take effect for the DML RETURNING use case, where it
adds unnecessary overhead.
References: #9273
[orm] [bug] Marked the internal EvaluatorCompiler module as private to the ORM, and
renamed it to _EvaluatorCompiler. For users that may have been relying
upon this, the name EvaluatorCompiler is still present, however this
use is not supported and will be removed in a future release.
[orm] [use_case] To accommodate a change in column ordering used by ORM Declarative in
SQLAlchemy 2.0, a new parameter _orm.mapped_column.sort_order
has been added that can be used to control the order of the columns defined
in the table by the ORM, for common use cases such as mixins with primary
key columns that should appear first in tables. The change notes at
change_9297 illustrate the default change in ordering behavior
(which is part of all SQLAlchemy 2.0 releases) as well as use of the
_orm.mapped_column.sort_order to control column ordering when
using mixins and multiple classes (new in 2.0.4).
References: #9297
[usecase] [orm declarative] Added new parameter dataclasses_callable to both the
_orm.MappedAsDataclass class as well as the
_orm.registry.mapped_as_dataclass() method which allows an
alternative callable to Python dataclasses.dataclass to be used in
order to produce dataclasses. The use case here is to drop in Pydantic's
dataclass function instead. Adjustments have been made to the mixin support
added for #9179 in version 2.0.1 so that the __annotations__
collection of the mixin is rewritten to not include the
_orm.Mapped container, in the same way as occurs with mapped
classes, so that the Pydantic dataclasses constructor is not exposed to
unknown types.
References: #9266
[sql] [bug] Fixed issue where element types of a tuple value would be hardcoded to take
on the types from a compared-to tuple, when the comparison were using the
ColumnOperators.in_() operator. This was inconsistent with the usual
way that types are determined for a binary expression, which is that the
actual element type on the right side is considered first before applying
the left-hand-side type.
References: #9313
[sql] Added public property _schema.Table.autoincrement_column that
returns the column identified as autoincrementing in the column.
References: #9277
[typing] [usecase] Improved the typing support for the hybrids_toplevel
extension, updated all documentation to use ORM Annotated Declarative
mappings, and added a new modifier called hybrid_property.inplace.
This modifier provides a way to alter the state of a hybrid_property
in place, which is essentially what very early versions of hybrids
did, before SQLAlchemy version 1.2.0 #3912 changed this to
remove in-place mutation. This in-place mutation is now restored on an
opt-in basis to allow a single hybrid to have multiple methods
set up, without the need to name all the methods the same and without the
need to carefully "chain" differently-named methods in order to maintain
the composition. Typing tools such as Mypy and Pyright do not allow
same-named methods on a class, so with this change a succinct method
of setting up hybrids with typing support is restored.
References: #9321
[oracle] [bug] Adjusted the behavior of the thick_mode parameter for the
oracledb dialect to correctly accept False as a value.
Previously, only None would indicate that thick mode should be
disabled.
References: #9295
[sql] [bug] [regression] Fixed critical regression in SQL expression formulation in the 2.0 series due to #7744 which improved support for SQL express
Released: February 9, 2023
[sql] [bug] [regression] Fixed critical regression in SQL expression formulation in the 2.0 series due to #7744 which improved support for SQL expressions that contained many elements against the same operator repeatedly; parenthesis grouping would be lost with expression elements beyond the first two elements.
References: #9271
…the previous syntax using VALUES() now emits a deprecation warning with those versions. Server version detection is employed to determine if tradition…
Released: February 6, 2023
[orm] [usecase] Added new event hook _orm.MapperEvents.after_mapper_constructed(),
which supplies an event hook to take place right as the
_orm.Mapper object has been fully constructed, but before the
_orm.registry.configure() call has been called. This allows code that
can create additional mappings and table structures based on the initial
configuration of a _orm.Mapper, which also integrates within
Declarative configuration. Previously, when using Declarative, where the
_orm.Mapper object is created within the class creation process,
there was no documented means of running code at this point. The change
is to immediately benefit custom mapping schemes such as that
of the examples_versioned_history example, which generate additional
mappers and tables in response to the creation of mapped classes.
References: #9220
[orm] [usecase] The infrequently used _orm.Mapper.iterate_properties attribute and
_orm.Mapper.get_property() method, which are primarily used
internally, no longer implicitly invoke the _orm.registry.configure()
process. Public access to these methods is extremely rare and the only
benefit to having _orm.registry.configure() would have been allowing
"backref" properties be present in these collections. In order to support
the new _orm.MapperEvents.after_mapper_constructed() event, iteration
and access to the internal _orm.MapperProperty objects is now
possible without triggering an implicit configure of the mapper itself.
The more-public facing route to iteration of all mapper attributes, the
_orm.Mapper.attrs collection and similar, will still implicitly
invoke the _orm.registry.configure() step thus making backref
attributes available.
In all cases, the _orm.registry.configure() is always available to
be called directly.
References: #9220
[orm] [bug] [regression] Fixed obscure ORM inheritance issue caused by #8705 where some
scenarios of inheriting mappers that indicated groups of columns from the
local table and the inheriting table together under a
_orm.column_property() would nonetheless warn that properties of the
same name were being combined implicitly.
References: #9232
[orm] [bug] [regression] Fixed regression where using the _orm.Mapper.version_id_col
feature with a regular Python-side incrementing column would fail to work
for SQLite and other databases that don't support "rowcount" with
"RETURNING", as "RETURNING" would be assumed for such columns even though
that's not what actually takes place.
References: #9228
[orm] [bug] [regression] Fixed regression when using _sql.Select.from_statement() in an ORM
context, where matching of columns to SQL labels based on name alone was
disabled for ORM-statements that weren't fully textual. This would prevent
arbitrary SQL expressions with column-name labels from matching up to the
entity to be loaded, which previously would work within the 1.4
and previous series, so the previous behavior has been restored.
References: #9217
[bug] [orm declarative] Fixed regression caused by the fix for #9171, which itself was
fixing a regression, involving the mechanics of __init__() on classes
that extend from _orm.DeclarativeBase. The change made it such
that __init__() was applied to the user-defined base if there were no
__init__() method directly on the class. This has been adjusted so that
__init__() is applied only if no other class in the hierarchy of the
user-defined base has an __init__() method. This again allows
user-defined base classes based on _orm.DeclarativeBase to include
mixins that themselves include a custom __init__() method.
References: #9249
[bug] [orm declarative] Fixed issue in ORM Declarative Dataclass mappings related to newly added
support for mixins added in 2.0.1 via #9179, where a combination
of using mixins plus ORM inheritance would mis-classify fields in some
cases leading to field-level dataclass arguments such as init=False being
lost.
References: #9226
[bug] [orm declarative] Repaired ORM Declarative mappings to allow for the
_orm.Mapper.primary_key parameter to be specified within
__mapper_args__ when using _orm.mapped_column(). Despite this
usage being directly in the 2.0 documentation, the _orm.Mapper was
not accepting the _orm.mapped_column() construct in this context. Ths
feature was already working for the _orm.Mapper.version_id_col
and _orm.Mapper.polymorphic_on parameters.
As part of this change, the __mapper_args__ attribute may be specified
without using _orm.declared_attr() on a non-mapped mixin class,
including a "primary_key" entry that refers to _schema.Column
or _orm.mapped_column() objects locally present on the mixin;
Declarative will also translate these columns into the correct ones for a
particular mapped class. This again was working already for the
_orm.Mapper.version_id_col and
_orm.Mapper.polymorphic_on parameters. Additionally,
elements within "primary_key" may be indicated as string names of
existing mapped properties.
References: #9240
[bug] [orm declarative] An explicit error is raised if a mapping attempts to mix the use of
_orm.MappedAsDataclass with
_orm.registry.mapped_as_dataclass() within the same class hierarchy,
as this produces issues with the dataclass function being applied at the
wrong time to the mapped class, leading to errors during the mapping
process.
References: #9211
[examples] [bug] Reworked the examples_versioned_history to work with
version 2.0, while at the same time improving the overall working of
this example to use newer APIs, including a newly added hook
_orm.MapperEvents.after_mapper_constructed().
References: #9220
[sql] [usecase] Added a full suite of new SQL bitwise operators, for performing database-side bitwise expressions on appropriate data values such as integers, bit-strings, and similar. Pull request courtesy Yegor Statkevich.
References: #8780
[asyncio] [bug] Repaired a regression caused by the fix for #8419 which caused
asyncpg connections to be reset (i.e. transaction rollback() called)
and returned to the pool normally in the case that the connection were not
explicitly returned to the connection pool and was instead being
intercepted by Python garbage collection, which would fail if the garbage
collection operation were being called outside of the asyncio event loop,
leading to a large amount of stack trace activity dumped into logging
and standard output.
The correct behavior is restored, which is that all asyncio connections
that are garbage collected due to not being explicitly returned to the
connection pool are detached from the pool and discarded, along with a
warning, rather than being returned the pool, as they cannot be reliably
reset. In the case of asyncpg connections, the asyncpg-specific
terminate() method will be used to end the connection more gracefully
within this process as opposed to just dropping it.
This change includes a small behavioral change that is hoped to be useful
for debugging asyncio applications, where the warning that's emitted in the
case of asyncio connections being unexpectedly garbage collected has been
made slightly more aggressive by moving it outside of a try/except
block and into a finally: block, where it will emit unconditionally
regardless of whether the detach/termination operation succeeded or not. It
will also have the effect that applications or test suites which promote
Python warnings to exceptions will see this as a full exception raise,
whereas previously it was not possible for this warning to actually
propagate as an exception. Applications and test suites which need to
tolerate this warning in the interim should adjust the Python warnings
filter to allow these warnings to not raise.
The behavior for traditional sync connections remains unchanged, that garbage collected connections continue to be returned to the pool normally without emitting a warning. This will likely be changed in a future major release to at least emit a similar warning as is emitted for asyncio drivers, as it is a usage error for pooled connections to be intercepted by garbage collection without being properly returned to the pool.
References: #9237
[mysql] [bug] [regression] Fixed regression caused by issue #9058 which adjusted the MySQL
dialect's has_table() to again use "DESCRIBE", where the specific error
code raised by MySQL version 8 when using a non-existent schema name was
unexpected and failed to be interpreted as a boolean result.
References: #9251
[mysql] [bug] Added support for MySQL 8's new AS <name> ON DUPLICATE KEY syntax when
using _mysql.Insert.on_duplicate_key_update(), which is required for
newer versions of MySQL 8 as the previous syntax using VALUES() now
emits a deprecation warning with those versions. Server version detection
is employed to determine if traditional MariaDB / MySQL < 8 VALUES()
syntax should be used, vs. the newer MySQL 8 required syntax. Pull request
courtesy Caspar Wylie.
References: #8626
[sqlite] [bug] Fixed the SQLite dialect's has_table() function to correctly report
False for queries that include a non-None schema name for a schema that
doesn't exist; previously, a database error was raised.
References: #9251
[orm] [bug] [regression] Fixed regression where ORM models that used joined table inheritance with a composite foreign key would encounter an internal
Released: February 1, 2023
[orm] [bug] [regression] Fixed regression where ORM models that used joined table inheritance with a composite foreign key would encounter an internal error in the mapper internals.
References: #9164
[orm] [bug] Added support for PEP 484 NewType to be used in the
_orm.registry.type_annotation_map as well as within
Mapped constructs. These types will behave in the same way as
custom subclasses of types right now; they must appear explicitly within
the _orm.registry.type_annotation_map to be mapped.
References: #9175
[orm] [bug] When using the MappedAsDataclass superclass, all classes within
the hierarchy that are subclasses of this class will now be run through the
@dataclasses.dataclass function whether or not they are actually
mapped, so that non-ORM fields declared on non-mapped classes within the
hierarchy will be used when mapped subclasses are turned into dataclasses.
This behavior applies both to intermediary classes mapped with
__abstract__ = True as well as to the user-defined declarative base
itself, assuming MappedAsDataclass is present as a superclass for
these classes.
This allows non-mapped attributes such as InitVar declarations on
superclasses to be used, without the need to run the
@dataclasses.dataclass decorator explicitly on each non-mapped class.
The new behavior is considered as correct as this is what the PEP 681
implementation expects when using a superclass to indicate dataclass
behavior.
References: #9179
[orm] [bug] Added support for PEP 586 Literal[] to be used in the
_orm.registry.type_annotation_map as well as within
Mapped constructs. To use custom types such as these, they must
appear explicitly within the _orm.registry.type_annotation_map
to be mapped. Pull request courtesy Frederik Aalund.
As part of this change, the support for sqltypes.Enum in the
_orm.registry.type_annotation_map has been expanded to include
support for Literal[] types consisting of string values to be used,
in addition to enum.Enum datatypes. If a Literal[] datatype
is used within Mapped[] that is not linked in
_orm.registry.type_annotation_map to a specific datatype,
a sqltypes.Enum will be used by default.
References: #9187
[orm] [bug] Fixed issue involving the use of sqltypes.Enum within the
_orm.registry.type_annotation_map where the
_sqltypes.Enum.native_enum parameter would not be correctly
copied to the mapped column datatype, if it were overridden
as stated in the documentation to set this parameter to False.
References: #9200
[orm] [bug] [regression] Fixed regression in DeclarativeBase class where the registry's
default constructor would not be applied to the base itself, which is
different from how the previous _orm.declarative_base() construct
works. This would prevent a mapped class with its own __init__() method
from calling super().__init__() in order to access the registry's
default constructor and automatically populate attributes, instead hitting
object.__init__() which would raise a TypeError on any arguments.
References: #9171
[orm] [bug] Improved the ruleset used to interpret PEP 593 Annotated types when
used with Annotated Declarative mapping, the inner type will be checked for
"Optional" in all cases which will be added to the criteria by which the
column is set as "nullable" or not; if the type within the Annotated
container is optional (or unioned with None), the column will be
considered nullable if there are no explicit
_orm.mapped_column.nullable parameters overriding it.
References: #9177
[orm] [bug] Improved the error reporting when linking strategy options from a base
class to another attribute that's off a subclass, where of_type()
should be used. Previously, when Load.options() is used, the message
would lack informative detail that of_type() should be used, which was
not the case when linking the options directly. The informative detail now
emits even if Load.options() is used.
References: #9182
[sql] [bug] Corrected the fix for #7664, released in version 2.0.0, to also
include DropSchema which was inadvertently missed in this fix,
allowing stringification without a dialect. The fixes for both constructs
is backported to the 1.4 series as of 1.4.47.
References: #7664
[sql] [bug] [regression] Fixed regression related to the implementation for the new
"insertmanyvalues" feature where an internal TypeError would occur in
arrangements where a _sql.insert() would be referred towards inside
of another _sql.insert() via a CTE; made additional repairs for this
use case for positional dialects such as asyncpg when using
"insertmanyvalues".
References: #9173
[typing] [bug] Opened up typing on Select.with_for_update.of to also accept
table and mapped class arguments, as seems to be available for the MySQL
dialect.
References: #9174
[typing] [bug] Fixed typing for limit/offset methods including Select.limit(),
Select.offset(), _orm.Query.limit(), _orm.Query.offset()
to allow None, which is the documented API to "cancel" the current
limit/offset.
References: #9183
[typing] [bug] Fixed typing issue where _orm.mapped_column() objects typed as
_orm.Mapped wouldn't be accepted in schema constraints such as
_schema.ForeignKey, _schema.UniqueConstraint or
_schema.Index.
References: #9170
[typing] [bug] Fixed typing for _expression.ColumnElement.cast() to accept
both Type[TypeEngine[T]] and TypeEngine[T]; previously
only TypeEngine[T] was accepted. Pull request courtesy Yurii Karabas.
References: #9156
…still accept zero arguments which will emit a deprecation warning at runtime. Typing is also added to support sending the fixed literal False for _sql…
Released: January 26, 2023
[orm] [bug] Improved the notification of warnings that are emitted within the configure mappers or flush process, which are often invoked as part of a different operation, to add additional context to the message that indicates one of these operations as the source of the warning within operations that may not be obviously related.
References: #7305
[feature] [orm extensions] Added new option to horizontal sharding API
_horizontal.set_shard_id which sets the effective shard identifier
to query against, for both the primary query as well as for all secondary
loaders including relationship eager loaders as well as relationship and
column lazy loaders.
References: #7226
[usecase] [orm extensions] Added new feature to AutomapBase for autoload of classes across
multiple schemas which may have overlapping names, by providing a
AutomapBase.prepare.modulename_for_table parameter which
allows customization of the __module__ attribute of newly generated
classes, as well as a new collection AutomapBase.by_module, which
stores a dot-separated namespace of module names linked to classes based on
the __module__ attribute.
Additionally, the AutomapBase.prepare() method may now be invoked
any number of times, with or without reflection enabled; only newly
added tables that were not previously mapped will be processed on each
call. Previously, the MetaData.reflect() method would need to be
called explicitly each time.
References: #5145
[sql] [bug] Fixed stringify for a the CreateSchema DDL construct, which would
fail with an AttributeError when stringified without a dialect.
This change is also backported to: 1.4.47
References: #7664
[typing] [bug] Added typing for the built-in generic functions that are available from the
:data:_sql.func namespace, which accept a particular set of arguments and
return a particular type, such as for _sql.count,
_sql.current_timestamp, etc.
Unknown interpreted text role "data".
References: #9129
[typing] [bug] Corrected the type passed for "lambda statements" so that a plain lambda is
accepted by mypy, pyright, others without any errors about argument types.
Additionally implemented typing for more of the public API for lambda
statements and ensured StatementLambdaElement is part of the
Executable hierarchy so it's typed as accepted by
_engine.Connection.execute().
References: #9120
[typing] [bug] The _sql.ColumnOperators.in_() and
_sql.ColumnOperators.not_in() methods are typed to include
Iterable[Any] rather than Sequence[Any] for more flexibility in
argument type.
References: #9122
[typing] [bug] The _sql.or_() and _sql.and_() from a typing perspective
require the first argument to be present, however these functions still
accept zero arguments which will emit a deprecation warning at runtime.
Typing is also added to support sending the fixed literal False for
_sql.or_() and True for _sql.and_() as the first argument
only, however the documentation now indicates sending the
_sql.false() and _sql.true() constructs in these cases as a
more explicit approach.
References: #9123
[typing] [bug] Fixed typing issue where iterating over a _orm.Query object
was not correctly typed.
References: #9125
[typing] [bug] Fixed typing issue where the object type when using _engine.Result
as a context manager were not preserved, indicating _engine.Result
in all cases rather than the specific _engine.Result sub-type.
Pull request courtesy Martin Baláž.
References: #9136
[typing] [bug] Fixed issue where using the _orm.relationship.remote_side
and similar parameters, passing an annotated declarative object typed as
_orm.Mapped, would not be accepted by the type checker.
References: #9150
[typing] [bug] Added typing to legacy operators such as isnot(), notin_(), etc.
which previously were referencing the newer operators but were not
themselves typed.
References: #9148
[mssql] [bug] Fixed bug where a schema name given with brackets, but no dots inside the
name, for parameters such as _schema.Table.schema would not be
interpreted within the context of the SQL Server dialect's documented
behavior of interpreting explicit brackets as token delimiters, first added
in 1.2 for #2626, when referring to the schema name in reflection
operations. The original assumption for #2626's behavior was that the
special interpretation of brackets was only significant if dots were
present, however in practice, the brackets are not included as part of the
identifier name for all SQL rendering operations since these are not valid
characters within regular or delimited identifiers. Pull request courtesy
Shan.
This change is also backported to: 1.4.47
References: #9133
[mssql] [bug] Fixed bug where a schema name given with brackets, but no dots inside the
name, for parameters such as _schema.Table.schema would not be
interpreted within the context of the SQL Server dialect's documented
behavior of interpreting explicit brackets as token delimiters, first added
in 1.2 for #2626, when referring to the schema name in reflection
operations. The original assumption for #2626's behavior was that the
special interpretation of brackets was only significant if dots were
present, however in practice, the brackets are not included as part of the
identifier name for all SQL rendering operations since these are not valid
characters within regular or delimited identifiers. Pull request courtesy
Shan.
This change is also backported to: 1.4.47
References: #9133
[mssql] [bug] [regression] The newly added comment reflection and rendering capability of the MSSQL
dialect, added in #7844, will now be disabled by default if it
cannot be determined that an unsupported backend such as Azure Synapse may
be in use; this backend does not support table and column comments and does
not support the SQL Server routines in use to generate them as well as to
reflect them. A new parameter supports_comments is added to the dialect
which defaults to None, indicating that comment support should be
auto-detected. When set to True or False, the comment support is
either enabled or disabled unconditionally.
References: #9142
[oracle] [bug] Added _oracle.ROWID to reflected types as this type may be used in
a "CREATE TABLE" statement.
This change is also backported to: 1.4.47
References: #5047
[orm] [feature] Added a new parameter to _orm.Mapper called _orm.Mapper.polymorphic_abstract. The purpose of this directive is so that the ORM will no
Released: January 18, 2023
[orm] [feature] Added a new parameter to _orm.Mapper called
_orm.Mapper.polymorphic_abstract. The purpose of this directive
is so that the ORM will not consider the class to be instantiated or loaded
directly, only subclasses. The actual effect is that the
_orm.Mapper will prevent direct instantiation of instances
of the class and will expect that the class does not have a distinct
polymorphic identity configured.
In practice, the class that is mapped with
_orm.Mapper.polymorphic_abstract can be used as the target of a
_orm.relationship() as well as be used in queries; subclasses must of
course include polymorphic identities in their mappings.
The new parameter is automatically applied to classes that subclass
the AbstractConcreteBase class, as this class is not intended
to be instantiated.
References: #9060
[orm] [bug] Fixed issue where using a pep-593 Annotated type in the
_orm.registry.type_annotation_map which itself contained a
generic plain container or collections.abc type (e.g. list,
dict, collections.abc.Sequence, etc. ) as the target type would
produce an internal error when the ORM were trying to interpret the
Annotated instance.
References: #9099
[orm] [bug] Added an error message when a _orm.relationship() is mapped against
an abstract container type, such as Mapped[Sequence[B]], without
providing the _orm.relationship.container_class parameter which
is necessary when the type is abstract. Previously the the abstract
container would attempt to be instantiated at a later step and fail.
References: #9100
[sql] [bug] Fixed bug / regression where using bindparam() with the same name
as a column in the Update.values() method of Update, as
well as the _sql.Insert.values() method of _sql.Insert in
2.0 only, would in some cases silently fail to honor the SQL expression in
which the parameter were presented, replacing the expression with a new
parameter of the same name and discarding any other elements of the SQL
expression, such as SQL functions, etc. The specific case would be
statements that were constructed against ORM entities rather than plain
Table instances, but would occur if the statement were invoked
with a Session or a Connection.
Update part of the issue was present in both 2.0 and 1.4 and is
backported to 1.4.
This change is also backported to: 1.4.47
References: #9075
[sql] [bug] Fixed bug / regression where using bindparam() with the same name
as a column in the Update.values() method of Update, as
well as the _sql.Insert.values() method of _sql.Insert in
2.0 only, would in some cases silently fail to honor the SQL expression in
which the parameter were presented, replacing the expression with a new
parameter of the same name and discarding any other elements of the SQL
expression, such as SQL functions, etc. The specific case would be
statements that were constructed against ORM entities rather than plain
Table instances, but would occur if the statement were invoked
with a Session or a Connection.
Update part of the issue was present in both 2.0 and 1.4 and is
backported to 1.4.
This change is also backported to: 1.4.47
References: #9075
[typing] [bug] Fixes to the annotations within the sqlalchemy.ext.hybrid extension for
more effective typing of user-defined methods. The typing now uses
PEP 612 features, now supported by recent versions of Mypy, to maintain
argument signatures for hybrid_method. Return values for hybrid
methods are accepted as SQL expressions in contexts such as
_sql.Select.where() while still supporting SQL methods.
References: #9096
[mypy] [bug] Adjustments made to the mypy plugin to accommodate for some potential changes being made for issue #236 sqlalchemy2-stubs when using SQLAlchemy 1.4. These changes are being kept in sync within SQLAlchemy 2.0. The changes are also backwards compatible with older versions of sqlalchemy2-stubs.
This change is also backported to: 1.4.47
[mypy] [bug] Fixed crash in mypy plugin which could occur on both 1.4 and 2.0 versions
if a decorator for the _orm.registry.mapped() decorator were used
that was referenced in an expression with more than two components (e.g.
@Backend.mapper_registry.mapped). This scenario is now ignored; when
using the plugin, the decorator expression needs to be two components (i.e.
@reg.mapped).
This change is also backported to: 1.4.47
References: #9102
[postgresql] [bug] Fixed regression where psycopg3 changed an API call as of version 3.1.8 to expect a specific object type that was previously not enforced, breaking connectivity for the psycopg3 dialect.
References: #9106
[oracle] [usecase] Added support for the Oracle SQL type TIMESTAMP WITH LOCAL TIME ZONE,
using a newly added Oracle-specific _oracle.TIMESTAMP datatype.
References: #9086
[orm] [bug] Fixed issue where an overly restrictive ORM mapping rule were added in 2.0 which prevented mappings against TableClause objects, such as t
Released: January 9, 2023
[orm] [bug] Fixed issue where an overly restrictive ORM mapping rule were added in 2.0
which prevented mappings against TableClause objects, such as
those used in the view recipe on the wiki.
References: #9071
[typing] [bug] The Data Class Transforms argument field_descriptors was renamed
to field_specifiers in the accepted version of PEP 681.
References: #9067
[postgresql] [bug] Added support to the asyncpg dialect to return the cursor.rowcount
value for SELECT statements when available. While this is not a typical use
for cursor.rowcount, the other PostgreSQL dialects generally provide
this value. Pull request courtesy Michael Gorven.
This change is also backported to: 1.4.47
References: #9048
[postgresql] [json] Implemented missing JSONB operations:
- `@@` using `_postgresql.JSONB.Comparator.path_match()`
- `@?` using `_postgresql.JSONB.Comparator.path_exists()`
- `#-` using `_postgresql.JSONB.Comparator.delete_path()`
Pull request curtesy of Guilherme Martins Crocetti.
References: #7147
[mysql] [usecase] Added support to MySQL index reflection to correctly reflect the
mysql_length dictionary, which previously was being ignored.
This change is also backported to: 1.4.47
References: #9047
[mysql] [bug] Restored the behavior of Inspector.has_table() to report on
temporary tables for MySQL / MariaDB. This is currently the behavior for
all other included dialects, but was removed for MySQL in 1.4 due to no
longer using the DESCRIBE command; there was no documented support for temp
tables being reported by the Inspector.has_table() method in this
version or on any previous version, so the previous behavior was undefined.
As SQLAlchemy 2.0 has added formal support for temp table status via
Inspector.has_table(), the MySQL /MariaDB dialect has been reverted
to use the "DESCRIBE" statement as it did in the SQLAlchemy 1.3 series and
previously, and test support is added to include MySQL / MariaDB for
this behavior. The previous issues with ROLLBACK being emitted which
1.4 sought to improve upon don't apply in SQLAlchemy 2.0 due to
simplifications in how Connection handles transactions.
DESCRIBE is necessary as MariaDB in particular has no consistently available public information schema of any kind in order to report on temp tables other than DESCRIBE/SHOW COLUMNS, which rely on throwing an error in order to report no results.
References: #9058
[oracle] [bug] Supported use case for foreign key constraints where the local column is
marked as "invisible". The errors normally generated when a
ForeignKeyConstraint is created that check for the target column
are disabled when reflecting, and the constraint is skipped with a warning
in the same way which already occurs for an Index with a similar
issue.
References: #9059
ShardedSession.id_chooser is still accepted in place of ShardedSession.identity_chooser with a deprecation warning. References: #7837
Released: December 28, 2022
[general] [bug] Fixed regression where the base compat module was calling upon
platform.architecture() in order to detect some system properties,
which results in an over-broad system call against the system-level
file call that is unavailable under some circumstances, including
within some secure environment configurations.
This change is also backported to: 1.4.46
References: #8995
[orm] [feature] Added a new default value for the Mapper.eager_defaults
parameter "auto", which will automatically fetch table default values
during a unit of work flush, if the dialect supports RETURNING for the
INSERT being run, as well as
insertmanyvalues <engine_insertmanyvalues> available. Eager fetches
for server-side UPDATE defaults, which are very uncommon, continue to only
take place if Mapper.eager_defaults is set to True, as
there is no batch-RETURNING form for UPDATE statements.
References: #8889
[orm] [usecase] Adjustments to the _orm.Session in terms of extensibility,
as well as updates to the ShardedSession extension:
- `_orm.Session.get()` now accepts
`_orm.Session.get.bind_arguments`, which in particular may be
useful when using the horizontal sharding extension.
- `_orm.Session.get_bind()` accepts arbitrary kw arguments, which
assists in developing code that uses a `_orm.Session` class which
overrides this method with additional arguments.
- Added a new ORM execution option `identity_token` which may be used
to directly affect the "identity token" that will be associated with
newly loaded ORM objects. This token is how sharding approaches
(namely the `ShardedSession`, but can be used in other cases
as well) separate object identities across different "shards".
- The `_orm.SessionEvents.do_orm_execute()` event hook may now be used
to affect all ORM-related options, including `autoflush`,
`populate_existing`, and `yield_per`; these options are re-consumed
subsequent to event hooks being invoked before they are acted upon.
Previously, options like `autoflush` would have been already evaluated
at this point. The new `identity_token` option is also supported in
this mode and is now used by the horizontal sharding extension.
- The `ShardedSession` class replaces the
`ShardedSession.id_chooser` hook with a new hook
`ShardedSession.identity_chooser`, which no longer relies upon
the legacy `_orm.Query` object.
`ShardedSession.id_chooser` is still accepted in place of
`ShardedSession.identity_chooser` with a deprecation warning.
References: #7837
[orm] [usecase] The behavior of "joining an external transaction into a Session" has been
revised and improved, allowing explicit control over how the
_orm.Session will accommodate an incoming
_engine.Connection that already has a transaction and possibly a
savepoint already established. The new parameter
_orm.Session.join_transaction_mode includes a series of option
values which can accommodate the existing transaction in several ways, most
importantly allowing a _orm.Session to operate in a fully
transactional style using savepoints exclusively, while leaving the
externally initiated transaction non-committed and active under all
circumstances, allowing test suites to rollback all changes that take place
within tests.
Additionally, revised the _orm.Session.close() method to fully close
out savepoints that may still be present, which also allows the
"external transaction" recipe to proceed without warnings if the
_orm.Session did not explicitly end its own SAVEPOINT
transactions.
References: #9015
[orm] [usecase] Removed the requirement that the __allow_unmapped__ attribute be used
on Declarative Dataclass Mapped class when non-Mapped[] annotations are
detected; previously, an error message that was intended to support legacy
ORM typed mappings would be raised, which additionally did not mention
correct patterns to use with Dataclasses specifically. This error message
is now no longer raised if _orm.registry.mapped_as_dataclass() or
_orm.MappedAsDataclass is used.
References: #8973
[orm] [bug] Fixed issue in the internal SQL traversal for DML statements like
_dml.Update and _dml.Delete which would cause among other
potential issues, a specific issue using lambda statements with the ORM
update/delete feature.
This change is also backported to: 1.4.46
References: #9033
[orm] [bug] Fixed bug where _orm.Session.merge() would fail to preserve the
current loaded contents of relationship attributes that were indicated with
the _orm.relationship.viewonly parameter, thus defeating
strategies that use _orm.Session.merge() to pull fully loaded objects
from caches and other similar techniques. In a related change, fixed issue
where an object that contains a loaded relationship that was nonetheless
configured as lazy='raise' on the mapping would fail when passed to
_orm.Session.merge(); checks for "raise" are now suspended within
the merge process assuming the _orm.Session.merge.load
parameter remains at its default of True.
Overall, this is a behavioral adjustment to a change introduced in the 1.4
series as of #4994, which took "merge" out of the set of cascades
applied by default to "viewonly" relationships. As "viewonly" relationships
aren't persisted under any circumstances, allowing their contents to
transfer during "merge" does not impact the persistence behavior of the
target object. This allows _orm.Session.merge() to correctly suit one
of its use cases, that of adding objects to a Session that were
loaded elsewhere, often for the purposes of restoring from a cache.
This change is also backported to: 1.4.45
References: #8862
[orm] [bug] Fixed issues in _orm.with_expression() where expressions that were
composed of columns that were referenced from the enclosing SELECT would
not render correct SQL in some contexts, in the case where the expression
had a label name that matched the attribute which used
_orm.query_expression(), even when _orm.query_expression() had
no default expression. For the moment, if the _orm.query_expression()
does have a default expression, that label name is still used for that
default, and an additional label with the same name will continue to be
ignored. Overall, this case is pretty thorny so further adjustments might
be warranted.
This change is also backported to: 1.4.45
References: #8881
[orm] [bug] A warning is emitted if a backref name used in _orm.relationship()
names an attribute on the target class which already has a method or
attribute assigned to that name, as the backref declaration will replace
that attribute.
References: #4629
[orm] [bug] A series of changes and improvements regarding
_orm.Session.refresh(). The overall change is that primary key
attributes for an object are now included in a refresh operation
unconditionally when relationship-bound attributes are to be refreshed,
even if not expired and even if not specified in the refresh.
- Improved `_orm.Session.refresh()` so that if autoflush is enabled
(as is the default for `_orm.Session`), the autoflush takes place
at an earlier part of the refresh process so that pending primary key
changes are applied without errors being raised. Previously, this
autoflush took place too late in the process and the SELECT statement
would not use the correct key to locate the row and an
`InvalidRequestError` would be raised.
- When the above condition is present, that is, unflushed primary key
changes are present on the object, but autoflush is not enabled,
the refresh() method now explicitly disallows the operation to proceed,
and an informative `InvalidRequestError` is raised asking that
the pending primary key changes be flushed first. Previously,
this use case was simply broken and `InvalidRequestError`
would be raised anyway. This restriction is so that it's safe for the
primary key attributes to be refreshed, as is necessary for the case of
being able to refresh the object with relationship-bound secondary
eagerloaders also being emitted. This rule applies in all cases to keep
API behavior consistent regardless of whether or not the PK cols are
actually needed in the refresh, as it is unusual to be refreshing
some attributes on an object while keeping other attributes "pending"
in any case.
- The `_orm.Session.refresh()` method has been enhanced such that
attributes which are `_orm.relationship()`-bound and linked to an
eager loader, either at mapping time or via last-used loader options,
will be refreshed in all cases even when a list of attributes is passed
that does not include any columns on the parent row. This builds upon the
feature first implemented for non-column attributes as part of
[#1763](https://www.sqlalchemy.org/trac/ticket/1763) fixed in 1.4 allowing eagerly-loaded relationship-bound
attributes to participate in the `_orm.Session.refresh()` operation.
If the refresh operation does not indicate any columns on the parent row
to be refreshed, the primary key columns will nonetheless be included
in the refresh operation, which allows the load to proceed into the
secondary relationship loaders indicated as it does normally.
Previously an `InvalidRequestError` error would be raised
for this condition ([#8703](https://www.sqlalchemy.org/trac/ticket/8703))
- Fixed issue where an unnecessary additional SELECT would be emitted in
the case where `_orm.Session.refresh()` were called with a
combination of expired attributes, as well as an eager loader such as
`_orm.selectinload()` that emits a "secondary" query, if the primary
key attributes were also in an expired state. As the primary key
attributes are now included in the refresh automatically, there is no
additional load for these attributes when a relationship loader
goes to select for them ([#8997](https://www.sqlalchemy.org/trac/ticket/8997))
- Fixed regression caused by [#8126](https://www.sqlalchemy.org/trac/ticket/8126) released in 2.0.0b1 where the
`_orm.Session.refresh()` method would fail with an
`AttributeError`, if passed both an expired column name as well as the
name of a relationship-bound attribute that was linked to a "secondary"
eagerloader such as the `_orm.selectinload()` eager loader
([#8996](https://www.sqlalchemy.org/trac/ticket/8996))
[orm] [bug] Improved a fix first made in version 1.4 for #8456 which scaled
back the usage of internal "polymorphic adapters", that are used to render
ORM queries when the _orm.Mapper.with_polymorphic parameter is
used. These adapters, which are very complex and error prone, are now used
only in those cases where an explicit user-supplied subquery is used for
_orm.Mapper.with_polymorphic, which includes only the use case
of concrete inheritance mappings that use the
_orm.polymorphic_union() helper, as well as the legacy use case of
using an aliased subquery for joined inheritance mappings, which is not
needed in modern use.
For the most common case of joined inheritance mappings that use the
built-in polymorphic loading scheme, which includes those which make use of
the _orm.Mapper.polymorphic_load parameter set to inline,
polymorphic adapters are now no longer used. This has both a positive
performance impact on the construction of queries as well as a
substantial simplification of the internal query rendering process.
The specific issue targeted was to allow a _orm.column_property()
to refer to joined-inheritance classes within a scalar subquery, which now
works as intuitively as is feasible.
References: #8168
[engine] [bug] Fixed a long-standing race condition in the connection pool which could
occur under eventlet/gevent monkeypatching schemes in conjunction with the
use of eventlet/gevent Timeout conditions, where a connection pool
checkout that's interrupted due to the timeout would fail to clean up the
failed state, causing the underlying connection record and sometimes the
database connection itself to "leak", leaving the pool in an invalid state
with unreachable entries. This issue was first identified and fixed in
SQLAlchemy 1.2 for #4225, however the failure modes detected in
that fix failed to accommodate for BaseException, rather than
Exception, which prevented eventlet/gevent Timeout from being
caught. In addition, a block within initial pool connect has also been
identified and hardened with a BaseException -> "clean failed connect"
block to accommodate for the same condition in this location.
Big thanks to Github user @niklaus for their tenacious efforts in
identifying and describing this intricate issue.
This change is also backported to: 1.4.46
References: #8974
[engine] [bug] Fixed a long-standing race condition in the connection pool which could
occur under eventlet/gevent monkeypatching schemes in conjunction with the
use of eventlet/gevent Timeout conditions, where a connection pool
checkout that's interrupted due to the timeout would fail to clean up the
failed state, causing the underlying connection record and sometimes the
database connection itself to "leak", leaving the pool in an invalid state
with unreachable entries. This issue was first identified and fixed in
SQLAlchemy 1.2 for #4225, however the failure modes detected in
that fix failed to accommodate for BaseException, rather than
Exception, which prevented eventlet/gevent Timeout from being
caught. In addition, a block within initial pool connect has also been
identified and hardened with a BaseException -> "clean failed connect"
block to accommodate for the same condition in this location.
Big thanks to Github user @niklaus for their tenacious efforts in
identifying and describing this intricate issue.
This change is also backported to: 1.4.46
References: #8974
[engine] [bug] Fixed issue where _engine.Result.freeze() method would not work for
textual SQL using either _sql.text() or
_engine.Connection.exec_driver_sql().
This change is also backported to: 1.4.45
References: #8963
[sql] [usecase] An informative re-raise is now thrown in the case where any "literal bindparam" render operation fails, indicating the value itself and the datatype in use, to assist in debugging when literal params are being rendered in a statement.
This change is also backported to: 1.4.45
References: #8800
[sql] [bug] Added parameter
FunctionElement.column_valued.joins_implicitly, which is
useful in preventing the "cartesian product" warning when making use of
table-valued or column-valued functions. This parameter was already
introduced for FunctionElement.table_valued() in #7845,
however it failed to be added for FunctionElement.column_valued()
as well.
This change is also backported to: 1.4.46
References: #9009
[sql] [bug] Fixed bug where SQL compilation would fail (assertion fail in 2.0, NoneType
error in 1.4) when using an expression whose type included
_types.TypeEngine.bind_expression(), in the context of an "expanding"
(i.e. "IN") parameter in conjunction with the literal_binds compiler
parameter.
This change is also backported to: 1.4.46
References: #8989
[sql] [bug] Fixed issue in lambda SQL feature where the calculated type of a literal
value would not take into account the type coercion rules of the "compared
to type", leading to a lack of typing information for SQL expressions, such
as comparisons to _types.JSON elements and similar.
This change is also backported to: 1.4.46
References: #9029
[sql] [bug] Added parameter
FunctionElement.column_valued.joins_implicitly, which is
useful in preventing the "cartesian product" warning when making use of
table-valued or column-valued functions. This parameter was already
introduced for FunctionElement.table_valued() in #7845,
however it failed to be added for FunctionElement.column_valued()
as well.
This change is also backported to: 1.4.46
References: #9009
[sql] [bug] Fixed a series of issues regarding the position and sometimes the identity
of rendered bound parameters, such as those used for SQLite, asyncpg,
MySQL, Oracle and others. Some compiled forms would not maintain the order
of parameters correctly, such as the PostgreSQL regexp_replace()
function, the "nesting" feature of the CTE construct first
introduced in #4123, and selectable tables formed by using the
FunctionElement.column_valued() method with Oracle.
This change is also backported to: 1.4.45
References: #8827
[sql] [bug] Added test support to ensure that all compiler visit_xyz() methods
across all Compiler implementations in SQLAlchemy accept a
**kw parameter, so that all compilers accept additional keyword
arguments under all circumstances.
References: #8988
[sql] [bug] The SQLCompiler.construct_params() method, as well as the
SQLCompiler.params accessor, will now return the
exact parameters that correspond to a compiled statement that used
the render_postcompile parameter to compile. Previously,
the method returned a parameter structure that by itself didn't correspond
to either the original parameters or the expanded ones.
Passing a new dictionary of parameters to
SQLCompiler.construct_params() for a SQLCompiler that was
constructed with render_postcompile is now disallowed; instead, to make
a new SQL string and parameter set for an alternate set of parameters, a
new method SQLCompiler.construct_expanded_state() is added which
will produce a new expanded form for the given parameter set, using the
ExpandedState container which includes a new SQL statement
and new parameter dictionary, as well as a positional parameter tuple.
References: #6114
[sql] [bug] To accommodate for third party dialects with different character escaping
needs regarding bound parameters, the system by which SQLAlchemy "escapes"
(i.e., replaces with another character in its place) special characters in
bound parameter names has been made extensible for third party dialects,
using the SQLCompiler.bindname_escape_chars dictionary which can
be overridden at the class declaration level on any SQLCompiler
subclass. As part of this change, also added the dot "." as a default
"escaped" character.
References: #8994
[typing] [bug] pep-484 typing has been completed for the
sqlalchemy.ext.horizontal_shard extension as well as the
sqlalchemy.orm.events module. Thanks to Gleb Kisenkov for their
efforts.
[asyncio] [bug] Removed non-functional merge() method from
_asyncio.AsyncResult. This method has never worked and was
included with _asyncio.AsyncResult in error.
This change is also backported to: 1.4.45
References: #8952
[postgresql] [usecase] Added the PostgreSQL type MACADDR8.
Pull request courtesy of Asim Farooq.
This change is also backported to: 1.4.46
References: #8393
[postgresql] [bug] Fixed bug where the PostgreSQL
_postgresql.OnConflictClause.constraint parameter would accept
an Index object, however would not expand this index out into its
individual index expressions, instead rendering its name in an ON CONFLICT
ON CONSTRAINT clause, which is not accepted by PostgreSQL; the "constraint
name" form only accepts unique or exclude constraint names. The parameter
continues to accept the index but now expands it out into its component
expressions for the render.
This change is also backported to: 1.4.46
References: #9023
[postgresql] [bug] Fixed bug where the PostgreSQL
_postgresql.OnConflictClause.constraint parameter would accept
an Index object, however would not expand this index out into its
individual index expressions, instead rendering its name in an ON CONFLICT
ON CONSTRAINT clause, which is not accepted by PostgreSQL; the "constraint
name" form only accepts unique or exclude constraint names. The parameter
continues to accept the index but now expands it out into its component
expressions for the render.
This change is also backported to: 1.4.46
References: #9023
[postgresql] [bug] Made an adjustment to how the PostgreSQL dialect considers column types
when it reflects columns from a table, to accommodate for alternative
backends which may return NULL from the PG format_type() function.
This change is also backported to: 1.4.45
References: #8748
[postgresql] [bug] Added support for explicit use of PG full text functions with asyncpg and
psycopg (SQLAlchemy 2.0 only), with regards to the REGCONFIG type cast
for the first argument, which previously would be incorrectly cast to a
VARCHAR, causing failures on these dialects that rely upon explicit type
casts. This includes support for _postgresql.to_tsvector,
_postgresql.to_tsquery, _postgresql.plainto_tsquery,
_postgresql.phraseto_tsquery,
_postgresql.websearch_to_tsquery,
_postgresql.ts_headline, each of which will determine based on
number of arguments passed if the first string argument should be
interpreted as a PostgreSQL "REGCONFIG" value; if so, the argument is typed
using a newly added type object _postgresql.REGCONFIG which is
then explicitly cast in the SQL expression.
References: #8977
[postgresql] [bug] Fixed regression where newly revised PostgreSQL range types such as
_postgresql.INT4RANGE could not be set up as the impl of a
TypeDecorator custom type, instead raising a TypeError.
References: #9020
[postgresql] [bug] The _postgresql.Range.__eq___() will now return NotImplemented
when comparing with an instance of a different class, instead of raising
an AttributeError exception.
References: #8984
[sqlite] [usecase] Added support for the SQLite backend to reflect the "DEFERRABLE" and "INITIALLY" keywords which may be present on a foreign key construct. Pull request courtesy Michael Gorven.
This change is also backported to: 1.4.45
References: #8903
[sqlite] [usecase] Added support for reflection of expression-oriented WHERE criteria included in indexes on the SQLite dialect, in a manner similar to that of the PostgreSQL dialect. Pull request courtesy Tobias Pfeiffer.
This change is also backported to: 1.4.45
References: #8804
[sqlite] [bug] Fixed regression caused by new support for reflection of partial indexes on
SQLite added in 1.4.45 for #8804, where the index_list pragma
command in very old versions of SQLite (possibly prior to 3.8.9) does not
return the current expected number of columns, leading to exceptions raised
when reflecting tables and indexes.
This change is also backported to: 1.4.46
References: #8969
[oracle] [bug] Fixed issue in Oracle compiler where the syntax for
FunctionElement.column_valued() was incorrect, rendering the name
COLUMN_VALUE without qualifying the source table correctly.
This change is also backported to: 1.4.45
References: #8945
[tests] [bug] Fixed issue in tox.ini file where changes in the tox 4.0 series to the format of "passenv" caused tox to not function correctly, in particular raising an error as of tox 4.0.6.
This change is also backported to: 1.4.46
[tests] [bug] Added new exclusion rule for third party dialects called
unusual_column_name_characters, which can be "closed" for third party
dialects that don't support column names with unusual characters such as
dots, slashes, or percent signs in them, even if the name is properly
quoted.
This change is also backported to: 1.4.46
References: #9002
[schema] [bug] Stricter rules are in place for appending of Column objects to Table objects, both moving some previous deprecation warnings to excepti…
Released: December 5, 2022
[orm] [feature] Added a new parameter _orm.mapped_column.use_existing_column to
accommodate the use case of a single-table inheritance mapping that uses
the pattern of more than one subclass indicating the same column to take
place on the superclass. This pattern was previously possible by using
_orm.declared_attr() in conjunction with locating the existing column
in the .__table__ of the superclass, however is now updated to work
with _orm.mapped_column() as well as with pep-484 typing, in a
simple and succinct way.
References: #8822
[orm] [usecase] Added support custom user-defined types which extend the Python
enum.Enum base class to be resolved automatically
to SQLAlchemy Enum SQL types, when using the Annotated
Declarative Table feature. The feature is made possible through new
lookup features added to the ORM type map feature, and includes support
for changing the arguments of the Enum that's generated by
default as well as setting up specific enum.Enum types within
the map with specific arguments.
References: #8859
[orm] [usecase] Added _orm.mapped_column.compare parameter to relevant ORM
attribute constructs including _orm.mapped_column(),
_orm.relationship() etc. to provide for the Python dataclasses
compare parameter on field(), when using the
orm_declarative_native_dataclasses feature. Pull request courtesy
Simon Schiele.
References: #8905
[orm] [bug] Fixed bug where _orm.Session.merge() would fail to preserve the
current loaded contents of relationship attributes that were indicated with
the _orm.relationship.viewonly parameter, thus defeating
strategies that use _orm.Session.merge() to pull fully loaded objects
from caches and other similar techniques. In a related change, fixed issue
where an object that contains a loaded relationship that was nonetheless
configured as lazy='raise' on the mapping would fail when passed to
_orm.Session.merge(); checks for "raise" are now suspended within
the merge process assuming the _orm.Session.merge.load
parameter remains at its default of True.
Overall, this is a behavioral adjustment to a change introduced in the 1.4
series as of #4994, which took "merge" out of the set of cascades
applied by default to "viewonly" relationships. As "viewonly" relationships
aren't persisted under any circumstances, allowing their contents to
transfer during "merge" does not impact the persistence behavior of the
target object. This allows _orm.Session.merge() to correctly suit one
of its use cases, that of adding objects to a Session that were
loaded elsewhere, often for the purposes of restoring from a cache.
This change is also backported to: 1.4.45
References: #8862
[orm] [bug] Fixed issues in _orm.with_expression() where expressions that were
composed of columns that were referenced from the enclosing SELECT would
not render correct SQL in some contexts, in the case where the expression
had a label name that matched the attribute which used
_orm.query_expression(), even when _orm.query_expression() had
no default expression. For the moment, if the _orm.query_expression()
does have a default expression, that label name is still used for that
default, and an additional label with the same name will continue to be
ignored. Overall, this case is pretty thorny so further adjustments might
be warranted.
This change is also backported to: 1.4.45
References: #8881
[orm] [bug] Fixed bug where _orm.Session.merge() would fail to preserve the
current loaded contents of relationship attributes that were indicated with
the _orm.relationship.viewonly parameter, thus defeating
strategies that use _orm.Session.merge() to pull fully loaded objects
from caches and other similar techniques. In a related change, fixed issue
where an object that contains a loaded relationship that was nonetheless
configured as lazy='raise' on the mapping would fail when passed to
_orm.Session.merge(); checks for "raise" are now suspended within
the merge process assuming the _orm.Session.merge.load
parameter remains at its default of True.
Overall, this is a behavioral adjustment to a change introduced in the 1.4
series as of #4994, which took "merge" out of the set of cascades
applied by default to "viewonly" relationships. As "viewonly" relationships
aren't persisted under any circumstances, allowing their contents to
transfer during "merge" does not impact the persistence behavior of the
target object. This allows _orm.Session.merge() to correctly suit one
of its use cases, that of adding objects to a Session that were
loaded elsewhere, often for the purposes of restoring from a cache.
This change is also backported to: 1.4.45
References: #8862
[orm] [bug] Fixed issues in _orm.with_expression() where expressions that were
composed of columns that were referenced from the enclosing SELECT would
not render correct SQL in some contexts, in the case where the expression
had a label name that matched the attribute which used
_orm.query_expression(), even when _orm.query_expression() had
no default expression. For the moment, if the _orm.query_expression()
does have a default expression, that label name is still used for that
default, and an additional label with the same name will continue to be
ignored. Overall, this case is pretty thorny so further adjustments might
be warranted.
This change is also backported to: 1.4.45
References: #8881
[orm] [bug] Fixed issue where use of an unknown datatype within a Mapped
annotation for a column-based attribute would silently fail to map the
attribute, rather than reporting an exception; an informative exception
message is now raised.
References: #8888
[orm] [bug] Fixed a suite of issues involving Mapped use with dictionary
types, such as Mapped[dict[str, str] | None], would not be correctly
interpreted in Declarative ORM mappings. Support to correctly
"de-optionalize" this type including for lookup in type_annotation_map
has been fixed.
References: #8777
[orm] [bug] [performance] Additional performance enhancements within ORM-enabled SQL statements,
specifically targeting callcounts within the construction of ORM
statements, using combinations of _orm.aliased() with
_sql.union() and similar "compound" constructs, in addition to direct
performance improvements to the corresponding_column() internal method
that is used heavily by the ORM by constructs like _orm.aliased() and
similar.
References: #8796
[orm] [bug] Fixed bug in orm_declarative_native_dataclasses feature where using
plain dataclass fields with the __allow_unmapped__ directive in a
mapping would not create a dataclass with the correct class-level state for
those fields, copying the raw Field object to the class inappropriately
after dataclasses itself had replaced the Field object with the
class-level default value.
References: #8880
[orm] [bug] [regression] Fixed regression where flushing a mapped class that's mapped against a
subquery, such as a direct mapping or some forms of concrete table
inheritance, would fail if the _orm.Mapper.eager_defaults
parameter were used.
References: #8812
[orm] [bug] Fixed regression in 2.0.0b3 caused by #8759 where indicating the
Mapped name using a qualified name such as
sqlalchemy.orm.Mapped would fail to be recognized by Declarative as
indicating the Mapped construct.
References: #8853
[usecase] [orm extensions] Added support for the association_proxy() extension function to
take part within Python dataclasses configuration, when using
the native dataclasses feature described at
orm_declarative_native_dataclasses. Included are attribute-level
arguments including association_proxy.init and
association_proxy.default_factory.
Documentation for association proxy has also been updated to use
"Annotated Declarative Table" forms within examples, including type
annotations used for AssocationProxy itself.
References: #8878
[sql] [usecase] An informative re-raise is now thrown in the case where any "literal bindparam" render operation fails, indicating the value itself and the datatype in use, to assist in debugging when literal params are being rendered in a statement.
This change is also backported to: 1.4.45
References: #8800
[sql] [usecase] Added _expression.ScalarValues that can be used as a column
element allowing using _expression.Values inside IN clauses
or in conjunction with ANY or ALL collection aggregates.
This new class is generated using the method
_expression.Values.scalar_values().
The _expression.Values instance is now coerced to a
_expression.ScalarValues when used in a IN or NOT IN
operation.
References: #6289
[sql] [bug] Fixed a series of issues regarding positionally rendered bound parameters,
such as those used for SQLite, asyncpg, MySQL and others. Some compiled
forms would not maintain the order of parameters correctly, such as the
PostgreSQL regexp_replace() function as well as within the "nesting"
feature of the CTE construct first introduced in #4123.
This change is also backported to: 1.4.45
References: #8827
[sql] [bug] Fixed critical memory issue identified in cache key generation, where for very large and complex ORM statements that make use of lots of ORM aliases with subqueries, cache key generation could produce excessively large keys that were orders of magnitude bigger than the statement itself. Much thanks to Rollo Konig Brock for their very patient, long term help in finally identifying this issue.
This change is also backported to: 1.4.44
References: #8790
[sql] [bug] The approach to the numeric pep-249 paramstyle has been rewritten, and
is now fully supported, including by features such as "expanding IN" and
"insertmanyvalues". Parameter names may also be repeated in the source SQL
construct which will be correctly represented within the numeric format
using a single parameter. Introduced an additional numeric paramstyle
called numeric_dollar, which is specifically what's used by the asyncpg
dialect; the paramstyle is equivalent to numeric except numeric
indicators are indicated by a dollar-sign rather than a colon. The asyncpg
dialect now uses numeric_dollar paramstyle directly, rather than
compiling to format style first.
The numeric and numeric_dollar paramstyles assume that the target
backend is capable of receiving the numeric parameters in any order,
and will match the given parameter values to the statement based on
matching their position (1-based) to the numeric indicator. This is the
normal behavior of "numeric" paramstyles, although it was observed that
the SQLite DBAPI implements a not-used "numeric" style that does not honor
parameter ordering.
References: #8849
[sql] [bug] Adjusted the rendering of RETURNING, in particular when using
_sql.Insert, such that it now renders columns using the same logic
as that of the Select construct to generate labels, which will
include disambiguating labels, as well as that a SQL function surrounding a
named column will be labeled using the column name itself. This establishes
better cross-compatibility when selecting rows from either Select
constructs or from DML statements that use UpdateBase.returning(). A
narrower scale change was also made for the 1.4 series that adjusted the
function label issue only.
References: #8770
[schema] [bug] Stricter rules are in place for appending of Column objects to
Table objects, both moving some previous deprecation warnings to
exceptions, and preventing some previous scenarios that would cause
duplicate columns to appear in tables, when
Table.extend_existing were set to True, for both
programmatic Table construction as well as during reflection
operations.
See change_8925 for a rundown of these changes.
References: #8925
[typing] [usecase] Added a new type SQLColumnExpression which may be indicated in
user code to represent any SQL column oriented expression, including both
those based on ColumnElement as well as on ORM
QueryableAttribute. This type is a real class, not an alias, so
can also be used as the foundation for other objects. An additional
ORM-specific subclass SQLORMExpression is also included.
References: #8847
[typing] [bug] Adjusted internal use of the Python enum.IntFlag class which changed
its behavioral contract in Python 3.11. This was not causing runtime
failures however caused typing runs to fail under Python 3.11.
References: #8783
[typing] [bug] The sqlalchemy.ext.mutable extension and sqlalchemy.ext.automap
extensions are now fully pep-484 typed. Huge thanks to Gleb Kisenkov for
their efforts on this.
[typing] [bug] Corrected typing support for the _orm.relationship.secondary
argument which may also accept a callable (lambda) that returns a
FromClause.
[typing] [bug] Improved the typing for sessionmaker and
async_sessionmaker, so that the default type of their return value
will be Session or AsyncSession, without the need to
type this explicitly. Previously, Mypy would not automaticaly infer these
return types from its generic base.
As part of this change, arguments for Session,
AsyncSession, sessionmaker and
async_sessionmaker beyond the initial "bind" argument have been
made keyword-only, which includes parameters that have always been
documented as keyword arguments, such as Session.autoflush,
Session.class_, etc.
Pull request courtesy Sam Bull.
References: #8842
[typing] [bug] Fixed issue where passing a callbale function returning an iterable
of column elements to _orm.relationship.order_by was
flagged as an error in type checkers.
References: #8776
[postgresql] [usecase] Complementing #8690, new comparison methods such as
_postgresql.Range.adjacent_to(),
_postgresql.Range.difference(), _postgresql.Range.union(),
etc., were added to the PG-specific range objects, bringing them in par
with the standard operators implemented by the underlying
_postgresql.AbstractRange.comparator_factory.
In addition, the __bool__() method of the class has been corrected to
be consistent with the common Python containers behavior as well as how
other popular PostgreSQL drivers do: it now tells whether the range
instance is not empty, rather than the other way around.
Pull request courtesy Lele Gaifax.
References: #8765
[postgresql] [change] [asyncpg] Changed the paramstyle used by asyncpg from format to
numeric_dollar. This has two main benefits since it does not require
additional processing of the statement and allows for duplicate parameters
to be present in the statements.
References: #8926
[postgresql] [bug] Made an adjustment to how the PostgreSQL dialect considers column types
when it reflects columns from a table, to accommodate for alternative
backends which may return NULL from the PG format_type() function.
This change is also backported to: 1.4.45
References: #8748
[postgresql] [bug] [mssql] For the PostgreSQL and SQL Server dialects only, adjusted the compiler so that when rendering column expressions in the RETURNING clause, the "non anon" label that's used in SELECT statements is suggested for SQL expression elements that generate a label; the primary example is a SQL function that may be emitting as part of the column's type, where the label name should match the column's name by default. This restores a not-well defined behavior that had changed in version 1.4.21 due to #6718, #6710. The Oracle dialect has a different RETURNING implementation and was not affected by this issue. Version 2.0 features an across the board change for its widely expanded support of RETURNING on other backends.
This change is also backported to: 1.4.44
References: #8770
[postgresql] [bug] Added additional type-detection for the new PostgreSQL
_postgresql.Range type, where previous cases that allowed the
psycopg2-native range objects to be received directly by the DBAPI without
SQLAlchemy intercepting them stopped working, as we now have our own value
object. The _postgresql.Range object has been enhanced such that
SQLAlchemy Core detects it in otherwise ambiguous situations (such as
comparison to dates) and applies appropriate bind handlers. Pull request
courtesy Lele Gaifax.
References: #8884
[sqlite] [usecase] Added support for the SQLite backend to reflect the "DEFERRABLE" and "INITIALLY" keywords which may be present on a foreign key construct. Pull request courtesy Michael Gorven.
This change is also backported to: 1.4.45
References: #8903
[sqlite] [usecase] Added support for reflection of expression-oriented WHERE criteria included in indexes on the SQLite dialect, in a manner similar to that of the PostgreSQL dialect. Pull request courtesy Tobias Pfeiffer.
This change is also backported to: 1.4.45
References: #8804
[mssql] [bug] Fixed regression caused by the combination of #8177, re-enable setinputsizes for SQL server unless fast_executemany + DBAPI executemany is used for a statement, along with #6047, implement "insertmanyvalues", which bypasses DBAPI executemany in place of a custom DBAPI execute for INSERT statements. setinputsizes would incorrectly not be used for a multiple parameter-set INSERT statement that used "insertmanyvalues" if fast_executemany were turned on, as the check would incorrectly assume this is a DBAPI executemany call. The "regression" would then be that the "insertmanyvalues" statement format is apparently slightly more sensitive to multiple rows that don't use the same types for each row, so in such a case setinputsizes is especially needed.
The fix repairs the fast_executemany check so that it only disables setinputsizes if true DBAPI executemany is to be used.
References: #8917
[oracle] [bug] Continued fixes for Oracle fix #8708 released in 1.4.43 where bound parameter names that start with underscores, which are disallowed by Oracle, were still not being properly escaped in all circumstances.
This change is also backported to: 1.4.45
References: #8708
[tests] [bug] Fixed issue where the --disable-asyncio parameter to the test suite
would fail to not actually run greenlet tests and would also not prevent
the suite from using a "wrapping" greenlet for the whole suite. This
parameter now ensures that no greenlet or asyncio use will occur within the
entire run when set.
This change is also backported to: 1.4.44
References: #8793
[engine] [usecase] Added new parameter PoolEvents.reset.reset_state parameter to the PoolEvents.reset() event, with deprecation logic in place that wi…
Released: November 4, 2022
[orm] [declarative] [bug] Added support in ORM declarative annotations for class names specified for
_orm.relationship(), as well as the name of the _orm.Mapped
symbol itself, to be different names than their direct class name, to
support scenarios such as where _orm.Mapped is imported as
from sqlalchemy.orm import Mapped as M, or where related class names
are imported with an alternate name in a similar fashion. Additionally, a
target class name given as the lead argument for _orm.relationship()
will always supersede the name given in the left hand annotation, so that
otherwise un-importable names that also don't match the class name can
still be used in annotations.
References: #8759
[orm] [declarative] [bug] Improved support for legacy 1.4 mappings that use annotations which don't
include Mapped[], by ensuring the __allow_unmapped__ attribute can
be used to allow such legacy annotations to pass through Annotated
Declarative without raising an error and without being interpreted in an
ORM runtime context. Additionally improved the error message generated when
this condition is detected, and added more documentation for how this
situation should be handled. Unfortunately the 1.4 WARN_SQLALCHEMY_20
migration warning cannot detect this particular configurational issue at
runtime with its current architecture.
References: #8692
[orm] [declarative] [bug] Changed a fundamental configuration behavior of Mapper, where
_schema.Column objects that are explicitly present in the
_orm.Mapper.properties dictionary, either directly or enclosed
within a mapper property object, will now be mapped within the order of how
they appear within the mapped Table (or other selectable) itself
(assuming they are in fact part of that table's list of columns), thereby
maintaining the same order of columns in the mapped selectable as is
instrumented on the mapped class, as well as what renders in an ORM SELECT
statement for that mapper. Previously (where "previously" means since
version 0.0.1), Column objects in the
_orm.Mapper.properties dictionary would always be mapped first,
ahead of when the other columns in the mapped Table would be
mapped, causing a discrepancy in the order in which the mapper would
assign attributes to the mapped class as well as the order in which they
would render in statements.
The change most prominently takes place in the way that Declarative
assigns declared columns to the Mapper, specifically how
Column (or _orm.mapped_column()) objects are handled
when they have a DDL name that is explicitly different from the mapped
attribute name, as well as when constructs such as _orm.deferred()
etc. are used. The new behavior will see the column ordering within
the mapped Table being the same order in which the attributes
are mapped onto the class, assigned within the Mapper itself,
and rendered in ORM statements such as SELECT statements, independent
of how the _schema.Column was configured against the
Mapper.
References: #8705
[orm] [declarative] [bug] Fixed issue in new dataclass mapping feature where a column declared on the decalrative base / abstract base / mixin would leak into the constructor for an inheriting subclass under some circumstances.
References: #8718
[bug] [orm declarative] Fixed issues within the declarative typing resolver (i.e. which resolves
ForwardRef objects) where types that were declared for columns in one
particular source file would raise NameError when the ultimate mapped
class were in another source file. The types are now resolved in terms
of the module for each class in which the types are used.
References: #8742
[engine] [feature] To better support the use case of iterating Result and
AsyncResult objects where user-defined exceptions may interrupt
the iteration, both objects as well as variants such as
ScalarResult, MappingResult,
AsyncScalarResult, AsyncMappingResult now support
context manager usage, where the result will be closed at the end of
the context manager block.
In addition, ensured that all the above
mentioned Result objects include a Result.close() method
as well as Result.closed accessors, including
ScalarResult and MappingResult which previously did
not have a .close() method.
References: #8710
[engine] [usecase] Added new parameter PoolEvents.reset.reset_state parameter to
the PoolEvents.reset() event, with deprecation logic in place that
will continue to accept event hooks using the previous set of arguments.
This indicates various state information about how the reset is taking
place and is used to allow custom reset schemes to take place with full
context given.
Within this change a fix that's also backported to 1.4 is included which
re-enables the PoolEvents.reset() event to continue to take place
under all circumstances, including when Connection has already
"reset" the connection.
The two changes together allow custom reset schemes to be implemented using
the PoolEvents.reset() event, instead of the
PoolEvents.checkin() event (which continues to function as it always
has).
References: #8717
[postgresql] [feature] Added new methods _postgresql.Range.contains() and
_postgresql.Range.contained_by() to the new Range data
object, which mirror the behavior of the PostgreSQL @> and <@
operators, as well as the
_postgresql.AbstractRange.comparator_factory.contains() and
_postgresql.AbstractRange.comparator_factory.contained_by() SQL
operator methods. Pull request courtesy Lele Gaifax.
References: #8706
[postgresql] [usecase] Refined the new approach to range objects described at change_7156
to accommodate driver-specific range and multirange objects, to better
accommodate both legacy code as well as when passing results from raw SQL
result sets back into new range or multirange expressions.
References: #8690
…depending on what the actual column is, so this deprecated case is removed. In 2.0, ORM enabled update/delete uses "auto" for "synchronize_session", w…
Released: October 20, 2022
[orm] [bug] Removed the warning that emits when using ORM-enabled update/delete regarding evaluation of columns by name, first added in #4073; this warning actually covers up a scenario that otherwise could populate the wrong Python value for an ORM mapped attribute depending on what the actual column is, so this deprecated case is removed. In 2.0, ORM enabled update/delete uses "auto" for "synchronize_session", which should do the right thing automatically for any given UPDATE expression.
References: #8656
[orm] [declarative] [usecase] Added support for mapped classes that are also Generic subclasses,
to be specified as a GenericAlias object (e.g. MyClass[str])
within statements and calls to _sa.inspect().
References: #8665
[orm] [declarative] [bug] Improved the DeclarativeBase class so that when combined with
other mixins like MappedAsDataclass, the order of the classes may
be in either order.
References: #8665
[orm] [declarative] [bug] Fixed bug in new ORM typed declarative mappings where the ability
to use Optional[MyClass] or similar forms such as MyClass | None
in the type annotation for a many-to-one relationship was not implemented,
leading to errors. Documentation has also been added for this use
case to the relationship configuration documentation.
References: #8668
[orm] [declarative] [bug] Fixed issue with new dataclass mapping feature where arguments passed to
the dataclasses API could sometimes be mis-ordered when dealing with mixins
that override _orm.mapped_column() declarations, leading to
initializer problems.
References: #8688
[sql] [bug] [regression] Fixed bug in new "insertmanyvalues" feature where INSERT that included a
subquery with _sql.bindparam() inside of it would fail to render
correctly in "insertmanyvalues" format. This affected psycopg2 most
directly as "insertmanyvalues" is used unconditionally with this driver.
References: #8639
[typing] [bug] Fixed typing issue where pylance strict mode would report "instance
variable overrides class variable" when using a method to define
__tablename__, __mapper_args__ or __table_args__.
References: #8645
[typing] [bug] Fixed typing issue where pylance strict mode would report "partially
unknown" datatype for the _orm.mapped_column() construct.
References: #8644
[mssql] [bug] Fixed regression caused by SQL Server pyodbc change #8177 where we
now use setinputsizes() by default; for VARCHAR, this fails if the
character size is greater than 4000 (or 2000, depending on data) characters
as the incoming datatype is NVARCHAR, which has a limit of 4000 characters,
despite the fact that VARCHAR can handle unlimited characters. Additional
pyodbc-specific typing information is now passed to setinputsizes()
when the datatype's size is > 2000 characters. The change is also applied
to the _types.JSON type which was also impacted by this issue for large
JSON serializations.
References: #8661
[mssql] [bug] The Sequence construct restores itself to the DDL behavior it
had prior to the 1.4 series, where creating a Sequence with
no additional arguments will emit a simple CREATE SEQUENCE instruction
without any additional parameters for "start value". For most backends,
this is how things worked previously in any case; however, for
MS SQL Server, the default value on this database is
-2**63; to prevent this generally impractical default
from taking effect on SQL Server, the Sequence.start parameter
should be provided. As usage of Sequence is unusual
for SQL Server which for many years has standardized on IDENTITY,
it is hoped that this change has minimal impact.
References: #7211
[general] [changed] Migrated the codebase to remove all pre-2.0 behaviors and architectures that were previously noted as deprecated for removal in 2.…
Released: October 13, 2022
[general] [changed] Migrated the codebase to remove all pre-2.0 behaviors and architectures that were previously noted as deprecated for removal in 2.0, including, but not limited to:
- removal of all Python 2 code, minimum version is now Python 3.7
- `_engine.Engine` and `_engine.Connection` now use the
new 2.0 style of working, which includes "autobegin", library level
autocommit removed, subtransactions and "branched" connections
removed
- Result objects use 2.0-style behaviors; `_result.Row` is fully
a named tuple without "mapping" behavior, use `_result.RowMapping`
for "mapping" behavior
- All Unicode encoding/decoding architecture has been removed from
SQLAlchemy. All modern DBAPI implementations support Unicode
transparently thanks to Python 3, so the `convert_unicode` feature
as well as related mechanisms to look for bytestrings in
DBAPI `cursor.description` etc. have been removed.
- The `.bind` attribute and parameter from `MetaData`,
`Table`, and from all DDL/DML/DQL elements that previously could
refer to a "bound engine"
- The standalone `sqlalchemy.orm.mapper()` function is removed; all
classical mapping should be done through the
`_orm.registry.map_imperatively()` method of `_orm.registry`.
- The `_orm.Query.join()` method no longer accepts strings for
relationship names; the long-documented approach of using
`Class.attrname` for join targets is now standard.
- `_orm.Query.join()` no longer accepts the "aliased" and
"from_joinpoint" arguments
- `_orm.Query.join()` no longer accepts chains of multiple join
targets in one method call.
- `Query.from_self()`, `Query.select_entity_from()` and
`Query.with_polymorphic()` are removed.
- The `_orm.relationship.cascade_backrefs` parameter must now
remain at its new default of `False`; the `save-update` cascade
no longer cascades along a backref.
- the `_orm.Session.future` parameter must always be set to
`True`. 2.0-style transactional patterns for `_orm.Session`
are now always in effect.
- Loader options no longer accept strings for attribute names. The
long-documented approach of using `Class.attrname` for loader option
targets is now standard.
- Legacy forms of `_sql.select()` removed, including
`select([cols])`, the "whereclause" and keyword parameters of
`some_table.select()`.
- Legacy "in-place mutator" methods on `_sql.Select` such as
`append_whereclause()`, `append_order_by()` etc are removed.
- Removed the very old "dbapi_proxy" module, which in very early
SQLAlchemy releases was used to provide a transparent connection pool
over a raw DBAPI connection.
References: #7257
[general] [changed] The _orm.Query.instances() method is deprecated. The behavioral
contract of this method, which is that it can iterate objects through
arbitrary result sets, is long obsolete and no longer tested.
Arbitrary statements can return objects by using constructs such
as :meth.Select.from_statement or _orm.aliased().
[platform] [feature] The SQLAlchemy C extensions have been replaced with all new implementations
written in Cython. Like the C extensions before, pre-built wheel files
for a wide range of platforms are available on pypi so that building
is not an issue for common platforms. For custom builds, python setup.py build_ext
works as before, needing only the additional Cython install. pyproject.toml
is also part of the source now which will establish the proper build dependencies
when using pip.
References: #7256
[platform] [change] SQLAlchemy's source build and installation now includes a pyproject.toml file
for full PEP 517 support.
References: #7311
[orm] [feature] [sql] Added new feature to all included dialects that support RETURNING
called "insertmanyvalues". This is a generalization of the
"fast executemany" feature first introduced for the psycopg2 driver
in 1.4 at change_5263, which allows the ORM to batch INSERT
statements into a much more efficient SQL structure while still being
able to fetch newly generated primary key and SQL default values
using RETURNING.
The feature now applies to the many dialects that support RETURNING along with multiple VALUES constructs for INSERT, including all PostgreSQL drivers, SQLite, MariaDB, MS SQL Server. Separately, the Oracle dialect also gains the same capability using native cx_Oracle or OracleDB features.
References: #6047
[orm] [feature] Added new parameter _orm.AttributeEvents.include_key, which
will include the dictionary or list key for operations such as
__setitem__() (e.g. obj[key] = value) and __delitem__() (e.g.
del obj[key]), using a new keyword parameter "key" or "keys", depending
on event, e.g. _orm.AttributeEvents.append.key,
_orm.AttributeEvents.bulk_replace.keys. This allows event
handlers to take into account the key that was passed to the operation and
is of particular importance for dictionary operations working with
_orm.MappedCollection.
References: #8375
[orm] [feature] Added new parameter _sql.Operators.op.python_impl, available
from _sql.Operators.op() and also when using the
_sql.Operators.custom_op constructor directly, which allows an
in-Python evaluation function to be provided along with the custom SQL
operator. This evaluation function becomes the implementation used when the
operator object is used given plain Python objects as operands on both
sides, and in particular is compatible with the
synchronize_session='evaluate' option used with
orm_expression_update_delete.
References: #3162
[orm] [feature] The _orm.Session (and by extension AsyncSession) now has
new state-tracking functionality that will proactively trap any unexpected
state changes which occur as a particular transactional method proceeds.
This is to allow situations where the _orm.Session is being used
in a thread-unsafe manner, where event hooks or similar may be calling
unexpected methods within operations, as well as potentially under other
concurrency situations such as asyncio or gevent to raise an informative
message when the illegal access first occurs, rather than passing silently
leading to secondary failures due to the _orm.Session being in an
invalid state.
References: #7433
[orm] [feature] The _orm.composite() mapping construct now supports automatic
resolution of values when used with a Python dataclass; the
__composite_values__() method no longer needs to be implemented as this
method is derived from inspection of the dataclass.
Additionally, classes mapped by _orm.composite now support
ordering comparison operations, e.g. <, >=, etc.
See the new documentation at mapper_composite for examples.
[orm] [feature] Added very experimental feature to the _orm.selectinload() and
_orm.immediateload() loader options called
_orm.selectinload.recursion_depth /
_orm.immediateload.recursion_depth , which allows a single
loader option to automatically recurse into self-referential relationships.
Is set to an integer indicating depth, and may also be set to -1 to
indicate to continue loading until no more levels deep are found.
Major internal changes to _orm.selectinload() and
_orm.immediateload() allow this feature to work while continuing
to make correct use of the compilation cache, as well as not using
arbitrary recursion, so any level of depth is supported (though would
emit that many queries). This may be useful for
self-referential structures that must be loaded fully eagerly, such as when
using asyncio.
A warning is also emitted when loader options are connected together with
arbitrary lengths (that is, without using the new recursion_depth
option) when excessive recursion depth is detected in related object
loading. This operation continues to use huge amounts of memory and
performs extremely poorly; the cache is disabled when this condition is
detected to protect the cache from being flooded with arbitrary statements.
References: #8126
[orm] [feature] Added new parameter _orm.Session.autobegin, which when set to
False will prevent the _orm.Session from beginning a
transaction implicitly. The _orm.Session.begin() method must be
called explicitly first in order to proceed with operations, otherwise an
error is raised whenever any operation would otherwise have begun
automatically. This option can be used to create a "safe"
_orm.Session that won't implicitly start new transactions.
As part of this change, also added a new status variable
_orm.SessionTransaction.origin which may be useful for event
handling code to be aware of the origin of a particular
_orm.SessionTransaction.
References: #6928
[orm] [feature] Declarative mixins which use _schema.Column objects that contain
_schema.ForeignKey references no longer need to use
_orm.declared_attr() to achieve this mapping; the
_schema.ForeignKey object is copied along with the
_schema.Column itself when the column is applied to the declared
mapping.
[orm] [usecase] Added _orm.load_only.raiseload parameter to the
_orm.load_only() loader option, so that the unloaded attributes may
have "raise" behavior rather than lazy loading. Previously there wasn't
really a way to do this with the _orm.load_only() option directly.
[orm] [change] To better accommodate explicit typing, the names of some ORM constructs that are typically constructed internally, but nonetheless are sometimes visible in messaging as well as typing, have been changed to more succinct names which also match the name of their constructing function (with different casing), in all cases maintaining aliases to the old names for the forseeable future:
- `_orm.RelationshipProperty` becomes an alias for the primary name
`_orm.Relationship`, which is constructed as always from the
`_orm.relationship()` function
- `_orm.SynonymProperty` becomes an alias for the primary name
`_orm.Synonym`, constructed as always from the
`_orm.synonym()` function
- `_orm.CompositeProperty` becomes an alias for the primary name
`_orm.Composite`, constructed as always from the
`_orm.composite()` function
[orm] [change] For consistency with the prominent ORM concept _orm.Mapped, the
names of the dictionary-oriented collections,
_orm.attribute_mapped_collection(),
_orm.column_mapped_collection(), and _orm.MappedCollection,
are changed to _orm.attribute_keyed_dict(),
_orm.column_keyed_dict() and _orm.KeyFuncDict, using the
phrase "dict" to minimize any confusion against the term "mapped". The old
names will remain indefinitely with no schedule for removal.
References: #8608
[orm] [bug] All _result.Result objects will now consistently raise
_exc.ResourceClosedError if they are used after a hard close,
which includes the "hard close" that occurs after calling "single row or
value" methods like _result.Result.first() and
_result.Result.scalar(). This was already the behavior of the most
common class of result objects returned for Core statement executions, i.e.
those based on _engine.CursorResult, so this behavior is not new.
However, the change has been extended to properly accommodate for the ORM
"filtering" result objects returned when using 2.0 style ORM queries,
which would previously behave in "soft closed" style of returning empty
results, or wouldn't actually "soft close" at all and would continue
yielding from the underlying cursor.
As part of this change, also added _result.Result.close() to the base
_result.Result class and implemented it for the filtered result
implementations that are used by the ORM, so that it is possible to call
the _engine.CursorResult.close() method on the underlying
_engine.CursorResult when the the yield_per execution option
is in use to close a server side cursor before remaining ORM results have
been fetched. This was again already available for Core result sets but the
change makes it available for 2.0 style ORM results as well.
This change is also backported to: 1.4.27
References: #7274
[orm] [bug] Fixed issue where the _orm.registry.map_declaratively() method
would return an internal "mapper config" object and not the
Mapper object as stated in the API documentation.
[orm] [bug] Fixed performance regression which appeared at least in version 1.3 if not
earlier (sometime after 1.0) where the loading of deferred columns, those
explicitly mapped with _orm.defer() as opposed to non-deferred
columns that were expired, from a joined inheritance subclass would not use
the "optimized" query which only queried the immediate table that contains
the unloaded columns, instead running a full ORM query which would emit a
JOIN for all base tables, which is not necessary when only loading columns
from the subclass.
References: #7463
[orm] [bug] The internals for the _orm.Load object and related loader strategy
patterns have been mostly rewritten, to take advantage of the fact that
only attribute-bound paths, not strings, are now supported. The rewrite
hopes to make it more straightforward to address new use cases and subtle
issues within the loader strategy system going forward.
References: #6986
[orm] [bug] Made an improvement to the "deferred" / "load_only" set of strategy options where if a certain object is loaded from two different logical paths within one query, attributes that have been configured by at least one of the options to be populated will be populated in all cases, even if other load paths for that same object did not set this option. previously, it was based on randomness as to which "path" addressed the object first.
References: #8166
[orm] [bug] Fixed issue in ORM enabled UPDATE when the statement is created against a joined-inheritance subclass, updating only local table columns, where the "fetch" synchronization strategy would not render the correct RETURNING clause for databases that use RETURNING for fetch synchronization. Also adjusts the strategy used for RETURNING in UPDATE FROM and DELETE FROM statements.
References: #8344
[orm] [bug] [asyncio] Removed the unused **kw arguments from
_asyncio.AsyncSession.begin and
_asyncio.AsyncSession.begin_nested. These kw aren't used and
appear to have been added to the API in error.
References: #7703
[orm] [bug] Changed the attribute access method used by
_orm.attribute_mapped_collection() and
_orm.column_mapped_collection(), used when populating the dictionary,
to assert that the data value on the object to be used as the dictionary
key is actually present, and is not instead using "None" due to the
attribute never being actually assigned. This is used to prevent a
mis-population of None for a key when assigning via a backref where the
"key" attribute on the object is not yet assigned.
As the failure mode here is a transitory condition that is not typically
persisted to the database, and is easy to produce via the constructor of
the class based on the order in which parameters are assigned, it is very
possible that many applications include this behavior already which is
silently passed over. To accommodate for applications where this error is
now raised, a new parameter
_orm.attribute_mapped_collection.ignore_unpopulated_attribute
is also added to both _orm.attribute_mapped_collection() and
_orm.column_mapped_collection() that instead causes the erroneous
backref assignment to be skipped.
References: #8372
[orm] [bug] Added new parameter AbstractConcreteBase.strict_attrs to the
AbstractConcreteBase declarative mixin class. The effect of this
parameter is that the scope of attributes on subclasses is correctly
limited to the subclass in which each attribute is declared, rather than
the previous behavior where all attributes of the entire hierarchy are
applied to the base "abstract" class. This produces a cleaner, more correct
mapping where subclasses no longer have non-useful attributes on them which
are only relevant to sibling classes. The default for this parameter is
False, which leaves the previous behavior unchanged; this is to support
existing code that makes explicit use of these attributes in queries.
To migrate to the newer approach, apply explicit attributes to the abstract
base class as needed.
References: #8403
[orm] [bug] The behavior of _orm.defer() regarding primary key and "polymorphic
discriminator" columns is revised such that these columns are no longer
deferrable, either explicitly or when using a wildcard such as
defer('*'). Previously, a wildcard deferral would not load
PK/polymorphic columns which led to errors in all cases, as the ORM relies
upon these columns to produce object identities. The behavior of explicit
deferral of primary key columns is unchanged as these deferrals already
were implicitly ignored.
References: #7495
[orm] [bug] Fixed bug in the behavior of the _orm.Mapper.eager_defaults
parameter such that client-side SQL default or onupdate expressions in the
table definition alone will trigger a fetch operation using RETURNING or
SELECT when the ORM emits an INSERT or UPDATE for the row. Previously, only
server side defaults established as part of table DDL and/or server-side
onupdate expressions would trigger this fetch, even though client-side SQL
expressions would be included when the fetch was rendered.
References: #7438
[engine] [feature] The DialectEvents.handle_error() event is now moved to the
DialectEvents suite from the EngineEvents suite, and
now participates in the connection pool "pre ping" event for those dialects
that make use of disconnect codes in order to detect if the database is
live. This allows end-user code to alter the state of "pre ping". Note that
this does not include dialects which contain a native "ping" method such as
that of psycopg2 or most MySQL dialects.
References: #5648
[engine] [feature] The ConnectionEvents.set_connection_execution_options()
and ConnectionEvents.set_engine_execution_options()
event hooks now allow the given options dictionary to be modified
in-place, where the new contents will be received as the ultimate
execution options to be acted upon. Previously, in-place modifications to
the dictionary were not supported.
[engine] [usecase] Generalized the _sa.create_engine.isolation_level parameter to
the base dialect so that it is no longer dependent on individual dialects
to be present. This parameter sets up the "isolation level" setting to
occur for all new database connections as soon as they are created by the
connection pool, where the value then stays set without being reset on
every checkin.
The _sa.create_engine.isolation_level parameter is essentially
equivalent in functionality to using the
_engine.Engine.execution_options.isolation_level parameter via
_engine.Engine.execution_options() for an engine-wide setting. The
difference is in that the former setting assigns the isolation level just
once when a connection is created, the latter sets and resets the given
level on each connection checkout.
References: #6342
[engine] [change] Some small API changes regarding engines and dialects:
- The `Dialect.set_isolation_level()`, `Dialect.get_isolation_level()`,
:meth:
dialect methods will always be passed the raw DBAPI connection
- The `Connection` and `Engine` classes no longer share a base
`Connectable` superclass, which has been removed.
- Added a new interface class `PoolProxiedConnection` - this is the
public facing interface for the familiar `_ConnectionFairy`
class which is nonetheless a private class.
References: #7122
[engine] [bug] [regression] Fixed regression where the _engine.CursorResult.fetchmany() method
would fail to autoclose a server-side cursor (i.e. when stream_results
or yield_per is in use, either Core or ORM oriented results) when the
results were fully exhausted.
This change is also backported to: 1.4.27
References: #7274
[engine] [bug] Fixed issue in future _engine.Engine where calling upon
_engine.Engine.begin() and entering the context manager would not
close the connection if the actual BEGIN operation failed for some reason,
such as an event handler raising an exception; this use case failed to be
tested for the future version of the engine. Note that the "future" context
managers which handle begin() blocks in Core and ORM don't actually run
the "BEGIN" operation until the context managers are actually entered. This
is different from the legacy version which runs the "BEGIN" operation up
front.
This change is also backported to: 1.4.27
References: #7272
[engine] [bug] For improved security, the _url.URL object will now use password
obfuscation by default when str(url) is called. To stringify a URL with
cleartext password, the _url.URL.render_as_string() may be used,
passing the _url.URL.render_as_string.hide_password parameter
as False. Thanks to our contributors for this pull request.
References: #8567
[engine] [bug] The _engine.Inspector.has_table() method will now consistently check
for views of the given name as well as tables. Previously this behavior was
dialect dependent, with PostgreSQL, MySQL/MariaDB and SQLite supporting it,
and Oracle and SQL Server not supporting it. Third party dialects should
also seek to ensure their _engine.Inspector.has_table() method
searches for views as well as tables for the given name.
References: #7161
[engine] [bug] Fixed issue in Result.columns() method where calling upon
Result.columns() with a single index could in some cases,
particularly ORM result object cases, cause the Result to yield
scalar objects rather than Row objects, as though the
Result.scalars() method had been called. In SQLAlchemy 1.4, this
scenario emits a warning that the behavior will change in SQLAlchemy 2.0.
References: #7953
[engine] [bug] Passing a DefaultGenerator object such as a Sequence to
the Connection.execute() method is deprecated, as this method is
typed as returning a CursorResult object, and not a plain scalar
value. The Connection.scalar() method should be used instead, which
has been reworked with new internal codepaths to suit invoking a SELECT for
default generation objects without going through the
Connection.execute() method.
[engine] [removed] Removed the previously deprecated case_sensitive parameter from
_sa.create_engine(), which would impact only the lookup of string
column names in Core-only result set rows; it had no effect on the behavior
of the ORM. The effective behavior of what case_sensitive refers
towards remains at its default value of True, meaning that string names
looked up in row._mapping will match case-sensitively, just like any
other Python mapping.
Note that the case_sensitive parameter was not in any way related to
the general subject of case sensitivity control, quoting, and "name
normalization" (i.e. converting for databases that consider all uppercase
words to be case insensitive) for DDL identifier names, which remains a
normal core feature of SQLAlchemy.
[engine] [removed] Removed legacy and deprecated package sqlalchemy.databases.
Please use sqlalchemy.dialects instead.
References: #7258
[engine] [deprecations] The _sa.create_engine.implicit_returning parameter is
deprecated on the _sa.create_engine() function only; the parameter
remains available on the _schema.Table object. This parameter was
originally intended to enable the "implicit returning" feature of
SQLAlchemy when it was first developed and was not enabled by default.
Under modern use, there's no reason this parameter should be disabled, and
it has been observed to cause confusion as it degrades performance and
makes it more difficult for the ORM to retrieve recently inserted server
defaults. The parameter remains available on _schema.Table to
specifically suit database-level edge cases which make RETURNING
infeasible, the sole example currently being SQL Server's limitation that
INSERT RETURNING may not be used on a table that has INSERT triggers on it.
References: #6962
[sql] [feature] Added long-requested case-insensitive string operators
_sql.ColumnOperators.icontains(),
_sql.ColumnOperators.istartswith(),
_sql.ColumnOperators.iendswith(), which produce case-insensitive
LIKE compositions (using ILIKE on PostgreSQL, and the LOWER() function on
all other backends) to complement the existing LIKE composition operators
_sql.ColumnOperators.contains(),
_sql.ColumnOperators.startswith(), etc. Huge thanks to Matias
Martinez Rebori for their meticulous and complete efforts in implementing
these new methods.
References: #3482
[sql] [feature] Added new syntax to the FromClause.c collection on all
FromClause objects allowing tuples of keys to be passed to
__getitem__(), along with support for the _sql.select() construct
to handle the resulting tuple-like collection directly, allowing the syntax
select(table.c['a', 'b', 'c']) to be possible. The sub-collection
returned is itself a ColumnCollection which is also directly
consumable by _sql.select() and similar now.
References: #8285
[sql] [usecase] Altered the compilation mechanics of the _dml.Insert construct
such that the "autoincrement primary key" column value will be fetched via
cursor.lastrowid or RETURNING even if present in the parameter set or
within the _dml.Insert.values() method as a plain bound value, for
single-row INSERT statements on specific backends that are known to
generate autoincrementing values even when explicit NULL is passed. This
restores a behavior that was in the 1.3 series for both the use case of
separate parameter set as well as _dml.Insert.values(). In 1.4, the
parameter set behavior unintentionally changed to no longer do this, but
the _dml.Insert.values() method would still fetch autoincrement
values up until 1.4.21 where #6770 changed the behavior yet again
again unintentionally as this use case was never covered.
The behavior is now defined as "working" to suit the case where databases such as SQLite, MySQL and MariaDB will ignore an explicit NULL primary key value and nonetheless invoke an autoincrement generator.
References: #7998
[sql] [usecase] Added new parameter HasCTE.add_cte.nest_here to
HasCTE.add_cte() which will "nest" a given CTE at the
level of the parent statement. This parameter is equivalent to using the
HasCTE.cte.nesting parameter, but may be more intuitive in
some scenarios as it allows the nesting attribute to be set simultaneously
along with the explicit level of the CTE.
The HasCTE.add_cte() method also accepts multiple CTE objects.
References: #7759
[sql] [bug] The FROM clauses that are established on a _sql.select() construct
when using the _sql.Select.select_from() method will now render first
in the FROM clause of the rendered SELECT, which serves to maintain the
ordering of clauses as was passed to the _sql.Select.select_from()
method itself without being affected by the presence of those clauses also
being mentioned in other parts of the query. If other elements of the
_sql.Select also generate FROM clauses, such as the columns clause
or WHERE clause, these will render after the clauses delivered by
_sql.Select.select_from() assuming they were not explictly passed to
_sql.Select.select_from() also. This improvement is useful in those
cases where a particular database generates a desirable query plan based on
a particular ordering of FROM clauses and allows full control over the
ordering of FROM clauses.
References: #7888
[sql] [bug] The Enum.length parameter, which sets the length of the
VARCHAR column for non-native enumeration types, is now used
unconditionally when emitting DDL for the VARCHAR datatype, including
when the Enum.native_enum parameter is set to True for
target backends that continue to use VARCHAR. Previously the parameter
would be erroneously ignored in this case. The warning previously emitted
for this case is now removed.
References: #7791
[sql] [bug] The in-place type detection for Python integers, as occurs with an
expression such as literal(25), will now apply value-based adaption as
well to accommodate Python large integers, where the datatype determined
will be BigInteger rather than Integer. This
accommodates for dialects such as that of asyncpg which both sends implicit
typing information to the driver as well as is sensitive to numeric scale.
References: #7909
[sql] [bug] Added if_exists and if_not_exists parameters for all "Create" /
"Drop" constructs including CreateSequence,
DropSequence, CreateIndex, DropIndex, etc.
allowing generic "IF EXISTS" / "IF NOT EXISTS" phrases to be rendered
within DDL. Pull request courtesy Jesse Bakker.
References: #7354
[sql] [bug] Improved the construction of SQL binary expressions to allow for very long
expressions against the same associative operator without special steps
needed in order to avoid high memory use and excess recursion depth. A
particular binary operation A op B can now be joined against another
element op C and the resulting structure will be "flattened" so that
the representation as well as SQL compilation does not require recursion.
One effect of this change is that string concatenation expressions which
use SQL functions come out as "flat", e.g. MySQL will now render
concat('x', 'y', 'z', ...)`` rather than nesting together two-element functions like concat(concat('x', 'y'), 'z'). Third-party dialects which override the string concatenation operator will need to implement a new method def visit_concat_op_expression_clauselist()to accompany the existingdef visit_concat_op_binary()` method.
References: #7744
[sql] [bug] Implemented full support for "truediv" and "floordiv" using the
"/" and "//" operators. A "truediv" operation between two expressions
using _types.Integer now considers the result to be
_types.Numeric, and the dialect-level compilation will cast
the right operand to a numeric type on a dialect-specific basis to ensure
truediv is achieved. For floordiv, conversion is also added for those
databases that don't already do floordiv by default (MySQL, Oracle) and
the FLOOR() function is rendered in this case, as well as for
cases where the right operand is not an integer (needed for PostgreSQL,
others).
The change resolves issues both with inconsistent behavior of the division operator on different backends and also fixes an issue where integer division on Oracle would fail to be able to fetch a result due to inappropriate outputtypehandlers.
References: #4926
[sql] [bug] Added an additional lookup step to the compiler which will track all FROM clauses which are tables, that may have the same name shared in multiple schemas where one of the schemas is the implicit "default" schema; in this case, the table name when referring to that name without a schema qualification will be rendered with an anonymous alias name at the compiler level in order to disambiguate the two (or more) names. The approach of schema-qualifying the normally unqualified name with the server-detected "default schema name" value was also considered, however this approach doesn't apply to Oracle nor is it accepted by SQL Server, nor would it work with multiple entries in the PostgreSQL search path. The name collision issue resolved here has been identified as affecting at least Oracle, PostgreSQL, SQL Server, MySQL and MariaDB.
References: #7471
[sql] [bug] The _functions.array_agg will now set the array dimensions to 1.
Improved _types.ARRAY processing to accept None values as
value of a multi-array.
References: #7083
[schema] [feature] Expanded on the "conditional DDL" system implemented by the
_schema.ExecutableDDLElement class (renamed from
_schema.DDLElement) to be directly available on
_schema.SchemaItem constructs such as _schema.Index,
_schema.ForeignKeyConstraint, etc. such that the conditional logic
for generating these elements is included within the default DDL emitting
process. This system can also be accommodated by a future release of
Alembic to support conditional DDL elements within all schema-management
systems.
References: #7631
[schema] [usecase] Added parameter _ddl.DropConstraint.if_exists to the
_ddl.DropConstraint construct which result in "IF EXISTS" DDL
being added to the DROP statement.
This phrase is not accepted by all databases and the operation will fail
on a database that does not support it as there is no similarly compatible
fallback within the scope of a single DDL statement.
Pull request courtesy Mike Fiedler.
References: #8141
[schema] [usecase] Implemented the DDL event hooks DDLEvents.before_create(),
DDLEvents.after_create(), DDLEvents.before_drop(),
DDLEvents.after_drop() for all SchemaItem objects that
include a distinct CREATE or DROP step, when that step is invoked as a
distinct SQL statement, including for ForeignKeyConstraint,
Sequence, Index, and PostgreSQL's
_postgresql.ENUM.
References: #8394
[schema] [performance] Rearchitected the schema reflection API to allow participating dialects to
make use of high performing batch queries to reflect the schemas of many
tables at once using fewer queries by an order of magnitude. The
new performance features are targeted first at the PostgreSQL and Oracle
backends, and may be applied to any dialect that makes use of SELECT
queries against system catalog tables to reflect tables. The change also
includes new API features and behavioral improvements to the
Inspector object, including consistent, cached behavior of
methods like Inspector.has_table(),
Inspector.get_table_names() and new methods
Inspector.has_schema() and Inspector.has_index().
References: #4379
[schema] [bug] The warnings that are emitted regarding reflection of indexes or unique
constraints, when the Table.include_columns parameter is used
to exclude columns that are then found to be part of those constraints,
have been removed. When the Table.include_columns parameter is
used it should be expected that the resulting Table construct
will not include constraints that rely upon omitted columns. This change
was made in response to #8100 which repaired
Table.include_columns in conjunction with foreign key
constraints that rely upon omitted columns, where the use case became
clear that omitting such constraints should be expected.
References: #8102
[schema] [postgresql] Added support for comments on Constraint objects, including
DDL and reflection; the field is added to the base Constraint
class and corresponding constructors, however PostgreSQL is the only
included backend to support the feature right now.
See parameters such as ForeignKeyConstraint.comment,
UniqueConstraint.comment or
CheckConstraint.comment.
References: #5677
[schema] [mariadb] [mysql] Add support for Partitioning and Sample pages on MySQL and MariaDB
reflected options.
The options are stored in the table dialect options dictionary, so
the following keyword need to be prefixed with mysql_ or mariadb_
depending on the backend.
Supported options are:
- `stats_sample_pages`
- `partition_by`
- `partitions`
- `subpartition_by`
These options are also reflected when loading a table from database,
and will populate the table _schema.Table.dialect_options.
Pull request courtesy of Ramon Will.
References: #4038
[typing] [improvement] The _sqltypes.TypeEngine.with_variant() method now returns a copy of
the original _sqltypes.TypeEngine object, rather than wrapping it
inside the Variant class, which is effectively removed (the import
symbol remains for backwards compatibility with code that may be testing
for this symbol). While the previous approach maintained in-Python
behaviors, maintaining the original type allows for clearer type checking
and debugging.
_sqltypes.TypeEngine.with_variant() also accepts multiple dialect
names per call as well, in particular this is helpful for related
backend names such as "mysql", "mariadb".
References: #6980
[postgresql] [feature] Added a new PostgreSQL _postgresql.DOMAIN datatype, which follows
the same CREATE TYPE / DROP TYPE behaviors as that of PostgreSQL
_postgresql.ENUM. Much thanks to David Baumgold for the efforts on
this.
References: #7316
[postgresql] [usecase] [asyncpg] Added overridable methods PGDialect_asyncpg.setup_asyncpg_json_codec
and PGDialect_asyncpg.setup_asyncpg_jsonb_codec codec, which handle the
required task of registering JSON/JSONB codecs for these datatypes when
using asyncpg. The change is that methods are broken out as individual,
overridable methods to support third party dialects that need to alter or
disable how these particular codecs are set up.
This change is also backported to: 1.4.27
References: #7284
[postgresql] [usecase] Added literal type rendering for the _sqltypes.ARRAY and
_postgresql.ARRAY datatypes. The generic stringify will render
using brackets, e.g. [1, 2, 3] and the PostgreSQL specific will use the
ARRAY literal e.g. ARRAY[1, 2, 3]. Multiple dimensions and quoting
are also taken into account.
References: #8138
[postgresql] [usecase] Adds support for PostgreSQL multirange types, introduced in PostgreSQL 14.
Support for PostgreSQL ranges and multiranges has now been generalized to
the psycopg3, psycopg2 and asyncpg backends, with room for further dialect
support, using a backend-agnostic _postgresql.Range data object
that's constructor-compatible with the previously used psycopg2 object. See
the new documentation for usage patterns.
In addition, range type handling has been enhanced so that it automatically
renders type casts, so that in-place round trips for statements that don't
provide the database with any context don't require the _sql.cast()
construct to be explicit for the database to know the desired type
(discussed at #8540).
Thanks very much to @zeeeeeb for the pull request implementing and testing the new datatypes and psycopg support.
[postgresql] [usecase] The "ping" query emitted when configuring
_sa.create_engine.pool_pre_ping for psycopg, asyncpg and
pg8000, but not for psycopg2, has been changed to be an empty query (;)
instead of SELECT 1; additionally, for the asyncpg driver, the
unnecessary use of a prepared statement for this query has been fixed.
Rationale is to eliminate the need for PostgreSQL to produce a query plan
when the ping is emitted. The operation is not currently supported by the
psycopg2 driver which continues to use SELECT 1.
References: #8491
[postgresql] [change] SQLAlchemy now requires PostgreSQL version 9 or greater. Older versions may still work in some limited use cases.
[postgresql] [change] [mssql] The parameter _types.UUID.as_uuid of _types.UUID,
previously specific to the PostgreSQL dialect but now generalized for Core
(along with a new backend-agnostic _types.Uuid datatype) now
defaults to True, indicating that Python UUID objects are accepted
by this datatype by default. Additionally, the SQL Server
_mssql.UNIQUEIDENTIFIER datatype has been converted to be a
UUID-receiving type; for legacy code that makes use of
_mssql.UNIQUEIDENTIFIER using string values, set the
_mssql.UNIQUEIDENTIFIER.as_uuid parameter to False.
References: #7225
[postgresql] [change] The _postgresql.ENUM.name parameter for the PostgreSQL-specific
_postgresql.ENUM datatype is now a required keyword argument. The
"name" is necessary in any case in order for the _postgresql.ENUM
to be usable as an error would be raised at SQL/DDL render time if "name"
were not present.
[postgresql] [change] In support of new PostgreSQL features including the psycopg3 dialect as
well as extended "fast insertmany" support, the system by which typing
information for bound parameters is passed to the PostgreSQL database has
been redesigned to use inline casts emitted by the SQL compiler, and is now
applied to all PostgreSQL dialects. This is in contrast to the previous
approach which would rely upon the DBAPI in use to render these casts
itself, which in cases such as that of pg8000 and the adapted asyncpg
driver, would use the pep-249 setinputsizes() method, or with the
psycopg2 driver would rely on the driver itself in most cases, with some
special exceptions made for ARRAY.
The new approach now has all PostgreSQL dialects rendering these casts as
needed using PostgreSQL double-colon style within the compiler, and the use
of setinputsizes() is removed for PostgreSQL dialects, as this was not
generally part of these DBAPIs in any case (pg8000 being the only
exception, which added the method at the request of SQLAlchemy developers).
Advantages to this approach include per-statement performance, as no second pass over the compiled statement is required at execution time, better support for all DBAPIs, as there is now one consistent system of applying typing information, and improved transparency, as the SQL logging output, as well as the string output of a compiled statement, will show these casts present in the statement directly, whereas previously these casts were not visible in logging output as they would occur after the statement were logged.
[postgresql] [bug] The Operators.match() operator now uses plainto_tsquery() for
PostgreSQL full text search, rather than to_tsquery(). The rationale
for this change is to provide better cross-compatibility with match on
other database backends. Full support for all PostgreSQL full text
functions remains available through the use of :data:.func in
conjunction with Operators.bool_op() (an improved version of
Operators.op() for boolean operators).
Unknown interpreted text role "data".
References: #7086
[postgresql] [removed] Removed support for multiple deprecated drivers:
- pypostgresql for PostgreSQL. This is available as an
external driver at [https://github.com/PyGreSQL](https://github.com/PyGreSQL)
- pygresql for PostgreSQL.
Please switch to one of the supported drivers or to the external version of the same driver.
References: #7258
[postgresql] [dialect] Added support for psycopg dialect supporting both sync and async
execution. This dialect is available under the postgresql+psycopg name
for both the _sa.create_engine() and
_asyncio.create_async_engine() engine-creation functions.
References: #6842
[postgresql] [psycopg2] Update psycopg2 dialect to use the DBAPI interface to execute two phase transactions. Previously SQL commands were execute to handle this kind of transactions.
References: #7238
[postgresql] [schema] Introduced the type _postgresql.JSONPATH that can be used
in cast expressions. This is required by some PostgreSQL dialects
when using functions such as jsonb_path_exists or
jsonb_path_match that accept a jsonpath as input.
References: #8216
[postgresql] [reflection] The PostgreSQL dialect now supports reflection of expression based indexes.
The reflection is supported both when using
_engine.Inspector.get_indexes() and when reflecting a
_schema.Table using _schema.Table.autoload_with.
Thanks to immerrr and Aidan Kane for the help on this ticket.
References: #7442
[mysql] [usecase] [mariadb] The ROLLUP function will now correctly render WITH ROLLUP on
MySql and MariaDB, allowing the use of group by rollup with these
backend.
References: #8503
[mysql] [bug] Fixed issue in MySQL _mysql.Insert.on_duplicate_key_update() which
would render the wrong column name when an expression were used in a VALUES
expression. Pull request courtesy Cristian Sabaila.
This change is also backported to: 1.4.27
References: #7281
[mysql] [removed] Removed support for the OurSQL driver for MySQL and MariaDB, as this driver does not seem to be maintained.
References: #7258
[mariadb] [usecase] Added a new execution option is_delete_using=True, which is consumed
by the ORM when using an ORM-enabled DELETE statement in conjunction with
the "fetch" synchronization strategy; this option indicates that the
DELETE statement is expected to use multiple tables, which on MariaDB
is the DELETE..USING syntax. The option then indicates that
RETURNING (newly implemented in SQLAlchemy 2.0 for MariaDB
for #7011) should not be used for databases that are known
to not support "DELETE..USING..RETURNING" syntax, even though they
support "DELETE..USING", which is MariaDB's current capability.
The rationale for this option is that the current workings of ORM-enabled
DELETE doesn't know up front if a DELETE statement is against multiple
tables or not until compilation occurs, which is cached in any case, yet it
needs to be known so that a SELECT for the to-be-deleted row can be emitted
up front. Instead of applying an across-the-board performance penalty for
all DELETE statements by proactively checking them all for this
relatively unusual SQL pattern, the is_delete_using=True execution
option is requested via a new exception message that is raised
within the compilation step. This exception message is specifically
(and only) raised when: the statement is an ORM-enabled DELETE where
the "fetch" synchronization strategy has been requested; the
backend is MariaDB or other backend with this specific limitation;
the statement has been detected within the initial compilation
that it would otherwise emit "DELETE..USING..RETURNING". By applying
the execution option, the ORM knows to run a SELECT upfront instead.
A similar option is implemented for ORM-enabled UPDATE but there is not
currently a backend where it is needed.
References: #8344
[mariadb] [usecase] Added INSERT..RETURNING and DELETE..RETURNING support for the MariaDB dialect. UPDATE..RETURNING is not yet supported by MariaDB. MariaDB supports INSERT..RETURNING as of 10.5.0 and DELETE..RETURNING as of 10.0.5.
References: #7011
[sqlite] [usecase] Added new parameter to SQLite for reflection methods called
sqlite_include_internal=True; when omitted, local tables that start
with the prefix sqlite_, which per SQLite documentation are noted as
"internal schema" tables such as the sqlite_sequence table generated to
support "AUTOINCREMENT" columns, will not be included in reflection methods
that return lists of local objects. This prevents issues for example when
using Alembic autogenerate, which previously would consider these
SQLite-generated tables as being remove from the model.
References: #8234
[sqlite] [usecase] Added RETURNING support for the SQLite dialect. SQLite supports RETURNING since version 3.35.
References: #6195
[sqlite] [usecase] The SQLite dialect now supports UPDATE..FROM syntax, for UPDATE statements
that may refer to additional tables within the WHERE criteria of the
statement without the need to use subqueries. This syntax is invoked
automatically when using the _dml.Update construct when more than
one table or other entity or selectable is used.
References: #7185
[sqlite] [performance] [usecase] SQLite datetime, date, and time datatypes now use Python standard lib
fromisoformat() methods in order to parse incoming datetime, date, and
time string values. This improves performance vs. the previous regular
expression-based approach, and also automatically accommodates for datetime
and time formats that contain either a six-digit "microseconds" format or a
three-digit "milliseconds" format.
References: #7029
[sqlite] [bug] Removed the warning that emits from the _types.Numeric type about
DBAPIs not supporting Decimal values natively. This warning was oriented
towards SQLite, which does not have any real way without additional
extensions or workarounds of handling precision numeric values more than 15
significant digits as it only uses floating point math to represent
numbers. As this is a known and documented limitation in SQLite itself, and
not a quirk of the pysqlite driver, there's no need for SQLAlchemy to warn
for this. The change does not otherwise modify how precision numerics are
handled. Values can continue to be handled as Decimal() or float()
as configured with the _types.Numeric, _types.Float , and
related datatypes, just without the ability to maintain precision beyond 15
significant digits when using SQLite, unless alternate representations such
as strings are used.
References: #7299
[sqlite] [bug] [performance] The SQLite dialect now defaults to _pool.QueuePool when a file
based database is used. This is set along with setting the
check_same_thread parameter to False. It has been observed that the
previous approach of defaulting to _pool.NullPool, which does not
hold onto database connections after they are released, did in fact have a
measurable negative performance impact. As always, the pool class is
customizable via the _sa.create_engine.poolclass parameter.
References: #7490
[mssql] [usecase] Implemented reflection of the "clustered index" flag mssql_clustered
for the SQL Server dialect. Pull request courtesy John Lennox.
References: #8288
[mssql] [usecase] Added support table and column comments on MSSQL when creating a table. Added support for reflecting table comments. Thanks to Daniel Hall for the help in this pull request.
References: #7844
[mssql] [bug] The use_setinputsizes parameter for the mssql+pyodbc dialect now
defaults to True; this is so that non-unicode string comparisons are
bound by pyodbc to pyodbc.SQL_VARCHAR rather than pyodbc.SQL_WVARCHAR,
allowing indexes against VARCHAR columns to take effect. In order for the
fast_executemany=True parameter to continue functioning, the
use_setinputsizes mode now skips the cursor.setinputsizes() call
specifically when fast_executemany is True and the specific method in
use is cursor.executemany(), which doesn't support setinputsizes. The
change also adds appropriate pyodbc DBAPI typing to values that are typed
as _types.Unicode or _types.UnicodeText, as well as
altered the base _types.JSON datatype to consider JSON string
values as _types.Unicode rather than _types.String.
References: #8177
[mssql] [removed] Removed support for the mxodbc driver due to lack of testing support. ODBC users may use the pyodbc dialect which is fully supported.
References: #7258
[oracle] [feature] Add support for the new oracle driver oracledb.
References: #8054
[oracle] [feature] Implemented DDL and reflection support for FLOAT datatypes which
include an explicit "binary_precision" value. Using the Oracle-specific
_oracle.FLOAT datatype, the new parameter
_oracle.FLOAT.binary_precision may be specified which will
render Oracle's precision for floating point types directly. This value is
interpreted during reflection. Upon reflecting back a FLOAT datatype,
the datatype returned is one of _types.DOUBLE_PRECISION for a
FLOAT for a precision of 126 (this is also Oracle's default precision
for FLOAT), _types.REAL for a precision of 63, and
_oracle.FLOAT for a custom precision, as per Oracle documentation.
As part of this change, the generic _sqltypes.Float.precision
value is explicitly rejected when generating DDL for Oracle, as this
precision cannot be accurately converted to "binary precision"; instead, an
error message encourages the use of
_sqltypes.TypeEngine.with_variant() so that Oracle's specific form of
precision may be chosen exactly. This is a backwards-incompatible change in
behavior, as the previous "precision" value was silently ignored for
Oracle.
References: #5465
[oracle] [feature] Full "RETURNING" support is implemented for the cx_Oracle dialect, covering two individual types of functionality:
- multi-row RETURNING is implemented, meaning multiple RETURNING rows are
now received for DML statements that produce more than one row for
RETURNING.
- "executemany RETURNING" is also implemented - this allows RETURNING to
yield row-per statement when `cursor.executemany()` is used.
The implementation of this part of the feature delivers dramatic
performance improvements to ORM inserts, in the same way as was
added for psycopg2 in the SQLAlchemy 1.4 change `change_5263`.
References: #6245
[oracle] [usecase] Oracle will now use FETCH FIRST N ROWS / OFFSET syntax for limit/offset
support by default for Oracle 12c and above. This syntax was already
available when _sql.Select.fetch() were used directly, it's now
implied for _sql.Select.limit() and _sql.Select.offset() as
well.
References: #8221
[oracle] [change] Materialized views on oracle are now reflected as views.
On previous versions of SQLAlchemy the views were returned among
the table names, not among the view names. As a side effect of
this change they are not reflected by default by
_sql.MetaData.reflect(), unless views=True is set.
To get a list of materialized views, use the new
inspection method Inspector.get_materialized_view_names().
[oracle] [bug] Adjustments made to the BLOB / CLOB / NCLOB datatypes in the cx_Oracle and oracledb dialects, to improve performance based on recommendations from Oracle developers.
References: #7494
[oracle] [bug] Related to the deprecation for
_sa.create_engine.implicit_returning, the "implicit_returning"
feature is now enabled for the Oracle dialect in all cases; previously, the
feature would be turned off when an Oracle 8/8i version were detected,
however online documentation indicates both versions support the same
RETURNING syntax as modern versions.
References: #6962
[oracle] cx_Oracle 7 is now the minimum version for cx_Oracle.
[feature] [types] Added new backend-agnostic _types.Uuid datatype generalized from
the PostgreSQL dialects to now be a core type, as well as migrated
_types.UUID from the PostgreSQL dialect. The SQL Server
_mssql.UNIQUEIDENTIFIER datatype also becomes a UUID-handling
datatype. Thanks to Trevor Gross for the help on this.
References: #7212
[feature] [types] Added Double, DOUBLE, DOUBLE_PRECISION
datatypes to the base sqlalchemy. module namespace, for explicit use of
double/double precision as well as generic "double" datatypes. Use
Double for generic support that will resolve to DOUBLE/DOUBLE
PRECISION/FLOAT as needed for different backends.
References: #5465
[usecase] [datatypes] Added modified ISO-8601 rendering (i.e. ISO-8601 with the T converted to a
space) when using literal_binds with the SQL compilers provided by the
PostgreSQL, MySQL, MariaDB, MSSQL, Oracle dialects. For Oracle, the ISO
format is wrapped inside of an appropriate TO_DATE() function call.
Previously this rendering was not implemented for dialect-specific
compilation.
References: #5052
[bug] [pool] The _pool.QueuePool now ignores max_overflow when
pool_size=0, properly making the pool unlimited in all cases.
References: #8523
[bug] [types] Python string values for which a SQL type is determined from the type of
the value, mainly when using _sql.literal(), will now apply the
_types.String type, rather than the _types.Unicode
datatype, for Python string values that test as "ascii only" using Python
str.isascii(). If the string is not isascii(), the
_types.Unicode datatype will be bound instead, which was used in
all string detection previously. This behavior only applies to in-place
detection of datatypes when using literal() or other contexts that have
no existing datatype, which is not usually the case under normal
_schema.Column comparison operations, where the type of the
_schema.Column being compared always takes precedence.
Use of the _types.Unicode datatype can determine literal string
formatting on backends such as SQL Server, where a literal value (i.e.
using literal_binds) will be rendered as N'<value>' instead of
'value'. For normal bound value handling, the _types.Unicode
datatype also may have implications for passing values to the DBAPI, again
in the case of SQL Server, the pyodbc driver supports the use of
setinputsizes mode <mssql_pyodbc_setinputsizes> which will handle
_types.String versus _types.Unicode differently.
References: #7551
[removed] [sybase] Removed the "sybase" internal dialect that was deprecated in previous SQLAlchemy versions. Third party dialect support is available.
References: #7258
[removed] [firebird] Removed the "firebird" internal dialect that was deprecated in previous SQLAlchemy versions. Third party dialect support is available.
References: #7258
[general] [change] The pin for setuptools<69.3 in pyproject.toml has been removed. This pin was to prevent a sudden change in setuptools to use PEP 62
Released: September 5, 2024
[general] [change] The pin for setuptools<69.3 in pyproject.toml has been removed.
This pin was to prevent a sudden change in setuptools to use PEP 625
from taking place, which would change the file name of SQLAlchemy's source
distribution on pypi to be an all lower case name, which is likely to cause
problems with various build environments that expected the previous naming
style. However, the presence of this pin is holding back environments that
otherwise want to use a newer setuptools, so we've decided to move forward
with this change, with the assumption that build environments will have
largely accommodated the setuptools change by now.
This change was first released in version 2.0.33 however is being backported to 1.4.54 to support ongoing releases.
References: #11818
[general] [change] The setuptools "test" command is removed from the 1.4 series as modern
versions of setuptools actively refuse to accommodate this extension being
present. This change was already part of the 2.0 series. To run the
test suite use the tox command.
[orm] [bug] [regression] Fixed regression from 1.3 where the column key used for a hybrid property might be populated with that of the underlying column that it returns, for a property that returns an ORM mapped column directly, rather than the key used by the hybrid property itself.
References: #11728
[postgresql] [bug] Fixed critical issue in the asyncpg driver where a rollback or commit that
fails specifically for the MissingGreenlet condition or any other error
that is not raised by asyncpg itself would discard the asyncpg transaction
in any case, even though the transaction were still idle, leaving to a
server side condition with an idle transaction that then goes back into the
connection pool. The flags for "transaction closed" are now not reset for
errors that are raised outside of asyncpg itself. When asyncpg itself
raises an error for .commit() or .rollback(), asyncpg does then
discard of this transaction.
References: #11819
[mypy] [bug] The deprecated mypy plugin is no longer fully functional with the latest series of mypy 1.11.0, as changes in the mypy interpreter are no…
Released: July 29, 2024
[general] [bug] Set up full Python 3.13 support to the extent currently possible, repairing issues within internal language helpers as well as the serializer extension module.
For version 1.4, this also modernizes the "extras" names in setup.cfg to use dashes and not underscores for two-word names. Underscore names are still present to accommodate potential compatibility issues.
References: #11417
[orm] [bug] [regression] Fixed regression going back to 1.4 where accessing a collection using the
"dynamic" strategy on a transient object and attempting to query would
raise an internal error rather than the expected NoResultFound
that occurred in 1.3.
References: #11562
[engine] [usecase] Modified the internal representation used for adapting asyncio calls to
greenlets to allow for duck-typed compatibility with third party libraries
that implement SQLAlchemy's "greenlet-to-asyncio" pattern directly.
Running code within a greenlet that features the attribute
__sqlalchemy_greenlet_provider__ = True will allow calls to
sqlalchemy.util.await_only() directly.
[engine] [bug] Adjustments to the C extensions, which are specific to the SQLAlchemy 1.x series, to work under Python 3.13. Pull request courtesy Ben Beasley.
References: #11499
[sql] [bug] Fixed caching issue where using the TextualSelect.add_cte() method
of the TextualSelect construct would not set a correct cache key
which distinguished between different CTE expressions.
References: #11471
[sql] [bug] Fixed caching issue where the
_sql.Select.with_for_update.key_share element of
_sql.Select.with_for_update() was not considered as part of the cache
key, leading to incorrect caching if different variations of this parameter
were used with an otherwise identical statement.
References: #11544
[sqlite] [bug] [reflection] Fixed reflection of computed column in SQLite to properly account for complex expressions.
References: #11582
[mssql] [bug] Fixed issue where SQL Server drivers don't support bound parameters when rendering the "frame specification" for a window function, e.g. "ROWS BETWEEN", etc.
References: #11514
[orm] [bug] Fixed bug where ORM _orm.with_loader_criteria() would not apply itself to a _sql.Select.join() where the ON clause were given as a plain S
Released: March 4, 2024
[orm] [bug] Fixed bug where ORM _orm.with_loader_criteria() would not apply
itself to a _sql.Select.join() where the ON clause were given as a
plain SQL comparison, rather than as a relationship target or similar.
This is a backport of the same issue fixed in version 2.0 for 2.0.22.
References: #10365
[orm] [bug] Improved a fix first implemented for #3208 released in version 0.9.8, where the registry of classes used internally by declarative could b
Released: January 2, 2024
[orm] [bug] Improved a fix first implemented for #3208 released in version 0.9.8, where the registry of classes used internally by declarative could be subject to a race condition in the case where individual mapped classes are being garbage collected at the same time while new mapped classes are being constructed, as can happen in some test suite configurations or dynamic class creation environments. In addition to the weakref check already added, the list of items being iterated is also copied first to avoid "list changed while iterating" errors. Pull request courtesy Yilei Yang.
References: #10782
[asyncio] [bug] Fixed critical issue in asyncio version of the connection pool where
calling _asyncio.AsyncEngine.dispose() would produce a new connection
pool that did not fully re-establish the use of asyncio-compatible mutexes,
leading to the use of a plain threading.Lock() which would then cause
deadlocks in an asyncio context when using concurrency features like
asyncio.gather().
References: #10813
A future release will rename the Identity.order, Sequence.order and Identity.on_null parameters to Oracle-specific names, deprecating the old names, t…
Released: October 29, 2023
[orm] [bug] Fixed fundamental issue which prevented some forms of ORM "annotations"
from taking place for subqueries which made use of _sql.Select.join()
against a relationship target. These annotations are used whenever a
subquery is used in special situations such as within
_orm.PropComparator.and_() and other ORM-specific scenarios.
References: #10223
[sql] [bug] Fixed issue where using the same bound parameter more than once with
literal_execute=True in some combinations with other literal rendering
parameters would cause the wrong values to render due to an iteration
issue.
References: #10142
[sql] [bug] Fixed issue where unpickling of a _schema.Column or other
_sql.ColumnElement would fail to restore the correct "comparator"
object, which is used to generate SQL expressions specific to the type
object.
References: #10213
[schema] [bug] Modified the rendering of the Oracle only Identity.order
parameter that's part of both Sequence and Identity to
only take place for the Oracle backend, and not other backends such as that
of PostgreSQL. A future release will rename the
Identity.order, Sequence.order and
Identity.on_null parameters to Oracle-specific names,
deprecating the old names, these parameters only apply to Oracle.
References: #10207
[mysql] [usecase] Updated aiomysql dialect since the dialect appears to be maintained again. Re-added to the ci testing using version 0.2.0.
[mysql] [bug] Repaired a new incompatibility in the MySQL "pre-ping" routine where the
False argument passed to connection.ping(), which is intended to
disable an unwanted "automatic reconnect" feature, is being deprecated in
MySQL drivers and backends, and is producing warnings for some versions of
MySQL's native client drivers. It's removed for mysqlclient, whereas for
PyMySQL and drivers based on PyMySQL, the parameter will be deprecated and
removed at some point, so API introspection is used to future proof against
these various stages of removal.
References: #10492
[mssql] [bug] [reflection] Fixed issue where identity column reflection would fail for a bigint column with a large identity start value (more than 18 digits).
References: #10504
[platform] [usecase] Compatibility improvements to work fully with Python 3.12
Released: July 5, 2023
[sql] [bug] Fixed issue where the _sql.ColumnOperators.regexp_match()
when using "flags" would not produce a "stable" cache key, that
is, the cache key would keep changing each time causing cache pollution.
The same issue existed for _sql.ColumnOperators.regexp_replace()
with both the flags and the actual replacement expression.
The flags are now represented as fixed modifier strings rendered as
safestrings rather than bound parameters, and the replacement
expression is established within the primary portion of the "binary"
element so that it generates an appropriate cache key.
Note that as part of this change, the
_sql.ColumnOperators.regexp_match.flags and
_sql.ColumnOperators.regexp_replace.flags have been modified to
render as literal strings only, whereas previously they were rendered as
full SQL expressions, typically bound parameters. These parameters should
always be passed as plain Python strings and not as SQL expression
constructs; it's not expected that SQL expression constructs were used in
practice for this parameter, so this is a backwards-incompatible change.
The change also modifies the internal structure of the expression
generated, for _sql.ColumnOperators.regexp_replace() with or without
flags, and for _sql.ColumnOperators.regexp_match() with flags. Third
party dialects which may have implemented regexp implementations of their
own (no such dialects could be located in a search, so impact is expected
to be low) would need to adjust the traversal of the structure to
accommodate.
References: #10042
[sql] [bug] Fixed issue in mostly-internal CacheKey construct where the
__ne__() operator were not properly implemented, leading to nonsensical
results when comparing CacheKey instances to each other.
[orm] [bug] Fixed critical caching issue where the combination of _orm.aliased() and _hybrid.hybrid_property() expression compositions would cause a c
Released: April 30, 2023
[orm] [bug] Fixed critical caching issue where the combination of
_orm.aliased() and _hybrid.hybrid_property() expression
compositions would cause a cache key mismatch, leading to cache keys that
held onto the actual _orm.aliased() object while also not matching
that of equivalent constructs, filling up the cache.
References: #9728
[orm] [bug] Fixed bug where various ORM-specific getters such as
ORMExecuteState.is_column_load,
ORMExecuteState.is_relationship_load,
ORMExecuteState.loader_strategy_path etc. would throw an
AttributeError if the SQL statement itself were a "compound select"
such as a UNION.
References: #9634
[orm] [bug] Fixed endless loop which could occur when using "relationship to aliased
class" feature and also indicating a recursive eager loader such as
lazy="selectinload" in the loader, in combination with another eager
loader on the opposite side. The check for cycles has been fixed to include
aliased class relationships.
References: #9590
[sql] [bug] Fixed bug / regression where using bindparam() with the same name as a column in the Update.values() method of Update, as well as the Inse
Released: March 18, 2023
[sql] [bug] Fixed bug / regression where using bindparam() with the same name
as a column in the Update.values() method of Update, as
well as the Insert.values() method of Insert in 2.0 only,
would in some cases silently fail to honor the SQL expression in which the
parameter were presented, replacing the expression with a new parameter of
the same name and discarding any other elements of the SQL expression, such
as SQL functions, etc. The specific case would be statements that were
constructed against ORM entities rather than plain Table
instances, but would occur if the statement were invoked with a
Session or a Connection.
Update part of the issue was present in both 2.0 and 1.4 and is
backported to 1.4.
References: #9075
[sql] [bug] Fixed stringify for a the CreateSchema and DropSchema
DDL constructs, which would fail with an AttributeError when
stringified without a dialect.
References: #7664
[sql] [bug] Fixed critical SQL caching issue where use of the
_sql.Operators.op() custom operator function would not produce an appropriate
cache key, leading to reduce the effectiveness of the SQL cache.
References: #9506
[mypy] [bug] Adjustments made to the mypy plugin to accommodate for some potential changes being made for issue #236 sqlalchemy2-stubs when using SQLAlchemy 1.4. These changes are being kept in sync within SQLAlchemy 2.0. The changes are also backwards compatible with older versions of sqlalchemy2-stubs.
[mypy] [bug] Fixed crash in mypy plugin which could occur on both 1.4 and 2.0 versions
if a decorator for the _orm.registry.mapped() decorator were used
that was referenced in an expression with more than two components (e.g.
@Backend.mapper_registry.mapped). This scenario is now ignored; when
using the plugin, the decorator expression needs to be two components (i.e.
@reg.mapped).
References: #9102
[postgresql] [bug] Added support to the asyncpg dialect to return the cursor.rowcount
value for SELECT statements when available. While this is not a typical use
for cursor.rowcount, the other PostgreSQL dialects generally provide
this value. Pull request courtesy Michael Gorven.
References: #9048
[mysql] [usecase] Added support to MySQL index reflection to correctly reflect the
mysql_length dictionary, which previously was being ignored.
References: #9047
[mssql] [bug] Fixed bug where a schema name given with brackets, but no dots inside the
name, for parameters such as _schema.Table.schema would not be
interpreted within the context of the SQL Server dialect's documented
behavior of interpreting explicit brackets as token delimiters, first added
in 1.2 for #2626, when referring to the schema name in reflection
operations. The original assumption for #2626's behavior was that the
special interpretation of brackets was only significant if dots were
present, however in practice, the brackets are not included as part of the
identifier name for all SQL rendering operations since these are not valid
characters within regular or delimited identifiers. Pull request courtesy
Shan.
References: #9133
[oracle] [bug] Added _oracle.ROWID to reflected types as this type may be used in
a "CREATE TABLE" statement.
References: #5047
[general] [change] A new deprecation "uber warning" is now emitted at runtime the first time any SQLAlchemy 2.0 deprecation warning would normally be…
Released: January 3, 2023
[general] [change] A new deprecation "uber warning" is now emitted at runtime the
first time any SQLAlchemy 2.0 deprecation warning would normally be
emitted, but the SQLALCHEMY_WARN_20 environment variable is not set.
The warning emits only once at most, before setting a boolean to prevent
it from emitting a second time.
This deprecation warning intends to notify users who may not have set an
appropriate constraint in their requirements files to block against a
surprise SQLAlchemy 2.0 upgrade and also alert that the SQLAlchemy 2.0
upgrade process is available, as the first full 2.0 release is expected
very soon. The deprecation warning can be silenced by setting the
environment variable SQLALCHEMY_SILENCE_UBER_WARNING to "1".
References: #8983
[general] [bug] Fixed regression where the base compat module was calling upon
platform.architecture() in order to detect some system properties,
which results in an over-broad system call against the system-level
file call that is unavailable under some circumstances, including
within some secure environment configurations.
References: #8995
[orm] [bug] Fixed issue in the internal SQL traversal for DML statements like
_dml.Update and _dml.Delete which would cause among other
potential issues, a specific issue using lambda statements with the ORM
update/delete feature.
References: #9033
[engine] [bug] Fixed a long-standing race condition in the connection pool which could
occur under eventlet/gevent monkeypatching schemes in conjunction with the
use of eventlet/gevent Timeout conditions, where a connection pool
checkout that's interrupted due to the timeout would fail to clean up the
failed state, causing the underlying connection record and sometimes the
database connection itself to "leak", leaving the pool in an invalid state
with unreachable entries. This issue was first identified and fixed in
SQLAlchemy 1.2 for #4225, however the failure modes detected in
that fix failed to accommodate for BaseException, rather than
Exception, which prevented eventlet/gevent Timeout from being
caught. In addition, a block within initial pool connect has also been
identified and hardened with a BaseException -> "clean failed connect"
block to accommodate for the same condition in this location.
Big thanks to Github user @niklaus for their tenacious efforts in
identifying and describing this intricate issue.
References: #8974
[sql] [bug] Added parameter
FunctionElement.column_valued.joins_implicitly, which is
useful in preventing the "cartesian product" warning when making use of
table-valued or column-valued functions. This parameter was already
introduced for FunctionElement.table_valued() in #7845,
however it failed to be added for FunctionElement.column_valued()
as well.
References: #9009
[sql] [bug] Fixed bug where SQL compilation would fail (assertion fail in 2.0, NoneType
error in 1.4) when using an expression whose type included
_types.TypeEngine.bind_expression(), in the context of an "expanding"
(i.e. "IN") parameter in conjunction with the literal_binds compiler
parameter.
References: #8989
[sql] [bug] Fixed issue in lambda SQL feature where the calculated type of a literal
value would not take into account the type coercion rules of the "compared
to type", leading to a lack of typing information for SQL expressions, such
as comparisons to _types.JSON elements and similar.
References: #9029
[postgresql] [usecase] Added the PostgreSQL type MACADDR8.
Pull request courtesy of Asim Farooq.
References: #8393
[postgresql] [bug] Fixed bug where the PostgreSQL
_postgresql.Insert.on_conflict_do_update.constraint parameter
would accept an Index object, however would not expand this index
out into its individual index expressions, instead rendering its name in an
ON CONFLICT ON CONSTRAINT clause, which is not accepted by PostgreSQL; the
"constraint name" form only accepts unique or exclude constraint names. The
parameter continues to accept the index but now expands it out into its
component expressions for the render.
References: #9023
[sqlite] [bug] Fixed regression caused by new support for reflection of partial indexes on
SQLite added in 1.4.45 for #8804, where the index_list pragma
command in very old versions of SQLite (possibly prior to 3.8.9) does not
return the current expected number of columns, leading to exceptions raised
when reflecting tables and indexes.
References: #8969
[tests] [bug] Fixed issue in tox.ini file where changes in the tox 4.0 series to the format of "passenv" caused tox to not function correctly, in particular raising an error as of tox 4.0.6.
[tests] [bug] Added new exclusion rule for third party dialects called
unusual_column_name_characters, which can be "closed" for third party
dialects that don't support column names with unusual characters such as
dots, slashes, or percent signs in them, even if the name is properly
quoted.
References: #9002
[orm] [bug] Fixed bug where _orm.Session.merge() would fail to preserve the current loaded contents of relationship attributes that were indicated wit
Released: December 10, 2022
[orm] [bug] Fixed bug where _orm.Session.merge() would fail to preserve the
current loaded contents of relationship attributes that were indicated with
the _orm.relationship.viewonly parameter, thus defeating
strategies that use _orm.Session.merge() to pull fully loaded objects
from caches and other similar techniques. In a related change, fixed issue
where an object that contains a loaded relationship that was nonetheless
configured as lazy='raise' on the mapping would fail when passed to
_orm.Session.merge(); checks for "raise" are now suspended within
the merge process assuming the _orm.Session.merge.load
parameter remains at its default of True.
Overall, this is a behavioral adjustment to a change introduced in the 1.4
series as of #4994, which took "merge" out of the set of cascades
applied by default to "viewonly" relationships. As "viewonly" relationships
aren't persisted under any circumstances, allowing their contents to
transfer during "merge" does not impact the persistence behavior of the
target object. This allows _orm.Session.merge() to correctly suit one
of its use cases, that of adding objects to a Session that were
loaded elsewhere, often for the purposes of restoring from a cache.
References: #8862
[orm] [bug] Fixed issues in _orm.with_expression() where expressions that were
composed of columns that were referenced from the enclosing SELECT would
not render correct SQL in some contexts, in the case where the expression
had a label name that matched the attribute which used
_orm.query_expression(), even when _orm.query_expression() had
no default expression. For the moment, if the _orm.query_expression()
does have a default expression, that label name is still used for that
default, and an additional label with the same name will continue to be
ignored. Overall, this case is pretty thorny so further adjustments might
be warranted.
References: #8881
[engine] [bug] Fixed issue where _engine.Result.freeze() method would not work for
textual SQL using either _sql.text() or
_engine.Connection.exec_driver_sql().
References: #8963
[sql] [usecase] An informative re-raise is now thrown in the case where any "literal bindparam" render operation fails, indicating the value itself and the datatype in use, to assist in debugging when literal params are being rendered in a statement.
References: #8800
[sql] [bug] Fixed a series of issues regarding the position and sometimes the identity
of rendered bound parameters, such as those used for SQLite, asyncpg,
MySQL, Oracle and others. Some compiled forms would not maintain the order
of parameters correctly, such as the PostgreSQL regexp_replace()
function, the "nesting" feature of the CTE construct first
introduced in #4123, and selectable tables formed by using the
FunctionElement.column_valued() method with Oracle.
References: #8827
[asyncio] [bug] Removed non-functional merge() method from
_asyncio.AsyncResult. This method has never worked and was
included with _asyncio.AsyncResult in error.
References: #8952
[postgresql] [bug] Made an adjustment to how the PostgreSQL dialect considers column types
when it reflects columns from a table, to accommodate for alternative
backends which may return NULL from the PG format_type() function.
References: #8748
[sqlite] [usecase] Added support for the SQLite backend to reflect the "DEFERRABLE" and "INITIALLY" keywords which may be present on a foreign key construct. Pull request courtesy Michael Gorven.
References: #8903
[sqlite] [usecase] Added support for reflection of expression-oriented WHERE criteria included in indexes on the SQLite dialect, in a manner similar to that of the PostgreSQL dialect. Pull request courtesy Tobias Pfeiffer.
References: #8804
[sqlite] [bug] Backported a fix for SQLite reflection of unique constraints in attached schemas, released in 2.0 as a small part of #4379. Previously, unique constraints in attached schemas would be ignored by SQLite reflection. Pull request courtesy Michael Gorven.
References: #8866
[oracle] [bug] Continued fixes for Oracle fix #8708 released in 1.4.43 where bound parameter names that start with underscores, which are disallowed by Oracle, were still not being properly escaped in all circumstances.
References: #8708
[oracle] [bug] Fixed issue in Oracle compiler where the syntax for
FunctionElement.column_valued() was incorrect, rendering the name
COLUMN_VALUE without qualifying the source table correctly.
References: #8945
[sql] [bug] Fixed critical memory issue identified in cache key generation, where for very large and complex ORM statements that make use of lots of O
Released: November 12, 2022
[sql] [bug] Fixed critical memory issue identified in cache key generation, where for very large and complex ORM statements that make use of lots of ORM aliases with subqueries, cache key generation could produce excessively large keys that were orders of magnitude bigger than the statement itself. Much thanks to Rollo Konig Brock for their very patient, long term help in finally identifying this issue.
References: #8790
[postgresql] [bug] [mssql] For the PostgreSQL and SQL Server dialects only, adjusted the compiler so that when rendering column expressions in the RETURNING clause, the "non anon" label that's used in SELECT statements is suggested for SQL expression elements that generate a label; the primary example is a SQL function that may be emitting as part of the column's type, where the label name should match the column's name by default. This restores a not-well defined behavior that had changed in version 1.4.21 due to #6718, #6710. The Oracle dialect has a different RETURNING implementation and was not affected by this issue. Version 2.0 features an across the board change for its widely expanded support of RETURNING on other backends.
References: #8770
insert(some_table).values(...).returning(some_table) against a full
Table object at once would fail to execute, raising an exception.[tests] [bug] Fixed issue where the --disable-asyncio parameter to the test suite
would fail to not actually run greenlet tests and would also not prevent
the suite from using a "wrapping" greenlet for the whole suite. This
parameter now ensures that no greenlet or asyncio use will occur within the
entire run when set.
References: #8793
[tests] [bug] Adjusted the test suite which tests the Mypy plugin to accommodate for changes in Mypy 0.990 regarding how it handles message output, which affect how sys.path is interpreted when determining if notes and errors should be printed for particular files. The change broke the test suite as the files within the test directory itself no longer produced messaging when run under the mypy API.
[orm] [bug] Fixed issue in joined eager loading where an assertion fail would occur with a particular combination of outer/inner joined eager loads, w
Released: November 4, 2022
[orm] [bug] Fixed issue in joined eager loading where an assertion fail would occur with a particular combination of outer/inner joined eager loads, when eager loading across three mappers where the middle mapper was an inherited subclass mapper.
References: #8738
[orm] [bug] Fixed bug involving Select constructs, where combinations of
Select.select_from() with Select.join(), as well as when
using Select.join_from(), would cause the
_orm.with_loader_criteria() feature as well as the IN criteria needed
for single-table inheritance queries to not render, in cases where the
columns clause of the query did not explicitly include the left-hand side
entity of the JOIN. The correct entity is now transferred to the
Join object that's generated internally, so that the criteria
against the left side entity is correctly added.
References: #8721
[orm] [bug] An informative exception is now raised when the
_orm.with_loader_criteria() option is used as a loader option added
to a specific "loader path", such as when using it within
Load.options(). This use is not supported as
_orm.with_loader_criteria() is only intended to be used as a top
level loader option. Previously, an internal error would be generated.
References: #8711
[orm] [bug] Improved "dictionary mode" for _orm.Session.get() so that synonym
names which refer to primary key attribute names may be indicated in the
named dictionary.
References: #8753
[orm] [bug] Fixed issue where "selectin_polymorphic" loading for inheritance mappers
would not function correctly if the _orm.Mapper.polymorphic_on
parameter referred to a SQL expression that was not directly mapped on the
class.
References: #8704
[orm] [bug] Fixed issue where the underlying DBAPI cursor would not be closed when
using the _orm.Query object as an iterator, if a user-defined exception
case were raised within the iteration process, thereby causing the iterator
to be closed by the Python interpreter. When using
_orm.Query.yield_per() to create server-side cursors, this would lead
to the usual MySQL-related issues with server side cursors out of sync,
and without direct access to the Result object, end-user code
could not access the cursor in order to close it.
To resolve, a catch for GeneratorExit is applied within the iterator
method, which will close the result object in those cases when the
iterator were interrupted, and by definition will be closed by the
Python interpreter.
As part of this change as implemented for the 1.4 series, ensured that
.close() methods are available on all Result implementations
including ScalarResult, MappingResult. The 2.0
version of this change also includes new context manager patterns for use
with Result classes.
References: #8710
[engine] [bug] [regression] Fixed issue where the PoolEvents.reset() event hook would not be be
called in all cases when a _engine.Connection were closed and was
in the process of returning its DBAPI connection to the connection pool.
The scenario was when the _engine.Connection had already emitted
.rollback() on its DBAPI connection within the process of returning
the connection to the pool, where it would then instruct the connection
pool to forego doing its own "reset" to save on the additional method
call. However, this prevented custom pool reset schemes from being
used within this hook, as such hooks by definition are doing more than
just calling .rollback(), and need to be invoked under all
circumstances. This was a regression that appeared in version 1.4.
For version 1.4, the PoolEvents.checkin() remains viable as an
alternate event hook to use for custom "reset" implementations. Version 2.0
will feature an improved version of PoolEvents.reset() which is
called for additional scenarios such as termination of asyncio connections,
and is also passed contextual information about the reset, to allow for
"custom connection reset" schemes which can respond to different reset
scenarios in different ways.
References: #8717
[engine] [bug] Ensured all Result objects include a Result.close() method
as well as a Result.closed attribute, including on
ScalarResult and MappingResult.
References: #8710
[sql] [bug] Fixed issue which prevented the _sql.literal_column() construct from
working properly within the context of a Select construct as well
as other potential places where "anonymized labels" might be generated, if
the literal expression contained characters which could interfere with
format strings, such as open parenthesis, due to an implementation detail
of the "anonymous label" structure.
References: #8724
[mssql] [bug] Fixed issue with Inspector.has_table(), which when used against a
temporary table with the SQL Server dialect would fail on some Azure
variants, due to an unnecessary information schema query that is not
supported on those server versions. Pull request courtesy Mike Barry.
References: #8714
[mssql] [bug] [reflection] Fixed issue with Inspector.has_table(), which when used against a
view with the SQL Server dialect would erroneously return False, due to
a regression in the 1.4 series which removed support for this on SQL
Server. The issue is not present in the 2.0 series which uses a different
reflection architecture. Test support is added to ensure has_table()
remains working per spec re: views.
References: #8700
[oracle] [bug] Fixed issue where bound parameter names, including those automatically derived from similarly-named database columns, which contained characters that normally require quoting with Oracle would not be escaped when using "expanding parameters" with the Oracle dialect, causing execution errors. The usual "quoting" for bound parameters used by the Oracle dialect is not used with the "expanding parameters" architecture, so escaping for a large range of characters is used instead, now using a list of characters/escapes that are specific to Oracle.
References: #8708
[oracle] [bug] Fixed issue where the nls_session_parameters view queried on first
connect in order to get the default decimal point character may not be
available depending on Oracle connection modes, and would therefore raise
an error. The approach to detecting decimal char has been simplified to
test a decimal value directly, instead of reading system views, which
works on any backend / driver.
References: #8744
[orm] [bug] The _orm.Session.execute.bind_arguments dictionary is no longer mutated when passed to _orm.Session.execute() and similar; instead, it's c
Released: October 16, 2022
[orm] [bug] The _orm.Session.execute.bind_arguments dictionary is no longer
mutated when passed to _orm.Session.execute() and similar; instead,
it's copied to an internal dictionary for state changes. Among other
things, this fixes and issue where the "clause" passed to the
_orm.Session.get_bind() method would be incorrectly referring to the
_sql.Select construct used for the "fetch" synchronization
strategy, when the actual query being emitted was a _dml.Delete or
_dml.Update. This would interfere with recipes for "routing
sessions".
References: #8614
[orm] [bug] A warning is emitted in ORM configurations when an explicit
_orm.remote() annotation is applied to columns that are local to the
immediate mapped class, when the referenced class does not include any of
the same table columns. Ideally this would raise an error at some point as
it's not correct from a mapping point of view.
References: #7094
[orm] [bug] A warning is emitted when attempting to configure a mapped class within an inheritance hierarchy where the mapper is not given any polymorphic identity, however there is a polymorphic discriminator column assigned. Such classes should be abstract if they never intend to load directly.
References: #7545
[orm] [bug] [regression] Fixed regression for 1.4 in _orm.contains_eager() where the "wrap in
subquery" logic of _orm.joinedload() would be inadvertently triggered
for use of the _orm.contains_eager() function with similar statements
(e.g. those that use distinct(), limit() or offset()), which
would then lead to secondary issues with queries that used some
combinations of SQL label names and aliasing. This "wrapping" is not
appropriate for _orm.contains_eager() which has always had the
contract that the user-defined SQL statement is unmodified with the
exception of adding the appropriate columns to be fetched.
References: #8569
[orm] [bug] [regression] Fixed regression where using ORM update() with synchronize_session='fetch' would fail due to the use of evaluators that are now used to determine the in-Python value for expressions in the the SET clause when refreshing objects; if the evaluators make use of math operators against non-numeric values such as PostgreSQL JSONB, the non-evaluable condition would fail to be detected correctly. The evaluator now limits the use of math mutation operators to numeric types only, with the exception of "+" that continues to work for strings as well. SQLAlchemy 2.0 may alter this further by fetching the SET values completely rather than using evaluation.
References: #8507
[engine] [bug] Fixed issue where mixing "*" with additional explicitly-named column
expressions within the columns clause of a _sql.select() construct
would cause result-column targeting to sometimes consider the label name or
other non-repeated names to be an ambiguous target.
References: #8536
[asyncio] [bug] Improved implementation of asyncio.shield() used in context managers as
added in #8145, such that the "close" operation is enclosed within
an asyncio.Task which is then strongly referenced as the operation
proceeds. This is per Python documentation indicating that the task is
otherwise not strongly referenced.
References: #8516
[postgresql] [usecase] _postgresql.aggregate_order_by now supports cache generation.
References: #8574
[mysql] [bug] Adjusted the regular expression used to match "CREATE VIEW" when testing for views to work more flexibly, no longer requiring the special keyword "ALGORITHM" in the middle, which was intended to be optional but was not working correctly. The change allows view reflection to work more completely on MySQL-compatible variants such as StarRocks. Pull request courtesy John Bodley.
References: #8588
[mssql] [bug] [regression] Fixed yet another regression in SQL Server isolation level fetch (see
#8231, #8475), this time with "Microsoft Dynamics CRM
Database via Azure Active Directory", which apparently lacks the
system_views view entirely. Error catching has been extended that under
no circumstances will this method ever fail, provided database connectivity
is present.
References: #8525
[orm] [bug] [events] Fixed event listening issue where event listeners added to a superclass would be lost if a subclass were created which then had i
Released: September 6, 2022
[orm] [bug] [events] Fixed event listening issue where event listeners added to a superclass
would be lost if a subclass were created which then had its own listeners
associated. The practical example is that of the sessionmaker
class created after events have been associated with the
_orm.Session class.
References: #8467
[orm] [bug] Hardened the cache key strategy for the _orm.aliased() and
_orm.with_polymorphic() constructs. While no issue involving actual
statements being cached can easily be demonstrated (if at all), these two
constructs were not including enough of what makes them unique in their
cache keys for caching on the aliased construct alone to be accurate.
References: #8401
[orm] [bug] [regression] Fixed regression appearing in the 1.4 series where a joined-inheritance query placed as a subquery within an enclosing query for that same entity would fail to render the JOIN correctly for the inner query. The issue manifested in two different ways prior and subsequent to version 1.4.18 (related issue #6595), in one case rendering JOIN twice, in the other losing the JOIN entirely. To resolve, the conditions under which "polymorphic loading" are applied have been scaled back to not be invoked for simple joined inheritance queries.
References: #8456
[orm] [bug] Fixed issue in sqlalchemy.ext.mutable extension where collection
links to the parent object would be lost if the object were merged with
Session.merge() while also passing Session.merge.load
as False.
References: #8446
[orm] [bug] Fixed issue involving _orm.with_loader_criteria() where a closure
variable used as bound parameter value within the lambda would not carry
forward correctly into additional relationship loaders such as
_orm.selectinload() and _orm.lazyload() after the statement
were cached, using the stale originally-cached value instead.
References: #8399
[sql] [bug] Fixed issue where use of the _sql.table() construct, passing a string
for the _sql.table.schema parameter, would fail to take the
"schema" string into account when producing a cache key, thus leading to
caching collisions if multiple, same-named _sql.table() constructs
with different schemas were used.
References: #8441
[asyncio] [bug] Integrated support for asyncpg's terminate() method call for cases
where the connection pool is recycling a possibly timed-out connection,
where a connection is being garbage collected that wasn't gracefully
closed, as well as when the connection has been invalidated. This allows
asyncpg to abandon the connection without waiting for a response that may
incur long timeouts.
References: #8419
[mssql] [bug] [regression] Fixed regression caused by the fix for #8231 released in 1.4.40
where connection would fail if the user did not have permission to query
the dm_exec_sessions or dm_pdw_nodes_exec_sessions system views
when trying to determine the current transaction isolation level.
References: #8475
[orm] [bug] Fixed issue where referencing a CTE multiple times in conjunction with a polymorphic SELECT could result in multiple "clones" of the same
Released: August 8, 2022
[orm] [bug] Fixed issue where referencing a CTE multiple times in conjunction with a polymorphic SELECT could result in multiple "clones" of the same CTE being constructed, which would then trigger these two CTEs as duplicates. To resolve, the two CTEs are deep-compared when this occurs to ensure that they are equivalent, then are treated as equivalent.
References: #8357
[orm] [bug] A _sql.select() construct that is passed a sole '*' argument for
SELECT *, either via string, _sql.text(), or
_sql.literal_column(), will be interpreted as a Core-level SQL
statement rather than as an ORM level statement. This is so that the *,
when expanded to match any number of columns, will result in all columns
returned in the result. the ORM- level interpretation of
_sql.select() needs to know the names and types of all ORM columns up
front which can't be achieved when '*' is used.
If '* is used amongst other expressions simultaneously with an ORM
statement, an error is raised as this can't be interpreted correctly by the
ORM.
References: #8235
[orm] [declarative] [bug] Fixed issue where a hierarchy of classes set up as an abstract or mixin
declarative classes could not declare standalone columns on a superclass
that would then be copied correctly to a _orm.declared_attr
callable that wanted to make use of them on a descendant class.
References: #8190
[engine] [usecase] Implemented new _engine.Connection.execution_options.yield_per
execution option for _engine.Connection in Core, to mirror that of
the same yield_per <orm_queryguide_yield_per> option available in
the ORM. The option sets both the
_engine.Connection.execution_options.stream_results option at
the same time as invoking _engine.Result.yield_per(), to provide the
most common streaming result configuration which also mirrors that of the
ORM use case in its usage pattern.
[engine] [bug] Fixed bug in _engine.Result where the usage of a buffered result
strategy would not be used if the dialect in use did not support an
explicit "server side cursor" setting, when using
_engine.Connection.execution_options.stream_results. This is in
error as DBAPIs such as that of SQLite and Oracle already use a
non-buffered result fetching scheme, which still benefits from usage of
partial result fetching. The "buffered" strategy is now used in all
cases where _engine.Connection.execution_options.stream_results
is set.
[engine] [bug] Added FilterResult.yield_per() so that result implementations
such as MappingResult, ScalarResult and
AsyncResult have access to this method.
References: #8199
[sql] [bug] Adjusted the SQL compilation for string containment functions
.contains(), .startswith(), .endswith() to force the use of the
string concatenation operator, rather than relying upon the overload of the
addition operator, so that non-standard use of these operators with for
example bytestrings still produces string concatenation operators.
References: #8253
[mypy] [bug] Fixed a crash of the mypy plugin when using a lambda as a Column default. Pull request curtesy of tchapi.
References: #8196
[asyncio] [bug] Added asyncio.shield() to the connection and session release process
specifically within the __aexit__() context manager exit, when using
AsyncConnection or AsyncSession as a context manager
that releases the object when the context manager is complete. This appears
to help with task cancellation when using alternate concurrency libraries
such as anyio, uvloop that otherwise don't provide an async context
for the connection pool to release the connection properly during task
cancellation.
References: #8145
[postgresql] [bug] Fixed issue in psycopg2 dialect where the "multiple hosts" feature
implemented for #4392, where multiple host:port pairs could be
passed in the query string as
?host=host1:port1&host=host2:port2&host=host3:port3 was not implemented
correctly, as it did not propagate the "port" parameter appropriately.
Connections that didn't use a different "port" likely worked without issue,
and connections that had "port" for some of the entries may have
incorrectly passed on that hostname. The format is now corrected to pass
hosts/ports appropriately.
As part of this change, maintained support for another multihost style that
worked unintentionally, which is comma-separated
?host=h1,h2,h3&port=p1,p2,p3. This format is more consistent with
libpq's query-string format, whereas the previous format is inspired by a
different aspect of libpq's URI format but is not quite the same thing.
If the two styles are mixed together, an error is raised as this is ambiguous.
References: #4392
[mssql] [bug] Fixed issues that prevented the new usage patterns for using DML with ORM
objects presented at orm_dml_returning_objects from working
correctly with the SQL Server pyodbc dialect.
References: #8210
[mssql] [bug] Fixed issue where the SQL Server dialect's query for the current isolation
level would fail on Azure Synapse Analytics, due to the way in which this
database handles transaction rollbacks after an error has occurred. The
initial query has been modified to no longer rely upon catching an error
when attempting to detect the appropriate system view. Additionally, to
better support this database's very specific "rollback" behavior,
implemented new parameter ignore_no_transaction_on_rollback indicating
that a rollback should ignore Azure Synapse error 'No corresponding
transaction found. (111214)', which is raised if no transaction is present
in conflict with the Python DBAPI.
Initial patch and valuable debugging assistance courtesy of @ww2406.
References: #8231
[bug] [types] Fixed issue where TypeDecorator would not correctly proxy the
__getitem__() operator when decorating the _types.ARRAY
datatype, without explicit workarounds.
References: #7249
[orm] [bug] [regression] Fixed regression caused by #8133 where the pickle format for mutable attributes was changed, without a fallback to recognize
Released: June 24, 2022
[orm] [bug] [regression] Fixed regression caused by #8133 where the pickle format for mutable attributes was changed, without a fallback to recognize the old format, causing in-place upgrades of SQLAlchemy to no longer be able to read pickled data from previous versions. A check plus a fallback for the old format is now in place.
References: #8133
[engine] [bug] Repaired a deprecation warning class decorator that was preventing key objects such as _engine.Connection from having a proper __weakre…
Released: June 23, 2022
[orm] [bug] [regression] Fixed regression caused by #8064 where a particular check for
column correspondence was made too liberal, resulting in incorrect
rendering for some ORM subqueries such as those using
PropComparator.has() or PropComparator.any() in conjunction
with joined-inheritance queries that also use legacy aliasing features.
References: #8162
[orm] [bug] [sql] Fixed an issue where _sql.GenerativeSelect.fetch() would not
be applied when executing a statement using the ORM.
References: #8091
[orm] [bug] Fixed issue where a _orm.with_loader_criteria() option could not be
pickled, as is necessary when it is carried along for propagation to lazy
loaders in conjunction with a caching scheme. Currently, the only form that
is supported as picklable is to pass the "where criteria" as a fixed
module-level callable function that produces a SQL expression. An ad-hoc
"lambda" can't be pickled, and a SQL expression object is usually not fully
picklable directly.
References: #8109
[engine] [bug] Repaired a deprecation warning class decorator that was preventing key
objects such as _engine.Connection from having a proper
__weakref__ attribute, causing operations like Python standard library
inspect.getmembers() to fail.
References: #8115
[sql] [bug] Fixed multiple observed race conditions related to lambda_stmt(),
including an initial "dogpile" issue when a new Python code object is
initially analyzed among multiple simultaneous threads which created both a
performance issue as well as some internal corruption of state.
Additionally repaired observed race condition which could occur when
"cloning" an expression construct that is also in the process of being
compiled or otherwise accessed in a different thread due to memoized
attributes altering the __dict__ while iterated, for Python versions
prior to 3.10; in particular the lambda SQL construct is sensitive to this
as it holds onto a single statement object persistently. The iteration has
been refined to use dict.copy() with or without an additional iteration
instead.
References: #8098
[sql] [bug] Enhanced the mechanism of Cast and other "wrapping"
column constructs to more fully preserve a wrapped Label
construct, including that the label name will be preserved in the
.c collection of a Subquery. The label was already
able to render in the SQL correctly on the outside of the construct
which it was wrapped inside.
References: #8084
[sql] [bug] Adjusted the fix made for #8056 which adjusted the escaping of
bound parameter names with special characters such that the escaped names
were translated after the SQL compilation step, which broke a published
recipe on the FAQ illustrating how to merge parameter names into the string
output of a compiled SQL string. The change restores the escaped names that
come from compiled.params and adds a conditional parameter to
SQLCompiler.construct_params() named escape_names that defaults
to True, restoring the old behavior by default.
References: #8113
[schema] [bug] Fixed bugs involving the Table.include_columns and the
Table.resolve_fks parameters on Table; these
little-used parameters were apparently not working for columns that refer
to foreign key constraints.
In the first case, not-included columns that refer to foreign keys would
still attempt to create a ForeignKey object, producing errors
when attempting to resolve the columns for the foreign key constraint
within reflection; foreign key constraints that refer to skipped columns
are now omitted from the table reflection process in the same way as
occurs for Index and UniqueConstraint objects with the
same conditions. No warning is produced however, as we likely want to
remove the include_columns warnings for all constraints in 2.0.
In the latter case, the production of table aliases or subqueries would
fail on an FK related table not found despite the presence of
resolve_fks=False; the logic has been repaired so that if a related
table is not found, the ForeignKey object is still proxied to the
aliased table or subquery (these ForeignKey objects are normally
used in the production of join conditions), but it is sent with a flag that
it's not resolvable. The aliased table / subquery will then work normally,
with the exception that it cannot be used to generate a join condition
automatically, as the foreign key information is missing. This was already
the behavior for such foreign key constraints produced using non-reflection
methods, such as joining Table objects from different
MetaData collections.
[schema] [bug] [mssql] Fixed issue where Table objects that made use of IDENTITY columns
with a Numeric datatype would produce errors when attempting to
reconcile the "autoincrement" column, preventing construction of the
Column from using the Column.autoincrement parameter
as well as emitting errors when attempting to invoke an Insert
construct.
References: #8111
[extensions] [bug] Fixed bug in Mutable where pickling and unpickling of an ORM
mapped instance would not correctly restore state for mappings that
contained multiple Mutable-enabled attributes.
References: #8133
[orm] [bug] Fixed issue where using a _orm.column_property() construct containing a subquery against an already-mapped column attribute would not corr
Released: May 31, 2022
[orm] [bug] Fixed issue where using a _orm.column_property() construct containing
a subquery against an already-mapped column attribute would not correctly
apply ORM-compilation behaviors to the subquery, including that the "IN"
expression added for a single-table inherits expression would fail to be
included.
References: #8064
[orm] [bug] Fixed issue where ORM results would apply incorrect key names to the
returned Row objects in the case where the set of columns to be
selected were changed, such as when using
Select.with_only_columns().
References: #8001
[orm] [bug] [oracle] [postgresql] Fixed bug, likely a regression from 1.3, where usage of column names that require bound parameter escaping, more concretely when using Oracle with column names that require quoting such as those that start with an underscore, or in less common cases with some PostgreSQL drivers when using column names that contain percent signs, would cause the ORM versioning feature to not work correctly if the versioning column itself had such a name, as the ORM assumes certain bound parameter naming conventions that were being interfered with via the quotes. This issue is related to #8053 and essentially revises the approach towards fixing this, revising the original issue #5653 that created the initial implementation for generalized bound-parameter name quoting.
References: #8056
[engine] [bug] [tests] Fixed issue where support for logging "stacklevel" implemented in #7612 required adjustment to work with recently released Python 3.11.0b1, also repairs the unit tests which tested this feature.
References: #8019
[sql] [bug] [postgresql] [sqlite] Fixed bug where the PostgreSQL
_postgresql.Insert.on_conflict_do_update() method and the SQLite
_sqlite.Insert.on_conflict_do_update() method would both fail to
correctly accommodate a column with a separate ".key" when specifying the
column using its key name in the dictionary passed to
_postgresql.Insert.on_conflict_do_update.set_, as well as if
the _postgresql.Insert.excluded collection were used as the
dictionary directly.
References: #8014
[sql] [bug] An informative error is raised for the use case where
Insert.from_select() is being passed a "compound select" object such
as a UNION, yet the INSERT statement needs to append additional columns to
support Python-side or explicit SQL defaults from the table metadata. In
this case a subquery of the compound object should be passed.
References: #8073
[sql] [bug] Fixed an issue where using bindparam() with no explicit data or type
given could be coerced into the incorrect type when used in expressions
such as when using ARRAY.Comparator.any() and
ARRAY.Comparator.all().
References: #7979
[sql] [bug] An informative error is raised if two individual BindParameter
objects share the same name, yet one is used within an "expanding" context
(typically an IN expression) and the other is not; mixing the same name in
these two different styles of usage is not supported and typically the
expanding=True parameter should be set on the parameters that are to
receive list values outside of IN expressions (where expanding is set
by default).
References: #8018
[mysql] [bug] Further adjustments to the MySQL PyODBC dialect to allow for complete connectivity, which was previously still not working despite fixes in #7871.
References: #7966
[mysql] [bug] Added disconnect code for MySQL error 4031, introduced in MySQL >= 8.0.24, indicating connection idle timeout exceeded. In particular this repairs an issue where pre-ping could not reconnect on a timed-out connection. Pull request courtesy valievkarim.
References: #8036
[mssql] [bug] Fix issue where a password with a leading "{" would result in login failure.
References: #8062
[mssql] [bug] [reflection] Explicitly specify the collation when reflecting table columns using MSSQL to prevent "collation conflict" errors.
References: #8035
[oracle] [usecase] Added two new error codes for Oracle disconnect handling to support early testing of the new "python-oracledb" driver released by Oracle.
References: #8066
[oracle] [bug] Fixed SQL compiler issue where the "bind processing" function for a bound
parameter would not be correctly applied to a bound value if the bound
parameter's name were "escaped". Concretely, this applies, among other
cases, to Oracle when a Column has a name that itself requires
quoting, such that the quoting-required name is then used for the bound
parameters generated within DML statements, and the datatype in use
requires bind processing, such as the Enum datatype.
References: #8053
[orm] [bug] [regression] Fixed regression where the change made for #7861, released in version 1.4.33, that brought the Insert construct to be partial
Released: April 26, 2022
[orm] [bug] [regression] Fixed regression where the change made for #7861, released in
version 1.4.33, that brought the Insert construct to be partially
recognized as an ORM-enabled statement did not properly transfer the
correct mapper / mapped table state to the Session, causing the
Session.get_bind() method to fail for a Session that was
bound to engines and/or connections using the Session.binds
parameter.
References: #7936
[orm] [declarative] [bug] Modified the DeclarativeMeta metaclass to pass cls.__dict__
into the declarative scanning process to look for attributes, rather than
the separate dictionary passed to the type's __init__() method. This
allows user-defined base classes that add attributes within an
__init_subclass__() to work as expected, as __init_subclass__() can
only affect the cls.__dict__ itself and not the other dictionary. This
is technically a regression from 1.3 where __dict__ was being used.
References: #7900
[engine] [bug] Fixed a memory leak in the C extensions which could occur when calling upon
named members of Row when the member does not exist under Python
3; in particular this could occur during NumPy transformations when it
attempts to call members such as .__array__, but the issue was
surrounding any AttributeError thrown by the Row object. This
issue does not apply to version 2.0 which has already transitioned to
Cython. Thanks much to Sebastian Berg for identifying the problem.
References: #7875
[engine] [bug] Added a warning regarding a bug which exists in the Result.columns()
method when passing 0 for the index in conjunction with a Result
that will return a single ORM entity, which indicates that the current
behavior of Result.columns() is broken in this case as the
Result object will yield scalar values and not Row
objects. The issue will be fixed in 2.0, which would be a
backwards-incompatible change for code that relies on the current broken
behavior. Code which wants to receive a collection of scalar values should
use the Result.scalars() method, which will return a new
ScalarResult object that yields non-row scalar objects.
References: #7953
[schema] [bug] Fixed bug where ForeignKeyConstraint naming conventions using the
referred_column_0 naming convention key would not work if the foreign
key constraint were set up as a ForeignKey object rather than an
explicit ForeignKeyConstraint object. As this change makes use of
a backport of some fixes from version 2.0, an additional little-known
feature that has likely been broken for many years is also fixed which is
that a ForeignKey object may refer to a referred table by name of
the table alone without using a column name, if the name of the referent
column is the same as that of the referred column.
The referred_column_0 naming convention key was previously not tested
with the ForeignKey object, only ForeignKeyConstraint,
and this bug reveals that the feature has never worked correctly unless
ForeignKeyConstraint is used for all FK constraints. This bug
traces back to the original introduction of the feature introduced for
#3989.
References: #7958
[asyncio] [bug] Repaired handling of contextvar.ContextVar objects inside of async
adapted event handlers. Previously, values applied to a ContextVar
would not be propagated in the specific case of calling upon awaitables
inside of non-awaitable code.
References: #7937
[postgresql] [bug] Fixed bug in ARRAY datatype in combination with Enum on
PostgreSQL where using the .any() or .all() methods to render SQL
ANY() or ALL(), given members of the Python enumeration as arguments, would
produce a type adaptation failure on all drivers.
References: #6515
[postgresql] [bug] Implemented _postgresql.UUID.python_type attribute for the
PostgreSQL _postgresql.UUID type object. The attribute will return
either str or uuid.UUID based on the
_postgresql.UUID.as_uuid parameter setting. Previously, this
attribute was unimplemented. Pull request courtesy Alex Grönholm.
References: #7943
[postgresql] [bug] Fixed an issue in the psycopg2 dialect when using the
create_engine.pool_pre_ping parameter which would cause
user-configured AUTOCOMMIT isolation level to be inadvertently reset by
the "ping" handler.
References: #7930
[mysql] [bug] [regression] Fixed a regression in the untested MySQL PyODBC dialect caused by the fix
for #7518 in version 1.4.32 where an argument was being propagated
incorrectly upon first connect, leading to a TypeError.
References: #7871
[tests] [bug] For third party dialects, repaired a missing requirement for the
SimpleUpdateDeleteTest suite test which was not checking for a working
"rowcount" function on the target dialect.
References: #7919
[sql] [bug] Fixed bug in newly implemented FunctionElement.table_valued.joins_implicitly feature where the parameter would not automatically propagate
Released: April 6, 2022
[sql] [bug] Fixed bug in newly implemented
FunctionElement.table_valued.joins_implicitly feature where
the parameter would not automatically propagate from the original
TableValuedAlias object to the secondary object produced when
calling upon TableValuedAlias.render_derived() or
TableValuedAlias.alias().
Additionally repaired these issues in TableValuedAlias:
- repaired a potential memory issue which could occur when
repeatedly calling `TableValuedAlias.render_derived()` against
successive copies of the same object (for .alias(), we currently
have to still continue chaining from the previous element. not sure
if this can be improved but this is standard behavior for .alias()
elsewhere)
- repaired issue where the individual element types would be lost when
calling upon `TableValuedAlias.render_derived()` or
`TableValuedAlias.alias()`.
References: #7890
[sql] [bug] [regression] Fixed regression caused by #7823 which impacted the caching system, such that bound parameters that had been "cloned" within ORM operations, such as polymorphic loading, would in some cases not acquire their correct execution-time value leading to incorrect bind values being rendered.
References: #7903
[orm] [bug] [regression] Fixed regression caused by #7861 where invoking an Insert construct which contained ORM entities directly via _orm.Session.ex
Released: March 31, 2022
[orm] [bug] [regression] Fixed regression caused by #7861 where invoking an
Insert construct which contained ORM entities directly via
_orm.Session.execute() would fail.
References: #7878
[postgresql] [bug] Scaled back a fix made for #6581 where "executemany values" mode for psycopg2 were disabled for all "ON CONFLICT" styles of INSERT, to not apply to the "ON CONFLICT DO NOTHING" clause, which does not include any parameters and is safe for "executemany values" mode. "ON CONFLICT DO UPDATE" is still blocked from "executemany values" as there may be additional parameters in the DO UPDATE clause that cannot be batched (which is the original issue fixed by #6581).
References: #7880
[orm] [usecase] Added _orm.with_polymorphic.adapt_on_names to the _orm.with_polymorphic() function, which allows a polymorphic load (typically with co
Released: March 31, 2022
[orm] [usecase] Added _orm.with_polymorphic.adapt_on_names to the
_orm.with_polymorphic() function, which allows a polymorphic load
(typically with concrete mapping) to be stated against an alternative
selectable that will adapt to the original mapped selectable on column
names alone.
References: #7805
[orm] [usecase] Added new attributes UpdateBase.returning_column_descriptions and
UpdateBase.entity_description to allow for inspection of ORM
attributes and entities that are installed as part of an Insert,
Update, or Delete construct. The
Select.column_descriptions accessor is also now implemented for
Core-only selectables.
References: #7861
[orm] [performance] [bug] Improvements in memory usage by the ORM, removing a significant set of intermediary expression objects that are typically stored when a copy of an expression object is created. These clones have been greatly reduced, reducing the number of total expression objects stored in memory by ORM mappings by about 30%.
References: #7823
[orm] [bug] [regression] Fixed regression in "dynamic" loader strategy where the
_orm.Query.filter_by() method would not be given an appropriate
entity to filter from, in the case where a "secondary" table were present
in the relationship being queried and the mapping were against something
complex such as a "with polymorphic".
References: #7868
[orm] [bug] Fixed bug where _orm.composite() attributes would not work in
conjunction with the _orm.selectin_polymorphic() loader strategy for
joined table inheritance.
References: #7801
[orm] [bug] Fixed issue where the _orm.selectin_polymorphic() loader option would
not work with joined inheritance mappers that don't have a fixed
"polymorphic_on" column. Additionally added test support for a wider
variety of usage patterns with this construct.
References: #7799
[orm] [bug] Fixed bug in _orm.with_loader_criteria() function where loader
criteria would not be applied to a joined eager load that were invoked
within the scope of a refresh operation for the parent object.
References: #7862
[orm] [bug] Fixed issue where the _orm.Mapper would reduce a user-defined
_orm.Mapper.primary_key argument too aggressively, in the case
of mapping to a UNION where for some of the SELECT entries, two columns
are essentially equivalent, but in another, they are not, such as in a
recursive CTE. The logic here has been changed to accept a given
user-defined PK as given, where columns will be related to the mapped
selectable but no longer "reduced" as this heuristic can't accommodate for
all situations.
References: #7842
[engine] [usecase] Added new parameter Engine.dispose.close, defaulting to True.
When False, the engine disposal does not touch the connections in the old
pool at all, simply dropping the pool and replacing it. This use case is so
that when the original pool is transferred from a parent process, the
parent process may continue to use those connections.
[engine] [bug] Further clarified connection-level logging to indicate the BEGIN, ROLLBACK
and COMMIT log messages do not actually indicate a real transaction when
the AUTOCOMMIT isolation level is in use; messaging has been extended to
include the BEGIN message itself, and the messaging has also been fixed to
accommodate when the Engine level
create_engine.isolation_level parameter was used directly.
References: #7853
[sql] [usecase] Added new parameter
FunctionElement.table_valued.joins_implicitly, for the
FunctionElement.table_valued() construct. This parameter indicates
that the given table-valued function implicitly joins to the table it
refers towards, essentially disabling the "from linting" feature, i.e. the
"cartesian product" warning, from taking effect due to the presence of this
parameter. May be used for functions such as func.json_each().
References: #7845
[sql] [bug] The bindparam.literal_execute parameter now takes part
of the cache generation of a bindparam(), since it changes
the sql string generated by the compiler.
Previously the correct bind values were used, but the literal_execute
would be ignored on subsequent executions of the same query.
References: #7876
[sql] [bug] [regression] Fixed regression caused by #7760 where the new capabilities of
TextualSelect were not fully implemented within the compiler
properly, leading to issues with composed INSERT constructs such as "INSERT
FROM SELECT" and "INSERT...ON CONFLICT" when combined with CTE and textual
statements.
References: #7798
[schema] [usecase] Added support so that the Table.to_metadata.referred_schema_fn
callable passed to Table.to_metadata() may return the value
BLANK_SCHEMA to indicate that the referenced foreign key should be
reset to None. The RETAIN_SCHEMA symbol may also be returned from
this function to indicate "no change", which will behave the same as
None currently does which also indicates no change.
References: #7860
[sqlite] [bug] [reflection] Fixed bug where the name of CHECK constraints under SQLite would not be reflected if the name were created using quotes, as is the case when the name uses mixed case or special characters.
References: #5463
[mssql] [bug] [regression] Fixed regression caused by #7160 where FK reflection in conjunction with a low compatibility level setting (compatibility level 80: SQL Server 2000) causes an "Ambiguous column name" error. Patch courtesy @Lin-Your.
References: #7812
[bug] [ext] Improved the error message that's raised for the case where the
association_proxy() construct attempts to access a target attribute
at the class level, and this access fails. The particular use case here is
when proxying to a hybrid attribute that does not include a working
class-level implementation.
References: #7827
…still installed. Warning filters that promote deprecation warnings to errors are now localized to SQLAlchemy-specific warnings, or within SQLAlchemy-s…
Released: March 6, 2022
[orm] [bug] [regression] Fixed regression where the ORM exception that is to be raised when an
INSERT silently fails to actually insert a row (such as from a trigger)
would not be reached, due to a runtime exception raised ahead of time due
to the missing primary key value, thus raising an uninformative exception
rather than the correct one. For 1.4 and above, a new
_ormexc.FlushError is added for this case that's raised earlier
than the previous "null identity" exception was for 1.3, as a situation
where the number of rows actually INSERTed does not match what was expected
is a more critical situation in 1.4 as it prevents batching of multiple
objects from working correctly. This is separate from the case where a
newly fetched primary key is fetched as NULL, which continues to raise the
existing "null identity" exception.
References: #7594
[orm] [bug] Fixed issue where using a fully qualified path for the classname in
_orm.relationship() that nonetheless contained an incorrect name for
path tokens that were not the first token, would fail to raise an
informative error and would instead fail randomly at a later step.
References: #7697
[engine] [bug] Adjusted the logging for key SQLAlchemy components including
_engine.Engine, _engine.Connection to establish an
appropriate stack level parameter, so that the Python logging tokens
funcName and lineno when used in custom logging formatters will
report the correct information, which can be useful when filtering log
output; supported on Python 3.8 and above. Pull request courtesy Markus
Gerstel.
References: #7612
[sql] [bug] Fixed type-related error messages that would fail for values that were tuples, due to string formatting syntax, including compile of unsupported literal values and invalid boolean values.
References: #7721
[sql] [bug] [mysql] Fixed issues in MySQL _mysql.SET datatype as well as the generic
Enum datatype where the __repr__() method would not render
all optional parameters in the string output, impacting the use of these
types in Alembic autogenerate. Pull request for MySQL courtesy Yuki
Nishimine.
[sql] [bug] The _sqltypes.Enum datatype now emits a warning if the
_sqltypes.Enum.length argument is specified without also
specifying _sqltypes.Enum.native_enum as False, as the
parameter is otherwise silently ignored in this case, despite the fact that
the _sqltypes.Enum datatype will still render VARCHAR DDL on
backends that don't have a native ENUM datatype such as SQLite. This
behavior may change in a future release so that "length" is honored for all
non-native "enum" types regardless of the "native_enum" setting.
[sql] [bug] Fixed issue where the HasCTE.add_cte() method as called upon a
TextualSelect instance was not being accommodated by the SQL
compiler. The fix additionally adds more "SELECT"-like compiler behavior to
TextualSelect including that DML CTEs such as UPDATE and INSERT
may be accommodated.
References: #7760
[asyncio] [bug] Fixed issues where a descriptive error message was not raised for some classes of event listening with an async engine, which should instead be a sync engine instance.
[asyncio] [bug] Fixed issue where the _asyncio.AsyncSession.execute() method failed
to raise an informative exception if the
_engine.Connection.execution_options.stream_results execution
option were used, which is incompatible with a sync-style
_result.Result object when using an asyncio calling style, as the
operation to fetch more rows would need to be awaited. An exception is now
raised in this scenario in the same way one was already raised when the
_engine.Connection.execution_options.stream_results option
would be used with the _asyncio.AsyncConnection.execute() method.
Additionally, for improved stability with state-sensitive database drivers such as asyncmy, the cursor is now closed when this error condition is raised; previously with the asyncmy dialect, the connection would go into an invalid state with unconsumed server side results remaining.
References: #7667
[postgresql] [usecase] Added compiler support for the PostgreSQL NOT VALID phrase when rendering
DDL for the CheckConstraint, ForeignKeyConstraint
and ForeignKey schema constructs. Pull request courtesy
Gilbert Gilb's.
References: #7600
[mysql] [bug] [regression] Fixed regression caused by #7518 where changing the syntax "SHOW VARIABLES" to "SELECT @@" broke compatibility with MySQL versions older than 5.6, including early 5.0 releases. While these are very old MySQL versions, a change in compatibility was not planned, so version-specific logic has been restored to fall back to "SHOW VARIABLES" for MySQL server versions < 5.6.
References: #7518
[mariadb] [bug] [regression] Fixed regression in mariadbconnector dialect as of mariadb connector 1.0.10
where the DBAPI no longer pre-buffers cursor.lastrowid, leading to errors
when inserting objects with the ORM as well as causing non-availability of
the _result.CursorResult.inserted_primary_key attribute. The
dialect now fetches this value proactively for situations where it applies.
References: #7738
[sqlite] [usecase] Added support for reflecting SQLite inline unique constraints where
the column names are formatted with SQLite "escape quotes" []
or ```, which are discarded by the database when producing the
column name.
References: #7736
[sqlite] [bug] Fixed issue where SQLite unique constraint reflection would fail to detect a column-inline UNIQUE constraint where the column name had an underscore in its name.
References: #7736
[oracle] [bug] Fixed issue in Oracle dialect where using a column name that requires
quoting when written as a bound parameter, such as "_id", would not
correctly track a Python generated default value due to the bound-parameter
rewriting missing this value, causing an Oracle error to be raised.
References: #7676
[oracle] [bug] [regression] Added support to parse "DPI" error codes from cx_Oracle exception objects
such as DPI-1080 and DPI-1010, both of which now indicate a
disconnect scenario as of cx_Oracle 8.3.
References: #7748
[tests] [bug] Improvements to the test suite's integration with pytest such that the
"warnings" plugin, if manually enabled, will not interfere with the test
suite, such that third parties can enable the warnings plugin or make use
of the -W parameter and SQLAlchemy's test suite will continue to pass.
Additionally, modernized the detection of the "pytest-xdist" plugin so that
plugins can be globally disabled using PYTEST_DISABLE_PLUGIN_AUTOLOAD=1
without breaking the test suite if xdist were still installed. Warning
filters that promote deprecation warnings to errors are now localized to
SQLAlchemy-specific warnings, or within SQLAlchemy-specific sources for
general Python deprecation warnings, so that non-SQLAlchemy deprecation
warnings emitted from pytest plugins should also not impact the test suite.
References: #7599
[tests] [bug] Made corrections to the default pytest configuration regarding how test discovery is configured, to fix issue where the test suite would not configure warnings correctly and also attempt to load example suites as tests, in the specific case where the SQLAlchemy checkout were located in an absolute path that had a super-directory named "test".
References: #7045
[orm] [bug] Fixed issue in _orm.Session.bulk_save_objects() where the sorting that takes place when the preserve_order parameter is set to False would
Released: January 20, 2022
[orm] [bug] Fixed issue in _orm.Session.bulk_save_objects() where the sorting
that takes place when the preserve_order parameter is set to False
would sort partially on Mapper objects, which is rejected in Python
3.11.
References: #7591
[postgresql] [bug] [regression] Fixed regression where the change in #7148 to repair ENUM handling in PostgreSQL broke the use case of an empty ARRAY of ENUM, preventing rows that contained an empty array from being handled correctly when fetching results.
References: #7590
[mysql] [bug] [regression] Fixed regression in asyncmy dialect caused by #7567 where removal of the PyMySQL dependency broke binary columns, due to the asyncmy dialect not being properly included within CI tests.
References: #7593
[mssql] Added support for FILESTREAM when using VARBINARY(max)
in MSSQL.
References: #7243
This is a relatively new 1.4 feature that helps to suit use cases that were previously served by the deprecated Query.from_self() method.
Released: January 19, 2022
[orm] [bug] Fixed issue in joined-inheritance load of additional attributes functionality in deep multi-level inheritance where an intermediary table that contained no columns would not be included in the tables joined, instead linking those tables to their primary key identifiers. While this works fine, it nonetheless in 1.4 began producing the cartesian product compiler warning. The logic has been changed so that these intermediary tables are included regardless. While this does include additional tables in the query that are not technically necessary, this only occurs for the highly unusual case of deep 3+ level inheritance with intermediary tables that have no non primary key columns, potential performance impact is therefore expected to be negligible.
References: #7507
[orm] [bug] Fixed issue where calling upon _orm.registry.map_imperatively() more
than once for the same class would produce an unexpected error, rather than
an informative error that the target class is already mapped. This behavior
differed from that of the _orm.mapper() function which does report an
informative message already.
References: #7579
[orm] [bug] [asyncio] Added missing method _asyncio.AsyncSession.invalidate() to the
_asyncio.AsyncSession class.
References: #7524
[orm] [bug] [regression] Fixed regression which appeared in 1.4.23 which could cause loader options
to be mis-handled in some cases, in particular when using joined table
inheritance in combination with the polymorphic_load="selectin" option
as well as relationship lazy loading, leading to a TypeError.
References: #7557
[orm] [bug] [regression] Fixed ORM regression where calling the _orm.aliased() function
against an existing _orm.aliased() construct would fail to produce
correct SQL if the existing construct were against a fixed table. The fix
allows that the original _orm.aliased() construct is disregarded if
it were only against a table that's now being replaced. It also allows for
correct behavior when constructing a _orm.aliased() without a
selectable argument against a _orm.aliased() that's against a
subuquery, to create an alias of that subquery (i.e. to change its name).
The nesting behavior of _orm.aliased() remains in place for the case
where the outer _orm.aliased() object is against a subquery which in
turn refers to the inner _orm.aliased() object. This is a relatively
new 1.4 feature that helps to suit use cases that were previously served by
the deprecated Query.from_self() method.
References: #7576
[orm] [bug] Fixed issue where _sql.Select.correlate_except() method, when passed
either the None value or no arguments, would not correlate any elements
when used in an ORM context (that is, passing ORM entities as FROM
clauses), rather than causing all FROM elements to be considered as
"correlated" in the same way which occurs when using Core-only constructs.
References: #7514
[orm] [bug] [regression] Fixed regression from 1.3 where the "subqueryload" loader strategy would
fail with a stack trace if used against a query that made use of
_orm.Query.from_statement() or _sql.Select.from_statement(). As
subqueryload requires modifying the original statement, it's not compatible
with the "from_statement" use case, especially for statements made against
the _sql.text() construct. The behavior now is equivalent to that of
1.3 and previously, which is that the loader strategy silently degrades to
not be used for such statements, typically falling back to using the
lazyload strategy.
References: #7505
[sql] [bug] [postgresql] Added additional rule to the system that determines TypeEngine
implementations from Python literals to apply a second level of adjustment
to the type, so that a Python datetime with or without tzinfo can set the
timezone=True parameter on the returned DateTime object, as
well as Time. This helps with some round-trip scenarios on
type-sensitive PostgreSQL dialects such as asyncpg, psycopg3 (2.0 only).
References: #7537
[sql] [bug] Added an informative error message when a method object is passed to a SQL construct. Previously, when such a callable were passed, as is a common typographical error when dealing with method-chained SQL constructs, they were interpreted as "lambda SQL" targets to be invoked at compilation time, which would lead to silent failures. As this feature was not intended to be used with methods, method objects are now rejected.
References: #7032
[mypy] [bug] Fixed Mypy crash when running id daemon mode caused by a
missing attribute on an internal mypy Var instance.
References: #7321
[asyncio] [usecase] Added new method AdaptedConnection.run_async() to the DBAPI
connection interface used by asyncio drivers, which allows methods to be
called against the underlying "driver" connection directly within a
sync-style function where the await keyword can't be used, such as
within SQLAlchemy event handler functions. The method is analogous to the
_asyncio.AsyncConnection.run_sync() method which translates
async-style calls to sync-style. The method is useful for things like
connection-pool on-connect handlers that need to invoke awaitable methods
on the driver connection when it's first created.
References: #7580
[postgresql] [usecase] Added string rendering to the postgresql.UUID datatype, so that
stringifying a statement with "literal_binds" that uses this type will
render an appropriate string value for the PostgreSQL backend. Pull request
courtesy José Duarte.
References: #7561
[postgresql] [bug] [asyncpg] Improved support for asyncpg handling of TIME WITH TIMEZONE, which was not fully implemented.
References: #7537
[postgresql] [bug] [mssql] [reflection] Fixed reflection of covering indexes to report include_columns as part
of the dialect_options entry in the reflected index dictionary, thereby
enabling round trips from reflection->create to be complete. Included
columns continue to also be present under the include_columns key for
backwards compatibility.
References: #7382
[postgresql] [bug] Fixed handling of array of enum values which require escape characters.
References: #7418
[mysql] [change] Replace SHOW VARIABLES LIKE statement with equivalent
SELECT @@variable in MySQL and MariaDB dialect initialization.
This should avoid mutex contention caused by SHOW VARIABLES,
improving initialization performance.
References: #7518
[mysql] [bug] Removed unnecessary dependency on PyMySQL from the asyncmy dialect. Pull request courtesy long2ice.
References: #7567
[orm] [usecase] Added _orm.Session.get.execution_options parameter which was previously missing from the _orm.Session.get() method.
Released: December 22, 2021
[orm] [usecase] Added _orm.Session.get.execution_options parameter which was
previously missing from the _orm.Session.get() method.
References: #7410
[orm] [bug] Fixed issue in new "loader criteria" method
_orm.PropComparator.and_() where usage with a loader strategy like
_orm.selectinload() against a column that was a member of the .c.
collection of a subquery object, where the subquery would be dynamically
added to the FROM clause of the statement, would be subject to stale
parameter values within the subquery in the SQL statement cache, as the
process used by the loader strategy to replace the parameters at execution
time would fail to accommodate the subquery when received in this form.
References: #7489
[orm] [bug] Fixed recursion overflow which could occur within ORM statement compilation
when using either the _orm.with_loader_criteria() feature or the the
_orm.PropComparator.and_() method within a loader strategy in
conjunction with a subquery which referred to the same entity being altered
by the criteria option, or loaded by the loader strategy. A check for
coming across the same loader criteria option in a recursive fashion has
been added to accommodate for this scenario.
References: #7491
[orm] [bug] [mypy] Fixed issue where the __class_getitem__() method of the generated
declarative base class by _orm.as_declarative() would lead to
inaccessible class attributes such as __table__, for cases where a
Generic[T] style typing declaration were used in the class hierarchy.
This is in continuation from the basic addition of __class_getitem__()
in #7368. Pull request courtesy Kai Mueller.
[orm] [bug] [regression] Fixed caching-related issue where the use of a loader option of the form
lazyload(aliased(A).bs).joinedload(B.cs) would fail to result in the
joinedload being invoked for runs subsequent to the query being cached, due
to a mismatch for the options / object path applied to the objects loaded
for a query with a lead entity that used aliased().
References: #7447
[engine] [bug] Corrected the error message for the AttributeError that's raised when
attempting to write to an attribute on the _result.Row class,
which is immutable. The previous message claimed the column didn't exist
which is misleading.
References: #7432
[engine] [bug] [regression] Fixed regression in the _engine.make_url() function used to parse URL
strings where the query string parsing would go into a recursion overflow
if a Python 2 u'' string were used.
References: #7446
[mypy] [bug] Fixed mypy regression where the release of mypy 0.930 added additional
internal checks to the format of "named types", requiring that they be
fully qualified and locatable. This broke the mypy plugin for SQLAlchemy,
raising an assertion error, as there was use of symbols such as
__builtins__ and other un-locatable or unqualified names that
previously had not raised any assertions.
References: #7496
[asyncio] [usecase] Added _asyncio.async_engine_config() function to create
an async engine from a configuration dict. This otherwise
behaves the same as _sa.engine_from_config().
References: #7301
[mariadb] [bug] Corrected the error classes inspected for the "is_disconnect" check for the
mariadbconnector dialect, which was failing for disconnects that
occurred due to common MySQL/MariaDB error codes such as 2006; the DBAPI
appears to currently use the mariadb.InterfaceError exception class for
disconnect errors such as error code 2006, which has been added to the list
of classes checked.
References: #7457
[tests] [bug] [regression] Fixed a regression in the test suite where the test called
CompareAndCopyTest::test_all_present would fail on some platforms due
to additional testing artifacts being detected. Pull request courtesy Nils
Philippsen.
References: #7450
[platform] [bug] Python 3.10 has deprecated "distutils" in favor of explicit use of "setuptools" in PEP 632; SQLAlchemy's setup.py has replaced import…
Released: December 9, 2021
[platform] [bug] Python 3.10 has deprecated "distutils" in favor of explicit use of
"setuptools" in PEP 632; SQLAlchemy's setup.py has replaced imports
accordingly. However, since setuptools itself only recently added the
replacement symbols mentioned in pep-632 as of November of 2021 in version
59.0.1, setup.py still has fallback imports to distutils, as SQLAlchemy
1.4 does not have a hard setuptools versioning requirement at this time.
SQLAlchemy 2.0 is expected to use a full PEP 517 installation layout
which will indicate appropriate setuptools versioning up front.
References: #7311
[orm] [bug] [ext] Fixed issue where the internal cloning used by the
_orm.PropComparator.any() method on a _orm.relationship() in
the case where the related class also makes use of ORM polymorphic loading,
would fail if a hybrid property on the related, polymorphic class were used
within the criteria for the any() operation.
References: #7425
[orm] [bug] [mypy] Fixed issue where the _orm.as_declarative() decorator and similar
functions used to generate the declarative base class would not copy the
__class_getitem__() method from a given superclass, which prevented the
use of pep-484 generics in conjunction with the Base class. Pull
request courtesy Kai Mueller.
References: #7368
[orm] [bug] [regression] Fixed ORM regression where the new behavior of "eager loaders run on
unexpire" added in #1763 would lead to loader option errors being
raised inappropriately for the case where a single _orm.Query or
_sql.Select were used to load multiple kinds of entities, along
with loader options that apply to just one of those kinds of entity like a
_orm.joinedload(), and later the objects would be refreshed from
expiration, where the loader options would attempt to be applied to the
mismatched object type and then raise an exception. The check for this
mismatch now bypasses raising an error for this case.
References: #7318
[orm] [bug] User defined ORM options, such as those illustrated in the dogpile.caching
example which subclass _orm.UserDefinedOption, by definition are
handled on every statement execution and do not need to be considered as
part of the cache key for the statement. Caching of the base
ExecutableOption class has been modified so that it is no longer
a HasCacheKey subclass directly, so that the presence of user
defined option objects will not have the unwanted side effect of disabling
statement caching. Only ORM specific loader and criteria options, which are
all internal to SQLAlchemy, now participate within the caching system.
References: #7394
[orm] [bug] Fixed issue where mappings that made use of _orm.synonym() and
potentially other kinds of "proxy" attributes would not in all cases
successfully generate a cache key for their SQL statements, leading to
degraded performance for those statements.
References: #7394
[orm] [bug] Fixed issue where a list mapped with _orm.relationship() would go
into an endless loop if in-place added to itself, i.e. the += operator
were used, as well as if .extend() were given the same list.
References: #7389
[orm] [bug] Fixed issue where if an exception occurred when the _orm.Session
were to close the connection within the _orm.Session.commit() method,
when using a context manager for _orm.Session.begin() , it would
attempt a rollback which would not be possible as the _orm.Session
was in between where the transaction is committed and the connection is
then to be returned to the pool, raising the exception "this
sessiontransaction is in the committed state". This exception can occur
mostly in an asyncio context where CancelledError can be raised.
References: #7388
[orm] [deprecated] Deprecated an undocumented loader option syntax ".*", which appears to
be no different than passing a single asterisk, and will emit a deprecation
warning if used. This syntax may have been intended for something but there
is currently no need for it.
References: #4390
[engine] [usecase] Added support for copy() and deepcopy() to the _url.URL
class. Pull request courtesy Tom Ritchford.
References: #7400
[sql] [usecase] "Compound select" methods like _sql.Select.union(),
_sql.Select.intersect_all() etc. now accept *other as an argument
rather than other to allow for multiple additional SELECTs to be
compounded with the parent statement at once. In particular, the change as
applied to _sql.CTE.union() and _sql.CTE.union_all() now allow
for a so-called "non-linear CTE" to be created with the _sql.CTE
construct, whereas previously there was no way to have more than two CTE
sub-elements in a UNION together while still correctly calling upon the CTE
in recursive fashion. Pull request courtesy Eric Masseran.
References: #7259
[sql] [usecase] Support multiple clause elements in the _sql.Exists.where() method,
unifying the api with the one presented by a normal _sql.select()
construct.
References: #7386
[sql] [bug] [regression] Extended the TypeDecorator.cache_ok attribute and corresponding
warning message if this flag is not defined, a behavior first established
for TypeDecorator as part of #6436, to also take place
for UserDefinedType, by generalizing the flag and associated
caching logic to a new common base for these two types,
ExternalType to create UserDefinedType.cache_ok.
The change means any current UserDefinedType will now cause SQL
statement caching to no longer take place for statements which make use of
the datatype, along with a warning being emitted, unless the class defines
the UserDefinedType.cache_ok flag as True. If the datatype cannot
form a deterministic, hashable cache key derived from its arguments,
the attribute may be set to False which will continue to keep caching disabled but will suppress the
warning. In particular, custom datatypes currently used in packages such as
SQLAlchemy-utils will need to implement this flag. The issue was observed
as a result of a SQLAlchemy-utils datatype that is not currently cacheable.
References: #7319
[sql] [bug] Custom SQL elements, third party dialects, custom or third party datatypes will all generate consistent warnings when they do not clearly opt in or out of SQL statement caching, which is achieved by setting the appropriate attributes on each type of class. The warning links to documentation sections which indicate the appropriate approach for each type of object in order for caching to be enabled.
References: #7394
[sql] [bug] Fixed missing caching directives for a few lesser used classes in SQL Core
which would cause [no key] to be logged for elements which made use of
these.
References: #7394
[mypy] [bug] Fixed Mypy crash which would occur when using Mypy plugin against code
which made use of _orm.declared_attr methods for non-mapped names
like __mapper_args__, __table_args__, or other dunder names, as the
plugin would try to interpret these as mapped attributes which would then
be later mis-handled. As part of this change, the decorated function is
still converted by the plugin into a generic assignment statement (e.g.
__mapper_args__: Any) so that the argument signature can continue to be
annotated in the same way one would for any other @classmethod without
Mypy complaining about the wrong argument type for a method that isn't
explicitly @classmethod.
References: #7321
[postgresql] [bug] Fixed missing caching directives for _postgresql.hstore and
_postgresql.array constructs which would cause [no key]
to be logged for these elements.
References: #7394
[orm] [bug] Fixed bug in "relationship to aliased class" feature introduced at relationship_aliased_class where it was not possible to create a loader
Released: November 11, 2021
[orm] [bug] Fixed bug in "relationship to aliased class" feature introduced at
relationship_aliased_class where it was not possible to create a
loader strategy option targeting an attribute on the target using the
_orm.aliased() construct directly in a second loader option, such as
selectinload(A.aliased_bs).joinedload(aliased_b.cs), without explicitly
qualifying using _orm.PropComparator.of_type() on the preceding
element of the path. Additionally, targeting the non-aliased class directly
would be accepted (inappropriately), but would silently fail, such as
selectinload(A.aliased_bs).joinedload(B.cs); this now raises an error
referring to the typing mismatch.
References: #7224
[orm] [bug] All _result.Result objects will now consistently raise
_exc.ResourceClosedError if they are used after a hard close,
which includes the "hard close" that occurs after calling "single row or
value" methods like _result.Result.first() and
_result.Result.scalar(). This was already the behavior of the most
common class of result objects returned for Core statement executions, i.e.
those based on _engine.CursorResult, so this behavior is not new.
However, the change has been extended to properly accommodate for the ORM
"filtering" result objects returned when using 2.0 style ORM queries,
which would previously behave in "soft closed" style of returning empty
results, or wouldn't actually "soft close" at all and would continue
yielding from the underlying cursor.
As part of this change, also added _result.Result.close() to the base
_result.Result class and implemented it for the filtered result
implementations that are used by the ORM, so that it is possible to call
the _engine.CursorResult.close() method on the underlying
_engine.CursorResult when the the yield_per execution option
is in use to close a server side cursor before remaining ORM results have
been fetched. This was again already available for Core result sets but the
change makes it available for 2.0 style ORM results as well.
References: #7274
[orm] [bug] [regression] Fixed 1.4 regression where _orm.Query.filter_by() would not function
correctly on a _orm.Query that was produced from
_orm.Query.union(), _orm.Query.from_self() or similar.
References: #7239
[orm] [bug] Fixed issue where deferred polymorphic loading of attributes from a
joined-table inheritance subclass would fail to populate the attribute
correctly if the _orm.load_only() option were used to originally
exclude that attribute, in the case where the load_only were descending
from a relationship loader option. The fix allows that other valid options
such as defer(..., raiseload=True) etc. still function as expected.
References: #7304
[orm] [bug] [regression] Fixed 1.4 regression where _orm.Query.filter_by() would not function
correctly when _orm.Query.join() were joined to an entity which made
use of _orm.PropComparator.of_type() to specify an aliased version of
the target entity. The issue also applies to future style ORM queries
constructed with _sql.select().
References: #7244
[engine] [bug] Fixed issue in future _future.Connection object where the
_future.Connection.execute() method would not accept a non-dict
mapping object, such as SQLAlchemy's own RowMapping or other
abc.collections.Mapping object as a parameter dictionary.
References: #7291
[engine] [bug] [regression] Fixed regression where the _engine.CursorResult.fetchmany() method
would fail to autoclose a server-side cursor (i.e. when stream_results
or yield_per is in use, either Core or ORM oriented results) when the
results were fully exhausted.
References: #7274
[engine] [bug] Fixed issue in future _future.Engine where calling upon
_future.Engine.begin() and entering the context manager would not
close the connection if the actual BEGIN operation failed for some reason,
such as an event handler raising an exception; this use case failed to be
tested for the future version of the engine. Note that the "future" context
managers which handle begin() blocks in Core and ORM don't actually run
the "BEGIN" operation until the context managers are actually entered. This
is different from the legacy version which runs the "BEGIN" operation up
front.
References: #7272
[sql] [usecase] Added TupleType to the top level sqlalchemy import namespace.
[sql] [bug] [regression] Fixed regression where the row objects returned for ORM queries, which are
now the normal _sql.Row objects, would not be interpreted by the
_sql.ColumnOperators.in_() operator as tuple values to be broken out
into individual bound parameters, and would instead pass them as single
values to the driver leading to failures. The change to the "expanding IN"
system now accommodates for the expression already being of type
TupleType and treats values accordingly if so. In the uncommon
case of using "tuple-in" with an untyped statement such as a textual
statement with no typing information, a tuple value is detected for values
that implement collections.abc.Sequence, but that are not str or
bytes, as always when testing for Sequence.
References: #7292
[sql] [bug] Fixed issue where using the feature of using a string label for ordering or
grouping described at tutorial_order_by_label would fail to function
correctly if used on a CTE construct, when the CTE were embedded
inside of an enclosing _sql.Select statement that itself was set
up as a scalar subquery.
References: #7269
[sql] [bug] [regression] Fixed regression where the _sql.text() construct would no longer be
accepted as a target case in the "whens" list within a _sql.case()
construct. The regression appears related to an attempt to guard against
some forms of literal values that were considered to be ambiguous when
passed here; however, there's no reason the target cases shouldn't be
interpreted as open-ended SQL expressions just like anywhere else, and a
literal string or tuple will be converted to a bound parameter as would be
the case elsewhere.
References: #7287
[schema] [bug] Fixed issue in Table where the
Table.implicit_returning parameter would not be
accommodated correctly when passed along with
Table.extend_existing to augment an existing
Table.
References: #7295
[postgresql] [usecase] [asyncpg] Added overridable methods PGDialect_asyncpg.setup_asyncpg_json_codec
and PGDialect_asyncpg.setup_asyncpg_jsonb_codec codec, which handle the
required task of registering JSON/JSONB codecs for these datatypes when
using asyncpg. The change is that methods are broken out as individual,
overridable methods to support third party dialects that need to alter or
disable how these particular codecs are set up.
References: #7284
[postgresql] [bug] [asyncpg] Changed the asyncpg dialect to bind the Float type to the "float"
PostgreSQL type instead of "numeric" so that the value float(inf) can
be accommodated. Added test suite support for persistence of the "inf"
value.
References: #7283
[postgresql] [pg8000] Improve array handling when using PostgreSQL with the pg8000 dialect.
References: #7167
[mysql] [bug] [mariadb] Reorganized the list of reserved words into two separate lists, one for MySQL and one for MariaDB, so that these diverging sets of words can be managed more accurately; adjusted the MySQL/MariaDB dialect to switch among these lists based on either explicitly configured or server-version-detected "MySQL" or "MariaDB" backend. Added all current reserved words through MySQL 8 and current MariaDB versions including recently added keywords like "lead" . Pull request courtesy Kevin Kirsche.
References: #7167
[mysql] [bug] Fixed issue in MySQL _mysql.Insert.on_duplicate_key_update() which
would render the wrong column name when an expression were used in a VALUES
expression. Pull request courtesy Cristian Sabaila.
References: #7281
[mssql] [bug] Adjusted the compiler's generation of "post compile" symbols including
those used for "expanding IN" as well as for the "schema translate map" to
not be based directly on plain bracketed strings with underscores, as this
conflicts directly with SQL Server's quoting format of also using brackets,
which produces false matches when the compiler replaces "post compile" and
"schema translate" symbols. The issue created easy to reproduce examples
both with the Inspector.get_schema_names() method when used in
conjunction with the
_engine.Connection.execution_options.schema_translate_map
feature, as well in the unlikely case that a symbol overlapping with the
internal name "POSTCOMPILE" would be used with a feature like "expanding
in".
References: #7300
One column per quarter.