1.4.24

Released: September 22, 2021

platform

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

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

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

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

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

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

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

sqlite

• [sqlite] [bug] Fixed bug where the error message for SQLite invalid isolation level on the pysqlite driver would fail to indicate that "AUTOCOMMIT" is one of the valid isolation levels.

mssql

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

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