For the full set of feature descriptions, see http://docs.sqlalchemy.org/en/latest/changelog/migration_06.html . This document is a work in progress.

All bug fixes and feature enhancements from the most recent 0.5 version and below are also included within 0.6.

Platforms targeted now include Python 2.4/2.5/2.6, Python 3.1, Jython2.5.

transaction isolation level may be specified with create_engine(… isolation_level=”…”); available on postgresql and sqlite. References: #443

Connection has execution_options(), generative method which accepts keywords that affect how the statement is executed w.r.t. the DBAPI. Currently supports “stream_results”, causes psycopg2 to use a server side cursor for that statement, as well as “autocommit”, which is the new location for the “autocommit” option from select() and text(). select() and text() also have .execution_options() as well as ORM Query().

fixed the import for entrypoint-driven dialects to not rely upon silly tb_info trick to determine import error status. References: #1630

added first() method to ResultProxy, returns first row and closes result set immediately.

RowProxy objects are now pickleable, i.e. the object returned by result.fetchone(), result.fetchall() etc.

RowProxy no longer has a close() method, as the row no longer maintains a reference to the parent. Call close() on the parent ResultProxy instead, or use autoclose.

ResultProxy internals have been overhauled to greatly reduce method call counts when fetching columns. Can provide a large speed improvement (up to more than 100%) when fetching large result sets. The improvement is larger when fetching columns that have no type-level processing applied and when using results as tuples (instead of as dictionaries). Many thanks to Elixir’s Gaëtan de Menten for this dramatic improvement ! References: #1586

Databases which rely upon postfetch of “last inserted id” to get at a generated sequence value (i.e. MySQL, MS-SQL) now work correctly when there is a composite primary key where the “autoincrement” column is not the first primary key column in the table.

the last_inserted_ids() method has been renamed to the descriptor “inserted_primary_key”.

setting echo=False on create_engine() now sets the loglevel to WARN instead of NOTSET. This so that logging can be disabled for a particular engine even if logging for “sqlalchemy.engine” is enabled overall. Note that the default setting of “echo” is None . References: #1554

ConnectionProxy now has wrapper methods for all transaction lifecycle events, including begin(), rollback(), commit() begin_nested(), begin_prepared(), prepare(), release_savepoint(), etc.

Connection pool logging now uses both INFO and DEBUG log levels for logging. INFO is for major events such as invalidated connections, DEBUG for all the acquire/return logging. echo_pool can be False, None, True or “debug” the same way as echo works.

All pyodbc-dialects now support extra pyodbc-specific kw arguments ‘ansi’, ‘unicode_results’, ‘autocommit’. References: #1621

the “threadlocal” engine has been rewritten and simplified and now supports SAVEPOINT operations.

deprecated or removed result.last_inserted_ids() is deprecated. Use result.inserted_primary_key

dialect.get_default_schema_name(connection) is now public via dialect.default_schema_name.

the “connection” argument from engine.transaction() and engine.run_callable() is removed - Connection itself now has those methods. All four methods accept *args and **kwargs which are passed to the given callable, as well as the operating connection.

Table reflection has been expanded and generalized into a new API called “sqlalchemy.engine.reflection.Inspector”. The Inspector object provides fine-grained information about a wide variety of schema information, with room for expansion, including table names, column names, view definitions, sequences, indexes, etc.

Views are now reflectable as ordinary Table objects. The same Table constructor is used, with the caveat that “effective” primary and foreign key constraints aren’t part of the reflection results; these have to be specified explicitly if desired.

The existing autoload=True system now uses Inspector underneath so that each dialect need only return “raw” data about tables and other objects - Inspector is the single place that information is compiled into Table objects so that consistency is at a maximum.

the DDL system has been greatly expanded. the DDL() class now extends the more generic DDLElement(), which forms the basis of many new constructs: CreateTable()

DropTable()

AddConstraint()

DropConstraint()

CreateIndex()

DropIndex()

CreateSequence()

DropSequence() These support “on” and “execute-at()” just like plain DDL() does. User-defined DDLElement subclasses can be created and linked to a compiler using the sqlalchemy.ext.compiler extension.

The signature of the “on” callable passed to DDL() and DDLElement() is revised as follows: ddl the DDLElement object itself event the string event name. target previously “schema_item”, the Table or MetaData object triggering the event. connection the Connection object in use for the operation. **kw keyword arguments. In the case of MetaData before/after create/drop, the list of Table objects for which CREATE/DROP DDL is to be issued is passed as the kw argument “tables”. This is necessary for metadata-level DDL that is dependent on the presence of specific tables. The “schema_item” attribute of DDL has been renamed to ”target”.

Dialect modules are now broken into database dialects plus DBAPI implementations. Connect URLs are now preferred to be specified using dialect+driver://…, i.e. “mysql+mysqldb://scott:tiger@localhost/test”. See the 0.6 documentation for examples.

the setuptools entrypoint for external dialects is now called “sqlalchemy.dialects”.

the “owner” keyword argument is removed from Table. Use “schema” to represent any namespaces to be prepended to the table name.

server_version_info becomes a static attribute.

dialects receive an initialize() event on initial connection to determine connection properties.

dialects receive a visit_pool event have an opportunity to establish pool listeners.

cached TypeEngine classes are cached per-dialect class instead of per-dialect.

new UserDefinedType should be used as a base class for new types, which preserves the 0.5 behavior of get_col_spec().

The result_processor() method of all type classes now accepts a second argument “coltype”, which is the DBAPI type argument from cursor.description. This argument can help some types decide on the most efficient processing of result values.

Deprecated Dialect.get_params() removed.

Dialect.get_rowcount() has been renamed to a descriptor “rowcount”, and calls cursor.rowcount directly. Dialects which need to hardwire a rowcount in for certain calls should override the method to provide different behavior.

DefaultRunner and subclasses have been removed. The job of this object has been simplified and moved into ExecutionContext. Dialects which support sequences should add a fire_sequence() method to their execution context implementation. References: #1566

Functions and operators generated by the compiler now use (almost) regular dispatch functions of the form “visit_<opname>” and “visit_<funcname>_fn” to provide customed processing. This replaces the need to copy the “functions” and “operators” dictionaries in compiler subclasses with straightforward visitor methods, and also allows compiler subclasses complete control over rendering, as the full _Function or _BinaryExpression object is passed in.

The construction of types within dialects has been totally overhauled. Dialects now define publicly available types as UPPERCASE names exclusively, and internal implementation types using underscore identifiers (i.e. are private). The system by which types are expressed in SQL and DDL has been moved to the compiler system. This has the effect that there are much fewer type objects within most dialects. A detailed document on this architecture for dialect authors is in lib/sqlalchemy/dialects/type_migration_guidelines.txt .

Types no longer make any guesses as to default parameters. In particular, Numeric, Float, NUMERIC, FLOAT, DECIMAL don’t generate any length or scale unless specified.

types.Binary is renamed to types.LargeBinary, it only produces BLOB, BYTEA, or a similar “long binary” type. New base BINARY and VARBINARY types have been added to access these MySQL/MS-SQL specific types in an agnostic way. References: #1664

String/Text/Unicode types now skip the unicode() check on each result column value if the dialect has detected the DBAPI as returning Python unicode objects natively. This check is issued on first connect using “SELECT CAST ‘some text’ AS VARCHAR(10)” or equivalent, then checking if the returned object is a Python unicode. This allows vast performance increases for native-unicode DBAPIs, including pysqlite/sqlite3, psycopg2, and pg8000.

Most types result processors have been checked for possible speed improvements. Specifically, the following generic types have been optimized, resulting in varying speed improvements: Unicode, PickleType, Interval, TypeDecorator, Binary. Also the following dbapi-specific implementations have been improved: Time, Date and DateTime on Sqlite, ARRAY on PostgreSQL, Time on MySQL, Numeric(as_decimal=False) on MySQL, oursql and pypostgresql, DateTime on cx_oracle and LOB-based types on cx_oracle.

Reflection of types now returns the exact UPPERCASE type within types.py, or the UPPERCASE type within the dialect itself if the type is not a standard SQL type. This means reflection now returns more accurate information about reflected types.

Added a new Enum generic type. Enum is a schema-aware object to support databases which require specific DDL in order to use enum or equivalent; in the case of PG it handles the details of CREATE TYPE , and on other databases without native enum support will by generate VARCHAR + an inline CHECK constraint to enforce the enum. References: #1109, #1511

The Interval type includes a “native” flag which controls if native INTERVAL types (postgresql + oracle) are selected if available, or not. “day_precision” and “second_precision” arguments are also added which propagate as appropriately to these native types. Related to. References: #1467

The Boolean type, when used on a backend that doesn’t have native boolean support, will generate a CHECK constraint “col IN (0, 1)” along with the int/smallint- based column type. This can be switched off if desired with create_constraint=False. Note that MySQL has no native boolean or CHECK constraint support so this feature isn’t available on that platform. References: #1589

PickleType now uses == for comparison of values when mutable=True, unless the “comparator” argument with a comparison function is specified to the type. Objects being pickled will be compared based on identity (which defeats the purpose of mutable=True) if __eq__() is not overridden or a comparison function is not provided.

The default “precision” and “scale” arguments of Numeric and Float have been removed and now default to None. NUMERIC and FLOAT will be rendered with no numeric arguments by default unless these values are provided.

AbstractType.get_search_list() is removed - the games that was used for are no longer necessary.

Added a generic BigInteger type, compiles to BIGINT or NUMBER(19). References: #1125

sqlsoup has been overhauled to explicitly support an 0.5 style session, using autocommit=False, autoflush=True. Default behavior of SQLSoup now requires the usual usage of commit() and rollback(), which have been added to its interface. An explicit Session or scoped_session can be passed to the constructor, allowing these arguments to be overridden.

sqlsoup db.<sometable>.update() and delete() now call query(cls).update() and delete(), respectively.

sqlsoup now has execute() and connection(), which call upon the Session methods of those names, ensuring that the bind is in terms of the SqlSoup object’s bind.

sqlsoup objects no longer have the ‘query’ attribute - it’s not needed for sqlsoup’s usage paradigm and it gets in the way of a column that is actually named ‘query’.

The signature of the proxy_factory callable passed to association_proxy is now (lazy_collection, creator, value_attr, association_proxy), adding a fourth argument that is the parent AssociationProxy argument. Allows serializability and subclassing of the built in collections. References: #1259