SQLAlchemy 2.1 Documentation
Changes and Migration
- What’s New in SQLAlchemy 2.1?
- 2.1 Changelog¶
- 2.0 Changelog
- 1.4 Changelog
- 1.3 Changelog
- 1.2 Changelog
- 1.1 Changelog
- 1.0 Changelog
- 0.9 Changelog
- 0.8 Changelog
- 0.7 Changelog
- 0.6 Changelog
- 0.5 Changelog
- 0.4 Changelog
- 0.3 Changelog
- 0.2 Changelog
- 0.1 Changelog
- SQLAlchemy 2.0 - Major Migration Guide
- What’s New in SQLAlchemy 2.0?
- What’s New in SQLAlchemy 1.4?
- What’s New in SQLAlchemy 1.3?
- What’s New in SQLAlchemy 1.2?
- What’s New in SQLAlchemy 1.1?
- What’s New in SQLAlchemy 1.0?
- What’s New in SQLAlchemy 0.9?
- What’s New in SQLAlchemy 0.8?
- What’s New in SQLAlchemy 0.7?
- What’s New in SQLAlchemy 0.6?
- What’s new in SQLAlchemy 0.5?
- What’s new in SQLAlchemy 0.4?
Project Versions
- Previous: What’s New in SQLAlchemy 2.1?
- Next: 2.0 Changelog
- Up: Home
- On this page:
2.1 Changelog¶
2.1.0b1¶
no release dateorm¶
[orm] [feature] ¶
The
relationship.back_populates
argument torelationship()
may now be passed as a Python callable, which resolves to either the direct linked ORM attribute, or a string value as before. ORM attributes are also accepted directly byrelationship.back_populates
. This change allows type checkers and IDEs to confirm the argument forrelationship.back_populates
is valid. Thanks to Priyanshu Parikh for the help on suggesting and helping to implement this feature.References: #10050
[orm] [usecase] ¶
The
Session.flush.objects
parameter is now deprecated.References: #10816
[orm] [usecase] ¶
Added the utility method
Session.merge_all()
andSession.delete_all()
that operate on a collection of instances.References: #11776
[orm] [change] ¶
The
first_init
ORM event has been removed. This event was non-functional throughout the 1.4 and 2.0 series and could not be invoked without raising an internal error, so it is not expected that there is any real-world use of this event hook.References: #10500
[orm] [change] ¶
Removed legacy signatures dating back to 0.9 release from the
SessionEvents.after_bulk_update()
andSessionEvents.after_bulk_delete()
.References: #10721
[orm] [change] ¶
A sweep through class and function names in the ORM renames many classes and functions that have no intent of public visibility to be underscored. This is to reduce ambiguity as to which APIs are intended to be targeted by third party applications and extensions. Third parties are encouraged to propose new public APIs in Discussions to the extent they are needed to replace those that have been clarified as private.
References: #10497
[orm] [changed] ¶
The “non primary” mapper feature, long deprecated in SQLAlchemy since version 1.3, has been removed. The sole use case for “non primary” mappers was that of using
relationship()
to link to a mapped class against an alternative selectable; this use case is now suited by the relationship_aliased_class feature.References: #12437
[orm] [bug] ¶
A significant behavioral change has been made to the behavior of the
mapped_column.default
andrelationship.default
parameters, when used with SQLAlchemy’s Declarative Dataclass Mapping feature introduced in 2.0, where the given value (assumed to be an immutable scalar value) is no longer passed to the@dataclass
API as a real default, instead a token that leaves the value un-set in the object’s__dict__
is used, in conjunction with a descriptor-level default. This prevents an un-set default value from overriding a default that was actually set elsewhere, such as in relationship / foreign key assignment patterns as well as inSession.merge()
scenarios. See the full writeup in the What’s New in SQLAlchemy 2.1? document which includes guidance on how to re-enable the 2.0 version of the behavior if needed.References: #12168
[orm] [bug] ¶
The
relationship.secondary
parameter no longer uses Pythoneval()
to evaluate the given string. This parameter when passed a string should resolve to a table name that’s present in the localMetaData
collection only, and never needs to be any kind of Python expression otherwise. To use a real deferred callable based on a name that may not be locally present yet, use a lambda instead.References: #10564
[orm] [bug] ¶
Revised the set “binary” operators for the association proxy
set()
interface to correctly raiseTypeError
for invalid use of the|
,&
,^
, and-
operators, as well as the in-place mutation versions of these methods, to match the behavior of standard Pythonset()
as well as SQLAlchemy ORM’s “intstrumented” set implementation.References: #11349
[orm] [bug] ¶
The behavior of
with_polymorphic()
when used with a single inheritance mapping has been changed such that its behavior should match as closely as possible to that of an equivalent joined inheritance mapping. Specifically this means that the base class specified in thewith_polymorphic()
construct will be the basemost class that is loaded, as well as all descendant classes of that basemost class. The change includes that the descendant classes named will no longer be exclusively indicated in “WHERE polymorphic_col IN” criteria; instead, the whole hierarchy starting with the given basemost class will be loaded. If the query indicates that rows should only be instances of a specific subclass within the polymorphic hierarchy, an error is raised if an incompatible superclass is loaded in the result since it cannot be made to match the requested class; this behavior is the same as what joined inheritance has done for many years. The change also allows a single result set to include column-level results from multiple sibling classes at once which was not previously possible with single table inheritance.References: #12395
[orm] ¶
The
noload()
relationship loader option and relatedlazy='noload'
setting is deprecated and will be removed in a future release. This option was originally intended for custom loader patterns that are no longer applicable in modern SQLAlchemy.References: #11045
[orm] ¶
Ignore
Session.join_transaction_mode
in all cases when the bind provided to theSession
is anEngine
. Previously if an event that executed before the session logic, likeConnectionEvents.engine_connect()
, left the connection with an active transaction, theSession.join_transaction_mode
behavior took place, leading to a surprising behavior.References: #11163
engine¶
[engine] [usecase] ¶
Added new execution option
Connection.execution_options.driver_column_names
. This option disables the “name normalize” step that takes place against the DBAPIcursor.description
for uppercase-default backends like Oracle, and will cause the keys of a result set (e.g. named tuple names, dictionary keys inRow._mapping
, etc.) to be exactly what was delivered in cursor.description. This is mostly useful for plain textual statements usingtext()
orConnection.exec_driver_sql()
.References: #10789
[engine] [change] ¶
An empty sequence passed to any
execute()
method now raised a deprecation warning, since such an executemany is invalid. Pull request courtesy of Carlos Sousa.References: #9647
[engine] [bug] ¶
Adjusted URL parsing and stringification to apply url quoting to the “database” portion of the URL. This allows a URL where the “database” portion includes special characters such as question marks to be accommodated.
References: #11234
sql¶
[sql] [feature] ¶
Added the ability to create custom SQL constructs that can define new clauses within SELECT, INSERT, UPDATE, and DELETE statements without needing to modify the construction or compilation code of of
Select
,Insert
,Update
, orDelete
directly. Support for testing these constructs, including caching support, is present along with an example test suite. The use case for these constructs is expected to be third party dialects for analytical SQL (so-called NewSQL) or other novel styles of database that introduce new clauses to these statements. A new example suite is included which illustrates theQUALIFY
SQL construct used by several NewSQL databases which includes a cachable implementation as well as a test suite.References: #12195
[sql] [usecase] ¶
Added support for the pow operator (
**
), with a default SQL implementation of thePOW()
function. On Oracle Database, PostgreSQL and MSSQL it renders asPOWER()
. As part of this change, the operator routes through a new first classfunc
memberpow
, which renders on Oracle Database, PostgreSQL and MSSQL asPOWER()
.References: #8579
[sql] [change] ¶
the
Numeric
andFloat
SQL types have been separated out so thatFloat
no longer inherits fromNumeric
; instead, they both extend from a common mixinNumericCommon
. This corrects for some architectural shortcomings where numeric and float types are typically separate, and establishes more consistency withInteger
also being a distinct type. The change should not have any end-user implications except for code that may be usingisinstance()
to test for theNumeric
datatype; third party dialects which rely upon specific implementation types for numeric and/or float may also require adjustment to maintain compatibility.References: #5252
[sql] [change] ¶
The
.c
and.columns
attributes on theSelect
andTextualSelect
constructs, which are not instances ofFromClause
, have been removed completely, in addition to the.select()
method as well as other codepaths which would implicitly generate a subquery from aSelect
without the need to explicitly call theSelect.subquery()
method.In the case of
.c
and.columns
, these attributes were never useful in practice and have caused a great deal of confusion, hence were deprecated back in version 1.4, and have emitted warnings since that version. Accessing the columns that are specific to aSelect
construct is done via theSelect.selected_columns
attribute, which was added in version 1.4 to suit the use case that users often expected.c
to accomplish. In the larger sense, implicit production of subqueries works against SQLAlchemy’s modern practice of making SQL structure as explicit as possible.Note that this is not related to the usual
FromClause.c
andFromClause.columns
attributes, common to objects such asTable
andSubquery
, which are unaffected by this change.See also
A SELECT statement is no longer implicitly considered to be a FROM clause - original notes from SQLAlchemy 1.4
References: #10236
[sql] [bug] ¶
Fixed issue in name normalization (e.g. “uppercase” backends like Oracle) where using a
TextualSelect
would not properly maintain as uppercase column names that were quoted as uppercase, even though theTextualSelect
includes aColumn
that explicitly holds this uppercase name.References: #10788
[sql] [bug] ¶
Enhanced the caching structure of the
over.rows
andover.range
so that different numerical values for the rows / range fields are cached on the same cache key, to the extent that the underlying SQL does not actually change (i.e. “unbounded”, “current row”, negative/positive status will still change the cache key). This prevents the use of many different numerical range/rows value for a query that is otherwise identical from filling up the SQL cache.Note that the semi-private compiler method
_format_frame_clause()
is removed by this fix, replaced with a new methodvisit_frame_clause()
. Third party dialects which may have referred to this method will need to change the name and revise the approach to rendering the correct SQL for that dialect.References: #11515
[sql] ¶
Removed the automatic coercion of executable objects, such as
Query
, when passed intoSession.execute()
. This usage raised a deprecation warning since the 1.4 series.References: #12218
schema¶
[schema] [bug] ¶
The
Float
andNumeric
types are no longer automatically considered as auto-incrementing columns when theColumn.autoincrement
parameter is left at its default of"auto"
on aColumn
that is part of the primary key. When the parameter is set toTrue
, aNumeric
type will be accepted as an auto-incrementing datatype for primary key columns, but only if its scale is explicitly given as zero; otherwise, an error is raised. This is a change from 2.0 where all numeric types including floats were automatically considered as “autoincrement” for primary key columns.References: #11811
[schema] ¶
Deprecate Oracle only parameters
Sequence.order
,Identity.order
andIdentity.on_null
. They should be configured using the dialect kwargsoracle_order
andoracle_on_null
.References: #10247
typing¶
[typing] [feature] ¶
The
Row
object now no longer makes use of an intermediaryTuple
in order to represent its individual element types; instead, the individual element types are present directly, via new PEP 646 integration, now available in more recent versions of Mypy. Mypy 1.7 or greater is now required for statements, results and rows to be correctly typed. Pull request courtesy Yurii Karabas.References: #10635
[typing] ¶
The default implementation of
TypeEngine.python_type
now returnsobject
instead ofNotImplementedError
, since that’s the base for all types in Python3. Thepython_type
ofJSON
no longer returnsdict
, but instead fallbacks to the generic implementation.References: #10646
[typing] [orm] ¶
Removed the deprecated mypy plugin. The plugin was non-functional with newer version of mypy and it’s no longer needed with modern SQLAlchemy declarative style.
References: #12293
asyncio¶
[asyncio] [change] ¶
Adapted all asyncio dialects, including aiosqlite, aiomysql, asyncmy, psycopg, asyncpg to use the generic asyncio connection adapter first added in #6521 for the aioodbc DBAPI, allowing these dialects to take advantage of a common framework.
References: #10415
[asyncio] [change] ¶
Added an initialize step to the import of
sqlalchemy.ext.asyncio
so thatgreenlet
will be imported only when the asyncio extension is first imported. Alternatively, thegreenlet
library is still imported lazily on first use to support use case that don’t make direct use of the SQLAlchemy asyncio extension.References: #10296
[asyncio] [change] ¶
Removed the compatibility
async_fallback
mode for async dialects, since it’s no longer used by SQLAlchemy tests. Also removed the internal functionawait_fallback()
and renamed the internal functionawait_only()
toawait_()
. No change is expected to user code.
postgresql¶
[postgresql] [feature] ¶
Added syntax extension
distinct_on()
to buildDISTINCT ON
clauses. The old api, that passed columns toSelect.distinct()
, is now deprecated.References: #12342
mysql¶
mariadb¶
[mariadb] [usecase] ¶
Modified the MariaDB dialect so that when using the
Uuid
datatype with MariaDB >= 10.7, leaving theUuid.native_uuid
parameter at its default of True, the nativeUUID
datatype will be rendered in DDL and used for database communication, rather thanCHAR(32)
(the non-native UUID type) as was the case previously. This is a behavioral change since 2.0, where the genericUuid
datatype deliveredCHAR(32)
for all MySQL and MariaDB variants. Support for all major DBAPIs is implemented including support for less common “insertmanyvalues” scenarios where UUID values are generated in different ways for primary keys. Thanks much to Volodymyr Kochetkov for delivering the PR.References: #10339
mssql¶
[mssql] [bug] ¶
Fix mssql+pyodbc issue where valid plus signs in an already-unquoted
odbc_connect=
(raw DBAPI) connection string are replaced with spaces.The pyodbc connector would unconditionally pass the odbc_connect value to unquote_plus(), even if it was not required. So, if the (unquoted) odbc_connect value contained
PWD=pass+word
that would get changed toPWD=pass word
, and the login would fail. One workaround was to quote just the plus sign —PWD=pass%2Bword
— which would then get unquoted toPWD=pass+word
.References: #11250
misc¶
[change] [installation] ¶
Python 3.9 or above is now required; support for Python 3.8 and 3.7 is dropped as these versions are EOL.
[change] [installation] ¶
The
greenlet
dependency used for asyncio support no longer installs by default. This dependency does not publish wheel files for every architecture and is not needed for applications that aren’t using asyncio features. Use thesqlalchemy[asyncio]
install target to include this dependency.References: #10197
[change] [setup] ¶
Updated the setup manifest definition to use PEP 621-compliant pyproject.toml. Also updated the extra install dependency to comply with PEP-685. Thanks for the help of Matt Oberle and KOLANICH on this change.
[misc] [changed] ¶
Removed multiple api that were deprecated in the 1.3 series and earlier. The list of removed features includes:
The
force
parameter ofIdentifierPreparer.quote
andIdentifierPreparer.quote_schema
;The
threaded
parameter of the cx-Oracle dialect;The
_json_serializer
and_json_deserializer
parameters of the SQLite dialect;The
collection.converter
decorator;The
Mapper.mapped_table
property;The
Session.close_all
method;
References: #12441
flambé! the dragon and The Alchemist image designs created and generously donated by Rotem Yaari.
Created using Sphinx 7.2.6. Documentation last generated: Sun 27 Apr 2025 04:02:25 PM EDT