Release: 1.4.0b1 pre release | Release Date: unreleased

SQLAlchemy 1.4 Documentation

1.4 Changelog


no release date


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

    References: #4656, #4689

  • [general] [change]

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

    References: #4826

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

    Added new entity-targeting capabilities to the Query object to 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(); a deep search is performed to locate any ORM entity related to the query in order to locate a mapper if one is not otherwise present.

    References: #4829

  • [orm] [bug]

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

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

    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.

    References: #4519

  • [orm] [bug]

    The Session object no longer initates 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]

    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]

    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]

    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]

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

    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

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

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

    The eagerload() and relation() were old aliases and are now deprecated. Use joinedload() and relationship() respectively.

    References: #5192


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

    Implemented the SQLAlchemy 2 create_engine() function which is used for forwards compatibility with SQLAlchemy 2. This engine features always-transactional behavior with autobegin.

    References: #4644

  • [engine] [changed]

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

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

    The long-deprecated Inspector.get_table_names.order_by parameter has been removed.

    References: #4755

  • [engine] [usecase]

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

    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]

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

    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

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

    References: #4710

  • [engine] [change]

    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 :meth:`_events.ConnectionEvents.handle_error` event. This chance also removes the attributes ``ExecutionContext.is_disconnect and ExecutionContext.exception

    References: #4643

  • [engine] [change]

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

    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.

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


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

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

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

    Change the method __str of ColumnCollection to avoid confusing it with a python list of string.

    References: #5191

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

    Reworked the 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]

    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]

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

    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]

    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.

    References: #4569, #4649

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

    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.

    References: #4235, #4633

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

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

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

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

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

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


  • [schema] [bug]

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

    Renamed the Table.tometadata() method to Table.to_metadata(). The previous name remains with a deprecation warning.

    References: #5413


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

    The psycopg2 dialect now defaults to using the very performant execute_values() psycopg2 extension for compiled INSERT statements, and also impements 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] [change]

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

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


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

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


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

  • [feature] [core]

    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.

    References: #4395, #4959, #5087

  • [feature] [pool]

    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

  • [feature] [performance]

    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

  • [usecase] [reflection]

    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

  • [usecase] [ext]

    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

  • [change] [reflection]

    The Inspector.reflecttable() was renamed to Inspector.reflect_table().

    References: #5244

  • [change] [types]

    Remove deprecated class Binary. Please use LargeBinary.

    References: #4643

  • [change] [tests]

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

  • [change] [platform]

    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

  • [installation]

    Installation has been modernized to use setup.cfg for most package metadata.

    References: #5404

  • [change]

    Python 3.4 has reached EOL and its support has been dropped from SQLAlchemy.

  • [deprecations] [dialects]

    Deprecate unsupported dialects and dbapi - Deprecate dialects firefis and sybase. - Deprecate DBAPI

    • adodbapi and mxODBC for mssql

    • oursql for mysql

    • pygresql and py-postgresql for postgresql

    References: #5189

Previous: SQLAlchemy 2.0 Transition Next: 1.3 Changelog