The Database Toolkit for Python
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
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
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
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
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
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
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.
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
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
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