github sqlalchemy/sqlalchemy rel_1_1_0b1


Released: June 16, 2016


  • [orm] [ext] [feature] A new ORM extension indexable_toplevel 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] [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 modification 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] [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.

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

    References: #3394

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

    References: #3081

  • [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] [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 :term:deleted. This new state
    represents an object that has been deleted from the :term:persistent
    state and will move to the :term: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.

    Unknown interpreted text role "term".

    Unknown interpreted text role "term".

    Unknown interpreted text role "term".

    References: #2677

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

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


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

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

    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

    References: #3536


  • [sql] [feature] Added TABLESAMPLE support via the new FromClause.tablesample()
    method and standalone function. Pull request courtesy Ilja Everilä.

    References: #3718

  • [sql] [bug] FromClause.count() is deprecated. This function makes use of
    an arbitrary column in the table and is not reliable; for Core use,
    func.count() should be preferred.

    References: #3724

  • [sql] [feature] Added support for ranges in window functions, using the
    expression.over.range_ and
    expression.over.rows parameters.

    References: #3049

  • [sql] [feature] Implemented reflection of CHECK constraints for SQLite and PostgreSQL.
    This is available via the new inspector method
    Inspector.get_check_constraints() as well as when reflecting
    Table objects in the form of CheckConstraint
    objects present in the constraints collection. Pull request courtesy
    Alex Grönholm.

  • [sql] [feature] New ColumnOperators.is_distinct_from() and
    ColumnOperators.isnot_distinct_from() operators; pull request
    courtesy Sebastian Bank.

  • [sql] [feature] Added a hook in DDLCompiler.visit_create_table() called
    DDLCompiler.create_table_suffix(), allowing custom dialects
    to add keywords after the "CREATE TABLE" clause. Pull request
    courtesy Mark Sandan.

  • [sql] [feature] Negative integer indexes are now accommodated by rows
    returned from a ResultProxy. Pull request courtesy
    Emanuele Gaifas.

  • [sql] [feature] Added Select.lateral() and related constructs to allow
    for the SQL standard LATERAL keyword, currently only supported
    by PostgreSQL.

    References: #2857

  • [sql] [feature] Added support for rendering "FULL OUTER JOIN" to both Core and ORM.
    Pull request courtesy Stefan Urbanek.

    References: #1957

  • [sql] [feature] CTE functionality has been expanded to support all DML, allowing
    INSERT, UPDATE, and DELETE statements to both specify their own
    WITH clause, as well as for these statements themselves to be
    CTE expressions when they include a RETURNING clause.

    References: #2551

  • [sql] [feature] Added support for PEP-435-style enumerated classes, namely
    Python 3's enum.Enum class but also including compatible
    enumeration libraries, to the types.Enum datatype.
    The types.Enum datatype now also performs in-Python validation
    of incoming values, and adds an option to forego creating the
    CHECK constraint Enum.create_constraint.
    Pull request courtesy Alex Grönholm.

    References: #3095, #3292

  • [sql] [feature] A deep improvement to the recently added TextClause.columns()
    method, and its interaction with result-row processing, now allows
    the columns passed to the method to be positionally matched with the
    result columns in the statement, rather than matching on name alone.
    The advantage to this includes that when linking a textual SQL statement
    to an ORM or Core table model, no system of labeling or de-duping of
    common column names needs to occur, which also means there's no need
    to worry about how label names match to ORM columns and so-forth. In
    addition, the ResultProxy has been further enhanced to
    map column and string keys to a row with greater precision in some

    References: #3501

  • [sql] [feature] Added a new type to core types.JSON. This is the
    base of the PostgreSQL postgresql.JSON type as well as that
    of the new mysql.JSON type, so that a PG/MySQL-agnostic
    JSON column may be used. The type features basic index and path
    searching support.

    References: #3619

  • [sql] [bug] Fixed an assertion that would raise somewhat inappropriately
    if a Index were associated with a Column that
    is associated with a lower-case-t TableClause; the
    association should be ignored for the purposes of associating
    the index with a Table.

    References: #3616

  • [sql] [change] [mysql] The system by which a Column considers itself to be an
    "auto increment" column has been changed, such that autoincrement
    is no longer implicitly enabled for a Table that has a
    composite primary key. In order to accommodate being able to enable
    autoincrement for a composite PK member column while at the same time
    maintaining SQLAlchemy's long standing behavior of enabling
    implicit autoincrement for a single integer primary key, a third
    state has been added to the Column.autoincrement parameter
    "auto", which is now the default.

    References: #3216

  • [sql] [bug] The type_coerce() construct is now a fully fledged Core
    expression element which is late-evaluated at compile time. Previously,
    the function was only a conversion function which would handle different
    expression inputs by returning either a Label of a column-oriented
    expression or a copy of a given BindParameter object,
    which in particular prevented the operation from being logically
    maintained when an ORM-level expression transformation would convert
    a column to a bound parameter (e.g. for lazy loading).

    References: #3531

  • [sql] [bug] The TypeDecorator type extender will now work in conjunction
    with a SchemaType implementation, typically Enum
    or Boolean with regards to ensuring that the per-table
    events are propagated from the implementation type to the outer type.
    These events are used
    to ensure that the constraints or PostgreSQL types (e.g. ENUM)
    are correctly created (and possibly dropped) along with the parent

    References: #2919

  • [sql] [feature] Added support for "set-aggregate" functions of the form
    <function> WITHIN GROUP (ORDER BY <criteria>), using the
    method FunctionElement.within_group(). A series of common
    set-aggregate functions with return types derived from the set have
    been added. This includes functions like percentile_cont,
    dense_rank and others.

    References: #1370

  • [sql] [feature] [postgresql] Added support for the SQL-standard function array_agg,
    which automatically returns an postgresql.ARRAY of the correct type
    and supports index / slice operations, as well as
    postgresql.array_agg(), which returns a postgresql.ARRAY
    with additional comparison features. As arrays are only
    supported on PostgreSQL at the moment, only actually works on
    PostgreSQL. Also added a new construct
    postgresql.aggregate_order_by in support of PG's
    "ORDER BY" extension.

    References: #3132

  • [sql] [feature] Added a new type to core types.ARRAY. This is the
    base of the PostgreSQL postgresql.ARRAY type, and is now part of Core
    to begin supporting various SQL-standard array-supporting features
    including some functions and eventual support for native arrays
    on other databases that have an "array" concept, such as DB2 or Oracle.
    Additionally, new operators expression.any_() and
    expression.all_() have been added. These support not just
    array constructs on PostgreSQL, but also subqueries that are usable
    on MySQL (but sadly not on PostgreSQL).

    References: #3516

  • [sql] [bug] The behavior of the union() construct and related constructs
    such as Query.union() now handle the case where the embedded
    SELECT statements need to be parenthesized due to the fact that they
    include LIMIT, OFFSET and/or ORDER BY. These queries do not work
    on SQLite
    , and will fail on that backend as they did before, but
    should now work on all other backends.

    References: #2528


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


  • [postgresql] [feature] Added support for PostgreSQL's INSERT..ON CONFLICT using a new
    PostgreSQL-specific postgresql.dml.Insert object.
    Pull request and extensive efforts here by Robin Thomas.

    References: #3529

  • [postgresql] [feature] The DDL for DROP INDEX will emit "CONCURRENTLY" if the
    postgresql_concurrently flag is set upon the
    Index and if the database in use is detected as
    PostgreSQL version 9.2 or greater. For CREATE INDEX, database
    version detection is also added which will omit the clause if
    PG version is less than 8.2. Pull request courtesy Iuri de Silvio.

  • [postgresql] [feature] Added new parameter PGInspector.get_view_names.include,
    allowing specification for what kinds of views should be returned.
    Currently "plain" and "materialized" views are included. Pull
    request courtesy Sebastian Bank.

    References: #3588

  • [postgresql] [feature] Added postgresql_tablespace as an argument to Index
    to allow specification of TABLESPACE for an index in PostgreSQL.
    Complements the same-named parameter on Table. Pull
    request courtesy Benjamin Bertrand.

    References: #3720

  • [postgresql] [feature] Added new parameter
    GenerativeSelect.with_for_update.key_share, which
    will render the FOR NO KEY UPDATE version of FOR UPDATE
    and FOR KEY SHARE instead of FOR SHARE
    on the PostgreSQL backend. Pull request courtesy Sergey Skopin.

  • [postgresql] [feature] [oracle] Added new parameter
    GenerativeSelect.with_for_update.skip_locked, which
    will render the SKIP LOCKED phrase for a FOR UPDATE or
    FOR SHARE lock on the PostgreSQL and Oracle backends. Pull
    request courtesy Jack Zhou.

  • [postgresql] [feature] Added a new dialect for the PyGreSQL PostgreSQL dialect. Thanks
    to Christoph Zwerschke and Kaolin Imago Fire for their efforts.

  • [postgresql] [change] The sqlalchemy.dialects.postgres module, long deprecated, is
    removed; this has emitted a warning for many years and projects
    should be calling upon sqlalchemy.dialects.postgresql.
    Engine URLs of the form postgres:// will still continue to function,

  • [postgresql] [bug] Added support for reflecting the source of materialized views
    to the PostgreSQL version of the Inspector.get_view_definition()

    References: #3587

  • [postgresql] [bug] The use of a postgresql.ARRAY object that refers
    to a types.Enum or postgresql.ENUM subtype
    will now emit the expected "CREATE TYPE" and "DROP TYPE" DDL when
    the type is used within a "CREATE TABLE" or "DROP TABLE".

    References: #2729

  • [postgresql] [bug] The "hashable" flag on special datatypes such as postgresql.ARRAY,
    postgresql.JSON and postgresql.HSTORE is now
    set to False, which allows these types to be fetchable in ORM
    queries that include entities within the row.

    References: #3499

  • [postgresql] [bug] The PostgreSQL postgresql.ARRAY type now supports multidimensional
    indexed access, e.g. expressions such as somecol[5][6] without
    any need for explicit casts or type coercions, provided
    that the postgresql.ARRAY.dimensions parameter is set to the
    desired number of dimensions.

    References: #3487

  • [postgresql] [bug] The return type for the postgresql.JSON and postgresql.JSONB
    when using indexed access has been fixed to work like PostgreSQL itself,
    and returns an expression that itself is of type postgresql.JSON
    or postgresql.JSONB. Previously, the accessor would return
    NullType which disallowed subsequent JSON-like operators to be

    References: #3503

  • [postgresql] [bug] The postgresql.JSON, postgresql.JSONB and
    postgresql.HSTORE datatypes now allow full control over the
    return type from an indexed textual access operation, either column[someindex].astext
    for a JSON type or column[someindex] for an HSTORE type,
    via the postgresql.JSON.astext_type and
    postgresql.HSTORE.text_type parameters.

    References: #3503

  • [postgresql] [bug] The postgresql.JSON.Comparator.astext modifier no longer
    calls upon ColumnElement.cast() implicitly, as PG's JSON/JSONB
    types allow cross-casting between each other as well. Code that
    makes use of ColumnElement.cast() on JSON indexed access,
    e.g. col[someindex].cast(Integer), will need to be changed
    to call postgresql.JSON.Comparator.astext explicitly.

    References: #3503

  • [postgresql] [feature] Added a new constant postgresql.JSON.NULL, indicating
    that the JSON NULL value should be used for a value
    regardless of other settings.

    References: #3514


  • [mysql] [feature] Added support for "autocommit" on MySQL drivers, via the
    AUTOCOMMIT isolation level setting. Pull request courtesy
    Roman Podoliaka.

    References: #3332

  • [mysql] [feature] Added mysql.JSON for MySQL 5.7. The JSON type provides
    persistence of JSON values in MySQL as well as basic operator support
    of "getitem" and "getpath", making use of the JSON_EXTRACT
    function in order to refer to individual paths in a JSON structure.

    References: #3547

  • [mysql] [change] The MySQL dialect no longer generates an extra "KEY" directive when
    generating CREATE TABLE DDL for a table using InnoDB with a
    composite primary key with AUTO_INCREMENT on a column that isn't the
    first column; to overcome InnoDB's limitation here, the PRIMARY KEY
    constraint is now generated with the AUTO_INCREMENT column placed
    first in the list of columns.

    References: #3216


  • [sqlite] [feature] The SQLite dialect now reflects ON UPDATE and ON DELETE phrases
    within foreign key constraints. Pull request courtesy
    Michal Petrucha.

  • [sqlite] [feature] The SQLite dialect now reflects the names of primary key constraints.
    Pull request courtesy Diana Clarke.

    References: #3629

  • [sqlite] [bug] The workaround for right-nested joins on SQLite, where they are rewritten
    as subqueries in order to work around SQLite's lack of support for this
    syntax, is lifted when SQLite version 3.7.16 or greater is detected.

    References: #3634

  • [sqlite] [bug] The workaround for SQLite's unexpected delivery of column names as
    tablename.columnname for some kinds of queries is now disabled
    when SQLite version 3.10.0 or greater is detected.

    References: #3633

  • [sqlite] [change] Added support to the SQLite dialect for the
    Inspector.get_schema_names() method to work with SQLite;
    pull request courtesy Brian Van Klaveren. Also repaired support
    for creation of indexes with schemas as well as reflection of
    foreign key constraints in schema-bound tables.


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

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

    References: #3534

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

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


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

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

    References: #3297

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

latest releases: rel_1_3_20, rel_1_3_19, rel_1_3_18...
pre-release13 months ago