SQLAlchemy Changelog

What's new in SQLAlchemy 0.9.8

Dec 15, 2014

orm:
[orm] [bug] [engine] Fixed bug that affected generally the same classes of event as that of #3199, when the named=True parameter would be used. Some events would fail to register, and others would not invoke the event arguments correctly, generally in the case of when an event was “wrapped” for adaption in some other way. The “named” mechanics have been rearranged to not interfere with the argument signature expected by internal wrapper functions.
[orm] [bug] Fixed bug that affected many classes of event, particularly ORM events but also engine events, where the usual logic of “de duplicating” a redundant call to event.listen() with the same arguments would fail, for those events where the listener function is wrapped. An assertion would be hit within registry.py. This assertion has now been integrated into the deduplication check, with the added bonus of a simpler means of checking deduplication across the board.
[orm] [bug] Fixed warning that would emit when a complex self-referential primaryjoin contained functions, while at the same time remote_side was specified; the warning would suggest setting “remote side”. It now only emits if remote_side isn’t present.
orm declarative:
[bug] [declarative] [orm] Fixed “‘NoneType’ object has no attribute ‘concrete’” error when using AbstractConcreteBase in conjunction with a subclass that declares __abstract__.
engine:
[engine] [bug] The execution options passed to an Engine either via create_engine.execution_options or Engine.update_execution_options() are not passed to the special Connection used to initialize the dialect within the “first connect” event; dialects will usually perform their own queries in this phase, and none of the current available options should be applied here. In particular, the “autocommit” option was causing an attempt to autocommit within this initial connect which would fail with an AttributeError due to the non-standard state of the Connection.
[engine] [bug] The string keys that are used to determine the columns impacted for an INSERT or UPDATE are now sorted when they contribute towards the “compiled cache” cache key. These keys were previously not deterministically ordered, meaning the same statement could be cached multiple times on equivalent keys, costing both in terms of memory as well as performance.
sql:
[sql] [bug] Fixed bug where a fair number of SQL elements within the sql package would fail to __repr__() successfully, due to a missing description attribute that would then invoke a recursion overflow when an internal AttributeError would then re-invoke __repr__().
[sql] [bug] An adjustment to table/index reflection such that if an index reports a column that isn’t found to be present in the table, a warning is emitted and the column is skipped. This can occur for some special system column situations as has been observed with Oracle.
[sql] [bug] Fixed bug in CTE where literal_binds compiler argument would not be always be correctly propagated when one CTE referred to another aliased CTE in a statement.
[sql] [bug] Fixed 0.9.7 regression caused by #3067 in conjunction with a mis-named unit test such that so-called “schema” types like Boolean and Enum could no longer be pickled.
postgresql:
[postgresql] [feature] [pg8000] Support is added for “sane multi row count” with the pg8000 driver, which applies mostly to when using versioning with the ORM. The feature is version-detected based on pg8000 1.9.14 or greater in use. Pull request courtesy Tony Locke.
[postgresql] [bug] A revisit to this issue first patched in 0.9.5, apparently psycopg2’s .closed accessor is not as reliable as we assumed, so we have added an explicit check for the exception messages “SSL SYSCALL error: Bad file descriptor” and “SSL SYSCALL error: EOF detected” when detecting an is-disconnect scenario. We will continue to consult psycopg2’s connection.closed as a first check.
[postgresql] [bug] Fixed bug where Postgresql JSON type was not able to persist or otherwise render a SQL NULL column value, rather than a JSON-encoded 'null'. To support this case, changes are as follows:
The value null() can now be specified, which will always result in a NULL value resulting in the statement.
A new parameter JSON.none_as_null is added, which when True indicates that the Python None value should be peristed as SQL NULL, rather than JSON-encoded 'null'.
Retrival of NULL as None is also repaired for DBAPIs other than psycopg2, namely pg8000.
[postgresql] [bug] The exception wrapping system for DBAPI errors can now accommodate non-standard DBAPI exceptions, such as the psycopg2 TransactionRollbackError. These exceptions will now be raised using the closest available subclass in sqlalchemy.exc, in the case of TransactionRollbackError, sqlalchemy.exc.OperationalError.
[postgresql] [bug] Fixed bug in postgresql.array object where comparison to a plain Python list would fail to use the correct array constructor. Pull request courtesy Andrew.
[postgresql] [bug] Added a supported FunctionElement.alias() method to functions, e.g. the func construct. Previously, behavior for this method was undefined. The current behavior mimics that of pre-0.9.4, which is that the function is turned into a single-column FROM clause with the given alias name, where the column itself is anonymously named.
mysql:
[mysql] [bug] [mysqlconnector] Mysqlconnector as of version 2.0, probably as a side effect of the python 3 merge, now does not expect percent signs (e.g. as used as the modulus operator and others) to be doubled, even when using the “pyformat” bound parameter format (this change is not documented by Mysqlconnector). The dialect now checks for py2k and for mysqlconnector less than version 2.0 when detecting if the modulus operator should be rendered as %% or %.
[mysql] [bug] [mysqlconnector] Unicode SQL is now passed for MySQLconnector version 2.0 and above; for Py2k and MySQL < 2.0, strings are encoded.
sqlite:
[sqlite] [bug] When selecting from a UNION using an attached database file, the pysqlite driver reports column names in cursor.description as ‘dbname.tablename.colname’, instead of ‘tablename.colname’ as it normally does for a UNION (note that it’s supposed to just be ‘colname’ for both, but we work around it). The column translation logic here has been adjusted to retrieve the rightmost token, rather than the second token, so it works in both cases. Workaround courtesy Tony Roberts.
mssql:
[mssql] [bug] Fixed the version string detection in the pymssql dialect to work with Microsoft SQL Azure, which changes the word “SQL Server” to “SQL Azure”.
oracle:
[oracle] [bug] Fixed long-standing bug in Oracle dialect where bound parameter names that started with numbers would not be quoted, as Oracle doesn’t like numerics in bound parameter names.
misc:
[bug] [declarative] Fixed an unlikely race condition observed in some exotic end-user setups, where the attempt to check for “duplicate class name” in declarative would hit upon a not-totally-cleaned-up weak reference related to some other class being removed; the check here now ensures the weakref still references an object before calling upon it further.
[bug] [ext] Fixed bug in ordering list where the order of items would be thrown off during a collection replace event, if the reorder_on_append flag were set to True. The fix ensures that the ordering list only impacts the list that is explicitly associated with the object.
[bug] [ext] Fixed bug where ext.mutable.MutableDict failed to implement the update() dictionary method, thus not catching changes. Pull request courtesy Matt Chisholm.
[bug] [ext] Fixed bug where a custom subclass of ext.mutable.MutableDict would not show up in a “coerce” operation, and would instead return a plain ext.mutable.MutableDict. Pull request courtesy Matt Chisholm.
[bug] [pool] Fixed bug in connection pool logging where the “connection checked out” debug logging message would not emit if the logging were set up using logging.setLevel(), rather than using the echo_pool flag. Tests to assert this logging have been added. This is a regression that was introduced in 0.9.0.

New in SQLAlchemy 0.5.0 (Jan 6, 2009)

new features
general
Documentation has been converted to Sphinx. In particular, the generated API documentation has been constructed into a full blown "API Reference" section which organizes editorial documentation combined with generated docstrings. Cross linking between sections and API docs are vastly improved, a javascript-powered search feature is provided, and a full index of all classes, functions and members is provided.
setup.py now imports setuptools only optionally. If not present, distutils is used. The new "pip" installer is recommended over easy_install as it installs in a more simplified way.
added an extremely basic illustration of a PostGIS integration to the examples folder.
orm
Query.with_polymorphic() now accepts a third argument "discriminator" which will replace the value of mapper.polymorphic_on for that query. Mappers themselves no longer require polymorphic_on to be set, even if the mapper has a polymorphic_identity. When not set, the mapper will load non-polymorphically by default. Together, these two features allow a non-polymorphic concrete inheritance setup to use polymorphic loading on a per-query basis, since concrete setups are prone to many issues when used polymorphically in all cases.
dynamic_loader accepts a query_class= to customize the Query classes used for both the dynamic collection and the queries built from it.
query.order_by() accepts None which will remove any pending order_by state from the query, as well as cancel out any mapper/relation configured ordering. This is primarily useful for overriding the ordering specified on a dynamic_loader(). [ticket:1079]
sql
RowProxy objects can be used in place of dictionary arguments sent to connection.execute() and friends. [ticket:935]
dialect
Added a new description_encoding attribute on the dialect that is used for encoding the column name when processing the metadata. This usually defaults to utf-8.
mssql
Added in a new MSGenericBinary type. This maps to the Binary type so it can implement the specialized behavior of treating length specified types as fixed-width Binary types and non-length types as an unbound variable length Binary type.
Added in new types: MSVarBinary and MSImage. [ticket:1249]
Added in the MSReal, MSNText, MSSmallDateTime, MSTime, MSDateTimeOffset, and MSDateTime2 types
sqlite
Table reflection now stores the actual DefaultClause value for the column. [ticket:1266]
bugfixes, behavioral changes
general
orm
Exceptions raised during compile_mappers() are now preserved to provide "sticky behavior"
if a hasattr() call on a pre-compiled mapped attribute triggers a failing compile and suppresses the exception, subsequent compilation is blocked and the exception will be reiterated on the next compile() call. This issue occurs frequently when using declarative.
property.of_type() is now recognized on a single-table inheriting target, when used in the context of prop.of_type(..).any()/has(), as well as query.join(prop.of_type(...)).
query.join() raises an error when the target of the join doesn't match the property-based attribute
while it's unlikely anyone is doing this, the SQLAlchemy author was guilty of this particular loosey-goosey behavior.
Fixed bug when using weak_instance_map=False where modified events would not be intercepted for a flush(). [ticket:1272]
Fixed some deep "column correspondence" issues which could impact a Query made against a selectable containing multiple versions of the same table, as well as unions and similar which contained the same table columns in different column positions at different levels. [ticket:1268]
Custom comparator classes used in conjunction with column_property(), relation() etc. can define new comparison methods on the Comparator, which will become available via __getattr__() on the InstrumentedAttribute. In the case of synonym() or comparable_property(), attributes are resolved first on the user-defined descriptor, then on the user-defined comparator.
Added ScopedSession.is_active accessor. [ticket:976]
Can pass mapped attributes and column objects as keys to query.update({}). [ticket:1262]
Mapped attributes passed to the values() of an expression level insert() or update() will use the keys of the mapped columns, not that of the mapped attribute.
Corrected problem with Query.delete() and Query.update() not working properly with bind parameters. [ticket:1242]
Query.select_from(), from_statement() ensure that the given argument is a FromClause, or Text/Select/Union, respectively.
Query() can be passed a "composite" attribute as a column expression and it will be expanded. Somewhat related to [ticket:1253].
Query() is a little more robust when passed various column expressions such as strings, clauselists, text() constructs (which may mean it just raises an error more nicely).
first() works as expected with Query.from_statement().
Fixed bug introduced in 0.5rc4 involving eager loading not functioning for properties which were added to a mapper post-compile using add_property() or equivalent.
Fixed bug where many-to-many relation() with viewonly=True would not correctly reference the link between secondary->remote.
Duplicate items in a list-based collection will be maintained when issuing INSERTs to a "secondary" table in a many-to-many relation. Assuming the m2m table has a unique or primary key constraint on it, this will raise the expected constraint violation instead of silently dropping the duplicate entries. Note that the old behavior remains for a one-to-many relation since collection entries in that case don't result in INSERT statements and SQLA doesn't manually police collections. [ticket:1232]
Query.add_column() can accept FromClause objects in the same manner as session.query() can.
Comparison of many-to-one relation to NULL is properly converted to IS NOT NULL based on not_().
Extra checks added to ensure explicit primaryjoin/secondaryjoin are ClauseElement instances, to prevent more confusing errors later on. [ticket:1087]
Improved mapper() check for non-class classes. [ticket:1236]
comparator_factory argument is now documented and supported by all MapperProperty types, including column_property(), relation(), backref(), and synonym() [ticket:5051].
Changed the name of PropertyLoader to RelationProperty, to be consistent with all the other names. PropertyLoader is still present as a synonym.
fixed "double iter()" call causing bus errors in shard API, removed errant result.close() left over from the 0.4 version. [ticket:1099] [ticket:1228]
made Session.merge cascades not trigger autoflush. Fixes merged instances getting prematurely inserted with missing values.
Two fixes to help prevent out-of-band columns from being rendered in polymorphic_union inheritance scenarios (which then causes extra tables to be rendered in the FROM clause causing cartesian products):
improvements to "column adaption" for a->b->c inheritance situations to better locate columns that are related to one another via multiple levels of indirection, rather than rendering the non-adapted column.
the "polymorphic discriminator" column is only rendered for the actual mapper being queried against. The column won't be "pulled in" from a subclass or superclass mapper since it's not needed.
Fixed shard_id argument on ShardedSession.execute(). [ticket:1072]
sql
Columns can again contain percent signs within their names. [ticket:1256]
sqlalchemy.sql.expression.Function is now a public class. It can be subclassed to provide user-defined SQL functions in an imperative style, including with pre-established behaviors. The postgis.py example illustrates one usage of this.
PickleType now favors == comparison by default, if the incoming object (such as a dict) implements __eq__(). If the object does not implement __eq__() and mutable=True, a deprecation warning is raised.
Fixed the import weirdness in sqlalchemy.sql to not export __names__ [ticket:1215].
Using the same ForeignKey object repeatedly raises an error instead of silently failing later. [ticket:1238]
Added NotImplementedError for params() method on Insert/Update/Delete constructs. These items currently don't support this functionality, which also would be a little misleading compared to values().
Reflected foreign keys will properly locate their referenced column, even if the column was given a "key" attribute different from the reflected name. This is achieved via a new flag on ForeignKey/ForeignKeyConstraint called "link_to_name", if True means the given name is the referred-to column's name, not its assigned key. [ticket:650]
select() can accept a ClauseList as a column in the same way as a Table or other selectable and the interior expressions will be used as column elements. [ticket:1253]
the "passive" flag on session.is_modified() is correctly propagated to the attribute manager.
union() and union_all() will not whack any order_by() that has been applied to the select()s inside. If you union() a select() with order_by() (presumably to support LIMIT/OFFSET), you should also call self_group() on it to apply parenthesis.
engine/pool
Connection.invalidate() checks for closed status to avoid attribute errors. [ticket:1246]
NullPool supports reconnect on failure behavior. [ticket:1094]
Added a mutex for the initial pool creation when using pool.manage(dbapi). This prevents a minor case of "dogpile" behavior which would otherwise occur upon a heavy load startup. [ticket:799]
_execute_clauseelement() goes back to being a private method. Subclassing Connection is not needed now that ConnectionProxy is available.
documentation
Tickets [ticket:1200] [ticket:1149].
Added note about create_session() defaults.
Added section about metadata.reflect().
Updated `TypeDecorator` section.
Rewrote the "threadlocal" strategy section of the docs due to recent confusion over this feature.
Removed badly out of date 'polymorphic_fetch' and 'select_table' docs from inheritance, reworked the second half of "joined table inheritance".
Documented `comparator_factory` kwarg, added new doc section "Custom Comparators".
mssql
Refactored the Date/Time types. The ``smalldatetime`` data type no longer truncates to a date only, and will now be mapped to the MSSmallDateTime type. [ticket:1254]
Corrected an issue with Numerics to accept an int.
Mapped ``char_length`` to the ``LEN()`` function.
If an ``INSERT`` includes a subselect the ``INSERT`` is converted from an ``INSERT INTO VALUES`` construct to a ``INSERT INTO SELECT`` construct.
If the column is part of a ``primary_key`` it will be ``NOT NULL`` since MSSQL doesn't allow ``NULL`` in primary_key columns.
``MSBinary`` now returns a ``BINARY`` instead of an ``IMAGE``. This is a backwards incompatible change in that ``BINARY`` is a fixed length data type whereas ``IMAGE`` is a variable length data type. [ticket:1249]
``get_default_schema_name`` is now reflected from the database based on the user's default schema. This only works with MSSQL 2005 and later. [ticket:1258]
Added collation support through the use of a new collation argument. This is supported on the following types: char, nchar, varchar, nvarchar, text, ntext. [ticket:1248]
Changes to the connection string parameters favor DSN as the default specification for pyodbc. See the mssql.py docstring for detailed usage instructions.
Added experimental support of savepoints. It currently does not work fully with sessions.
Support for three levels of column nullability: NULL, NOT NULL, and the database's configured default. The default Column configuration (nullable=True) will now generate NULL in the DDL. Previously no specification was emitted and the database default would take effect (usually NULL, but not always). To explicitly request the database default, configure columns with nullable=None and no specification will be emitted in DDL. This is backwards incompatible behavior. [ticket:1243]
postgres
"%" signs in text() constructs are automatically escaped to "%%". Because of the backwards incompatible nature of this change, a warning is emitted if '%%' is detected in the string. [ticket:1267]
Calling alias.execute() in conjunction with server_side_cursors won't raise AttributeError.
Added Index reflection support to Postgres, using a great patch we long neglected, submitted by Ken Kuhlman. [ticket:714]
oracle
Adjusted the format of create_xid() to repair two-phase commit. We now have field reports of Oracle two-phase commit working properly with this change.
Added OracleNVarchar type, produces NVARCHAR2, and also subclasses Unicode so that convert_unicode=True by default. NVARCHAR2 reflects into this type automatically so these columns pass unicode on a reflected table with no explicit convert_unicode=True flags. [ticket:1233]
Fixed bug which was preventing out params of certain types from being received; thanks a ton to huddlej at wwu.edu ! [ticket:1265]
mysql
"%" signs in text() constructs are automatically escaped to "%%". Because of the backwards incompatible nature of this change, a warning is emitted if '%%' is detected in the string.
Fixed bug in exception raise when FK columns not present during reflection. [ticket:1241]
Fixed bug involving reflection of a remote-schema table with a foreign key ref to another table in that schema.
associationproxy
The association proxy properties are make themselves available at the class level, e.g. MyClass.aproxy. Previously this evaluated to None.
declarative
The full list of arguments accepted as string by backref() includes 'primaryjoin', 'secondaryjoin', 'secondary', 'foreign_keys', 'remote_side', 'order_by'.