Release: 1.1.0b2 | Release Date: July 1, 2016

SQLAlchemy 1.1 Documentation

1.1 Changelog


Released: July 1, 2016


  • [sql] [bug] Fixed issue in SQL math negation operator where the type of the expression would no longer be the numeric type of the original. This would cause issues where the type determined result set behaviors.

    This change is also backported to: 1.0.14

    References: #3735

  • [sql] [bug] Fixed bug whereby the __getstate__ / __setstate__ methods for sqlalchemy.util.Properties were non-working due to the transition in the 1.0 series to __slots__. The issue potentially impacted some third-party applications. Pull request courtesy Pieter Mulder.

    This change is also backported to: 1.0.14

    References: #3728

  • [sql] [bug] The processing performed by the Boolean datatype for backends that only feature integer types has been made consistent between the pure Python and C-extension versions, in that the C-extension version will accept any integer value from the database as a boolean, not just zero and one; additionally, non-boolean integer values being sent to the database are coerced to exactly zero or one, instead of being passed as the original integer value.

    References: #3730

  • [sql] [bug] Rolled back the validation rules a bit in Enum to allow unknown string values to pass through, unless the flag validate_string=True is passed to the Enum; any other kind of object is still of course rejected. While the immediate use is to allow comparisons to enums with LIKE, the fact that this use exists indicates there may be more unknown-string-comparsion use cases than we expected, which hints that perhaps there are some unknown string-INSERT cases too.

    References: #3725


  • [postgresql] [bug] [ext] Made a slight behavioral change in the sqlalchemy.ext.compiler extension, whereby the existing compilation schemes for an established construct would be removed if that construct was itself didn’t already have its own dedicated __visit_name__. This was a rare occurrence in 1.0, however in 1.1 postgresql.ARRAY subclasses sqltypes.ARRAY and has this behavior. As a result, setting up a compilation handler for another dialect such as SQLite would render the main postgresql.ARRAY object no longer compilable.

    References: #3732



Released: June 16, 2016


  • [orm] [feature] [ext] A new ORM extension Indexable is added, which allows construction of Python attributes which refer to specific elements of “indexed” structures such as arrays and JSON fields. Pull request courtesy Jeong YunWon.

  • [orm] [feature] Added new flag Session.bulk_insert_mappings.render_nulls which allows an ORM bulk INSERT to occur with NULL values rendered; this bypasses server side defaults, however allows all statements to be formed with the same set of columns, allowing them to be batched. Pull request courtesy Tobias Sauerwein.

  • [orm] [feature] Added new event AttributeEvents.init_scalar(), as well as a new example suite illustrating its use. This event can be used to provide a Core-generated default value to a Python-side attribute before the object is persisted.

    References: #1311

  • [orm] [feature] Added AutomapBase.prepare.schema to the AutomapBase.prepare() method, to indicate which schema tables should be reflected from if not the default schema. Pull request courtesy Josh Marlow.

    References: pull request github:237

  • [orm] [feature] Added new parameter orm.mapper.passive_deletes to available mapper options. This allows a DELETE to proceed for a joined-table inheritance mapping against the base table only, while allowing for ON DELETE CASCADE to handle deleting the row from the subclass tables.

    References: #2349

  • [orm] [feature] Calling str() on a core SQL construct has been made more “friendly”, when the construct contains non-standard SQL elements such as RETURNING, array index operations, or dialect-specific or custom datatypes. A string is now returned in these cases rendering an approximation of the construct (typically the Postgresql-style version of it) rather than raising an error.

    References: #3631

  • [orm] [feature] The str() call for Query will now take into account the Engine to which the Session is bound, when generating the string form of the SQL, so that the actual SQL that would be emitted to the database is shown, if possible. Previously, only the engine associated with the MetaData to which the mappings are associated would be used, if present. If no bind can be located either on the Session or on the MetaData to which the mappings are associated, then the “default” dialect is used to render the SQL, as was the case previously.

    References: #3081

  • [orm] [feature] The SessionEvents suite now includes events to allow unambiguous tracking of all object lifecycle state transitions in terms of the Session itself, e.g. pending, transient, persistent, detached. The state of the object within each event is also defined.

    References: #2677

  • [orm] [feature] Added a new session lifecycle state deleted. This new state represents an object that has been deleted from the persistent state and will move to the detached state once the transaction is committed. This resolves the long-standing issue that objects which were deleted existed in a gray area between persistent and detached. The InstanceState.persistent accessor will no longer report on a deleted object as persistent; the InstanceState.deleted accessor will instead be True for these objects, until they become detached.

    References: #2677

  • [orm] [feature] Added new checks for the common error case of passing mapped classes or mapped instances into contexts where they are interpreted as SQL bound parameters; a new exception is raised for this.

    References: #3321

  • [orm] [feature] Added new relationship loading strategy orm.raiseload() (also accessible via lazy='raise'). This strategy behaves almost like orm.noload() but instead of returning None it raises an InvalidRequestError. Pull request courtesy Adrian Moennich.

    References: #3512, pull request github:193

  • [orm] [bug] Fixed an issue where a many-to-one change of an object from one parent to another could work inconsistently when combined with an un-flushed modication of the foreign key attribute. The attribute move now considers the database-committed value of the foreign key in order to locate the “previous” parent of the object being moved. This allows events to fire off correctly including backref events. Previously, these events would not always fire. Applications which may have relied on the previously broken behavior may be affected.

    References: #3708

  • [orm] [bug] Fixed bug where deferred columns would inadvertently be set up for database load on the next object-wide unexpire, when the object were merged into the session with session.merge(obj, load=False).

    References: #3488

  • [orm] [bug] [mysql] Further continuing on the common MySQL exception case of a savepoint being cancelled first covered in #2696, the failure mode in which the Session is placed when a SAVEPOINT vanishes before rollback has been improved to allow the Session to still function outside of that savepoint. It is assumed that the savepoint operation failed and was cancelled.

    References: #3680

  • [orm] [bug] Fixed bug where a newly inserted instance that is rolled back would still potentially cause persistence conflicts on the next transaction, because the instance would not be checked that it was expired. This fix will resolve a large class of cases that erronously cause the “New instance with identity X conflicts with persistent instance Y” error.

    References: #3677

  • [orm] [bug] An improvement to the workings of Query.correlate() such that when a “polymorphic” entity is used which represents a straight join of several tables, the statement will ensure that all the tables within the join are part of what’s correlating.

    References: #3662

  • [orm] [bug] Fixed bug which would cause an eagerly loaded many-to-one attribute to not be loaded, if the joined eager load were from a row where the same entity were present multiple times, some calling for the attribute to be eagerly loaded and others not. The logic here is revised to take in the attribute even though a different loader path has handled the parent entity already.

    References: #3431

  • [orm] [bug] A refinement to the logic which adds columns to the resulting SQL when Query.distinct() is combined with Query.order_by() such that columns which are already present will not be added a second time, even if they are labeled with a different name. Regardless of this change, the extra columns added to the SQL have never been returned in the final result, so this change only impacts the string form of the statement as well as its behavior when used in a Core execution context. Additionally, columns are no longer added when the DISTINCT ON format is used, provided the query is not wrapped inside a subquery due to joined eager loading.

    References: #3641

  • [orm] [bug] Fixed issue where two same-named relationships that refer to a base class and a concrete-inherited subclass would raise an error if those relationships were set up using “backref”, while setting up the identical configuration using relationship() instead with the conflicting names would succeed, as is allowed in the case of a concrete mapping.

    References: #3630

  • [orm] [bug] The Session.merge() method now tracks pending objects by primary key before emitting an INSERT, and merges distinct objects with duplicate primary keys together as they are encountered, which is essentially semi-deterministic at best. This behavior matches what happens already with persistent objects.

    References: #3601

  • [orm] [bug] Fixed bug where the “single table inheritance” criteria would be added onto the end of a query in some inappropriate situations, such as when querying from an exists() of a single-inheritance subclass.

    References: #3582

  • [orm] [bug] Added a new type-level modifier TypeEngine.evaluates_none() which indicates to the ORM that a positive set of None should be persisted as the value NULL, instead of omitting the column from the INSERT statement. This feature is used both as part of the implementation for #3514 as well as a standalone feature available on any type.

    References: #3250

  • [orm] [bug] Internal calls to “bookkeeping” functions within Session.bulk_save_objects() and related bulk methods have been scaled back to the extent that this functionality is not currently used, e.g. checks for column default values to be fetched after an INSERT or UPDATE statement.

    References: #3526

  • [orm] [bug] [postgresql] Additional fixes have been made regarding the value of None in conjunction with the Postgresql postgresql.JSON type. When the JSON.none_as_null flag is left at its default value of False, the ORM will now correctly insert the Json “‘null’” string into the column whenever the value on the ORM object is set to the value None or when the value None is used with Session.bulk_insert_mappings(), including if the column has a default or server default on it.

    References: #3514

  • [orm] [change] The Mapper.order_by parameter is deprecated. This is an old parameter no longer relevant to how SQLAlchemy works, once the Query object was introduced. By deprecating it we establish that we aren’t supporting non-working use cases and that we encourage applications to move off of the use of this parameter.

    References: #3394

  • [orm] [change] The Session.weak_identity_map parameter is deprecated. See the new recipe at Session Referencing Behavior for an event-based approach to maintaining strong identity map behavior.

    References: #2677


  • [engine] [feature] Added connection pool events ConnectionEvents.close(), ConnectionEvents.detach(), ConnectionEvents.close_detached().

  • [engine] [feature] All string formatting of bound parameter sets and result rows for logging, exception, and repr() purposes now truncate very large scalar values within each collection, including an “N characters truncated” notation, similar to how the display for large multiple-parameter sets are themselves truncated.

    References: #2837

  • [engine] [feature] Multi-tenancy schema translation for Table objects is added. This supports the use case of an application that uses the same set of Table objects in many schemas, such as schema-per-user. A new execution option Connection.execution_options.schema_translate_map is added.

    References: #2685

  • [engine] [feature] Added a new entrypoint system to the engine to allow “plugins” to be stated in the query string for a URL. Custom plugins can be written which will be given the chance up front to alter and/or consume the engine’s URL and keyword arguments, and then at engine create time will be given the engine itself to allow additional modifications or event registration. Plugins are written as a subclass of CreateEnginePlugin; see that class for details.

    References: #3536



  • [schema] [enhancement] The default generation functions passed to Column objects are now run through “update_wrapper”, or an equivalent function if a callable non-function is passed, so that introspection tools preserve the name and docstring of the wrapped function. Pull request courtesy hsum.

    References: pull request github:204





  • [mssql] [feature] The mssql_clustered flag available on UniqueConstraint, PrimaryKeyConstraint, Index now defaults to None, and can be set to False which will render the NONCLUSTERED keyword in particular for a primary key, allowing a different index to be used as “clustered”. Pull request courtesy Saulius Žemaitaitis.

  • [mssql] [feature] Added basic isolation level support to the SQL Server dialects via create_engine.isolation_level and Connection.execution_options.isolation_level parameters.

    References: #3534

  • [mssql] [bug] Adjustments to the mxODBC dialect to make use of the BinaryNull symbol when appropriate in conjunction with the VARBINARY data type. Pull request courtesy Sheila Allen.

    References: pull request bitbucket:58

  • [mssql] [bug] Fixed issue where the SQL Server dialect would reflect a string- or other variable-length column type with unbounded length by assigning the token "max" to the length attribute of the string. While using the "max" token explicitly is supported by the SQL Server dialect, it isn’t part of the normal contract of the base string types, and instead the length should just be left as None. The dialect now assigns the length to None on reflection of the type so that the type behaves normally in other contexts.

    References: #3504

  • [mssql] [change] The legacy_schema_aliasing flag, introduced in version 1.0.5 as part of #3424 to allow disabling of the MSSQL dialect’s attempts to create aliases for schema-qualified tables, now defaults to False; the old behavior is now disabled unless explicitly turned on.

    References: #3434


  • [feature] [ext] Added MutableSet and MutableList helper classes to the Mutation Tracking extension. Pull request courtesy Jeong YunWon.

    References: #3297

  • [bug] [ext] The docstring specified on a hybrid property or method is now honored at the class level, allowing it to work with tools like Sphinx autodoc. The mechanics here necessarily involve some wrapping of expressions to occur for hybrid properties, which may cause them to appear differently using introspection.

    References: #3653

  • [bug] [sybase] The unsupported Sybase dialect now raises NotImplementedError when attempting to compile a query that includes “offset”; Sybase has no straightforward “offset” feature.

    References: #2278