1.4 Changelog

This document details individual issue-level changes made throughout 1.4 releases. For a narrative overview of what’s new in 1.4, see What’s New in SQLAlchemy 1.4?.

1.4.2

no release date

1.4.1

Released: March 17, 2021

orm

• [orm] [bug] [regression]

  Fixed regression where producing a Core expression construct such as select() using ORM entities would eagerly configure the mappers, in an effort to maintain compatibility with the Query object which necessarily does this to support many backref-related legacy cases. However, core 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 select() and other Core constructs in the absence of ORM loading types of functions such as Load.

  The change maintains the behavior of Query so that backwards compatibility is maintained. However, when using a 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 configure_mappers() or the newer configure() has been called elsewhere. Prefer using 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 Query.exists() method would fail to create an expression if the entity list of the Query were an arbitrary SQL column expression.

  References: #6076

• [orm] [bug] [regression]

  Fixed regression where calling upon Query.count() in conjunction with a loader option such as joinedload() would fail to ignore the loader option. This is a behavior that has always been very specific to the Query.count() method; an error is normally raised if a given Query has options that don’t apply to what it is returning.

  References: #6052

• [orm] [bug] [regression]

  Fixed regression in 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

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

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

• [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 create_engine(), at which point its behavior can be customized using the DialectEvents.do_setinputsizes() hook.

  See also

  Setinputsizes Support

  References: #6058

misc

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

1.4.0

Released: March 15, 2021

orm

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

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

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

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

  Selecting ORM Objects Inline with UPDATE.. RETURNING or INSERT..RETURNING

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

  Example Two - Dataclasses with Declarative Table

  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 towards 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 1.4 / 2.0 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 <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 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 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 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 rows since SQLAlchemy 1.0 are usually aligned with column constructs positionally.

  For legacy ORM queries using 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

• [schema] [feature]

  Added TypeEngine.as_generic() to map dialect-specific types, such as sqlalchemy.dialects.mysql.INTEGER, with the “best match” generic SQLAlchemy type, in this case Integer. Pull request courtesy Andrew Hannigan.

  See also

  Reflecting with Database-Agnostic Types - example usage

  References: #5659

• [schema] [usecase]

  The DDLEvents.column_reflect() event may now be applied to a MetaData object where it will take effect for the Table objects local to that collection.

  See also

  DDLEvents.column_reflect()

  Automating Column Naming Schemes from Reflected Tables - in the ORM mapping documentation

  Intercepting Column Definitions - in the Automap documentation

  References: #5712

• [schema] [usecase]

  Added parameters CreateTable.if_not_exists, CreateIndex.if_not_exists, DropTable.if_exists and DropIndex.if_exists to the CreateTable, DropTable, CreateIndex and 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 Identity construct such that when applied to a Column, it will automatically imply that the value of Column.nullable should default to False, in a similar manner as when the 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 Column.nullable parameter at the same time.

  References: #5775

asyncio

• [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 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 TimeoutError. Also repaired the 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 DBAPI is used. Using a standard DBAPI with async SQLAlchemy will cause it to block like any sync call, interrupting the executing asyncio loop.

postgresql

• [postgresql] [usecase]

  Added new parameter ExcludeConstraint.ops to the 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 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.

  See also

  Prepared Statement Cache

• [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 Table construct such as text() as the argument to 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 Column objects as well as ORM instrumented attributes as keys in the set_ dictionary passed to the Insert.on_conflict_do_update() and Insert.on_conflict_do_update() methods, which match to the Column objects in the .c collection of the target 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

• [mysql] [feature]

  Added support for the aiomysql driver when using the asyncio SQLAlchemy extension.

  See also

  aiomysql

  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

• [sqlite] [usecase]

  Implemented INSERT… ON CONFLICT clause for SQLite. Pull request courtesy Ramon Williams.

  See also

  INSERT…ON CONFLICT (Upsert)

  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

• [mssql] [bug] [datatypes] [mysql]

  Decimal accuracy and behavior has been improved when extracting floating point and/or decimal values from JSON strings using the 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 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

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

misc

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

  References: #5497, #5708

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

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

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

  See also

  Repaired internal importing conventions such that code linters may work correctly

  References: #4656, #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

• [platform] [change]

  Installation has been modernized to use setup.cfg for most package metadata.

  References: #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+.

  See also

  Python 3.6 is the minimum Python 3 version; Python 2.7 still supported

  References: #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

orm

• [orm] [feature]

  The ORM can now generate queries previously only available when using Query using the select() construct directly. A new system by which ORM “plugins” may establish themselves within a Core Select allow the majority of query building logic previously inside of Query to now take place within a compilation-level extension for Select. Similar changes have been made for the Update and Delete constructs as well. The constructs when invoked using Session.execute() now do ORM-related work within the method. For Select, the Result object returned now contains ORM-level entities and results.

  See also

  ORM Query is internally unified with select, update, delete; 2.0 style execution available

  References: #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.join() as well as loader options like joinedload(). Additionally, a “global” version of the option allows limiting criteria to be applied to particular entities in a query globally.

  See also

  Adding Criteria to loader options

  Adding global WHERE / ON criteria

  with_loader_criteria()

  References: #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.

  See also

  Declarative is now integrated into the ORM with new features

  Python Dataclasses, attrs Supported w/ Declarative, Imperative Mappings

  References: #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.

  See also

  Eager loaders emit during unexpire operations

  References: #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.

  See also

  Python Dataclasses, attrs Supported w/ Declarative, Imperative Mappings

  Declarative is now integrated into the ORM with new features

  References: #5027

• [orm] [feature]

  Added “raiseload” feature for ORM mapped columns via 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.

  See also

  Raiseload for Columns

  References: #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

• [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 relationship.overlaps is added to suit those very rare cases where such an overlapping persistence arrangement may be unavoidable.

  References: #5171

• [orm] [usecase]

  The ORM bulk update and delete operations, historically available via the Query.update() and Query.delete() methods as well as via the Update and 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 with_loader_criteria() construct is also supported for with bulk update/delete operations.

  References: #3903, #5018

• [orm] [usecase]

  Update relationship.sync_backref flag in a relationship to make it implicitly False in viewonly=True relationships, preventing synchronization events.

  See also

  Viewonly relationships don’t synchronize backrefs

  References: #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.

  See also

  The “New instance conflicts with existing identity” error is now a warning

  References: #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.

  See also

  Many Core and ORM statement objects now perform much of their construction and validation in the compile phase

• [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 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 selectinload() strategy is likely preferable for eager loading of collections in any case.

  See also

  ORM Rows not uniquified by default

  References: #4395

• [orm] [change]

  The ORM will now warn when asked to coerce a select() construct into a subquery implicitly. This occurs within places such as the Query.select_entity_from() and Query.select_from() methods as well as within the with_polymorphic() function. When a SelectBase (which is what’s produced by select()) or Query object is passed directly to these functions and others, the ORM is typically coercing them to be a subquery by calling the SelectBase.alias() method automatically (which is now superseded by the SelectBase.subquery() method). See the migration notes linked below for further details.

  See also

  A SELECT statement is no longer implicitly considered to be a FROM clause

  References: #4617

• [orm] [change]

  The “KeyedTuple” class returned by 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”.

  See also

  The “KeyedTuple” object returned by Query is replaced by Row

  References: #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.

  See also

  ORM Bulk Update and Delete use RETURNING for “fetch” strategy when available

• [orm] [performance] [postgresql]

  Implemented support for the psycopg2 execute_values() extension within the ORM flush process via the enhancements to Core made in #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.

  See also

  ORM Batch inserts with psycopg2 now batch statements with RETURNING in most cases

  References: #5263

• [orm] [bug]

  A query that is against a mapped inheritance subclass which also uses 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 would add this criteria to the outside query however this interferes with some kinds of query that return other kinds of entities as well.

  See also

  Stricter behavior when querying inheritance mappings using custom queries

  References: #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

• [orm] [bug]

  Fixed bug where a versioning column specified on a mapper against a 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, by fact that a 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

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

• [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 and the database itself remain unaffected.

  See also

  Session features new “autobegin” behavior

  References: #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 is against a Core statement that was ultimately generated from a method such as Query.subquery(). First implemented using a deep search, the current approach leverages the unified select() construct to keep track of the first mapper that is part of the construct.

  References: #4829

• [orm] [bug] [inheritance]

  An ArgumentError is now raised if both the selectable and flat parameters are set to True in 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

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

• [orm] [bug]

  An error is raised if any persistence-related “cascade” settings are made on a 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 where this setting was changed to emit a warning in 1.3.

  See also

  Persistence-related cascade operations disallowed with viewonly=True

  References: #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

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

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

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

  See also

  Accessing an uninitialized collection attribute on a transient object no longer mutates __dict__

  References: #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

• [orm] [bug]

  The behavior of the 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 relationship(), or more generally can be set up across the board by setting the the Session.future flag to True.

  See also

  cascade_backrefs behavior deprecated for removal in 2.0

  References: #5150

• [orm] [deprecated]

  The “slice index” feature used by 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 Session.future flag is set in which case they will raise IndexError.

  References: #5606

• [orm] [deprecated]

  Calling the Query.instances() method without passing a QueryContext is deprecated. The original use case for this was that a 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 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.from_statement() method should be used.

  References: #4719

• [orm] [deprecated]

  Using strings to represent relationship names in ORM operations such as Query.join(), as well as strings for all ORM attribute names in loader options like 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 Query.join() are also deprecated. The aliased() construct now provides for a great deal of flexibility and capability and should be used directly.

  See also

  ORM Query - Joining / loading on relationships uses attributes, not strings

  ORM Query - join(…, aliased=True), from_joinpoint removed

  References: #4705, #5202

• [orm] [deprecated]

  Deprecated logic in Query.distinct() that automatically adds columns in the ORDER BY clause to the columns clause; this will be removed in 2.0.

  See also

  Using DISTINCT with additional columns, but only select the entity

  References: #5134

• [orm] [deprecated]

  Passing keyword arguments to methods such as Session.execute() to be passed into the Session.get_bind() method is deprecated; the new Session.execute.bind_arguments dictionary should be passed instead.

  References: #5573

• [orm] [deprecated]

  The eagerload() and relation() were old aliases and are now deprecated. Use joinedload() and relationship() respectively.

  References: #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

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

• [orm] [removed]

  Remove deprecated function comparable_property. Please refer to the 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.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

engine

• [engine] [feature]

  Implemented an all-new Result object that replaces the previous ResultProxy object. As implemented in Core, the subclass 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 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.

  See also

  New Result object

  References: #4395, #4959, #5087

• [engine] [feature] [orm]

  SQLAlchemy now includes support for Python asyncio within both Core and ORM, using the included asyncio extension. The extension makes use of the greenlet 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 asyncpg driver for PostgreSQL.

  See also

  Asynchronous IO Support for Core and ORM

  References: #3414

• [engine] [feature] [alchemy2]

  Implemented the 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.

  See also

  Migrating to SQLAlchemy 2.0

  References: #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

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

• [engine] [feature]

  The Table.autoload_with parameter now accepts an Inspector object directly, as well as any Engine or Connection as was the case before.

  References: #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 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.

  See also

  RowProxy is no longer a “proxy”; is now called Row and behaves like an enhanced named tuple

  References: #4710

• [engine] [change] [performance] [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

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

• [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, #4808.

  References: #5004

• [engine] [bug]

  The 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.

  See also

  Connection-level transactions can now be inactive based on subtransaction

  References: #4712

• [engine] [bug]

  Adjusted the dialect initialization process such that the Dialect.on_connect() is not called a second time on the first connection. The hook is called first, then the 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

• [engine] [deprecated]

  The URL object is now an immutable named tuple. To modify a URL object, use the URL.set() method to produce a new URL object.

  See also

  The URL object is now immutable - notes on migration

  References: #5526

• [engine] [deprecated]

  The 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 RemovedIn20Warning when SQLAlchemy 2.0 Deprecations Mode is in use.

  See also

  “Implicit” and “Connectionless” execution, “bound metadata” removed

  References: #4634

• [engine] [deprecated]

  The server_side_cursors engine-wide parameter is deprecated and will be removed in a future release. For unbuffered cursors, the Connection.execution_options.stream_results execution option should be used on a per-execution basis.

• [engine] [deprecated]

  The Connection.connect() method is deprecated as is the concept of “connection branching”, which copies a 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

• [engine] [deprecated]

  The case_sensitive flag on 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

• [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 MetaData.create_all() when used against an Engine will run the operation in a BEGIN block if one is not started already.

  See also

  SQLAlchemy 2.0 Deprecations Mode

  References: #4846

• [engine] [deprecated]

  Deprecated the behavior by which a Column can be used as the key in a result set row lookup, when that 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 text() constructs, have been improved so that this fallback logic is avoided in most cases.

  References: #4877

• [engine] [deprecated]

  Deprecated remaining engine-level introspection and utility methods including Engine.run_callable(), Engine.transaction(), Engine.table_names(), Engine.has_table(). The utility methods are superseded by modern context-manager patterns, and the table introspection tasks are suited by the Inspector object.

  References: #4755

• [engine] [removed]

  Remove deprecated method get_primary_keys in the Dialect and Inspector classes. Please refer to the Dialect.get_pk_constraint() and Inspector.get_primary_keys() methods.

  Remove deprecated event dbapi_error and the method ConnectionEvents.dbapi_error. Please refer to the ConnectionEvents.handle_error() event. This change also removes the attributes ExecutionContext.is_disconnect and ExecutionContext.exception.

  References: #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

• [engine] [removed]

  The long-deprecated Inspector.get_table_names.order_by parameter has been removed.

  References: #4755

• [engine] [renamed]

  The Inspector.reflecttable() was renamed to Inspector.reflect_table().

  References: #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.

  See also

  Built-in FROM linting will warn for any potential cartesian products in a SELECT statement

  References: #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.

  See also

  New “post compile” bound parameters used for LIMIT/OFFSET in Oracle, SQL Server

  References: #4808

• [sql] [feature]

  Add support for regular expression on supported backends. Two operations have been defined:

  • ColumnOperators.regexp_match() implementing a regular expression match like function.

  • ColumnOperators.regexp_replace() implementing a regular expression string replace function.

  Supported backends include SQLite, PostgreSQL, MySQL / MariaDB, and Oracle.

  See also

  Support for SQL Regular Expression operators

  References: #1390

• [sql] [feature]

  The 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 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 ColumnCollection has been revised to support duplicate columns as well as to allow integer index access.

  See also

  SELECT objects and derived FROM clauses allow for duplicate columns and column labels

  References: #4753

• [sql] [feature]

  Enhanced the disambiguating labels feature of the 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 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.

  See also

  Selecting from the query itself as a subquery, e.g. “from_self()” - Illustrates the new disambiguation feature as part of a strategy to migrate away from the Query.from_self() method.

  References: #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.

  See also

  All IN expressions render parameters for each value in the list on the fly (e.g. expanding parameters)

  References: #4645

• [sql] [feature]

  Along with the new transparent statement caching feature introduced as part of #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.

  See also

  Using Lambdas to add significant speed gains to statement production

  References: #5380

• [sql] [usecase]

  The Index.create() and Index.drop() methods now have a parameter Index.create.checkfirst, in the same way as that of 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

• [sql] [usecase]

  The true() and false() operators may now be applied as the “onclause” of a 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 for and/or expressions.

• [sql] [usecase]

  Change the method __str of ColumnCollection to avoid confusing it with a python list of string.

  References: #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

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

  See also

  Improved column labeling for simple column expressions using CAST or similar

  References: #4449

• [sql] [change]

  The “clause coercion” system, which is SQLAlchemy Core’s system of receiving arguments and resolving them into 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 select() objects.

  References: #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

• [sql] [change]

  The 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.

  See also

  select(), case() now accept positional expressions

  References: #5284

• [sql] [change]

  As part of the SQLAlchemy 2.0 migration project, a conceptual change has been made to the role of the 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 FromClause. For end users, the change mostly means that any placement of a select() construct in the FROM clause of another select() requires first that it be wrapped in a subquery first, which historically is through the use of the SelectBase.alias() method, and is now also available through the use of 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.

  See also

  A SELECT statement is no longer implicitly considered to be a FROM clause

  References: #4617

• [sql] [change]

  Added a new Core class Subquery, which takes the place of Alias when creating named subqueries against a SelectBase object. Subquery acts in the same way as Alias and is produced from the SelectBase.subquery() method; for ease of use and backwards compatibility, the SelectBase.alias() method is synonymous with this new method.

  See also

  A SELECT statement is no longer implicitly considered to be a FROM clause

  References: #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.

  See also

  Transparent SQL Compilation Caching added to All DQL, DML Statements in Core, ORM

  SQL Compilation Caching

  References: #4639

• [sql] [bug]

  Fixed issue where when constructing constraints from ORM-bound columns, primarily 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

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

• [sql] [bug]

  Improved the 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 tuple_() in a select() or Query will now raise a CompileError at the point at which the 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 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

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

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

• [sql] [bug]

  Reworked the ClauseElement.compare() methods in terms of a new visitor-based approach, and additionally added test coverage ensuring that all 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

• [sql] [bug]

  Deprecate usage of DISTINCT ON in dialect other than PostgreSQL. Deprecate old usage of string distinct in MySQL dialect

  References: #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 CompoundSelect.order_by() in terms of a 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 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 CompoundSelect.selected_columns collections to be usable in the CompoundSelect.order_by() method.

  References: #4617

• [sql] [bug]

  The Join construct no longer considers the “onclause” as a source of additional FROM objects to be omitted from the FROM list of an enclosing 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 Select / Join behave a bit more intuitively.

  References: #4621

• [sql] [deprecated]

  The 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

• [sql] [deprecated]

  The Table class now raises a deprecation warning when columns with the same name are defined. To replace a column a new parameter Table.append_column.replace_existing was added to the Table.append_column() method.

  The 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.

  See also

  “threadlocal” engine strategy deprecated - from the 1.3 migration notes which discusses the rationale for deprecation.

  References: #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 iterate() and 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.

• [sql] [removed]

  Remove deprecated methods Compiled.compile, ClauseElement.__and__ and ClauseElement.__or__ and attribute Over.func.

  Remove deprecated FromClause.count method. Please use the count function available from the func namespace.

  References: #4643

• [sql] [removed]

  Remove deprecated parameters text.bindparams and text.typemap. Please refer to the TextClause.bindparams() and TextClause.columns() methods.

  Remove deprecated parameter Table.useexisting. Please use Table.extend_existing.

  References: #4643

• [sql] [renamed]

  Table parameter mustexist has been renamed to Table.must_exist and will now warn when used.

• [sql] [renamed]

  The SelectBase.as_scalar() and Query.as_scalar() methods have been renamed to SelectBase.scalar_subquery() and Query.scalar_subquery(), respectively. The old names continue to exist within 1.4 series with a deprecation warning. In addition, the implicit coercion of SelectBase, Alias, and other SELECT oriented objects into scalar subqueries when evaluated in a column context is also deprecated, and emits a warning that the 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 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 ` select() object or Query object.

  This change is part of the larger change to convert 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

• [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, #5435

• [sql] [postgresql]

  Allow specifying the data type when creating a Sequence in PostgreSQL by using the parameter Sequence.data_type.

  References: #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

• [sql] [reflection]

  Added support for reflecting “identity” columns, which are now returned as part of the structure returned by Inspector.get_columns(). When reflecting full Table objects, identity columns will be represented using the Identity construct. Currently the supported backends are PostgreSQL >= 10, Oracle >= 12 and MSSQL (with different syntax and a subset of functionalities).

  References: #5324, #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.

  See also

  Enum and Boolean datatypes no longer default to “create constraint”

  References: #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

• [schema] [removed]

  Remove deprecated class Binary. Please use LargeBinary.

  References: #4643

• [schema] [renamed]

  Renamed the Table.tometadata() method to Table.to_metadata(). The previous name remains with a deprecation warning.

  References: #5413

• [schema] [sql]

  Added the 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, #5360, #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

• [extensions] [change]

  Added new parameter AutomapBase.prepare.autoload_with which supersedes AutomapBase.prepare.reflect and AutomapBase.prepare.engine.

  References: #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.

  See also

  Setting READ ONLY / DEFERRABLE

  References: #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

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

  See also

  psycopg2 dialect features “execute_values” with RETURNING for INSERT statements by default - full list of changes regarding the executemany_mode parameter.

  References: #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

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

mysql

• [mysql] [feature]

  Added support for MariaDB Connector/Python to the mysql dialect. Original pull request courtesy Georg Richter.

  References: #5459

• [mysql] [usecase]

  Added a new dialect token “mariadb” that may be used in place of “mysql” in the 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

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

  See also

  Added Sequence support for MariaDB 10.3

  References: #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, 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

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

• [mysql] [deprecated]

  The OurSQL dialect is deprecated.

  References: #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 ENUM and SET in the mysql dialect. The values passed to the enum or the set are quoted by SQLAlchemy when needed automatically.

  References: #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.

  See also

  Removed “join rewriting” logic from SQLite dialect; updated imports

  References: #4895

mssql

• [mssql] [feature] [sql]

  Added support for the JSON datatype on the SQL Server dialect using the 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

• [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 Auto Increment Behavior / IDENTITY Columns. 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.

  See also

  Added Sequence support distinct from IDENTITY to SQL Server

  References: #4235, #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

• [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 Index. The entry is both part of the dictionary returned by Inspector.get_indexes() as well as part of a reflected Index construct that was reflected. Pull request courtesy Ramon Williams.

  References: #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

• [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 still used for a query that features only LIMIT. Pull request courtesy Elkin.

  References: #5084

• [mssql] [bug] [schema]

  Fixed an issue where sqlalchemy.engine.reflection.has_table() always returned False for temporary tables.

  References: #5597

• [mssql] [bug]

  Fixed the base class of the DATETIMEOFFSET datatype to be based on the DateTime class hierarchy, as this is a datetime-holding datatype.

  References: #4980

• [mssql] [deprecated]

  The adodbapi and mxODBC dialects are deprecated.

  References: #5189

• [mssql]

  The mssql dialect will assume that at least MSSQL 2005 is used. There is no hard exception raised if a previous version is detected, but operations may fail for older versions.

• [mssql] [reflection]

  As part of the support for reflecting Identity objects, the method Inspector.get_columns() no longer returns mssql_identity_start and mssql_identity_increment as part of the dialect_options. Use the information in the identity key instead.

  References: #5527

• [mssql] [engine]

  Deprecated the legacy_schema_aliasing parameter to sqlalchemy.create_engine(). This is a long-outdated parameter that has defaulted to False since version 1.1.

  References: #4809

oracle

• [oracle] [usecase]

  The max_identifier_length for the Oracle dialect is now 128 characters by default, unless compatibility version less than 12.2 upon first connect, in which case the legacy length of 30 characters is used. This is a continuation of the issue as committed to the 1.3 series which adds max identifier length detection upon first connect as well as warns for the change in Oracle server.

  See also

  Max Identifier Lengths - in the Oracle dialect documentation

  References: #4857

• [oracle] [change]

  The LIMIT / OFFSET scheme used in Oracle now makes use of named subqueries rather than unnamed subqueries when it transparently rewrites a SELECT statement to one that uses a subquery that includes ROWNUM. The change is part of a larger change where unnamed subqueries are no longer directly supported by Core, as well as to modernize the internal use of the select() construct within the Oracle dialect.

• [oracle] [bug]

  Correctly render Sequence and Identity column options nominvalue and nomaxvalue as NOMAXVALUE` and ``NOMINVALUE on oracle database.

• [oracle] [bug]

  The INTERVAL class of the Oracle dialect is now correctly a subclass of the abstract version of Interval as well as the correct “emulated” base class, which allows for correct behavior under both native and non-native modes; previously it was only based on TypeEngine.

  References: #4971

firebird

• [firebird] [deprecated]

  The Firebird dialect is deprecated, as there is now a 3rd party dialect that supports this database.

  References: #5189

misc

• [misc] [deprecated]

  The Sybase dialect is deprecated.

  References: #5189