2.0.0b1

Released: October 13, 2022

general

• [general] [changed] Migrated the codebase to remove all pre-2.0 behaviors and architectures that were previously noted as deprecated for removal in 2.0, including, but not limited to:

  -   removal of all Python 2 code, minimum version is now Python 3.7
  
  -   `_engine.Engine` and `_engine.Connection` now use the
      new 2.0 style of working, which includes "autobegin", library level
      autocommit removed, subtransactions and "branched" connections
      removed
  
  -   Result objects use 2.0-style behaviors; `_result.Row` is fully
      a named tuple without "mapping" behavior, use `_result.RowMapping`
      for "mapping" behavior
  
  -   All Unicode encoding/decoding architecture has been removed from
      SQLAlchemy.  All modern DBAPI implementations support Unicode
      transparently thanks to Python 3, so the `convert_unicode` feature
      as well as related mechanisms to look for bytestrings in
      DBAPI `cursor.description` etc. have been removed.
  
  -   The `.bind` attribute and parameter from `MetaData`,
      `Table`, and from all DDL/DML/DQL elements that previously could
      refer to a "bound engine"
  
  -   The standalone `sqlalchemy.orm.mapper()` function is removed; all
      classical mapping should be done through the
      `_orm.registry.map_imperatively()` method of `_orm.registry`.
  
  -   The `_orm.Query.join()` method no longer accepts strings for
      relationship names; the long-documented approach of using
      `Class.attrname` for join targets is now standard.
  
  -   `_orm.Query.join()` no longer accepts the "aliased" and
      "from_joinpoint" arguments
  
  -   `_orm.Query.join()` no longer accepts chains of multiple join
      targets in one method call.
  
  -   `Query.from_self()`, `Query.select_entity_from()` and
      `Query.with_polymorphic()` are removed.
  
  -   The `_orm.relationship.cascade_backrefs` parameter must now
      remain at its new default of `False`; the `save-update` cascade
      no longer cascades along a backref.
  
  -   the `_orm.Session.future` parameter must always be set to
      `True`.  2.0-style transactional patterns for `_orm.Session`
      are now always in effect.
  
  -   Loader options no longer accept strings for attribute names.  The
      long-documented approach of using `Class.attrname` for loader option
      targets is now standard.
  
  -   Legacy forms of `_sql.select()` removed, including
      `select([cols])`, the "whereclause" and keyword parameters of
      `some_table.select()`.
  
  -   Legacy "in-place mutator" methods on `_sql.Select` such as
      `append_whereclause()`, `append_order_by()` etc are removed.
  
  -   Removed the very old "dbapi_proxy" module, which in very early
      SQLAlchemy releases was used to provide a transparent connection pool
      over a raw DBAPI connection.
  

  References: #7257

• [general] [changed] The _orm.Query.instances() method is deprecated. The behavioral contract of this method, which is that it can iterate objects through arbitrary result sets, is long obsolete and no longer tested. Arbitrary statements can return objects by using constructs such as :meth.Select.from_statement or _orm.aliased().

platform

• [platform] [feature] The SQLAlchemy C extensions have been replaced with all new implementations written in Cython. Like the C extensions before, pre-built wheel files for a wide range of platforms are available on pypi so that building is not an issue for common platforms. For custom builds, python setup.py build_ext works as before, needing only the additional Cython install. pyproject.toml is also part of the source now which will establish the proper build dependencies when using pip.

  References: #7256

• [platform] [change] SQLAlchemy's source build and installation now includes a pyproject.toml file for full PEP 517 support.

  References: #7311

orm

• [orm] [feature] [sql] Added new feature to all included dialects that support RETURNING called "insertmanyvalues". This is a generalization of the "fast executemany" feature first introduced for the psycopg2 driver in 1.4 at change_5263, which allows the ORM to batch INSERT statements into a much more efficient SQL structure while still being able to fetch newly generated primary key and SQL default values using RETURNING.

  The feature now applies to the many dialects that support RETURNING along with multiple VALUES constructs for INSERT, including all PostgreSQL drivers, SQLite, MariaDB, MS SQL Server. Separately, the Oracle dialect also gains the same capability using native cx_Oracle or OracleDB features.

  References: #6047

• [orm] [feature] Added new parameter _orm.AttributeEvents.include_key, which will include the dictionary or list key for operations such as __setitem__() (e.g. obj[key] = value) and __delitem__() (e.g. del obj[key]), using a new keyword parameter "key" or "keys", depending on event, e.g. _orm.AttributeEvents.append.key, _orm.AttributeEvents.bulk_replace.keys. This allows event handlers to take into account the key that was passed to the operation and is of particular importance for dictionary operations working with _orm.MappedCollection.

  References: #8375

• [orm] [feature] Added new parameter _sql.Operators.op.python_impl, available from _sql.Operators.op() and also when using the _sql.Operators.custom_op constructor directly, which allows an in-Python evaluation function to be provided along with the custom SQL operator. This evaluation function becomes the implementation used when the operator object is used given plain Python objects as operands on both sides, and in particular is compatible with the synchronize_session='evaluate' option used with orm_expression_update_delete.

  References: #3162

• [orm] [feature] The _orm.Session (and by extension AsyncSession) now has new state-tracking functionality that will proactively trap any unexpected state changes which occur as a particular transactional method proceeds. This is to allow situations where the _orm.Session is being used in a thread-unsafe manner, where event hooks or similar may be calling unexpected methods within operations, as well as potentially under other concurrency situations such as asyncio or gevent to raise an informative message when the illegal access first occurs, rather than passing silently leading to secondary failures due to the _orm.Session being in an invalid state.

  References: #7433

• [orm] [feature] The _orm.composite() mapping construct now supports automatic resolution of values when used with a Python dataclass; the __composite_values__() method no longer needs to be implemented as this method is derived from inspection of the dataclass.

  Additionally, classes mapped by _orm.composite now support ordering comparison operations, e.g. <, >=, etc.

  See the new documentation at mapper_composite for examples.

• [orm] [feature] Added very experimental feature to the _orm.selectinload() and _orm.immediateload() loader options called _orm.selectinload.recursion_depth / _orm.immediateload.recursion_depth , which allows a single loader option to automatically recurse into self-referential relationships. Is set to an integer indicating depth, and may also be set to -1 to indicate to continue loading until no more levels deep are found. Major internal changes to _orm.selectinload() and _orm.immediateload() allow this feature to work while continuing to make correct use of the compilation cache, as well as not using arbitrary recursion, so any level of depth is supported (though would emit that many queries). This may be useful for self-referential structures that must be loaded fully eagerly, such as when using asyncio.

  A warning is also emitted when loader options are connected together with arbitrary lengths (that is, without using the new recursion_depth option) when excessive recursion depth is detected in related object loading. This operation continues to use huge amounts of memory and performs extremely poorly; the cache is disabled when this condition is detected to protect the cache from being flooded with arbitrary statements.

  References: #8126

• [orm] [feature] Added new parameter _orm.Session.autobegin, which when set to False will prevent the _orm.Session from beginning a transaction implicitly. The _orm.Session.begin() method must be called explicitly first in order to proceed with operations, otherwise an error is raised whenever any operation would otherwise have begun automatically. This option can be used to create a "safe" _orm.Session that won't implicitly start new transactions.

  As part of this change, also added a new status variable _orm.SessionTransaction.origin which may be useful for event handling code to be aware of the origin of a particular _orm.SessionTransaction.

  References: #6928

• [orm] [feature] Declarative mixins which use _schema.Column objects that contain _schema.ForeignKey references no longer need to use _orm.declared_attr() to achieve this mapping; the _schema.ForeignKey object is copied along with the _schema.Column itself when the column is applied to the declared mapping.

• [orm] [usecase] Added _orm.load_only.raiseload parameter to the _orm.load_only() loader option, so that the unloaded attributes may have "raise" behavior rather than lazy loading. Previously there wasn't really a way to do this with the _orm.load_only() option directly.

• [orm] [change] To better accommodate explicit typing, the names of some ORM constructs that are typically constructed internally, but nonetheless are sometimes visible in messaging as well as typing, have been changed to more succinct names which also match the name of their constructing function (with different casing), in all cases maintaining aliases to the old names for the forseeable future:

  -   `_orm.RelationshipProperty` becomes an alias for the primary name
      `_orm.Relationship`, which is constructed as always from the
      `_orm.relationship()` function
  
  -   `_orm.SynonymProperty` becomes an alias for the primary name
      `_orm.Synonym`, constructed as always from the
      `_orm.synonym()` function
  
  -   `_orm.CompositeProperty` becomes an alias for the primary name
      `_orm.Composite`, constructed as always from the
      `_orm.composite()` function
  

• [orm] [change] For consistency with the prominent ORM concept _orm.Mapped, the names of the dictionary-oriented collections, _orm.attribute_mapped_collection(), _orm.column_mapped_collection(), and _orm.MappedCollection, are changed to _orm.attribute_keyed_dict(), _orm.column_keyed_dict() and _orm.KeyFuncDict, using the phrase "dict" to minimize any confusion against the term "mapped". The old names will remain indefinitely with no schedule for removal.

  References: #8608

• [orm] [bug] All _result.Result objects will now consistently raise _exc.ResourceClosedError if they are used after a hard close, which includes the "hard close" that occurs after calling "single row or value" methods like _result.Result.first() and _result.Result.scalar(). This was already the behavior of the most common class of result objects returned for Core statement executions, i.e. those based on _engine.CursorResult, so this behavior is not new. However, the change has been extended to properly accommodate for the ORM "filtering" result objects returned when using 2.0 style ORM queries, which would previously behave in "soft closed" style of returning empty results, or wouldn't actually "soft close" at all and would continue yielding from the underlying cursor.

  As part of this change, also added _result.Result.close() to the base _result.Result class and implemented it for the filtered result implementations that are used by the ORM, so that it is possible to call the _engine.CursorResult.close() method on the underlying _engine.CursorResult when the the yield_per execution option is in use to close a server side cursor before remaining ORM results have been fetched. This was again already available for Core result sets but the change makes it available for 2.0 style ORM results as well.

  This change is also backported to: 1.4.27

  References: #7274

• [orm] [bug] Fixed issue where the _orm.registry.map_declaratively() method would return an internal "mapper config" object and not the Mapper object as stated in the API documentation.

• [orm] [bug] Fixed performance regression which appeared at least in version 1.3 if not earlier (sometime after 1.0) where the loading of deferred columns, those explicitly mapped with _orm.defer() as opposed to non-deferred columns that were expired, from a joined inheritance subclass would not use the "optimized" query which only queried the immediate table that contains the unloaded columns, instead running a full ORM query which would emit a JOIN for all base tables, which is not necessary when only loading columns from the subclass.

  References: #7463

• [orm] [bug] The internals for the _orm.Load object and related loader strategy patterns have been mostly rewritten, to take advantage of the fact that only attribute-bound paths, not strings, are now supported. The rewrite hopes to make it more straightforward to address new use cases and subtle issues within the loader strategy system going forward.

  References: #6986

• [orm] [bug] Made an improvement to the "deferred" / "load_only" set of strategy options where if a certain object is loaded from two different logical paths within one query, attributes that have been configured by at least one of the options to be populated will be populated in all cases, even if other load paths for that same object did not set this option. previously, it was based on randomness as to which "path" addressed the object first.

  References: #8166

• [orm] [bug] Fixed issue in ORM enabled UPDATE when the statement is created against a joined-inheritance subclass, updating only local table columns, where the "fetch" synchronization strategy would not render the correct RETURNING clause for databases that use RETURNING for fetch synchronization. Also adjusts the strategy used for RETURNING in UPDATE FROM and DELETE FROM statements.

  References: #8344

• [orm] [bug] [asyncio] Removed the unused **kw arguments from _asyncio.AsyncSession.begin and _asyncio.AsyncSession.begin_nested. These kw aren't used and appear to have been added to the API in error.

  References: #7703

• [orm] [bug] Changed the attribute access method used by _orm.attribute_mapped_collection() and _orm.column_mapped_collection(), used when populating the dictionary, to assert that the data value on the object to be used as the dictionary key is actually present, and is not instead using "None" due to the attribute never being actually assigned. This is used to prevent a mis-population of None for a key when assigning via a backref where the "key" attribute on the object is not yet assigned.

  As the failure mode here is a transitory condition that is not typically persisted to the database, and is easy to produce via the constructor of the class based on the order in which parameters are assigned, it is very possible that many applications include this behavior already which is silently passed over. To accommodate for applications where this error is now raised, a new parameter _orm.attribute_mapped_collection.ignore_unpopulated_attribute is also added to both _orm.attribute_mapped_collection() and _orm.column_mapped_collection() that instead causes the erroneous backref assignment to be skipped.

  References: #8372

• [orm] [bug] Added new parameter AbstractConcreteBase.strict_attrs to the AbstractConcreteBase declarative mixin class. The effect of this parameter is that the scope of attributes on subclasses is correctly limited to the subclass in which each attribute is declared, rather than the previous behavior where all attributes of the entire hierarchy are applied to the base "abstract" class. This produces a cleaner, more correct mapping where subclasses no longer have non-useful attributes on them which are only relevant to sibling classes. The default for this parameter is False, which leaves the previous behavior unchanged; this is to support existing code that makes explicit use of these attributes in queries. To migrate to the newer approach, apply explicit attributes to the abstract base class as needed.

  References: #8403

• [orm] [bug] The behavior of _orm.defer() regarding primary key and "polymorphic discriminator" columns is revised such that these columns are no longer deferrable, either explicitly or when using a wildcard such as defer('*'). Previously, a wildcard deferral would not load PK/polymorphic columns which led to errors in all cases, as the ORM relies upon these columns to produce object identities. The behavior of explicit deferral of primary key columns is unchanged as these deferrals already were implicitly ignored.

  References: #7495

• [orm] [bug] Fixed bug in the behavior of the _orm.Mapper.eager_defaults parameter such that client-side SQL default or onupdate expressions in the table definition alone will trigger a fetch operation using RETURNING or SELECT when the ORM emits an INSERT or UPDATE for the row. Previously, only server side defaults established as part of table DDL and/or server-side onupdate expressions would trigger this fetch, even though client-side SQL expressions would be included when the fetch was rendered.

  References: #7438

engine

• [engine] [feature] The DialectEvents.handle_error() event is now moved to the DialectEvents suite from the EngineEvents suite, and now participates in the connection pool "pre ping" event for those dialects that make use of disconnect codes in order to detect if the database is live. This allows end-user code to alter the state of "pre ping". Note that this does not include dialects which contain a native "ping" method such as that of psycopg2 or most MySQL dialects.

  References: #5648

• [engine] [feature] The ConnectionEvents.set_connection_execution_options() and ConnectionEvents.set_engine_execution_options() event hooks now allow the given options dictionary to be modified in-place, where the new contents will be received as the ultimate execution options to be acted upon. Previously, in-place modifications to the dictionary were not supported.

• [engine] [usecase] Generalized the _sa.create_engine.isolation_level parameter to the base dialect so that it is no longer dependent on individual dialects to be present. This parameter sets up the "isolation level" setting to occur for all new database connections as soon as they are created by the connection pool, where the value then stays set without being reset on every checkin.

  The _sa.create_engine.isolation_level parameter is essentially equivalent in functionality to using the _engine.Engine.execution_options.isolation_level parameter via _engine.Engine.execution_options() for an engine-wide setting. The difference is in that the former setting assigns the isolation level just once when a connection is created, the latter sets and resets the given level on each connection checkout.

  References: #6342

• [engine] [change] Some small API changes regarding engines and dialects:

  -   The `Dialect.set_isolation_level()`, `Dialect.get_isolation_level()`,
      :meth:
      dialect methods will always be passed the raw DBAPI connection
  
  -   The `Connection` and `Engine` classes no longer share a base
      `Connectable` superclass, which has been removed.
  
  -   Added a new interface class `PoolProxiedConnection` - this is the
      public facing interface for the familiar `_ConnectionFairy`
      class which is nonetheless a private class.
  

  References: #7122

• [engine] [bug] [regression] Fixed regression where the _engine.CursorResult.fetchmany() method would fail to autoclose a server-side cursor (i.e. when stream_results or yield_per is in use, either Core or ORM oriented results) when the results were fully exhausted.

  This change is also backported to: 1.4.27

  References: #7274

• [engine] [bug] Fixed issue in future _engine.Engine where calling upon _engine.Engine.begin() and entering the context manager would not close the connection if the actual BEGIN operation failed for some reason, such as an event handler raising an exception; this use case failed to be tested for the future version of the engine. Note that the "future" context managers which handle begin() blocks in Core and ORM don't actually run the "BEGIN" operation until the context managers are actually entered. This is different from the legacy version which runs the "BEGIN" operation up front.

  This change is also backported to: 1.4.27

  References: #7272

• [engine] [bug] For improved security, the _url.URL object will now use password obfuscation by default when str(url) is called. To stringify a URL with cleartext password, the _url.URL.render_as_string() may be used, passing the _url.URL.render_as_string.hide_password parameter as False. Thanks to our contributors for this pull request.

  References: #8567

• [engine] [bug] The _engine.Inspector.has_table() method will now consistently check for views of the given name as well as tables. Previously this behavior was dialect dependent, with PostgreSQL, MySQL/MariaDB and SQLite supporting it, and Oracle and SQL Server not supporting it. Third party dialects should also seek to ensure their _engine.Inspector.has_table() method searches for views as well as tables for the given name.

  References: #7161

• [engine] [bug] Fixed issue in Result.columns() method where calling upon Result.columns() with a single index could in some cases, particularly ORM result object cases, cause the Result to yield scalar objects rather than Row objects, as though the Result.scalars() method had been called. In SQLAlchemy 1.4, this scenario emits a warning that the behavior will change in SQLAlchemy 2.0.

  References: #7953

• [engine] [bug] Passing a DefaultGenerator object such as a Sequence to the Connection.execute() method is deprecated, as this method is typed as returning a CursorResult object, and not a plain scalar value. The Connection.scalar() method should be used instead, which has been reworked with new internal codepaths to suit invoking a SELECT for default generation objects without going through the Connection.execute() method.

• [engine] [removed] Removed the previously deprecated case_sensitive parameter from _sa.create_engine(), which would impact only the lookup of string column names in Core-only result set rows; it had no effect on the behavior of the ORM. The effective behavior of what case_sensitive refers towards remains at its default value of True, meaning that string names looked up in row._mapping will match case-sensitively, just like any other Python mapping.

  Note that the case_sensitive parameter was not in any way related to the general subject of case sensitivity control, quoting, and "name normalization" (i.e. converting for databases that consider all uppercase words to be case insensitive) for DDL identifier names, which remains a normal core feature of SQLAlchemy.

• [engine] [removed] Removed legacy and deprecated package sqlalchemy.databases. Please use sqlalchemy.dialects instead.

  References: #7258

• [engine] [deprecations] The _sa.create_engine.implicit_returning parameter is deprecated on the _sa.create_engine() function only; the parameter remains available on the _schema.Table object. This parameter was originally intended to enable the "implicit returning" feature of SQLAlchemy when it was first developed and was not enabled by default. Under modern use, there's no reason this parameter should be disabled, and it has been observed to cause confusion as it degrades performance and makes it more difficult for the ORM to retrieve recently inserted server defaults. The parameter remains available on _schema.Table to specifically suit database-level edge cases which make RETURNING infeasible, the sole example currently being SQL Server's limitation that INSERT RETURNING may not be used on a table that has INSERT triggers on it.

  References: #6962

sql

• [sql] [feature] Added long-requested case-insensitive string operators _sql.ColumnOperators.icontains(), _sql.ColumnOperators.istartswith(), _sql.ColumnOperators.iendswith(), which produce case-insensitive LIKE compositions (using ILIKE on PostgreSQL, and the LOWER() function on all other backends) to complement the existing LIKE composition operators _sql.ColumnOperators.contains(), _sql.ColumnOperators.startswith(), etc. Huge thanks to Matias Martinez Rebori for their meticulous and complete efforts in implementing these new methods.

  References: #3482

• [sql] [feature] Added new syntax to the FromClause.c collection on all FromClause objects allowing tuples of keys to be passed to __getitem__(), along with support for the _sql.select() construct to handle the resulting tuple-like collection directly, allowing the syntax select(table.c['a', 'b', 'c']) to be possible. The sub-collection returned is itself a ColumnCollection which is also directly consumable by _sql.select() and similar now.

  References: #8285

• [sql] [usecase] Altered the compilation mechanics of the _dml.Insert construct such that the "autoincrement primary key" column value will be fetched via cursor.lastrowid or RETURNING even if present in the parameter set or within the _dml.Insert.values() method as a plain bound value, for single-row INSERT statements on specific backends that are known to generate autoincrementing values even when explicit NULL is passed. This restores a behavior that was in the 1.3 series for both the use case of separate parameter set as well as _dml.Insert.values(). In 1.4, the parameter set behavior unintentionally changed to no longer do this, but the _dml.Insert.values() method would still fetch autoincrement values up until 1.4.21 where #6770 changed the behavior yet again again unintentionally as this use case was never covered.

  The behavior is now defined as "working" to suit the case where databases such as SQLite, MySQL and MariaDB will ignore an explicit NULL primary key value and nonetheless invoke an autoincrement generator.

  References: #7998

• [sql] [usecase] Added new parameter HasCTE.add_cte.nest_here to HasCTE.add_cte() which will "nest" a given CTE at the level of the parent statement. This parameter is equivalent to using the HasCTE.cte.nesting parameter, but may be more intuitive in some scenarios as it allows the nesting attribute to be set simultaneously along with the explicit level of the CTE.

  The HasCTE.add_cte() method also accepts multiple CTE objects.

  References: #7759

• [sql] [bug] The FROM clauses that are established on a _sql.select() construct when using the _sql.Select.select_from() method will now render first in the FROM clause of the rendered SELECT, which serves to maintain the ordering of clauses as was passed to the _sql.Select.select_from() method itself without being affected by the presence of those clauses also being mentioned in other parts of the query. If other elements of the _sql.Select also generate FROM clauses, such as the columns clause or WHERE clause, these will render after the clauses delivered by _sql.Select.select_from() assuming they were not explictly passed to _sql.Select.select_from() also. This improvement is useful in those cases where a particular database generates a desirable query plan based on a particular ordering of FROM clauses and allows full control over the ordering of FROM clauses.

  References: #7888

• [sql] [bug] The Enum.length parameter, which sets the length of the VARCHAR column for non-native enumeration types, is now used unconditionally when emitting DDL for the VARCHAR datatype, including when the Enum.native_enum parameter is set to True for target backends that continue to use VARCHAR. Previously the parameter would be erroneously ignored in this case. The warning previously emitted for this case is now removed.

  References: #7791

• [sql] [bug] The in-place type detection for Python integers, as occurs with an expression such as literal(25), will now apply value-based adaption as well to accommodate Python large integers, where the datatype determined will be BigInteger rather than Integer. This accommodates for dialects such as that of asyncpg which both sends implicit typing information to the driver as well as is sensitive to numeric scale.

  References: #7909

• [sql] [bug] Added if_exists and if_not_exists parameters for all "Create" / "Drop" constructs including CreateSequence, DropSequence, CreateIndex, DropIndex, etc. allowing generic "IF EXISTS" / "IF NOT EXISTS" phrases to be rendered within DDL. Pull request courtesy Jesse Bakker.

  References: #7354

• [sql] [bug] Improved the construction of SQL binary expressions to allow for very long expressions against the same associative operator without special steps needed in order to avoid high memory use and excess recursion depth. A particular binary operation A op B can now be joined against another element op C and the resulting structure will be "flattened" so that the representation as well as SQL compilation does not require recursion.

  One effect of this change is that string concatenation expressions which use SQL functions come out as "flat", e.g. MySQL will now render concat('x', 'y', 'z', ...)`` rather than nesting together two-element functions like concat(concat('x', 'y'), 'z'). Third-party dialects which override the string concatenation operator will need to implement a new method def visit_concat_op_expression_clauselist()to accompany the existingdef visit_concat_op_binary()` method.

  References: #7744

• [sql] [bug] Implemented full support for "truediv" and "floordiv" using the "/" and "//" operators. A "truediv" operation between two expressions using _types.Integer now considers the result to be _types.Numeric, and the dialect-level compilation will cast the right operand to a numeric type on a dialect-specific basis to ensure truediv is achieved. For floordiv, conversion is also added for those databases that don't already do floordiv by default (MySQL, Oracle) and the FLOOR() function is rendered in this case, as well as for cases where the right operand is not an integer (needed for PostgreSQL, others).

  The change resolves issues both with inconsistent behavior of the division operator on different backends and also fixes an issue where integer division on Oracle would fail to be able to fetch a result due to inappropriate outputtypehandlers.

  References: #4926

• [sql] [bug] Added an additional lookup step to the compiler which will track all FROM clauses which are tables, that may have the same name shared in multiple schemas where one of the schemas is the implicit "default" schema; in this case, the table name when referring to that name without a schema qualification will be rendered with an anonymous alias name at the compiler level in order to disambiguate the two (or more) names. The approach of schema-qualifying the normally unqualified name with the server-detected "default schema name" value was also considered, however this approach doesn't apply to Oracle nor is it accepted by SQL Server, nor would it work with multiple entries in the PostgreSQL search path. The name collision issue resolved here has been identified as affecting at least Oracle, PostgreSQL, SQL Server, MySQL and MariaDB.

  References: #7471

• [sql] [bug] The _functions.array_agg will now set the array dimensions to 1. Improved _types.ARRAY processing to accept None values as value of a multi-array.

  References: #7083

schema

• [schema] [feature] Expanded on the "conditional DDL" system implemented by the _schema.ExecutableDDLElement class (renamed from _schema.DDLElement) to be directly available on _schema.SchemaItem constructs such as _schema.Index, _schema.ForeignKeyConstraint, etc. such that the conditional logic for generating these elements is included within the default DDL emitting process. This system can also be accommodated by a future release of Alembic to support conditional DDL elements within all schema-management systems.

  References: #7631

• [schema] [usecase] Added parameter _ddl.DropConstraint.if_exists to the _ddl.DropConstraint construct which result in "IF EXISTS" DDL being added to the DROP statement. This phrase is 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 Mike Fiedler.

  References: #8141

• [schema] [usecase] Implemented the DDL event hooks DDLEvents.before_create(), DDLEvents.after_create(), DDLEvents.before_drop(), DDLEvents.after_drop() for all SchemaItem objects that include a distinct CREATE or DROP step, when that step is invoked as a distinct SQL statement, including for ForeignKeyConstraint, Sequence, Index, and PostgreSQL's _postgresql.ENUM.

  References: #8394

• [schema] [performance] Rearchitected the schema reflection API to allow participating dialects to make use of high performing batch queries to reflect the schemas of many tables at once using fewer queries by an order of magnitude. The new performance features are targeted first at the PostgreSQL and Oracle backends, and may be applied to any dialect that makes use of SELECT queries against system catalog tables to reflect tables. The change also includes new API features and behavioral improvements to the Inspector object, including consistent, cached behavior of methods like Inspector.has_table(), Inspector.get_table_names() and new methods Inspector.has_schema() and Inspector.has_index().

  References: #4379

• [schema] [bug] The warnings that are emitted regarding reflection of indexes or unique constraints, when the Table.include_columns parameter is used to exclude columns that are then found to be part of those constraints, have been removed. When the Table.include_columns parameter is used it should be expected that the resulting Table construct will not include constraints that rely upon omitted columns. This change was made in response to #8100 which repaired Table.include_columns in conjunction with foreign key constraints that rely upon omitted columns, where the use case became clear that omitting such constraints should be expected.

  References: #8102

• [schema] [postgresql] Added support for comments on Constraint objects, including DDL and reflection; the field is added to the base Constraint class and corresponding constructors, however PostgreSQL is the only included backend to support the feature right now. See parameters such as ForeignKeyConstraint.comment, UniqueConstraint.comment or CheckConstraint.comment.

  References: #5677

• [schema] [mariadb] [mysql] Add support for Partitioning and Sample pages on MySQL and MariaDB reflected options. The options are stored in the table dialect options dictionary, so the following keyword need to be prefixed with mysql_ or mariadb_ depending on the backend. Supported options are:

  -   `stats_sample_pages`
  
  -   `partition_by`
  
  -   `partitions`
  
  -   `subpartition_by`
  

  These options are also reflected when loading a table from database, and will populate the table _schema.Table.dialect_options. Pull request courtesy of Ramon Will.

  References: #4038

typing

• [typing] [improvement] The _sqltypes.TypeEngine.with_variant() method now returns a copy of the original _sqltypes.TypeEngine object, rather than wrapping it inside the Variant class, which is effectively removed (the import symbol remains for backwards compatibility with code that may be testing for this symbol). While the previous approach maintained in-Python behaviors, maintaining the original type allows for clearer type checking and debugging.

  _sqltypes.TypeEngine.with_variant() also accepts multiple dialect names per call as well, in particular this is helpful for related backend names such as "mysql", "mariadb".

  References: #6980

postgresql

• [postgresql] [feature] Added a new PostgreSQL _postgresql.DOMAIN datatype, which follows the same CREATE TYPE / DROP TYPE behaviors as that of PostgreSQL _postgresql.ENUM. Much thanks to David Baumgold for the efforts on this.

  References: #7316

• [postgresql] [usecase] [asyncpg] Added overridable methods PGDialect_asyncpg.setup_asyncpg_json_codec and PGDialect_asyncpg.setup_asyncpg_jsonb_codec codec, which handle the required task of registering JSON/JSONB codecs for these datatypes when using asyncpg. The change is that methods are broken out as individual, overridable methods to support third party dialects that need to alter or disable how these particular codecs are set up.

  This change is also backported to: 1.4.27

  References: #7284

• [postgresql] [usecase] Added literal type rendering for the _sqltypes.ARRAY and _postgresql.ARRAY datatypes. The generic stringify will render using brackets, e.g. [1, 2, 3] and the PostgreSQL specific will use the ARRAY literal e.g. ARRAY[1, 2, 3]. Multiple dimensions and quoting are also taken into account.

  References: #8138

• [postgresql] [usecase] Adds support for PostgreSQL multirange types, introduced in PostgreSQL 14. Support for PostgreSQL ranges and multiranges has now been generalized to the psycopg3, psycopg2 and asyncpg backends, with room for further dialect support, using a backend-agnostic _postgresql.Range data object that's constructor-compatible with the previously used psycopg2 object. See the new documentation for usage patterns.

  In addition, range type handling has been enhanced so that it automatically renders type casts, so that in-place round trips for statements that don't provide the database with any context don't require the _sql.cast() construct to be explicit for the database to know the desired type (discussed at #8540).

  Thanks very much to @zeeeeeb for the pull request implementing and testing the new datatypes and psycopg support.

  References: #7156, #8540

• [postgresql] [usecase] The "ping" query emitted when configuring _sa.create_engine.pool_pre_ping for psycopg, asyncpg and pg8000, but not for psycopg2, has been changed to be an empty query (;) instead of SELECT 1; additionally, for the asyncpg driver, the unnecessary use of a prepared statement for this query has been fixed. Rationale is to eliminate the need for PostgreSQL to produce a query plan when the ping is emitted. The operation is not currently supported by the psycopg2 driver which continues to use SELECT 1.

  References: #8491

• [postgresql] [change] SQLAlchemy now requires PostgreSQL version 9 or greater. Older versions may still work in some limited use cases.

• [postgresql] [change] [mssql] The parameter _types.UUID.as_uuid of _types.UUID, previously specific to the PostgreSQL dialect but now generalized for Core (along with a new backend-agnostic _types.Uuid datatype) now defaults to True, indicating that Python UUID objects are accepted by this datatype by default. Additionally, the SQL Server _mssql.UNIQUEIDENTIFIER datatype has been converted to be a UUID-receiving type; for legacy code that makes use of _mssql.UNIQUEIDENTIFIER using string values, set the _mssql.UNIQUEIDENTIFIER.as_uuid parameter to False.

  References: #7225

• [postgresql] [change] The _postgresql.ENUM.name parameter for the PostgreSQL-specific _postgresql.ENUM datatype is now a required keyword argument. The "name" is necessary in any case in order for the _postgresql.ENUM to be usable as an error would be raised at SQL/DDL render time if "name" were not present.

• [postgresql] [change] In support of new PostgreSQL features including the psycopg3 dialect as well as extended "fast insertmany" support, the system by which typing information for bound parameters is passed to the PostgreSQL database has been redesigned to use inline casts emitted by the SQL compiler, and is now applied to all PostgreSQL dialects. This is in contrast to the previous approach which would rely upon the DBAPI in use to render these casts itself, which in cases such as that of pg8000 and the adapted asyncpg driver, would use the pep-249 setinputsizes() method, or with the psycopg2 driver would rely on the driver itself in most cases, with some special exceptions made for ARRAY.

  The new approach now has all PostgreSQL dialects rendering these casts as needed using PostgreSQL double-colon style within the compiler, and the use of setinputsizes() is removed for PostgreSQL dialects, as this was not generally part of these DBAPIs in any case (pg8000 being the only exception, which added the method at the request of SQLAlchemy developers).

  Advantages to this approach include per-statement performance, as no second pass over the compiled statement is required at execution time, better support for all DBAPIs, as there is now one consistent system of applying typing information, and improved transparency, as the SQL logging output, as well as the string output of a compiled statement, will show these casts present in the statement directly, whereas previously these casts were not visible in logging output as they would occur after the statement were logged.

• [postgresql] [bug] The Operators.match() operator now uses plainto_tsquery() for PostgreSQL full text search, rather than to_tsquery(). The rationale for this change is to provide better cross-compatibility with match on other database backends. Full support for all PostgreSQL full text functions remains available through the use of :data:.func in conjunction with Operators.bool_op() (an improved version of Operators.op() for boolean operators).

  Unknown interpreted text role "data".

  References: #7086

• [postgresql] [removed] Removed support for multiple deprecated drivers:

  -   pypostgresql for PostgreSQL. This is available as an
      external driver at [https://github.com/PyGreSQL](https://github.com/PyGreSQL)
  
  -   pygresql for PostgreSQL.
  

  Please switch to one of the supported drivers or to the external version of the same driver.

  References: #7258

• [postgresql] [dialect] Added support for psycopg dialect supporting both sync and async execution. This dialect is available under the postgresql+psycopg name for both the _sa.create_engine() and _asyncio.create_async_engine() engine-creation functions.

  References: #6842

• [postgresql] [psycopg2] Update psycopg2 dialect to use the DBAPI interface to execute two phase transactions. Previously SQL commands were execute to handle this kind of transactions.

  References: #7238

• [postgresql] [schema] Introduced the type _postgresql.JSONPATH that can be used in cast expressions. This is required by some PostgreSQL dialects when using functions such as jsonb_path_exists or jsonb_path_match that accept a jsonpath as input.

  References: #8216

• [postgresql] [reflection] The PostgreSQL dialect now supports reflection of expression based indexes. The reflection is supported both when using _engine.Inspector.get_indexes() and when reflecting a _schema.Table using _schema.Table.autoload_with. Thanks to immerrr and Aidan Kane for the help on this ticket.

  References: #7442

mysql

• [mysql] [usecase] [mariadb] The ROLLUP function will now correctly render WITH ROLLUP on MySql and MariaDB, allowing the use of group by rollup with these backend.

  References: #8503

• [mysql] [bug] Fixed issue in MySQL _mysql.Insert.on_duplicate_key_update() which would render the wrong column name when an expression were used in a VALUES expression. Pull request courtesy Cristian Sabaila.

  This change is also backported to: 1.4.27

  References: #7281

• [mysql] [removed] Removed support for the OurSQL driver for MySQL and MariaDB, as this driver does not seem to be maintained.

  References: #7258

mariadb

• [mariadb] [usecase] Added a new execution option is_delete_using=True, which is consumed by the ORM when using an ORM-enabled DELETE statement in conjunction with the "fetch" synchronization strategy; this option indicates that the DELETE statement is expected to use multiple tables, which on MariaDB is the DELETE..USING syntax. The option then indicates that RETURNING (newly implemented in SQLAlchemy 2.0 for MariaDB for #7011) should not be used for databases that are known to not support "DELETE..USING..RETURNING" syntax, even though they support "DELETE..USING", which is MariaDB's current capability.

  The rationale for this option is that the current workings of ORM-enabled DELETE doesn't know up front if a DELETE statement is against multiple tables or not until compilation occurs, which is cached in any case, yet it needs to be known so that a SELECT for the to-be-deleted row can be emitted up front. Instead of applying an across-the-board performance penalty for all DELETE statements by proactively checking them all for this relatively unusual SQL pattern, the is_delete_using=True execution option is requested via a new exception message that is raised within the compilation step. This exception message is specifically (and only) raised when: the statement is an ORM-enabled DELETE where the "fetch" synchronization strategy has been requested; the backend is MariaDB or other backend with this specific limitation; the statement has been detected within the initial compilation that it would otherwise emit "DELETE..USING..RETURNING". By applying the execution option, the ORM knows to run a SELECT upfront instead. A similar option is implemented for ORM-enabled UPDATE but there is not currently a backend where it is needed.

  References: #8344

• [mariadb] [usecase] Added INSERT..RETURNING and DELETE..RETURNING support for the MariaDB dialect. UPDATE..RETURNING is not yet supported by MariaDB. MariaDB supports INSERT..RETURNING as of 10.5.0 and DELETE..RETURNING as of 10.0.5.

  References: #7011

sqlite

• [sqlite] [usecase] Added new parameter to SQLite for reflection methods called sqlite_include_internal=True; when omitted, local tables that start with the prefix sqlite_, which per SQLite documentation are noted as "internal schema" tables such as the sqlite_sequence table generated to support "AUTOINCREMENT" columns, will not be included in reflection methods that return lists of local objects. This prevents issues for example when using Alembic autogenerate, which previously would consider these SQLite-generated tables as being remove from the model.

  References: #8234

• [sqlite] [usecase] Added RETURNING support for the SQLite dialect. SQLite supports RETURNING since version 3.35.

  References: #6195

• [sqlite] [usecase] The SQLite dialect now supports UPDATE..FROM syntax, for UPDATE statements that may refer to additional tables within the WHERE criteria of the statement without the need to use subqueries. This syntax is invoked automatically when using the _dml.Update construct when more than one table or other entity or selectable is used.

  References: #7185

• [sqlite] [performance] [usecase] SQLite datetime, date, and time datatypes now use Python standard lib fromisoformat() methods in order to parse incoming datetime, date, and time string values. This improves performance vs. the previous regular expression-based approach, and also automatically accommodates for datetime and time formats that contain either a six-digit "microseconds" format or a three-digit "milliseconds" format.

  References: #7029

• [sqlite] [bug] Removed the warning that emits from the _types.Numeric type about DBAPIs not supporting Decimal values natively. This warning was oriented towards SQLite, which does not have any real way without additional extensions or workarounds of handling precision numeric values more than 15 significant digits as it only uses floating point math to represent numbers. As this is a known and documented limitation in SQLite itself, and not a quirk of the pysqlite driver, there's no need for SQLAlchemy to warn for this. The change does not otherwise modify how precision numerics are handled. Values can continue to be handled as Decimal() or float() as configured with the _types.Numeric, _types.Float , and related datatypes, just without the ability to maintain precision beyond 15 significant digits when using SQLite, unless alternate representations such as strings are used.

  References: #7299

• [sqlite] [bug] [performance] The SQLite dialect now defaults to _pool.QueuePool when a file based database is used. This is set along with setting the check_same_thread parameter to False. It has been observed that the previous approach of defaulting to _pool.NullPool, which does not hold onto database connections after they are released, did in fact have a measurable negative performance impact. As always, the pool class is customizable via the _sa.create_engine.poolclass parameter.

  References: #7490

mssql

• [mssql] [usecase] Implemented reflection of the "clustered index" flag mssql_clustered for the SQL Server dialect. Pull request courtesy John Lennox.

  References: #8288

• [mssql] [usecase] Added support table and column comments on MSSQL when creating a table. Added support for reflecting table comments. Thanks to Daniel Hall for the help in this pull request.

  References: #7844

• [mssql] [bug] The use_setinputsizes parameter for the mssql+pyodbc dialect now defaults to True; this is so that non-unicode string comparisons are bound by pyodbc to pyodbc.SQL_VARCHAR rather than pyodbc.SQL_WVARCHAR, allowing indexes against VARCHAR columns to take effect. In order for the fast_executemany=True parameter to continue functioning, the use_setinputsizes mode now skips the cursor.setinputsizes() call specifically when fast_executemany is True and the specific method in use is cursor.executemany(), which doesn't support setinputsizes. The change also adds appropriate pyodbc DBAPI typing to values that are typed as _types.Unicode or _types.UnicodeText, as well as altered the base _types.JSON datatype to consider JSON string values as _types.Unicode rather than _types.String.

  References: #8177

• [mssql] [removed] Removed support for the mxodbc driver due to lack of testing support. ODBC users may use the pyodbc dialect which is fully supported.

  References: #7258

oracle

• [oracle] [feature] Add support for the new oracle driver oracledb.

  References: #8054

• [oracle] [feature] Implemented DDL and reflection support for FLOAT datatypes which include an explicit "binary_precision" value. Using the Oracle-specific _oracle.FLOAT datatype, the new parameter _oracle.FLOAT.binary_precision may be specified which will render Oracle's precision for floating point types directly. This value is interpreted during reflection. Upon reflecting back a FLOAT datatype, the datatype returned is one of _types.DOUBLE_PRECISION for a FLOAT for a precision of 126 (this is also Oracle's default precision for FLOAT), _types.REAL for a precision of 63, and _oracle.FLOAT for a custom precision, as per Oracle documentation.

  As part of this change, the generic _sqltypes.Float.precision value is explicitly rejected when generating DDL for Oracle, as this precision cannot be accurately converted to "binary precision"; instead, an error message encourages the use of _sqltypes.TypeEngine.with_variant() so that Oracle's specific form of precision may be chosen exactly. This is a backwards-incompatible change in behavior, as the previous "precision" value was silently ignored for Oracle.

  References: #5465

• [oracle] [feature] Full "RETURNING" support is implemented for the cx_Oracle dialect, covering two individual types of functionality:

  -   multi-row RETURNING is implemented, meaning multiple RETURNING rows are
      now received for DML statements that produce more than one row for
      RETURNING.
  
  -   "executemany RETURNING" is also implemented - this allows RETURNING to
      yield row-per statement when `cursor.executemany()` is used.
      The implementation of this part of the feature delivers dramatic
      performance improvements to ORM inserts, in the same way as was
      added for psycopg2 in the SQLAlchemy 1.4 change `change_5263`.
  

  References: #6245

• [oracle] [usecase] Oracle will now use FETCH FIRST N ROWS / OFFSET syntax for limit/offset support by default for Oracle 12c and above. This syntax was already available when _sql.Select.fetch() were used directly, it's now implied for _sql.Select.limit() and _sql.Select.offset() as well.

  References: #8221

• [oracle] [change] Materialized views on oracle are now reflected as views. On previous versions of SQLAlchemy the views were returned among the table names, not among the view names. As a side effect of this change they are not reflected by default by _sql.MetaData.reflect(), unless views=True is set. To get a list of materialized views, use the new inspection method Inspector.get_materialized_view_names().

• [oracle] [bug] Adjustments made to the BLOB / CLOB / NCLOB datatypes in the cx_Oracle and oracledb dialects, to improve performance based on recommendations from Oracle developers.

  References: #7494

• [oracle] [bug] Related to the deprecation for _sa.create_engine.implicit_returning, the "implicit_returning" feature is now enabled for the Oracle dialect in all cases; previously, the feature would be turned off when an Oracle 8/8i version were detected, however online documentation indicates both versions support the same RETURNING syntax as modern versions.

  References: #6962

• [oracle] cx_Oracle 7 is now the minimum version for cx_Oracle.

misc

• [feature] [types] Added new backend-agnostic _types.Uuid datatype generalized from the PostgreSQL dialects to now be a core type, as well as migrated _types.UUID from the PostgreSQL dialect. The SQL Server _mssql.UNIQUEIDENTIFIER datatype also becomes a UUID-handling datatype. Thanks to Trevor Gross for the help on this.

  References: #7212

• [feature] [types] Added Double, DOUBLE, DOUBLE_PRECISION datatypes to the base sqlalchemy. module namespace, for explicit use of double/double precision as well as generic "double" datatypes. Use Double for generic support that will resolve to DOUBLE/DOUBLE PRECISION/FLOAT as needed for different backends.

  References: #5465

• [usecase] [datatypes] Added modified ISO-8601 rendering (i.e. ISO-8601 with the T converted to a space) when using literal_binds with the SQL compilers provided by the PostgreSQL, MySQL, MariaDB, MSSQL, Oracle dialects. For Oracle, the ISO format is wrapped inside of an appropriate TO_DATE() function call. Previously this rendering was not implemented for dialect-specific compilation.

  References: #5052

• [bug] [pool] The _pool.QueuePool now ignores max_overflow when pool_size=0, properly making the pool unlimited in all cases.

  References: #8523

• [bug] [types] Python string values for which a SQL type is determined from the type of the value, mainly when using _sql.literal(), will now apply the _types.String type, rather than the _types.Unicode datatype, for Python string values that test as "ascii only" using Python str.isascii(). If the string is not isascii(), the _types.Unicode datatype will be bound instead, which was used in all string detection previously. This behavior only applies to in-place detection of datatypes when using literal() or other contexts that have no existing datatype, which is not usually the case under normal _schema.Column comparison operations, where the type of the _schema.Column being compared always takes precedence.

  Use of the _types.Unicode datatype can determine literal string formatting on backends such as SQL Server, where a literal value (i.e. using literal_binds) will be rendered as N'<value>' instead of 'value'. For normal bound value handling, the _types.Unicode datatype also may have implications for passing values to the DBAPI, again in the case of SQL Server, the pyodbc driver supports the use of setinputsizes mode <mssql_pyodbc_setinputsizes> which will handle _types.String versus _types.Unicode differently.

  References: #7551

• [removed] [sybase] Removed the "sybase" internal dialect that was deprecated in previous SQLAlchemy versions. Third party dialect support is available.

  References: #7258

• [removed] [firebird] Removed the "firebird" internal dialect that was deprecated in previous SQLAlchemy versions. Third party dialect support is available.

  References: #7258