1.3 Changelog
- 1.3.11
  - engine
  - firebird
- 1.3.10
  - mssql
  - oracle
- 1.3.9
  - orm
  - engine
  - sql
  - sqlite
  - mssql
  - oracle
  - misc
- 1.3.8
- 1.3.7
- 1.3.6
- 1.3.5
- 1.3.4
- 1.3.3
- 1.3.2
- 1.3.1
  - orm
  - mssql
- 1.3.0
- 1.3.0b3
- 1.3.0b2
- 1.3.0b1

1.3 Changelog

1.3.11

no release date

engine

[engine] [bug]

Fixed bug where parameter repr as used in logging and error reporting needsadditional context in order to distinguish between a list of parameters fora single statement and a list of parameter lists, as the “list of lists”structure could also indicate a single parameter list where the firstparameter itself is a list, such as for an array parameter. Theengine/connection now passes in an additional boolean indicating how theparameters should be considered. The only SQLAlchemy backend that expectsarrays as parameters is that of psycopg2 which uses pyformat parameters,so this issue has not been too apparent, however as other drivers that usepositional gain more features it is important that this be supported. Italso eliminates the need for the parameter repr function to guess based onthe parameter structure passed.References: #4902

firebird

[firebird] [bug]

Added additional “disconnect” message “Error writing data to theconnection” to Firebird disconnection detection. Pull request courtesylukens.References: #4903

1.3.10

Released: October 9, 2019

mssql

[mssql] [bug]

Fixed bug in SQL Server dialect with new “max_identifier_length” featurewhere the mssql dialect already featured this flag, and the implementationdid not accommodate for the new initialization hook correctly.References: #4857

oracle

[oracle] [bug]

Fixed regression in Oracle dialect that was inadvertently using maxidentifier length of 128 characters on Oracle server 12.2 and greater eventhough the stated contract for the remainder of the 1.3 series is thatthis value stays at 30 until version SQLAlchemy 1.4. Also repaired issueswith the retrieval of the “compatibility” version, and removed the warningemitted when the “v$parameter” view was not accessible as this was causinguser confusion.References: #4857, #4898

1.3.9

Released: October 4, 2019

orm

[orm] [bug]

Fixed regression in selectinload loader strategy caused by #4775(released in version 1.3.6) where a many-to-one attribute of None would nolonger be populated by the loader. While this was usually not noticeabledue to the lazyloader populating None upon get, it would lead to a detachedinstance error if the object were detached.References: #4872

[orm] [bug]

Passing a plain string expression to Session.query() is deprecated,as all string coercions were removed in #4481 and this one shouldhave been included. The literal_column() function may be used toproduce a textual column expression.References: #4873

[orm] [bug]

A warning is emitted for a condition in which the Session mayimplicitly swap an object out of the identity map for another one with thesame primary key, detaching the old one, which can be an observed result ofload operations which occur within the SessionEvents.after_flush()hook. The warning is intended to notify the user that some specialcondition has caused this to happen and that the previous object may not bein the expected state.References: #4890

engine

[engine] [usecase]

Added new create_engine() parametercreate_engine.max_identifier_length. This overrides thedialect-coded “max identifier length” in order to accommodate for databasesthat have recently changed this length and the SQLAlchemy dialect hasnot yet been adjusted to detect for that version. This parameter interactswith the existing create_engine.label_length parameter in thatit establishes the maximum (and default) value for anonymously generatedlabels. Additionally, post-connection detection of max identifier lengthshas been added to the dialect system. This feature is first being usedby the Oracle dialect.

See also

Max Identifier Lengths - in the Oracle dialect documentation

References: #4857

sql

[sql] [bug]

Characters that interfere with “pyformat” or “named” formats in boundparameters, namely %, (, ) and the space character, as well as a fewother typically undesirable characters, are stripped early for abindparam() that is using an anonymized name, which is typicallygenerated automatically from a named column which itself includes thesecharacters in its name and does not use a .key, so that they do notinterfere either with the SQLAlchemy compiler’s use of string formatting orwith the driver-level parsing of the parameter, both of which could bedemonstrated before the fix. The change only applies to anonymizedparameter names that are generated and consumed internally, not end-userdefined names, so the change should have no impact on any existing code.Applies in particular to the psycopg2 driver which does not otherwise quotespecial parameter names, but also strips leading underscores to suit Oracle(but not yet leading numbers, as some anon parameters are currentlyentirely numeric/underscore based); Oracle in any case continues to quoteparameter names that include special characters.References: #4837

[sql] [usecase]

Added an explicit error message for the case when objects passed toTable are not SchemaItem objects, rather than resolvingto an attribute error.References: #4847

sqlite

[sqlite] [usecase]

Added support for sqlite “URI” connections, which allow for sqlite-specificflags to be passed in the query string such as “read only” for Pythonsqlite3 drivers that support this.

See also

URI Connections

References: #4863

mssql

[mssql] [bug]

Added identifier quoting to the schema name applied to the “use” statementwhich is invoked when a SQL Server multipart schema name is used within aTable that is being reflected, as well as for Inspectormethods such as Inspector.get_table_names(); this accommodates forspecial characters or spaces in the database name. Additionally, the “use”statement is not emitted if the current database matches the target ownerdatabase name being passed.References: #4883

oracle

[oracle] [bug]

Restored adding cx_Oracle.DATETIME to the setinputsizes() call when aSQLAlchemy Date, DateTime or Time datatype isused, as some complex queries require this to be present. This was removedin the 1.2 series for arbitrary reasons.References: #4886

[oracle] [usecase]

The Oracle dialect now emits a warning if Oracle version 12.2 or greater isused, and the create_engine.max_identifier_length parameter isnot set. The version in this specific case defaults to that of the“compatibility” version set in the Oracle server configuration, not theactual server version. In version 1.4, the default max_identifier_lengthfor 12.2 or greater will move to 128 characters. In order to maintainforwards compatibility, applications should setcreate_engine.max_identifier_length to 30 in order to maintainthe same length behavior, or to 128 in order to test the upcoming behavior.This length determines among other things how generated constraint namesare truncated for statements like CREATE CONSTRAINT and DROPCONSTRAINT, which means a the new length may produce a name-mismatchagainst a name that was generated with the old length, impacting databasemigrations.

See also

Max Identifier Lengths - in the Oracle dialect documentation

References: #4857

misc

[bug] [tests]

Fixed unit test regression released in 1.3.8 that would cause failure forOracle, SQL Server and other non-native ENUM platforms due to newenumeration tests added as part of #4285 enum sortability in theunit of work; the enumerations created constraints that were duplicated onname.References: #4285

1.3.8

Released: August 27, 2019

orm

[orm] [bug]

Fixed bug where Load objects were not pickleable due tomapper/relationship state in the internal context dictionary. Theseobjects are now converted to picklable using similar techniques as that ofother elements within the loader option system that have long beenserializable.References: #4823

[orm] [usecase]

Added support for the use of an Enum datatype using Pythonpep-435 enumeration objects as values for use as a primary key columnmapped by the ORM. As these values are not inherently sortable, asrequired by the ORM for primary keys, a newTypeEngine.sort_key_function attribute is added to the typingsystem which allows any SQL type to implement a sorting for Python objectsof its type which is consulted by the unit of work. The Enumtype then defines this using the database value of a given enumeration.The sorting scheme can be also be redefined by passing a callable to theEnum.sort_key_function parameter. Pull request courtesyNicolas Caniart.References: #4285

engine

[engine] [feature]

Added new parameter create_engine.hide_parameters which whenset to True will cause SQL parameters to no longer be logged, nor renderedin the string representation of a StatementError object.References: #4815

[engine] [bug]

Fixed an issue whereby if the dialect “initialize” process which occurs onfirst connect would encounter an unexpected exception, the initializeprocess would fail to complete and then no longer attempt on subsequentconnection attempts, leaving the dialect in an un-initialized, or partiallyinitialized state, within the scope of parameters that need to beestablished based on inspection of a live connection. The “invoke once”logic in the event system has been reworked to accommodate for thisoccurrence using new, private API features that establish an “exec once”hook that will continue to allow the initializer to fire off on subsequentconnections, until it completes without raising an exception. This does notimpact the behavior of the existing once=True flag within the eventsystem.References: #4807

postgresql

[postgresql] [bug]

Revised the approach for the just added support for the psycopg2“execute_values()” feature added in 1.3.7 for #4623. The approachrelied upon a regular expression that would fail to match for a morecomplex INSERT statement such as one which had subqueries involved. Thenew approach matches exactly the string that was rendered as the VALUESclause.References: #4623

[postgresql] [bug]

Fixed bug where Postgresql operators such aspostgresql.ARRAY.Comparator.contains() andpostgresql.ARRAY.Comparator.contained_by() would fail to functioncorrectly for non-integer values when used against apostgresql.array object, due to an erroneous assert statement.References: #4822

[postgresql] [usecase]

Added support for reflection of CHECK constraints that include the specialPostgreSQL qualifier “NOT VALID”, which can be present for CHECKconstraints that were added to an exsiting table with the directive thatthey not be applied to existing data in the table. The PostgreSQLdictionary for CHECK constraints as returned byInspector.get_check_constraints() may include an additional entrydialect_options which within will contain an entry "not_valid":True if this symbol is detected. Pull request courtesy Bill Finn.References: #4824

sqlite

[sqlite] [bug] [reflection]

Fixed bug where a FOREIGN KEY that was set up to refer to the parent tableby table name only without the column names would not correctly bereflected as far as setting up the “referred columns”, since SQLite’sPRAGMA does not report on these columns if they weren’t given explicitly.For some reason this was harcoded to assume the name of the local column,which might work for some cases but is not correct. The new approachreflects the primary key of the referred table and uses the constraintcolumns list as the referred columns list, if the remote column(s) aren’tpresent in the reflected pragma directly.References: #4810

1.3.7

Released: August 14, 2019

orm

[orm] [bug]

Fixed regression caused by new selectinload for many-to-one logic wherea primaryjoin condition not based on real foreign keys would causeKeyError if a related object did not exist for a given key value on theparent object.References: #4777

[orm] [bug]

Fixed bug where using Query.first() or a slice expression inconjunction with a query that has an expression based “offset” appliedwould raise TypeError, due to an “or” conditional against “offset” that didnot expect it to be a SQL expression as opposed to an integer or None.References: #4803

sql

[sql] [bug]

Fixed issue where Index object which contained a mixture offunctional expressions which were not resolvable to a particular column,in combination with string-based column names, would fail to initializeits internal state correctly leading to failures during DDL compilation.References: #4778

[sql] [bug]

Fixed bug where TypeEngine.column_expression() method would not beapplied to subsequent SELECT statements inside of a UNION or otherCompoundSelect, even though the SELECT statements are rendered atthe topmost level of the statement. New logic now differentiates betweenrendering the column expression, which is needed for all SELECTs in thelist, vs. gathering the returned data type for the result row, which isneeded only for the first SELECT.References: #4787

[sql] [bug]

Fixed issue where internal cloning of SELECT constructs could lead to a keyerror if the copy of the SELECT changed its state such that its list ofcolumns changed. This was observed to be occurring in some ORM scenarioswhich may be unique to 1.3 and above, so is partially a regression fix.References: #4780

postgresql

[postgresql] [usecase]

Added new dialect flag for the psycopg2 dialect, executemany_mode whichsupersedes the previous experimental use_batch_mode flag.executemany_mode supports both the “execute batch” and “execute values”functions provided by psycopg2, the latter which is used for compiledinsert() constructs. Pull request courtesy Yuval Dinari.

See also

Psycopg2 Fast Execution Helpers

References: #4623

mysql

[mysql] [bug]

The MySQL dialects will emit “SET NAMES” at the start of a connection whencharset is given to the MySQL driver, to appease an apparent behaviorobserved in MySQL 8.0 that raises a collation error when a UNION includesstring columns unioned against columns of the form CAST(NULL AS CHAR(..)),which is what SQLAlchemy’s polymorphic_union function does. The issueseems to have affected PyMySQL for at least a year, however has recentlyappeared as of mysqlclient 1.4.4 based on changes in how this DBAPI createsa connection. As the presence of this directive impacts three separateMySQL charset settings which each have intricate effects based on theirpresense, SQLAlchemy will now emit the directive on new connections toensure correct behavior.References: #4804

[mysql] [bug]

Added another fix for an upstream MySQL 8 issue where a case sensitivetable name is reported incorrectly in foreign key constraint reflection,this is an extension of the fix first added for #4344 whichaffects a case sensitive column name. The new issue occurs through MySQL8.0.17, so the general logic of the 88718 fix remains in place.

References: #4751

[mysql] [usecase]

Added reserved words ARRAY and MEMBER to the MySQL reserved words list, asMySQL 8.0 has now made these reserved.References: #4783

sqlite

[sqlite] [bug]

The dialects that support json are supposed to take argumentsjson_serializer and json_deserializer at the create_engine() level,however the SQLite dialect calls them _json_serilizer and_json_deserilalizer. The names have been corrected, the old names areaccepted with a change warning, and these parameters are now documented ascreate_engine.json_serializer andcreate_engine.json_deserializer.References: #4798

[sqlite] [bug]

Fixed bug where usage of “PRAGMA table_info” in SQLite dialect meant thatreflection features to detect for table existence, list of table columns,and list of foreign keys, would default to any table in any attacheddatabase, when no schema name was given and the table did not exist in thebase schema. The fix explicitly runs PRAGMA for the ‘main’ schema and thenthe ‘temp’ schema if the ‘main’ returned no rows, to maintain the behaviorof tables + temp tables in the “no schema” namespace, attached tables onlyin the “schema” namespace.References: #4793

mssql

[mssql] [usecase]

Added new mssql.try_cast() construct for SQL Server which emits“TRY_CAST” syntax. Pull request courtesy Leonel Atencio.References: #4782

misc

[bug] [events]

Fixed issue in event system where using the once=True flag withdynamically generated listener functions would cause event registration offuture events to fail if those listener functions were garbage collectedafter they were used, due to an assumption that a listened function isstrongly referenced. The “once” wrapped is now modified to stronglyreference the inner function persistently, and documentation is updatedthat using “once” does not imply automatic de-registration of listenerfunctions.References: #4794

1.3.6

Released: July 21, 2019

orm

[orm] [feature]

Added new loader option method Load.options() which allows loaderoptions to be constructed hierarchically, so that many sub-options can beapplied to a particular path without needing to call defaultload()many times. Thanks to Alessio Bogon for the idea.References: #4736

[orm] [bug]

Fixed regression caused by #4365 where a join from an entity toitself without using aliases no longer raises an informative error message,instead failing on an assertion. The informative error condition has beenrestored.References: #4773

[orm] [bug]

Fixed an issue where the orm._ORMJoin.join() method, which is anot-internally-used ORM-level method that exposes what is normally aninternal process of Query.join(), did not propagate the full andouterjoin keyword arguments correctly. Pull request courtesy DenisKataev.References: #4713

[orm] [bug]

Fixed bug where a many-to-one relationship that specified uselist=Truewould fail to update correctly during a primary key change where a relatedcolumn needs to change.References: #4772

[orm] [bug]

Fixed bug where the detection for many-to-one or one-to-one use with a“dynamic” relationship, which is an invalid configuration, would fail toraise if the relationship were configured with uselist=True. Thecurrent fix is that it warns, instead of raises, as this would otherwise bebackwards incompatible, however in a future release it will be a raise.References: #4772

[orm] [bug]

Fixed bug where a synonym created against a mapped attribute that does notexist yet, as is the case when it refers to backref before mappers areconfigured, would raise recursion errors when trying to test for attributeson it which ultimately don’t exist (as occurs when the classes are runthrough Sphinx autodoc), as the unconfigured state of the synonym would putit into an attribute not found loop.References: #4767

[orm] [performance]

The optimization applied to selectin loading in #4340 where a JOINis not needed to eagerly load related items is now applied to many-to-onerelationships as well, so that only the related table is queried for asimple join condition. In this case, the related items are queriedbased on the value of a foreign key column on the parent; if these columnsare deferred or otherwise not loaded on any of the parent objects inthe collection, the loader falls back to the JOIN method.References: #4775

engine

[engine] [bug]

Fixed bug where using reflection function such as MetaData.reflect()with an Engine object that had execution options applied to itwould fail, as the resulting OptionEngine proxy object failed toinclude a .engine attribute used within the reflection routines.References: #4754

sql

[sql] [bug]

Adjusted the initialization for Enum to minimize how often itinvokes the .members attribute of a given PEP-435 enumerationobject, to suit the case where this attribute is expensive to invoke, as isthe case for some popular third party enumeration libraries.References: #4758

[sql] [bug] [postgresql]

Fixed issue where the array_agg construct in combination withFunctionElement.filter() would not produce the correct operatorprecedence in combination with the array index operator.References: #4760

[sql] [bug]

Fixed an unlikely issue where the “corresponding column” routine for unionsand other CompoundSelect objects could return the wrong column insome overlapping column situtations, thus potentially impacting some ORMoperations when set operations are in use, if the underlyingselect() constructs were used previously in other similar kinds ofroutines, due to a cached value not being cleared.References: #4747

postgresql

[postgresql] [usecase]

Added support for reflection of indexes on PostgreSQL partitioned tables,which was added to PostgreSQL as of version 11.References: #4771

[postgresql] [usecase]

Added support for multidimensional Postgresql array literals via nestingthe postgresql.array object within another one. Themultidimensional array type is detected automatically.

See also

postgresql.array

References: #4756

mysql

[mysql] [bug]

Fixed bug where the special logic to render “NULL” for theTIMESTAMP datatype when nullable=True would not work if thecolumn’s datatype were a TypeDecorator or a Variant.The logic now ensures that it unwraps down to the originalTIMESTAMP so that this special case NULL keyword is correctlyrendered when requested.References: #4743

[mysql] [bug]

Enhanced MySQL/MariaDB version string parsing to accommodate for exoticMariaDB version strings where the “MariaDB” word is embedded among otheralphanumeric characters such as “MariaDBV1”. This detection is critical inorder to correctly accommodate for API features that have split between MySQLand MariaDB such as the “transaction_isolation” system variable.References: #4624

sqlite

[sqlite] [usecase]

Added support for composite (tuple) IN operators with SQLite, by renderingthe VALUES keyword for this backend. As other backends such as DB2 areknown to use the same syntax, the syntax is enabled in the base compilerusing a dialect-level flag tuplein_values. The change also includessupport for “empty IN tuple” expressions for SQLite when using “in()”between a tuple value and an empty set.References: #4766

mssql

[mssql] [bug]

Ensured that the queries used to reflect indexes and view definitions willexplicitly CAST string parameters into NVARCHAR, as many SQL Server driversfrequently treat string values, particularly those with non-asciicharacters or larger string values, as TEXT which often don’t comparecorrectly against VARCHAR characters in SQL Server’s information schematables for some reason. These CAST operations already take place forreflection queries against SQL Server information_schema. tables butwere missing from three additional queries that are against sys.tables.References: #4745

1.3.5

Released: June 17, 2019

orm

[orm] [bug]

Fixed a series of related bugs regarding joined table inheritance more thantwo levels deep, in conjunction with modification to primary key values,where those primary key columns are also linked together in a foreign keyrelationship as is typical for joined table inheritance. The intermediarytable in a three-level inheritance hierarchy will now get its UPDATE ifonly the primary key value has changed and passive_updates=False (e.g.foreign key constraints not being enforced), whereas before it would beskipped; similarly, with passive_updates=True (e.g. ON UPDATE CASCADE ineffect), the third-level table will not receive an UPDATE statement as wasthe case earlier which would fail since CASCADE already modified it. In arelated issue, a relationship linked to a three-level inheritance hierarchyon the primary key of an intermediary table of a joined-inheritancehierarchy will also correctly have its foreign key column updated when theparent object’s primary key is modified, even if that parent object is asubclass of the linked parent class, whereas before these classes wouldnot be counted.References: #4723

[orm] [bug]

Fixed bug where the Mapper.all_orm_descriptors accessor wouldreturn an entry for the Mapper itself under the declarativemapper_ key, when this is not a descriptor. The .is_attributeflag that’s present on all InspectionAttr objects is nowconsulted, which has also been modified to be True for an associationproxy, as it was erroneously set to False for this object.References: #4729

[orm] [bug]

Fixed regression in Query.join() where the aliased=True flagwould not properly apply clause adaptation to filter criteria, if aprevious join were made to the same entity. This is because the adapterswere placed in the wrong order. The order has been reversed so that theadapter for the most recent aliased=True call takes precedence as wasthe case in 1.2 and earlier. This broke the “elementtree” examples amongother things.References: #4704

[orm] [bug] [py3k]

Replaced the Python compatbility routines for getfullargspec() with afully vendored version from Python 3.3. Originally, Python was emittingdeprecation warnings for this function in Python 3.8 alphas. While thischange was reverted, it was observed that Python 3 implementations forgetfullargspec() are an order of magnitude slower as of the 3.4 serieswhere it was rewritten against Signature. While Python plans toimprove upon this situation, SQLAlchemy projects for now are using a simplereplacement to avoid any future issues.References: #4674

[orm] [bug]

Reworked the attribute mechanics used by AliasedClass to nolonger rely upon calling getattribute on the MRO of the wrappedclass, and to instead resolve the attribute normally on the wrapped classusing getattr(), and then unwrap/adapt that. This allows a greater rangeof attribute styles on the mapped class including special getattr()schemes; but it also makes the code simpler and more resilient in general.References: #4694

sql

[sql] [bug]

Fixed a series of quoting issues which all stemmed from the concept of theliteral_column() construct, which when being “proxied” through asubquery to be referred towards by a label that matches its text, the labelwould not have quoting rules applied to it, even if the string in theLabel were set up as a quoted_name construct. Notapplying quoting to the text of the Label is a bug because thistext is strictly a SQL identifier name and not a SQL expression, and thestring should not have quotes embedded into it already unlike theliteral_column() which it may be applied towards. The existingbehavior of a non-labeled literal_column() being propagated as is onthe outside of a subquery is maintained in order to help with manualquoting schemes, although it’s not clear if valid SQL can be generated forsuch a construct in any case.References: #4730

postgresql

[postgresql] [bug]

Fixed bug where PostgreSQL dialect could not correctly reflect an ENUMdatatype that has no members, returning a list with None for theget_enums() call and raising a TypeError when reflecting a column whichhas such a datatype. The inspection now returns an empty list.References: #4701

[postgresql] [usecase]

Added support for column sorting flags when reflecting indexes forPostgreSQL, including ASC, DESC, NULLSFIRST, NULLSLAST. Also adds thisfacility to the reflection system in general which can be applied to otherdialects in future releases. Pull request courtesy Eli Collins.References: #4717

mysql

[mysql] [bug]

Fixed bug where MySQL ON DUPLICATE KEY UPDATE would not accommodate settinga column to the value NULL. Pull request courtesy Lukáš Banič.References: #4715

1.3.4

Released: May 27, 2019

orm

[orm] [bug]

Fixed issue where the AttributeEvents.active_history flagwould not be set for an event listener that propgated to a subclass via theAttributeEvents.propagate flag. This bug has been presentfor the full span of the AttributeEvents system.References: #4695

[orm] [bug]

Fixed regression where new association proxy system was still not proxyinghybrid attributes when they made use of the @hybrid_property.expressiondecorator to return an alternate SQL expression, or when the hybridreturned an arbitrary PropComparator, at the expression level.This involved further generalization of the heuristics used to detect thetype of object being proxied at the level of QueryableAttribute,to better detect if the descriptor ultimately serves mapped classes orcolumn expressions.References: #4690

[orm] [bug]

Applied the mapper “configure mutex” against the declarative class mappingprocess, to guard against the race which can occur if mappers are usedwhile dynamic module import schemes are still in the process of configuringmappers for related classes. This does not guard against all possible raceconditions, such as if the concurrent import has not yet encountered thedependent classes as of yet, however it guards against as much as possiblewithin the SQLAlchemy declarative process.References: #4686

[orm] [bug]

A warning is now emitted for the case where a transient object is beingmerged into the session with Session.merge() when that object isalready transient in the Session. This warns for the case wherethe object would normally be double-inserted.References: #4647

[orm] [bug]

Fixed regression in new relationship m2o comparison logic first introducedat Improvement to the behavior of many-to-one query expressions when comparing to an attribute that is persisted asNULL and is in an un-fetched state in the mapped instance. Since theattribute has no explicit default, it needs to default to NULL whenaccessed in a persistent setting.References: #4676

engine

[engine] [bug] [postgresql]

Moved the “rollback” which occurs during dialect initialization so that itoccurs after additional dialect-specific initialize steps, in particularthose of the psycopg2 dialect which would inadvertently leave transactionalstate on the first new connection, which could interfere with somepsycopg2-specific APIs which require that no transaction is started. Pullrequest courtesy Matthew Wilkes.References: #4663

sql

[sql] [bug]

Fixed that the GenericFunction class was inadvertentlyregistering itself as one of the named functions. Pull request courtesyAdrien Berchet.References: #4653

[sql] [bug]

Fixed issue where double negation of a boolean column wouldn’t resetthe “NOT” operator.References: #4618

[sql] [bug]

The GenericFunction namespace is being migrated so that functionnames are looked up in a case-insensitive manner, as SQL functions do notcollide on case sensitive differences nor is this something which wouldoccur with user-defined functions or stored procedures. Lookups forfunctions declared with GenericFunction now use a caseinsensitive scheme, however a deprecation case is supported which allowstwo or more GenericFunction objects with the same name ofdifferent cases to exist, which will cause case sensitive lookups to occurfor that particular name, while emitting a warning at function registrationtime. Thanks to Adrien Berchet for a lot of work on this complicatedfeature.References: #4569

postgresql

[postgresql] [bug] [orm]

Fixed an issue where the “number of rows matched” warning would emit even ifthe dialect reported “supports_sane_multi_rowcount=False”, as is the casefor psycogp2 with use_batch_mode=True and others.References: #4661

mysql

[mysql] [bug]

Added support for DROP CHECK constraint which is required by MySQL 8.0.16to drop a CHECK constraint; MariaDB supports plain DROP CONSTRAINT. Thelogic distinguishes between the two syntaxes by checking the server versionstring for MariaDB presence. Alembic migrations has already workedaround this issue by implementing its own DROP for MySQL / MariaDB CHECKconstraints, however this change implements it straight in Core so that itsavailable for general use. Pull request courtesy Hannes Hansen.References: #4650

mssql

[mssql] [feature]

Added support for SQL Server filtered indexes, via the mssql_whereparameter which works similarly to that of the postgresql_where indexfunction in the PostgreSQL dialect.

See also

Filtered Indexes

References: #4657

[mssql] [bug]

Added error code 20047 to “is_disconnect” for pymssql. Pull requestcourtesy Jon Schuff.References: #4680

misc

[misc] [bug]

Removed errant “sqla_nose.py” symbol from MANIFEST.in which created anundesirable warning message.References: #4625

1.3.3

Released: April 15, 2019

orm

[orm] [bug]

Fixed 1.3 regression in new “ambiguous FROMs” query logic introduced inQuery.join() handles ambiguity in deciding the “left” side more explicitly where a Query that explicitly places an entityin the FROM clause with Query.select_from() and also joins to itusing Query.join() would later cause an “ambiguous FROM” error ifthat entity were used in additional joins, as the entity appears twice inthe “from” list of the Query. The fix resolves this ambiguity byfolding the standalone entity into the join that it’s already a part of inthe same way that ultimately happens when the SELECT statement is rendered.References: #4584

[orm] [bug]

Adjusted the Query.filter_by() method to not call and()internally against multiple criteria, instead passing it off toQuery.filter() as a series of criteria, instead of a single criteria.This allows Query.filter_by() to defer to Query.filter()’streatment of variable numbers of clauses, including the case where the listis empty. In this case, the Query object will not have a.whereclause, which allows subsequent “no whereclause” methods likeQuery.select_from() to behave consistently.References: #4606

postgresql

[postgresql] [bug]

Fixed regression from release 1.3.2 caused by #4562 where a URLthat contained only a query string and no hostname, such as for thepurposes of specifying a service file with connection information, would nolonger be propagated to psycopg2 properly. The change in #4562has been adjusted to further suit psycopg2’s exact requirements, which isthat if there are any connection parameters whatsoever, the “dsn” parameteris no longer required, so in this case the query string parameters arepassed alone.References: #4601

mssql

[mssql] [bug]

Fixed issue in SQL Server dialect where if a bound parameter were present inan ORDER BY expression that would ultimately not be rendered in the SQLServer version of the statement, the parameters would still be part of theexecution parameters, leading to DBAPI-level errors. Pull request courtesyMatt Lewellyn.References: #4587

misc

[bug] [pool]

Fixed behavioral regression as a result of deprecating the “use_threadlocal”flag for Pool, where the SingletonThreadPool no longermakes use of this option which causes the “rollback on return” logic to takeplace when the same Engine is used multiple times in the contextof a transaction to connect or implicitly execute, thereby cancelling thetransaction. While this is not the recommended way to work with enginesand connections, it is nonetheless a confusing behavioral change as whenusing SingletonThreadPool, the transaction should stay openregardless of what else is done with the same engine in the same thread.The use_threadlocal flag remains deprecated however theSingletonThreadPool now implements its own version of the samelogic.References: #4585

[bug] [ext]

Fixed bug where using copy.copy() or copy.deepcopy() onMutableList would cause the items within the list to beduplicated, due to an inconsistency in how Python pickle and copy both makeuse of getstate() and setstate() regarding lists. In orderto resolve, a reduce_ex method had to be added toMutableList. In order to maintain backwards compatibility withexisting pickles based on getstate(), the setstate() methodremains as well; the test suite asserts that pickles made against the oldversion of the class can still be deserialized by the pickle module.References: #4603

1.3.2

Released: April 2, 2019

orm

[orm] [bug] [ext]

Restored instance-level support for plain Python descriptors, e.g.@property objects, in conjunction with association proxies, in that ifthe proxied object is not within ORM scope at all, it gets classified as“ambiguous” but is proxed directly. For class level access, a basic classlevel__get__() now returns theAmbiguousAssociationProxyInstance directly, rather than raisingits exception, which is the closest approximation to the previous behaviorthat returned the AssociationProxy itself that’s possible. Alsoimproved the stringification of these objects to be more descriptive ofcurrent state.References: #4573, #4574

[orm] [bug]

Fixed bug where use of with_polymorphic() or other aliased constructwould not properly adapt when the aliased target were used as theSelect.correlate_except() target of a subquery used inside of acolumn_property(). This required a fix to the clause adaptionmechanics to properly handle a selectable that shows up in the “correlateexcept” list, in a similar manner as which occurs for selectables that showup in the “correlate” list. This is ultimately a fairly fundamental bugthat has lasted for a long time but it is hard to come across it.References: #4537

[orm] [bug]

Fixed regression where a new error message that was supposed to raise whenattempting to link a relationship option to an AliasedClass without usingPropComparator.of_type() would instead raise an AttributeError.Note that in 1.3, it is no longer valid to create an option path from aplain mapper relationship to an AliasedClass without usingPropComparator.of_type().References: #4566

sql

[sql] [bug] [documentation]

Thanks to TypeEngine methods bind_expression, column_expression work with Variant, type-specific types, we no longer need to rely on recipes thatsubclass dialect-specific types directly, TypeDecorator can nowhandle all cases. Additionally, the above change made it slightly lesslikely that a direct subclass of a base SQLAlchemy type would work asexpected, which could be misleading. Documentation has been updated to useTypeDecorator for these examples including the PostgreSQL“ArrayOfEnum” example datatype and direct support for the “subclass a typedirectly” has been removed.References: #4580

postgresql

[postgresql] [feature]

Added support for parameter-less connection URLs for the psycopg2 dialect,meaning, the URL can be passed to create_engine() as"postgresql+psycopg2://" with no additional arguments to indicate anempty DSN passed to libpq, which indicates to connect to “localhost” withno username, password, or database given. Pull request courtesy JulianMehnle.References: #4562

[postgresql] [bug]

Modified the Select.with_for_update.of parameter so that if ajoin or other composed selectable is passed, the individual Tableobjects will be filtered from it, allowing one to pass a join() object tothe parameter, as occurs normally when using joined table inheritance withthe ORM. Pull request courtesy Raymond Lu.References: #4550

1.3.1

Released: March 9, 2019

orm

[orm] [bug] [ext]

Fixed regression where an association proxy linked to a synonym would nolonger work, both at instance level and at class level.References: #4522

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.This change is also backported to: 1.2.19

References: #4536

[mssql] [bug]

Fixed regression in SQL Server reflection due to #4393 where theremoval of open-ended **kw from the Float datatype causedreflection of this type to fail due to a “scale” argument being passed.References: #4525

1.3.0

Released: March 4, 2019

orm

[orm] [feature]

The Query.get() method can now accept a dictionary of attribute keysand values as a means of indicating the primary key value to load; isparticularly useful for composite primary keys. Pull request courtesySanjana S.References: #4316

[orm] [feature]

A SQL expression can now be assigned to a primary key attribute for an ORMflush in the same manner as ordinary attributes as described inEmbedding SQL Insert/Update Expressions into a Flush where the expression will be evaulatedand then returned to the ORM using RETURNING, or in the case of pysqlite,works using the cursor.lastrowid attribute.Requires either a database thatsupports RETURNING (e.g. Postgresql, Oracle, SQL Server) or pysqlite.References: #3133

engine

[engine] [feature]

Revised the formatting for StatementError when stringified. Eacherror detail is broken up over multiple newlines instead of spaced out on asingle line. Additionally, the SQL representation now stringifies the SQLstatement rather than using repr(), so that newlines are rendered as is.Pull request courtesy Nate Clark.

References: #4500

sql

[sql] [bug]

The Alias class and related subclasses CTE,Lateral and TableSample have been reworked so that it isnot possible for a user to construct the objects directly. These constructsrequire that the standalone construction function or selectable-bound methodbe used to instantiate new objects.References: #4509

schema

[schema] [feature]

Added new parameters Table.resolve_fks andMetaData.reflect.resolve_fks which when set to False willdisable the automatic reflection of related tables encountered inForeignKey objects, which can both reduce SQL overhead for omittedtables as well as avoid tables that can’t be reflected for database-specificreasons. Two Table objects present in the same MetaDatacollection can still refer to each other even if the reflection of the twotables occurred separately.References: #4517

1.3.0b3

Released: February 8, 2019

orm

[orm] [bug]

Improved the behavior of orm.with_polymorphic() in conjunction withloader options, in particular wildcard operations as well asorm.load_only(). The polymorphic object will be more accuratelytargeted so that column-level options on the entity will correctly takeeffect.The issue is a continuation of the same kinds of things fixed in#4468.References: #4469

orm declarative

[bug] [declarative] [orm]

Added some helper exceptions that invoke when a mapping based onAbstractConcreteBase, DeferredReflection, orAutoMap is used before the mapping is ready to be used, whichcontain descriptive information on the class, rather than falling throughinto other failure modes that are less informative.References: #4470

sql

[sql] [bug]

Fully removed the behavior of strings passed directly as components of aselect() or Query object being coerced to text()constructs automatically; the warning that has been emitted is now anArgumentError or in the case of order_by() / group_by() a CompileError.This has emitted a warning since version 1.0 however its presence continuesto create concerns for the potential of mis-use of this behavior.

Note that public CVEs have been posted for order_by() / group_by() whichare resolved by this commit: CVE-2019-7164 CVE-2019-7548

References: #4481

[sql] [bug]

Quoting is applied to Function names, those which are usually butnot necessarily generated from the sql.func construct, at compiletime if they contain illegal characters, such as spaces or punctuation. Thenames are as before treated as case insensitive however, meaning if thenames contain uppercase or mixed case characters, that alone does nottrigger quoting. The case insensitivity is currently maintained forbackwards compatibility.References: #4467

[sql] [bug]

Added “SQL phrase validation” to key DDL phrases that are accepted as plainstrings, including ForeignKeyConstraint.on_delete,ForeignKeyConstraint.on_update,ExcludeConstraint.using,ForeignKeyConstraint.initially, for areas where a series of SQLkeywords only are expected.Any non-space characters that suggest the phrasewould need to be quoted will raise a CompileError. This changeis related to the series of changes committed as part of #4481.References: #4481

postgresql

[postgresql] [bug]

Fixed issue where using an uppercase name for an index type (e.g. GIST,BTREE, etc. ) or an EXCLUDE constraint would treat it as an identifier tobe quoted, rather than rendering it as is. The new behavior converts thesetypes to lowercase and ensures they contain only valid SQL characters.References: #4473

misc

[bug] [ext]

Implemented a more comprehensive assignment operation (e.g. “bulk replace”)when using association proxy with sets or dictionaries. Fixes the problemof redundant proxy objects being created to replace the old ones, whichleads to excessive events and SQL and in the case of unique constraintswill cause the flush to fail.

References: #2642

[change] [tests]

The test system has removed support for Nose, which is unmaintained forseveral years and is producing warnings under Python 3. The test suite iscurrently standardized on Pytest. Pull request courtesy Parth Shandilya.References: #4460

1.3.0b2

Released: January 25, 2019

general

[general] [change]

A large change throughout the library has ensured that all objects,parameters, and behaviors which have been noted as deprecated or legacy nowemit DeprecationWarning warnings when invoked.As the Python 3interpreter now defaults to displaying deprecation warnings, as well as thatmodern test suites based on tools like tox and pytest tend to displaydeprecation warnings, this change should make it easier to note what APIfeatures are obsolete. A major rationale for this change is so that long-deprecated features that nonetheless still see continue to see real worlduse can finally be removed in the near future; the biggest example of thisare the SessionExtension and MapperExtension classes aswell as a handful of other pre-event extension hooks, which have beendeprecated since version 0.7 but still remain in the library. Another isthat several major longstanding behaviors are to be deprecated as well,including the threadlocal engine strategy, the convert_unicode flag, and nonprimary mappers.

References: #4393

orm

[orm] [feature]

Implemented a new feature whereby the AliasedClass construct cannow be used as the target of a relationship(). This allows theconcept of “non primary mappers” to no longer be necessary, as theAliasedClass is much easier to configure and automatically inheritsall the relationships of the mapped class, as well as preserves theability for loader options to work normally.

References: #4423

[orm] [feature]

Added new MapperEvents.before_mapper_configured() event. Thisevent complements the other “configure” stage mapper events with a permapper event that receives each Mapper right before itsconfigure step, and additionally may be used to prevent or delay theconfiguration of specific Mapper objects using a newreturn value orm.interfaces.EXT_SKIP. See thedocumentation link for an example.

References: #4397

[orm] [bug]

Fixed long-standing issue where duplicate collection members would cause abackref to delete the association between the member and its parent objectwhen one of the duplicates were removed, as occurs as a side effect ofswapping two objects in one statement.

References: #1103

[orm] [bug]

Extended the fix first made as part of #3287, where a loader optionmade against a subclass using a wildcard would extend itself to includeapplication of the wildcard to attributes on the super classes as well, to a“bound” loader option as well, e.g. in an expression likeLoad(SomeSubClass).load_only('foo'). Columns that are part of theparent class of SomeSubClass will also be excluded in the same way as ifthe unbound option load_only('foo') were used.References: #4373

[orm] [bug]

Improved error messages emitted by the ORM in the area of loader optiontraversal. This includes early detection of mis-matched loader strategiesalong with a clearer explanation why these strategies don’t match.References: #4433

[orm] [bug]

The “remove” event for collections is now called before the item is removedin the case of the collection.remove() method, as is consistent with thebehavior for most other forms of collection item removal (such asdelitem, replacement under setitem). For pop() methods,the remove event still fires after the operation.

[orm] [bug] [engine]

Added accessors for execution options to Core and ORM, viaQuery.get_execution_options(),Connection.get_execution_options(),Engine.get_execution_options(), andExecutable.get_execution_options(). PR courtesy Daniel Lister.References: #4464

[orm] [bug]

Fixed issue in association proxy due to #3423 which caused the useof custom PropComparator objects with hybrid attributes, such asthe one demonstrated in the dictlike-polymorphic example to notfunction within an association proxy. The strictness that was added in#3423 has been relaxed, and additional logic to accommodate foran association proxy that links to a custom hybrid have been added.References: #4446

[orm] [bug]

Implemented the .get_history() method, which also implies availabilityof AttributeState.history, for synonym() attributes.Previously, trying to access attribute history via a synonym would raise anAttributeError.References: #3777

[orm] [change]

Added a new function close_all_sessions() which takesover the task of the Session.close_all() method, whichis now deprecated as this is confusing as a classmethod.Pull request courtesy Augustin Trancart.References: #4412

orm declarative

[orm declarative] [bug]

Added a clause_element() method to ColumnProperty whichcan allow the usage of a not-fully-declared column or deferred attribute ina declarative mapped class slightly more friendly when it’s used in aconstraint or other column-oriented scenario within the class declaration,though this still can’t work in open-ended expressions; prefer to call theColumnProperty.expression attribute if receiving TypeError.References: #4372

engine

[engine] [feature]

Added public accessor QueuePool.timeout() that returns the configuredtimeout for a QueuePool object. Pull request courtesy Irina Delamare.References: #3689

[engine] [change]

The “threadlocal” engine strategy which has been a legacy feature ofSQLAlchemy since around version 0.2 is now deprecated, along with thePool.threadlocal parameter of Pool which has noeffect in most modern use cases.

References: #4393

sql

[sql] [feature]

Amended the AnsiFunction class, the base of common SQLfunctions like CURRENT_TIMESTAMP, to accept positional argumentslike a regular ad-hoc function. This to suit the case that many ofthese functions on specific backends accept arguments such as“fractional seconds” precision and such. If the function is createdwith arguments, it renders the parenthesis and the arguments. Ifno arguments are present, the compiler generates the non-parenthesized form.References: #4386

[sql] [change]

The create_engine.convert_unicode andString.convert_unicode parameters have been deprecated. Theseparameters were built back when most Python DBAPIs had little to no supportfor Python Unicode objects, and SQLAlchemy needed to take on the verycomplex task of marshalling data and SQL strings between Unicode andbytestrings throughout the system in a performant way. Thanks to Python 3,DBAPIs were compelled to adapt to Unicode-aware APIs and today all DBAPIssupported by SQLAlchemy support Unicode natively, including on Python 2,allowing this long-lived and very complicated feature to finally be (mostly)removed. There are still of course a few Python 2 edge cases whereSQLAlchemy has to deal with Unicode however these are handled automatically;in modern use, there should be no need for end-user interaction with theseflags.

See also

convert_unicode parameters deprecated

References: #4393

mssql

[mssql] [bug]

The literal_processor for the Unicode andUnicodeText datatypes now render an N character in front ofthe literal string expression as required by SQL Server for Unicode stringvalues rendered in SQL expressions.References: #4442

misc

[bug] [ext]

Fixed a regression in 1.3.0b1 caused by #3423 where associationproxy objects that access an attribute that’s only present on a polymorphicsubclass would raise an AttributeError even though the actual instancebeing accessed was an instance of that subclass.References: #4401

1.3.0b1

Released: November 16, 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.This change is also backported to: 1.2.5

[orm] [feature]

Added new flag Session.bulk_save_objects.preserve_order to theSession.bulk_save_objects() method, which defaults to True. When setto False, the given mappings will be grouped into inserts and updates pereach object type, to allow for greater opportunities to batch commonoperations together. Pull request courtesy Alessandro Cucci.

[orm] [feature]

The “selectin” loader strategy now omits the JOIN in the case of a simpleone-to-many load, where it instead relies loads only from the relatedtable, relying upon the foreign key columns of the related table in orderto match up to primary keys in the parent table. This optimization can bedisabled by setting the relationship.omit_join flag to False.Many thanks to Jayson Reis for the efforts on this.

References: #4340

[orm] [feature]

Added .info dictionary to the InstanceState class, the objectthat comes from calling inspect() on a mapped object.

See also

info dictionary added to InstanceState

References: #4257

[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.This change is also backported to: 1.2.12

References: #4334

[orm] [bug]

Fixed issue regarding passive_deletes=”all”, where the foreign keyattribute of an object is maintained with its value even after the objectis removed from its parent collection. Previously, the unit of work wouldset this to NULL even though passive_deletes indicated it should not bemodified.

References: #3844

[orm] [bug]

Improved the behavior of a relationship-bound many-to-one object expressionsuch that the retrieval of column values on the related object are nowresilient against the object being detached from its parentSession, even if the attribute has been expired. New featureswithin the InstanceState are used to memoize the last known valueof a particular column attribute before its expired, so that the expressioncan still evaluate when the object is detached and expired at the sametime. Error conditions are also improved using modern attribute statefeatures to produce more specific messages as needed.

References: #4359

[orm] [bug] [mysql] [postgresql]

The ORM now doubles the “FOR UPDATE” clause within the subquery thatrenders in conjunction with joined eager loading in some cases, as it hasbeen observed that MySQL does not lock the rows from a subquery. Thismeans the query renders with two FOR UPDATE clauses; note that on somebackends such as Oracle, FOR UPDATE clauses on subqueries are silentlyignored since they are unnecessary. Additionally, in the case of the “OF”clause used primarily with PostgreSQL, the FOR UPDATE is rendered only onthe inner subquery when this is used so that the selectable can be targetedto the table within the SELECT statement.

References: #4246

[orm] [bug]

Refactored Query.join() to further clarify the individual componentsof structuring the join. This refactor adds the ability forQuery.join() to determine the most appropriate “left” side of thejoin when there is more than one element in the FROM list or the query isagainst multiple entities. If more than one FROM/entity matches, an erroris raised that asks for an ON clause to be specified to resolve theambiguity. In particular this targets the regression we saw in#4363 but is also of general use. The codepaths withinQuery.join() are now easier to follow and the error cases aredecided more specifically at an earlier point in the operation.

References: #4365

[orm] [bug]

Fixed long-standing issue in Query where a scalar subquery suchas produced by Query.exists(), Query.as_scalar() and otherderivations from Query.statement would not correctly be adaptedwhen used in a new Query that required entity adaptation, such aswhen the query were turned into a union, or a from_self(), etc. The changeremoves the “no adaptation” annotation from the select() objectproduced by the Query.statement accessor.References: #4304

[orm] [bug]

An informative exception is re-raised when a primary key value is notsortable in Python during an ORM flush under Python 3, such as an Enumthat has no lt() method; normally Python 3 raises a TypeErrorin this case. The flush process sorts persistent objects by primary keyin Python so the values must be sortable.References: #4232

[orm] [bug]

Removed the collection converter used by the MappedCollectionclass. This converter was used only to assert that the incoming dictionarykeys matched that of their corresponding objects, and only during a bulk setoperation. The converter can interfere with a custom validator orAttributeEvents.bulk_replace() listener that wants to convertincoming values further. The TypeError which would be raised by thisconverter when an incoming key didn’t match the value is removed; incomingvalues during a bulk assignment will be keyed to their value-generated key,and not the key that’s explicitly present in the dictionary.

Overall, @converter is superseded by theAttributeEvents.bulk_replace() event handler added as part of#3896.References: #3604

[orm] [bug]

Added new behavior to the lazy load that takes place when the “old” value ofa many-to-one is retrieved, such that exceptions which would be raised dueto either lazy="raise" or a detached session error are skipped.

References: #4353

[orm] [bug]

A long-standing oversight in the ORM, the delete method for a many-to-one relationship was non-functional, e.g. for an operation such as dela.b. This is now implemented and is equivalent to setting the attributeto None.

See also

“del” implemented for ORM attributes

References: #4354

orm declarative

[bug] [declarative] [orm]

Fixed bug where declarative would not update the state of theMapper as far as what attributes were present, when additionalattributes were added or removed after the mapper attribute collections hadalready been called and memoized. Additionally, a NotImplementedErroris now raised if a fully mapped attribute (e.g. column, relationship, etc.)is deleted from a class that is currently mapped, since the mapper will notfunction correctly if the attribute has been removed.References: #4133

engine

[engine] [feature]

Added new “lifo” mode to QueuePool, typically enabled by settingthe flag create_engine.pool_use_lifo to True. “lifo” modemeans the same connection just checked in will be the first to be checkedout again, allowing excess connections to be cleaned up from the serverside during periods of the pool being only partially utilized. Pull requestcourtesy Taem Park.

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.This change is also backported to: 1.2.13

[sql] [feature]

Added Sequence to the “string SQL” system that will render ameaningful string expression ("<next sequence value: my_sequence>")when stringifying without a dialect a statement that includes a “sequencenextvalue” expression, rather than raising a compilation error.References: #4144

[sql] [feature]

Added new naming convention tokens column_0N_name, column_0_N_name,etc., which will render the names / keys / labels for all columns referencedby a particular constraint in a sequence. In order to accommodate for thelength of such a naming convention, the SQL compiler’s auto-truncationfeature now applies itself to constraint names as well, which creates ashortened, deterministically generated name for the constraint that willapply to a target backend without going over the character limit of thatbackend.

The change also repairs two other issues. One is that the column_0_keytoken wasn’t available even though this token was documented, the other wasthat the referred_column_0_name token would inadvertently render the.key and not the .name of the column if these two values weredifferent.

References: #3989

[sql] [feature]

Added new logic to the “expanding IN” bound parameter feature whereby ifthe given list is empty, a special “empty set” expression that is specificto different backends is generated, thus allowing IN expressions to befully dynamic including empty IN expressions.

References: #4271

[sql] [feature]

The Python builtin dir() is now supported for a SQLAlchemy “properties”object, such as that of a Core columns collection (e.g. .c),mapper.attrs, etc. Allows iPython autocompletion to work as well.Pull request courtesy Uwe Korn.

[sql] [feature]

Added new feature FunctionElement.as_comparison() which allows a SQLfunction to act as a binary comparison operation that can work within theORM.

References: #3831

[sql] [bug]

Added “like” based operators as “comparison” operators, includingColumnOperators.startswith()ColumnOperators.endswith()ColumnOperators.ilike()ColumnOperators.notilike() among manyothers, so that all of these operators can be the basis for an ORM“primaryjoin” condition.References: #4302

[sql] [bug]

Fixed issue with TypeEngine.bind_expression() andTypeEngine.column_expression() methods where these methods would notwork if the target type were part of a Variant, or other targettype of a TypeDecorator. Additionally, the SQL compiler nowcalls upon the dialect-level implementation when it renders these methodsso that dialects can now provide for SQL-level processing for built-intypes.

References: #3981

postgresql

[postgresql] [feature]

Added new PG type postgresql.REGCLASS which assists in castingtable names to OID values. Pull request courtesy Sebastian Bank.This change is also backported to: 1.2.7

References: #4160

[postgresql] [feature]

Added rudimental support for reflection of PostgreSQLpartitioned tables, e.g. that relkind=’p’ is added to reflectionqueries that return table information.

References: #4237

mysql

[mysql] [feature]

Support added for the “WITH PARSER” syntax of CREATE FULLTEXT INDEXin MySQL, using the mysql_with_parser keyword argument. Reflectionis also supported, which accommodates MySQL’s special comment formatfor reporting on this option as well. Additionally, the “FULLTEXT” and“SPATIAL” index prefixes are now reflected back into the mysql_prefixindex option.References: #4219

[mysql] [feature]

Added support for the parameters in an ON DUPLICATE KEY UPDATE statement onMySQL to be ordered, since parameter order in a MySQL UPDATE clause issignificant, in a similar manner as that described atParameter-Ordered Updates. Pull request courtesy Maxim Bublis.

[mysql] [feature]

The “pre-ping” feature of the connection pool now usesthe ping() method of the DBAPI connection in the case ofmysqlclient, PyMySQL and mysql-connector-python. Pull requestcourtesy Maxim Bublis.

sqlite

[sqlite] [feature]

Added support for SQLite’s json functionality via the newSQLite implementation for types.JSON, sqlite.JSON.The name used for the type is JSON, following an example found atSQLite’s own documentation. Pull request courtesy Ilja Everilä.

See also

Support for SQLite JSON Added

References: #3850

[sqlite] [feature]

Implemented the SQLite ON CONFLICT clause as understood at the DDLlevel, e.g. for primary key, unique, and CHECK constraints as well asspecified on a Column to satisfy inline primary key and NOT NULL.Pull request courtesy Denis Kataev.

References: #4360

mssql

[mssql] [feature]

Added fast_executemany=True parameter to the SQL Server pyodbc dialect,which enables use of pyodbc’s new performance feature of the same namewhen using Microsoft ODBC drivers.

See also

Support for pyodbc fast_executemany

References: #4158

[mssql] [bug]

Deprecated the use of Sequence with SQL Server in order to affectthe “start” and “increment” of the IDENTITY value, in favor of newparameters mssql_identity_start and mssql_identity_increment whichset these parameters directly. Sequence will be used to generatereal CREATE SEQUENCE DDL with SQL Server in a future release.

References: #4362

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.

This change is also backported to: 1.2.9

References: #4290

[oracle] [bug]

Updated the parameters that can be sent to the cx_Oracle DBAPI to both allowfor all current parameters as well as for future parameters not added yet.In addition, removed unused parameters that were deprecated in version 1.2,and additionally we are now defaulting “threaded” to False.

References: #4369

[oracle] [bug]

The Oracle dialect will no longer use the NCHAR/NCLOB datatypesrepresent generic unicode strings or clob fields in conjunction withUnicode and UnicodeText unless the flaguse_nchar_for_unicode=True is passed to create_engine() -this includes CREATE TABLE behavior as well as setinputsizes() forbound parameters. On the read side, automatic Unicode conversion underPython 2 has been added to CHAR/VARCHAR/CLOB result rows, to match thebehavior of cx_Oracle under Python 3. In order to mitigate the performancehit under Python 2, SQLAlchemy’s very performant (when C extensionsare built) native Unicode handlers are used under Python 2.

References: #4242

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().This change is also backported to: 1.2.9

References: #4243

[feature] [ext]

Added new feature BakedQuery.to_query(), which allows for aclean way of using one BakedQuery as a subquery inside of anotherBakedQuery without needing to refer explicitly to aSession.References: #4318

[feature] [ext]

The AssociationProxy now has standard column comparison operationssuch as ColumnOperators.like() andColumnOperators.startswith() available when the target attribute is aplain column - the EXISTS expression that joins to the target table isrendered as usual, but the column expression is then use within the WHEREcriteria of the EXISTS. Note that this alters the behavior of the.contains() method on the association proxy to make use ofColumnOperators.contains() when used on a column-based attribute.

References: #4351

[feature] [ext]

Added support for bulk Query.update() and Query.delete()to the ShardedQuery class within the horizontal shardingextension. This also adds an additional expansion hook to thebulk update/delete methods Query._execute_crud().

References: #4196

[bug] [ext]

Reworked AssociationProxy to store state that’s specific to aparent class in a separate object, so that a singleAssociationProxy can serve for multiple parent classes, as isintrinsic to inheritance, without any ambiguity in the state returned by it.A new method AssociationProxy.for_class() is added to allowinspection of class-specific state.

References: #3423

[bug] [ext]

The long-standing behavior of the association proxy collection maintainingonly a weak reference to the parent object is reverted; the proxy will nowmaintain a strong reference to the parent for as long as the proxycollection itself is also in memory, eliminating the “stale associationproxy” error. This change is being made on an experimental basis to see ifany use cases arise where it causes side effects.

See also

New Features and Improvements - Core

References: #4268

[bug] [ext]

Fixed multiple issues regarding de-association of scalar objects with theassociation proxy. del now works, and additionally a new flagAssociationProxy.cascade_scalar_deletes is added, which whenset to True indicates that setting a scalar attribute to None ordeleting via del will also set the source association to None.

References: #4308