Release: 1.0.0 | Release Date: Not released

SQLAlchemy 1.0 Documentation

1.0 Changelog

1.0.0

no release date

Version 1.0.0 is the first release of the 1.0 series. Many changes described here are also present in the 0.9 and sometimes the 0.8 series as well. For changes that are specific to 1.0 with an emphasis on compatibility concerns, see What’s New in SQLAlchemy 1.0?.

orm

  • [orm] [feature] The “primaryjoin” model has been stretched a bit further to allow a join condition that is strictly from a single column to itself, translated through some kind of SQL function or expression. This is kind of experimental, but the first proof of concept is a “materialized path” join condition where a path string is compared to itself using “like”. The Operators.like() operator has also been added to the list of valid operators to use in a primaryjoin condition.

    This change is also backported to: 0.9.5

    References: #3029

  • [orm] [feature] Added new utility function make_transient_to_detached() which can be used to manufacture objects that behave as though they were loaded from a session, then detached. Attributes that aren’t present are marked as expired, and the object can be added to a Session where it will act like a persistent one.

    This change is also backported to: 0.9.5

    References: #3017

  • [orm] [feature] Added new event handlers AttributeEvents.init_collection() and AttributeEvents.dispose_collection(), which track when a collection is first associated with an instance and when it is replaced. These handlers supersede the collection.linker() annotation. The old hook remains supported through an event adapter.

  • [orm] [feature] The Query will raise an exception when Query.yield_per() is used with mappings or options where either subquery eager loading, or joined eager loading with collections, would take place. These loading strategies are not currently compatible with yield_per, so by raising this error, the method is safer to use. Eager loads can be disabled with the lazyload('*') option or Query.enable_eagerloads().

  • [orm] [feature] A new implementation for KeyedTuple used by the Query object offers dramatic speed improvements when fetching large numbers of column-oriented rows.

    References: #3176

  • [orm] [feature] The behavior of joinedload.innerjoin as well as relationship.innerjoin is now to use “nested” inner joins, that is, right-nested, as the default behavior when an inner join joined eager load is chained to an outer join eager load.

    References: #3008

  • [orm] [feature] UPDATE statements can now be batched within an ORM flush into more performant executemany() call, similarly to how INSERT statements can be batched; this will be invoked within flush to the degree that subsequent UPDATE statements for the same mapping and table involve the identical columns within the VALUES clause, that no SET-level SQL expressions are embedded, and that the versioning requirements for the mapping are compatible with the backend dialect’s ability to return a correct rowcount for an executemany operation.

  • [orm] [feature] The info parameter has been added to the constructor for SynonymProperty and ComparableProperty.

    References: #2963

  • [orm] [feature] The InspectionAttr.info() collection is now moved down to InspectionAttr, where in addition to being available on all MapperProperty objects, it is also now available on hybrid properties, association proxies, when accessed via Mapper.all_orm_descriptors.

    References: #2971

  • [orm] [changed] The proc() callable passed to the create_row_processor() method of custom Bundle classes now accepts only a single “row” argument.

  • [orm] [changed] Deprecated event hooks removed: populate_instance, create_instance, translate_row, append_result

    See also

    migration_deprecated_orm_events

  • [orm] [bug] Fixed bug in subquery eager loading where a long chain of eager loads across a polymorphic-subclass boundary in conjunction with polymorphic loading would fail to locate the subclass-link in the chain, erroring out with a missing property name on an AliasedClass.

    This change is also backported to: 0.9.5, 0.8.7

    References: #3055

  • [orm] [bug] Fixed ORM bug where the class_mapper() function would mask AttributeErrors or KeyErrors that should raise during mapper configuration due to user errors. The catch for attribute/keyerror has been made more specific to not include the configuration step.

    This change is also backported to: 0.9.5, 0.8.7

    References: #3047

  • [orm] [bug] [engine] Fixed bug that affected generally the same classes of event as that of #3199, when the named=True parameter would be used. Some events would fail to register, and others would not invoke the event arguments correctly, generally in the case of when an event was “wrapped” for adaption in some other way. The “named” mechanics have been rearranged to not interfere with the argument signature expected by internal wrapper functions.

    This change is also backported to: 0.9.8

    References: #3197

  • [orm] [bug] Fixed bug that affected many classes of event, particularly ORM events but also engine events, where the usual logic of “de duplicating” a redundant call to event.listen() with the same arguments would fail, for those events where the listener function is wrapped. An assertion would be hit within registry.py. This assertion has now been integrated into the deduplication check, with the added bonus of a simpler means of checking deduplication across the board.

    This change is also backported to: 0.9.8

    References: #3199

  • [orm] [bug] Fixed warning that would emit when a complex self-referential primaryjoin contained functions, while at the same time remote_side was specified; the warning would suggest setting “remote side”. It now only emits if remote_side isn’t present.

    This change is also backported to: 0.9.8

    References: #3194

  • [orm] [bug] [eagerloading] Fixed a regression caused by #2976 released in 0.9.4 where the “outer join” propagation along a chain of joined eager loads would incorrectly convert an “inner join” along a sibling join path into an outer join as well, when only descendant paths should be receiving the “outer join” propagation; additionally, fixed related issue where “nested” join propagation would take place inappropriately between two sibling join paths.

    This change is also backported to: 0.9.7

    References: #3131

  • [orm] [bug] Fixed a regression from 0.9.0 due to #2736 where the Query.select_from() method no longer set up the “from entity” of the Query object correctly, so that subsequent Query.filter_by() or Query.join() calls would fail to check the appropriate “from” entity when searching for attributes by string name.

    This change is also backported to: 0.9.7

    References: #2736, #3083

  • [orm] [bug] Fixed bug where items that were persisted, deleted, or had a primary key change within a savepoint block would not participate in being restored to their former state (not in session, in session, previous PK) after the outer transaction were rolled back.

    This change is also backported to: 0.9.7

    References: #3108

  • [orm] [bug] Fixed bug in subquery eager loading in conjunction with with_polymorphic(), the targeting of entities and columns in the subquery load has been made more accurate with respect to this type of entity and others.

    This change is also backported to: 0.9.7

    References: #3106

  • [orm] [bug] Additional checks have been added for the case where an inheriting mapper is implicitly combining one of its column-based attributes with that of the parent, where those columns normally don’t necessarily share the same value. This is an extension of an existing check that was added via #1892; however this new check emits only a warning, instead of an exception, to allow for applications that may be relying upon the existing behavior.

    This change is also backported to: 0.9.5

    References: #3042

  • [orm] [bug] Modified the behavior of orm.load_only() such that primary key columns are always added to the list of columns to be “undeferred”; otherwise, the ORM can’t load the row’s identity. Apparently, one can defer the mapped primary keys and the ORM will fail, that hasn’t been changed. But as load_only is essentially saying “defer all but X”, it’s more critical that PK cols not be part of this deferral.

    This change is also backported to: 0.9.5

    References: #3080

  • [orm] [bug] Fixed a few edge cases which arise in the so-called “row switch” scenario, where an INSERT/DELETE can be turned into an UPDATE. In this situation, a many-to-one relationship set to None, or in some cases a scalar attribute set to None, may not be detected as a net change in value, and therefore the UPDATE would not reset what was on the previous row. This is due to some as-yet unresovled side effects of the way attribute history works in terms of implicitly assuming None isn’t really a “change” for a previously un-set attribute. See also #3061.

    Note

    This change has been REVERTED in 0.9.6. The full fix will be in version 1.0 of SQLAlchemy.

    This change is also backported to: 0.9.5

    References: #3060

  • [orm] [bug] Related to #3060, an adjustment has been made to the unit of work such that loading for related many-to-one objects is slightly more aggressive, in the case of a graph of self-referential objects that are to be deleted; the load of related objects is to help determine the correct order for deletion if passive_deletes is not set.

    This change is also backported to: 0.9.5

  • [orm] [bug] Fixed bug in SQLite join rewriting where anonymized column names due to repeats would not correctly be rewritten in subqueries. This would affect SELECT queries with any kind of subquery + join.

    This change is also backported to: 0.9.5

    References: #3057

  • [orm] [bug] [sql] Fixes to the newly enhanced boolean coercion in #2804 where the new rules for “where” and “having” woudn’t take effect for the “whereclause” and “having” kw arguments of the select() construct, which is also what Query uses so wasn’t working in the ORM either.

    This change is also backported to: 0.9.5

    References: #3013

  • [orm] [bug] Fixed bug in subquery eager loading where a long chain of eager loads across a polymorphic-subclass boundary in conjunction with polymorphic loading would fail to locate the subclass-link in the chain, erroring out with a missing property name on an AliasedClass.

    This change is also backported to: 0.9.5, 0.8.7

    References: #3055

  • [orm] [bug] Fixed ORM bug where the class_mapper() function would mask AttributeErrors or KeyErrors that should raise during mapper configuration due to user errors. The catch for attribute/keyerror has been made more specific to not include the configuration step.

    This change is also backported to: 0.9.5, 0.8.7

    References: #3047

  • [orm] [bug] A major rework to the behavior of expression labels, most specifically when used with ColumnProperty constructs with custom SQL expressions and in conjunction with the “order by labels” logic first introduced in 0.9. Fixes include that an order_by(Entity.some_col_prop) will now make use of “order by label” rules even if Entity has been subject to aliasing, either via inheritance rendering or via the use of the aliased() construct; rendering of the same column property multiple times with aliasing (e.g. query(Entity.some_prop, entity_alias.some_prop)) will label each occurrence of the entity with a distinct label, and additionally “order by label” rules will work for both (e.g. order_by(Entity.some_prop, entity_alias.some_prop)). Additional issues that could prevent the “order by label” logic from working in 0.9, most notably that the state of a Label could change such that “order by label” would stop working depending on how things were called, has been fixed.

    References: #3148, #3188

  • [orm] [bug] Changed the approach by which the “single inheritance criterion” is applied, when using Query.from_self(), or its common user Query.count(). The criteria to limit rows to those with a certain type is now indicated on the inside subquery, not the outside one, so that even if the “type” column is not available in the columns clause, we can filter on it on the “inner” query.

    References: #3177

  • [orm] [bug] Made a small adjustment to the mechanics of lazy loading, such that it has less chance of interfering with a joinload() in the very rare circumstance that an object points to itself; in this scenario, the object refers to itself while loading its attributes which can cause a mixup between loaders. The use case of “object points to itself” is not fully supported, but the fix also removes some overhead so for now is part of testing.

    References: #3145

  • [orm] [bug] The “resurrect” ORM event has been removed. This event hook had no purpose since the old “mutable attribute” system was removed in 0.8.

    References: #3171

  • [orm] [bug] Fixed bug where attribute “set” events or columns with @validates would have events triggered within the flush process, when those columns were the targets of a “fetch and populate” operation, such as an autoincremented primary key, a Python side default, or a server-side default “eagerly” fetched via RETURNING.

    References: #3167

  • [orm] [bug] [py3k] The IdentityMap exposed from Session.identity now returns lists for items() and values() in Py3K. Early porting to Py3K here had these returning iterators, when they technically should be “iterable views”..for now, lists are OK.

  • [orm] [bug] The “evaulator” for query.update()/delete() won’t work with multi-table updates, and needs to be set to synchronize_session=False or synchronize_session=’fetch’; this now raises an exception, with a message to change the synchronize setting. This is upgraded from a warning emitted as of 0.9.7.

    References: #3117

  • [orm] [enhancement] Adjustment to attribute mechanics concerning when a value is implicitly initialized to None via first access; this action, which has always resulted in a population of the attribute, no longer does so; the None value is returned but the underlying attribute receives no set event. This is consistent with how collections work and allows attribute mechanics to behave more consistently; in particular, getting an attribute with no value does not squash the event that should proceed if the value is actually set to None.

    where bound parameters are rendered inline as strings based on a compile-time option. Work on this feature is courtesy of Dobes Vandermeer.

    References: #3061

engine

  • [engine] [feature] Added new event ConnectionEvents.handle_error(), a more fully featured and comprehensive replacement for ConnectionEvents.dbapi_error().

    This change is also backported to: 0.9.7

    References: #3076

  • [engine] [feature] A new style of warning can be emitted which will “filter” up to N occurrences of a parameterized string. This allows parameterized warnings that can refer to their arguments to be delivered a fixed number of times until allowing Python warning filters to squelch them, and prevents memory from growing unbounded within Python’s warning registries.

    References: #3178

  • [engine] [bug] The string keys that are used to determine the columns impacted for an INSERT or UPDATE are now sorted when they contribute towards the “compiled cache” cache key. These keys were previously not deterministically ordered, meaning the same statement could be cached multiple times on equivalent keys, costing both in terms of memory as well as performance.

    This change is also backported to: 0.9.8

    References: #3165

  • [engine] [bug] Fixed bug which would occur if a DBAPI exception occurs when the engine first connects and does its initial checks, and the exception is not a disconnect exception, yet the cursor raises an error when we try to close it. In this case the real exception would be quashed as we tried to log the cursor close exception via the connection pool and failed, as we were trying to access the pool’s logger in a way that is inappropriate in this very specific scenario.

    This change is also backported to: 0.9.5

    References: #3063

  • [engine] [bug] Fixed some “double invalidate” situations were detected where a connection invalidation could occur within an already critical section like a connection.close(); ultimately, these conditions are caused by the change in #2907, in that the “reset on return” feature calls out to the Connection/Transaction in order to handle it, where “disconnect detection” might be caught. However, it’s possible that the more recent change in #2985 made it more likely for this to be seen as the “connection invalidate” operation is much quicker, as the issue is more reproducible on 0.9.4 than 0.9.3.

    Checks are now added within any section that an invalidate might occur to halt further disallowed operations on the invalidated connection. This includes two fixes both at the engine level and at the pool level. While the issue was observed with highly concurrent gevent cases, it could in theory occur in any kind of scenario where a disconnect occurs within the connection close operation.

    This change is also backported to: 0.9.5

    References: #3043

  • [engine] [bug] Removing (or adding) an event listener at the same time that the event is being run itself, either from inside the listener or from a concurrent thread, now raises a RuntimeError, as the collection used is now an instance of colletions.deque() and does not support changes while being iterated. Previously, a plain Python list was used where removal from inside the event itself would produce silent failures.

    References: #3163

sql

  • [sql] [feature] Liberalized the contract for Index a bit in that you can specify a text() expression as the target; the index no longer needs to have a table-bound column present if the index is to be manually added to the table, either via inline declaration or via Table.append_constraint().

    This change is also backported to: 0.9.5

    References: #3028

  • [sql] [feature] Added new flag expression.between.symmetric, when set to True renders “BETWEEN SYMMETRIC”. Also added a new negation operator “notbetween_op”, which now allows an expression like ~col.between(x, y) to render as “col NOT BETWEEN x AND y”, rather than a parentheiszed NOT string.

    This change is also backported to: 0.9.5

    References: #2990

  • [sql] [feature] Added new method Select.with_statement_hint() and ORM method Query.with_statement_hint() to support statement-level hints that are not specific to a table.

    References: #3206

  • [sql] [feature] The info parameter has been added as a constructor argument to all schema constructs including MetaData, Index, ForeignKey, ForeignKeyConstraint, UniqueConstraint, PrimaryKeyConstraint, CheckConstraint.

    References: #2963

  • [sql] [feature] The Table.autoload_with flag now implies that Table.autoload should be True. Pull request courtesy Malik Diarra.

    References: #3027

  • [sql] [feature] The Select.limit() and Select.offset() methods now accept any SQL expression, in addition to integer values, as arguments. Typically this is used to allow a bound parameter to be passed, which can be substituted with a value later thus allowing Python-side caching of the SQL query. The implementation here is fully backwards compatible with existing third party dialects, however those dialects which implement special LIMIT/OFFSET systems will need modification in order to take advantage of the new capabilities. Limit and offset also support “literal_binds” mode,

    References: #3034

  • [sql] [changed] The column() and table() constructs are now importable from the “from sqlalchemy” namespace, just like every other Core construct.

  • [sql] [changed] The implicit conversion of strings to text() constructs when passed to most builder methods of select() as well as Query now emits a warning with just the plain string sent. The textual conversion still proceeds normally, however. The only method that accepts a string without a warning are the “label reference” methods like order_by(), group_by(); these functions will now at compile time attempt to resolve a single string argument to a column or label expression present in the selectable; if none is located, the expression still renders, but you get the warning again. The rationale here is that the implicit conversion from string to text is more unexpected than not these days, and it is better that the user send more direction to the Core / ORM when passing a raw string as to what direction should be taken. Core/ORM tutorials have been updated to go more in depth as to how text is handled.

    References: #2992

  • [sql] [bug] Fixed bug in Enum and other SchemaType subclasses where direct association of the type with a MetaData would lead to a hang when events (like create events) were emitted on the MetaData.

    This change is also backported to: 0.9.7, 0.8.7

    References: #3124

  • [sql] [bug] Fixed a bug within the custom operator plus TypeEngine.with_variant() system, whereby using a TypeDecorator in conjunction with variant would fail with an MRO error when a comparison operator was used.

    This change is also backported to: 0.9.7, 0.8.7

    References: #3102

  • [sql] [bug] Fixed bug in INSERT..FROM SELECT construct where selecting from a UNION would wrap the union in an anonymous (e.g. unlabled) subquery.

    This change is also backported to: 0.9.5, 0.8.7

    References: #3044

  • [sql] [bug] Fixed bug where Table.update() and Table.delete() would produce an empty WHERE clause when an empty and_() or or_() or other blank expression were applied. This is now consistent with that of select().

    This change is also backported to: 0.9.5, 0.8.7

    References: #3045

  • [sql] [bug] An adjustment to table/index reflection such that if an index reports a column that isn’t found to be present in the table, a warning is emitted and the column is skipped. This can occur for some special system column situations as has been observed with Oracle.

    This change is also backported to: 0.9.8

    References: #3180

  • [sql] [bug] Fixed bug in CTE where literal_binds compiler argument would not be always be correctly propagated when one CTE referred to another aliased CTE in a statement.

    This change is also backported to: 0.9.8

    References: #3154

  • [sql] [bug] Fixed 0.9.7 regression caused by #3067 in conjunction with a mis-named unit test such that so-called “schema” types like Boolean and Enum could no longer be pickled.

    This change is also backported to: 0.9.8

    References: #3144, #3067

  • [sql] [bug] Fix bug in naming convention feature where using a check constraint convention that includes constraint_name would then force all Boolean and Enum types to require names as well, as these implicitly create a constraint, even if the ultimate target backend were one that does not require generation of the constraint such as Postgresql. The mechanics of naming conventions for these particular constraints has been reorganized such that the naming determination is done at DDL compile time, rather than at constraint/table construction time.

    This change is also backported to: 0.9.7

    References: #3067

  • [sql] [bug] Fixed bug in common table expressions whereby positional bound parameters could be expressed in the wrong final order when CTEs were nested in certain ways.

    This change is also backported to: 0.9.7

    References: #3090

  • [sql] [bug] Fixed bug where multi-valued Insert construct would fail to check subsequent values entries beyond the first one given for literal SQL expressions.

    This change is also backported to: 0.9.7

    References: #3069

  • [sql] [bug] Added a “str()” step to the dialect_kwargs iteration for Python version < 2.6.5, working around the “no unicode keyword arg” bug as these args are passed along as keyword args within some reflection processes.

    This change is also backported to: 0.9.7

    References: #3123

  • [sql] [bug] The TypeEngine.with_variant() method will now accept a type class as an argument which is internally converted to an instance, using the same convention long established by other constructs such as Column.

    This change is also backported to: 0.9.7

    References: #3122

  • [sql] [bug] The Column.nullable flag is implicitly set to False when that Column is referred to in an explicit PrimaryKeyConstraint for that table. This behavior now matches that of when the Column itself has the Column.primary_key flag set to True, which is intended to be an exactly equivalent case.

    This change is also backported to: 0.9.5

    References: #3023

  • [sql] [bug] Fixed bug where the Operators.__and__(), Operators.__or__() and Operators.__invert__() operator overload methods could not be overridden within a custom TypeEngine.Comparator implementation.

    This change is also backported to: 0.9.5

    References: #3012

  • [sql] [bug] Fixed bug in new DialectKWArgs.argument_for() method where adding an argument for a construct not previously included for any special arguments would fail.

    This change is also backported to: 0.9.5

    References: #3024

  • [sql] [bug] Fixed regression introduced in 0.9 where new “ORDER BY <labelname>” feature from #1068 would not apply quoting rules to the label name as rendered in the ORDER BY.

    This change is also backported to: 0.9.5

    References: #1068, #3020

  • [sql] [bug] Restored the import for Function to the sqlalchemy.sql.expression import namespace, which was removed at the beginning of 0.9.

    This change is also backported to: 0.9.5

  • [sql] [bug] Fixed bug in Enum and other SchemaType subclasses where direct association of the type with a MetaData would lead to a hang when events (like create events) were emitted on the MetaData.

    This change is also backported to: 0.9.7, 0.8.7

    References: #3124

  • [sql] [bug] Fixed a bug within the custom operator plus TypeEngine.with_variant() system, whereby using a TypeDecorator in conjunction with variant would fail with an MRO error when a comparison operator was used.

    This change is also backported to: 0.9.7, 0.8.7

    References: #3102

  • [sql] [bug] Fixed bug in INSERT..FROM SELECT construct where selecting from a UNION would wrap the union in an anonymous (e.g. unlabled) subquery.

    This change is also backported to: 0.9.5, 0.8.7

    References: #3044

  • [sql] [bug] Fixed bug where Table.update() and Table.delete() would produce an empty WHERE clause when an empty and_() or or_() or other blank expression were applied. This is now consistent with that of select().

    This change is also backported to: 0.9.5, 0.8.7

    References: #3045

  • [sql] [bug] [engine] Fixed bug where a “branched” connection, that is the kind you get when you call Connection.connect(), would not share invalidation status with the parent. The architecture of branching has been tweaked a bit so that the branched connection defers to the parent for all invalidation status and operations.

    References: #3215

  • [sql] [bug] [engine] Fixed bug where a “branched” connection, that is the kind you get when you call Connection.connect(), would not share transaction status with the parent. The architecture of branching has been tweaked a bit so that the branched connection defers to the parent for all transactional status and operations.

    References: #3190

  • [sql] [bug] Using Insert.from_select() now implies inline=True on insert(). This helps to fix a bug where an INSERT...FROM SELECT construct would inadvertently be compiled as “implicit returning” on supporting backends, which would cause breakage in the case of an INSERT that inserts zero rows (as implicit returning expects a row), as well as arbitrary return data in the case of an INSERT that inserts multiple rows (e.g. only the first row of many). A similar change is also applied to an INSERT..VALUES with multiple parameter sets; implicit RETURNING will no longer emit for this statement either. As both of these constructs deal with varible numbers of rows, the ResultProxy.inserted_primary_key accessor does not apply. Previously, there was a documentation note that one may prefer inline=True with INSERT..FROM SELECT as some databases don’t support returning and therefore can’t do “implicit” returning, but there’s no reason an INSERT...FROM SELECT needs implicit returning in any case. Regular explicit Insert.returning() should be used to return variable numbers of result rows if inserted data is needed.

    References: #3169

postgresql

  • [postgresql] [feature] [pg8000] Support is added for “sane multi row count” with the pg8000 driver, which applies mostly to when using versioning with the ORM. The feature is version-detected based on pg8000 1.9.14 or greater in use. Pull request courtesy Tony Locke.

    This change is also backported to: 0.9.8

    References: pull request github:125

  • [postgresql] [feature] Added kw argument postgresql_regconfig to the Operators.match() operator, allows the “reg config” argument to be specified to the to_tsquery() function emitted. Pull request courtesy Jonathan Vanasco.

    This change is also backported to: 0.9.7

    References: #3078, pull request bitbucket:22

  • [postgresql] [feature] Added support for Postgresql JSONB via JSONB. Pull request courtesy Damian Dimmich.

    This change is also backported to: 0.9.7

    References: pull request github:101

  • [postgresql] [feature] Added support for AUTOCOMMIT isolation level when using the pg8000 DBAPI. Pull request courtesy Tony Locke.

    This change is also backported to: 0.9.5

    References: pull request github:88

  • [postgresql] [feature] Added a new flag ARRAY.zero_indexes to the Postgresql ARRAY type. When set to True, a value of one will be added to all array index values before passing to the database, allowing better interoperability between Python style zero-based indexes and Postgresql one-based indexes. Pull request courtesy Alexey Terentev.

    This change is also backported to: 0.9.5

    References: #2785, pull request bitbucket:18

  • [postgresql] [feature] Support has been added for reflection of materialized views and foreign tables, as well as support for materialized views within Inspector.get_view_names(), and a new method PGInspector.get_foreign_table_names() available on the Postgresql version of Inspector. Pull request courtesy Rodrigo Menezes.

    References: #2891, pull request github:128

  • [postgresql] [feature] Added support for PG table options TABLESPACE, ON COMMIT, WITH(OUT) OIDS, and INHERITS, when rendering DDL via the Table construct. Pull request courtesy malikdiarra.

    References: #2051

  • [postgresql] [feature] Added new method PGInspector.get_enums(), when using the inspector for Postgresql will provide a list of ENUM types. Pull request courtesy Ilya Pekelny.

    References: pull request github:126

  • [postgresql] [bug] Added the hashable=False flag to the PG HSTORE type, which is needed to allow the ORM to skip over trying to “hash” an ORM-mapped HSTORE column when requesting it in a mixed column/entity list. Patch courtesy Gunnlaugur Þór Briem.

    This change is also backported to: 0.9.5, 0.8.7

    References: #3053

  • [postgresql] [bug] Added a new “disconnect” message “connection has been closed unexpectedly”. This appears to be related to newer versions of SSL. Pull request courtesy Antti Haapala.

    This change is also backported to: 0.9.5, 0.8.7

    References: pull request bitbucket:13

  • [postgresql] [bug] A revisit to this issue first patched in 0.9.5, apparently psycopg2’s .closed accessor is not as reliable as we assumed, so we have added an explicit check for the exception messages “SSL SYSCALL error: Bad file descriptor” and “SSL SYSCALL error: EOF detected” when detecting an is-disconnect scenario. We will continue to consult psycopg2’s connection.closed as a first check.

    This change is also backported to: 0.9.8

    References: #3021

  • [postgresql] [bug] Fixed bug where Postgresql JSON type was not able to persist or otherwise render a SQL NULL column value, rather than a JSON-encoded 'null'. To support this case, changes are as follows:

    • The value null() can now be specified, which will always result in a NULL value resulting in the statement.
    • A new parameter JSON.none_as_null is added, which when True indicates that the Python None value should be peristed as SQL NULL, rather than JSON-encoded 'null'.

    Retrival of NULL as None is also repaired for DBAPIs other than psycopg2, namely pg8000.

    This change is also backported to: 0.9.8

    References: #3159

  • [postgresql] [bug] The exception wrapping system for DBAPI errors can now accommodate non-standard DBAPI exceptions, such as the psycopg2 TransactionRollbackError. These exceptions will now be raised using the closest available subclass in sqlalchemy.exc, in the case of TransactionRollbackError, sqlalchemy.exc.OperationalError.

    This change is also backported to: 0.9.8

    References: #3075

  • [postgresql] [bug] Fixed bug in postgresql.array object where comparison to a plain Python list would fail to use the correct array constructor. Pull request courtesy Andrew.

    This change is also backported to: 0.9.8

    References: #3141, pull request github:124

  • [postgresql] [bug] Added a supported FunctionElement.alias() method to functions, e.g. the func construct. Previously, behavior for this method was undefined. The current behavior mimics that of pre-0.9.4, which is that the function is turned into a single-column FROM clause with the given alias name, where the column itself is anonymously named.

    This change is also backported to: 0.9.8

    References: #3137

  • [postgresql] [bug] [pg8000] Fixed bug introduced in 0.9.5 by new pg8000 isolation level feature where engine-level isolation level parameter would raise an error on connect.

    This change is also backported to: 0.9.7

    References: #3134

  • [postgresql] [bug] The psycopg2 .closed accessor is now consulted when determining if an exception is a “disconnect” error; ideally, this should remove the need for any other inspection of the exception message to detect disconnect, however we will leave those existing messages in place as a fallback. This should be able to handle newer cases like “SSL EOF” conditions. Pull request courtesy Dirk Mueller.

    This change is also backported to: 0.9.5

    References: #3021, pull request github:87

  • [postgresql] [bug] Added the hashable=False flag to the PG HSTORE type, which is needed to allow the ORM to skip over trying to “hash” an ORM-mapped HSTORE column when requesting it in a mixed column/entity list. Patch courtesy Gunnlaugur Þór Briem.

    This change is also backported to: 0.9.5, 0.8.7

    References: #3053

  • [postgresql] [bug] Added a new “disconnect” message “connection has been closed unexpectedly”. This appears to be related to newer versions of SSL. Pull request courtesy Antti Haapala.

    This change is also backported to: 0.9.5, 0.8.7

    References: pull request bitbucket:13

  • [postgresql] [enhancement] Added a new type postgresql.OID to the Postgresql dialect. While “oid” is generally a private type within PG that is not exposed in modern versions, there are some PG use cases such as large object support where these types might be exposed, as well as within some user-reported schema reflection use cases.

    This change is also backported to: 0.9.5

    References: #3002

mysql

  • [mysql] [bug] MySQL error 2014 “commands out of sync” appears to be raised as a ProgrammingError, not OperationalError, in modern MySQL-Python versions; all MySQL error codes that are tested for “is disconnect” are now checked within OperationalError and ProgrammingError regardless.

    This change is also backported to: 0.9.7, 0.8.7

    References: #3101

  • [mysql] [bug] Fixed bug where column names added to mysql_length parameter on an index needed to have the same quoting for quoted names in order to be recognized. The fix makes the quotes optional but also provides the old behavior for backwards compatibility with those using the workaround.

    This change is also backported to: 0.9.5, 0.8.7

    References: #3085

  • [mysql] [bug] Added support for reflecting tables where an index includes KEY_BLOCK_SIZE using an equal sign. Pull request courtesy Sean McGivern.

    This change is also backported to: 0.9.5, 0.8.7

    References: pull request bitbucket:15

  • [mysql] [bug] MySQL error 2014 “commands out of sync” appears to be raised as a ProgrammingError, not OperationalError, in modern MySQL-Python versions; all MySQL error codes that are tested for “is disconnect” are now checked within OperationalError and ProgrammingError regardless.

    This change is also backported to: 0.9.7, 0.8.7

    References: #3101

  • [mysql] [bug] Fixed bug where column names added to mysql_length parameter on an index needed to have the same quoting for quoted names in order to be recognized. The fix makes the quotes optional but also provides the old behavior for backwards compatibility with those using the workaround.

    This change is also backported to: 0.9.5, 0.8.7

    References: #3085

  • [mysql] [bug] Added support for reflecting tables where an index includes KEY_BLOCK_SIZE using an equal sign. Pull request courtesy Sean McGivern.

    This change is also backported to: 0.9.5, 0.8.7

    References: pull request bitbucket:15

  • [mysql] [bug] MySQL boolean symbols “true”, “false” work again. 0.9’s change in #2682 disallowed the MySQL dialect from making use of the “true” and “false” symbols in the context of “IS” / “IS NOT”, but MySQL supports this syntax even though it has no boolean type. MySQL remains “non native boolean”, but the true() and false() symbols again produce the keywords “true” and “false”, so that an expression like column.is_(true()) again works on MySQL.

    References: #3186

  • [mysql] [bug] The MySQL dialect will now disable ConnectionEvents.handle_error() events from firing for those statements which it uses internally to detect if a table exists or not. This is achieved using an execution option skip_user_error_events that disables the handle error event for the scope of that execution. In this way, user code that rewrites exceptions doesn’t need to worry about the MySQL dialect or other dialects that occasionally need to catch SQLAlchemy specific exceptions.

  • [mysql] [bug] Changed the default value of “raise_on_warnings” to False for MySQLconnector. This was set at True for some reason. The “buffered” flag unfortunately must stay at True as MySQLconnector does not allow a cursor to be closed unless all results are fully fetched.

    References: #2515

sqlite

  • [sqlite] [bug] When selecting from a UNION using an attached database file, the pysqlite driver reports column names in cursor.description as ‘dbname.tablename.colname’, instead of ‘tablename.colname’ as it normally does for a UNION (note that it’s supposed to just be ‘colname’ for both, but we work around it). The column translation logic here has been adjusted to retrieve the rightmost token, rather than the second token, so it works in both cases. Workaround courtesy Tony Roberts.

    This change is also backported to: 0.9.8

    References: #3211

  • [sqlite] [bug] Fixed a SQLite join rewriting issue where a subquery that is embedded as a scalar subquery such as within an IN would receive inappropriate substitutions from the enclosing query, if the same table were present inside the subquery as were in the enclosing query such as in a joined inheritance scenario.

    This change is also backported to: 0.9.7

    References: #3130

  • [sqlite] [bug] SQLite now supports reflection of unique constraints from temp tables; previously, this would fail with a TypeError. Pull request courtesy Johannes Erdfelt.

    See also

    SQLite/Oracle have distinct methods for temporary table/view name reporting - changes regarding SQLite temporary table and view reflection.

    References: #3203, pull request bitbucket:31

  • [sqlite] [bug] Added Inspector.get_temp_table_names() and Inspector.get_temp_view_names(); currently, only the SQLite and Oracle dialects support these methods. The return of temporary table and view names has been removed from SQLite and Oracle’s version of Inspector.get_table_names() and Inspector.get_view_names(); other database backends cannot support this information (such as MySQL), and the scope of operation is different in that the tables can be local to a session and typically aren’t supported in remote schemas.

    References: #3204

mssql

  • [mssql] [feature] Enabled “multivalues insert” for SQL Server 2008. Pull request courtesy Albert Cervin. Also expanded the checks for “IDENTITY INSERT” mode to include when the identity key is present in the VALUEs clause of the statement.

    This change is also backported to: 0.9.7

    References: pull request github:98

  • [mssql] [changed] The hostname-based connection format for SQL Server when using pyodbc will no longer specify a default “driver name”, and a warning is emitted if this is missing. The optimal driver name for SQL Server changes frequently and is per-platform, so hostname based connections need to specify this. DSN-based connections are preferred.

    References: #3182

  • [mssql] [bug] Added statement encoding to the “SET IDENTITY_INSERT” statements which operate when an explicit INSERT is being interjected into an IDENTITY column, to support non-ascii table identifiers on drivers such as pyodbc + unix + py2k that don’t support unicode statements.

    This change is also backported to: 0.9.7, 0.8.7

  • [mssql] [bug] In the SQL Server pyodbc dialect, repaired the implementation for the description_encoding dialect parameter, which when not explicitly set was preventing cursor.description from being parsed correctly in the case of result sets that contained names in alternate encodings. This parameter shouldn’t be needed going forward.

    This change is also backported to: 0.9.7, 0.8.7

    References: #3091

  • [mssql] [bug] Fixed the version string detection in the pymssql dialect to work with Microsoft SQL Azure, which changes the word “SQL Server” to “SQL Azure”.

    This change is also backported to: 0.9.8

    References: #3151

  • [mssql] [bug] Revised the query used to determine the current default schema name to use the database_principal_id() function in conjunction with the sys.database_principals view so that we can determine the default schema independently of the type of login in progress (e.g., SQL Server, Windows, etc).

    This change is also backported to: 0.9.5

    References: #3025

  • [mssql] [bug] Added statement encoding to the “SET IDENTITY_INSERT” statements which operate when an explicit INSERT is being interjected into an IDENTITY column, to support non-ascii table identifiers on drivers such as pyodbc + unix + py2k that don’t support unicode statements.

    This change is also backported to: 0.9.7, 0.8.7

  • [mssql] [bug] In the SQL Server pyodbc dialect, repaired the implementation for the description_encoding dialect parameter, which when not explicitly set was preventing cursor.description from being parsed correctly in the case of result sets that contained names in alternate encodings. This parameter shouldn’t be needed going forward.

    This change is also backported to: 0.9.7, 0.8.7

    References: #3091

oracle

  • [oracle] [feature] Added support for the Oracle table option ON COMMIT.

  • [oracle] [bug] [tests] Fixed bug in oracle dialect test suite where in one test, ‘username’ was assumed to be in the database URL, even though this might not be the case.

    This change is also backported to: 0.9.7

    References: #3128

misc

  • [feature] [examples] Added a new example illustrating materialized paths, using the latest relationship features. Example courtesy Jack Zhou.

    This change is also backported to: 0.9.5

    References: pull request bitbucket:21

  • [feature] [declarative] The declared_attr construct has newly improved behaviors and features in conjunction with declarative. The decorated function will now have access to the final column copies present on the local mixin when invoked, and will also be invoked exactly once for each mapped class, the returned result being memoized. A new modifier declared_attr.cascading is added as well.

    References: #3150

  • [feature] [ext] The sqlalchemy.ext.automap extension will now set cascade="all, delete-orphan" automatically on a one-to-many relationship/backref where the foreign key is detected as containing one or more non-nullable columns. This argument is present in the keywords passed to automap.generate_relationship() in this case and can still be overridden. Additionally, if the ForeignKeyConstraint specifies ondelete="CASCADE" for a non-nullable or ondelete="SET NULL" for a nullable set of columns, the argument passive_deletes=True is also added to the relationship. Note that not all backends support reflection of ondelete, but backends that do include Postgresql and MySQL.

    References: #3210

  • [removed] The Drizzle dialect has been removed from the Core; it is now available as sqlalchemy-drizzle, an independent, third party dialect. The dialect is still based almost entirely off of the MySQL dialect present in SQLAlchemy.

  • [bug] [declarative] The __mapper_args__ dictionary is copied from a declarative mixin or abstract class when accessed, so that modifications made to this dictionary by declarative itself won’t conflict with that of other mappings. The dictionary is modified regarding the version_id_col and polymorphic_on arguments, replacing the column within with the one that is officially mapped to the local class/table.

    This change is also backported to: 0.9.5, 0.8.7

    References: #3062

  • [bug] [ext] Fixed bug in mutable extension where MutableDict did not report change events for the setdefault() dictionary operation.

    This change is also backported to: 0.9.5, 0.8.7

    References: #3093, #3051

  • [bug] [ext] Fixed bug where MutableDict.setdefault() didn’t return the existing or new value (this bug was not released in any 0.8 version). Pull request courtesy Thomas Hervé.

    This change is also backported to: 0.9.5, 0.8.7

    References: #3093, #3051, pull request bitbucket:24

  • [bug] [declarative] Fixed an unlikely race condition observed in some exotic end-user setups, where the attempt to check for “duplicate class name” in declarative would hit upon a not-totally-cleaned-up weak reference related to some other class being removed; the check here now ensures the weakref still references an object before calling upon it further.

    This change is also backported to: 0.9.8

    References: #3208

  • [bug] [ext] Fixed bug in ordering list where the order of items would be thrown off during a collection replace event, if the reorder_on_append flag were set to True. The fix ensures that the ordering list only impacts the list that is explicitly associated with the object.

    This change is also backported to: 0.9.8

    References: #3191

  • [bug] [ext] Fixed bug where ext.mutable.MutableDict failed to implement the update() dictionary method, thus not catching changes. Pull request courtesy Matt Chisholm.

    This change is also backported to: 0.9.8

  • [bug] [ext] Fixed bug where a custom subclass of ext.mutable.MutableDict would not show up in a “coerce” operation, and would instead return a plain ext.mutable.MutableDict. Pull request courtesy Matt Chisholm.

    This change is also backported to: 0.9.8

  • [bug] [pool] Fixed bug in connection pool logging where the “connection checked out” debug logging message would not emit if the logging were set up using logging.setLevel(), rather than using the echo_pool flag. Tests to assert this logging have been added. This is a regression that was introduced in 0.9.0.

    This change is also backported to: 0.9.8

    References: #3168

  • [bug] [tests] Fixed bug where “python setup.py test” wasn’t calling into distutils appropriately, and errors would be emitted at the end of the test suite.

    This change is also backported to: 0.9.7

  • [bug] [declarative] Fixed bug when the declarative __abstract__ flag was not being distinguished for when it was actually the value False. The __abstract__ flag needs to acutally evaluate to a True value at the level being tested.

    This change is also backported to: 0.9.7

    References: #3097

  • [bug] [testsuite] In public test suite, shanged to use of String(40) from less-supported Text in StringTest.test_literal_backslashes. Pullreq courtesy Jan.

    This change is also backported to: 0.9.5

    References: pull request github:95

  • [bug] [tests] [py3k] Corrected for some deprecation warnings involving the imp module and Python 3.3 or greater, when running tests. Pull request courtesy Matt Chisholm.

    This change is also backported to: 0.9.5

    References: #2830, pull request bitbucket:2830

  • [bug] [declarative] The __mapper_args__ dictionary is copied from a declarative mixin or abstract class when accessed, so that modifications made to this dictionary by declarative itself won’t conflict with that of other mappings. The dictionary is modified regarding the version_id_col and polymorphic_on arguments, replacing the column within with the one that is officially mapped to the local class/table.

    This change is also backported to: 0.9.5, 0.8.7

    References: #3062

  • [bug] [ext] Fixed bug in mutable extension where MutableDict did not report change events for the setdefault() dictionary operation.

    This change is also backported to: 0.9.5, 0.8.7

    References: #3093, #3051

  • [bug] [ext] Fixed bug where MutableDict.setdefault() didn’t return the existing or new value (this bug was not released in any 0.8 version). Pull request courtesy Thomas Hervé.

    This change is also backported to: 0.9.5, 0.8.7

    References: #3093, #3051, pull request bitbucket:24

  • [bug] [declarative] A relationship set up with declared_attr on a AbstractConcreteBase base class will now be configured on the abstract base mapping automatically, in addition to being set up on descendant concrete classes as usual.

    References: #2670