1.1.0b1

Released: June 16, 2016

orm

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

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

  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

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

sql

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

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

  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

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

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

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

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

  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

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

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

• [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
  Connection.execution_options.isolation_level
  parameters.

  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

misc

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