2.0 Changelog


no release date


  • [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 and 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; Row is fully a named tuple without “mapping” behavior, use 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 registry.map_imperatively() method of registry.

    • The Query.join() method no longer accepts strings for relationship names; the long-documented approach of using Class.attrname for join targets is now standard.

    • Query.join() no longer accepts the “aliased” and “from_joinpoint” arguments

    • 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 relationship.cascade_backrefs parameter must now remain at its new default of False; the save-update cascade no longer cascades along a backref.

    • the Session.future parameter must always be set to True. 2.0-style transactional patterns for 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 select() removed, including select([cols]), the “whereclause” and keyword parameters of some_table.select().

    • Legacy “in-place mutator” methods on 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


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

    Added new parameter Operators.op.python_impl, available from Operators.op() and also when using the 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 UPDATE and DELETE with arbitrary WHERE clause.

    References: #3162

  • [orm] [feature]

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

  • [orm] [feature]

    The 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 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 Session being in an invalid state.

    References: #7433

  • [orm] [feature]

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

    See the new documentation at Composite Column Types for examples.

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

    All Result objects will now consistently raise 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.first() and 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 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.close() to the base Result class and implemented it for the filtered result implementations that are used by the ORM, so that it is possible to call the CursorResult.close() method on the underlying 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 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 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 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] [asyncio]

    Removed the unused **kw arguments from begin and begin_nested. These kw aren’t used and appear to have been added to the API in error.

    References: #7703

  • [orm] [bug]

    Fixed issue where the registry.map_declaratively() method would return an internal “mapper config” object and not the Mapper object as stated in the API documentation.


  • [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 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 create_engine.isolation_level parameter is essentially equivalent in functionality to using the Engine.execution_options.isolation_level parameter via 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:

    References: #7122

  • [engine] [bug] [regression]

    Fixed regression where the 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 where calling upon 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]

    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]

    The 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 Inspector.has_table() method searches for views as well as tables for the given name.

    References: #7161

  • [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 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 create_engine.implicit_returning parameter is deprecated on the create_engine() function only; the parameter remains available on the 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 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] [usecase]

    Altered the compilation mechanics of the 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 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 Insert.values(). In 1.4, the parameter set behavior unintentionally changed to no longer do this, but the 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]

    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 existing def visit_concat_op_binary() method.

    References: #7744

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

    Implemented full support for “truediv” and “floordiv” using the “/” and “//” operators. A “truediv” operation between two expressions using Integer now considers the result to be 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


  • [schema] [feature]

    Expanded on the “conditional DDL” system implemented by the ExecutableDDLElement class (renamed from DDLElement) to be directly available on SchemaItem constructs such as Index, 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



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

    The parameter UUID.as_uuid of UUID now defaults to True.

    References: #7225

  • [postgresql] [removed]

    Removed support for multiple deprecated drivers:

    - pypostgresql for PostgreSQL. This is available as an
      external driver at 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] [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] [dialect]

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

    References: #6842


  • [mysql] [bug]

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


  • [sqlite] [usecase] [performance]

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

    The SQLite dialect now defaults to 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 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 create_engine.poolclass parameter.

    References: #7490

  • [sqlite] [bug]

    Removed the warning that emits from the 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 Numeric, 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


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

    Implemented DDL and reflection support for FLOAT datatypes which include an explicit “binary_precision” value. Using the Oracle-specific FLOAT datatype, the new parameter 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 DOUBLE_PRECISION for a FLOAT for a precision of 126 (this is also Oracle’s default precision for FLOAT), REAL for a precision of 63, and FLOAT for a custom precision, as per Oracle documentation.

    As part of this change, the generic 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 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, meaning multiple RETURNING rows are now recived for DML statements that produce more than one row for RETURNING.

    References: #6245

  • [oracle] [bug]

    Related to the deprecation for 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.


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

    Python string values for which a SQL type is determined from the type of the value, mainly when using literal(), will now apply the String type, rather than the Unicode datatype, for Python string values that test as “ascii only” using Python str.isascii(). If the string is not isascii(), the 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 Column comparison operations, where the type of the Column being compared always takes precedence.

    Use of the 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 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 which will handle String versus Unicode differently.

    References: #7551

  • [removed] [firebird]

    Removed the “firebird” internal dialect that was deprecated in previous SQLAlchemy versions. Third party dialect support is available.

    References: #7258

  • [removed] [sybase]

    Removed the “sybase” internal dialect that was deprecated in previous SQLAlchemy versions. Third party dialect support is available.

    References: #7258