1.2 Changelog

1.2.20

no release date

1.2.19

Released: April 15, 2019

orm

• [orm] [bug]

Fixed a regression in 1.2 due to the introduction of baked queries forrelationship lazy loaders, where a race condition is created during thegeneration of the “lazy clause” which occurs within a memoized attribute. Iftwo threads initialize the memoized attribute concurrently, the baked querycould be generated with bind parameter keys that are then replaced with newkeys by the next run, leading to a lazy load query that specifies therelated criteria as None. The fix establishes that the parameter namesare fixed before the new clause and parameter objects are generated, so thatthe names are the same every time.References: #4507

engine

• [engine] [bug]

Comparing two objects of URL using eq() did not take portnumber into consideration, two objects differing only by port number wereconsidered equal. Port comparison is now added in eq() method ofURL, objects differing by port number are now not equal.Additionally, ne() was not implemented for URL whichcaused unexpected result when != was used in Python2, since there are noimplied relationships among the comparison operators in Python2.References: #4406

mssql

• [mssql] [bug]

A commit() is emitted after an isolation level change to SNAPSHOT, as bothpyodbc and pymssql open an implicit transaction which blocks subsequent SQLfrom being emitted in the current transaction.References: #4536

oracle

• [oracle] [bug]

Added support for reflection of the NCHAR datatype to the Oracledialect, and added NCHAR to the list of types exported by theOracle dialect.References: #4506

misc

• [bug] [examples]

Fixed bug in large_resultsets example case where a re-named “id” variabledue to code reformatting caused the test to fail. Pull request courtesyMatt Schuchhardt.References: #4528

1.2.18

Released: February 15, 2019

orm

• [orm] [bug]

Fixed a regression in 1.2 where a wildcard/load_only loader option wouldnot work correctly against a loader path where of_type() were used to limitto a particular subclass. The fix only works for of_type() of a simplesubclass so far, not a with_polymorphic entity which will be addressed in aseparate issue; it is unlikely this latter case was working previously.References: #4468

• [orm] [bug]

Fixed fairly simple but critical issue where theSessionEvents.pending_to_persistent() event would be invoked forobjects not just when they move from pending to persistent, but when theywere also already persistent and just being updated, thus causing the eventto be invoked for all objects on every update.References: #4489

sql

• [sql] [bug]

Fixed issue where the JSON type had a read-onlyJSON.should_evaluate_none attribute, which would cause failureswhen making use of the TypeEngine.evaluates_none() method inconjunction with this type. Pull request courtesy Sanjana S.References: #4485

mysql

• [mysql] [bug]

Fixed a second regression caused by #4344 (the first was#4361), which works around MySQL issue 88718, where the lowercasing function used was not correct for Python 2 with OSX/Windows casingconventions, which would then raise TypeError. Full coverage has beenadded to this logic so that every codepath is exercised in a mock style forall three casing conventions on all versions of Python. MySQL 8.0 hasmeanwhile fixed issue 88718 so the workaround is only applies to aparticular span of MySQL 8.0 versions.References: #4492

sqlite

• [sqlite] [bug]

Fixed bug in SQLite DDL where using an expression as a server side defaultrequired that it be contained within parenthesis to be accepted by thesqlite parser. Pull request courtesy Bartlomiej Biernacki.References: #4474

mssql

• [mssql] [bug]

Fixed bug where the SQL Server “IDENTITY_INSERT” logic that allows an INSERTto proceed with an explicit value on an IDENTITY column was not detectingthe case where Insert.values() were used with a dictionary thatcontained a Column as key and a SQL expression as a value.References: #4499

1.2.17

Released: January 25, 2019

orm

• [orm] [feature]

Added new event hooks QueryEvents.before_compile_update() andQueryEvents.before_compile_delete() which complementQueryEvents.before_compile() in the case of the Query.update()and Query.delete() methods.References: #4461

• [orm] [bug]

Fixed issue where when using single-table inheritance in conjunction with ajoined inheritance hierarchy that uses “with polymorphic” loading, the“single table criteria” for that single-table entity could get confused forthat of other entities from the same hierarchy used in the same query.Theadaption of the “single table criteria” is made more specific to the targetentity to avoid it accidentally getting adapted to other tables in thequery.References: #4454

postgresql

• [postgresql] [bug]

Revised the query used when reflecting CHECK constraints to make use of thepg_get_constraintdef function, as the consrc column is beingdeprecated in PG 12. Thanks to John A Stevenson for the tip.References: #4463

oracle

• [oracle] [bug]

Fixed regression in integer precision logic due to the refactor of thecx_Oracle dialect in 1.2. We now no longer apply the cx_Oracle.NATIVE_INTtype to result columns sending integer values (detected as positiveprecision with scale ==0) which encounters integer overflow issues withvalues that go beyond the 32 bit boundary. Instead, the output variableis left untyped so that cx_Oracle can choose the best option.References: #4457

1.2.16

Released: January 11, 2019

engine

• [engine] [bug]

Fixed a regression introduced in version 1.2 where a refactorof the SQLAlchemyError base exception class introduced aninappropriate coercion of a plain string message into Unicode underpython 2k, which is not handled by the Python interpreter for charactersoutside of the platform’s encoding (typically ascii). TheSQLAlchemyError class now passes a bytestring through underPy2K for str() as is the behavior of exception objects in generalunder Py2K, does a safe coercion to unicode utf-8 withbackslash fallback for unicode(). For Py3K the message istypically unicode already, but if not is again safe-coerced with utf-8with backslash fallback for the str() method.References: #4429

sql

• [sql] [bug] [mysql] [oracle]

Fixed issue where the DDL emitted for DropTableComment, whichwill be used by an upcoming version of Alembic, was incorrect for the MySQLand Oracle databases.References: #4436

postgresql

• [postgresql] [bug]

Fixed issue where a postgresql.ENUM or a custom domain presentin a remote schema would not be recognized within column reflection ifthe name of the enum/domain or the name of the schema required quoting.A new parsing scheme now fully parses out quoted or non-quoted tokensincluding support for SQL-escaped quotes.References: #4416

• [postgresql] [bug]

Fixed issue where multiple postgresql.ENUM objects referred toby the same MetaData object would fail to be created ifmultiple objects had the same name under different schema names. Theinternal memoization the PostgreSQL dialect uses to track if it hascreated a particular postgresql.ENUM in the database duringa DDL creation sequence now takes schema name into account.

sqlite

• [sqlite] [bug]

Reflection of an index based on SQL expressions are now skipped with awarning, in the same way as that of the Postgresql dialect, where we currentlydo not support reflecting indexes that have SQL expressions within them.Previously, an index with columns of None were produced which would breaktools like Alembic.References: #4431

misc

Fixed issue in “expanding IN” feature where using the same bound parametername more than once in a query would lead to a KeyError within the processof rewriting the parameters in the query.References: #4394

1.2.15

Released: December 11, 2018

orm

• [orm] [bug]

Fixed bug where the ORM annotations could be incorrect for theprimaryjoin/secondaryjoin a relationship if one used the patternForeignKey(SomeClass.id) in the declarative mappings. This patternwould leak undesired annotations into the join conditions which can breakaliasing operations done within Query that are not supposed toimpact elements in that join condition. These annotations are now removedup front if present.References: #4367

• [orm] [bug]

In continuing with a similar theme as that of very recent #4349,repaired issue with RelationshipProperty.Comparator.any() andRelationshipProperty.Comparator.has() where the “secondary”selectable needs to be explicitly part of the FROM clause in theEXISTS subquery to suit the case where this “secondary” is a Joinobject.References: #4366

• [orm] [bug]

Fixed regression caused by #4349 where adding the “secondary”table to the FROM clause for a dynamic loader would affect the ability ofthe Query to make a subsequent join to another entity. The fixadds the primary entity as the first element of the FROM list sinceQuery.join() wants to jump from that. Version 1.3 will havea more comprehensive solution to this problem as well (#4365).References: #4363

• [orm] [bug]

Fixed bug where chaining of mapper options usingRelationshipProperty.of_type() in conjunction with a chained optionthat refers to an attribute name by string only would fail to locate theattribute.

orm declarative

• [bug] [declarative] [orm]

A warning is emitted in the case that a column() object is applied toa declarative class, as it seems likely this intended to be aColumn object.References: #4374

misc

Added support for the write_timeout flag accepted by mysqlclient andpymysql to be passed in the URL string.References: #4381

Fixed issue where reflection of a PostgreSQL domain that is expressed as anarray would fail to be recognized. Pull request courtesy Jakub Synowiec.References: #4377, #4380

1.2.14

Released: November 10, 2018

orm

• [orm] [bug]

Fixed bug in Session.bulk_update_mappings() where alternate mappedattribute names would result in the primary key column of the UPDATEstatement being included in the SET clause, as well as the WHERE clause;while usually harmless, for SQL Server this can raise an error due to theIDENTITY column. This is a continuation of the same bug that was fixed in#3849, where testing was insufficient to catch this additionalflaw.References: #4357

• [orm] [bug]

Fixed a minor performance issue which could in some cases add unnecessaryoverhead to result fetching, involving the use of ORM columns and entitiesthat include those same columns at the same time within a query. The issuehas to do with hash / eq overhead when referring to the column in differentways.References: #4347

mysql

• [mysql] [bug]

Fixed regression caused by #4344 released in 1.2.13, where the fixfor MySQL 8.0’s case sensitivity problem with referenced column names whenreflecting foreign key referents is worked around using theinformation_schema.columns view. The workaround was failing on OSX /lower_case_table_names=2 which produces non-matching casing for theinformation_schema.columns vs. that of SHOW CREATE TABLE, so incase-insensitive SQL modes case-insensitive matching is now used.References: #4361

1.2.13

Released: October 31, 2018

orm

• [orm] [bug]

Fixed bug where “dynamic” loader needs to explicitly set the “secondary”table in the FROM clause of the query, to suit the case where the secondaryis a join object that is otherwise not pulled into the query from itscolumns alone.References: #4349

orm declarative

• [bug] [declarative] [orm]

Fixed regression caused by #4326 in version 1.2.12 where usingdeclared_attr with a mixin in conjunction withorm.synonym() would fail to map the synonym properly to an inheritedsubclass.References: #4350

• [bug] [declarative] [orm]

The column conflict resolution technique discussed atResolving Column Conflicts is now functional for a Columnthat is also a primary key column. Previously, a check for primary keycolumns declared on a single-inheritance subclass would occur before thecolumn copy were allowed to pass.References: #4352

sql

• [sql] [feature]

Refactored SQLCompiler to expose aSQLCompiler.group_by_clause() method similar to theSQLCompiler.order_by_clause() and SQLCompiler.limit_clause()methods, which can be overridden by dialects to customize how GROUP BYrenders. Pull request courtesy Samuel Chou.

• [sql] [bug]

Fixed bug where the Enum.create_constraint flag on theEnum datatype would not be propagated to copies of the type, whichaffects use cases such as declarative mixins and abstract bases.References: #4341

postgresql

• [postgresql] [bug]

Added support for the aggregate_order_by function to receivemultiple ORDER BY elements, previously only a single element was accepted.References: #4337

mysql

• [mysql] [bug]

Added word function to the list of reserved words for MySQL, which isnow a keyword in MySQL 8.0References: #4348

• [mysql] [bug]

Added a workaround for a MySQL bug #88718 introduced in the 8.0 series,where the reflection of a foreign key constraint is not reporting thecorrect case sensitivity for the referred column, leading to errors duringuse of the reflected constraint such as when using the automap extension.The workaround emits an additional query to the information_schema tables inorder to retrieve the correct case sensitive name.References: #4344

misc

• [misc] [bug]

Fixed issue where part of the utility language helper internals was passingthe wrong kind of argument to the Python import builtin as the listof modules to be imported. The issue produced no symptoms within the corelibrary but could cause issues with external applications that redefine theimport builtin or otherwise instrument it. Pull request courtesy JoeUrciuoli.

• [misc] [bug] [py3k]

Fixed additional warnings generated by Python 3.7 due to changes in theorganization of the Python collections and collections.abc packages.Previous collections warnings were fixed in version 1.2.11. Pull requestcourtesy xtreak.References: #4339

• [bug] [ext]

Added missing .index() method to list-based association collectionsin the association proxy extension.

1.2.12

Released: September 19, 2018

orm

• [orm] [bug]

Added a check within the weakref cleanup for the InstanceStateobject to check for the presence of the dict builtin, in an effort toreduce error messages generated when these cleanups occur during interpretershutdown. Pull request courtesy Romuald Brunet.

• [orm] [bug]

Fixed bug where use of Lateral construct in conjunction withQuery.join() as well as Query.select_entity_from() would notapply clause adaption to the right side of the join. “lateral” introducesthe use case of the right side of a join being correlatable. Previously,adaptation of this clause wasn’t considered. Note that in 1.2 only,a selectable introduced by Query.subquery() is still not adapteddue to #4304; the selectable needs to be produced by theselect() function to be the right side of the “lateral” join.References: #4334

• [orm] [bug]

Fixed 1.2 regression caused by #3472 where the handling of an“updated_at” style column within the context of a post-update operationwould also occur for a row that is to be deleted following the update,meaning both that a column with a Python-side value generator would showthe now-deleted value that was emitted for the UPDATE before the DELETE(which was not the previous behavior), as well as that a SQL- emitted valuegenerator would have the attribute expired, meaning the previous valuewould be unreachable due to the row having been deleted and the objectdetached from the session.The “postfetch” logic that was added as part of#3472 is now skipped entirely for an object that ultimately is tobe deleted.References: #4327

orm declarative

• [bug] [declarative] [orm]

Fixed bug where the declarative scan for attributes would receive theexpression proxy delivered by a hybrid attribute at the class level, andnot the hybrid attribute itself, when receiving the descriptor via the@declared_attr callable on a subclass of an already-mapped class. Thiswould lead to an attribute that did not report itself as a hybrid whenviewed within Mapper.all_orm_descriptors.References: #4326

postgresql

• [postgresql] [bug]

Fixed bug in PostgreSQL dialect where compiler keyword arguments such asliteral_binds=True were not being propagated to a DISTINCT ONexpression.References: #4325

• [postgresql] [bug]

Fixed the postgresql.array_agg() function, which is a slightlyaltered version of the usual functions.array_agg() function, to alsoaccept an incoming “type” argument without forcing an ARRAY around it,essentially the same thing that was fixed for the generic function in 1.1in #4107.References: #4324

• [postgresql] [bug]

Fixed bug in PostgreSQL ENUM reflection where a case-sensitive, quoted namewould be reported by the query including quotes, which would not match atarget column during table reflection as the quotes needed to be strippedoff.References: #4323

oracle

• [oracle] [bug]

Fixed issue for cx_Oracle 7.0 where the behavior of Oracle param.getvalue()now returns a list, rather than a single scalar value, breakingautoincrement logic throughout the Core and ORM. The dml_ret_array_valcompatibility flag is used for cx_Oracle 6.3 and 6.4 to establish compatiblebehavior with 7.0 and forward, for cx_Oracle 6.2.1 and prior a versionnumber check falls back to the old logic.References: #4335

misc

• [bug] [ext]

Fixed issue where BakedQuery did not include the specific queryclass used by the Session as part of the cache key, leading toincompatibilities when using custom query classes, in particular theShardedQuery which has some different argument signatures.References: #4328

1.2.11

Released: August 20, 2018

orm declarative

• [bug] [declarative] [orm]

Fixed issue in previously untested use case, allowing a declarative mappedclass to inherit from a classically-mapped class outside of the declarativebase, including that it accommodates for unmapped intermediate classes. Anunmapped intermediate class may specify abstract, which is nowinterpreted correctly, or the intermediate class can remain unmarked, andthe classically mapped base class will be detected within the hierarchyregardless. In order to anticipate existing scenarios which may be mixingin classical mappings into existing declarative hierarchies, an error isnow raised if multiple mapped bases are detected for a given class.References: #4321

sql

• [sql] [bug]

Fixed issue that is closely related to #3639 where an expressionrendered in a boolean context on a non-native boolean backend wouldbe compared to 1/0 even though it is already an implicitly booleanexpression, when ColumnElement.self_group() were used. While thisdoes not affect the user-friendly backends (MySQL, SQLite) it was nothandled by Oracle (and possibly SQL Server). Whether or not theexpression is implicitly boolean on any database is now determinedup front as an additional check to not generate the integer comparisonwithin the compilation of the statement.References: #4320

• [sql] [bug]

Added missing window function parametersWithinGroup.over.range_ and WithinGroup.over.rowsparameters to the WithinGroup.over() andFunctionFilter.over() methods, to correspond to the range/rowsfeature added to the “over” method of SQL functions as part of#3049 in version 1.1.References: #4322

• [sql] [bug]

Fixed bug where the multi-table support for UPDATE and DELETE statementsdid not consider the additional FROM elements as targets for correlation,when a correlated SELECT were also combined with the statement. Thischange now includes that a SELECT statement in the WHERE clause for such astatement will try to auto-correlate back to these additional tables in theparent UPDATE/DELETE or unconditionally correlate ifSelect.correlate() is used. Note that auto-correlation raises anerror if the SELECT statement would have no FROM clauses as a result, whichcan now occur if the parent UPDATE/DELETE specifies the same tables in itsadditional set of tables; specify Select.correlate() explicitly toresolve.References: #4313

oracle

• [oracle] [bug]

For cx_Oracle, Integer datatypes will now be bound to “int”, per advicefrom the cx_Oracle developers. Previously, using cx_Oracle.NUMBER caused aloss in precision within the cx_Oracle 6.x series.References: #4309

misc

• [bug] [py3k]

Started importing “collections” from “collections.abc” under Python 3.3 andgreater for Python 3.8 compatibility. Pull request courtesy NathanielKnight.

Fixed issue where the “schema” name used for a SQLite database within tablereflection would not quote the schema name correctly. Pull requestcourtesy Phillip Cloud.

1.2.10

Released: July 13, 2018

orm

• [orm] [bug]

Fixed bug in Bundle construct where placing two columns of thesame name would be de-duplicated, when the Bundle were used aspart of the rendered SQL, such as in the ORDER BY or GROUP BY of the statement.References: #4295

• [orm] [bug]

Fixed regression in 1.2.9 due to #4287 where using aLoad option in conjunction with a string wildcard would resultin a TypeError.References: #4298

sql

• [sql] [bug]

Fixed bug where a Sequence would be dropped explicitly before anyTable that refers to it, which breaks in the case when thesequence is also involved in a server-side default for that table, whenusing MetaData.drop_all(). The step which processes sequencesto be dropped via non server-side column default functions is now invokedafter the table itself is dropped.References: #4300

1.2.9

Released: June 29, 2018

orm

• [orm] [bug]

Fixed issue where chaining multiple join elements inside ofQuery.join() might not correctly adapt to the previous left-handside, when chaining joined inheritance classes that share the same baseclass.References: #3505

• [orm] [bug]

Fixed bug in cache key generation for baked queries which could cause atoo-short cache key to be generated for the case of eager loads acrosssubclasses. This could in turn cause the eagerload query to be cached inplace of a non-eagerload query, or vice versa, for a polymorhic “selectin”load, or possibly for lazy loads or selectin loads as well.References: #4287

• [orm] [bug]

Fixed bug in new polymorphic selectin loading where the BakedQuery usedinternally would be mutated by the given loader options, which would bothinappropriately mutate the subclass query as well as carry over the effectto subsequent queries.References: #4286

• [orm] [bug]

Fixed regression caused by #4256 (itself a regression fix for#4228) which breaks an undocumented behavior which converted for anon-sequence of entities passed directly to the Query constructorinto a single-element sequence. While this behavior was never supported ordocumented, it’s already in use so has been added as a behavioral contractto Query.References: #4269

• [orm] [bug]

Fixed an issue that was both a performance regression in 1.2 as well as anincorrect result regarding the “baked” lazy loader, involving thegeneration of cache keys from the original Query object’s loaderoptions. If the loader options were built up in a “branched” style usingcommon base elements for multiple options, the same options would berendered into the cache key repeatedly, causing both a performance issue aswell as generating the wrong cache key. This is fixed, along with aperformance improvement when such “branched” options are applied viaQuery.options() to prevent the same option objects from beingapplied repeatedly.References: #4270

sql

• [sql] [bug]

Fixed regression in 1.2 due to #4147 where a Table thathas had some of its indexed columns redefined with new ones, as would occurwhen overriding columns during reflection or when usingTable.extend_existing, such that the Table.tometadata()method would fail when attempting to copy those indexes as they stillreferred to the replaced column. The copy logic now accommodates for thiscondition.References: #4279

mysql

• [mysql] [bug]

Fixed percent-sign doubling in mysql-connector-python dialect, which doesnot require de-doubling of percent signs. Additionally, the mysql-connector-python driver is inconsistent in how it passes the column namesin cursor.description, so a workaround decoder has been added toconditionally decode these randomly-sometimes-bytes values to unicode onlyif needed. Also improved test support for mysql-connector-python, howeverit should be noted that this driver still has issues with unicode thatcontinue to be unresolved as of yet.

• [mysql] [bug]

Fixed bug in index reflection where on MySQL 8.0 an index that includesASC or DESC in an indexed column specification would not be correctlyreflected, as MySQL 8.0 introduces support for returning this informationin a table definition string.References: #4293

• [mysql] [bug]

Fixed bug in MySQLdb dialect and variants such as PyMySQL where anadditional “unicode returns” check upon connection makes explicit use ofthe “utf8” character set, which in MySQL 8.0 emits a warning that utf8mb4should be used. This is now replaced with a utf8mb4 equivalent.Documentation is also updated for the MySQL dialect to specify utf8mb4 inall examples. Additional changes have been made to the test suite to useutf8mb3 charsets and databases (there seem to be collation issues in someedge cases with utf8mb4), and to support configuration default changes madein MySQL 8.0 such as explicit_defaults_for_timestamp as well as new errorsraised for invalid MyISAM indexes.References: #4283

• [mysql] [bug]

The Update construct now accommodates a Join objectas supported by MySQL for UPDATE..FROM. As the construct alreadyaccepted an alias object for a similar purpose, the feature of UPDATEagainst a non-table was already implied so this has been added.References: #3645

sqlite

• [sqlite] [bug]

Fixed issue in test suite where SQLite 3.24 added a new reserved word thatconflicted with a usage in TypeReflectionTest. Pull request courtesy NilsPhilippsen.

mssql

• [mssql] [bug]

Fixed bug in MSSQL reflection where when two same-named tables in differentschemas had same-named primary key constraints, foreign key constraintsreferring to one of the tables would have their columns doubled, causingerrors. Pull request courtesy Sean Dunn.References: #4288

• [mssql] [bug] [py3k]

Fixed issue within the SQL Server dialect under Python 3 where when runningagainst a non-standard SQL server database that does not contain either the“sys.dm_exec_sessions” or “sys.dm_pdw_nodes_exec_sessions” views, leadingto a failure to fetch the isolation level, the error raise would fail dueto an UnboundLocalError.References: #4273

oracle

• [oracle] [feature]

Added a new event currently used only by the cx_Oracle dialect,DialectEvents.setiputsizes(). The event passes a dictionary ofBindParameter objects to DBAPI-specific type objects that will bepassed, after conversion to parameter names, to the cx_Oraclecursor.setinputsizes() method. This allows both visibility into thesetinputsizes process as well as the ability to alter the behavior of whatdatatypes are passed to this method.

See also

Fine grained control over cx_Oracle data binding performance with setinputsizes

References: #4290

• [oracle] [bug] [mysql]

Fixed INSERT FROM SELECT with CTEs for the Oracle and MySQL dialects, wherethe CTE was being placed above the entire statement as is typical withother databases, however Oracle and MariaDB 10.2 wants the CTE underneaththe “INSERT” segment. Note that the Oracle and MySQL dialects don’t yetwork when a CTE is applied to a subquery inside of an UPDATE or DELETEstatement, as the CTE is still applied to the top rather than inside thesubquery.References: #4275

misc

• [feature] [ext]

Added new attribute Query.lazy_loaded_from which is populatedwith an InstanceState that is using this Query inorder to lazy load a relationship. The rationale for this is thatit serves as a hint for the horizontal sharding feature to use, such thatthe identity token of the state can be used as the default identity tokento use for the query within id_chooser().References: #4243

• [bug] [py3k]

Replaced the usage of inspect.formatargspec() with a vendored versioncopied from the Python standard library, as inspect.formatargspec()is deprecated and as of Python 3.7.0 is emitting a warning.References: #4291

1.2.8

Released: May 28, 2018

orm

• [orm] [bug]

Fixed regression in 1.2.7 caused by #4228, which itself was fixinga 1.2-level regression, where the query_cls callable passed to aSession was assumed to be a subclass of Query withclass method availability, as opposed to an arbitrary callable. Inparticular, the dogpile caching example illustrates query_cls as afunction and not a Query subclass.References: #4256

• [orm] [bug]

Fixed a long-standing regression that occurred in version1.0, which prevented the use of a custom MapperOptionthat alters the _params of a Query object for alazy load, since the lazy loader itself would overwrite thoseparameters. This applies to the “temporal range” exampleon the wiki. Note however that theQuery.populate_existing() method is now required inorder to rewrite the mapper options associated with an objectalready loaded in the identity map.

As part of this change, a custom definedMapperOption will now cause lazy loaders related tothe target object to use a non-baked query by default unlessthe MapperOption._generate_cache_key() method is implemented.In particular, this repairs one regression which occurred whenusing the dogpile.cache “advanced” example, which was notreturning cached results and instead emitting SQL due to anincompatibility with the baked query loader; with the change,the RelationshipCache option included for many releasesin the dogpile example will disable the “baked” query altogether.Note that the dogpile example is also modernized to avoid bothof these issues as part of issue #4258.References: #4128

• [orm] [bug]

Fixed bug where the new baked.Result.with_post_criteria()method would not interact with a subquery-eager loader correctly,in that the “post criteria” would not be applied to embeddedsubquery eager loaders. This is related to #4128 in thatthe post criteria feature is now used by the lazy loader.

• [orm] [bug]

Updated the dogpile.caching example to include new structures thataccommodate for the “baked” query system, which is used by default withinlazy loaders and some eager relationship loaders. The dogpile.caching“relationship_caching” and “advanced” examples were also broken due to#4256. The issue here is also worked-around by the fix in#4128.References: #4258

engine

• [engine] [bug]

Fixed connection pool issue whereby if a disconnection error were raisedduring the connection pool’s “reset on return” sequence in conjunction withan explicit transaction opened against the enclosing Connectionobject (such as from calling Session.close() without a rollback orcommit, or calling Connection.close() without first closing atransaction declared with Connection.begin()), a double-checkin wouldresult, which could then lead towards concurrent checkouts of the sameconnection. The double-checkin condition is now prevented overall by anassertion, as well as the specific double-checkin scenario has beenfixed.References: #4252

• [engine] [bug]

Fixed a reference leak issue where the values of the parameter dictionaryused in a statement execution would remain referenced by the “compiledcache”, as a result of storing the key view used by Python 3 dictionarykeys(). Pull request courtesy Olivier Grisel.

sql

• [sql] [bug]

Fixed issue where the “ambiguous literal” error message used wheninterpreting literal values as SQL expression values would encounter atuple value, and fail to format the message properly. Pull request courtesyMiguel Ventura.

mssql

• [mssql] [bug]

Fixed a 1.2 regression caused by #4061 where the SQL Server“BIT” type would be considered to be “native boolean”. The goal herewas to avoid creating a CHECK constraint on the column, however the biggerissue is that the BIT value does not behave like a true/false constantand cannot be interpreted as a standalone expression, e.g.“WHERE ”. The SQL Server dialect now goes back to beingnon-native boolean, but with an extra flag that still avoids creatingthe CHECK constraint.References: #4250

oracle

• [oracle] [bug]

The Oracle BINARY_FLOAT and BINARY_DOUBLE datatypes now participate withincx_Oracle.setinputsizes(), passing along NATIVE_FLOAT, so as to support theNaN value. Additionally, oracle.BINARY_FLOAT,oracle.BINARY_DOUBLE and oracle.DOUBLE_PRECISION nowsubclass Float, since these are floating point datatypes, notdecimal. These datatypes were already defaulting theFloat.asdecimal flag to False in line with whatFloat already does.References: #4264

• [oracle] [bug]

Added reflection capabilities for the oracle.BINARY_FLOAT,oracle.BINARY_DOUBLE datatypes.

• [oracle] [bug]

Altered the Oracle dialect such that when an Integer type is inuse, the cx_Oracle.NUMERIC type is set up for setinputsizes(). InSQLAlchemy 1.1 and earlier, cx_Oracle.NUMERIC was passed for all numerictypes unconditionally, and in 1.2 this was removed to allow for betternumeric precision. However, for integers, some database/client setupswill fail to coerce boolean values True/False into integers which introducesregressive behavior when using SQLAlchemy 1.2. Overall, the setinputsizeslogic seems like it will need a lot more flexibility going forward so thisis a start for that.References: #4259

misc

• [bug] [ext]

The horizontal sharding extension now makes use of the identity tokenadded to ORM identity keys as part of #4137, when an objectrefresh or column-based deferred load or unexpiration operation occurs.Since we know the “shard” that the object originated from, we makeuse of this value when refreshing, thereby avoiding queries againstother shards that don’t match this object’s identity in any case.References: #4247

• [bug] [ext]

Fixed a race condition which could occur if automapAutomapBase.prepare() were used within a multi-threaded contextagainst other threads which may call configure_mappers() as aresult of use of other mappers. The unfinished mapping work of automapis particularly sensitive to being pulled in by aconfigure_mappers() step leading to errors.References: #4266

• [bug] [tests]

Fixed a bug in the test suite where if an external dialect returnedNone for server_version_info, the exclusion logic would raise anAttributeError.References: #4249

1.2.7

Released: April 20, 2018

orm

• [orm] [bug]

Fixed regression in 1.2 within sharded query feature where thenew “identity_token” element was not being correctly considered withinthe scope of a lazy load operation, when searching the identity mapfor a related many-to-one element. The new behavior will allow formaking use of the “id_chooser” in order to determine the best identitykey to retrieve from the identity map. In order to achieve this, somerefactoring of 1.2’s “identity_token” approach has made some slight changesto the implementation of ShardedQuery which should be noted for otherderivations of this class.References: #4228

• [orm] [bug]

Fixed issue in single-inheritance loading where the use of an aliasedentity against a single-inheritance subclass in conjunction with theQuery.select_from() method would cause the SQL to be rendered withthe unaliased table mixed in to the query, causing a cartesian product. Inparticular this was affecting the new “selectin” loader when used against asingle-inheritance subclass.References: #4241

sql

• [sql] [bug]

Fixed issue where the compilation of an INSERT statement with the“literal_binds” option that also uses an explicit sequence and “inline”generation, as on PostgreSQL and Oracle, would fail to accommodate theextra keyword argument within the sequence processing routine.References: #4231

postgresql

• [postgresql] [feature]

Added new PG type postgresql.REGCLASS which assists in castingtable names to OID values. Pull request courtesy Sebastian Bank.References: #4160

• [postgresql] [bug]

Fixed bug where the special “not equals” operator for the PostgreSQL“range” datatypes such as DATERANGE would fail to render “IS NOT NULL” whencompared to the Python None value.References: #4229

mssql

• [mssql] [bug]

Fixed 1.2 regression caused by #4060 where the query used toreflect SQL Server cross-schema foreign keys was limiting the criteriaincorrectly.References: #4234

oracle

• [oracle] [bug]

The Oracle NUMBER datatype is reflected as INTEGER if the precision is NULLand the scale is zero, as this is how INTEGER values come back whenreflected from Oracle’s tables. Pull request courtesy Kent Bower.

1.2.6

Released: March 30, 2018

orm

• [orm] [bug]

Fixed bug where using Mutable.associate_with() orMutable.as_mutable() in conjunction with a class that has non-primary mappers set up with alternatively-named attributes would produce anattribute error. Since non-primary mappers are not used for persistence,the mutable extension now excludes non-primary mappers from itsinstrumentation steps.References: #4215

engine

• [engine] [bug]

Fixed bug in connection pool where a connection could be present in thepool without all of its “connect” event handlers called, if a previous“connect” handler threw an exception; note that the dialects themselveshave connect handlers that emit SQL, such as those which set transactionisolation, which can fail if the database is in a non-available state, butstill allows a connection. The connection is now invalidated first if anyof the connect handlers fail.References: #4225

sql

• [sql] [bug]

Fixed a regression that occurred from the previous fix to #4204 inversion 1.2.5, where a CTE that refers to itself after theCTE.alias() method has been called would not refer to itselfcorrectly.References: #4204

postgresql

• [postgresql] [feature]

Added support for “PARTITION BY” in PostgreSQL table definitions,using “postgresql_partition_by”. Pull request courtesyVsevolod Solovyov.

mssql

• [mssql] [bug]

Adjusted the SQL Server version detection for pyodbc to only allow fornumeric tokens, filtering out non-integers, since the dialect does tuple-numeric comparisons with this value. This is normally true for all knownSQL Server / pyodbc drivers in any case.References: #4227

oracle

• [oracle] [bug]

The minimum cx_Oracle version supported is 5.2 (June 2015). Previously,the dialect asserted against version 5.0 but as of 1.2.2 we are using somesymbols that did not appear until 5.2.References: #4211

misc

• [bug] [declarative]

Removed a warning that would be emitted when calling upontable_args, mapper_args as named with a @declared_attrmethod, when called from a non-mapped declarative mixin. Calling thesedirectly is documented as the approach to use when one is overriding oneof these methods on a mapped class. The warning still emits for regularattribute names.References: #4221

1.2.5

Released: March 6, 2018

orm

• [orm] [feature]

Added new feature Query.only_return_tuples(). Causes theQuery object to return keyed tuple objects unconditionally evenif the query is against a single entity. Pull request courtesy EricAtkin.

• [orm] [bug]

Fixed bug in new “polymorphic selectin” loading when a selection ofpolymorphic objects were to be partially loaded from a relationshiplazy loader, leading to an “empty IN” condition within the load thatraises an error for the “inline” form of “IN”.References: #4199

• [orm] [bug]

Fixed 1.2 regression where a mapper option that contains anAliasedClass object, as is typical when using theQueryableAttribute.of_type() method, could not be pickled. 1.1’sbehavior was to omit the aliased class objects from the path, so thisbehavior is restored.References: #4209

sql

• [sql] [bug]

Fixed bug in :class:.CTE construct along the same lines as that of#4204 where a CTE that was aliased would not copy itselfcorrectly during a “clone” operation as is frequent within the ORM as wellas when using the ClauseElement.params() method.References: #4210

• [sql] [bug]

Fixed bug in CTE rendering where a CTE that was also turned intoan Alias would not render its “ctename AS aliasname” clauseappropriately if there were more than one reference to the CTE in a FROMclause.References: #4204

• [sql] [bug]

Fixed bug in new “expanding IN parameter” feature where the bind parameterprocessors for values wasn’t working at all, tests failed to cover thispretty basic case which includes that ENUM values weren’t working.References: #4198

postgresql

• [postgresql] [bug] [py3k]

Fixed bug in PostgreSQL COLLATE / ARRAY adjustment first introducedin #4006 where new behaviors in Python 3.7 regular expressionscaused the fix to fail.This change is also backported to: 1.1.18

References: #4208

mysql

• [mysql] [bug]

MySQL dialects now query the server version using SELECT @@versionexplicitly to the server to ensure we are getting the correct versioninformation back. Proxy servers like MaxScale interfere with the valuethat is passed to the DBAPI’s connection.server_version value so thisis no longer reliable.This change is also backported to: 1.1.18

References: #4205

1.2.4

Released: February 22, 2018

orm

• [orm] [bug]

Fixed 1.2 regression in ORM versioning feature where a mapping against aselect() or alias() that also used a versioning columnagainst the underlying table would fail due to the check added as part of#3673.References: #4193

engine

• [engine] [bug]

Fixed regression caused in 1.2.3 due to fix from #4181 wherethe changes to the event system involving Engine andOptionEngine did not accommodate for event removals, whichwould raise an AttributeError when invoked at the classlevel.References: #4190

sql

• [sql] [bug]

Fixed bug where CTE expressions would not have their name or alias namequoted when the given name is case sensitive or otherwise requires quoting.Pull request courtesy Eric Atkin.References: #4197

1.2.3

Released: February 16, 2018

orm

• [orm] [feature]

Added new argument attributes.set_attribute.inititatorto the attributes.set_attribute() function, allowing anevent token received from a listener function to be propagatedto subsequent set events.

• [orm] [bug]

Fixed issue in post_update feature where an UPDATE is emittedwhen the parent object has been deleted but the dependent objectis not. This issue has existed for a long time howeversince 1.2 now asserts rows matched for post_update, thiswas raising an error.This change is also backported to: 1.1.16

References: #4187

• [orm] [bug]

Fixed regression caused by fix for issue #4116 affecting versions1.2.2 as well as 1.1.15, which had the effect of mis-calculation of the“owning class” of an AssociationProxy as the NoneType classin some declarative mixin/inheritance situations as well as if theassociation proxy were accessed off of an un-mapped class. The “figure outthe owner” logic has been replaced by an in-depth routine that searchesthrough the complete mapper hierarchy assigned to the class or subclass todetermine the correct (we hope) match; will not assign the owner if nomatch is found. An exception is now raised if the proxy is usedagainst an un-mapped instance.This change is also backported to: 1.1.16

References: #4185

• [orm] [bug]

Fixed bug where the Bundle object did notcorrectly report upon the primary Mapper objectrepresented by the bundle, if any. An immediateside effect of this issue was that the new selectinloadloader strategy wouldn’t work with the horizontal shardingextension.References: #4175

• [orm] [bug]

Fixed bug in concrete inheritance mapping where user-definedattributes such as hybrid properties that mirror the namesof mapped attributes from sibling classes would be overwritten bythe mapper as non-accessible at the instance level. Additionallyensured that user-bound descriptors are not implicitly invoked at the classlevel during the mapper configuration stage.References: #4188

• [orm] [bug]

Fixed bug where the orm.reconstructor() eventhelper would not be recognized if it were applied to theinit() method of the mapped class.References: #4178

engine

• [engine] [bug]

Fixed bug where events associated with an Engineat the class level would be doubled when theEngine.execution_options() method were used. Toachieve this, the semi-private class OptionEngineno longer accepts events directly at the class leveland will raise an error; the class only propagates class-levelevents from its parent Engine. Instance-levelevents continue to work as before.References: #4181

• [engine] [bug]

The URL object now allows query keys to be specified multipletimes where their values will be joined into a list. This is to supportthe plugins feature documented at CreateEnginePlugin whichdocuments that “plugin” can be passed multiple times. Additionally, theplugin names can be passed to create_engine() outside of the URLusing the new create_engine.plugins parameter.References: #4170

sql

• [sql] [feature]

Added support for Enum to persist the values of the enumeration,rather than the keys, when using a Python pep-435 style enumerated object.The user supplies a callable function that will return the string values tobe persisted. This allows enumerations against non-string values to bevalue-persistable as well. Pull request courtesy Jon Snyder.References: #3906

• [sql] [bug]

Fixed bug where the Enum type wouldn’t handleenum “aliases” correctly, when more than one key refers to thesame value. Pull request courtesy Daniel Knell.References: #4180

postgresql

• [postgresql] [bug]

Added “SSL SYSCALL error: Operation timed out” to the listof messages that trigger a “disconnect” scenario for thepsycopg2 driver. Pull request courtesy André Cruz.This change is also backported to: 1.1.16

• [postgresql] [bug]

Added “TRUNCATE” to the list of keywords accepted by thePostgreSQL dialect as an “autocommit”-triggering keyword.Pull request courtesy Jacob Hayes.This change is also backported to: 1.1.16

sqlite

• [sqlite] [bug]

Fixed the import error raised when a platformhas neither pysqlite2 nor sqlite3 installed, suchthat the sqlite3-related import error is raised,not the pysqlite2 one which is not the actualfailure mode. Pull request courtesy Robin.

oracle

• [oracle] [feature]

The ON DELETE options for foreign keys are now part ofOracle reflection. Oracle does not support ON UPDATEcascades. Pull request courtesy Miroslav Shubernetskiy.

• [oracle] [bug]

Fixed bug in cx_Oracle disconnect detection, used by pre_ping and otherfeatures, where an error could be raised as DatabaseError which includes anumeric error code; previously we weren’t checking in this case for adisconnect code.References: #4182

misc

• [bug] [pool]

Fixed a fairly serious connection pool bug where a connection that isacquired after being refreshed as a result of a user-definedDisconnectionError or due to the 1.2-released “pre_ping” featurewould not be correctly reset if the connection were returned to the pool byweakref cleanup (e.g. the front-facing object is garbage collected); theweakref would still refer to the previously invalidated DBAPI connectionwhich would have the reset operation erroneously called upon it instead.This would lead to stack traces in the logs and a connection being checkedinto the pool without being reset, which can cause locking issues.This change is also backported to: 1.1.16

References: #4184

• [bug] [tests]

A test added in 1.2 thought to confirm a Python 2.7 behavior turns out tobe confirming the behavior only as of Python 2.7.8. Python bug #8743 stillimpacts set comparison in Python 2.7.7 and earlier, so the test in questioninvolving AssociationSet no longer runs for these older Python 2.7versions.References: #3265

1.2.2

Released: January 24, 2018

orm

• [orm] [bug]

Fixed 1.2 regression regarding new bulk_replace eventwhere a backref would fail to remove an object from theprevious owner when a bulk-assignment assigned theobject to a new owner.References: #4171

mysql

• [mysql] [bug]

Added more MySQL 8.0 reserved words to the MySQL dialectfor quoting purposes. Pull request courtesyRiccardo Magliocchetti.

mssql

• [mssql] [bug]

Added ODBC error code 10054 to the list of errorcodes that count as a disconnect for ODBC / MSSQL server.References: #4164

oracle

• [oracle] [bug]

The cx_Oracle dialect now calls setinputsizes() with cx_Oracle.NCHARunconditionally when the NVARCHAR2 datatype, in SQLAlchemy correspondingto sqltypes.Unicode(), is in use. Per cx_Oracle’s author this allowsthe correct conversions to occur within the Oracle client regardlessof the setting for NLS_NCHAR_CHARACTERSET.References: #4163

1.2.1

Released: January 15, 2018

orm

• [orm] [bug]

Fixed bug where an object that is expunged during a rollback ofa nested or subtransaction which also had its primary key mutatedwould not be correctly removed from the session, causing subsequentissues in using the session.This change is also backported to: 1.1.16

References: #4151

• [orm] [bug]

Fixed regression where pickle format of a Load / UnboundLoad object (e.g.loader options) changed and _setstate() was raising anUnboundLocalError for an object received from the legacy format, eventhough an attempt was made to do so. tests are now added to ensure thisworks.References: #4159

• [orm] [bug]

Fixed regression caused by new lazyload caching scheme in #3954where a query that makes use of loader options with of_type would causelazy loads of unrelated paths to fail with a TypeError.References: #4153

• [orm] [bug]

Fixed bug in new “selectin” relationship loader where the loader could tryto load a non-existent relationship when loading a collection ofpolymorphic objects, where only some of the mappers include thatrelationship, typically when PropComparator.of_type() is being used.References: #4156

sql

• [sql] [bug]

Fixed bug in Insert.values() where using the “multi-values”format in combination with Column objects as keys ratherthan strings would fail. Pull request courtesy Aubrey Stark-Toller.This change is also backported to: 1.1.16

References: #4162

mssql

• [mssql] [bug]

Fixed regression in 1.2 where newly repaired quotingof collation names in #3785 breaks SQL Server,which explicitly does not understand a quoted collationname. Whether or not mixed-case collation names arequoted or not is now deferred down to a dialect-leveldecision so that each dialect can prepare these identifiersdirectly.References: #4154

oracle

• [oracle] [bug]

Fixed regression where the removal of most setinputsizesrules from cx_Oracle dialect impacted the TIMESTAMPdatatype’s ability to retrieve fractional seconds.References: #4157

• [oracle] [bug]

Fixed regression in Oracle imports where a missing comma causedan undefined symbol to be present. Pull request courtesyMiroslav Shubernetskiy.

misc

• [bug] [ext]

Fixed regression in association proxy due to #3769(allow for chained any() / has()) where contains() againstan association proxy chained in the form(o2m relationship, associationproxy(m2o relationship, m2o relationship))would raise an error regarding the re-application of contains()on the final link of the chain.References: #4150

• [bug] [tests]

Removed an oracle-specific requirements rule from the publictest suite that was interfering with third party dialectsuites.

• [bug] [tests]

Added a new exclusion rule group_by_complex_expressionwhich disables tests that use “GROUP BY ”, which seemsto be not viable for at least two third party dialects.

1.2.0

Released: December 27, 2017

orm

• [orm] [feature]

Added a new data member to the identity key tupleused by the ORM’s identity map, known as the“identity_token”. This token defaults to None butmay be used by database sharding schemes to differentiateobjects in memory with the same primary key that comefrom different databases. The horizontal shardingextension integrates this token applying the shardidentifier to it, thus allowing primary keys to beduplicated across horizontally sharded backends.

See also

Identity key enhancements to support sharding

References: #4137

• [orm] [bug] [ext]

Fixed bug where the association proxy would inadvertently link itselfto an AliasedClass object if it were called first withthe AliasedClass as a parent, causing errors upon subsequentusage.This change is also backported to: 1.1.15

References: #4116

• [orm] [bug]

Fixed bug in contains_eager() query option where making use of apath that used PropComparator.of_type() to refer to a subclassacross more than one level of joins would also require that the “alias”argument were provided with the same subtype in order to avoid addingunwanted FROM clauses to the query; additionally, usingcontains_eager() across subclasses that use aliased() objectsof subclasses as the PropComparator.of_type() argument will alsorender correctly.References: #4130

• [orm] [bug]

The Query.exists() method will now disable eager loaders for whenthe query is rendered. Previously, joined-eager load joins would be renderedunnecessarily as well as subquery eager load queries would be needlesslygenerated. The new behavior matches that of the Query.subquery()method.References: #4032

orm declarative

• [bug] [declarative] [orm]

Fixed bug where a descriptor that is elsewhere a mapped columnor relationship within a hierarchy based on AbstractConcreteBasewould be referred towards during a refresh operation, causing an erroras the attribute is not mapped as a mapper property.A similar issue can arise for other attributes like the “type” columnadded by AbstractConcreteBase if the class fails to include“concrete=True” in its mapper, however the check here should alsoprevent that scenario from causing a problem.This change is also backported to: 1.1.15

References: #4124

engine

• [engine] [feature]

The “password” attribute of the url.URL object can now beany user-defined or user-subclassed string object that responds to thePython str() builtin. The object passed will be maintained as thedatamember url.URL.password_original and will be consultedwhen the url.URL.password attribute is read to produce thestring value.References: #4089

sql

• [sql] [bug]

Fixed bug where repr of ColumnDefault would failif the argument were a tuple. Pull request courtesy Nicolas Caniart.This change is also backported to: 1.1.15

References: #4126

• [sql] [bug]

Reworked the new “autoescape” feature introduced inNew “autoescape” option for startswith(), endswith() in 1.2.0b2 to be fully automatic; the escapecharacter now defaults to a forwards slash "/" andis applied to percent, underscore, as well as the escapecharacter itself, for fully automatic escaping. Thecharacter can also be changed using the “escape” parameter.

See also

New “autoescape” option for startswith(), endswith()

References: #2694

• [sql] [bug]

Fixed bug where the Table.tometadata() method would not properlyaccommodate Index objects that didn’t consist of simplecolumn expressions, such as indexes against a text() construct,indexes that used SQL expressions or func, etc. The routinenow copies expressions fully to a new Index object whilesubstituting all table-bound Column objects for thoseof the target table.References: #4147

• [sql] [bug]

Changed the “visit name” of ColumnElement from “column” to“column_element”, so that when this element is used as the basis for auser-defined SQL element, it is not assumed to behave like a table-boundColumnClause when processed by various SQL traversal utilities,as are commonly used by the ORM.References: #4142

• [sql] [bug] [ext]

Fixed issue in ARRAY datatype which is essentially the sameissue as that of #3832, except not a regression, wherecolumn attachment events on top of ARRAY would not firecorrectly, thus interfering with systems which rely upon this. A keyuse case that was broken by this is the use of mixins to declarecolumns that make use of MutableList.as_mutable().References: #4141

• [sql] [bug]

Fixed bug in new “expanding bind parameter” feature whereby if multipleparams were used in one statement, the regular expression would notmatch the parameter name correctly.References: #4140

• [sql] [enhancement]

Implemented “DELETE..FROM” syntax for PostgreSQL, MySQL, MS SQL Server(as well as within the unsupported Sybase dialect) in a manner similarto how “UPDATE..FROM” works. A DELETE statement that refers to more thanone table will switch into “multi-table” mode and render the appropriate“USING” or multi-table “FROM” clause as understood by the database.Pull request courtesy Pieter Mulder.

See also

Multiple-table criteria support for DELETE

References: #959

postgresql

• [postgresql] [feature]

Added new postgresql.MONEY datatype. Pull request courtesyCleber J Santos.

mysql

• [mysql] [bug]

MySQL 5.7.20 now warns for use of the @tx_isolation variable; a versioncheck is now performed and uses @transaction_isolation insteadto prevent this warning.This change is also backported to: 1.1.15

References: #4120

• [mysql] [bug]

Fixed regression from issue 1.2.0b3 where “MariaDB” version comparison canfail for some particular MariaDB version strings under Python 3.References: #4115

mssql

• [mssql] [bug]

Fixed bug where sqltypes.BINARY and sqltypes.VARBINARY datatypeswould not include correct bound-value handlers for pyodbc,which allows the pyodbc.NullParam value to be passed thathelps with FreeTDS.References: #4121

oracle

• [oracle] [bug]

Added some additional rules to fully handle Decimal('Infinity'),Decimal('-Infinity') values with cx_Oracle numerics when usingasdecimal=True.References: #4064

misc

• [misc] [feature]

Added a new errors section to the documentation with backgroundabout common error messages. Selected exceptions within SQLAlchemywill include a link in their string output to the relevant sectionwithin this page.

• [enhancement] [ext]

Added new method baked.Result.with_post_criteria() to bakedquery system, allowing non-SQL-modifying transformations to take placeafter the query has been pulled from the cache. Among other things,this method can be used with horizontal_shard.ShardedQueryto set the shard identifier. horizontal_shard.ShardedQueryhas also been modified such that its ShardedQuery.get() methodinteracts correctly with that of baked.Result.References: #4135

1.2.0b3

Released: October 13, 2017

orm

• [orm] [bug]

Fixed bug where ORM relationship would warn against conflicting synctargets (e.g. two relationships would both write to the same column) forsibling classes in an inheritance hierarchy, where the two relationshipswould never actually conflict during writes.This change is also backported to: 1.1.15

References: #4078

• [orm] [bug]

Fixed bug where correlated select used against single-table inheritanceentity would fail to render correctly in the outer query, due to adjustmentfor single inheritance discriminator criteria inappropriately re-applyingthe criteria to the outer query.This change is also backported to: 1.1.15

References: #4103

• [orm] [bug]

Fixed bug in Session.merge() following along similar lines as thatof #4030, where an internal check for a target object inthe identity map could lead to an error if it were to be garbage collectedimmediately before the merge routine actually retrieves the object.This change is also backported to: 1.1.14

References: #4069

• [orm] [bug]

Fixed bug where an undefer_group() option would not be recognizedif it extended from a relationship that was loading using joined eagerloading. Additionally, as the bug led to excess work being performed,Python function call counts are also improved by 20% within the initialcalculation of result set columns, complementing the joined eager loadimprovements of #3915.This change is also backported to: 1.1.14

References: #4048

• [orm] [bug]

Fixed bug in Session.merge() where objects in a collection that hadthe primary key attribute set to None for a key that is typicallyautoincrementing would be considered to be a database-persisted key forpart of the internal deduplication process, causing only one object toactually be inserted in the database.This change is also backported to: 1.1.14

References: #4056

• [orm] [bug]

An InvalidRequestError is raised when a synonym()is used against an attribute that is not against a MapperProperty,such as an association proxy. Previously, a recursion overflow wouldoccur trying to locate non-existent attributes.This change is also backported to: 1.1.14

References: #4067

• [orm] [bug]

Fixed regression introduced in 1.2.0b1 due to #3934 where theSession would fail to “deactivate” the transaction, if arollback failed (the target issue is when MySQL loses track of a SAVEPOINT).This would cause a subsequent call to Session.rollback() to raisean error a second time, rather than completing and bringing theSession back to ACTIVE.References: #4050

• [orm] [bug]

Fixed issue where the make_transient_to_detached() functionwould expire all attributes on the target object, including “deferred”attributes, which has the effect of the attribute being undeferredfor the next refresh, causing an unexpected load of the attribute.References: #4084

• [orm] [bug]

Fixed bug involving delete-orphan cascade where a related itemthat becomes an orphan before the parent object is part of asession is still tracked as moving into orphan status, which resultsin it being expunged from the session rather than being flushed.

Note

This fix was inadvertently merged during the 1.2.0b3release and was not added to the changelog at that time.This changelog note was added to the release retroactively as ofversion 1.2.13.

References: #4040

• [orm] [bug]

Fixed bug in “selectin” polymorphic loading, loads subclasses using separate IN queries which prevented “selectin” and“inline” settings in a multi-level class hierarchy from interactingtogether as expected. A new example is added to the documentation.

See also

Combining selectin and with_polymorphic

References: #4026

• [orm] [bug]

Removed the warnings that are emitted when the LRU caches employedby the mapper as well as loader strategies reach their threshold; thepurpose of this warning was at first a guard against excess cache keysbeing generated but became basically a check on the “creating manyengines” antipattern. While this is still an antipattern, the presenceof test suites which both create an engine per test as well as raiseon all warnings will be an inconvenience; it should not be criticalthat such test suites change their architecture just for this warning(though engine-per-test suite is always better).References: #4071

• [orm] [bug]

Fixed regression where the use of a undefer_group() optionin conjunction with a lazy loaded relationship option would causean attribute error, due to a bug in the SQL cache key generationadded in 1.2 as part of #3954.References: #4049

• [orm] [bug]

Modified the change made to the ORM update/delete evaluator in#3366 such that if an unmapped column expression is presentin the update or delete, if the evaluator can match its name to themapped columns of the target class, a warning is emitted, rather thanraising UnevaluatableError. This is essentially the pre-1.2 behavior,and is to allow migration for applications that are currently relyingupon this pattern. However, if the given attribute name cannot bematched to the columns of the mapper, the UnevaluatableError isstill raised, which is what was fixed in #3366.References: #4073

orm declarative

• [bug] [declarative] [orm]

A warning is emitted if a subclass attempts to override an attributethat was declared on a superclass using @declared_attr.cascadingthat the overridden attribute will be ignored. This usecase cannot be fully supported down to further subclasses without morecomplex development efforts, so for consistency the “cascading” ishonored all the way down regardless of overriding attributes.References: #4091

• [bug] [declarative] [orm]

A warning is emitted if the @declaredattr.cascading attribute isused with a special declarative name such as _tablename, as thishas no effect.References: #4092

engine

• [engine] [feature]

Added next() and next() methods to ResultProxy,so that the next() builtin function works on the object directly.ResultProxy has long had an iter() method which alreadyallows it to respond to the iter() builtin. The implementationfor iter() is unchanged, as performance testing has indicatedthat iteration using a next() method with StopIterationis about 20% slower in both Python 2.7 and 3.6.References: #4077

• [engine] [bug]

Made some adjustments to Pool and Connection suchthat recovery logic is not run underneath exception catches forpool.Empty, AttributeError, since when the recovery operationitself fails, Python 3 creates a misleading stack trace referring to theEmpty / AttributeError as the cause, when in fact these exceptioncatches are part of control flow.References: #4028

sql

• [sql] [bug]

Fixed bug where the recently added ColumnOperators.any_()and ColumnOperators.all_() methods didn’t work when calledas methods, as opposed to using the standalone functionsany_() and all_(). Alsoadded documentation examples for these relatively unintuitiveSQL operators.This change is also backported to: 1.1.15

References: #4093

• [sql] [bug]

Added a new method DefaultExecutionContext.get_current_parameters()which is used within a function-based default value generator inorder to retrieve the current parameters being passed to the statement.The new function differs from theDefaultExecutionContext.current_parameters attribute inthat it also provides for optional grouping of parameters thatcorrespond to a multi-valued “insert” construct. Previously it was notpossible to identify the subset of parameters that were relevant tothe function call.

See also

Parameter helper for multi-valued INSERT with contextual default generator

Context-Sensitive Default Functions

References: #4075

• [sql] [bug]

Fixed bug in new SQL comments feature where table and column commentwould not be copied when using Table.tometadata().References: #4087

• [sql] [bug]

In release 1.1, the Boolean type was broken in thatboolean coercion via bool() would occur for backends that did notfeature “native boolean”, but would not occur for native boolean backends,meaning the string "0" now behaved inconsistently. After a poll, aconsensus was reached that non-boolean values should be raising an error,especially in the ambiguous case of string "0"; so the Booleandatatype will now raise ValueError if an incoming value is notwithin the range None, True, False, 1, 0.

See also

Boolean datatype now enforces strict True/False/None values

References: #4102

• [sql] [bug]

Refined the behavior of Operators.op() such that in all cases,if the Operators.op.is_comparison flag is set to True,the return type of the resulting expression will beBoolean, and if the flag is False, the return type of theresulting expression will be the same type as that of the left-handexpression, which is the typical default behavior of other operators.Also added a new parameter Operators.op.return_type as wellas a helper method Operators.bool_op().

See also

The typing behavior of custom operators has been made consistent

References: #4063

• [sql] [bug]

Internal refinements to the Enum, Interval, andBoolean types, which now extend a common mixinEmulated that indicates a type that provides Python-sideemulation of a DB native type, switching out to the DB native type when asupporting backend is in use. The PostgreSQL INTERVAL typewhen used directly will now include the correct type coercion rules forSQL expressions that also take effect for sqltypes.Interval(such as adding a date to an interval yields a datetime).References: #4088

postgresql

• [postgresql] [feature]

Added a new flag use_batch_mode to the psycopg2 dialect. This flagenables the use of psycopg2’s psycopg2.extras.execute_batchextension when the Engine calls uponcursor.executemany(). This extension provides a criticalperformance increase by over an order of magnitude when running INSERTstatements in batch. The flag is False by default as it is consideredto be experimental for now.

See also

Support for Batch Mode / Fast Execution Helpers

References: #4109

• [postgresql] [bug]

Made further fixes to the ARRAY class in conjunction withCOLLATE, as the fix made in #4006 failed to accommodatefor a multidimensional array.This change is also backported to: 1.1.15

References: #4006

• [postgresql] [bug]

Fixed bug in array_agg function where passing an argumentthat is already of type ARRAY, such as a PostgreSQLpostgresql.array construct, would produce a ValueError, dueto the function attempting to nest the arrays.This change is also backported to: 1.1.15

References: #4107

• [postgresql] [bug]

Fixed bug in PostgreSQL postgresql.dml.Insert.on_conflict_do_update()which would prevent the insert statement from being used as a CTE,e.g. via Insert.cte(), within another statement.This change is also backported to: 1.1.15

References: #4074

• [postgresql] [bug]

Fixed bug where the pg8000 driver would fail if usingMetaData.reflect() with a schema name, since the schema name wouldbe sent as a “quoted_name” object that’s a string subclass, which pg8000doesn’t recognize. The quoted_name type is added to pg8000’spy_types collection on connect.References: #4041

• [postgresql] [bug]

Enabled UUID support for the pg8000 driver, which supports native Pythonuuid round trips for this datatype. Arrays of UUID are still not supported,however.References: #4016

mysql

• [mysql] [bug]

Warning emitted when MariaDB 10.2.8 or earlier in the 10.2series is detected as there are major issues with CHECKconstraints within these versions that were resolved as of10.2.9.

Note that this changelog message was NOT released withSQLAlchemy 1.2.0b3 and was added retroactively.This change is also backported to: 1.1.15

References: #4097

• [mysql] [bug]

Changed the name of the .values attribute of the new MySQLINSERT..ON DUPLICATE KEY UPDATE construct to .inserted, asInsert already has a method called Insert.values().The .inserted attribute ultimately renders the MySQL VALUES()function.References: #4072

mssql

• [mssql] [feature]

Added a new mssql.TIMESTAMP datatype, thatcorrectly acts like a binary datatype for SQL Serverrather than a datetime type, as SQL Server breaks theSQL standard here. Also added mssql.ROWVERSION,as the “TIMESTAMP” type in SQL Server is deprecated infavor of ROWVERSION.References: #4086

• [mssql] [feature]

Added support for “AUTOCOMMIT” isolation level, as establishedvia Connection.execution_options(), to thePyODBC and pymssql dialects. This isolation level sets theappropriate DBAPI-specific flags on the underlyingconnection object.References: #4058

• [mssql] [bug]

Added a full range of “connection closed” exception codes to thePyODBC dialect for SQL Server, including ‘08S01’, ‘01002’, ‘08003’,‘08007’, ‘08S02’, ‘08001’, ‘HYT00’, ‘HY010’. Previously, only ‘08S01’was covered.This change is also backported to: 1.1.15

References: #4095

• [mssql] [bug]

SQL Server supports what SQLAlchemy calls “native boolean”with its BIT type, as this type only accepts 0 or 1 and theDBAPIs return its value as True/False. So the SQL Serverdialects now enable “native boolean” support, in that aCHECK constraint is not generated for a Booleandatatype. The only difference vs. other native booleanis that there are no “true” / “false” constants so “1” and“0” are still rendered here.References: #4061

• [mssql] [bug]

Fixed the pymssql dialect so that percent signs in SQL text, suchas used in modulus expressions or literal textual values, arenot doubled up, as seems to be what pymssql expects. This isdespite the fact that the pymssql DBAPI uses the “pyformat” parameterstyle which itself considers the percent sign to be significant.References: #4057

• [mssql] [bug]

Fixed bug where the SQL Server dialect could pull columns from multipleschemas when reflecting a self-referential foreign key constraint, ifmultiple schemas contained a constraint of the same name against atable of the same name.References: #4060

• [mssql] [bug] [orm]

Added a new class of “rowcount support” for dialects that is specific towhen “RETURNING”, which on SQL Server looks like “OUTPUT inserted”, is inuse, as the PyODBC backend isn’t able to give us rowcount on an UPDATE orDELETE statement when OUTPUT is in effect. This primarily affects the ORMwhen a flush is updating a row that contains server-calculated values,raising an error if the backend does not return the expected row count.PyODBC now states that it supports rowcount except if OUTPUT.inserted ispresent, which is taken into account by the ORM during a flush as towhether it will look for a rowcount.References: #4062

• [mssql] [bug] [orm]

Enabled the “sane_rowcount” flag for the pymssql dialect, indicatingthat the DBAPI now reports the correct number of rows affected froman UPDATE or DELETE statement. This impacts mostly the ORM versioningfeature in that it now can verify the number of rows affected on atarget version.

• [mssql] [bug]

Added a rule to SQL Server index reflection to ignore the so-called“heap” index that is implicitly present on a table that does notspecify a clustered index.References: #4059

oracle

• [oracle] [bug] [performance] [py2k]

Fixed performance regression caused by the fix for #3937 wherecx_Oracle as of version 5.3 dropped the .UNICODE symbol from itsnamespace, which was interpreted as cx_Oracle’s “WITH_UNICODE” mode beingturned on unconditionally, which invokes functions on the SQLAlchemyside which convert all strings to unicode unconditionally and causinga performance impact. In fact, per cx_Oracle’s author the“WITH_UNICODE” mode has been removed entirely as of 5.1, so the expensive unicodeconversion functions are no longer necessary and are disabled ifcx_Oracle 5.1 or greater is detected under Python 2. The warning against“WITH_UNICODE” mode that was removed under #3937 is also restored.This change is also backported to: 1.1.13, 1.0.19

References: #4035

• [oracle] [bug]

Partial support for persisting and retrieving the Oracle value“infinity” is implemented with cx_Oracle, using Python float valuesonly, e.g. float("inf"). Decimal support is not yet fulfilled bythe cx_Oracle DBAPI driver.References: #4064

• [oracle] [bug]

The cx_Oracle dialect has been reworked and modernized to take advantage ofnew patterns that weren’t present in the old 4.x series of cx_Oracle. Thisincludes that the minimum cx_Oracle version is the 5.x series and thatcx_Oracle 6.x is now fully tested. The most significant change involvestype conversions, primarily regarding the numeric / floating point and LOBdatatypes, making more effective use of cx_Oracle type handling hooks tosimplify how bind parameter and result data is processed.

See also

Major Refactor to cx_Oracle Dialect, Typing System

• [oracle] [bug]

two phase support for cx_Oracle has been completely removed for allversions of cx_Oracle, whereas in 1.2.0b1 this change only took effect forthe 6.x series of cx_Oracle. This feature never worked correctlyin any version of cx_Oracle and in cx_Oracle 6.x, the API which SQLAlchemyrelied upon was removed.

See also

Major Refactor to cx_Oracle Dialect, Typing System

References: #3997

• [oracle] [bug]

The column keys present in a result set when using Insert.returning()with the cx_Oracle backend now use the correct column / label nameslike that of all other dialects. Previously, these came out asret_nnn.

See also

Major Refactor to cx_Oracle Dialect, Typing System

• [oracle] [bug]

Several parameters to the cx_Oracle dialect are now deprecated and willhave no effect: auto_setinputsizes, exclude_setinputsizes,allow_twophase.

See also

Major Refactor to cx_Oracle Dialect, Typing System

• [oracle] [bug]

Fixed bug where an index reflected under Oracle with an expression like“column DESC” would not be returned, if the table also had no primarykey, as a result of logic that attempts to filter out theindex implicitly added by Oracle onto the primary key columns.References: #4042

• [oracle] [bug]

Fixed more regressions caused by cx_Oracle 6.0; at the moment, the onlybehavioral change for users is disconnect detection now detects forcx_Oracle.DatabaseError in addition to cx_Oracle.InterfaceError, asthis behavior seems to have changed. Other issues regarding numericprecision and uncloseable connections are pending with the upstreamcx_Oracle issue tracker.References: #4045

• [oracle] [bug]

Fixed bug where Oracle 8 “non ansi” join mode would not add the(+) operator to expressions that used an operator other than the= operator. The (+) needs to be on all columns that are partof the right-hand side.References: #4076

1.2.0b2

Released: July 24, 2017

orm

• [orm] [bug]

Fixed regression from 1.1.11 where adding additional non-entitycolumns to a query that includes an entity with subqueryloadrelationships would fail, due to an inspection added in 1.1.11 as aresult of #4011.This change is also backported to: 1.1.12

References: #4033

• [orm] [bug]

Fixed bug involving JSON NULL evaluation logic added in 1.1 as partof #3514 where the logic would not accommodate ORMmapped attributes named differently from the Columnthat was mapped.This change is also backported to: 1.1.12

References: #4031

• [orm] [bug]

Added KeyError checks to all methods withinWeakInstanceDict where a check for key in dict isfollowed by indexed access to that key, to guard against a race againstgarbage collection that under load can remove the key from the dictafter the code assumes its present, leading to very infrequentKeyError raises.This change is also backported to: 1.1.12

References: #4030

misc

• [bug] [py3k] [tests]

Fixed issue in testing fixtures which was incompatible with a changemade as of Python 3.6.2 involving context managers.This change is also backported to: 1.1.12, 1.0.18

References: #4034

1.2.0b1

Released: July 10, 2017

orm

• [orm] [feature]

An aliased() construct can now be passed to theQuery.select_entity_from() method. Entities will be pulledfrom the selectable represented by the aliased() construct.This allows special options for aliased() such asaliased.adapt_on_names to be used in conjunction withQuery.select_entity_from().This change is also backported to: 1.1.7

References: #3933

• [orm] [feature]

Added .autocommit attribute to scoped_session, proxyingthe .autocommit attribute of the underling Sessioncurrently assigned to the thread. Pull request courtesyBen Fagin.

• [orm] [feature]

Added a new feature orm.with_expression() that allows an ad-hocSQL expression to be added to a specific entity in a query at resulttime. This is an alternative to the SQL expression being delivered asa separate element in the result tuple.

See also

ORM attributes that can receive ad-hoc SQL expressions

References: #3058

• [orm] [feature]

Added a new style of mapper-level inheritance loading“polymorphic selectin”. This style of loadingemits queries for each subclass in an inheritancehierarchy subsequent to the load of the baseobject type, using IN to specify the desiredprimary key values.

See also

“selectin” polymorphic loading, loads subclasses using separate IN queries

References: #3948

• [orm] [feature]

Added a new kind of eager loading called “selectin” loading. Thisstyle of loading is very similar to “subquery” eager loading,except that it uses an IN expression given a list of primary keyvalues from the loaded parent objects, rather than re-stating theoriginal query. This produces a more efficient query that is“baked” (e.g. the SQL string is cached) and also works in thecontext of Query.yield_per().

See also

New “selectin” eager loading, loads all collections at once using IN

References: #3944

• [orm] [feature]

The lazy="select" loader strategy now makes used of theBakedQuery query caching system in all cases. Thisremoves most overhead of generating a Query object andrunning it into a select() and then string SQL statement fromthe process of lazy-loading related collections and objects. The“baked” lazy loader has also been improved such that it can nowcache in most cases where query load options are used.

See also

“Baked” loading now the default for lazy loads

References: #3954

• [orm] [feature] [ext]

The Query.update() method can now accommodate bothhybrid attributes as well as composite attributes as a sourceof the key to be placed in the SET clause. For hybrids, anadditional decorator hybrid_property.update_expression()is supplied for which the user supplies a tuple-returning function.

See also

Support for bulk updates of hybrids, composites

References: #3229

• [orm] [feature]

Added new attribute event AttributeEvents.bulk_replace().This event is triggered when a collection is assigned to arelationship, before the incoming collection is compared with theexisting one. This early event allows for conversion of incomingnon-ORM objects as well. The event is integrated with the@validates decorator.

See also

New bulk_replace event

References: #3896

• [orm] [feature]

Added new event handler AttributeEvents.modified() which istriggered when the func:.attributes.flag_modified function isinvoked, which is common when using the sqlalchemy.ext.mutableextension module.

See also

New “modified” event handler for sqlalchemy.ext.mutable

References: #3303

• [orm] [bug]

Fixed issue with subquery eagerloading which continues on fromthe series of issues fixed in #2699, #3106,#3893 involving that the “subquery” contains the correctFROM clause when beginning from a joined inheritance subclassand then subquery eager loading onto a relationship fromthe base class, while the query also includes criteria againstthe subclass. The fix in the previous tickets did not accommodatefor additional subqueryload operations loading more deeply fromthe first level, so the fix has been further generalized.This change is also backported to: 1.1.11

References: #4011

• [orm] [bug]

Fixed bug where a cascade such as “delete-orphan” (but others as well)would fail to locate an object linked to a relationship that itselfis local to a subclass in an inheritance relationship, thus causingthe operation to not take place.This change is also backported to: 1.1.10

References: #3986

• [orm] [bug]

Fixed a race condition which could occur under threaded environmentsas a result of the caching added via #3915. An internalcollection of Column objects could be regenerated on an aliasobject inappropriately, confusing a joined eager loader when itattempts to render SQL and collect results and resulting in anattribute error. The collection is now generated up front beforethe alias object is cached and shared among threads.This change is also backported to: 1.1.7

References: #3947

• [orm] [bug]

An UPDATE emitted as a result of therelationship.post_update feature will now integrate withthe versioning feature to both bump the version id of the row as wellas assert that the existing version number was matched.

See also

post_update integrates with ORM versioning

References: #3496

• [orm] [bug]

Repaired several use cases involving therelationship.post_update feature when used in conjunctionwith a column that has an “onupdate” value. When the UPDATE emits,the corresponding object attribute is now expired or refreshed so thatthe newly generated “onupdate” value can populate on the object;previously the stale value would remain. Additionally, if the targetattribute is set in Python for the INSERT of the object, the value isnow re-sent during the UPDATE so that the “onupdate” does not overwriteit (note this works just as well for server-generated onupdates).Finally, the SessionEvents.refresh_flush() event is now emittedfor these attributes when refreshed within the flush.

See also

Refinements to post_update in conjunction with onupdate

References: #3471, #3472

• [orm] [bug]

Fixed bug where programmatic version_id counter in conjunction withjoined table inheritance would fail if the version_id counterwere not actually incremented and no other values on the base tablewere modified, as the UPDATE would have an empty SET clause. Sinceprogrammatic version_id where version counter is not incrementedis a documented use case, this specific condition is now detectedand the UPDATE now sets the version_id value to itself, so thatconcurrency checks still take place.References: #3996

• [orm] [bug]

The versioning feature does not support NULL for the version counter.An exception is now raised if the version id is programmatic andwas set to NULL for an UPDATE. Pull request courtesy Diana Clarke.References: #3673

• [orm] [bug]

Removed a very old keyword argument from scoped_sessioncalled scope. This keyword was never documented and was anearly attempt at allowing for variable scopes.

See also

“scope” keyword removed from scoped_session

References: #3796

• [orm] [bug]

Fixed bug where combining a “with_polymorphic” load in conjunctionwith subclass-linked relationships that specify joinedload withinnerjoin=True, would fail to demote those “innerjoins” to“outerjoins” to suit the other polymorphic classes that don’tsupport that relationship. This applies to both a single and ajoined inheritance polymorphic load.References: #3988

• [orm] [bug]

Added new argument with_for_update to theSession.refresh() method. When the Query.with_lockmode()method were deprecated in favor of Query.with_for_update(),the Session.refresh() method was never updated to reflectthe new option.

See also

Added “for update” arguments to Session.refresh

References: #3991

• [orm] [bug]

Fixed bug where a column_property() that is also marked as“deferred” would be marked as “expired” during a flush, causing itto be loaded along with the unexpiry of regular attributes eventhough this attribute was never accessed.References: #3984

• [orm] [bug]

Fixed bug in subquery eager loading where the “join_depth” parameterfor self-referential relationships would not be correctly honored,loading all available levels deep rather than correctly countingthe specified number of levels for eager loading.References: #3967

• [orm] [bug]

Added warnings to the LRU “compiled cache” used by the Mapper(and ultimately will be for other ORM-based LRU caches) such thatwhen the cache starts hitting its size limits, the application willemit a warning that this is a performance-degrading situation thatmay require attention. The LRU caches can reach their size limitsprimarily if an application is making use of an unbounded numberof Engine objects, which is an antipattern. Otherwise,this may suggest an issue that should be brought to the SQLAlchemydeveloper’s attention.

• [orm] [bug]

Fixed bug to improve upon the specificity of loader options thattake effect subsequent to the lazy load of a related entity, sothat the loader options will match to an aliased or non-aliasedentity more specifically if those options include entity information.References: #3963

• [orm] [bug]

The attributes.flag_modified() function now raisesInvalidRequestError if the named attribute key is notpresent within the object, as this is assumed to be presentin the flush process. To mark an object “dirty” for a flushwithout referring to any specific attribute, theattributes.flag_dirty() function may be used.

See also

Use flag_dirty() to mark an object as “dirty” without any attribute changing

References: #3753

• [orm] [bug]

The “evaluate” strategy used by Query.update() andQuery.delete() can now accommodate a simpleobject comparison from a many-to-one relationship to an instance,when the attribute names of the primary key / foreign key columnsdon’t match the actual names of the columns. Previously this woulddo a simple name-based match and fail with an AttributeError.References: #3366

• [orm] [bug]

The @validates decorator now allows the decorated method to receiveobjects from a “bulk collection set” operation that have not yetbeen compared to the existing collection. This allows incoming valuesto be converted to compatible ORM objects as is already allowedfrom an “append” event. Note that this means that the@validates method is called for all values during a collectionassignment, rather than just the ones that are new.

See also

A @validates method receives all values on bulk-collection set before comparison

References: #3896

• [orm] [bug]

Fixed bug in single-table inheritance where the select_from()argument would not be taken into account when limiting rowsto a subclass. Previously, only expressions in thecolumns requested would be taken into account.

See also

Fixed issue involving single-table inheritance with select_from()

References: #3891

• [orm] [bug]

When assigning a collection to an attribute mapped by a relationship,the previous collection is no longer mutated. Previously, the oldcollection would be emptied out in conjunction with the “item remove”events that fire off; the events now fire off without affectingthe old collection.

See also

Previous collection is no longer mutated upon replacement

References: #3913

• [orm] [bug]

The state of the Session is now present when theSessionEvents.after_rollback() event is emitted, that is, theattribute state of objects prior to their being expired. This is nowconsistent with the behavior of theSessionEvents.after_commit() event which also emits before theattribute state of objects is expired.

See also

The after_rollback() Session event now emits before the expiration of objects

References: #3934

• [orm] [bug]

Fixed bug where Query.with_parent() would not work if theQuery were against an aliased() construct rather thana regular mapped class. Also adds a new parameterutil.with_parent.from_entity to the standaloneutil.with_parent() function as well asQuery.with_parent().References: #3607

orm declarative

• [bug] [declarative] [orm]

Fixed bug where using declared_attr on anAbstractConcreteBase where a particular return value were somenon-mapped symbol, including None, would cause the attributeto hard-evaluate just once and store the value to the objectdictionary, not allowing it to invoke for subclasses. This behavioris normal when declared_attr is on a mapped class, anddoes not occur on a mixin or abstract class. SinceAbstractConcreteBase is both “abstract” and actually“mapped”, a special exception case is made here so that the“abstract” behavior takes precedence for declared_attr.References: #3848

engine

• [engine] [feature]

Added native “pessimistic disconnection” handling to the Poolobject. The new parameter Pool.pre_ping, available fromthe engine as create_engine.pool_pre_ping, applies anefficient form of the “pre-ping” recipe featured in the poolingdocumentation, which upon each connection check out, emits a simplestatement, typically “SELECT 1”, to test the connection for liveness.If the existing connection is no longer able to respond to commands,the connection is transparently recycled, and all other connectionsmade prior to the current timestamp are invalidated.

See also

Disconnect Handling - Pessimistic

Pessimistic disconnection detection added to the connection pool

References: #3919

• [engine] [bug]

Added an exception handler that will warn for the “cause” exception onPy2K when the “autorollback” feature of Connection itselfraises an exception. In Py3K, the two exceptions are naturally reportedby the interpreter as one occurring during the handling of the other.This is continuing with the series of changes for rollback failurehandling that were last visited as part of #2696 in 1.0.12.This change is also backported to: 1.1.7

References: #3946

• [engine] [bug]

Fixed bug where in the unusual case of passing aCompiled object directly to Connection.execute(),the dialect with which the Compiled object were generatedwas not consulted for the paramstyle of the string statement, insteadassuming it would match the dialect-level paramstyle, causingmismatches to occur.References: #3938

sql

• [sql] [feature]

Added a new kind of bindparam() called “expanding”. This isfor use in IN expressions where the list of elements is renderedinto individual bound parameters at statement execution time, ratherthan at statement compilation time. This allows both a single boundparameter name to be linked to an IN expression of multiple elements,as well as allows query caching to be used with IN expressions. Thenew feature allows the related features of “select in” loading and“polymorphic in” loading to make use of the baked query extensionto reduce call overhead. This feature should be considered to beexperimental for 1.2.

See also

Late-expanded IN parameter sets allow IN expressions with cached statements

References: #3953

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

Added support for SQL comments on Table and Columnobjects, via the new Table.comment andColumn.comment arguments. The comments are includedas part of DDL on table creation, either inline or via an appropriateALTER statement, and are also reflected back within table reflection,as well as via the Inspector. Supported backends currentlyinclude MySQL, PostgreSQL, and Oracle. Many thanks to Frazer McLeanfor a large amount of effort on this.

See also

Support for SQL Comments on Table, Column, includes DDL, reflection

References: #1546

• [sql] [feature]

The longstanding behavior of the ColumnOperators.in_() andColumnOperators.notin_() operators emitting a warning whenthe right-hand condition is an empty sequence has been revised;a simple “static” expression of “1 != 1” or “1 = 1” is now renderedby default, rather than pulling in the original left-handexpression. This causes the result for a NULL column comparisonagainst an empty set to change from NULL to true/false. Thebehavior is configurable, and the old behavior can be enabledusing the create_engine.empty_in_strategy parameterto create_engine().

See also

The IN / NOT IN operator’s empty collection behavior is now configurable; default expression simplified

References: #3907

• [sql] [feature]

Added a new option autoescape to the “startswith” and“endswith” classes of comparators; this supplies an escape characteralso applies it to all occurrences of the wildcard characters “%”and “_” automatically. Pull request courtesy Diana Clarke.

Note

This feature has been changed as of 1.2.0 from its initialimplementation in 1.2.0b2 such that autoescape is now passed as aboolean value, rather than a specific character to use as the escapecharacter.

See also

New “autoescape” option for startswith(), endswith()

References: #2694

• [sql] [bug]

Fixed AttributeError which would occur in WithinGroupconstruct during an iteration of the structure.This change is also backported to: 1.1.11

References: #4012

• [sql] [bug]

Fixed regression released in 1.1.5 due to #3859 whereadjustments to the “right-hand-side” evaluation of an expressionbased on Variant to honor the underlying type’s“right-hand-side” rules caused the Variant typeto be inappropriately lost, in those cases when we do want theleft-hand side type to be transferred directly to the right hand sideso that bind-level rules can be applied to the expression’s argument.This change is also backported to: 1.1.9

References: #3952

• [sql] [bug] [postgresql]

Changed the mechanics of ResultProxy to unconditionallydelay the “autoclose” step until the Connection is donewith the object; in the case where PostgreSQL ON CONFLICT withRETURNING returns no rows, autoclose was occurring in this previouslynon-existent use case, causing the usual autocommit behavior thatoccurs unconditionally upon INSERT/UPDATE/DELETE to fail.This change is also backported to: 1.1.9

References: #3955

• [sql] [bug]

The rules for type coercion between Numeric, Integer,and date-related types now include additional logic that will attemptto preserve the settings of the incoming type on the “resolved” type.Currently the target for this is the asdecimal flag, so thata math operation between Numeric or Float andInteger will preserve the “asdecimal” flag as well asif the type should be the Float subclass.

See also

Stronger typing added to “float” datatypes

References: #4018

• [sql] [bug] [mysql]

The result processor for the Float type now unconditionallyruns values through the float() processor if the dialectspecifies that it also supports “native decimal” mode. While mostbackends will deliver Python float objects for a floating pointdatatype, the MySQL backends in some cases lack the typing informationin order to provide this and return Decimal unless the floatconversion is done.

See also

Stronger typing added to “float” datatypes

References: #4020

• [sql] [bug]

Added some extra strictness to the handling of Python “float” valuespassed to SQL statements. A “float” value will be associated with theFloat datatype and not the Decimal-coercing Numericdatatype as was the case before, eliminating a confusing warningemitted on SQLite as well as unnecessary coercion to Decimal.

See also

Stronger typing added to “float” datatypes

References: #4017

• [sql] [bug]

The operator precedence for all comparison operators such as LIKE, IS,IN, MATCH, equals, greater than, less than, etc. has all been mergedinto one level, so that expressions which make use of these againsteach other will produce parentheses between them. This suits thestated operator precedence of databases like Oracle, MySQL and otherswhich place all of these operators as equal precedence, as well asPostgreSQL as of 9.5 which has also flattened its operator precedence.

See also

Flattened operator precedence for comparison operators

References: #3999

• [sql] [bug]

Repaired issue where the type of an expression that usedColumnOperators.is_() or similar would not be a “boolean” type,instead the type would be “nulltype”, as well as when using customcomparison operators against an untyped expression. This typing canimpact how the expression behaves in larger contexts as well asin result-row-handling.References: #3873

• [sql] [bug]

Fixed the negation of a Label construct so that theinner element is negated correctly, when the not_() modifieris applied to the labeled expression.References: #3969

• [sql] [bug]

The system by which percent signs in SQL statements are “doubled”for escaping purposes has been refined. The “doubling” of percentsigns mostly associated with the literal_column constructas well as operators like ColumnOperators.contains() nowoccurs based on the stated paramstyle of the DBAPI in use; forpercent-sensitive paramstyles as are common with the PostgreSQLand MySQL drivers the doubling will occur, for others like thatof SQLite it will not. This allows more database-agnostic useof the literal_column construct to be possible.

See also

Percent signs in literal_column() now conditionally escaped

References: #3740

• [sql] [bug]

Fixed bug where a column-level CheckConstraint would failto compile the SQL expression using the underlying dialect compileras well as apply proper flags to generate literal values asinline, in the case that the sqltext is a Core expression andnot just a plain string. This was long-ago fixed for table-levelcheck constraints in 0.9 as part of #2742, which more commonlyfeature Core SQL expressions as opposed to plain string expressions.References: #3957

• [sql] [bug]

Fixed bug where a SQL-oriented Python-side column default could fail tobe executed properly upon INSERT in the “pre-execute” codepath, if theSQL itself were an untyped expression, such as plain text. The “pre-execute” codepath is fairly uncommon however can apply to non-integerprimary key columns with SQL defaults when RETURNING is not used.References: #3923

• [sql] [bug]

The expression used for COLLATE as rendered by the column-levelexpression.collate() and ColumnOperators.collate() is nowquoted as an identifier when the name is case sensitive, e.g. hasuppercase characters. Note that this does not impact type-levelcollation, which is already quoted.

See also

The column-level COLLATE keyword now quotes the collation name

References: #3785

• [sql] [bug]

Fixed bug where the use of an Alias object in a columncontext would raise an argument error when it tried to group itselfinto a parenthesized expression. Using Alias in this wayis not yet a fully supported API, however it applies to some end-userrecipes and may have a more prominent role in support of somefuture PostgreSQL features.References: #3939

schema

• [schema] [bug]

An ArgumentError is now raised if aForeignKeyConstraint object is created with a mismatchednumber of “local” and “remote” columns, which otherwise causes theinternal state of the constraint to be incorrect. Note that thisalso impacts the condition where a dialect’s reflection processproduces a mismatched set of columns for a foreign key constraint.This change is also backported to: 1.1.10

References: #3949

postgresql

• [postgresql] [bug]

Continuing with the fix that correctly handles PostgreSQLversion string “10devel” released in 1.1.8, an additional regexpbump to handle version strings of the form “10beta1”. WhilePostgreSQL now offers better ways to get this information, weare sticking w/ the regexp at least through 1.1.x for the leastamount of risk to compatibility w/ older or alternate PostgreSQLdatabases.This change is also backported to: 1.1.11

References: #4005

• [postgresql] [bug]

Fixed bug where using ARRAY with a string type thatfeatures a collation would fail to produce the correct syntaxwithin CREATE TABLE.This change is also backported to: 1.1.11

References: #4006

• [postgresql] [bug]

Added “autocommit” support for GRANT, REVOKE keywords. Pull requestcourtesy Jacob Hayes.This change is also backported to: 1.1.10

• [postgresql] [bug]

Added support for parsing the PostgreSQL version string fora development version like “PostgreSQL 10devel”. Pull requestcourtesy Sean McCully.This change is also backported to: 1.1.8

• [postgresql] [bug]

Fixed bug where the base sqltypes.ARRAY datatype would notinvoke the bind/result processors of postgresql.ARRAY.References: #3964

• [postgresql] [bug]

Added support for all possible “fields” identifiers when reflecting thePostgreSQL INTERVAL datatype, e.g. “YEAR”, “MONTH”, “DAY TOMINUTE”, etc.. In addition, the postgresql.INTERVALdatatype itself now includes a new parameterpostgresql.INTERVAL.fields where these qualifiers can bespecified; the qualifier is also reflected back into the resultingdatatype upon reflection / inspection.

See also

Support for fields specification in INTERVAL, including full reflection

References: #3959

mysql

• [mysql] [feature]

Added support for MySQL’s ON DUPLICATE KEY UPDATEMySQL-specific mysql.dml.Insert object.Pull request courtesy Michael Doronin.

See also

Support for INSERT..ON DUPLICATE KEY UPDATE

References: #4009

• [mysql] [bug]

MySQL 5.7 has introduced permission limiting for the “SHOW VARIABLES”command; the MySQL dialect will now handle when SHOW returns norow, in particular for the initial fetch of SQL_MODE, and willemit a warning that user permissions should be modified to allow therow to be present.This change is also backported to: 1.1.11

References: #4007

• [mysql] [bug]

Removed an ancient and unnecessary intercept of the UTC_TIMESTAMPMySQL function, which was getting in the way of using it with aparameter.This change is also backported to: 1.1.10

References: #3966

• [mysql] [bug]

Fixed bug in MySQL dialect regarding rendering of table options inconjunction with PARTITION options when rendering CREATE TABLE.The PARTITION related options need to follow the table options,whereas previously this ordering was not enforced.This change is also backported to: 1.1.10

References: #3961

• [mysql] [bug]

Added support for views that are unreflectable due to staletable definitions, when calling MetaData.reflect(); a warningis emitted for the table that cannot respond to DESCRIBE,but the operation succeeds.References: #3871

mssql

• [mssql] [bug]

Fixed bug where SQL Server transaction isolation must be fetchedfrom a different view when using Azure data warehouse, the queryis now attempted against both views and then a NotImplementedis raised unconditionally if failure continues to provide thebest resiliency against future arbitrary API changes in newSQL Server versions.This change is also backported to: 1.1.11

References: #3994

• [mssql] [bug]

Added a placeholder type mssql.XML to the SQL Serverdialect, so that a reflected table which includes this type canbe re-rendered as a CREATE TABLE. The type has no special round-tripbehavior nor does it currently support additional qualifyingarguments.This change is also backported to: 1.1.11

References: #3973

• [mssql] [bug]

The SQL Server dialect now allows for a database and/or owner namewith a dot inside of it, using brackets explicitly in the string aroundthe owner and optionally the database name as well. In addition,sending the quoted_name construct for the schema name willnot split on the dot and will deliver the full string as the “owner”.quoted_name is also now available from the sqlalchemy.sqlimport space.

See also

SQL Server schema names with embedded dots supported

References: #2626

oracle

• [oracle] [feature] [postgresql]

Added new keywords Sequence.cache andSequence.order to Sequence, to allow renderingof the CACHE parameter understood by Oracle and PostgreSQL, and theORDER parameter understood by Oracle. Pull requestcourtesy David Moore.This change is also backported to: 1.1.12

• [oracle] [feature]

The Oracle dialect now inspects unique and check constraints when usingInspector.get_unique_constraints(),Inspector.get_check_constraints().As Oracle does not have unique constraints that are separate from a uniqueIndex, a Table that’s reflected will still continueto not have UniqueConstraint objects associated with it.Pull requests courtesy Eloy Felix.

See also

Oracle Unique, Check constraints now reflected

References: #4003

• [oracle] [bug]

Support for two-phase transactions has been removed entirely forcx_Oracle when version 6.0b1 or later of the DBAPI is in use. The two-phase feature historically has never been usable under cx_Oracle 5.x inany case, and cx_Oracle 6.x has removed the connection-level “twophase”flag upon which this feature relied.This change is also backported to: 1.1.11

References: #3997

• [oracle] [bug]

Fixed bug in cx_Oracle dialect where version string parsing wouldfail for cx_Oracle version 6.0b1 due to the “b” character. Versionstring parsing is now via a regexp rather than a simple split.This change is also backported to: 1.1.10

References: #3975

• [oracle] [bug]

The cx_Oracle dialect now supports “sane multi rowcount”, that is,when a series of parameter sets are executed via DBAPIcursor.executemany(), we can make use of cursor.rowcount toverify the number of rows matched. This has an impact within theORM when detecting concurrent modification scenarios, in thatsome simple conditions can now be detected even when the ORMis batching statements, as well as when the more strict versioningfeature is used, the ORM can still use statement batching. Theflag is enabled for cx_Oracle assuming at least version 5.0, whichis now commonplace.References: #3932

• [oracle] [bug]

Oracle reflection now “normalizes” the name given to a foreign keyconstraint, that is, returns it as all lower case for a caseinsensitive name. This was already the behavior for indexesand primary key constraints as well as all table and column names.This will allow Alembic autogenerate scripts to compare and renderforeign key constraint names correctly when initially specifiedas case insensitive.

See also

Oracle foreign key constraint names are now “name normalized”

References: #3276

misc

• [feature] [ext]

Added new flag Session.enable_baked_queries to theSession to allow baked queries to be disabledsession-wide, reducing memory use. Also added new Bakerywrapper so that the bakery returned by BakedQuery.bakerycan be inspected.

• [bug] [ext]

Protected against testing “None” as a class in the case wheredeclarative classes are being garbage collected and newautomap prepare() operations are taking place concurrently, veryinfrequently hitting a weakref that has not been fully acted uponafter gc.This change is also backported to: 1.1.10

References: #3980

• [bug] [ext]

Fixed bug in sqlalchemy.ext.mutable where theMutable.as_mutable() method would not track a type that hadbeen copied using TypeEngine.copy(). This became more ofa regression in 1.1 compared to 1.0 because the TypeDecoratorclass is now a subclass of SchemaEventTarget, which amongother things indicates to the parent Column that the typeshould be copied when the Column is. These copies arecommon when using declarative with mixins or abstract classes.This change is also backported to: 1.1.8

References: #3950

• [bug] [ext]

Added support for bound parameters, e.g. those normally set upvia Query.params(), to the baked.Result.count()method. Previously, support for parameters were omitted. Pull requestcourtesy Pat Deegan.This change is also backported to: 1.1.8

• [bug] [ext]

The AssociationProxy.any(), AssociationProxy.has()and AssociationProxy.contains() comparison methods now supportlinkage to an attribute that is itself also anAssociationProxy, recursively.

See also

AssociationProxy any(), has(), contains() work with chained association proxies

References: #3769

• [bug] [ext]

Implemented in-place mutation operators ior, iand,ixor and isub for mutable.MutableSetand iadd for mutable.MutableList so that changeevents are fired off when these mutator methods are used to alter thecollection.

See also

In-place mutation operators work for MutableSet, MutableList

References: #3853

• [bug] [declarative]

A warning is emitted if the declaredattr.cascading modifieris used with a declarative attribute that is itself declared ona class that is to be mapped, as opposed to a declarative mixinclass or _abstract class. The declared_attr.cascadingmodifier currently only applies to mixin/abstract classes.References: #3847

• [bug] [ext]

Improved the association proxy list collection so that prematureautoflush against a newly created association object can be preventedin the case where list.append() is being used, and a lazy loadwould be invoked when the association proxy accesses the endpointcollection. The endpoint collection is now accessed first beforethe creator is invoked to produce the association object.References: #3941

• [bug] [ext]

The sqlalchemy.ext.hybrid.hybrid_property class now supportscalling mutators like @setter, @expression etc. multiple timesacross subclasses, and now provides a @getter mutator, so thata particular hybrid can be repurposed across subclasses or otherclasses. This now matches the behavior of @property in standardPython.

See also

Hybrid attributes support reuse among subclasses, redefinition of @getter

References: #3911, #3912

• [bug] [ext]

Fixed a bug in the sqlalchemy.ext.serializer extension wherebyan “annotated” SQL element (as produced by the ORM for many typesof SQL expressions) could not be reliably serialized. Also bumpedthe default pickle level for the serializer to “HIGHEST_PROTOCOL”.References: #3918