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

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

1.4.0

Released: March 15, 2021

orm

• [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 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 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 relationship.query_class parameter stopped being functional for “dynamic” relationships. The AppenderQuery remains dependent on the legacy Query class; users are encouraged to migrate from the use of “dynamic” relationships to using with_parent() instead.
  References: #5981
  
• [orm] [bug] [regression]
  Fixed regression where Query.join() would produce no effect if the query itself as well as the join target were against a 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 Query if the statement produced had not ORM entities present within it.
  References: #6003
  
• [orm] [bug] [asyncio]
  The API for AsyncSession.delete() is now an awaitable; this method cascades along relationships which must be loaded in a similar manner as the 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

• [engine] [bug]
  Fixed bug where the “schema_translate_map” feature failed to be taken into account for the use case of direct execution of 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 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 Connection explicitly closes out the transaction before returning the pool to the connection.
  References: #6004
  

sql

• [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 FromClause.alias() and 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]
  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 Operators.op() and 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 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 distinct() is not oriented towards being directly SELECTed (use 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 ” 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

• [schema] [bug] [sqlite]
  Fixed issue where the CHECK constraint generated by Boolean or 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
  
• [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 Table will update its name as new primary key 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 Table.tometadata() method is the public API that provides copying for Table objects.
  References: #5953
  

mypy

• [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.
  See also
  Mypy / Pep-484 Support for ORM Mappings
  References: #4609
  

postgresql

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

• [mssql] [bug]
  Fix a reflection error for MSSQL 2005 introduced by the reflection of filtered indexes.
  References: #5919
  

misc

• [usecase] [ext]
  Add new parameter AutomapBase.prepare.reflection_options to allow passing of 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
  

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 Select.from_statement() in an ORM context.
  See also
  Using INSERT, UPDATE and ON CONFLICT (i.e. upsert) to return ORM Objects
  
• [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
  

engine

• [engine] [bug] [postgresql]
  Continued with the improvement made as part of #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).
  See also
  psycopg2 dialect no longer has limitations regarding bound parameter names
  References: #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
  
• [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
  
• [sql] [bug]
  Fixed 1.4 regression where the Function.in_() method was not covered by tests and failed to function properly in all cases.
  References: #5934
  
• [sql] [bug]
  Fixed regression where use of an arbitrary iterable with the 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
  

1.4.0b2

Released: February 3, 2021

general

• [general] [bug]
  Fixed a SQLite  source file that had non-ascii characters inside of its docstring  without a source encoding, introduced within the “INSERT…ON CONFLICT”  feature, which would cause failures under Python 2.
  

platform

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

• [orm] [usecase]
  Added ORMExecuteState.bind_mapper and ORMExecuteState.all_mappers accessors to 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 AsyncSession.scalar(), AsyncSession.get() as well as support for sessionmaker.begin() to work as an async context manager with AsyncSession. Also added AsyncSession.in_transaction() accessor.
  References: #5796, #5797, #5802
  
• [orm] [changed]
  Mapper “configuration”, which occurs within the 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 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 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 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 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 Session similar to that of the 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 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 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 with_loader_criteria() feature as well as the SessionEvents.do_orm_execute() event handler that is often used in conjunction [ticket:5760]:

• fixed issue where with_loader_criteria() function would fail if the given entity or base included non-mapped mixins in its descending class hierarchy [ticket:5766]
  
• The 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 Session.refresh(). These loads are necessarily based on primary key identity where additional WHERE criteria is never appropriate. [ticket:5762]
  
• Added new attribute ORMExecuteState.is_column_load to indicate that a 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 with_loader_criteria() function as above ignores these in any case now. [ticket:5761]
  
• Fixed issue where the 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]
  

• References: #5760, #5761, #5762, #5764, #5766
  
• [orm] [bug]
  Fixed 1.4 regression where the use of 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 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 Mapper.primary_key to establish certain columns as “primary”.
  References: #5867
  

orm declarative

• [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.
  See also
  Mapping pre-existing dataclasses using Declarative-style fields
  References: #5745
  

engine

• [engine] [feature]
  Dialect-specific constructs such as 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 AddConstraint, which will adapt its stringify dialect to one indicated by the element within it, such as the ExcludeConstraint object.
  
• [engine] [feature]
  Added new execution option Connection.execution_options.logging_token. This option will add an additional per-message token to log messages generated by the Connection as it executes statements. This token is not part of the logger name itself (that part can be affected using the existing 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 Connection or Engine.
  See also
  Setting Per-Connection / Sub-Engine Tokens
  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 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

• [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 to as the “record” datatype. Table valued  functions are also supported by Oracle and SQL Server.Features added include:

• the FunctionElement.table_valued() modifier that creates a table-like selectable object from a SQL function
  
• A 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 TableValuedAlias.render_derived() method.
  
• Support for PostgreSQL’s “WITH ORDINALITY” construct using the 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 FunctionElement.column_valued() method
  
• A way to SELECT a single column from a table-valued expression without using a FROM clause via the FunctionElement.scalar_table_valued() method.
  

• See also
  Table-Valued Functions - in the SQLAlchemy Unified Tutorial
  References: #3566
  
• [sql] [usecase]
  Multiple calls to “returning”, e.g. Insert.returning(), may now be chained to add new columns to the RETURNING clause.
  References: #5695
  
• [sql] [usecase]
  Added Select.outerjoin_from() method to complement Select.join_from().
  
• [sql] [usecase]
  Adjusted the “literal_binds” feature of 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 Select.join() method where chaining from the current JOIN wasn’t looking at the right  state, causing an expression like “FROM a JOIN b , b JOIN c ” rather  than “FROM a JOIN b JOIN c ”.
  References: #5858
  
• [sql] [bug]
  Deprecation warnings are emitted under “SQLALCHEMY_WARN_20” mode when passing a plain string to Session.execute().
  References: #5754
  
• [sql] [bug] [orm]A  wide variety of fixes to the “lambda SQL” feature introduced at Using  Lambdas to add significant speed gains to statement production have been  implemented based on user feedback, with an emphasis on its use within  the with_loader_criteria() feature where it is most prominently used [ticket:5760]:

• Fixed the issue where boolean True/False values, which  were referred to 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 literal() objects, would fail to be accommodated correctly. [ticket:5768]
  

• References: #5760, #5763, #5765, #5768, #5770
  
• [sql] [bug] [mysql] [postgresql] [sqlite]
  An informative error message is now raised for a selected set of DML methods (currently all part of Insert constructs) if they are called a second time, which would implicitly  cancel out the previous setting. The methods altered include: on_conflict_do_update, on_conflict_do_nothing (SQLite), on_conflict_do_update, on_conflict_do_nothing (PostgreSQL), on_duplicate_key_update (MySQL)
  References: #5169
  
• [sql] [bug]
  Fixed issue in new Values construct where passing tuples of objects would fall back to per-value type detection rather than making use of the Column objects passed directly to 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 Sequence and Identity objects.
  References: #5722
  
• [sql]
  Replace Query.with_labels() and GenerativeSelect.apply_labels() with explicit getters and setters GenerativeSelect.get_label_style() and GenerativeSelect.set_label_style() to accommodate the three supported label styles: LABEL_STYLE_DISAMBIGUATE_ONLY, LABEL_STYLE_TABLENAME_PLUS_COL, and LABEL_STYLE_NONE.
  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