1.4 Changelog¶
This document details individual issue-level changes made throughout 1.4 releases. For a narrative overview of what’s new in 1.4, see What’s New in SQLAlchemy 1.4?.
1.4.49¶
no release date1.4.48¶
Released: April 30, 2023orm¶
Fixed critical caching issue where the combination of
aliased()
andhybrid_property()
expression compositions would cause a cache key mismatch, leading to cache keys that held onto the actualaliased()
object while also not matching that of equivalent constructs, filling up the cache.References: #9728
Fixed bug where various ORM-specific getters such as
ORMExecuteState.is_column_load
,ORMExecuteState.is_relationship_load
,ORMExecuteState.loader_strategy_path
etc. would throw anAttributeError
if the SQL statement itself were a “compound select” such as a UNION.References: #9634
Fixed endless loop which could occur when using “relationship to aliased class” feature and also indicating a recursive eager loader such as
lazy="selectinload"
in the loader, in combination with another eager loader on the opposite side. The check for cycles has been fixed to include aliased class relationships.References: #9590
1.4.47¶
Released: March 18, 2023sql¶
Fixed bug / regression where using
bindparam()
with the same name as a column in theUpdate.values()
method ofUpdate
, as well as theInsert.values()
method ofInsert
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 plainTable
instances, but would occur if the statement were invoked with aSession
or aConnection
.Update
part of the issue was present in both 2.0 and 1.4 and is backported to 1.4.References: #9075
Fixed stringify for a the
CreateSchema
andDropSchema
DDL constructs, which would fail with anAttributeError
when stringified without a dialect.References: #7664
Fixed critical SQL caching issue where use of the
Operators.op()
custom operator function would not produce an appropriate cache key, leading to reduce the effectiveness of the SQL cache.References: #9506
mypy¶
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.
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
).References: #9102
postgresql¶
Added support to the asyncpg dialect to return the
cursor.rowcount
value for SELECT statements when available. While this is not a typical use forcursor.rowcount
, the other PostgreSQL dialects generally provide this value. Pull request courtesy Michael Gorven.References: #9048
mysql¶
Added support to MySQL index reflection to correctly reflect the
mysql_length
dictionary, which previously was being ignored.References: #9047
mssql¶
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.References: #9133
oracle¶
1.4.46¶
Released: January 3, 2023general¶
A new deprecation “uber warning” is now emitted at runtime the first time any SQLAlchemy 2.0 deprecation warning would normally be emitted, but the
SQLALCHEMY_WARN_20
environment variable is not set. The warning emits only once at most, before setting a boolean to prevent it from emitting a second time.This deprecation warning intends to notify users who may not have set an appropriate constraint in their requirements files to block against a surprise SQLAlchemy 2.0 upgrade and also alert that the SQLAlchemy 2.0 upgrade process is available, as the first full 2.0 release is expected very soon. The deprecation warning can be silenced by setting the environment variable
SQLALCHEMY_SILENCE_UBER_WARNING
to"1"
.References: #8983
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-levelfile
call that is unavailable under some circumstances, including within some secure environment configurations.References: #8995
orm¶
engine¶
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 forBaseException
, rather thanException
, which prevented eventlet/geventTimeout
from being caught. In addition, a block within initial pool connect has also been identified and hardened with aBaseException
-> “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.References: #8974
sql¶
Added parameter
FunctionElement.column_valued.joins_implicitly
, which is useful in preventing the “cartesian product” warning when making use of table-valued or column-valued functions. This parameter was already introduced forFunctionElement.table_valued()
in #7845, however it failed to be added forFunctionElement.column_valued()
as well.References: #9009
Fixed bug where SQL compilation would fail (assertion fail in 2.0, NoneType error in 1.4) when using an expression whose type included
TypeEngine.bind_expression()
, in the context of an “expanding” (i.e. “IN”) parameter in conjunction with theliteral_binds
compiler parameter.References: #8989
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.References: #9029
postgresql¶
Added the PostgreSQL type
MACADDR8
. Pull request courtesy of Asim Farooq.References: #8393
Fixed bug where the PostgreSQL
Insert.on_conflict_do_update.constraint
parameter would accept anIndex
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.References: #9023
sqlite¶
Fixed regression caused by new support for reflection of partial indexes on SQLite added in 1.4.45 for #8804, where the
index_list
pragma command in very old versions of SQLite (possibly prior to 3.8.9) does not return the current expected number of columns, leading to exceptions raised when reflecting tables and indexes.References: #8969
tests¶
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.
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.References: #9002
1.4.45¶
Released: December 10, 2022orm¶
Fixed bug where
Session.merge()
would fail to preserve the current loaded contents of relationship attributes that were indicated with therelationship.viewonly
parameter, thus defeating strategies that useSession.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 aslazy='raise'
on the mapping would fail when passed toSession.merge()
; checks for “raise” are now suspended within the merge process assuming theSession.merge.load
parameter remains at its default ofTrue
.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 aSession
that were loaded elsewhere, often for the purposes of restoring from a cache.References: #8862
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 usedquery_expression()
, even whenquery_expression()
had no default expression. For the moment, if thequery_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.References: #8881
engine¶
Fixed issue where
Result.freeze()
method would not work for textual SQL using eithertext()
orConnection.exec_driver_sql()
.References: #8963
sql¶
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.
References: #8800
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 theCTE
construct first introduced in #4123, and selectable tables formed by using theFunctionElement.column_valued()
method with Oracle.References: #8827
asyncio¶
Removed non-functional
merge()
method fromAsyncResult
. This method has never worked and was included withAsyncResult
in error.References: #8952
postgresql¶
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.References: #8748
sqlite¶
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.
References: #8903
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.
References: #8804
Backported a fix for SQLite reflection of unique constraints in attached schemas, released in 2.0 as a small part of #4379. Previously, unique constraints in attached schemas would be ignored by SQLite reflection. Pull request courtesy Michael Gorven.
References: #8866
oracle¶
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.
References: #8708
Fixed issue in Oracle compiler where the syntax for
FunctionElement.column_valued()
was incorrect, rendering the nameCOLUMN_VALUE
without qualifying the source table correctly.References: #8945
1.4.44¶
Released: November 12, 2022sql¶
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.
References: #8790
postgresql¶
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.
References: #8770
oracle¶
Fixed issue in the Oracle dialect where an INSERT statement that used
insert(some_table).values(...).returning(some_table)
against a fullTable
object at once would fail to execute, raising an exception.
tests¶
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.References: #8793
Adjusted the test suite which tests the Mypy plugin to accommodate for changes in Mypy 0.990 regarding how it handles message output, which affect how sys.path is interpreted when determining if notes and errors should be printed for particular files. The change broke the test suite as the files within the test directory itself no longer produced messaging when run under the mypy API.
1.4.43¶
Released: November 4, 2022orm¶
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.
References: #8738
Fixed bug involving
Select
constructs, where combinations ofSelect.select_from()
withSelect.join()
, as well as when usingSelect.join_from()
, would cause thewith_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 theJoin
object that’s generated internally, so that the criteria against the left side entity is correctly added.References: #8721
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 withinLoad.options()
. This use is not supported aswith_loader_criteria()
is only intended to be used as a top level loader option. Previously, an internal error would be generated.References: #8711
Improved “dictionary mode” for
Session.get()
so that synonym names which refer to primary key attribute names may be indicated in the named dictionary.References: #8753
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.References: #8704
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 usingQuery.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 theResult
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 allResult
implementations includingScalarResult
,MappingResult
. The 2.0 version of this change also includes new context manager patterns for use withResult
classes.References: #8710
engine¶
Fixed issue where the
PoolEvents.reset()
event hook would not be be called in all cases when aConnection
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 ofPoolEvents.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.References: #8717
Ensured all
Result
objects include aResult.close()
method as well as aResult.closed
attribute, including onScalarResult
andMappingResult
.References: #8710
sql¶
Fixed issue which prevented the
literal_column()
construct from working properly within the context of aSelect
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.References: #8724
mssql¶
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.References: #8714
Fixed issue with
Inspector.has_table()
, which when used against a view with the SQL Server dialect would erroneously returnFalse
, 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 ensurehas_table()
remains working per spec re: views.References: #8700
oracle¶
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.
References: #8708
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.References: #8744
1.4.42¶
Released: October 16, 2022orm¶
The
Session.execute.bind_arguments
dictionary is no longer mutated when passed toSession.execute()
and similar; instead, it’s copied to an internal dictionary for state changes. Among other things, this fixes and issue where the “clause” passed to theSession.get_bind()
method would be incorrectly referring to theSelect
construct used for the “fetch” synchronization strategy, when the actual query being emitted was aDelete
orUpdate
. This would interfere with recipes for “routing sessions”.References: #8614
A warning is emitted in ORM configurations when an explicit
remote()
annotation is applied to columns that are local to the immediate mapped class, when the referenced class does not include any of the same table columns. Ideally this would raise an error at some point as it’s not correct from a mapping point of view.References: #7094
A warning is emitted when attempting to configure a mapped class within an inheritance hierarchy where the mapper is not given any polymorphic identity, however there is a polymorphic discriminator column assigned. Such classes should be abstract if they never intend to load directly.
References: #7545
Fixed regression for 1.4 in
contains_eager()
where the “wrap in subquery” logic ofjoinedload()
would be inadvertently triggered for use of thecontains_eager()
function with similar statements (e.g. those that usedistinct()
,limit()
oroffset()
), which would then lead to secondary issues with queries that used some combinations of SQL label names and aliasing. This “wrapping” is not appropriate forcontains_eager()
which has always had the contract that the user-defined SQL statement is unmodified with the exception of adding the appropriate columns to be fetched.References: #8569
Fixed regression where using ORM update() with synchronize_session=’fetch’ would fail due to the use of evaluators that are now used to determine the in-Python value for expressions in the the SET clause when refreshing objects; if the evaluators make use of math operators against non-numeric values such as PostgreSQL JSONB, the non-evaluable condition would fail to be detected correctly. The evaluator now limits the use of math mutation operators to numeric types only, with the exception of “+” that continues to work for strings as well. SQLAlchemy 2.0 may alter this further by fetching the SET values completely rather than using evaluation.
References: #8507
engine¶
asyncio¶
Improved implementation of
asyncio.shield()
used in context managers as added in #8145, such that the “close” operation is enclosed within anasyncio.Task
which is then strongly referenced as the operation proceeds. This is per Python documentation indicating that the task is otherwise not strongly referenced.References: #8516
postgresql¶
aggregate_order_by
now supports cache generation.References: #8574
mysql¶
Adjusted the regular expression used to match “CREATE VIEW” when testing for views to work more flexibly, no longer requiring the special keyword “ALGORITHM” in the middle, which was intended to be optional but was not working correctly. The change allows view reflection to work more completely on MySQL-compatible variants such as StarRocks. Pull request courtesy John Bodley.
References: #8588
mssql¶
Fixed yet another regression in SQL Server isolation level fetch (see #8231, #8475), this time with “Microsoft Dynamics CRM Database via Azure Active Directory”, which apparently lacks the
system_views
view entirely. Error catching has been extended that under no circumstances will this method ever fail, provided database connectivity is present.References: #8525
1.4.41¶
Released: September 6, 2022orm¶
Fixed event listening issue where event listeners added to a superclass would be lost if a subclass were created which then had its own listeners associated. The practical example is that of the
sessionmaker
class created after events have been associated with theSession
class.References: #8467
Hardened the cache key strategy for the
aliased()
andwith_polymorphic()
constructs. While no issue involving actual statements being cached can easily be demonstrated (if at all), these two constructs were not including enough of what makes them unique in their cache keys for caching on the aliased construct alone to be accurate.References: #8401
Fixed regression appearing in the 1.4 series where a joined-inheritance query placed as a subquery within an enclosing query for that same entity would fail to render the JOIN correctly for the inner query. The issue manifested in two different ways prior and subsequent to version 1.4.18 (related issue #6595), in one case rendering JOIN twice, in the other losing the JOIN entirely. To resolve, the conditions under which “polymorphic loading” are applied have been scaled back to not be invoked for simple joined inheritance queries.
References: #8456
Fixed issue in
sqlalchemy.ext.mutable
extension where collection links to the parent object would be lost if the object were merged withSession.merge()
while also passingSession.merge.load
as False.References: #8446
Fixed issue involving
with_loader_criteria()
where a closure variable used as bound parameter value within the lambda would not carry forward correctly into additional relationship loaders such asselectinload()
andlazyload()
after the statement were cached, using the stale originally-cached value instead.References: #8399
sql¶
Fixed issue where use of the
table()
construct, passing a string for thetable.schema
parameter, would fail to take the “schema” string into account when producing a cache key, thus leading to caching collisions if multiple, same-namedtable()
constructs with different schemas were used.References: #8441
asyncio¶
Integrated support for asyncpg’s
terminate()
method call for cases where the connection pool is recycling a possibly timed-out connection, where a connection is being garbage collected that wasn’t gracefully closed, as well as when the connection has been invalidated. This allows asyncpg to abandon the connection without waiting for a response that may incur long timeouts.References: #8419
mssql¶
1.4.40¶
Released: August 8, 2022orm¶
Fixed issue where referencing a CTE multiple times in conjunction with a polymorphic SELECT could result in multiple “clones” of the same CTE being constructed, which would then trigger these two CTEs as duplicates. To resolve, the two CTEs are deep-compared when this occurs to ensure that they are equivalent, then are treated as equivalent.
References: #8357
A
select()
construct that is passed a sole ‘*’ argument forSELECT *
, either via string,text()
, orliteral_column()
, will be interpreted as a Core-level SQL statement rather than as an ORM level statement. This is so that the*
, when expanded to match any number of columns, will result in all columns returned in the result. the ORM- level interpretation ofselect()
needs to know the names and types of all ORM columns up front which can’t be achieved when'*'
is used.If
'*
is used amongst other expressions simultaneously with an ORM statement, an error is raised as this can’t be interpreted correctly by the ORM.References: #8235
orm declarative¶
Fixed issue where a hierarchy of classes set up as an abstract or mixin declarative classes could not declare standalone columns on a superclass that would then be copied correctly to a
declared_attr
callable that wanted to make use of them on a descendant class.References: #8190
engine¶
Implemented new
Connection.execution_options.yield_per
execution option forConnection
in Core, to mirror that of the same yield_per option available in the ORM. The option sets both theConnection.execution_options.stream_results
option at the same time as invokingResult.yield_per()
, to provide the most common streaming result configuration which also mirrors that of the ORM use case in its usage pattern.See also
Using Server Side Cursors (a.k.a. stream results) - revised documentation
Fixed bug in
Result
where the usage of a buffered result strategy would not be used if the dialect in use did not support an explicit “server side cursor” setting, when usingConnection.execution_options.stream_results
. This is in error as DBAPIs such as that of SQLite and Oracle already use a non-buffered result fetching scheme, which still benefits from usage of partial result fetching. The “buffered” strategy is now used in all cases whereConnection.execution_options.stream_results
is set.Added
FilterResult.yield_per()
so that result implementations such asMappingResult
,ScalarResult
andAsyncResult
have access to this method.References: #8199
sql¶
Adjusted the SQL compilation for string containment functions
.contains()
,.startswith()
,.endswith()
to force the use of the string concatenation operator, rather than relying upon the overload of the addition operator, so that non-standard use of these operators with for example bytestrings still produces string concatenation operators.References: #8253
mypy¶
Fixed a crash of the mypy plugin when using a lambda as a Column default. Pull request courtesy of tchapi.
References: #8196
asyncio¶
Added
asyncio.shield()
to the connection and session release process specifically within the__aexit__()
context manager exit, when usingAsyncConnection
orAsyncSession
as a context manager that releases the object when the context manager is complete. This appears to help with task cancellation when using alternate concurrency libraries such asanyio
,uvloop
that otherwise don’t provide an async context for the connection pool to release the connection properly during task cancellation.References: #8145
postgresql¶
Fixed issue in psycopg2 dialect where the “multiple hosts” feature implemented for #4392, where multiple
host:port
pairs could be passed in the query string as?host=host1:port1&host=host2:port2&host=host3:port3
was not implemented correctly, as it did not propagate the “port” parameter appropriately. Connections that didn’t use a different “port” likely worked without issue, and connections that had “port” for some of the entries may have incorrectly passed on that hostname. The format is now corrected to pass hosts/ports appropriately.As part of this change, maintained support for another multihost style that worked unintentionally, which is comma-separated
?host=h1,h2,h3&port=p1,p2,p3
. This format is more consistent with libpq’s query-string format, whereas the previous format is inspired by a different aspect of libpq’s URI format but is not quite the same thing.If the two styles are mixed together, an error is raised as this is ambiguous.
References: #4392
mssql¶
Fixed issues that prevented the new usage patterns for using DML with ORM objects presented at Using INSERT, UPDATE and ON CONFLICT (i.e. upsert) to return ORM Objects from working correctly with the SQL Server pyodbc dialect.
References: #8210
Fixed issue where the SQL Server dialect’s query for the current isolation level would fail on Azure Synapse Analytics, due to the way in which this database handles transaction rollbacks after an error has occurred. The initial query has been modified to no longer rely upon catching an error when attempting to detect the appropriate system view. Additionally, to better support this database’s very specific “rollback” behavior, implemented new parameter
ignore_no_transaction_on_rollback
indicating that a rollback should ignore Azure Synapse error ‘No corresponding transaction found. (111214)’, which is raised if no transaction is present in conflict with the Python DBAPI.Initial patch and valuable debugging assistance courtesy of @ww2406.
References: #8231
misc¶
Fixed issue where
TypeDecorator
would not correctly proxy the__getitem__()
operator when decorating theARRAY
datatype, without explicit workarounds.References: #7249
1.4.39¶
Released: June 24, 2022orm¶
Fixed regression caused by #8133 where the pickle format for mutable attributes was changed, without a fallback to recognize the old format, causing in-place upgrades of SQLAlchemy to no longer be able to read pickled data from previous versions. A check plus a fallback for the old format is now in place.
References: #8133
1.4.38¶
Released: June 23, 2022orm¶
Fixed regression caused by #8064 where a particular check for column correspondence was made too liberal, resulting in incorrect rendering for some ORM subqueries such as those using
PropComparator.has()
orPropComparator.any()
in conjunction with joined-inheritance queries that also use legacy aliasing features.References: #8162
Fixed an issue where
GenerativeSelect.fetch()
would not be applied when executing a statement using the ORM.References: #8091
Fixed issue where a
with_loader_criteria()
option could not be pickled, as is necessary when it is carried along for propagation to lazy loaders in conjunction with a caching scheme. Currently, the only form that is supported as picklable is to pass the “where criteria” as a fixed module-level callable function that produces a SQL expression. An ad-hoc “lambda” can’t be pickled, and a SQL expression object is usually not fully picklable directly.References: #8109
engine¶
Repaired a deprecation warning class decorator that was preventing key objects such as
Connection
from having a proper__weakref__
attribute, causing operations like Python standard libraryinspect.getmembers()
to fail.References: #8115
sql¶
Fixed multiple observed race conditions related to
lambda_stmt()
, including an initial “dogpile” issue when a new Python code object is initially analyzed among multiple simultaneous threads which created both a performance issue as well as some internal corruption of state. Additionally repaired observed race condition which could occur when “cloning” an expression construct that is also in the process of being compiled or otherwise accessed in a different thread due to memoized attributes altering the__dict__
while iterated, for Python versions prior to 3.10; in particular the lambda SQL construct is sensitive to this as it holds onto a single statement object persistently. The iteration has been refined to usedict.copy()
with or without an additional iteration instead.References: #8098
Enhanced the mechanism of
Cast
and other “wrapping” column constructs to more fully preserve a wrappedLabel
construct, including that the label name will be preserved in the.c
collection of aSubquery
. The label was already able to render in the SQL correctly on the outside of the construct which it was wrapped inside.References: #8084
Adjusted the fix made for #8056 which adjusted the escaping of bound parameter names with special characters such that the escaped names were translated after the SQL compilation step, which broke a published recipe on the FAQ illustrating how to merge parameter names into the string output of a compiled SQL string. The change restores the escaped names that come from
compiled.params
and adds a conditional parameter toSQLCompiler.construct_params()
namedescape_names
that defaults toTrue
, restoring the old behavior by default.References: #8113
schema¶
Fixed bugs involving the
Table.include_columns
and theTable.resolve_fks
parameters onTable
; these little-used parameters were apparently not working for columns that refer to foreign key constraints.In the first case, not-included columns that refer to foreign keys would still attempt to create a
ForeignKey
object, producing errors when attempting to resolve the columns for the foreign key constraint within reflection; foreign key constraints that refer to skipped columns are now omitted from the table reflection process in the same way as occurs forIndex
andUniqueConstraint
objects with the same conditions. No warning is produced however, as we likely want to remove the include_columns warnings for all constraints in 2.0.In the latter case, the production of table aliases or subqueries would fail on an FK related table not found despite the presence of
resolve_fks=False
; the logic has been repaired so that if a related table is not found, theForeignKey
object is still proxied to the aliased table or subquery (theseForeignKey
objects are normally used in the production of join conditions), but it is sent with a flag that it’s not resolvable. The aliased table / subquery will then work normally, with the exception that it cannot be used to generate a join condition automatically, as the foreign key information is missing. This was already the behavior for such foreign key constraints produced using non-reflection methods, such as joiningTable
objects from differentMetaData
collections.Fixed issue where
Table
objects that made use of IDENTITY columns with aNumeric
datatype would produce errors when attempting to reconcile the “autoincrement” column, preventing construction of theColumn
from using theColumn.autoincrement
parameter as well as emitting errors when attempting to invoke anInsert
construct.References: #8111
extensions¶
1.4.37¶
Released: May 31, 2022orm¶
Fixed issue where using a
column_property()
construct containing a subquery against an already-mapped column attribute would not correctly apply ORM-compilation behaviors to the subquery, including that the “IN” expression added for a single-table inherits expression would fail to be included.References: #8064
Fixed issue where ORM results would apply incorrect key names to the returned
Row
objects in the case where the set of columns to be selected were changed, such as when usingSelect.with_only_columns()
.References: #8001
Fixed bug, likely a regression from 1.3, where usage of column names that require bound parameter escaping, more concretely when using Oracle with column names that require quoting such as those that start with an underscore, or in less common cases with some PostgreSQL drivers when using column names that contain percent signs, would cause the ORM versioning feature to not work correctly if the versioning column itself had such a name, as the ORM assumes certain bound parameter naming conventions that were being interfered with via the quotes. This issue is related to #8053 and essentially revises the approach towards fixing this, revising the original issue #5653 that created the initial implementation for generalized bound-parameter name quoting.
References: #8056
engine¶
sql¶
Fixed bug where the PostgreSQL
Insert.on_conflict_do_update()
method and the SQLiteInsert.on_conflict_do_update()
method would both fail to correctly accommodate a column with a separate “.key” when specifying the column using its key name in the dictionary passed toInsert.on_conflict_do_update.set_
, as well as if theInsert.excluded
collection were used as the dictionary directly.References: #8014
An informative error is raised for the use case where
Insert.from_select()
is being passed a “compound select” object such as a UNION, yet the INSERT statement needs to append additional columns to support Python-side or explicit SQL defaults from the table metadata. In this case a subquery of the compound object should be passed.References: #8073
Fixed an issue where using
bindparam()
with no explicit data or type given could be coerced into the incorrect type when used in expressions such as when usingComparator.any()
andComparator.all()
.References: #7979
An informative error is raised if two individual
BindParameter
objects share the same name, yet one is used within an “expanding” context (typically an IN expression) and the other is not; mixing the same name in these two different styles of usage is not supported and typically theexpanding=True
parameter should be set on the parameters that are to receive list values outside of IN expressions (whereexpanding
is set by default).References: #8018
mysql¶
Further adjustments to the MySQL PyODBC dialect to allow for complete connectivity, which was previously still not working despite fixes in #7871.
References: #7966
Added disconnect code for MySQL error 4031, introduced in MySQL >= 8.0.24, indicating connection idle timeout exceeded. In particular this repairs an issue where pre-ping could not reconnect on a timed-out connection. Pull request courtesy valievkarim.
References: #8036
mssql¶
oracle¶
Added two new error codes for Oracle disconnect handling to support early testing of the new “python-oracledb” driver released by Oracle.
References: #8066
Fixed SQL compiler issue where the “bind processing” function for a bound parameter would not be correctly applied to a bound value if the bound parameter’s name were “escaped”. Concretely, this applies, among other cases, to Oracle when a
Column
has a name that itself requires quoting, such that the quoting-required name is then used for the bound parameters generated within DML statements, and the datatype in use requires bind processing, such as theEnum
datatype.References: #8053
1.4.36¶
Released: April 26, 2022orm¶
Fixed regression where the change made for #7861, released in version 1.4.33, that brought the
Insert
construct to be partially recognized as an ORM-enabled statement did not properly transfer the correct mapper / mapped table state to theSession
, causing theSession.get_bind()
method to fail for aSession
that was bound to engines and/or connections using theSession.binds
parameter.References: #7936
orm declarative¶
Modified the
DeclarativeMeta
metaclass to passcls.__dict__
into the declarative scanning process to look for attributes, rather than the separate dictionary passed to the type’s__init__()
method. This allows user-defined base classes that add attributes within an__init_subclass__()
to work as expected, as__init_subclass__()
can only affect thecls.__dict__
itself and not the other dictionary. This is technically a regression from 1.3 where__dict__
was being used.References: #7900
engine¶
Fixed a memory leak in the C extensions which could occur when calling upon named members of
Row
when the member does not exist under Python 3; in particular this could occur during NumPy transformations when it attempts to call members such as.__array__
, but the issue was surrounding anyAttributeError
thrown by theRow
object. This issue does not apply to version 2.0 which has already transitioned to Cython. Thanks much to Sebastian Berg for identifying the problem.References: #7875
Added a warning regarding a bug which exists in the
Result.columns()
method when passing 0 for the index in conjunction with aResult
that will return a single ORM entity, which indicates that the current behavior ofResult.columns()
is broken in this case as theResult
object will yield scalar values and notRow
objects. The issue will be fixed in 2.0, which would be a backwards-incompatible change for code that relies on the current broken behavior. Code which wants to receive a collection of scalar values should use theResult.scalars()
method, which will return a newScalarResult
object that yields non-row scalar objects.References: #7953
schema¶
Fixed bug where
ForeignKeyConstraint
naming conventions using thereferred_column_0
naming convention key would not work if the foreign key constraint were set up as aForeignKey
object rather than an explicitForeignKeyConstraint
object. As this change makes use of a backport of some fixes from version 2.0, an additional little-known feature that has likely been broken for many years is also fixed which is that aForeignKey
object may refer to a referred table by name of the table alone without using a column name, if the name of the referent column is the same as that of the referred column.The
referred_column_0
naming convention key was previously not tested with theForeignKey
object, onlyForeignKeyConstraint
, and this bug reveals that the feature has never worked correctly unlessForeignKeyConstraint
is used for all FK constraints. This bug traces back to the original introduction of the feature introduced for #3989.References: #7958
asyncio¶
Repaired handling of
contextvar.ContextVar
objects inside of async adapted event handlers. Previously, values applied to aContextVar
would not be propagated in the specific case of calling upon awaitables inside of non-awaitable code.References: #7937
postgresql¶
Fixed bug in
ARRAY
datatype in combination withEnum
on PostgreSQL where using the.any()
or.all()
methods to render SQL ANY() or ALL(), given members of the Python enumeration as arguments, would produce a type adaptation failure on all drivers.References: #6515
Implemented
UUID.python_type
attribute for the PostgreSQLUUID
type object. The attribute will return eitherstr
oruuid.UUID
based on theUUID.as_uuid
parameter setting. Previously, this attribute was unimplemented. Pull request courtesy Alex Grönholm.References: #7943
Fixed an issue in the psycopg2 dialect when using the
create_engine.pool_pre_ping
parameter which would cause user-configuredAUTOCOMMIT
isolation level to be inadvertently reset by the “ping” handler.References: #7930
mysql¶
tests¶
For third party dialects, repaired a missing requirement for the
SimpleUpdateDeleteTest
suite test which was not checking for a working “rowcount” function on the target dialect.References: #7919
1.4.35¶
Released: April 6, 2022sql¶
Fixed bug in newly implemented
FunctionElement.table_valued.joins_implicitly
feature where the parameter would not automatically propagate from the originalTableValuedAlias
object to the secondary object produced when calling uponTableValuedAlias.render_derived()
orTableValuedAlias.alias()
.Additionally repaired these issues in
TableValuedAlias
:repaired a potential memory issue which could occur when repeatedly calling
TableValuedAlias.render_derived()
against successive copies of the same object (for .alias(), we currently have to still continue chaining from the previous element. not sure if this can be improved but this is standard behavior for .alias() elsewhere)repaired issue where the individual element types would be lost when calling upon
TableValuedAlias.render_derived()
orTableValuedAlias.alias()
.
References: #7890
Fixed regression caused by #7823 which impacted the caching system, such that bound parameters that had been “cloned” within ORM operations, such as polymorphic loading, would in some cases not acquire their correct execution-time value leading to incorrect bind values being rendered.
References: #7903
1.4.34¶
Released: March 31, 2022orm¶
Fixed regression caused by #7861 where invoking an
Insert
construct which contained ORM entities directly viaSession.execute()
would fail.References: #7878
postgresql¶
Scaled back a fix made for #6581 where “executemany values” mode for psycopg2 were disabled for all “ON CONFLICT” styles of INSERT, to not apply to the “ON CONFLICT DO NOTHING” clause, which does not include any parameters and is safe for “executemany values” mode. “ON CONFLICT DO UPDATE” is still blocked from “executemany values” as there may be additional parameters in the DO UPDATE clause that cannot be batched (which is the original issue fixed by #6581).
References: #7880
1.4.33¶
Released: March 31, 2022orm¶
Added
with_polymorphic.adapt_on_names
to thewith_polymorphic()
function, which allows a polymorphic load (typically with concrete mapping) to be stated against an alternative selectable that will adapt to the original mapped selectable on column names alone.References: #7805
Added new attributes
UpdateBase.returning_column_descriptions
andUpdateBase.entity_description
to allow for inspection of ORM attributes and entities that are installed as part of anInsert
,Update
, orDelete
construct. TheSelect.column_descriptions
accessor is also now implemented for Core-only selectables.References: #7861
Fixed regression in “dynamic” loader strategy where the
Query.filter_by()
method would not be given an appropriate entity to filter from, in the case where a “secondary” table were present in the relationship being queried and the mapping were against something complex such as a “with polymorphic”.References: #7868
Fixed bug where
composite()
attributes would not work in conjunction with theselectin_polymorphic()
loader strategy for joined table inheritance.References: #7801
Improvements in memory usage by the ORM, removing a significant set of intermediary expression objects that are typically stored when a copy of an expression object is created. These clones have been greatly reduced, reducing the number of total expression objects stored in memory by ORM mappings by about 30%.
References: #7823
Fixed issue where the
selectin_polymorphic()
loader option would not work with joined inheritance mappers that don’t have a fixed “polymorphic_on” column. Additionally added test support for a wider variety of usage patterns with this construct.References: #7799
Fixed bug in
with_loader_criteria()
function where loader criteria would not be applied to a joined eager load that were invoked within the scope of a refresh operation for the parent object.References: #7862
Fixed issue where the
Mapper
would reduce a user-definedMapper.primary_key
argument too aggressively, in the case of mapping to aUNION
where for some of the SELECT entries, two columns are essentially equivalent, but in another, they are not, such as in a recursive CTE. The logic here has been changed to accept a given user-defined PK as given, where columns will be related to the mapped selectable but no longer “reduced” as this heuristic can’t accommodate for all situations.References: #7842
engine¶
Added new parameter
Engine.dispose.close
, defaulting to True. When False, the engine disposal does not touch the connections in the old pool at all, simply dropping the pool and replacing it. This use case is so that when the original pool is transferred from a parent process, the parent process may continue to use those connections.See also
Using Connection Pools with Multiprocessing or os.fork() - revised documentation
Further clarified connection-level logging to indicate the BEGIN, ROLLBACK and COMMIT log messages do not actually indicate a real transaction when the AUTOCOMMIT isolation level is in use; messaging has been extended to include the BEGIN message itself, and the messaging has also been fixed to accommodate when the
Engine
levelcreate_engine.isolation_level
parameter was used directly.References: #7853
sql¶
Added new parameter
FunctionElement.table_valued.joins_implicitly
, for theFunctionElement.table_valued()
construct. This parameter indicates that the given table-valued function implicitly joins to the table it refers towards, essentially disabling the “from linting” feature, i.e. the “cartesian product” warning, from taking effect due to the presence of this parameter. May be used for functions such asfunc.json_each()
.References: #7845
The
bindparam.literal_execute
parameter now takes part of the cache generation of abindparam()
, since it changes the sql string generated by the compiler. Previously the correct bind values were used, but theliteral_execute
would be ignored on subsequent executions of the same query.References: #7876
Fixed regression caused by #7760 where the new capabilities of
TextualSelect
were not fully implemented within the compiler properly, leading to issues with composed INSERT constructs such as “INSERT FROM SELECT” and “INSERT…ON CONFLICT” when combined with CTE and textual statements.References: #7798
schema¶
Added support so that the
Table.to_metadata.referred_schema_fn
callable passed toTable.to_metadata()
may return the valueBLANK_SCHEMA
to indicate that the referenced foreign key should be reset to None. TheRETAIN_SCHEMA
symbol may also be returned from this function to indicate “no change”, which will behave the same asNone
currently does which also indicates no change.References: #7860
sqlite¶
Fixed bug where the name of CHECK constraints under SQLite would not be reflected if the name were created using quotes, as is the case when the name uses mixed case or special characters.
References: #5463
mssql¶
misc¶
Improved the error message that’s raised for the case where the
association_proxy()
construct attempts to access a target attribute at the class level, and this access fails. The particular use case here is when proxying to a hybrid attribute that does not include a working class-level implementation.References: #7827
1.4.32¶
Released: March 6, 2022orm¶
Fixed regression where the ORM exception that is to be raised when an INSERT silently fails to actually insert a row (such as from a trigger) would not be reached, due to a runtime exception raised ahead of time due to the missing primary key value, thus raising an uninformative exception rather than the correct one. For 1.4 and above, a new
FlushError
is added for this case that’s raised earlier than the previous “null identity” exception was for 1.3, as a situation where the number of rows actually INSERTed does not match what was expected is a more critical situation in 1.4 as it prevents batching of multiple objects from working correctly. This is separate from the case where a newly fetched primary key is fetched as NULL, which continues to raise the existing “null identity” exception.References: #7594
Fixed issue where using a fully qualified path for the classname in
relationship()
that nonetheless contained an incorrect name for path tokens that were not the first token, would fail to raise an informative error and would instead fail randomly at a later step.References: #7697
engine¶
Adjusted the logging for key SQLAlchemy components including
Engine
,Connection
to establish an appropriate stack level parameter, so that the Python logging tokensfuncName
andlineno
when used in custom logging formatters will report the correct information, which can be useful when filtering log output; supported on Python 3.8 and above. Pull request courtesy Markus Gerstel.References: #7612
sql¶
Fixed type-related error messages that would fail for values that were tuples, due to string formatting syntax, including compile of unsupported literal values and invalid boolean values.
References: #7721
Fixed issues in MySQL
SET
datatype as well as the genericEnum
datatype where the__repr__()
method would not render all optional parameters in the string output, impacting the use of these types in Alembic autogenerate. Pull request for MySQL courtesy Yuki Nishimine.The
Enum
datatype now emits a warning if theEnum.length
argument is specified without also specifyingEnum.native_enum
as False, as the parameter is otherwise silently ignored in this case, despite the fact that theEnum
datatype will still render VARCHAR DDL on backends that don’t have a native ENUM datatype such as SQLite. This behavior may change in a future release so that “length” is honored for all non-native “enum” types regardless of the “native_enum” setting.Fixed issue where the
HasCTE.add_cte()
method as called upon aTextualSelect
instance was not being accommodated by the SQL compiler. The fix additionally adds more “SELECT”-like compiler behavior toTextualSelect
including that DML CTEs such as UPDATE and INSERT may be accommodated.References: #7760
asyncio¶
Fixed issues where a descriptive error message was not raised for some classes of event listening with an async engine, which should instead be a sync engine instance.
Fixed issue where the
AsyncSession.execute()
method failed to raise an informative exception if theConnection.execution_options.stream_results
execution option were used, which is incompatible with a sync-styleResult
object when using an asyncio calling style, as the operation to fetch more rows would need to be awaited. An exception is now raised in this scenario in the same way one was already raised when theConnection.execution_options.stream_results
option would be used with theAsyncConnection.execute()
method.Additionally, for improved stability with state-sensitive database drivers such as asyncmy, the cursor is now closed when this error condition is raised; previously with the asyncmy dialect, the connection would go into an invalid state with unconsumed server side results remaining.
References: #7667
postgresql¶
Added compiler support for the PostgreSQL
NOT VALID
phrase when rendering DDL for theCheckConstraint
,ForeignKeyConstraint
andForeignKey
schema constructs. Pull request courtesy Gilbert Gilb’s.See also
References: #7600
mysql¶
Fixed regression caused by #7518 where changing the syntax “SHOW VARIABLES” to “SELECT @@” broke compatibility with MySQL versions older than 5.6, including early 5.0 releases. While these are very old MySQL versions, a change in compatibility was not planned, so version-specific logic has been restored to fall back to “SHOW VARIABLES” for MySQL server versions < 5.6.
References: #7518
mariadb¶
Fixed regression in mariadbconnector dialect as of mariadb connector 1.0.10 where the DBAPI no longer pre-buffers cursor.lastrowid, leading to errors when inserting objects with the ORM as well as causing non-availability of the
CursorResult.inserted_primary_key
attribute. The dialect now fetches this value proactively for situations where it applies.References: #7738
sqlite¶
Added support for reflecting SQLite inline unique constraints where the column names are formatted with SQLite “escape quotes”
[]
or`
, which are discarded by the database when producing the column name.References: #7736
Fixed issue where SQLite unique constraint reflection would fail to detect a column-inline UNIQUE constraint where the column name had an underscore in its name.
References: #7736
oracle¶
Fixed issue in Oracle dialect where using a column name that requires quoting when written as a bound parameter, such as
"_id"
, would not correctly track a Python generated default value due to the bound-parameter rewriting missing this value, causing an Oracle error to be raised.References: #7676
Added support to parse “DPI” error codes from cx_Oracle exception objects such as
DPI-1080
andDPI-1010
, both of which now indicate a disconnect scenario as of cx_Oracle 8.3.References: #7748
tests¶
Improvements to the test suite’s integration with pytest such that the “warnings” plugin, if manually enabled, will not interfere with the test suite, such that third parties can enable the warnings plugin or make use of the
-W
parameter and SQLAlchemy’s test suite will continue to pass. Additionally, modernized the detection of the “pytest-xdist” plugin so that plugins can be globally disabled using PYTEST_DISABLE_PLUGIN_AUTOLOAD=1 without breaking the test suite if xdist were still installed. Warning filters that promote deprecation warnings to errors are now localized to SQLAlchemy-specific warnings, or within SQLAlchemy-specific sources for general Python deprecation warnings, so that non-SQLAlchemy deprecation warnings emitted from pytest plugins should also not impact the test suite.References: #7599
Made corrections to the default pytest configuration regarding how test discovery is configured, to fix issue where the test suite would not configure warnings correctly and also attempt to load example suites as tests, in the specific case where the SQLAlchemy checkout were located in an absolute path that had a super-directory named “test”.
References: #7045
1.4.31¶
Released: January 20, 2022orm¶
Fixed issue in
Session.bulk_save_objects()
where the sorting that takes place when thepreserve_order
parameter is set to False would sort partially onMapper
objects, which is rejected in Python 3.11.References: #7591
postgresql¶
mysql¶
mssql¶
Added support for
FILESTREAM
when usingVARBINARY(max)
in MSSQL.See also
VARBINARY.filestream
References: #7243
1.4.30¶
Released: January 19, 2022orm¶
Fixed issue in joined-inheritance load of additional attributes functionality in deep multi-level inheritance where an intermediary table that contained no columns would not be included in the tables joined, instead linking those tables to their primary key identifiers. While this works fine, it nonetheless in 1.4 began producing the cartesian product compiler warning. The logic has been changed so that these intermediary tables are included regardless. While this does include additional tables in the query that are not technically necessary, this only occurs for the highly unusual case of deep 3+ level inheritance with intermediary tables that have no non primary key columns, potential performance impact is therefore expected to be negligible.
References: #7507
Fixed issue where calling upon
registry.map_imperatively()
more than once for the same class would produce an unexpected error, rather than an informative error that the target class is already mapped. This behavior differed from that of themapper()
function which does report an informative message already.References: #7579
Added missing method
AsyncSession.invalidate()
to theAsyncSession
class.References: #7524
Fixed regression which appeared in 1.4.23 which could cause loader options to be mis-handled in some cases, in particular when using joined table inheritance in combination with the
polymorphic_load="selectin"
option as well as relationship lazy loading, leading to aTypeError
.References: #7557
Fixed ORM regression where calling the
aliased()
function against an existingaliased()
construct would fail to produce correct SQL if the existing construct were against a fixed table. The fix allows that the originalaliased()
construct is disregarded if it were only against a table that’s now being replaced. It also allows for correct behavior when constructing aaliased()
without a selectable argument against aaliased()
that’s against a subuquery, to create an alias of that subquery (i.e. to change its name).The nesting behavior of
aliased()
remains in place for the case where the outeraliased()
object is against a subquery which in turn refers to the inneraliased()
object. This is a relatively new 1.4 feature that helps to suit use cases that were previously served by the deprecatedQuery.from_self()
method.References: #7576
Fixed issue where
Select.correlate_except()
method, when passed either theNone
value or no arguments, would not correlate any elements when used in an ORM context (that is, passing ORM entities as FROM clauses), rather than causing all FROM elements to be considered as “correlated” in the same way which occurs when using Core-only constructs.References: #7514
Fixed regression from 1.3 where the “subqueryload” loader strategy would fail with a stack trace if used against a query that made use of
Query.from_statement()
orSelect.from_statement()
. As subqueryload requires modifying the original statement, it’s not compatible with the “from_statement” use case, especially for statements made against thetext()
construct. The behavior now is equivalent to that of 1.3 and previously, which is that the loader strategy silently degrades to not be used for such statements, typically falling back to using the lazyload strategy.References: #7505
sql¶
Added additional rule to the system that determines
TypeEngine
implementations from Python literals to apply a second level of adjustment to the type, so that a Python datetime with or without tzinfo can set thetimezone=True
parameter on the returnedDateTime
object, as well asTime
. This helps with some round-trip scenarios on type-sensitive PostgreSQL dialects such as asyncpg, psycopg3 (2.0 only).References: #7537
Added an informative error message when a method object is passed to a SQL construct. Previously, when such a callable were passed, as is a common typographical error when dealing with method-chained SQL constructs, they were interpreted as “lambda SQL” targets to be invoked at compilation time, which would lead to silent failures. As this feature was not intended to be used with methods, method objects are now rejected.
References: #7032
mypy¶
Fixed Mypy crash when running id daemon mode caused by a missing attribute on an internal mypy
Var
instance.References: #7321
asyncio¶
Added new method
AdaptedConnection.run_async()
to the DBAPI connection interface used by asyncio drivers, which allows methods to be called against the underlying “driver” connection directly within a sync-style function where theawait
keyword can’t be used, such as within SQLAlchemy event handler functions. The method is analogous to theAsyncConnection.run_sync()
method which translates async-style calls to sync-style. The method is useful for things like connection-pool on-connect handlers that need to invoke awaitable methods on the driver connection when it’s first created.References: #7580
postgresql¶
Added string rendering to the
UUID
datatype, so that stringifying a statement with “literal_binds” that uses this type will render an appropriate string value for the PostgreSQL backend. Pull request courtesy José Duarte.References: #7561
Improved support for asyncpg handling of TIME WITH TIMEZONE, which was not fully implemented.
References: #7537
Fixed reflection of covering indexes to report
include_columns
as part of thedialect_options
entry in the reflected index dictionary, thereby enabling round trips from reflection->create to be complete. Included columns continue to also be present under theinclude_columns
key for backwards compatibility.References: #7382
Fixed handling of array of enum values which require escape characters.
References: #7418
mysql¶
Replace
SHOW VARIABLES LIKE
statement with equivalentSELECT @@variable
in MySQL and MariaDB dialect initialization. This should avoid mutex contention caused bySHOW VARIABLES
, improving initialization performance.References: #7518
Removed unnecessary dependency on PyMySQL from the asyncmy dialect. Pull request courtesy long2ice.
References: #7567
1.4.29¶
Released: December 22, 2021orm¶
Added
Session.get.execution_options
parameter which was previously missing from theSession.get()
method.References: #7410
Fixed issue in new “loader criteria” method
PropComparator.and_()
where usage with a loader strategy likeselectinload()
against a column that was a member of the.c.
collection of a subquery object, where the subquery would be dynamically added to the FROM clause of the statement, would be subject to stale parameter values within the subquery in the SQL statement cache, as the process used by the loader strategy to replace the parameters at execution time would fail to accommodate the subquery when received in this form.References: #7489
Fixed recursion overflow which could occur within ORM statement compilation when using either the
with_loader_criteria()
feature or the thePropComparator.and_()
method within a loader strategy in conjunction with a subquery which referred to the same entity being altered by the criteria option, or loaded by the loader strategy. A check for coming across the same loader criteria option in a recursive fashion has been added to accommodate for this scenario.References: #7491
Fixed issue where the
__class_getitem__()
method of the generated declarative base class byas_declarative()
would lead to inaccessible class attributes such as__table__
, for cases where aGeneric[T]
style typing declaration were used in the class hierarchy. This is in continuation from the basic addition of__class_getitem__()
in #7368. Pull request courtesy Kai Mueller.Fixed caching-related issue where the use of a loader option of the form
lazyload(aliased(A).bs).joinedload(B.cs)
would fail to result in the joinedload being invoked for runs subsequent to the query being cached, due to a mismatch for the options / object path applied to the objects loaded for a query with a lead entity that usedaliased()
.References: #7447
engine¶
Corrected the error message for the
AttributeError
that’s raised when attempting to write to an attribute on theRow
class, which is immutable. The previous message claimed the column didn’t exist which is misleading.References: #7432
Fixed regression in the
make_url()
function used to parse URL strings where the query string parsing would go into a recursion overflow if a Python 2u''
string were used.References: #7446
mypy¶
Fixed mypy regression where the release of mypy 0.930 added additional internal checks to the format of “named types”, requiring that they be fully qualified and locatable. This broke the mypy plugin for SQLAlchemy, raising an assertion error, as there was use of symbols such as
__builtins__
and other un-locatable or unqualified names that previously had not raised any assertions.References: #7496
asyncio¶
Added
async_engine_config()
function to create an async engine from a configuration dict. This otherwise behaves the same asengine_from_config()
.References: #7301
mariadb¶
Corrected the error classes inspected for the “is_disconnect” check for the
mariadbconnector
dialect, which was failing for disconnects that occurred due to common MySQL/MariaDB error codes such as 2006; the DBAPI appears to currently use themariadb.InterfaceError
exception class for disconnect errors such as error code 2006, which has been added to the list of classes checked.References: #7457
tests¶
Fixed a regression in the test suite where the test called
CompareAndCopyTest::test_all_present
would fail on some platforms due to additional testing artifacts being detected. Pull request courtesy Nils Philippsen.References: #7450
1.4.28¶
Released: December 9, 2021platform¶
Python 3.10 has deprecated “distutils” in favor of explicit use of “setuptools” in PEP 632; SQLAlchemy’s setup.py has replaced imports accordingly. However, since setuptools itself only recently added the replacement symbols mentioned in pep-632 as of November of 2021 in version 59.0.1,
setup.py
still has fallback imports to distutils, as SQLAlchemy 1.4 does not have a hard setuptools versioning requirement at this time. SQLAlchemy 2.0 is expected to use a full PEP 517 installation layout which will indicate appropriate setuptools versioning up front.References: #7311
orm¶
Fixed issue where the internal cloning used by the
PropComparator.any()
method on arelationship()
in the case where the related class also makes use of ORM polymorphic loading, would fail if a hybrid property on the related, polymorphic class were used within the criteria for theany()
operation.References: #7425
Fixed issue where the
as_declarative()
decorator and similar functions used to generate the declarative base class would not copy the__class_getitem__()
method from a given superclass, which prevented the use of pep-484 generics in conjunction with theBase
class. Pull request courtesy Kai Mueller.References: #7368
Fixed ORM regression where the new behavior of “eager loaders run on unexpire” added in #1763 would lead to loader option errors being raised inappropriately for the case where a single
Query
orSelect
were used to load multiple kinds of entities, along with loader options that apply to just one of those kinds of entity like ajoinedload()
, and later the objects would be refreshed from expiration, where the loader options would attempt to be applied to the mismatched object type and then raise an exception. The check for this mismatch now bypasses raising an error for this case.References: #7318
User defined ORM options, such as those illustrated in the dogpile.caching example which subclass
UserDefinedOption
, by definition are handled on every statement execution and do not need to be considered as part of the cache key for the statement. Caching of the baseExecutableOption
class has been modified so that it is no longer aHasCacheKey
subclass directly, so that the presence of user defined option objects will not have the unwanted side effect of disabling statement caching. Only ORM specific loader and criteria options, which are all internal to SQLAlchemy, now participate within the caching system.References: #7394
Fixed issue where mappings that made use of
synonym()
and potentially other kinds of “proxy” attributes would not in all cases successfully generate a cache key for their SQL statements, leading to degraded performance for those statements.References: #7394
Fixed issue where a list mapped with
relationship()
would go into an endless loop if in-place added to itself, i.e. the+=
operator were used, as well as if.extend()
were given the same list.References: #7389
Fixed issue where if an exception occurred when the
Session
were to close the connection within theSession.commit()
method, when using a context manager forSession.begin()
, it would attempt a rollback which would not be possible as theSession
was in between where the transaction is committed and the connection is then to be returned to the pool, raising the exception “this sessiontransaction is in the committed state”. This exception can occur mostly in an asyncio context where CancelledError can be raised.References: #7388
Deprecated an undocumented loader option syntax
".*"
, which appears to be no different than passing a single asterisk, and will emit a deprecation warning if used. This syntax may have been intended for something but there is currently no need for it.References: #4390
engine¶
sql¶
”Compound select” methods like
Select.union()
,Select.intersect_all()
etc. now accept*other
as an argument rather thanother
to allow for multiple additional SELECTs to be compounded with the parent statement at once. In particular, the change as applied toCTE.union()
andCTE.union_all()
now allow for a so-called “non-linear CTE” to be created with theCTE
construct, whereas previously there was no way to have more than two CTE sub-elements in a UNION together while still correctly calling upon the CTE in recursive fashion. Pull request courtesy Eric Masseran.References: #7259
Support multiple clause elements in the
Exists.where()
method, unifying the api with the one presented by a normalselect()
construct.References: #7386
Extended the
TypeDecorator.cache_ok
attribute and corresponding warning message if this flag is not defined, a behavior first established forTypeDecorator
as part of #6436, to also take place forUserDefinedType
, by generalizing the flag and associated caching logic to a new common base for these two types,ExternalType
to createUserDefinedType.cache_ok
.The change means any current
UserDefinedType
will now cause SQL statement caching to no longer take place for statements which make use of the datatype, along with a warning being emitted, unless the class defines theUserDefinedType.cache_ok
flag as True. If the datatype cannot form a deterministic, hashable cache key derived from its arguments, the attribute may be set to False which will continue to keep caching disabled but will suppress the warning. In particular, custom datatypes currently used in packages such as SQLAlchemy-utils will need to implement this flag. The issue was observed as a result of a SQLAlchemy-utils datatype that is not currently cacheable.See also
References: #7319
Custom SQL elements, third party dialects, custom or third party datatypes will all generate consistent warnings when they do not clearly opt in or out of SQL statement caching, which is achieved by setting the appropriate attributes on each type of class. The warning links to documentation sections which indicate the appropriate approach for each type of object in order for caching to be enabled.
References: #7394
Fixed missing caching directives for a few lesser used classes in SQL Core which would cause
[no key]
to be logged for elements which made use of these.References: #7394
mypy¶
Fixed Mypy crash which would occur when using Mypy plugin against code which made use of
declared_attr
methods for non-mapped names like__mapper_args__
,__table_args__
, or other dunder names, as the plugin would try to interpret these as mapped attributes which would then be later mis-handled. As part of this change, the decorated function is still converted by the plugin into a generic assignment statement (e.g.__mapper_args__: Any
) so that the argument signature can continue to be annotated in the same way one would for any other@classmethod
without Mypy complaining about the wrong argument type for a method that isn’t explicitly@classmethod
.References: #7321
postgresql¶
tests¶
Implemented support for the test suite to run correctly under Pytest 7. Previously, only Pytest 6.x was supported for Python 3, however the version was not pinned on the upper bound in tox.ini. Pytest is not pinned in tox.ini to be lower than version 8 so that SQLAlchemy versions released with the current codebase will be able to be tested under tox without changes to the environment. Much thanks to the Pytest developers for their help with this issue.
1.4.27¶
Released: November 11, 2021orm¶
Fixed bug in “relationship to aliased class” feature introduced at Relationship to Aliased Class where it was not possible to create a loader strategy option targeting an attribute on the target using the
aliased()
construct directly in a second loader option, such asselectinload(A.aliased_bs).joinedload(aliased_b.cs)
, without explicitly qualifying usingPropComparator.of_type()
on the preceding element of the path. Additionally, targeting the non-aliased class directly would be accepted (inappropriately), but would silently fail, such asselectinload(A.aliased_bs).joinedload(B.cs)
; this now raises an error referring to the typing mismatch.References: #7224
All
Result
objects will now consistently raiseResourceClosedError
if they are used after a hard close, which includes the “hard close” that occurs after calling “single row or value” methods likeResult.first()
andResult.scalar()
. This was already the behavior of the most common class of result objects returned for Core statement executions, i.e. those based onCursorResult
, 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 baseResult
class and implemented it for the filtered result implementations that are used by the ORM, so that it is possible to call theCursorResult.close()
method on the underlyingCursorResult
when the theyield_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.References: #7274
Fixed 1.4 regression where
Query.filter_by()
would not function correctly on aQuery
that was produced fromQuery.union()
,Query.from_self()
or similar.References: #7239
Fixed issue where deferred polymorphic loading of attributes from a joined-table inheritance subclass would fail to populate the attribute correctly if the
load_only()
option were used to originally exclude that attribute, in the case where the load_only were descending from a relationship loader option. The fix allows that other valid options such asdefer(..., raiseload=True)
etc. still function as expected.References: #7304
Fixed 1.4 regression where
Query.filter_by()
would not function correctly whenQuery.join()
were joined to an entity which made use ofPropComparator.of_type()
to specify an aliased version of the target entity. The issue also applies to future style ORM queries constructed withselect()
.References: #7244
engine¶
Fixed issue in future
Connection
object where theConnection.execute()
method would not accept a non-dict mapping object, such as SQLAlchemy’s ownRowMapping
or otherabc.collections.Mapping
object as a parameter dictionary.References: #7291
Fixed regression where the
CursorResult.fetchmany()
method would fail to autoclose a server-side cursor (i.e. whenstream_results
oryield_per
is in use, either Core or ORM oriented results) when the results were fully exhausted.References: #7274
Fixed issue in future
Engine
where calling uponEngine.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 handlebegin()
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.References: #7272
sql¶
Added
TupleType
to the top levelsqlalchemy
import namespace.Fixed regression where the row objects returned for ORM queries, which are now the normal
Row
objects, would not be interpreted by theColumnOperators.in_()
operator as tuple values to be broken out into individual bound parameters, and would instead pass them as single values to the driver leading to failures. The change to the “expanding IN” system now accommodates for the expression already being of typeTupleType
and treats values accordingly if so. In the uncommon case of using “tuple-in” with an untyped statement such as a textual statement with no typing information, a tuple value is detected for values that implementcollections.abc.Sequence
, but that are notstr
orbytes
, as always when testing forSequence
.References: #7292
Fixed issue where using the feature of using a string label for ordering or grouping described at Ordering or Grouping by a Label would fail to function correctly if used on a
CTE
construct, when the CTE were embedded inside of an enclosingSelect
statement that itself was set up as a scalar subquery.References: #7269
Fixed regression where the
text()
construct would no longer be accepted as a target case in the “whens” list within acase()
construct. The regression appears related to an attempt to guard against some forms of literal values that were considered to be ambiguous when passed here; however, there’s no reason the target cases shouldn’t be interpreted as open-ended SQL expressions just like anywhere else, and a literal string or tuple will be converted to a bound parameter as would be the case elsewhere.References: #7287
schema¶
Fixed issue in
Table
where theTable.implicit_returning
parameter would not be accommodated correctly when passed along withTable.extend_existing
to augment an existingTable
.References: #7295
postgresql¶
Added overridable methods
PGDialect_asyncpg.setup_asyncpg_json_codec
andPGDialect_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.References: #7284
Changed the asyncpg dialect to bind the
Float
type to the “float” PostgreSQL type instead of “numeric” so that the valuefloat(inf)
can be accommodated. Added test suite support for persistence of the “inf” value.References: #7283
Improve array handling when using PostgreSQL with the pg8000 dialect.
References: #7167
mysql¶
Reorganized the list of reserved words into two separate lists, one for MySQL and one for MariaDB, so that these diverging sets of words can be managed more accurately; adjusted the MySQL/MariaDB dialect to switch among these lists based on either explicitly configured or server-version-detected “MySQL” or “MariaDB” backend. Added all current reserved words through MySQL 8 and current MariaDB versions including recently added keywords like “lead” . Pull request courtesy Kevin Kirsche.
References: #7167
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.References: #7281
mssql¶
Adjusted the compiler’s generation of “post compile” symbols including those used for “expanding IN” as well as for the “schema translate map” to not be based directly on plain bracketed strings with underscores, as this conflicts directly with SQL Server’s quoting format of also using brackets, which produces false matches when the compiler replaces “post compile” and “schema translate” symbols. The issue created easy to reproduce examples both with the
Inspector.get_schema_names()
method when used in conjunction with theConnection.execution_options.schema_translate_map
feature, as well in the unlikely case that a symbol overlapping with the internal name “POSTCOMPILE” would be used with a feature like “expanding in”.References: #7300
1.4.26¶
Released: October 19, 2021orm¶
Improved the exception message generated when configuring a mapping with joined table inheritance where the two tables either have no foreign key relationships set up, or where they have multiple foreign key relationships set up. The message is now ORM specific and includes context that the
Mapper.inherit_condition
parameter may be needed particularly for the ambiguous foreign keys case.Fixed issue with
with_loader_criteria()
feature where ON criteria would not be added to a JOIN for a query of the formselect(A).join(B)
, stating a target while making use of an implicit ON clause.References: #7189
Fixed bug where the ORM “plugin”, necessary for features such as
with_loader_criteria()
to work correctly, would not be applied to aselect()
which queried from an ORM column expression if it made use of theColumnElement.label()
modifier.References: #7205
Add missing methods added in #6991 to
scoped_session
andasync_scoped_session()
.References: #7103
An extra layer of warning messages has been added to the functionality of
Query.join()
and the ORM version ofSelect.join()
, where a few places where “automatic aliasing” continues to occur will now be called out as a pattern to avoid, mostly specific to the area of joined table inheritance where classes that share common base tables are being joined together without using explicit aliases. One case emits a legacy warning for a pattern that’s not recommended, the other case is fully deprecated.The automatic aliasing within ORM join() which occurs for overlapping mapped tables does not work consistently with all APIs such as
contains_eager()
, and rather than continue to try to make these use cases work everywhere, replacing with a more user-explicit pattern is clearer, less prone to bugs and simplifies SQLAlchemy’s internals further.The warnings include links to the errors.rst page where each pattern is demonstrated along with the recommended pattern to fix.
Fixed bug where iterating a
Result
from aSession
after thatSession
were closed would partially attach objects to that session in an essentially invalid state. It now raises an exception with a link to new documentation if an un-buffered result is iterated from aSession
that was closed or otherwise had theSession.expunge_all()
method called after thatResult
was generated. Theprebuffer_rows
execution option, as is used automatically by the asyncio extension for client-side result sets, may be used to produce aResult
where the ORM objects are prebuffered, and in this case iterating the result will produce a series of detached objects.References: #7128
Related to #7153, fixed an issue where result column lookups would fail for “adapted” SELECT statements that selected for “constant” value expressions most typically the NULL expression, as would occur in such places as joined eager loading in conjunction with limit/offset. This was overall a regression due to issue #6259 which removed all “adaption” for constants like NULL, “true”, and “false” when rewriting expressions in a SQL statement, but this broke the case where the same adaption logic were used to resolve the constant to a labeled expression for the purposes of result set targeting.
References: #7154
Fixed regression where ORM loaded objects could not be pickled in cases where loader options making use of
"*"
were used in certain combinations, such as combining thejoinedload()
loader strategy withraiseload('*')
of sub-elements.References: #7134
Fixed regression where the use of a
hybrid_property
attribute or a mappedcomposite()
attribute as a key passed to theUpdate.values()
method for an ORM-enabledUpdate
statement, as well as when using it via the legacyQuery.update()
method, would be processed for incoming ORM/hybrid/composite values within the compilation stage of the UPDATE statement, which meant that in those cases where caching occurred, subsequent invocations of the same statement would no longer receive the correct values. This would include not only hybrids that use thehybrid_property.update_expression()
method, but any use of a plain hybrid attribute as well. For composites, the issue instead caused a non-repeatable cache key to be generated, which would break caching and could fill up the statement cache with repeated statements.The
Update
construct now handles the processing of key/value pairs passed toUpdate.values()
andUpdate.ordered_values()
up front when the construct is first generated, before the cache key has been generated so that the key/value pairs are processed each time, and so that the cache key is generated against the individual column/value pairs that will ultimately be used in the statement.References: #7209
Passing a
Query
object toSession.execute()
is not the intended use of this object, and will now raise a deprecation warning.References: #6284
examples¶
Repaired the examples in examples/versioned_rows to use SQLAlchemy 1.4 APIs correctly; these examples had been missed when API changes like removing “passive” from
Session.is_modified()
were made as well as theSessionEvents.do_orm_execute()
event hook were added.References: #7169
engine¶
Fixed issue where the deprecation warning for the
URL
constructor which indicates that theURL.create()
method should be used would not emit if a full positional argument list of seven arguments were passed; additionally, validation of URL arguments will now occur if the constructor is called in this way, which was being skipped previously.References: #7130
The
Inspector.reflect_table()
method now supports reflecting tables that do not have user defined columns. This allowsMetaData.reflect()
to properly complete reflection on databases that contain such tables. Currently, only PostgreSQL is known to support such a construct among the common database backends.References: #3247
Implemented proper
__reduce__()
methods for all SQLAlchemy exception objects to ensure they all support clean round trips when pickling, as exception objects are often serialized for the purposes of various debugging tools.References: #7077
sql¶
Fixed issue where SQL queries using the
FunctionElement.within_group()
construct could not be pickled, typically when using thesqlalchemy.ext.serializer
extension but also for general generic pickling.References: #6520
Repaired issue in new
HasCTE.cte.nesting
parameter introduced with #4123 where a recursiveCTE
usingHasCTE.cte.recursive
in typical conjunction with UNION would not compile correctly. Additionally makes some adjustments so that theCTE
construct creates a correct cache key. Pull request courtesy Eric Masseran.References: #4123
Account for the
table.schema
parameter passed to thetable()
construct, such that it is taken into account when accessing theTableClause.fullname
attribute.References: #7061
Fixed an inconsistency in the
ColumnOperators.any_()
/ColumnOperators.all_()
functions / methods where the special behavior these functions have of “flipping” the expression such that the “ANY” / “ALL” expression is always on the right side would not function if the comparison were against the None value, that is, “column.any_() == None” should produce the same SQL expression as “null() == column.any_()”. Added more docs to clarify this as well, plus mentions that any_() / all_() generally supersede the ARRAY version “any()” / “all()”.References: #7140
Fixed issue where “expanding IN” would fail to function correctly with datatypes that use the
TypeEngine.bind_expression()
method, where the method would need to be applied to each element of the IN expression rather than the overall IN expression itself.References: #7177
Adjusted the “column disambiguation” logic that’s new in 1.4, where the same expression repeated gets an “extra anonymous” label, so that the logic more aggressively deduplicates those labels when the repeated element is the same Python expression object each time, as occurs in cases like when using “singleton” values like
null()
. This is based on the observation that at least some databases (e.g. MySQL, but not SQLite) will raise an error if the same label is repeated inside of a subquery.References: #7153
mypy¶
Fixed issue in mypy plugin to improve upon some issues detecting
Enum()
SQL types containing custom Python enumeration classes. Pull request courtesy Hiroshi Ogawa.References: #6435
postgresql¶
Added a “disconnect” condition for the “SSL SYSCALL error: Bad address” error message as reported by psycopg2. Pull request courtesy Zeke Brechtel.
References: #5387
Fixed issue where IN expressions against a series of array elements, as can be done with PostgreSQL, would fail to function correctly due to multiple issues within the “expanding IN” feature of SQLAlchemy Core that was standardized in version 1.4. The psycopg2 dialect now makes use of the
TypeEngine.bind_expression()
method withARRAY
to portably apply the correct casts to elements. The asyncpg dialect was not affected by this issue as it applies bind-level casts at the driver level rather than at the compiler level.References: #7177
mysql¶
Fixes to accommodate for the MariaDB 10.6 series, including backwards incompatible changes in both the mariadb-connector Python driver (supported on SQLAlchemy 1.4 only) as well as the native 10.6 client libraries that are used automatically by the mysqlclient DBAPI (applies to both 1.3 and 1.4). The “utf8mb3” encoding symbol is now reported by these client libraries when the encoding is stated as “utf8”, leading to lookup and encoding errors within the MySQL dialect that does not expect this symbol. Updates to both the MySQL base library to accommodate for this utf8mb3 symbol being reported as well as to the test suite. Thanks to Georg Richter for support.
This change is also backported to: 1.3.25
Fixed issue in MySQL
match()
construct where passing a clause expression such asbindparam()
or other SQL expression for the “against” parameter would fail. Pull request courtesy Anton Kovalevich.References: #7144
Fixed installation issue where the
sqlalchemy.dialects.mysql
module would not be importable if “greenlet” were not installed.References: #7204
mssql¶
Added reflection support for SQL Server foreign key options, including “ON UPDATE” and “ON DELETE” values of “CASCADE” and “SET NULL”.
Fixed issue with
Inspector.get_foreign_keys()
where foreign keys were omitted if they were established against a unique index instead of a unique constraint.References: #7160
Fixed issue with
Inspector.has_table()
where it would return False if a local temp table with the same name from a different session happened to be returned first when querying tempdb. This is a continuation of #6910 which accounted for the temp table existing only in the alternate session and not the current one.References: #7168
Fixed bug in SQL Server
DATETIMEOFFSET
datatype where the ODBC implementation would not generate the correct DDL, for cases where the type were converted using thedialect.type_descriptor()
method, the usage of which is illustrated in some documented examples forTypeDecorator
, though not necessary for most datatypes. Regression was introduced by #6366. As part of this change, the full list of SQL Server date types have been amended to return a “dialect impl” that generates the same DDL name as the supertype.References: #7129
1.4.25¶
Released: September 22, 2021platform¶
1.4.24¶
Released: September 22, 2021platform¶
Further adjusted the “greenlet” package specifier in setup.cfg to use a long chain of “or” expressions, so that the comparison of
platform_machine
to a specific identifier matches only the complete string.References: #7024
orm¶
Added loader options to
Session.merge()
andAsyncSession.merge()
via a newSession.merge.options
parameter, which will apply the given loader options to theget()
used internally by merge, allowing eager loading of relationships etc. to be applied when the merge process loads a new object. Pull request courtesy Daniel Stone.References: #6955
Fixed ORM issue where column expressions passed to
query()
or ORM-enabledselect()
would be deduplicated on the identity of the object, such as a phrase likeselect(A.id, null(), null())
would produce only one “NULL” expression, which previously was not the case in 1.3. However, the change also allows for ORM expressions to render as given as well, such asselect(A.data, A.data)
will produce a result row with two columns.References: #6979
Fixed issue in recently repaired
Query.with_entities()
method where the flag that determines automatic uniquing for legacy ORMQuery
objects only would be set toTrue
inappropriately in cases where thewith_entities()
call would be setting theQuery
to return column-only rows, which are not uniqued.References: #6924
engine¶
Improve the interface used by adapted drivers, like the asyncio ones, to access the actual connection object returned by the driver.
The
_ConnectionFairy
object has two new attributes:_ConnectionFairy.dbapi_connection
always represents a DBAPI compatible object. For pep-249 drivers, this is the DBAPI connection as it always has been, previously accessed under the.connection
attribute. For asyncio drivers that SQLAlchemy adapts into a pep-249 interface, the returned object will normally be a SQLAlchemy adaption object calledAdaptedConnection
._ConnectionFairy.driver_connection
always represents the actual connection object maintained by the third party pep-249 DBAPI or async driver in use. For standard pep-249 DBAPIs, this will always be the same object as that of thedbapi_connection
. For an asyncio driver, it will be the underlying asyncio-only connection object.
The
.connection
attribute remains available and is now a legacy alias of.dbapi_connection
.References: #6832
Added new methods
Session.scalars()
,Connection.scalars()
,AsyncSession.scalars()
andAsyncSession.stream_scalars()
, which provide a short cut to the use case of receiving a row-orientedResult
object and converting it to aScalarResult
object via theResult.scalars()
method, to return a list of values rather than a list of rows. The new methods are analogous to the long existingSession.scalar()
andConnection.scalar()
methods used to return a single value from the first row only. Pull request courtesy Miguel Grinberg.References: #6990
Fixed issue where the ability of the
ConnectionEvents.before_execute()
method to alter the SQL statement object passed, returning the new object to be invoked, was inadvertently removed. This behavior has been restored.References: #6913
Ensure that
str()
is called on the anURL.create.password
argument, allowing usage of objects that implement the__str__()
method as password attributes. Also clarified that one such object is not appropriate to dynamically change the password for each database connection; the approaches at Generating dynamic authentication tokens should be used instead.References: #6958
Fixed issue in
URL
where validation of “drivername” would not appropriately respond to theNone
value where a string were expected.References: #6983
Fixed issue where an engine that had
create_engine.implicit_returning
set to False would fail to function when PostgreSQL’s “fast insertmany” feature were used in conjunction with aSequence
, as well as if any kind of “executemany” with “return_defaults()” were used in conjunction with aSequence
. Note that PostgreSQL “fast insertmany” uses “RETURNING” by definition, when the SQL statement is passed to the driver; overall, thecreate_engine.implicit_returning
flag is legacy and has no real use in modern SQLAlchemy, and will be deprecated in a separate change.References: #6963
sql¶
Added new parameter
HasCTE.cte.nesting
to theCTE
constructor andHasCTE.cte()
method, which flags the CTE as one which should remain nested within an enclosing CTE, rather than being moved to the top level of the outermost SELECT. While in the vast majority of cases there is no difference in SQL functionality, users have identified various edge-cases where true nesting of CTE constructs is desirable. Much thanks to Eric Masseran for lots of work on this intricate feature.References: #4123
Implemented missing methods in
FunctionElement
which, while unused, would lead pylint to report them as unimplemented abstract methods.References: #7052
Fixed a two issues where combinations of
select()
andjoin()
when adapted to form a copy of the element would not completely copy the state of all column objects associated with subqueries. A key problem this caused is that usage of theClauseElement.params()
method (which should probably be moved into a legacy category as it is inefficient and error prone) would leave copies of the oldBindParameter
objects around, leading to issues in correctly setting the parameters at execution time.References: #7055
Fixed issue related to new
HasCTE.add_cte()
feature where pairing two “INSERT..FROM SELECT” statements simultaneously would lose track of the two independent SELECT statements, leading to the wrong SQL.References: #7036
Fixed issue where using ORM column expressions as keys in the list of dictionaries passed to
Insert.values()
for “multi-valued insert” would not be processed correctly into the correct column expressions.References: #7060
mypy¶
asyncio¶
Added initial support for the
asyncmy
asyncio database driver for MySQL and MariaDB. This driver is very new, however appears to be the only current alternative to theaiomysql
driver which currently appears to be unmaintained and is not working with current Python versions. Much thanks to long2ice for the pull request for this dialect.See also
References: #6993
The
AsyncSession
now supports overriding whichSession
it uses as the proxied instance. A customSession
class can be passed using theAsyncSession.sync_session_class
parameter or by subclassing theAsyncSession
and specifying a customAsyncSession.sync_session_class
.References: #6746
Fixed a bug in
AsyncSession.execute()
andAsyncSession.stream()
that requiredexecution_options
to be an instance ofimmutabledict
when defined. It now correctly accepts any mapping.References: #6943
Added missing
**kw
arguments to theAsyncSession.connection()
method.Deprecate usage of
scoped_session
with asyncio drivers. When using Asyncio theasync_scoped_session
should be used instead.References: #6746
postgresql¶
Qualify
version()
call to avoid shadowing issues if a different search path is configured by the user.References: #6912
The
ENUM
datatype is PostgreSQL-native and therefore should not be used with thenative_enum=False
flag. This flag is now ignored if passed to theENUM
datatype and a warning is emitted; previously the flag would cause the type object to fail to function correctly.References: #6106
sqlite¶
Fixed bug where the error message for SQLite invalid isolation level on the pysqlite driver would fail to indicate that “AUTOCOMMIT” is one of the valid isolation levels.
mssql¶
Fixed an issue where
sqlalchemy.engine.reflection.has_table()
returnedTrue
for local temporary tables that actually belonged to a different SQL Server session (connection). An extra check is now performed to ensure that the temp table detected is in fact owned by the current session.References: #6910
oracle¶
Added a CAST(VARCHAR2(128)) to the “table name”, “owner”, and other DDL-name parameters as used in reflection queries against Oracle system views such as ALL_TABLES, ALL_TAB_CONSTRAINTS, etc to better enable indexing to take place against these columns, as they previously would be implicitly handled as NVARCHAR2 due to Python’s use of Unicode for strings; these columns are documented in all Oracle versions as being VARCHAR2 with lengths varying from 30 to 128 characters depending on server version. Additionally, test support has been enabled for Unicode-named DDL structures against Oracle databases.
References: #4486
1.4.23¶
Released: August 18, 2021general¶
The setup requirements have been modified such
greenlet
is a default requirement only for those platforms that are well known forgreenlet
to be installable and for which there is already a pre-built binary on pypi; the current list isx86_64 aarch64 ppc64le amd64 win32
. For other platforms, greenlet will not install by default, which should enable installation and test suite running of SQLAlchemy 1.4 on platforms that don’t supportgreenlet
, excluding any asyncio features. In order to install with thegreenlet
dependency included on a machine architecture outside of the above list, the[asyncio]
extra may be included by runningpip install sqlalchemy[asyncio]
which will then attempt to installgreenlet
.Additionally, the test suite has been repaired so that tests can complete fully when greenlet is not installed, with appropriate skips for asyncio-related tests.
References: #6136
orm¶
Added new attribute
Select.columns_clause_froms
that will retrieve the FROM list implied by the columns clause of theSelect
statement. This differs from the oldSelect.froms
collection in that it does not perform any ORM compilation steps, which necessarily deannotate the FROM elements and do things like compute joinedloads etc., which makes it not an appropriate candidate for theSelect.select_from()
method. Additionally adds a new parameterSelect.with_only_columns.maintain_column_froms
that transfers this collection toSelect.select_from()
before replacing the columns collection.In addition, the
Select.froms
is renamed toSelect.get_final_froms()
, to stress that this collection is not a simple accessor and is instead calculated given the full state of the object, which can be an expensive call when used in an ORM context.Additionally fixes a regression involving the
with_only_columns()
function to support applying criteria to column elements that were replaced with eitherSelect.with_only_columns()
orQuery.with_entities()
, which had broken as part of #6503 released in 1.4.19.References: #6808
Fixed issue where a bound parameter object that was “cloned” would cause a name conflict in the compiler, if more than one clone of this parameter were used at the same time in a single statement. This could occur in particular with things like ORM single table inheritance queries that indicated the same “discriminator” value multiple times in one query.
References: #6824
Fixed issue in loader strategies where the use of the
Load.options()
method, particularly when nesting multiple calls, would generate an overly long and more importantly non-deterministic cache key, leading to very large cache keys which were also not allowing efficient cache usage, both in terms of total memory used as well as number of entries used in the cache itself.References: #6869
Revised the means by which the
ORMExecuteState.user_defined_options
accessor receivesUserDefinedOption
and related option objects from the context, with particular emphasis on the “selectinload” on the loader strategy where this previously was not working; other strategies did not have this problem. The objects that are associated with the current query being executed, and not that of a query being cached, are now propagated unconditionally. This essentially separates them out from the “loader strategy” options which are explicitly associated with the compiled state of a query and need to be used in relation to the cached query.The effect of this fix is that a user-defined option, such as those used by the dogpile.caching example as well as for other recipes such as defining a “shard id” for the horizontal sharing extension, will be correctly propagated to eager and lazy loaders regardless of whether a cached query was ultimately invoked.
References: #6887
Fixed issue where the unit of work would internally use a 2.0-deprecated SQL expression form, emitting a deprecation warning when SQLALCHEMY_WARN_20 were enabled.
References: #6812
Fixed issue in
selectinload()
where use of the newPropComparator.and_()
feature within options that were nested more than one level deep would fail to update bound parameter values that were in the nested criteria, as a side effect of SQL statement caching.References: #6881
Adjusted ORM loader internals to no longer use the “lambda caching” system that was added in 1.4, as well as repaired one location that was still using the previous “baked query” system for a query. The lambda caching system remains an effective way to reduce the overhead of building up queries that have relatively fixed usage patterns. In the case of loader strategies, the queries used are responsible for moving through lots of arbitrary options and criteria, which is both generated and sometimes consumed by end-user code, that make the lambda cache concept not any more efficient than not using it, at the cost of more complexity. In particular the problems noted by #6881 and #6887 are made are made considerably less complicated by removing this feature internally.
Fixed an issue where the
Bundle
construct would not create proper cache keys, leading to inefficient use of the query cache. This had some impact on the “selectinload” strategy and was identified as part of #6889.References: #6889
sql¶
Fix issue in
CTE
where newHasCTE.add_cte()
method added in version 1.4.21 / #6752 failed to function correctly for “compound select” structures such asunion()
,union_all()
,except()
, etc. Pull request courtesy Eric Masseran.References: #6752
Fixed an issue in the
CacheKey.to_offline_string()
method used by the dogpile.caching example where attempting to create a proper cache key from the special “lambda” query generated by the lazy loader would fail to include the parameter values, leading to an incorrect cache key.References: #6858
Adjusted the “from linter” warning feature to accommodate for a chain of joins more than one level deep where the ON clauses don’t explicitly match up the targets, such as an expression such as “ON TRUE”. This mode of use is intended to cancel the cartesian product warning simply by the fact that there’s a JOIN from “a to b”, which was not working for the case where the chain of joins had more than one element.
References: #6886
Fixed issue in lambda caching system where an element of a query that produces no cache key, like a custom option or clause element, would still populate the expression in the “lambda cache” inappropriately.
schema¶
Unify behaviour
Enum
in native and non-native implementations regarding the accepted values for an enum with aliased elements. WhenEnum.omit_aliases
isFalse
all values, alias included, are accepted as valid values. WhenEnum.omit_aliases
isTrue
only non aliased values are accepted as valid values.References: #6146
mypy¶
Added support for SQLAlchemy classes to be defined in user code using “generic class” syntax as defined by
sqlalchemy2-stubs
, e.g.Column[String]
, without the need for qualifying these constructs within aTYPE_CHECKING
block by implementing the Python special method__class_getitem__()
, which allows this syntax to pass without error at runtime.
postgresql¶
Added the “is_comparison” flag to the PostgreSQL “overlaps”, “contained_by”, “contains” operators, so that they work in relevant ORM contexts as well as in conjunction with the “from linter” feature.
References: #6886
mssql¶
Fixed issue where the
literal_binds
compiler flag, as used externally to render bound parameters inline, would fail to work when used with a certain class of parameters known as “literal_execute”, which covers things like LIMIT and OFFSET values for dialects where the drivers don’t allow a bound parameter, such as SQL Server’s “TOP” clause. The issue locally seemed to affect only the MSSQL dialect.References: #6863
misc¶
Fixed issue where the horizontal sharding extension would not correctly accommodate for a plain textual SQL statement passed to
Session.execute()
.References: #6816
1.4.22¶
Released: July 21, 2021orm¶
Fixed issue in new
Table.table_valued()
method where the resultingTableValuedColumn
construct would not respond correctly to alias adaptation as is used throughout the ORM, such as for eager loading, polymorphic loading, etc.References: #6775
Fixed issue where usage of the
Result.unique()
method with an ORM result that included column expressions with unhashable types, such asJSON
orARRAY
using non-tuples would silently fall back to using theid()
function, rather than raising an error. This now raises an error when theResult.unique()
method is used in a 2.0 style ORM query. Additionally, hashability is assumed to be True for result values of unknown type, such as often happens when using SQL functions of unknown return type; if values are truly not hashable then thehash()
itself will raise.For legacy ORM queries, since the legacy
Query
object uniquifies in all cases, the old rules remain in place, which is to useid()
for result values of unknown type as this legacy uniquing is mostly for the purpose of uniquing ORM entities and not column values.References: #6769
Fixed an issue where clearing of mappers during things like test suite teardowns could cause a “dictionary changed size” warning during garbage collection, due to iteration of a weak-referencing dictionary. A
list()
has been applied to prevent concurrent GC from affecting this operation.References: #6771
Fixed critical caching issue where the ORM’s persistence feature using INSERT..RETURNING would cache an incorrect query when mixing the “bulk save” and standard “flush” forms of INSERT.
References: #6793
engine¶
Added some guards against
KeyError
in the event system to accommodate the case that the interpreter is shutting down at the same timeEngine.dispose()
is being called, which would cause stack trace warnings.References: #6740
sql¶
Fixed issue where use of the
case.whens
parameter passing a dictionary positionally and not as a keyword argument would emit a 2.0 deprecation warning, referring to the deprecation of passing a list positionally. The dictionary format of “whens”, passed positionally, is still supported and was accidentally marked as deprecated.References: #6786
Fixed issue where type-specific bound parameter handlers would not be called upon in the case of using the
Insert.values()
method with the PythonNone
value; in particular, this would be noticed when using theJSON
datatype as well as related PostgreSQL specific types such asJSONB
which would fail to encode the PythonNone
value into JSON null, however the issue was generalized to any bound parameter handler in conjunction with this specific method ofInsert
.References: #6770
1.4.21¶
Released: July 14, 2021orm¶
Modified the approach used for history tracking of scalar object relationships that are not many-to-one, i.e. one-to-one relationships that would otherwise be one-to-many. When replacing a one-to-one value, the “old” value that would be replaced is no longer loaded immediately, and is instead handled during the flush process. This eliminates an historically troublesome lazy load that otherwise often occurs when assigning to a one-to-one attribute, and is particularly troublesome when using “lazy=’raise’” as well as asyncio use cases.
This change does cause a behavioral change within the
AttributeEvents.set()
event, which is nonetheless currently documented, which is that the event applied to such a one-to-one attribute will no longer receive the “old” parameter if it is unloaded and therelationship.active_history
flag is not set. As is documented inAttributeEvents.set()
, if the event handler needs to receive the “old” value when the event fires off, the active_history flag must be established either with the event listener or with the relationship. This is already the behavior with other kinds of attributes such as many-to-one and column value references.The change additionally will defer updating a backref on the “old” value in the less common case that the “old” value is locally present in the session, but isn’t loaded on the relationship in question, until the next flush occurs. If this causes an issue, again the normal
relationship.active_history
flag can be set toTrue
on the relationship.References: #6708
Fixed regression caused in 1.4.19 due to #6503 and related involving
Query.with_entities()
where the new structure used would be inappropriately transferred to an enclosingQuery
when making use of set operations such asQuery.union()
, causing the JOIN instructions within to be applied to the outside query as well.References: #6698
Fixed regression which appeared in version 1.4.3 due to #6060 where rules that limit ORM adaptation of derived selectables interfered with other ORM-adaptation based cases, in this case when applying adaptations for a
with_polymorphic()
against a mapping which uses acolumn_property()
which in turn makes use of a scalar select that includes aaliased()
object of the mapped table.References: #6762
Fixed ORM regression where ad-hoc label names generated for hybrid properties and potentially other similar types of ORM-enabled expressions would usually be propagated outwards through subqueries, allowing the name to be retained in the final keys of the result set even when selecting from subqueries. Additional state is now tracked in this case that isn’t lost when a hybrid is selected out of a Core select / subquery.
References: #6718
sql¶
Added new method
HasCTE.add_cte()
to each of theselect()
,insert()
,update()
anddelete()
constructs. This method will add the givenCTE
as an “independent” CTE of the statement, meaning it renders in the WITH clause above the statement unconditionally even if it is not otherwise referenced in the primary statement. This is a popular use case on the PostgreSQL database where a CTE is used for a DML statement that runs against database rows independently of the primary statement.References: #6752
Fixed issue in CTE constructs where a recursive CTE that referred to a SELECT that has duplicate column names, which are typically deduplicated using labeling logic in 1.4, would fail to refer to the deduplicated label name correctly within the WITH clause.
References: #6710
Fixed regression where the
tablesample()
construct would fail to be executable when constructed given a floating-point sampling value not embedded within a SQL function.References: #6735
postgresql¶
Fixed issue in
Insert.on_conflict_do_nothing()
andInsert.on_conflict_do_update()
where the name of a unique constraint passed as theconstraint
parameter would not be properly truncated for length if it were based on a naming convention that generated a too-long name for the PostgreSQL max identifier length of 63 characters, in the same way which occurs within a CREATE TABLE statement.References: #6755
Fixed issue where the PostgreSQL
ENUM
datatype as embedded in theARRAY
datatype would fail to emit correctly in create/drop when theschema_translate_map
feature were also in use. Additionally repairs a related issue where the sameschema_translate_map
feature would not work for theENUM
datatype in combination with aCAST
, that’s also intrinsic to how theARRAY(ENUM)
combination works on the PostgreSQL dialect.References: #6739
Fixed issue in
Insert.on_conflict_do_nothing()
andInsert.on_conflict_do_update()
where the name of a unique constraint passed as theconstraint
parameter would not be properly quoted if it contained characters which required quoting.References: #6696
mssql¶
Fixed regression where the special dotted-schema name handling for the SQL Server dialect would not function correctly if the dotted schema name were used within the
schema_translate_map
feature.References: #6697
1.4.20¶
Released: June 28, 2021orm¶
Fixed regression in ORM regarding an internal reconstitution step for the
with_polymorphic()
construct, when the user-facing object is garbage collected as the query is processed. The reconstitution was not ensuring the sub-entities for the “polymorphic” case were handled, leading to anAttributeError
.References: #6680
Adjusted
Query.union()
and similar set operations to be correctly compatible with the new capabilities just added in #6661, with SQLAlchemy 1.4.19, such that the SELECT statements rendered as elements of the UNION or other set operation will include directly mapped columns that are mapped as deferred; this both fixes a regression involving unions with multiple levels of nesting that would produce a column mismatch, and also allows theundefer()
option to be used at the top level of such aQuery
without having to apply the option to each of the elements within the UNION.References: #6678
Adjusted the check in the mapper for a callable object that is used as a
@validates
validator function or a@reconstructor
reconstruction function, to check for “callable” more liberally such as to accommodate objects based on fundamental attributes like__func__
and__call__
, rather than testing forMethodType
/FunctionType
, allowing things like cython functions to work properly. Pull request courtesy Miłosz Stypiński.References: #6538
engine¶
Fixed an issue in the C extension for the
Row
class which could lead to a memory leak in the unlikely case of aRow
object which referred to an ORM object that then was mutated to refer back to theRow
itself, creating a cycle. The Python C APIs for tracking GC cycles has been added to the nativeRow
implementation to accommodate for this case.References: #5348
Fixed old issue where a
select()
made against the token “*”, which then yielded exactly one column, would fail to correctly organize thecursor.description
column name into the keys of the result object.References: #6665
sql¶
Add a impl parameter to
PickleType
constructor, allowing any arbitrary type to be used in place of the default implementation ofLargeBinary
. Pull request courtesy jason3gb.References: #6646
Fixed the class hierarchy for the
Sequence
and the more generalDefaultGenerator
base, as these are “executable” as statements they need to includeExecutable
in their hierarchy, not justStatementRole
as was applied arbitrarily toSequence
previously. The fix allowsSequence
to work in all.execute()
methods including withSession.execute()
which was not working in the case that aSessionEvents.do_orm_execute()
handler was also established.References: #6668
schema¶
Fixed issue where passing
None
for the value ofTable.prefixes
would not store an empty list, but rather the constantNone
, which may be unexpected by third party dialects. The issue is revealed by a usage in recent versions of Alembic that are passingNone
for this value. Pull request courtesy Kai Mueller.References: #6685
mysql¶
Made a small adjustment in the table reflection feature of the MySQL dialect to accommodate for alternate MySQL-oriented databases such as TiDB which include their own “comment” directives at the end of a constraint directive within “CREATE TABLE” where the format doesn’t have the additional space character after the comment, in this case the TiDB “clustered index” feature. Pull request courtesy Daniël van Eeden.
References: #6659
misc¶
Fixed regression in
sqlalchemy.ext.automap
extension such that the use case of creating an explicit mapped class to a table that is also therelationship.secondary
element of arelationship()
that automap will be generating would emit the “overlaps” warnings introduced in 1.4 and discussed at relationship X will copy column Q to column P, which conflicts with relationship(s): ‘Y’. While generating this case from automap is still subject to the same caveats that the “overlaps” warning refers towards, as automap is intended for more ad-hoc use cases, the condition which produces the warning is disabled when a many-to-many relationship with this particular pattern is generated.References: #6679
1.4.19¶
Released: June 22, 2021orm¶
Fixed further regressions in the same area as that of #6052 where loader options as well as invocations of methods like
Query.join()
would fail if the left side of the statement for which the option/join depends upon were replaced by using theQuery.with_entities()
method, or when using 2.0 style queries when using theSelect.with_only_columns()
method. A new set of state has been added to the objects which tracks the “left” entities that the options / join were made against which is memoized when the lead entities are changed.Refined the behavior of ORM subquery rendering with regards to deferred columns and column properties to be more compatible with that of 1.3 while also providing for 1.4’s newer features. As a subquery in 1.4 does not make use of loader options, including
undefer()
, a subquery that is against an ORM entity with deferred attributes will now render those deferred attributes that refer directly to mapped table columns, as these are needed in the outer SELECT if that outer SELECT makes use of these columns; however a deferred attribute that refers to a composed SQL expression as we normally do withcolumn_property()
will not be part of the subquery, as these can be selected explicitly if needed in the subquery. If the entity is being SELECTed from this subquery, the column expression can still render on “the outside” in terms of the derived subquery columns. This produces essentially the same behavior as when working with 1.3. However in this case the fix has to also make sure that the.selected_columns
collection of an ORM-enabledselect()
also follows these rules, which in particular allows recursive CTEs to render correctly in this scenario, which were previously failing to render correctly due to this issue.References: #6661
sql¶
Fixed issue in CTE constructs mostly relevant to ORM use cases where a recursive CTE against “anonymous” labels such as those seen in ORM
column_property()
mappings would render in theWITH RECURSIVE xyz(...)
section as their raw internal label and not a cleanly anonymized name.References: #6663
mypy¶
Fixed issue in mypy plugin where class info for a custom declarative base would not be handled correctly on a cached mypy pass, leading to an AssertionError being raised.
References: #6476
asyncio¶
Implemented
async_scoped_session
to address some asyncio-related incompatibilities betweenscoped_session
andAsyncSession
, in which some methods (notably theasync_scoped_session.remove()
method) should be used with theawait
keyword.See also
References: #6583
Fixed bug in asyncio implementation where the greenlet adaptation system failed to propagate
BaseException
subclasses, most notably includingasyncio.CancelledError
, to the exception handling logic used by the engine to invalidate and clean up the connection, thus preventing connections from being correctly disposed when a task was cancelled.References: #6652
postgresql¶
Fixed issue where the
INTERVAL
datatype on PostgreSQL and Oracle would produce anAttributeError
when used in the context of a comparison operation against atimedelta()
object. Pull request courtesy MajorDallas.References: #6649
Fixed issue where the pool “pre ping” feature would implicitly start a transaction, which would then interfere with custom transactional flags such as PostgreSQL’s “read only” mode when used with the psycopg2 driver.
References: #6621
mysql¶
mssql¶
Made improvements to the server version regexp used by the pymssql dialect to prevent a regexp overflow in case of an invalid version string.
Fixed bug where the “schema_translate_map” feature would fail to function correctly in conjunction with an INSERT into a table that has an IDENTITY column, where the value of the IDENTITY column were specified in the values of the INSERT thus triggering SQLAlchemy’s feature of setting IDENTITY INSERT to “on”; it’s in this directive where the schema translate map would fail to be honored.
References: #6658
1.4.18¶
Released: June 10, 2021orm¶
Clarified the current purpose of the
relationship.bake_queries
flag, which in 1.4 is to enable or disable “lambda caching” of statements within the “lazyload” and “selectinload” loader strategies; this is separate from the more foundational SQL query cache that is used for most statements. Additionally, the lazy loader no longer uses its own cache for many-to-one SQL queries, which was an implementation quirk that doesn’t exist for any other loader scenario. Finally, the “lru cache” warning that the lazyloader and selectinloader strategies could emit when handling a wide array of class/relationship combinations has been removed; based on analysis of some end-user cases, this warning doesn’t suggest any significant issue. While settingbake_queries=False
for such a relationship will remove this cache from being used, there’s no particular performance gain in this case as using no caching vs. using a cache that needs to refresh often likely still wins out on the caching being used side.Adjusted the means by which classes such as
scoped_session
andAsyncSession
are generated from the baseSession
class, such that customSession
subclasses such as that used by Flask-SQLAlchemy don’t need to implement positional arguments when they call into the superclass method, and can continue using the same argument styles as in previous releases.References: #6285
Fixed issue where query production for joinedload against a complex left hand side involving joined-table inheritance could fail to produce a correct query, due to a clause adaption issue.
References: #6595
Fixed regression involving how the ORM would resolve a given mapped column to a result row, where under cases such as joined eager loading, a slightly more expensive “fallback” could take place to set up this resolution due to some logic that was removed since 1.3. The issue could also cause deprecation warnings involving column resolution to be emitted when using a 1.4 style query with joined eager loading.
References: #6596
Fixed issue in experimental “select ORM objects from INSERT/UPDATE” use case where an error was raised if the statement were against a single-table-inheritance subclass.
References: #6591
The warning that’s emitted for
relationship()
when multiple relationships would overlap with each other as far as foreign key attributes written towards, now includes the specific “overlaps” argument to use for each warning in order to silence the warning without changing the mapping.References: #6400
asyncio¶
Implemented a new registry architecture that allows the
Async
version of an object, likeAsyncSession
,AsyncConnection
, etc., to be locatable given the proxied “sync” object, i.e.Session
,Connection
. Previously, to the degree such lookup functions were used, anAsync
object would be re-created each time, which was less than ideal as the identity and state of the “async” object would not be preserved across calls.From there, new helper functions
async_object_session()
,async_session()
as well as a newInstanceState
attributeInstanceState.async_session
have been added, which are used to retrieve the originalAsyncSession
associated with an ORM mapped object, aSession
associated with anAsyncSession
, and anAsyncSession
associated with anInstanceState
, respectively.This patch also implements new methods
AsyncSession.in_nested_transaction()
,AsyncSession.get_transaction()
,AsyncSession.get_nested_transaction()
.References: #6319
Fixed an issue that presented itself when using the
NullPool
or theStaticPool
with an async engine. This mostly affected the aiosqlite dialect.References: #6575
Added
asyncio.exceptions.TimeoutError
,asyncio.exceptions.CancelledError
as so-called “exit exceptions”, a class of exceptions that include things likeGreenletExit
andKeyboardInterrupt
, which are considered to be events that warrant considering a DBAPI connection to be in an unusable state where it should be recycled.References: #6592
postgresql¶
Fixed regression where using the PostgreSQL “INSERT..ON CONFLICT” structure would fail to work with the psycopg2 driver if it were used in an “executemany” context along with bound parameters in the “SET” clause, due to the implicit use of the psycopg2 fast execution helpers which are not appropriate for this style of INSERT statement; as these helpers are the default in 1.4 this is effectively a regression. Additional checks to exclude this kind of statement from that particular extension have been added.
References: #6581
sqlite¶
Add note regarding encryption-related pragmas for pysqlcipher passed in the url.
This change is also backported to: 1.3.25
References: #6589
The fix for pysqlcipher released in version 1.4.3 #5848 was unfortunately non-working, in that the new
on_connect_url
hook was erroneously not receiving aURL
object under normal usage ofcreate_engine()
and instead received a string that was unhandled; the test suite failed to fully set up the actual conditions under which this hook is called. This has been fixed.References: #6586
1.4.17¶
Released: May 29, 2021orm¶
Fixed regression caused by just-released performance fix mentioned in #6550 where a query.join() to a relationship could produce an AttributeError if the query were made against non-ORM structures only, a fairly unusual calling pattern.
References: #6558
1.4.16¶
Released: May 28, 2021general¶
orm¶
Fixed issue when using
relationship.cascade_backrefs
parameter set toFalse
, which per cascade_backrefs behavior deprecated for removal in 2.0 is set to become the standard behavior in SQLAlchemy 2.0, where adding the item to a collection that uniquifies, such asset
ordict
would fail to fire a cascade event if the object were already associated in that collection via the backref. This fix represents a fundamental change in the collection mechanics by introducing a new event state which can fire off for a collection mutation even if there is no net change on the collection; the action is now suited using a new event hookAttributeEvents.append_wo_mutation()
.References: #6471
Fixed regression involving clause adaption of labeled ORM compound elements, such as single-table inheritance discriminator expressions with conditionals or CASE expressions, which could cause aliased expressions such as those used in ORM join / joinedload operations to not be adapted correctly, such as referring to the wrong table in the ON clause in a join.
This change also improves a performance bump that was located within the process of invoking
Select.join()
given an ORM attribute as a target.References: #6550
Fixed regression where the full combination of joined inheritance, global with_polymorphic, self-referential relationship and joined loading would fail to be able to produce a query with the scope of lazy loads and object refresh operations that also attempted to render the joined loader.
References: #6495
Enhanced the bind resolution rules for
Session.execute()
so that when a non-ORM statement such as aninsert()
construct nonetheless is built against ORM objects, to the greatest degree possible the ORM entity will be used to resolve the bind, such as for aSession
that has a bind map set up on a common superclass without specific mappers or tables named in the map.References: #6484
engine¶
Fixed issue where an
@
sign in the database portion of a URL would not be interpreted correctly if the URL also had a username:password section.References: #6482
Fixed a long-standing issue with
URL
where query parameters following the question mark would not be parsed correctly if the URL did not contain a database portion with a backslash.References: #6329
sql¶
Fixed regression in dynamic loader strategy and
relationship()
overall where therelationship.order_by
parameter were stored as a mutable list, which could then be mutated when combined with additional “order_by” methods used against the dynamic query object, causing the ORDER BY criteria to continue to grow repetitively.References: #6549
mssql¶
misc¶
Fixed a deprecation warning that was emitted when using
automap_base()
without passing an existingBase
.References: #6529
Remove pep484 types from the code. Current effort is around the stub package, and having typing in two places makes thing worse, since the types in the SQLAlchemy source were usually outdated compared to the version in the stubs.
References: #6461
Fixed regression in the
sqlalchemy.ext.instrumentation
extension that prevented instrumentation disposal from working completely. This fix includes both a 1.4 regression fix as well as a fix for a related issue that existed in 1.3 also. As part of this change, thesqlalchemy.ext.instrumentation.InstrumentationManager
class now has a new methodunregister()
, which replaces the previous methoddispose()
, which was not called as of version 1.4.References: #6390
1.4.15¶
Released: May 11, 2021general¶
A new approach has been applied to the warnings system in SQLAlchemy to accurately predict the appropriate stack level for each warning dynamically. This allows evaluating the source of SQLAlchemy-generated warnings and deprecation warnings to be more straightforward as the warning will indicate the source line within end-user code, rather than from an arbitrary level within SQLAlchemy’s own source code.
References: #6241
orm¶
Fixed additional regression caused by “eager loaders run on unexpire” feature #1763 where the feature would run for a
contains_eager()
eagerload option in the case that thecontains_eager()
were chained to an additional eager loader option, which would then produce an incorrect query as the original query-bound join criteria were no longer present.References: #6449
Fixed issue in subquery loader strategy which prevented caching from working correctly. This would have been seen in the logs as a “generated” message instead of “cached” for all subqueryload SQL emitted, which by saturating the cache with new keys would degrade overall performance; it also would produce “LRU size alert” warnings.
References: #6459
sql¶
Adjusted the logic added as part of #6397 in 1.4.12 so that internal mutation of the
BindParameter
object occurs within the clause construction phase as it did before, rather than in the compilation phase. In the latter case, the mutation still produced side effects against the incoming construct and additionally could potentially interfere with other internal mutation routines.References: #6460
mysql¶
Added support for the
ssl_check_hostname=
parameter in mysql connection URIs and updated the mysql dialect documentation regarding secure connections. Original pull request courtesy of Jerry Zhao.References: #5397
1.4.14¶
Released: May 6, 2021orm¶
Fixed regression involving
lazy='dynamic'
loader in conjunction with a detached object. The previous behavior was that the dynamic loader upon calling methods like.all()
returns empty lists for detached objects without error, this has been restored; however a warning is now emitted as this is not the correct result. Other dynamic loader scenarios correctly raiseDetachedInstanceError
.References: #6426
engine¶
Applied consistent behavior to the use case of calling
.commit()
or.rollback()
inside of an existing.begin()
context manager, with the addition of potentially emitting SQL within the block subsequent to the commit or rollback. This change continues upon the change first added in #6155 where the use case of calling “rollback” inside of a.begin()
contextmanager block was proposed:calling
.commit()
or.rollback()
will now be allowed without error or warning within all scopes, including that of legacy and futureEngine
, ORMSession
, asyncioAsyncEngine
. Previously, theSession
disallowed this.The remaining scope of the context manager is then closed; when the block ends, a check is emitted to see if the transaction was already ended, and if so the block returns without action.
It will now raise an error if subsequent SQL of any kind is emitted within the block, after
.commit()
or.rollback()
is called. The block should be closed as the state of the executable object would otherwise be undefined in this state.
References: #6288
Established a deprecation path for calling upon the
CursorResult.keys()
method for a statement that returns no rows to provide support for legacy patterns used by the “records” package as well as any other non-migrated applications. Previously, this would raiseResourceClosedException
unconditionally in the same way as it does when attempting to fetch rows. While this is the correct behavior going forward, theLegacyCursorResult
object will now in this case return an empty list for.keys()
as it did in 1.3, while also emitting a 2.0 deprecation warning. The_cursor.CursorResult
, used when using a 2.0-style “future” engine, will continue to raise as it does now.References: #6427
sql¶
Fixed regression caused by the “empty in” change just made in #6397 1.4.12 where the expression needs to be parenthesized for the “not in” use case, otherwise the condition will interfere with the other filtering criteria.
References: #6428
The
TypeDecorator
class will now emit a warning when used in SQL compilation with caching unless the.cache_ok
flag is set toTrue
orFalse
. A new class-level attributeTypeDecorator.cache_ok
may be set which will be used as an indication that all the parameters passed to the object are safe to be used as a cache key if set toTrue
,False
means they are not.References: #6436
1.4.13¶
Released: May 3, 2021orm¶
Fixed regression in
selectinload
loader strategy that would cause it to cache its internal state incorrectly when handling relationships that join across more than one column, such as when using a composite foreign key. The invalid caching would then cause other unrelated loader operations to fail.References: #6410
Fixed regression where
Query.filter_by()
would not work if the lead entity were a SQL function or other expression derived from the primary entity in question, rather than a simple entity or column of that entity. Additionally, improved the behavior ofSelect.filter_by()
overall to work with column expressions even in a non-ORM context.References: #6414
Fixed regression where using
selectinload()
andsubqueryload()
to load a two-level-deep path would lead to an attribute error.References: #6419
Fixed regression where using the
noload()
loader strategy in conjunction with a “dynamic” relationship would lead to an attribute error as the noload strategy would attempt to apply itself to the dynamic loader.References: #6420
engine¶
Restored a legacy transactional behavior that was inadvertently removed from the
Connection
as it was never tested as a known use case in previous versions, where calling upon theConnection.begin_nested()
method, when no transaction is present, does not create a SAVEPOINT at all and instead starts an outer transaction, returning aRootTransaction
object instead of aNestedTransaction
object. ThisRootTransaction
then will emit a real COMMIT on the database connection when committed. Previously, the 2.0 style behavior was present in all cases that would autobegin a transaction but not commit it, which is a behavioral change.When using a 2.0 style connection object, the behavior is unchanged from previous 1.4 versions; calling
Connection.begin_nested()
will “autobegin” the outer transaction if not already present, and then as instructed emit a SAVEPOINT, returning theNestedTransaction
object. The outer transaction is committed by calling uponConnection.commit()
, as is “commit-as-you-go” style usage.In non-“future” mode, while the old behavior is restored, it also emits a 2.0 deprecation warning as this is a legacy behavior.
References: #6408
asyncio¶
Fixed a regression introduced by #6337 that would create an
asyncio.Lock
which could be attached to the wrong loop when instantiating the async engine before any asyncio loop was started, leading to an asyncio error message when attempting to use the engine under certain circumstances.References: #6409
postgresql¶
Add support for server side cursors in the pg8000 dialect for PostgreSQL. This allows use of the
Connection.execution_options.stream_results
option.References: #6198
1.4.12¶
Released: April 29, 2021orm¶
Fixed issue in
Session.bulk_save_objects()
when used with persistent objects which would fail to track the primary key of mappings where the column name of the primary key were different than the attribute name.This change is also backported to: 1.3.25
References: #6392
Fixed critical regression where bound parameter tracking as used in the SQL caching system could fail to track all parameters for the case where the same SQL expression containing a parameter were used in an ORM-related query using a feature such as class inheritance, which was then embedded in an enclosing expression which would make use of that same expression multiple times, such as a UNION. The ORM would individually copy the individual SELECT statements as part of compilation with class inheritance, which then embedded in the enclosing statement would fail to accommodate for all parameters. The logic that tracks this condition has been adjusted to work for multiple copies of a parameter.
References: #6391
Fixed two distinct issues mostly affecting
hybrid_property
, which would come into play under common mis-configuration scenarios that were silently ignored in 1.3, and now failed in 1.4, where the “expression” implementation would return a nonClauseElement
such as a boolean value. For both issues, 1.3’s behavior was to silently ignore the mis-configuration and ultimately attempt to interpret the value as a SQL expression, which would lead to an incorrect query.Fixed issue regarding interaction of the attribute system with hybrid_property, where if the
__clause_element__()
method of the attribute returned a non-ClauseElement
object, an internalAttributeError
would lead the attribute to return theexpression
function on the hybrid_property itself, as the attribute error was against the name.expression
which would invoke the__getattr__()
method as a fallback. This now raises explicitly. In 1.3 the non-ClauseElement
was returned directly.Fixed issue in SQL argument coercions system where passing the wrong kind of object to methods that expect column expressions would fail if the object were altogether not a SQLAlchemy object, such as a Python function, in cases where the object were not just coerced into a bound value. Again 1.3 did not have a comprehensive argument coercion system so this case would also pass silently.
References: #6350
Fixed issue where using a
Select
as a subquery in an ORM context would modify theSelect
in place to disable eagerloads on that object, which would then cause that sameSelect
to not eagerload if it were then re-used in a top-level execution context.References: #6378
Fixed issue where the new autobegin behavior failed to “autobegin” in the case where an existing persistent object has an attribute change, which would then impact the behavior of
Session.rollback()
in that no snapshot was created to be rolled back. The “attribute modify” mechanics have been updated to ensure “autobegin”, which does not perform any database work, does occur when persistent attributes change in the same manner as whenSession.add()
is called. This is a regression as in 1.3, the rollback() method always had a transaction to roll back and would expire every time.Fixed regression in ORM where using hybrid property to indicate an expression from a different entity would confuse the column-labeling logic in the ORM and attempt to derive the name of the hybrid from that other class, leading to an attribute error. The owning class of the hybrid attribute is now tracked along with the name.
References: #6386
Fixed regression in hybrid_property where a hybrid against a SQL function would generate an
AttributeError
when attempting to generate an entry for the.c
collection of a subquery in some cases; among other things this would impact its use in cases like that ofQuery.count()
.References: #6401
Adjusted the declarative scan for dataclasses so that the inheritance behavior of
declared_attr()
established on a mixin, when using the new form of having it inside of adataclasses.field()
construct and not actually a descriptor attribute on the class, correctly accommodates the case when the target class to be mapped is a subclass of an existing mapped class which has already mapped thatdeclared_attr()
, and therefore should not be re-applied to this class.References: #6346
Fixed an issue with the (deprecated in 1.4)
ForeignKeyConstraint.copy()
method that caused an error when invoked with theschema
argument.References: #6353
engine¶
Fixed issue where usage of an explicit
Sequence
would produce inconsistent “inline” behavior for anInsert
construct that includes multiple values phrases; the first seq would be inline but subsequent ones would be “pre-execute”, leading to inconsistent sequence ordering. The sequence expressions are now fully inline.References: #6361
sql¶
Revised the “EMPTY IN” expression to no longer rely upon using a subquery, as this was causing some compatibility and performance problems. The new approach for selected databases takes advantage of using a NULL-returning IN expression combined with the usual “1 != 1” or “1 = 1” expression appended by AND or OR. The expression is now the default for all backends other than SQLite, which still had some compatibility issues regarding tuple “IN” for older SQLite versions.
Third party dialects can still override how the “empty set” expression renders by implementing a new compiler method
def visit_empty_set_op_expr(self, type_, expand_op)
, which takes precedence over the existingdef visit_empty_set_expr(self, element_types)
which remains in place.Fixed regression where usage of the
text()
construct inside the columns clause of aSelect
construct, which is better handled by using aliteral_column()
construct, would nonetheless prevent constructs likeunion()
from working correctly. Other use cases, such as constructing subuqeries, continue to work the same as in prior versions where thetext()
construct is silently omitted from the collection of exported columns. Also repairs similar use within the ORM.References: #6343
Fixed regression involving legacy methods such as
Select.append_column()
where internal assertions would fail.References: #6261
Fixed regression caused by #5395 where tuning back the check for sequences in
select()
now caused failures when doing 2.0-style querying with a mapped class that also happens to have an__iter__()
method. Tuned the check some more to accommodate this as well as some other interesting__iter__()
scenarios.References: #6300
schema¶
Ensure that the MySQL and MariaDB dialect ignore the
Identity
construct while rendering theAUTO_INCREMENT
keyword in a create table.The Oracle and PostgreSQL compiler was updated to not render
Identity
if the database version does not support it (Oracle < 12 and PostgreSQL < 10). Previously it was rendered regardless of the database version.References: #6338
postgresql¶
Fixed very old issue where the
Enum
datatype would not inherit theMetaData.schema
parameter of aMetaData
object when that object were passed to theEnum
usingEnum.metadata
.References: #6373
sqlite¶
Default to using
SingletonThreadPool
for in-memory SQLite databases created using URI filenames. Previously the default pool used was theNullPool
that precented sharing the same database between multiple engines.References: #6379
mssql¶
Add
TypeEngine.as_generic()
support forsqlalchemy.dialects.mysql.BIT
columns, mapping them toBoolean
.References: #6345
Fixed regression caused by #6306 which added support for
DateTime(timezone=True)
, where the previous behavior of the pyodbc driver of implicitly dropping the tzinfo from a timezone-aware date when INSERTing into a timezone-naive DATETIME column were lost, leading to a SQL Server error when inserting timezone-aware datetime objects into timezone-native database columns.References: #6366
1.4.11¶
Released: April 21, 2021orm declarative¶
Fixed regression where recent changes to support Python dataclasses had the inadvertent effect that an ORM mapped class could not successfully override the
__new__()
method.References: #6331
engine¶
1.4.10¶
Released: April 20, 2021orm¶
Altered some of the behavior repaired in #6232 where the
immediateload
loader strategy no longer goes into recursive loops; the modification is that an eager load (joinedload, selectinload, or subqueryload) from A->bs->B which then statesimmediateload
for a simple manytoone B->a->A that’s in the identity map will populate the B->A, so that this attribute is back-populated when the collection of A/A.bs are loaded. This allows the objects to be functional when detached.Fixed bug in new
with_loader_criteria()
feature where using a mixin class withdeclared_attr()
on an attribute that were accessed inside the custom lambda would emit a warning regarding using an unmapped declared attr, when the lambda callable were first initialized. This warning is now prevented using special instrumentation for this lambda initialization step.References: #6320
Fixed additional regression caused by the “eagerloaders on refresh” feature added in #1763 where the refresh operation historically would set
populate_existing
, which given the new feature now overwrites pending changes on eagerly loaded objects when autoflush is false. The populate_existing flag has been turned off for this case and a more specific method used to ensure the correct attributes refreshed.References: #6326
Fixed an issue when using 2.0 style execution that prevented using
Result.scalar_one()
orResult.scalar_one_or_none()
after callingResult.unique()
, for the case where the ORM is returning a single-element row in any case.References: #6299
sql¶
Fixed issue in SQL compiler where the bound parameters set up for a
Values
construct wouldn’t be positionally tracked correctly if inside of aCTE
, affecting database drivers that support VALUES + ctes and use positional parameters such as SQL Server in particular as well as asyncpg. The fix also repairs support for compiler flags such asliteral_binds
.References: #6327
Repaired and solidified issues regarding custom functions and other arbitrary expression constructs which within SQLAlchemy’s column labeling mechanics would seek to use
str(obj)
to get a string representation to use as an anonymous column name in the.c
collection of a subquery. This is a very legacy behavior that performs poorly and leads to lots of issues, so has been revised to no longer perform any compilation by establishing specific methods onFunctionElement
to handle this case, as SQL functions are the only use case that it came into play. An effect of this behavior is that an unlabeled column expression with no derivable name will be given an arbitrary label starting with the prefix"_no_label"
in the.c
collection of a subquery; these were previously being represented either as the generic stringification of that expression, or as an internal symbol.References: #6256
schema¶
Fixed issue where
next_value()
was not deriving its type from the correspondingSequence
, instead hardcoded toInteger
. The specific numeric type is now used.References: #6287
mypy¶
Fixed issue where mypy plugin would not correctly interpret an explicit
Mapped
annotation in conjunction with arelationship()
that refers to a class by string name; the correct annotation would be downgraded to a less specific one leading to typing errors.References: #6255
mssql¶
The
DateTime.timezone
parameter when set toTrue
will now make use of theDATETIMEOFFSET
column type with SQL Server when used to emit DDL, rather thanDATETIME
where the flag was silently ignored.References: #6306
misc¶
Fixed
instrument_declarative()
that called a non existing registry method.References: #6291
1.4.9¶
Released: April 17, 2021orm¶
Established support for
synoynm()
in conjunction with hybrid property, assocaitionproxy is set up completely, including that synonyms can be established linking to these constructs which work fully. This is a behavior that was semi-explicitly disallowed previously, however since it did not fail in every scenario, explicit support for assoc proxy and hybrids has been added.References: #6267
Fixed a critical performance issue where the traversal of a
select()
construct would traverse a repetitive product of the represented FROM clauses as they were each referred towards by columns in the columns clause; for a series of nested subqueries with lots of columns this could cause a large delay and significant memory growth. This traversal is used by a wide variety of SQL and ORM functions, including by the ORMSession
when it’s configured to have “table-per-bind”, which while this is not a common use case, it seems to be what Flask-SQLAlchemy is hardcoded as using, so the issue impacts Flask-SQLAlchemy users. The traversal has been repaired to uniqify on FROM clauses which was effectively what would happen implicitly with the pre-1.4 architecture.References: #6304
Fixed regression where an attribute that is mapped to a
synonym()
could not be used in column loader options such asload_only()
.References: #6272
sql¶
Fixed regression where an empty in statement on a tuple would result in an error when compiled with the option
literal_binds=True
.References: #6290
postgresql¶
Fixed an argument error in the default and PostgreSQL compilers that would interfere with an UPDATE..FROM or DELETE..FROM..USING statement that was then SELECTed from as a CTE.
References: #6303
1.4.8¶
Released: April 15, 2021orm¶
Fixed a cache leak involving the
with_expression()
loader option, where the given SQL expression would not be correctly considered as part of the cache key.Additionally, fixed regression involving the corresponding
query_expression()
feature. While the bug technically exists in 1.3 as well, it was not exposed until 1.4. The “default expr” value ofnull()
would be rendered when not needed, and additionally was also not adapted correctly when the ORM rewrites statements such as when using joined eager loading. The fix ensures “singleton” expressions likeNULL
andtrue
aren’t “adapted” to refer to columns in ORM statements, and additionally ensures that aquery_expression()
with no default expression doesn’t render in the statement if awith_expression()
isn’t used.References: #6259
Fixed issue in the new feature of
Session.refresh()
introduced by #1763 where eagerly loaded relationships are also refreshed, where thelazy="raise"
andlazy="raise_on_sql"
loader strategies would interfere with theimmediateload()
loader strategy, thus breaking the feature for relationships that were loaded withselectinload()
,subqueryload()
as well.References: #6252
engine¶
The
Dialect.has_table()
method now raises an informative exception if a non-Connection is passed to it, as this incorrect behavior seems to be common. This method is not intended for external use outside of a dialect. Please use theInspector.has_table()
method or for cross-compatibility with older SQLAlchemy versions, theEngine.has_table()
method.
sql¶
The tuple returned by
CursorResult.inserted_primary_key
is now aRow
object with a named tuple interface on top of the existing tuple interface.References: #3314
Fixed regression where the
BindParameter
object would not properly render for an IN expression (i.e. using the “post compile” feature in 1.4) if the object were copied from either an internal cloning operation, or from a pickle operation, and the parameter name contained spaces or other special characters.References: #6249
Fixed regression where the introduction of the INSERT syntax “INSERT… VALUES (DEFAULT)” was not supported on some backends that do however support “INSERT..DEFAULT VALUES”, including SQLite. The two syntaxes are now each individually supported or non-supported for each dialect, for example MySQL supports “VALUES (DEFAULT)” but not “DEFAULT VALUES”. Support for Oracle has also been enabled.
References: #6254
mypy¶
Updated Mypy plugin to only use the public plugin interface of the semantic analyzer.
Revised the fix for
OrderingList
from version 1.4.7 which was testing against the incorrect API.References: #6205
asyncio¶
Fix typo that prevented setting the
bind
attribute of anAsyncSession
to the correct value.References: #6220
mssql¶
Fixed an additional regression in the same area as that of #6173, #6184, where using a value of 0 for OFFSET in conjunction with LIMIT with SQL Server would create a statement using “TOP”, as was the behavior in 1.3, however due to caching would then fail to respond accordingly to other values of OFFSET. If the “0” wasn’t first, then it would be fine. For the fix, the “TOP” syntax is now only emitted if the OFFSET value is omitted entirely, that is,
Select.offset()
is not used. Note that this change now requires that if the “with_ties” or “percent” modifiers are used, the statement can’t specify an OFFSET of zero, it now needs to be omitted entirely.References: #6265
1.4.7¶
Released: April 9, 2021orm¶
Fixed regression where the
subqueryload()
loader strategy would fail to correctly accommodate sub-options, such as adefer()
option on a column, if the “path” of the subqueryload were more than one level deep.References: #6221
Fixed regression where the
merge_frozen_result()
function relied upon by the dogpile.caching example was not included in tests and began failing due to incorrect internal arguments.References: #6211
Fixed critical regression where the
Session
could fail to “autobegin” a new transaction when a flush occurred without an existing transaction in place, implicitly placing theSession
into legacy autocommit mode which commit the transaction. TheSession
now has a check that will prevent this condition from occurring, in addition to repairing the flush issue.Additionally, scaled back part of the change made as part of #5226 which can run autoflush during an unexpire operation, to not actually do this in the case of a
Session
using legacySession.autocommit
mode, as this incurs a commit within a refresh operation.References: #6233
Fixed regression where the ORM compilation scheme would assume the function name of a hybrid property would be the same as the attribute name in such a way that an
AttributeError
would be raised, when it would attempt to determine the correct name for each element in a result tuple. A similar issue exists in 1.3 but only impacts the names of tuple rows. The fix here adds a check that the hybrid’s function name is actually present in the__dict__
of the class or its superclasses before assigning this name; otherwise, the hybrid is considered to be “unnamed” and ORM result tuples will use the naming scheme of the underlying expression.References: #6215
Fixed critical regression caused by the new feature added as part of #1763, eager loaders are invoked on unexpire operations. The new feature makes use of the “immediateload” eager loader strategy as a substitute for a collection loading strategy, which unlike the other “post-load” strategies was not accommodating for recursive invocations between mutually-dependent relationships, leading to recursion overflow errors.
References: #6232
engine¶
Fixed up the behavior of the
Row
object when dictionary access is used upon it, meaning converting to a dict viadict(row)
or accessing members using strings or other objects i.e.row["some_key"]
works as it would with a dictionary, rather than raisingTypeError
as would be the case with a tuple, whether or not the C extensions are in place. This was originally supposed to emit a 2.0 deprecation warning for the “non-future” case usingLegacyRow
, and was to raiseTypeError
for the “future”Row
class. However, the C version ofRow
was failing to raise thisTypeError
, and to complicate matters, theSession.execute()
method now returnsRow
in all cases to maintain consistency with the ORM result case, so users who didn’t have C extensions installed would see different behavior in this one case for existing pre-1.4 style code.Therefore, in order to soften the overall upgrade scheme as most users have not been exposed to the more strict behavior of
Row
up through 1.4.6,LegacyRow
andRow
both provide for string-key access as well as support fordict(row)
, in all cases emitting the 2.0 deprecation warning whenSQLALCHEMY_WARN_20
is enabled. TheRow
object still uses tuple-like behavior for__contains__
, which is probably the only noticeable behavioral change compared toLegacyRow
, other than the removal of dictionary-style methodsvalues()
anditems()
.References: #6218
sql¶
Enhanced the “expanding” feature used for
ColumnOperators.in_()
operations to infer the type of expression from the right hand list of elements, if the left hand side does not have any explicit type set up. This allows the expression to support stringification among other things. In 1.3, “expanding” was not automatically used forColumnOperators.in_()
expressions, so in that sense this change fixes a behavioral regression.References: #6222
Fixed the “stringify” compiler to support a basic stringification of a “multirow” INSERT statement, i.e. one with multiple tuples following the VALUES keyword.
schema¶
Fixed regression where usage of a token in the
Connection.execution_options.schema_translate_map
dictionary which contained special characters such as braces would fail to be substituted properly. Use of square bracket characters[]
is now explicitly disallowed as these are used as a delimiter character in the current implementation.References: #6216
mypy¶
Fixed issue in Mypy plugin where the plugin wasn’t inferring the correct type for columns of subclasses that don’t directly descend from
TypeEngine
, in particular that ofTypeDecorator
andUserDefinedType
.
tests¶
Added a new flag to
DefaultDialect
calledsupports_schemas
; third party dialects may set this flag toFalse
to disable SQLAlchemy’s schema-level tests when running the test suite for a third party dialect.
1.4.6¶
Released: April 6, 2021orm¶
Fixed regression where a deprecated form of
Query.join()
were used, passing a series of entities to join from without any ON clause in a singleQuery.join()
call, would fail to function correctly.References: #6203
Fixed critical regression where the
Query.yield_per()
method in the ORM would set up the internalResult
to yield chunks at a time, however made use of the newResult.unique()
method which uniques across the entire result. This would lead to lost rows since the ORM is usingid(obj)
as the uniquing function, which leads to repeated identifiers for new objects as already-seen objects are garbage collected. 1.3’s behavior here was to “unique” across each chunk, which does not actually produce “uniqued” results when results are yielded in chunks. As theQuery.yield_per()
method is already explicitly disallowed when joined eager loading is in place, which is the primary rationale for the “uniquing” feature, the “uniquing” feature is now turned off entirely whenQuery.yield_per()
is used.This regression only applies to the legacy
Query
object; when using 2.0 style execution, “uniquing” is not automatically applied. To prevent the issue from arising from explicit use ofResult.unique()
, an error is now raised if rows are fetched from a “uniqued” ORM-levelResult
if any yield per API is also in use, as the purpose ofyield_per
is to allow for arbitrarily large numbers of rows, which cannot be uniqued in memory without growing the number of entries to fit the complete result size.References: #6206
sql¶
Fixed further regressions in the same area as that of #6173 released in 1.4.5, where a “postcompile” parameter, again most typically those used for LIMIT/OFFSET rendering in Oracle and SQL Server, would fail to be processed correctly if the same parameter rendered in multiple places in the statement.
References: #6202
Executing a
Subquery
usingConnection.execute()
is deprecated and will emit a deprecation warning; this use case was an oversight that should have been removed from 1.4. The operation will now execute the underlyingSelect
object directly for backwards compatibility. Similarly, theCTE
class is also not appropriate for execution. In 1.3, attempting to execute a CTE would result in an invalid “blank” SQL statement being executed; since this use case was not working it now raisesObjectNotExecutableError
. Previously, 1.4 was attempting to execute the CTE as a statement however it was working only erratically.References: #6204
schema¶
The
Table
object now raises an informative error message if it is instantiated without passing at least theTable.name
andTable.metadata
arguments positionally. Previously, if these were passed as keyword arguments, the object would silently fail to initialize correctly.This change is also backported to: 1.3.25
References: #6135
mypy¶
Applied a series of refactorings and fixes to accommodate for Mypy “incremental” mode across multiple files, which previously was not taken into account. In this mode the Mypy plugin has to accommodate Python datatypes expressed in other files coming in with less information than they have on a direct run.
Additionally, a new decorator
declarative_mixin()
is added, which is necessary for the Mypy plugin to be able to definifitely identify a Declarative mixin class that is otherwise not used inside a particular Python file.References: #6147
Fixed issue where the Mypy plugin would fail to interpret the “collection_class” of a relationship if it were a callable and not a class. Also improved type matching and error reporting for collection-oriented relationships.
References: #6205
asyncio¶
Added accessors
.sqlstate
and synonym.pgcode
to the.orig
attribute of the SQLAlchemy exception class raised by the asyncpg DBAPI adapter, that is, the intermediary exception object that wraps on top of that raised by the asyncpg library itself, but below the level of the SQLAlchemy dialect.References: #6199
1.4.5¶
Released: April 2, 2021orm¶
Fixed regression where the
joinedload()
loader strategy would not successfully joinedload to a mapper that is mapper against aCTE
construct.References: #6172
Scaled back the warning message added in #5171 to not warn for overlapping columns in an inheritance scenario where a particular relationship is local to a subclass and therefore does not represent an overlap.
References: #6171
sql¶
Fixed bug in new
FunctionElement.render_derived()
feature where column names rendered out explicitly in the alias SQL would not have proper quoting applied for case sensitive names and other non-alphanumeric names.References: #6183
Fixed regression where use of the
Operators.in_()
method with aSelect
object against a non-table-bound column would produce anAttributeError
, or more generally using aScalarSelect
that has no datatype in a binary expression would produce invalid state.References: #6181
Added a new flag to the
Dialect
class calledDialect.supports_statement_cache
. This flag now needs to be present directly on a dialect class in order for SQLAlchemy’s query cache to take effect for that dialect. The rationale is based on discovered issues such as #6173 revealing that dialects which hardcode literal values from the compiled statement, often the numerical parameters used for LIMIT / OFFSET, will not be compatible with caching until these dialects are revised to use the parameters present in the statement only. For third party dialects where this flag is not applied, the SQL logging will show the message “dialect does not support caching”, indicating the dialect should seek to apply this flag once they have verified that no per-statement literal values are being rendered within the compilation phase.See also
References: #6184
schema¶
Introduce a new parameter
Enum.omit_aliases
inEnum
type allow filtering aliases when using a pep435 Enum. Previous versions of SQLAlchemy kept aliases in all cases, creating database enum type with additional states, meaning that they were treated as different values in the db. For backward compatibility this flag defaults toFalse
in the 1.4 series, but will be switched toTrue
in a future version. A deprecation warning is raise if this flag is not specified and the passed enum contains aliases.References: #6146
mypy¶
Fixed issue in mypy plugin where newly added support for
as_declarative()
needed to more fully add theDeclarativeMeta
class to the mypy interpreter’s state so that it does not result in a name not found error; additionally improves how global names are setup for the plugin including theMapped
name.References: #sqlalchemy/sqlalchemy2-stubs/#14
asyncio¶
Fixed issue where the asyncio extension could not be loaded if running Python 3.6 with the backport library of
contextvars
installed.References: #6166
postgresql¶
Fixed regression caused by #6023 where the PostgreSQL cast operator applied to elements within an
ARRAY
when using psycopg2 would fail to use the correct type in the case that the datatype were also embedded within an instance of theVariant
adapter.Additionally, repairs support for the correct CREATE TYPE to be emitted when using a
Variant(ARRAY(some_schema_type))
.This change is also backported to: 1.3.25
References: #6182
Fixed typo in the fix for #6099 released in 1.4.4 that completely prevented this change from working correctly, i.e. the error message did not match what was actually emitted by pg8000.
References: #6099
Fixed issue where the PostgreSQL
PGInspector
, when generated against anEngine
, would fail for.get_enums()
,.get_view_names()
,.get_foreign_table_names()
and.get_table_oid()
when used against a “future” style engine and not the connection directly.References: #6170
mysql¶
Fixed regression in the MySQL dialect where the reflection query used to detect if a table exists would fail on very old MySQL 5.0 and 5.1 versions.
References: #6163
mssql¶
Fixed a regression in MSSQL 2012+ that prevented the order by clause to be rendered when
offset=0
is used in a subquery.References: #6163
oracle¶
Fixed critical regression where the Oracle compiler would not maintain the correct parameter values in the LIMIT/OFFSET for a select due to a caching issue.
References: #6173
1.4.4¶
Released: March 30, 2021orm¶
Fixed critical issue in the new
PropComparator.and_()
feature where loader strategies that emit secondary SELECT statements such asselectinload()
andlazyload()
would fail to accommodate for bound parameters in the user-defined criteria in terms of the current statement being executed, as opposed to the cached statement, causing stale bound values to be used.This also adds a warning for the case where an object that uses
lazyload()
in conjunction withPropComparator.and_()
is attempted to be serialized; the loader criteria cannot reliably be serialized and deserialized and eager loading should be used for this case.References: #6139
Fixed missing method
Session.get()
from theScopedSession
interface.References: #6144
engine¶
Modified the context manager used by
Transaction
so that an “already detached” warning is not emitted by the ending of the context manager itself, if the transaction were already manually rolled back inside the block. This applies to regular transactions, savepoint transactions, and legacy “marker” transactions. A warning is still emitted if the.rollback()
method is called explicitly more than once.References: #6155
Repair wrong arguments to exception handling method in CursorResult.
References: #6138
postgresql¶
Fixed issue in PostgreSQL reflection where a column expressing “NOT NULL” will supersede the nullability of a corresponding domain.
This change is also backported to: 1.3.24
References: #6161
Modified the
is_disconnect()
handler for the pg8000 dialect, which now accommodates for a newInterfaceError
emitted by pg8000 1.19.0. Pull request courtesy Hamdi Burak Usul.References: #6099
misc¶
Adjusted the usage of the
importlib_metadata
library for loading setuptools entrypoints in order to accommodate for some deprecation changes.
1.4.3¶
Released: March 25, 2021orm¶
Fixed a bug where python 2.7.5 (default on CentOS 7) wasn’t able to import sqlalchemy, because on this version of Python
exec "statement"
andexec("statement")
do not behave the same way. The compatibilityexec_()
function was used instead.References: #6069
Fixed bug where ORM queries using a correlated subquery in conjunction with
column_property()
would fail to correlate correctly to an enclosing subquery or to a CTE whenSelect.correlate_except()
were used in the property to control correlation, in cases where the subquery contained the same selectables as ones within the correlated subquery that were intended to not be correlated.References: #6060
Fixed bug where combinations of the new “relationship with criteria” feature could fail in conjunction with features that make use of the new “lambda SQL” feature, including loader strategies such as selectinload and lazyload, for more complicated scenarios such as polymorphic loading.
References: #6131
Repaired support so that the
ClauseElement.params()
method can work correctly with aSelect
object that includes joins across ORM relationship structures, which is a new feature in 1.4.References: #6124
Fixed issue where a “removed in 2.0” warning were generated internally by the relationship loader mechanics.
References: #6115
orm declarative¶
engine¶
Restored the
ResultProxy
name back to thesqlalchemy.engine
namespace. This name refers to theLegacyCursorResult
object.References: #6119
schema¶
Adjusted the logic that emits DROP statements for
Sequence
objects among the dropping of multiple tables, such that allSequence
objects are dropped after all tables, even if the givenSequence
is related only to aTable
object and not directly to the overallMetaData
object. The use case supports the sameSequence
being associated with more than oneTable
at a time.This change is also backported to: 1.3.24
References: #6071
mypy¶
Added support for the Mypy extension to correctly interpret a declarative base class that’s generated using the
as_declarative()
function as well as theregistry.as_declarative_base()
method.Fixed bug in Mypy plugin where the Python type detection for the
Boolean
column type would produce an exception; additionally implemented support forEnum
, including detection of a string-based enum vs. use of Pythonenum.Enum
.References: #6109
postgresql¶
Adjusted the psycopg2 dialect to emit an explicit PostgreSQL-style cast for bound parameters that contain ARRAY elements. This allows the full range of datatypes to function correctly within arrays. The asyncpg dialect already generated these internal casts in the final statement. This also includes support for array slice updates as well as the PostgreSQL-specific
ARRAY.contains()
method.This change is also backported to: 1.3.24
References: #6023
Fixed reflection of identity columns in tables with mixed case names in PostgreSQL.
References: #6129
sqlite¶
Added support for the aiosqlite database driver for use with the SQLAlchemy asyncio extension.
See also
References: #5920
Repaired the
pysqlcipher
dialect to connect correctly which had regressed in 1.4, and added test + CI support to maintain the driver in working condition. The dialect now imports thesqlcipher3
module for Python 3 by default before falling back topysqlcipher3
which is documented as now being unmaintained.See also
References: #5848
1.4.2¶
Released: March 19, 2021orm¶
Added support for the
declared_attr
object to work in the context of dataclass fields.References: #6100
Fixed issue in new ORM dataclasses functionality where dataclass fields on an abstract base or mixin that contained column or other mapping constructs would not be mapped if they also included a “default” key within the dataclasses.field() object.
References: #6093
Fixed regression where the
Query.selectable
accessor, which is a synonym forQuery.__clause_element__()
, got removed, it’s now restored.References: #6088
Fixed regression where use of an unnamed SQL expression such as a SQL function would raise a column targeting error if the query itself were using joinedload for an entity and was also being wrapped in a subquery by the joinedload eager loading process.
References: #6086
Fixed regression where the
Query.filter_by()
method would fail to locate the correct source entity if theQuery.join()
method had been used targeting an entity without any kind of ON clause.References: #6092
Fixed regression where the SQL compilation of a
Function
would not work correctly if the object had been “annotated”, which is an internal memoization process used mostly by the ORM. In particular it could affect ORM lazy loads which make greater use of this feature in 1.4.References: #6095
Fixed regression where the
ConcreteBase
would fail to map at all when a mapped column name overlapped with the discriminator column name, producing an assertion error. The use case here did not function correctly in 1.3 as the polymorphic union would produce a query that ignored the discriminator column entirely, while emitting duplicate column warnings. As 1.4’s architecture cannot easily reproduce this essentially broken behavior of 1.3 at theselect()
level right now, the use case now raises an informative error message instructing the user to use the.ConcreteBase._concrete_discriminator_name
attribute to resolve the conflict. To assist with this configuration,.ConcreteBase._concrete_discriminator_name
may be placed on the base class only where it will be automatically used by subclasses; previously this was not the case.References: #6090
engine¶
sql¶
Fixed issue where using a
func
that includes dotted packagenames would fail to be cacheable by the SQL caching system due to a Python list of names that needed to be a tuple.References: #6101
Fixed regression in the
case()
construct, where the “dictionary” form of argument specification failed to work correctly if it were passed positionally, rather than as a “whens” keyword argument.References: #6097
mypy¶
Fixed issue in MyPy extension which crashed on detecting the type of a
Column
if the type were given with a module prefix likesa.Integer()
.References: #sqlalchemy/sqlalchemy2-stubs/2
postgresql¶
Rename the column name used by a reflection query that used a reserved word in some postgresql compatible databases.
References: #6982
1.4.1¶
Released: March 17, 2021orm¶
Fixed regression where producing a Core expression construct such as
select()
using ORM entities would eagerly configure the mappers, in an effort to maintain compatibility with theQuery
object which necessarily does this to support many backref-related legacy cases. However, coreselect()
constructs are also used in mapper configurations and such, and to that degree this eager configuration is more of an inconvenience, so eager configure has been disabled for theselect()
and other Core constructs in the absence of ORM loading types of functions such asLoad
.The change maintains the behavior of
Query
so that backwards compatibility is maintained. However, when using aselect()
in conjunction with ORM entities, a “backref” that isn’t explicitly placed on one of the classes until mapper configure time won’t be available unlessconfigure_mappers()
or the newerconfigure()
has been called elsewhere. Prefer usingrelationship.back_populates
for more explicit relationship configuration which does not have the eager configure requirement.References: #6066
Fixed a critical regression in the relationship lazy loader where the SQL criteria used to fetch a related many-to-one object could go stale in relation to other memoized structures within the loader if the mapper had configuration changes, such as can occur when mappers are late configured or configured on demand, producing a comparison to None and returning no object. Huge thanks to Alan Hamlett for their help tracking this down late into the night.
References: #6055
Fixed regression where the
Query.exists()
method would fail to create an expression if the entity list of theQuery
were an arbitrary SQL column expression.References: #6076
Fixed regression where calling upon
Query.count()
in conjunction with a loader option such asjoinedload()
would fail to ignore the loader option. This is a behavior that has always been very specific to theQuery.count()
method; an error is normally raised if a givenQuery
has options that don’t apply to what it is returning.References: #6052
Fixed regression in
Session.identity_key()
, including that the method and related methods were not covered by any unit test as well as that the method contained a typo preventing it from functioning correctly.References: #6067
orm declarative¶
Fixed bug where user-mapped classes that contained an attribute named “registry” would cause conflicts with the new registry-based mapping system when using
DeclarativeMeta
. While the attribute remains something that can be set explicitly on a declarative base to be consumed by the metaclass, once located it is placed under a private class variable so it does not conflict with future subclasses that use the same name for other purposes.References: #6054
engine¶
The Python
namedtuple()
has the behavior such that the namescount
andindex
will be served as tuple values if the named tuple includes those names; if they are absent, then their behavior as methods ofcollections.abc.Sequence
is maintained. Therefore theRow
andLegacyRow
classes have been fixed so that they work in this same way, maintaining the expected behavior for database rows that have columns named “index” or “count”.References: #6074
mssql¶
Fixed regression where a new setinputsizes() API that’s available for pyodbc was enabled, which is apparently incompatible with pyodbc’s fast_executemany() mode in the absence of more accurate typing information, which as of yet is not fully implemented or tested. The pyodbc dialect and connector has been modified so that setinputsizes() is not used at all unless the parameter
use_setinputsizes
is passed to the dialect, e.g. viacreate_engine()
, at which point its behavior can be customized using theDialectEvents.do_setinputsizes()
hook.See also
References: #6058
misc¶
Added back
items
andvalues
toColumnCollection
class. The regression was introduced while adding support for duplicate columns in from clauses and selectable in ticket #4753.References: #6068
1.4.0¶
Released: March 15, 2021orm¶
Removed very old warning that states that passive_deletes is not intended for many-to-one relationships. While it is likely that in many cases placing this parameter on a many-to-one relationship is not what was intended, there are use cases where delete cascade may want to be disallowed following from such a relationship.
This change is also backported to: 1.3.24
References: #5983
Fixed issue where the process of joining two tables could fail if one of the tables had an unrelated, unresolvable foreign key constraint which would raise
NoReferenceError
within the join process, which nonetheless could be bypassed to allow the join to complete. The logic which tested the exception for significance within the process would make assumptions about the construct which would fail.This change is also backported to: 1.3.24
References: #5952
Fixed issue where the
MutableComposite
construct could be placed into an invalid state when the parent object was already loaded, and then covered by a subsequent query, due to the composite properties’ refresh handler replacing the object with a new one not handled by the mutable extension.This change is also backported to: 1.3.24
References: #6001
Fixed regression where the
relationship.query_class
parameter stopped being functional for “dynamic” relationships. TheAppenderQuery
remains dependent on the legacyQuery
class; users are encouraged to migrate from the use of “dynamic” relationships to usingwith_parent()
instead.References: #5981
Fixed regression where
Query.join()
would produce no effect if the query itself as well as the join target were against aTable
object, rather than a mapped class. This was part of a more systemic issue where the legacy ORM query compiler would not be correctly used from aQuery
if the statement produced had not ORM entities present within it.References: #6003
The API for
AsyncSession.delete()
is now an awaitable; this method cascades along relationships which must be loaded in a similar manner as theAsyncSession.merge()
method.References: #5998
The unit of work process now turns off all “lazy=’raise’” behavior altogether when a flush is proceeding. While there are areas where the UOW is sometimes loading things that aren’t ultimately needed, the lazy=”raise” strategy is not helpful here as the user often does not have much control or visibility into the flush process.
References: #5984
engine¶
Fixed bug where the “schema_translate_map” feature failed to be taken into account for the use case of direct execution of
DefaultGenerator
objects such as sequences, which included the case where they were “pre-executed” in order to generate primary key values when implicit_returning was disabled.This change is also backported to: 1.3.24
References: #5929
Improved engine logging to note ROLLBACK and COMMIT which is logged while the DBAPI driver is in AUTOCOMMIT mode. These ROLLBACK/COMMIT are library level and do not have any effect when AUTOCOMMIT is in effect, however it’s still worthwhile to log as these indicate where SQLAlchemy sees the “transaction” demarcation.
References: #6002
Fixed a regression where the “reset agent” of the connection pool wasn’t really being utilized by the
Connection
when it were closed, and also leading to a double-rollback scenario that was somewhat wasteful. The newer architecture of the engine has been updated so that the connection pool “reset-on-return” logic will be skipped when theConnection
explicitly closes out the transaction before returning the pool to the connection.References: #6004
sql¶
Altered the compilation for the
CTE
construct so that a string is returned representing the inner SELECT statement if theCTE
is stringified directly, outside of the context of an enclosing SELECT; This is the same behavior ofFromClause.alias()
andSelect.subquery()
. Previously, a blank string would be returned as the CTE is normally placed above a SELECT after that SELECT has been generated, which is generally misleading when debugging.Fixed bug where the “percent escaping” feature that occurs with dialects that use the “format” or “pyformat” bound parameter styles was not enabled for the
Operators.op()
andcustom_op
constructs, for custom operators that use percent signs. The percent sign will now be automatically doubled based on the paramstyle as necessary.References: #6016
Fixed regression where the “unsupported compilation error” for unknown datatypes would fail to raise correctly.
References: #5979
Fixed regression where usage of the standalone
distinct()
used in the form of being directly SELECTed would fail to be locatable in the result set by column identity, which is how the ORM locates columns. While standalonedistinct()
is not oriented towards being directly SELECTed (useselect.distinct()
for a regularSELECT DISTINCT..
) , it was usable to a limited extent in this way previously (but wouldn’t work in subqueries, for example). The column targeting for unary expressions such as “DISTINCT <col>” has been improved so that this case works again, and an additional improvement has been made so that usage of this form in a subquery at least generates valid SQL which was not the case previously.The change additionally enhances the ability to target elements in
row._mapping
based on SQL expression objects in ORM-enabled SELECT statements, including whether the statement was invoked byconnection.execute()
orsession.execute()
.References: #6008
schema¶
Fixed issue where the CHECK constraint generated by
Boolean
orEnum
would fail to render the naming convention correctly after the first compilation, due to an unintended change of state within the name given to the constraint. This issue was first introduced in 0.9 in the fix for issue #3067, and the fix revises the approach taken at that time which appears to have been more involved than what was needed.This change is also backported to: 1.3.24
References: #6007
Repaired / implemented support for primary key constraint naming conventions that use column names/keys/etc as part of the convention. In particular, this includes that the
PrimaryKeyConstraint
object that’s automatically associated with aTable
will update its name as new primary keyColumn
objects are added to the table and then to the constraint. Internal failure modes related to this constraint construction process including no columns present, no name present or blank name present are now accommodated.This change is also backported to: 1.3.24
References: #5919
Deprecated all schema-level
.copy()
methods and renamed to_copy()
. These are not standard Python “copy()” methods as they typically rely upon being instantiated within particular contexts which are passed to the method as optional keyword arguments. TheTable.tometadata()
method is the public API that provides copying forTable
objects.References: #5953
mypy¶
Rudimentary and experimental support for Mypy has been added in the form of a new plugin, which itself depends on new typing stubs for SQLAlchemy. The plugin allows declarative mappings in their standard form to both be compatible with Mypy as well as to provide typing support for mapped classes and instances.
References: #4609
postgresql¶
Added an
asyncio.Lock()
within SQLAlchemy’s emulated DBAPI cursor, local to the connection, for the asyncpg and aiomysql dialects for the scope of thecursor.execute()
andcursor.executemany()
methods. The rationale is to prevent failures and corruption for the case where the connection is used in multiple awaitables at once.While this use case can also occur with threaded code and non-asyncio dialects, we anticipate this kind of use will be more common under asyncio, as the asyncio API is encouraging of such use. It’s definitely better to use a distinct connection per concurrent awaitable however as concurrency will not be achieved otherwise.
For the asyncpg dialect, this is so that the space between the call to
prepare()
andfetch()
is prevented from allowing concurrent executions on the connection from causing interface error exceptions, as well as preventing race conditions when starting a new transaction. Other PostgreSQL DBAPIs are threadsafe at the connection level so this intends to provide a similar behavior, outside the realm of server side cursors.For the aiomysql dialect, the mutex will provide safety such that the statement execution and the result set fetch, which are two distinct steps at the connection level, won’t get corrupted by concurrent executions on the same connection.
References: #5967
Fixed issue where using
aggregate_order_by
would return ARRAY(NullType) under certain conditions, interfering with the ability of the result object to return data correctly.This change is also backported to: 1.3.24
References: #5989
mssql¶
Fix a reflection error for MSSQL 2005 introduced by the reflection of filtered indexes.
References: #5919
misc¶
Add new parameter
AutomapBase.prepare.reflection_options
to allow passing ofMetaData.reflect()
options likeonly
or dialect-specific reflection options likeoracle_resolve_synonyms
.References: #5942
The
sqlalchemy.ext.mutable
extension now tracks the “parents” collection using theInstanceState
associated with objects, rather than the object itself. The latter approach required that the object be hashable so that it can be inside of aWeakKeyDictionary
, which goes against the behavioral contract of the ORM overall which is that ORM mapped objects do not need to provide any particular kind of__hash__()
method and that unhashable objects are supported.References: #6020
1.4.0b3¶
Released: February 15, 2021orm¶
The ORM used in 2.0 style can now return ORM objects from the rows returned by an UPDATE..RETURNING or INSERT..RETURNING statement, by supplying the construct to
Select.from_statement()
in an ORM context.Fixed issue in new 1.4/2.0 style ORM queries where a statement-level label style would not be preserved in the keys used by result rows; this has been applied to all combinations of Core/ORM columns / session vs. connection etc. so that the linkage from statement to result row is the same in all cases. As part of this change, the labeling of column expressions in rows has been improved to retain the original name of the ORM attribute even if used in a subquery.
References: #5933
engine¶
Continued with the improvement made as part of #5653 to further support bound parameter names, including those generated against column names, for names that include colons, parenthesis, and question marks, as well as improved test support, so that bound parameter names even if they are auto-derived from column names should have no problem including for parenthesis in psycopg2’s “pyformat” style.
As part of this change, the format used by the asyncpg DBAPI adapter (which is local to SQLAlchemy’s asyncpg dialect) has been changed from using “qmark” paramstyle to “format”, as there is a standard and internally supported SQL string escaping style for names that use percent signs with “format” style (i.e. to double percent signs), as opposed to names that use question marks with “qmark” style (where an escaping system is not defined by pep-249 or Python).
References: #5941
sql¶
Enhance
set_
keyword ofOnConflictDoUpdate
to accept aColumnCollection
, such as the.c.
collection from aSelectable
, or the.excluded
contextual object.References: #5939
Fixed bug where the “cartesian product” assertion was not correctly accommodating for joins between tables that relied upon the use of LATERAL to connect from a subquery to another subquery in the enclosing context.
References: #5924
Fixed 1.4 regression where the
Function.in_()
method was not covered by tests and failed to function properly in all cases.References: #5934
Fixed regression where use of an arbitrary iterable with the
select()
function was not working, outside of plain lists. The forwards/backwards compatibility logic here now checks for a wider range of incoming “iterable” types including that a.c
collection from a selectable can be passed directly. Pull request compliments of Oliver Rice.References: #5935
1.4.0b2¶
Released: February 3, 2021general¶
Fixed a SQLite source file that had non-ascii characters inside of its docstring without a source encoding, introduced within the “INSERT..ON CONFLICT” feature, which would cause failures under Python 2.
platform¶
Adjusted some elements related to internal class production at import time which added significant latency to the time spent to import the library vs. that of 1.3. The time is now about 20-30% slower than 1.3 instead of 200%.
References: #5681
orm¶
Added
ORMExecuteState.bind_mapper
andORMExecuteState.all_mappers
accessors toORMExecuteState
event object, so that handlers can respond to the target mapper and/or mapped class or classes involved in an ORM statement execution.Added
AsyncSession.scalar()
,AsyncSession.get()
as well as support forsessionmaker.begin()
to work as an async context manager withAsyncSession
. Also addedAsyncSession.in_transaction()
accessor.Mapper “configuration”, which occurs within the
configure_mappers()
function, is now organized to be on a per-registry basis. This allows for example the mappers within a certain declarative base to be configured, but not those of another base that is also present in memory. The goal is to provide a means of reducing application startup time by only running the “configure” process for sets of mappers that are needed. This also adds theregistry.configure()
method that will run configure for the mappers local in a particular registry only.References: #5897
Added a comprehensive check and an informative error message for the case where a mapped class, or a string mapped class name, is passed to
relationship.secondary
. This is an extremely common error which warrants a clear message.Additionally, added a new rule to the class registry resolution such that with regards to the
relationship.secondary
parameter, if a mapped class and its table are of the identical string name, theTable
will be favored when resolving this parameter. In all other cases, the class continues to be favored if a class and table share the identical name.This change is also backported to: 1.3.21
References: #5774
Fixed bug involving the
restore_load_context
option of ORM events such asInstanceEvents.load()
such that the flag would not be carried along to subclasses which were mapped after the event handler were first established.This change is also backported to: 1.3.21
References: #5737
Fixed issue in new
Session
similar to that of theConnection
where the new “autobegin” logic could be tripped into a re-entrant (recursive) state if SQL were executed within theSessionEvents.after_transaction_create()
event hook.References: #5845
Improved the unit of work topological sorting system such that the toplogical sort is now deterministic based on the sorting of the input set, which itself is now sorted at the level of mappers, so that the same inputs of affected mappers should produce the same output every time, among mappers / tables that don’t have any dependency on each other. This further reduces the chance of deadlocks as can be observed in a flush that UPDATEs among multiple, unrelated tables such that row locks are generated.
References: #5735
Fixed regression where the
Bundle.single_entity
flag would take effect for aBundle
even though it were not set. Additionally, this flag is legacy as it only makes sense for theQuery
object and not 2.0 style execution. a deprecation warning is emitted when used with new-style execution.References: #5702
Fixed regression where creating an
aliased
construct against a plain selectable and including a name would raise an assertionerror.References: #5750
Related to the fixes for the lambda criteria system within Core, within the ORM implemented a variety of fixes for the
with_loader_criteria()
feature as well as theSessionEvents.do_orm_execute()
event handler that is often used in conjunction [ticket:5760]:fixed issue where
with_loader_criteria()
function would fail if the given entity or base included non-mapped mixins in its descending class hierarchy [ticket:5766]The
with_loader_criteria()
feature is now unconditionally disabled for the case of ORM “refresh” operations, including loads of deferred or expired column attributes as well as for explicit operations likeSession.refresh()
. These loads are necessarily based on primary key identity where additional WHERE criteria is never appropriate. [ticket:5762]Added new attribute
ORMExecuteState.is_column_load
to indicate that aSessionEvents.do_orm_execute()
handler that a particular operation is a primary-key-directed column attribute load, where additional criteria should not be added. Thewith_loader_criteria()
function as above ignores these in any case now. [ticket:5761]Fixed issue where the
ORMExecuteState.is_relationship_load
attribute would not be set correctly for many lazy loads as well as all selectinloads. The flag is essential in order to test if options should be added to statements or if they would already have been propagated via relationship loads. [ticket:5764]
Fixed 1.4 regression where the use of
Query.having()
in conjunction with queries with internally adapted SQL elements (common in inheritance scenarios) would fail due to an incorrect function call. Pull request courtesy esoh.References: #5781
Fixed an issue where the API to create a custom executable SQL construct using the
sqlalchemy.ext.compiles
extension according to the documentation that’s been up for many years would no longer function if onlyExecutable, ClauseElement
were used as the base classes, additional classes were needed if wanting to useSession.execute()
. This has been resolved so that those extra classes aren’t needed.Fixed ORM unit of work regression where an errant “assert primary_key” statement interferes with primary key generation sequences that don’t actually consider the columns in the table to use a real primary key constraint, instead using
Mapper.primary_key
to establish certain columns as “primary”.References: #5867
orm declarative¶
Added an alternate resolution scheme to Declarative that will extract the SQLAlchemy column or mapped property from the “metadata” dictionary of a dataclasses.Field object. This allows full declarative mappings to be combined with dataclass fields.
References: #5745
engine¶
Dialect-specific constructs such as
Insert.on_conflict_do_update()
can now stringify in-place without the need to specify an explicit dialect object. The constructs, when called upon forstr()
,print()
, etc. now have internal direction to call upon their appropriate dialect rather than the “default”dialect which doesn’t know how to stringify these. The approach is also adapted to generic schema-level create/drop such asAddConstraint
, which will adapt its stringify dialect to one indicated by the element within it, such as theExcludeConstraint
object.Added new execution option
Connection.execution_options.logging_token
. This option will add an additional per-message token to log messages generated by theConnection
as it executes statements. This token is not part of the logger name itself (that part can be affected using the existingcreate_engine.logging_name
parameter), so is appropriate for ad-hoc connection use without the side effect of creating many new loggers. The option can be set at the level ofConnection
orEngine
.References: #5911
Fixed bug in the 2.0 “future” version of
Engine
where emitting SQL during theEngineEvents.begin()
event hook would cause a re-entrant (recursive) condition due to autobegin, affecting among other things the recipe documented for SQLite to allow for savepoints and serializable isolation support.References: #5845
Adjusted the “setinputsizes” logic relied upon by the cx_Oracle, asyncpg and pg8000 dialects to support a
TypeDecorator
that includes an override theTypeDecorator.get_dbapi_type()
method.Added the “future” keyword to the list of words that are known by the
engine_from_config()
function, so that the values “true” and “false” may be configured as “boolean” values when using a key such assqlalchemy.future = true
orsqlalchemy.future = false
.
sql¶
Implemented support for “table valued functions” along with additional syntaxes supported by PostgreSQL, one of the most commonly requested features. Table valued functions are SQL functions that return lists of values or rows, and are prevalent in PostgreSQL in the area of JSON functions, where the “table value” is commonly referred towards as the “record” datatype. Table valued functions are also supported by Oracle and SQL Server.
Features added include:
the
FunctionElement.table_valued()
modifier that creates a table-like selectable object from a SQL functionA
TableValuedAlias
construct that renders a SQL function as a named tableSupport for PostgreSQL’s special “derived column” syntax that includes column names and sometimes datatypes, such as for the
json_to_recordset
function, using theTableValuedAlias.render_derived()
method.Support for PostgreSQL’s “WITH ORDINALITY” construct using the
FunctionElement.table_valued.with_ordinality
parameterSupport for selection FROM a SQL function as column-valued scalar, a syntax supported by PostgreSQL and Oracle, via the
FunctionElement.column_valued()
methodA way to SELECT a single column from a table-valued expression without using a FROM clause via the
FunctionElement.scalar_table_valued()
method.
See also
References: #3566
Multiple calls to “returning”, e.g.
Insert.returning()
, may now be chained to add new columns to the RETURNING clause.References: #5695
Added
Select.outerjoin_from()
method to complementSelect.join_from()
.Adjusted the “literal_binds” feature of
Compiler
to render NULL for a bound parameter that hasNone
as the value, either explicitly passed or omitted. The previous error message “bind parameter without a renderable value” is removed, and a missing orNone
value will now render NULL in all cases. Previously, rendering of NULL was starting to happen for DML statements due to internal refactorings, but was not explicitly part of test coverage, which it now is.While no error is raised, when the context is within that of a column comparison, and the operator is not “IS”/”IS NOT”, a warning is emitted that this is not generally useful from a SQL perspective.
References: #5888
Fixed issue in new
Select.join()
method where chaining from the current JOIN wasn’t looking at the right state, causing an expression like “FROM a JOIN b <onclause>, b JOIN c <onclause>” rather than “FROM a JOIN b <onclause> JOIN c <onclause>”.References: #5858
Deprecation warnings are emitted under “SQLALCHEMY_WARN_20” mode when passing a plain string to
Session.execute()
.References: #5754
A wide variety of fixes to the “lambda SQL” feature introduced at Using Lambdas to add significant speed gains to statement production have been implemented based on user feedback, with an emphasis on its use within the
with_loader_criteria()
feature where it is most prominently used [ticket:5760]:fixed issue where boolean True/False values referred towards in the closure variables of the lambda would cause failures [ticket:5763]
Repaired a non-working detection for Python functions embedded in the lambda that produce bound values; this case is likely not supportable so raises an informative error, where the function should be invoked outside the lambda itself. New documentation has been added to further detail this behavior. [ticket:5770]
The lambda system by default now rejects the use of non-SQL elements within the closure variables of the lambda entirely, where the error suggests the two options of either explicitly ignoring closure variables that are not SQL parameters, or specifying a specific set of values to be considered as part of the cache key based on hash value. This critically prevents the lambda system from assuming that arbitrary objects within the lambda’s closure are appropriate for caching while also refusing to ignore them by default, preventing the case where their state might not be constant and have an impact on the SQL construct produced. The error message is comprehensive and new documentation has been added to further detail this behavior. [ticket:5765]
Fixed support for the edge case where an
in_()
expression against a list of SQL elements, such asliteral()
objects, would fail to be accommodated correctly. [ticket:5768]
An informative error message is now raised for a selected set of DML methods (currently all part of
Insert
constructs) if they are called a second time, which would implicitly cancel out the previous setting. The methods altered include:on_conflict_do_update
,on_conflict_do_nothing
(SQLite),on_conflict_do_update
,on_conflict_do_nothing
(PostgreSQL),on_duplicate_key_update
(MySQL)References: #5169
Fixed issue in new
Values
construct where passing tuples of objects would fall back to per-value type detection rather than making use of theColumn
objects passed directly toValues
that tells SQLAlchemy what the expected type is. This would lead to issues for objects such as enumerations and numpy strings that are not actually necessary since the expected type is given.References: #5785
Fixed issue where a
RemovedIn20Warning
would erroneously emit when the.bind
attribute were accessed internally on objects, particularly when stringifying a SQL construct.References: #5717
Properly render
cycle=False
andorder=False
asNO CYCLE
andNO ORDER
inSequence
andIdentity
objects.References: #5722
Replace
Query.with_labels()
andGenerativeSelect.apply_labels()
with explicit getters and settersGenerativeSelect.get_label_style()
andGenerativeSelect.set_label_style()
to accommodate the three supported label styles:LABEL_STYLE_DISAMBIGUATE_ONLY
,LABEL_STYLE_TABLENAME_PLUS_COL
, andLABEL_STYLE_NONE
.In addition, for Core and “future style” ORM queries,
LABEL_STYLE_DISAMBIGUATE_ONLY
is now the default label style. This style differs from the existing “no labels” style in that labeling is applied in the case of column name conflicts; withLABEL_STYLE_NONE
, a duplicate column name is not accessible via name in any case.For cases where labeling is significant, namely that the
.c
collection of a subquery is able to refer to all columns unambiguously, the behavior ofLABEL_STYLE_DISAMBIGUATE_ONLY
is now sufficient for all SQLAlchemy features across Core and ORM which involve this behavior. Result set rows since SQLAlchemy 1.0 are usually aligned with column constructs positionally.For legacy ORM queries using
Query
, the table-plus-column names labeling style applied byLABEL_STYLE_TABLENAME_PLUS_COL
continues to be used so that existing test suites and logging facilities see no change in behavior by default.References: #4757
schema¶
Added
TypeEngine.as_generic()
to map dialect-specific types, such assqlalchemy.dialects.mysql.INTEGER
, with the “best match” generic SQLAlchemy type, in this caseInteger
. Pull request courtesy Andrew Hannigan.See also
Reflecting with Database-Agnostic Types - example usage
References: #5659
The
DDLEvents.column_reflect()
event may now be applied to aMetaData
object where it will take effect for theTable
objects local to that collection.See also
Automating Column Naming Schemes from Reflected Tables - in the ORM mapping documentation
Intercepting Column Definitions - in the Automap documentation
References: #5712
Added parameters
CreateTable.if_not_exists
,CreateIndex.if_not_exists
,DropTable.if_exists
andDropIndex.if_exists
to theCreateTable
,DropTable
,CreateIndex
andDropIndex
constructs which result in “IF NOT EXISTS” / “IF EXISTS” DDL being added to the CREATE/DROP. These phrases are not accepted by all databases and the operation will fail on a database that does not support it as there is no similarly compatible fallback within the scope of a single DDL statement. Pull request courtesy Ramon Williams.References: #2843
Altered the behavior of the
Identity
construct such that when applied to aColumn
, it will automatically imply that the value ofColumn.nullable
should default toFalse
, in a similar manner as when theColumn.primary_key
parameter is set toTrue
. This matches the default behavior of all supporting databases whereIDENTITY
impliesNOT NULL
. The PostgreSQL backend is the only one that supports addingNULL
to anIDENTITY
column, which is here supported by passing aTrue
value for theColumn.nullable
parameter at the same time.References: #5775
asyncio¶
The
AsyncEngine
,AsyncConnection
andAsyncTransaction
objects may be compared using Python==
or!=
, which will compare the two given objects based on the “sync” object they are proxying towards. This is useful as there are cases particularly forAsyncTransaction
where multiple instances ofAsyncTransaction
can be proxying towards the same syncTransaction
, and are actually equivalent. TheAsyncConnection.get_transaction()
method will currently return a new proxyingAsyncTransaction
each time as theAsyncTransaction
is not otherwise statefully associated with its originatingAsyncConnection
.Adjusted the greenlet integration, which provides support for Python asyncio in SQLAlchemy, to accommodate for the handling of Python
contextvars
(introduced in Python 3.7) forgreenlet
versions greater than 0.4.17. Greenlet version 0.4.17 added automatic handling of contextvars in a backwards-incompatible way; we’ve coordinated with the greenlet authors to add a preferred API for this in versions subsequent to 0.4.17 which is now supported by SQLAlchemy’s greenlet integration. For greenlet versions prior to 0.4.17 no behavioral change is needed, version 0.4.17 itself is blocked from the dependencies.References: #5615
Implemented “connection-binding” for
AsyncSession
, the ability to pass anAsyncConnection
to create anAsyncSession
. Previously, this use case was not implemented and would use the associated engine when the connection were passed. This fixes the issue where the “join a session to an external transaction” use case would not work correctly for theAsyncSession
. Additionally, added methodsAsyncConnection.in_transaction()
,AsyncConnection.in_nested_transaction()
,AsyncConnection.get_transaction()
,AsyncConnection.get_nested_transaction()
andAsyncConnection.info
attribute.References: #5811
Fixed bug in asyncio connection pool where
asyncio.TimeoutError
would be raised rather thanTimeoutError
. Also repaired thecreate_engine.pool_timeout
parameter set to zero when using the async engine, which previously would ignore the timeout and block rather than timing out immediately as is the behavior with regularQueuePool
.References: #5827
When using an asyncio engine, the connection pool will now detach and discard a pooled connection that is was not explicitly closed/returned to the pool when its tracking object is garbage collected, emitting a warning that the connection was not properly closed. As this operation occurs during Python gc finalizers, it’s not safe to run any IO operations upon the connection including transaction rollback or connection close as this will often be outside of the event loop.
The
AsyncAdaptedQueue
used by default on async dpapis should instantiate a queue only when it’s first used to avoid binding it to a possibly wrong event loop.References: #5823
The SQLAlchemy async mode now detects and raises an informative error when an non asyncio compatible DBAPI is used. Using a standard
DBAPI
with async SQLAlchemy will cause it to block like any sync call, interrupting the executing asyncio loop.
postgresql¶
Added new parameter
ExcludeConstraint.ops
to theExcludeConstraint
object, to support operator class specification with this constraint. Pull request courtesy Alon Menczer.This change is also backported to: 1.3.21
References: #5604
Added a read/write
.autocommit
attribute to the DBAPI-adaptation layer for the asyncpg dialect. This so that when working with DBAPI-specific schemes that need to use “autocommit” directly with the DBAPI connection, the same.autocommit
attribute which works with both psycopg2 as well as pg8000 is available.Fixed issue where the psycopg2 dialect would silently pass the
use_native_unicode=False
flag without actually having any effect under Python 3, as the psycopg2 DBAPI uses Unicode unconditionally under Python 3. This usage now raises anArgumentError
when used under Python 3. Added test support for Python 2.Enhanced the performance of the asyncpg dialect by caching the asyncpg PreparedStatement objects on a per-connection basis. For a test case that makes use of the same statement on a set of pooled connections this appears to grant a 10-20% speed improvement. The cache size is adjustable and may also be disabled.
See also
Fixed regression introduced in 1.3.2 for the PostgreSQL dialect, also copied out to the MySQL dialect’s feature in 1.3.18, where usage of a non
Table
construct such astext()
as the argument toSelect.with_for_update.of
would fail to be accommodated correctly within the PostgreSQL or MySQL compilers.This change is also backported to: 1.3.21
References: #5729
Fixed a small regression where the query for “show standard_conforming_strings” upon initialization would be emitted even if the server version info were detected as less than version 8.2, previously it would only occur for server version 8.2 or greater. The query fails on Amazon Redshift which reports a PG server version older than this value.
References: #5698
Established support for
Column
objects as well as ORM instrumented attributes as keys in theset_
dictionary passed to theInsert.on_conflict_do_update()
andInsert.on_conflict_do_update()
methods, which match to theColumn
objects in the.c
collection of the targetTable
. Previously, only string column names were expected; a column expression would be assumed to be an out-of-table expression that would render fully along with a warning.References: #5722
Fixed bug in asyncpg dialect where a failure during a “commit” or less likely a “rollback” should cancel the entire transaction; it’s no longer possible to emit rollback. Previously the connection would continue to await a rollback that could not succeed as asyncpg would reject it.
References: #5824
mysql¶
Added support for the aiomysql driver when using the asyncio SQLAlchemy extension.
See also
References: #5747
Fixed issue where reflecting a server default on MariaDB only that contained a decimal point in the value would fail to be reflected correctly, leading towards a reflected table that lacked any server default.
This change is also backported to: 1.3.21
References: #5744
sqlite¶
Implemented INSERT… ON CONFLICT clause for SQLite. Pull request courtesy Ramon Williams.
See also
References: #4010
Use python
re.search()
instead ofre.match()
as the operation used by theColumn.regexp_match()
method when using sqlite. This matches the behavior of regular expressions on other databases as well as that of well-known SQLite plugins.References: #5699
mssql¶
Decimal accuracy and behavior has been improved when extracting floating point and/or decimal values from JSON strings using the
Comparator.as_float()
method, when the numeric value inside of the JSON string has many significant digits; previously, MySQL backends would truncate values with many significant digits and SQL Server backends would raise an exception due to a DECIMAL cast with insufficient significant digits. Both backends now use a FLOAT-compatible approach that does not hardcode significant digits for floating point values. For precision numerics, a new methodComparator.as_numeric()
has been added which accepts arguments for precision and scale, and will return values as PythonDecimal
objects with no floating point conversion assuming the DBAPI supports it (all but pysqlite).References: #5788
oracle¶
Fixed regression which occurred due to #5755 which implemented isolation level support for Oracle. It has been reported that many Oracle accounts don’t actually have permission to query the
v$transaction
view so this feature has been altered to gracefully fallback when it fails upon database connect, where the dialect will assume “READ COMMITTED” is the default isolation level as was the case prior to SQLAlchemy 1.3.21. However, explicit use of theConnection.get_isolation_level()
method must now necessarily raise an exception, as Oracle databases with this restriction explicitly disallow the user from reading the current isolation level.This change is also backported to: 1.3.22
References: #5784
Oracle two-phase transactions at a rudimentary level are now no longer deprecated. After receiving support from cx_Oracle devs we can provide for basic xid + begin/prepare support with some limitations, which will work more fully in an upcoming release of cx_Oracle. Two phase “recovery” is not currently supported.
References: #5884
The Oracle dialect now uses
select sys_context( 'userenv', 'current_schema' ) from dual
to get the default schema name, rather thanSELECT USER FROM DUAL
, to accommodate for changes to the session-local schema name under Oracle.References: #5716
tests¶
Improve documentation and add test for sub-second pool timeouts. Pull request courtesy Jordan Pittier.
References: #5582
misc¶
The internal mechanics of the engine connection routine has been altered such that it’s now guaranteed that a user-defined event handler for the
PoolEvents.connect()
handler, when established usinginsert=True
, will allow an event handler to run that is definitely invoked before any dialect-specific initialization starts up, most notably when it does things like detect default schema name. Previously, this would occur in most cases but not unconditionally. A new example is added to the schema documentation illustrating how to establish the “default schema name” within an on-connect event.Fixed bug where the now-deprecated
autoload
parameter was being called internally within the reflection routines when a related table were reflected.References: #5684
Fixed regression where a connection pool event specified with a keyword, most notably
insert=True
, would be lost when the event were set up. This would prevent startup events that need to fire before dialect-level events from working correctly.References: #5708
Fixed issue where connection pool would not return connections to the pool or otherwise be finalized upon garbage collection under pypy if the checked out connection fell out of scope without being closed. This is a long standing issue due to pypy’s difference in GC behavior that does not call weakref finalizers if they are relative to another object that is also being garbage collected. A strong reference to the related record is now maintained so that the weakref has a strong-referenced “base” to trigger off of.
References: #5842
1.4.0b1¶
Released: November 2, 2020general¶
”python setup.py test” is no longer a test runner, as this is deprecated by Pypa. Please use “tox” with no arguments for a basic test run.
References: #4789
Refactored the internal conventions used to cross-import modules that have mutual dependencies between them, such that the inspected arguments of functions and methods are no longer modified. This allows tools like pylint, Pycharm, other code linters, as well as hypothetical pep-484 implementations added in the future to function correctly as they no longer see missing arguments to function calls. The new approach is also simpler and more performant.
platform¶
The
importlib_metadata
library is used to scan for setuptools entrypoints rather than pkg_resources. as importlib_metadata is a small library that is included as of Python 3.8, the compatibility library is installed as a dependency for Python versions older than 3.8.References: #5400
Installation has been modernized to use setup.cfg for most package metadata.
References: #5404
Dropped support for python 3.4 and 3.5 that has reached EOL. SQLAlchemy 1.4 series requires python 2.7 or 3.6+.
References: #5634
Removed all dialect code related to support for Jython and zxJDBC. Jython has not been supported by SQLAlchemy for many years and it is not expected that the current zxJDBC code is at all functional; for the moment it just takes up space and adds confusion by showing up in documentation. At the moment, it appears that Jython has achieved Python 2.7 support in its releases but not Python 3. If Jython were to be supported again, the form it should take is against the Python 3 version of Jython, and the various zxJDBC stubs for various backends should be implemented as a third party dialect.
References: #5094
orm¶
The ORM can now generate queries previously only available when using
Query
using theselect()
construct directly. A new system by which ORM “plugins” may establish themselves within a CoreSelect
allow the majority of query building logic previously inside ofQuery
to now take place within a compilation-level extension forSelect
. Similar changes have been made for theUpdate
andDelete
constructs as well. The constructs when invoked usingSession.execute()
now do ORM-related work within the method. ForSelect
, theResult
object returned now contains ORM-level entities and results.References: #5159
Added the ability to add arbitrary criteria to the ON clause generated by a relationship attribute in a query, which applies to methods such as
Query.join()
as well as loader options likejoinedload()
. Additionally, a “global” version of the option allows limiting criteria to be applied to particular entities in a query globally.References: #4472
The ORM Declarative system is now unified into the ORM itself, with new import spaces under
sqlalchemy.orm
and new kinds of mappings. Support for decorator-based mappings without using a base class, support for classical style-mapper() calls that have access to the declarative class registry for relationships, and full integration of Declarative with 3rd party class attribute systems likedataclasses
andattrs
is now supported.See also
Declarative is now integrated into the ORM with new features
Python Dataclasses, attrs Supported w/ Declarative, Imperative Mappings
References: #5508
Eager loaders, such as joined loading, SELECT IN loading, etc., when configured on a mapper or via query options will now be invoked during the refresh on an expired object; in the case of selectinload and subqueryload, since the additional load is for a single object only, the “immediateload” scheme is used in these cases which resembles the single-parent query emitted by lazy loading.
References: #1763
Added support for direct mapping of Python classes that are defined using the Python
dataclasses
decorator. Pull request courtesy Václav Klusák. The new feature integrates into new support at the Declarative level for systems such asdataclasses
andattrs
.See also
Python Dataclasses, attrs Supported w/ Declarative, Imperative Mappings
Declarative is now integrated into the ORM with new features
References: #5027
Added “raiseload” feature for ORM mapped columns via
defer.raiseload
parameter ondefer()
anddeferred()
. This provides similar behavior for column-expression mapped attributes as theraiseload()
option does for relationship mapped attributes. The change also includes some behavioral changes to deferred columns regarding expiration; see the migration notes for details.See also
References: #4826
The evaluator that takes place within the ORM bulk update and delete for synchronize_session=”evaluate” now supports the IN and NOT IN operators. Tuple IN is also supported.
References: #1653
Enhanced logic that tracks if relationships will be conflicting with each other when they write to the same column to include simple cases of two relationships that should have a “backref” between them. This means that if two relationships are not viewonly, are not linked with back_populates and are not otherwise in an inheriting sibling/overriding arrangement, and will populate the same foreign key column, a warning is emitted at mapper configuration time warning that a conflict may arise. A new parameter
relationship.overlaps
is added to suit those very rare cases where such an overlapping persistence arrangement may be unavoidable.References: #5171
The ORM bulk update and delete operations, historically available via the
Query.update()
andQuery.delete()
methods as well as via theUpdate
andDelete
constructs for 2.0 style execution, will now automatically accommodate for the additional WHERE criteria needed for a single-table inheritance discriminator in order to limit the statement to rows referring to the specific subtype requested. The newwith_loader_criteria()
construct is also supported for with bulk update/delete operations.Update
relationship.sync_backref
flag in a relationship to make it implicitlyFalse
inviewonly=True
relationships, preventing synchronization events.References: #5237
The condition where a pending object being flushed with an identity that already exists in the identity map has been adjusted to emit a warning, rather than throw a
FlushError
. The rationale is so that the flush will proceed and raise aIntegrityError
instead, in the same way as if the existing object were not present in the identity map already. This helps with schemes that are using theIntegrityError
as a means of catching whether or not a row already exists in the table.References: #4662
A selection of Core and ORM query objects now perform much more of their Python computational tasks within the compile step, rather than at construction time. This is to support an upcoming caching model that will provide for caching of the compiled statement structure based on a cache key that is derived from the statement construct, which itself is expected to be newly constructed in Python code each time it is used. This means that the internal state of these objects may not be the same as it used to be, as well as that some but not all error raise scenarios for various kinds of argument validation will occur within the compilation / execution phase, rather than at statement construction time. See the migration notes linked below for complete details.
The automatic uniquing of rows on the client side is turned off for the new 2.0 style of ORM querying. This improves both clarity and performance. However, uniquing of rows on the client side is generally necessary when using joined eager loading for collections, as there will be duplicates of the primary entity for each element in the collection because a join was used. This uniquing must now be manually enabled and can be achieved using the new
Result.unique()
modifier. To avoid silent failure, the ORM explicitly requires the method be called when the result of an ORM query in 2.0 style makes use of joined load collections. The newerselectinload()
strategy is likely preferable for eager loading of collections in any case.See also
References: #4395
The ORM will now warn when asked to coerce a
select()
construct into a subquery implicitly. This occurs within places such as theQuery.select_entity_from()
andQuery.select_from()
methods as well as within thewith_polymorphic()
function. When aSelectBase
(which is what’s produced byselect()
) orQuery
object is passed directly to these functions and others, the ORM is typically coercing them to be a subquery by calling theSelectBase.alias()
method automatically (which is now superseded by theSelectBase.subquery()
method). See the migration notes linked below for further details.References: #4617
The “KeyedTuple” class returned by
Query
is now replaced with the CoreRow
class, which behaves in the same way as KeyedTuple. In SQLAlchemy 2.0, both Core and ORM will return result rows using the sameRow
object. In the interim, Core uses a backwards-compatibility classLegacyRow
that maintains the former mapping/tuple hybrid behavior used by “RowProxy”.References: #4710
The bulk update and delete methods
Query.update()
andQuery.delete()
, as well as their 2.0-style counterparts, now make use of RETURNING when the “fetch” strategy is used in order to fetch the list of affected primary key identites, rather than emitting a separate SELECT, when the backend in use supports RETURNING. Additionally, the “fetch” strategy will in ordinary cases not expire the attributes that have been updated, and will instead apply the updated values directly in the same way that the “evaluate” strategy does, to avoid having to refresh the object. The “evaluate” strategy will also fall back to expiring attributes that were updated to a SQL expression that was unevaluable in Python.Implemented support for the psycopg2
execute_values()
extension within the ORM flush process via the enhancements to Core made in #5401, so that this extension is used both as a strategy to batch INSERT statements together as well as that RETURNING may now be used among multiple parameter sets to retrieve primary key values back in batch. This allows nearly all INSERT statements emitted by the ORM on behalf of PostgreSQL to be submitted in batch and also via theexecute_values()
extension which benches at five times faster than plain executemany() for this particular backend.References: #5263
A query that is against a mapped inheritance subclass which also uses
Query.select_entity_from()
or a similar technique in order to provide an existing subquery to SELECT from, will now raise an error if the given subquery returns entities that do not correspond to the given subclass, that is, they are sibling or superclasses in the same hierarchy. Previously, these would be returned without error. Additionally, if the inheritance mapping is a single-inheritance mapping, the given subquery must apply the appropriate filtering against the polymorphic discriminator column in order to avoid this error; previously, theQuery
would add this criteria to the outside query however this interferes with some kinds of query that return other kinds of entities as well.References: #5122
The internal attribute symbols NO_VALUE and NEVER_SET have been unified, as there was no meaningful difference between these two symbols, other than a few codepaths where they were differentiated in subtle and undocumented ways, these have been fixed.
References: #4696
Fixed bug where a versioning column specified on a mapper against a
select()
construct where the version_id_col itself were against the underlying table would incur additional loads when accessed, even if the value were locally persisted by the flush. The actual fix is a result of the changes in #4617, by fact that aselect()
object no longer has a.c
attribute and therefore does not confuse the mapper into thinking there’s an unknown column value present.References: #4194
An
UnmappedInstanceError
is now raised forInstrumentedAttribute
if an instance is an unmapped object. Prior to this anAttributeError
was raised. Pull request courtesy Ramon Williams.References: #3858
The
Session
object no longer initiates aSessionTransaction
object immediately upon construction or after the previous transaction is closed; instead, “autobegin” logic now initiates the newSessionTransaction
on demand when it is next needed. Rationale includes to remove reference cycles from aSession
that has been closed out, as well as to remove the overhead incurred by the creation ofSessionTransaction
objects that are often discarded immediately. This change affects the behavior of theSessionEvents.after_transaction_create()
hook in that the event will be emitted when theSession
first requires aSessionTransaction
be present, rather than whenever theSession
were created or the previousSessionTransaction
were closed. Interactions with theEngine
and the database itself remain unaffected.References: #5074
Added new entity-targeting capabilities to the ORM query context help with the case where the
Session
is using a bind dictionary against mapped classes, rather than a single bind, and theQuery
is against a Core statement that was ultimately generated from a method such asQuery.subquery()
. First implemented using a deep search, the current approach leverages the unifiedselect()
construct to keep track of the first mapper that is part of the construct.References: #4829
An
ArgumentError
is now raised if both theselectable
andflat
parameters are set to True inwith_polymorphic()
. The selectable name is already aliased and applying flat=True overrides the selectable name with an anonymous name that would’ve previously caused the code to break. Pull request courtesy Ramon Williams.References: #4212
Fixed issue in polymorphic loading internals which would fall back to a more expensive, soon-to-be-deprecated form of result column lookup within certain unexpiration scenarios in conjunction with the use of “with_polymorphic”.
References: #4718
An error is raised if any persistence-related “cascade” settings are made on a
relationship()
that also sets up viewonly=True. The “cascade” settings now default to non-persistence related settings only when viewonly is also set. This is the continuation from #4993 where this setting was changed to emit a warning in 1.3.References: #4994
Improved declarative inheritance scanning to not get tripped up when the same base class appears multiple times in the base inheritance list.
References: #4699
Fixed bug in ORM versioning feature where assignment of an explicit version_id for a counter configured against a mapped selectable where version_id_col is against the underlying table would fail if the previous value were expired; this was due to the fact that the mapped attribute would not be configured with active_history=True.
References: #4195
An exception is now raised if the ORM loads a row for a polymorphic instance that has a primary key but the discriminator column is NULL, as discriminator columns should not be null.
References: #4836
Accessing a collection-oriented attribute on a newly created object no longer mutates
__dict__
, but still returns an empty collection as has always been the case. This allows collection-oriented attributes to work consistently in comparison to scalar attributes which returnNone
, but also don’t mutate__dict__
. In order to accommodate for the collection being mutated, the same empty collection is returned each time once initially created, and when it is mutated (e.g. an item appended, added, etc.) it is then moved into__dict__
. This removes the last of mutating side-effects on read-only attribute access within the ORM.See also
Accessing an uninitialized collection attribute on a transient object no longer mutates __dict__
References: #4519
The refresh of an expired object will now trigger an autoflush if the list of expired attributes include one or more attributes that were explicitly expired or refreshed using the
Session.expire()
orSession.refresh()
methods. This is an attempt to find a middle ground between the normal unexpiry of attributes that can happen in many cases where autoflush is not desirable, vs. the case where attributes are being explicitly expired or refreshed and it is possible that these attributes depend upon other pending state within the session that needs to be flushed. The two methods now also gain a new flagSession.expire.autoflush
andSession.refresh.autoflush
, defaulting to True; when set to False, this will disable the autoflush that occurs on unexpire for these attributes.References: #5226
The behavior of the
relationship.cascade_backrefs
flag will be reversed in 2.0 and set toFalse
unconditionally, such that backrefs don’t cascade save-update operations from a forwards-assignment to a backwards assignment. A 2.0 deprecation warning is emitted when the parameter is left at its default ofTrue
at the point at which such a cascade operation actually takes place. The new behavior can be established as always by setting the flag toFalse
on a specificrelationship()
, or more generally can be set up across the board by setting the theSession.future
flag to True.References: #5150
The “slice index” feature used by
Query
as well as by the dynamic relationship loader will no longer accept negative indexes in SQLAlchemy 2.0. These operations do not work efficiently and load the entire collection in, which is both surprising and undesirable. These will warn in 1.4 unless theSession.future
flag is set in which case they will raise IndexError.References: #5606
Calling the
Query.instances()
method without passing aQueryContext
is deprecated. The original use case for this was that aQuery
could yield ORM objects when given only the entities to be selected as well as a DBAPI cursor object. However, for this to work correctly there is essential metadata that is passed from a SQLAlchemyResultProxy
that is derived from the mapped column expressions, which comes originally from theQueryContext
. To retrieve ORM results from arbitrary SELECT statements, theQuery.from_statement()
method should be used.References: #4719
Using strings to represent relationship names in ORM operations such as
Query.join()
, as well as strings for all ORM attribute names in loader options likeselectinload()
is deprecated and will be removed in SQLAlchemy 2.0. The class-bound attribute should be passed instead. This provides much better specificity to the given method, allows for modifiers such asof_type()
, and reduces internal complexity.Additionally, the
aliased
andfrom_joinpoint
parameters toQuery.join()
are also deprecated. Thealiased()
construct now provides for a great deal of flexibility and capability and should be used directly.Deprecated logic in
Query.distinct()
that automatically adds columns in the ORDER BY clause to the columns clause; this will be removed in 2.0.References: #5134
Passing keyword arguments to methods such as
Session.execute()
to be passed into theSession.get_bind()
method is deprecated; the newSession.execute.bind_arguments
dictionary should be passed instead.References: #5573
The
eagerload()
andrelation()
were old aliases and are now deprecated. Usejoinedload()
andrelationship()
respectively.References: #5192
All long-deprecated “extension” classes have been removed, including MapperExtension, SessionExtension, PoolListener, ConnectionProxy, AttributeExtension. These classes have been deprecated since version 0.7 long superseded by the event listener system.
References: #4638
Remove the deprecated loader options
joinedload_all
,subqueryload_all
,lazyload_all
,selectinload_all
. The normal version with method chaining should be used in their place.References: #4642
Remove deprecated function
comparable_property
. Please refer to thehybrid
extension. This also removes the functioncomparable_using
in the declarative extension.Remove deprecated function
compile_mappers
. Please useconfigure_mappers()
Remove deprecated method
collection.linker
. Please refer to theAttributeEvents.init_collection()
andAttributeEvents.dispose_collection()
event handlers.Remove deprecated method
Session.prune
and parameterSession.weak_identity_map
. See the recipe at Session Referencing Behavior for an event-based approach to maintaining strong identity references. This change also removes the classStrongInstanceDict
.Remove deprecated parameter
mapper.order_by
. UseQuery.order_by()
to determine the ordering of a result set.Remove deprecated parameter
Session._enable_transaction_accounting
.Remove deprecated parameter
Session.is_modified.passive
.References: #4643
engine¶
Implemented an all-new
Result
object that replaces the previousResultProxy
object. As implemented in Core, the subclassCursorResult
features a compatible calling interface with the previousResultProxy
, and additionally adds a great amount of new functionality that can be applied to Core result sets as well as ORM result sets, which are now integrated into the same model.Result
includes features such as column selection and rearrangement, improved fetchmany patterns, uniquing, as well as a variety of implementations that can be used to create database results from in-memory structures as well.See also
SQLAlchemy now includes support for Python asyncio within both Core and ORM, using the included asyncio extension. The extension makes use of the greenlet library in order to adapt SQLAlchemy’s sync-oriented internals such that an asyncio interface that ultimately interacts with an asyncio database adapter is now feasible. The single driver supported at the moment is the asyncpg driver for PostgreSQL.
References: #3414
Implemented the
create_engine.future
parameter which enables forwards compatibility with SQLAlchemy 2. is used for forwards compatibility with SQLAlchemy 2. This engine features always-transactional behavior with autobegin.References: #4644
Reworked the “setinputsizes()” set of dialect hooks to be correctly extensible for any arbitrary DBAPI, by allowing dialects individual hooks that may invoke cursor.setinputsizes() in the appropriate style for that DBAPI. In particular this is intended to support pyodbc’s style of usage which is fundamentally different from that of cx_Oracle. Added support for pyodbc.
References: #5649
Added new reflection method
Inspector.get_sequence_names()
which returns all the sequences defined andInspector.has_sequence()
to check if a particular sequence exits. Support for this method has been added to the backend that supportSequence
: PostgreSQL, Oracle and MariaDB >= 10.3.References: #2056
The
Table.autoload_with
parameter now accepts anInspector
object directly, as well as anyEngine
orConnection
as was the case before.References: #4755
The
RowProxy
class is no longer a “proxy” object, and is instead directly populated with the post-processed contents of the DBAPI row tuple upon construction. Now namedRow
, the mechanics of how the Python-level value processors have been simplified, particularly as it impacts the format of the C code, so that a DBAPI row is processed into a result tuple up front. The object returned by theResultProxy
is now theLegacyRow
subclass, which maintains mapping/tuple hybrid behavior, however the baseRow
class now behaves more fully like a named tuple.See also
RowProxy is no longer a “proxy”; is now called Row and behaves like an enhanced named tuple
References: #4710
The pool “pre-ping” feature has been refined to not invoke for a DBAPI connection that was just opened in the same checkout operation. pre ping only applies to a DBAPI connection that’s been checked into the pool and is being checked out again.
References: #4524
Disabled the “unicode returns” check that runs on dialect startup when running under Python 3, which for many years has occurred in order to test the current DBAPI’s behavior for whether or not it returns Python Unicode or Py2K strings for the VARCHAR and NVARCHAR datatypes. The check still occurs by default under Python 2, however the mechanism to test the behavior will be removed in SQLAlchemy 2.0 when Python 2 support is also removed.
This logic was very effective when it was needed, however now that Python 3 is standard, all DBAPIs are expected to return Python 3 strings for character datatypes. In the unlikely case that a third party DBAPI does not support this, the conversion logic within
String
is still available and the third party dialect may specify this in its upfront dialect flags by setting the dialect level flagreturns_unicode_strings
to one ofString.RETURNS_CONDITIONAL
orString.RETURNS_BYTES
, both of which will enable Unicode conversion even under Python 3.References: #5315
Revised the
Connection.execution_options.schema_translate_map
feature such that the processing of the SQL statement to receive a specific schema name occurs within the execution phase of the statement, rather than at the compile phase. This is to support the statement being efficiently cached. Previously, the current schema being rendered into the statement for a particular run would be considered as part of the cache key itself, meaning that for a run against hundreds of schemas, there would be hundreds of cache keys, rendering the cache much less performant. The new behavior is that the rendering is done in a similar manner as the “post compile” rendering added in 1.4 as part of #4645, #4808.References: #5004
The
Connection
object will now not clear a rolled-back transaction until the outermost transaction is explicitly rolled back. This is essentially the same behavior that the ORMSession
has had for a long time, where an explicit call to.rollback()
on all enclosing transactions is required for the transaction to logically clear, even though the DBAPI-level transaction has already been rolled back. The new behavior helps with situations such as the “ORM rollback test suite” pattern where the test suite rolls the transaction back within the ORM scope, but the test harness which seeks to control the scope of the transaction externally does not expect a new transaction to start implicitly.References: #4712
Adjusted the dialect initialization process such that the
Dialect.on_connect()
is not called a second time on the first connection. The hook is called first, then theDialect.initialize()
is called if that connection is the first for that dialect, then no more events are called. This eliminates the two calls to the “on_connect” function which can produce very difficult debugging situations.References: #5497
The
URL
object is now an immutable named tuple. To modify a URL object, use theURL.set()
method to produce a new URL object.See also
The URL object is now immutable - notes on migration
References: #5526
The
MetaData.bind
argument as well as the overall concept of “bound metadata” is deprecated in SQLAlchemy 1.4 and will be removed in SQLAlchemy 2.0. The parameter as well as related functions now emit aRemovedIn20Warning
when SQLAlchemy 2.0 Deprecations Mode is in use.References: #4634
The
server_side_cursors
engine-wide parameter is deprecated and will be removed in a future release. For unbuffered cursors, theConnection.execution_options.stream_results
execution option should be used on a per-execution basis.The
Connection.connect()
method is deprecated as is the concept of “connection branching”, which copies aConnection
into a new one that has a no-op “.close()” method. This pattern is oriented around the “connectionless execution” concept which is also being removed in 2.0.References: #5131
The
case_sensitive
flag oncreate_engine()
is deprecated; this flag was part of the transition of the result row object to allow case sensitive column matching as the default, while providing backwards compatibility for the former matching method. All string access for a row should be assumed to be case sensitive just like any other Python mapping.References: #4878
”Implicit autocommit”, which is the COMMIT that occurs when a DML or DDL statement is emitted on a connection, is deprecated and won’t be part of SQLAlchemy 2.0. A 2.0-style warning is emitted when autocommit takes effect, so that the calling code may be adjusted to use an explicit transaction.
As part of this change, DDL methods such as
MetaData.create_all()
when used against anEngine
will run the operation in a BEGIN block if one is not started already.See also
References: #4846
Deprecated the behavior by which a
Column
can be used as the key in a result set row lookup, when thatColumn
is not part of the SQL selectable that is being selected; that is, it is only matched on name. A deprecation warning is now emitted for this case. Various ORM use cases, such as those involvingtext()
constructs, have been improved so that this fallback logic is avoided in most cases.References: #4877
Deprecated remaining engine-level introspection and utility methods including
Engine.run_callable()
,Engine.transaction()
,Engine.table_names()
,Engine.has_table()
. The utility methods are superseded by modern context-manager patterns, and the table introspection tasks are suited by theInspector
object.References: #4755
Remove deprecated method
get_primary_keys
in theDialect
andInspector
classes. Please refer to theDialect.get_pk_constraint()
andInspector.get_primary_keys()
methods.Remove deprecated event
dbapi_error
and the methodConnectionEvents.dbapi_error
. Please refer to theConnectionEvents.handle_error()
event. This change also removes the attributesExecutionContext.is_disconnect
andExecutionContext.exception
.References: #4643
The internal dialect method
Dialect.reflecttable
has been removed. A review of third party dialects has not found any making use of this method, as it was already documented as one that should not be used by external dialects. Additionally, the privateEngine._run_visitor
method is also removed.References: #4755
The long-deprecated
Inspector.get_table_names.order_by
parameter has been removed.References: #4755
The
Inspector.reflecttable()
was renamed toInspector.reflect_table()
.References: #5244
sql¶
Added “from linting” as a built-in feature to the SQL compiler. This allows the compiler to maintain graph of all the FROM clauses in a particular SELECT statement, linked by criteria in either the WHERE or in JOIN clauses that link these FROM clauses together. If any two FROM clauses have no path between them, a warning is emitted that the query may be producing a cartesian product. As the Core expression language as well as the ORM are built on an “implicit FROMs” model where a particular FROM clause is automatically added if any part of the query refers to it, it is easy for this to happen inadvertently and it is hoped that the new feature helps with this issue.
References: #4737
Added new “post compile parameters” feature. This feature allows a
bindparam()
construct to have its value rendered into the SQL string before being passed to the DBAPI driver, but after the compilation step, using the “literal render” feature of the compiler. The immediate rationale for this feature is to support LIMIT/OFFSET schemes that don’t work or perform well as bound parameters handled by the database driver, while still allowing for SQLAlchemy SQL constructs to be cacheable in their compiled form. The immediate targets for the new feature are the “TOP N” clause used by SQL Server (and Sybase) which does not support a bound parameter, as well as the “ROWNUM” and optional “FIRST_ROWS()” schemes used by the Oracle dialect, the former of which has been known to perform better without bound parameters and the latter of which does not support a bound parameter. The feature builds upon the mechanisms first developed to support “expanding” parameters for IN expressions. As part of this feature, the Oracleuse_binds_for_limits
feature is turned on unconditionally and this flag is now deprecated.References: #4808
Add support for regular expression on supported backends. Two operations have been defined:
ColumnOperators.regexp_match()
implementing a regular expression match like function.ColumnOperators.regexp_replace()
implementing a regular expression string replace function.
Supported backends include SQLite, PostgreSQL, MySQL / MariaDB, and Oracle.
References: #1390
The
select()
construct and related constructs now allow for duplication of column labels and columns themselves in the columns clause, mirroring exactly how column expressions were passed in. This allows the tuples returned by an executed result to match what was SELECTed for in the first place, which is how the ORMQuery
works, so this establishes better cross-compatibility between the two constructs. Additionally, it allows column-positioning-sensitive structures such as UNIONs (i.e._selectable.CompoundSelect
) to be more intuitively constructed in those cases where a particular column might appear in more than one place. To support this change, theColumnCollection
has been revised to support duplicate columns as well as to allow integer index access.References: #4753
Enhanced the disambiguating labels feature of the
select()
construct such that when a select statement is used in a subquery, repeated column names from different tables are now automatically labeled with a unique label name, without the need to use the full “apply_labels()” feature that combines tablename plus column name. The disambiguated labels are available as plain string keys in the .c collection of the subquery, and most importantly the feature allows an ORMaliased()
construct against the combination of an entity and an arbitrary subquery to work correctly, targeting the correct columns despite same-named columns in the source tables, without the need for an “apply labels” warning.See also
Selecting from the query itself as a subquery, e.g. “from_self()” - Illustrates the new disambiguation feature as part of a strategy to migrate away from the
Query.from_self()
method.References: #5221
The “expanding IN” feature, which generates IN expressions at query execution time which are based on the particular parameters associated with the statement execution, is now used for all IN expressions made against lists of literal values. This allows IN expressions to be fully cacheable independently of the list of values being passed, and also includes support for empty lists. For any scenario where the IN expression contains non-literal SQL expressions, the old behavior of pre-rendering for each position in the IN is maintained. The change also completes support for expanding IN with tuples, where previously type-specific bind processors weren’t taking effect.
References: #4645
Along with the new transparent statement caching feature introduced as part of #4369, a new feature intended to decrease the Python overhead of creating statements is added, allowing lambdas to be used when indicating arguments being passed to a statement object such as select(), Query(), update(), etc., as well as allowing the construction of full statements within lambdas in a similar manner as that of the “baked query” system. The rationale of using lambdas is adapted from that of the “baked query” approach which uses lambdas to encapsulate any amount of Python code into a callable that only needs to be called when the statement is first constructed into a string. The new feature however is more sophisticated in that Python literal values that would be passed as parameters are automatically extracted, so that there is no longer a need to use bindparam() objects with such queries. Use of the feature is optional and can be used to as small or as great a degree as is desired, while still allowing statements to be fully cacheable.
References: #5380
The
Index.create()
andIndex.drop()
methods now have a parameterIndex.create.checkfirst
, in the same way as that ofTable
andSequence
, which when enabled will cause the operation to detect if the index exists (or not) before performing a create or drop operation.References: #527
The
true()
andfalse()
operators may now be applied as the “onclause” of ajoin()
on a backend that does not support “native boolean” expressions, e.g. Oracle or SQL Server, and the expression will render as “1=1” for true and “1=0” false. This is the behavior that was introduced many years ago in #2804 for and/or expressions.Change the method
__str
ofColumnCollection
to avoid confusing it with a python list of string.References: #5191
Add support to
FETCH {FIRST | NEXT} [ count ] {ROW | ROWS} {ONLY | WITH TIES}
in the select for the supported backends, currently PostgreSQL, Oracle and MSSQL.References: #5576
Additional logic has been added such that certain SQL expressions which typically wrap a single database column will use the name of that column as their “anonymous label” name within a SELECT statement, potentially making key-based lookups in result tuples more intuitive. The primary example of this is that of a CAST expression, e.g.
CAST(table.colname AS INTEGER)
, which will export its default name as “colname”, rather than the usual “anon_1” label, that is,CAST(table.colname AS INTEGER) AS colname
. If the inner expression doesn’t have a name, then the previous “anonymous label” logic is used. When using SELECT statements that make use ofSelect.apply_labels()
, such as those emitted by the ORM, the labeling logic will produce<tablename>_<inner column name>
in the same was as if the column were named alone. The logic applies right now to thecast()
andtype_coerce()
constructs as well as some single-element boolean expressions.References: #4449
The “clause coercion” system, which is SQLAlchemy Core’s system of receiving arguments and resolving them into
ClauseElement
structures in order to build up SQL expression objects, has been rewritten from a series of ad-hoc functions to a fully consistent class-based system. This change is internal and should have no impact on end users other than more specific error messages when the wrong kind of argument is passed to an expression object, however the change is part of a larger set of changes involving the role and behavior ofselect()
objects.References: #4617
Added a core
Values
object that enables a VALUES construct to be used in the FROM clause of an SQL statement for databases that support it (mainly PostgreSQL and SQL Server).References: #4868
The
select()
construct is moving towards a new calling form that isselect(col1, col2, col3, ..)
, with all other keyword arguments removed, as these are all suited using generative methods. The single list of column or table arguments passed toselect()
is still accepted, however is no longer necessary if expressions are passed in a simple positional style. Other keyword arguments are disallowed when this form is used.References: #5284
As part of the SQLAlchemy 2.0 migration project, a conceptual change has been made to the role of the
SelectBase
class hierarchy, which is the root of all “SELECT” statement constructs, in that they no longer serve directly as FROM clauses, that is, they no longer subclassFromClause
. For end users, the change mostly means that any placement of aselect()
construct in the FROM clause of anotherselect()
requires first that it be wrapped in a subquery first, which historically is through the use of theSelectBase.alias()
method, and is now also available through the use ofSelectBase.subquery()
. This was usually a requirement in any case since several databases don’t accept unnamed SELECT subqueries in their FROM clause in any case.References: #4617
Added a new Core class
Subquery
, which takes the place ofAlias
when creating named subqueries against aSelectBase
object.Subquery
acts in the same way asAlias
and is produced from theSelectBase.subquery()
method; for ease of use and backwards compatibility, theSelectBase.alias()
method is synonymous with this new method.References: #4617
An all-encompassing reorganization and refactoring of Core and ORM internals now allows all Core and ORM statements within the areas of DQL (e.g. SELECTs) and DML (e.g. INSERT, UPDATE, DELETE) to allow their SQL compilation as well as the construction of result-fetching metadata to be fully cached in most cases. This effectively provides a transparent and generalized version of what the “Baked Query” extension has offered for the ORM in past versions. The new feature can calculate the cache key for any given SQL construction based on the string that it would ultimately produce for a given dialect, allowing functions that compose the equivalent select(), Query(), insert(), update() or delete() object each time to have that statement cached after it’s generated the first time.
The feature is enabled transparently but includes some new programming paradigms that may be employed to make the caching even more efficient.
References: #4639
Fixed issue where when constructing constraints from ORM-bound columns, primarily
ForeignKey
objects but alsoUniqueConstraint
,CheckConstraint
and others, the ORM-levelInstrumentedAttribute
is discarded entirely, and all ORM-level annotations from the columns are removed; this is so that the constraints are still fully pickleable without the ORM-level entities being pulled in. These annotations are not necessary to be present at the schema/metadata level.References: #5001
Registered function names based on
GenericFunction
are now retrieved in a case-insensitive fashion in all cases, removing the deprecation logic from 1.3 which temporarily allowed multipleGenericFunction
objects to exist with differing cases. AGenericFunction
that replaces another on the same name whether or not it’s case sensitive emits a warning before replacing the object.Creating an
and_()
oror_()
construct with no arguments or empty*args
will now emit a deprecation warning, as the SQL produced is a no-op (i.e. it renders as a blank string). This behavior is considered to be non-intuitive, so for empty or possibly emptyand_()
oror_()
constructs, an appropriate default boolean should be included, such asand_(True, *args)
oror_(False, *args)
. As has been the case for many major versions of SQLAlchemy, these particular boolean values will not render if the*args
portion is non-empty.References: #5054
Improved the
tuple_()
construct such that it behaves predictably when used in a columns-clause context. The SQL tuple is not supported as a “SELECT” columns clause element on most backends; on those that do (PostgreSQL, not surprisingly), the Python DBAPI does not have a “nested type” concept so there are still challenges in fetching rows for such an object. Use oftuple_()
in aselect()
orQuery
will now raise aCompileError
at the point at which thetuple_()
object is seen as presenting itself for fetching rows (i.e., if the tuple is in the columns clause of a subquery, no error is raised). For ORM use,theBundle
object is an explicit directive that a series of columns should be returned as a sub-tuple per row and is suggested by the error message. Additionally ,the tuple will now render with parenthesis in all contexts. Previously, the parenthesization would not render in a columns context leading to non-defined behavior.References: #5127
Improved support for column names that contain percent signs in the string, including repaired issues involving anonymous labels that also embedded a column name with a percent sign in it, as well as re-established support for bound parameter names with percent signs embedded on the psycopg2 dialect, using a late-escaping process similar to that used by the cx_Oracle dialect.
References: #5653
Custom functions that are created as subclasses of
FunctionElement
will now generate an “anonymous label” based on the “name” of the function just like any otherFunction
object, e.g."SELECT myfunc() AS myfunc_1"
. While SELECT statements no longer require labels in order for the result proxy object to function, the ORM still targets columns in rows by using objects as mapping keys, which works more reliably when the column expressions have distinct names. In any case, the behavior is now made consistent between functions generated byfunc
and those generated as customFunctionElement
objects.References: #4887
Reworked the
ClauseElement.compare()
methods in terms of a new visitor-based approach, and additionally added test coverage ensuring that allClauseElement
subclasses can be accurately compared against each other in terms of structure. Structural comparison capability is used to a small degree within the ORM currently, however it also may form the basis for new caching features.References: #4336
Deprecate usage of
DISTINCT ON
in dialect other than PostgreSQL. Deprecate old usage of string distinct in MySQL dialectReferences: #4002
The ORDER BY clause of a
_selectable.CompoundSelect
, e.g. UNION, EXCEPT, etc. will not render the table name associated with a given column when applyingCompoundSelect.order_by()
in terms of aTable
- bound column. Most databases require that the names in the ORDER BY clause be expressed as label names only which are matched to names in the first SELECT statement. The change is related to #4617 in that a previous workaround was to refer to the.c
attribute of the_selectable.CompoundSelect
in order to get at a column that has no table name. As the subquery is now named, this change allows both the workaround to continue to work, as well as allows table-bound columns as well as theCompoundSelect.selected_columns
collections to be usable in theCompoundSelect.order_by()
method.References: #4617
The
Join
construct no longer considers the “onclause” as a source of additional FROM objects to be omitted from the FROM list of an enclosingSelect
object as standalone FROM objects. This applies to an ON clause that includes a reference to another FROM object outside the JOIN; while this is usually not correct from a SQL perspective, it’s also incorrect for it to be omitted, and the behavioral change makes theSelect
/Join
behave a bit more intuitively.References: #4621
The
Join.alias()
method is deprecated and will be removed in SQLAlchemy 2.0. An explicit select + subquery, or aliasing of the inner tables, should be used instead.References: #5010
The
Table
class now raises a deprecation warning when columns with the same name are defined. To replace a column a new parameterTable.append_column.replace_existing
was added to theTable.append_column()
method.The
ColumnCollection.contains_column()
will now raises an error when called with a string, suggesting the caller to usein
instead.The “threadlocal” execution strategy, deprecated in 1.3, has been removed for 1.4, as well as the concept of “engine strategies” and the
Engine.contextual_connect
method. The “strategy=’mock’” keyword argument is still accepted for now with a deprecation warning; usecreate_mock_engine()
instead for this use case.See also
“threadlocal” engine strategy deprecated - from the 1.3 migration notes which discusses the rationale for deprecation.
References: #4632
Removed the
sqlalchemy.sql.visitors.iterate_depthfirst
andsqlalchemy.sql.visitors.traverse_depthfirst
functions. These functions were unused by any part of SQLAlchemy. Theiterate()
andtraverse()
functions are commonly used for these functions. Also removed unused options from the remaining functions including “column_collections”, “schema_visitor”.Removed the concept of a bound engine from the
Compiler
object, and removed the.execute()
and.scalar()
methods fromCompiler
. These were essentially forgotten methods from over a decade ago and had no practical use, and it’s not appropriate for theCompiler
object itself to be maintaining a reference to anEngine
.Remove deprecated methods
Compiled.compile
,ClauseElement.__and__
andClauseElement.__or__
and attributeOver.func
.Remove deprecated
FromClause.count
method. Please use thecount
function available from thefunc
namespace.References: #4643
Remove deprecated parameters
text.bindparams
andtext.typemap
. Please refer to theTextClause.bindparams()
andTextClause.columns()
methods.Remove deprecated parameter
Table.useexisting
. Please useTable.extend_existing
.References: #4643
Table
parametermustexist
has been renamed toTable.must_exist
and will now warn when used.The
SelectBase.as_scalar()
andQuery.as_scalar()
methods have been renamed toSelectBase.scalar_subquery()
andQuery.scalar_subquery()
, respectively. The old names continue to exist within 1.4 series with a deprecation warning. In addition, the implicit coercion ofSelectBase
,Alias
, and other SELECT oriented objects into scalar subqueries when evaluated in a column context is also deprecated, and emits a warning that theSelectBase.scalar_subquery()
method should be called explicitly. This warning will in a later major release become an error, however the message will always be clear whenSelectBase.scalar_subquery()
needs to be invoked. The latter part of the change is for clarity and to reduce the implicit decisionmaking by the query coercion system. TheSubquery.as_scalar()
method, which was previouslyAlias.as_scalar
, is also deprecated;.scalar_subquery()
should be invoked directly from `select()
object orQuery
object.This change is part of the larger change to convert
select()
objects to no longer be directly part of the “from clause” class hierarchy, which also includes an overhaul of the clause coercion system.References: #4617
Several operators are renamed to achieve more consistent naming across SQLAlchemy.
The operator changes are:
isfalse
is nowis_false
isnot_distinct_from
is nowis_not_distinct_from
istrue
is nowis_true
notbetween
is nownot_between
notcontains
is nownot_contains
notendswith
is nownot_endswith
notilike
is nownot_ilike
notlike
is nownot_like
notmatch
is nownot_match
notstartswith
is nownot_startswith
nullsfirst
is nownulls_first
nullslast
is nownulls_last
isnot
is nowis_not
notin_
is nownot_in
Because these are core operators, the internal migration strategy for this change is to support legacy terms for an extended period of time – if not indefinitely – but update all documentation, tutorials, and internal usage to the new terms. The new terms are used to define the functions, and the legacy terms have been deprecated into aliases of the new terms.
Allow specifying the data type when creating a
Sequence
in PostgreSQL by using the parameterSequence.data_type
.References: #5498
The “NO ACTION” keyword for foreign key “ON UPDATE” is now considered to be the default cascade for a foreign key on all supporting backends (SQlite, MySQL, PostgreSQL) and when detected is not included in the reflection dictionary; this is already the behavior for PostgreSQL and MySQL for all previous SQLAlchemy versions in any case. The “RESTRICT” keyword is positively stored when detected; PostgreSQL does report on this keyword, and MySQL as of version 8.0 does as well. On earlier MySQL versions, it is not reported by the database.
References: #4741
Added support for reflecting “identity” columns, which are now returned as part of the structure returned by
Inspector.get_columns()
. When reflecting fullTable
objects, identity columns will be represented using theIdentity
construct. Currently the supported backends are PostgreSQL >= 10, Oracle >= 12 and MSSQL (with different syntax and a subset of functionalities).
schema¶
The
Enum.create_constraint
andBoolean.create_constraint
parameters now default to False, indicating when a so-called “non-native” version of these two datatypes is created, a CHECK constraint will not be generated by default. These CHECK constraints present schema-management maintenance complexities that should be opted in to, rather than being turned on by default.References: #5367
Cleaned up the internal
str()
for datatypes so that all types produce a string representation without any dialect present, including that it works for third-party dialect types without that dialect being present. The string representation defaults to being the UPPERCASE name of that type with nothing else.References: #4262
Remove deprecated class
Binary
. Please useLargeBinary
.References: #4643
Renamed the
Table.tometadata()
method toTable.to_metadata()
. The previous name remains with a deprecation warning.References: #5413
Added the
Identity
construct that can be used to configure identity columns rendered with GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY. Currently the supported backends are PostgreSQL >= 10, Oracle >= 12 and MSSQL (with different syntax and a subset of functionalities).
extensions¶
Custom compiler constructs created using the
sqlalchemy.ext.compiled
extension will automatically add contextual information to the compiler when a custom construct is interpreted as an element in the columns clause of a SELECT statement, such that the custom element will be targetable as a key in result row mappings, which is the kind of targeting that the ORM uses in order to match column elements into result tuples.References: #4887
Added new parameter
AutomapBase.prepare.autoload_with
which supersedesAutomapBase.prepare.reflect
andAutomapBase.prepare.engine
.References: #5142
postgresql¶
Added support for PostgreSQL “readonly” and “deferrable” flags for all of psycopg2, asyncpg and pg8000 dialects. This takes advantage of a newly generalized version of the “isolation level” API to support other kinds of session attributes set via execution options that are reliably reset when connections are returned to the connection pool.
See also
References: #5549
The maximum buffer size for the
BufferedRowResultProxy
, which is used by dialects such as PostgreSQL whenstream_results=True
, can now be set to a number greater than 1000 and the buffer will grow to that size. Previously, the buffer would not go beyond 1000 even if the value were set larger. The growth of the buffer is also now based on a simple multiplying factor currently set to 5. Pull request courtesy Soumaya Mauthoor.References: #4914
When using the psycopg2 dialect for PostgreSQL, psycopg2 minimum version is set at 2.7. The psycopg2 dialect relies upon many features of psycopg2 released in the past few years, so to simplify the dialect, version 2.7, released in March, 2017 is now the minimum version required.
The psycopg2 dialect now defaults to using the very performant
execute_values()
psycopg2 extension for compiled INSERT statements, and also implements RETURNING support when this extension is used. This allows INSERT statements that even include an autoincremented SERIAL or IDENTITY value to run very fast while still being able to return the newly generated primary key values. The ORM will then integrate this new feature in a separate change.See also
psycopg2 dialect features “execute_values” with RETURNING for INSERT statements by default - full list of changes regarding the
executemany_mode
parameter.References: #5401
The pg8000 dialect has been revised and modernized for the most recent version of the pg8000 driver for PostgreSQL. Pull request courtesy Tony Locke. Note that this necessarily pins pg8000 at 1.16.6 or greater, which no longer has Python 2 support. Python 2 users who require pg8000 should ensure their requirements are pinned at
SQLAlchemy<1.4
.The pygresql and py-postgresql dialects are deprecated.
References: #5189
Remove support for deprecated engine URLs of the form
postgres://
; this has emitted a warning for many years and projects should be usingpostgresql://
.References: #4643
mysql¶
Added support for MariaDB Connector/Python to the mysql dialect. Original pull request courtesy Georg Richter.
References: #5459
Added a new dialect token “mariadb” that may be used in place of “mysql” in the
create_engine()
URL. This will deliver a MariaDB dialect subclass of the MySQLDialect in use that forces the “is_mariadb” flag to True. The dialect will raise an error if a server version string that does not indicate MariaDB in use is received. This is useful for MariaDB-specific testing scenarios as well as to support applications that are hardcoding to MariaDB-only concepts. As MariaDB and MySQL featuresets and usage patterns continue to diverge, this pattern may become more prominent.References: #5496
Added support for use of the
Sequence
construct with MariaDB 10.3 and greater, as this is now supported by this database. The construct integrates with theTable
object in the same way that it does for other databases like PostgreSQL and Oracle; if is present on the integer primary key “autoincrement” column, it is used to generate defaults. For backwards compatibility, to support aTable
that has aSequence
on it to support sequence only databases like Oracle, while still not having the sequence fire off for MariaDB, the optional=True flag should be set, which indicates the sequence should only be used to generate the primary key if the target database offers no other option.References: #4976
The MySQL and MariaDB dialects now query from the information_schema.tables system view in order to determine if a particular table exists or not. Previously, the “DESCRIBE” command was used with an exception catch to detect non-existent, which would have the undesirable effect of emitting a ROLLBACK on the connection. There appeared to be legacy encoding issues which prevented the use of “SHOW TABLES”, for this, but as MySQL support is now at 5.0.2 or above due to #4189, the information_schema tables are now available in all cases.
The “skip_locked” keyword used with
with_for_update()
will render “SKIP LOCKED” on all MySQL backends, meaning it will fail for MySQL less than version 8 and on current MariaDB backends. This is because those backends do not support “SKIP LOCKED” or any equivalent, so this error should not be silently ignored. This is upgraded from a warning in the 1.3 series.References: #5568
MySQL dialect’s server_version_info tuple is now all numeric. String tokens like “MariaDB” are no longer present so that numeric comparison works in all cases. The .is_mariadb flag on the dialect should be consulted for whether or not mariadb was detected. Additionally removed structures meant to support extremely old MySQL versions 3.x and 4.x; the minimum MySQL version supported is now version 5.0.2.
References: #4189
The OurSQL dialect is deprecated.
References: #5189
Remove deprecated dialect
mysql+gaerdbms
that has been deprecated since version 1.0. Use the MySQLdb dialect directly.Remove deprecated parameter
quoting
fromENUM
andSET
in themysql
dialect. The values passed to the enum or the set are quoted by SQLAlchemy when needed automatically.References: #4643
sqlite¶
Dropped support for right-nested join rewriting to support old SQLite versions prior to 3.7.16, released in 2013. It is expected that all modern Python versions among those now supported should all include much newer versions of SQLite.
References: #4895
mssql¶
Added support for the
JSON
datatype on the SQL Server dialect using theJSON
implementation, which implements SQL Server’s JSON functionality against theNVARCHAR(max)
datatype as per SQL Server documentation. Implementation courtesy Gord Thompson.References: #4384
Added support for “CREATE SEQUENCE” and full
Sequence
support for Microsoft SQL Server. This removes the deprecated feature of usingSequence
objects to manipulate IDENTITY characteristics which should now be performed usingmssql_identity_start
andmssql_identity_increment
as documented at Auto Increment Behavior / IDENTITY Columns. The change includes a new parameterSequence.data_type
to accommodate SQL Server’s choice of datatype, which for that backend includes INTEGER, BIGINT, and DECIMAL(n, 0). The default starting value for SQL Server’s version ofSequence
has been set at 1; this default is now emitted within the CREATE SEQUENCE DDL for all backends.Improved support for covering indexes (with INCLUDE columns). Added the ability for postgresql to render CREATE INDEX statements with an INCLUDE clause from Core. Index reflection also report INCLUDE columns separately for both mssql and postgresql (11+).
References: #4458
Added support for inspection / reflection of partial indexes / filtered indexes, i.e. those which use the
mssql_where
orpostgresql_where
parameters, withIndex
. The entry is both part of the dictionary returned byInspector.get_indexes()
as well as part of a reflectedIndex
construct that was reflected. Pull request courtesy Ramon Williams.References: #4966
Added support for reflection of temporary tables with the SQL Server dialect. Table names that are prefixed by a pound sign “#” are now introspected from the MSSQL “tempdb” system catalog.
References: #5506
SQL Server OFFSET and FETCH keywords are now used for limit/offset, rather than using a window function, for SQL Server versions 11 and higher. TOP is still used for a query that features only LIMIT. Pull request courtesy Elkin.
References: #5084
Fixed an issue where
sqlalchemy.engine.reflection.has_table()
always returnedFalse
for temporary tables.References: #5597
Fixed the base class of the
DATETIMEOFFSET
datatype to be based on theDateTime
class hierarchy, as this is a datetime-holding datatype.References: #4980
The adodbapi and mxODBC dialects are deprecated.
References: #5189
The mssql dialect will assume that at least MSSQL 2005 is used. There is no hard exception raised if a previous version is detected, but operations may fail for older versions.
As part of the support for reflecting
Identity
objects, the methodInspector.get_columns()
no longer returnsmssql_identity_start
andmssql_identity_increment
as part of thedialect_options
. Use the information in theidentity
key instead.References: #5527
Deprecated the
legacy_schema_aliasing
parameter tosqlalchemy.create_engine()
. This is a long-outdated parameter that has defaulted to False since version 1.1.References: #4809
oracle¶
The max_identifier_length for the Oracle dialect is now 128 characters by default, unless compatibility version less than 12.2 upon first connect, in which case the legacy length of 30 characters is used. This is a continuation of the issue as committed to the 1.3 series which adds max identifier length detection upon first connect as well as warns for the change in Oracle server.
See also
Max Identifier Lengths - in the Oracle dialect documentation
References: #4857
The LIMIT / OFFSET scheme used in Oracle now makes use of named subqueries rather than unnamed subqueries when it transparently rewrites a SELECT statement to one that uses a subquery that includes ROWNUM. The change is part of a larger change where unnamed subqueries are no longer directly supported by Core, as well as to modernize the internal use of the select() construct within the Oracle dialect.
Correctly render
Sequence
andIdentity
column optionsnominvalue
andnomaxvalue
asNOMAXVALUE` and ``NOMINVALUE
on oracle database.The
INTERVAL
class of the Oracle dialect is now correctly a subclass of the abstract version ofInterval
as well as the correct “emulated” base class, which allows for correct behavior under both native and non-native modes; previously it was only based onTypeEngine
.References: #4971