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?.
[orm] [bug] ¶
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
setting bake_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.
[orm] [bug] [regression] ¶
Adjusted the means by which classes such as scoped_session
and AsyncSession
are generated from the base
Session
class, such that custom Session
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
[orm] [bug] [regression] ¶
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
[orm] [bug] [performance] [regression] ¶
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
[orm] [bug] ¶
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
[orm] [bug] ¶
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] [usecase] ¶
Implemented a new registry architecture that allows the Async
version
of an object, like AsyncSession
, AsyncConnection
, etc., to be
locatable given the proxied “sync” object, i.e. Session
,
Connection
. Previously, to the degree such lookup functions were used,
an Async
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 new InstanceState
attribute InstanceState.async_session
have been added, which
are used to retrieve the original AsyncSession
associated
with an ORM mapped object, a Session
associated with an
AsyncSession
, and an AsyncSession
associated with an InstanceState
, respectively.
This patch also implements new methods
AsyncSession.in_nested_transaction()
,
AsyncSession.get_transaction()
,
AsyncSession.get_nested_transaction()
.
References: #6319
[asyncio] [bug] ¶
Fixed an issue that presented itself when using the NullPool
or the StaticPool
with an async engine. This mostly affected
the aiosqlite dialect.
References: #6575
[asyncio] [bug] ¶
Added asyncio.exceptions.TimeoutError
,
asyncio.exceptions.CancelledError
as so-called “exit exceptions”, a
class of exceptions that include things like GreenletExit
and
KeyboardInterrupt
, 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] [bug] [regression] ¶
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] [bug] ¶
Add note regarding encryption-related pragmas for pysqlcipher passed in the url.
This change is also backported to: 1.3.25
References: #6589
[sqlite] [bug] [regression] ¶
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 a URL
object under normal usage of
create_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
[orm] [bug] ¶
Fixed issue when using relationship.cascade_backrefs
parameter set to False
, 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 as set
or dict
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 hook
AttributeEvents.append_wo_mutation()
.
References: #6471
[orm] [bug] [regression] ¶
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
[orm] [bug] [regression] ¶
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
[orm] [bug] ¶
Enhanced the bind resolution rules for Session.execute()
so that
when a non-ORM statement such as an insert()
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 a
Session
that has a bind map set up on a common superclass
without specific mappers or tables named in the map.
References: #6484
[engine] [bug] ¶
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
[engine] [bug] ¶
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] [bug] [regression] ¶
Fixed regression in dynamic loader strategy and relationship()
overall where the relationship.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
[bug] [ext] ¶
Fixed a deprecation warning that was emitted when using
automap_base()
without passing an existing
Base
.
References: #6529
[bug] [pep484] ¶
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
[bug] [ext] [regression] ¶
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, the
sqlalchemy.ext.instrumentation.InstrumentationManager
class now
has a new method unregister()
, which replaces the previous method
dispose()
, which was not called as of version 1.4.
References: #6390
[general] [feature] ¶
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] [bug] [regression] ¶
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 the
contains_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
[orm] [bug] ¶
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] [bug] ¶
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
[orm] [bug] [regression] ¶
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
raise DetachedInstanceError
.
References: #6426
[engine] [usecase] [orm] ¶
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 future Engine
, ORM
Session
, asyncio AsyncEngine
. Previously,
the Session
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
[engine] [bug] [regression] ¶
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
raise ResourceClosedException
unconditionally in the same way as
it does when attempting to fetch rows. While this is the correct behavior
going forward, the _cursor.LegacyCursorResult
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] [bug] [regression] ¶
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
[sql] [bug] [regression] ¶
The TypeDecorator
class will now emit a warning when used in SQL
compilation with caching unless the .cache_ok
flag is set to True
or False
. A new class-level attribute TypeDecorator.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 to True
,
False
means they are not.
References: #6436
[orm] [bug] [regression] ¶
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
[orm] [bug] [regression] ¶
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 of
Select.filter_by()
overall to work with column expressions even
in a non-ORM context.
References: #6414
[orm] [bug] [regression] ¶
Fixed regression where using selectinload()
and
subqueryload()
to load a two-level-deep path would lead to an
attribute error.
References: #6419
[orm] [bug] [regression] ¶
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] [bug] [regression] ¶
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 the
Connection.begin_nested()
method, when no transaction is
present, does not create a SAVEPOINT at all and instead starts an outer
transaction, returning a RootTransaction
object instead of a
NestedTransaction
object. This RootTransaction
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 the NestedTransaction
object. The outer transaction is committed by calling upon
Connection.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] [bug] [regression] ¶
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] [usecase] ¶
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
[orm] [bug] ¶
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
[orm] [bug] [caching] [regression] ¶
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
[orm] [bug] ¶
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 non
ClauseElement
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 internal
AttributeError
would lead the attribute to return the expression
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
[orm] [bug] ¶
Fixed issue where using a Select
as a subquery in an ORM
context would modify the Select
in place to disable
eagerloads on that object, which would then cause that same
Select
to not eagerload if it were then re-used in a
top-level execution context.
References: #6378
[orm] [bug] [regression] ¶
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 when
Session.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.
[orm] [bug] [regression] ¶
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
[orm] [bug] [regression] ¶
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 of Query.count()
.
References: #6401
[orm] [bug] [dataclasses] ¶
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 a dataclasses.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 that declared_attr()
, and
therefore should not be re-applied to this class.
References: #6346
[orm] [bug] ¶
Fixed an issue with the (deprecated in 1.4)
ForeignKeyConstraint.copy()
method that caused an error when
invoked with the schema
argument.
References: #6353
[engine] [bug] ¶
Fixed issue where usage of an explicit Sequence
would produce
inconsistent “inline” behavior for an Insert
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] [bug] ¶
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 existing
def visit_empty_set_expr(self, element_types)
which remains in place.
[sql] [bug] [regression] ¶
Fixed regression where usage of the text()
construct inside the
columns clause of a Select
construct, which is better handled
by using a literal_column()
construct, would nonetheless prevent
constructs like union()
from working correctly. Other use cases,
such as constructing subuqeries, continue to work the same as in prior
versions where the text()
construct is silently omitted from the
collection of exported columns. Also repairs similar use within the
ORM.
References: #6343
[sql] [bug] [regression] ¶
Fixed regression involving legacy methods such as
Select.append_column()
where internal assertions would fail.
References: #6261
[sql] [bug] [regression] ¶
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] [bug] [mariadb] [mysql] [oracle] [postgresql] ¶
Ensure that the MySQL and MariaDB dialect ignore the
Identity
construct while rendering the AUTO_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] [bug] ¶
Fixed very old issue where the Enum
datatype would not
inherit the MetaData.schema
parameter of a
MetaData
object when that object were passed to the
Enum
using Enum.metadata
.
References: #6373
[mssql] [bug] [schema] ¶
Add TypeEngine.as_generic()
support for
sqlalchemy.dialects.mysql.BIT
columns, mapping
them to Boolean
.
References: #6345
[mssql] [bug] [regression] ¶
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
[engine] [bug] [regression] ¶
Fixed critical regression caused by the change in #5497 where the connection pool “init” phase no longer occurred within mutexed isolation, allowing other threads to proceed with the dialect uninitialized, which could then impact the compilation of SQL statements.
References: #6337
[orm] [usecase] ¶
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 states immediateload
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.
[orm] [bug] ¶
Fixed bug in new with_loader_criteria()
feature where using a
mixin class with declared_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
[orm] [bug] [regression] ¶
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
[orm] [bug] [result] ¶
Fixed an issue when using 2.0 style execution that prevented using
Result.scalar_one()
or
Result.scalar_one_or_none()
after calling
Result.unique()
, for the case where the ORM is returning a
single-element row in any case.
References: #6299
[sql] [bug] ¶
Fixed issue in SQL compiler where the bound parameters set up for a
Values
construct wouldn’t be positionally tracked correctly if
inside of a CTE
, 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 as literal_binds
.
References: #6327
[sql] [bug] ¶
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 on FunctionElement
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] [bug] ¶
Fixed issue where next_value()
was not deriving its type
from the corresponding Sequence
, instead hardcoded to
Integer
. The specific numeric type is now used.
References: #6287
[mypy] [bug] ¶
Fixed issue where mypy plugin would not correctly interpret an explicit
Mapped
annotation in conjunction with a
relationship()
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] [usecase] ¶
The DateTime.timezone
parameter when set to True
will now make use of the DATETIMEOFFSET
column type with SQL Server
when used to emit DDL, rather than DATETIME
where the flag was silently
ignored.
References: #6306
[orm] [usecase] ¶
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
[orm] [bug] [performance] [regression] [sql] ¶
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 ORM Session
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
[orm] [bug] [regression] ¶
Fixed regression where an attribute that is mapped to a
synonym()
could not be used in column loader options such as
load_only()
.
References: #6272
[orm] [bug] [regression] ¶
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 of
null()
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 like NULL
and true
aren’t “adapted” to refer to columns in ORM statements, and
additionally ensures that a query_expression()
with no default
expression doesn’t render in the statement if a
with_expression()
isn’t used.
References: #6259
[orm] [bug] ¶
Fixed issue in the new feature of Session.refresh()
introduced
by #1763 where eagerly loaded relationships are also refreshed,
where the lazy="raise"
and lazy="raise_on_sql"
loader strategies
would interfere with the immediateload()
loader strategy, thus
breaking the feature for relationships that were loaded with
selectinload()
, subqueryload()
as well.
References: #6252
[engine] [bug] ¶
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 the Inspector.has_table()
method
or for cross-compatibility with older SQLAlchemy versions, the
Engine.has_table()
method.
[sql] [feature] ¶
The tuple returned by CursorResult.inserted_primary_key
is now a
Row
object with a named tuple interface on top of the
existing tuple interface.
References: #3314
[sql] [bug] [regression] ¶
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
[sql] [bug] [regression] [sqlite] ¶
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
[asyncio] [bug] ¶
Fix typo that prevented setting the bind
attribute of an
AsyncSession
to the correct value.
References: #6220
[mssql] [bug] [regression] ¶
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
[orm] [bug] [regression] ¶
Fixed regression where the subqueryload()
loader strategy would
fail to correctly accommodate sub-options, such as a defer()
option on a column, if the “path” of the subqueryload were more than one
level deep.
References: #6221
[orm] [bug] [regression] ¶
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
[orm] [bug] [regression] ¶
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 the Session
into
legacy autocommit mode which commit the transaction. The
Session
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 legacy
Session.autocommit
mode, as this incurs a commit within
a refresh operation.
References: #6233
[orm] [bug] [regression] ¶
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
[orm] [bug] [regression] ¶
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] [bug] [regression] ¶
Fixed up the behavior of the Row
object when dictionary
access is used upon it, meaning converting to a dict via dict(row)
or
accessing members using strings or other objects i.e. row["some_key"]
works as it would with a dictionary, rather than raising TypeError
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 using LegacyRow
, and was to raise
TypeError
for the “future” Row
class. However, the C
version of Row
was failing to raise this TypeError
,
and to complicate matters, the Session.execute()
method now
returns Row
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
and Row
both
provide for string-key access as well as support for dict(row)
, in all
cases emitting the 2.0 deprecation warning when SQLALCHEMY_WARN_20
is
enabled. The Row
object still uses tuple-like behavior for
__contains__
, which is probably the only noticeable behavioral change
compared to LegacyRow
, other than the removal of
dictionary-style methods values()
and items()
.
References: #6218
[sql] [bug] [regression] ¶
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 for
ColumnOperators.in_()
expressions, so in that sense this change
fixes a behavioral regression.
References: #6222
[sql] [bug] ¶
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] [bug] [regression] ¶
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] [bug] ¶
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 of TypeDecorator
and
UserDefinedType
.
[change] [tests] ¶
Added a new flag to DefaultDialect
called supports_schemas
;
third party dialects may set this flag to False
to disable SQLAlchemy’s
schema-level tests when running the test suite for a third party dialect.
[orm] [bug] [regression] ¶
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
single Query.join()
call, would fail to function correctly.
References: #6203
[orm] [bug] [regression] ¶
Fixed critical regression where the Query.yield_per()
method in
the ORM would set up the internal Result
to yield chunks
at a time, however made use of the new Result.unique()
method
which uniques across the entire result. This would lead to lost rows since
the ORM is using id(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 the Query.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 when Query.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 of
Result.unique()
, an error is now raised if rows are fetched
from a “uniqued” ORM-level Result
if any
yield per API is also in use, as the
purpose of yield_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] [bug] [mssql] [oracle] [regression] ¶
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
[sql] [bug] ¶
Executing a Subquery
using Connection.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 underlying Select
object directly for backwards
compatibility. Similarly, the CTE
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 raises ObjectNotExecutableError
.
Previously, 1.4 was attempting to execute the CTE as a statement however it
was working only erratically.
References: #6204
[schema] [bug] ¶
The Table
object now raises an informative error message if
it is instantiated without passing at least the Table.name
and Table.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] [bug] ¶
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
[mypy] [bug] ¶
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] [usecase] [postgresql] ¶
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
[orm] [bug] [regression] ¶
Fixed regression where the joinedload()
loader strategy would
not successfully joinedload to a mapper that is mapper against a
CTE
construct.
References: #6172
[orm] [bug] [regression] ¶
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] [bug] [postgresql] ¶
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
[sql] [bug] [regression] ¶
Fixed regression where use of the Operators.in_()
method with a
Select
object against a non-table-bound column would produce
an AttributeError
, or more generally using a ScalarSelect
that has no datatype in a binary expression would produce invalid state.
References: #6181
[sql] [bug] ¶
Added a new flag to the Dialect
class called
Dialect.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] [bug] ¶
Introduce a new parameter Enum.omit_aliases
in
Enum
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 to False
in the 1.4 series, but will be switched to True
in a future version. A deprecation warning is raise if this flag is not
specified and the passed enum contains aliases.
References: #6146
[mypy] [bug] ¶
Fixed issue in mypy plugin where newly added support for
as_declarative()
needed to more fully add the
DeclarativeMeta
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 the Mapped
name.
References: #sqlalchemy/sqlalchemy2-stubs/#14
[postgresql] [bug] [regression] ¶
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 the Variant
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
[postgresql] [bug] ¶
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
[postgresql] [bug] ¶
Fixed issue where the PostgreSQL PGInspector
, when generated
against an Engine
, 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
[orm] [bug] ¶
Fixed critical issue in the new PropComparator.and_()
feature
where loader strategies that emit secondary SELECT statements such as
selectinload()
and lazyload()
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 with PropComparator.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
[orm] [bug] [regression] ¶
Fixed missing method Session.get()
from the
ScopedSession
interface.
References: #6144
[engine] [usecase] ¶
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
[engine] [bug] ¶
Repair wrong arguments to exception handling method in CursorResult.
References: #6138
[postgresql] [bug] [reflection] ¶
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
[postgresql] [bug] ¶
Modified the is_disconnect()
handler for the pg8000 dialect, which now
accommodates for a new InterfaceError
emitted by pg8000 1.19.0. Pull
request courtesy Hamdi Burak Usul.
References: #6099
[misc] [bug] ¶
Adjusted the usage of the importlib_metadata
library for loading
setuptools entrypoints in order to accommodate for some deprecation
changes.
[orm] [bug] ¶
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"
and
exec("statement")
do not behave the same way. The compatibility
exec_()
function was used instead.
References: #6069
[orm] [bug] ¶
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 when Select.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
[orm] [bug] ¶
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
[orm] [bug] ¶
Repaired support so that the ClauseElement.params()
method can
work correctly with a Select
object that includes joins
across ORM relationship structures, which is a new feature in 1.4.
References: #6124
[orm] [bug] ¶
Fixed issue where a “removed in 2.0” warning were generated internally by the relationship loader mechanics.
References: #6115
[engine] [bug] [regression] ¶
Restored the ResultProxy
name back to the
sqlalchemy.engine
namespace. This name refers to the
LegacyCursorResult
object.
References: #6119
[schema] [bug] ¶
Adjusted the logic that emits DROP statements for Sequence
objects among the dropping of multiple tables, such that all
Sequence
objects are dropped after all tables, even if the
given Sequence
is related only to a Table
object and not directly to the overall MetaData
object.
The use case supports the same Sequence
being associated
with more than one Table
at a time.
This change is also backported to: 1.3.24
References: #6071
[mypy] [bug] ¶
Added support for the Mypy extension to correctly interpret a declarative
base class that’s generated using the as_declarative()
function
as well as the registry.as_declarative_base()
method.
[mypy] [bug] ¶
Fixed bug in Mypy plugin where the Python type detection
for the Boolean
column type would produce
an exception; additionally implemented support for Enum
,
including detection of a string-based enum vs. use of Python enum.Enum
.
References: #6109
[postgresql] [bug] [types] ¶
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
[postgresql] [bug] [reflection] ¶
Fixed reflection of identity columns in tables with mixed case names in PostgreSQL.
References: #6129
[sqlite] [feature] [asyncio] ¶
Added support for the aiosqlite database driver for use with the SQLAlchemy asyncio extension.
See also
References: #5920
[sqlite] [bug] [regression] ¶
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 the sqlcipher3
module
for Python 3 by default before falling back to pysqlcipher3
which
is documented as now being unmaintained.
See also
References: #5848
[orm] [usecase] [dataclasses] ¶
Added support for the declared_attr
object to work in the
context of dataclass fields.
References: #6100
[orm] [bug] [dataclasses] ¶
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
[orm] [bug] [regression] ¶
Fixed regression where the Query.selectable
accessor, which is
a synonym for Query.__clause_element__()
, got removed, it’s now
restored.
References: #6088
[orm] [bug] [regression] ¶
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
[orm] [bug] [regression] ¶
Fixed regression where the Query.filter_by()
method would fail
to locate the correct source entity if the Query.join()
method
had been used targeting an entity without any kind of ON clause.
References: #6092
[orm] [bug] [regression] ¶
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
[orm] [bug] ¶
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 the select()
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
[sql] [bug] [regression] ¶
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
[sql] [bug] [regression] ¶
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] [bug] ¶
Fixed issue in MyPy extension which crashed on detecting the type of a
Column
if the type were given with a module prefix like
sa.Integer()
.
References: #sqlalchemy/sqlalchemy2-stubs/2
[orm] [bug] [regression] ¶
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 the Query
object
which necessarily does this to support many backref-related legacy cases.
However, core select()
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 the
select()
and other Core constructs in the absence of ORM loading
types of functions such as Load
.
The change maintains the behavior of Query
so that backwards
compatibility is maintained. However, when using a select()
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 unless
configure_mappers()
or the newer configure()
has been called elsewhere. Prefer using
relationship.back_populates
for more explicit relationship
configuration which does not have the eager configure requirement.
References: #6066
[orm] [bug] [regression] ¶
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
[orm] [bug] [regression] ¶
Fixed regression where the Query.exists()
method would fail to
create an expression if the entity list of the Query
were
an arbitrary SQL column expression.
References: #6076
[orm] [bug] [regression] ¶
Fixed regression where calling upon Query.count()
in conjunction
with a loader option such as joinedload()
would fail to ignore
the loader option. This is a behavior that has always been very specific to
the Query.count()
method; an error is normally raised if a given
Query
has options that don’t apply to what it is returning.
References: #6052
[orm] [bug] [regression] ¶
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] [bug] [regression] ¶
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] [bug] [regression] ¶
The Python namedtuple()
has the behavior such that the names count
and index
will be served as tuple values if the named tuple includes
those names; if they are absent, then their behavior as methods of
collections.abc.Sequence
is maintained. Therefore the
Row
and LegacyRow
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] [bug] [regression] ¶
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.
via create_engine()
, at which point its behavior can be
customized using the DialectEvents.do_setinputsizes()
hook.
See also
References: #6058
[orm] [bug] ¶
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
[orm] [bug] ¶
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
[orm] [bug] ¶
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
[orm] [bug] ¶
Fixed regression where the relationship.query_class
parameter stopped being functional for “dynamic” relationships. The
AppenderQuery
remains dependent on the legacy Query
class; users are encouraged to migrate from the use of “dynamic”
relationships to using with_parent()
instead.
References: #5981
[orm] [bug] [regression] ¶
Fixed regression where Query.join()
would produce no effect if
the query itself as well as the join target were against a
Table
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 a Query
if the statement produced had not
ORM entities present within it.
References: #6003
[orm] [bug] [asyncio] ¶
The API for AsyncSession.delete()
is now an awaitable;
this method cascades along relationships which must be loaded in a
similar manner as the AsyncSession.merge()
method.
References: #5998
[orm] [bug] ¶
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] [bug] ¶
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
[engine] [bug] ¶
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
[engine] [bug] [regression] ¶
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 the
Connection
explicitly closes out the transaction before
returning the pool to the connection.
References: #6004
[sql] [change] ¶
Altered the compilation for the CTE
construct so that a string is
returned representing the inner SELECT statement if the CTE
is
stringified directly, outside of the context of an enclosing SELECT; This
is the same behavior of FromClause.alias()
and
Select.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.
[sql] [bug] ¶
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()
and custom_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
[sql] [bug] [regression] ¶
Fixed regression where the “unsupported compilation error” for unknown datatypes would fail to raise correctly.
References: #5979
[sql] [bug] [regression] ¶
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
standalone distinct()
is not oriented towards being directly
SELECTed (use select.distinct()
for a regular
SELECT 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 by
connection.execute()
or session.execute()
.
References: #6008
[schema] [bug] [sqlite] ¶
Fixed issue where the CHECK constraint generated by Boolean
or Enum
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
[schema] [bug] ¶
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 a Table
will update
its name as new primary key Column
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
[schema] [bug] ¶
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. The
Table.tometadata()
method is the public API that provides
copying for Table
objects.
References: #5953
[mypy] [feature] ¶
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] [usecase] [asyncio] [mysql] ¶
Added an asyncio.Lock()
within SQLAlchemy’s emulated DBAPI cursor,
local to the connection, for the asyncpg and aiomysql dialects for the
scope of the cursor.execute()
and cursor.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()
and fetch()
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
[postgresql] [bug] ¶
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
[usecase] [ext] ¶
Add new parameter
AutomapBase.prepare.reflection_options
to allow passing of MetaData.reflect()
options like only
or dialect-specific reflection options like oracle_resolve_synonyms
.
References: #5942
[bug] [ext] ¶
The sqlalchemy.ext.mutable
extension now tracks the “parents”
collection using the InstanceState
associated with objects,
rather than the object itself. The latter approach required that the object
be hashable so that it can be inside of a WeakKeyDictionary
, 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
[orm] [feature] ¶
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.
[orm] [bug] ¶
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] [bug] [postgresql] ¶
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] [usecase] [postgresql] [sqlite] ¶
Enhance set_
keyword of OnConflictDoUpdate
to accept a
ColumnCollection
, such as the .c.
collection from a
Selectable
, or the .excluded
contextual object.
References: #5939
[sql] [bug] ¶
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
[sql] [bug] ¶
Fixed 1.4 regression where the Function.in_()
method was
not covered by tests and failed to function properly in all cases.
References: #5934
[sql] [bug] ¶
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
[general] [bug] ¶
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.
[orm] [usecase] ¶
Added ORMExecuteState.bind_mapper
and
ORMExecuteState.all_mappers
accessors to
ORMExecuteState
event object, so that handlers can respond to
the target mapper and/or mapped class or classes involved in an ORM
statement execution.
[orm] [usecase] [asyncio] ¶
Added AsyncSession.scalar()
,
AsyncSession.get()
as well as support for
sessionmaker.begin()
to work as an async context manager with
AsyncSession
. Also added
AsyncSession.in_transaction()
accessor.
[orm] [changed] ¶
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 the
registry.configure()
method that will run configure for the
mappers local in a particular registry only.
References: #5897
[orm] [bug] ¶
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, the
Table
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
[orm] [bug] ¶
Fixed bug involving the restore_load_context
option of ORM events such
as InstanceEvents.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
[orm] [bug] [regression] ¶
Fixed issue in new Session
similar to that of the
Connection
where the new “autobegin” logic could be
tripped into a re-entrant (recursive) state if SQL were executed within the
SessionEvents.after_transaction_create()
event hook.
References: #5845
[orm] [bug] [unitofwork] ¶
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
[orm] [bug] ¶
Fixed regression where the Bundle.single_entity
flag would
take effect for a Bundle
even though it were not set.
Additionally, this flag is legacy as it only makes sense for the
Query
object and not 2.0 style execution. a deprecation
warning is emitted when used with new-style execution.
References: #5702
[orm] [bug] ¶
Fixed regression where creating an aliased
construct against
a plain selectable and including a name would raise an assertionerror.
References: #5750
[orm] [bug] ¶
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 the
SessionEvents.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 like Session.refresh()
. These loads are necessarily
based on primary key identity where addiional WHERE criteria is
never appropriate. [ticket:5762]
Added new attribute ORMExecuteState.is_column_load
to indicate
that a SessionEvents.do_orm_execute()
handler that a particular
operation is a primary-key-directed column attribute load, where additional
criteria should not be added. The with_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]
[orm] [bug] ¶
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
[orm] [bug] ¶
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
only Executable, ClauseElement
were used as the base classes,
additional classes were needed if wanting to use
Session.execute()
. This has been resolved so that those extra
classes aren’t needed.
[orm] [bug] [regression] ¶
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
[engine] [feature] ¶
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 for str()
, 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 as
AddConstraint
, which will adapt its stringify dialect to
one indicated by the element within it, such as the
ExcludeConstraint
object.
[engine] [feature] ¶
Added new execution option
Connection.execution_options.logging_token
. This option
will add an additional per-message token to log messages generated by the
Connection
as it executes statements. This token is not
part of the logger name itself (that part can be affected using the
existing create_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 of
Connection
or Engine
.
References: #5911
[engine] [bug] [sqlite] ¶
Fixed bug in the 2.0 “future” version of Engine
where emitting
SQL during the EngineEvents.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
[engine] [bug] [oracle] [postgresql] ¶
Adjusted the “setinputsizes” logic relied upon by the cx_Oracle, asyncpg
and pg8000 dialects to support a TypeDecorator
that includes
an override the TypeDecorator.get_dbapi_type()
method.
[engine] [bug] ¶
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
as sqlalchemy.future = true
or sqlalchemy.future = false
.
[sql] [feature] ¶
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 function
A TableValuedAlias
construct that renders a SQL function
as a named table
Support for PostgreSQL’s special “derived column” syntax that includes
column names and sometimes datatypes, such as for the
json_to_recordset
function, using the
TableValuedAlias.render_derived()
method.
Support for PostgreSQL’s “WITH ORDINALITY” construct using the
FunctionElement.table_valued.with_ordinality
parameter
Support for selection FROM a SQL function as column-valued scalar, a
syntax supported by PostgreSQL and Oracle, via the
FunctionElement.column_valued()
method
A 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
Table-Valued Functions - in the SQLAlchemy 1.4 / 2.0 Tutorial
References: #3566
[sql] [usecase] ¶
Multiple calls to “returning”, e.g. Insert.returning()
,
may now be chained to add new columns to the RETURNING clause.
References: #5695
[sql] [usecase] ¶
Added Select.outerjoin_from()
method to complement
Select.join_from()
.
[sql] [usecase] ¶
Adjusted the “literal_binds” feature of Compiler
to render
NULL for a bound parameter that has None
as the value, either
explicitly passed or omitted. The previous error message “bind parameter
without a renderable value” is removed, and a missing or None
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
[sql] [bug] ¶
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
[sql] [bug] ¶
Deprecation warnings are emitted under “SQLALCHEMY_WARN_20” mode when
passing a plain string to Session.execute()
.
References: #5754
[sql] [bug] [orm] ¶
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 as literal()
objects,
would fail to be accommodated correctly. [ticket:5768]
[sql] [bug] [mysql] [postgresql] [sqlite] ¶
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
[sql] [bug] ¶
Fixed issue in new Values
construct where passing tuples of
objects would fall back to per-value type detection rather than making use
of the Column
objects passed directly to
Values
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
[sql] [bug] ¶
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
[sql] [bug] ¶
Properly render cycle=False
and order=False
as NO CYCLE
and
NO ORDER
in Sequence
and Identity
objects.
References: #5722
[sql] ¶
Replace Query.with_labels()
and
GenerativeSelect.apply_labels()
with explicit getters and
setters GenerativeSelect.get_label_style()
and
GenerativeSelect.set_label_style()
to accommodate the three
supported label styles: LABEL_STYLE_DISAMBIGUATE_ONLY
,
LABEL_STYLE_TABLENAME_PLUS_COL
, and
LABEL_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; with LABEL_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
of LABEL_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 by LABEL_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] [feature] ¶
Added TypeEngine.as_generic()
to map dialect-specific types,
such as sqlalchemy.dialects.mysql.INTEGER
, with the “best match”
generic SQLAlchemy type, in this case Integer
. Pull
request courtesy Andrew Hannigan.
See also
Reflecting with Database-Agnostic Types - example usage
References: #5659
[schema] [usecase] ¶
The DDLEvents.column_reflect()
event may now be applied to a
MetaData
object where it will take effect for the
Table
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
[schema] [usecase] ¶
Added parameters CreateTable.if_not_exists
,
CreateIndex.if_not_exists
,
DropTable.if_exists
and
DropIndex.if_exists
to the CreateTable
,
DropTable
, CreateIndex
and
DropIndex
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
[schema] [changed] ¶
Altered the behavior of the Identity
construct such that
when applied to a Column
, it will automatically imply that
the value of Column.nullable
should default to False
,
in a similar manner as when the Column.primary_key
parameter is set to True
. This matches the default behavior of all
supporting databases where IDENTITY
implies NOT NULL
. The
PostgreSQL backend is the only one that supports adding NULL
to an
IDENTITY
column, which is here supported by passing a True
value
for the Column.nullable
parameter at the same time.
References: #5775
[asyncio] [usecase] ¶
The AsyncEngine
, AsyncConnection
and
AsyncTransaction
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
for AsyncTransaction
where multiple instances of
AsyncTransaction
can be proxying towards the same sync
Transaction
, and are actually equivalent. The
AsyncConnection.get_transaction()
method will currently return a new
proxying AsyncTransaction
each time as the
AsyncTransaction
is not otherwise statefully associated with its
originating AsyncConnection
.
[asyncio] [bug] ¶
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) for greenlet
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
[asyncio] [bug] ¶
Implemented “connection-binding” for AsyncSession
, the ability to
pass an AsyncConnection
to create an AsyncSession
.
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 the AsyncSession
. Additionally, added methods
AsyncConnection.in_transaction()
,
AsyncConnection.in_nested_transaction()
,
AsyncConnection.get_transaction()
,
AsyncConnection.get_nested_transaction()
and
AsyncConnection.info
attribute.
References: #5811
[asyncio] [bug] ¶
Fixed bug in asyncio connection pool where asyncio.TimeoutError
would
be raised rather than TimeoutError
. Also repaired the
create_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 regular
QueuePool
.
References: #5827
[asyncio] [bug] [pool] ¶
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
[asyncio] ¶
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] [usecase] ¶
Added new parameter ExcludeConstraint.ops
to the
ExcludeConstraint
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
[postgresql] [usecase] ¶
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.
[postgresql] [changed] ¶
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 an ArgumentError
when used under
Python 3. Added test support for Python 2.
[postgresql] [performance] ¶
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
[postgresql] [bug] [mysql] ¶
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 as text()
as the argument
to Select.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
[postgresql] [bug] ¶
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
[postgresql] [bug] ¶
Established support for Column
objects as well as ORM
instrumented attributes as keys in the set_
dictionary passed to the
Insert.on_conflict_do_update()
and
Insert.on_conflict_do_update()
methods, which match to the
Column
objects in the .c
collection of the target
Table
. 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
[postgresql] [bug] [asyncio] ¶
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] [feature] ¶
Added support for the aiomysql driver when using the asyncio SQLAlchemy extension.
See also
References: #5747
[mysql] [bug] [reflection] ¶
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] [usecase] ¶
Implemented INSERT… ON CONFLICT clause for SQLite. Pull request courtesy Ramon Williams.
See also
References: #4010
[sqlite] [bug] ¶
Use python re.search()
instead of re.match()
as the operation
used by the Column.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] [bug] [datatypes] [mysql] ¶
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 method
Comparator.as_numeric()
has been added which
accepts arguments for precision and scale, and will return values as Python
Decimal
objects with no floating point conversion assuming the DBAPI
supports it (all but pysqlite).
References: #5788
[oracle] [bug] ¶
Fixed regression which occured 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 the Connection.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] [bug] ¶
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
[oracle] [bug] ¶
The Oracle dialect now uses
select sys_context( 'userenv', 'current_schema' ) from dual
to get
the default schema name, rather than SELECT USER FROM DUAL
, to
accommodate for changes to the session-local schema name under Oracle.
References: #5716
[usecase] [pool] [tests] ¶
Improve documentation and add test for sub-second pool timeouts. Pull request courtesy Jordan Pittier.
References: #5582
[usecase] [pool] ¶
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 using
insert=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.
[bug] [reflection] ¶
Fixed bug where the now-deprecated autoload
parameter was being called
internally within the reflection routines when a related table were
reflected.
References: #5684
[bug] [pool] ¶
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
[bug] [pool] [pypy] ¶
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
[general] [change] ¶
”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
[general] [bug] ¶
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] [change] ¶
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
[platform] [change] ¶
Installation has been modernized to use setup.cfg for most package metadata.
References: #5404
[platform] [removed] ¶
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
[platform] [removed] ¶
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] [feature] ¶
The ORM can now generate queries previously only available when using
Query
using the select()
construct directly.
A new system by which ORM “plugins” may establish themselves within a
Core Select
allow the majority of query building logic
previously inside of Query
to now take place within
a compilation-level extension for Select
. Similar changes
have been made for the Update
and Delete
constructs as well. The constructs when invoked using Session.execute()
now do ORM-related work within the method. For Select
,
the Result
object returned now contains ORM-level
entities and results.
References: #5159
[orm] [feature] ¶
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 like
joinedload()
. Additionally, a “global” version of the option
allows limiting criteria to be applied to particular entities in
a query globally.
References: #4472
[orm] [feature] ¶
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 like dataclasses
and attrs
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
[orm] [feature] ¶
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
[orm] [feature] ¶
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 as dataclasses
and attrs
.
See also
Python Dataclasses, attrs Supported w/ Declarative, Imperative Mappings
Declarative is now integrated into the ORM with new features
References: #5027
[orm] [feature] ¶
Added “raiseload” feature for ORM mapped columns via defer.raiseload
parameter on defer()
and deferred()
. This provides
similar behavior for column-expression mapped attributes as the
raiseload()
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
[orm] [usecase] ¶
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
[orm] [usecase] ¶
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
[orm] [usecase] ¶
The ORM bulk update and delete operations, historically available via the
Query.update()
and Query.delete()
methods as well as
via the Update
and Delete
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 new with_loader_criteria()
construct is also supported for with bulk update/delete operations.
[orm] [usecase] ¶
Update relationship.sync_backref
flag in a relationship
to make it implicitly False
in viewonly=True
relationships,
preventing synchronization events.
References: #5237
[orm] [change] ¶
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 a IntegrityError
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 the
IntegrityError
as a means of catching whether or not a row
already exists in the table.
References: #4662
[orm] [change] [sql] ¶
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.
[orm] [change] ¶
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 newer
selectinload()
strategy is likely preferable for eager loading
of collections in any case.
See also
References: #4395
[orm] [change] ¶
The ORM will now warn when asked to coerce a select()
construct into
a subquery implicitly. This occurs within places such as the
Query.select_entity_from()
and Query.select_from()
methods
as well as within the with_polymorphic()
function. When a
SelectBase
(which is what’s produced by select()
) or
Query
object is passed directly to these functions and others,
the ORM is typically coercing them to be a subquery by calling the
SelectBase.alias()
method automatically (which is now superseded by
the SelectBase.subquery()
method). See the migration notes linked
below for further details.
References: #4617
[orm] [change] ¶
The “KeyedTuple” class returned by Query
is now replaced with the
Core Row
class, which behaves in the same way as KeyedTuple.
In SQLAlchemy 2.0, both Core and ORM will return result rows using the same
Row
object. In the interim, Core uses a backwards-compatibility
class LegacyRow
that maintains the former mapping/tuple hybrid
behavior used by “RowProxy”.
References: #4710
[orm] [performance] ¶
The bulk update and delete methods Query.update()
and
Query.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.
[orm] [performance] [postgresql] ¶
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 the execute_values()
extension which benches at five times faster than plain
executemany() for this particular backend.
References: #5263
[orm] [bug] ¶
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, the Query
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
[orm] [bug] ¶
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
[orm] [bug] ¶
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 a select()
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
[orm] [bug] ¶
An UnmappedInstanceError
is now raised for InstrumentedAttribute
if an instance is an unmapped object. Prior to this an AttributeError
was raised. Pull request courtesy Ramon Williams.
References: #3858
[orm] [bug] ¶
The Session
object no longer initiates a
SessionTransaction
object immediately upon construction or after
the previous transaction is closed; instead, “autobegin” logic now
initiates the new SessionTransaction
on demand when it is next
needed. Rationale includes to remove reference cycles from a
Session
that has been closed out, as well as to remove the
overhead incurred by the creation of SessionTransaction
objects
that are often discarded immediately. This change affects the behavior of
the SessionEvents.after_transaction_create()
hook in that the event
will be emitted when the Session
first requires a
SessionTransaction
be present, rather than whenever the
Session
were created or the previous SessionTransaction
were closed. Interactions with the Engine
and the database
itself remain unaffected.
References: #5074
[orm] [bug] ¶
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 the Query
is against a Core statement that was ultimately generated from a method
such as Query.subquery()
. First implemented using a deep
search, the current approach leverages the unified select()
construct to keep track of the first mapper that is part of
the construct.
References: #4829
[orm] [bug] [inheritance] ¶
An ArgumentError
is now raised if both the selectable
and
flat
parameters are set to True in with_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
[orm] [bug] ¶
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
[orm] [bug] ¶
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
[orm] [bug] ¶
Improved declarative inheritance scanning to not get tripped up when the same base class appears multiple times in the base inheritance list.
References: #4699
[orm] [bug] ¶
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
[orm] [bug] ¶
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
[orm] [bug] ¶
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 return None
, 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
[orm] [bug] ¶
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()
or
Session.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 flag
Session.expire.autoflush
and
Session.refresh.autoflush
, defaulting to True; when set to
False, this will disable the autoflush that occurs on unexpire for these
attributes.
References: #5226
[orm] [bug] ¶
The behavior of the relationship.cascade_backrefs
flag
will be reversed in 2.0 and set to False
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 of True
at the point at which such a
cascade operation actually takes place. The new behavior can be
established as always by setting the flag to False
on a specific
relationship()
, or more generally can be set up across the board
by setting the the Session.future
flag to True.
References: #5150
[orm] [deprecated] ¶
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 the Session.future
flag is set in
which case they will raise IndexError.
References: #5606
[orm] [deprecated] ¶
Calling the Query.instances()
method without passing a
QueryContext
is deprecated. The original use case for this was
that a Query
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 SQLAlchemy
ResultProxy
that is derived from the mapped column expressions,
which comes originally from the QueryContext
. To retrieve ORM
results from arbitrary SELECT statements, the Query.from_statement()
method should be used.
References: #4719
[orm] [deprecated] ¶
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 like selectinload()
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 as of_type()
, and
reduces internal complexity.
Additionally, the aliased
and from_joinpoint
parameters to
Query.join()
are also deprecated. The aliased()
construct now provides for a great deal of flexibility and capability
and should be used directly.
[orm] [deprecated] ¶
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
[orm] [deprecated] ¶
Passing keyword arguments to methods such as Session.execute()
to be passed into the Session.get_bind()
method is deprecated;
the new Session.execute.bind_arguments
dictionary should
be passed instead.
References: #5573
[orm] [deprecated] ¶
The eagerload()
and relation()
were old aliases and are
now deprecated. Use joinedload()
and relationship()
respectively.
References: #5192
[orm] [removed] ¶
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
[orm] [removed] ¶
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
[orm] [removed] ¶
Remove deprecated function comparable_property
. Please refer to the
hybrid
extension. This also removes the function
comparable_using
in the declarative extension.
Remove deprecated function compile_mappers
. Please use
configure_mappers()
Remove deprecated method collection.linker
. Please refer to the
AttributeEvents.init_collection()
and
AttributeEvents.dispose_collection()
event handlers.
Remove deprecated method Session.prune
and parameter
Session.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 class StrongInstanceDict
.
Remove deprecated parameter mapper.order_by
. Use Query.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] [feature] ¶
Implemented an all-new Result
object that replaces the previous
ResultProxy
object. As implemented in Core, the subclass
CursorResult
features a compatible calling interface with the
previous ResultProxy
, 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
[engine] [feature] [orm] ¶
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
[engine] [feature] [alchemy2] ¶
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.
See also
References: #4644
[engine] [feature] [pyodbc] ¶
Reworked the “setinputsizes()” set of dialect hooks to be correctly extensible for any arbirary 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
[engine] [feature] ¶
Added new reflection method Inspector.get_sequence_names()
which
returns all the sequences defined and Inspector.has_sequence()
to
check if a particular sequence exits.
Support for this method has been added to the backend that support
Sequence
: PostgreSQL, Oracle and MariaDB >= 10.3.
References: #2056
[engine] [feature] ¶
The Table.autoload_with
parameter now accepts an Inspector
object
directly, as well as any Engine
or Connection
as was the case before.
References: #4755
[engine] [change] ¶
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 named Row
, 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 the ResultProxy
is now the
LegacyRow
subclass, which maintains mapping/tuple hybrid behavior,
however the base Row
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
[engine] [performance] ¶
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
[engine] [performance] [change] [py3k] ¶
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 flag returns_unicode_strings
to one of String.RETURNS_CONDITIONAL
or
String.RETURNS_BYTES
, both of which will enable Unicode conversion
even under Python 3.
References: #5315
[engine] [bug] ¶
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
[engine] [bug] ¶
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 ORM Session
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
[engine] [bug] ¶
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 the
Dialect.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
[engine] [deprecated] ¶
The URL
object is now an immutable named tuple. To modify
a URL object, use the URL.set()
method to produce a new URL
object.
See also
The URL object is now immutable - notes on migration
References: #5526
[engine] [deprecated] ¶
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 a RemovedIn20Warning
when SQLAlchemy 2.0 Deprecations Mode is
in use.
References: #4634
[engine] [deprecated] ¶
The server_side_cursors
engine-wide parameter is deprecated and will be
removed in a future release. For unbuffered cursors, the
Connection.execution_options.stream_results
execution
option should be used on a per-execution basis.
[engine] [deprecated] ¶
The Connection.connect()
method is deprecated as is the concept of
“connection branching”, which copies a Connection
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
[engine] [deprecated] ¶
The case_sensitive
flag on create_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
[engine] [deprecated] ¶
”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 an
Engine
will run the operation in a BEGIN block if one is
not started already.
See also
References: #4846
[engine] [deprecated] ¶
Deprecated the behavior by which a Column
can be used as the key
in a result set row lookup, when that Column
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 involving text()
constructs, have been improved
so that this fallback logic is avoided in most cases.
References: #4877
[engine] [deprecated] ¶
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 the Inspector
object.
References: #4755
[engine] [removed] ¶
Remove deprecated method get_primary_keys
in the Dialect
and
Inspector
classes. Please refer to the
Dialect.get_pk_constraint()
and Inspector.get_primary_keys()
methods.
Remove deprecated event dbapi_error
and the method
ConnectionEvents.dbapi_error
. Please refer to the
ConnectionEvents.handle_error()
event.
This change also removes the attributes ExecutionContext.is_disconnect
and ExecutionContext.exception
.
References: #4643
[engine] [removed] ¶
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 private Engine._run_visitor
method
is also removed.
References: #4755
[engine] [removed] ¶
The long-deprecated Inspector.get_table_names.order_by
parameter has
been removed.
References: #4755
[engine] [renamed] ¶
The Inspector.reflecttable()
was renamed to
Inspector.reflect_table()
.
References: #5244
[sql] [feature] ¶
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
[sql] [feature] [mssql] [oracle] ¶
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 Oracle use_binds_for_limits
feature is turned on
unconditionally and this flag is now deprecated.
References: #4808
[sql] [feature] ¶
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
[sql] [feature] ¶
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 ORM Query
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, the ColumnCollection
has been
revised to support duplicate columns as well as to allow integer index
access.
References: #4753
[sql] [feature] ¶
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 ORM
aliased()
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
[sql] [feature] ¶
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
[sql] [feature] ¶
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
[sql] [usecase] ¶
The Index.create()
and Index.drop()
methods now have a
parameter Index.create.checkfirst
, in the same way as that of
Table
and Sequence
, which when enabled will cause the
operation to detect if the index exists (or not) before performing a create
or drop operation.
References: #527
[sql] [usecase] ¶
The true()
and false()
operators may now be applied as the
“onclause” of a join()
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.
[sql] [usecase] ¶
Change the method __str
of ColumnCollection
to avoid
confusing it with a python list of string.
References: #5191
[sql] [usecase] ¶
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
[sql] [usecase] ¶
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 of
Select.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 the
cast()
and type_coerce()
constructs as well as some
single-element boolean expressions.
References: #4449
[sql] [change] ¶
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 of select()
objects.
References: #4617
[sql] [change] ¶
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
[sql] [change] ¶
The select()
construct is moving towards a new calling
form that is select(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 to select()
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
[sql] [change] ¶
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 subclass
FromClause
. For end users, the change mostly means that any
placement of a select()
construct in the FROM clause of another
select()
requires first that it be wrapped in a subquery first,
which historically is through the use of the SelectBase.alias()
method, and is now also available through the use of
SelectBase.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
[sql] [change] ¶
Added a new Core class Subquery
, which takes the place of
Alias
when creating named subqueries against a SelectBase
object. Subquery
acts in the same way as Alias
and is produced from the SelectBase.subquery()
method; for
ease of use and backwards compatibility, the SelectBase.alias()
method is synonymous with this new method.
References: #4617
[sql] [performance] ¶
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
[sql] [bug] ¶
Fixed issue where when constructing constraints from ORM-bound columns,
primarily ForeignKey
objects but also UniqueConstraint
,
CheckConstraint
and others, the ORM-level
InstrumentedAttribute
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
[sql] [bug] ¶
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 multiple
GenericFunction
objects to exist with differing cases. A
GenericFunction
that replaces another on the same name whether or
not it’s case sensitive emits a warning before replacing the object.
[sql] [bug] ¶
Creating an and_()
or or_()
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 empty and_()
or
or_()
constructs, an appropriate default boolean should be included,
such as and_(True, *args)
or or_(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
[sql] [bug] ¶
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 of tuple_()
in a select()
or
Query
will now raise a CompileError
at the
point at which the tuple_()
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,the Bundle
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
[sql] [bug] [postgresql] ¶
Improved support for column names that contain percent signs in the string, including repaired issues involving anoymous 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
[sql] [bug] ¶
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 other Function
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 by
func
and those generated as custom FunctionElement
objects.
References: #4887
[sql] [bug] ¶
Reworked the ClauseElement.compare()
methods in terms of a new
visitor-based approach, and additionally added test coverage ensuring that
all ClauseElement
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
[sql] [bug] ¶
Deprecate usage of DISTINCT ON
in dialect other than PostgreSQL.
Deprecate old usage of string distinct in MySQL dialect
References: #4002
[sql] [bug] ¶
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 applying
CompoundSelect.order_by()
in terms of a Table
- 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 the
CompoundSelect.selected_columns
collections to be usable in the
CompoundSelect.order_by()
method.
References: #4617
[sql] [bug] ¶
The Join
construct no longer considers the “onclause” as a source
of additional FROM objects to be omitted from the FROM list of an enclosing
Select
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 the
Select
/ Join
behave a bit more intuitively.
References: #4621
[sql] [deprecated] ¶
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
[sql] [deprecated] ¶
The Table
class now raises a deprecation warning
when columns with the same name are defined. To replace a column a new
parameter Table.append_column.replace_existing
was
added to the Table.append_column()
method.
The ColumnCollection.contains_column()
will now
raises an error when called with a string, suggesting the caller
to use in
instead.
[sql] [removed] ¶
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; use
create_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
[sql] [removed] ¶
Removed the sqlalchemy.sql.visitors.iterate_depthfirst
and
sqlalchemy.sql.visitors.traverse_depthfirst
functions. These functions
were unused by any part of SQLAlchemy. The
iterate()
and traverse()
functions are commonly used for these functions. Also removed unused
options from the remaining functions including “column_collections”,
“schema_visitor”.
[sql] [removed] ¶
Removed the concept of a bound engine from the Compiler
object,
and removed the .execute()
and .scalar()
methods from
Compiler
. These were essentially forgotten methods from over a
decade ago and had no practical use, and it’s not appropriate for the
Compiler
object itself to be maintaining a reference to an
Engine
.
[sql] [removed] ¶
Remove deprecated methods Compiled.compile
, ClauseElement.__and__
and
ClauseElement.__or__
and attribute Over.func
.
Remove deprecated FromClause.count
method. Please use the
count
function available from the
func
namespace.
References: #4643
[sql] [removed] ¶
Remove deprecated parameters text.bindparams
and text.typemap
.
Please refer to the TextClause.bindparams()
and
TextClause.columns()
methods.
Remove deprecated parameter Table.useexisting
. Please use
Table.extend_existing
.
References: #4643
[sql] [renamed] ¶
Table
parameter mustexist
has been renamed
to Table.must_exist
and will now warn when used.
[sql] [renamed] ¶
The SelectBase.as_scalar()
and Query.as_scalar()
methods have
been renamed to SelectBase.scalar_subquery()
and
Query.scalar_subquery()
, respectively. The old names continue to
exist within 1.4 series with a deprecation warning. In addition, the
implicit coercion of SelectBase
, Alias
, and other
SELECT oriented objects into scalar subqueries when evaluated in a column
context is also deprecated, and emits a warning that the
SelectBase.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 when SelectBase.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. The
Subquery.as_scalar()
method, which was previously
Alias.as_scalar
, is also deprecated; .scalar_subquery()
should be
invoked directly from ` select()
object or Query
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
[sql] [renamed] ¶
Several operators are renamed to achieve more consistent naming across SQLAlchemy.
The operator changes are:
isfalse
is now is_false
isnot_distinct_from
is now is_not_distinct_from
istrue
is now is_true
notbetween
is now not_between
notcontains
is now not_contains
notendswith
is now not_endswith
notilike
is now not_ilike
notlike
is now not_like
notmatch
is now not_match
notstartswith
is now not_startswith
nullsfirst
is now nulls_first
nullslast
is now nulls_last
isnot
is now is_not
not_in_
is now not_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.
[sql] [postgresql] ¶
Allow specifying the data type when creating a Sequence
in
PostgreSQL by using the parameter Sequence.data_type
.
References: #5498
[sql] [reflection] ¶
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
[sql] [reflection] ¶
Added support for reflecting “identity” columns, which are now returned
as part of the structure returned by Inspector.get_columns()
.
When reflecting full Table
objects, identity columns will
be represented using the Identity
construct.
Currently the supported backends are
PostgreSQL >= 10, Oracle >= 12 and MSSQL (with different syntax
and a subset of functionalities).
[schema] [change] ¶
The Enum.create_constraint
and
Boolean.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
[schema] [bug] ¶
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
[schema] [removed] ¶
Remove deprecated class Binary
. Please use LargeBinary
.
References: #4643
[schema] [renamed] ¶
Renamed the Table.tometadata()
method to
Table.to_metadata()
. The previous name remains with a
deprecation warning.
References: #5413
[schema] [sql] ¶
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] [usecase] ¶
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
[extensions] [change] ¶
Added new parameter AutomapBase.prepare.autoload_with
which supersedes AutomapBase.prepare.reflect
and AutomapBase.prepare.engine
.
References: #5142
[postgresql] [usecase] ¶
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
[postgresql] [usecase] ¶
The maximum buffer size for the BufferedRowResultProxy
, which
is used by dialects such as PostgreSQL when stream_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
[postgresql] [change] ¶
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.
[postgresql] [performance] ¶
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
[postgresql] [bug] ¶
The pg8000 dialect has been revised and modernized for the most recent version of the pg8000 driver for PostgreSQL. Changes to the dialect include:
All data types are now sent as text rather than binary.
Using adapters, custom types can be plugged in to pg8000.
Previously, named prepared statements were used for all statements. Now unnamed prepared statements are used by default, and named prepared statements can be used explicitly by calling the Connection.prepare() method, which returns a PreparedStatement object.
Pull request courtesy Tony Locke.
[postgresql] [deprecated] ¶
The pygresql and py-postgresql dialects are deprecated.
References: #5189
[postgresql] [removed] ¶
Remove support for deprecated engine URLs of the form postgres://
;
this has emitted a warning for many years and projects should be
using postgresql://
.
References: #4643
[mysql] [feature] ¶
Added support for MariaDB Connector/Python to the mysql dialect. Original pull request courtesy Georg Richter.
References: #5459
[mysql] [usecase] ¶
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
[mysql] [usecase] ¶
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 the Table
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 a Table
that has a
Sequence
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
[mysql] [bug] ¶
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.
[mysql] [bug] ¶
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] [bug] ¶
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
[mysql] [deprecated] ¶
The OurSQL dialect is deprecated.
References: #5189
[mysql] [removed] ¶
Remove deprecated dialect mysql+gaerdbms
that has been deprecated
since version 1.0. Use the MySQLdb dialect directly.
Remove deprecated parameter quoting
from ENUM
and SET
in the mysql
dialect. The values passed to the
enum or the set are quoted by SQLAlchemy when needed automatically.
References: #4643
[mssql] [feature] [sql] ¶
Added support for the JSON
datatype on the SQL Server
dialect using the JSON
implementation, which implements SQL
Server’s JSON functionality against the NVARCHAR(max)
datatype as per
SQL Server documentation. Implementation courtesy Gord Thompson.
References: #4384
[mssql] [feature] ¶
Added support for “CREATE SEQUENCE” and full Sequence
support for
Microsoft SQL Server. This removes the deprecated feature of using
Sequence
objects to manipulate IDENTITY characteristics which
should now be performed using mssql_identity_start
and
mssql_identity_increment
as documented at Auto Increment Behavior / IDENTITY Columns. The
change includes a new parameter Sequence.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 of Sequence
has been set at 1; this
default is now emitted within the CREATE SEQUENCE DDL for all backends.
[mssql] [usecase] [postgresql] [reflection] [schema] ¶
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
[mssql] [usecase] [postgresql] ¶
Added support for inspection / reflection of partial indexes / filtered
indexes, i.e. those which use the mssql_where
or postgresql_where
parameters, with Index
. The entry is both part of the
dictionary returned by Inspector.get_indexes()
as well as part of a
reflected Index
construct that was reflected. Pull
request courtesy Ramon Williams.
References: #4966
[mssql] [usecase] [reflection] ¶
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
[mssql] [change] ¶
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
[mssql] [bug] [schema] ¶
Fixed an issue where sqlalchemy.engine.reflection.has_table()
always returned
False
for temporary tables.
References: #5597
[mssql] [bug] ¶
Fixed the base class of the DATETIMEOFFSET
datatype to
be based on the DateTime
class hierarchy, as this is a
datetime-holding datatype.
References: #4980
[mssql] [deprecated] ¶
The adodbapi and mxODBC dialects are deprecated.
References: #5189
[mssql] ¶
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.
[mssql] [reflection] ¶
As part of the support for reflecting Identity
objects,
the method Inspector.get_columns()
no longer returns
mssql_identity_start
and mssql_identity_increment
as part of the
dialect_options
. Use the information in the identity
key instead.
References: #5527
[mssql] [engine] ¶
Deprecated the legacy_schema_aliasing
parameter to
sqlalchemy.create_engine()
. This is a long-outdated parameter that has
defaulted to False since version 1.1.
References: #4809
[oracle] [usecase] ¶
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
[oracle] [change] ¶
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.
[oracle] [bug] ¶
Correctly render Sequence
and Identity
column options nominvalue
and nomaxvalue
as NOMAXVALUE` and
``NOMINVALUE
on oracle database.
[oracle] [bug] ¶
The INTERVAL
class of the Oracle dialect is now correctly
a subclass of the abstract version of Interval
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 on
TypeEngine
.
References: #4971
flambé! the dragon and The Alchemist image designs created and generously donated by Rotem Yaari.
Created using Sphinx 4.0.2.