SQLAlchemy 1.1 Documentation
1.1.0b2¶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
[sql] [bug] Fixed bug whereby the
__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
[sql] [bug] The processing performed by the¶
Booleandatatype 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.
[sql] [bug] Rolled back the validation rules a bit in
Enumto allow unknown string values to pass through, unless the flag
validate_string=Trueis 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.¶
[postgresql] [bug] [ext] Made a slight behavioral change in the
sqlalchemy.ext.compilerextension, 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
sqltypes.ARRAYand has this behavior. As a result, setting up a compilation handler for another dialect such as SQLite would render the main
postgresql.ARRAYobject no longer compilable.¶
[mysql] [bug] Dialed back the “order the primary key columns per auto-increment” described in No more generation of an implicit KEY for composite primary key w/ AUTO_INCREMENT a bit, so that if the
PrimaryKeyConstraintis explicitly defined, the order of columns is maintained exactly, allowing control of this behavior when necessary.¶
1.1.0b1¶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_nullswhich 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.
[orm] [feature] Added
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_deletesto 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.
[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.¶
[orm] [feature] The¶
Querywill now take into account the
Engineto which the
Sessionis 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
MetaDatato which the mappings are associated would be used, if present. If no bind can be located either on the
Sessionor on the
MetaDatato which the mappings are associated, then the “default” dialect is used to render the SQL, as was the case previously.
[orm] [feature] The¶
SessionEventssuite now includes events to allow unambiguous tracking of all object lifecycle state transitions in terms of the
Sessionitself, e.g. pending, transient, persistent, detached. The state of the object within each event is also defined.
[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.persistentaccessor will no longer report on a deleted object as persistent; the
InstanceState.deletedaccessor will instead be True for these objects, until they become detached.
[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.¶
[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
Noneit raises an InvalidRequestError. Pull request courtesy Adrian Moennich.
[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.¶
[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
[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¶
Sessionis placed when a SAVEPOINT vanishes before rollback has been improved to allow the
Sessionto still function outside of that savepoint. It is assumed that the savepoint operation failed and was cancelled.
[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.¶
[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.
[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.¶
[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.
[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.
[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.
[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.¶
[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.
[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.¶
[orm] [bug] [postgresql] Additional fixes have been made regarding the value of
Nonein conjunction with the Postgresql
postgresql.JSONtype. When the
JSON.none_as_nullflag 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
Noneor when the value
Noneis used with
Session.bulk_insert_mappings(), including if the column has a default or server default on it.
[orm] [change] The
Mapper.order_byparameter 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.
[engine] [feature] Added connection pool events
[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.
[engine] [feature] Multi-tenancy schema translation for¶
Tableobjects is added. This supports the use case of an application that uses the same set of
Tableobjects in many schemas, such as schema-per-user. A new execution option
[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.¶
[sql] [feature] Added TABLESAMPLE support via the new
FromClause.tablesample()method and standalone function. Pull request courtesy Ilja Everilä.
[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
Tableobjects in the form of
CheckConstraintobjects present in the constraints collection. Pull request courtesy Alex Grönholm.¶
References: pull request bitbucket:80
[sql] [feature] Added a hook in
DDLCompiler.create_table_suffix(), allowing custom dialects to add keywords after the “CREATE TABLE” clause. Pull request courtesy Mark Sandan.¶
References: pull request github:275
[sql] [feature] Negative integer indexes are now accommodated by rows returned from a¶
ResultProxy. Pull request courtesy Emanuele Gaifas.
References: pull request github:231
[sql] [feature] Added
Select.lateral()and related constructs to allow for the SQL standard LATERAL keyword, currently only supported by Postgresql.
[sql] [feature] Added support for rendering “FULL OUTER JOIN” to both Core and ORM. Pull request courtesy Stefan Urbanek.¶
[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.¶
[sql] [feature] Added support for PEP-435-style enumerated classes, namely Python 3’s
enum.Enumclass but also including compatible enumeration libraries, to the
types.Enumdatatype 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.
[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
ResultProxyhas been further enhanced to map column and string keys to a row with greater precision in some cases.
TextClause.columns() will match columns positionally, not by name, when passed positionally - backwards compatibility remarks
[sql] [feature] Added a new type to core
types.JSON. This is the base of the PostgreSQL
postgresql.JSONtype as well as that of the new
mysql.JSONtype, so that a PG/MySQL-agnostic JSON column may be used. The type features basic index and path searching support.
[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
[sql] [feature] [postgresql] Added support for the SQL-standard function¶
array_agg, which automatically returns an
postgresql.ARRAYof the correct type and supports index / slice operations, as well as
postgresql.array_agg(), which returns a
postgresql.ARRAYwith 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_byin support of PG’s “ORDER BY” extension.
[sql] [feature] Added a new type to core¶
types.ARRAY. This is the base of the PostgreSQL
postgresql.ARRAYtype, 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.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).
[sql] [bug] Fixed an assertion that would raise somewhat inappropriately if a
Indexwere associated with a
Columnthat is associated with a lower-case-t
TableClause; the association should be ignored for the purposes of associating the index with a
[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
Labelof a column-oriented expression or a copy of a given
BindParameterobject, 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).
[sql] [bug] The¶
TypeDecoratortype extender will now work in conjunction with a
Booleanwith 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.
[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.
[sql] [mysql] [change] The system by which a
Columnconsiders itself to be an “auto increment” column has been changed, such that autoincrement is no longer implicitly enabled for a
Tablethat 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
"auto", which is now the default.
[schema] [enhancement] The default generation functions passed to
Columnobjects 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
[postgresql] [feature] Added support for Postgresql’s INSERT..ON CONFLICT using a new Postgresql-specific¶
postgresql.dml.Insertobject. Pull request and extensive efforts here by Robin Thomas.
[postgresql] [feature] The DDL for DROP INDEX will emit “CONCURRENTLY” if the
postgresql_concurrentlyflag is set upon the
Indexand 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.¶
References: pull request bitbucket:84
[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.¶
[postgresql] [feature] Added
postgresql_tablespaceas an argument to
Indexto allow specification of TABLESPACE for an index in Postgresql. Complements the same-named parameter on
Table. Pull request courtesy Benjamin Bertrand.¶
[postgresql] [feature] Added new parameter
GenerativeSelect.with_for_update.key_share, which will render the
FOR NO KEY UPDATEversion of
FOR KEY SHAREinstead of
FOR SHAREon the Postgresql backend. Pull request courtesy Sergey Skopin.¶
References: pull request github:297
[postgresql] [feature] [oracle] Added new parameter
GenerativeSelect.with_for_update.skip_locked, which will render the
SKIP LOCKEDphrase for a
FOR SHARElock on the Postgresql and Oracle backends. Pull request courtesy Jack Zhou.¶
References: pull request bitbucket:86
[postgresql] [feature] Added a new dialect for the PyGreSQL Postgresql dialect. Thanks to Christoph Zwerschke and Kaolin Imago Fire for their efforts.¶
[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.
[postgresql] [bug] The use of a¶
postgresql.ARRAYobject that refers to a
postgresql.ENUMsubtype will now emit the expected “CREATE TYPE” and “DROP TYPE” DDL when the type is used within a “CREATE TABLE” or “DROP TABLE”.
[postgresql] [bug] The “hashable” flag on special datatypes such as
postgresql.HSTOREis now set to False, which allows these types to be fetchable in ORM queries that include entities within the row.
[postgresql] [bug] The Postgresql¶
postgresql.ARRAYtype now supports multidimensional indexed access, e.g. expressions such as
somecolwithout any need for explicit casts or type coercions, provided that the
postgresql.ARRAY.dimensionsparameter is set to the desired number of dimensions.
[postgresql] [bug] The return type for the¶
postgresql.JSONBwhen using indexed access has been fixed to work like Postgresql itself, and returns an expression that itself is of type
postgresql.JSONB. Previously, the accessor would return
NullTypewhich disallowed subsequent JSON-like operators to be used.
[postgresql] [bug] The¶
postgresql.HSTOREdatatypes now allow full control over the return type from an indexed textual access operation, either
column[someindex].astextfor a JSON type or
column[someindex]for an HSTORE type, via the
[postgresql] [bug] The¶
postgresql.JSON.Comparator.astextmodifier 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] [change] The
sqlalchemy.dialects.postgresmodule, 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.¶
[mysql] [feature] Added support for “autocommit” on MySQL drivers, via the AUTOCOMMIT isolation level setting. Pull request courtesy Roman Podoliaka.¶
[mysql] [feature] Added
mysql.JSONfor 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_EXTRACTfunction in order to refer to individual paths in a JSON structure.
[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.
[sqlite] [feature] The SQLite dialect now reflects ON UPDATE and ON DELETE phrases within foreign key constraints. Pull request courtesy Michal Petrucha.¶
References: pull request github:244
[sqlite] [feature] The SQLite dialect now reflects the names of primary key constraints. Pull request courtesy Diana Clarke.¶
[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.¶
[sqlite] [bug] The workaround for SQLite’s unexpected delivery of column names as¶
tablename.columnnamefor some kinds of queries is now disabled when SQLite version 3.10.0 or greater is detected.
[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.
References: pull request github:198
[mssql] [feature] The
mssql_clusteredflag available on
Indexnow 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
BinaryNullsymbol when appropriate in conjunction with the
VARBINARYdata 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.
[mssql] [change] The¶
legacy_schema_aliasingflag, 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.
[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.¶
[bug] [sybase] The unsupported Sybase dialect now raises
NotImplementedErrorwhen attempting to compile a query that includes “offset”; Sybase has no straightforward “offset” feature.¶