0.9 Changelog

0.9.10

Released: July 22, 2015

orm

• [orm] [feature]

Added a new entry "entity" to the dictionaries returned byQuery.column_descriptions. This refers to the primary ORMmapped class or aliased class that is referred to by the expression.Compared to the existing entry for "type", it will always bea mapped entity, even if extracted from a column expression, orNone if the given expression is a pure core expression.See also #3403 which repaired a regression in this featurewhich was unreleased in 0.9.10 but was released in the 1.0 version.References: #3320

• [orm] [bug]

Query doesn’t support joins, subselects, or specialFROM clauses when using the Query.update() orQuery.delete() methods; instead of silently ignoring thesefields if methods like Query.join() orQuery.select_from() has been called, a warning is emitted.As of 1.0.0b5 this will raise an error.References: #3349

• [orm] [bug]

Fixed bug where the state tracking within multiple, nestedSession.begin_nested() operations would fail to propagatethe “dirty” flag for an object that had been updated withinthe inner savepoint, such that if the enclosing savepoint wererolled back, the object would not be part of the state that wasexpired and therefore reverted to its database state.References: #3352

engine

• [engine] [bug]

Added the string value "none" to those accepted by thePool.reset_on_return parameter as a synonym for None,so that string values can be used for all settings, allowingutilities like engine_from_config() to be usable withoutissue.References: #3375

sql

• [sql] [feature]

Added official support for a CTE used by the SELECT presentinside of Insert.from_select(). This behavior workedaccidentally up until 0.9.9, when it no longer worked due tounrelated changes as part of #3248. Note that thisis the rendering of the WITH clause after the INSERT, before theSELECT; the full functionality of CTEs rendered at the toplevel of INSERT, UPDATE, DELETE is a new feature targeted for alater release.References: #3418

• [sql] [bug]

Fixed issue where a MetaData object that used a namingconvention would not properly work with pickle. The attribute wasskipped leading to inconsistencies and failures if the unpickledMetaData object were used to base additional tablesfrom.References: #3362

postgresql

• [postgresql] [bug]

Fixed a long-standing bug where the Enum type as usedwith the psycopg2 dialect in conjunction with non-ascii valuesand native_enum=False would fail to decode return results properly.This stemmed from when the PG postgresql.ENUM type usedto be a standalone type without a “non native” option.References: #3354

mysql

• [mysql] [bug] [pymysql]

Fixed unicode support for PyMySQL when using an “executemany”operation with unicode parameters. SQLAlchemy now passes boththe statement as well as the bound parameters as unicodeobjects, as PyMySQL generally uses string interpolationinternally to produce the final statement, and in the case ofexecutemany does the “encode” step only on the final statement.References: #3337

• [mysql] [bug] [py3k]

Fixed the mysql.BIT type on Py3K which was not using theord() function correctly. Pull request courtesy David Marin.References: #3333

sqlite

• [sqlite] [bug]

Fixed bug in SQLite dialect where reflection of UNIQUE constraintsthat included non-alphabetic characters in the names, like dots orspaces, would not be reflected with their name.References: #3495

misc

• [bug] [ext]

Fixed bug where when using extended attribute instrumentation system,the correct exception would not be raised when class_mapper()were called with an invalid input that also happened to notbe weak referencable, such as an integer.References: #3408

• [bug] [pypy] [tests]

Fixed an import that prevented “pypy setup.py test” from workingcorrectly.References: #3406

• [bug] [ext]

Fixed regression from 0.9.9 where the as_declarative()symbol was removed from the sqlalchemy.ext.declarativenamespace.References: #3324

0.9.9

Released: March 10, 2015

orm

• [orm] [feature]

Added new parameter Session.connection.execution_optionswhich may be used to set up execution options on a Connectionwhen it is first checked out, before the transaction has begun.This is used to set up options such as isolation level on theconnection before the transaction starts.

See also

Setting Transaction Isolation Levels - new documentation sectiondetailing best practices for setting transaction isolation withsessions.

References: #3296

• [orm] [feature]

Added new method Session.invalidate(), functions similarlyto Session.close(), except also callsConnection.invalidate()on all connections, guaranteeing that they will not be returned tothe connection pool. This is useful in situations e.g. dealingwith gevent timeouts when it is not safe to use the connection further,even for rollbacks.

• [orm] [bug]

Fixed bugs in ORM object comparisons where comparison ofmany-to-one != None would fail if the source were an aliasedclass, or if the query needed to apply special aliasing to theexpression due to aliased joins or polymorphic querying; also fixedbug in the case where comparing a many-to-one to an object statewould fail if the query needed to apply special aliasingdue to aliased joins or polymorphic querying.References: #3310

• [orm] [bug]

Fixed bug where internal assertion would fail in the case wherean after_rollback() handler for a Session incorrectlyadds state to that Session within the handler, and the taskto warn and remove this state (established by #2389) attemptsto proceed.References: #3309

• [orm] [bug]

Fixed bug where TypeError raised when Query.join() calledwith unknown kw arguments would raise its own TypeError dueto broken formatting. Pull request courtesy Malthe Borch.

• [orm] [bug]

Fixed bug in lazy loading SQL construction whereby a complexprimaryjoin that referred to the same “local” column multipletimes in the “column that points to itself” style of self-referentialjoin would not be substituted in all cases. The logic to determinesubstitutions here has been reworked to be more open-ended.References: #3300

• [orm] [bug]

The “wildcard” loader options, in particular the one set up bythe orm.load_only() option to cover all attributes notexplicitly mentioned, now takes into account the superclassesof a given entity, if that entity is mapped with inheritance mapping,so that attribute names within the superclasses are also omittedfrom the load. Additionally, the polymorphic discriminator columnis unconditionally included in the list, just in the same way thatprimary key columns are, so that even with load_only() set up,polymorphic loading of subtypes continues to function correctly.References: #3287

• [orm] [bug] [pypy]

Fixed bug where if an exception were thrown at the start of aQuery before it fetched results, particularly whenrow processors can’t be formed, the cursor would stay open withresults pending and not actually be closed. This is typically onlyan issue on an interpreter like PyPy where the cursor isn’timmediately GC’ed, and can in some circumstances lead to transactions/locks being open longer than is desirable.References: #3285

• [orm] [bug]

Fixed a leak which would occur in the unsupported and highlynon-recommended use case of replacing a relationship on a fixedmapped class many times, referring to an arbitrarily growing number oftarget mappers. A warning is emitted when the old relationship isreplaced, however if the mapping were already used for querying, theold relationship would still be referenced within some registries.References: #3251

• [orm] [bug] [sqlite]

Fixed bug regarding expression mutations which could expressitself as a “Could not locate column” error when usingQuery to select from multiple, anonymous columnentities when querying against SQLite, as a side effect of the“join rewriting” feature used by the SQLite dialect.References: #3241

• [orm] [bug]

Fixed bug where the ON clause for Query.join(),and Query.outerjoin() to a single-inheritance subclassusing of_type() would not render the “single table criteria” inthe ON clause if the from_joinpoint=True flag were set.References: #3232

engine

• [engine] [feature]

Added new user-space accessors for viewing transaction isolationlevels; Connection.get_isolation_level(),Connection.default_isolation_level.

• [engine] [bug]

Fixed bug in Connection and pool where theConnection.invalidate() method, or an invalidation dueto a database disconnect, would fail if theisolation_level parameter had been used withConnection.execution_options(); the “finalizer” that resetsthe isolation level would be called on the no longer opened connection.References: #3302

• [engine] [bug]

A warning is emitted if the isolation_level parameter is usedwith Connection.execution_options() when a Transactionis in play; DBAPIs and/or SQLAlchemy dialects such as psycopg2,MySQLdb may implicitly rollback or commit the transaction, ornot change the setting til next transaction, so this is never safe.References: #3296

sql

• [sql] [bug]

Added the nativeenum flag to the _repr() outputof Enum, which is mostly important when using it withAlembic autogenerate. Pull request courtesy Dimitris Theodorou.

• [sql] [bug]

Fixed bug where using a TypeDecorator that implementeda type that was also a TypeDecorator would fail withPython’s “Cannot create a consistent method resolution order (MRO)”error, when any kind of SQL comparison expression were used againstan object using this type.References: #3278

• [sql] [bug]

Fixed issue where the columns from a SELECT embedded in anINSERT, either through the values clause or as a “from select”,would pollute the column types used in the result set produced bythe RETURNING clause when columns from both statements shared thesame name, leading to potential errors or mis-adaptation whenretrieving the returning rows.References: #3248

schema

• [schema] [bug]

Fixed bug in 0.9’s foreign key setup system, such thatthe logic used to link a ForeignKey to its parent could failwhen the foreign key used “link_to_name=True” in conjunction witha target Table that would not receive its parent column untillater, such as within a reflection + “useexisting” scenario,if the target column in fact had a key value different from its name,as would occur in reflection if column reflect events were used toalter the .key of reflected Column objects so that thelink_to_name becomes significant. Also repaired support for columntype via FK transmission in a similar way when target columns had adifferent key and were referenced using link_to_name.References: #1765, #3298

postgresql

• [postgresql] [feature]

Added support for the CONCURRENTLY keyword with PostgreSQLindexes, established using postgresql_concurrently. Pullrequest courtesy Iuri de Silvio.

See also

Indexes with CONCURRENTLY

• [postgresql] [bug]

Repaired support for PostgreSQL UUID types in conjunction withthe ARRAY type when using psycopg2. The psycopg2 dialect nowemploys use of the psycopg2.extras.register_uuid() hookso that UUID values are always passed to/from the DBAPI asUUID() objects. The UUID.as_uuid flag is stillhonored, except with psycopg2 we need to convert returnedUUID objects back into strings when this is disabled.References: #2940

• [postgresql] [bug]

Added support for the postgresql.JSONB datatype whenusing psycopg2 2.5.4 or greater, which features native conversionof JSONB data so that SQLAlchemy’s converters must be disabled;additionally, the newly added psycopg2 extensionextras.register_default_jsonb is used to establish a JSONdeserializer passed to the dialect via the json_deserializerargument. Also repaired the PostgreSQL integration tests whichweren’t actually round-tripping the JSONB type as opposed to theJSON type. Pull request courtesy Mateusz Susik.

• [postgresql] [bug]

Repaired the use of the “array_oid” flag when registering theHSTORE type with older psycopg2 versions < 2.4.3, which does notsupport this flag, as well as use of the native json serializerhook “register_default_json” with user-defined json_deserializeron psycopg2 versions < 2.5, which does not include native json.

• [postgresql] [bug]

Fixed bug where PostgreSQL dialect would fail to render anexpression in an Index that did not correspond directlyto a table-bound column; typically when a text() constructwas one of the expressions within the index; or could misinterpret thelist of expressions if one or more of them were such an expression.References: #3174

mysql

• [mysql] [bug]

Added a version check to the MySQLdb dialect surrounding thecheck for ‘utf8_bin’ collation, as this fails on MySQL server < 5.0.References: #3274

• [mysql] [change]

The gaerdbms dialect is no longer necessary, and emits adeprecation warning. Google now recommends using the MySQLdbdialect directly.References: #3275

sqlite

• [sqlite] [feature]

Added support for partial indexes (e.g. with a WHERE clause) onSQLite. Pull request courtesy Kai Groner.

See also

Partial Indexes

• [sqlite] [feature]

Added a new SQLite backend for the SQLCipher backend. This backendprovides for encrypted SQLite databases using the pysqlcipher Pythondriver, which is very similar to the pysqlite driver.

See also

pysqlcipher

misc

• [bug] [ext] [py3k]

Fixed bug where the association proxy list class would not interpretslices correctly under Py3K. Pull request courtesyGilles Dartiguelongue.

• [bug] [examples]

Updated the Versioning with a History Table example such thatmapped columns are re-mapped tomatch column names as well as grouping of columns; in particular,this allows columns that are explicitly grouped in a same-column-namedjoined inheritance scenario to be mapped in the same way in thehistory mappings, avoiding warnings added in the 0.9 seriesregarding this pattern and allowing the same view of attributekeys.

• [bug] [examples]

Fixed a bug in the examples/generic_associations/discriminator_on_association.pyexample, where the subclasses of AddressAssociation were not beingmapped as “single table inheritance”, leading to problems when tryingto use the mappings further.

0.9.8

Released: October 13, 2014

orm

• [orm] [bug] [engine]

Fixed bug that affected generally the same classes of eventas that of #3199, when the named=True parameterwould be used. Some events would fail to register, and otherswould not invoke the event arguments correctly, generally in thecase of when an event was “wrapped” for adaption in some other way.The “named” mechanics have been rearranged to not interfere withthe argument signature expected by internal wrapper functions.References: #3197

• [orm] [bug]

Fixed bug that affected many classes of event, particularlyORM 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 thelistener function is wrapped. An assertion would be hit withinregistry.py. This assertion has now been integrated into thededuplication check, with the added bonus of a simpler meansof checking deduplication across the board.References: #3199

• [orm] [bug]

Fixed warning that would emit when a complex self-referentialprimaryjoin contained functions, while at the same time remote_sidewas specified; the warning would suggest setting “remote side”.It now only emits if remote_side isn’t present.References: #3194

orm declarative

• [bug] [declarative] [orm]

Fixed “‘NoneType’ object has no attribute ‘concrete’” errorwhen using AbstractConcreteBase in conjunction witha subclass that declares abstract.References: #3185

engine

• [engine] [bug]

The execution options passed to an Engine either viacreate_engine.execution_options orEngine.update_execution_options() are not passed to thespecial Connection used to initialize the dialectwithin the “first connect” event; dialects will usuallyperform their own queries in this phase, and none of thecurrent available options should be applied here. Inparticular, the “autocommit” option was causing an attempt toautocommit within this initial connect which would fail withan AttributeError due to the non-standard state of theConnection.References: #3200

• [engine] [bug]

The string keys that are used to determine the columns impactedfor an INSERT or UPDATE are now sorted when they contribute towardsthe “compiled cache” cache key. These keys were previously notdeterministically ordered, meaning the same statement could becached multiple times on equivalent keys, costing both in terms ofmemory as well as performance.References: #3165

sql

• [sql] [bug]

Fixed bug where a fair number of SQL elements withinthe sql package would fail to repr() successfully,due to a missing description attribute that would then invokea recursion overflow when an internal AttributeError would thenre-invoke repr().References: #3195

• [sql] [bug]

An adjustment to table/index reflection such that if an indexreports a column that isn’t found to be present in the table,a warning is emitted and the column is skipped. This can occurfor some special system column situations as has been observedwith Oracle.References: #3180

• [sql] [bug]

Fixed bug in CTE where literal_binds compiler argument would notbe always be correctly propagated when one CTE referred to anotheraliased CTE in a statement.References: #3154

• [sql] [bug]

Fixed 0.9.7 regression caused by #3067 in conjunction witha mis-named unit test such that so-called “schema” types likeBoolean and Enum could no longer be pickled.References: #3067, #3144

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 greaterin use. Pull request courtesy Tony Locke.

• [postgresql] [bug]

A revisit to this issue first patched in 0.9.5, apparentlypsycopg2’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 anis-disconnect scenario. We will continue to consult psycopg2’sconnection.closed as a first check.References: #3021

• [postgresql] [bug]

Fixed bug where PostgreSQL JSON type was not able to persist orotherwise 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 alwaysresult in a NULL value resulting in the statement.

• A new parameter JSON.none_as_null is added, whichwhen True indicates that the Python None value should bepersisted as SQL NULL, rather than JSON-encoded 'null'.

Retrival of NULL as None is also repaired for DBAPIs other thanpsycopg2, namely pg8000.References: #3159

• [postgresql] [bug]

The exception wrapping system for DBAPI errors can now accommodatenon-standard DBAPI exceptions, such as the psycopg2TransactionRollbackError. These exceptions will now be raisedusing the closest available subclass in sqlalchemy.exc, in thecase of TransactionRollbackError, sqlalchemy.exc.OperationalError.References: #3075

• [postgresql] [bug]

Fixed bug in postgresql.array object where comparisonto a plain Python list would fail to use the correct array constructor.Pull request courtesy Andrew.References: #3141

• [postgresql] [bug]

Added a supported FunctionElement.alias() method to functions,e.g. the func construct. Previously, behavior for this methodwas undefined. The current behavior mimics that of pre-0.9.4,which is that the function is turned into a single-column FROMclause with the given alias name, where the column itself isanonymously named.References: #3137

mysql

• [mysql] [bug] [mysqlconnector]

Mysqlconnector as of version 2.0, probably as a side effect ofthe python 3 merge, now does not expect percent signs (e.g.as used as the modulus operator and others) to be doubled,even when using the “pyformat” bound parameter format (thischange is not documented by Mysqlconnector). The dialect nowchecks for py2k and for mysqlconnector less than version 2.0when detecting if the modulus operator should be rendered as%% or %.

• [mysql] [bug] [mysqlconnector]

Unicode SQL is now passed for MySQLconnector version 2.0 and above;for Py2k and MySQL < 2.0, strings are encoded.

sqlite

• [sqlite] [bug]

When selecting from a UNION using an attached database file,the pysqlite driver reports column names in cursor.descriptionas ‘dbname.tablename.colname’, instead of ‘tablename.colname’ asit normally does for a UNION (note that it’s supposed to just be‘colname’ for both, but we work around it). The column translationlogic here has been adjusted to retrieve the rightmost token, ratherthan the second token, so it works in both cases. Workaroundcourtesy Tony Roberts.References: #3211

mssql

• [mssql] [bug]

Fixed the version string detection in the pymssql dialect towork with Microsoft SQL Azure, which changes the word “SQL Server”to “SQL Azure”.References: #3151

oracle

• [oracle] [bug]

Fixed long-standing bug in Oracle dialect where bound parameternames that started with numbers would not be quoted, as Oracledoesn’t like numerics in bound parameter names.References: #2138

misc

• [bug] [declarative]

Fixed an unlikely race condition observed in some exotic end-usersetups, where the attempt to check for “duplicate class name” indeclarative would hit upon a not-totally-cleaned-up weak referencerelated to some other class being removed; the check here now ensuresthe weakref still references an object before calling upon it further.References: #3208

• [bug] [ext]

Fixed bug in ordering list where the order of items would bethrown off during a collection replace event, if thereorder_on_append flag were set to True. The fix ensures that theordering list only impacts the list that is explicitly associatedwith the object.References: #3191

• [bug] [ext]

Fixed bug where ext.mutable.MutableDictfailed to implement the update() dictionary method, thusnot catching changes. Pull request courtesy Matt Chisholm.

• [bug] [ext]

Fixed bug where a custom subclass of ext.mutable.MutableDictwould not show up in a “coerce” operation, and would insteadreturn a plain ext.mutable.MutableDict. Pull requestcourtesy Matt Chisholm.

• [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 usinglogging.setLevel(), rather than using the echo_pool flag.Tests to assert this logging have been added. This is aregression that was introduced in 0.9.0.References: #3168

0.9.7

Released: July 22, 2014

orm

• [orm] [bug] [eagerloading]

Fixed a regression caused by #2976 released in 0.9.4 wherethe “outer join” propagation along a chain of joined eager loadswould incorrectly convert an “inner join” along a sibling join pathinto an outer join as well, when only descendant paths should bereceiving the “outer join” propagation; additionally, fixed relatedissue where “nested” join propagation would take place inappropriatelybetween two sibling join paths.References: #3131

• [orm] [bug]

Fixed a regression from 0.9.0 due to #2736 where theQuery.select_from() method no longer set up the “fromentity” of the Query object correctly, so thatsubsequent Query.filter_by() or Query.join()calls would fail to check the appropriate “from” entity whensearching for attributes by string name.References: #2736, #3083

• [orm] [bug]

The “evaluator” for query.update()/delete() won’t work with multi-tableupdates, and needs to be set to synchronize_session=False orsynchronize_session=’fetch’; a warning is now emitted. In1.0 this will be promoted to a full exception.References: #3117

• [orm] [bug]

Fixed bug where items that were persisted, deleted, or had aprimary key change within a savepoint block would notparticipate in being restored to their former state (not insession, in session, previous PK) after the outer transactionwere rolled back.References: #3108

• [orm] [bug]

Fixed bug in subquery eager loading in conjunction withwith_polymorphic(), the targeting of entities and columnsin the subquery load has been made more accurate with respectto this type of entity and others.References: #3106

• [orm] [bug]

Fixed bug involving dynamic attributes, that was again a regressionof #3060 from version 0.9.5. A self-referential relationshipwith lazy=’dynamic’ would raise a TypeError within a flush operation.References: #3099

engine

• [engine] [feature]

Added new event ConnectionEvents.handle_error(), a morefully featured and comprehensive replacement forConnectionEvents.dbapi_error().References: #3076

sql

• [sql] [bug]

Fixed bug in Enum and other SchemaTypesubclasses where direct association of the type with aMetaData would lead to a hang when events(like create events) were emitted on the MetaData.This change is also backported to: 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 withvariant would fail with an MRO error when a comparison operator was used.This change is also backported to: 0.8.7

References: #3102

• [sql] [bug]

Fix bug in naming convention feature where using a checkconstraint convention that includes constraint_name wouldthen force all Boolean and Enum types torequire names as well, as these implicitly create aconstraint, even if the ultimate target backend were one that doesnot require generation of the constraint such as PostgreSQL.The mechanics of naming conventions for these particularconstraints has been reorganized such that the namingdetermination is done at DDL compile time, rather than atconstraint/table construction time.References: #3067

• [sql] [bug]

Fixed bug in common table expressions whereby positional boundparameters could be expressed in the wrong final orderwhen CTEs were nested in certain ways.References: #3090

• [sql] [bug]

Fixed bug where multi-valued Insert construct would failto check subsequent values entries beyond the first one givenfor literal SQL expressions.References: #3069

• [sql] [bug]

Added a “str()” step to the dialect_kwargs iteration forPython version < 2.6.5, working around the“no unicode keyword arg” bug as these args are passed along askeyword args within some reflection processes.References: #3123

• [sql] [bug]

The TypeEngine.with_variant() method will now accept atype class as an argument which is internally converted to aninstance, using the same convention long established by otherconstructs such as Column.References: #3122

postgresql

• [postgresql] [feature]

Added kw argument postgresql_regconfig to theColumnOperators.match() operator, allows the “reg config” argumentto be specified to the to_tsquery() function emitted.Pull request courtesy Jonathan Vanasco.References: #3078

• [postgresql] [feature]

Added support for PostgreSQL JSONB via JSONB. Pull requestcourtesy Damian Dimmich.

• [postgresql] [bug] [pg8000]

Fixed bug introduced in 0.9.5 by new pg8000 isolation level featurewhere engine-level isolation level parameter would raise an erroron connect.References: #3134

mysql

• [mysql] [bug]

MySQL error 2014 “commands out of sync” appears to be raised as aProgrammingError, not OperationalError, in modern MySQL-Python versions;all MySQL error codes that are tested for “is disconnect” are nowchecked within OperationalError and ProgrammingError regardless.This change is also backported to: 0.8.7

References: #3101

sqlite

• [sqlite] [bug]

Fixed a SQLite join rewriting issue where a subquery that is embeddedas a scalar subquery such as within an IN would receive inappropriatesubstitutions from the enclosing query, if the same table were presentinside the subquery as were in the enclosing query such as in ajoined inheritance scenario.References: #3130

mssql

• [mssql] [feature]

Enabled “multivalues insert” for SQL Server 2008. Pull requestcourtesy Albert Cervin. Also expanded the checks for “IDENTITY INSERT”mode to include when the identity key is present in theVALUEs clause of the statement.

• [mssql] [bug]

Added statement encoding to the “SET IDENTITY_INSERT”statements which operate when an explicit INSERT is beinginterjected into an IDENTITY column, to support non-ascii tableidentifiers on drivers such as pyodbc + unix + py2k that don’tsupport unicode statements.This change is also backported to: 0.8.7

• [mssql] [bug]

In the SQL Server pyodbc dialect, repaired the implementationfor the description_encoding dialect parameter, which whennot explicitly set was preventing cursor.description frombeing parsed correctly in the case of result sets thatcontained names in alternate encodings. This parametershouldn’t be needed going forward.This change is also backported to: 0.8.7

References: #3091

• [mssql] [bug]

Fixed a regression from 0.9.5 caused by #3025 where thequery used to determine “default schema” is invalid in SQL Server 2000.For SQL Server 2000 we go back to defaulting to the “schema name”parameter of the dialect, which is configurable but defaultsto ‘dbo’.References: #3025

oracle

• [oracle] [bug] [tests]

Fixed bug in oracle dialect test suite where in one test,‘username’ was assumed to be in the database URL, even thoughthis might not be the case.References: #3128

misc

• [bug] [tests]

Fixed bug where “python setup.py test” wasn’t calling intodistutils appropriately, and errors would be emitted at the endof the test suite.

• [bug] [declarative]

Fixed bug when the declarative abstract flag was not beingdistinguished for when it was actually the value False.The abstract flag needs to actually evaluate to a Truevalue at the level being tested.References: #3097

0.9.6

Released: June 23, 2014

orm

• [orm] [bug]

Reverted the change for #3060 - this is a unit of workfix that is updated more comprehensively in 1.0 via #3061.The fix in #3060 unfortunately produces a new issue wherebyan eager load of a many-to-one attribute can produce an eventthat is interpreted into an attribute change.References: #3060

0.9.5

Released: June 23, 2014

orm

• [orm] [feature]

The “primaryjoin” model has been stretched a bit further to allowa join condition that is strictly from a single column to itself,translated through some kind of SQL function or expression. Thisis kind of experimental, but the first proof of concept is a“materialized path” join condition where a path string is comparedto itself using “like”. The ColumnOperators.like() operator hasalso been added to the list of valid operators to use in a primaryjoincondition.References: #3029

• [orm] [feature]

Added new utility function make_transient_to_detached() which canbe used to manufacture objects that behave as though they were loadedfrom a session, then detached. Attributes that aren’t presentare marked as expired, and the object can be added to a Sessionwhere it will act like a persistent one.References: #3017

• [orm] [bug]

Fixed bug in subquery eager loading where a long chain ofeager loads across a polymorphic-subclass boundary in conjunctionwith polymorphic loading would fail to locate the subclass-link in thechain, erroring out with a missing property name on anAliasedClass.This change is also backported to: 0.8.7

References: #3055

• [orm] [bug]

Fixed ORM bug where the class_mapper() function would maskAttributeErrors or KeyErrors that should raise during mapperconfiguration due to user errors. The catch for attribute/keyerrorhas been made more specific to not include the configuration step.This change is also backported to: 0.8.7

References: #3047

• [orm] [bug]

Additional checks have been added for the case where an inheritingmapper is implicitly combining one of its column-based attributeswith that of the parent, where those columns normally don’t necessarilyshare the same value. This is an extension of an existing check thatwas added via #1892; however this new check emits only awarning, instead of an exception, to allow for applications that maybe relying upon the existing behavior.

See also

I’m getting a warning or error about “Implicitly combining column X under attribute Y”

References: #3042

• [orm] [bug]

Modified the behavior of orm.load_only() such that primary keycolumns 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, thathasn’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 thisdeferral.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, orin some cases a scalar attribute set to None, may not be detectedas a net change in value, and therefore the UPDATE would not resetwhat was on the previous row. This is due to some as-yetunresolved side effects of the way attribute history works in termsof implicitly assuming None isn’t really a “change” for a previouslyun-set attribute. See also #3061.

Note

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

References: #3060

• [orm] [bug]

Related to #3060, an adjustment has been made to the unitof work such that loading for related many-to-one objects is slightlymore aggressive, in the case of a graph of self-referential objectsthat are to be deleted; the load of related objects is to helpdetermine the correct order for deletion if passive_deletes isnot set.

• [orm] [bug]

Fixed bug in SQLite join rewriting where anonymized column namesdue to repeats would not correctly be rewritten in subqueries.This would affect SELECT queries with any kind of subquery + join.References: #3057

• [orm] [bug] [sql]

Fixes to the newly enhanced boolean coercion in #2804 wherethe 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 theORM either.References: #3013

engine

• [engine] [bug]

Fixed bug which would occur if a DBAPI exceptionoccurs when the engine first connects and does its initial checks,and the exception is not a disconnect exception, yet the cursorraises an error when we try to close it. In this case the realexception would be quashed as we tried to log the cursor closeexception via the connection pool and failed, as we were tryingto access the pool’s logger in a way that is inappropriatein this very specific scenario.References: #3063

• [engine] [bug]

Fixed some “double invalidate” situations were detected wherea connection invalidation could occur within an already critical sectionlike a connection.close(); ultimately, these conditions are causedby the change in #2907, in that the “reset on return” featurecalls out to the Connection/Transaction in order to handle it, where“disconnect detection” might be caught. However, it’s possible thatthe more recent change in #2985 made it more likely for thisto 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 thatan invalidate might occur to halt further disallowed operationson the invalidated connection. This includes two fixes both at theengine level and at the pool level. While the issue was observedwith highly concurrent gevent cases, it could in theory occur inany kind of scenario where a disconnect occurs within the connectionclose operation.References: #3043

sql

• [sql] [feature]

Liberalized the contract for Index a bit in that you canspecify a text() expression as the target; the index no longerneeds to have a table-bound column present if the index is to bemanually added to the table, either via inline declaration or viaTable.append_constraint().References: #3028

• [sql] [feature]

Added new flag expression.between.symmetric, when set to Truerenders “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 parenthesized NOTstring.References: #2990

• [sql] [bug]

Fixed bug in INSERT..FROM SELECT construct where selecting from aUNION would wrap the union in an anonymous (e.g. unlabeled) subquery.This change is also backported to: 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 isnow consistent with that of select().This change is also backported to: 0.8.7

References: #3045

• [sql] [bug]

The Column.nullable flag is implicitly set to Falsewhen that Column is referred to in an explicitPrimaryKeyConstraint for that table. This behavior nowmatches that of when the Column itself has theColumn.primary_key flag set to True, which isintended to be an exactly equivalent case.References: #3023

• [sql] [bug]

Fixed bug where the Operators.and(),Operators.or() and Operators.invert()operator overload methods could not be overridden within a customTypeEngine.Comparator implementation.References: #3012

• [sql] [bug]

Fixed bug in new DialectKWArgs.argument_for() method whereadding an argument for a construct not previously included for anyspecial arguments would fail.References: #3024

• [sql] [bug]

Fixed regression introduced in 0.9 where new “ORDER BY ”feature from #1068 would not apply quoting rules to thelabel name as rendered in the ORDER BY.References: #1068, #3020

• [sql] [bug]

Restored the import for Function to the sqlalchemy.sql.expressionimport namespace, which was removed at the beginning of 0.9.

postgresql

• [postgresql] [feature]

Added support for AUTOCOMMIT isolation level when using the pg8000DBAPI. Pull request courtesy Tony Locke.

• [postgresql] [feature]

Added a new flag ARRAY.zero_indexes to the PostgreSQLARRAY type. When set to True, a value of one will beadded to all array index values before passing to the database, allowingbetter interoperability between Python style zero-based indexes andPostgreSQL one-based indexes. Pull request courtesy Alexey Terentev.References: #2785

• [postgresql] [bug]

Added the hashable=False flag to the PG HSTORE type, whichis needed to allow the ORM to skip over trying to “hash” an ORM-mappedHSTORE column when requesting it in a mixed column/entity list.Patch courtesy Gunnlaugur Þór Briem.This change is also backported to: 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.8.7

• [postgresql] [bug]

The psycopg2 .closed accessor is now consulted when determiningif an exception is a “disconnect” error; ideally, this should removethe need for any other inspection of the exception message to detectdisconnect, however we will leave those existing messages in placeas a fallback. This should be able to handle newer cases like“SSL EOF” conditions. Pull request courtesy Dirk Mueller.References: #3021

• [postgresql] [enhancement]

Added a new type postgresql.OID to the PostgreSQL dialect.While “oid” is generally a private type within PG that is not exposedin modern versions, there are some PG use cases such as large objectsupport where these types might be exposed, as well as within someuser-reported schema reflection use cases.References: #3002

mysql

• [mysql] [bug]

Fixed bug where column names added to mysql_length parameteron an index needed to have the same quoting for quoted names inorder to be recognized. The fix makes the quotes optional butalso provides the old behavior for backwards compatibility with thoseusing the workaround.This change is also backported to: 0.8.7

References: #3085

• [mysql] [bug]

Added support for reflecting tables where an index includesKEY_BLOCK_SIZE using an equal sign. Pull request courtesySean McGivern.This change is also backported to: 0.8.7

mssql

• [mssql] [bug]

Revised the query used to determine the current default schema nameto use the database_principal_id() function in conjunction withthe sys.database_principals view so that we can determinethe default schema independently of the type of login in progress(e.g., SQL Server, Windows, etc).References: #3025

firebird

• [firebird] [bug]

Fixed bug where the combination of “limit” rendering as“SELECT FIRST n ROWS” using a bound parameter (only firebird has both),combined with column-level subquerieswhich also feature “limit” as well as “positional” bound parameters(e.g. qmark style) would erroneously assign the subquery-level positionsbefore that of the enclosing SELECT, thus returning parameters whichare out of order.References: #3038

misc

• [feature] [examples]

Added a new example illustrating materialized paths, using thelatest relationship features. Example courtesy Jack Zhou.

• [bug] [declarative]

The mapper_args dictionary is copied from a declarativemixin or abstract class when accessed, so that modifications madeto this dictionary by declarative itself won’t conflict with thatof other mappings. The dictionary is modified regarding theversion_id_col and polymorphic_on arguments, replacing thecolumn within with the one that is officially mapped to the localclass/table.This change is also backported to: 0.8.7

References: #3062

• [bug] [ext]

Fixed bug in mutable extension where MutableDict did notreport change events for the setdefault() dictionary operation.This change is also backported to: 0.8.7

References: #3051, #3093

• [bug] [ext]

Fixed bug where MutableDict.setdefault() didn’t return theexisting 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.8.7

References: #3051, #3093

• [bug] [testsuite]

In public test suite, changed to use of String(40) fromless-supported Text in StringTest.test_literal_backslashes.Pullreq courtesy Jan.

• [bug] [py3k] [tests]

Corrected for some deprecation warnings involving the impmodule and Python 3.3 or greater, when running tests. Pullrequest courtesy Matt Chisholm.References: #2830

0.9.4

Released: March 28, 2014

general

• [general] [feature]

Support has been added for pytest to run tests. This runneris currently being supported in addition to nose, and will likelybe preferred to nose going forward. The nose plugin system usedby SQLAlchemy has been split out so that it works under pytest aswell. There are no plans to drop support for nose at the momentand we hope that the test suite itself can continue to remain asagnostic of testing platform as possible. See the fileREADME.unittests.rst for updated information on running testswith pytest.

The test plugin system has also been enhanced to support runningtests against multiple database URLs at once, by specifying the —dband/or —dburi flags multiple times. This does not run the entire testsuite for each database, but instead allows test cases that are specificto certain backends make use of that backend as the test is run.When using pytest as the test runner, the system will also runspecific test suites multiple times, once for each database, particularlythose tests within the “dialect suite”. The plan is that the enhancedsystem will also be used by Alembic, and allow Alembic to runmigration operation tests against multiple backends in one run, includingthird-party backends not included within Alembic itself.Third party dialects and extensions are also encouraged to standardizeon SQLAlchemy’s test suite as a basis; see the file README.dialects.rstfor background on building out from SQLAlchemy’s test platform.

• [general] [bug]

Adjusted setup.py file to support the possible futureremoval of the setuptools.Feature extension from setuptools.If this keyword isn’t present, the setup will still succeedwith setuptools rather than falling back to distutils. C extensionbuilding can be disabled now also by setting theDISABLE_SQLALCHEMY_CEXT environment variable. This variable workswhether or not setuptools is even available.This change is also backported to: 0.8.6

References: #2986

• [general] [bug]

Fixed some test/feature failures occurring in Python 3.4,in particular the logic used to wrap “column default” callableswouldn’t work properly for Python built-ins.References: #2979

orm

• [orm] [feature]

Added new parameter orm.mapper.confirm_deleted_rows. Defaultsto True, indicates that a series of DELETE statements should confirmthat the cursor rowcount matches the number of primary keys that shouldhave matched; this behavior had been taken off in most cases(except when version_id is used) to support the unusual edge case ofself-referential ON DELETE CASCADE; to accommodate this, the messageis now just a warning, not an exception, and the flag can be usedto indicate a mapping that expects self-referential cascadeddeletes of this nature. See also #2403 for background on theoriginal change.References: #3007

• [orm] [feature]

A warning is emitted if the MapperEvents.before_configured()or MapperEvents.after_configured() events are applied to aspecific mapper or mapped class, as the events are only invokedfor the Mapper target at the general level.

• [orm] [feature]

Added a new keyword argument once=True to event.listen()and event.listens_for(). This is a convenience feature whichwill wrap the given listener such that it is only invoked once.

• [orm] [feature]

Added a new option to relationship.innerjoin which isto specify the string "nested". When set to "nested" as opposedto True, the “chaining” of joins will parenthesize the inner join on theright side of an existing outer join, instead of chaining as a stringof outer joins. This possibly should have been the default behaviorwhen 0.9 was released, as we introduced the feature of right-nestedjoins in the ORM, however we are keeping it as a non-default for nowto avoid further surprises.

See also

Right-nested inner joins available in joined eager loads

References: #2976

• [orm] [bug]

Fixed ORM bug where changing the primary key of an object, then markingit for DELETE would fail to target the correct row for DELETE.This change is also backported to: 0.8.6

References: #3006

• [orm] [bug]

Fixed regression from 0.8.3 as a result of #2818where Query.exists() wouldn’t work on a query that onlyhad a Query.select_from() entry but no other entities.This change is also backported to: 0.8.6

References: #2995

• [orm] [bug]

Improved an error message which would occur if a query() were madeagainst a non-selectable, such as a literal_column(), and thenan attempt was made to use Query.join() such that the “left”side would be determined as None and then fail. This conditionis now detected explicitly.This change is also backported to: 0.8.6

• [orm] [bug]

Removed stale names from sqlalchemy.orm.interfaces.all andrefreshed with current names, so that an import from thismodule again works.This change is also *backported to: 0.8.6

References: #2975

• [orm] [bug]

Fixed a very old behavior where the lazy load emitted for a one-to-manycould inappropriately pull in the parent table, and also return resultsinconsistent based on what’s in the parent table, when the primaryjoinincludes some kind of discriminator against the parent table, suchas and_(parent.id == child.parent_id, parent.deleted == False).While this primaryjoin doesn’t make that much sense for a one-to-many,it is slightly more common when applied to the many-to-one side, andthe one-to-many comes as a result of a backref.Loading rows from child in this case would keep parent.deleted == Falseas is within the query, thereby yanking it into the FROM clauseand doing a cartesian product. The new behavior will now substitutethe value of the local “parent.deleted” for that parameter as isappropriate. Though typically, a real-world app probably wants to use adifferent primaryjoin for the o2m side in any case.References: #2948

• [orm] [bug]

Improved the check for “how to join from A to B” such that whena table has multiple, composite foreign keys targeting a parent table,the relationship.foreign_keys argument will be properlyinterpreted in order to resolve the ambiguity; previously this conditionwould raise that there were multiple FK paths when in fact theforeign_keys argument should be establishing which one is expected.References: #2965

• [orm] [bug]

Added support for the not-quite-yet-documented insert=Trueflag for event.listen() to work with mapper / instance events.

• [orm] [bug] [engine]

Fixed bug where events set to listen at the classlevel (e.g. on the Mapper or ClassManagerlevel, as opposed to on an individual mapped class, and also onConnection) that also made use of internal argument conversion(which is most within those categories) would fail to be removable.References: #2973

• [orm] [bug]

Fixed regression from 0.8 where using an option likeorm.lazyload() with the “wildcard” expression, e.g. "*",would raise an assertion error in the case where the query didn’tcontain any actual entities. This assertion is meant for other casesand was catching this one inadvertently.

• [orm] [bug] [sqlite]

More fixes to SQLite “join rewriting”; the fix from #2967implemented right before the release of 0.9.3 affected the case wherea UNION contained nested joins in it. “Join rewriting” is a featurewith a wide range of possibilities and is the first intricate“SQL rewriting” feature we’ve introduced in years, so we’re sort ofgoing through a lot of iterations with it (not unlike eager loadingback in the 0.2/0.3 series, polymorphic loading in 0.4/0.5). We shouldbe there soon so thanks for bearing with us :).References: #2969

engine

• [engine] [feature]

Added some new event mechanics for dialect-level events; the initialimplementation allows an event handler to redefine the specific mechanicsby which an arbitrary dialect invokes execute() or executemany() on aDBAPI cursor. The new events, at this point semi-public and experimental,are in support of some upcoming transaction-related extensions.

• [engine] [feature]

An event listener can now be associated with a Engine,after one or more Connection objects have been created(such as by an orm Session or via explicit connect)and the listener will pick up events from those connections.Previously, performance concerns pushed the event transfer fromEngine to Connection at init-time only, butwe’ve inlined a bunch of conditional checks to make this possiblewithout any additional function calls.References: #2978

• [engine] [bug]

A major improvement made to the mechanics by which the Enginerecycles the connection pool when a “disconnect” condition is detected;instead of discarding the pool and explicitly closing out connections,the pool is retained and a “generational” timestamp is updated toreflect the current time, thereby causing all existing connectionsto be recycled when they are next checked out. This greatly simplifiesthe recycle process, removes the need for “waking up” connect attemptswaiting on the old pool and eliminates the race condition that manyimmediately-discarded “pool” objects could be created during therecycle operation.References: #2985

• [engine] [bug]

The ConnectionEvents.after_cursor_execute() event is nowemitted for the “_cursor_execute()” method of Connection;this is the “quick” executor that is used for things likewhen a sequence is executed ahead of an INSERT statement, as well asfor dialect startup checks like unicode returns, charset, etc.the ConnectionEvents.before_cursor_execute() event was alreadyinvoked here. The “executemany” flag is now always set to Falsehere, as this event always corresponds to a single execution.Previously the flag could be True if we were acting on behalf ofan executemany INSERT statement.

sql

• [sql] [feature]

Added support for literal rendering of boolean values, e.g.“true” / “false” or “1” / “0”.

• [sql] [feature]

Added a new feature schema.conv(), the purpose of which is tomark a constraint name as already having had a naming convention applied.This token will be used by Alembic migrations as of Alembic 0.6.4in order to render constraints in migration scripts with names markedas already having been subject to a naming convention.

• [sql] [feature]

The new dialect-level keyword argument system for schema-levelconstructs has been enhanced in order to assist with existingschemes that rely upon addition of ad-hoc keyword arguments toconstructs.

E.g., a construct such as Index will again acceptad-hoc keyword arguments within the Index.kwargs collection,after construction:

idx = Index('a', 'b')
idx.kwargs['mysql_someargument'] = True

To suit the use case of allowing custom arguments at construction time,the DialectKWArgs.argument_for() method now allows this registration:

Index.argument_for('mysql', 'someargument', False)
 
idx = Index('a', 'b', mysql_someargument=True)

See also

DialectKWArgs.argument_for()

References: #2866, #2962

• [sql] [bug]

Fixed bug in tuple_() construct where the “type” of essentiallythe first SQL expression would be applied as the “comparison type”to a compared tuple value; this has the effect in some cases of aninappropriate “type coercion” occurring, such as when a tuple thathas a mix of String and Binary values improperly coerces targetvalues to Binary even though that’s not what they are on the leftside. tuple_() now expects heterogeneous types within itslist of values.This change is also backported to: 0.8.6

References: #2977

• [sql] [bug]

Fixed an 0.9 regression where a Table that failed toreflect correctly wouldn’t be removed from the parentMetaData, even though in an invalid state. Pullreqcourtesy Roman Podoliaka.References: #2988

• [sql] [bug]

MetaData.naming_convention feature will now alsoapply to CheckConstraint objects that are associateddirectly with a Column instead of just on theTable.

• [sql] [bug]

Fixed bug in new MetaData.naming_convention featurewhere the name of a check constraint making use of the“%(constraint_name)s” token would get doubled up for theconstraint generated by a boolean or enum type, and overallduplicate events would cause the “%(constraint_name)s” tokento keep compounding itself.References: #2991

• [sql] [bug]

Adjusted the logic which applies names to the .c collection whena no-name BindParameter is received, e.g. via sql.literal()or similar; the “key” of the bind param is used as the key within.c. rather than the rendered name. Since these binds have “anonymous”names in any case, this allows individual bound parameters tohave their own name within a selectable if they are otherwise unlabeled.References: #2974

• [sql] [bug]

Some changes to how the FromClause.c collection behaveswhen presented with duplicate columns. The behavior of emitting awarning and replacing the old column with the same name stillremains to some degree; the replacement in particular is to maintainbackwards compatibility. However, the replaced column still remainsassociated with the c collection now in a collection ._all_columns,which is used by constructs such as aliases and unions, to deal withthe set of columns in c more towards what is actually in thelist of columns rather than the unique set of key names. This helpswith situations where SELECT statements with same-named columnsare used in unions and such, so that the union can match the columnsup positionally and also there’s some chance of FromClause.corresponding_column()still being usable here (it can now return a column that is onlyin selectable.c._all_columns and not otherwise named).The new collection is underscored as we still need to decide where thislist might end up. Theoretically itwould become the result of iter(selectable.c), however this would meanthat the length of the iteration would no longer match the length ofkeys(), and that behavior needs to be checked out.References: #2974

• [sql] [bug]

Fixed issue in new TextClause.columns() method where the orderingof columns given positionally would not be preserved. This couldhave potential impact in positional situations such as applying theresulting TextAsFrom object to a union.

postgresql

• [postgresql] [feature]

Enabled “sane multi-row count” checking for the psycopg2 DBAPI, asthis seems to be supported as of psycopg2 2.0.9.This change is also backported to: 0.8.6

• [postgresql] [bug]

Fixed regression caused by release 0.8.5 / 0.9.3’s compatibilityenhancements where index reflection on PostgreSQL versions specificto only the 8.1, 8.2 series againbroke, surrounding the ever problematic int2vector type. Whileint2vector supports array operations as of 8.1, apparently it onlysupports CAST to a varchar as of 8.3.This change is also backported to: 0.8.6

References: #3000

mysql

• [mysql] [bug]

Tweaked the settings for mysql-connector-python; in Py2K, the“supports unicode statements” flag is now False, so that SQLAlchemywill encode the SQL string (note: not the parameters)to bytes before sending to the database. This seems to allowall unicode-related tests to pass for mysql-connector, including thosethat use non-ascii table/column names, as well as some tests for theTEXT type using unicode under cursor.executemany().

oracle

• [oracle] [feature]

Added a new engine option coerce_to_unicode=True to thecx_Oracle dialect, which restores the cx_Oracle outputtypehandlerapproach to Python unicode conversion under Python 2, which wasremoved in 0.9.2 as a result of #2911. Some use cases wouldprefer that unicode coercion is unconditional for all string values,despite performance concerns. Pull request courtesyChristoph Zwerschke.References: #2911

• [oracle] [bug]

Added new datatype oracle.DATE, which is a subclass ofDateTime. As Oracle has no “datetime” type per se,it instead has only DATE, it is appropriate here that theDATE type as present in the Oracle dialect be an instance ofDateTime. This issue doesn’t change anything as far asthe behavior of the type, as data conversion is handled by theDBAPI in any case, however the improved subclass layout will helpthe use cases of inspecting types for cross-database compatibility.Also removed uppercase DATETIME from the Oracle dialect as thistype isn’t functional in that context.References: #2987

misc

• [bug] [ext]

Fixed bug in mutable extension as well asattributes.flag_modified() where the change event would not bepropagated if the attribute had been reassigned to itself.This change is also backported to: 0.8.6

References: #2997

• [bug] [automap] [ext]

Added support to automap for the case where a relationship shouldnot be created between two classes that are in a joined inheritancerelationship, for those foreign keys that link the subclass back tothe superclass.References: #3004

• [bug] [tests]

Fixed a few errant u'' strings that would prevent tests from passingin Py3.2. Patch courtesy Arfrever Frehtes Taifersar Arahesis.References: #2980

• [bug] [pool]

Fixed small issue in SingletonThreadPool where the currentconnection to be returned might get inadvertently cleaned out duringthe “cleanup” process. Patch courtesy jd23.

• [bug] [ext] [py3k]

Fixed bug in association proxy where assigning an empty slice(e.g. x[:] = […]) would fail on Py3k.

• [bug] [ext]

Fixed a regression in association proxy caused by #2810 whichcaused a user-provided “getter” to no longer receive values of Nonewhen fetching scalar values from a target that is non-present. Thecheck for None introduced by this change is now moved into the defaultgetter, so a user-provided getter will also again receive values ofNone.References: #2810

• [bug] [examples]

Fixed bug in the versioned_history example where column-level INSERTdefaults would prevent history values of NULL from being written.

0.9.3

Released: February 19, 2014

orm

• [orm] [feature]

Added new MapperEvents.before_configured() event which allowsan event at the start of configure_mappers(), as wellas declare_first() hook within declarative to complementdeclare_last().

• [orm] [bug]

Fixed bug where Query.get() would fail to consistentlyraise the InvalidRequestError that invokes when calledon a query with existing criterion, when the given identity isalready present in the identity map.This change is also backported to: 0.8.5

References: #2951

• [orm] [bug] [sqlite]

Fixed bug in SQLite “join rewriting” where usage of an exists() constructwould fail to be rewritten properly, such as when the exists ismapped to a column_property in an intricate nested-join scenario.Also fixed a somewhat related issue where join rewriting would failon the columns clause of the SELECT statement if the targets werealiased tables, as opposed to individual aliased columns.References: #2967

• [orm] [bug]

Fixed an 0.9 regression where ORM instance or mapper events appliedto a base class such as a declarative base with the propagate=Trueflag would fail to apply to existing mapped classes which alsoused inheritance due to an assertion. Additionally, repaired anattribute error which could occur during removal of such an event,depending on how it was first assigned.References: #2949

• [orm] [bug]

Improved the initialization logic of composite attributes such thatcalling MyClass.attribute will not require that the configuremappers step has occurred, e.g. it will just work without throwingany error.References: #2935

• [orm] [bug]

More issues with [ticket:2932] first resolved in 0.9.2 whereusing a column key of the form <tablename>_<columnname>matching that of an aliased column in the text would still notmatch at the ORM level, which is ultimately due to a corecolumn-matching issue. Additional rules have been added so that thecolumn _label is taken into account when working with aTextAsFrom construct or with literal columns.References: #2932

orm declarative

• [bug] [declarative] [orm]

Fixed bug where AbstractConcreteBase would fail to befully usable within declarative relationship configuration, as itsstring classname would not be available in the registry of classnamesat mapper configuration time. The class now explicitly adds itselfto the class registry, and additionally both AbstractConcreteBaseas well as ConcreteBase set themselves up before mappersare configured within the configure_mappers() setup, usingthe new MapperEvents.before_configured() event.References: #2950

engine

• [engine] [bug] [pool]

Fixed a critical regression caused by #2880 where the newlyconcurrent ability to return connections from the pool means that the“first_connect” event is now no longer synchronized either, thus leadingto dialect mis-configurations under even minimal concurrency situations.This change is also backported to: 0.8.5

References: #2880, #2964

sql

• [sql] [bug]

Fixed bug where calling Insert.values() with an empty listor tuple would raise an IndexError. It now produces an emptyinsert construct as would be the case with an empty dictionary.This change is also backported to: 0.8.5

References: #2944

• [sql] [bug]

Fixed bug where in_() would go into an endless loop iferroneously passed a column expression whose comparator includedthe getitem() method, such as a column that uses thepostgresql.ARRAY type.This change is also backported to: 0.8.5

References: #2957

• [sql] [bug]

Fixed regression in new “naming convention” feature where conventionswould fail if the referred table in a foreign key contained a schemaname. Pull request courtesy Thomas Farvour.

• [sql] [bug]

Fixed bug where so-called “literal render” of bindparam()constructs would fail if the bind were constructed with a callable,rather than a direct value. This prevented ORM expressionsfrom being rendered with the “literal_binds” compiler flag.

postgresql

• [postgresql] [feature]

Added the TypeEngine.python_type convenience accessor onto thepostgresql.ARRAY type. Pull request courtesy Alexey Terentev.

• [postgresql] [bug]

Added an additional message to psycopg2 disconnect detection,“could not send data to server”, which complements the existing“could not receive data from server” and has been observed by users.This change is also backported to: 0.8.5

References: #2936

• [postgresql] [bug]

Support has been improved for PostgreSQL reflection behavior on very old(pre 8.1) versions of PostgreSQL, and potentially other PG enginessuch as Redshift (assuming Redshift reports the version as < 8.1).The query for “indexes” as well as “primary keys” relies upon inspectinga so-called “int2vector” datatype, which refuses to coerce to an arrayprior to 8.1 causing failures regarding the “ANY()” operator usedin the query. Extensive googling has located the very hacky, butrecommended-by-PG-core-developer query to use when PG version < 8.1is in use, so index and primary key constraint reflection now workon these versions.

This change is also backported to: 0.8.5

• [postgresql] [bug]

Revised this very old issue where the PostgreSQL “get primary key”reflection query were updated to take into account primary key constraintsthat were renamed; the newer query fails on very old versions ofPostgreSQL such as version 7, so the old query is restored in those caseswhen server_version_info < (8, 0) is detected.This change is also backported to: 0.8.5

References: #2291

• [postgresql] [bug]

Added server version detection to the newly added dialect startupquery for “show standard_conforming_strings”; as this variable wasadded as of PG 8.2, we skip the query for PG versions who report aversion string earlier than that.References: #2946

mysql

• [mysql] [feature]

Added new MySQL-specific mysql.DATETIME which includesfractional seconds support; also added fractional seconds supportto mysql.TIMESTAMP. DBAPI support is limited, thoughfractional seconds are known to be supported by MySQL Connector/Python.Patch courtesy Geert JM Vanderkelen.This change is also backported to: 0.8.5

References: #2941

• [mysql] [bug]

Added support for the PARTITION BY and PARTITIONSMySQL table keywords, specified as mysql_partition_by='value' andmysql_partitions='value' to Table. Pull requestcourtesy Marcus McCurdy.This change is also backported to: 0.8.5

References: #2966

• [mysql] [bug]

Fixed bug which prevented MySQLdb-based dialects (e.g.pymysql) from working in Py3K, where a check for “connectioncharset” would fail due to Py3K’s more strict value comparisonrules. The call in question wasn’t taking the databaseversion into account in any case as the server version wasstill None at that point, so the method overall has beensimplified to rely upon connection.character_set_name().This change is also backported to: 0.8.5

References: #2933

• [mysql] [bug] [cymysql]

Fixed bug in cymysql dialect where a version string such as'33a-MariaDB' would fail to parse properly. Pull requestcourtesy Matt Schmidt.References: #2934

sqlite

• [sqlite] [bug]

The SQLite dialect will now skip unsupported arguments when reflectingtypes; such as if it encounters a string like INTEGER(5), theINTEGER type will be instantiated without the “5” being included,based on detecting a TypeError on the first attempt.

• [sqlite] [bug]

Support has been added to SQLite type reflection to fully supportthe “type affinity” contract specified at http://www.sqlite.org/datatype3.html.In this scheme, keywords like INT, CHAR, BLOB orREAL located in the type name generically associate the type withone of five affinities. Pull request courtesy Erich Blume.

See also

Type Reflection

misc

• [feature] [examples]

Added optional “changed” column to the versioned rows example, as wellas support for when the versioned Table has an explicitschema argument. Pull requestcourtesy jplaverdure.

• [bug] [ext]

Fixed bug where the AutomapBase class of thenew automap extension would fail if classeswere pre-arranged in single or potentially joined inheritance patterns.The repaired joined inheritance issue could also potentially apply whenusing DeferredReflection as well.

0.9.2

Released: February 2, 2014

orm

• [orm] [feature]

Added a new parameter Operators.op.is_comparison. Thisflag allows a custom op from Operators.op() to be consideredas a “comparison” operator, thus usable for customrelationship.primaryjoin conditions.

See also

Using custom operators in join conditions

• [orm] [feature]

Support is improved for supplying a join() construct as thetarget of relationship.secondary for the purposesof creating very complex relationship() join conditions.The change includes adjustments to query joining, joined eager loadingto not render a SELECT subquery, changes to lazy loading such thatthe “secondary” target is properly included in the SELECT, andchanges to declarative to better support specification of ajoin() object with classes as targets.

The new use case is somewhat experimental, but a new documentation sectionhas been added.

See also

Composite “Secondary” Joins

• [orm] [bug]

Fixed error message when an iterator object is passed toclass_mapper() or similar, where the error would fail torender on string formatting. Pullreq courtesy Kyle Stark.This change is also backported to: 0.8.5

• [orm] [bug]

Fixed bug in new TextAsFrom construct where Column-oriented row lookups were not matching up to the ad-hoc ColumnClauseobjects that TextAsFrom generates, thereby making it notusable as a target in Query.from_statement(). Also fixedQuery.from_statement() mechanics to not mistake a TextAsFromfor a Select construct. This bug is also an 0.9 regressionas the Text.columns() method is called to accommodate thetext.typemap argument.References: #2932

• [orm] [bug]

Added a new directive used within the scope of an attribute “set” operationto disable autoflush, in the case that the attribute needs to lazy-loadthe “old” value, as in when replacing one-to-one values or somekinds of many-to-one. A flush at this point otherwise occursat the point that the attribute is None and can cause NULL violations.References: #2921

• [orm] [bug]

Fixed an 0.9 regression where the automatic aliasing applied byQuery and in other situations where selects or joinswere aliased (such as joined table inheritance) could fail if auser-defined Column subclass were used in the expression.In this case, the subclass would fail to propagate ORM-specific“annotations” along needed by the adaptation. The “expressionannotations” system has been corrected to account for this case.References: #2918

• [orm] [bug]

Fixed a bug involving the new flattened JOIN structures whichare used with joinedload() (thereby causing a regressionin joined eager loading) as well as aliased()in conjunction with the flat=True flag and joined-table inheritance;basically multiple joins across a “parent JOIN sub” entity using differentpaths to get to a target class wouldn’t form the correct ON conditions.An adjustment / simplification made in the mechanics of figuringout the “left side” of the join in the case of an aliased, joined-inhclass repairs the issue.References: #2908

engine

• [engine] [feature] [pool]

Added a new pool event PoolEvents.invalidate(). Called whena DBAPI connection is to be marked as “invalidated” and discardedfrom the pool.

sql

• [sql] [feature]

Added MetaData.reflect.**dialect_kwargsto support dialect-level reflection options for all Tableobjects reflected.

• [sql] [feature]

Added a new feature which allows automated naming conventions to beapplied to Constraint and Index objects. Basedon a recipe in the wiki, the new feature uses schema-events to set upnames as various schema objects are associated with each other. Theevents then expose a configuration system through a new argumentMetaData.naming_convention. This system allows productionof both simple and custom naming schemes for constraints and indexeson a per-MetaData basis.

See also

Configuring Constraint Naming Conventions

References: #2923

• [sql] [feature]

Options can now be specified on a PrimaryKeyConstraint objectindependently of the specification of columns in the table withthe primary_key=True flag; use a PrimaryKeyConstraintobject with no columns in it to achieve this result.

Previously, an explicit PrimaryKeyConstraint would have theeffect of those columns marked as primary_key=True being ignored;since this is no longer the case, the PrimaryKeyConstraintwill now assert that either one style or the other is used to specifythe columns, or if both are present, that the column lists matchexactly. If an inconsistent set of columns in thePrimaryKeyConstraintand within the Table marked as primary_key=True arepresent, a warning is emitted, and the list of columns is takenonly from the PrimaryKeyConstraint alone as was the casein previous releases.

See also

PrimaryKeyConstraint

References: #2910

• [sql] [feature]

The system by which schema constructs and certain SQL constructsaccept dialect-specific keyword arguments has been enhanced. Thissystem includes commonly the Table and Index constructs,which accept a wide variety of dialect-specific arguments such asmysql_engine and postgresql_where, as well as the constructsPrimaryKeyConstraint, UniqueConstraint,Update, Insert and Delete, and alsonewly added kwarg capability to ForeignKeyConstraintand ForeignKey. The change is that participating dialectscan now specify acceptable argument lists for these constructs, allowingan argument error to be raised if an invalid keyword is specified fora particular dialect. If the dialect portion of the keyword is unrecognized,a warning is emitted only; while the system will actually make useof setuptools entrypoints in order to locate non-local dialects,the use case where certain dialect-specific arguments are usedin an environment where that third-party dialect is uninstalled remainssupported. Dialects also have to explicitly opt-in to this system,so that external dialects which aren’t making use of this systemwill remain unaffected.References: #2866

• [sql] [bug]

The behavior of Table.tometadata() has been adjusted such thatthe schema target of a ForeignKey will not be changed unlessthat schema matches that of the parent table. That is, ifa table “schema_a.user” has a foreign key to “schema_b.order.id”,the “schema_b” target will be maintained whether or not the“schema” argument is passed to Table.tometadata(). Howeverif a table “schema_a.user” refers to “schema_a.order.id”, the presenceof “schema_a” will be updated on both the parent and referred tables.This is a behavioral change hence isn’t likely to be backported to0.8; it is assumed that the previous behavior is pretty buggyhowever and that it’s unlikely anyone was relying upon it.

Additionally, a new parameter has been addedTable.tometadata.referred_schema_fn. This refers to acallable function which will be used to determine the new referredschema for any ForeignKeyConstraint encountered in thetometadata operation. This callable can be used to revert to theprevious behavior or to customize how referred schemas are treatedon a per-constraint basis.References: #2913

• [sql] [bug]

Fixed bug whereby binary type would fail in some casesif used with a “test” dialect, such as a DefaultDialect or otherdialect with no DBAPI.

• [sql] [bug] [py3k]

Fixed bug where “literal binds” wouldn’t work with a bound parameterthat’s a binary type. A similar, but different, issue is fixedin 0.8.

• [sql] [bug]

Fixed regression whereby the “annotation” system used by the ORM was leakinginto the names used by standard functions in sqlalchemy.sql.functions,such as func.coalesce() and func.max(). Using these functionsin ORM attributes and thus producing annotated versions of them couldcorrupt the actual function name rendered in the SQL.References: #2927

• [sql] [bug]

Fixed 0.9 regression where the new sortable support for RowProxywould lead to TypeError when compared to non-tuple types as it attemptedto apply tuple() to the “other” object unconditionally. Thefull range of Python comparison operators have now been implemented onRowProxy, using an approach that guarantees a comparisonsystem that is equivalent to that of a tuple, and the “other” objectis only coerced if it’s an instance of RowProxy.References: #2848, #2924

• [sql] [bug]

A UniqueConstraint created inline with a Tablethat has no columns within it will be skipped. Pullreq courtesyDerek Harland.

• [sql] [bug] [orm]

Fixed the multiple-table “UPDATE..FROM” construct, only usable onMySQL, to correctly render the SET clause among multiple columnswith the same name across tables. This also changes the name used forthe bound parameter in the SET clause to “_” forthe non-primary table only; as this parameter is typically specifiedusing the Column object directly this should not have animpact on applications. The fix takes effect for bothTable.update() as well as Query.update() in the ORM.References: #2912

schema

• [schema] [bug]

Restored sqlalchemy.schema.SchemaVisitor to the .schemamodule. Pullreq courtesy Sean Dague.

postgresql

• [postgresql] [feature]

Added a new dialect-level argument postgresql_ignore_search_path;this argument is accepted by both the Table constructoras well as by the MetaData.reflect() method. When in useagainst PostgreSQL, a foreign-key referenced table which specifiesa remote schema name will retain that schema name even if the nameis present in the search_path; the default behavior since 0.7.3has been that schemas present in search_path would not be copiedto reflected ForeignKey objects. The documentation has beenupdated to describe in detail the behavior of the pg_get_constraintdef()function and how the postgresql_ignore_search_path feature essentiallydetermines if we will honor the schema qualification reported bythis function or not.

See also

Remote-Schema Table Introspection and PostgreSQL search_path

References: #2922

mysql

• [mysql] [bug]

Some missing methods added to the cymysql dialect, including_get_server_version_info() and _detect_charset(). Pullreqcourtesy Hajime Nakagami.This change is also backported to: 0.8.5

• [mysql] [bug] [sql]

Added new test coverage for so-called “down adaptions” of SQL types,where a more specific type is adapted to a more generic one - thisuse case is needed by some third party tools such as sqlacodegen.The specific cases that needed repair within this test suite were thatof mysql.ENUM being downcast into a types.Enum,and that of SQLite date types being cast into generic date types.The adapt() method needed to become more specific here to counteractthe removal of a “catch all” **kwargs collection on the baseTypeEngine class that was removed in 0.9.References: #2917

• [mysql] [bug]

The MySQL CAST compilation now takes into account aspects of a stringtype such as “charset” and “collation”. While MySQL wants all character-based CAST calls to use the CHAR type, we now create a real CHARobject at CAST time and copy over all the parameters it has, so thatan expression like cast(x, mysql.TEXT(charset='utf8')) willrender CAST(t.col AS CHAR CHARACTER SET utf8).

• [mysql] [bug]

Added new “unicode returns” detection to the MySQL dialect andto the default dialect system overall, such that any dialectcan add extra “tests” to the on-first-connect “does this DBAPIreturn unicode directly?” detection. In this case, we areadding a check specifically against the “utf8” encoding withan explicit “utf8_bin” collation type (after checking thatthis collation is available) to test for some buggy unicodebehavior observed with MySQLdb version 1.2.3. While MySQLdbhas resolved this issue as of 1.2.4, the check here shouldguard against regressions. The change also allows the “unicode”checks to log in the engine logs, which was not previouslythe case.References: #2906

• [mysql] [bug] [engine] [pool]

Connection now associates a newRootTransaction or TwoPhaseTransactionwith its immediate _ConnectionFairy as a “reset handler”for the span of that transaction, which takes over the taskof calling commit() or rollback() for the “reset on return” behaviorof Pool if the transaction was not otherwise completed.This resolves the issue that a picky transactionlike that of MySQL two-phase will beproperly closed out when the connection is closed without anexplicit rollback or commit (e.g. no longer raises “XAER_RMFAIL”in this case - note this only shows up in logging as the exceptionis not propagated within pool reset).This issue would arise e.g. when using an ormSession with twophase set, and thenSession.close() is called without an explicit rollback orcommit. The change also has the effect that you will now seean explicit “ROLLBACK” in the logs when using a Sessionobject in non-autocommit mode regardless of how that session wasdiscarded. Thanks to Jeff Dairiki and Laurence Rowe for isolatingthe issue here.References: #2907

sqlite

• [sqlite] [bug]

Fixed bug whereby SQLite compiler failed to propagate compiler argumentssuch as “literal binds” into a CAST expression.

mssql

• [mssql] [feature]

Added an option mssql_clustered to the UniqueConstraintand PrimaryKeyConstraint constructs; on SQL Server, this addsthe CLUSTERED keyword to the constraint construct within DDL.Pullreq courtesy Derek Harland.

oracle

• [oracle] [bug]

It’s been observed that the usage of a cxOracle “outputtypehandler”in Python 2.xx in order to coerce string values to Unicode is inordinatelyexpensive; even though cx_Oracle is written in C, when you pass thePython unicode primitive to cursor.var() and associate with an outputhandler, the library counts every conversion as a Python function callwith all the requisite overhead being recorded; this _despite the factwhen running in Python 3, all strings are also unconditionally coercedto unicode but it does not incur this overhead,meaning that cx_Oracle is failing to use performant techniques in Py2K.As SQLAlchemy cannot easily select for this style of type handler on aper-column basis, the handler was assembled unconditionally therebyadding the overhead to all string access.

So this logic has been replaced with SQLAlchemy’s own unicodeconversion system, which nowonly takes effect in Py2K for columns that are requested as unicode.When C extensions are used, SQLAlchemy’s system appears to be 2-3x faster thancx_Oracle’s. Additionally, SQLAlchemy’s unicode conversion has beenenhanced such that when the “conditional” converter is required(now needed for the Oracle backend), the check for “already unicode” is nowperformed in C and no longer introduces significant overhead.

This change has two impacts on the cxOracle backend. One is thatstring values in Py2K which aren’t specifically requested with theUnicode type or convert_unicode=True will now come back as str,not unicode - this behavior is similar to a backend such asMySQL. Additionally, when unicode values are requested with the cx_Oraclebackend, if the C extensions are _not used, there is now an additionaloverhead of an isinstance() check per column. This tradeoff has beenmade as it can be worked around and no longer places a performance burdenon the likely majority of Oracle result columns that are non-unicodestrings.References: #2911

misc

• [bug] [examples]

Added a tweak to the “history_meta” example where the check for“history” on a relationship-bound attribute will now no longer emitany SQL if the relationship is unloaded.

• [bug] [pool]

The argument names for the PoolEvents.reset() event have beenrenamed to dbapi_connection and connection_record in orderto maintain consistency with all the other pool events. It is expectedthat any existing listeners for this relatively new andseldom-used event are using positional style to receive arguments inany case.

• [bug] [cextensions] [py3k]

Fixed an issue where the C extensions in Py3K are using the wrong APIto specify the top-level module function, which breaksin Python 3.4b2. Py3.4b2 changes PyMODINIT_FUNC to return“void” instead of PyObject , so we now make sure to use“PyMODINIT_FUNC” instead of PyObject directly. Pull requestcourtesy cgohlke.

0.9.1

Released: January 5, 2014

orm

• [orm] [feature] [extensions]

A new, experimental extension sqlalchemy.ext.automap is added.This extension expands upon the functionality of Declarative as well asthe DeferredReflection class to produce a base class whichautomatically generates mapped classes and relationships based ontable metadata.

See also

Automap Extension

Automap

• [orm] [bug] [events]

Fixed regression where using a functools.partial() with the eventsystem would cause a recursion overflow due to usage of inspect.getargspec()on it in order to detect a legacy calling signature for certain events,and apparently there’s no way to do this with a partial object. Insteadwe skip the legacy check and assume the modern style; the check itselfnow only occurs for the SessionEvents.after_bulk_update andSessionEvents.after_bulk_delete events. Those two events will requirethe new signature style if assigned to a “partial” event listener.References: #2905

• [orm] [bug]

Fixed bug where using new Session.info attribute would failif the .info argument were only passed to the sessionmakercreation call but not to the object itself. Courtesy Robin Schoonover.

• [orm] [bug]

Fixed regression where we don’t check the given name against thecorrect string class when setting up a backref based on a name,therefore causing the error “too many values to unpack”. This wasrelated to the Py3k conversion.References: #2901

• [orm] [bug]

Fixed regression where we apparently still create an implicitalias when saying query(B).join(B.cs), where “C” is a joined inhclass; however, this implicit alias was created only consideringthe immediate left side, and not a longer chain of joins along differentjoined-inh subclasses of the same base. As long as we’re stillimplicitly aliasing in this case, the behavior is dialed back a bitso that it will alias the right side in a wider variety of cases.References: #2903

orm declarative

• [bug] [declarative] [orm]

Fixed an extremely unlikely memory issue where when usingDeferredReflectionto define classes pending for reflection, if some subset of thoseclasses were discarded before the DeferredReflection.prepare()method were called to reflect and map the class, a strong referenceto the class would remain held within the declarative internals.This internal collection of “classes to map” now uses weakreferences against the classes themselves.

• [bug] [declarative] [orm]

A quasi-regression where apparently in 0.8 you can set a class-levelattribute on declarative to simply refer directly to an InstrumentedAttributeon a superclass or on the class itself, and itacts more or less like a synonym; in 0.9, this fails to set up enoughbookkeeping to keep up with the more liberalized backref logicfrom #2789. Even though this use case was never directlyconsidered, it is now detected by declarative at the “setattr()” levelas well as when setting up a subclass, and the mirrored/renamed attributeis now set up as a synonym() instead.References: #2900

sql

• [sql] [feature]

Conjunctions like and_() and or_() can now acceptPython generators as a single argument, e.g.:

and_(x == y for x, y in tuples)

The logic here looks for a single argument *args where the firstelement is an instance of types.GeneratorType.

schema

• [schema] [feature]

The Table.extend_existing and Table.autoload_replaceparameters are now available on the MetaData.reflect()method.

0.9.0

Released: December 30, 2013

orm

• [orm] [feature]

The exc.StatementError or DBAPI-related subclassnow can accommodate additional information about the “reason” forthe exception; the Session now adds some detail to itwhen the exception occurs within an autoflush. This approachis taken as opposed to combining FlushError witha Python 3 style “chained exception” approach so as to maintaincompatibility both with Py2K code as well as code that alreadycatches IntegrityError or similar.

• [orm] [feature] [backrefs]

Added new argument include_backrefs=True to thevalidates() function; when set to False, a validation eventwill not be triggered if the event was initated as a backref toan attribute operation from the other side.

See also

include_backrefs=False option for @validates

References: #1535

• [orm] [feature]

A new API for specifying the FOR UPDATE clause of a SELECTis added with the new Query.with_for_update() method,to complement the new GenerativeSelect.with_for_update() method.Pull request courtesy Mario Lassnig.

See also

New FOR UPDATE support on select(), Query()

• [orm] [bug]

An adjustment to the subqueryload() strategy which ensures thatthe query runs after the loading process has begun; this is so thatthe subqueryload takes precedence over other loaders that may behitting the same attribute due to other eager/noload situationsat the wrong time.This change is also backported to: 0.8.5

References: #2887

• [orm] [bug]

Fixed bug when using joined table inheritance from a table to aselect/alias on the base, where the PK columns were also not samenamed; the persistence system would fail to copy primary key valuesfrom the base table to the inherited table upon INSERT.This change is also backported to: 0.8.5

References: #2885

• [orm] [bug]

composite() will raise an informative error message when thecolumns/attribute (names) passed don’t resolve to a Column or mappedattribute (such as an erroneous tuple); previously raised an unboundlocal.This change is also backported to: 0.8.5

References: #2889

• [orm] [bug]

Fixed a regression introduced by #2818 where the EXISTSquery being generated would produce a “columns being replaced”warning for a statement with two same-named columns,as the internal SELECT wouldn’t have use_labels set.This change is also backported to: 0.8.4

References: #2818

• [orm] [bug] [collections] [py3k]

Added support for the Python 3 method list.clear() withinthe ORM collection instrumentation system; pull requestcourtesy Eduardo Schettino.

• [orm] [bug]

Some refinements to the AliasedClass construct with regardsto descriptors, like hybrids, synonyms, composites, user-defineddescriptors, etc. The attributeadaptation which goes on has been made more robust, such that if a descriptorreturns another instrumented attribute, rather than a compound SQLexpression element, the operation will still proceed.Additionally, the “adapted” operator will retain its class; previously,a change in class from InstrumentedAttribute to QueryableAttribute(a superclass) would interact with Python’s operator system such thatan expression like aliased(MyClass.x) > MyClass.x would reverse itselfto read myclass.x < myclass_1.x. The adapted attribute will alsorefer to the new AliasedClass as its parent which was notalways the case before.References: #2872

• [orm] [bug]

The viewonly flag on relationship() will now preventattribute history from being written on behalf of the target attribute.This has the effect of the object not being written to theSession.dirty list if it is mutated. Previously, the object wouldbe present in Session.dirty, but no change would take place on behalfof the modified attribute during flush. The attribute still emitsevents such as backref events and user-defined events and will stillreceive mutations from backrefs.

See also

viewonly=True on relationship() prevents history from taking effect

References: #2833

• [orm] [bug]

Added support for new Session.info attribute toscoped_session.

• [orm] [bug]

Fixed bug where usage of new Bundle object would causethe Query.column_descriptions attribute to fail.

• [orm] [bug] [sql] [sqlite]

Fixed a regression introduced by the join rewriting feature of#2369 and #2587 where a nested join with one sidealready an aliased select would fail to translate the ON clause on theoutside correctly; in the ORM this could be seen when using aSELECT statement as a “secondary” table.References: #2858

orm declarative

• [bug] [declarative] [orm]

Declarative does an extra check to detect if the sameColumn is mapped multiple times under different properties(which typically should be a synonym() instead) or if twoor more Column objects are given the same name, raisinga warning if this condition is detected.References: #2828

• [bug] [declarative] [orm]

The DeferredReflection class has been enhanced to provideautomatic reflection support for the “secondary” table referredto by a relationship(). “secondary”, when specifiedeither as a string table name, or as a Table object withonly a name and MetaData object will also be includedin the reflection process when DeferredReflection.prepare()is called.References: #2865

• [bug] [declarative] [orm]

Fixed bug where in Py2K a unicode literal would not be acceptedas the string name of a class or other argument withindeclarative using relationship().

engine

• [engine] [feature]

The engine_from_config() function has been improved so thatwe will be able to parse dialect-specific arguments from stringconfiguration dictionaries. Dialect classes can now provide theirown list of parameter types and string-conversion routines.The feature is not yet used by the built-in dialects, however.References: #2875

• [engine] [bug]

A DBAPI that raises an error on connect() which is not a subclassof dbapi.Error (such as TypeError, NotImplementedError, etc.)will propagate the exception unchanged. Previously,the error handling specific to the connect() routine would bothinappropriately run the exception through the dialect’sDialect.is_disconnect() routine as well as wrap it ina sqlalchemy.exc.DBAPIError. It is now propagated unchangedin the same way as occurs within the execute process.This change is also backported to: 0.8.4

References: #2881

• [engine] [bug] [pool]

The QueuePool has been enhanced to not block new connectionattempts when an existing connection attempt is blocking. Previously,the production of new connections was serialized within the blockthat monitored overflow; the overflow counter is now altered withinits own critical section outside of the connection process itself.This change is also backported to: 0.8.4

References: #2880

• [engine] [bug] [pool]

Made a slight adjustment to the logic which waits for a pooledconnection to be available, such that for a connection poolwith no timeout specified, it will every half a second break out ofthe wait to check for the so-called “abort” flag, which allows thewaiter to break out in case the whole connection pool was dumped;normally the waiter should break out due to a notify_all() but it’spossible this notify_all() is missed in very slim cases.This is an extension of logic first introduced in 0.8.0, and theissue has only been observed occasionally in stress tests.This change is also backported to: 0.8.4

References: #2522

• [engine] [bug]

Fixed bug where SQL statement would be improperly ASCII-encodedwhen a pre-DBAPI StatementError were raised withinConnection.execute(), causing encoding errors fornon-ASCII statements. The stringification now remains withinPython unicode thus avoiding encoding errors.This change is also backported to: 0.8.4

References: #2871

• [engine] [bug]

The create_engine() routine and the relatedmake_url() function no longer considers the + signto be a space within the password field. The parsing has beenadjusted to match RFC 1738 exactly, in that both usernameand password expect only :, @, and / to beencoded.

See also

The “password” portion of a create_engine() no longer considers the + sign as an encoded space

References: #2873

• [engine] [bug]

The RowProxy object is now sortable in Python as a regulartuple is; this is accomplished via ensuring tuple() conversion onboth sides within the eq() method as well asthe addition of a lt() method.

See also

RowProxy now has tuple-sorting behavior

References: #2848

sql

• [sql] [feature]

New improvements to the text() construct, includingmore flexible ways to set up bound parameters and return types;in particular, a text() can now be turned into a fullFROM-object, embeddable in other statements as an alias or CTEusing the new method TextClause.columns(). The text()construct can also render “inline” bound parameters when the constructis compiled in a “literal bound” context.

See also

New text() Capabilities

References: #2877, #2882

• [sql] [feature]

A new API for specifying the FOR UPDATE clause of a SELECTis added with the new GenerativeSelect.with_for_update() method.This method supports a more straightforward system of settingdialect-specific options compared to the for_update keywordargument of select(), and also includes support for theSQL standard FOR UPDATE OF clause. The ORM also includesa new corresponding method Query.with_for_update().Pull request courtesy Mario Lassnig.

See also

New FOR UPDATE support on select(), Query()

• [sql] [feature]

The precision used when coercing a returned floating point value toPython Decimal via string is now configurable. Theflag decimal_return_scale is now supported by all Numericand Float types, which will ensure this many digits are takenfrom the native floating point value when it is converted to string.If not present, the type will make use of the value of .scale, ifthe type supports this setting and it is non-None. Otherwise the originaldefault length of 10 is used.

See also

Floating Point String-Conversion Precision Configurable for Native Floating Point Types

References: #2867

• [sql] [bug]

Fixed issue where a primary key column that has a Sequence on it,yet the column is not the “auto increment” column, either becauseit has a foreign key constraint or autoincrement=False set,would attempt to fire the Sequence on INSERT for backends that don’tsupport sequences, when presented with an INSERT missing the primarykey value. This would take place on non-sequence backends likeSQLite, MySQL.This change is also backported to: 0.8.5

References: #2896

• [sql] [bug]

Fixed bug with Insert.from_select() method where the orderof the given names would not be taken into account when generatingthe INSERT statement, thus producing a mismatch versus the columnnames in the given SELECT statement. Also noted thatInsert.from_select() implies that Python-side insert defaultscannot be used, since the statement has no VALUES clause.This change is also backported to: 0.8.5

References: #2895

• [sql] [bug]

The cast() function, when given a plain literal value,will now apply the given type to the given literal value on thebind parameter side according to the type given to the cast,in the same manner as that of the type_coerce() function.However unlike type_coerce(), this only takes effect if anon-clauseelement value is passed to cast(); an existing typedconstruct will retain its type.

• [sql] [bug]

The ForeignKey class more aggressively checks the givencolumn argument. If not a string, it checks that the object isat least a ColumnClause, or an object that resolves to one,and that the .table attribute, if present, refers to aTableClause or subclass, and not something like anAlias. Otherwise, a ArgumentError is raised.References: #2883

• [sql] [bug]

The precedence rules for the ColumnOperators.collate() operatorhave been modified, such that the COLLATE operator is now of lowerprecedence than the comparison operators. This has the effect thata COLLATE applied to a comparison will not render parenthesisaround the comparison, which is not parsed by backends such asMSSQL. The change is backwards incompatible for those setups thatwere working around the issue by applying Operators.collate()to an individual element of the comparison expression,rather than the comparison expression as a whole.

See also

The precedence rules for COLLATE have been changed

References: #2879

• [sql] [enhancement]

The exception raised when a BindParameter is presentin a compiled statement without a value now includes the key nameof the bound parameter in the error message.This change is also backported to: 0.8.5

schema

• [schema] [bug]

Fixed a regression caused by #2812 where the repr() fortable and column names would fail if the name contained non-asciicharacters.References: #2868

postgresql

• [postgresql] [feature]

Support for PostgreSQL JSON has been added, using the newJSON type. Huge thanks to Nathan Rice forimplementing and testing this.References: #2581

• [postgresql] [feature]

Added support for PostgreSQL TSVECTOR via thepostgresql.TSVECTOR type. Pull request courtesyNoufal Ibrahim.

• [postgresql] [bug]

Fixed bug where index reflection would mis-interpret indkey valueswhen using the pypostgresql adapter, which returns these valuesas lists vs. psycopg2’s return type of string.This change is also backported to: 0.8.4

References: #2855

• [postgresql] [bug]

Now using psycopg2 UNICODEARRAY extension for handling unicode arrayswith psycopg2 + normal “native unicode” mode, in the same way theUNICODE extension is used.

• [postgresql] [bug]

Fixed bug where values within an ENUM weren’t escaped for singlequote signs. Note that this is backwards-incompatible for existingworkarounds that manually escape the single quotes.

See also

PostgreSQL CREATE TYPE AS ENUM now applies quoting to values

References: #2878

mysql

• [mysql] [bug]

Improvements to the system by which SQL types generate withinrepr(), particularly with regards to the MySQL integer/numeric/character types which feature a wide variety of keyword arguments.The repr() is important for use with Alembic autogeneratefor when Python code is rendered in a migration script.References: #2893

mssql

• [mssql] [bug] [firebird]

The “asdecimal” flag used with the Float type will nowwork with Firebird as well as the mssql+pyodbc dialects; previously thedecimal conversion was not occurring.This change is also backported to: 0.8.5

• [mssql] [bug] [pymssql]

Added “Net-Lib error during Connection reset by peer” messageto the list of messages checked for “disconnect” within thepymssql dialect. Courtesy John Anderson.This change is also backported to: 0.8.5

• [mssql] [bug]

Fixed bug introduced in 0.8.0 where the DROP INDEXstatement for an index in MSSQL would render incorrectly if theindex were in an alternate schema; the schemaname/tablenamewould be reversed. The format has been also been revised tomatch current MSSQL documentation. Courtesy Derek Harland.This change is also backported to: 0.8.4

oracle

• [oracle] [bug]

Added ORA-02396 “maximum idle time” error code to list of“is disconnect” codes with cx_oracle.This change is also backported to: 0.8.4

References: #2864

• [oracle] [bug]

Fixed bug where Oracle VARCHAR types given with no length(e.g. for a CAST or similar) would incorrectly render None CHARor similar.This change is also backported to: 0.8.4

References: #2870

firebird

• [firebird] [bug]

The firebird dialect will quote identifiers which begin with anunderscore. Courtesy Treeve Jelbert.This change is also backported to: 0.8.5

References: #2897

• [firebird] [bug]

Fixed bug in Firebird index reflection where the columns within theindex were not sorted correctly; they are now sortedin order of RDB$FIELD_POSITION.This change is also backported to: 0.8.5

• [firebird] [bug]

Changed the queries used by Firebird to list table and view namesto query from the rdb$relations view instead of therdb$relation_fields and rdb$view_relations views.Variants of both the old and new queries are mentioned on manyFAQ and blogs, however the new queries are taken straight fromthe “Firebird FAQ” which appears to be the most official sourceof info.References: #2898

misc

• [removed]

The “informix” and “informixdb” dialects have been removed; the codeis now available as a separate repository on Bitbucket. The IBM-DBproject has provided production-level Informix support since theinformixdb dialect was first added.

• [bug] [declarative]

Error message when a string arg sent to relationship() whichdoesn’t resolve to a class or mapper has been corrected to workthe same way as when a non-string arg is received, which indicatesthe name of the relationship which had the configurational error.This change is also backported to: 0.8.5

References: #2888

• [bug] [ext]

Fixed bug which prevented the serializer extension from workingcorrectly with table or column names that contain non-ASCIIcharacters.This change is also backported to: 0.8.4

References: #2869

• [bug] [examples]

Fixed bug which prevented history_meta recipe from working withjoined inheritance schemes more than one level deep.

0.9.0b1

Released: October 26, 2013

general

• [general] [feature] [py3k]

The C extensions are ported to Python 3 and will build underany supported CPython 2 or 3 environment.References: #2161

• [general] [feature] [py3k]

The codebase is now “in-place” for Python2 and 3, the need to run 2to3 has been removed.Compatibility is now against Python 2.6 on forward.References: #2671

• [general]

A large refactoring of packages has reorganizedthe import structure of many Core modules as well as some aspectsof the ORM modules. In particular sqlalchemy.sql has been brokenout into several more modules than before so that the very large sizeof sqlalchemy.sql.expression is now pared down. The efforthas focused on a large reduction in import cycles. Additionally,the system of API functions in sqlalchemy.sql.expression andsqlalchemy.orm has been reorganized to eliminate redundancyin documentation between the functions vs. the objects they produce.

orm

• [orm] [feature]

Added new option to relationship() distinct_target_key.This enables the subquery eager loader strategy to apply a DISTINCTto the innermost SELECT subquery, to assist in the case whereduplicate rows are generated by the innermost query which correspondsto this relationship (there’s not yet a general solution to the issueof dupe rows within subquery eager loading, however, when joins outsideof the innermost subquery produce dupes). When the flagis set to True, the DISTINCT is rendered unconditionally, and whenit is set to None, DISTINCT is rendered if the innermost relationshiptargets columns that do not comprise a full primary key.The option defaults to False in 0.8 (e.g. off by default in all cases),None in 0.9 (e.g. automatic by default). Thanks to Alexander Kovalfor help with this.

See also

Subquery Eager Loading will apply DISTINCT to the innermost SELECT for some queries

This change is also backported to: 0.8.3

References: #2836

• [orm] [feature]

The association proxy now returns None when fetching a scalarattribute off of a scalar relationship, where the scalar relationshipitself points to None, instead of raising an AttributeError.

See also

Association Proxy Missing Scalar returns None

References: #2810

• [orm] [feature]

Added new method AttributeState.load_history(), works likeAttributeState.history but also fires loader callables.

See also

attributes.get_history() will query from the DB by default if value not present

References: #2787

• [orm] [feature]

Added a new load option orm.load_only(). This allows a seriesof column names to be specified as loading “only” those attributes,deferring the rest.References: #1418

• [orm] [feature]

The system of loader options has been entirely rearchitected to buildupon a much more comprehensive base, the Load object. Thisbase allows any common loader option like joinedload(),defer(), etc. to be used in a “chained” style for the purposeof specifying options down a path, such as joinedload("foo").subqueryload("bar").The new system supersedes the usage of dot-separated path names,multiple attributes within options, and the usage of _all() options.

See also

New Query Options API; load_only() option

References: #1418

• [orm] [feature]

The composite() construct now maintains the return objectwhen used in a column-oriented Query, rather than expandingout into individual columns. This makes use of the new Bundlefeature internally. This behavior is backwards incompatible; toselect from a composite column which will expand out, useMyClass.some_composite.clauses.

See also

Composite attributes are now returned as their object form when queried on a per-attribute basis

References: #2824

• [orm] [feature]

A new construct Bundle is added, which allows for specificationof groups of column expressions to a Query construct.The group of columns are returned as a single tuple by default. Thebehavior of Bundle can be overridden however to provideany sort of result processing to the returned row. The behaviorof Bundle is also embedded into composite attributes nowwhen they are used in a column-oriented Query.

See also

Column Bundles for ORM queries

Composite attributes are now returned as their object form when queried on a per-attribute basis

References: #2824

• [orm] [feature]

The version_id_generator parameter of Mapper can now be specifiedto rely upon server generated version identifiers, using triggersor other database-provided versioning features, or via an optional programmaticvalue, by setting version_id_generator=False.When using a server-generated version identifier, the ORM will use RETURNING whenavailable to immediatelyload the new version value, else it will emit a second SELECT.References: #2793

• [orm] [feature]

The eager_defaults flag of Mapper will now allow thenewly generated default values to be fetched using an inlineRETURNING clause, rather than a second SELECT statement, for backendsthat support RETURNING.References: #2793

• [orm] [feature]

Added a new attribute Session.info to Session;this is a dictionary where applications can store arbitrarydata local to a Session.The contents of Session.info can be also be initializedusing the info argument of Session orsessionmaker.

• [orm] [feature]

Removal of event listeners is now implemented. The feature isprovided via the event.remove() function.

See also

Event Removal API

References: #2268

• [orm] [feature]

The mechanism by which attribute events pass along anAttributeImpl as an “initiator” token has been changed;the object is now an event-specific object called attributes.Event.Additionally, the attribute system no longer halts events basedon a matching “initiator” token; this logic has been moved to bespecific to ORM backref event handlers, which are the typical sourceof the re-propagation of an attribute event onto subsequent append/set/removeoperations. End user code which emulates the behavior of backrefsmust now ensure that recursive event propagation schemes are halted,if the scheme does not use the backref handlers. Using this new system,backref handlers can now perform a“two-hop” operation when an object is appended to a collection,associated with a new many-to-one, de-associated with the previousmany-to-one, and then removed from a previous collection. Before thischange, the last step of removal from the previous collection wouldnot occur.

See also

Backref handlers can now propagate more than one level deep

References: #2789

• [orm] [feature]

A major change regarding how the ORM constructs joins wherethe right side is itself a join or left outer join. The ORMis now configured to allow simple nesting of joins ofthe form a JOIN (b JOIN c ON b.id=c.id) ON a.id=b.id,rather than forcing the right side into a SELECT subquery.This should allow significant performance improvements on mostbackends, most particularly MySQL. The one database backendthat has for many years held back this change, SQLite, is now addressed bymoving the production of the SELECT subquery from theORM to the SQL compiler; so that a right-nested join on SQLite will stillultimately render with a SELECT, while all other backendsare no longer impacted by this workaround.

As part of this change, a new argument flat=True has been addedto the orm.aliased(), Join.alias(), andorm.with_polymorphic() functions, which allows an “alias” of aJOIN to be produced which applies an anonymous alias to each componenttable within the join, rather than producing a subquery.

See also

Many JOIN and LEFT OUTER JOIN expressions will no longer be wrapped in (SELECT * FROM ..) AS ANON_1

References: #2587

• [orm] [bug]

Fixed bug where using an annotation such as remote() orforeign() on a Column before association with a parentTable could produce issues related to the parent table notrendering within joins, due to the inherent copy operation performedby an annotation.This change is also backported to: 0.8.3

References: #2813

• [orm] [bug]

Fixed bug where Query.exists() failed to work correctlywithout any WHERE criterion. Courtesy Vladimir Magamedov.This change is also backported to: 0.8.3

References: #2818

• [orm] [bug]

Fixed a potential issue in an ordered sequence implementation usedby the ORM to iterate mapper hierarchies; under the Jython interpreterthis implementation wasn’t ordered, even though cPython and PyPymaintained ordering.This change is also backported to: 0.8.3

References: #2794

• [orm] [bug]

Fixed bug in ORM-level event registration where the “raw” or“propagate” flags could potentially be mis-configured in some“unmapped base class” configurations.This change is also backported to: 0.8.3

References: #2786

• [orm] [bug]

A performance fix related to the usage of the defer() optionwhen loading mapped entities. The function overhead of applyinga per-object deferred callable to an instance at load time wassignificantly higher than that of just loading the data from the row(note that defer() is meant to reduce DB/network overhead, notnecessarily function call count); the function call overhead is nowless than that of loading data from the column in all cases. Thereis also a reduction in the number of “lazy callable” objects createdper load from N (total deferred values in the result) to 1 (totalnumber of deferred cols).This change is also backported to: 0.8.3

References: #2778

• [orm] [bug]

Fixed bug whereby attribute history functions would failwhen an object we moved from “persistent” to “pending”using the make_transient() function, for operationsinvolving collection-based backrefs.This change is also backported to: 0.8.3

References: #2773

• [orm] [bug]

A warning is emitted when trying to flush an object of an inheritedclass where the polymorphic discriminator has been assignedto a value that is invalid for the class.This change is also backported to: 0.8.2

References: #2750

• [orm] [bug]

Fixed bug in polymorphic SQL generation where multiple joined-inheritanceentities against the same base class joined to each other as wellwould not track columns on the base table independently of each other ifthe string of joins were more than two entities long.This change is also backported to: 0.8.2

References: #2759

• [orm] [bug]

Fixed bug where sending a composite attribute into Query.order_by()would produce a parenthesized expression not accepted by some databases.This change is also backported to: 0.8.2

References: #2754

• [orm] [bug]

Fixed the interaction between composite attributes andthe aliased() function. Previously, composite attributeswouldn’t work correctly in comparison operations when aliasingwas applied.This change is also backported to: 0.8.2

References: #2755

• [orm] [bug] [ext]

Fixed bug where MutableDict didn’t report a change eventwhen clear() was called.This change is also backported to: 0.8.2

References: #2730

• [orm] [bug]

Fixed bug where list instrumentation would fail to represent asetslice of [0:0] correctly, which in particular could occurwhen using insert(0, item) with the association proxy. Dueto some quirk in Python collections, the issue was much more likelywith Python 3 rather than 2.This change is also backported to: 0.8.3, 0.7.11

References: #2807

• [orm] [bug]

attributes.get_history() when used with a scalar column-mappedattribute will now honor the “passive” flagpassed to it; as this defaults to PASSIVE_OFF, the function willby default query the database if the value is not present.This is a behavioral change vs. 0.8.

See also

attributes.get_history() will query from the DB by default if value not present

References: #2787

• [orm] [bug] [associationproxy]

Added additional criterion to the ==, != comparators, used withscalar values, for comparisons to None to also take into accountthe association record itself being non-present, in addition to theexisting test for the scalar endpoint on the association recordbeing NULL. Previously, comparing Cls.scalar == None would returnrecords for which Cls.associated were present andCls.associated.scalar is None, but not rows for whichCls.associated is non-present. More significantly, theinverse operation Cls.scalar != None would return Clsrows for which Cls.associated was non-present.

The case for Cls.scalar != 'somevalue' is also modifiedto act more like a direct SQL comparison; only rows forwhich Cls.associated is present and Associated.scalaris non-NULL and not equal to 'somevalue' are returned.Previously, this would be a simple NOT EXISTS.

Also added a special use case where youcan call Cls.scalar.has() with no arguments,when Cls.scalar is a column-based value - this returns whether ornot Cls.associated has any rows present, regardless of whetheror not Cls.associated.scalar is NULL or not.

See also

Association Proxy SQL Expression Improvements and Fixes

References: #2751

• [orm] [bug]

Fixed an obscure bug where the wrong results would befetched when joining/joinedloading across a many-to-manyrelationship to a single-table-inheritingsubclass with a specific discriminator value, due to “secondary”rows that would come back. The “secondary” and right-sidetables are now inner joined inside of parenthesis for allORM joins on many-to-many relationships so that the left->rightjoin can accurately filtered. This change was made possibleby finally addressing the issue with right-nested joinsoutlined in #2587.

See also

Many JOIN and LEFT OUTER JOIN expressions will no longer be wrapped in (SELECT * FROM ..) AS ANON_1

References: #2369

• [orm] [bug]

The “auto-aliasing” behavior of the Query.select_from()method has been turned off. The specific behavior is nowavailable via a new method Query.select_entity_from().The auto-aliasing behavior here was never well documented andis generally not what’s desired, as Query.select_from()has become more oriented towards controlling how a JOIN isrendered. Query.select_entity_from() will also be madeavailable in 0.8 so that applications which rely on the auto-aliasingcan shift their applications to use this method.

See also

Query.select_from() no longer applies the clause to corresponding entities

References: #2736

orm declarative

• [feature] [declarative] [orm]

Added a convenience class decorator as_declarative(), isa wrapper for declarative_base() which allows an existing baseclass to be applied using a nifty class-decorated approach.This change is also backported to: 0.8.3

• [feature] [declarative] [orm]

ORM descriptors such as hybrid properties can now be referencedby name in a string argument used with order_by,primaryjoin, or similar in relationship(),in addition to column-bound attributes.This change is also backported to: 0.8.2

References: #2761

engine

• [engine] [feature]

repr() for the URL of an Enginewill now conceal the password using asterisks.Courtesy Gunnlaugur Þór Briem.This change is also backported to: 0.8.3

References: #2821

• [engine] [feature]

New events added to ConnectionEvents:

• ConnectionEvents.engine_connect()

• ConnectionEvents.set_connection_execution_options()

• ConnectionEvents.set_engine_execution_options()References: #2770

• [engine] [bug] [oracle]

Dialect.initialize() is not called a second time if an Engineis recreated, due to a disconnect error. This fixes a particularissue in the Oracle 8 dialect, but in general the dialect.initialize()phase should only be once per dialect.This change is also backported to: 0.8.3

References: #2776

• [engine] [bug] [pool]

Fixed bug where QueuePool would lose the correctchecked out count if an existing pooled connection failed to reconnectafter an invalidate or recycle event.This change is also backported to: 0.8.3

References: #2772

• [engine] [bug]

Fixed bug where the reset_on_return argument to various Poolimplementations would not be propagated when the pool was regenerated.Courtesy Eevee.This change is also backported to: 0.8.2

• [engine] [bug]

The regexp used by the make_url() function now parsesipv6 addresses, e.g. surrounded by brackets.This change is also backported to: 0.8.3, 0.7.11

References: #2851

• [engine] [bug]

The method signature of Dialect.reflecttable(), which inall known cases is provided by DefaultDialect, has beentightened to expect include_columns and exclude_columnsarguments without any kw option, reducing ambiguity - previouslyexclude_columns was missing.References: #2748

sql

• [sql] [feature]

Added support for “unique constraint” reflection, via theInspector.get_unique_constraints() method.Thanks for Roman Podolyaka for the patch.This change is also backported to: 0.8.4

References: #1443

• [sql] [feature]

The update(), insert(), and delete() constructswill now interpret ORM entities as target tables to be operated upon,e.g.:

from sqlalchemy import insert, update, delete
 
ins = insert(SomeMappedClass).values(x=5)
 
del_ = delete(SomeMappedClass).where(SomeMappedClass.id == 5)
 
upd = update(SomeMappedClass).where(SomeMappedClass.id == 5).values(name='ed')

This change is also backported to: 0.8.3

• [sql] [feature] [mysql] [postgresql]

The PostgreSQL and MySQL dialects now support reflection/inspectionof foreign key options, including ON UPDATE, ON DELETE. PostgreSQLalso reflects MATCH, DEFERRABLE, and INITIALLY. Courtesy ijl.References: #2183

• [sql] [feature]

A bindparam() construct with a “null” type (e.g. no typespecified) is now copied when used in a typed expression, and thenew copy is assigned the actual type of the compared column. Previously,this logic would occur on the given bindparam() in place.Additionally, a similar process now occurs for bindparam() constructspassed to ValuesBase.values() for an Insert orUpdate construct, within the compilation phase of theconstruct.

These are both subtle behavioral changes which may impact someusages.

See also

A bindparam() construct with no type gets upgraded via copy when a type is available

References: #2850

• [sql] [feature]

An overhaul of expression handling for special symbols particularlywith conjunctions, e.g.None expression.null()expression.true()expression.false(), including consistency in rendering NULLin conjunctions, “short-circuiting” of and_() and or_()expressions which contain boolean constants, and rendering ofboolean constants and expressions as compared to “1” or “0” for backendsthat don’t feature true/false constants.

See also

Improved rendering of Boolean constants, NULL constants, conjunctions

References: #2734, #2804, #2823

• [sql] [feature]

The typing system now handles the task of rendering “literal bind” values,e.g. values that are normally bound parameters but due to context mustbe rendered as strings, typically within DDL constructs such asCHECK constraints and indexes (note that “literal bind” valuesbecome used by DDL as of #2742). A new methodTypeEngine.literal_processor() serves as the base, andTypeDecorator.process_literal_param() is added to allow wrappingof a native literal rendering method.

See also

The typing system now handles the task of rendering “literal bind” values

References: #2838

• [sql] [feature]

The Table.tometadata() method now produces copies ofall SchemaItem.info dictionaries from all SchemaItemobjects within the structure including columns, constraints,foreign keys, etc. As these dictionariesare copies, they are independent of the original dictionary.Previously, only the .info dictionary of Column was transferredwithin this operation, and it was only linked in place, not copied.References: #2716

• [sql] [feature]

The default argument of Column now accepts a classor object method as an argument, in addition to a standalone function;will properly detect if the “context” argument is accepted or not.

• [sql] [feature]

Added new method to the insert() constructInsert.from_select(). Given a list of columns anda selectable, renders INSERT INTO (table) (columns) SELECT ...While this feature is highlighted as part of 0.9 it is alsobackported to 0.8.3.

See also

INSERT from SELECT

References: #722

• [sql] [feature]

Provided a new attribute for TypeDecoratorcalled TypeDecorator.coerce_to_is_types,to make it easier to control how comparisons using== or != to None and boolean types goesabout producing an IS expression, or a plainequality expression with a bound parameter.References: #2734, #2744

• [sql] [feature]

A label() construct will now render as its name alonein an ORDER BY clause, if that label is also referred toin the columns clause of the select, instead of rewriting thefull expression. This gives the database a better chance tooptimize the evaluation of the same expression in two differentcontexts.

See also

Label constructs can now render as their name alone in an ORDER BY

References: #1068

• [sql] [bug]

Fixed bug where type_coerce() would not interpret ORMelements with a clause_element() method properly.This change is also backported to: 0.8.3

References: #2849

• [sql] [bug]

The Enum and Boolean types now bypassany custom (e.g. TypeDecorator) type in use when producing theCHECK constraint for the “non native” type. This so that the custom typeisn’t involved in the expression within the CHECK, since thisexpression is against the “impl” value and not the “decorated” value.This change is also backported to: 0.8.3

References: #2842

• [sql] [bug]

The .unique flag on Index could be produced as Noneif it was generated from a Column that didn’t specify unique(where it defaults to None). The flag will now always be True orFalse.This change is also backported to: 0.8.3

References: #2825

• [sql] [bug]

Fixed bug in default compiler plus those of postgresql, mysql, andmssql to ensure that any literal SQL expression values arerendered directly as literals, instead of as bound parameters,within a CREATE INDEX statement. This also changes the renderingscheme for other DDL such as constraints.This change is also backported to: 0.8.3

References: #2742

• [sql] [bug]

A select() that is made to refer to itself in its FROM clause,typically via in-place mutation, will raise an informative errormessage rather than causing a recursion overflow.This change is also backported to: 0.8.3

References: #2815

• [sql] [bug]

Fixed bug where using the column_reflect event to change the .keyof the incoming Column would prevent primary key constraints,indexes, and foreign key constraints from being correctly reflected.This change is also backported to: 0.8.3

References: #2811

• [sql] [bug]

The ColumnOperators.notin_() operator added in 0.8 now properlyproduces the negation of the expression “IN” returnswhen used against an empty collection.This change is also backported to: 0.8.3

• [sql] [bug] [postgresql]

Fixed bug where the expression system relied upon the str()form of a some expressions when referring to the .c collectionon a select() construct, but the str() form isn’t availablesince the element relies on dialect-specific compilation constructs,notably the getitem() operator as used with a PostgreSQLARRAY element. The fix also adds a new exception classUnsupportedCompilationError which is raised in those caseswhere a compiler is asked to compile something it doesn’t knowhow to.This change is also backported to: 0.8.3

References: #2780

• [sql] [bug]

Multiple fixes to the correlation behavior ofSelect constructs, first introduced in 0.8.0:

• To satisfy the use case where FROM entries should becorrelated outwards to a SELECT that encloses another,which then encloses this one, correlation now worksacross multiple levels when explicit correlation isestablished via Select.correlate(), providedthat the target select is somewhere along the chaincontained by a WHERE/ORDER BY/columns clause, notjust nested FROM clauses. This makesSelect.correlate() act more compatibly tothat of 0.7 again while still maintaining the new“smart” correlation.

• When explicit correlation is not used, the usual“implicit” correlation limits its behavior to justthe immediate enclosing SELECT, to maximize compatibilitywith 0.7 applications, and also prevents correlationacross nested FROMs in this case, maintaining compatibilitywith 0.8.0/0.8.1.

• The Select.correlate_except() method was notpreventing the given FROM clauses from correlation inall cases, and also would cause FROM clauses to be incorrectlyomitted entirely (more like what 0.7 would do),this has been fixed.

• Calling select.correlate_except(None) will enterall FROM clauses into correlation as would be expected.This change is also backported to: 0.8.2

References: #2668, #2746

• [sql] [bug]

Fixed bug whereby joining a select() of a table “A” with multipleforeign key paths to a table “B”, to that table “B”, would failto produce the “ambiguous join condition” error that would bereported if you join table “A” directly to “B”; it would insteadproduce a join condition with multiple criteria.This change is also backported to: 0.8.2

References: #2738

• [sql] [bug] [reflection]

Fixed bug whereby using MetaData.reflect() across a remoteschema as well as a local schema could produce wrong resultsin the case where both schemas had a table of the same name.This change is also backported to: 0.8.2

References: #2728

• [sql] [bug]

Removed the “not implemented” iter() call from the baseColumnOperators class, while this was introducedin 0.8.0 to prevent an endless, memory-growing loop when one alsoimplements a getitem() method on a customoperator and then calls erroneously list() on that object,it had the effect of causing column elements to report that theywere in fact iterable types which then throw an error when you tryto iterate. There’s no real way to have both sides here so westick with Python best practices. Careful with implementinggetitem() on your custom operators!This change is also backported to: 0.8.2

References: #2726

• [sql] [bug]

Fixed regression dating back to 0.7.9 whereby the name of a CTE mightnot be properly quoted if it was referred to in multiple FROM clauses.This change is also backported to: 0.8.3, 0.7.11

References: #2801

• [sql] [bug] [cte]

Fixed bug in common table expression system where if the CTE wereused only as an alias() construct, it would not render using theWITH keyword.This change is also backported to: 0.8.3, 0.7.11

References: #2783

• [sql] [bug]

Fixed bug in CheckConstraint DDL where the “quote” flag from aColumn object would not be propagated.This change is also backported to: 0.8.3, 0.7.11

References: #2784

• [sql] [bug]

The “name” attribute is set on Index before the “attach”events are called, so that attachment events can be used to dynamicallygenerate a name for the index based on the parent table and/orcolumns.References: #2835

• [sql] [bug]

The erroneous kw arg “schema” has been removed from the ForeignKeyobject. this was an accidental commit that did nothing; a warning is raisedin 0.8.3 when this kw arg is used.References: #2831

• [sql] [bug]

A rework to the way that “quoted” identifiers are handled, in thatinstead of relying upon various quote=True flags being passed around,these flags are converted into rich string objects with quoting informationincluded at the point at which they are passed to common schema constructslike Table, Column, etc. This solves the issueof various methods that don’t correctly honor the “quote” flag suchas Engine.has_table() and related methods. The quoted_nameobject is a string subclass that can also be used explicitly if needed;the object will hold onto the quoting preferences passed and willalso bypass the “name normalization” performed by dialects thatstandardize on uppercase symbols, such as Oracle, Firebird and DB2.The upshot is that the “uppercase” backends can now work with force-quotednames, such as lowercase-quoted names and new reserved words.

See also

Schema identifiers now carry along their own quoting information

References: #2812

• [sql] [bug]

The resolution of ForeignKey objects to theirtarget Column has been reworked to be asimmediate as possible, based on the moment that thetarget Column is associated with the sameMetaData as this ForeignKey, ratherthan waiting for the first time a join is constructed,or similar. This along with other improvements allowsearlier detection of some foreign key configurationissues. Also included here is a rework of thetype-propagation system, so thatit should be reliable now to set the type as Noneon any Column that refers to another viaForeignKey - the type will be copied from thetarget column as soon as that other column is associated,and now works for composite foreign keys as well.

See also

Columns can reliably get their type from a column referred to via ForeignKey

References: #1765

postgresql

• [postgresql] [feature]

Support for PostgreSQL 9.2 range types has been added.Currently, no type translation is provided, so worksdirectly with strings or psycopg2 2.5 range extension typesat the moment. Patch courtesy Chris Withers.This change is also backported to: 0.8.2

• [postgresql] [feature]

Added support for “AUTOCOMMIT” isolation when using the psycopg2DBAPI. The keyword is available via the isolation_levelexecution option. Patch courtesy Roman Podolyaka.This change is also backported to: 0.8.2

References: #2072

• [postgresql] [feature]

Added support for rendering SMALLSERIAL when a SmallIntegertype is used on a primary key autoincrement column, based on serverversion detection of PostgreSQL version 9.2 or greater.References: #2840

• [postgresql] [bug]

Removed a 128-character truncation from the reflection of theserver default for a column; this code was original fromPG system views which truncated the string for readability.This change is also backported to: 0.8.3

References: #2844

• [postgresql] [bug]

Parenthesis will be applied to a compound SQL expression asrendered in the column list of a CREATE INDEX statement.This change is also backported to: 0.8.3

References: #2742

• [postgresql] [bug]

Fixed bug where PostgreSQL version strings that had a prefix precedingthe words “PostgreSQL” or “EnterpriseDB” would not parse.Courtesy Scott Schaefer.This change is also backported to: 0.8.3

References: #2819

• [postgresql] [bug]

The behavior of extract() has been simplified on thePostgreSQL dialect to no longer inject a hardcoded ::timestampor similar cast into the given expression, as this interferedwith types such as timezone-aware datetimes, but alsodoes not appear to be at all necessary with modern versionsof psycopg2.This change is also backported to: 0.8.2

References: #2740

• [postgresql] [bug]

Fixed bug in HSTORE type where keys/values that containedbackslashed quotes would not be escaped correctly whenusing the “non native” (i.e. non-psycopg2) meansof translating HSTORE data. Patch courtesy Ryan Kelly.This change is also backported to: 0.8.2

References: #2766

• [postgresql] [bug]

Fixed bug where the order of columns in a multi-columnPostgreSQL index would be reflected in the wrong order.Courtesy Roman Podolyaka.This change is also backported to: 0.8.2

References: #2767

mysql

• [mysql] [feature]

The mysql_length parameter used with Index can nowbe passed as a dictionary of column names/lengths, for usewith composite indexes. Big thanks to Roman Podolyaka for thepatch.This change is also backported to: 0.8.2

References: #2704

• [mysql] [feature]

The MySQL mysql.SET type now features the same auto-quotingbehavior as that of mysql.ENUM. Quotes are not required whensetting up the value, but quotes that are present will be auto-detectedalong with a warning. This also helps with Alembic wherethe SET type doesn’t render with quotes.References: #2817

• [mysql] [bug]

The change in #2721, which is that the deferrable keywordof ForeignKeyConstraint is silently ignored on the MySQLbackend, will be reverted as of 0.9; this keyword will now render again, raisingerrors on MySQL as it is not understood - the same behavior will alsoapply to the initially keyword. In 0.8, the keywords will remainignored but a warning is emitted. Additionally, the match keywordnow raises a CompileError on 0.9 and emits a warning on 0.8;this keyword is not only silently ignored by MySQL but also breaksthe ON UPDATE/ON DELETE options.

To use a ForeignKeyConstraintthat does not render or renders differently on MySQL, use a customcompilation option. An example of this usage has been added to thedocumentation, see MySQL Foreign Keys.This change is also backported to: 0.8.3

References: #2721, #2839

• [mysql] [bug]

MySQL-connector dialect now allows options in the create_enginequery string to override those defaults set up in the connect,including “buffered” and “raise_on_warnings”.This change is also backported to: 0.8.3

References: #2515

• [mysql] [bug]

Fixed bug when using multi-table UPDATE where a supplementaltable is a SELECT with its own bound parameters, where the positioningof the bound parameters would be reversed versus the statementitself when using MySQL’s special syntax.This change is also backported to: 0.8.2

References: #2768

• [mysql] [bug]

Added another conditional to the mysql+gaerdbms dialect todetect so-called “development” mode, where we should use therdbms_mysqldb DBAPI. Patch courtesy Brett Slatkin.This change is also backported to: 0.8.2

References: #2715

• [mysql] [bug]

The deferrable keyword argument on ForeignKey andForeignKeyConstraint will not render the DEFERRABLE keywordon the MySQL dialect. For a long time we left this in place becausea non-deferrable foreign key would act very differently than a deferrableone, but some environments just disable FKs on MySQL, so we’ll be lessopinionated here.This change is also backported to: 0.8.2

References: #2721

• [mysql] [bug]

Updates to MySQL reserved words for versions 5.5, 5.6, courtesyHanno Schlichting.This change is also backported to: 0.8.3, 0.7.11

References: #2791

• [mysql] [bug]

Fix and test parsing of MySQL foreign key options within reflection;this complements the work in #2183 where we begin to supportreflection of foreign key options such as ON UPDATE/ON DELETEcascade.References: #2839

• [mysql] [bug]

Improved support for the cymysql driver, supporting version 0.6.5,courtesy Hajime Nakagami.

sqlite

• [sqlite] [bug]

The newly added SQLite DATETIME arguments storage_format andregexp apparently were not fully implemented correctly; while thearguments were accepted, in practice they would have no effect;this has been fixed.This change is also backported to: 0.8.3

References: #2781

• [sqlite] [bug]

Added sqlalchemy.types.BIGINT to the list of type names that can bereflected by the SQLite dialect; courtesy Russell Stuart.This change is also backported to: 0.8.2

References: #2764

mssql

• [mssql] [bug]

When querying the information schema on SQL Server 2000, removeda CAST call that was added in 0.8.1 to help with driver issues,which apparently is not compatible on 2000.The CAST remains in place for SQL Server 2005 and greater.This change is also backported to: 0.8.2

References: #2747

• [mssql] [bug] [pyodbc]

Fixes to MSSQL with Python 3 + pyodbc, including that statementsare passed correctly.References: #2355

oracle

• [oracle] [feature] [py3k]

The Oracle unit tests with cx_oracle now passfully under Python 3.

• [oracle] [bug]

Fixed bug where Oracle table reflection using synonyms would failif the synonym and the table were in different remote schemas.Patch to fix courtesy Kyle Derr.This change is also backported to: 0.8.3

References: #2853

firebird

• [firebird] [feature]

Added new flag retaining=True to the kinterbasdb and fdb dialects.This controls the value of the retaining flag sent to thecommit() and rollback() methods of the DBAPI connection.Due to historical concerns, this flag defaults to True in 0.8.2,however in 0.9.0b1 this flag defaults to False.This change is also backported to: 0.8.2

References: #2763

• [firebird] [feature]

The fdb dialect is now the default dialect whenspecified without a dialect qualifier, i.e. firebird://,per the Firebird project publishing fdb as theirofficial Python driver.References: #2504

• [firebird] [bug]

Type lookup when reflecting the Firebird types LONG andINT64 has been fixed so that LONG is treated as INTEGER,INT64 treated as BIGINT, unless the type has a “precision”in which case it’s treated as NUMERIC. Patch courtesyRussell Stuart.This change is also backported to: 0.8.2

References: #2757

misc

• [feature]

Added a new flag system=True to Column, which marksthe column as a “system” column which is automatically made presentby the database (such as PostgreSQL oid or xmin). Thecolumn will be omitted from the CREATE TABLE statement but willotherwise be available for querying. In addition, theCreateColumn construct can be applied to a customcompilation rule which allows skipping of columns, by producinga rule that returns None.This change is also backported to: 0.8.3

• [feature] [examples]

Improved the examples in examples/generic_associations, includingthat discriminator_on_association.py makes use of single tableinheritance do the work with the “discriminator”. Alsoadded a true “generic foreign key” example, which works similarlyto other popular frameworks in that it uses an open-ended integerto point to any other table, foregoing traditional referentialintegrity. While we don’t recommend this pattern, information wantsto be free.This change is also backported to: 0.8.3

• [feature] [core]

Added a new variant to UpdateBase.returning() calledValuesBase.return_defaults(); this allows arbitrary columnsto be added to the RETURNING clause of the statement without interferingwith the compilers usual “implicit returning” feature, which is used toefficiently fetch newly generated primary key values. For supportingbackends, a dictionary of all fetched values is present atResultProxy.returned_defaults.References: #2793

• [feature] [pool]

Added pool logging for “rollback-on-return” and the less used“commit-on-return”. This is enabled with the rest of pool“debug” logging.References: #2752

• [bug] [examples]

Added “autoincrement=False” to the history table created in theversioning example, as this table shouldn’t have autoinc on itin any case, courtesy Patrick Schmid.This change is also backported to: 0.8.3

• [bug] [ext]

Fixed bug whereby if a composite type were set upwith a function instead of a class, the mutable extensionwould trip up when it tried to check that columnfor being a MutableComposite (which it isn’t).Courtesy asldevi.This change is also backported to: 0.8.2

• [bug] [examples]

Fixed an issue with the “versioning” recipe whereby a many-to-onereference could produce a meaningless version for the target,even though it was not changed, when backrefs were present.Patch courtesy Matt Chisholm.This change is also backported to: 0.8.2

• [requirements]

The Python mock libraryis now required in order to run the unit test suite. While partof the standard library as of Python 3.3, previous Python installationswill need to install this in order to run unit tests or touse the sqlalchemy.testing package for external dialects.This change is also backported to: 0.8.2