Release: 1.4.0b1 pre release | Release Date: unreleased

SQLAlchemy 1.4 Documentation

1.4 Changelog

1.4.0b1

no release date

general

  • [general] [change]

    All long-deprecated “extension” classes have been removed, including MapperExtension, SessionExtension, PoolListener, ConnectionProxy, AttributExtension. These classes have been deprecated since version 0.7 long superseded by the event listener system.

    References: #4638

orm

  • [orm] [feature]

    Added “raiseload” feature for ORM mapped columns via orm.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] [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 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]

    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]

    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]

    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 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 uinsg the IntegrityError as a means of catching whether or not a row already exists in the table.

    References: #4662

  • [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 superceded by the SelectBase.subquery() method). See the migration notes linked below for further details.

    References: #4617

engine

  • [engine] [feature]

    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. See the migration notes for further details.

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

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

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

    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]

    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]

    The ORDER BY clause of a 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 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] [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]

    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]

    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

sqlite

oracle

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

misc

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

Previous: What’s New in SQLAlchemy 1.4? Next: 1.3 Changelog