1.4.27

Released: November 11, 2021

orm

• [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

• [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

• [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

• [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

• [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

• [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

• [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