2.0 Changelog

2.0.1

no release date

orm

• [orm] [bug] [regression]

  Fixed regression where ORM models that used joined table inheritance with a composite foreign key would encounter an internal error in the mapper internals.

  References: #9164

• [orm] [bug]

  Improved the error reporting when linking strategy options from a base class to another attribute that’s off a subclass, where of_type() should be used. Previously, when Load.options() is used, the message would lack informative detail that of_type() should be used, which was not the case when linking the options directly. The informative detail now emits even if Load.options() is used.

  References: #9182

sql

• [sql] [bug] [regression]

  Fixed regression related to the implementation for the new “insertmanyvalues” feature where an internal TypeError would occur in arrangements where a insert() would be referred towards inside of another insert() via a CTE; made additional repairs for this use case for positional dialects such as asyncpg when using “insertmanyvalues”.

  References: #9173

• [sql] [bug]

  Corrected the fix for #7664, released in version 2.0.0, to also include DropSchema which was inadvertently missed in this fix, allowing stringification without a dialect. The fixes for both constructs is backported to the 1.4 series as of 1.4.47.

  References: #7664

typing

• [typing] [bug]

  Fixed typing for ColumnElement.cast() to accept both Type[TypeEngine[T]] and TypeEngine[T]; previously only TypeEngine[T] was accepted. Pull request courtesy Yurii Karabas.

  References: #9156

• [typing] [bug]

  Opened up typing on with_for_update.of() to also accept table and mapped class arguments, as seems to be available for the MySQL dialect.

  References: #9174

• [typing] [bug]

  Fixed typing issue where mapped_column() objects typed as Mapped wouldn’t be accepted in schema constraints such as ForeignKey, UniqueConstraint or Index.

  References: #9170

2.0.0

Released: January 26, 2023

orm

• [orm] [bug]

  Improved the notification of warnings that are emitted within the configure mappers or flush process, which are often invoked as part of a different operation, to add additional context to the message that indicates one of these operations as the source of the warning within operations that may not be obviously related.

  References: #7305

orm extensions

• [feature] [orm extensions]

  Added new option to horizontal sharding API set_shard_id which sets the effective shard identifier to query against, for both the primary query as well as for all secondary loaders including relationship eager loaders as well as relationship and column lazy loaders.

  References: #7226

• [usecase] [orm extensions]

  Added new feature to AutomapBase for autoload of classes across multiple schemas which may have overlapping names, by providing a AutomapBase.prepare.modulename_for_table parameter which allows customization of the __module__ attribute of newly generated classes, as well as a new collection AutomapBase.by_module, which stores a dot-separated namespace of module names linked to classes based on the __module__ attribute.

  Additionally, the AutomapBase.prepare() method may now be invoked any number of times, with or without reflection enabled; only newly added tables that were not previously mapped will be processed on each call. Previously, the MetaData.reflect() method would need to be called explicitly each time.

  See also

  Generating Mappings from Multiple Schemas - illustrates use of both techniques at once.

  References: #5145

sql

• [sql] [bug]

  Fixed stringify for a the CreateSchema DDL construct, which would fail with an AttributeError when stringified without a dialect. Update: Note this fix failed to accommodate for DropSchema; a followup fix in version 2.0.1 repairs this case. The fix for both elements is backported to 1.4.47.

  References: #7664

typing

• [typing] [bug]

  Added typing for the built-in generic functions that are available from the func namespace, which accept a particular set of arguments and return a particular type, such as for count, current_timestamp, etc.

  References: #9129

• [typing] [bug]

  Corrected the type passed for “lambda statements” so that a plain lambda is accepted by mypy, pyright, others without any errors about argument types. Additionally implemented typing for more of the public API for lambda statements and ensured StatementLambdaElement is part of the Executable hierarchy so it’s typed as accepted by Connection.execute().

  References: #9120

• [typing] [bug]

  The ColumnOperators.in_() and ColumnOperators.not_in() methods are typed to include Iterable[Any] rather than Sequence[Any] for more flexibility in argument type.

  References: #9122

• [typing] [bug]

  The or_() and and_() from a typing perspective require the first argument to be present, however these functions still accept zero arguments which will emit a deprecation warning at runtime. Typing is also added to support sending the fixed literal False for or_() and True for and_() as the first argument only, however the documentation now indicates sending the false() and true() constructs in these cases as a more explicit approach.

  References: #9123

• [typing] [bug]

  Fixed typing issue where iterating over a Query object was not correctly typed.

  References: #9125

• [typing] [bug]

  Fixed typing issue where the object type when using Result as a context manager were not preserved, indicating Result in all cases rather than the specific Result sub-type. Pull request courtesy Martin Baláž.

  References: #9136

• [typing] [bug]

  Fixed issue where using the relationship.remote_side and similar parameters, passing an annotated declarative object typed as Mapped, would not be accepted by the type checker.

  References: #9150

• [typing] [bug]

  Added typing to legacy operators such as isnot(), notin_(), etc. which previously were referencing the newer operators but were not themselves typed.

  References: #9148

mssql

• [mssql] [bug]

  Fixed bug where a schema name given with brackets, but no dots inside the name, for parameters such as Table.schema would not be interpreted within the context of the SQL Server dialect’s documented behavior of interpreting explicit brackets as token delimiters, first added in 1.2 for #2626, when referring to the schema name in reflection operations. The original assumption for #2626’s behavior was that the special interpretation of brackets was only significant if dots were present, however in practice, the brackets are not included as part of the identifier name for all SQL rendering operations since these are not valid characters within regular or delimited identifiers. Pull request courtesy Shan.

  This change is also backported to: 1.4.47

  References: #9133

• [mssql] [bug] [regression]

  The newly added comment reflection and rendering capability of the MSSQL dialect, added in #7844, will now be disabled by default if it cannot be determined that an unsupported backend such as Azure Synapse may be in use; this backend does not support table and column comments and does not support the SQL Server routines in use to generate them as well as to reflect them. A new parameter supports_comments is added to the dialect which defaults to None, indicating that comment support should be auto-detected. When set to True or False, the comment support is either enabled or disabled unconditionally.

  See also

  DDL Comment Support

  References: #9142

oracle

• [oracle] [bug]

  Added ROWID to reflected types as this type may be used in a “CREATE TABLE” statement.

  This change is also backported to: 1.4.47

  References: #5047

2.0.0rc3

Released: January 18, 2023

orm

• [orm] [feature]

  Added a new parameter to Mapper called Mapper.polymorphic_abstract. The purpose of this directive is so that the ORM will not consider the class to be instantiated or loaded directly, only subclasses. The actual effect is that the Mapper will prevent direct instantiation of instances of the class and will expect that the class does not have a distinct polymorphic identity configured.

  In practice, the class that is mapped with Mapper.polymorphic_abstract can be used as the target of a relationship() as well as be used in queries; subclasses must of course include polymorphic identities in their mappings.

  The new parameter is automatically applied to classes that subclass the AbstractConcreteBase class, as this class is not intended to be instantiated.

  See also

  Building Deeper Hierarchies with polymorphic_abstract

  References: #9060

• [orm] [bug]

  Fixed issue where using a pep-593 Annotated type in the registry.type_annotation_map which itself contained a generic plain container or collections.abc type (e.g. list, dict, collections.abc.Sequence, etc. ) as the target type would produce an internal error when the ORM were trying to interpret the Annotated instance.

  References: #9099

• [orm] [bug]

  Added an error message when a relationship() is mapped against an abstract container type, such as Mapped[Sequence[B]], without providing the relationship.container_class parameter which is necessary when the type is abstract. Previously the the abstract container would attempt to be instantiated at a later step and fail.

  References: #9100

sql

• [sql] [bug]

  Fixed bug / regression where using bindparam() with the same name as a column in the Update.values() method of Update, as well as the Insert.values() method of Insert in 2.0 only, would in some cases silently fail to honor the SQL expression in which the parameter were presented, replacing the expression with a new parameter of the same name and discarding any other elements of the SQL expression, such as SQL functions, etc. The specific case would be statements that were constructed against ORM entities rather than plain Table instances, but would occur if the statement were invoked with a Session or a Connection.

  Update part of the issue was present in both 2.0 and 1.4 and is backported to 1.4.

  This change is also backported to: 1.4.47

  References: #9075

typing

• [typing] [bug]

  Fixes to the annotations within the sqlalchemy.ext.hybrid extension for more effective typing of user-defined methods. The typing now uses PEP 612 features, now supported by recent versions of Mypy, to maintain argument signatures for hybrid_method. Return values for hybrid methods are accepted as SQL expressions in contexts such as Select.where() while still supporting SQL methods.

  References: #9096

mypy

• [mypy] [bug]

  Adjustments made to the mypy plugin to accommodate for some potential changes being made for issue #236 sqlalchemy2-stubs when using SQLAlchemy 1.4. These changes are being kept in sync within SQLAlchemy 2.0. The changes are also backwards compatible with older versions of sqlalchemy2-stubs.

  This change is also backported to: 1.4.47

• [mypy] [bug]

  Fixed crash in mypy plugin which could occur on both 1.4 and 2.0 versions if a decorator for the mapped() decorator were used that was referenced in an expression with more than two components (e.g. @Backend.mapper_registry.mapped). This scenario is now ignored; when using the plugin, the decorator expression needs to be two components (i.e. @reg.mapped).

  This change is also backported to: 1.4.47

  References: #9102

postgresql

• [postgresql] [bug]

  Fixed regression where psycopg3 changed an API call as of version 3.1.8 to expect a specific object type that was previously not enforced, breaking connectivity for the psycopg3 dialect.

  References: #9106

oracle

• [oracle] [usecase]

  Added support for the Oracle SQL type TIMESTAMP WITH LOCAL TIME ZONE, using a newly added Oracle-specific TIMESTAMP datatype.

  References: #9086

2.0.0rc2

Released: January 9, 2023

orm

• [orm] [bug]

  Fixed issue where an overly restrictive ORM mapping rule were added in 2.0 which prevented mappings against TableClause objects, such as those used in the view recipe on the wiki.

  References: #9071

typing

• [typing] [bug]

  The Data Class Transforms argument field_descriptors was renamed to field_specifiers in the accepted version of PEP 681.

  References: #9067

postgresql

• [postgresql] [bug]

  Added support to the asyncpg dialect to return the cursor.rowcount value for SELECT statements when available. While this is not a typical use for cursor.rowcount, the other PostgreSQL dialects generally provide this value. Pull request courtesy Michael Gorven.

  This change is also backported to: 1.4.47

  References: #9048

• [postgresql] [json]

  Implemented missing JSONB operations:

  • @@ using Comparator.path_match()

  • @? using Comparator.path_exists()

  • #- using Comparator.delete_path()

  Pull request curtesy of Guilherme Martins Crocetti.

  References: #7147

mysql

• [mysql] [usecase]

  Added support to MySQL index reflection to correctly reflect the mysql_length dictionary, which previously was being ignored.

  This change is also backported to: 1.4.47

  References: #9047

• [mysql] [bug]

  Restored the behavior of Inspector.has_table() to report on temporary tables for MySQL / MariaDB. This is currently the behavior for all other included dialects, but was removed for MySQL in 1.4 due to no longer using the DESCRIBE command; there was no documented support for temp tables being reported by the Inspector.has_table() method in this version or on any previous version, so the previous behavior was undefined.

  As SQLAlchemy 2.0 has added formal support for temp table status via Inspector.has_table(), the MySQL /MariaDB dialect has been reverted to use the “DESCRIBE” statement as it did in the SQLAlchemy 1.3 series and previously, and test support is added to include MySQL / MariaDB for this behavior. The previous issues with ROLLBACK being emitted which 1.4 sought to improve upon don’t apply in SQLAlchemy 2.0 due to simplifications in how Connection handles transactions.

  DESCRIBE is necessary as MariaDB in particular has no consistently available public information schema of any kind in order to report on temp tables other than DESCRIBE/SHOW COLUMNS, which rely on throwing an error in order to report no results.

  References: #9058

oracle

• [oracle] [bug]

  Supported use case for foreign key constraints where the local column is marked as “invisible”. The errors normally generated when a ForeignKeyConstraint is created that check for the target column are disabled when reflecting, and the constraint is skipped with a warning in the same way which already occurs for an Index with a similar issue.

  References: #9059

2.0.0rc1

Released: December 28, 2022

general

• [general] [bug]

  Fixed regression where the base compat module was calling upon platform.architecture() in order to detect some system properties, which results in an over-broad system call against the system-level file call that is unavailable under some circumstances, including within some secure environment configurations.

  This change is also backported to: 1.4.46

  References: #8995

orm

• [orm] [feature]

  Added a new default value for the Mapper.eager_defaults parameter “auto”, which will automatically fetch table default values during a unit of work flush, if the dialect supports RETURNING for the INSERT being run, as well as insertmanyvalues available. Eager fetches for server-side UPDATE defaults, which are very uncommon, continue to only take place if Mapper.eager_defaults is set to True, as there is no batch-RETURNING form for UPDATE statements.

  References: #8889

• [orm] [usecase]

  Adjustments to the Session in terms of extensibility, as well as updates to the ShardedSession extension:

  • Session.get() now accepts Session.get.bind_arguments, which in particular may be useful when using the horizontal sharding extension.

  • Session.get_bind() accepts arbitrary kw arguments, which assists in developing code that uses a Session class which overrides this method with additional arguments.

  • Added a new ORM execution option identity_token which may be used to directly affect the “identity token” that will be associated with newly loaded ORM objects. This token is how sharding approaches (namely the ShardedSession, but can be used in other cases as well) separate object identities across different “shards”.

    See also

    Identity Token

  • The SessionEvents.do_orm_execute() event hook may now be used to affect all ORM-related options, including autoflush, populate_existing, and yield_per; these options are re-consumed subsequent to event hooks being invoked before they are acted upon. Previously, options like autoflush would have been already evaluated at this point. The new identity_token option is also supported in this mode and is now used by the horizontal sharding extension.

  • The ShardedSession class replaces the ShardedSession.id_chooser hook with a new hook ShardedSession.identity_chooser, which no longer relies upon the legacy Query object. ShardedSession.id_chooser is still accepted in place of ShardedSession.identity_chooser with a deprecation warning.

  References: #7837

• [orm] [usecase]

  The behavior of “joining an external transaction into a Session” has been revised and improved, allowing explicit control over how the Session will accommodate an incoming Connection that already has a transaction and possibly a savepoint already established. The new parameter Session.join_transaction_mode includes a series of option values which can accommodate the existing transaction in several ways, most importantly allowing a Session to operate in a fully transactional style using savepoints exclusively, while leaving the externally initiated transaction non-committed and active under all circumstances, allowing test suites to rollback all changes that take place within tests.

  Additionally, revised the Session.close() method to fully close out savepoints that may still be present, which also allows the “external transaction” recipe to proceed without warnings if the Session did not explicitly end its own SAVEPOINT transactions.

  See also

  New transaction join modes for Session

  References: #9015

• [orm] [usecase]

  Removed the requirement that the __allow_unmapped__ attribute be used on Declarative Dataclass Mapped class when non-Mapped[] annotations are detected; previously, an error message that was intended to support legacy ORM typed mappings would be raised, which additionally did not mention correct patterns to use with Dataclasses specifically. This error message is now no longer raised if registry.mapped_as_dataclass() or MappedAsDataclass is used.

  See also

  Using Non-Mapped Dataclass Fields

  References: #8973

• [orm] [bug]

  Fixed issue in the internal SQL traversal for DML statements like Update and Delete which would cause among other potential issues, a specific issue using lambda statements with the ORM update/delete feature.

  This change is also backported to: 1.4.46

  References: #9033

• [orm] [bug]

  Fixed bug where Session.merge() would fail to preserve the current loaded contents of relationship attributes that were indicated with the relationship.viewonly parameter, thus defeating strategies that use Session.merge() to pull fully loaded objects from caches and other similar techniques. In a related change, fixed issue where an object that contains a loaded relationship that was nonetheless configured as lazy='raise' on the mapping would fail when passed to Session.merge(); checks for “raise” are now suspended within the merge process assuming the Session.merge.load parameter remains at its default of True.

  Overall, this is a behavioral adjustment to a change introduced in the 1.4 series as of #4994, which took “merge” out of the set of cascades applied by default to “viewonly” relationships. As “viewonly” relationships aren’t persisted under any circumstances, allowing their contents to transfer during “merge” does not impact the persistence behavior of the target object. This allows Session.merge() to correctly suit one of its use cases, that of adding objects to a Session that were loaded elsewhere, often for the purposes of restoring from a cache.

  This change is also backported to: 1.4.45

  References: #8862

• [orm] [bug]

  Fixed issues in with_expression() where expressions that were composed of columns that were referenced from the enclosing SELECT would not render correct SQL in some contexts, in the case where the expression had a label name that matched the attribute which used query_expression(), even when query_expression() had no default expression. For the moment, if the query_expression() does have a default expression, that label name is still used for that default, and an additional label with the same name will continue to be ignored. Overall, this case is pretty thorny so further adjustments might be warranted.

  This change is also backported to: 1.4.45

  References: #8881

• [orm] [bug]

  A warning is emitted if a backref name used in relationship() names an attribute on the target class which already has a method or attribute assigned to that name, as the backref declaration will replace that attribute.

  References: #4629

• [orm] [bug]

  A series of changes and improvements regarding Session.refresh(). The overall change is that primary key attributes for an object are now included in a refresh operation unconditionally when relationship-bound attributes are to be refreshed, even if not expired and even if not specified in the refresh.

  • Improved Session.refresh() so that if autoflush is enabled (as is the default for Session), the autoflush takes place at an earlier part of the refresh process so that pending primary key changes are applied without errors being raised. Previously, this autoflush took place too late in the process and the SELECT statement would not use the correct key to locate the row and an InvalidRequestError would be raised.

  • When the above condition is present, that is, unflushed primary key changes are present on the object, but autoflush is not enabled, the refresh() method now explicitly disallows the operation to proceed, and an informative InvalidRequestError is raised asking that the pending primary key changes be flushed first. Previously, this use case was simply broken and InvalidRequestError would be raised anyway. This restriction is so that it’s safe for the primary key attributes to be refreshed, as is necessary for the case of being able to refresh the object with relationship-bound secondary eagerloaders also being emitted. This rule applies in all cases to keep API behavior consistent regardless of whether or not the PK cols are actually needed in the refresh, as it is unusual to be refreshing some attributes on an object while keeping other attributes “pending” in any case.

  • The Session.refresh() method has been enhanced such that attributes which are relationship()-bound and linked to an eager loader, either at mapping time or via last-used loader options, will be refreshed in all cases even when a list of attributes is passed that does not include any columns on the parent row. This builds upon the feature first implemented for non-column attributes as part of #1763 fixed in 1.4 allowing eagerly-loaded relationship-bound attributes to participate in the Session.refresh() operation. If the refresh operation does not indicate any columns on the parent row to be refreshed, the primary key columns will nonetheless be included in the refresh operation, which allows the load to proceed into the secondary relationship loaders indicated as it does normally. Previously an InvalidRequestError error would be raised for this condition (#8703)

  • Fixed issue where an unnecessary additional SELECT would be emitted in the case where Session.refresh() were called with a combination of expired attributes, as well as an eager loader such as selectinload() that emits a “secondary” query, if the primary key attributes were also in an expired state. As the primary key attributes are now included in the refresh automatically, there is no additional load for these attributes when a relationship loader goes to select for them (#8997)

  • Fixed regression caused by #8126 released in 2.0.0b1 where the Session.refresh() method would fail with an AttributeError, if passed both an expired column name as well as the name of a relationship-bound attribute that was linked to a “secondary” eagerloader such as the selectinload() eager loader (#8996)

  References: #8703, #8996, #8997

• [orm] [bug]

  Improved a fix first made in version 1.4 for #8456 which scaled back the usage of internal “polymorphic adapters”, that are used to render ORM queries when the Mapper.with_polymorphic parameter is used. These adapters, which are very complex and error prone, are now used only in those cases where an explicit user-supplied subquery is used for Mapper.with_polymorphic, which includes only the use case of concrete inheritance mappings that use the polymorphic_union() helper, as well as the legacy use case of using an aliased subquery for joined inheritance mappings, which is not needed in modern use.

  For the most common case of joined inheritance mappings that use the built-in polymorphic loading scheme, which includes those which make use of the Mapper.polymorphic_load parameter set to inline, polymorphic adapters are now no longer used. This has both a positive performance impact on the construction of queries as well as a substantial simplification of the internal query rendering process.

  The specific issue targeted was to allow a column_property() to refer to joined-inheritance classes within a scalar subquery, which now works as intuitively as is feasible.

  References: #8168

engine

• [engine] [bug]

  Fixed a long-standing race condition in the connection pool which could occur under eventlet/gevent monkeypatching schemes in conjunction with the use of eventlet/gevent Timeout conditions, where a connection pool checkout that’s interrupted due to the timeout would fail to clean up the failed state, causing the underlying connection record and sometimes the database connection itself to “leak”, leaving the pool in an invalid state with unreachable entries. This issue was first identified and fixed in SQLAlchemy 1.2 for #4225, however the failure modes detected in that fix failed to accommodate for BaseException, rather than Exception, which prevented eventlet/gevent Timeout from being caught. In addition, a block within initial pool connect has also been identified and hardened with a BaseException -> “clean failed connect” block to accommodate for the same condition in this location. Big thanks to Github user @niklaus for their tenacious efforts in identifying and describing this intricate issue.

  This change is also backported to: 1.4.46

  References: #8974

• [engine] [bug]

  Fixed issue where Result.freeze() method would not work for textual SQL using either text() or Connection.exec_driver_sql().

  This change is also backported to: 1.4.45

  References: #8963

sql

• [sql] [usecase]

  An informative re-raise is now thrown in the case where any “literal bindparam” render operation fails, indicating the value itself and the datatype in use, to assist in debugging when literal params are being rendered in a statement.

  This change is also backported to: 1.4.45

  References: #8800

• [sql] [bug]

  Fixed issue in lambda SQL feature where the calculated type of a literal value would not take into account the type coercion rules of the “compared to type”, leading to a lack of typing information for SQL expressions, such as comparisons to JSON elements and similar.

  This change is also backported to: 1.4.46

  References: #9029

• [sql] [bug]

  Fixed a series of issues regarding the position and sometimes the identity of rendered bound parameters, such as those used for SQLite, asyncpg, MySQL, Oracle and others. Some compiled forms would not maintain the order of parameters correctly, such as the PostgreSQL regexp_replace() function, the “nesting” feature of the CTE construct first introduced in #4123, and selectable tables formed by using the FunctionElement.column_valued() method with Oracle.

  This change is also backported to: 1.4.45

  References: #8827

• [sql] [bug]

  Added test support to ensure that all compiler visit_xyz() methods across all Compiler implementations in SQLAlchemy accept a **kw parameter, so that all compilers accept additional keyword arguments under all circumstances.

  References: #8988

• [sql] [bug]

  The SQLCompiler.construct_params() method, as well as the SQLCompiler.params accessor, will now return the exact parameters that correspond to a compiled statement that used the render_postcompile parameter to compile. Previously, the method returned a parameter structure that by itself didn’t correspond to either the original parameters or the expanded ones.

  Passing a new dictionary of parameters to SQLCompiler.construct_params() for a SQLCompiler that was constructed with render_postcompile is now disallowed; instead, to make a new SQL string and parameter set for an alternate set of parameters, a new method SQLCompiler.construct_expanded_state() is added which will produce a new expanded form for the given parameter set, using the ExpandedState container which includes a new SQL statement and new parameter dictionary, as well as a positional parameter tuple.

  References: #6114

• [sql] [bug]

  To accommodate for third party dialects with different character escaping needs regarding bound parameters, the system by which SQLAlchemy “escapes” (i.e., replaces with another character in its place) special characters in bound parameter names has been made extensible for third party dialects, using the SQLCompiler.bindname_escape_chars dictionary which can be overridden at the class declaration level on any SQLCompiler subclass. As part of this change, also added the dot "." as a default “escaped” character.

  References: #8994

typing

• [typing] [bug]

  pep-484 typing has been completed for the sqlalchemy.ext.horizontal_shard extension as well as the sqlalchemy.orm.events module. Thanks to Gleb Kisenkov for their efforts.

  References: #6810, #9025

asyncio

• [asyncio] [bug]

  Removed non-functional merge() method from AsyncResult. This method has never worked and was included with AsyncResult in error.

  This change is also backported to: 1.4.45

  References: #8952

postgresql

• [postgresql] [bug]

  Fixed bug where the PostgreSQL Insert.on_conflict_do_update.constraint parameter would accept an Index object, however would not expand this index out into its individual index expressions, instead rendering its name in an ON CONFLICT ON CONSTRAINT clause, which is not accepted by PostgreSQL; the “constraint name” form only accepts unique or exclude constraint names. The parameter continues to accept the index but now expands it out into its component expressions for the render.

  This change is also backported to: 1.4.46

  References: #9023

• [postgresql] [bug]

  Made an adjustment to how the PostgreSQL dialect considers column types when it reflects columns from a table, to accommodate for alternative backends which may return NULL from the PG format_type() function.

  This change is also backported to: 1.4.45

  References: #8748

• [postgresql] [bug]

  Added support for explicit use of PG full text functions with asyncpg and psycopg (SQLAlchemy 2.0 only), with regards to the REGCONFIG type cast for the first argument, which previously would be incorrectly cast to a VARCHAR, causing failures on these dialects that rely upon explicit type casts. This includes support for to_tsvector, to_tsquery, plainto_tsquery, phraseto_tsquery, websearch_to_tsquery, ts_headline, each of which will determine based on number of arguments passed if the first string argument should be interpreted as a PostgreSQL “REGCONFIG” value; if so, the argument is typed using a newly added type object REGCONFIG which is then explicitly cast in the SQL expression.

  References: #8977

• [postgresql] [bug]

  Fixed regression where newly revised PostgreSQL range types such as INT4RANGE could not be set up as the impl of a TypeDecorator custom type, instead raising a TypeError.

  References: #9020

• [postgresql] [bug]

  The Range.__eq___() will now return NotImplemented when comparing with an instance of a different class, instead of raising an AttributeError exception.

  References: #8984

sqlite

• [sqlite] [usecase]

  Added support for the SQLite backend to reflect the “DEFERRABLE” and “INITIALLY” keywords which may be present on a foreign key construct. Pull request courtesy Michael Gorven.

  This change is also backported to: 1.4.45

  References: #8903

• [sqlite] [usecase]

  Added support for reflection of expression-oriented WHERE criteria included in indexes on the SQLite dialect, in a manner similar to that of the PostgreSQL dialect. Pull request courtesy Tobias Pfeiffer.

  This change is also backported to: 1.4.45

  References: #8804

oracle

• [oracle] [bug]

  Fixed issue in Oracle compiler where the syntax for FunctionElement.column_valued() was incorrect, rendering the name COLUMN_VALUE without qualifying the source table correctly.

  This change is also backported to: 1.4.45

  References: #8945

tests

• [tests] [bug]

  Fixed issue in tox.ini file where changes in the tox 4.0 series to the format of “passenv” caused tox to not function correctly, in particular raising an error as of tox 4.0.6.

  This change is also backported to: 1.4.46

• [tests] [bug]

  Added new exclusion rule for third party dialects called unusual_column_name_characters, which can be “closed” for third party dialects that don’t support column names with unusual characters such as dots, slashes, or percent signs in them, even if the name is properly quoted.

  This change is also backported to: 1.4.46

  References: #9002

2.0.0b4

Released: December 5, 2022

orm

• [orm] [feature]

  Added a new parameter mapped_column.use_existing_column to accommodate the use case of a single-table inheritance mapping that uses the pattern of more than one subclass indicating the same column to take place on the superclass. This pattern was previously possible by using declared_attr() in conjunction with locating the existing column in the .__table__ of the superclass, however is now updated to work with mapped_column() as well as with pep-484 typing, in a simple and succinct way.

  See also

  Resolving Column Conflicts with use_existing_column

  References: #8822

• [orm] [usecase]

  Added support custom user-defined types which extend the Python enum.Enum base class to be resolved automatically to SQLAlchemy Enum SQL types, when using the Annotated Declarative Table feature. The feature is made possible through new lookup features added to the ORM type map feature, and includes support for changing the arguments of the Enum that’s generated by default as well as setting up specific enum.Enum types within the map with specific arguments.

  See also

  Using Python Enum types in the type map

  References: #8859

• [orm] [usecase]

  Added mapped_column.compare parameter to relevant ORM attribute constructs including mapped_column(), relationship() etc. to provide for the Python dataclasses compare parameter on field(), when using the Declarative Dataclass Mapping feature. Pull request courtesy Simon Schiele.

  References: #8905

• [orm] [performance] [bug]

  Additional performance enhancements within ORM-enabled SQL statements, specifically targeting callcounts within the construction of ORM statements, using combinations of aliased() with union() and similar “compound” constructs, in addition to direct performance improvements to the corresponding_column() internal method that is used heavily by the ORM by constructs like aliased() and similar.

  References: #8796

• [orm] [bug]

  Fixed issue where use of an unknown datatype within a Mapped annotation for a column-based attribute would silently fail to map the attribute, rather than reporting an exception; an informative exception message is now raised.

  References: #8888

• [orm] [bug]

  Fixed a suite of issues involving Mapped use with dictionary types, such as Mapped[dict[str, str] | None], would not be correctly interpreted in Declarative ORM mappings. Support to correctly “de-optionalize” this type including for lookup in type_annotation_map has been fixed.

  References: #8777

• [orm] [bug]

  Fixed bug in Declarative Dataclass Mapping feature where using plain dataclass fields with the __allow_unmapped__ directive in a mapping would not create a dataclass with the correct class-level state for those fields, copying the raw Field object to the class inappropriately after dataclasses itself had replaced the Field object with the class-level default value.

  References: #8880

• [orm] [bug] [regression]

  Fixed regression where flushing a mapped class that’s mapped against a subquery, such as a direct mapping or some forms of concrete table inheritance, would fail if the Mapper.eager_defaults parameter were used.

  References: #8812

• [orm] [bug]

  Fixed regression in 2.0.0b3 caused by #8759 where indicating the Mapped name using a qualified name such as sqlalchemy.orm.Mapped would fail to be recognized by Declarative as indicating the Mapped construct.

  References: #8853

orm extensions

• [usecase] [orm extensions]

  Added support for the association_proxy() extension function to take part within Python dataclasses configuration, when using the native dataclasses feature described at Declarative Dataclass Mapping. Included are attribute-level arguments including association_proxy.init and association_proxy.default_factory.

  Documentation for association proxy has also been updated to use “Annotated Declarative Table” forms within examples, including type annotations used for AssocationProxy itself.

  References: #8878

sql

• [sql] [usecase]

  Added ScalarValues that can be used as a column element allowing using Values inside IN clauses or in conjunction with ANY or ALL collection aggregates. This new class is generated using the method Values.scalar_values(). The Values instance is now coerced to a ScalarValues when used in a IN or NOT IN operation.

  References: #6289

• [sql] [bug]

  Fixed critical memory issue identified in cache key generation, where for very large and complex ORM statements that make use of lots of ORM aliases with subqueries, cache key generation could produce excessively large keys that were orders of magnitude bigger than the statement itself. Much thanks to Rollo Konig Brock for their very patient, long term help in finally identifying this issue.

  This change is also backported to: 1.4.44

  References: #8790

• [sql] [bug]

  The approach to the numeric pep-249 paramstyle has been rewritten, and is now fully supported, including by features such as “expanding IN” and “insertmanyvalues”. Parameter names may also be repeated in the source SQL construct which will be correctly represented within the numeric format using a single parameter. Introduced an additional numeric paramstyle called numeric_dollar, which is specifically what’s used by the asyncpg dialect; the paramstyle is equivalent to numeric except numeric indicators are indicated by a dollar-sign rather than a colon. The asyncpg dialect now uses numeric_dollar paramstyle directly, rather than compiling to format style first.

  The numeric and numeric_dollar paramstyles assume that the target backend is capable of receiving the numeric parameters in any order, and will match the given parameter values to the statement based on matching their position (1-based) to the numeric indicator. This is the normal behavior of “numeric” paramstyles, although it was observed that the SQLite DBAPI implements a not-used “numeric” style that does not honor parameter ordering.

  References: #8849

• [sql] [bug]

  Adjusted the rendering of RETURNING, in particular when using Insert, such that it now renders columns using the same logic as that of the Select construct to generate labels, which will include disambiguating labels, as well as that a SQL function surrounding a named column will be labeled using the column name itself. This establishes better cross-compatibility when selecting rows from either Select constructs or from DML statements that use UpdateBase.returning(). A narrower scale change was also made for the 1.4 series that adjusted the function label issue only.

  References: #8770

schema

• [schema] [bug]

  Stricter rules are in place for appending of Column objects to Table objects, both moving some previous deprecation warnings to exceptions, and preventing some previous scenarios that would cause duplicate columns to appear in tables, when Table.extend_existing were set to True, for both programmatic Table construction as well as during reflection operations.

  See Stricter rules for replacement of Columns in Table objects with same-names, keys for a rundown of these changes.

  See also

  Stricter rules for replacement of Columns in Table objects with same-names, keys

  References: #8925

typing

• [typing] [usecase]

  Added a new type SQLColumnExpression which may be indicated in user code to represent any SQL column oriented expression, including both those based on ColumnElement as well as on ORM QueryableAttribute. This type is a real class, not an alias, so can also be used as the foundation for other objects. An additional ORM-specific subclass SQLORMExpression is also included.

  References: #8847

• [typing] [bug]

  Adjusted internal use of the Python enum.IntFlag class which changed its behavioral contract in Python 3.11. This was not causing runtime failures however caused typing runs to fail under Python 3.11.

  References: #8783

• [typing] [bug]

  The sqlalchemy.ext.mutable extension and sqlalchemy.ext.automap extensions are now fully pep-484 typed. Huge thanks to Gleb Kisenkov for their efforts on this.

  References: #6810, #8667

• [typing] [bug]

  Corrected typing support for the relationship.secondary argument which may also accept a callable (lambda) that returns a FromClause.

• [typing] [bug]

  Improved the typing for sessionmaker and async_sessionmaker, so that the default type of their return value will be Session or AsyncSession, without the need to type this explicitly. Previously, Mypy would not automaticaly infer these return types from its generic base.

  As part of this change, arguments for Session, AsyncSession, sessionmaker and async_sessionmaker beyond the initial “bind” argument have been made keyword-only, which includes parameters that have always been documented as keyword arguments, such as Session.autoflush, Session.class_, etc.

  Pull request courtesy Sam Bull.

  References: #8842

• [typing] [bug]

  Fixed issue where passing a callbale function returning an iterable of column elements to relationship.order_by was flagged as an error in type checkers.

  References: #8776

postgresql

• [postgresql] [usecase]

  Complementing #8690, new comparison methods such as Range.adjacent_to(), Range.difference(), Range.union(), etc., were added to the PG-specific range objects, bringing them in par with the standard operators implemented by the underlying AbstractRange.comparator_factory.

  In addition, the __bool__() method of the class has been corrected to be consistent with the common Python containers behavior as well as how other popular PostgreSQL drivers do: it now tells whether the range instance is not empty, rather than the other way around.

  Pull request courtesy Lele Gaifax.

  References: #8765

• [postgresql] [change] [asyncpg]

  Changed the paramstyle used by asyncpg from format to numeric_dollar. This has two main benefits since it does not require additional processing of the statement and allows for duplicate parameters to be present in the statements.

  References: #8926

• [postgresql] [bug] [mssql]

  For the PostgreSQL and SQL Server dialects only, adjusted the compiler so that when rendering column expressions in the RETURNING clause, the “non anon” label that’s used in SELECT statements is suggested for SQL expression elements that generate a label; the primary example is a SQL function that may be emitting as part of the column’s type, where the label name should match the column’s name by default. This restores a not-well defined behavior that had changed in version 1.4.21 due to #6718, #6710. The Oracle dialect has a different RETURNING implementation and was not affected by this issue. Version 2.0 features an across the board change for its widely expanded support of RETURNING on other backends.

  This change is also backported to: 1.4.44

  References: #8770

• [postgresql] [bug]

  Added additional type-detection for the new PostgreSQL Range type, where previous cases that allowed the psycopg2-native range objects to be received directly by the DBAPI without SQLAlchemy intercepting them stopped working, as we now have our own value object. The Range object has been enhanced such that SQLAlchemy Core detects it in otherwise ambiguous situations (such as comparison to dates) and applies appropriate bind handlers. Pull request courtesy Lele Gaifax.

  References: #8884

mssql

• [mssql] [bug]

  Fixed regression caused by the combination of #8177, re-enable setinputsizes for SQL server unless fast_executemany + DBAPI executemany is used for a statement, along with #6047, implement “insertmanyvalues”, which bypasses DBAPI executemany in place of a custom DBAPI execute for INSERT statements. setinputsizes would incorrectly not be used for a multiple parameter-set INSERT statement that used “insertmanyvalues” if fast_executemany were turned on, as the check would incorrectly assume this is a DBAPI executemany call. The “regression” would then be that the “insertmanyvalues” statement format is apparently slightly more sensitive to multiple rows that don’t use the same types for each row, so in such a case setinputsizes is especially needed.

  The fix repairs the fast_executemany check so that it only disables setinputsizes if true DBAPI executemany is to be used.

  References: #8917

oracle

• [oracle] [bug]

  Continued fixes for Oracle fix #8708 released in 1.4.43 where bound parameter names that start with underscores, which are disallowed by Oracle, were still not being properly escaped in all circumstances.

  This change is also backported to: 1.4.45

  References: #8708

tests

• [tests] [bug]

  Fixed issue where the --disable-asyncio parameter to the test suite would fail to not actually run greenlet tests and would also not prevent the suite from using a “wrapping” greenlet for the whole suite. This parameter now ensures that no greenlet or asyncio use will occur within the entire run when set.

  This change is also backported to: 1.4.44

  References: #8793

2.0.0b3

Released: November 4, 2022

orm

• [orm] [bug]

  Fixed issue in joined eager loading where an assertion fail would occur with a particular combination of outer/inner joined eager loads, when eager loading across three mappers where the middle mapper was an inherited subclass mapper.

  This change is also backported to: 1.4.43

  References: #8738

• [orm] [bug]

  Fixed bug involving Select constructs, where combinations of Select.select_from() with Select.join(), as well as when using Select.join_from(), would cause the with_loader_criteria() feature as well as the IN criteria needed for single-table inheritance queries to not render, in cases where the columns clause of the query did not explicitly include the left-hand side entity of the JOIN. The correct entity is now transferred to the Join object that’s generated internally, so that the criteria against the left side entity is correctly added.

  This change is also backported to: 1.4.43

  References: #8721

• [orm] [bug]

  An informative exception is now raised when the with_loader_criteria() option is used as a loader option added to a specific “loader path”, such as when using it within Load.options(). This use is not supported as with_loader_criteria() is only intended to be used as a top level loader option. Previously, an internal error would be generated.

  This change is also backported to: 1.4.43

  References: #8711

• [orm] [bug]

  Improved “dictionary mode” for Session.get() so that synonym names which refer to primary key attribute names may be indicated in the named dictionary.

  This change is also backported to: 1.4.43

  References: #8753

• [orm] [bug]

  Fixed issue where “selectin_polymorphic” loading for inheritance mappers would not function correctly if the Mapper.polymorphic_on parameter referred to a SQL expression that was not directly mapped on the class.

  This change is also backported to: 1.4.43

  References: #8704

• [orm] [bug]

  Fixed issue where the underlying DBAPI cursor would not be closed when using the Query object as an iterator, if a user-defined exception case were raised within the iteration process, thereby causing the iterator to be closed by the Python interpreter. When using Query.yield_per() to create server-side cursors, this would lead to the usual MySQL-related issues with server side cursors out of sync, and without direct access to the Result object, end-user code could not access the cursor in order to close it.

  To resolve, a catch for GeneratorExit is applied within the iterator method, which will close the result object in those cases when the iterator were interrupted, and by definition will be closed by the Python interpreter.

  As part of this change as implemented for the 1.4 series, ensured that .close() methods are available on all Result implementations including ScalarResult, MappingResult. The 2.0 version of this change also includes new context manager patterns for use with Result classes.

  This change is also backported to: 1.4.43

  References: #8710

orm declarative

• [orm] [declarative] [bug]

  Added support in ORM declarative annotations for class names specified for relationship(), as well as the name of the Mapped symbol itself, to be different names than their direct class name, to support scenarios such as where Mapped is imported as from sqlalchemy.orm import Mapped as M, or where related class names are imported with an alternate name in a similar fashion. Additionally, a target class name given as the lead argument for relationship() will always supersede the name given in the left hand annotation, so that otherwise un-importable names that also don’t match the class name can still be used in annotations.

  References: #8759

• [orm] [declarative] [bug]

  Improved support for legacy 1.4 mappings that use annotations which don’t include Mapped[], by ensuring the __allow_unmapped__ attribute can be used to allow such legacy annotations to pass through Annotated Declarative without raising an error and without being interpreted in an ORM runtime context. Additionally improved the error message generated when this condition is detected, and added more documentation for how this situation should be handled. Unfortunately the 1.4 WARN_SQLALCHEMY_20 migration warning cannot detect this particular configurational issue at runtime with its current architecture.

  References: #8692

• [orm] [declarative] [bug]

  Changed a fundamental configuration behavior of Mapper, where Column objects that are explicitly present in the Mapper.properties dictionary, either directly or enclosed within a mapper property object, will now be mapped within the order of how they appear within the mapped Table (or other selectable) itself (assuming they are in fact part of that table’s list of columns), thereby maintaining the same order of columns in the mapped selectable as is instrumented on the mapped class, as well as what renders in an ORM SELECT statement for that mapper. Previously (where “previously” means since version 0.0.1), Column objects in the Mapper.properties dictionary would always be mapped first, ahead of when the other columns in the mapped Table would be mapped, causing a discrepancy in the order in which the mapper would assign attributes to the mapped class as well as the order in which they would render in statements.

  The change most prominently takes place in the way that Declarative assigns declared columns to the Mapper, specifically how Column (or mapped_column()) objects are handled when they have a DDL name that is explicitly different from the mapped attribute name, as well as when constructs such as deferred() etc. are used. The new behavior will see the column ordering within the mapped Table being the same order in which the attributes are mapped onto the class, assigned within the Mapper itself, and rendered in ORM statements such as SELECT statements, independent of how the Column was configured against the Mapper.

  References: #8705

• [orm] [declarative] [bug]

  Fixed issue in new dataclass mapping feature where a column declared on the decalrative base / abstract base / mixin would leak into the constructor for an inheriting subclass under some circumstances.

  References: #8718

• [bug] [orm declarative]

  Fixed issues within the declarative typing resolver (i.e. which resolves ForwardRef objects) where types that were declared for columns in one particular source file would raise NameError when the ultimate mapped class were in another source file. The types are now resolved in terms of the module for each class in which the types are used.

  References: #8742

engine

• [engine] [feature]

  To better support the use case of iterating Result and AsyncResult objects where user-defined exceptions may interrupt the iteration, both objects as well as variants such as ScalarResult, MappingResult, AsyncScalarResult, AsyncMappingResult now support context manager usage, where the result will be closed at the end of the context manager block.

  In addition, ensured that all the above mentioned Result objects include a Result.close() method as well as Result.closed accessors, including ScalarResult and MappingResult which previously did not have a .close() method.

  See also

  Context Manager Support for Result, AsyncResult

  References: #8710

• [engine] [usecase]

  Added new parameter PoolEvents.reset.reset_state parameter to the PoolEvents.reset() event, with deprecation logic in place that will continue to accept event hooks using the previous set of arguments. This indicates various state information about how the reset is taking place and is used to allow custom reset schemes to take place with full context given.

  Within this change a fix that’s also backported to 1.4 is included which re-enables the PoolEvents.reset() event to continue to take place under all circumstances, including when Connection has already “reset” the connection.

  The two changes together allow custom reset schemes to be implemented using the PoolEvents.reset() event, instead of the PoolEvents.checkin() event (which continues to function as it always has).

  References: #8717

• [engine] [bug] [regression]

  Fixed issue where the PoolEvents.reset() event hook would not be be called in all cases when a Connection were closed and was in the process of returning its DBAPI connection to the connection pool.

  The scenario was when the Connection had already emitted .rollback() on its DBAPI connection within the process of returning the connection to the pool, where it would then instruct the connection pool to forego doing its own “reset” to save on the additional method call. However, this prevented custom pool reset schemes from being used within this hook, as such hooks by definition are doing more than just calling .rollback(), and need to be invoked under all circumstances. This was a regression that appeared in version 1.4.

  For version 1.4, the PoolEvents.checkin() remains viable as an alternate event hook to use for custom “reset” implementations. Version 2.0 will feature an improved version of PoolEvents.reset() which is called for additional scenarios such as termination of asyncio connections, and is also passed contextual information about the reset, to allow for “custom connection reset” schemes which can respond to different reset scenarios in different ways.

  This change is also backported to: 1.4.43

  References: #8717

sql

• [sql] [bug]

  Fixed issue which prevented the literal_column() construct from working properly within the context of a Select construct as well as other potential places where “anonymized labels” might be generated, if the literal expression contained characters which could interfere with format strings, such as open parenthesis, due to an implementation detail of the “anonymous label” structure.

  This change is also backported to: 1.4.43

  References: #8724

typing

• [typing] [bug]

  Corrected various typing issues within the engine and async engine packages.

postgresql

• [postgresql] [feature]

  Added new methods Range.contains() and Range.contained_by() to the new Range data object, which mirror the behavior of the PostgreSQL @> and <@ operators, as well as the comparator_factory.contains() and comparator_factory.contained_by() SQL operator methods. Pull request courtesy Lele Gaifax.

  References: #8706

• [postgresql] [usecase]

  Refined the new approach to range objects described at New RANGE / MULTIRANGE support and changes for PostgreSQL backends to accommodate driver-specific range and multirange objects, to better accommodate both legacy code as well as when passing results from raw SQL result sets back into new range or multirange expressions.

  References: #8690

mssql

• [mssql] [bug]

  Fixed issue with Inspector.has_table(), which when used against a temporary table with the SQL Server dialect would fail on some Azure variants, due to an unnecessary information schema query that is not supported on those server versions. Pull request courtesy Mike Barry.

  This change is also backported to: 1.4.43

  References: #8714

• [mssql] [bug] [reflection]

  Fixed issue with Inspector.has_table(), which when used against a view with the SQL Server dialect would erroneously return False, due to a regression in the 1.4 series which removed support for this on SQL Server. The issue is not present in the 2.0 series which uses a different reflection architecture. Test support is added to ensure has_table() remains working per spec re: views.

  This change is also backported to: 1.4.43

  References: #8700

oracle

• [oracle] [bug]

  Fixed issue where bound parameter names, including those automatically derived from similarly-named database columns, which contained characters that normally require quoting with Oracle would not be escaped when using “expanding parameters” with the Oracle dialect, causing execution errors. The usual “quoting” for bound parameters used by the Oracle dialect is not used with the “expanding parameters” architecture, so escaping for a large range of characters is used instead, now using a list of characters/escapes that are specific to Oracle.

  This change is also backported to: 1.4.43

  References: #8708

• [oracle] [bug]

  Fixed issue where the nls_session_parameters view queried on first connect in order to get the default decimal point character may not be available depending on Oracle connection modes, and would therefore raise an error. The approach to detecting decimal char has been simplified to test a decimal value directly, instead of reading system views, which works on any backend / driver.

  This change is also backported to: 1.4.43

  References: #8744

2.0.0b2

Released: October 20, 2022

orm

• [orm] [bug]

  Removed the warning that emits when using ORM-enabled update/delete regarding evaluation of columns by name, first added in #4073; this warning actually covers up a scenario that otherwise could populate the wrong Python value for an ORM mapped attribute depending on what the actual column is, so this deprecated case is removed. In 2.0, ORM enabled update/delete uses “auto” for “synchronize_session”, which should do the right thing automatically for any given UPDATE expression.

  References: #8656

orm declarative

• [orm] [declarative] [usecase]

  Added support for mapped classes that are also Generic subclasses, to be specified as a GenericAlias object (e.g. MyClass[str]) within statements and calls to inspect().

  References: #8665

• [orm] [declarative] [bug]

  Improved the DeclarativeBase class so that when combined with other mixins like MappedAsDataclass, the order of the classes may be in either order.

  References: #8665

• [orm] [declarative] [bug]

  Fixed bug in new ORM typed declarative mappings where the ability to use Optional[MyClass] or similar forms such as MyClass | None in the type annotation for a many-to-one relationship was not implemented, leading to errors. Documentation has also been added for this use case to the relationship configuration documentation.

  References: #8668

• [orm] [declarative] [bug]

  Fixed issue with new dataclass mapping feature where arguments passed to the dataclasses API could sometimes be mis-ordered when dealing with mixins that override mapped_column() declarations, leading to initializer problems.

  References: #8688

sql

• [sql] [bug] [regression]

  Fixed bug in new “insertmanyvalues” feature where INSERT that included a subquery with bindparam() inside of it would fail to render correctly in “insertmanyvalues” format. This affected psycopg2 most directly as “insertmanyvalues” is used unconditionally with this driver.

  References: #8639

typing

• [typing] [bug]

  Fixed typing issue where pylance strict mode would report “instance variable overrides class variable” when using a method to define __tablename__, __mapper_args__ or __table_args__.

  References: #8645

• [typing] [bug]

  Fixed typing issue where pylance strict mode would report “partially unknown” datatype for the mapped_column() construct.

  References: #8644

mssql

• [mssql] [bug]

  Fixed regression caused by SQL Server pyodbc change #8177 where we now use setinputsizes() by default; for VARCHAR, this fails if the character size is greater than 4000 (or 2000, depending on data) characters as the incoming datatype is NVARCHAR, which has a limit of 4000 characters, despite the fact that VARCHAR can handle unlimited characters. Additional pyodbc-specific typing information is now passed to setinputsizes() when the datatype’s size is > 2000 characters. The change is also applied to the JSON type which was also impacted by this issue for large JSON serializations.

  References: #8661

• [mssql] [bug]

  The Sequence construct restores itself to the DDL behavior it had prior to the 1.4 series, where creating a Sequence with no additional arguments will emit a simple CREATE SEQUENCE instruction without any additional parameters for “start value”. For most backends, this is how things worked previously in any case; however, for MS SQL Server, the default value on this database is -2**63; to prevent this generally impractical default from taking effect on SQL Server, the Sequence.start parameter should be provided. As usage of Sequence is unusual for SQL Server which for many years has standardized on IDENTITY, it is hoped that this change has minimal impact.

  See also

  The Sequence construct reverts to not having any explicit default “start” value; impacts MS SQL Server

  References: #7211

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

• [general] [changed]

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

  See also

  C Extensions now ported to Cython

  References: #7256

• [platform] [change]

  SQLAlchemy’s source build and installation now includes a pyproject.toml file for full PEP 517 support.

  See also

  Installation is now fully pep-517 enabled

  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 ORM Batch inserts with psycopg2 now batch statements with RETURNING in most cases, 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 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. AttributeEvents.append.key, 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 MappedCollection.

  References: #8375

• [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 ORM-Enabled INSERT, UPDATE, and DELETE statements.

  References: #3162

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

  See also

  Session raises proactively when illegal concurrent or reentrant access is detected

  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.

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

  See the new documentation at Composite Column Types for examples.

• [orm] [feature]

  Added very experimental feature to the selectinload() and immediateload() loader options called selectinload.recursion_depth / 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 selectinload() and 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 Session.autobegin, which when set to False will prevent the Session from beginning a transaction implicitly. The 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” Session that won’t implicitly start new transactions.

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

  References: #6928

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

  Added load_only.raiseload parameter to the 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 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:

  • RelationshipProperty becomes an alias for the primary name Relationship, which is constructed as always from the relationship() function

  • SynonymProperty becomes an alias for the primary name Synonym, constructed as always from the synonym() function

  • CompositeProperty becomes an alias for the primary name Composite, constructed as always from the composite() function

• [orm] [change]

  For consistency with the prominent ORM concept Mapped, the names of the dictionary-oriented collections, attribute_mapped_collection(), column_mapped_collection(), and MappedCollection, are changed to attribute_keyed_dict(), column_keyed_dict() and 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 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 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.

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

  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 begin and 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 attribute_mapped_collection() and 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 attribute_mapped_collection.ignore_unpopulated_attribute is also added to both attribute_mapped_collection() and 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 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 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 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:

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

  The QueuePool now ignores max_overflow when pool_size=0, properly making the pool unlimited in all cases.

  References: #8523

• [engine] [bug]

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

  See also

  str(engine.url) will obfuscate the password by default

  References: #8567

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

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

• [sql] [feature]

  Added long-requested case-insensitive string operators ColumnOperators.icontains(), ColumnOperators.istartswith(), 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 ColumnOperators.contains(), 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 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 select() and similar now.

  See also

  Setting the COLUMNS and FROM clause

  References: #8285

• [sql] [feature]

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

  References: #7212

• [sql] [feature]

  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

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

  See also

  DATE, TIME, DATETIME datatypes now support literal rendering on all backends

  References: #5052

• [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 select() construct when using the 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 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 Select also generate FROM clauses, such as the columns clause or WHERE clause, these will render after the clauses delivered by Select.select_from() assuming they were not explictly passed to 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 existing def 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 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.

  See also

  Python division operator performs true division for all backends; added floor division

  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]

  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

• [sql] [bug]

  The array_agg will now set the array dimensions to 1. Improved 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 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.

  See also

  New Conditional DDL for Constraints and Indexes

  References: #7631

• [schema] [usecase]

  Added parameter DropConstraint.if_exists to the 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 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().

  See also

  Major Architectural, Performance and API Enhancements for Database Reflection - full background

  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 Table.dialect_options. Pull request courtesy of Ramon Will.

  References: #4038

typing

• [typing] [improvement]

  The TypeEngine.with_variant() method now returns a copy of the original 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.

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

  See also

  “with_variant()” clones the original TypeEngine rather than changing the type

  References: #6980

postgresql

• [postgresql] [feature]

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

  See also

  DOMAIN

  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 ARRAY and 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 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 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.

  See also

  New RANGE / MULTIRANGE support and changes for PostgreSQL backends

  Range and Multirange Types

  References: #7156, #8540

• [postgresql] [usecase]

  The “ping” query emitted when configuring 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 UUID.as_uuid of UUID, previously specific to the PostgreSQL dialect but now generalized for Core (along with a new backend-agnostic Uuid datatype) now defaults to True, indicating that Python UUID objects are accepted by this datatype by default. Additionally, the SQL Server UNIQUEIDENTIFIER datatype has been converted to be a UUID-receiving type; for legacy code that makes use of UNIQUEIDENTIFIER using string values, set the UNIQUEIDENTIFIER.as_uuid parameter to False.

  References: #7225

• [postgresql] [change]

  The ENUM.name parameter for the PostgreSQL-specific ENUM datatype is now a required keyword argument. The “name” is necessary in any case in order for the 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 func in conjunction with Operators.bool_op() (an improved version of Operators.op() for boolean operators).

  See also

  match() operator on PostgreSQL uses plainto_tsquery() rather than to_tsquery()

  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

  • 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 create_engine() and create_async_engine() engine-creation functions.

  See also

  Dialect support for psycopg 3 (a.k.a. “psycopg”)

  psycopg

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

  See also

  JSON Types - PostgreSQL JSON types.

  References: #8216

• [postgresql] [reflection]

  The PostgreSQL dialect now supports reflection of expression based indexes. The reflection is supported both when using Inspector.get_indexes() and when reflecting a Table using 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 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.

  See also

  Reflecting internal schema tables

  References: #8234

• [sqlite] [usecase]

  Added RETURNING support for the SQLite dialect. SQLite supports RETURNING since version 3.35.

  References: #6195

• [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] [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 Update construct when more than one table or other entity or selectable is used.

  References: #7185

• [sqlite] [performance] [bug]

  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.

  See also

  The SQLite dialect uses QueuePool for file-based databases

  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

• [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 Unicode or UnicodeText, as well as altered the base JSON datatype to consider JSON string values as Unicode rather than 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.

  See also

  Dialect support for oracledb

  python-oracledb

  References: #8054

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

  See also

  New Oracle FLOAT type with binary precision; decimal precision not accepted directly

  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 ORM Batch inserts with psycopg2 now batch statements with RETURNING in most cases.

  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 Select.fetch() were used directly, it’s now implied for Select.limit() and 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 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 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

• [removed] [sybase]

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

  See also

  External Dialects

  References: #7258

• [removed] [firebird]

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

  See also

  External Dialects

  References: #7258