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.9
Last release today
16 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
344 releases · first in 2006
Drag across the ruler to compare, or tap a release to open it.
[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
Your coding agent can read these notes before it upgrades. Set up the MCP server →
[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
[mysql] [bug] [mariadb] Fixes to accommodate for the MariaDB 10.6 series, including backwards incompatible changes in both the mariadb-connector Pytho…
Released: October 19, 2021
[orm] [bug] Improved the exception message generated when configuring a mapping with
joined table inheritance where the two tables either have no foreign key
relationships set up, or where they have multiple foreign key relationships
set up. The message is now ORM specific and includes context that the
_orm.Mapper.inherit_condition parameter may be needed
particularly for the ambiguous foreign keys case.
[orm] [bug] Fixed issue with _orm.with_loader_criteria() feature where ON
criteria would not be added to a JOIN for a query of the form
select(A).join(B), stating a target while making use of an implicit
ON clause.
References: #7189
[orm] [bug] Fixed bug where the ORM "plugin", necessary for features such as
_orm.with_loader_criteria() to work correctly, would not be applied
to a _sql.select() which queried from an ORM column expression if it
made use of the _sql.ColumnElement.label() modifier.
References: #7205
[orm] [bug] Add missing methods added in #6991 to
_scoping.scoped_session and _asyncio.async_scoped_session().
References: #7103
[orm] [bug] An extra layer of warning messages has been added to the functionality
of _orm.Query.join() and the ORM version of
_sql.Select.join(), where a few places where "automatic aliasing"
continues to occur will now be called out as a pattern to avoid, mostly
specific to the area of joined table inheritance where classes that share
common base tables are being joined together without using explicit aliases.
One case emits a legacy warning for a pattern that's not recommended,
the other case is fully deprecated.
The automatic aliasing within ORM join() which occurs for overlapping
mapped tables does not work consistently with all APIs such as
_orm.contains_eager(), and rather than continue to try to make
these use cases work everywhere, replacing with a more user-explicit
pattern is clearer, less prone to bugs and simplifies SQLAlchemy's
internals further.
The warnings include links to the errors.rst page where each pattern is demonstrated along with the recommended pattern to fix.
[orm] [bug] Fixed bug where iterating a Result from a _orm.Session
after that _orm.Session were closed would partially attach objects
to that session in an essentially invalid state. It now raises an exception
with a link to new documentation if an un-buffered result is iterated
from a _orm.Session that was closed or otherwise had the
_orm.Session.expunge_all() method called after that Result
was generated. The prebuffer_rows execution option, as is used
automatically by the asyncio extension for client-side result sets, may be
used to produce a Result where the ORM objects are prebuffered,
and in this case iterating the result will produce a series of detached
objects.
References: #7128
[orm] [bug] Related to #7153, fixed an issue where result column lookups would fail for "adapted" SELECT statements that selected for "constant" value expressions most typically the NULL expression, as would occur in such places as joined eager loading in conjunction with limit/offset. This was overall a regression due to issue #6259 which removed all "adaption" for constants like NULL, "true", and "false" when rewriting expressions in a SQL statement, but this broke the case where the same adaption logic were used to resolve the constant to a labeled expression for the purposes of result set targeting.
References: #7154
[orm] [bug] [regression] Fixed regression where ORM loaded objects could not be pickled in cases
where loader options making use of "*" were used in certain
combinations, such as combining the _orm.joinedload() loader strategy
with raiseload('*') of sub-elements.
References: #7134
[orm] [bug] [regression] Fixed regression where the use of a _hybrid.hybrid_property
attribute or a mapped _orm.composite() attribute as a key passed to
the _dml.Update.values() method for an ORM-enabled
_dml.Update statement, as well as when using it via the legacy
_orm.Query.update() method, would be processed for incoming
ORM/hybrid/composite values within the compilation stage of the UPDATE
statement, which meant that in those cases where caching occurred,
subsequent invocations of the same statement would no longer receive the
correct values. This would include not only hybrids that use the
_hybrid.hybrid_property.update_expression() method, but any use of a
plain hybrid attribute as well. For composites, the issue instead caused a
non-repeatable cache key to be generated, which would break caching and
could fill up the statement cache with repeated statements.
The _dml.Update construct now handles the processing of key/value
pairs passed to _dml.Update.values() and
_dml.Update.ordered_values() up front when the construct is first
generated, before the cache key has been generated so that the key/value
pairs are processed each time, and so that the cache key is generated
against the individual column/value pairs that will ultimately be
used in the statement.
References: #7209
[orm] Passing a Query object to _orm.Session.execute() is not
the intended use of this object, and will now raise a deprecation warning.
References: #6284
[examples] [bug] Repaired the examples in examples/versioned_rows to use SQLAlchemy 1.4 APIs
correctly; these examples had been missed when API changes like removing
"passive" from _orm.Session.is_modified() were made as well as the
_ormevents.SessionEvents.do_orm_execute() event hook were added.
References: #7169
[engine] [bug] Fixed issue where the deprecation warning for the URL constructor
which indicates that the URL.create() method should be used would
not emit if a full positional argument list of seven arguments were passed;
additionally, validation of URL arguments will now occur if the constructor
is called in this way, which was being skipped previously.
References: #7130
[engine] [bug] [postgresql] The _reflection.Inspector.reflect_table() method now supports
reflecting tables that do not have user defined columns. This allows
_schema.MetaData.reflect() to properly complete reflection on
databases that contain such tables. Currently, only PostgreSQL is known to
support such a construct among the common database backends.
References: #3247
[engine] [bug] Implemented proper __reduce__() methods for all SQLAlchemy exception
objects to ensure they all support clean round trips when pickling, as
exception objects are often serialized for the purposes of various
debugging tools.
References: #7077
[sql] [bug] Fixed issue where SQL queries using the
_functions.FunctionElement.within_group() construct could not be
pickled, typically when using the sqlalchemy.ext.serializer extension
but also for general generic pickling.
References: #6520
[sql] [bug] Repaired issue in new _sql.HasCTE.cte.nesting parameter
introduced with #4123 where a recursive _sql.CTE using
_sql.HasCTE.cte.recursive in typical conjunction with UNION
would not compile correctly. Additionally makes some adjustments so that
the _sql.CTE construct creates a correct cache key.
Pull request courtesy Eric Masseran.
References: #4123
[sql] [bug] Account for the _sql.table.schema parameter passed to
the _sql.table() construct, such that it is taken into account
when accessing the _sql.TableClause.fullname attribute.
References: #7061
[sql] [bug] Fixed an inconsistency in the _sql.ColumnOperators.any_() /
_sql.ColumnOperators.all_() functions / methods where the special
behavior these functions have of "flipping" the expression such that the
"ANY" / "ALL" expression is always on the right side would not function if
the comparison were against the None value, that is, "column.any_() ==
None" should produce the same SQL expression as "null() == column.any_()".
Added more docs to clarify this as well, plus mentions that any_() / all_()
generally supersede the ARRAY version "any()" / "all()".
References: #7140
[sql] [bug] [regression] Fixed issue where "expanding IN" would fail to function correctly with
datatypes that use the _types.TypeEngine.bind_expression() method,
where the method would need to be applied to each element of the
IN expression rather than the overall IN expression itself.
References: #7177
[sql] [bug] Adjusted the "column disambiguation" logic that's new in 1.4, where the
same expression repeated gets an "extra anonymous" label, so that the logic
more aggressively deduplicates those labels when the repeated element
is the same Python expression object each time, as occurs in cases like
when using "singleton" values like _sql.null(). This is based on
the observation that at least some databases (e.g. MySQL, but not SQLite)
will raise an error if the same label is repeated inside of a subquery.
References: #7153
[mypy] [bug] Fixed issue in mypy plugin to improve upon some issues detecting Enum()
SQL types containing custom Python enumeration classes. Pull request
courtesy Hiroshi Ogawa.
References: #6435
[postgresql] [bug] Added a "disconnect" condition for the "SSL SYSCALL error: Bad address" error message as reported by psycopg2. Pull request courtesy Zeke Brechtel.
References: #5387
[postgresql] [bug] [regression] Fixed issue where IN expressions against a series of array elements, as can
be done with PostgreSQL, would fail to function correctly due to multiple
issues within the "expanding IN" feature of SQLAlchemy Core that was
standardized in version 1.4. The psycopg2 dialect now makes use of the
_types.TypeEngine.bind_expression() method with _types.ARRAY
to portably apply the correct casts to elements. The asyncpg dialect was
not affected by this issue as it applies bind-level casts at the driver
level rather than at the compiler level.
References: #7177
[mysql] [bug] [mariadb] Fixes to accommodate for the MariaDB 10.6 series, including backwards incompatible changes in both the mariadb-connector Python driver (supported on SQLAlchemy 1.4 only) as well as the native 10.6 client libraries that are used automatically by the mysqlclient DBAPI (applies to both 1.3 and 1.4). The "utf8mb3" encoding symbol is now reported by these client libraries when the encoding is stated as "utf8", leading to lookup and encoding errors within the MySQL dialect that does not expect this symbol. Updates to both the MySQL base library to accommodate for this utf8mb3 symbol being reported as well as to the test suite. Thanks to Georg Richter for support.
This change is also backported to: 1.3.25
[mysql] [bug] Fixed issue in MySQL _mysql.match() construct where passing a clause
expression such as _sql.bindparam() or other SQL expression for the
"against" parameter would fail. Pull request courtesy Anton Kovalevich.
References: #7144
[mysql] [bug] Fixed installation issue where the sqlalchemy.dialects.mysql module
would not be importable if "greenlet" were not installed.
References: #7204
[mssql] [usecase] Added reflection support for SQL Server foreign key options, including "ON UPDATE" and "ON DELETE" values of "CASCADE" and "SET NULL".
[mssql] [bug] Fixed issue with Inspector.get_foreign_keys() where foreign
keys were omitted if they were established against a unique
index instead of a unique constraint.
References: #7160
[mssql] [bug] Fixed issue with Inspector.has_table() where it would return False
if a local temp table with the same name from a different session happened
to be returned first when querying tempdb. This is a continuation of
#6910 which accounted for the temp table existing only in the
alternate session and not the current one.
References: #7168
[mssql] [bug] [regression] Fixed bug in SQL Server _mssql.DATETIMEOFFSET datatype where the
ODBC implementation would not generate the correct DDL, for cases where the
type were converted using the dialect.type_descriptor() method, the
usage of which is illustrated in some documented examples for
TypeDecorator, though not necessary for most datatypes.
Regression was introduced by #6366. As part of this change, the
full list of SQL Server date types have been amended to return a "dialect
impl" that generates the same DDL name as the supertype.
References: #7129
[platform] [bug] [regression] Fixed regression due to #7024 where the reorganization of the "platform machine" names used by the greenlet dependency m
Released: September 22, 2021
…no real use in modern SQLAlchemy, and will be deprecated in a separate change.
Released: September 22, 2021
[platform] [bug] Further adjusted the "greenlet" package specifier in setup.cfg to use a
long chain of "or" expressions, so that the comparison of
platform_machine to a specific identifier matches only the complete
string.
References: #7024
[orm] [usecase] Added loader options to _orm.Session.merge() and
_asyncio.AsyncSession.merge() via a new
_orm.Session.merge.options parameter, which will apply the
given loader options to the get() used internally by merge, allowing
eager loading of relationships etc. to be applied when the merge process
loads a new object. Pull request courtesy Daniel Stone.
References: #6955
[orm] [bug] [regression] Fixed ORM issue where column expressions passed to query() or
ORM-enabled select() would be deduplicated on the identity of the
object, such as a phrase like select(A.id, null(), null()) would
produce only one "NULL" expression, which previously was not the case in
1.3. However, the change also allows for ORM expressions to render as given
as well, such as select(A.data, A.data) will produce a result row with
two columns.
References: #6979
[orm] [bug] [regression] Fixed issue in recently repaired Query.with_entities() method where the
flag that determines automatic uniquing for legacy ORM Query objects
only would be set to True inappropriately in cases where the
with_entities() call would be setting the Query to return
column-only rows, which are not uniqued.
References: #6924
[engine] [usecase] [asyncio] Improve the interface used by adapted drivers, like the asyncio ones, to access the actual connection object returned by the driver.
The _ConnectionFairy object has two new attributes:
- `_ConnectionFairy.dbapi_connection` always represents a DBAPI
compatible object. For pep-249 drivers, this is the DBAPI connection as
it always has been, previously accessed under the `.connection`
attribute. For asyncio drivers that SQLAlchemy adapts into a pep-249
interface, the returned object will normally be a SQLAlchemy adaption
object called `_engine.AdaptedConnection`.
- `_ConnectionFairy.driver_connection` always represents the actual
connection object maintained by the third party pep-249 DBAPI or async
driver in use. For standard pep-249 DBAPIs, this will always be the same
object as that of the `dbapi_connection`. For an asyncio driver, it
will be the underlying asyncio-only connection object.
The .connection attribute remains available and is now a legacy alias
of .dbapi_connection.
References: #6832
[engine] [usecase] [orm] Added new methods _orm.Session.scalars(),
_engine.Connection.scalars(), _asyncio.AsyncSession.scalars()
and _asyncio.AsyncSession.stream_scalars(), which provide a short cut
to the use case of receiving a row-oriented _result.Result object
and converting it to a _result.ScalarResult object via the
_engine.Result.scalars() method, to return a list of values rather
than a list of rows. The new methods are analogous to the long existing
_orm.Session.scalar() and _engine.Connection.scalar() methods
used to return a single value from the first row only. Pull request
courtesy Miguel Grinberg.
References: #6990
[engine] [bug] [regression] Fixed issue where the ability of the
_events.ConnectionEvents.before_execute() method to alter the SQL
statement object passed, returning the new object to be invoked, was
inadvertently removed. This behavior has been restored.
References: #6913
[engine] [bug] Ensure that str() is called on the an
_url.URL.create.password argument, allowing usage of objects
that implement the __str__() method as password attributes. Also
clarified that one such object is not appropriate to dynamically change the
password for each database connection; the approaches at
engines_dynamic_tokens should be used instead.
References: #6958
[engine] [bug] Fixed issue in _engine.URL where validation of "drivername" would
not appropriately respond to the None value where a string were
expected.
References: #6983
[engine] [bug] [postgresql] Fixed issue where an engine that had
_sa.create_engine.implicit_returning set to False would fail to
function when PostgreSQL's "fast insertmany" feature were used in
conjunction with a Sequence, as well as if any kind of "executemany"
with "return_defaults()" were used in conjunction with a Sequence. Note
that PostgreSQL "fast insertmany" uses "RETURNING" by definition, when the
SQL statement is passed to the driver; overall, the
_sa.create_engine.implicit_returning flag is legacy and has no
real use in modern SQLAlchemy, and will be deprecated in a separate change.
References: #6963
[sql] [usecase] Added new parameter _sql.HasCTE.cte.nesting to the
_sql.CTE constructor and _sql.HasCTE.cte() method, which
flags the CTE as one which should remain nested within an enclosing CTE,
rather than being moved to the top level of the outermost SELECT. While in
the vast majority of cases there is no difference in SQL functionality,
users have identified various edge-cases where true nesting of CTE
constructs is desirable. Much thanks to Eric Masseran for lots of work on
this intricate feature.
References: #4123
[sql] [bug] Implemented missing methods in _functions.FunctionElement which,
while unused, would lead pylint to report them as unimplemented abstract
methods.
References: #7052
[sql] [bug] Fixed a two issues where combinations of select() and join() when
adapted to form a copy of the element would not completely copy the state
of all column objects associated with subqueries. A key problem this caused
is that usage of the _sql.ClauseElement.params() method (which should
probably be moved into a legacy category as it is inefficient and error
prone) would leave copies of the old _sql.BindParameter objects
around, leading to issues in correctly setting the parameters at execution
time.
References: #7055
[sql] [bug] Fixed issue related to new _sql.HasCTE.add_cte() feature where
pairing two "INSERT..FROM SELECT" statements simultaneously would lose
track of the two independent SELECT statements, leading to the wrong SQL.
References: #7036
[sql] [bug] Fixed issue where using ORM column expressions as keys in the list of
dictionaries passed to _sql.Insert.values() for "multi-valued insert"
would not be processed correctly into the correct column expressions.
References: #7060
[mypy] [bug] Fixed issue where mypy plugin would crash when interpreting a
query_expression() construct.
References: #6950
[mypy] [bug] Fixed issue in mypy plugin where columns on a mixin would not be correctly
interpreted if the mapped class relied upon a __tablename__ routine
that came from a superclass.
References: #6937
[asyncio] [feature] [mysql] Added initial support for the asyncmy asyncio database driver for MySQL
and MariaDB. This driver is very new, however appears to be the only
current alternative to the aiomysql driver which currently appears to
be unmaintained and is not working with current Python versions. Much
thanks to long2ice for the pull request for this dialect.
References: #6993
[asyncio] [usecase] The _asyncio.AsyncSession now supports overriding which
_orm.Session it uses as the proxied instance. A custom Session
class can be passed using the AsyncSession.sync_session_class
parameter or by subclassing the AsyncSession and specifying a custom
AsyncSession.sync_session_class.
References: #6746
[asyncio] [bug] Fixed a bug in _asyncio.AsyncSession.execute() and
_asyncio.AsyncSession.stream() that required execution_options
to be an instance of immutabledict when defined. It now
correctly accepts any mapping.
References: #6943
[asyncio] [bug] Added missing **kw arguments to the
_asyncio.AsyncSession.connection() method.
[asyncio] [bug] Deprecate usage of _orm.scoped_session with asyncio drivers. When
using Asyncio the _asyncio.async_scoped_session should be used
instead.
References: #6746
[postgresql] [bug] Qualify version() call to avoid shadowing issues if a different
search path is configured by the user.
References: #6912
[postgresql] [bug] The _postgresql.ENUM datatype is PostgreSQL-native and therefore
should not be used with the native_enum=False flag. This flag is now
ignored if passed to the _postgresql.ENUM datatype and a warning
is emitted; previously the flag would cause the type object to fail to
function correctly.
References: #6106
[mssql] [bug] [reflection] Fixed an issue where _reflection.has_table() returned
True for local temporary tables that actually belonged to a
different SQL Server session (connection). An extra check is now
performed to ensure that the temp table detected is in fact owned
by the current session.
References: #6910
[oracle] [bug] [performance] Added a CAST(VARCHAR2(128)) to the "table name", "owner", and other DDL-name parameters as used in reflection queries against Oracle system views such as ALL_TABLES, ALL_TAB_CONSTRAINTS, etc to better enable indexing to take place against these columns, as they previously would be implicitly handled as NVARCHAR2 due to Python's use of Unicode for strings; these columns are documented in all Oracle versions as being VARCHAR2 with lengths varying from 30 to 128 characters depending on server version. Additionally, test support has been enabled for Unicode-named DDL structures against Oracle databases.
References: #4486
[orm] [bug] Fixed issue where the unit of work would internally use a 2.0-deprecated SQL expression form, emitting a deprecation warning when SQLALCHE…
Released: August 18, 2021
[general] [bug] The setup requirements have been modified such greenlet is a default
requirement only for those platforms that are well known for greenlet
to be installable and for which there is already a pre-built binary on
pypi; the current list is x86_64 aarch64 ppc64le amd64 win32. For other
platforms, greenlet will not install by default, which should enable
installation and test suite running of SQLAlchemy 1.4 on platforms that
don't support greenlet, excluding any asyncio features. In order to
install with the greenlet dependency included on a machine architecture
outside of the above list, the [asyncio] extra may be included by
running pip install sqlalchemy[asyncio] which will then attempt to
install greenlet.
Additionally, the test suite has been repaired so that tests can complete fully when greenlet is not installed, with appropriate skips for asyncio-related tests.
References: #6136
[orm] [usecase] Added new attribute _sql.Select.columns_clause_froms that will
retrieve the FROM list implied by the columns clause of the
_sql.Select statement. This differs from the old
_sql.Select.froms collection in that it does not perform any ORM
compilation steps, which necessarily deannotate the FROM elements and do
things like compute joinedloads etc., which makes it not an appropriate
candidate for the _sql.Select.select_from() method. Additionally adds
a new parameter
_sql.Select.with_only_columns.maintain_column_froms that
transfers this collection to _sql.Select.select_from() before
replacing the columns collection.
In addition, the _sql.Select.froms is renamed to
_sql.Select.get_final_froms(), to stress that this collection is not
a simple accessor and is instead calculated given the full state of the
object, which can be an expensive call when used in an ORM context.
Additionally fixes a regression involving the
_orm.with_only_columns() function to support applying criteria to
column elements that were replaced with either
_sql.Select.with_only_columns() or _orm.Query.with_entities() ,
which had broken as part of #6503 released in 1.4.19.
References: #6808
[orm] [bug] [sql] Fixed issue where a bound parameter object that was "cloned" would cause a name conflict in the compiler, if more than one clone of this parameter were used at the same time in a single statement. This could occur in particular with things like ORM single table inheritance queries that indicated the same "discriminator" value multiple times in one query.
References: #6824
[orm] [bug] Fixed issue in loader strategies where the use of the
_orm.Load.options() method, particularly when nesting multiple calls,
would generate an overly long and more importantly non-deterministic cache
key, leading to very large cache keys which were also not allowing
efficient cache usage, both in terms of total memory used as well as number
of entries used in the cache itself.
References: #6869
[orm] [bug] Revised the means by which the
_orm.ORMExecuteState.user_defined_options accessor receives
_orm.UserDefinedOption and related option objects from the
context, with particular emphasis on the "selectinload" on the loader
strategy where this previously was not working; other strategies did not
have this problem. The objects that are associated with the current query
being executed, and not that of a query being cached, are now propagated
unconditionally. This essentially separates them out from the "loader
strategy" options which are explicitly associated with the compiled state
of a query and need to be used in relation to the cached query.
The effect of this fix is that a user-defined option, such as those used by the dogpile.caching example as well as for other recipes such as defining a "shard id" for the horizontal sharing extension, will be correctly propagated to eager and lazy loaders regardless of whether a cached query was ultimately invoked.
References: #6887
[orm] [bug] Fixed issue where the unit of work would internally use a 2.0-deprecated SQL expression form, emitting a deprecation warning when SQLALCHEMY_WARN_20 were enabled.
References: #6812
[orm] [bug] Fixed issue in _orm.selectinload() where use of the new
_orm.PropComparator.and_() feature within options that were nested
more than one level deep would fail to update bound parameter values that
were in the nested criteria, as a side effect of SQL statement caching.
References: #6881
[orm] [bug] Adjusted ORM loader internals to no longer use the "lambda caching" system that was added in 1.4, as well as repaired one location that was still using the previous "baked query" system for a query. The lambda caching system remains an effective way to reduce the overhead of building up queries that have relatively fixed usage patterns. In the case of loader strategies, the queries used are responsible for moving through lots of arbitrary options and criteria, which is both generated and sometimes consumed by end-user code, that make the lambda cache concept not any more efficient than not using it, at the cost of more complexity. In particular the problems noted by #6881 and #6887 are made are made considerably less complicated by removing this feature internally.
[orm] [bug] Fixed an issue where the _orm.Bundle construct would not create
proper cache keys, leading to inefficient use of the query cache. This
had some impact on the "selectinload" strategy and was identified as
part of #6889.
References: #6889
[sql] [bug] Fix issue in _sql.CTE where new _sql.HasCTE.add_cte() method
added in version 1.4.21 / #6752 failed to function correctly for
"compound select" structures such as _sql.union(),
_sql.union_all(), _sql.except(), etc. Pull request courtesy
Eric Masseran.
References: #6752
[sql] [bug] Fixed an issue in the CacheKey.to_offline_string() method used by the
dogpile.caching example where attempting to create a proper cache key from
the special "lambda" query generated by the lazy loader would fail to
include the parameter values, leading to an incorrect cache key.
References: #6858
[sql] [bug] Adjusted the "from linter" warning feature to accommodate for a chain of joins more than one level deep where the ON clauses don't explicitly match up the targets, such as an expression such as "ON TRUE". This mode of use is intended to cancel the cartesian product warning simply by the fact that there's a JOIN from "a to b", which was not working for the case where the chain of joins had more than one element.
References: #6886
[sql] [bug] Fixed issue in lambda caching system where an element of a query that produces no cache key, like a custom option or clause element, would still populate the expression in the "lambda cache" inappropriately.
[schema] [enum] Unify behaviour _schema.Enum in native and non-native
implementations regarding the accepted values for an enum with
aliased elements.
When _schema.Enum.omit_aliases is False all values,
alias included, are accepted as valid values.
When _schema.Enum.omit_aliases is True only non aliased values
are accepted as valid values.
References: #6146
[mypy] [usecase] Added support for SQLAlchemy classes to be defined in user code using
"generic class" syntax as defined by sqlalchemy2-stubs, e.g.
Column[String], without the need for qualifying these constructs within
a TYPE_CHECKING block by implementing the Python special method
__class_getitem__(), which allows this syntax to pass without error at
runtime.
[postgresql] [bug] Added the "is_comparison" flag to the PostgreSQL "overlaps", "contained_by", "contains" operators, so that they work in relevant ORM contexts as well as in conjunction with the "from linter" feature.
References: #6886
[mssql] [bug] [sql] Fixed issue where the literal_binds compiler flag, as used externally
to render bound parameters inline, would fail to work when used with a
certain class of parameters known as "literal_execute", which covers things
like LIMIT and OFFSET values for dialects where the drivers don't allow a
bound parameter, such as SQL Server's "TOP" clause. The issue locally
seemed to affect only the MSSQL dialect.
References: #6863
[bug] [ext] Fixed issue where the horizontal sharding extension would not correctly
accommodate for a plain textual SQL statement passed to
_orm.Session.execute().
References: #6816
…and not as a keyword argument would emit a 2.0 deprecation warning, referring to the deprecation of passing a list positionally. The dictionary format…
Released: July 21, 2021
[orm] [bug] Fixed issue in new _schema.Table.table_valued() method where the
resulting _sql.TableValuedColumn construct would not respond
correctly to alias adaptation as is used throughout the ORM, such as for
eager loading, polymorphic loading, etc.
References: #6775
[orm] [bug] Fixed issue where usage of the _result.Result.unique() method with an
ORM result that included column expressions with unhashable types, such as
JSON or ARRAY using non-tuples would silently fall back to using
the id() function, rather than raising an error. This now raises an
error when the _result.Result.unique() method is used in a 2.0 style
ORM query. Additionally, hashability is assumed to be True for result
values of unknown type, such as often happens when using SQL functions of
unknown return type; if values are truly not hashable then the hash()
itself will raise.
For legacy ORM queries, since the legacy _orm.Query object
uniquifies in all cases, the old rules remain in place, which is to use
id() for result values of unknown type as this legacy uniquing is
mostly for the purpose of uniquing ORM entities and not column values.
References: #6769
[orm] [bug] Fixed an issue where clearing of mappers during things like test suite
teardowns could cause a "dictionary changed size" warning during garbage
collection, due to iteration of a weak-referencing dictionary. A list()
has been applied to prevent concurrent GC from affecting this operation.
References: #6771
[orm] [bug] [regression] Fixed critical caching issue where the ORM's persistence feature using INSERT..RETURNING would cache an incorrect query when mixing the "bulk save" and standard "flush" forms of INSERT.
References: #6793
[engine] [bug] Added some guards against KeyError in the event system to accommodate
the case that the interpreter is shutting down at the same time
_engine.Engine.dispose() is being called, which would cause stack
trace warnings.
References: #6740
[sql] [bug] Fixed issue where use of the _sql.case.whens parameter passing
a dictionary positionally and not as a keyword argument would emit a 2.0
deprecation warning, referring to the deprecation of passing a list
positionally. The dictionary format of "whens", passed positionally, is
still supported and was accidentally marked as deprecated.
References: #6786
[sql] [bug] Fixed issue where type-specific bound parameter handlers would not be
called upon in the case of using the _sql.Insert.values() method with
the Python None value; in particular, this would be noticed when using
the _types.JSON datatype as well as related PostgreSQL specific
types such as _postgresql.JSONB which would fail to encode the
Python None value into JSON null, however the issue was generalized to
any bound parameter handler in conjunction with this specific method of
_sql.Insert.
References: #6770
[orm] [usecase] Modified the approach used for history tracking of scalar object relationships that are not many-to-one, i.e. one-to-one relationships
Released: July 14, 2021
[orm] [usecase] Modified the approach used for history tracking of scalar object relationships that are not many-to-one, i.e. one-to-one relationships that would otherwise be one-to-many. When replacing a one-to-one value, the "old" value that would be replaced is no longer loaded immediately, and is instead handled during the flush process. This eliminates an historically troublesome lazy load that otherwise often occurs when assigning to a one-to-one attribute, and is particularly troublesome when using "lazy='raise'" as well as asyncio use cases.
This change does cause a behavioral change within the
_orm.AttributeEvents.set() event, which is nonetheless currently
documented, which is that the event applied to such a one-to-one attribute
will no longer receive the "old" parameter if it is unloaded and the
_orm.relationship.active_history flag is not set. As is
documented in _orm.AttributeEvents.set(), if the event handler needs
to receive the "old" value when the event fires off, the active_history
flag must be established either with the event listener or with the
relationship. This is already the behavior with other kinds of attributes
such as many-to-one and column value references.
The change additionally will defer updating a backref on the "old" value
in the less common case that the "old" value is locally present in the
session, but isn't loaded on the relationship in question, until the
next flush occurs. If this causes an issue, again the normal
_orm.relationship.active_history flag can be set to True
on the relationship.
References: #6708
[orm] [bug] [regression] Fixed regression caused in 1.4.19 due to #6503 and related
involving _orm.Query.with_entities() where the new structure used
would be inappropriately transferred to an enclosing _orm.Query
when making use of set operations such as _orm.Query.union(), causing
the JOIN instructions within to be applied to the outside query as well.
References: #6698
[orm] [bug] [regression] Fixed regression which appeared in version 1.4.3 due to #6060
where rules that limit ORM adaptation of derived selectables interfered
with other ORM-adaptation based cases, in this case when applying
adaptations for a _orm.with_polymorphic() against a mapping which
uses a _orm.column_property() which in turn makes use of a scalar
select that includes a _orm.aliased() object of the mapped table.
References: #6762
[orm] [regression] Fixed ORM regression where ad-hoc label names generated for hybrid properties and potentially other similar types of ORM-enabled expressions would usually be propagated outwards through subqueries, allowing the name to be retained in the final keys of the result set even when selecting from subqueries. Additional state is now tracked in this case that isn't lost when a hybrid is selected out of a Core select / subquery.
References: #6718
[sql] [usecase] Added new method _sql.HasCTE.add_cte() to each of the
_sql.select(), _sql.insert(), _sql.update() and
_sql.delete() constructs. This method will add the given
_sql.CTE as an "independent" CTE of the statement, meaning it
renders in the WITH clause above the statement unconditionally even if it
is not otherwise referenced in the primary statement. This is a popular use
case on the PostgreSQL database where a CTE is used for a DML statement
that runs against database rows independently of the primary statement.
References: #6752
[sql] [bug] Fixed issue in CTE constructs where a recursive CTE that referred to a SELECT that has duplicate column names, which are typically deduplicated using labeling logic in 1.4, would fail to refer to the deduplicated label name correctly within the WITH clause.
References: #6710
[sql] [bug] [regression] Fixed regression where the _sql.tablesample() construct would fail to
be executable when constructed given a floating-point sampling value not
embedded within a SQL function.
References: #6735
[postgresql] [bug] Fixed issue in _postgresql.Insert.on_conflict_do_nothing() and
_postgresql.Insert.on_conflict_do_update() where the name of a unique
constraint passed as the constraint parameter would not be properly
truncated for length if it were based on a naming convention that generated
a too-long name for the PostgreSQL max identifier length of 63 characters,
in the same way which occurs within a CREATE TABLE statement.
References: #6755
[postgresql] [bug] Fixed issue where the PostgreSQL ENUM datatype as embedded in the
ARRAY datatype would fail to emit correctly in create/drop when the
schema_translate_map feature were also in use. Additionally repairs a
related issue where the same schema_translate_map feature would not
work for the ENUM datatype in combination with a CAST, that's also
intrinsic to how the ARRAY(ENUM) combination works on the PostgreSQL
dialect.
References: #6739
[postgresql] [bug] Fixed issue in _postgresql.Insert.on_conflict_do_nothing() and
_postgresql.Insert.on_conflict_do_update() where the name of a unique
constraint passed as the constraint parameter would not be properly
quoted if it contained characters which required quoting.
References: #6696
[mssql] [bug] [regression] Fixed regression where the special dotted-schema name handling for the SQL
Server dialect would not function correctly if the dotted schema name were
used within the schema_translate_map feature.
References: #6697
[orm] [bug] [regression] Fixed regression in ORM regarding an internal reconstitution step for the _orm.with_polymorphic() construct, when the user-fa
Released: June 28, 2021
[orm] [bug] [regression] Fixed regression in ORM regarding an internal reconstitution step for the
_orm.with_polymorphic() construct, when the user-facing object is
garbage collected as the query is processed. The reconstitution was not
ensuring the sub-entities for the "polymorphic" case were handled, leading
to an AttributeError.
References: #6680
[orm] [bug] [regression] Adjusted _orm.Query.union() and similar set operations to be
correctly compatible with the new capabilities just added in
#6661, with SQLAlchemy 1.4.19, such that the SELECT statements
rendered as elements of the UNION or other set operation will include
directly mapped columns that are mapped as deferred; this both fixes a
regression involving unions with multiple levels of nesting that would
produce a column mismatch, and also allows the _orm.undefer() option
to be used at the top level of such a _orm.Query without having to
apply the option to each of the elements within the UNION.
References: #6678
[orm] [bug] Adjusted the check in the mapper for a callable object that is used as a
@validates validator function or a @reconstructor reconstruction
function, to check for "callable" more liberally such as to accommodate
objects based on fundamental attributes like __func__ and
__call___, rather than testing for MethodType / FunctionType,
allowing things like cython functions to work properly. Pull request
courtesy Miłosz Stypiński.
References: #6538
[engine] [bug] Fixed an issue in the C extension for the _result.Row class which
could lead to a memory leak in the unlikely case of a _result.Row
object which referred to an ORM object that then was mutated to refer back
to the Row itself, creating a cycle. The Python C APIs for tracking GC
cycles has been added to the native _result.Row implementation to
accommodate for this case.
References: #5348
[engine] [bug] Fixed old issue where a _sql.select() made against the token "*",
which then yielded exactly one column, would fail to correctly organize the
cursor.description column name into the keys of the result object.
References: #6665
[sql] [usecase] Add a impl parameter to _types.PickleType constructor, allowing
any arbitary type to be used in place of the default implementation of
_types.LargeBinary. Pull request courtesy jason3gb.
References: #6646
[sql] [bug] [orm] Fixed the class hierarchy for the _schema.Sequence and the more
general _schema.DefaultGenerator base, as these are "executable"
as statements they need to include _sql.Executable in their
hierarchy, not just _roles.StatementRole as was applied
arbitrarily to _schema.Sequence previously. The fix allows
_schema.Sequence to work in all .execute() methods including
with _orm.Session.execute() which was not working in the case that a
_orm.SessionEvents.do_orm_execute() handler was also established.
References: #6668
[schema] [bug] Fixed issue where passing None for the value of
_schema.Table.prefixes would not store an empty list, but
rather the constant None, which may be unexpected by third party
dialects. The issue is revealed by a usage in recent versions of Alembic
that are passing None for this value. Pull request courtesy Kai
Mueller.
References: #6685
[mysql] [usecase] Made a small adjustment in the table reflection feature of the MySQL dialect to accommodate for alternate MySQL-oriented databases such as TiDB which include their own "comment" directives at the end of a constraint directive within "CREATE TABLE" where the format doesn't have the additional space character after the comment, in this case the TiDB "clustered index" feature. Pull request courtesy Daniël van Eeden.
References: #6659
[bug] [ext] [regression] Fixed regression in sqlalchemy.ext.automap extension such that the
use case of creating an explicit mapped class to a table that is also the
_orm.relationship.secondary element of a
_orm.relationship() that automap will be generating would emit the
"overlaps" warnings introduced in 1.4 and discussed at error_qzyx.
While generating this case from automap is still subject to the same
caveats that the "overlaps" warning refers towards, as automap is intended
for more ad-hoc use cases, the condition which produces the warning is
disabled when a many-to-many relationship with this particular pattern is
generated.
References: #6679
[orm] [bug] [regression] Fixed further regressions in the same area as that of #6052 where loader options as well as invocations of methods like _orm.
Released: June 22, 2021
[orm] [bug] [regression] Fixed further regressions in the same area as that of #6052 where
loader options as well as invocations of methods like
_orm.Query.join() would fail if the left side of the statement for
which the option/join depends upon were replaced by using the
_orm.Query.with_entities() method, or when using 2.0 style queries
when using the _sql.Select.with_only_columns() method. A new set of
state has been added to the objects which tracks the "left" entities that
the options / join were made against which is memoized when the lead
entities are changed.
[orm] [bug] Refined the behavior of ORM subquery rendering with regards to deferred
columns and column properties to be more compatible with that of 1.3 while
also providing for 1.4's newer features. As a subquery in 1.4 does not make
use of loader options, including _orm.undefer(), a subquery that is
against an ORM entity with deferred attributes will now render those
deferred attributes that refer directly to mapped table columns, as these
are needed in the outer SELECT if that outer SELECT makes use of these
columns; however a deferred attribute that refers to a composed SQL
expression as we normally do with _orm.column_property() will not be
part of the subquery, as these can be selected explicitly if needed in the
subquery. If the entity is being SELECTed from this subquery, the column
expression can still render on "the outside" in terms of the derived
subquery columns. This produces essentially the same behavior as when
working with 1.3. However in this case the fix has to also make sure that
the .selected_columns collection of an ORM-enabled _sql.select()
also follows these rules, which in particular allows recursive CTEs to
render correctly in this scenario, which were previously failing to render
correctly due to this issue.
References: #6661
[sql] [bug] Fixed issue in CTE constructs mostly relevant to ORM use cases where a
recursive CTE against "anonymous" labels such as those seen in ORM
column_property() mappings would render in the
WITH RECURSIVE xyz(...) section as their raw internal label and not a
cleanly anonymized name.
References: #6663
[mypy] [bug] Fixed issue in mypy plugin where class info for a custom declarative base would not be handled correctly on a cached mypy pass, leading to an AssertionError being raised.
References: #6476
[asyncio] [usecase] Implemented _asyncio.async_scoped_session to address some
asyncio-related incompatibilities between _orm.scoped_session and
_asyncio.AsyncSession, in which some methods (notably the
_asyncio.async_scoped_session.remove() method) should be used with
the await keyword.
References: #6583
[asyncio] [bug] [postgresql] Fixed bug in asyncio implementation where the greenlet adaptation system
failed to propagate BaseException subclasses, most notably including
asyncio.CancelledError, to the exception handling logic used by the
engine to invalidate and clean up the connection, thus preventing
connections from being correctly disposed when a task was cancelled.
References: #6652
[postgresql] [bug] [oracle] Fixed issue where the INTERVAL datatype on PostgreSQL and Oracle would
produce an AttributeError when used in the context of a comparison
operation against a timedelta() object. Pull request courtesy
MajorDallas.
References: #6649
[postgresql] [bug] Fixed issue where the pool "pre ping" feature would implicitly start a transaction, which would then interfere with custom transactional flags such as PostgreSQL's "read only" mode when used with the psycopg2 driver.
References: #6621
[mysql] [usecase] Added new construct _mysql.match, which provides for the full
range of MySQL's MATCH operator including multiple column support and
modifiers. Pull request courtesy Anton Kovalevich.
References: #6132
[mssql] [change] Made improvements to the server version regexp used by the pymssql dialect to prevent a regexp overflow in case of an invalid version string.
[mssql] [bug] Fixed bug where the "schema_translate_map" feature would fail to function correctly in conjunction with an INSERT into a table that has an IDENTITY column, where the value of the IDENTITY column were specified in the values of the INSERT thus triggering SQLAlchemy's feature of setting IDENTITY INSERT to "on"; it's in this directive where the schema translate map would fail to be honored.
References: #6658
…removed since 1.3. The issue could also cause deprecation warnings involving column resolution to be emitted when using a 1.4 style query with joined…
Released: June 10, 2021
[orm] [performance] [bug] [regression] Fixed regression involving how the ORM would resolve a given mapped column to a result row, where under cases such as joined eager loading, a slightly more expensive "fallback" could take place to set up this resolution due to some logic that was removed since 1.3. The issue could also cause deprecation warnings involving column resolution to be emitted when using a 1.4 style query with joined eager loading.
References: #6596
[orm] [bug] Clarified the current purpose of the
_orm.relationship.bake_queries flag, which in 1.4 is to enable
or disable "lambda caching" of statements within the "lazyload" and
"selectinload" loader strategies; this is separate from the more
foundational SQL query cache that is used for most statements.
Additionally, the lazy loader no longer uses its own cache for many-to-one
SQL queries, which was an implementation quirk that doesn't exist for any
other loader scenario. Finally, the "lru cache" warning that the lazyloader
and selectinloader strategies could emit when handling a wide array of
class/relationship combinations has been removed; based on analysis of some
end-user cases, this warning doesn't suggest any significant issue. While
setting bake_queries=False for such a relationship will remove this
cache from being used, there's no particular performance gain in this case
as using no caching vs. using a cache that needs to refresh often likely
still wins out on the caching being used side.
[orm] [bug] [regression] Adjusted the means by which classes such as _orm.scoped_session
and _asyncio.AsyncSession are generated from the base
_orm.Session class, such that custom _orm.Session
subclasses such as that used by Flask-SQLAlchemy don't need to implement
positional arguments when they call into the superclass method, and can
continue using the same argument styles as in previous releases.
References: #6285
[orm] [bug] [regression] Fixed issue where query production for joinedload against a complex left hand side involving joined-table inheritance could fail to produce a correct query, due to a clause adaption issue.
References: #6595
[orm] [bug] Fixed issue in experimental "select ORM objects from INSERT/UPDATE" use case where an error was raised if the statement were against a single-table-inheritance subclass.
References: #6591
[orm] [bug] The warning that's emitted for _orm.relationship() when multiple
relationships would overlap with each other as far as foreign key
attributes written towards, now includes the specific "overlaps" argument
to use for each warning in order to silence the warning without changing
the mapping.
References: #6400
[asyncio] [usecase] Implemented a new registry architecture that allows the Async version
of an object, like AsyncSession, AsyncConnection, etc., to be
locatable given the proxied "sync" object, i.e. Session,
Connection. Previously, to the degree such lookup functions were used,
an Async object would be re-created each time, which was less than
ideal as the identity and state of the "async" object would not be
preserved across calls.
From there, new helper functions _asyncio.async_object_session(),
_asyncio.async_session() as well as a new _orm.InstanceState
attribute _orm.InstanceState.async_session have been added, which
are used to retrieve the original _asyncio.AsyncSession associated
with an ORM mapped object, a _orm.Session associated with an
_asyncio.AsyncSession, and an _asyncio.AsyncSession
associated with an _orm.InstanceState, respectively.
This patch also implements new methods
_asyncio.AsyncSession.in_nested_transaction(),
_asyncio.AsyncSession.get_transaction(),
_asyncio.AsyncSession.get_nested_transaction().
References: #6319
[asyncio] [bug] Fixed an issue that presented itself when using the _pool.NullPool
or the _pool.StaticPool with an async engine. This mostly affected
the aiosqlite dialect.
References: #6575
[asyncio] [bug] Added asyncio.exceptions.TimeoutError,
asyncio.exceptions.CancelledError as so-called "exit exceptions", a
class of exceptions that include things like GreenletExit and
KeyboardInterrupt, which are considered to be events that warrant
considering a DBAPI connection to be in an unusable state where it should
be recycled.
References: #6592
[postgresql] [bug] [regression] Fixed regression where using the PostgreSQL "INSERT..ON CONFLICT" structure would fail to work with the psycopg2 driver if it were used in an "executemany" context along with bound parameters in the "SET" clause, due to the implicit use of the psycopg2 fast execution helpers which are not appropriate for this style of INSERT statement; as these helpers are the default in 1.4 this is effectively a regression. Additional checks to exclude this kind of statement from that particular extension have been added.
References: #6581
[sqlite] [bug] Add note regarding encryption-related pragmas for pysqlcipher passed in the url.
This change is also backported to: 1.3.25
References: #6589
[sqlite] [bug] [regression] The fix for pysqlcipher released in version 1.4.3 #5848 was
unfortunately non-working, in that the new on_connect_url hook was
erroneously not receiving a URL object under normal usage of
_sa.create_engine() and instead received a string that was unhandled;
the test suite failed to fully set up the actual conditions under which
this hook is called. This has been fixed.
References: #6586
[orm] [bug] [regression] Fixed regression caused by just-released performance fix mentioned in #6550 where a query.join() to a relationship could prod
Released: May 29, 2021
[orm] [bug] [regression] Fixed regression caused by just-released performance fix mentioned in #6550 where a query.join() to a relationship could produce an AttributeError if the query were made against non-ORM structures only, a fairly unusual calling pattern.
References: #6558
[general] [bug] Resolved various deprecation warnings which were appearing as of Python version 3.10.0b1.
Released: May 28, 2021
[general] [bug] Resolved various deprecation warnings which were appearing as of Python version 3.10.0b1.
[orm] [bug] Fixed issue when using _orm.relationship.cascade_backrefs
parameter set to False, which per change_5150 is set to become
the standard behavior in SQLAlchemy 2.0, where adding the item to a
collection that uniquifies, such as set or dict would fail to fire
a cascade event if the object were already associated in that collection
via the backref. This fix represents a fundamental change in the collection
mechanics by introducing a new event state which can fire off for a
collection mutation even if there is no net change on the collection; the
action is now suited using a new event hook
_orm.AttributeEvents.append_wo_mutation().
References: #6471
[orm] [bug] [regression] Fixed regression involving clause adaption of labeled ORM compound elements, such as single-table inheritance discriminator expressions with conditionals or CASE expressions, which could cause aliased expressions such as those used in ORM join / joinedload operations to not be adapted correctly, such as referring to the wrong table in the ON clause in a join.
This change also improves a performance bump that was located within the
process of invoking _sql.Select.join() given an ORM attribute
as a target.
References: #6550
[orm] [bug] [regression] Fixed regression where the full combination of joined inheritance, global with_polymorphic, self-referential relationship and joined loading would fail to be able to produce a query with the scope of lazy loads and object refresh operations that also attempted to render the joined loader.
References: #6495
[orm] [bug] Enhanced the bind resolution rules for _orm.Session.execute() so that
when a non-ORM statement such as an _sql.insert() construct
nonetheless is built against ORM objects, to the greatest degree possible
the ORM entity will be used to resolve the bind, such as for a
_orm.Session that has a bind map set up on a common superclass
without specific mappers or tables named in the map.
References: #6484
[engine] [bug] Fixed issue where an @ sign in the database portion of a URL would not
be interpreted correctly if the URL also had a username:password section.
References: #6482
[engine] [bug] Fixed a long-standing issue with URL where query parameters
following the question mark would not be parsed correctly if the URL did
not contain a database portion with a backslash.
References: #6329
[sql] [bug] [regression] Fixed regression in dynamic loader strategy and _orm.relationship()
overall where the _orm.relationship.order_by parameter were
stored as a mutable list, which could then be mutated when combined with
additional "order_by" methods used against the dynamic query object,
causing the ORDER BY criteria to continue to grow repetitively.
References: #6549
[mssql] [usecase] Implemented support for a _sql.CTE construct to be used directly
as the target of a _sql.delete() construct, i.e. "WITH ... AS cte
DELETE FROM cte". This appears to be a useful feature of SQL Server.
References: #6464
[bug] [ext] Fixed a deprecation warning that was emitted when using
_automap.automap_base() without passing an existing
Base.
References: #6529
[bug] [pep484] Remove pep484 types from the code. Current effort is around the stub package, and having typing in two places makes thing worse, since the types in the SQLAlchemy source were usually outdated compared to the version in the stubs.
References: #6461
[bug] [ext] [regression] Fixed regression in the sqlalchemy.ext.instrumentation extension that
prevented instrumentation disposal from working completely. This fix
includes both a 1.4 regression fix as well as a fix for a related issue
that existed in 1.3 also. As part of this change, the
sqlalchemy.ext.instrumentation.InstrumentationManager class now
has a new method unregister(), which replaces the previous method
dispose(), which was not called as of version 1.4.
References: #6390
This allows evaluating the source of SQLAlchemy-generated warnings and deprecation warnings to be more straightforward as the warning will indicate th…
Released: May 11, 2021
[general] [feature] A new approach has been applied to the warnings system in SQLAlchemy to accurately predict the appropriate stack level for each warning dynamically. This allows evaluating the source of SQLAlchemy-generated warnings and deprecation warnings to be more straightforward as the warning will indicate the source line within end-user code, rather than from an arbitrary level within SQLAlchemy's own source code.
References: #6241
[orm] [bug] [regression] Fixed additional regression caused by "eager loaders run on unexpire"
feature #1763 where the feature would run for a
contains_eager() eagerload option in the case that the
contains_eager() were chained to an additional eager loader option,
which would then produce an incorrect query as the original query-bound
join criteria were no longer present.
References: #6449
[orm] [bug] Fixed issue in subquery loader strategy which prevented caching from working correctly. This would have been seen in the logs as a "generated" message instead of "cached" for all subqueryload SQL emitted, which by saturating the cache with new keys would degrade overall performance; it also would produce "LRU size alert" warnings.
References: #6459
[sql] [bug] Adjusted the logic added as part of #6397 in 1.4.12 so that
internal mutation of the BindParameter object occurs within the
clause construction phase as it did before, rather than in the compilation
phase. In the latter case, the mutation still produced side effects against
the incoming construct and additionally could potentially interfere with
other internal mutation routines.
References: #6460
[mysql] [bug] [documentation] Added support for the ssl_check_hostname= parameter in mysql connection
URIs and updated the mysql dialect documentation regarding secure
connections. Original pull request courtesy of Jerry Zhao.
References: #5397
[engine] [bug] [regression] Established a deprecation path for calling upon the _cursor.CursorResult.keys() method for a statement that returns no row…
Released: May 6, 2021
[orm] [bug] [regression] Fixed regression involving lazy='dynamic' loader in conjunction with a
detached object. The previous behavior was that the dynamic loader upon
calling methods like .all() returns empty lists for detached objects
without error, this has been restored; however a warning is now emitted as
this is not the correct result. Other dynamic loader scenarios correctly
raise DetachedInstanceError.
References: #6426
[engine] [usecase] [orm] Applied consistent behavior to the use case of
calling .commit() or .rollback() inside of an existing
.begin() context manager, with the addition of potentially
emitting SQL within the block subsequent to the commit or rollback.
This change continues upon the change first added in
#6155 where the use case of calling "rollback" inside of
a .begin() contextmanager block was proposed:
- calling `.commit()` or `.rollback()` will now be allowed
without error or warning within all scopes, including
that of legacy and future `_engine.Engine`, ORM
`_orm.Session`, asyncio `AsyncEngine`. Previously,
the `_orm.Session` disallowed this.
- The remaining scope of the context manager is then closed;
when the block ends, a check is emitted to see if the transaction
was already ended, and if so the block returns without action.
- It will now raise **an error** if subsequent SQL of any kind
is emitted within the block, **after** `.commit()` or
`.rollback()` is called. The block should be closed as
the state of the executable object would otherwise be undefined
in this state.
References: #6288
[engine] [bug] [regression] Established a deprecation path for calling upon the
_cursor.CursorResult.keys() method for a statement that returns no
rows to provide support for legacy patterns used by the "records" package
as well as any other non-migrated applications. Previously, this would
raise ResourceClosedException unconditionally in the same way as
it does when attempting to fetch rows. While this is the correct behavior
going forward, the _cursor.LegacyCursorResult object will now in
this case return an empty list for .keys() as it did in 1.3, while also
emitting a 2.0 deprecation warning. The _cursor.CursorResult, used
when using a 2.0-style "future" engine, will continue to raise as it does
now.
References: #6427
[sql] [bug] [regression] Fixed regression caused by the "empty in" change just made in #6397 1.4.12 where the expression needs to be parenthesized for the "not in" use case, otherwise the condition will interfere with the other filtering criteria.
References: #6428
[sql] [bug] [regression] The TypeDecorator class will now emit a warning when used in SQL
compilation with caching unless the .cache_ok flag is set to True
or False. A new class-level attribute TypeDecorator.cache_ok
may be set which will be used as an indication that all the parameters
passed to the object are safe to be used as a cache key if set to True,
False means they are not.
References: #6436
In non-"future" mode, while the old behavior is restored, it also emits a 2.0 deprecation warning as this is a legacy behavior.
# 1.4.13
Released: May 3, 2021 ## orm
[orm] [bug] [regression] Fixed regression in selectinload loader strategy that would cause it to cache its internal state incorrectly when handling relationships that join across more than one column, such as when using a composite foreign key. The invalid caching would then cause other unrelated loader operations to fail.
References: [#6410](http://www.sqlalchemy.org/trac/ticket/6410)
[orm] [bug] [regression] Fixed regression where _orm.Query.filter_by() would not work if the lead entity were a SQL function or other expression derived from the primary entity in question, rather than a simple entity or column of that entity. Additionally, improved the behavior of _sql.Select.filter_by() overall to work with column expressions even in a non-ORM context.
References: [#6414](http://www.sqlalchemy.org/trac/ticket/6414)
[orm] [bug] [regression] Fixed regression where using _orm.selectinload() and _orm.subqueryload() to load a two-level-deep path would lead to an attribute error.
References: [#6419](http://www.sqlalchemy.org/trac/ticket/6419)
[orm] [bug] [regression] Fixed regression where using the _orm.noload() loader strategy in conjunction with a "dynamic" relationship would lead to an attribute error as the noload strategy would attempt to apply itself to the dynamic loader.
References: [#6420](http://www.sqlalchemy.org/trac/ticket/6420)
## engine
[engine] [bug] [regression] Restored a legacy transactional behavior that was inadvertently removed from the _engine.Connection as it was never tested as a known use case in previous versions, where calling upon the _engine.Connection.begin_nested() method, when no transaction is present, does not create a SAVEPOINT at all and instead starts an outer transaction, returning a RootTransaction object instead of a NestedTransaction object. This RootTransaction then will emit a real COMMIT on the database connection when committed. Previously, the 2.0 style behavior was present in all cases that would autobegin a transaction but not commit it, which is a behavioral change.
When using a 2.0 style connection object, the behavior is unchanged from previous 1.4 versions; calling _future.Connection.begin_nested() will "autobegin" the outer transaction if not already present, and then as instructed emit a SAVEPOINT, returning the NestedTransaction object. The outer transaction is committed by calling upon _future.Connection.commit(), as is "commit-as-you-go" style usage.
Unknown interpreted text role "term".
In non-"future" mode, while the old behavior is restored, it also emits a 2.0 deprecation warning as this is a legacy behavior.
References: [#6408](http://www.sqlalchemy.org/trac/ticket/6408)
## asyncio
[asyncio] [bug] [regression] Fixed a regression introduced by [#6337](http://www.sqlalchemy.org/trac/ticket/6337) that would create an asyncio.Lock which could be attached to the wrong loop when instantiating the async engine before any asyncio loop was started, leading to an asyncio error message when attempting to use the engine under certain circumstances.
References: [#6409](http://www.sqlalchemy.org/trac/ticket/6409)
## postgresql
[postgresql] [usecase] Add support for server side cursors in the pg8000 dialect for PostgreSQL. This allows use of the Connection.execution_options.stream_results option.
References: [#6198](http://www.sqlalchemy.org/trac/ticket/6198)
[orm] [bug] Fixed an issue with the (deprecated in 1.4) _schema.ForeignKeyConstraint.copy() method that caused an error when invoked with the schema a…
Released: April 29, 2021
[orm] [bug] Fixed issue in _orm.Session.bulk_save_objects() when used with persistent
objects which would fail to track the primary key of mappings where the
column name of the primary key were different than the attribute name.
This change is also backported to: 1.3.25
References: #6392
[orm] [bug] [caching] [regression] Fixed critical regression where bound parameter tracking as used in the SQL caching system could fail to track all parameters for the case where the same SQL expression containing a parameter were used in an ORM-related query using a feature such as class inheritance, which was then embedded in an enclosing expression which would make use of that same expression multiple times, such as a UNION. The ORM would individually copy the individual SELECT statements as part of compilation with class inheritance, which then embedded in the enclosing statement would fail to accommodate for all parameters. The logic that tracks this condition has been adjusted to work for multiple copies of a parameter.
References: #6391
[orm] [bug] Fixed two distinct issues mostly affecting
_hybrid.hybrid_property, which would come into play under common
mis-configuration scenarios that were silently ignored in 1.3, and now
failed in 1.4, where the "expression" implementation would return a non
_sql.ClauseElement such as a boolean value. For both issues, 1.3's
behavior was to silently ignore the mis-configuration and ultimately
attempt to interpret the value as a SQL expression, which would lead to an
incorrect query.
- Fixed issue regarding interaction of the attribute system with
hybrid_property, where if the `__clause_element__()` method of the
attribute returned a non-`_sql.ClauseElement` object, an internal
`AttributeError` would lead the attribute to return the `expression`
function on the hybrid_property itself, as the attribute error was
against the name `.expression` which would invoke the `__getattr__()`
method as a fallback. This now raises explicitly. In 1.3 the
non-`_sql.ClauseElement` was returned directly.
- Fixed issue in SQL argument coercions system where passing the wrong
kind of object to methods that expect column expressions would fail if
the object were altogether not a SQLAlchemy object, such as a Python
function, in cases where the object were not just coerced into a bound
value. Again 1.3 did not have a comprehensive argument coercion system
so this case would also pass silently.
References: #6350
[orm] [bug] Fixed issue where using a _sql.Select as a subquery in an ORM
context would modify the _sql.Select in place to disable
eagerloads on that object, which would then cause that same
_sql.Select to not eagerload if it were then re-used in a
top-level execution context.
References: #6378
[orm] [bug] [regression] Fixed issue where the new autobegin <session_autobegin> behavior
failed to "autobegin" in the case where an existing persistent object has
an attribute change, which would then impact the behavior of
_orm.Session.rollback() in that no snapshot was created to be rolled
back. The "attribute modify" mechanics have been updated to ensure
"autobegin", which does not perform any database work, does occur when
persistent attributes change in the same manner as when
_orm.Session.add() is called. This is a regression as in 1.3, the
rollback() method always had a transaction to roll back and would expire
every time.
[orm] [bug] [regression] Fixed regression in ORM where using hybrid property to indicate an expression from a different entity would confuse the column-labeling logic in the ORM and attempt to derive the name of the hybrid from that other class, leading to an attribute error. The owning class of the hybrid attribute is now tracked along with the name.
References: #6386
[orm] [bug] [regression] Fixed regression in hybrid_property where a hybrid against a SQL function
would generate an AttributeError when attempting to generate an entry
for the .c collection of a subquery in some cases; among other things
this would impact its use in cases like that of Query.count().
References: #6401
[orm] [bug] [dataclasses] Adjusted the declarative scan for dataclasses so that the inheritance
behavior of _orm.declared_attr() established on a mixin, when using
the new form of having it inside of a dataclasses.field() construct and
not actually a descriptor attribute on the class, correctly accommodates
the case when the target class to be mapped is a subclass of an existing
mapped class which has already mapped that _orm.declared_attr(), and
therefore should not be re-applied to this class.
References: #6346
[orm] [bug] Fixed an issue with the (deprecated in 1.4)
_schema.ForeignKeyConstraint.copy() method that caused an error when
invoked with the schema argument.
References: #6353
[engine] [bug] Fixed issue where usage of an explicit Sequence would produce
inconsistent "inline" behavior for an Insert construct that
includes multiple values phrases; the first seq would be inline but
subsequent ones would be "pre-execute", leading to inconsistent sequence
ordering. The sequence expressions are now fully inline.
References: #6361
[sql] [bug] Revised the "EMPTY IN" expression to no longer rely upon using a subquery, as this was causing some compatibility and performance problems. The new approach for selected databases takes advantage of using a NULL-returning IN expression combined with the usual "1 != 1" or "1 = 1" expression appended by AND or OR. The expression is now the default for all backends other than SQLite, which still had some compatibility issues regarding tuple "IN" for older SQLite versions.
Third party dialects can still override how the "empty set" expression
renders by implementing a new compiler method
def visit_empty_set_op_expr(self, type_, expand_op), which takes
precedence over the existing
def visit_empty_set_expr(self, element_types) which remains in place.
References: [#6258 6397](http://www.sqlalchemy.org/trac/ticket/6258 6397)
[sql] [bug] [regression] Fixed regression where usage of the _sql.text() construct inside the
columns clause of a _sql.Select construct, which is better handled
by using a _sql.literal_column() construct, would nonetheless prevent
constructs like _sql.union() from working correctly. Other use cases,
such as constructing subuqeries, continue to work the same as in prior
versions where the _sql.text() construct is silently omitted from the
collection of exported columns. Also repairs similar use within the
ORM.
References: #6343
[sql] [bug] [regression] Fixed regression involving legacy methods such as
_sql.Select.append_column() where internal assertions would fail.
References: #6261
[sql] [bug] [regression] Fixed regression caused by #5395 where tuning back the check for
sequences in _sql.select() now caused failures when doing 2.0-style
querying with a mapped class that also happens to have an __iter__()
method. Tuned the check some more to accommodate this as well as some other
interesting __iter__() scenarios.
References: #6300
[schema] [bug] [mariadb] [mysql] [oracle] [postgresql] Ensure that the MySQL and MariaDB dialect ignore the
_sql.Identity construct while rendering the AUTO_INCREMENT
keyword in a create table.
The Oracle and PostgreSQL compiler was updated to not render
_sql.Identity if the database version does not support it
(Oracle < 12 and PostgreSQL < 10). Previously it was rendered regardless
of the database version.
References: #6338
[postgresql] [bug] Fixed very old issue where the _types.Enum datatype would not
inherit the _schema.MetaData.schema parameter of a
_schema.MetaData object when that object were passed to the
_types.Enum using _types.Enum.metadata.
References: #6373
[sqlite] [usecase] Default to using SingletonThreadPool for in-memory SQLite databases
created using URI filenames. Previously the default pool used was the
NullPool that precented sharing the same database between multiple
engines.
References: #6379
[mssql] [bug] [schema] Add _types.TypeEngine.as_generic() support for
sqlalchemy.dialects.mysql.BIT columns, mapping
them to _sql.sqltypes.Boolean.
References: #6345
[mssql] [bug] [regression] Fixed regression caused by #6306 which added support for
DateTime(timezone=True), where the previous behavior of the pyodbc
driver of implicitly dropping the tzinfo from a timezone-aware date when
INSERTing into a timezone-naive DATETIME column were lost, leading to a SQL
Server error when inserting timezone-aware datetime objects into
timezone-native database columns.
References: #6366
[orm] [declarative] [bug] [regression] Fixed regression where recent changes to support Python dataclasses had the inadvertent effect that an ORM mapp
Released: April 21, 2021
[orm] [declarative] [bug] [regression] Fixed regression where recent changes to support Python dataclasses had the
inadvertent effect that an ORM mapped class could not successfully override
the __new__() method.
References: #6331
[engine] [bug] [regression] Fixed critical regression caused by the change in #5497 where the connection pool "init" phase no longer occurred within mutexed isolation, allowing other threads to proceed with the dialect uninitialized, which could then impact the compilation of SQL statements.
References: #6337
[orm] [usecase] Altered some of the behavior repaired in #6232 where the immediateload loader strategy no longer goes into recursive loops; the modifi
Released: April 20, 2021
[orm] [usecase] Altered some of the behavior repaired in #6232 where the
immediateload loader strategy no longer goes into recursive loops; the
modification is that an eager load (joinedload, selectinload, or
subqueryload) from A->bs->B which then states immediateload for a
simple manytoone B->a->A that's in the identity map will populate the B->A,
so that this attribute is back-populated when the collection of A/A.bs are
loaded. This allows the objects to be functional when detached.
[orm] [bug] Fixed bug in new _orm.with_loader_criteria() feature where using a
mixin class with _orm.declared_attr() on an attribute that were
accessed inside the custom lambda would emit a warning regarding using an
unmapped declared attr, when the lambda callable were first initialized.
This warning is now prevented using special instrumentation for this
lambda initialization step.
References: #6320
[orm] [bug] [regression] Fixed additional regression caused by the "eagerloaders on refresh" feature
added in #1763 where the refresh operation historically would set
populate_existing, which given the new feature now overwrites pending
changes on eagerly loaded objects when autoflush is false. The
populate_existing flag has been turned off for this case and a more
specific method used to ensure the correct attributes refreshed.
References: #6326
[orm] [bug] [result] Fixed an issue when using 2.0 style execution that prevented using
_result.Result.scalar_one() or
_result.Result.scalar_one_or_none() after calling
_result.Result.unique(), for the case where the ORM is returning a
single-element row in any case.
References: #6299
[sql] [bug] Fixed issue in SQL compiler where the bound parameters set up for a
Values construct wouldn't be positionally tracked correctly if
inside of a _sql.CTE, affecting database drivers that support
VALUES + ctes and use positional parameters such as SQL Server in
particular as well as asyncpg. The fix also repairs support for
compiler flags such as literal_binds.
References: #6327
[sql] [bug] Repaired and solidified issues regarding custom functions and other
arbitrary expression constructs which within SQLAlchemy's column labeling
mechanics would seek to use str(obj) to get a string representation to
use as an anonymous column name in the .c collection of a subquery.
This is a very legacy behavior that performs poorly and leads to lots of
issues, so has been revised to no longer perform any compilation by
establishing specific methods on FunctionElement to handle this
case, as SQL functions are the only use case that it came into play. An
effect of this behavior is that an unlabeled column expression with no
derivable name will be given an arbitrary label starting with the prefix
"_no_label" in the .c collection of a subquery; these were
previously being represented either as the generic stringification of that
expression, or as an internal symbol.
References: #6256
[schema] [usecase] [mssql] The _types.DateTime.timezone parameter when set to True
will now make use of the DATETIMEOFFSET column type with SQL Server
when used to emit DDL, rather than DATETIME where the flag was silently
ignored.
References: #6306
[schema] [bug] Fixed issue where _functions.next_value() was not deriving its type
from the corresponding _schema.Sequence, instead hardcoded to
_types.Integer. The specific numeric type is now used.
References: #6287
[mypy] [bug] Fixed issue where mypy plugin would not correctly interpret an explicit
_orm.Mapped annotation in conjunction with a
_orm.relationship() that refers to a class by string name; the
correct annotation would be downgraded to a less specific one leading to
typing errors.
References: #6255
[bug] [declarative] [regression] Fixed _declarative.instrument_declarative() that called
a non existing registry method.
References: #6291
[orm] [usecase] Established support for _orm.synoynm() in conjunction with hybrid property, assocaitionproxy is set up completely, including that syno
Released: April 17, 2021
[orm] [usecase] Established support for _orm.synoynm() in conjunction with
hybrid property, assocaitionproxy is set up completely, including that
synonyms can be established linking to these constructs which work
fully. This is a behavior that was semi-explicitly disallowed previously,
however since it did not fail in every scenario, explicit support
for assoc proxy and hybrids has been added.
References: #6267
[orm] [performance] [bug] [regression] [sql] Fixed a critical performance issue where the traversal of a
_sql.select() construct would traverse a repetitive product of the
represented FROM clauses as they were each referred towards by columns in
the columns clause; for a series of nested subqueries with lots of columns
this could cause a large delay and significant memory growth. This
traversal is used by a wide variety of SQL and ORM functions, including by
the ORM _orm.Session when it's configured to have
"table-per-bind", which while this is not a common use case, it seems to be
what Flask-SQLAlchemy is hardcoded as using, so the issue impacts
Flask-SQLAlchemy users. The traversal has been repaired to uniqify on FROM
clauses which was effectively what would happen implicitly with the pre-1.4
architecture.
References: #6304
[orm] [bug] [regression] Fixed regression where an attribute that is mapped to a
_orm.synonym() could not be used in column loader options such as
_orm.load_only().
References: #6272
[sql] [bug] [regression] Fixed regression where an empty in statement on a tuple would result
in an error when compiled with the option literal_binds=True.
References: #6290
[postgresql] [bug] [regression] [sql] Fixed an argument error in the default and PostgreSQL compilers that would interfere with an UPDATE..FROM or DELETE..FROM..USING statement that was then SELECTed from as a CTE.
References: #6303
[orm] [bug] [regression] Fixed a cache leak involving the _orm.with_expression() loader option, where the given SQL expression would not be correctly
Released: April 15, 2021
[orm] [bug] [regression] Fixed a cache leak involving the _orm.with_expression() loader
option, where the given SQL expression would not be correctly considered as
part of the cache key.
Additionally, fixed regression involving the corresponding
_orm.query_expression() feature. While the bug technically exists in
1.3 as well, it was not exposed until 1.4. The "default expr" value of
null() would be rendered when not needed, and additionally was also not
adapted correctly when the ORM rewrites statements such as when using
joined eager loading. The fix ensures "singleton" expressions like NULL
and true aren't "adapted" to refer to columns in ORM statements, and
additionally ensures that a _orm.query_expression() with no default
expression doesn't render in the statement if a
_orm.with_expression() isn't used.
References: #6259
[orm] [bug] Fixed issue in the new feature of _orm.Session.refresh() introduced
by #1763 where eagerly loaded relationships are also refreshed,
where the lazy="raise" and lazy="raise_on_sql" loader strategies
would interfere with the _orm.immediateload() loader strategy, thus
breaking the feature for relationships that were loaded with
_orm.selectinload(), _orm.subqueryload() as well.
References: #6252
_engine.Dialect.has_table() method now raises an informative
exception if a non-Connection is passed to it, as this incorrect behavior
seems to be common. This method is not intended for external use outside
of a dialect. Please use the Inspector.has_table() method
or for cross-compatibility with older SQLAlchemy versions, the
_engine.Engine.has_table() method.[sql] [feature] The tuple returned by CursorResult.inserted_primary_key is now a
_result.Row object with a named tuple interface on top of the
existing tuple interface.
References: #3314
[sql] [bug] [regression] Fixed regression where the _sql.BindParameter object would not
properly render for an IN expression (i.e. using the "post compile" feature
in 1.4) if the object were copied from either an internal cloning
operation, or from a pickle operation, and the parameter name contained
spaces or other special characters.
References: #6249
[sql] [bug] [regression] [sqlite] Fixed regression where the introduction of the INSERT syntax "INSERT... VALUES (DEFAULT)" was not supported on some backends that do however support "INSERT..DEFAULT VALUES", including SQLite. The two syntaxes are now each individually supported or non-supported for each dialect, for example MySQL supports "VALUES (DEFAULT)" but not "DEFAULT VALUES". Support for Oracle has also been enabled.
References: #6254
[mypy] [change] Updated Mypy plugin to only use the public plugin interface of the semantic analyzer.
[mypy] [bug] Revised the fix for OrderingList from version 1.4.7 which was testing
against the incorrect API.
References: #6205
[asyncio] [bug] Fix typo that prevented setting the bind attribute of an
_asyncio.AsyncSession to the correct value.
References: #6220
[mssql] [bug] [regression] Fixed an additional regression in the same area as that of #6173,
#6184, where using a value of 0 for OFFSET in conjunction with
LIMIT with SQL Server would create a statement using "TOP", as was the
behavior in 1.3, however due to caching would then fail to respond
accordingly to other values of OFFSET. If the "0" wasn't first, then it
would be fine. For the fix, the "TOP" syntax is now only emitted if the
OFFSET value is omitted entirely, that is, _sql.Select.offset() is
not used. Note that this change now requires that if the "with_ties" or
"percent" modifiers are used, the statement can't specify an OFFSET of
zero, it now needs to be omitted entirely.
References: #6265
This was originally supposed to emit a 2.0 deprecation warning for the "non-future" case using _result.LegacyRow, and was to raise TypeError for the "…
Released: April 9, 2021
[orm] [bug] [regression] Fixed regression where the _orm.subqueryload() loader strategy would
fail to correctly accommodate sub-options, such as a _orm.defer()
option on a column, if the "path" of the subqueryload were more than one
level deep.
References: #6221
[orm] [bug] [regression] Fixed regression where the _orm.merge_frozen_result() function relied
upon by the dogpile.caching example was not included in tests and began
failing due to incorrect internal arguments.
References: #6211
[orm] [bug] [regression] Fixed critical regression where the _orm.Session could fail to
"autobegin" a new transaction when a flush occurred without an existing
transaction in place, implicitly placing the _orm.Session into
legacy autocommit mode which commit the transaction. The
_orm.Session now has a check that will prevent this condition from
occurring, in addition to repairing the flush issue.
Additionally, scaled back part of the change made as part of #5226
which can run autoflush during an unexpire operation, to not actually
do this in the case of a _orm.Session using legacy
_orm.Session.autocommit mode, as this incurs a commit within
a refresh operation.
References: #6233
[orm] [bug] [regression] Fixed regression where the ORM compilation scheme would assume the function
name of a hybrid property would be the same as the attribute name in such a
way that an AttributeError would be raised, when it would attempt to
determine the correct name for each element in a result tuple. A similar
issue exists in 1.3 but only impacts the names of tuple rows. The fix here
adds a check that the hybrid's function name is actually present in the
__dict__ of the class or its superclasses before assigning this name;
otherwise, the hybrid is considered to be "unnamed" and ORM result tuples
will use the naming scheme of the underlying expression.
References: #6215
[orm] [bug] [regression] Fixed critical regression caused by the new feature added as part of #1763, eager loaders are invoked on unexpire operations. The new feature makes use of the "immediateload" eager loader strategy as a substitute for a collection loading strategy, which unlike the other "post-load" strategies was not accommodating for recursive invocations between mutually-dependent relationships, leading to recursion overflow errors.
References: #6232
[engine] [bug] [regression] Fixed up the behavior of the _result.Row object when dictionary
access is used upon it, meaning converting to a dict via dict(row) or
accessing members using strings or other objects i.e. row["some_key"]
works as it would with a dictionary, rather than raising TypeError as
would be the case with a tuple, whether or not the C extensions are in
place. This was originally supposed to emit a 2.0 deprecation warning for
the "non-future" case using _result.LegacyRow, and was to raise
TypeError for the "future" _result.Row class. However, the C
version of _result.Row was failing to raise this TypeError,
and to complicate matters, the _orm.Session.execute() method now
returns _result.Row in all cases to maintain consistency with the
ORM result case, so users who didn't have C extensions installed would
see different behavior in this one case for existing pre-1.4 style
code.
Therefore, in order to soften the overall upgrade scheme as most users have
not been exposed to the more strict behavior of _result.Row up
through 1.4.6, _result.LegacyRow and _result.Row both
provide for string-key access as well as support for dict(row), in all
cases emitting the 2.0 deprecation warning when SQLALCHEMY_WARN_20 is
enabled. The _result.Row object still uses tuple-like behavior for
__contains__, which is probably the only noticeable behavioral change
compared to _result.LegacyRow, other than the removal of
dictionary-style methods values() and items().
References: #6218
[sql] [bug] [regression] Enhanced the "expanding" feature used for _sql.ColumnOperators.in_()
operations to infer the type of expression from the right hand list of
elements, if the left hand side does not have any explicit type set up.
This allows the expression to support stringification among other things.
In 1.3, "expanding" was not automatically used for
_sql.ColumnOperators.in_() expressions, so in that sense this change
fixes a behavioral regression.
References: #6222
[sql] [bug] Fixed the "stringify" compiler to support a basic stringification of a "multirow" INSERT statement, i.e. one with multiple tuples following the VALUES keyword.
[schema] [bug] [regression] Fixed regression where usage of a token in the
_engine.Connection.execution_options.schema_translate_map
dictionary which contained special characters such as braces would fail to
be substituted properly. Use of square bracket characters [] is now
explicitly disallowed as these are used as a delimiter character in the
current implementation.
References: #6216
TypeEngine, in particular that of TypeDecorator and
UserDefinedType.DefaultDialect called supports_schema;
third party dialects may set this flag to True to enable SQLAlchemy's
schema-level tests when running the test suite for a third party dialect.[orm] [bug] [regression] Fixed regression where a deprecated form of _orm.Query.join() were used, passing a series of entities to join from without an…
# 1.4.6
Released: April 6, 2021 ## orm
[orm] [bug] [regression] Fixed regression where a deprecated form of _orm.Query.join() were used, passing a series of entities to join from without any ON clause in a single _orm.Query.join() call, would fail to function correctly.
References: [#6203](http://www.sqlalchemy.org/trac/ticket/6203)
[orm] [bug] [regression] Fixed critical regression where the _orm.Query.yield_per() method in the ORM would set up the internal _engine.Result to yield chunks at a time, however made use of the new _engine.Result.unique() method which uniques across the entire result. This would lead to lost rows since the ORM is using id(obj) as the uniquing function, which leads to repeated identifiers for new objects as already-seen objects are garbage collected. 1.3's behavior here was to "unique" across each chunk, which does not actually produce "uniqued" results when results are yielded in chunks. As the _orm.Query.yield_per() method is already explicitly disallowed when joined eager loading is in place, which is the primary rationale for the "uniquing" feature, the "uniquing" feature is now turned off entirely when _orm.Query.yield_per() is used.
This regression only applies to the legacy _orm.Query object; when using 2.0 style execution, "uniquing" is not automatically applied. To prevent the issue from arising from explicit use of _engine.Result.unique(), an error is now raised if rows are fetched from a "uniqued" ORM-level _engine.Result if any yield per <orm_queryguide_yield_per> API is also in use, as the purpose of yield_per is to allow for arbitrarily large numbers of rows, which cannot be uniqued in memory without growing the number of entries to fit the complete result size.
Unknown interpreted text role "term".
References: [#6206](http://www.sqlalchemy.org/trac/ticket/6206)
## sql
[sql] [bug] [mssql] [oracle] [regression] Fixed further regressions in the same area as that of [#6173](http://www.sqlalchemy.org/trac/ticket/6173) released in 1.4.5, where a "postcompile" parameter, again most typically those used for LIMIT/OFFSET rendering in Oracle and SQL Server, would fail to be processed correctly if the same parameter rendered in multiple places in the statement.
References: [#6202](http://www.sqlalchemy.org/trac/ticket/6202)
[sql] [bug] Executing a _sql.Subquery using _engine.Connection.execute() is deprecated and will emit a deprecation warning; this use case was an oversight that should have been removed from 1.4. The operation will now execute the underlying _sql.Select object directly for backwards compatibility. Similarly, the _sql.CTE class is also not appropriate for execution. In 1.3, attempting to execute a CTE would result in an invalid "blank" SQL statement being executed; since this use case was not working it now raises _exc.ObjectNotExecutableError. Previously, 1.4 was attempting to execute the CTE as a statement however it was working only erratically.
References: [#6204](http://www.sqlalchemy.org/trac/ticket/6204)
## schema
[schema] [bug] The _schema.Table object now raises an informative error message if it is instantiated without passing at least the _schema.Table.name and _schema.Table.metadata arguments positionally. Previously, if these were passed as keyword arguments, the object would silently fail to initialize correctly.
This change is also backported to: 1.3.25
References: [#6135](http://www.sqlalchemy.org/trac/ticket/6135)
## mypy
[mypy] [bug] Applied a series of refactorings and fixes to accommodate for Mypy "incremental" mode across multiple files, which previously was not taken into account. In this mode the Mypy plugin has to accommodate Python datatypes expressed in other files coming in with less information than they have on a direct run.
Additionally, a new decorator _orm.declarative_mixin() is added, which is necessary for the Mypy plugin to be able to definifitely identify a Declarative mixin class that is otherwise not used inside a particular Python file.
References: [#6147](http://www.sqlalchemy.org/trac/ticket/6147)
[mypy] [bug] Fixed issue where the Mypy plugin would fail to interpret the "collection_class" of a relationship if it were a callable and not a class. Also improved type matching and error reporting for collection-oriented relationships.
References: [#6205](http://www.sqlalchemy.org/trac/ticket/6205)
## asyncio
[asyncio] [usecase] [postgresql] Added accessors .sqlstate and synonym .pgcode to the .orig attribute of the SQLAlchemy exception class raised by the asyncpg DBAPI adapter, that is, the intermediary exception object that wraps on top of that raised by the asyncpg library itself, but below the level of the SQLAlchemy dialect.
References: [#6199](http://www.sqlalchemy.org/trac/ticket/6199)
…will be switched to True in a future version. A deprecation warning is raise if this flag is not specified and the passed enum contains aliases.
Released: April 2, 2021
[orm] [bug] [regression] Fixed regression where the _orm.joinedload() loader strategy would
not successfully joinedload to a mapper that is mapper against a
CTE construct.
References: #6172
[orm] [bug] [regression] Scaled back the warning message added in #5171 to not warn for overlapping columns in an inheritance scenario where a particular relationship is local to a subclass and therefore does not represent an overlap.
References: #6171
[sql] [bug] [postgresql] Fixed bug in new _functions.FunctionElement.render_derived() feature
where column names rendered out explicitly in the alias SQL would not have
proper quoting applied for case sensitive names and other non-alphanumeric
names.
References: #6183
[sql] [bug] [regression] Fixed regression where use of the Operators.in_() method with a
_sql.Select object against a non-table-bound column would produce
an AttributeError, or more generally using a _sql.ScalarSelect
that has no datatype in a binary expression would produce invalid state.
References: #6181
[sql] [bug] Added a new flag to the _engine.Dialect class called
_engine.Dialect.supports_statement_cache. This flag now needs to be present
directly on a dialect class in order for SQLAlchemy's
query cache <sql_caching> to take effect for that dialect. The
rationale is based on discovered issues such as #6173 revealing
that dialects which hardcode literal values from the compiled statement,
often the numerical parameters used for LIMIT / OFFSET, will not be
compatible with caching until these dialects are revised to use the
parameters present in the statement only. For third party dialects where
this flag is not applied, the SQL logging will show the message "dialect
does not support caching", indicating the dialect should seek to apply this
flag once they have verified that no per-statement literal values are being
rendered within the compilation phase.
References: #6184
[schema] [bug] Introduce a new parameter _types.Enum.omit_aliases in
_types.Enum type allow filtering aliases when using a pep435 Enum.
Previous versions of SQLAlchemy kept aliases in all cases, creating
database enum type with additional states, meaning that they were treated
as different values in the db. For backward compatibility this flag
defaults to False in the 1.4 series, but will be switched to True
in a future version. A deprecation warning is raise if this flag is not
specified and the passed enum contains aliases.
References: #6146
[mypy] [bug] Fixed issue in mypy plugin where newly added support for
_orm.as_declarative() needed to more fully add the
DeclarativeMeta class to the mypy interpreter's state so that it does
not result in a name not found error; additionally improves how global
names are setup for the plugin including the Mapped name.
References: #sqlalchemy/sqlalchemy2-stubs/#14
[asyncio] [bug] Fixed issue where the asyncio extension could not be loaded
if running Python 3.6 with the backport library of
contextvars installed.
References: #6166
[postgresql] [bug] [regression] Fixed regression caused by #6023 where the PostgreSQL cast
operator applied to elements within an _types.ARRAY when using
psycopg2 would fail to use the correct type in the case that the datatype
were also embedded within an instance of the _types.Variant
adapter.
Additionally, repairs support for the correct CREATE TYPE to be emitted
when using a Variant(ARRAY(some_schema_type)).
This change is also backported to: 1.3.25
References: #6182
[postgresql] [bug] Fixed typo in the fix for #6099 released in 1.4.4 that completely prevented this change from working correctly, i.e. the error message did not match what was actually emitted by pg8000.
References: #6099
[postgresql] [bug] Fixed issue where the PostgreSQL PGInspector, when generated
against an _engine.Engine, would fail for .get_enums(),
.get_view_names(), .get_foreign_table_names() and
.get_table_oid() when used against a "future" style engine and not the
connection directly.
References: #6170
[mysql] [bug] [regression] Fixed regression in the MySQL dialect where the reflection query used to detect if a table exists would fail on very old MySQL 5.0 and 5.1 versions.
References: #6163
[mssql] [bug] Fixed a regression in MSSQL 2012+ that prevented the order by clause
to be rendered when offset=0 is used in a subquery.
References: #6163
[oracle] [bug] [regression] Fixed critical regression where the Oracle compiler would not maintain the correct parameter values in the LIMIT/OFFSET for a select due to a caching issue.
References: #6173
…entrypoints in order to accommodate for some deprecation changes.
Released: March 30, 2021
[orm] [bug] Fixed critical issue in the new _orm.PropComparator.and_() feature
where loader strategies that emit secondary SELECT statements such as
_orm.selectinload() and _orm.lazyload() would fail to
accommodate for bound parameters in the user-defined criteria in terms of
the current statement being executed, as opposed to the cached statement,
causing stale bound values to be used.
This also adds a warning for the case where an object that uses
_orm.lazyload() in conjunction with _orm.PropComparator.and_()
is attempted to be serialized; the loader criteria cannot reliably
be serialized and deserialized and eager loading should be used for this
case.
References: #6139
[orm] [bug] [regression] Fixed missing method _orm.Session.get() from the
_orm.ScopedSession interface.
References: #6144
[engine] [usecase] Modified the context manager used by _engine.Transaction so that
an "already detached" warning is not emitted by the ending of the context
manager itself, if the transaction were already manually rolled back inside
the block. This applies to regular transactions, savepoint transactions,
and legacy "marker" transactions. A warning is still emitted if the
.rollback() method is called explicitly more than once.
References: #6155
[engine] [bug] Repair wrong arguments to exception handling method in CursorResult.
References: #6138
[postgresql] [bug] [reflection] Fixed issue in PostgreSQL reflection where a column expressing "NOT NULL" will supersede the nullability of a corresponding domain.
This change is also backported to: 1.3.24
References: #6161
[postgresql] [bug] Modified the is_disconnect() handler for the pg8000 dialect, which now
accommodates for a new InterfaceError emitted by pg8000 1.19.0. Pull
request courtesy Hamdi Burak Usul.
References: #6099
importlib_metadata library for loading
setuptools entrypoints in order to accommodate for some deprecation
changes.[orm] [bug] Fixed a bug where python 2.7.5 (default on CentOS 7) wasn't able to import sqlalchemy, because on this version of Python exec "statement"
Released: March 25, 2021
[orm] [bug] Fixed a bug where python 2.7.5 (default on CentOS 7) wasn't able to import
sqlalchemy, because on this version of Python exec "statement" and
exec("statement") do not behave the same way. The compatibility
exec_() function was used instead.
References: #6069
[orm] [bug] Fixed bug where ORM queries using a correlated subquery in conjunction with
_orm.column_property() would fail to correlate correctly to an
enclosing subquery or to a CTE when _sql.Select.correlate_except()
were used in the property to control correlation, in cases where the
subquery contained the same selectables as ones within the correlated
subquery that were intended to not be correlated.
References: #6060
[orm] [bug] Fixed bug where combinations of the new "relationship with criteria" feature could fail in conjunction with features that make use of the new "lambda SQL" feature, including loader strategies such as selectinload and lazyload, for more complicated scenarios such as polymorphic loading.
References: #6131
[orm] [bug] Repaired support so that the _sql.ClauseElement.params() method can
work correctly with a _sql.Select object that includes joins
across ORM relationship structures, which is a new feature in 1.4.
References: #6124
[orm] [bug] Fixed issue where a "removed in 2.0" warning were generated internally by the relationship loader mechanics.
References: #6115
[orm] [declarative] [bug] [regression] Fixed regression where the .metadata attribute on a per class level
would not be honored, breaking the use case of per-class-hierarchy
schema.MetaData for abstract declarative classes and mixins.
References: #6128
[engine] [bug] [regression] Restored the _engine.ResultProxy name back to the
sqlalchemy.engine namespace. This name refers to the
_engine.LegacyCursorResult object.
References: #6119
[mypy] [bug] Added support for the Mypy extension to correctly interpret a declarative
base class that's generated using the _orm.as_declarative() function
as well as the _orm.registry.as_declarative_base() method.
[mypy] [bug] Fixed bug in Mypy plugin where the Python type detection
for the _types.Boolean column type would produce
an exception; additionally implemented support for _types.Enum,
including detection of a string-based enum vs. use of Python enum.Enum.
References: #6109
[postgresql] [bug] [reflection] Fixed reflection of identity columns in tables with mixed case names in PostgreSQL.
References: #6129
[sqlite] [feature] [asyncio] Added support for the aiosqlite database driver for use with the SQLAlchemy asyncio extension.
References: #5920
[sqlite] [bug] [regression] Repaired the pysqlcipher dialect to connect correctly which had
regressed in 1.4, and added test + CI support to maintain the driver
in working condition. The dialect now imports the sqlcipher3 module
for Python 3 by default before falling back to pysqlcipher3 which
is documented as now being unmaintained.
References: #5848
[orm] [usecase] [dataclasses] Added support for the _orm.declared_attr object to work in the context of dataclass fields.
Released: March 19, 2021
[orm] [usecase] [dataclasses] Added support for the _orm.declared_attr object to work in the
context of dataclass fields.
References: #6100
[orm] [bug] [dataclasses] Fixed issue in new ORM dataclasses functionality where dataclass fields on an abstract base or mixin that contained column or other mapping constructs would not be mapped if they also included a "default" key within the dataclasses.field() object.
References: #6093
[orm] [bug] [regression] Fixed regression where the _orm.Query.selectable accessor, which is
a synonym for _orm.Query.__clause_element__(), got removed, it's now
restored.
References: #6088
[orm] [bug] [regression] Fixed regression where use of an unnamed SQL expression such as a SQL function would raise a column targeting error if the query itself were using joinedload for an entity and was also being wrapped in a subquery by the joinedload eager loading process.
References: #6086
[orm] [bug] [regression] Fixed regression where the _orm.Query.filter_by() method would fail
to locate the correct source entity if the _orm.Query.join() method
had been used targeting an entity without any kind of ON clause.
References: #6092
[orm] [bug] [regression] Fixed regression where the SQL compilation of a Function would
not work correctly if the object had been "annotated", which is an internal
memoization process used mostly by the ORM. In particular it could affect
ORM lazy loads which make greater use of this feature in 1.4.
References: #6095
[orm] [bug] Fixed regression where the ConcreteBase would fail to map at all
when a mapped column name overlapped with the discriminator column name,
producing an assertion error. The use case here did not function correctly
in 1.3 as the polymorphic union would produce a query that ignored the
discriminator column entirely, while emitting duplicate column warnings. As
1.4's architecture cannot easily reproduce this essentially broken behavior
of 1.3 at the select() level right now, the use case now raises an
informative error message instructing the user to use the
.ConcreteBase._concrete_discriminator_name attribute to resolve the
conflict. To assist with this configuration,
.ConcreteBase._concrete_discriminator_name may be placed on the base
class only where it will be automatically used by subclasses; previously
this was not the case.
References: #6090
sqlalchemy.engine.reflection. This
ensures that the base _reflection.Inspector class is properly
registered so that _sa.inspect() works for third party dialects that
don't otherwise import this package.[sql] [bug] [regression] Fixed issue where using a func that includes dotted packagenames would
fail to be cacheable by the SQL caching system due to a Python list of
names that needed to be a tuple.
References: #6101
[sql] [bug] [regression] Fixed regression in the _sql.case() construct, where the "dictionary"
form of argument specification failed to work correctly if it were passed
positionally, rather than as a "whens" keyword argument.
References: #6097
[mypy] [bug] Fixed issue in MyPy extension which crashed on detecting the type of a
Column if the type were given with a module prefix like
sa.Integer().
References: #sqlalchemy/sqlalchemy2-stubs/2
[postgresql] [usecase] Rename the column name used by a reflection query that used a reserved word in some postgresql compatible databases.
References: #6982
[orm] [bug] [regression] Fixed regression where producing a Core expression construct such as _sql.select() using ORM entities would eagerly configure
Released: March 17, 2021
[orm] [bug] [regression] Fixed regression where producing a Core expression construct such as
_sql.select() using ORM entities would eagerly configure the mappers,
in an effort to maintain compatibility with the _orm.Query object
which necessarily does this to support many backref-related legacy cases.
However, core _sql.select() constructs are also used in mapper
configurations and such, and to that degree this eager configuration is
more of an inconvenience, so eager configure has been disabled for the
_sql.select() and other Core constructs in the absence of ORM loading
types of functions such as _orm.Load.
The change maintains the behavior of _orm.Query so that backwards
compatibility is maintained. However, when using a _sql.select() in
conjunction with ORM entities, a "backref" that isn't explicitly placed on
one of the classes until mapper configure time won't be available unless
_orm.configure_mappers() or the newer _orm.registry.configure()
has been called elsewhere. Prefer using
_orm.relationship.back_populates for more explicit relationship
configuration which does not have the eager configure requirement.
References: #6066
[orm] [bug] [regression] Fixed a critical regression in the relationship lazy loader where the SQL criteria used to fetch a related many-to-one object could go stale in relation to other memoized structures within the loader if the mapper had configuration changes, such as can occur when mappers are late configured or configured on demand, producing a comparison to None and returning no object. Huge thanks to Alan Hamlett for their help tracking this down late into the night.
References: #6055
[orm] [bug] [regression] Fixed regression where the _orm.Query.exists() method would fail to
create an expression if the entity list of the _orm.Query were
an arbitrary SQL column expression.
References: #6076
[orm] [bug] [regression] Fixed regression where calling upon _orm.Query.count() in conjunction
with a loader option such as _orm.joinedload() would fail to ignore
the loader option. This is a behavior that has always been very specific to
the _orm.Query.count() method; an error is normally raised if a given
_orm.Query has options that don't apply to what it is returning.
References: #6052
[orm] [bug] [regression] Fixed regression in _orm.Session.identity_key(), including that the
method and related methods were not covered by any unit test as well as
that the method contained a typo preventing it from functioning correctly.
References: #6067
[orm] [declarative] [bug] [regression] Fixed bug where user-mapped classes that contained an attribute named
"registry" would cause conflicts with the new registry-based mapping system
when using DeclarativeMeta. While the attribute remains
something that can be set explicitly on a declarative base to be
consumed by the metaclass, once located it is placed under a private
class variable so it does not conflict with future subclasses that use
the same name for other purposes.
References: #6054
[engine] [bug] [regression] The Python namedtuple() has the behavior such that the names count
and index will be served as tuple values if the named tuple includes
those names; if they are absent, then their behavior as methods of
collections.abc.Sequence is maintained. Therefore the
_result.Row and _result.LegacyRow classes have been fixed
so that they work in this same way, maintaining the expected behavior for
database rows that have columns named "index" or "count".
References: #6074
[mssql] [bug] [regression] Fixed regression where a new setinputsizes() API that's available for
pyodbc was enabled, which is apparently incompatible with pyodbc's
fast_executemany() mode in the absence of more accurate typing information,
which as of yet is not fully implemented or tested. The pyodbc dialect and
connector has been modified so that setinputsizes() is not used at all
unless the parameter use_setinputsizes is passed to the dialect, e.g.
via _sa.create_engine(), at which point its behavior can be
customized using the DialectEvents.do_setinputsizes() hook.
References: #6058
[bug] [regression] Added back items and values to ColumnCollection class.
The regression was introduced while adding support for duplicate
columns in from clauses and selectable in ticket #4753.
References: #6068
[schema] [bug] Deprecated all schema-level .copy() methods and renamed to _copy(). These are not standard Python "copy()" methods as they typically re…
Released: March 15, 2021
[orm] [bug] Removed very old warning that states that passive_deletes is not intended for many-to-one relationships. While it is likely that in many cases placing this parameter on a many-to-one relationship is not what was intended, there are use cases where delete cascade may want to be disallowed following from such a relationship.
This change is also backported to: 1.3.24
References: #5983
[orm] [bug] Fixed issue where the process of joining two tables could fail if one of
the tables had an unrelated, unresolvable foreign key constraint which
would raise _exc.NoReferenceError within the join process, which
nonetheless could be bypassed to allow the join to complete. The logic
which tested the exception for significance within the process would make
assumptions about the construct which would fail.
This change is also backported to: 1.3.24
References: #5952
[orm] [bug] Fixed issue where the _mutable.MutableComposite construct could be
placed into an invalid state when the parent object was already loaded, and
then covered by a subsequent query, due to the composite properties'
refresh handler replacing the object with a new one not handled by the
mutable extension.
This change is also backported to: 1.3.24
References: #6001
[orm] [bug] Fixed regression where the _orm.relationship.query_class
parameter stopped being functional for "dynamic" relationships. The
AppenderQuery remains dependent on the legacy _orm.Query
class; users are encouraged to migrate from the use of "dynamic"
relationships to using _orm.with_parent() instead.
References: #5981
[orm] [bug] [regression] Fixed regression where _orm.Query.join() would produce no effect if
the query itself as well as the join target were against a
_schema.Table object, rather than a mapped class. This was part of
a more systemic issue where the legacy ORM query compiler would not be
correctly used from a _orm.Query if the statement produced had not
ORM entities present within it.
References: #6003
[orm] [bug] [asyncio] The API for _asyncio.AsyncSession.delete() is now an awaitable;
this method cascades along relationships which must be loaded in a
similar manner as the _asyncio.AsyncSession.merge() method.
References: #5998
[orm] [bug] The unit of work process now turns off all "lazy='raise'" behavior altogether when a flush is proceeding. While there are areas where the UOW is sometimes loading things that aren't ultimately needed, the lazy="raise" strategy is not helpful here as the user often does not have much control or visibility into the flush process.
References: #5984
[engine] [bug] Fixed bug where the "schema_translate_map" feature failed to be taken into
account for the use case of direct execution of
_schema.DefaultGenerator objects such as sequences, which included
the case where they were "pre-executed" in order to generate primary key
values when implicit_returning was disabled.
This change is also backported to: 1.3.24
References: #5929
[engine] [bug] Improved engine logging to note ROLLBACK and COMMIT which is logged while the DBAPI driver is in AUTOCOMMIT mode. These ROLLBACK/COMMIT are library level and do not have any effect when AUTOCOMMIT is in effect, however it's still worthwhile to log as these indicate where SQLAlchemy sees the "transaction" demarcation.
References: #6002
[engine] [bug] [regression] Fixed a regression where the "reset agent" of the connection pool wasn't
really being utilized by the _engine.Connection when it were
closed, and also leading to a double-rollback scenario that was somewhat
wasteful. The newer architecture of the engine has been updated so that
the connection pool "reset-on-return" logic will be skipped when the
_engine.Connection explicitly closes out the transaction before
returning the pool to the connection.
References: #6004
[sql] [change] Altered the compilation for the CTE construct so that a string is
returned representing the inner SELECT statement if the CTE is
stringified directly, outside of the context of an enclosing SELECT; This
is the same behavior of _sql.FromClause.alias() and
_sql.Select.subquery(). Previously, a blank string would be
returned as the CTE is normally placed above a SELECT after that SELECT has
been generated, which is generally misleading when debugging.
[sql] [bug] [sqlite] Fixed issue where the CHECK constraint generated by _types.Boolean
or _types.Enum would fail to render the naming convention
correctly after the first compilation, due to an unintended change of state
within the name given to the constraint. This issue was first introduced in
0.9 in the fix for issue #3067, and the fix revises the approach taken at
that time which appears to have been more involved than what was needed.
This change is also backported to: 1.3.24
References: #6007
[sql] [bug] Fixed bug where the "percent escaping" feature that occurs with dialects
that use the "format" or "pyformat" bound parameter styles was not enabled
for the _sql.Operators.op() and _sql.custom_op constructs,
for custom operators that use percent signs. The percent sign will now be
automatically doubled based on the paramstyle as necessary.
References: #6016
[sql] [bug] [regression] Fixed regression where the "unsupported compilation error" for unknown datatypes would fail to raise correctly.
References: #5979
[sql] [bug] [regression] Fixed regression where usage of the standalone _sql.distinct() used
in the form of being directly SELECTed would fail to be locatable in the
result set by column identity, which is how the ORM locates columns. While
standalone _sql.distinct() is not oriented towards being directly
SELECTed (use _sql.select.distinct() for a regular
SELECT DISTINCT..) , it was usable to a limited extent in this way
previously (but wouldn't work in subqueries, for example). The column
targeting for unary expressions such as "DISTINCT <col>" has been improved
so that this case works again, and an additional improvement has been made
so that usage of this form in a subquery at least generates valid SQL which
was not the case previously.
The change additionally enhances the ability to target elements in
row._mapping based on SQL expression objects in ORM-enabled
SELECT statements, including whether the statement was invoked by
connection.execute() or session.execute().
References: #6008
[schema] [bug] Repaired / implemented support for primary key constraint naming
conventions that use column names/keys/etc as part of the convention. In
particular, this includes that the PrimaryKeyConstraint object
that's automatically associated with a schema.Table will update
its name as new primary key _schema.Column objects are added to
the table and then to the constraint. Internal failure modes related to
this constraint construction process including no columns present, no name
present or blank name present are now accommodated.
This change is also backported to: 1.3.24
References: #5919
[schema] [bug] Deprecated all schema-level .copy() methods and renamed to
_copy(). These are not standard Python "copy()" methods as they
typically rely upon being instantiated within particular contexts
which are passed to the method as optional keyword arguments. The
_schema.Table.tometadata() method is the public API that provides
copying for _schema.Table objects.
References: #5953
[mypy] [feature] Rudimentary and experimental support for Mypy has been added in the form of a new plugin, which itself depends on new typing stubs for SQLAlchemy. The plugin allows declarative mappings in their standard form to both be compatible with Mypy as well as to provide typing support for mapped classes and instances.
References: #4609
[postgresql] [usecase] [asyncio] [mysql] Added an asyncio.Lock() within SQLAlchemy's emulated DBAPI cursor,
local to the connection, for the asyncpg and aiomysql dialects for the
scope of the cursor.execute() and cursor.executemany() methods. The
rationale is to prevent failures and corruption for the case where the
connection is used in multiple awaitables at once.
While this use case can also occur with threaded code and non-asyncio dialects, we anticipate this kind of use will be more common under asyncio, as the asyncio API is encouraging of such use. It's definitely better to use a distinct connection per concurrent awaitable however as concurrency will not be achieved otherwise.
For the asyncpg dialect, this is so that the space between
the call to prepare() and fetch() is prevented from allowing
concurrent executions on the connection from causing interface error
exceptions, as well as preventing race conditions when starting a new
transaction. Other PostgreSQL DBAPIs are threadsafe at the connection level
so this intends to provide a similar behavior, outside the realm of server
side cursors.
For the aiomysql dialect, the mutex will provide safety such that the statement execution and the result set fetch, which are two distinct steps at the connection level, won't get corrupted by concurrent executions on the same connection.
References: #5967
[postgresql] [bug] Fixed issue where using _postgresql.aggregate_order_by would
return ARRAY(NullType) under certain conditions, interfering with
the ability of the result object to return data correctly.
This change is also backported to: 1.3.24
References: #5989
[mssql] [bug] Fix a reflection error for MSSQL 2005 introduced by the reflection of filtered indexes.
References: #5919
[usecase] [ext] Add new parameter
_automap.AutomapBase.prepare.reflection_options
to allow passing of _schema.MetaData.reflect() options like only
or dialect-specific reflection options like oracle_resolve_synonyms.
References: #5942
[bug] [ext] The sqlalchemy.ext.mutable extension now tracks the "parents"
collection using the InstanceState associated with objects,
rather than the object itself. The latter approach required that the object
be hashable so that it can be inside of a WeakKeyDictionary, which goes
against the behavioral contract of the ORM overall which is that ORM mapped
objects do not need to provide any particular kind of __hash__() method
and that unhashable objects are supported.
References: #6020
[orm] [feature] The ORM used in 2.0 style can now return ORM objects from the rows returned by an UPDATE..RETURNING or INSERT..RETURNING statement, by
# 1.4.0b3
Released: February 15, 2021 ## orm
[orm] [feature] The ORM used in 2.0 style can now return ORM objects from the rows returned by an UPDATE..RETURNING or INSERT..RETURNING statement, by supplying the construct to _sql.Select.from_statement() in an ORM context.
Unknown interpreted text role "term".
[orm] [bug] Fixed issue in new 1.4/2.0 style ORM queries where a statement-level label style would not be preserved in the keys used by result rows; this has been applied to all combinations of Core/ORM columns / session vs. connection etc. so that the linkage from statement to result row is the same in all cases. As part of this change, the labeling of column expressions in rows has been improved to retain the original name of the ORM attribute even if used in a subquery.
References: [#5933](http://www.sqlalchemy.org/trac/ticket/5933)
## engine
[engine] [bug] [postgresql] Continued with the improvement made as part of [#5653](http://www.sqlalchemy.org/trac/ticket/5653) to further support bound parameter names, including those generated against column names, for names that include colons, parenthesis, and question marks, as well as improved test support, so that bound parameter names even if they are auto-derived from column names should have no problem including for parenthesis in psycopg2's "pyformat" style.
As part of this change, the format used by the asyncpg DBAPI adapter (which is local to SQLAlchemy's asyncpg dialect) has been changed from using "qmark" paramstyle to "format", as there is a standard and internally supported SQL string escaping style for names that use percent signs with "format" style (i.e. to double percent signs), as opposed to names that use question marks with "qmark" style (where an escaping system is not defined by pep-249 or Python).
References: [#5941](http://www.sqlalchemy.org/trac/ticket/5941)
## sql
[sql] [usecase] [postgresql] [sqlite] Enhance set_ keyword of OnConflictDoUpdate to accept a ColumnCollection, such as the .c. collection from a Selectable, or the .excluded contextual object.
References: [#5939](http://www.sqlalchemy.org/trac/ticket/5939)
[sql] [bug] Fixed bug where the "cartesian product" assertion was not correctly accommodating for joins between tables that relied upon the use of LATERAL to connect from a subquery to another subquery in the enclosing context.
References: [#5924](http://www.sqlalchemy.org/trac/ticket/5924)
[sql] [bug] Fixed 1.4 regression where the _functions.Function.in_() method was not covered by tests and failed to function properly in all cases.
References: [#5934](http://www.sqlalchemy.org/trac/ticket/5934)
[sql] [bug] Fixed regression where use of an arbitrary iterable with the _sql.select() function was not working, outside of plain lists. The forwards/backwards compatibility logic here now checks for a wider range of incoming "iterable" types including that a .c collection from a selectable can be passed directly. Pull request compliments of Oliver Rice.
References: [#5935](http://www.sqlalchemy.org/trac/ticket/5935)
Additionally, this flag is legacy as it only makes sense for the _orm.Query object and not 2.0 style execution. a deprecation warning is emitted when…
Released: February 3, 2021
[platform] [performance] Adjusted some elements related to internal class production at import time which added significant latency to the time spent to import the library vs. that of 1.3. The time is now about 20-30% slower than 1.3 instead of 200%.
References: #5681
[orm] [usecase] Added _orm.ORMExecuteState.bind_mapper and
_orm.ORMExecuteState.all_mappers accessors to
_orm.ORMExecuteState event object, so that handlers can respond to
the target mapper and/or mapped class or classes involved in an ORM
statement execution.
[orm] [usecase] [asyncio] Added _asyncio.AsyncSession.scalar(),
_asyncio.AsyncSession.get() as well as support for
_orm.sessionmaker.begin() to work as an async context manager with
_asyncio.AsyncSession. Also added
_asyncio.AsyncSession.in_transaction() accessor.
[orm] [changed] Mapper "configuration", which occurs within the
_orm.configure_mappers() function, is now organized to be on a
per-registry basis. This allows for example the mappers within a certain
declarative base to be configured, but not those of another base that is
also present in memory. The goal is to provide a means of reducing
application startup time by only running the "configure" process for sets
of mappers that are needed. This also adds the
_orm.registry.configure() method that will run configure for the
mappers local in a particular registry only.
References: #5897
[orm] [bug] Added a comprehensive check and an informative error message for the case
where a mapped class, or a string mapped class name, is passed to
_orm.relationship.secondary. This is an extremely common error
which warrants a clear message.
Additionally, added a new rule to the class registry resolution such that
with regards to the _orm.relationship.secondary parameter, if a
mapped class and its table are of the identical string name, the
Table will be favored when resolving this parameter. In all
other cases, the class continues to be favored if a class and table
share the identical name.
This change is also backported to: 1.3.21
References: #5774
[orm] [bug] Fixed bug involving the restore_load_context option of ORM events such
as _ormevent.InstanceEvents.load() such that the flag would not be
carried along to subclasses which were mapped after the event handler were
first established.
This change is also backported to: 1.3.21
References: #5737
[orm] [bug] [regression] Fixed issue in new _orm.Session similar to that of the
_engine.Connection where the new "autobegin" logic could be
tripped into a re-entrant (recursive) state if SQL were executed within the
SessionEvents.after_transaction_create() event hook.
References: #5845
[orm] [bug] [unitofwork] Improved the unit of work topological sorting system such that the toplogical sort is now deterministic based on the sorting of the input set, which itself is now sorted at the level of mappers, so that the same inputs of affected mappers should produce the same output every time, among mappers / tables that don't have any dependency on each other. This further reduces the chance of deadlocks as can be observed in a flush that UPDATEs among multiple, unrelated tables such that row locks are generated.
References: #5735
[orm] [bug] Fixed regression where the Bundle.single_entity flag would
take effect for a Bundle even though it were not set.
Additionally, this flag is legacy as it only makes sense for the
_orm.Query object and not 2.0 style execution. a deprecation
warning is emitted when used with new-style execution.
References: #5702
[orm] [bug] Fixed regression where creating an _orm.aliased construct against
a plain selectable and including a name would raise an assertionerror.
References: #5750
[orm] [bug] Related to the fixes for the lambda criteria system within Core, within the
ORM implemented a variety of fixes for the
_orm.with_loader_criteria() feature as well as the
_orm.SessionEvents.do_orm_execute() event handler that is often
used in conjunction [ticket:5760]:
- fixed issue where `_orm.with_loader_criteria()` function would fail
if the given entity or base included non-mapped mixins in its descending
class hierarchy [ticket:5766]
- The `_orm.with_loader_criteria()` feature is now unconditionally
disabled for the case of ORM "refresh" operations, including loads
of deferred or expired column attributes as well as for explicit
operations like `_orm.Session.refresh()`. These loads are necessarily
based on primary key identity where addiional WHERE criteria is
never appropriate. [ticket:5762]
- Added new attribute `_orm.ORMExecuteState.is_column_load` to indicate
that a `_orm.SessionEvents.do_orm_execute()` handler that a particular
operation is a primary-key-directed column attribute load, where additional
criteria should not be added. The `_orm.with_loader_criteria()`
function as above ignores these in any case now. [ticket:5761]
- Fixed issue where the `_orm.ORMExecuteState.is_relationship_load`
attribute would not be set correctly for many lazy loads as well as all
selectinloads. The flag is essential in order to test if options should
be added to statements or if they would already have been propagated via
relationship loads. [ticket:5764]
[orm] [bug] Fixed 1.4 regression where the use of _orm.Query.having() in
conjunction with queries with internally adapted SQL elements (common in
inheritance scenarios) would fail due to an incorrect function call. Pull
request courtesy esoh.
References: #5781
[orm] [bug] Fixed an issue where the API to create a custom executable SQL construct
using the sqlalchemy.ext.compiles extension according to the
documentation that's been up for many years would no longer function if
only Executable, ClauseElement were used as the base classes,
additional classes were needed if wanting to use
_orm.Session.execute(). This has been resolved so that those extra
classes aren't needed.
[orm] [bug] [regression] Fixed ORM unit of work regression where an errant "assert primary_key"
statement interferes with primary key generation sequences that don't
actually consider the columns in the table to use a real primary key
constraint, instead using _orm.mapper.primary_key to establish
certain columns as "primary".
References: #5867
[orm] [declarative] [feature] Added an alternate resolution scheme to Declarative that will extract the SQLAlchemy column or mapped property from the "metadata" dictionary of a dataclasses.Field object. This allows full declarative mappings to be combined with dataclass fields.
References: #5745
[engine] [feature] Dialect-specific constructs such as
_postgresql.Insert.on_conflict_do_update() can now stringify in-place
without the need to specify an explicit dialect object. The constructs,
when called upon for str(), print(), etc. now have internal
direction to call upon their appropriate dialect rather than the
"default"dialect which doesn't know how to stringify these. The approach
is also adapted to generic schema-level create/drop such as
_schema.AddConstraint, which will adapt its stringify dialect to
one indicated by the element within it, such as the
_postgresql.ExcludeConstraint object.
[engine] [feature] Added new execution option
_engine.Connection.execution_options.logging_token. This option
will add an additional per-message token to log messages generated by the
_engine.Connection as it executes statements. This token is not
part of the logger name itself (that part can be affected using the
existing _sa.create_engine.logging_name parameter), so is
appropriate for ad-hoc connection use without the side effect of creating
many new loggers. The option can be set at the level of
_engine.Connection or _engine.Engine.
References: #5911
[engine] [bug] [sqlite] Fixed bug in the 2.0 "future" version of Engine where emitting
SQL during the EngineEvents.begin() event hook would cause a
re-entrant (recursive) condition due to autobegin, affecting among other
things the recipe documented for SQLite to allow for savepoints and
serializable isolation support.
References: #5845
[engine] [bug] [oracle] [postgresql] Adjusted the "setinputsizes" logic relied upon by the cx_Oracle, asyncpg
and pg8000 dialects to support a TypeDecorator that includes
an override the TypeDecorator.get_dbapi_type() method.
[engine] [bug] Added the "future" keyword to the list of words that are known by the
_sa.engine_from_config() function, so that the values "true" and
"false" may be configured as "boolean" values when using a key such
as sqlalchemy.future = true or sqlalchemy.future = false.
[sql] [feature] Implemented support for "table valued functions" along with additional syntaxes supported by PostgreSQL, one of the most commonly requested features. Table valued functions are SQL functions that return lists of values or rows, and are prevalent in PostgreSQL in the area of JSON functions, where the "table value" is commonly referred towards as the "record" datatype. Table valued functions are also supported by Oracle and SQL Server.
Features added include:
- the `_functions.FunctionElement.table_valued()` modifier that creates a table-like
selectable object from a SQL function
- A `_sql.TableValuedAlias` construct that renders a SQL function
as a named table
- Support for PostgreSQL's special "derived column" syntax that includes
column names and sometimes datatypes, such as for the
`json_to_recordset` function, using the
`_sql.TableValuedAlias.render_derived()` method.
- Support for PostgreSQL's "WITH ORDINALITY" construct using the
`_functions.FunctionElement.table_valued.with_ordinality` parameter
- Support for selection FROM a SQL function as column-valued scalar, a
syntax supported by PostgreSQL and Oracle, via the
`_functions.FunctionElement.column_valued()` method
- A way to SELECT a single column from a table-valued expression without
using a FROM clause via the `_functions.FunctionElement.scalar_table_valued()`
method.
References: #3566
[sql] [usecase] Multiple calls to "returning", e.g. _sql.Insert.returning(),
may now be chained to add new columns to the RETURNING clause.
References: #5695
[sql] [usecase] Added _sql.Select.outerjoin_from() method to complement
_sql.Select.join_from().
[sql] [usecase] Adjusted the "literal_binds" feature of _sql.Compiler to render
NULL for a bound parameter that has None as the value, either
explicitly passed or omitted. The previous error message "bind parameter
without a renderable value" is removed, and a missing or None value
will now render NULL in all cases. Previously, rendering of NULL was
starting to happen for DML statements due to internal refactorings, but was
not explicitly part of test coverage, which it now is.
While no error is raised, when the context is within that of a column comparison, and the operator is not "IS"/"IS NOT", a warning is emitted that this is not generally useful from a SQL perspective.
References: #5888
[sql] [bug] Fixed issue in new _sql.Select.join() method where chaining from the
current JOIN wasn't looking at the right state, causing an expression like
"FROM a JOIN b <onclause>, b JOIN c <onclause>" rather than
"FROM a JOIN b <onclause> JOIN c <onclause>".
References: #5858
[sql] [bug] Deprecation warnings are emitted under "SQLALCHEMY_WARN_20" mode when
passing a plain string to _orm.Session.execute().
References: #5754
[sql] [bug] [orm] A wide variety of fixes to the "lambda SQL" feature introduced at
engine_lambda_caching have been implemented based on user feedback,
with an emphasis on its use within the _orm.with_loader_criteria()
feature where it is most prominently used [ticket:5760]:
- fixed issue where boolean True/False values referred towards in the
closure variables of the lambda would cause failures [ticket:5763]
- Repaired a non-working detection for Python functions embedded in the
lambda that produce bound values; this case is likely not supportable
so raises an informative error, where the function should be invoked
outside the lambda itself. New documentation has been added to
further detail this behavior. [ticket:5770]
- The lambda system by default now rejects the use of non-SQL elements
within the closure variables of the lambda entirely, where the error
suggests the two options of either explicitly ignoring closure variables
that are not SQL parameters, or specifying a specific set of values to be
considered as part of the cache key based on hash value. This critically
prevents the lambda system from assuming that arbitrary objects within
the lambda's closure are appropriate for caching while also refusing to
ignore them by default, preventing the case where their state might
not be constant and have an impact on the SQL construct produced.
The error message is comprehensive and new documentation has been
added to further detail this behavior. [ticket:5765]
- Fixed support for the edge case where an `in_()` expression
against a list of SQL elements, such as `_sql.literal()` objects,
would fail to be accommodated correctly. [ticket:5768]
[sql] [bug] [mysql] [postgresql] [sqlite] An informative error message is now raised for a selected set of DML
methods (currently all part of _dml.Insert constructs) if they are
called a second time, which would implicitly cancel out the previous
setting. The methods altered include:
_sqlite.Insert.on_conflict_do_update,
_sqlite.Insert.on_conflict_do_nothing (SQLite),
_postgresql.Insert.on_conflict_do_update,
_postgresql.Insert.on_conflict_do_nothing (PostgreSQL),
_mysql.Insert.on_duplicate_key_update (MySQL)
References: #5169
[sql] [bug] Fixed issue in new _sql.Values construct where passing tuples of
objects would fall back to per-value type detection rather than making use
of the _schema.Column objects passed directly to
_sql.Values that tells SQLAlchemy what the expected type is. This
would lead to issues for objects such as enumerations and numpy strings
that are not actually necessary since the expected type is given.
References: #5785
[sql] [bug] Fixed issue where a RemovedIn20Warning would erroneously emit
when the .bind attribute were accessed internally on objects,
particularly when stringifying a SQL construct.
References: #5717
[sql] [bug] Properly render cycle=False and order=False as NO CYCLE and
NO ORDER in _sql.Sequence and _sql.Identity
objects.
References: #5722
[sql] Replace _orm.Query.with_labels() and
_sql.GenerativeSelect.apply_labels() with explicit getters and
setters _sql.GenerativeSelect.get_label_style() and
_sql.GenerativeSelect.set_label_style() to accommodate the three
supported label styles: :data:_sql.LABEL_STYLE_DISAMBIGUATE_ONLY,
:data:_sql.LABEL_STYLE_TABLENAME_PLUS_COL, and
:data:_sql.LABEL_STYLE_NONE.
Unknown interpreted text role "data".
Unknown interpreted text role "data".
Unknown interpreted text role "data".
In addition, for Core and "future style" ORM queries,
LABEL_STYLE_DISAMBIGUATE_ONLY is now the default label style. This
style differs from the existing "no labels" style in that labeling is
applied in the case of column name conflicts; with LABEL_STYLE_NONE, a
duplicate column name is not accessible via name in any case.
For cases where labeling is significant, namely that the .c collection
of a subquery is able to refer to all columns unambiguously, the behavior
of LABEL_STYLE_DISAMBIGUATE_ONLY is now sufficient for all
SQLAlchemy features across Core and ORM which involve this behavior.
Result set rows since SQLAlchemy 1.0 are usually aligned with column
constructs positionally.
For legacy ORM queries using _query.Query, the table-plus-column
names labeling style applied by LABEL_STYLE_TABLENAME_PLUS_COL
continues to be used so that existing test suites and logging facilities
see no change in behavior by default.
References: #4757
[schema] [feature] Added _types.TypeEngine.as_generic() to map dialect-specific types,
such as sqlalchemy.dialects.mysql.INTEGER, with the "best match"
generic SQLAlchemy type, in this case _types.Integer. Pull
request courtesy Andrew Hannigan.
References: #5659
[schema] [usecase] The _events.DDLEvents.column_reflect() event may now be applied to a
_schema.MetaData object where it will take effect for the
_schema.Table objects local to that collection.
References: #5712
[schema] [usecase] Added parameters _ddl.CreateTable.if_not_exists,
_ddl.CreateIndex.if_not_exists,
_ddl.DropTable.if_exists and
_ddl.DropIndex.if_exists to the _ddl.CreateTable,
_ddl.DropTable, _ddl.CreateIndex and
_ddl.DropIndex constructs which result in "IF NOT EXISTS" / "IF
EXISTS" DDL being added to the CREATE/DROP. These phrases are 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 Ramon Williams.
References: #2843
[schema] [changed] Altered the behavior of the _schema.Identity construct such that
when applied to a _schema.Column, it will automatically imply that
the value of _sql.Column.nullable should default to False,
in a similar manner as when the _sql.Column.primary_key
parameter is set to True. This matches the default behavior of all
supporting databases where IDENTITY implies NOT NULL. The
PostgreSQL backend is the only one that supports adding NULL to an
IDENTITY column, which is here supported by passing a True value
for the _sql.Column.nullable parameter at the same time.
References: #5775
[asyncio] [usecase] The AsyncEngine, AsyncConnection and
AsyncTransaction objects may be compared using Python == or
!=, which will compare the two given objects based on the "sync" object
they are proxying towards. This is useful as there are cases particularly
for AsyncTransaction where multiple instances of
AsyncTransaction can be proxying towards the same sync
_engine.Transaction, and are actually equivalent. The
AsyncConnection.get_transaction() method will currently return a new
proxying AsyncTransaction each time as the
AsyncTransaction is not otherwise statefully associated with its
originating AsyncConnection.
[asyncio] [bug] Adjusted the greenlet integration, which provides support for Python asyncio
in SQLAlchemy, to accommodate for the handling of Python contextvars
(introduced in Python 3.7) for greenlet versions greater than 0.4.17.
Greenlet version 0.4.17 added automatic handling of contextvars in a
backwards-incompatible way; we've coordinated with the greenlet authors to
add a preferred API for this in versions subsequent to 0.4.17 which is now
supported by SQLAlchemy's greenlet integration. For greenlet versions prior
to 0.4.17 no behavioral change is needed, version 0.4.17 itself is blocked
from the dependencies.
References: #5615
[asyncio] [bug] Implemented "connection-binding" for AsyncSession, the ability to
pass an AsyncConnection to create an AsyncSession.
Previously, this use case was not implemented and would use the associated
engine when the connection were passed. This fixes the issue where the
"join a session to an external transaction" use case would not work
correctly for the AsyncSession. Additionally, added methods
AsyncConnection.in_transaction(),
AsyncConnection.in_nested_transaction(),
AsyncConnection.get_transaction(),
AsyncConnection.get_nested_transaction() and
AsyncConnection.info attribute.
References: #5811
[asyncio] [bug] Fixed bug in asyncio connection pool where asyncio.TimeoutError would
be raised rather than exc.TimeoutError. Also repaired the
_sa.create_engine.pool_timeout parameter set to zero when using
the async engine, which previously would ignore the timeout and block
rather than timing out immediately as is the behavior with regular
QueuePool.
References: #5827
[asyncio] [bug] [pool] When using an asyncio engine, the connection pool will now detach and discard a pooled connection that is was not explicitly closed/returned to the pool when its tracking object is garbage collected, emitting a warning that the connection was not properly closed. As this operation occurs during Python gc finalizers, it's not safe to run any IO operations upon the connection including transaction rollback or connection close as this will often be outside of the event loop.
The AsyncAdaptedQueue used by default on async dpapis
should instantiate a queue only when it's first used
to avoid binding it to a possibly wrong event loop.
References: #5823
[asyncio] The SQLAlchemy async mode now detects and raises an informative
error when an non asyncio compatible :term:DBAPI is used.
Using a standard DBAPI with async SQLAlchemy will cause
it to block like any sync call, interrupting the executing asyncio
loop.
Unknown interpreted text role "term".
[postgresql] [usecase] Added new parameter _postgresql.ExcludeConstraint.ops to the
_postgresql.ExcludeConstraint object, to support operator class
specification with this constraint. Pull request courtesy Alon Menczer.
This change is also backported to: 1.3.21
References: #5604
[postgresql] [usecase] Added a read/write .autocommit attribute to the DBAPI-adaptation layer
for the asyncpg dialect. This so that when working with DBAPI-specific
schemes that need to use "autocommit" directly with the DBAPI connection,
the same .autocommit attribute which works with both psycopg2 as well
as pg8000 is available.
[postgresql] [changed] Fixed issue where the psycopg2 dialect would silently pass the
use_native_unicode=False flag without actually having any effect under
Python 3, as the psycopg2 DBAPI uses Unicode unconditionally under Python
3. This usage now raises an _exc.ArgumentError when used under
Python 3. Added test support for Python 2.
[postgresql] [performance] Enhanced the performance of the asyncpg dialect by caching the asyncpg PreparedStatement objects on a per-connection basis. For a test case that makes use of the same statement on a set of pooled connections this appears to grant a 10-20% speed improvement. The cache size is adjustable and may also be disabled.
[postgresql] [bug] [mysql] Fixed regression introduced in 1.3.2 for the PostgreSQL dialect, also
copied out to the MySQL dialect's feature in 1.3.18, where usage of a non
_schema.Table construct such as _sql.text() as the argument
to _sql.Select.with_for_update.of would fail to be accommodated
correctly within the PostgreSQL or MySQL compilers.
This change is also backported to: 1.3.21
References: #5729
[postgresql] [bug] Fixed a small regression where the query for "show standard_conforming_strings" upon initialization would be emitted even if the server version info were detected as less than version 8.2, previously it would only occur for server version 8.2 or greater. The query fails on Amazon Redshift which reports a PG server version older than this value.
References: #5698
[postgresql] [bug] Established support for _schema.Column objects as well as ORM
instrumented attributes as keys in the set_ dictionary passed to the
_postgresql.Insert.on_conflict_do_update() and
_sqlite.Insert.on_conflict_do_update() methods, which match to the
_schema.Column objects in the .c collection of the target
_schema.Table. Previously, only string column names were
expected; a column expression would be assumed to be an out-of-table
expression that would render fully along with a warning.
References: #5722
[postgresql] [bug] [asyncio] Fixed bug in asyncpg dialect where a failure during a "commit" or less likely a "rollback" should cancel the entire transaction; it's no longer possible to emit rollback. Previously the connection would continue to await a rollback that could not succeed as asyncpg would reject it.
References: #5824
[mysql] [feature] Added support for the aiomysql driver when using the asyncio SQLAlchemy extension.
References: #5747
[mysql] [bug] [reflection] Fixed issue where reflecting a server default on MariaDB only that contained a decimal point in the value would fail to be reflected correctly, leading towards a reflected table that lacked any server default.
This change is also backported to: 1.3.21
References: #5744
[sqlite] [usecase] Implemented INSERT... ON CONFLICT clause for SQLite. Pull request courtesy Ramon Williams.
References: #4010
[sqlite] [bug] Use python re.search() instead of re.match() as the operation
used by the Column.regexp_match() method when using sqlite.
This matches the behavior of regular expressions on other databases
as well as that of well-known SQLite plugins.
References: #5699
[mssql] [bug] [datatypes] [mysql] Decimal accuracy and behavior has been improved when extracting floating
point and/or decimal values from JSON strings using the
_sql.sqltypes.JSON.Comparator.as_float() method, when the numeric
value inside of the JSON string has many significant digits; previously,
MySQL backends would truncate values with many significant digits and SQL
Server backends would raise an exception due to a DECIMAL cast with
insufficient significant digits. Both backends now use a FLOAT-compatible
approach that does not hardcode significant digits for floating point
values. For precision numerics, a new method
_sql.sqltypes.JSON.Comparator.as_numeric() has been added which
accepts arguments for precision and scale, and will return values as Python
Decimal objects with no floating point conversion assuming the DBAPI
supports it (all but pysqlite).
References: #5788
[oracle] [bug] Fixed regression which occured due to #5755 which implemented
isolation level support for Oracle. It has been reported that many Oracle
accounts don't actually have permission to query the v$transaction
view so this feature has been altered to gracefully fallback when it fails
upon database connect, where the dialect will assume "READ COMMITTED" is
the default isolation level as was the case prior to SQLAlchemy 1.3.21.
However, explicit use of the _engine.Connection.get_isolation_level()
method must now necessarily raise an exception, as Oracle databases with
this restriction explicitly disallow the user from reading the current
isolation level.
This change is also backported to: 1.3.22
References: #5784
[oracle] [bug] Oracle two-phase transactions at a rudimentary level are now no longer deprecated. After receiving support from cx_Oracle devs we can provide for basic xid + begin/prepare support with some limitations, which will work more fully in an upcoming release of cx_Oracle. Two phase "recovery" is not currently supported.
References: #5884
[oracle] [bug] The Oracle dialect now uses
select sys_context( 'userenv', 'current_schema' ) from dual to get
the default schema name, rather than SELECT USER FROM DUAL, to
accommodate for changes to the session-local schema name under Oracle.
References: #5716
[usecase] [pool] [tests] Improve documentation and add test for sub-second pool timeouts. Pull request courtesy Jordan Pittier.
References: #5582
[usecase] [pool] The internal mechanics of the engine connection routine has been altered
such that it's now guaranteed that a user-defined event handler for the
_pool.PoolEvents.connect() handler, when established using
insert=True, will allow an event handler to run that is definitely
invoked before any dialect-specific initialization starts up, most
notably when it does things like detect default schema name.
Previously, this would occur in most cases but not unconditionally.
A new example is added to the schema documentation illustrating how to
establish the "default schema name" within an on-connect event.
[bug] [reflection] Fixed bug where the now-deprecated autoload parameter was being called
internally within the reflection routines when a related table were
reflected.
References: #5684
[bug] [pool] Fixed regression where a connection pool event specified with a keyword,
most notably insert=True, would be lost when the event were set up.
This would prevent startup events that need to fire before dialect-level
events from working correctly.
References: #5708
[bug] [pool] [pypy] Fixed issue where connection pool would not return connections to the pool or otherwise be finalized upon garbage collection under pypy if the checked out connection fell out of scope without being closed. This is a long standing issue due to pypy's difference in GC behavior that does not call weakref finalizers if they are relative to another object that is also being garbage collected. A strong reference to the related record is now maintained so that the weakref has a strong-referenced "base" to trigger off of.
References: #5842
[general] [change] "python setup.py test" is no longer a test runner, as this is deprecated by Pypa. Please use "tox" with no arguments for a basic te…
# 1.4.0b1
Released: November 2, 2020 ## general
[general] [change] "python setup.py test" is no longer a test runner, as this is deprecated by Pypa. Please use "tox" with no arguments for a basic test run.
References: [#4789](http://www.sqlalchemy.org/trac/ticket/4789)
[general] [bug] Refactored the internal conventions used to cross-import modules that have mutual dependencies between them, such that the inspected arguments of functions and methods are no longer modified. This allows tools like pylint, Pycharm, other code linters, as well as hypothetical pep-484 implementations added in the future to function correctly as they no longer see missing arguments to function calls. The new approach is also simpler and more performant.
References: [#4656](http://www.sqlalchemy.org/trac/ticket/4656), [#4689](http://www.sqlalchemy.org/trac/ticket/4689)
## platform
[platform] [change] The importlib_metadata library is used to scan for setuptools entrypoints rather than pkg_resources. as importlib_metadata is a small library that is included as of Python 3.8, the compatibility library is installed as a dependency for Python versions older than 3.8.
References: [#5400](http://www.sqlalchemy.org/trac/ticket/5400)
[platform] [change] Installation has been modernized to use setup.cfg for most package metadata.
References: [#5404](http://www.sqlalchemy.org/trac/ticket/5404)
[platform] [removed] Dropped support for python 3.4 and 3.5 that has reached EOL. SQLAlchemy 1.4 series requires python 2.7 or 3.6+.
References: [#5634](http://www.sqlalchemy.org/trac/ticket/5634)
[platform] [removed] Removed all dialect code related to support for Jython and zxJDBC. Jython has not been supported by SQLAlchemy for many years and it is not expected that the current zxJDBC code is at all functional; for the moment it just takes up space and adds confusion by showing up in documentation. At the moment, it appears that Jython has achieved Python 2.7 support in its releases but not Python 3. If Jython were to be supported again, the form it should take is against the Python 3 version of Jython, and the various zxJDBC stubs for various backends should be implemented as a third party dialect.
References: [#5094](http://www.sqlalchemy.org/trac/ticket/5094)
## orm
[orm] [feature] The ORM can now generate queries previously only available when using _orm.Query using the _sql.select() construct directly. A new system by which ORM "plugins" may establish themselves within a Core _sql.Select allow the majority of query building logic previously inside of _orm.Query to now take place within a compilation-level extension for _sql.Select. Similar changes have been made for the _sql.Update and _sql.Delete constructs as well. The constructs when invoked using _orm.Session.execute() now do ORM-related work within the method. For _sql.Select, the _engine.Result object returned now contains ORM-level entities and results.
References: [#5159](http://www.sqlalchemy.org/trac/ticket/5159)
[orm] [feature] Added the ability to add arbitrary criteria to the ON clause generated by a relationship attribute in a query, which applies to methods such as _query.Query.join() as well as loader options like _orm.joinedload(). Additionally, a "global" version of the option allows limiting criteria to be applied to particular entities in a query globally.
References: [#4472](http://www.sqlalchemy.org/trac/ticket/4472)
[orm] [feature] The ORM Declarative system is now unified into the ORM itself, with new import spaces under sqlalchemy.orm and new kinds of mappings. Support for decorator-based mappings without using a base class, support for classical style-mapper() calls that have access to the declarative class registry for relationships, and full integration of Declarative with 3rd party class attribute systems like dataclasses and attrs is now supported.
References: [#5508](http://www.sqlalchemy.org/trac/ticket/5508)
[orm] [feature] Eager loaders, such as joined loading, SELECT IN loading, etc., when configured on a mapper or via query options will now be invoked during the refresh on an expired object; in the case of selectinload and subqueryload, since the additional load is for a single object only, the "immediateload" scheme is used in these cases which resembles the single-parent query emitted by lazy loading.
References: [#1763](http://www.sqlalchemy.org/trac/ticket/1763)
[orm] [feature] Added support for direct mapping of Python classes that are defined using the Python dataclasses decorator. Pull request courtesy Václav Klusák. The new feature integrates into new support at the Declarative level for systems such as dataclasses and attrs.
References: [#5027](http://www.sqlalchemy.org/trac/ticket/5027)
[orm] [feature] Added "raiseload" feature for ORM mapped columns via orm.defer.raiseload parameter on defer() and deferred(). This provides similar behavior for column-expression mapped attributes as the raiseload() option does for relationship mapped attributes. The change also includes some behavioral changes to deferred columns regarding expiration; see the migration notes for details.
References: [#4826](http://www.sqlalchemy.org/trac/ticket/4826)
[orm] [usecase] The evaluator that takes place within the ORM bulk update and delete for synchronize_session="evaluate" now supports the IN and NOT IN operators. Tuple IN is also supported.
References: [#1653](http://www.sqlalchemy.org/trac/ticket/1653)
[orm] [usecase] Enhanced logic that tracks if relationships will be conflicting with each other when they write to the same column to include simple cases of two relationships that should have a "backref" between them. This means that if two relationships are not viewonly, are not linked with back_populates and are not otherwise in an inheriting sibling/overriding arrangement, and will populate the same foreign key column, a warning is emitted at mapper configuration time warning that a conflict may arise. A new parameter _orm.relationship.overlaps is added to suit those very rare cases where such an overlapping persistence arrangement may be unavoidable.
References: [#5171](http://www.sqlalchemy.org/trac/ticket/5171)
[orm] [usecase] The ORM bulk update and delete operations, historically available via the _orm.Query.update() and _orm.Query.delete() methods as well as via the _dml.Update and _dml.Delete constructs for 2.0 style execution, will now automatically accommodate for the additional WHERE criteria needed for a single-table inheritance discriminator in order to limit the statement to rows referring to the specific subtype requested. The new _orm.with_loader_criteria() construct is also supported for with bulk update/delete operations.
Unknown interpreted text role "term".
References: [#3903](http://www.sqlalchemy.org/trac/ticket/3903), [#5018](http://www.sqlalchemy.org/trac/ticket/5018)
[orm] [usecase] Update _orm.relationship.sync_backref flag in a relationship to make it implicitly False in viewonly=True relationships, preventing synchronization events.
References: [#5237](http://www.sqlalchemy.org/trac/ticket/5237)
[orm] [change] The condition where a pending object being flushed with an identity that already exists in the identity map has been adjusted to emit a warning, rather than throw a FlushError. The rationale is so that the flush will proceed and raise a IntegrityError instead, in the same way as if the existing object were not present in the identity map already. This helps with schemes that are using the IntegrityError as a means of catching whether or not a row already exists in the table.
References: [#4662](http://www.sqlalchemy.org/trac/ticket/4662)
[orm] [change] [sql] A selection of Core and ORM query objects now perform much more of their Python computational tasks within the compile step, rather than at construction time. This is to support an upcoming caching model that will provide for caching of the compiled statement structure based on a cache key that is derived from the statement construct, which itself is expected to be newly constructed in Python code each time it is used. This means that the internal state of these objects may not be the same as it used to be, as well as that some but not all error raise scenarios for various kinds of argument validation will occur within the compilation / execution phase, rather than at statement construction time. See the migration notes linked below for complete details.
[orm] [change] The automatic uniquing of rows on the client side is turned off for the new 2.0 style of ORM querying. This improves both clarity and performance. However, uniquing of rows on the client side is generally necessary when using joined eager loading for collections, as there will be duplicates of the primary entity for each element in the collection because a join was used. This uniquing must now be manually enabled and can be achieved using the new _engine.Result.unique() modifier. To avoid silent failure, the ORM explicitly requires the method be called when the result of an ORM query in 2.0 style makes use of joined load collections. The newer _orm.selectinload() strategy is likely preferable for eager loading of collections in any case.
Unknown interpreted text role "term".
References: [#4395](http://www.sqlalchemy.org/trac/ticket/4395)
[orm] [change] The ORM will now warn when asked to coerce a _expression.select() construct into a subquery implicitly. This occurs within places such as the _query.Query.select_entity_from() and _query.Query.select_from() methods as well as within the with_polymorphic() function. When a _expression.SelectBase (which is what's produced by _expression.select()) or _query.Query object is passed directly to these functions and others, the ORM is typically coercing them to be a subquery by calling the _expression.SelectBase.alias() method automatically (which is now superseded by the _expression.SelectBase.subquery() method). See the migration notes linked below for further details.
References: [#4617](http://www.sqlalchemy.org/trac/ticket/4617)
[orm] [change] The "KeyedTuple" class returned by _query.Query is now replaced with the Core Row class, which behaves in the same way as KeyedTuple. In SQLAlchemy 2.0, both Core and ORM will return result rows using the same Row object. In the interim, Core uses a backwards-compatibility class LegacyRow that maintains the former mapping/tuple hybrid behavior used by "RowProxy".
References: [#4710](http://www.sqlalchemy.org/trac/ticket/4710)
[orm] [performance] The bulk update and delete methods Query.update() and Query.delete(), as well as their 2.0-style counterparts, now make use of RETURNING when the "fetch" strategy is used in order to fetch the list of affected primary key identites, rather than emitting a separate SELECT, when the backend in use supports RETURNING. Additionally, the "fetch" strategy will in ordinary cases not expire the attributes that have been updated, and will instead apply the updated values directly in the same way that the "evaluate" strategy does, to avoid having to refresh the object. The "evaluate" strategy will also fall back to expiring attributes that were updated to a SQL expression that was unevaluable in Python.
[orm] [performance] [postgresql] Implemented support for the psycopg2 execute_values() extension within the ORM flush process via the enhancements to Core made in [#5401](http://www.sqlalchemy.org/trac/ticket/5401), so that this extension is used both as a strategy to batch INSERT statements together as well as that RETURNING may now be used among multiple parameter sets to retrieve primary key values back in batch. This allows nearly all INSERT statements emitted by the ORM on behalf of PostgreSQL to be submitted in batch and also via the execute_values() extension which benches at five times faster than plain executemany() for this particular backend.
References: [#5263](http://www.sqlalchemy.org/trac/ticket/5263)
[orm] [bug] A query that is against a mapped inheritance subclass which also uses _query.Query.select_entity_from() or a similar technique in order to provide an existing subquery to SELECT from, will now raise an error if the given subquery returns entities that do not correspond to the given subclass, that is, they are sibling or superclasses in the same hierarchy. Previously, these would be returned without error. Additionally, if the inheritance mapping is a single-inheritance mapping, the given subquery must apply the appropriate filtering against the polymorphic discriminator column in order to avoid this error; previously, the _query.Query would add this criteria to the outside query however this interferes with some kinds of query that return other kinds of entities as well.
References: [#5122](http://www.sqlalchemy.org/trac/ticket/5122)
[orm] [bug] The internal attribute symbols NO_VALUE and NEVER_SET have been unified, as there was no meaningful difference between these two symbols, other than a few codepaths where they were differentiated in subtle and undocumented ways, these have been fixed.
References: [#4696](http://www.sqlalchemy.org/trac/ticket/4696)
[orm] [bug] Fixed bug where a versioning column specified on a mapper against a _expression.select() construct where the version_id_col itself were against the underlying table would incur additional loads when accessed, even if the value were locally persisted by the flush. The actual fix is a result of the changes in [#4617](http://www.sqlalchemy.org/trac/ticket/4617), by fact that a _expression.select() object no longer has a .c attribute and therefore does not confuse the mapper into thinking there's an unknown column value present.
References: [#4194](http://www.sqlalchemy.org/trac/ticket/4194)
[orm] [bug] An UnmappedInstanceError is now raised for InstrumentedAttribute if an instance is an unmapped object. Prior to this an AttributeError was raised. Pull request courtesy Ramon Williams.
References: [#3858](http://www.sqlalchemy.org/trac/ticket/3858)
[orm] [bug] The Session object no longer initiates a SessionTransaction object immediately upon construction or after the previous transaction is closed; instead, "autobegin" logic now initiates the new SessionTransaction on demand when it is next needed. Rationale includes to remove reference cycles from a Session that has been closed out, as well as to remove the overhead incurred by the creation of SessionTransaction objects that are often discarded immediately. This change affects the behavior of the SessionEvents.after_transaction_create() hook in that the event will be emitted when the Session first requires a SessionTransaction be present, rather than whenever the Session were created or the previous SessionTransaction were closed. Interactions with the _engine.Engine and the database itself remain unaffected.
References: [#5074](http://www.sqlalchemy.org/trac/ticket/5074)
[orm] [bug] Added new entity-targeting capabilities to the ORM query context help with the case where the Session is using a bind dictionary against mapped classes, rather than a single bind, and the _query.Query is against a Core statement that was ultimately generated from a method such as _query.Query.subquery(). First implemented using a deep search, the current approach leverages the unified _sql.select() construct to keep track of the first mapper that is part of the construct.
References: [#4829](http://www.sqlalchemy.org/trac/ticket/4829)
[orm] [bug] [inheritance] An ArgumentError is now raised if both the selectable and flat parameters are set to True in orm.with_polymorphic(). The selectable name is already aliased and applying flat=True overrides the selectable name with an anonymous name that would've previously caused the code to break. Pull request courtesy Ramon Williams.
References: [#4212](http://www.sqlalchemy.org/trac/ticket/4212)
[orm] [bug] Fixed issue in polymorphic loading internals which would fall back to a more expensive, soon-to-be-deprecated form of result column lookup within certain unexpiration scenarios in conjunction with the use of "with_polymorphic".
References: [#4718](http://www.sqlalchemy.org/trac/ticket/4718)
[orm] [bug] An error is raised if any persistence-related "cascade" settings are made on a _orm.relationship() that also sets up viewonly=True. The "cascade" settings now default to non-persistence related settings only when viewonly is also set. This is the continuation from [#4993](http://www.sqlalchemy.org/trac/ticket/4993) where this setting was changed to emit a warning in 1.3.
References: [#4994](http://www.sqlalchemy.org/trac/ticket/4994)
[orm] [bug] Improved declarative inheritance scanning to not get tripped up when the same base class appears multiple times in the base inheritance list.
References: [#4699](http://www.sqlalchemy.org/trac/ticket/4699)
[orm] [bug] Fixed bug in ORM versioning feature where assignment of an explicit version_id for a counter configured against a mapped selectable where version_id_col is against the underlying table would fail if the previous value were expired; this was due to the fact that the mapped attribute would not be configured with active_history=True.
References: [#4195](http://www.sqlalchemy.org/trac/ticket/4195)
[orm] [bug] An exception is now raised if the ORM loads a row for a polymorphic instance that has a primary key but the discriminator column is NULL, as discriminator columns should not be null.
References: [#4836](http://www.sqlalchemy.org/trac/ticket/4836)
[orm] [bug] Accessing a collection-oriented attribute on a newly created object no longer mutates __dict__, but still returns an empty collection as has always been the case. This allows collection-oriented attributes to work consistently in comparison to scalar attributes which return None, but also don't mutate __dict__. In order to accommodate for the collection being mutated, the same empty collection is returned each time once initially created, and when it is mutated (e.g. an item appended, added, etc.) it is then moved into __dict__. This removes the last of mutating side-effects on read-only attribute access within the ORM.
References: [#4519](http://www.sqlalchemy.org/trac/ticket/4519)
[orm] [bug] The refresh of an expired object will now trigger an autoflush if the list of expired attributes include one or more attributes that were explicitly expired or refreshed using the Session.expire() or Session.refresh() methods. This is an attempt to find a middle ground between the normal unexpiry of attributes that can happen in many cases where autoflush is not desirable, vs. the case where attributes are being explicitly expired or refreshed and it is possible that these attributes depend upon other pending state within the session that needs to be flushed. The two methods now also gain a new flag Session.expire.autoflush and Session.refresh.autoflush, defaulting to True; when set to False, this will disable the autoflush that occurs on unexpire for these attributes.
References: [#5226](http://www.sqlalchemy.org/trac/ticket/5226)
[orm] [bug] The behavior of the _orm.relationship.cascade_backrefs flag will be reversed in 2.0 and set to False unconditionally, such that backrefs don't cascade save-update operations from a forwards-assignment to a backwards assignment. A 2.0 deprecation warning is emitted when the parameter is left at its default of True at the point at which such a cascade operation actually takes place. The new behavior can be established as always by setting the flag to False on a specific _orm.relationship(), or more generally can be set up across the board by setting the the _orm.Session.future flag to True.
References: [#5150](http://www.sqlalchemy.org/trac/ticket/5150)
[orm] [deprecated] The "slice index" feature used by _orm.Query as well as by the dynamic relationship loader will no longer accept negative indexes in SQLAlchemy 2.0. These operations do not work efficiently and load the entire collection in, which is both surprising and undesirable. These will warn in 1.4 unless the _orm.Session.future flag is set in which case they will raise IndexError.
References: [#5606](http://www.sqlalchemy.org/trac/ticket/5606)
[orm] [deprecated] Calling the _query.Query.instances() method without passing a QueryContext is deprecated. The original use case for this was that a _query.Query could yield ORM objects when given only the entities to be selected as well as a DBAPI cursor object. However, for this to work correctly there is essential metadata that is passed from a SQLAlchemy _engine.ResultProxy that is derived from the mapped column expressions, which comes originally from the QueryContext. To retrieve ORM results from arbitrary SELECT statements, the _query.Query.from_statement() method should be used.
References: [#4719](http://www.sqlalchemy.org/trac/ticket/4719)
[orm] [deprecated] Using strings to represent relationship names in ORM operations such as _orm.Query.join(), as well as strings for all ORM attribute names in loader options like _orm.selectinload() is deprecated and will be removed in SQLAlchemy 2.0. The class-bound attribute should be passed instead. This provides much better specificity to the given method, allows for modifiers such as of_type(), and reduces internal complexity.
Additionally, the aliased and from_joinpoint parameters to _orm.Query.join() are also deprecated. The _orm.aliased() construct now provides for a great deal of flexibility and capability and should be used directly.
References: [#4705](http://www.sqlalchemy.org/trac/ticket/4705), [#5202](http://www.sqlalchemy.org/trac/ticket/5202)
[orm] [deprecated] Deprecated logic in _query.Query.distinct() that automatically adds columns in the ORDER BY clause to the columns clause; this will be removed in 2.0.
References: [#5134](http://www.sqlalchemy.org/trac/ticket/5134)
[orm] [deprecated] Passing keyword arguments to methods such as _orm.Session.execute() to be passed into the _orm.Session.get_bind() method is deprecated; the new _orm.Session.execute.bind_arguments dictionary should be passed instead.
References: [#5573](http://www.sqlalchemy.org/trac/ticket/5573)
[orm] [deprecated] The eagerload() and relation() were old aliases and are now deprecated. Use _orm.joinedload() and _orm.relationship() respectively.
References: [#5192](http://www.sqlalchemy.org/trac/ticket/5192)
[orm] [removed] All long-deprecated "extension" classes have been removed, including MapperExtension, SessionExtension, PoolListener, ConnectionProxy, AttributeExtension. These classes have been deprecated since version 0.7 long superseded by the event listener system.
References: [#4638](http://www.sqlalchemy.org/trac/ticket/4638)
[orm] [removed] Remove the deprecated loader options joinedload_all, subqueryload_all, lazyload_all, selectinload_all. The normal version with method chaining should be used in their place.
References: [#4642](http://www.sqlalchemy.org/trac/ticket/4642)
[orm] [removed] Remove deprecated function comparable_property. Please refer to the ~sqlalchemy.ext.hybrid extension. This also removes the function comparable_using in the declarative extension.
Remove deprecated function compile_mappers. Please use configure_mappers()
Remove deprecated method collection.linker. Please refer to the AttributeEvents.init_collection() and AttributeEvents.dispose_collection() event handlers.
Remove deprecated method Session.prune and parameter Session.weak_identity_map. See the recipe at session_referencing_behavior for an event-based approach to maintaining strong identity references. This change also removes the class StrongInstanceDict.
Remove deprecated parameter mapper.order_by. Use _query.Query.order_by() to determine the ordering of a result set.
Remove deprecated parameter Session._enable_transaction_accounting.
Remove deprecated parameter Session.is_modified.passive.
References: [#4643](http://www.sqlalchemy.org/trac/ticket/4643)
## engine
[engine] [feature] Implemented an all-new _result.Result object that replaces the previous ResultProxy object. As implemented in Core, the subclass _result.CursorResult features a compatible calling interface with the previous ResultProxy, and additionally adds a great amount of new functionality that can be applied to Core result sets as well as ORM result sets, which are now integrated into the same model. _result.Result includes features such as column selection and rearrangement, improved fetchmany patterns, uniquing, as well as a variety of implementations that can be used to create database results from in-memory structures as well.
References: [#4395](http://www.sqlalchemy.org/trac/ticket/4395), [#4959](http://www.sqlalchemy.org/trac/ticket/4959), [#5087](http://www.sqlalchemy.org/trac/ticket/5087)
[engine] [feature] [orm] SQLAlchemy now includes support for Python asyncio within both Core and ORM, using the included asyncio extension <asyncio_toplevel>. The extension makes use of the [greenlet](https://greenlet.readthedocs.io/en/latest/) library in order to adapt SQLAlchemy's sync-oriented internals such that an asyncio interface that ultimately interacts with an asyncio database adapter is now feasible. The single driver supported at the moment is the dialect-postgresql-asyncpg driver for PostgreSQL.
References: [#3414](http://www.sqlalchemy.org/trac/ticket/3414)
[engine] [feature] [alchemy2] Implemented the _sa.create_engine.future parameter which enables forwards compatibility with SQLAlchemy 2. is used for forwards compatibility with SQLAlchemy 2. This engine features always-transactional behavior with autobegin.
References: [#4644](http://www.sqlalchemy.org/trac/ticket/4644)
[engine] [feature] [pyodbc] Reworked the "setinputsizes()" set of dialect hooks to be correctly extensible for any arbirary DBAPI, by allowing dialects individual hooks that may invoke cursor.setinputsizes() in the appropriate style for that DBAPI. In particular this is intended to support pyodbc's style of usage which is fundamentally different from that of cx_Oracle. Added support for pyodbc.
References: [#5649](http://www.sqlalchemy.org/trac/ticket/5649)
[engine] [feature] Added new reflection method Inspector.get_sequence_names() which returns all the sequences defined and Inspector.has_sequence() to check if a particular sequence exits. Support for this method has been added to the backend that support Sequence: PostgreSQL, Oracle and MariaDB >= 10.3.
References: [#2056](http://www.sqlalchemy.org/trac/ticket/2056)
[engine] [feature] The _schema.Table.autoload_with parameter now accepts an _reflection.Inspector object directly, as well as any _engine.Engine or _engine.Connection as was the case before.
References: [#4755](http://www.sqlalchemy.org/trac/ticket/4755)
[engine] [change] The RowProxy class is no longer a "proxy" object, and is instead directly populated with the post-processed contents of the DBAPI row tuple upon construction. Now named Row, the mechanics of how the Python-level value processors have been simplified, particularly as it impacts the format of the C code, so that a DBAPI row is processed into a result tuple up front. The object returned by the _engine.ResultProxy is now the LegacyRow subclass, which maintains mapping/tuple hybrid behavior, however the base Row class now behaves more fully like a named tuple.
References: [#4710](http://www.sqlalchemy.org/trac/ticket/4710)
[engine] [performance] The pool "pre-ping" feature has been refined to not invoke for a DBAPI connection that was just opened in the same checkout operation. pre ping only applies to a DBAPI connection that's been checked into the pool and is being checked out again.
References: [#4524](http://www.sqlalchemy.org/trac/ticket/4524)
[engine] [performance] [change] [py3k] Disabled the "unicode returns" check that runs on dialect startup when running under Python 3, which for many years has occurred in order to test the current DBAPI's behavior for whether or not it returns Python Unicode or Py2K strings for the VARCHAR and NVARCHAR datatypes. The check still occurs by default under Python 2, however the mechanism to test the behavior will be removed in SQLAlchemy 2.0 when Python 2 support is also removed.
This logic was very effective when it was needed, however now that Python 3 is standard, all DBAPIs are expected to return Python 3 strings for character datatypes. In the unlikely case that a third party DBAPI does not support this, the conversion logic within String is still available and the third party dialect may specify this in its upfront dialect flags by setting the dialect level flag returns_unicode_strings to one of String.RETURNS_CONDITIONAL or String.RETURNS_BYTES, both of which will enable Unicode conversion even under Python 3.
References: [#5315](http://www.sqlalchemy.org/trac/ticket/5315)
[engine] [bug] Revised the Connection.execution_options.schema_translate_map feature such that the processing of the SQL statement to receive a specific schema name occurs within the execution phase of the statement, rather than at the compile phase. This is to support the statement being efficiently cached. Previously, the current schema being rendered into the statement for a particular run would be considered as part of the cache key itself, meaning that for a run against hundreds of schemas, there would be hundreds of cache keys, rendering the cache much less performant. The new behavior is that the rendering is done in a similar manner as the "post compile" rendering added in 1.4 as part of [#4645](http://www.sqlalchemy.org/trac/ticket/4645), [#4808](http://www.sqlalchemy.org/trac/ticket/4808).
References: [#5004](http://www.sqlalchemy.org/trac/ticket/5004)
[engine] [bug] The _engine.Connection object will now not clear a rolled-back transaction until the outermost transaction is explicitly rolled back. This is essentially the same behavior that the ORM Session has had for a long time, where an explicit call to .rollback() on all enclosing transactions is required for the transaction to logically clear, even though the DBAPI-level transaction has already been rolled back. The new behavior helps with situations such as the "ORM rollback test suite" pattern where the test suite rolls the transaction back within the ORM scope, but the test harness which seeks to control the scope of the transaction externally does not expect a new transaction to start implicitly.
References: [#4712](http://www.sqlalchemy.org/trac/ticket/4712)
[engine] [bug] Adjusted the dialect initialization process such that the _engine.Dialect.on_connect() is not called a second time on the first connection. The hook is called first, then the _engine.Dialect.initialize() is called if that connection is the first for that dialect, then no more events are called. This eliminates the two calls to the "on_connect" function which can produce very difficult debugging situations.
References: [#5497](http://www.sqlalchemy.org/trac/ticket/5497)
[engine] [deprecated] The _engine.URL object is now an immutable named tuple. To modify a URL object, use the _engine.URL.set() method to produce a new URL object.
References: [#5526](http://www.sqlalchemy.org/trac/ticket/5526)
[engine] [deprecated] The _schema.MetaData.bind argument as well as the overall concept of "bound metadata" is deprecated in SQLAlchemy 1.4 and will be removed in SQLAlchemy 2.0. The parameter as well as related functions now emit a _exc.RemovedIn20Warning when deprecation_20_mode is in use.
References: [#4634](http://www.sqlalchemy.org/trac/ticket/4634)
[engine] [deprecated] The server_side_cursors engine-wide parameter is deprecated and will be removed in a future release. For unbuffered cursors, the _engine.Connection.execution_options.stream_results execution option should be used on a per-execution basis.
[engine] [deprecated] The _engine.Connection.connect() method is deprecated as is the concept of "connection branching", which copies a _engine.Connection into a new one that has a no-op ".close()" method. This pattern is oriented around the "connectionless execution" concept which is also being removed in 2.0.
References: [#5131](http://www.sqlalchemy.org/trac/ticket/5131)
[engine] [deprecated] The case_sensitive flag on _sa.create_engine() is deprecated; this flag was part of the transition of the result row object to allow case sensitive column matching as the default, while providing backwards compatibility for the former matching method. All string access for a row should be assumed to be case sensitive just like any other Python mapping.
References: [#4878](http://www.sqlalchemy.org/trac/ticket/4878)
[engine] [deprecated] "Implicit autocommit", which is the COMMIT that occurs when a DML or DDL statement is emitted on a connection, is deprecated and won't be part of SQLAlchemy 2.0. A 2.0-style warning is emitted when autocommit takes effect, so that the calling code may be adjusted to use an explicit transaction.
As part of this change, DDL methods such as _schema.MetaData.create_all() when used against an _engine.Engine will run the operation in a BEGIN block if one is not started already.
References: [#4846](http://www.sqlalchemy.org/trac/ticket/4846)
[engine] [deprecated] Deprecated the behavior by which a _schema.Column can be used as the key in a result set row lookup, when that _schema.Column is not part of the SQL selectable that is being selected; that is, it is only matched on name. A deprecation warning is now emitted for this case. Various ORM use cases, such as those involving _expression.text() constructs, have been improved so that this fallback logic is avoided in most cases.
References: [#4877](http://www.sqlalchemy.org/trac/ticket/4877)
[engine] [deprecated] Deprecated remaining engine-level introspection and utility methods including _engine.Engine.run_callable(), _engine.Engine.transaction(), _engine.Engine.table_names(), _engine.Engine.has_table(). The utility methods are superseded by modern context-manager patterns, and the table introspection tasks are suited by the _reflection.Inspector object.
References: [#4755](http://www.sqlalchemy.org/trac/ticket/4755)
[engine] [removed] Remove deprecated method get_primary_keys in the Dialect and _reflection.Inspector classes. Please refer to the Dialect.get_pk_constraint() and _reflection.Inspector.get_primary_keys() methods.
Remove deprecated event dbapi_error and the method ConnectionEvents.dbapi_error. Please refer to the _events.ConnectionEvents.handle_error() event. This change also removes the attributes ExecutionContext.is_disconnect and ExecutionContext.exception.
References: [#4643](http://www.sqlalchemy.org/trac/ticket/4643)
[engine] [removed] The internal dialect method Dialect.reflecttable has been removed. A review of third party dialects has not found any making use of this method, as it was already documented as one that should not be used by external dialects. Additionally, the private Engine._run_visitor method is also removed.
References: [#4755](http://www.sqlalchemy.org/trac/ticket/4755)
[engine] [removed] The long-deprecated Inspector.get_table_names.order_by parameter has been removed.
References: [#4755](http://www.sqlalchemy.org/trac/ticket/4755)
[engine] [renamed] The _reflection.Inspector.reflecttable() was renamed to _reflection.Inspector.reflect_table().
References: [#5244](http://www.sqlalchemy.org/trac/ticket/5244)
## sql
[sql] [feature] Added "from linting" as a built-in feature to the SQL compiler. This allows the compiler to maintain graph of all the FROM clauses in a particular SELECT statement, linked by criteria in either the WHERE or in JOIN clauses that link these FROM clauses together. If any two FROM clauses have no path between them, a warning is emitted that the query may be producing a cartesian product. As the Core expression language as well as the ORM are built on an "implicit FROMs" model where a particular FROM clause is automatically added if any part of the query refers to it, it is easy for this to happen inadvertently and it is hoped that the new feature helps with this issue.
References: [#4737](http://www.sqlalchemy.org/trac/ticket/4737)
[sql] [feature] [mssql] [oracle] Added new "post compile parameters" feature. This feature allows a bindparam() construct to have its value rendered into the SQL string before being passed to the DBAPI driver, but after the compilation step, using the "literal render" feature of the compiler. The immediate rationale for this feature is to support LIMIT/OFFSET schemes that don't work or perform well as bound parameters handled by the database driver, while still allowing for SQLAlchemy SQL constructs to be cacheable in their compiled form. The immediate targets for the new feature are the "TOP N" clause used by SQL Server (and Sybase) which does not support a bound parameter, as well as the "ROWNUM" and optional "FIRST_ROWS()" schemes used by the Oracle dialect, the former of which has been known to perform better without bound parameters and the latter of which does not support a bound parameter. The feature builds upon the mechanisms first developed to support "expanding" parameters for IN expressions. As part of this feature, the Oracle use_binds_for_limits feature is turned on unconditionally and this flag is now deprecated.
References: [#4808](http://www.sqlalchemy.org/trac/ticket/4808)
[sql] [feature] Add support for regular expression on supported backends. Two operations have been defined:
_sql.ColumnOperators.regexp_match() implementing a regular expression match like function.
_sql.ColumnOperators.regexp_replace() implementing a regular expression string replace function.
Supported backends include SQLite, PostgreSQL, MySQL / MariaDB, and Oracle.
References: [#1390](http://www.sqlalchemy.org/trac/ticket/1390)
[sql] [feature] The _expression.select() construct and related constructs now allow for duplication of column labels and columns themselves in the columns clause, mirroring exactly how column expressions were passed in. This allows the tuples returned by an executed result to match what was SELECTed for in the first place, which is how the ORM _query.Query works, so this establishes better cross-compatibility between the two constructs. Additionally, it allows column-positioning-sensitive structures such as UNIONs (i.e. _selectable.CompoundSelect) to be more intuitively constructed in those cases where a particular column might appear in more than one place. To support this change, the _expression.ColumnCollection has been revised to support duplicate columns as well as to allow integer index access.
References: [#4753](http://www.sqlalchemy.org/trac/ticket/4753)
[sql] [feature] Enhanced the disambiguating labels feature of the _expression.select() construct such that when a select statement is used in a subquery, repeated column names from different tables are now automatically labeled with a unique label name, without the need to use the full "apply_labels()" feature that combines tablename plus column name. The disambiguated labels are available as plain string keys in the .c collection of the subquery, and most importantly the feature allows an ORM _orm.aliased() construct against the combination of an entity and an arbitrary subquery to work correctly, targeting the correct columns despite same-named columns in the source tables, without the need for an "apply labels" warning.
References: [#5221](http://www.sqlalchemy.org/trac/ticket/5221)
[sql] [feature] The "expanding IN" feature, which generates IN expressions at query execution time which are based on the particular parameters associated with the statement execution, is now used for all IN expressions made against lists of literal values. This allows IN expressions to be fully cacheable independently of the list of values being passed, and also includes support for empty lists. For any scenario where the IN expression contains non-literal SQL expressions, the old behavior of pre-rendering for each position in the IN is maintained. The change also completes support for expanding IN with tuples, where previously type-specific bind processors weren't taking effect.
References: [#4645](http://www.sqlalchemy.org/trac/ticket/4645)
[sql] [feature] Along with the new transparent statement caching feature introduced as part of [#4369](http://www.sqlalchemy.org/trac/ticket/4369), a new feature intended to decrease the Python overhead of creating statements is added, allowing lambdas to be used when indicating arguments being passed to a statement object such as select(), Query(), update(), etc., as well as allowing the construction of full statements within lambdas in a similar manner as that of the "baked query" system. The rationale of using lambdas is adapted from that of the "baked query" approach which uses lambdas to encapsulate any amount of Python code into a callable that only needs to be called when the statement is first constructed into a string. The new feature however is more sophisticated in that Python literal values that would be passed as parameters are automatically extracted, so that there is no longer a need to use bindparam() objects with such queries. Use of the feature is optional and can be used to as small or as great a degree as is desired, while still allowing statements to be fully cacheable.
References: [#5380](http://www.sqlalchemy.org/trac/ticket/5380)
[sql] [usecase] The Index.create() and Index.drop() methods now have a parameter Index.create.checkfirst, in the same way as that of _schema.Table and Sequence, which when enabled will cause the operation to detect if the index exists (or not) before performing a create or drop operation.
References: [#527](http://www.sqlalchemy.org/trac/ticket/527)
[sql] [usecase] The true() and false() operators may now be applied as the "onclause" of a _expression.join() on a backend that does not support "native boolean" expressions, e.g. Oracle or SQL Server, and the expression will render as "1=1" for true and "1=0" false. This is the behavior that was introduced many years ago in [#2804](http://www.sqlalchemy.org/trac/ticket/2804) for and/or expressions.
[sql] [usecase] Change the method __str of ColumnCollection to avoid confusing it with a python list of string.
References: [#5191](http://www.sqlalchemy.org/trac/ticket/5191)
[sql] [usecase] Add support to FETCH {FIRST | NEXT} [ count ] {ROW | ROWS} {ONLY | WITH TIES} in the select for the supported backends, currently PostgreSQL, Oracle and MSSQL.
References: [#5576](http://www.sqlalchemy.org/trac/ticket/5576)
[sql] [usecase] Additional logic has been added such that certain SQL expressions which typically wrap a single database column will use the name of that column as their "anonymous label" name within a SELECT statement, potentially making key-based lookups in result tuples more intuitive. The primary example of this is that of a CAST expression, e.g. CAST(table.colname AS INTEGER), which will export its default name as "colname", rather than the usual "anon_1" label, that is, CAST(table.colname AS INTEGER) AS colname. If the inner expression doesn't have a name, then the previous "anonymous label" logic is used. When using SELECT statements that make use of _expression.Select.apply_labels(), such as those emitted by the ORM, the labeling logic will produce <tablename>_<inner column name> in the same was as if the column were named alone. The logic applies right now to the cast() and type_coerce() constructs as well as some single-element boolean expressions.
References: [#4449](http://www.sqlalchemy.org/trac/ticket/4449)
[sql] [change] The "clause coercion" system, which is SQLAlchemy Core's system of receiving arguments and resolving them into _expression.ClauseElement structures in order to build up SQL expression objects, has been rewritten from a series of ad-hoc functions to a fully consistent class-based system. This change is internal and should have no impact on end users other than more specific error messages when the wrong kind of argument is passed to an expression object, however the change is part of a larger set of changes involving the role and behavior of _expression.select() objects.
References: [#4617](http://www.sqlalchemy.org/trac/ticket/4617)
[sql] [change] Added a core Values object that enables a VALUES construct to be used in the FROM clause of an SQL statement for databases that support it (mainly PostgreSQL and SQL Server).
References: [#4868](http://www.sqlalchemy.org/trac/ticket/4868)
[sql] [change] The _expression.select() construct is moving towards a new calling form that is select(col1, col2, col3, ..), with all other keyword arguments removed, as these are all suited using generative methods. The single list of column or table arguments passed to select() is still accepted, however is no longer necessary if expressions are passed in a simple positional style. Other keyword arguments are disallowed when this form is used.
References: [#5284](http://www.sqlalchemy.org/trac/ticket/5284)
[sql] [change] As part of the SQLAlchemy 2.0 migration project, a conceptual change has been made to the role of the _expression.SelectBase class hierarchy, which is the root of all "SELECT" statement constructs, in that they no longer serve directly as FROM clauses, that is, they no longer subclass _expression.FromClause. For end users, the change mostly means that any placement of a _expression.select() construct in the FROM clause of another _expression.select() requires first that it be wrapped in a subquery first, which historically is through the use of the _expression.SelectBase.alias() method, and is now also available through the use of _expression.SelectBase.subquery(). This was usually a requirement in any case since several databases don't accept unnamed SELECT subqueries in their FROM clause in any case.
References: [#4617](http://www.sqlalchemy.org/trac/ticket/4617)
[sql] [change] Added a new Core class Subquery, which takes the place of _expression.Alias when creating named subqueries against a _expression.SelectBase object. Subquery acts in the same way as _expression.Alias and is produced from the _expression.SelectBase.subquery() method; for ease of use and backwards compatibility, the _expression.SelectBase.alias() method is synonymous with this new method.
References: [#4617](http://www.sqlalchemy.org/trac/ticket/4617)
[sql] [performance] An all-encompassing reorganization and refactoring of Core and ORM internals now allows all Core and ORM statements within the areas of DQL (e.g. SELECTs) and DML (e.g. INSERT, UPDATE, DELETE) to allow their SQL compilation as well as the construction of result-fetching metadata to be fully cached in most cases. This effectively provides a transparent and generalized version of what the "Baked Query" extension has offered for the ORM in past versions. The new feature can calculate the cache key for any given SQL construction based on the string that it would ultimately produce for a given dialect, allowing functions that compose the equivalent select(), Query(), insert(), update() or delete() object each time to have that statement cached after it's generated the first time.
The feature is enabled transparently but includes some new programming paradigms that may be employed to make the caching even more efficient.
References: [#4639](http://www.sqlalchemy.org/trac/ticket/4639)
[sql] [bug] Fixed issue where when constructing constraints from ORM-bound columns, primarily _schema.ForeignKey objects but also UniqueConstraint, CheckConstraint and others, the ORM-level InstrumentedAttribute is discarded entirely, and all ORM-level annotations from the columns are removed; this is so that the constraints are still fully pickleable without the ORM-level entities being pulled in. These annotations are not necessary to be present at the schema/metadata level.
References: [#5001](http://www.sqlalchemy.org/trac/ticket/5001)
[sql] [bug] Registered function names based on GenericFunction are now retrieved in a case-insensitive fashion in all cases, removing the deprecation logic from 1.3 which temporarily allowed multiple GenericFunction objects to exist with differing cases. A GenericFunction that replaces another on the same name whether or not it's case sensitive emits a warning before replacing the object.
References: [#4569](http://www.sqlalchemy.org/trac/ticket/4569), [#4649](http://www.sqlalchemy.org/trac/ticket/4649)
[sql] [bug] Creating an and_() or or_() construct with no arguments or empty *args will now emit a deprecation warning, as the SQL produced is a no-op (i.e. it renders as a blank string). This behavior is considered to be non-intuitive, so for empty or possibly empty and_() or or_() constructs, an appropriate default boolean should be included, such as and_(True, *args) or or_(False, *args). As has been the case for many major versions of SQLAlchemy, these particular boolean values will not render if the *args portion is non-empty.
References: [#5054](http://www.sqlalchemy.org/trac/ticket/5054)
[sql] [bug] Improved the _sql.tuple_() construct such that it behaves predictably when used in a columns-clause context. The SQL tuple is not supported as a "SELECT" columns clause element on most backends; on those that do (PostgreSQL, not surprisingly), the Python DBAPI does not have a "nested type" concept so there are still challenges in fetching rows for such an object. Use of _sql.tuple_() in a _sql.select() or _orm.Query will now raise a _exc.CompileError at the point at which the _sql.tuple_() object is seen as presenting itself for fetching rows (i.e., if the tuple is in the columns clause of a subquery, no error is raised). For ORM use,the _orm.Bundle object is an explicit directive that a series of columns should be returned as a sub-tuple per row and is suggested by the error message. Additionally ,the tuple will now render with parenthesis in all contexts. Previously, the parenthesization would not render in a columns context leading to non-defined behavior.
References: [#5127](http://www.sqlalchemy.org/trac/ticket/5127)
[sql] [bug] [postgresql] Improved support for column names that contain percent signs in the string, including repaired issues involving anoymous labels that also embedded a column name with a percent sign in it, as well as re-established support for bound parameter names with percent signs embedded on the psycopg2 dialect, using a late-escaping process similar to that used by the cx_Oracle dialect.
References: [#5653](http://www.sqlalchemy.org/trac/ticket/5653)
[sql] [bug] Custom functions that are created as subclasses of FunctionElement will now generate an "anonymous label" based on the "name" of the function just like any other Function object, e.g. "SELECT myfunc() AS myfunc_1". While SELECT statements no longer require labels in order for the result proxy object to function, the ORM still targets columns in rows by using objects as mapping keys, which works more reliably when the column expressions have distinct names. In any case, the behavior is now made consistent between functions generated by func and those generated as custom FunctionElement objects.
References: [#4887](http://www.sqlalchemy.org/trac/ticket/4887)
[sql] [bug] Reworked the _expression.ClauseElement.compare() methods in terms of a new visitor-based approach, and additionally added test coverage ensuring that all _expression.ClauseElement subclasses can be accurately compared against each other in terms of structure. Structural comparison capability is used to a small degree within the ORM currently, however it also may form the basis for new caching features.
References: [#4336](http://www.sqlalchemy.org/trac/ticket/4336)
[sql] [bug] Deprecate usage of DISTINCT ON in dialect other than PostgreSQL. Deprecate old usage of string distinct in MySQL dialect
References: [#4002](http://www.sqlalchemy.org/trac/ticket/4002)
[sql] [bug] The ORDER BY clause of a _selectable.CompoundSelect, e.g. UNION, EXCEPT, etc. will not render the table name associated with a given column when applying _selectable.CompoundSelect.order_by() in terms of a _schema.Table - bound column. Most databases require that the names in the ORDER BY clause be expressed as label names only which are matched to names in the first SELECT statement. The change is related to [#4617](http://www.sqlalchemy.org/trac/ticket/4617) in that a previous workaround was to refer to the .c attribute of the _selectable.CompoundSelect in order to get at a column that has no table name. As the subquery is now named, this change allows both the workaround to continue to work, as well as allows table-bound columns as well as the _selectable.CompoundSelect.selected_columns collections to be usable in the _selectable.CompoundSelect.order_by() method.
References: [#4617](http://www.sqlalchemy.org/trac/ticket/4617)
[sql] [bug] The _expression.Join construct no longer considers the "onclause" as a source of additional FROM objects to be omitted from the FROM list of an enclosing _expression.Select object as standalone FROM objects. This applies to an ON clause that includes a reference to another FROM object outside the JOIN; while this is usually not correct from a SQL perspective, it's also incorrect for it to be omitted, and the behavioral change makes the _expression.Select / _expression.Join behave a bit more intuitively.
References: [#4621](http://www.sqlalchemy.org/trac/ticket/4621)
[sql] [deprecated] The _sql.Join.alias() method is deprecated and will be removed in SQLAlchemy 2.0. An explicit select + subquery, or aliasing of the inner tables, should be used instead.
References: [#5010](http://www.sqlalchemy.org/trac/ticket/5010)
[sql] [deprecated] The _schema.Table class now raises a deprecation warning when columns with the same name are defined. To replace a column a new parameter _schema.Table.append_column.replace_existing was added to the _schema.Table.append_column() method.
The _expression.ColumnCollection.contains_column() will now raises an error when called with a string, suggesting the caller to use in instead.
[sql] [removed] The "threadlocal" execution strategy, deprecated in 1.3, has been removed for 1.4, as well as the concept of "engine strategies" and the Engine.contextual_connect method. The "strategy='mock'" keyword argument is still accepted for now with a deprecation warning; use create_mock_engine() instead for this use case.
References: [#4632](http://www.sqlalchemy.org/trac/ticket/4632)
[sql] [removed] Removed the sqlalchemy.sql.visitors.iterate_depthfirst and sqlalchemy.sql.visitors.traverse_depthfirst functions. These functions were unused by any part of SQLAlchemy. The _sa.sql.visitors.iterate() and _sa.sql.visitors.traverse() functions are commonly used for these functions. Also removed unused options from the remaining functions including "column_collections", "schema_visitor".
[sql] [removed] Removed the concept of a bound engine from the Compiler object, and removed the .execute() and .scalar() methods from Compiler. These were essentially forgotten methods from over a decade ago and had no practical use, and it's not appropriate for the Compiler object itself to be maintaining a reference to an _engine.Engine.
[sql] [removed] Remove deprecated methods Compiled.compile, ClauseElement.__and__ and ClauseElement.__or__ and attribute Over.func.
Remove deprecated FromClause.count method. Please use the _functions.count function available from the func namespace.
References: [#4643](http://www.sqlalchemy.org/trac/ticket/4643)
[sql] [removed] Remove deprecated parameters text.bindparams and text.typemap. Please refer to the _expression.TextClause.bindparams() and _expression.TextClause.columns() methods.
Remove deprecated parameter Table.useexisting. Please use _schema.Table.extend_existing.
References: [#4643](http://www.sqlalchemy.org/trac/ticket/4643)
[sql] [renamed] _schema.Table parameter mustexist has been renamed to _schema.Table.must_exist and will now warn when used.
[sql] [renamed] The _expression.SelectBase.as_scalar() and _query.Query.as_scalar() methods have been renamed to _expression.SelectBase.scalar_subquery() and _query.Query.scalar_subquery(), respectively. The old names continue to exist within 1.4 series with a deprecation warning. In addition, the implicit coercion of _expression.SelectBase, _expression.Alias, and other SELECT oriented objects into scalar subqueries when evaluated in a column context is also deprecated, and emits a warning that the _expression.SelectBase.scalar_subquery() method should be called explicitly. This warning will in a later major release become an error, however the message will always be clear when _expression.SelectBase.scalar_subquery() needs to be invoked. The latter part of the change is for clarity and to reduce the implicit decisionmaking by the query coercion system. The Subquery.as_scalar() method, which was previously Alias.as_scalar, is also deprecated; .scalar_subquery() should be invoked directly from ` _expression.select() object or _query.Query object.
This change is part of the larger change to convert _expression.select() objects to no longer be directly part of the "from clause" class hierarchy, which also includes an overhaul of the clause coercion system.
References: [#4617](http://www.sqlalchemy.org/trac/ticket/4617)
[sql] [renamed] Several operators are renamed to achieve more consistent naming across SQLAlchemy.
The operator changes are:
isfalse is now is_false
isnot_distinct_from is now is_not_distinct_from
istrue is now is_true
notbetween is now not_between
notcontains is now not_contains
notendswith is now not_endswith
notilike is now not_ilike
notlike is now not_like
notmatch is now not_match
notstartswith is now not_startswith
nullsfirst is now nulls_first
nullslast is now nulls_last
isnot is now is_not
not_in_ is now not_in
Because these are core operators, the internal migration strategy for this change is to support legacy terms for an extended period of time -- if not indefinitely -- but update all documentation, tutorials, and internal usage to the new terms. The new terms are used to define the functions, and the legacy terms have been deprecated into aliases of the new terms.
References: [#5429](http://www.sqlalchemy.org/trac/ticket/5429), [#5435](http://www.sqlalchemy.org/trac/ticket/5435)
[sql] [postgresql] Allow specifying the data type when creating a Sequence in PostgreSQL by using the parameter Sequence.data_type.
References: [#5498](http://www.sqlalchemy.org/trac/ticket/5498)
[sql] [reflection] The "NO ACTION" keyword for foreign key "ON UPDATE" is now considered to be the default cascade for a foreign key on all supporting backends (SQlite, MySQL, PostgreSQL) and when detected is not included in the reflection dictionary; this is already the behavior for PostgreSQL and MySQL for all previous SQLAlchemy versions in any case. The "RESTRICT" keyword is positively stored when detected; PostgreSQL does report on this keyword, and MySQL as of version 8.0 does as well. On earlier MySQL versions, it is not reported by the database.
References: [#4741](http://www.sqlalchemy.org/trac/ticket/4741)
[sql] [reflection] Added support for reflecting "identity" columns, which are now returned as part of the structure returned by _reflection.Inspector.get_columns(). When reflecting full _schema.Table objects, identity columns will be represented using the _schema.Identity construct. Currently the supported backends are PostgreSQL >= 10, Oracle >= 12 and MSSQL (with different syntax and a subset of functionalities).
References: [#5324](http://www.sqlalchemy.org/trac/ticket/5324), [#5527](http://www.sqlalchemy.org/trac/ticket/5527)
## schema
[schema] [change] The Enum.create_constraint and Boolean.create_constraint parameters now default to False, indicating when a so-called "non-native" version of these two datatypes is created, a CHECK constraint will not be generated by default. These CHECK constraints present schema-management maintenance complexities that should be opted in to, rather than being turned on by default.
References: [#5367](http://www.sqlalchemy.org/trac/ticket/5367)
[schema] [bug] Cleaned up the internal str() for datatypes so that all types produce a string representation without any dialect present, including that it works for third-party dialect types without that dialect being present. The string representation defaults to being the UPPERCASE name of that type with nothing else.
References: [#4262](http://www.sqlalchemy.org/trac/ticket/4262)
[schema] [removed] Remove deprecated class Binary. Please use LargeBinary.
References: [#4643](http://www.sqlalchemy.org/trac/ticket/4643)
[schema] [renamed] Renamed the _schema.Table.tometadata() method to _schema.Table.to_metadata(). The previous name remains with a deprecation warning.
References: [#5413](http://www.sqlalchemy.org/trac/ticket/5413)
[schema] [sql] Added the _schema.Identity construct that can be used to configure identity columns rendered with GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY. Currently the supported backends are PostgreSQL >= 10, Oracle >= 12 and MSSQL (with different syntax and a subset of functionalities).
References: [#5324](http://www.sqlalchemy.org/trac/ticket/5324), [#5360](http://www.sqlalchemy.org/trac/ticket/5360), [#5362](http://www.sqlalchemy.org/trac/ticket/5362)
## extensions
[extensions] [usecase] Custom compiler constructs created using the sqlalchemy.ext.compiled extension will automatically add contextual information to the compiler when a custom construct is interpreted as an element in the columns clause of a SELECT statement, such that the custom element will be targetable as a key in result row mappings, which is the kind of targeting that the ORM uses in order to match column elements into result tuples.
References: [#4887](http://www.sqlalchemy.org/trac/ticket/4887)
[extensions] [change] Added new parameter _automap.AutomapBase.prepare.autoload_with which supersedes _automap.AutomapBase.prepare.reflect and _automap.AutomapBase.prepare.engine.
References: [#5142](http://www.sqlalchemy.org/trac/ticket/5142)
## postgresql
[postgresql] [usecase] Added support for PostgreSQL "readonly" and "deferrable" flags for all of psycopg2, asyncpg and pg8000 dialects. This takes advantage of a newly generalized version of the "isolation level" API to support other kinds of session attributes set via execution options that are reliably reset when connections are returned to the connection pool.
References: [#5549](http://www.sqlalchemy.org/trac/ticket/5549)
[postgresql] [usecase] The maximum buffer size for the BufferedRowResultProxy, which is used by dialects such as PostgreSQL when stream_results=True, can now be set to a number greater than 1000 and the buffer will grow to that size. Previously, the buffer would not go beyond 1000 even if the value were set larger. The growth of the buffer is also now based on a simple multiplying factor currently set to 5. Pull request courtesy Soumaya Mauthoor.
References: [#4914](http://www.sqlalchemy.org/trac/ticket/4914)
[postgresql] [change] When using the psycopg2 dialect for PostgreSQL, psycopg2 minimum version is set at 2.7. The psycopg2 dialect relies upon many features of psycopg2 released in the past few years, so to simplify the dialect, version 2.7, released in March, 2017 is now the minimum version required.
[postgresql] [performance] The psycopg2 dialect now defaults to using the very performant execute_values() psycopg2 extension for compiled INSERT statements, and also implements RETURNING support when this extension is used. This allows INSERT statements that even include an autoincremented SERIAL or IDENTITY value to run very fast while still being able to return the newly generated primary key values. The ORM will then integrate this new feature in a separate change.
References: [#5401](http://www.sqlalchemy.org/trac/ticket/5401)
[postgresql] [bug] The pg8000 dialect has been revised and modernized for the most recent version of the pg8000 driver for PostgreSQL. Changes to the dialect include:
All data types are now sent as text rather than binary.
Using adapters, custom types can be plugged in to pg8000.
Previously, named prepared statements were used for all statements. Now unnamed prepared statements are used by default, and named prepared statements can be used explicitly by calling the Connection.prepare() method, which returns a PreparedStatement object.
Pull request courtesy Tony Locke.
[postgresql] [deprecated] The pygresql and py-postgresql dialects are deprecated.
References: [#5189](http://www.sqlalchemy.org/trac/ticket/5189)
[postgresql] [removed] Remove support for deprecated engine URLs of the form postgres://; this has emitted a warning for many years and projects should be using postgresql://.
References: [#4643](http://www.sqlalchemy.org/trac/ticket/4643)
## mysql
[mysql] [feature] Added support for MariaDB Connector/Python to the mysql dialect. Original pull request courtesy Georg Richter.
References: [#5459](http://www.sqlalchemy.org/trac/ticket/5459)
[mysql] [usecase] Added a new dialect token "mariadb" that may be used in place of "mysql" in the _sa.create_engine() URL. This will deliver a MariaDB dialect subclass of the MySQLDialect in use that forces the "is_mariadb" flag to True. The dialect will raise an error if a server version string that does not indicate MariaDB in use is received. This is useful for MariaDB-specific testing scenarios as well as to support applications that are hardcoding to MariaDB-only concepts. As MariaDB and MySQL featuresets and usage patterns continue to diverge, this pattern may become more prominent.
References: [#5496](http://www.sqlalchemy.org/trac/ticket/5496)
[mysql] [usecase] Added support for use of the Sequence construct with MariaDB 10.3 and greater, as this is now supported by this database. The construct integrates with the _schema.Table object in the same way that it does for other databases like PostgreSQL and Oracle; if is present on the integer primary key "autoincrement" column, it is used to generate defaults. For backwards compatibility, to support a _schema.Table that has a Sequence on it to support sequence only databases like Oracle, while still not having the sequence fire off for MariaDB, the optional=True flag should be set, which indicates the sequence should only be used to generate the primary key if the target database offers no other option.
References: [#4976](http://www.sqlalchemy.org/trac/ticket/4976)
[mysql] [bug] The MySQL and MariaDB dialects now query from the information_schema.tables system view in order to determine if a particular table exists or not. Previously, the "DESCRIBE" command was used with an exception catch to detect non-existent, which would have the undesirable effect of emitting a ROLLBACK on the connection. There appeared to be legacy encoding issues which prevented the use of "SHOW TABLES", for this, but as MySQL support is now at 5.0.2 or above due to [#4189](http://www.sqlalchemy.org/trac/ticket/4189), the information_schema tables are now available in all cases.
[mysql] [bug] The "skip_locked" keyword used with with_for_update() will render "SKIP LOCKED" on all MySQL backends, meaning it will fail for MySQL less than version 8 and on current MariaDB backends. This is because those backends do not support "SKIP LOCKED" or any equivalent, so this error should not be silently ignored. This is upgraded from a warning in the 1.3 series.
References: [#5568](http://www.sqlalchemy.org/trac/ticket/5568)
[mysql] [bug] MySQL dialect's server_version_info tuple is now all numeric. String tokens like "MariaDB" are no longer present so that numeric comparison works in all cases. The .is_mariadb flag on the dialect should be consulted for whether or not mariadb was detected. Additionally removed structures meant to support extremely old MySQL versions 3.x and 4.x; the minimum MySQL version supported is now version 5.0.2.
References: [#4189](http://www.sqlalchemy.org/trac/ticket/4189)
[mysql] [deprecated] The OurSQL dialect is deprecated.
References: [#5189](http://www.sqlalchemy.org/trac/ticket/5189)
[mysql] [removed] Remove deprecated dialect mysql+gaerdbms that has been deprecated since version 1.0. Use the MySQLdb dialect directly.
Remove deprecated parameter quoting from mysql.ENUM and mysql.SET in the mysql dialect. The values passed to the enum or the set are quoted by SQLAlchemy when needed automatically.
References: [#4643](http://www.sqlalchemy.org/trac/ticket/4643)
## sqlite
[sqlite] [change] Dropped support for right-nested join rewriting to support old SQLite versions prior to 3.7.16, released in 2013. It is expected that all modern Python versions among those now supported should all include much newer versions of SQLite.
References: [#4895](http://www.sqlalchemy.org/trac/ticket/4895)
## mssql
[mssql] [feature] [sql] Added support for the _types.JSON datatype on the SQL Server dialect using the _mssql.JSON implementation, which implements SQL Server's JSON functionality against the NVARCHAR(max) datatype as per SQL Server documentation. Implementation courtesy Gord Thompson.
References: [#4384](http://www.sqlalchemy.org/trac/ticket/4384)
[mssql] [feature] Added support for "CREATE SEQUENCE" and full Sequence support for Microsoft SQL Server. This removes the deprecated feature of using Sequence objects to manipulate IDENTITY characteristics which should now be performed using mssql_identity_start and mssql_identity_increment as documented at mssql_identity. The change includes a new parameter Sequence.data_type to accommodate SQL Server's choice of datatype, which for that backend includes INTEGER, BIGINT, and DECIMAL(n, 0). The default starting value for SQL Server's version of Sequence has been set at 1; this default is now emitted within the CREATE SEQUENCE DDL for all backends.
References: [#4235](http://www.sqlalchemy.org/trac/ticket/4235), [#4633](http://www.sqlalchemy.org/trac/ticket/4633)
[mssql] [usecase] [postgresql] [reflection] [schema] Improved support for covering indexes (with INCLUDE columns). Added the ability for postgresql to render CREATE INDEX statements with an INCLUDE clause from Core. Index reflection also report INCLUDE columns separately for both mssql and postgresql (11+).
References: [#4458](http://www.sqlalchemy.org/trac/ticket/4458)
[mssql] [usecase] [postgresql] Added support for inspection / reflection of partial indexes / filtered indexes, i.e. those which use the mssql_where or postgresql_where parameters, with _schema.Index. The entry is both part of the dictionary returned by Inspector.get_indexes() as well as part of a reflected _schema.Index construct that was reflected. Pull request courtesy Ramon Williams.
References: [#4966](http://www.sqlalchemy.org/trac/ticket/4966)
[mssql] [usecase] [reflection] Added support for reflection of temporary tables with the SQL Server dialect. Table names that are prefixed by a pound sign "#" are now introspected from the MSSQL "tempdb" system catalog.
References: [#5506](http://www.sqlalchemy.org/trac/ticket/5506)
[mssql] [change] SQL Server OFFSET and FETCH keywords are now used for limit/offset, rather than using a window function, for SQL Server versions 11 and higher. TOP is
Note truncated.
[orm] [bug] Removed very old warning that states that passive_deletes is not intended for many-to-one relationships. While it is likely that in many c
Released: March 30, 2021
[orm] [bug] Removed very old warning that states that passive_deletes is not intended for many-to-one relationships. While it is likely that in many cases placing this parameter on a many-to-one relationship is not what was intended, there are use cases where delete cascade may want to be disallowed following from such a relationship.
References: #5983
[orm] [bug] Fixed issue where the process of joining two tables could fail if one of
the tables had an unrelated, unresolvable foreign key constraint which
would raise _exc.NoReferenceError within the join process, which
nonetheless could be bypassed to allow the join to complete. The logic
which tested the exception for significance within the process would make
assumptions about the construct which would fail.
References: #5952
[orm] [bug] Fixed issue where the _mutable.MutableComposite construct could be
placed into an invalid state when the parent object was already loaded, and
then covered by a subsequent query, due to the composite properties'
refresh handler replacing the object with a new one not handled by the
mutable extension.
References: #6001
[engine] [bug] Fixed bug where the "schema_translate_map" feature failed to be taken into
account for the use case of direct execution of
_schema.DefaultGenerator objects such as sequences, which included
the case where they were "pre-executed" in order to generate primary key
values when implicit_returning was disabled.
References: #5929
[schema] [bug] Fixed bug first introduced in as some combination of #2892,
#2919 nnd #3832 where the attachment events for a
_types.TypeDecorator would be doubled up against the "impl" class,
if the "impl" were also a _types.SchemaType. The real-world case
is any _types.TypeDecorator against _types.Enum or
_types.Boolean would get a doubled
_schema.CheckConstraint when the create_constraint=True flag
is set.
References: #6152
[schema] [bug] [sqlite] Fixed issue where the CHECK constraint generated by _types.Boolean
or _types.Enum would fail to render the naming convention
correctly after the first compilation, due to an unintended change of state
within the name given to the constraint. This issue was first introduced in
0.9 in the fix for issue #3067, and the fix revises the approach taken at
that time which appears to have been more involved than what was needed.
References: #6007
[schema] [bug] Repaired / implemented support for primary key constraint naming
conventions that use column names/keys/etc as part of the convention. In
particular, this includes that the PrimaryKeyConstraint object
that's automatically associated with a schema.Table will update
its name as new primary key _schema.Column objects are added to
the table and then to the constraint. Internal failure modes related to
this constraint construction process including no columns present, no name
present or blank name present are now accommodated.
References: #5919
[schema] [bug] Adjusted the logic that emits DROP statements for _schema.Sequence
objects among the dropping of multiple tables, such that all
_schema.Sequence objects are dropped after all tables, even if the
given _schema.Sequence is related only to a _schema.Table
object and not directly to the overall _schema.MetaData object.
The use case supports the same _schema.Sequence being associated
with more than one _schema.Table at a time.
References: #6071
[postgresql] [bug] Fixed issue where using _postgresql.aggregate_order_by would
return ARRAY(NullType) under certain conditions, interfering with
the ability of the result object to return data correctly.
References: #5989
[postgresql] [bug] [reflection] Fixed issue in PostgreSQL reflection where a column expressing "NOT NULL" will supersede the nullability of a corresponding domain.
References: #6161
[postgresql] [bug] [types] Adjusted the psycopg2 dialect to emit an explicit PostgreSQL-style cast for
bound parameters that contain ARRAY elements. This allows the full range of
datatypes to function correctly within arrays. The asyncpg dialect already
generated these internal casts in the final statement. This also includes
support for array slice updates as well as the PostgreSQL-specific
_postgresql.ARRAY.contains() method.
References: #6023
[mssql] [bug] [reflection] Fixed issue regarding SQL Server reflection for older SQL Server 2005 version, a call to sp_columns would not proceed correctly without being prefixed with the EXEC keyword. This method is not used in current 1.4 series.
References: #5921
[mysql] [bug] Fixed deprecation warnings that arose as a result of the release of PyMySQL 1.0, including deprecation warnings for the "db" and "passwd…
Released: February 1, 2021
[sql] [bug] Fixed bug where making use of the TypeEngine.with_variant() method
on a TypeDecorator type would fail to take into account the
dialect-specific mappings in use, due to a rule in TypeDecorator
that was instead attempting to check for chains of TypeDecorator
instances.
References: #5816
[postgresql] [bug] For SQLAlchemy 1.3 only, setup.py pins pg8000 to a version lower than 1.16.6. Version 1.16.6 and above is supported by SQLAlchemy 1.4. Pull request courtesy Giuseppe Lumia.
References: #5645
[postgresql] [bug] Fixed issue where using _schema.Table.to_metadata() (called
_schema.Table.tometadata() in 1.3) in conjunction with a PostgreSQL
_postgresql.ExcludeConstraint that made use of ad-hoc column
expressions would fail to copy correctly.
References: #5850
[mysql] [usecase] Casting to FLOAT is now supported in MySQL >= (8, 0, 17) and
MariaDb >= (10, 4, 5).
References: #5808
[mysql] [bug] [reflection] Fixed bug where MySQL server default reflection would fail for numeric values with a negation symbol present.
References: #5860
[mysql] [bug] Fixed long-lived bug in MySQL dialect where the maximum identifier length of 255 was too long for names of all types of constraints, not just indexes, all of which have a size limit of 64. As metadata naming conventions can create too-long names in this area, apply the limit to the identifier generator within the DDL compiler.
References: #5898
[mysql] [bug] Fixed deprecation warnings that arose as a result of the release of PyMySQL 1.0, including deprecation warnings for the "db" and "passwd" parameters now replaced with "database" and "password".
References: #5821
[mysql] [bug] Fixed regression from SQLAlchemy 1.3.20 caused by the fix for
#5462 which adds double-parenthesis for MySQL functional
expressions in indexes, as is required by the backend, this inadvertently
extended to include arbitrary _sql.text() expressions as well as
Alembic's internal textual component, which are required by Alembic for
arbitrary index expressions which don't imply double parenthesis. The
check has been narrowed to include only binary/ unary/functional
expressions directly.
References: #5800
[oracle] [bug] Fixed regression in Oracle dialect introduced by #4894 in SQLAlchemy 1.3.11 where use of a SQL expression in RETURNING for an UPDATE would fail to compile, due to a check for "server_default" when an arbitrary SQL expression is not a column.
References: #5813
[oracle] [bug] Fixed bug in Oracle dialect where retriving a CLOB/BLOB column via
_dml.Insert.returning() would fail as the LOB value would need to be
read when returned; additionally, repaired support for retrieval of Unicode
values via RETURNING under Python 2.
References: #5812
[bug] [ext] Fixed issue where the stringification that is sometimes called when
attempting to generate the "key" for the .c collection on a selectable
would fail if the column were an unlabeled custom SQL construct using the
sqlalchemy.ext.compiler extension, and did not provide a default
compilation form; while this seems like an unusual case, it can get invoked
for some ORM scenarios such as when the expression is used in an "order by"
in combination with joined eager loading. The issue is that the lack of a
default compiler function was raising CompileError and not
UnsupportedCompilationError.
References: #5836
[oracle] [bug] Fixed regression which occured due to #5755 which implemented isolation level support for Oracle. It has been reported that many Oracle
Released: December 18, 2020
[oracle] [bug] Fixed regression which occured due to #5755 which implemented
isolation level support for Oracle. It has been reported that many Oracle
accounts don't actually have permission to query the v$transaction
view so this feature has been altered to gracefully fallback when it fails
upon database connect, where the dialect will assume "READ COMMITTED" is
the default isolation level as was the case prior to SQLAlchemy 1.3.21.
However, explicit use of the _engine.Connection.get_isolation_level()
method must now necessarily raise an exception, as Oracle databases with
this restriction explicitly disallow the user from reading the current
isolation level.
References: #5784
[orm] [bug] Added a comprehensive check and an informative error message for the case where a mapped class, or a string mapped class name, is passed t
Released: December 17, 2020
[orm] [bug] Added a comprehensive check and an informative error message for the case
where a mapped class, or a string mapped class name, is passed to
_orm.relationship.secondary. This is an extremely common error
which warrants a clear message.
Additionally, added a new rule to the class registry resolution such that
with regards to the _orm.relationship.secondary parameter, if a
mapped class and its table are of the identical string name, the
Table will be favored when resolving this parameter. In all
other cases, the class continues to be favored if a class and table
share the identical name.
References: #5774
[orm] [bug] Fixed bug in _query.Query.update() where objects in the
_ormsession.Session that were already expired would be
unnecessarily SELECTed individually when they were refreshed by the
"evaluate"synchronize strategy.
References: #5664
[orm] [bug] Fixed bug involving the restore_load_context option of ORM events such
as _ormevent.InstanceEvents.load() such that the flag would not be
carried along to subclasses which were mapped after the event handler were
first established.
References: #5737
[sql] [bug] A warning is emmitted if a returning() method such as
_sql.Insert.returning() is called multiple times, as this does not
yet support additive operation. Version 1.4 will support additive
operation for this. Additionally, any combination of the
_sql.Insert.returning() and _sql.ValuesBase.return_defaults()
methods now raises an error as these methods are mutually exclusive;
previously the operation would fail silently.
References: #5691
[sql] [bug] Fixed structural compiler issue where some constructs such as MySQL /
PostgreSQL "on conflict / on duplicate key" would rely upon the state of
the _sql.Compiler object being fixed against their statement as
the top level statement, which would fail in cases where those statements
are branched from a different context, such as a DDL construct linked to a
SQL statement.
References: #5656
[postgresql] [usecase] Added new parameter _postgresql.ExcludeConstraint.ops to the
_postgresql.ExcludeConstraint object, to support operator class
specification with this constraint. Pull request courtesy Alon Menczer.
References: #5604
[postgresql] [bug] [mysql] Fixed regression introduced in 1.3.2 for the PostgreSQL dialect, also
copied out to the MySQL dialect's feature in 1.3.18, where usage of a non
_schema.Table construct such as _sql.text() as the argument
to _sql.Select.with_for_update.of would fail to be accommodated
correctly within the PostgreSQL or MySQL compilers.
References: #5729
[mysql] [bug] [reflection] Fixed issue where reflecting a server default on MariaDB only that contained a decimal point in the value would fail to be reflected correctly, leading towards a reflected table that lacked any server default.
References: #5744
[mysql] [sql] Added missing keywords to the RESERVED_WORDS list for the MySQL
dialect: action, level, mode, status, text, time.
Pull request courtesy Oscar Batori.
References: #5696
[sqlite] [usecase] Added sqlite_with_rowid=False dialect keyword to enable creating
tables as CREATE TABLE … WITHOUT ROWID. Patch courtesy Sean Anderson.
References: #5685
[mssql] [bug] Fixed bug where a CREATE INDEX statement was rendered incorrectly when
both mssql-include and mssql_where were specified. Pull request
courtesy @Adiorz.
References: #5751
[mssql] [bug] Added SQL Server code "01000" to the list of disconnect codes.
References: #5646
[mssql] [reflection] [sqlite] Fixed issue with composite primary key columns not being reported in the correct order. Patch courtesy @fulpm.
References: #5661
[oracle] [usecase] Implemented support for the SERIALIZABLE isolation level for Oracle
databases, as well as a real implementation for
_engine.Connection.get_isolation_level().
References: #5755
…backends, and will then be ignored. This is a deprecated behavior that will raise in SQLAlchemy 1.4, as an application that requests "skip locked" is…
Released: October 12, 2020
[orm] [bug] An ArgumentError with more detail is now raised if the target
parameter for _query.Query.join() is set to an unmapped object.
Prior to this change a less detailed AttributeError was raised.
Pull request courtesy Ramon Williams.
References: #4428
[orm] [bug] Fixed issue where using a loader option against a string attribute name that is not actually a mapped attribute, such as a plain Python descriptor, would raise an uninformative AttributeError; a descriptive error is now raised.
References: #4589
[engine] [bug] Fixed issue where a non-string object sent to
_exc.SQLAlchemyError or a subclass, as occurs with some third
party dialects, would fail to stringify correctly. Pull request
courtesy Andrzej Bartosiński.
References: #5599
[engine] [bug] Repaired a function-level import that was not using SQLAlchemy's standard late-import system within the sqlalchemy.exc module.
References: #5632
[sql] [bug] Fixed issue where the pickle.dumps() operation against
_expression.Over construct would produce a recursion overflow.
References: #5644
[sql] [bug] Fixed bug where an error was not raised in the case where a
_sql.column() were added to more than one _sql.table() at a
time. This raised correctly for the _schema.Column and
_schema.Table objects. An _exc.ArgumentError is now
raised when this occurs.
References: #5618
[postgresql] [usecase] The psycopg2 dialect now support PostgreSQL multiple host connections, by passing host/port combinations to the query string. Pull request courtesy Ramon Williams.
References: #4392
[postgresql] [bug] Adjusted the _types.ARRAY.Comparator.any() and
_types.ARRAY.Comparator.all() methods to implement a straight "NOT"
operation for negation, rather than negating the comparison operator.
References: #5518
[postgresql] [bug] Fixed issue where the _postgresql.ENUM type would not consult the
schema translate map when emitting a CREATE TYPE or DROP TYPE during the
test to see if the type exists or not. Additionally, repaired an issue
where if the same enum were encountered multiple times in a single DDL
sequence, the "check" query would run repeatedly rather than relying upon a
cached value.
References: #5520
[mysql] [usecase] Adjusted the MySQL dialect to correctly parenthesize functional index expressions as accepted by MySQL 8. Pull request courtesy Ramon Williams.
References: #5462
[mysql] [bug] The "skip_locked" keyword used with with_for_update() will emit a
warning when used on MariaDB backends, and will then be ignored. This is
a deprecated behavior that will raise in SQLAlchemy 1.4, as an application
that requests "skip locked" is looking for a non-blocking operation which
is not available on those backends.
References: #5568
[mysql] [bug] Fixed bug where an UPDATE statement against a JOIN using MySQL multi-table format would fail to include the table prefix for the target table if the statement had no WHERE clause, as only the WHERE clause were scanned to detect a "multi table update" at that particular point. The target is now also scanned if it's a JOIN to get the leftmost table as the primary table and the additional entries as additional FROM entries.
References: #5617
[mysql] [change] Add new MySQL reserved words: cube, lateral added in MySQL 8.0.1
and 8.0.14, respectively; this indicates that these terms will be quoted if
used as table or column identifier names.
References: #5539
[mssql] [bug] Fixed issue where a SQLAlchemy connection URI for Azure DW with
authentication=ActiveDirectoryIntegrated (and no username+password)
was not constructing the ODBC connection string in a way that was
acceptable to the Azure DW instance.
References: #5592
[bug] [pool] Fixed issue where the following pool parameters were not being propagated
to the new pool created when _engine.Engine.dispose() were called:
pre_ping, use_lifo. Additionally the recycle and
reset_on_return parameter is now propagated for the
_engine.AssertionPool class.
References: #5582
[bug] [associationproxy] [ext] An informative error is now raised when attempting to use an association proxy element as a plain column expression to be SELECTed from or used in a SQL function; this use case is not currently supported.
[bug] [tests] Fixed incompatibilities in the test suite when running against Pytest 6.x.
References: #5635
The change additionally adjusts the "automatically add ORDER BY columns when DISTINCT is present" behavior of ORM query, deprecated in 1.4, to more ac…
Released: August 17, 2020
[orm] [usecase] Adjusted the workings of the _orm.Mapper.all_orm_descriptors()
accessor to represent the attributes in the order that they are located in
a deterministic way, assuming the use of Python 3.6 or higher which
maintains the sorting order of class attributes based on how they were
declared. This sorting is not guaranteed to match the declared order of
attributes in all cases however; see the method documentation for the exact
scheme.
References: #5494
[declarative] [orm] [usecase] The name of the virtual column used when using the
_declarative.AbstractConcreteBase and
_declarative.ConcreteBase classes can now be customized, to allow
for models that have a column that is actually named type. Pull
request courtesy Jesse-Bakker.
References: #5513
[sql] [bug] Repaired an issue where the "ORDER BY" clause rendering a label name rather than a complete expression, which is particularly important for SQL Server, would fail to occur if the expression were enclosed in a parenthesized grouping in some cases. This case has been added to test support. The change additionally adjusts the "automatically add ORDER BY columns when DISTINCT is present" behavior of ORM query, deprecated in 1.4, to more accurately detect column expressions that are already present.
References: #5470
[sql] [bug] [datatypes] The LookupError message will now provide the user with up to four
possible values that a column is constrained to via the Enum.
Values longer than 11 characters will be truncated and replaced with
ellipses. Pull request courtesy Ramon Williams.
References: #4733
[sql] [bug] Fixed issue where the
_engine.Connection.execution_options.schema_translate_map
feature would not take effect when the _schema.Sequence.next_value()
function function for a _schema.Sequence were used in the
_schema.Column.server_default parameter and the create table
DDL were emitted.
References: #5500
[postgresql] [bug] Fixed issue where the return type for the various RANGE comparison
operators would itself be the same RANGE type rather than BOOLEAN, which
would cause an undesirable result in the case that a
TypeDecorator that defined result-processing behavior were in
use. Pull request courtesy Jim Bosch.
References: #5476
[mysql] [usecase] The MySQL dialect will render FROM DUAL for a SELECT statement that has no FROM clause but has a WHERE clause. This allows things like "SELECT 1 WHERE EXISTS (subquery)" kinds of queries to be used as well as other use cases.
References: #5481
[mysql] [bug] Fixed an issue where CREATE TABLE statements were not specifying the COLLATE keyword correctly.
References: #5411
[mysql] [bug] Added MariaDB code 1927 to the list of "disconnect" codes, as recent MariaDB versions apparently use this code when the database server was stopped.
References: #5493
[sqlite] [bug] [mssql] [reflection] Applied a sweep through all included dialects to ensure names that contain
single or double quotes are properly escaped when querying system tables,
for all Inspector methods that accept object names as an argument
(e.g. table names, view names, etc). SQLite and MSSQL contained two
quoting issues that were repaired.
References: #5456
[mssql] [bug] [sql] Fixed bug where the mssql dialect incorrectly escaped object names that contained ']' character(s).
References: #5467
[orm] [usecase] Improve error message when using _query.Query.filter_by() in a query where the first entity is not a mapped class.
Released: June 25, 2020
[orm] [usecase] Improve error message when using _query.Query.filter_by() in
a query where the first entity is not a mapped class.
References: #5326
[orm] [usecase] Added a new parameter _orm.query_expression.default_expr to the
_orm.query_expression() construct, which will be appled to queries
automatically if the _orm.with_expression() option is not used. Pull
request courtesy Haoyu Sun.
References: #5198
[engine] [bug] Further refinements to the fixes to the "reset" agent fixed in #5326, which now emits a warning when it is not being correctly invoked and corrects for the behavior. Additional scenarios have been identified and fixed where this warning was being emitted.
References: #5326
[engine] [bug] Fixed issue in URL object where stringifying the object
would not URL encode special characters, preventing the URL from being
re-consumable as a real URL. Pull request courtesy Miguel Grinberg.
References: #5341
[sql] [schema] Introduce IdentityOptions to store common parameters for
sequences and identity columns.
References: #5324
[sql] [usecase] Added a ".schema" parameter to the _expression.table() construct,
allowing ad-hoc table expressions to also include a schema name.
Pull request courtesy Dylan Modesitt.
References: #5309
[sql] [bug] Correctly apply self_group in type_coerce element.
The type coerce element did not correctly apply grouping rules when using in an expression
References: #5344
[sql] [change] [sybase] Added .offset support to sybase dialect.
Pull request courtesy Alan D. Snow.
References: #5294
[sql] [bug] Added Select.with_hint() output to the generic SQL string that is
produced when calling str() on a statement. Previously, this clause
would be omitted under the assumption that it was dialect specific.
The hint text is presented within brackets to indicate the rendering
of such hints varies among backends.
References: #5353
[schema] [bug] Fixed issue where dialect_options were omitted when a
database object (e.g., Table) was copied using
tometadata().
References: #5276
[mysql] [usecase] Implemented row-level locking support for mysql. Pull request courtesy Quentin Somerville.
References: #4860
[sqlite] [bug] Added "exists" to the list of reserved words for SQLite so that this word will be quoted when used as a label or column name. Pull request courtesy Thodoris Sotiropoulos.
References: #5395
[sqlite] [usecase] SQLite 3.31 added support for computed column. This change enables their support in SQLAlchemy when targeting SQLite.
References: #5297
[mssql] [bug] Refined the logic used by the SQL Server dialect to interpret multi-part schema names that contain many dots, to not actually lose any dots if the name does not have bracking or quoting used, and additionally to support a "dbname" token that has many parts including that it may have multiple, independently-bracketed sections.
[mssql] [bug] [pyodbc] Fixed an issue in the pyodbc connector such that a warning about pyodbc "drivername" would be emitted when using a totally empty URL. Empty URLs are normal when producing a non-connected dialect object or when using the "creator" argument to create_engine(). The warning now only emits if the driver name is missing but other parameters are still present.
References: #5346
[mssql] [bug] Fixed issue with assembling the ODBC connection string for the pyodbc DBAPI. Tokens containing semicolons and/or braces "{}" were not being correctly escaped, causing the ODBC driver to misinterpret the connection string attributes.
References: #5373
[mssql] [bug] Fixed issue where datetime.time parameters were being converted to
datetime.datetime, making them incompatible with comparisons like
>= against an actual _mssql.TIME column.
References: #5339
[mssql] [bug] Fixed an issue where the is_disconnect function in the SQL Server
pyodbc dialect was incorrectly reporting the disconnect state when the
exception messsage had a substring that matched a SQL Server ODBC error
code.
References: #5359
[mssql] [change] Moved the supports_sane_rowcount_returning = False requirement from
the PyODBCConnector level to the MSDialect_pyodbc since pyodbc
does work properly in some circumstances.
References: #5321
[oracle] [bug] [reflection] Fixed bug in Oracle dialect where indexes that contain the full set of primary key columns would be mistaken as the primary key index itself, which is omitted, even if there were multiples. The check has been refined to compare the name of the primary key constraint against the index name itself, rather than trying to guess based on the columns present in the index.
References: #5421
--raw to the examples.performance suite
which will dump the raw profile test for consumption by any
number of profiling visualizer tools. Removed the "runsnake"
option as runsnake is very hard to build at this point;…been installed, otherwise fall back to the (now deprecated) internal Firebird dialect.
Released: May 13, 2020
[orm] [bug] Fixed bug where using with_polymorphic() as the target of a join via
RelationshipComparator.of_type() on a mapper that already has a
subquery-based with_polymorphic setting that's equivalent to the one
requested would not correctly alias the ON clause in the join.
References: #5288
[orm] [bug] Fixed issue in the area of where loader options such as selectinload() interact with the baked query system, such that the caching of a query is not supposed to occur if the loader options themselves have elements such as with_polymorphic() objects in them that currently are not cache-compatible. The baked loader could sometimes not fully invalidate itself in these some of these scenarios leading to missed eager loads.
References: #5303
[orm] [bug] Modified the internal "identity set" implementation, which is a set that
hashes objects on their id() rather than their hash values, to not actually
call the __hash__() method of the objects, which are typically
user-mapped objects. Some methods were calling this method as a side
effect of the implementation.
References: #5304
[orm] [bug] An informative error message is raised when an ORM many-to-one comparison
is attempted against an object that is not an actual mapped instance.
Comparisons such as those to scalar subqueries aren't supported;
generalized comparison with subqueries is better achieved using
~.RelationshipProperty.Comparator.has().
References: #5269
[orm] [usecase] Added an accessor ColumnProperty.Comparator.expressions which
provides access to the group of columns mapped under a multi-column
ColumnProperty attribute.
References: #5262
[orm] [usecase] Introduce _orm.relationship.sync_backref flag in a relationship
to control if the synchronization events that mutate the in-Python
attributes are added. This supersedes the previous change #5149,
which warned that viewonly=True relationship target of a
back_populates or backref configuration would be disallowed.
References: #5237
[engine] [bug] Fixed fairly critical issue where the DBAPI connection could be returned to the connection pool while still in an un-rolled-back state. The reset agent responsible for rolling back the connection could be corrupted in the case that the transaction was "closed" without being rolled back or committed, which can occur in some scenarios when using ORM sessions and emitting .close() in a certain pattern involving savepoints. The fix ensures that the reset agent is always active.
References: #5326
[schema] Add comment attribute to _schema.Column __repr__ method.
References: #4138
[schema] [bug] Fixed issue where an Index that is deferred in being associated
with a table, such as as when it contains a Column that is not
associated with any Table yet, would fail to attach correctly if
it also contained a non table-oriented expession.
References: #5298
[schema] [bug] A warning is emitted when making use of the MetaData.sorted_tables
attribute as well as the _schema.sort_tables() function, and the
given tables cannot be correctly sorted due to a cyclic dependency between
foreign key constraints. In this case, the functions will no longer sort
the involved tables by foreign key, and a warning will be emitted. Other
tables that are not part of the cycle will still be returned in dependency
order. Previously, the sorted_table routines would return a collection that
would unconditionally omit all foreign keys when a cycle was detected, and
no warning was emitted.
References: #5316
[postgresql] [usecase] Added support for columns or type ARRAY of Enum,
JSON or _postgresql.JSONB in PostgreSQL.
Previously a workaround was required in these use cases.
References: #5265
[postgresql] [usecase] Raise an explicit exc.CompileError when adding a table with a
column of type ARRAY of Enum configured with
Enum.native_enum set to False when
Enum.create_constraint is not set to False
References: #5266
[mssql] [bug] [reflection] Fix a regression introduced by the reflection of computed column in MSSQL when using the legacy TDS version 4.2. The dialect will try to detect the protocol version of first connect and run in compatibility mode if it cannot detect it.
References: #5255
[mssql] [bug] [reflection] Fix a regression introduced by the reflection of computed column in
MSSQL when using SQL server versions before 2012, which does not support
the concat function.
References: #5271
[oracle] [bug] Some modifications to how the cx_oracle dialect sets up per-column outputtype handlers for LOB and numeric datatypes to adjust for potential changes coming in cx_Oracle 8.
References: #5246
[oracle] [bug] [performance] Changed the implementation of fetching CLOB and BLOB objects to use cx_Oracle's native implementation which fetches CLOB/BLOB objects inline with other result columns, rather than performing a separate fetch. As always, this can be disabled by setting auto_convert_lobs to False.
As part of this change, the behavior of a CLOB that was given a blank string on INSERT now returns None on SELECT, which is now consistent with that of VARCHAR on Oracle.
References: #5314
[firebird] [change] Adjusted dialect loading for firebird:// URIs so the external
sqlalchemy-firebird dialect will be used if it has been installed,
otherwise fall back to the (now deprecated) internal Firebird dialect.
References: #5278
[orm] [bug] Fixed bug in orm.selectinload() loading option where two or more loaders that represent different relationships with the same string key n
Released: April 7, 2020
[orm] [bug] Fixed bug in orm.selectinload() loading option where two or more
loaders that represent different relationships with the same string key
name as referenced from a single orm.with_polymorphic() construct
with multiple subclass mappers would fail to invoke each subqueryload
separately, instead making use of a single string-based slot that would
prevent the other loaders from being invoked.
References: #5228
[orm] [performance] Modified the queries used by subqueryload and selectinload to no longer ORDER BY the primary key of the parent entity; this ordering was there to allow the rows as they come in to be copied into lists directly with a minimal level of Python-side collation. However, these ORDER BY clauses can negatively impact the performance of the query as in many scenarios these columns are derived from a subquery or are otherwise not actual primary key columns such that SQL planners cannot make use of indexes. The Python-side collation uses the native itertools.group_by() to collate the incoming rows, and has been modified to allow multiple row-groups-per-parent to be assembled together using list.extend(), which should still allow for relatively fast Python-side performance. There will still be an ORDER BY present for a relationship that includes an explicit order_by parameter, however this is the only ORDER BY that will be added to the query for both kinds of loading.
References: #5162
[orm] [bug] Fixed issue where a lazyload that uses session-local "get" against a target many-to-one relationship where an object with the correct primary key is present, however it's an instance of a sibling class, does not correctly return None as is the case when the lazy loader actually emits a load for that row.
References: #5210
[bug] [declarative] [orm] The string argument accepted as the first positional argument by the
relationship() function when using the Declarative API is no longer
interpreted using the Python eval() function; instead, the name is dot
separated and the names are looked up directly in the name resolution
dictionary without treating the value as a Python expression. However,
passing a string argument to the other relationship() parameters
that necessarily must accept Python expressions will still use eval();
the documentation has been clarified to ensure that there is no ambiguity
that this is in use.
References: #5238
[sql] [types] Add ability to literal compile a DateTime, Date
or :class:"Time" when using the string dialect for debugging purposes.
This change does not impact real dialect implementation that retain
their current behavior.
References: #5052
[schema] [reflection] Added support for reflection of "computed" columns, which are now returned
as part of the structure returned by Inspector.get_columns().
When reflecting full Table objects, computed columns will
be represented using the Computed construct.
References: #5063
[postgresql] [bug] Fixed issue where a "covering" index, e.g. those which have an INCLUDE clause, would be reflected including all the columns in INCLUDE clause as regular columns. A warning is now emitted if these additional columns are detected indicating that they are currently ignored. Note that full support for "covering" indexes is part of #4458. Pull request courtesy Marat Sharafutdinov.
References: #5205
[mysql] [bug] Fixed issue in MySQL dialect when connecting to a psuedo-MySQL database such as that provided by ProxySQL, the up front check for isolation level when it returns no row will not prevent the dialect from continuing to connect. A warning is emitted that the isolation level could not be detected.
References: #5239
[sqlite] [usecase] Implemented AUTOCOMMIT isolation level for SQLite when using pysqlite.
References: #5164
[mssql] [mysql] [oracle] [usecase] Added support for ColumnOperators.is_distinct_from() and
ColumnOperators.isnot_distinct_from() to SQL Server,
MySQL, and Oracle.
References: #5137
[oracle] [usecase] Implemented AUTOCOMMIT isolation level for Oracle when using cx_Oracle. Also added a fixed default isolation level of READ COMMITTED for Oracle.
References: #5200
[oracle] [bug] [reflection] Fixed regression / incorrect fix caused by fix for #5146 where the Oracle dialect reads from the "all_tab_comments" view to get table comments but fails to accommodate for the current owner of the table being requested, causing it to read the wrong comment if multiple tables of the same name exist in multiple schemas.
References: #5146
[bug] [tests] Fixed an issue that prevented the test suite from running with the recently released py.test 5.4.0.
References: #5201
[enum] [types] The Enum type now supports the parameter Enum.length
to specify the length of the VARCHAR column to create when using
non native enums by setting Enum.native_enum to False
References: #5183
[installer] Ensured that the "pyproject.toml" file is not included in builds, as the presence of this file indicates to pip that a pep-517 installation process should be used. As this mode of operation appears to be not well supported by current tools / distros, these problems are avoided within the scope of SQLAlchemy installation by omitting the file.
References: #5207
[orm] [bug] Adjusted the error message emitted by Query.join() when a left hand side can't be located that the Query.select_from() method is the best
Released: March 11, 2020
[orm] [bug] Adjusted the error message emitted by Query.join() when a left hand
side can't be located that the Query.select_from() method is the
best way to resolve the issue. Also, within the 1.3 series, used a
deterministic ordering when determining the FROM clause from a given column
entity passed to Query so that the same expression is determined
each time.
References: #5194
[orm] [bug] Fixed regression in 1.3.14 due to #4849 where a sys.exc_info() call failed to be invoked correctly when a flush error would occur. Test coverage has been added for this exception case.
References: #5196
[general] [bug] [py3k] Applied an explicit "cause" to most if not all internally raised exceptions that are raised from within an internal exception c
Released: March 10, 2020
[general] [bug] [py3k] Applied an explicit "cause" to most if not all internally raised exceptions
that are raised from within an internal exception catch, to avoid
misleading stacktraces that suggest an error within the handling of an
exception. While it would be preferable to suppress the internally caught
exception in the way that the __suppress_context__ attribute would,
there does not as yet seem to be a way to do this without suppressing an
enclosing user constructed context, so for now it exposes the internally
caught exception as the cause so that full information about the context
of the error is maintained.
References: #4849
[orm] [bug] Fixed regression caused in 1.3.13 by #5056 where a refactor of the
ORM path registry system made it such that a path could no longer be
compared to an empty tuple, which can occur in a particular kind of joined
eager loading path. The "empty tuple" use case has been resolved so that
the path registry is compared to a path registry in all cases; the
PathRegistry object itself now implements __eq__() and
__ne__() methods which will take place for all equality comparisons and
continue to succeed in the not anticipated case that a non-
PathRegistry object is compared, while emitting a warning that
this object should not be the subject of the comparison.
References: #5110
[orm] [bug] Setting a relationship to viewonly=True which is also the target of a back_populates or backref configuration will now emit a warning and eventually be disallowed. back_populates refers specifically to mutation of an attribute or collection, which is disallowed when the attribute is subject to viewonly=True. The viewonly attribute is not subject to persistence behaviors which means it will not reflect correct results when it is locally mutated.
References: #5149
[orm] [bug] Fixed an additional regression in the same area as that of #5080
introduced in 1.3.0b3 via #4468 where the ability to create a
joined option across a with_polymorphic() into a relationship
against the base class of that with_polymorphic, and then further into
regular mapped relationships would fail as the base class component would
not add itself to the load path in a way that could be located by the
loader strategy. The changes applied in #5080 have been further
refined to also accommodate this scenario.
References: #5121
[orm] [usecase] Added a new flag InstanceEvents.restore_load_context and
SessionEvents.restore_load_context which apply to the
InstanceEvents.load(), InstanceEvents.refresh(), and
SessionEvents.loaded_as_persistent() events, which when set will
restore the "load context" of the object after the event hook has been
called. This ensures that the object remains within the "loader context"
of the load operation that is already ongoing, rather than the object being
transferred to a new load context due to refresh operations which may have
occurred in the event. A warning is now emitted when this condition occurs,
which recommends use of the flag to resolve this case. The flag is
"opt-in" so that there is no risk introduced to existing applications.
The change additionally adds support for the raw=True flag to
session lifecycle events.
References: #5129
[engine] [bug] Expanded the scope of cursor/connection cleanup when a statement is executed to include when the result object fails to be constructed, or an after_cursor_execute() event raises an error, or autocommit / autoclose fails. This allows the DBAPI cursor to be cleaned up on failure and for connectionless execution allows the connection to be closed out and returned to the connection pool, where previously it waiting until garbage collection would trigger a pool return.
References: #5182
[sql] [bug] [postgresql] Fixed bug where a CTE of an INSERT/UPDATE/DELETE that also uses RETURNING could then not be SELECTed from directly, as the internal state of the compiler would try to treat the outer SELECT as a DELETE statement itself and access nonexistent state.
References: #5181
[postgresql] [bug] Fixed issue where the "schema_translate_map" feature would not work with a
PostgreSQL native enumeration type (i.e. Enum,
postgresql.ENUM) in that while the "CREATE TYPE" statement would
be emitted with the correct schema, the schema would not be rendered in
the CREATE TABLE statement at the point at which the enumeration was
referenced.
References: #5158
[postgresql] [bug] [reflection] Fixed bug where PostgreSQL reflection of CHECK constraints would fail to parse the constraint if the SQL text contained newline characters. The regular expression has been adjusted to accommodate for this case. Pull request courtesy Eric Borczuk.
References: #5170
[mysql] [bug] Fixed issue in MySQL mysql.Insert.on_duplicate_key_update() construct
where using a SQL function or other composed expression for a column argument
would not properly render the VALUES keyword surrounding the column
itself.
References: #5173
[mssql] [bug] Fixed issue where the mssql.DATETIMEOFFSET type would not
accommodate for the None value, introduced as part of the series of
fixes for this type first introduced in #4983, #5045.
Additionally, added support for passing a backend-specific date formatted
string through this type, as is typically allowed for date/time types on
most other DBAPIs.
References: #5132
[oracle] [bug] Fixed a reflection bug where table comments could only be retrieved for tables actually owned by the user but not for tables visible to the user but owned by someone else. Pull request courtesy Dave Hirschfeld.
References: #5146
[bug] [performance] Revised an internal change to the test system added as a result of #5085 where a testing-related module per dialect would be loaded unconditionally upon making use of that dialect, pulling in SQLAlchemy's testing framework as well as the ORM into the module import space. This would only impact initial startup time and memory to a modest extent, however it's best that these additional modules aren't reverse-dependent on straight Core usage.
References: #5180
[bug] [installation] Vendored the inspect.formatannotation function inside of
sqlalchemy.util.compat, which is needed for the vendored version of
inspect.formatargspec. The function is not documented in cPython and
is not guaranteed to be available in future Python versions.
References: #5138
[ext] [usecase] Added keyword arguments to the MutableList.sort() function so that a
key function as well as the "reverse" keyword argument can be provided.
References: #5114
[orm] [bug] [engine] Added test support and repaired a wide variety of unnecessary reference cycles created for short-lived objects, mostly in the are
Released: January 22, 2020
[orm] [bug] [engine] Added test support and repaired a wide variety of unnecessary reference cycles created for short-lived objects, mostly in the area of ORM queries. Thanks much to Carson Ip for the help on this.
[orm] [bug] Fixed regression in loader options introduced in 1.3.0b3 via #4468
where the ability to create a loader option using
PropComparator.of_type() targeting an aliased entity that is an
inheriting subclass of the entity which the preceding relationship refers
to would fail to produce a matching path. See also #5082 fixed
in this same release which involves a similar kind of issue.
References: #5107
[orm] [bug] Fixed regression in joined eager loading introduced in 1.3.0b3 via
#4468 where the ability to create a joined option across a
with_polymorphic() into a polymorphic subclass using
RelationshipProperty.of_type() and then further along regular mapped
relationships would fail as the polymorphic subclass would not add itself
to the load path in a way that could be located by the loader strategy. A
tweak has been made to resolve this scenario.
References: #5082
[orm] [performance] Identified a performance issue in the system by which a join is constructed based on a mapped relationship. The clause adaption system would be used for the majority of join expressions including in the common case where no adaptation is needed. The conditions under which this adaptation occur have been refined so that average non-aliased joins along a simple relationship without a "secondary" table use about 70% less function calls.
[orm] [bug] Repaired a warning in the ORM flush process that was not covered by test coverage when deleting objects that use the "version_id" feature. This warning is generally unreachable unless using a dialect that sets the "supports_sane_rowcount" flag to False, which is not typically the case however is possible for some MySQL configurations as well as older Firebird drivers, and likely some third party dialects.
References: #5068
[orm] [bug] Fixed bug where usage of joined eager loading would not properly wrap the
query inside of a subquery when Query.group_by() were used against
the query. When any kind of result-limiting approach is used, such as
DISTINCT, LIMIT, OFFSET, joined eager loading embeds the row-limited query
inside of a subquery so that the collection results are not impacted. For
some reason, the presence of GROUP BY was never included in this criterion,
even though it has a similar effect as using DISTINCT. Additionally, the
bug would prevent using GROUP BY at all for a joined eager load query for
most database platforms which forbid non-aggregated, non-grouped columns
from being in the query, as the additional columns for the joined eager
load would not be accepted by the database.
References: #5065
[engine] [bug] Fixed issue where the collection of value processors on a
Compiled object would be mutated when "expanding IN" parameters
were used with a datatype that has bind value processors; in particular,
this would mean that when using statement caching and/or baked queries, the
same compiled._bind_processors collection would be mutated concurrently.
Since these processors are the same function for a given bind parameter
namespace every time, there was no actual negative effect of this issue,
however, the execution of a Compiled object should never be
causing any changes in its state, especially given that they are intended
to be thread-safe and reusable once fully constructed.
References: #5048
[sql] [usecase] A function created using GenericFunction can now specify that the
name of the function should be rendered with or without quotes by assigning
the quoted_name construct to the .name element of the object.
Prior to 1.3.4, quoting was never applied to function names, and some
quoting was introduced in #4467 but no means to force quoting for
a mixed case name was available. Additionally, the quoted_name
construct when used as the name will properly register its lowercase name
in the function registry so that the name continues to be available via the
func. registry.
References: #5079
[postgresql] [bug] Fixed issue where the PostgreSQL dialect would fail to parse a reflected CHECK constraint that was a boolean-valued function (as opposed to a boolean-valued expression).
References: #5039
[postgresql] [tests] Improved detection of two phase transactions requirement for the PostgreSQL database by testing that max_prepared_transactions is set to a value greater than 0. Pull request courtesy Federico Caselli.
References: #5057
[postgresql] [usecase] Added support for prefixes to the CTE construct, to allow
support for Postgresql 12 "MATERIALIZED" and "NOT MATERIALIZED" phrases.
Pull request courtesy Marat Sharafutdinov.
References: #5040
[mssql] [bug] Fixed issue where a timezone-aware datetime value being converted to
string for use as a parameter value of a mssql.DATETIMEOFFSET
column was omitting the fractional seconds.
References: #5045
[bug] [ext] Fixed bug in sqlalchemy.ext.serializer where a unique
BindParameter object could conflict with itself if it were
present in the mapping itself, as well as the filter condition of the
query, as one side would be used against the non-deserialized version and
the other side would use the deserialized version. Logic is added to
BindParameter similar to its "clone" method which will uniquify
the parameter name upon deserialize so that it doesn't conflict with its
original.
References: #5086
[bug] [tests] Fixed a few test failures which would occur on Windows due to SQLite file locking issues, as well as some timing issues in connection pool related tests; pull request courtesy Federico Caselli.
References: #4946
This keyword argument and the others passed to select() will ultimately be deprecated for SQLAlchemy 2.0.
Released: December 16, 2019
[orm] [bug] Fixed issue involving lazy="raise" strategy where an ORM delete of an
object would raise for a simple "use-get" style many-to-one relationship
that had lazy="raise" configured. This is inconsistent vs. the change
introduced in 1.3 as part of #4353, where it was established that
a history operation that does not expect emit SQL should bypass the
lazy="raise" check, and instead effectively treat it as
lazy="raise_on_sql" for this case. The fix adjusts the lazy loader
strategy to not raise for the case where the lazy load was instructed that
it should not emit SQL if the object were not present.
References: #4997
[orm] [bug] Fixed regression introduced in 1.3.0 related to the association proxy
refactor in #4351 that prevented composite() attributes
from working in terms of an association proxy that references them.
References: #5000
[orm] [bug] Setting persistence-related flags on relationship() while also
setting viewonly=True will now emit a regular warning, as these flags do
not make sense for a viewonly=True relationship. In particular, the
"cascade" settings have their own warning that is generated based on the
individual values, such as "delete, delete-orphan", that should not apply
to a viewonly relationship. Note however that in the case of "cascade",
these settings are still erroneously taking effect even though the
relationship is set up as "viewonly". In 1.4, all persistence-related
cascade settings will be disallowed on a viewonly=True relationship in
order to resolve this issue.
References: #4993
[orm] [bug] [py3k] Fixed issue where when assigning a collection to itself as a slice, the
mutation operation would fail as it would first erase the assigned
collection inadvertently. As an assignment that does not change the
contents should not generate events, the operation is now a no-op. Note
that the fix only applies to Python 3; in Python 2, the __setitem__
hook isn't called in this case; __setslice__ is used instead which
recreates the list item-by-item in all cases.
References: #4990
[orm] [bug] Fixed issue where by if the "begin" of a transaction failed at the Core
engine/connection level, such as due to network error or database is locked
for some transactional recipes, within the context of the Session
procuring that connection from the conneciton pool and then immediately
returning it, the ORM Session would not close the connection
despite this connection not being stored within the state of that
Session. This would lead to the connection being cleaned out by
the connection pool weakref handler within garbage collection which is an
unpreferred codepath that in some special configurations can emit errors in
standard error.
References: #5034
[sql] [bug] Fixed bug where "distinct" keyword passed to select() would not
treat a string value as a "label reference" in the same way that the
select.distinct() does; it would instead raise unconditionally. This
keyword argument and the others passed to select() will ultimately
be deprecated for SQLAlchemy 2.0.
References: #5028
[sql] [bug] Changed the text of the exception for "Can't resolve label reference" to include other kinds of label coercions, namely that "DISTINCT" is also in this category under the PostgreSQL dialect.
[sqlite] [bug] Fixed issue to workaround SQLite's behavior of assigning "numeric" affinity
to JSON datatypes, first described at change_3850, which returns
scalar numeric JSON values as a number and not as a string that can be JSON
deserialized. The SQLite-specific JSON deserializer now gracefully
degrades for this case as an exception and bypasses deserialization for
single numeric values, as from a JSON perspective they are already
deserialized.
References: #5014
[mssql] [bug] Repaired support for the mssql.DATETIMEOFFSET datatype on PyODBC,
by adding PyODBC-level result handlers as it does not include native
support for this datatype. This includes usage of the Python 3 "timezone"
tzinfo subclass in order to set up a timezone, which on Python 2 makes
use of a minimal backport of "timezone" in sqlalchemy.util.
References: #4983
[orm] [usecase] Added accessor Query.is_single_entity() to Query, which will indicate if the results returned by this Query will be a list of ORM enti
Released: November 11, 2019
[orm] [usecase] Added accessor Query.is_single_entity() to Query, which
will indicate if the results returned by this Query will be a
list of ORM entities, or a tuple of entities or column expressions.
SQLAlchemy hopes to improve upon the behavior of single entity / tuples in
future releases such that the behavior would be explicit up front, however
this attribute should be helpful with the current behavior. Pull request
courtesy Patrick Hayes.
References: #4934
[orm] [bug] The relationship.omit_join flag was not intended to be
manually set to True, and will now emit a warning when this occurs. The
omit_join optimization is detected automatically, and the omit_join
flag was only intended to disable the optimization in the hypothetical case
that the optimization may have interfered with correct results, which has
not been observed with the modern version of this feature. Setting the
flag to True when it is not automatically detected may cause the selectin
load feature to not work correctly when a non-default primary join
condition is in use.
References: #4954
[orm] [bug] A warning is emitted if a primary key value is passed to Query.get()
that consists of None for all primary key column positions. Previously,
passing a single None outside of a tuple would raise a TypeError and
passing a composite None (tuple of None values) would silently pass
through. The fix now coerces the single None into a tuple where it is
handled consistently with the other None conditions. Thanks to Lev
Izraelit for the help with this.
References: #4915
[orm] [bug] The BakedQuery will not cache a query that was modified by a
QueryEvents.before_compile() event, so that compilation hooks that
may be applying ad-hoc modifications to queries will take effect on each
run. In particular this is helpful for events that modify queries used in
lazy loading as well as eager loading such as "select in" loading. In
order to re-enable caching for a query modified by this event, a new
flag bake_ok is added; see baked_with_before_compile for
details.
A longer term plan to provide a new form of SQL caching should solve this kind of issue more comprehensively.
References: #4947
[orm] [bug] Fixed ORM bug where a "secondary" table that referred to a selectable which
in some way would refer to the local primary table would apply aliasing to
both sides of the join condition when a relationship-related join, either
via Query.join() or by joinedload(), were generated. The
"local" side is now excluded.
References: #4974
[engine] [bug] Fixed bug where parameter repr as used in logging and error reporting needs additional context in order to distinguish between a list of parameters for a single statement and a list of parameter lists, as the "list of lists" structure could also indicate a single parameter list where the first parameter itself is a list, such as for an array parameter. The engine/connection now passes in an additional boolean indicating how the parameters should be considered. The only SQLAlchemy backend that expects arrays as parameters is that of psycopg2 which uses pyformat parameters, so this issue has not been too apparent, however as other drivers that use positional gain more features it is important that this be supported. It also eliminates the need for the parameter repr function to guess based on the parameter structure passed.
References: #4902
[engine] [bug] [postgresql] Fixed bug in Inspector where the cache key generation did not
take into account arguments passed in the form of tuples, such as the tuple
of view name styles to return for the PostgreSQL dialect. This would lead
the inspector to cache too generally for a more specific set of criteria.
The logic has been adjusted to include every keyword element in the cache,
as every argument is expected to be appropriate for a cache else the
caching decorator should be bypassed by the dialect.
References: #4955
[sql] [bug] [py3k] Changed the repr() of the quoted_name construct to use
regular string repr() under Python 3, rather than running it through
"backslashreplace" escaping, which can be misleading.
References: #4931
[sql] [usecase] Added new accessors to expressions of type JSON to allow for
specific datatype access and comparison, covering strings, integers,
numeric, boolean elements. This revises the documented approach of
CASTing to string when comparing values, instead adding specific
functionality into the PostgreSQL, SQlite, MySQL dialects to reliably
deliver these basic types in all cases.
References: #4276
[sql] [usecase] The text() construct now supports "unique" bound parameters, which
will dynamically uniquify themselves on compilation thus allowing multiple
text() constructs with the same bound parameter names to be combined
together.
References: #4933
[schema] [bug] Fixed bug where a table that would have a column label overlap with a plain
column name, such as "foo.id AS foo_id" vs. "foo.foo_id", would prematurely
generate the ._label attribute for a column before this overlap could
be detected due to the use of the index=True or unique=True flag on
the column in conjunction with the default naming convention of
"column_0_label". This would then lead to failures when ._label
were used later to generate a bound parameter name, in particular those
used by the ORM when generating the WHERE clause for an UPDATE statement.
The issue has been fixed by using an alternate ._label accessor for DDL
generation that does not affect the state of the Column. The
accessor also bypasses the key-deduplication step as it is not necessary
for DDL, the naming is now consistently "<tablename>_<columnname>"
without any subsequent numeric symbols when used in DDL.
References: #4911
[schema] [usecase] Added DDL support for "computed columns"; these are DDL column specifications for columns that have a server-computed value, either upon SELECT (known as "virtual") or at the point of which they are INSERTed or UPDATEd (known as "stored"). Support is established for Postgresql, MySQL, Oracle SQL Server and Firebird. Thanks to Federico Caselli for lots of work on this one.
References: #4894
[mysql] [bug] Added "Connection was killed" message interpreted from the base pymysql.Error class in order to detect closed connection, based on reports that this message is arriving via a pymysql.InternalError() object which indicates pymysql is not handling it correctly.
References: #4945
[mssql] [bug] Fixed issue in MSSQL dialect where an expression-based OFFSET value in a SELECT would be rejected, even though the dialect can render this expression inside of a ROW NUMBER-oriented LIMIT/OFFSET construct.
References: #4973
[mssql] [bug] Fixed an issue in the Engine.table_names() method where it would
feed the dialect's default schema name back into the dialect level table
function, which in the case of SQL Server would interpret it as a
dot-tokenized schema name as viewed by the mssql dialect, which would
cause the method to fail in the case where the database username actually
had a dot inside of it. In 1.3, this method is still used by the
MetaData.reflect() function so is a prominent codepath. In 1.4,
which is the current master development branch, this issue doesn't exist,
both because MetaData.reflect() isn't using this method nor does the
method pass the default schema name explicitly. The fix nonetheless
guards against the default server name value returned by the dialect from
being interpreted as dot-tokenized name under any circumstances by
wrapping it in quoted_name().
References: #4923
[oracle] [bug] [firebird] Modified the approach of "name normalization" for the Oracle and Firebird
dialects, which converts from the UPPERCASE-as-case-insensitive convention
of these dialects into lowercase-as-case-insensitive for SQLAlchemy, to not
automatically apply the quoted_name construct to a name that
matches itself under upper or lower case conversion, as is the case for
many non-european characters. All names used within metadata structures
are converted to quoted_name objects in any case; the change
here would only affect the output of some inspection functions.
References: #4931
[oracle] [usecase] Added dialect-level flag encoding_errors to the cx_Oracle dialect,
which can be specified as part of create_engine(). This is passed
to SQLAlchemy's unicode decoding converter under Python 2, and to
cx_Oracle's cursor.var() object as the encodingErrors parameter
under Python 3, for the very unusual case that broken encodings are present
in the target database which cannot be fetched unless error handling is
relaxed. The value is ultimately one of the Python "encoding errors"
parameters passed to decode().
References: #4799
[oracle] [bug] The sqltypes.NCHAR datatype will now bind to the
cx_Oracle.FIXED_NCHAR DBAPI data bindings when used in a bound
parameter, which supplies proper comparison behavior against a
variable-length string. Previously, the sqltypes.NCHAR datatype
would bind to cx_oracle.NCHAR which is not fixed length; the
sqltypes.CHAR datatype already binds to cx_Oracle.FIXED_CHAR
so it is now consistent that sqltypes.NCHAR binds to
cx_Oracle.FIXED_NCHAR.
References: #4913
[firebird] [bug] Added additional "disconnect" message "Error writing data to the connection" to Firebird disconnection detection. Pull request courtesy lukens.
References: #4903
[bug] [tests] Fixed test failures which would occur with newer SQLite as of version 3.30 or greater, due to their addition of nulls ordering syntax as well as new restrictions on aggregate functions. Pull request courtesy Nils Philippsen.
References: #4920
[bug] [installation] [windows] Added a workaround for a setuptools-related failure that has been observed as occurring on Windows installations, where setuptools is not correctly reporting a build error when the MSVC build dependencies are not installed and therefore not allowing graceful degradation into non C extensions builds.
References: #4967
[bug] [mssql] Fixed bug in SQL Server dialect with new "max_identifier_length" feature where the mssql dialect already featured this flag, and the imp
Released: October 9, 2019
[bug] [mssql] Fixed bug in SQL Server dialect with new "max_identifier_length" feature where the mssql dialect already featured this flag, and the implementation did not accommodate for the new initialization hook correctly.
References: #4857
[bug] [oracle] Fixed regression in Oracle dialect that was inadvertently using max identifier length of 128 characters on Oracle server 12.2 and greater even though the stated contract for the remainder of the 1.3 series is that this value stays at 30 until version SQLAlchemy 1.4. Also repaired issues with the retrieval of the "compatibility" version, and removed the warning emitted when the "v$parameter" view was not accessible as this was causing user confusion.
[bug] [orm] Passing a plain string expression to Session.query() is deprecated, as all string coercions were removed in #4481 and this one should have…
Released: October 4, 2019
[engine] [usecase] Added new create_engine() parameter
create_engine.max_identifier_length. This overrides the
dialect-coded "max identifier length" in order to accommodate for databases
that have recently changed this length and the SQLAlchemy dialect has
not yet been adjusted to detect for that version. This parameter interacts
with the existing create_engine.label_length parameter in that
it establishes the maximum (and default) value for anonymously generated
labels. Additionally, post-connection detection of max identifier lengths
has been added to the dialect system. This feature is first being used
by the Oracle dialect.
References: #4857
[oracle] [usecase] The Oracle dialect now emits a warning if Oracle version 12.2 or greater is
used, and the create_engine.max_identifier_length parameter is
not set. The version in this specific case defaults to that of the
"compatibility" version set in the Oracle server configuration, not the
actual server version. In version 1.4, the default max_identifier_length
for 12.2 or greater will move to 128 characters. In order to maintain
forwards compatibility, applications should set
create_engine.max_identifier_length to 30 in order to maintain
the same length behavior, or to 128 in order to test the upcoming behavior.
This length determines among other things how generated constraint names
are truncated for statements like CREATE CONSTRAINT and DROP CONSTRAINT, which means a the new length may produce a name-mismatch
against a name that was generated with the old length, impacting database
migrations.
References: #4857
[sqlite] [usecase] Added support for sqlite "URI" connections, which allow for sqlite-specific flags to be passed in the query string such as "read only" for Python sqlite3 drivers that support this.
References: #4863
[bug] [tests] Fixed unit test regression released in 1.3.8 that would cause failure for Oracle, SQL Server and other non-native ENUM platforms due to new enumeration tests added as part of #4285 enum sortability in the unit of work; the enumerations created constraints that were duplicated on name.
References: #4285
[bug] [oracle] Restored adding cx_Oracle.DATETIME to the setinputsizes() call when a
SQLAlchemy Date, DateTime or Time datatype is
used, as some complex queries require this to be present. This was removed
in the 1.2 series for arbitrary reasons.
References: #4886
[bug] [mssql] Added identifier quoting to the schema name applied to the "use" statement
which is invoked when a SQL Server multipart schema name is used within a
Table that is being reflected, as well as for Inspector
methods such as Inspector.get_table_names(); this accommodates for
special characters or spaces in the database name. Additionally, the "use"
statement is not emitted if the current database matches the target owner
database name being passed.
References: #4883
[bug] [orm] Fixed regression in selectinload loader strategy caused by #4775 (released in version 1.3.6) where a many-to-one attribute of None would no longer be populated by the loader. While this was usually not noticeable due to the lazyloader populating None upon get, it would lead to a detached instance error if the object were detached.
References: #4872
[bug] [orm] Passing a plain string expression to Session.query() is deprecated,
as all string coercions were removed in #4481 and this one should
have been included. The literal_column() function may be used to
produce a textual column expression.
References: #4873
[sql] [usecase] Added an explicit error message for the case when objects passed to
Table are not SchemaItem objects, rather than resolving
to an attribute error.
References: #4847
[bug] [orm] A warning is emitted for a condition in which the Session may
implicitly swap an object out of the identity map for another one with the
same primary key, detaching the old one, which can be an observed result of
load operations which occur within the SessionEvents.after_flush()
hook. The warning is intended to notify the user that some special
condition has caused this to happen and that the previous object may not be
in the expected state.
References: #4890
[bug] [sql] Characters that interfere with "pyformat" or "named" formats in bound
parameters, namely %, (, ) and the space character, as well as a few
other typically undesirable characters, are stripped early for a
bindparam() that is using an anonymized name, which is typically
generated automatically from a named column which itself includes these
characters in its name and does not use a .key, so that they do not
interfere either with the SQLAlchemy compiler's use of string formatting or
with the driver-level parsing of the parameter, both of which could be
demonstrated before the fix. The change only applies to anonymized
parameter names that are generated and consumed internally, not end-user
defined names, so the change should have no impact on any existing code.
Applies in particular to the psycopg2 driver which does not otherwise quote
special parameter names, but also strips leading underscores to suit Oracle
(but not yet leading numbers, as some anon parameters are currently
entirely numeric/underscore based); Oracle in any case continues to quote
parameter names that include special characters.
References: #4837
[bug] [orm] Fixed bug where Load objects were not pickleable due to mapper/relationship state in the internal context dictionary. These objects are no
Released: August 27, 2019
[bug] [orm] Fixed bug where Load objects were not pickleable due to
mapper/relationship state in the internal context dictionary. These
objects are now converted to picklable using similar techniques as that of
other elements within the loader option system that have long been
serializable.
References: #4823
[bug] [postgresql] Revised the approach for the just added support for the psycopg2 "execute_values()" feature added in 1.3.7 for #4623. The approach relied upon a regular expression that would fail to match for a more complex INSERT statement such as one which had subqueries involved. The new approach matches exactly the string that was rendered as the VALUES clause.
References: #4623
[orm] [usecase] Added support for the use of an Enum datatype using Python
pep-435 enumeration objects as values for use as a primary key column
mapped by the ORM. As these values are not inherently sortable, as
required by the ORM for primary keys, a new
TypeEngine.sort_key_function attribute is added to the typing
system which allows any SQL type to implement a sorting for Python objects
of its type which is consulted by the unit of work. The Enum
type then defines this using the database value of a given enumeration.
The sorting scheme can be also be redefined by passing a callable to the
Enum.sort_key_function parameter. Pull request courtesy
Nicolas Caniart.
References: #4285
[bug] [engine] Fixed an issue whereby if the dialect "initialize" process which occurs on
first connect would encounter an unexpected exception, the initialize
process would fail to complete and then no longer attempt on subsequent
connection attempts, leaving the dialect in an un-initialized, or partially
initialized state, within the scope of parameters that need to be
established based on inspection of a live connection. The "invoke once"
logic in the event system has been reworked to accommodate for this
occurrence using new, private API features that establish an "exec once"
hook that will continue to allow the initializer to fire off on subsequent
connections, until it completes without raising an exception. This does not
impact the behavior of the existing once=True flag within the event
system.
References: #4807
[bug] [reflection] [sqlite] Fixed bug where a FOREIGN KEY that was set up to refer to the parent table by table name only without the column names would not correctly be reflected as far as setting up the "referred columns", since SQLite's PRAGMA does not report on these columns if they weren't given explicitly. For some reason this was harcoded to assume the name of the local column, which might work for some cases but is not correct. The new approach reflects the primary key of the referred table and uses the constraint columns list as the referred columns list, if the remote column(s) aren't present in the reflected pragma directly.
References: #4810
[bug] [postgresql] Fixed bug where Postgresql operators such as
postgresql.ARRAY.Comparator.contains() and
postgresql.ARRAY.Comparator.contained_by() would fail to function
correctly for non-integer values when used against a
postgresql.array object, due to an erroneous assert statement.
References: #4822
[engine] [feature] Added new parameter create_engine.hide_parameters which when
set to True will cause SQL parameters to no longer be logged, nor rendered
in the string representation of a StatementError object.
References: #4815
[postgresql] [usecase] Added support for reflection of CHECK constraints that include the special
PostgreSQL qualifier "NOT VALID", which can be present for CHECK
constraints that were added to an exsiting table with the directive that
they not be applied to existing data in the table. The PostgreSQL
dictionary for CHECK constraints as returned by
Inspector.get_check_constraints() may include an additional entry
dialect_options which within will contain an entry "not_valid": True if this symbol is detected. Pull request courtesy Bill Finn.
References: #4824
[bug] [sql] Fixed issue where Index object which contained a mixture of functional expressions which were not resolvable to a particular column, in co
Released: August 14, 2019
[bug] [sql] Fixed issue where Index object which contained a mixture of
functional expressions which were not resolvable to a particular column,
in combination with string-based column names, would fail to initialize
its internal state correctly leading to failures during DDL compilation.
References: #4778
[bug] [sqlite] The dialects that support json are supposed to take arguments
json_serializer and json_deserializer at the create_engine() level,
however the SQLite dialect calls them _json_serilizer and
_json_deserilalizer. The names have been corrected, the old names are
accepted with a change warning, and these parameters are now documented as
create_engine.json_serializer and
create_engine.json_deserializer.
References: #4798
[bug] [mysql] The MySQL dialects will emit "SET NAMES" at the start of a connection when charset is given to the MySQL driver, to appease an apparent behavior observed in MySQL 8.0 that raises a collation error when a UNION includes string columns unioned against columns of the form CAST(NULL AS CHAR(..)), which is what SQLAlchemy's polymorphic_union function does. The issue seems to have affected PyMySQL for at least a year, however has recently appeared as of mysqlclient 1.4.4 based on changes in how this DBAPI creates a connection. As the presence of this directive impacts three separate MySQL charset settings which each have intricate effects based on their presense, SQLAlchemy will now emit the directive on new connections to ensure correct behavior.
References: #4804
[postgresql] [usecase] Added new dialect flag for the psycopg2 dialect, executemany_mode which
supersedes the previous experimental use_batch_mode flag.
executemany_mode supports both the "execute batch" and "execute values"
functions provided by psycopg2, the latter which is used for compiled
insert() constructs. Pull request courtesy Yuval Dinari.
References: #4623
[bug] [sql] Fixed bug where TypeEngine.column_expression() method would not be
applied to subsequent SELECT statements inside of a UNION or other
CompoundSelect, even though the SELECT statements are rendered at
the topmost level of the statement. New logic now differentiates between
rendering the column expression, which is needed for all SELECTs in the
list, vs. gathering the returned data type for the result row, which is
needed only for the first SELECT.
References: #4787
[bug] [sqlite] Fixed bug where usage of "PRAGMA table_info" in SQLite dialect meant that reflection features to detect for table existence, list of table columns, and list of foreign keys, would default to any table in any attached database, when no schema name was given and the table did not exist in the base schema. The fix explicitly runs PRAGMA for the 'main' schema and then the 'temp' schema if the 'main' returned no rows, to maintain the behavior of tables + temp tables in the "no schema" namespace, attached tables only in the "schema" namespace.
References: #4793
[bug] [sql] Fixed issue where internal cloning of SELECT constructs could lead to a key error if the copy of the SELECT changed its state such that its list of columns changed. This was observed to be occurring in some ORM scenarios which may be unique to 1.3 and above, so is partially a regression fix.
References: #4780
[bug] [orm] Fixed regression caused by new selectinload for many-to-one logic where a primaryjoin condition not based on real foreign keys would cause KeyError if a related object did not exist for a given key value on the parent object.
References: #4777
[mysql] [usecase] Added reserved words ARRAY and MEMBER to the MySQL reserved words list, as MySQL 8.0 has now made these reserved.
References: #4783
[bug] [events] Fixed issue in event system where using the once=True flag with
dynamically generated listener functions would cause event registration of
future events to fail if those listener functions were garbage collected
after they were used, due to an assumption that a listened function is
strongly referenced. The "once" wrapped is now modified to strongly
reference the inner function persistently, and documentation is updated
that using "once" does not imply automatic de-registration of listener
functions.
References: #4794
[bug] [mysql] Added another fix for an upstream MySQL 8 issue where a case sensitive table name is reported incorrectly in foreign key constraint reflection, this is an extension of the fix first added for #4344 which affects a case sensitive column name. The new issue occurs through MySQL 8.0.17, so the general logic of the 88718 fix remains in place.
References: #4751
[mssql] [usecase] Added new mssql.try_cast() construct for SQL Server which emits
"TRY_CAST" syntax. Pull request courtesy Leonel Atencio.
References: #4782
[bug] [orm] Fixed bug where using Query.first() or a slice expression in
conjunction with a query that has an expression based "offset" applied
would raise TypeError, due to an "or" conditional against "offset" that did
not expect it to be a SQL expression as opposed to an integer or None.
References: #4803
The current fix is that it warns, instead of raises, as this would otherwise be backwards incompatible, however in a future release it will be a raise…
Released: July 21, 2019
[bug] [engine] Fixed bug where using reflection function such as MetaData.reflect()
with an Engine object that had execution options applied to it
would fail, as the resulting OptionEngine proxy object failed to
include a .engine attribute used within the reflection routines.
References: #4754
[bug] [mysql] Fixed bug where the special logic to render "NULL" for the
TIMESTAMP datatype when nullable=True would not work if the
column's datatype were a TypeDecorator or a Variant.
The logic now ensures that it unwraps down to the original
TIMESTAMP so that this special case NULL keyword is correctly
rendered when requested.
References: #4743
[orm] [performance] The optimization applied to selectin loading in #4340 where a JOIN is not needed to eagerly load related items is now applied to many-to-one relationships as well, so that only the related table is queried for a simple join condition. In this case, the related items are queried based on the value of a foreign key column on the parent; if these columns are deferred or otherwise not loaded on any of the parent objects in the collection, the loader falls back to the JOIN method.
References: #4775
[bug] [orm] Fixed regression caused by #4365 where a join from an entity to itself without using aliases no longer raises an informative error message, instead failing on an assertion. The informative error condition has been restored.
References: #4773
[feature] [orm] Added new loader option method Load.options() which allows loader
options to be constructed hierarchically, so that many sub-options can be
applied to a particular path without needing to call defaultload()
many times. Thanks to Alessio Bogon for the idea.
References: #4736
[postgresql] [usecase] Added support for reflection of indexes on PostgreSQL partitioned tables, which was added to PostgreSQL as of version 11.
References: #4771
[bug] [mysql] Enhanced MySQL/MariaDB version string parsing to accommodate for exotic MariaDB version strings where the "MariaDB" word is embedded among other alphanumeric characters such as "MariaDBV1". This detection is critical in order to correctly accommodate for API features that have split between MySQL and MariaDB such as the "transaction_isolation" system variable.
References: #4624
[bug] [mssql] Ensured that the queries used to reflect indexes and view definitions will
explicitly CAST string parameters into NVARCHAR, as many SQL Server drivers
frequently treat string values, particularly those with non-ascii
characters or larger string values, as TEXT which often don't compare
correctly against VARCHAR characters in SQL Server's information schema
tables for some reason. These CAST operations already take place for
reflection queries against SQL Server information_schema. tables but
were missing from three additional queries that are against sys.
tables.
References: #4745
[bug] [orm] Fixed an issue where the orm._ORMJoin.join() method, which is a
not-internally-used ORM-level method that exposes what is normally an
internal process of Query.join(), did not propagate the full and
outerjoin keyword arguments correctly. Pull request courtesy Denis
Kataev.
References: #4713
[bug] [sql] Adjusted the initialization for Enum to minimize how often it
invokes the .__members__ attribute of a given PEP-435 enumeration
object, to suit the case where this attribute is expensive to invoke, as is
the case for some popular third party enumeration libraries.
References: #4758
[bug] [orm] Fixed bug where a many-to-one relationship that specified uselist=True
would fail to update correctly during a primary key change where a related
column needs to change.
References: #4772
[bug] [orm] Fixed bug where the detection for many-to-one or one-to-one use with a
"dynamic" relationship, which is an invalid configuration, would fail to
raise if the relationship were configured with uselist=True. The
current fix is that it warns, instead of raises, as this would otherwise be
backwards incompatible, however in a future release it will be a raise.
References: #4772
[bug] [orm] Fixed bug where a synonym created against a mapped attribute that does not exist yet, as is the case when it refers to backref before mappers are configured, would raise recursion errors when trying to test for attributes on it which ultimately don't exist (as occurs when the classes are run through Sphinx autodoc), as the unconfigured state of the synonym would put it into an attribute not found loop.
References: #4767
[postgresql] [usecase] Added support for multidimensional Postgresql array literals via nesting
the postgresql.array object within another one. The
multidimensional array type is detected automatically.
References: #4756
[bug] [postgresql] [sql] Fixed issue where the array_agg construct in combination with
FunctionElement.filter() would not produce the correct operator
precedence in combination with the array index operator.
References: #4760
[bug] [sql] Fixed an unlikely issue where the "corresponding column" routine for unions
and other CompoundSelect objects could return the wrong column in
some overlapping column situtations, thus potentially impacting some ORM
operations when set operations are in use, if the underlying
select() constructs were used previously in other similar kinds of
routines, due to a cached value not being cleared.
References: #4747
[sqlite] [usecase] Added support for composite (tuple) IN operators with SQLite, by rendering
the VALUES keyword for this backend. As other backends such as DB2 are
known to use the same syntax, the syntax is enabled in the base compiler
using a dialect-level flag tuple_in_values. The change also includes
support for "empty IN tuple" expressions for SQLite when using "in_()"
between a tuple value and an empty set.
References: #4766
Originally, Python was emitting deprecation warnings for this function in Python 3.8 alphas. While this change was reverted, it was observed that Pyth…
Released: June 17, 2019
[bug] [mysql] Fixed bug where MySQL ON DUPLICATE KEY UPDATE would not accommodate setting a column to the value NULL. Pull request courtesy Lukáš Banič.
References: #4715
[bug] [orm] Fixed a series of related bugs regarding joined table inheritance more than two levels deep, in conjunction with modification to primary key values, where those primary key columns are also linked together in a foreign key relationship as is typical for joined table inheritance. The intermediary table in a three-level inheritance hierarchy will now get its UPDATE if only the primary key value has changed and passive_updates=False (e.g. foreign key constraints not being enforced), whereas before it would be skipped; similarly, with passive_updates=True (e.g. ON UPDATE CASCADE in effect), the third-level table will not receive an UPDATE statement as was the case earlier which would fail since CASCADE already modified it. In a related issue, a relationship linked to a three-level inheritance hierarchy on the primary key of an intermediary table of a joined-inheritance hierarchy will also correctly have its foreign key column updated when the parent object's primary key is modified, even if that parent object is a subclass of the linked parent class, whereas before these classes would not be counted.
References: #4723
[bug] [orm] Fixed bug where the Mapper.all_orm_descriptors accessor would
return an entry for the Mapper itself under the declarative
__mapper___ key, when this is not a descriptor. The .is_attribute
flag that's present on all InspectionAttr objects is now
consulted, which has also been modified to be True for an association
proxy, as it was erroneously set to False for this object.
References: #4729
[bug] [orm] Fixed regression in Query.join() where the aliased=True flag
would not properly apply clause adaptation to filter criteria, if a
previous join were made to the same entity. This is because the adapters
were placed in the wrong order. The order has been reversed so that the
adapter for the most recent aliased=True call takes precedence as was
the case in 1.2 and earlier. This broke the "elementtree" examples among
other things.
References: #4704
[bug] [orm] [py3k] Replaced the Python compatbility routines for getfullargspec() with a
fully vendored version from Python 3.3. Originally, Python was emitting
deprecation warnings for this function in Python 3.8 alphas. While this
change was reverted, it was observed that Python 3 implementations for
getfullargspec() are an order of magnitude slower as of the 3.4 series
where it was rewritten against Signature. While Python plans to
improve upon this situation, SQLAlchemy projects for now are using a simple
replacement to avoid any future issues.
References: #4674
[bug] [orm] Reworked the attribute mechanics used by AliasedClass to no
longer rely upon calling __getattribute__ on the MRO of the wrapped
class, and to instead resolve the attribute normally on the wrapped class
using getattr(), and then unwrap/adapt that. This allows a greater range
of attribute styles on the mapped class including special __getattr__()
schemes; but it also makes the code simpler and more resilient in general.
References: #4694
[postgresql] [usecase] Added support for column sorting flags when reflecting indexes for PostgreSQL, including ASC, DESC, NULLSFIRST, NULLSLAST. Also adds this facility to the reflection system in general which can be applied to other dialects in future releases. Pull request courtesy Eli Collins.
References: #4717
[bug] [postgresql] Fixed bug where PostgreSQL dialect could not correctly reflect an ENUM
datatype that has no members, returning a list with None for the
get_enums() call and raising a TypeError when reflecting a column which
has such a datatype. The inspection now returns an empty list.
References: #4701
[bug] [sql] Fixed a series of quoting issues which all stemmed from the concept of the
literal_column() construct, which when being "proxied" through a
subquery to be referred towards by a label that matches its text, the label
would not have quoting rules applied to it, even if the string in the
Label were set up as a quoted_name construct. Not
applying quoting to the text of the Label is a bug because this
text is strictly a SQL identifier name and not a SQL expression, and the
string should not have quotes embedded into it already unlike the
literal_column() which it may be applied towards. The existing
behavior of a non-labeled literal_column() being propagated as is on
the outside of a subquery is maintained in order to help with manual
quoting schemes, although it's not clear if valid SQL can be generated for
such a construct in any case.
References: #4730
Lookups for functions declared with GenericFunction now use a case insensitive scheme, however a deprecation case is supported which allows two or mor…
Released: May 27, 2019
[feature] [mssql] Added support for SQL Server filtered indexes, via the mssql_where
parameter which works similarly to that of the postgresql_where index
function in the PostgreSQL dialect.
References: #4657
[bug] [misc] Removed errant "sqla_nose.py" symbol from MANIFEST.in which created an undesirable warning message.
References: #4625
[bug] [sql] Fixed that the GenericFunction class was inadvertently
registering itself as one of the named functions. Pull request courtesy
Adrien Berchet.
References: #4653
[bug] [engine] [postgresql] Moved the "rollback" which occurs during dialect initialization so that it occurs after additional dialect-specific initialize steps, in particular those of the psycopg2 dialect which would inadvertently leave transactional state on the first new connection, which could interfere with some psycopg2-specific APIs which require that no transaction is started. Pull request courtesy Matthew Wilkes.
References: #4663
[bug] [orm] Fixed issue where the AttributeEvents.active_history flag
would not be set for an event listener that propgated to a subclass via the
AttributeEvents.propagate flag. This bug has been present
for the full span of the AttributeEvents system.
References: #4695
[bug] [orm] Fixed regression where new association proxy system was still not proxying
hybrid attributes when they made use of the @hybrid_property.expression
decorator to return an alternate SQL expression, or when the hybrid
returned an arbitrary PropComparator, at the expression level.
This involved further generalization of the heuristics used to detect the
type of object being proxied at the level of QueryableAttribute,
to better detect if the descriptor ultimately serves mapped classes or
column expressions.
References: #4690
[bug] [orm] Applied the mapper "configure mutex" against the declarative class mapping process, to guard against the race which can occur if mappers are used while dynamic module import schemes are still in the process of configuring mappers for related classes. This does not guard against all possible race conditions, such as if the concurrent import has not yet encountered the dependent classes as of yet, however it guards against as much as possible within the SQLAlchemy declarative process.
References: #4686
[bug] [mssql] Added error code 20047 to "is_disconnect" for pymssql. Pull request courtesy Jon Schuff.
References: #4680
[bug] [orm] [postgresql] Fixed an issue where the "number of rows matched" warning would emit even if
the dialect reported "supports_sane_multi_rowcount=False", as is the case
for psycogp2 with use_batch_mode=True and others.
References: #4661
[bug] [sql] Fixed issue where double negation of a boolean column wouldn't reset the "NOT" operator.
References: #4618
[bug] [mysql] Added support for DROP CHECK constraint which is required by MySQL 8.0.16 to drop a CHECK constraint; MariaDB supports plain DROP CONSTRAINT. The logic distinguishes between the two syntaxes by checking the server version string for MariaDB presence. Alembic migrations has already worked around this issue by implementing its own DROP for MySQL / MariaDB CHECK constraints, however this change implements it straight in Core so that its available for general use. Pull request courtesy Hannes Hansen.
References: #4650
[bug] [orm] A warning is now emitted for the case where a transient object is being
merged into the session with Session.merge() when that object is
already transient in the Session. This warns for the case where
the object would normally be double-inserted.
References: #4647
[bug] [orm] Fixed regression in new relationship m2o comparison logic first introduced
at change_4359 when comparing to an attribute that is persisted as
NULL and is in an un-fetched state in the mapped instance. Since the
attribute has no explicit default, it needs to default to NULL when
accessed in a persistent setting.
References: #4676
[bug] [sql] The GenericFunction namespace is being migrated so that function
names are looked up in a case-insensitive manner, as SQL functions do not
collide on case sensitive differences nor is this something which would
occur with user-defined functions or stored procedures. Lookups for
functions declared with GenericFunction now use a case
insensitive scheme, however a deprecation case is supported which allows
two or more GenericFunction objects with the same name of
different cases to exist, which will cause case sensitive lookups to occur
for that particular name, while emitting a warning at function registration
time. Thanks to Adrien Berchet for a lot of work on this complicated
feature.
References: #4569
[bug] [pool] Fixed behavioral regression as a result of deprecating the "use_threadlocal" flag for Pool, where the SingletonThreadPool no longer makes…
Released: April 15, 2019
[bug] [postgresql] Fixed regression from release 1.3.2 caused by #4562 where a URL that contained only a query string and no hostname, such as for the purposes of specifying a service file with connection information, would no longer be propagated to psycopg2 properly. The change in #4562 has been adjusted to further suit psycopg2's exact requirements, which is that if there are any connection parameters whatsoever, the "dsn" parameter is no longer required, so in this case the query string parameters are passed alone.
References: #4601
[bug] [pool] Fixed behavioral regression as a result of deprecating the "use_threadlocal"
flag for Pool, where the SingletonThreadPool no longer
makes use of this option which causes the "rollback on return" logic to take
place when the same Engine is used multiple times in the context
of a transaction to connect or implicitly execute, thereby cancelling the
transaction. While this is not the recommended way to work with engines
and connections, it is nonetheless a confusing behavioral change as when
using SingletonThreadPool, the transaction should stay open
regardless of what else is done with the same engine in the same thread.
The use_threadlocal flag remains deprecated however the
SingletonThreadPool now implements its own version of the same
logic.
References: #4585
[bug] [orm] Fixed 1.3 regression in new "ambiguous FROMs" query logic introduced in
change_4365 where a Query that explicitly places an entity
in the FROM clause with Query.select_from() and also joins to it
using Query.join() would later cause an "ambiguous FROM" error if
that entity were used in additional joins, as the entity appears twice in
the "from" list of the Query. The fix resolves this ambiguity by
folding the standalone entity into the join that it's already a part of in
the same way that ultimately happens when the SELECT statement is rendered.
References: #4584
[bug] [ext] Fixed bug where using copy.copy() or copy.deepcopy() on
MutableList would cause the items within the list to be
duplicated, due to an inconsistency in how Python pickle and copy both make
use of __getstate__() and __setstate__() regarding lists. In order
to resolve, a __reduce_ex__ method had to be added to
MutableList. In order to maintain backwards compatibility with
existing pickles based on __getstate__(), the __setstate__() method
remains as well; the test suite asserts that pickles made against the old
version of the class can still be deserialized by the pickle module.
References: #4603
[bug] [orm] Adjusted the Query.filter_by() method to not call and()
internally against multiple criteria, instead passing it off to
Query.filter() as a series of criteria, instead of a single criteria.
This allows Query.filter_by() to defer to Query.filter()'s
treatment of variable numbers of clauses, including the case where the list
is empty. In this case, the Query object will not have a
.whereclause, which allows subsequent "no whereclause" methods like
Query.select_from() to behave consistently.
References: #4606
[bug] [mssql] Fixed issue in SQL Server dialect where if a bound parameter were present in an ORDER BY expression that would ultimately not be rendered in the SQL Server version of the statement, the parameters would still be part of the execution parameters, leading to DBAPI-level errors. Pull request courtesy Matt Lewellyn.
References: #4587
[bug] [documentation] [sql] Thanks to change_3981, we no longer need to rely on recipes that subclass dialect-specific types directly, TypeDecorator c
Released: April 2, 2019
[bug] [documentation] [sql] Thanks to change_3981, we no longer need to rely on recipes that
subclass dialect-specific types directly, TypeDecorator can now
handle all cases. Additionally, the above change made it slightly less
likely that a direct subclass of a base SQLAlchemy type would work as
expected, which could be misleading. Documentation has been updated to use
TypeDecorator for these examples including the PostgreSQL
"ArrayOfEnum" example datatype and direct support for the "subclass a type
directly" has been removed.
References: #4580
[bug] [postgresql] Modified the Select.with_for_update.of parameter so that if a
join or other composed selectable is passed, the individual Table
objects will be filtered from it, allowing one to pass a join() object to
the parameter, as occurs normally when using joined table inheritance with
the ORM. Pull request courtesy Raymond Lu.
References: #4550
[feature] [postgresql] Added support for parameter-less connection URLs for the psycopg2 dialect,
meaning, the URL can be passed to create_engine() as
"postgresql+psycopg2://" with no additional arguments to indicate an
empty DSN passed to libpq, which indicates to connect to "localhost" with
no username, password, or database given. Pull request courtesy Julian
Mehnle.
References: #4562
[bug] [ext] [orm] Restored instance-level support for plain Python descriptors, e.g.
@property objects, in conjunction with association proxies, in that if
the proxied object is not within ORM scope at all, it gets classified as
"ambiguous" but is proxed directly. For class level access, a basic class
level__get__() now returns the
AmbiguousAssociationProxyInstance directly, rather than raising
its exception, which is the closest approximation to the previous behavior
that returned the AssociationProxy itself that's possible. Also
improved the stringification of these objects to be more descriptive of
current state.
[bug] [orm] Fixed bug where use of with_polymorphic() or other aliased construct
would not properly adapt when the aliased target were used as the
Select.correlate_except() target of a subquery used inside of a
column_property(). This required a fix to the clause adaption
mechanics to properly handle a selectable that shows up in the "correlate
except" list, in a similar manner as which occurs for selectables that show
up in the "correlate" list. This is ultimately a fairly fundamental bug
that has lasted for a long time but it is hard to come across it.
References: #4537
[bug] [orm] Fixed regression where a new error message that was supposed to raise when
attempting to link a relationship option to an AliasedClass without using
PropComparator.of_type() would instead raise an AttributeError.
Note that in 1.3, it is no longer valid to create an option path from a
plain mapper relationship to an AliasedClass without using
PropComparator.of_type().
References: #4566
[bug] [mssql] A commit() is emitted after an isolation level change to SNAPSHOT, as both pyodbc and pymssql open an implicit transaction which blocks
Released: March 9, 2019
[bug] [mssql] A commit() is emitted after an isolation level change to SNAPSHOT, as both pyodbc and pymssql open an implicit transaction which blocks subsequent SQL from being emitted in the current transaction.
This change is also backported to: 1.2.19
References: #4536
[bug] [mssql] Fixed regression in SQL Server reflection due to #4393 where the
removal of open-ended **kw from the Float datatype caused
reflection of this type to fail due to a "scale" argument being passed.
References: #4525
[bug] [ext] [orm] Fixed regression where an association proxy linked to a synonym would no longer work, both at instance level and at class level.
References: #4522
[feature] [schema] Added new parameters Table.resolve_fks and MetaData.reflect.resolve_fks which when set to False will disable the automatic reflecti
Released: March 4, 2019
[feature] [schema] Added new parameters Table.resolve_fks and
MetaData.reflect.resolve_fks which when set to False will
disable the automatic reflection of related tables encountered in
ForeignKey objects, which can both reduce SQL overhead for omitted
tables as well as avoid tables that can't be reflected for database-specific
reasons. Two Table objects present in the same MetaData
collection can still refer to each other even if the reflection of the two
tables occurred separately.
References: #4517
[feature] [orm] The Query.get() method can now accept a dictionary of attribute keys
and values as a means of indicating the primary key value to load; is
particularly useful for composite primary keys. Pull request courtesy
Sanjana S.
References: #4316
[feature] [orm] A SQL expression can now be assigned to a primary key attribute for an ORM
flush in the same manner as ordinary attributes as described in
flush_embedded_sql_expressions where the expression will be evaulated
and then returned to the ORM using RETURNING, or in the case of pysqlite,
works using the cursor.lastrowid attribute.Requires either a database that
supports RETURNING (e.g. Postgresql, Oracle, SQL Server) or pysqlite.
References: #3133
[bug] [sql] The Alias class and related subclasses CTE,
Lateral and TableSample have been reworked so that it is
not possible for a user to construct the objects directly. These constructs
require that the standalone construction function or selectable-bound method
be used to instantiate new objects.
References: #4509
[engine] [feature] Revised the formatting for StatementError when stringified. Each
error detail is broken up over multiple newlines instead of spaced out on a
single line. Additionally, the SQL representation now stringifies the SQL
statement rather than using repr(), so that newlines are rendered as is.
Pull request courtesy Nate Clark.
References: #4500
Note that public CVEs have been posted for order_by() / group_by() which are resolved by this commit: CVE-2019-7164 CVE-2019-7548
Released: February 8, 2019
[bug] [ext] Implemented a more comprehensive assignment operation (e.g. "bulk replace") when using association proxy with sets or dictionaries. Fixes the problem of redundant proxy objects being created to replace the old ones, which leads to excessive events and SQL and in the case of unique constraints will cause the flush to fail.
References: #2642
[bug] [postgresql] Fixed issue where using an uppercase name for an index type (e.g. GIST, BTREE, etc. ) or an EXCLUDE constraint would treat it as an identifier to be quoted, rather than rendering it as is. The new behavior converts these types to lowercase and ensures they contain only valid SQL characters.
References: #4473
[bug] [orm] Improved the behavior of orm.with_polymorphic() in conjunction with
loader options, in particular wildcard operations as well as
orm.load_only(). The polymorphic object will be more accurately
targeted so that column-level options on the entity will correctly take
effect.The issue is a continuation of the same kinds of things fixed in
#4468.
References: #4469
[bug] [sql] Fully removed the behavior of strings passed directly as components of a
select() or Query object being coerced to text()
constructs automatically; the warning that has been emitted is now an
ArgumentError or in the case of order_by() / group_by() a CompileError.
This has emitted a warning since version 1.0 however its presence continues
to create concerns for the potential of mis-use of this behavior.
Note that public CVEs have been posted for order_by() / group_by() which are resolved by this commit: CVE-2019-7164 CVE-2019-7548
References: #4481
[bug] [sql] Quoting is applied to Function names, those which are usually but
not necessarily generated from the sql.func construct, at compile
time if they contain illegal characters, such as spaces or punctuation. The
names are as before treated as case insensitive however, meaning if the
names contain uppercase or mixed case characters, that alone does not
trigger quoting. The case insensitivity is currently maintained for
backwards compatibility.
References: #4467
[bug] [sql] Added "SQL phrase validation" to key DDL phrases that are accepted as plain
strings, including ForeignKeyConstraint.on_delete,
ForeignKeyConstraint.on_update,
ExcludeConstraint.using,
ForeignKeyConstraint.initially, for areas where a series of SQL
keywords only are expected.Any non-space characters that suggest the phrase
would need to be quoted will raise a CompileError. This change
is related to the series of changes committed as part of #4481.
References: #4481
[bug] [declarative] [orm] Added some helper exceptions that invoke when a mapping based on
AbstractConcreteBase, DeferredReflection, or
AutoMap is used before the mapping is ready to be used, which
contain descriptive information on the class, rather than falling through
into other failure modes that are less informative.
References: #4470
[change] [tests] The test system has removed support for Nose, which is unmaintained for several years and is producing warnings under Python 3. The test suite is currently standardized on Pytest. Pull request courtesy Parth Shandilya.
References: #4460
…of the Session.close_all() method, which is now deprecated as this is confusing as a classmethod. Pull request courtesy Augustin Trancart.
Released: January 25, 2019
[bug] [ext] Fixed a regression in 1.3.0b1 caused by #3423 where association
proxy objects that access an attribute that's only present on a polymorphic
subclass would raise an AttributeError even though the actual instance
being accessed was an instance of that subclass.
References: #4401
[bug] [orm] Fixed long-standing issue where duplicate collection members would cause a backref to delete the association between the member and its parent object when one of the duplicates were removed, as occurs as a side effect of swapping two objects in one statement.
References: #1103
[bug] [mssql] The literal_processor for the Unicode and
UnicodeText datatypes now render an N character in front of
the literal string expression as required by SQL Server for Unicode string
values rendered in SQL expressions.
References: #4442
[feature] [orm] Implemented a new feature whereby the AliasedClass construct can
now be used as the target of a relationship(). This allows the
concept of "non primary mappers" to no longer be necessary, as the
AliasedClass is much easier to configure and automatically inherits
all the relationships of the mapped class, as well as preserves the
ability for loader options to work normally.
References: #4423
[bug] [orm] Extended the fix first made as part of #3287, where a loader option
made against a subclass using a wildcard would extend itself to include
application of the wildcard to attributes on the super classes as well, to a
"bound" loader option as well, e.g. in an expression like
Load(SomeSubClass).load_only('foo'). Columns that are part of the
parent class of SomeSubClass will also be excluded in the same way as if
the unbound option load_only('foo') were used.
References: #4373
[bug] [orm] Improved error messages emitted by the ORM in the area of loader option traversal. This includes early detection of mis-matched loader strategies along with a clearer explanation why these strategies don't match.
References: #4433
[change] [orm] Added a new function close_all_sessions() which takes
over the task of the Session.close_all() method, which
is now deprecated as this is confusing as a classmethod.
Pull request courtesy Augustin Trancart.
References: #4412
[feature] [orm] Added new MapperEvents.before_mapper_configured() event. This
event complements the other "configure" stage mapper events with a per
mapper event that receives each Mapper right before its
configure step, and additionally may be used to prevent or delay the
configuration of specific Mapper objects using a new
return value orm.interfaces.EXT_SKIP. See the
documentation link for an example.
References: #4397
[bug] [orm] The "remove" event for collections is now called before the item is removed
in the case of the collection.remove() method, as is consistent with the
behavior for most other forms of collection item removal (such as
__delitem__, replacement under __setitem__). For pop() methods,
the remove event still fires after the operation.
[bug] [orm declarative] Added a __clause_element__() method to ColumnProperty which
can allow the usage of a not-fully-declared column or deferred attribute in
a declarative mapped class slightly more friendly when it's used in a
constraint or other column-oriented scenario within the class declaration,
though this still can't work in open-ended expressions; prefer to call the
ColumnProperty.expression attribute if receiving TypeError.
References: #4372
[bug] [engine] [orm] Added accessors for execution options to Core and ORM, via
Query.get_execution_options(),
Connection.get_execution_options(),
Engine.get_execution_options(), and
Executable.get_execution_options(). PR courtesy Daniel Lister.
References: #4464
[bug] [orm] Fixed issue in association proxy due to #3423 which caused the use
of custom PropComparator objects with hybrid attributes, such as
the one demonstrated in the dictlike-polymorphic example to not
function within an association proxy. The strictness that was added in
#3423 has been relaxed, and additional logic to accommodate for
an association proxy that links to a custom hybrid have been added.
References: #4446
[change] [general] A large change throughout the library has ensured that all objects,
parameters, and behaviors which have been noted as deprecated or legacy now
emit DeprecationWarning warnings when invoked.As the Python 3
interpreter now defaults to displaying deprecation warnings, as well as that
modern test suites based on tools like tox and pytest tend to display
deprecation warnings, this change should make it easier to note what API
features are obsolete. A major rationale for this change is so that long-
deprecated features that nonetheless still see continue to see real world
use can finally be removed in the near future; the biggest example of this
are the SessionExtension and MapperExtension classes as
well as a handful of other pre-event extension hooks, which have been
deprecated since version 0.7 but still remain in the library. Another is
that several major longstanding behaviors are to be deprecated as well,
including the threadlocal engine strategy, the convert_unicode flag, and non
primary mappers.
References: #4393
[change] [engine] The "threadlocal" engine strategy which has been a legacy feature of
SQLAlchemy since around version 0.2 is now deprecated, along with the
Pool.threadlocal parameter of Pool which has no
effect in most modern use cases.
References: #4393
[change] [sql] The create_engine.convert_unicode and
String.convert_unicode parameters have been deprecated. These
parameters were built back when most Python DBAPIs had little to no support
for Python Unicode objects, and SQLAlchemy needed to take on the very
complex task of marshalling data and SQL strings between Unicode and
bytestrings throughout the system in a performant way. Thanks to Python 3,
DBAPIs were compelled to adapt to Unicode-aware APIs and today all DBAPIs
supported by SQLAlchemy support Unicode natively, including on Python 2,
allowing this long-lived and very complicated feature to finally be (mostly)
removed. There are still of course a few Python 2 edge cases where
SQLAlchemy has to deal with Unicode however these are handled automatically;
in modern use, there should be no need for end-user interaction with these
flags.
References: #4393
[bug] [orm] Implemented the .get_history() method, which also implies availability
of AttributeState.history, for synonym() attributes.
Previously, trying to access attribute history via a synonym would raise an
AttributeError.
References: #3777
[engine] [feature] Added public accessor QueuePool.timeout() that returns the configured
timeout for a QueuePool object. Pull request courtesy Irina Delamare.
References: #3689
[feature] [sql] Amended the AnsiFunction class, the base of common SQL
functions like CURRENT_TIMESTAMP, to accept positional arguments
like a regular ad-hoc function. This to suit the case that many of
these functions on specific backends accept arguments such as
"fractional seconds" precision and such. If the function is created
with arguments, it renders the parenthesis and the arguments. If
no arguments are present, the compiler generates the non-parenthesized form.
References: #4386
One column per quarter.