SqlAlchemy 2.0 中文文档（五十九）（4）

SqlAlchemy 2.0 中文文档（五十九）（3）https://developer.aliyun.com/article/1563149

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 Session.merge() and AsyncSession.merge() via a new 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 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.
  See also
  How do I get at the raw DBAPI connection when using an Engine?
  References: #6832
  
• [engine] [usecase] [orm]
  Added new methods Session.scalars(), Connection.scalars(), AsyncSession.scalars() and AsyncSession.stream_scalars(), which provide a short cut to the use case of receiving a row-oriented Result object and converting it to a ScalarResult object via the Result.scalars() method, to return a list of values rather than a list of rows. The new methods are analogous to the long existing Session.scalar() and 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 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.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 Generating dynamic authentication tokens  should be used instead.
  References: #6958
  
• [engine] [bug]
  Fixed issue in 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 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 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 HasCTE.cte.nesting to the CTE constructor and 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 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 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 BindParameter objects around, leading to issues in correctly setting the parameters at execution time.
  References: #7055
  
• [sql] [bug]
  Fixed issue related to new 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 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.
  See also
  asyncmy
  References: #6993
  
• [asyncio] [usecase]
  The AsyncSession now supports overriding which 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 AsyncSession.execute() and 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 AsyncSession.connection() method.
  
• [asyncio] [bug]
  Deprecate usage of scoped_session with asyncio drivers. When using Asyncio the 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 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 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 sqlalchemy.engine.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] [performance] [bug]
  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
  

1.4.23

Released: August 18, 2021

general

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

• [orm] [usecase]
  Added new attribute Select.columns_clause_froms that will retrieve the FROM list implied by the columns clause of the Select statement. This differs from the old 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 Select.select_from() method. Additionally adds a new parameter Select.with_only_columns.maintain_column_froms that transfers this collection to Select.select_from() before replacing the columns collection.
  In addition, the Select.froms is renamed to 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 with_only_columns() function to support applying criteria to column elements that were replaced with either Select.with_only_columns() or 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 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 ORMExecuteState.user_defined_options accessor receives 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 selectinload() where use of the new 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.
  References: #6079, #6889
  
• [orm] [bug]
  Fixed an issue where the 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

• [sql] [bug]
  Fix issue in CTE where new HasCTE.add_cte() method added in version 1.4.21 / #6752 failed to function correctly for “compound select” structures such as union(), union_all(), 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

• [schema] [enum]
  Unify behaviour Enum in native and non-native implementations regarding the accepted values for an enum with aliased elements. When Enum.omit_aliases is False all values, alias included, are accepted as valid values. When Enum.omit_aliases is True only non aliased values are accepted as valid values.
  References: #6146
  

mypy

• [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.
  References: #6759, #6804
  

postgresql

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

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

misc

• [bug] [ext]
  Fixed issue where the horizontal sharding extension would not correctly accommodate for a plain textual SQL statement passed to Session.execute().
  References: #6816
  

1.4.22

Released: July 21, 2021

orm

• [orm] [bug]
  Fixed issue in new Table.table_valued() method where the resulting 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.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.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 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

• [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.dispose() is being called, which would cause stack trace warnings.
  References: #6740
  

sql

• [sql] [bug]
  Fixed issue where use of the 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 Insert.values() method with the Python None value; in particular, this would be noticed when using the JSON datatype as well as related PostgreSQL specific types such as 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 Insert.
  References: #6770
  

1.4.21

Released: July 14, 2021

orm

• [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 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 relationship.active_history flag is not set. As is documented in 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 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 Query.with_entities() where the new structure used would be inappropriately transferred to an enclosing Query when making use of set operations such as 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 with_polymorphic() against a mapping which uses a column_property() which in turn makes use of a scalar select that includes a 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

• [sql] [usecase]
  Added new method HasCTE.add_cte() to each of the select(), insert(), update() and delete() constructs. This method will add the given 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 tablesample() construct would fail to be executable when constructed given a  floating-point sampling value not embedded within a SQL function.
  References: #6735
  

postgresql

• [postgresql] [bug]
  Fixed issue in Insert.on_conflict_do_nothing() and 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 Insert.on_conflict_do_nothing() and 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

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

1.4.20

Released: June 28, 2021

orm

• [orm] [bug] [regression]
  Fixed regression in ORM regarding an internal reconstitution step for the 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 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 undefer() option to be used at the top level of such a 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

• [engine] [bug]
  Fixed an issue in the C extension for the Row class which could lead to a memory leak in the unlikely case of a 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 Row implementation to accommodate for this case.
  References: #5348
  
• [engine] [bug]
  Fixed old issue where a 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

• [sql] [usecase]
  Add a impl parameter to PickleType constructor, allowing any arbitrary type to be used in place of the default implementation of LargeBinary. Pull request courtesy jason3gb.
  References: #6646
  
• [sql] [bug] [orm]
  Fixed the class hierarchy for the Sequence and the more general DefaultGenerator base, as these are “executable” as statements they need to include Executable in their hierarchy, not just StatementRole as was applied arbitrarily to Sequence previously. The fix allows Sequence to work in all .execute() methods including with Session.execute() which was not working in the case that a SessionEvents.do_orm_execute() handler was also established.
  References: #6668
  

schema

• [schema] [bug]
  Fixed issue where passing None for the value of 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

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

misc

• [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 relationship.secondary element of a relationship() that automap will be generating would emit the “overlaps” warnings  introduced in 1.4 and discussed at relationship X will copy column Q to  column P, which conflicts with relationship(s): ‘Y’. While generating  this case from automap is still subject to the same caveats mentioned in  the ‘overlaps’ warning, since automap is primarily intended for more  ad-hoc use cases, the condition triggering the warning is disabled when a  many-to-many relationship with this specific pattern is generated.
  References: #6679
  

1.4.19

Released: June 22, 2021

orm

• [orm] [bug] [regression]
  Fixed further regressions in the same area as that of #6052 where loader options as well as invocations of methods like Query.join() would fail if the left side of the statement for which the option/join depends upon were replaced by using the Query.with_entities() method, or when using 2.0 style queries when using the 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.
  References: #6253, #6503
  
• [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 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 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 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

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

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

• [asyncio] [usecase]
  Implemented async_scoped_session to address some asyncio-related incompatibilities between scoped_session and AsyncSession, in which some methods (notably the async_scoped_session.remove() method) should be used with the await keyword.
  See also
  Using asyncio scoped session
  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

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

• [mysql] [usecase]
  Added new construct match,  which provides for the full range of MySQL’s MATCH operator including  multiple column support and modifiers. Pull request courtesy Anton  Kovalevich.
  See also
  match
  References: #6132
  

mssql

• [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.
  References: #6253, #6503
  
• [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

SqlAlchemy 2.0 中文文档（五十九）（5）https://developer.aliyun.com/article/1563151