0.8 Changelog

0.8.7

Released: July 22, 2014

orm

• [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.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.References: #3047

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.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.References: #3102

• [sql] [bug]

Fixed bug in INSERT..FROM SELECT construct where selecting from aUNION would wrap the union in an anonymous (e.g. unlabeled) subquery.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().References: #3045

postgresql

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

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.References: #3101

• [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.References: #3085

• [mysql] [bug]

Added support for reflecting tables where an index includesKEY_BLOCK_SIZE using an equal sign. Pull request courtesySean McGivern.

mssql

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

• [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.References: #3091

misc

• [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.References: #3062

• [bug] [ext]

Fixed bug in mutable extension where MutableDict did notreport change events for the setdefault() dictionary operation.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é.References: #3051, #3093

0.8.6

Released: March 28, 2014

general

• [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.References: #2986

orm

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

• [orm] [bug]

Removed stale names from sqlalchemy.orm.interfaces.all andrefreshed with current names, so that an import * from thismodule again works.References: #2975

sql

• [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.References: #2977

postgresql

• [postgresql] [feature]

Enabled “sane multi-row count” checking for the psycopg2 DBAPI, asthis seems to be supported as of psycopg2 2.0.9.

• [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.References: #3000

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.References: #2997

0.8.5

Released: February 19, 2014

orm

• [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.References: #2951

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

• [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.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.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.References: #2889

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.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.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.References: #2957

• [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.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.References: #2895

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

postgresql

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

• [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.References: #2291

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.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.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().References: #2933

• [mysql] [bug]

Some missing methods added to the cymysql dialect, including_get_server_version_info() and _detect_charset(). Pullreqcourtesy Hajime Nakagami.

sqlite

• [sqlite] [bug]

Restored a change that was missed in the backport of uniqueconstraint reflection to 0.8, where UniqueConstraintwith SQLite would fail if reserved keywords were included in thenames of columns. Pull request courtesy Roman Podolyaka.

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.

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

firebird

• [firebird] [bug]

The firebird dialect will quote identifiers which begin with anunderscore. Courtesy Treeve Jelbert.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.

misc

• [bug] [py3k]

Fixed Py3K bug where a missing import would cause “literal binary”mode to fail to import “util.binary_type” when rendering a boundparameter. 0.9 handles this differently. Pull request courtesyAndreas Zeidler.

• [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.References: #2888

0.8.4

Released: December 8, 2013

orm

• [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.References: #2818

engine

• [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.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.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.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.References: #2871

sql

• [sql] [feature]

Added support for “unique constraint” reflection, via theInspector.get_unique_constraints() method.Thanks for Roman Podolyaka for the patch.References: #1443

postgresql

• [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.References: #2855

mssql

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

oracle

• [oracle] [bug]

Added ORA-02396 “maximum idle time” error code to list of“is disconnect” codes with cx_oracle.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.References: #2870

misc

• [bug] [ext]

Fixed bug which prevented the serializer extension from workingcorrectly with table or column names that contain non-ASCIIcharacters.References: #2869

0.8.3

Released: October 26, 2013

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

References: #2836

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

References: #2807

• [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.References: #2813

• [orm] [bug]

Fixed bug where Query.exists() failed to work correctlywithout any WHERE criterion. Courtesy Vladimir Magamedov.References: #2818

• [orm] [bug]

Backported a change from 0.9 whereby the iteration of a hierarchyof mappers used in polymorphic inheritance loads is sorted,which allows the SELECT statements generated for polymorphic queriesto have deterministic rendering, which in turn helps with cachingschemes that cache on the SQL string itself.References: #2779

• [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.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.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).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.References: #2773

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.

engine

• [engine] [feature]

repr() for the URL of an Enginewill now conceal the password using asterisks.Courtesy Gunnlaugur Þór Briem.References: #2821

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

References: #2851

• [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.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.References: #2772

sql

• [sql] [feature]

Added new method to the insert() constructInsert.from_select(). Given a list of columns anda selectable, renders INSERT INTO (table) (columns) SELECT ...References: #722

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

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

References: #2784

• [sql] [bug]

Fixed bug where type_coerce() would not interpret ORMelements with a clause_element() method properly.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.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.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.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.References: #2815

• [sql] [bug]

Non-working “schema” argument on ForeignKey is deprecated;raises a warning. Removed in 0.9.References: #2831

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

• [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.References: #2780

postgresql

• [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.References: #2844

• [postgresql] [bug]

Parenthesis will be applied to a compound SQL expression asrendered in the column list of a CREATE INDEX statement.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.References: #2819

mysql

• [mysql] [bug]

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

References: #2791

• [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.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”.References: #2515

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.References: #2781

oracle

• [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.References: #2853

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.

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

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

0.8.2

Released: July 3, 2013

orm

• [orm] [feature]

Added a new method Query.select_entity_from() whichwill in 0.9 replace part of the functionality ofQuery.select_from(). In 0.8, the two methods performthe same function, so that code can be migrated to use theQuery.select_entity_from() method as appropriate.See the 0.9 migration guide for details.References: #2736

• [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.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.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.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.References: #2755

• [orm] [bug] [ext]

Fixed bug where MutableDict didn’t report a change eventwhen clear() was called.References: #2730

• [orm] [bug]

Fixed a regression caused by #2682 whereby theevaluation invoked by Query.update() and Query.delete()would hit upon unsupported True and False symbolswhich now appear due to the usage of IS.References: #2737

• [orm] [bug]

Fixed a regression from 0.7 caused by this ticket, whichmade the check for recursion overflow in self-referentialeager joining too loose, missing a particular circumstancewhere a subclass had lazy=”joined” or “subquery” configuredand the load was a “with_polymorphic” against the base.References: #2481

• [orm] [bug]

Fixed a regression from 0.7 where the contextmanager featureof Session.begin_nested() would fail to correctlyroll back the transaction when a flush error occurred, insteadraising its own exception while leaving the session stillpending a rollback.References: #2718

orm declarative

• [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.References: #2761

engine

• [engine] [bug]

Fixed bug where the reset_on_return argument to various Poolimplementations would not be propagated when the pool was regenerated.Courtesy Eevee.

• [engine] [bug] [sybase]

Fixed a bug where the routine to detect the correct kwargsbeing sent to create_engine() would fail in some cases,such as with the Sybase dialect.References: #2732

sql

• [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] [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.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.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.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!References: #2726

• [sql] [bug] [mssql]

Regression from this ticket caused the unsupported keyword“true” to render, added logic to convert this to 1/0for SQL server.References: #2682

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.

• [postgresql] [feature]

Added support for “AUTOCOMMIT” isolation when using the psycopg2DBAPI. The keyword is available via the isolation_levelexecution option. Patch courtesy Roman Podolyaka.References: #2072

• [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.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.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.References: #2767

• [postgresql] [bug]

Fixed the HSTORE type to correctly encode/decode for unicode.This is always on, as the hstore is a textual type, andmatches the behavior of psycopg2 when using Python 3.Courtesy Dmitry Mugtasimov.References: #2735

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.References: #2704

• [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.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.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.References: #2721

• [mysql] [bug]

Updated mysqlconnector dialect to check for disconnect basedon the apparent string message sent in the exception; testedagainst mysqlconnector 1.0.9.

sqlite

• [sqlite] [bug]

Added sqlalchemy.types.BIGINT to the list of type names that can bereflected by the SQLite dialect; courtesy Russell Stuart.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.References: #2747

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.References: #2763

• [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.References: #2757

misc

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

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

• [bug] [examples]

Fixed a small bug in the dogpile example where the generationof SQL cache keys wasn’t applying deduping labels to thestatement the same way Query normally does.

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

0.8.1

Released: April 27, 2013

orm

• [orm] [feature]

Added a convenience method to Query that turns a query into anEXISTS subquery of the formEXISTS (SELECT 1 FROM … WHERE …).References: #2673

• [orm] [bug]

Fixed bug when a query of the form:query(SubClass).options(subqueryload(Baseclass.attrname)),where SubClass is a joined inh of BaseClass,would fail to apply the JOIN inside the subqueryon the attribute load, producing a cartesian product.The populated results still tended to be correct as additionalrows are just ignored, so this issue may be present as aperformance degradation in applications that areotherwise working correctly.This change is also backported to: 0.7.11

References: #2699

• [orm] [bug]

Fixed bug in unit of work whereby a joined-inheritancesubclass could insert the row for the “sub” tablebefore the parent table, if the two tables had noForeignKey constraints set up between them.This change is also backported to: 0.7.11

References: #2689

• [orm] [bug]

Fixes to the sqlalchemy.ext.serializer extension, includingthat the “id” passed from the pickler is turned into a stringto prevent against bytes being parsed on Py3K, as well as thatrelationship() and orm.join() constructs are now properlyserialized.References: #2698

• [orm] [bug]

A significant improvement to the inner workings of query.join(),such that the decisionmaking involved on how to join has beendramatically simplified. New test cases now pass such asmultiple joins extending from the middle of an already complexseries of joins involving inheritance and such. Joining fromdeeply nested subquery structures is still complicated andnot without caveats, but with these improvements the edgecases are hopefully pushed even farther out to the edges.References: #2714

• [orm] [bug]

Added a conditional to the unpickling process for ORMmapped objects, such that if the reference to the objectwere lost when the object was pickled, we don’terroneously try to set up _sa_instance_state - fixesa NoneType error.

• [orm] [bug]

Fixed bug where many-to-many relationship with uselist=Falsewould fail to delete the association row and raise an errorif the scalar attribute were set to None. This was aregression introduced by the changes for #2229.References: #2710

• [orm] [bug]

Improved the behavior of instance management regardingthe creation of strong references within the Session;an object will no longer have an internal reference cyclecreated if it’s in the transient state or moves into thedetached state - the strong ref is created only when theobject is attached to a Session and is removed when theobject is detached. This makes it somewhat safer for anobject to have a _del() method, even though this isnot recommended, as relationships with backrefs producecycles too. A warning has been added when a class witha _del() method is mapped.References: #2708

• [orm] [bug]

Fixed bug whereby ORM would run the wrong kind ofquery when refreshing an inheritance-mapped classwhere the superclass was mapped to a non-Tableobject, like a custom join() or a select(),running a query that assumed a hierarchy that’smapped to individual Table-per-class.References: #2697

• [orm] [bug]

Fixed _repr()_ on mapper property constructsto work before the object is initialized, sothat Sphinx builds with recent Sphinx versionscan read them.

orm declarative

• [bug] [declarative] [orm]

Fixed indirect regression regarding has_inherited_table(),where since it considers the current class’ table, wassensitive to when it was called. This is 0.7’s behavior also,but in 0.7 things tended to “work out” within events likemapper_args(). has_inherited_table() now onlyconsiders superclasses, so should return the same answerregarding the current class no matter when it’s called(obviously assuming the state of the superclass).References: #2656

sql

• [sql] [feature]

Loosened the check on dialect-specific argument namespassed to Table(); since we want to support external dialectsand also want to support args without a certain dialectbeing installed, it only checks the format of the arg now,rather than looking for that dialect in sqlalchemy.dialects.

• [sql] [bug] [mysql]

Fully implemented the IS and IS NOT operators withregards to the True/False constants. An expression likecol.is(True) will now render col IS trueon the target platform, rather than converting the True/False constant to an integer bound parameter.This allows the is() operator to work on MySQL whengiven True/False constants.References: #2682

• [sql] [bug]

A major fix to the way in which a select() object produceslabeled columns when applylabels() is used; this modeproduces a SELECT where each column is labeled as in, to remove column name collisionsfor a multiple table select. The fix is that if two labelscollide when combined with the table name, i.e.“foo.bar_id” and “foo_bar.id”, anonymous aliasing will beapplied to one of the dupes. This allows the ORM to handleboth columns independently; previously, 0.7would in some cases silently emit a second SELECT for thecolumn that was “duped”, and in 0.8 an ambiguous column errorwould be emitted. The “keys” applied to the .c. collectionof the select() will also be deduped, so that the “columnbeing replaced” warning will no longer emit for any select()that specifies use_labels, though the dupe key will be givenan anonymous label which isn’t generally user-friendly.References: #2702

• [sql] [bug]

Fixed bug where disconnect detect on error wouldraise an attribute error if the error were beingraised after the Connection object had alreadybeen closed.References: #2691

• [sql] [bug]

Reworked internal exception raises that emita rollback() before re-raising, so that the stacktrace is preserved from sys.exc_info() before enteringthe rollback. This so that the traceback is preservedwhen using coroutine frameworks which may have switchedcontexts before the rollback function returns.References: #2703

• [sql] [bug] [postgresql]

The _Binary base type now converts values throughthe bytes() callable when run on Python 3; in particularpsycopg2 2.5 with Python 3.3 seems to now be returningthe “memoryview” type, so this is converted to bytesbefore return.

• [sql] [bug]

Improvements to Connection auto-invalidationhandling. If a non-disconnect error occurs,but leads to a delayed disconnect error within errorhandling (happens with MySQL), the disconnect conditionis detected. The Connection can now also be closedwhen in an invalid state, meaning it will raise “closed”on next usage, and additionally the “close with result”feature will work even if the autorollback in an errorhandling routine fails and regardless of whether thecondition is a disconnect or not.References: #2695

• [sql] [bug]

Fixed bug whereby a DBAPI that can return “0”for cursor.lastrowid would not function correctlyin conjunction with ResultProxy.inserted_primary_key.

postgresql

• [postgresql] [bug]

Opened up the checking for “disconnect” with psycopg2/libpqto check for all the various “disconnect” messages withinthe full exception hierarchy. Specifically the“closed the connection unexpectedly” message has now beenseen in at least three different exception types.Courtesy Eli Collins.References: #2712

• [postgresql] [bug]

The operators for the PostgreSQL ARRAY type supportsinput types of sets, generators, etc. even whena dimension is not specified, by turning the giveniterable into a collection unconditionally.References: #2681

• [postgresql] [bug]

Added missing HSTORE type to postgresql type namesso that the type can be reflected.References: #2680

mysql

• [mysql] [bug]

Fixes to support the latest cymysql DBAPI, courtesyHajime Nakagami.

• [mysql] [bug]

Improvements to the operation of the pymysql dialect onPython 3, including some important decode/bytes steps.Issues remain with BLOB types due to driver issues.Courtesy Ben Trofatter.References: #2663

• [mysql] [bug]

Updated a regexp to correctly extract error code ongoogle app engine v1.7.5 and newer. CourtesyDan Ring.

mssql

• [mssql] [bug]

Part of a longer series of fixes needed for pyodbc+mssql, a CAST to NVARCHAR(max) has been added to the boundparameter for the table name and schema name in all information schemaqueries to avoid the issue of comparing NVARCHAR to NTEXT,which seems to be rejected by the ODBC driver in some cases,such as FreeTDS (0.91 only?) plus unicode bound parameters being passed.The issue seems to be specific to the SQL Server informationschema tables and the workaround is harmless for those caseswhere the problem doesn’t exist in the first place.References: #2355

• [mssql] [bug]

Added support for additional “disconnect” messagesto the pymssql dialect. Courtesy John Anderson.

• [mssql] [bug]

Fixed Py3K bug regarding “binary” types andpymssql. Courtesy Marc Abramowitz.References: #2683

misc

• [bug] [examples]

Fixed a long-standing bug in the caching example, wherethe limit/offset parameter values wouldn’t be taken intoaccount when computing the cache key. The_key_from_query() function has been simplified to workdirectly from the final compiled statement in order to getat both the full statement as well as the fully processedparameter list.

0.8.0

Released: March 9, 2013

Note

There are some new behavioral changes as of 0.8.0not present in 0.8.0b2. They are present in themigration document as follows:

• The consideration of a “pending” object as an “orphan” has been made more aggressive

• create_all() and drop_all() will now honor an empty list as such

• Correlation is now always context-specific

orm

• [orm] [feature]

A meaningful QueryableAttribute.info attribute isadded, which proxies down to the .info attribute on eitherthe schema.Column object if directly present, orthe MapperProperty otherwise. The full behavioris documented and ensured by tests to remain stable.References: #2675

• [orm] [feature]

Can set/change the “cascade” attribute on a relationship()construct after it’s been constructed already. This is nota pattern for normal use but we like to change the settingfor demonstration purposes in tutorials.

• [orm] [feature]

Added new helper function was_deleted(), returns Trueif the given object was the subject of a Session.delete()operation.References: #2658

• [orm] [feature]

Extended the Runtime Inspection API system so that all Python descriptorsassociated with the ORM or its extensions can be retrieved.This fulfills the common request of being able to inspectall QueryableAttribute descriptors in addition toextension types such as hybrid_property andAssociationProxy. See Mapper.all_orm_descriptors.

• [orm] [removed]

The undocumented (and hopefully unused) system of producingcustom collections using an instrumentation datastructureassociated with the collection has been removed, as this was a complexand untested feature which was also essentially redundant versus thedecorator approach. Other internal simplifications to theorm.collections module have been made as well.

• [orm] [bug]

Improved checking for an existing backref name conflict duringmapper configuration; will now test for name conflicts onsuperclasses and subclasses, in addition to the current mapper,as these conflicts break things just as much. This is new for0.8, but see below for a warning that will also be triggeredin 0.7.11.References: #2674

• [orm] [bug]

Improved the error message emitted when a “backref loop” is detected,that is when an attribute event triggers a bidirectionalassignment between two other attributes with no end.This condition can occur not just when an object of the wrongtype is assigned, but also when an attribute is mis-configuredto backref into an existing backref pair. Also in 0.7.11.References: #2674

• [orm] [bug]

A warning is emitted when a MapperProperty is assigned to a mapperthat replaces an existing property, if the properties in questionaren’t plain column-based properties. Replacement of relationshipproperties is rarely (ever?) what is intended and usually refers to amapper mis-configuration. Also in 0.7.11.References: #2674

• [orm] [bug]

A clear error message is emitted if an event handlerattempts to emit SQL on a Session within the after_commit()handler, where there is not a viable transaction in progress.References: #2662

• [orm] [bug]

Detection of a primary key change within the processof cascading a natural primary key update will succeedeven if the key is composite and only some of theattributes have changed.References: #2665

• [orm] [bug]

An object that’s deleted from a session will be de-associated withthat session fully after the transaction is committed, that isthe object_session() function will return None.References: #2658

• [orm] [bug]

Fixed bug whereby Query.yield_per() would set the executionoptions incorrectly, thereby breaking subsequent usage of theQuery.execution_options() method. Courtesy Ryan Kelly.References: #2661

• [orm] [bug]

Fixed the consideration of the between() operatorso that it works correctly with the new relationship local/remotesystem.References: #1768

• [orm] [bug]

the consideration of a pending object asan “orphan” has been modified to more closely match thebehavior as that of persistent objects, which is that the objectis expunged from the Session as soon as it isde-associated from any of its orphan-enabled parents. Previously,the pending object would be expunged only if de-associatedfrom all of its orphan-enabled parents. The new flag legacy_is_orphanis added to orm.mapper() which re-establishes thelegacy behavior.

See the change note and example case at The consideration of a “pending” object as an “orphan” has been made more aggressivefor a detailed discussion of this change.References: #2655

• [orm] [bug]

Fixed the (most likely never used) “@collection.link” collectionmethod, which fires off each time the collection is associatedor de-associated with a mapped object - the decoratorwas not tested or functional. The decorator methodis now named collection.linker() though the name “link”remains for backwards compatibility. Courtesy Luca Wehrstedt.References: #2653

• [orm] [bug]

Made some fixes to the system of producing custom instrumentedcollections, mainly that the usage of the @collection decoratorswill now honor the mro of the given class, applying thelogic of the sub-most classes’ version of a particular collectionmethod. Previously, it wasn’t predictable when subclassingan existing instrumented class such as MappedCollectionwhether or not custom methods would resolve correctly.References: #2654

• [orm] [bug]

Fixed potential memory leak which could occur if anarbitrary number of sessionmaker objectswere created. The anonymous subclass created bythe sessionmaker, when dereferenced, would not be garbagecollected due to remaining class-level references from theevent package. This issue also applies to any custom systemthat made use of ad-hoc subclasses in conjunction withan event dispatcher. Also in 0.7.10.References: #2650

• [orm] [bug]

Query.merge_result() can now load rows from an outer joinwhere an entity may be None without throwing an error.Also in 0.7.10.References: #2640

• [orm] [bug]

Fixes to the “dynamic” loader on relationship(), includesthat backrefs will work properly even when autoflush is disabled,history events are more accurate in scenarios where multiple add/removeof the same object occurs.References: #2637

sql

• [sql] [feature]

Added a new argument to Enum and its baseSchemaType inherit_schema. When set to True,the type will set its schema attribute of that of theTable to which it is associated. This also occursduring a Table.tometadata() operation; the SchemaTypeis now copied in all cases when Table.tometadata() happens,and if inherit_schema=True, the type will take on the newschema name passed to the method. The schema is importantwhen used with the PostgreSQL backend, as the type results ina CREATE TYPE statement.References: #2657

• [sql] [feature]

Index now supports arbitrary SQL expressions and/orfunctions, in addition to straight columns. Common modifiersinclude using somecolumn.desc() for a descending index andfunc.lower(somecolumn) for a case-insensitive index, depending on thecapabilities of the target backend.References: #695

• [sql] [bug]

The behavior of SELECT correlation has been improved such thatthe Select.correlate() and Select.correlate_except()methods, as well as their ORM analogues, will still retain“auto-correlation” behavior in that the FROM clause is modifiedonly if the output would be legal SQL; that is, the FROM clauseis left intact if the correlated SELECT is not used in the contextof an enclosing SELECT inside of the WHERE, columns, or HAVING clause.The two methods now only specify conditions to the default“auto correlation”, rather than absolute FROM lists.References: #2668

• [sql] [bug]

Fixed a bug regarding column annotations which in particularcould impact some usages of the new orm.remote() andorm.local() annotation functions, where annotationscould be lost when the column were used in a subsequentexpression.References: #1768, #2660

• [sql] [bug]

The ColumnOperators.in_() operator will now coercevalues of None to null().References: #2496

• [sql] [bug]

Fixed bug where Table.tometadata() would fail if aColumn had both a foreign key as well as analternate “.key” name for the column. Also in 0.7.10.References: #2643

• [sql] [bug]

insert().returning() raises an informative CompileError if attemptedto compile on a dialect that doesn’t support RETURNING.References: #2629

• [sql] [bug]

Tweaked the “REQUIRED” symbol used by the compiler to identifyINSERT/UPDATE bound parameters that need to be passed, so thatit’s more easily identifiable when writing custom bind-handlingcode.References: #2648

schema

• [schema] [bug]

MetaData.create_all() and MetaData.drop_all() willnow accommodate an empty list as an instruction to not create/dropany items, rather than ignoring the collection.References: #2664

postgresql

• [postgresql] [feature]

Added support for PostgreSQL’s traditional SUBSTRINGfunction syntax, renders as “SUBSTRING(x FROM y FOR z)”when regular func.substring() is used.Courtesy Gunnlaugur Þór Briem.This change is also backported to: 0.7.11

References: #2676

• [postgresql] [feature]

Added postgresql.ARRAY.Comparator.any() andpostgresql.ARRAY.Comparator.all()methods, as well as standalone expression constructs. Big thanksto Audrius Kažukauskas for the terrific work here.

• [postgresql] [bug]

Fixed bug in array() construct whereby using itinside of an expression.insert() construct would produce anerror regarding a parameter issue in the self_group() method.

mysql

• [mysql] [feature]

New dialect for CyMySQL added, courtesy Hajime Nakagami.

• [mysql] [feature]

GAE dialect now accepts username/password arguments in the URL,courtesy Owen Nelson.

• [mysql] [bug] [gae]

Added a conditional import to the gaerdbms dialect which attemptsto import rdbms_apiproxy vs. rdbms_googleapi to workon both dev and production platforms. Also now honors theinstance attribute. Courtesy Sean Lynch.Also in 0.7.10.References: #2649

• [mysql] [bug]

GAE dialect won’t fail on None match if the error code can’t be extractedfrom the exception throw; courtesy Owen Nelson.

mssql

• [mssql] [feature]

Added mssql_include and mssql_clustered options toIndex, renders the INCLUDE and CLUSTERED keywords,respectively. Courtesy Derek Harland.

• [mssql] [feature]

DDL for IDENTITY columns is now supported onnon-primary key columns, by establishing aSequence construct on anyinteger column. Courtesy Derek Harland.References: #2644

• [mssql] [bug]

Added a py3K conditional around unnecessary .decode()call in mssql information schema, fixes reflectionin Py3K. Also in 0.7.10.References: #2638

• [mssql] [bug]

Fixed a regression whereby the “collation” parameterof the character types CHAR, NCHAR, etc. stopped working,as “collation” is now supported by the base string types.The TEXT, NCHAR, CHAR, VARCHAR types within theMSSQL dialect are now synonyms for the base types.

oracle

• [oracle] [bug]

The cx_oracle dialect will no longer run the bind parameter namesthrough encode(), as this is not valid on Python 3, and preventedstatements from functioning correctly on Python 3. We nowencode only if supports_unicode_binds is False, which is notthe case for cx_oracle when at least version 5 of cx_oracle is used.

misc

• [bug] [tests]

Fixed an import of “logging” in test_execute which was notworking on some linux platforms. Also in 0.7.11.References: #2669

• [bug] [examples]

Fixed a regression in the examples/dogpile_caching examplewhich was due to the change in #2614.

0.8.0b2

Released: December 14, 2012

orm

• [orm] [feature]

Added KeyedTuple._asdict() and KeyedTuple._fieldsto the KeyedTuple class to provide some degree of compatibilitywith the Python standard library collections.namedtuple().References: #2601

• [orm] [feature]

Allow synonyms to be used when defining primary and secondaryjoins for relationships.

• [orm] [feature] [extensions]

The sqlalchemy.ext.mutable extension now includes theexample MutableDict class as part of the extension.

• [orm] [bug]

The Query.select_from() method can now be used with aaliased() construct without it interfering with the entitiesbeing selected. Basically, a statement like this:

ua = aliased(User)
session.query(User.name).select_from(ua).join(User, User.name > ua.name)

Will maintain the columns clause of the SELECT as coming from theunaliased “user”, as specified; the select_from only takes place in theFROM clause:

SELECT users.name AS users_name FROM users AS users_1
JOIN users ON users.name < users_1.name

Note that this behavior is in contrastto the original, older use case for Query.select_from(), which is thatof restating the mapped entity in terms of a different selectable:

session.query(User.name).\
  select_from(user_table.select().where(user_table.c.id > 5))

Which produces:

SELECT anon_1.name AS anon_1_name FROM (SELECT users.id AS id,
users.name AS name FROM users WHERE users.id > :id_1) AS anon_1

It was the “aliasing” behavior of the latter use case that wasgetting in the way of the former use case. The method nowspecifically considers a SQL expression likeexpression.select() or expression.alias()separately from a mapped entity like a aliased()construct.References: #2635

• [orm] [bug]

The MutableComposite type did not allow for theMutableBase.coerce() method to be used, even thoughthe code seemed to indicate this intent, so this now worksand a brief example is added. As a side-effect,the mechanics of this event handler have been changed so thatnew MutableComposite types no longer add per-typeglobal event handlers. Also in 0.7.10.References: #2624

• [orm] [bug]

A second overhaul of aliasing/internal pathing mechanicsnow allows two subclasses to have different relationshipsof the same name, supported with subquery or joined eagerloading on both simultaneously when a full polymorphicload is used.References: #2614

• [orm] [bug]

Fixed bug whereby a multi-hop subqueryload withina particular with_polymorphic load would produce a KeyError.Takes advantage of the same internal pathing overhaulas #2614.References: #2617

• [orm] [bug]

Fixed regression where query.update() would producean error if an object matched by the “fetch”synchronization strategy wasn’t locally present.Courtesy Scott Torborg.References: #2602

engine

• [engine] [feature]

The Connection.connect() and Connection.contextual_connect()methods now return a “branched” version so that the Connection.close()method can be called on the returned connection without affecting theoriginal. Allows symmetry when using Engine andConnection objects as context managers:

with conn.connect() as c: # leaves the Connection open
  c.execute("...")
 
with engine.connect() as c:  # closes the Connection
  c.execute("...")

• [engine] [bug]

Fixed MetaData.reflect() to correctly usethe given Connection, if given, withoutopening a second connection from that connection’sEngine.This change is also backported to: 0.7.10

References: #2604

• [engine]

The “reflect=True” argument to MetaData is deprecated.Please use the MetaData.reflect() method.

sql

• [sql] [feature]

The Insert construct now supports multi-valued inserts,that is, an INSERT that renders like“INSERT INTO table VALUES (…), (…), …”.Supported by PostgreSQL, SQLite, and MySQL.Big thanks to Idan Kamara for doing the legwork on this one.

See also

Multiple-VALUES support for Insert

References: #2623

• [sql] [bug]

Fixed bug where using server_onupdate=without passing the “for_update=True” flag would apply the defaultobject to the server_default, blowing away whatever was there.The explicit for_update=True argument shouldn’t be needed with this usage(especially since the documentation shows an example without it beingused) so it is now arranged internally using a copy of the given defaultobject, if the flag isn’t set to what corresponds to that argument.This change is also backported to: 0.7.10

References: #2631

• [sql] [bug]

Fixed a regression caused by #2410 whereby aCheckConstraint would apply itself back to theoriginal table during a Table.tometadata() operation, asit would parse the SQL expression for a parent table. Theoperation now copies the given expression to correspond to thenew table.References: #2633

• [sql] [bug]

Fixed bug whereby using a label_length on dialect that was smallerthan the size of actual column identifiers would fail to renderthe columns correctly in a SELECT statement.References: #2610

• [sql] [bug]

The DECIMAL type now honors the “precision” and“scale” arguments when rendering DDL.References: #2618

• [sql] [bug]

Made an adjustment to the “boolean”, (i.e. nonzero)evaluation of binary expressions, i.e. x1 == x2, suchthat the “auto-grouping” applied by BinaryExpressionin some cases won’t get in the way of this comparison.Previously, an expression like:

expr1 = mycolumn > 2
bool(expr1 == expr1)

Would evaluate as False, even though this is an identitycomparison, because mycolumn > 2 would be “grouped” beforebeing placed into the BinaryExpression, thus changingits identity. BinaryExpression now keeps trackof the “original” objects passed in.Additionally the nonzero method now only returns ifthe operator is == or != - all others raise TypeError.References: #2621

• [sql] [bug]

Fixed a gotcha where inadvertently calling list() on aColumnElement would go into an endless loop, ifColumnOperators.getitem() were implemented.A new NotImplementedError is emitted via iter().

• [sql] [bug]

Fixed bug in type_coerce() whereby typing informationcould be lost if the statement were used as a subqueryinside of another statement, as well as other similarsituations. Among other things, would causetyping information to be lost when the Oracle/mssql dialectswould apply limit/offset wrappings.References: #2603

• [sql] [bug]

Fixed bug whereby the “.key” of a Column wasn’t beingused when producing a “proxy” of the column againsta selectable. This probably didn’t occur in 0.7since 0.7 doesn’t respect the “.key” in a widerrange of scenarios.References: #2597

postgresql

• [postgresql] [feature]

HSTORE is now available in the PostgreSQL dialect.Will also use psycopg2’s extensions if available. CourtesyAudrius Kažukauskas.References: #2606

sqlite

• [sqlite] [bug]

More adjustment to this SQLite related issue which was released in0.7.9, to intercept legacy SQLite quoting characters when reflectingforeign keys. In addition to intercepting double quotes, otherquoting characters such as brackets, backticks, and single quotesare now also intercepted.This change is also backported to: 0.7.10

References: #2568

mssql

• [mssql] [feature]

Support for reflection of the “name” of primary keyconstraints added, courtesy Dave Moore.References: #2600

• [mssql] [bug]

Fixed bug whereby using “key” with Columnin conjunction with “schema” for the owningTable would fail to locate result rows dueto the MSSQL dialect’s “schema rendering”logic’s failure to take .key into account.This change is also backported to: 0.7.10

oracle

• [oracle] [bug]

Fixed table reflection for Oracle when accessing a synonym that refersto a DBLINK remote database; while the syntax has been present in theOracle dialect for some time, up until now it has never been tested.The syntax has been tested against a sample database linking to itself,however there’s still some uncertainty as to what should be used for the“owner” when querying the remote database for table information.Currently, the value of “username” from user_db_links is used tomatch the “owner”.References: #2619

• [oracle] [bug]

The Oracle LONG type, while an unbounded text type, does not appearto use the cx_Oracle.LOB type when result rows are returned,so the dialect has been repaired to exclude LONG fromhaving cx_Oracle.LOB filtering applied. Also in 0.7.10.References: #2620

• [oracle] [bug]

Repaired the usage of .prepare() in conjunction withcx_Oracle so that a return value of False will resultin no call to connection.commit(), hence avoiding“no transaction” errors. Two-phase transactions havenow been shown to work in a rudimental fashion withSQLAlchemy and cx_oracle, however are subject to caveatsobserved with the driver; check the documentationfor details. Also in 0.7.10.References: #2611

firebird

• [firebird] [bug]

Added missing import for “fdb” to the experimental“firebird+fdb” dialect.References: #2622

misc

• [feature] [sybase]

Reflection support has been added to the Sybase dialect.Big thanks to Ben Trofatter for all the work developing andtesting this.References: #1753

• [feature] [pool]

The Pool will now log all connection.close()operations equally, including closes which occur forinvalidated connections, detached connections, and connectionsbeyond the pool capacity.

• [feature] [pool]

The Pool now consults the Dialect forfunctionality regarding how the connection should be“auto rolled back”, as well as closed. This grants morecontrol of transaction scope to the dialect, so that wewill be better able to implement transactional workaroundslike those potentially needed for pysqlite and cx_oracle.References: #2611

• [feature] [pool]

Added new PoolEvents.reset() hook to capturethe event before a connection is auto-rolled back, uponreturn to the pool. Together withConnectionEvents.rollback() this allows all rollbackevents to be intercepted.

• [informix]

Some cruft regarding informix transaction handling has beenremoved, including a feature that would skip callingcommit()/rollback() as well as some hardcoded isolation levelassumptions on begin().. The status of this dialect is notwell understood as we don’t have any users working with it,nor any access to an Informix database. If someone withaccess to Informix wants to help test this dialect, pleaselet us know.

0.8.0b1

Released: October 30, 2012

general

• [general] [removed]

The “sqlalchemy.exceptions”synonym for “sqlalchemy.exc” is removedfully.References: #2433

• [general]

SQLAlchemy 0.8 now targets Python 2.5 andabove. Python 2.4 is no longer supported.

orm

• [orm] [feature]

Major rewrite of relationship()internals now allow join conditions whichinclude columns pointing to themselveswithin composite foreign keys. A newAPI for very specialized primaryjoin conditionsis added, allowing conditions based onSQL functions, CAST, etc. to be handledby placing the annotation functionsremote() and foreign() inline within theexpression when necessary. Previous recipesusing the semi-private _local_remote_pairsapproach can be upgraded to this newapproach.

See also

Rewritten relationship() mechanics

References: #1401

• [orm] [feature]

New standalone function with_polymorphic()provides the functionality of query.with_polymorphic()in a standalone form. It can be applied to anyentity within a query, including as the targetof a join in place of the “of_type()” modifier.References: #2333

• [orm] [feature]

The of_type() construct on attributesnow accepts aliased() class constructs as wellas with_polymorphic constructs, and works withquery.join(), any(), has(), and alsoeager loaders subqueryload(), joinedload(),contains_eager()References: #1106, #2438

• [orm] [feature]

Improvements to event listening formapped classes allows that unmapped classescan be specified for instance- and mapper-events.The established events will be automaticallyset up on subclasses of that class when thepropagate=True flag is passed, and theevents will be set up for that class itselfif and when it is ultimately mapped.References: #2585

• [orm] [feature]

The “deferred declarativereflection” system has been moved into thedeclarative extension itself, using thenew DeferredReflection class. Thisclass is now tested with both singleand joined table inheritance use cases.References: #2485

• [orm] [feature]

Added new core function “inspect()”,which serves as a generic gateway tointrospection into mappers, objects,others. The Mapper and InstanceStateobjects have been enhanced with a publicAPI that allows inspection of mappedattributes, including filters for column-boundor relationship-bound properties, inspectionof current object state, history ofattributes, etc.References: #2208

• [orm] [feature]

Calling rollback() within asession.begin_nested() will now only expirethose objects that had net changes within thescope of that transaction, that is objects whichwere dirty or were modified on a flush. Thisallows the typical use case for begin_nested(),that of altering a small subset of objects, toleave in place the data from the larger enclosingset of objects that weren’t modified inthat sub-transaction.References: #2452

• [orm] [feature]

Added utility featureSession.enable_relationship_loading(),supersedes relationship.load_on_pending.Both features should be avoided, however.References: #2372

• [orm] [feature]

Added support for .info dictionary argument tocolumn_property(), relationship(), composite().All MapperProperty classes have an auto-creating .infodict available overall.

• [orm] [feature]

Adding/removing None from a mapped collectionnow generates attribute events. Previously, a Noneappend would be ignored in some cases. Relatedto.References: #2229

• [orm] [feature]

The presence of None in a mapped collectionnow raises an error during flush. Previously,None values in collections would be silently ignored.References: #2229

• [orm] [feature]

The Query.update() method is nowmore lenient as to the tablebeing updated. Plain Table objects are bettersupported now, and additional a joined-inheritancesubclass may be used with update(); the subclasstable will be the target of the update,and if the parent table is referenced in theWHERE clause, the compiler will call uponUPDATE..FROM syntax as allowed by the dialectto satisfy the WHERE clause. MySQL’s multi-tableupdate feature is also supported if columnsare specified by object in the “values” dictionary.PG’s DELETE..USING is also not availablein Core yet.

• [orm] [feature]

New session events after_transaction_createand after_transaction_endallows tracking of new SessionTransaction objects.If the object is inspected, can be used to determinewhen a session first becomes active and whenit deactivates.

• [orm] [feature]

The Query can now load entity/scalar-mixed“tuple” rows that containtypes which aren’t hashable, by setting the flag“hashable=False” on the corresponding TypeEngine objectin use. Custom types that return unhashable types(typically lists) can set this flag to False.References: #2592

• [orm] [feature]

Query now “auto correlates” bydefault in the same way as select() does.Previously, a Query used as a subqueryin another would require the correlate()method be called explicitly in order tocorrelate a table on the inside to theoutside. As always, correlate(None)disables correlation.References: #2179

• [orm] [feature]

The after_attach event is nowemitted after the object is establishedin Session.new or Session.identity_mapupon Session.add(), Session.merge(),etc., so that the object is representedin these collections when the eventis called. Added before_attachevent to accommodate use cases thatneed autoflush w pre-attached object.References: #2464

• [orm] [feature]

The Session will produce warningswhen unsupported methods are used inside the“execute” portion of the flush. These arethe familiar methods add(), delete(), etc.as well as collection and related-objectmanipulations, as called within mapper-levelflush eventslike after_insert(), after_update(), etc.It’s been prominently documented for a longtime that SQLAlchemy cannot guaranteeresults when the Session is manipulated withinthe execution of the flush plan,however users are still doing it, so nowthere’s a warning. Maybe someday the Sessionwill be enhanced to support these operationsinside of the flush, but for now, resultscan’t be guaranteed.

• [orm] [feature]

ORM entities can be passedto the core select() construct as wellas to the select_from(),correlate(), and correlate_except()methods of select(), where they will be unwrappedinto selectables.References: #2245

• [orm] [feature]

Some support for auto-rendering of arelationship join condition based on the mappedattribute, with usage of core SQL constructs.E.g. select([SomeClass]).where(SomeClass.somerelationship)would render SELECT from “someclass” and use theprimaryjoin of “somerelationship” as the WHEREclause. This changes the previous meaningof “SomeClass.somerelationship” when used in acore SQL context; previously, it would “resolve”to the parent selectable, which wasn’t generallyuseful. Also works with query.filter().Related to.References: #2245

• [orm] [feature]

The registry of classesin declarativebase() is now aWeakValueDictionary. So subclasses of“Base” that are dereferenced will begarbage collected, _if they are notreferred to by any other mappers/superclassmappers. See the next note for this ticket.References: #2526

• [orm] [feature]

Conflicts between columns onsingle-inheritance declarative subclasses,with or without using a mixin, can be resolvedusing a new @declared_attr usage describedin the documentation.References: #2472

• [orm] [feature]

declared_attr can now be usedon non-mixin classes, even though this is generallyonly useful for single-inheritance subclasscolumn conflict resolution.References: #2472

• [orm] [feature]

declared_attr can now be used withattributes that are not Column or MapperProperty;including any user-defined value as wellas association proxy objects.References: #2517

• [orm] [feature]

Very limited support forinheriting mappers to be GC’ed when theclass itself is deferenced. The mappermust not have its own table (i.e.single table inh only) without polymorphicattributes in place.This allows for the use case ofcreating a temporary subclass of a declarativemapped class, with no table or mappingdirectives of its own, to be garbage collectedwhen dereferenced by a unit test.References: #2526

• [orm] [feature]

Declarative now maintains a registryof classes by string name as well as by fullmodule-qualified name. Multiple classes with thesame name can now be looked up based on a module-qualifiedstring within relationship(). Simple class namelookups where more than one class shares the samename now raises an informative error message.References: #2338

• [orm] [feature]

Can now provide class-bound attributesthat override columns which are of anynon-ORM type, not just descriptors.References: #2535

• [orm] [feature]

Added with_labels andreduce_columns keyword arguments toQuery.subquery(), to provide two alternatestrategies for producing queries with uniquely-named columns. .References: #1729

• [orm] [feature]

A warning is emitted when a referenceto an instrumented collection is no longerassociated with the parent class due toexpiration/attribute refresh/collectionreplacement, but an appendor remove operation is received on thenow-detached collection.References: #2476

• [orm] [removed]

The legacy “mutable” system of theORM, including the MutableType class as wellas the mutable=True flag on PickleTypeand postgresql.ARRAY has been removed.In-place mutations are detected by the ORMusing the sqlalchemy.ext.mutable extension,introduced in 0.7. The removal of MutableTypeand associated constructs removes a greatdeal of complexity from SQLAlchemy’s internals.The approach performed poorly as it would incura scan of the full contents of the Sessionwhen in use.References: #2442

• [orm] [removed]

Deprecated identifiers removed:

• allow_null_pks mapper() argument(use allow_partial_pks)

• _get_col_to_prop() mapper method(use get_property_by_column())

• dont_load argument to Session.merge()(use load=True)

• sqlalchemy.orm.shard module(use sqlalchemy.ext.horizontal_shard)

• [orm] [bug]

ORM will perform extra effort to determinethat an FK dependency between two tables isnot significant during flush if the tablesare related via joined inheritance and the FKdependency is not part of the inherit_condition,saves the user a use_alter directive.References: #2527

• [orm] [bug]

The instrumentation events class_instrument(),class_uninstrument(), and attribute_instrument()will now fire off only for descendant classesof the class assigned to listen(). Previously,an event listener would be assigned to listenfor all classes in all cases regardless of the“target” argument passed.References: #2590

• [orm] [bug]

with_polymorphic() produces JOINsin the correct order and with correct inheritingtables in the case of sending multi-levelsubclasses in an arbitrary order or withintermediary classes missing.References: #1900

• [orm] [bug]

Improvements to joined/subquery eagerloading dealing with chains of subclass entitiessharing a common base, with no specific “join depth”provided. Will chain out toeach subclass mapper individually before detectinga “cycle”, rather than considering the base classto be the source of the “cycle”.References: #2481

• [orm] [bug]

The “passive” flag on Session.is_modified()no longer has any effect. is_modified() inall cases looks only at local in-memorymodified flags and will not emit anySQL or invoke loader callables/initializers.References: #2320

• [orm] [bug]

The warning emitted when usingdelete-orphan cascade with one-to-manyor many-to-many without single-parent=Trueis now an error. The ORMwould fail to function subsequent to thiswarning in any case.References: #2405

• [orm] [bug]

Lazy loads emitted within flush eventssuch as before_flush(), before_update(),etc. will now function as they wouldwithin non-event code, regarding considerationof the PK/FK values used in the lazy-emittedquery. Previously,special flags would be established thatwould cause lazy loads to load related itemsbased on the “previous” value of theparent PK/FK values specifically when calledupon within a flush; the signal to loadin this way is now localized to where theunit of work actually needs to load thatway. Note that the UOW doessometimes load these collections beforethe before_update() event is called,so the usage of “passive_updates” or notcan affect whether or not a collection willrepresent the “old” or “new” data, whenaccessed within a flush event, basedon when the lazy load was emitted.The change is backwards incompatible inthe exceedingly small chance thatuser event code depended on the oldbehavior.References: #2350

• [orm] [bug]

Continuing regarding extrastate post-flush due to event listeners;any states that are marked as “dirty” from anattribute perspective, usually via column-attributeset events within after_insert(), after_update(),etc., will get the “history” flag resetin all cases, instead of only those instancesthat were part of the flush. This has the effectthat this “dirty” state doesn’t carry overafter the flush and won’t result in UPDATEstatements. A warning is emitted to thiseffect; the set_committed_state()method can be used to assign attributes on objectswithout producing history events.References: #2566, #2582

• [orm] [bug]

Fixed a disconnect that slowly evolvedbetween a @declared_attr Column and adirectly-defined Column on a mixin. In bothcases, the Column will be applied to thedeclared class’ table, but not to that of ajoined inheritance subclass. Previously,the directly-defined Column would be placedon both the base and the sub table, which isn’ttypically what’s desired.References: #2565

• [orm] [bug]

Declarative can now propagate a columndeclared on a single-table inheritance subclassup to the parent class’ table, when the parentclass is itself mapped to a join() or select()statement, directly or via joined inheritance,and not just a Table.References: #2549

• [orm] [bug]

An error is emitted when uselist=Falseis combined with a “dynamic” loader.This is a warning in 0.7.9.

• [orm] [moved]

The InstrumentationManager interfaceand the entire related system of alternateclass implementation is now moved outto sqlalchemy.ext.instrumentation. This isa seldom used system that adds significantcomplexity and overhead to the mechanics ofclass instrumentation. The new architectureallows it to remain unused untilInstrumentationManager is actually imported,at which point it is bootstrapped intothe core.

engine

• [engine] [feature]

Connection event listeners cannow be associated with individualConnection objects, not just Engineobjects.References: #2511

• [engine] [feature]

The before_cursor_execute eventfires off for so-called “_cursor_execute”events, which are usually special-caseexecutions of primary-key bound sequencesand default-generation SQLphrases that invoke separately when RETURNINGis not used with INSERT.References: #2459

• [engine] [feature]

The libraries used by the test suitehave been moved around a bit so that they arepart of the SQLAlchemy install again. In addition,a new suite of tests is present in thenew sqlalchemy.testing.suite package. This isan under-development system that hopes to providea universal testing suite for external dialects.Dialects which are maintained outside of SQLAlchemycan use the new test fixture as the frameworkfor their own tests, and will get for free a“compliance” suite of dialect-focused tests,including an improved “requirements” systemwhere specific capabilities and features canbe enabled or disabled for testing.

• [engine] [feature]

Added a new systemfor registration of new dialects in-processwithout using an entrypoint. See thedocs for “Registering New Dialects”.References: #2462

• [engine] [feature]

The “required” flag is set toTrue by default, if not passed explicitly,on bindparam() if the “value” or “callable”parameters are not passed.This will cause statement execution to checkfor the parameter being present in the finalcollection of bound parameters, rather thanimplicitly assigning None.References: #2556

• [engine] [feature]

Various API tweaks to the “dialect”API to better support highly specializedsystems such as the Akiban database, includingmore hooks to allow an execution context toaccess type processors.

• [engine] [feature]

Inspector.get_primary_keys() isdeprecated; use Inspector.get_pk_constraint().Courtesy Diana Clarke.References: #2422

• [engine] [feature]

New C extension module “utils” hasbeen added for additional function speedupsas we have time to implement.

• [engine] [bug]

The Inspector.get_table_names()order_by=”foreign_key” feature now sortstables by dependee first, to be consistentwith util.sort_tables and metadata.sorted_tables.

• [engine] [bug]

Fixed bug whereby if a database restartaffected multiple connections, eachconnection would individually invoke a newdisposal of the pool, even though onlyone disposal is needed.References: #2522

• [engine] [bug]

The names of the columns on the.c. attribute of a select().applylabels()is now based on insteadof _, for those columnsthat have a distinctly named .key.References: #2397

• [engine] [bug]

The autoload_replace flag on Table,when False, will cause any reflected foreign keyconstraints which refer to already-declaredcolumns to be skipped, assuming that thein-Python declared column will take overthe task of specifying in-Python ForeignKeyor ForeignKeyConstraint declarations.

• [engine] [bug]

The ResultProxy methods inserted_primary_key,last_updated_params(), last_inserted_params(),postfetch_cols(), prefetch_cols() allassert that the given statement is a compiledconstruct, and is an insert() or update()statement as is appropriate, elseraise InvalidRequestError.References: #2498

• [engine]

ResultProxy.last_inserted_ids is removed,replaced by inserted_primary_key.

sql

• [sql] [feature]

Added a new method Engine.execution_options()to Engine. This method works similarly toConnection.execution_options() in that it createsa copy of the parent object which will refer to the newset of options. The method can be used to buildsharding schemes where each engine shares the sameunderlying pool of connections. The methodhas been tested against the horizontal shardrecipe in the ORM as well.

See also

Engine.execution_options()

• [sql] [feature]

Major rework of operator systemin Core, to allow redefinition of existingoperators as well as addition of new operatorsat the type level. New types can be createdfrom existing ones which add or redefineoperations that are exported out to columnexpressions, in a similar manner to how theORM has allowed comparator_factory. The newarchitecture moves this capability into theCore so that it is consistently usable inall cases, propagating cleanly using existingtype propagation behavior.References: #2547

• [sql] [feature]

To complement, typescan now provide “bind expressions” and“column expressions” which allow compile-timeinjection of SQL expressions into statementson a per-column or per-bind level. This isto suit the use case of a type which needsto augment bind- and result- behavior at theSQL level, as opposed to in the Python level.Allows for schemes like transparent encryption/decryption, usage of PostGIS functions, etc.References: #1534, #2547

• [sql] [feature]

The Core operator system now includesthe getitem operator, i.e. the bracketoperator in Python. This is used at firstto provide index and slice behavior to thePostgreSQL ARRAY type, and also provides a hookfor end-user definition of custom getitemschemes which can be applied at the typelevel as well as within ORM-level customoperator schemes. lshift (<<)and rshift (>>) are also supported asoptional operators.

Note that this change has the effect thatdescriptor-based getitem schemes used bythe ORM in conjunction with synonym() or other“descriptor-wrapped” schemes will needto start using a custom comparator in orderto maintain this behavior.

• [sql] [feature]

Revised the rules used to determinethe operator precedence for the user-definedoperator, i.e. that granted using the op()method. Previously, the smallest precedencewas applied in all cases, now the defaultprecedence is zero, lower than all operatorsexcept “comma” (such as, used in the argumentlist of a func call) and “AS”, and isalso customizable via the “precedence” argumenton the op() method.References: #2537

• [sql] [feature]

Added “collation” parameter to allString types. When present, renders asCOLLATE . This to support theCOLLATE keyword now supported by severaldatabases including MySQL, SQLite, and PostgreSQL.References: #2276

• [sql] [feature]

Custom unary operators can now beused by combining operators.custom_op() withUnaryExpression().

• [sql] [feature]

Enhanced GenericFunction and func.to allow for user-defined GenericFunctionsubclasses to be available via the func.namespace automatically by classname,optionally using a package name, as wellas with the ability to have the renderedname different from the identified namein func.*.

• [sql] [feature]

The cast() and extract() constructswill now be produced via the func. accessoras well, as users naturally try to access thesenames from func. they might as well dowhat’s expected, even though the returnedobject is not a FunctionElement.References: #2562

• [sql] [feature]

The Inspector object can now beacquired using the new inspect() service,part ofReferences: #2208

• [sql] [feature]

The column_reflect event nowaccepts the Inspector object as the firstargument, preceding “table”. Code whichuses the 0.7 version of this very newevent will need modification to add the“inspector” object as the first argument.References: #2418

• [sql] [feature]

The behavior of column targetingin result sets is now case sensitive bydefault. SQLAlchemy for many years wouldrun a case-insensitive conversion on these values,probably to alleviate early case sensitivityissues with dialects like Oracle andFirebird. These issues have been more cleanlysolved in more modern versions so the performancehit of calling lower() on identifiers is removed.The case insensitive comparisons can be re-enabledby setting “case_insensitive=False” oncreate_engine().References: #2423

• [sql] [feature]

The “unconsumed column names” warning emittedwhen keys are present in insert.values() or update.values()that aren’t in the target table is now an exception.References: #2415

• [sql] [feature]

Added “MATCH” clause to ForeignKey,ForeignKeyConstraint, courtesy Ryan Kelly.References: #2502

• [sql] [feature]

Added support for DELETE and UPDATE froman alias of a table, which would assumedlybe related to itself elsewhere in the query,courtesy Ryan Kelly.References: #2507

• [sql] [feature]

select() features a correlate_except()method, auto correlates all selectables except thosepassed.

• [sql] [feature]

The prefix_with() method is now availableon each of select(), insert(), update(), delete(),all with the same API, accepting multipleprefix calls, as well as a “dialect name” so thatthe prefix can be limited to one kind of dialect.References: #2431

• [sql] [feature]

Added reduce_columns() methodto select() construct, replaces columns inlineusing the util.reduce_columns utility functionto remove equivalent columns. reduce_columns()also adds “with_only_synonyms” to limit thereduction just to those columns which have the samename. The deprecated fold_equivalents() feature isremoved.References: #1729

• [sql] [feature]

Reworked the startswith(), endswith(),contains() operators to do a better job withnegation (NOT LIKE), and also to assemble themat compilation time so that their rendered SQLcan be altered, such as in the case for FirebirdSTARTING WITHReferences: #2470

• [sql] [feature]

Added a hook to the system of renderingCREATE TABLE that provides access to the render for eachColumn individually, by constructing a @compilesfunction against the new schema.CreateColumnconstruct.References: #2463

• [sql] [feature]

“scalar” selects now have a WHERE methodto help with generative building. Also slight adjustmentregarding how SS “correlates” columns; the new methodologyno longer applies meaning to the underlyingTable column being selected. This improvessome fairly esoteric situations, and the logicthat was there didn’t seem to have any purpose.

• [sql] [feature]

An explicit error is raised whena ForeignKeyConstraint() that wasconstructed to refer to multiple remote tablesis first used.References: #2455

• [sql] [feature]

Added ColumnOperators.notin_(),ColumnOperators.notlike(),ColumnOperators.notilike() to ColumnOperators.References: #2580

• [sql] [changed]

Most classes in expression.sqlare no longer preceded with an underscore,i.e. Label, SelectBase, Generative, CompareMixin._BindParamClause is also renamed toBindParameter. The old underscore names forthese classes will remain available as synonymsfor the foreseeable future.

• [sql] [removed]

The long-deprecated and non-functional assert_unicode flag oncreate_engine() as well as String is removed.

• [sql] [bug]

Fixed bug where keyword arguments passed toCompiler.process() wouldn’t get propagatedto the column expressions present in the columnsclause of a SELECT statement. In particular this wouldcome up when used by custom compilation schemes thatrelied upon special flags.References: #2593

• [sql] [bug] [orm]

The auto-correlation feature of select(), andby proxy that of Query, will nottake effect for a SELECT statement that is beingrendered directly in the FROM list of the enclosingSELECT. Correlation in SQL only applies to columnexpressions such as those in the WHERE, ORDER BY,columns clause.References: #2595

• [sql] [bug]

A tweak to column precedence which moves the“concat” and “match” operators to be the same asthat of “is”, “like”, and others; this helps withparenthesization rendering when used in conjunctionwith “IS”.References: #2564

• [sql] [bug]

Applying a column expression to a selectstatement using a label with or without othermodifying constructs will no longer “target” thatexpression to the underlying Column; this affectsORM operations that rely upon Column targetingin order to retrieve results. That is, a querylike query(User.id, User.id.label(‘foo’)) will nowtrack the value of each “User.id” expression separatelyinstead of munging them together. It is not expectedthat any users will be impacted by this; however,a usage that uses select() in conjunction withquery.from_statement() and attempts to load fullycomposed ORM entities may not function as expectedif the select() named Column objects with arbitrary.label() names, as these will no longer target tothe Column objects mapped by that entity.References: #2591

• [sql] [bug]

Fixes to the interpretation of theColumn “default” parameter as a callableto not pass ExecutionContext into a keywordargument parameter.References: #2520

• [sql] [bug]

All of UniqueConstraint, ForeignKeyConstraint,CheckConstraint, and PrimaryKeyConstraint willattach themselves to their parent table automaticallywhen they refer to a Table-bound Column object directly(i.e. not just string column name), and refer toone and only one Table. Prior to 0.8 this behavioroccurred for UniqueConstraint and PrimaryKeyConstraint,but not ForeignKeyConstraint or CheckConstraint.References: #2410

• [sql] [bug]

TypeDecorator now includes a generic repr()that works in terms of the “impl” type by default.This is a behavioral change for those TypeDecoratorclasses that specify a custom init method; thosetypes will need to re-define repr() if they needrepr() to provide a faithful constructor representation.References: #2594

• [sql] [bug]

column.label(None) now produces ananonymous label, instead of returning thecolumn object itself, consistent with the behaviorof label(column, None).References: #2168

• [sql] [change]

The Text() type renders the lengthgiven to it, if a length was specified.

postgresql

• [postgresql] [feature]

postgresql.ARRAY features an optional“dimension” argument, will assign a specificnumber of dimensions to the array which willrender in DDL as ARRAY[][]…, also improvesperformance of bind/result processing.References: #2441

• [postgresql] [feature]

postgresql.ARRAY now supportsindexing and slicing. The Python [] operatoris available on all SQL expressions that areof type ARRAY; integer or simple slices can bepassed. The slices can also be used on theassignment side in the SET clause of an UPDATEstatement by passing them into Update.values();see the docs for examples.

• [postgresql] [feature]

Added new “array literal” constructpostgresql.array(). Basically a “tuple” thatrenders as ARRAY[1,2,3].

• [postgresql] [feature]

Added support for the PostgreSQL ONLYkeyword, which can appear corresponding to atable in a SELECT, UPDATE, or DELETE statement.The phrase is established using with_hint().Courtesy Ryan KellyReferences: #2506

• [postgresql] [feature]

The “ischema_names” dictionary of thePostgreSQL dialect is “unofficially” customizable.Meaning, new types such as PostGIS types canbe added into this dictionary, and the PG typereflection code should be able to handle simpletypes with variable numbers of arguments.The functionality here is “unofficial” forthree reasons:

• this is not an “official” API. Ideallyan “official” API would allow custom type-handlingcallables at the dialect or global levelin a generic way.

• This is only implemented for the PG dialect,in particular because PG has broad supportfor custom types vs. other database backends.A real API would be implemented at thedefault dialect level.

• The reflection code here is only tested againstsimple types and probably has issues with morecompositional types.

patch courtesy Éric Lemoine.

mysql

• [mysql] [feature]

Added TIME type to mysql dialect,accepts “fst” argument which is the new“fractional seconds” specifier for recentMySQL versions. The datatype will interpreta microseconds portion received from the driver,however note that at this time most/all MySQLDBAPIs do not support returning this value.References: #2534

• [mysql] [bug]

Dialect no longer emits expensive servercollations query, as well as server casing,on first connect. These functions are stillavailable as semi-private.References: #2404

sqlite

• [sqlite] [feature]

the SQLite date and time typeshave been overhauled to support a more openended format for input and output, usingname based format strings and regexps. Anew argument “microseconds” also providesthe option to omit the “microseconds”portion of timestamps. Thanks toNathan Wright for the work and tests onthis.References: #2363

• [sqlite]

Added types.NCHAR, types.NVARCHARto the SQLite dialect’s list of recognized type namesfor reflection. SQLite returns the name givento a type as the name returned.References: rc3addcc9ffad

mssql

• [mssql] [feature]

SQL Server dialect can be givendatabase-qualified schema names,i.e. “schema=’mydatabase.dbo’”; reflectionoperations will detect this, split the schemaamong the “.” to get the owner separately,and emit a “USE mydatabase” statement beforereflecting targets within the “dbo” owner;the existing database returned fromDB_NAME() is then restored.

• [mssql] [feature]

updated support for the mxodbcdriver; mxodbc 3.2.1 is recommended for fullcompatibility.

• [mssql] [bug]

removed legacy behavior wherebya column comparison to a scalar SELECT via== would coerce to an IN with the SQL serverdialect. This is implicitbehavior which fails in other scenariosso is removed. Code which relies on thisneeds to be modified to use column.in_(select)explicitly.References: #2277

oracle

• [oracle] [feature]

The types of columns excluded from thesetinputsizes() set can be customized by sendinga list of string DBAPI type names to exclude,using the exclude_setinputsizes dialect parameter.This list was previously fixed. The list alsonow defaults to STRING, UNICODE, removingCLOB, NCLOB from the list.References: #2561

• [oracle] [bug]

Quoting information is now passed alongfrom a Column with quote=True when generatinga same-named bound parameter to the bindparam()object, as is the case in generated INSERT and UPDATEstatements, so that unknown reserved names canbe fully supported.References: #2437

• [oracle] [bug]

The CreateIndex construct in Oraclewill now schema-qualify the name of the indexto be that of the parent table. Previously thisname was omitted which apparently creates theindex in the default schema, rather than thatof the table.

firebird

• [firebird] [feature]

The “startswith()” operator rendersas “STARTING WITH”, “~startswith()” rendersas “NOT STARTING WITH”, using FB’s more efficientoperator.References: #2470

• [firebird] [feature]

An experimental dialect for the fdbdriver is added, but is untested as I cannotget the fdb package to build.References: #2504

• [firebird] [bug]

CompileError is raised when VARCHAR withno length is attempted to be emitted, sameway as MySQL.References: #2505

• [firebird] [bug]

Firebird now uses strict “ansi bind rules”so that bound parameters don’t render in thecolumns clause of a statement - they renderliterally instead.

• [firebird] [bug]

Support for passing datetime as date whenusing the DateTime type with Firebird; otherdialects support this.

misc

• [feature] [access]

the MS Access dialect has beenmoved to its own project on Bitbucket,taking advantage of the new SQLAlchemydialect compliance suite. The dialect isstill in very rough shape and probably notready for general use yet, howeverit does have extremely rudimentalfunctionality now.https://bitbucket.org/zzzeek/sqlalchemy-access

• [moved] [maxdb]

The MaxDB dialect, which hasn’t beenfunctional for several years, ismoved out to a pending bitbucket project,https://bitbucket.org/zzzeek/sqlalchemy-maxdb.

• [examples]

The Beaker caching example has been convertedto use dogpile.cache.This is a new caching library written by the samecreator of Beaker’s caching internals, and represents avastly improved, simplified, and modernized system of caching.

See also

Dogpile Caching

References: #2589