0.3 Changelog

0.3.11

Released: Sun Oct 14 2007

orm

• [orm]

added a check for joining from A->B using join(), along twodifferent m2m tables. this raises an error in 0.3 but ispossible in 0.4 when aliases are used.References: #687

• [orm]

fixed small exception throw bug in Session.merge()

• [orm]

fixed bug where mapper, being linked to a join where one table hadno PK columns, would not detect that the joined table had no PK.

• [orm]

fixed bugs in determining proper sync clauses from custom inheritconditionsReferences: #769

• [orm]

backref remove object operation doesn’t fail if the other-sidecollection doesn’t contain the item, supports noload collectionsReferences: #813

engine

• [engine]

fixed another occasional race condition which could occurwhen using pool with threadlocal setting

sql

• [sql]

tweak DISTINCT precedence for clauses likefunc.count(t.c.col.distinct())

• [sql]

Fixed detection of internal ‘$’ characters in :bind$paramsReferences: #719

• [sql]

don’t assume join criterion consists only of column objectsReferences: #768

• [sql]

adjusted operator precedence of NOT to match ‘==’ and others, so that~(x==y) produces NOT (x=y), which is compatible with MySQL < 5.0(doesn’t like “NOT x=y”)References: #764

mysql

• [mysql]

fixed specification of YEAR columns when generating schema

sqlite

• [sqlite]

passthrough for stringified dates

mssql

• [mssql]

added support for TIME columns (simulated using DATETIME)References: #679

• [mssql]

added support for BIGINT, MONEY, SMALLMONEY, UNIQUEIDENTIFIER andSQL_VARIANTReferences: #721

• [mssql]

index names are now quoted when dropping from reflected tablesReferences: #684

• [mssql]

can now specify a DSN for PyODBC, using a URI like mssql:///?dsn=bob

oracle

• [oracle]

removed LONG_STRING, LONG_BINARY from “binary” types, so type objectsdon’t try to read their values as LOB.References: #622, #751

firebird

• [firebird]

supports_sane_rowcount() set to False due to ticket #370 (right way).

• [firebird]

fixed reflection of Column’s nullable property.

misc

• [postgres]

when reflecting tables from alternate schemas, the “default” placed uponthe primary key, i.e. usually a sequence name, has the “schema” nameunconditionally quoted, so that schema names which need quoting are fine.its slightly unnecessary for schema names which don’t need quotingbut not harmful.

0.3.10

Released: Fri Jul 20 2007

general

• [general]

a new mutex that was added in 0.3.9 causes the pool_timeoutfeature to fail during a race condition; threads wouldraise TimeoutError immediately with no delay if many threadspush the pool into overflow at the same time. this issue has beenfixed.

orm

• [orm]

cleanup to connection-bound sessions, SessionTransaction

sql

• [sql]

got connection-bound metadata to work with implicit execution

• [sql]

foreign key specs can have any character in their identifiersReferences: #667

• [sql]

added commutativity-awareness to binary clause comparisons toeach other, improves ORM lazy load optimizationReferences: #664

misc

• [postgres]

fixed max identifier length (63)References: #571

0.3.9

Released: Sun Jul 15 2007

general

• [general]

better error message for NoSuchColumnErrorReferences: #607

• [general]

finally figured out how to get setuptools version in, availableas sqlalchemy.versionReferences: #428

• [general]

the various “engine” arguments, such as “engine”, “connectable”,“engine_or_url”, “bind_to”, etc. are all present, but deprecated.they all get replaced by the single term “bind”. you alsoset the “bind” of MetaData usingmetadata.bind =

orm

• [orm]

forwards-compatibility with 0.4: added one(), first(), andall() to Query. almost all Query functionality from 0.4 ispresent in 0.3.9 for forwards-compat purposes.

• [orm]

reset_joinpoint() really really works this time, promise ! letsyou re-join from the root:query.join([‘a’, ‘b’]).filter().reset_joinpoint().join([‘a’, ‘c’]).filter().all()in 0.4 all join() calls start from the “root”

• [orm]

added synchronization to the mapper() construction step, to avoidthread collisions when pre-existing mappers are compiling in adifferent threadReferences: #613

• [orm]

a warning is issued by Mapper when two primary key columns of thesame name are munged into a single attribute. this happens frequentlywhen mapping to joins (or inheritance).

• [orm]

synonym() properties are fully supported by all Query joining/with_parent operationsReferences: #598

• [orm]

fixed very stupid bug when deleting items with many-to-manyuselist=False relations

• [orm]

remember all that stuff about polymorphic_union ? forjoined table inheritance ? Funny thing…You sort of don’t need it for joined table inheritance, youcan just string all the tables together via outerjoin().The UNION still applies if concrete tables are involved,though (since nothing to join them on).

• [orm]

small fix to eager loading to better work with eager loadsto polymorphic mappers that are using a straight “outerjoin”clause

sql

• [sql]

ForeignKey to a table in a schema that’s not the default schemarequires the schema to be explicit; i.e. ForeignKey(‘alt_schema.users.id’)

• [sql]

MetaData can now be constructed with an engine or url as the firstargument, just like BoundMetaData

• [sql]

BoundMetaData is now deprecated, and MetaData is a direct substitute.

• [sql]

DynamicMetaData has been renamed to ThreadLocalMetaData. theDynamicMetaData name is deprecated and is an alias for ThreadLocalMetaDataor a regular MetaData if threadlocal=False

• [sql]

composite primary key is represented as a non-keyed set to allow forcomposite keys consisting of cols with the same name; occurs within aJoin. helps inheritance scenarios formulate correct PK.

• [sql]

improved ability to get the “correct” and most minimal set of primary keycolumns from a join, equating foreign keys and otherwise equated columns.this is also mostly to help inheritance scenarios formulate the bestchoice of primary key columns.References: #185

• [sql]

added ‘bind’ argument to Sequence.create()/drop(), ColumnDefault.execute()

• [sql]

columns can be overridden in a reflected table with a “key”attribute different than the column’s name, including for primary keycolumnsReferences: #650

• [sql]

fixed “ambiguous column” result detection, when dupe col names existin a resultReferences: #657

• [sql]

some enhancements to “column targeting”, the ability to match a columnto a “corresponding” column in another selectable. this affects mostlyORM ability to map to complex joins

• [sql]

MetaData and all SchemaItems are safe to use with pickle. slowtable reflections can be dumped into a pickled file to be reused later.Just reconnect the engine to the metadata after unpickling.References: #619

• [sql]

added a mutex to QueuePool’s “overflow” calculation to prevent a racecondition that can bypass max_overflow

• [sql]

fixed grouping of compound selects to give correct results. will breakon sqlite in some cases, but those cases were producing incorrectresults anyway, sqlite doesn’t support grouped compound selectsReferences: #623

• [sql]

fixed precedence of operators so that parenthesis are correctly appliedReferences: #620

• [sql]

calling .in_() (i.e. with no arguments) will return“CASE WHEN ( IS NULL) THEN NULL ELSE 0 END = 1)”, so thatNULL or False is returned in all cases, rather than throwing an errorReferences: #545

• [sql]

fixed “where”/”from” criterion of select() to accept a unicode stringin addition to regular string - both convert to text()

• [sql]

added standalone distinct() function in addition to column.distinct()References: #558

• [sql]

result.last_inserted_ids() should return a list that is identicallysized to the primary key constraint of the table. values that were“passively” created and not available via cursor.lastrowid will be None.

• [sql]

long-identifier detection fixed to use > rather than >= formax ident lengthReferences: #589

• [sql]

fixed bug where selectable.corresponding_column(selectable.c.col)would not return selectable.c.col, if the selectable is a joinof a table and another join involving the same table. messedup ORM decision makingReferences: #593

• [sql]

added Interval type to types.pyReferences: #595

mysql

• [mysql]

fixed catching of some errors that imply a dropped connectionReferences: #625

• [mysql]

fixed escaping of the modulo operatorReferences: #624

• [mysql]

added ‘fields’ to reserved wordsReferences: #590

• [mysql]

various reflection enhancement/fixes

sqlite

• [sqlite]

rearranged dialect initialization so it has time to warn about pysqlite1being too old.

• [sqlite]

sqlite better handles datetime/date/time objects mixed and matchedwith various Date/Time/DateTime columns

• [sqlite]

string PK column inserts don’t get overwritten with OIDReferences: #603

mssql

• [mssql]

fix port option handling for pyodbcReferences: #634

• [mssql]

now able to reflect start and increment values for identity columns

• [mssql]

preliminary support for using scope_identity() with pyodbc

oracle

• [oracle]

datetime fixes: got subsecond TIMESTAMP to work,added OracleDate which supports types.Date with only year/month/dayReferences: #604

• [oracle]

added dialect flag “auto_convert_lobs”, defaults to True; will cause anyLOB objects detected in a result set to be forced into OracleBinaryso that the LOB is read() automatically, if no typemap was present(i.e., if a textual execute() was issued).

• [oracle]

mod operator ‘%’ produces MODReferences: #624

• [oracle]

converts cx_oracle datetime objects to Python datetime.datetime whenPython 2.3 usedReferences: #542

• [oracle]

fixed unicode conversion in Oracle TEXT type

misc

• [ext]

iteration over dict association proxies is now dict-like, notInstrumentedList-like (e.g. over keys instead of values)

• [ext]

association proxies no longer bind tightly to source collections, and are constructed with a thunk insteadReferences: #597

• [ext]

added selectone_by() to assignmapper

• [postgres]

fixed escaping of the modulo operatorReferences: #624

• [postgres]

added support for reflection of domainsReferences: #570

• [postgres]

types which are missing during reflection resolve to Null typeinstead of raising an error

• [postgres]

the fix in “schema” above fixes reflection of foreign keys from analt-schema table to a public schema table

0.3.8

Released: Sat Jun 02 2007

orm

• [orm]

added reset_joinpoint() method to Query, moves the “join point”back to the starting mapper. 0.4 will change the behavior ofjoin() to reset the “join point” in all cases so this is aninterim method. for forwards compatibility, ensure joins acrossmultiple relations are specified using a single join(), i.e.join([‘a’, ‘b’, ‘c’]).

• [orm]

fixed bug in query.instances() that wouldn’t handle more thanon additional mapper or one additional column.

• [orm]

“delete-orphan” no longer implies “delete”. ongoing effort toseparate the behavior of these two operations.

• [orm]

many-to-many relationships properly set the type of bind paramsfor delete operations on the association table

• [orm]

many-to-many relationships check that the number of rows deletedfrom the association table by a delete operation matches theexpected results

• [orm]

session.get() and session.load() propagate **kwargs through toquery

• [orm]

fix to polymorphic query which allows the originalpolymorphic_union to be embedded into a correlated subqueryReferences: #577

• [orm]

fix to select_by(=

sql

• [sql]

_Label class overrides compare_self to return its ultimateobject. meaning, if you say someexpr.label(‘foo’) == 5, itproduces the correct “someexpr == 5”.

• [sql]

_Label propagates “_hide_froms()” so that scalar selectsbehave more properly with regards to FROM clause #574

• [sql]

fix to long name generation when using oid_column as an order by(oids used heavily in mapper queries)

• [sql]

significant speed improvement to ResultProxy, pre-cachesTypeEngine dialect implementations and saves on function callsper column

• [sql]

parenthesis are applied to clauses via a new _Groupingconstruct. uses operator precedence to more intelligently applyparenthesis to clauses, provides cleaner nesting of clauses(doesn’t mutate clauses placed in other clauses, i.e. no ‘parens’flag)

• [sql]

added ‘modifier’ keyword, works like func. except does notadd parenthesis. e.g. select([modifier.DISTINCT(…)]) etc.

• [sql]

removed “no group by’s in a select that’s part of a UNION”restrictionReferences: #578

mysql

• [mysql]

Nearly all MySQL column types are now supported for declarationand reflection. Added NCHAR, NVARCHAR, VARBINARY, TINYBLOB,LONGBLOB, YEAR

• [mysql]

The sqltypes.Binary passthrough now always builds a BLOB,avoiding problems with very old database versions

• [mysql]

support for column-level CHARACTER SET and COLLATE declarations,as well as ASCII, UNICODE, NATIONAL and BINARY shorthand.

firebird

• [firebird]

set max identifier length to 31

• [firebird]

supports_sane_rowcount() set to False due to ticket #370.versioned_id_col feature wont work in FB.

• [firebird]

some execution fixes

• [firebird]

new association proxy implementation, implementing completeproxies to list, dict and set-based relation collections

• [firebird]

added orderinglist, a custom list class that synchronizes anobject attribute with that object’s position in the list

• [firebird]

small fix to SelectResultsExt to not bypass itself duringselect().

• [firebird]

added filter(), filter_by() to assignmapper

misc

• [engines]

added detach() to Connection, allows underlying DBAPI connectionto be detached from its pool, closing on dereference/close()instead of being reused by the pool.

• [engines]

added invalidate() to Connection, immediately invalidates theConnection and its underlying DBAPI connection.

0.3.7

Released: Sun Apr 29 2007

orm

• [orm]

fixed critical issue when, after options(eagerload()) is used,the mapper would then always apply query “wrapping” behaviorfor all subsequent LIMIT/OFFSET/DISTINCT queries, even if noeager loading was applied on those subsequent queries.

• [orm]

added query.with_parent(someinstance) method. searches fortarget instance using lazy join criterion from parent instance.takes optional string “property” to isolate the desired relation.also adds static Query.query_from_parent(instance, property)version.References: #541

• [orm]

improved query.XXX_by(someprop=someinstance) querying to usesimilar methodology to with_parent, i.e. using the “lazy” clausewhich prevents adding the remote instance’s table to the SQL,thereby making more complex conditions possibleReferences: #554

• [orm]

added generative versions of aggregates, i.e. sum(), avg(), etc.to query. used via query.apply_max(), apply_sum(), etc.#552

• [orm]

fix to using distinct() or distinct=True in combination withjoin() and similar

• [orm]

corresponding to label/bindparam name generation, eager loadersgenerate deterministic names for the aliases they create usingmd5 hashes.

• [orm]

improved/fixed custom collection classes when giving it “set”/“sets.Set” classes or subclasses (was still looking for append()methods on them during lazy loads)

• [orm]

restored old “column_property()” ORM function (used to be called“column()”) to force any column expression to be added as a propertyon a mapper, particularly those that aren’t present in the mappedselectable. this allows “scalar expressions” of any kind to beadded as relations (though they have issues with eager loads).

• [orm]

fix to many-to-many relationships targeting polymorphic mappersReferences: #533

• [orm]

making progress with session.merge() as well as combining itsusage with entity_nameReferences: #543

• [orm]

the usual adjustments to relationships between inheriting mappers,in this case establishing relation()s to subclass mappers wherethe join conditions come from the superclass’ table

sql

• [sql]

keys() of result set columns are not lowercased, come backexactly as they’re expressed in cursor.description. note thiscauses colnames to be all caps in oracle.

• [sql]

preliminary support for unicode table names, column names andSQL statements added, for databases which can support them.Works with sqlite and postgres so far. MySQL mostly worksexcept the has_table() function does not work. Reflectionworks too.

• [sql]

the Unicode type is now a direct subclass of String, which nowcontains all the “convert_unicode” logic. This helps the varietyof unicode situations that occur in db’s such as MS-SQL to bebetter handled and allows subclassing of the Unicode datatype.References: #522

• [sql]

ClauseElements can be used in in_() clauses now, such as bindparameters, etc. #476

• [sql]

reverse operators implemented for CompareMixin elements,allows expressions like “5 + somecolumn” etc. #474

• [sql]

the “where” criterion of an update() and delete() now correlatesembedded select() statements against the table being updated ordeleted. this works the same as nested select() statementcorrelation, and can be disabled via the correlate=False flag onthe embedded select().

• [sql]

column labels are now generated in the compilation phase, whichmeans their lengths are dialect-dependent. So on oracle a labelthat gets truncated to 30 chars will go out to 63 characterson postgres. Also, the true labelname is always attached as theaccessor on the parent Selectable so there’s no need to be awareof the “truncated” label names.References: #512

• [sql]

column label and bind param “truncation” also generatedeterministic names now, based on their ordering within thefull statement being compiled. this means the same statementwill produce the same string across application restarts andallowing DB query plan caching to work better.

• [sql]

the “mini” column labels generated when using subqueries, whichare to work around glitchy SQLite behavior that doesn’t understand“foo.id” as equivalent to “id”, are now only generated in the casethat those named columns are selected from (part of)References: #513

• [sql]

the label() method on ColumnElement will properly propagate theTypeEngine of the base element out to the label, including a label()created from a scalar=True select() statement.

• [sql]

MS-SQL better detects when a query is a subquery and knows not togenerate ORDER BY phrases for thoseReferences: #513

• [sql]

fix for fetchmany() “size” argument being positional in mostdbapisReferences: #505

• [sql]

sending None as an argument to func. will producean argument of NULL

• [sql]

query strings in unicode URLs get keys encoded to asciifor **kwargs compat

• [sql]

slight tweak to raw execute() change to also support tuplesfor positional parameters, not just listsReferences: #523

• [sql]

fix to case() construct to propagate the type of the firstWHEN condition as the return type of the case statement

mysql

• [mysql]

support for SSL arguments given as inline within URL query string,prefixed with “ssl_”, courtesy terjeros@gmail.com.

• [mysql] []

mysql uses “DESCRIBE.”, catching exceptionsif table doesn’t exist, in order to determine if a table exists.this supports unicode table names as well as schema names. testedwith MySQL5 but should work with 4.1 series as well. (#557)

sqlite

• [sqlite]

removed silly behavior where sqlite would reflect UNIQUE indexesas part of the primary key (?!)

mssql

• [mssql]

pyodbc is now the preferred DB-API for MSSQL, and if no module isspecifically requested, will be loaded first on a module probe.

• [mssql]

The @@SCOPE_IDENTITY is now used instead of @@IDENTITY. Thisbehavior may be overridden with the engine_connect“use_scope_identity” keyword parameter, which may also be specifiedin the dburi.

oracle

• [oracle]

small fix to allow successive compiles of the same SELECT objectwhich features LIMIT/OFFSET. oracle dialect needs to modifythe object to have ROW_NUMBER OVER and wasn’t performingthe full series of steps on successive compiles.

misc

• [engines]

warnings module used for issuing warnings (instead of logging)

• [engines]

cleanup of DBAPI import strategies across all enginesReferences: #480

• [engines]

refactoring of engine internals which reduces complexity,number of codepaths; places more state inside of ExecutionContextto allow more dialect control of cursor handling, result sets.ResultProxy totally refactored and also has two versions of“buffered” result sets used for different purposes.

• [engines]

server side cursor support fully functional in postgres.References: #514

• [engines]

improved framework for auto-invalidation of connections that havelost their underlying database, via dialect-specific detectionof exceptions corresponding to that database’s disconnectrelated error messages. Additionally, when a “connection nolonger open” condition is detected, the entire connection poolis discarded and replaced with a new instance. #516

• [engines]

the dialects within sqlalchemy.databases become a setuptoolsentry points. loading the built-in database dialects works thesame as always, but if none found will fall back to tryingpkg_resources to load an external moduleReferences: #521

• [engines]

Engine contains a “url” attribute referencing the url.URL objectused by create_engine().

• [informix]

informix support added ! courtesy James Zhang, who put a tonof effort in.

• [extensions]

big fix to AssociationProxy so that multiple AssociationProxyobjects can be associated with a single association collection.

• [extensions]

assignmapper names methods according to their keys (i.e. _name)#551

0.3.6

Released: Fri Mar 23 2007

orm

• [orm]

the full featureset of the SelectResults extension has been mergedinto a new set of methods available off of Query. These methodsall provide “generative” behavior, whereby the Query is copiedand a new one returned with additional criterion added.The new methods include:

• filter() - applies select criterion to the query

• filter_by() - applies “by”-style criterion to the query

• avg() - return the avg() function on the given column

• join() - join to a property (or across a list of properties)

• outerjoin() - like join() but uses LEFT OUTER JOIN

• limit()/offset() - apply LIMIT/OFFSET range-based accesswhich applies limit/offset: session.query(Foo)[3:5]

• distinct() - apply DISTINCT

• list() - evaluate the criterion and return results

no incompatible changes have been made to Query’s API and no methodshave been deprecated. Existing methods like select(), select_by(),get(), get_by() all execute the query at once and return resultslike they always did. join_to()/join_via() are still there althoughthe generative join()/outerjoin() methods are easier to use.

• [orm]

the return value for multiple mappers used with instances() nowreturns a cartesian product of the requested list of mappers,represented as a list of tuples. this corresponds to the documentedbehavior. So that instances match up properly, the “uniquing” isdisabled when this feature is used.

• [orm]

Query has add_entity() and add_column() generative methods. thesewill add the given mapper/class or ColumnElement to the query atcompile time, and apply them to the instances() method. the user isresponsible for constructing reasonable join conditions (otherwiseyou can get full cartesian products). result set is the list oftuples, non-uniqued.

• [orm]

strings and columns can also be sent to the *args of instances()where those exact result columns will be part of the result tuples.

• [orm]

a full select() construct can be passed to query.select() (whichworked anyway), but also query.selectfirst(), query.selectone()which will be used as is (i.e. no query is compiled). workssimilarly to sending the results to instances().

• [orm]

eager loading will not “aliasize” “order by” clauses that wereplaced in the select statement by something other than the eagerloader itself, to fix possibility of dupe columns as illustrated in. however, this means you have to be more careful withthe columns placed in the “order by” of Query.select(), that youhave explicitly named them in your criterion (i.e. you cant rely onthe eager loader adding them in for you)References: #495

• [orm]

added a handy multi-use “identity_key()” method to Session, allowingthe generation of identity keys for primary key values, instances,and rows, courtesy Daniel Miller

• [orm]

many-to-many table will be properly handled even for operations thatoccur on the “backref” side of the operationReferences: #249

• [orm]

added “refresh-expire” cascade. allows refresh() andexpire() calls to propagate along relationships.References: #492

• [orm]

more fixes to polymorphic relations, involving proper lazy-clausegeneration on many-to-one relationships to polymorphic mappers. also fixes to detection of “direction”, more specifictargeting of columns that belong to the polymorphic union vs. thosethat don’t.References: #493

• [orm]

some fixes to relationship calcs when using “viewonly=True” to pullin other tables into the join condition which aren’t parent of therelationship’s parent/child mappings

• [orm]

flush fixes on cyclical-referential relationships that containreferences to other instances outside of the cyclical chain, whensome of the objects in the cycle are not actually part of the flush

• [orm]

put an aggressive check for “flushing object A with a collection ofB’s, but you put a C in the collection” error condition - even ifC is a subclass of B, unless B’s mapper loads polymorphically.Otherwise, the collection will later load a “B” which should be a“C” (since its not polymorphic) which breaks in bi-directionalrelationships (i.e. C has its A, but A’s backref will lazyload it asa different instance of type “B”) This check is goingto bite some of you who do this without issues, so the error messagewill also document a flag “enable_typechecks=False” to disable thischecking. But be aware that bi-directional relationships inparticular become fragile without this check.References: #500

sql

• [sql]

bindparam() names are now repeatable! specify twodistinct bindparam()s with the same name in a single statement,and the key will be shared. proper positional/named args translateat compile time. for the old behavior of “aliasing” bind parameterswith conflicting names, specify “unique=True” - this option isstill used internally for all the auto-generated (value-based)bind parameters.

• [sql]

slightly better support for bind params as column clauses, eithervia bindparam() or via literal(), i.e. select([literal(‘foo’)])

• [sql]

MetaData can bind to an engine either via “url” or “engine” kwargsto constructor, or by using connect() method. BoundMetaData isidentical to MetaData except engine_or_url param is required.DynamicMetaData is the same and provides thread-local connections bedefault.

• [sql]

exists() becomes useable as a standalone selectable, not just in aWHERE clause, i.e. exists([columns], criterion).select()

• [sql]

correlated subqueries work inside of ORDER BY, GROUP BY

• [sql]

fixed function execution with explicit connections, i.e.conn.execute(func.dosomething())

• [sql]

use_labels flag on select() wont auto-create labels for literal textcolumn elements, since we can make no assumptions about the text. tocreate labels for literal columns, you can say “somecol ASsomelabel”, or use literal_column(“somecol”).label(“somelabel”)

• [sql]

quoting wont occur for literal columns when they are “proxied” intothe column collection for their selectable (is_literal flag ispropagated). literal columns are specified vialiteral_column(“somestring”).

• [sql]

added “fold_equivalents” boolean argument to Join.select(), whichremoves ‘duplicate’ columns from the resulting column clause thatare known to be equivalent based on the join condition. this is ofgreat usage when constructing subqueries of joins which Postgrescomplains about if duplicate column names are present.

• [sql]

fixed use_alter flag on ForeignKeyConstraintReferences: #503

• [sql]

fixed usage of 2.4-only “reversed” in topological.pyReferences: #506

• [sql]

for hackers, refactored the “visitor” system of ClauseElement andSchemaItem so that the traversal of items is controlled by theClauseVisitor itself, using the method visitor.traverse(item).accept_visitor() methods can still be called directly but will notdo any traversal of child items. ClauseElement/SchemaItem now have aconfigurable get_children() method to return the collection of childelements for each parent object. This allows the full traversal ofitems to be clear and unambiguous (as well as loggable), with aneasy method of limiting a traversal (just pass flags which arepicked up by appropriate get_children() methods).References: #501

• [sql]

the “else_” parameter to the case statement now properly works whenset to zero.

mysql

• [mysql]

added a catchall **kwargs to MSString, to help reflection ofobscure types (like “varchar() binary” in MS 4.0)

• [mysql]

added explicit MSTimeStamp type which takes effect when usingtypes.TIMESTAMP.

oracle

• [oracle]

got binary working for any size input ! cx_oracle works fine,it was my fault as BINARY was being passed and not BLOB forsetinputsizes (also unit tests weren’t even setting input sizes).

• [oracle]

also fixed CLOB read/write on a separate changeset.

• [oracle]

auto_setinputsizes defaults to True for Oracle, fixed cases whereit improperly propagated bad types.

misc

• [extensions]

options() method on SelectResults now implemented “generatively”like the rest of the SelectResults methods. Butyou’re going to just use Query now anyway.References: #472

• [extensions]

query() method is added by assignmapper. this helps withnavigating to all the new generative methods on Query.

• [ms-sql]

  • removed seconds input on DATE column types (probably
  • should remove the time altogether)

• [ms-sql]

null values in float fields no longer raise errors

• [ms-sql]

LIMIT with OFFSET now raises an error (MS-SQL has no OFFSET support)

• [ms-sql]

added an facility to use the MSSQL type VARCHAR(max) instead of TEXTfor large unsized string fields. Use the new “text_as_varchar” toturn it on.References: #509

• [ms-sql]

ORDER BY clauses without a LIMIT are now stripped in subqueries, asMS-SQL forbids this usage

• [ms-sql]

cleanup of module importing code; specifiable DB-API module; moreexplicit ordering of module preferences.References: #480

0.3.5

Released: Thu Feb 22 2007

orm

• [orm] [bugs]

another refactoring to relationship calculation. Allows more accurateORM behavior with relationships from/to/between mappers, particularlypolymorphic mappers, also their usage with Query, SelectResults. ticketsinclude,,.References: #439, #441, #448

• [orm] [bugs]

removed deprecated method of specifying custom collections on classes;you must now use the “collection_class” option. the old way wasbeginning to produce conflicts when people used assign_mapper(), whichnow patches an “options” method, in conjunction with a relationshipnamed “options”. (relationships take precedence over monkeypatchedassign_mapper methods).

• [orm] [bugs]

extension() query option propagates to Mapper._instance() method so thatall loading-related methods get calledReferences: #454

• [orm] [bugs]

eager relation to an inheriting mapper wont fail if no rows returned forthe relationship.

• [orm] [bugs]

eager relation loading bug fixed for eager relation on multipledescendant classesReferences: #486

• [orm] [bugs]

fix for very large topological sorts, courtesy ants.aasma at gmailReferences: #423

• [orm] [bugs]

eager loading is slightly more strict about detecting “self-referential”relationships, specifically between polymorphic mappers. this results inan “eager degrade” to lazy loading.

• [orm] [bugs]

improved support for complex queries embedded into “where” criterion forquery.select()References: #449

• [orm] [bugs]

mapper options like eagerload(), lazyload(), deferred(), will work for“synonym()” relationshipsReferences: #485

• [orm] [bugs]

fixed bug where cascade operations incorrectly included deletedcollection items in the cascadeReferences: #445

• [orm] [bugs]

fixed relationship deletion error when one-to-many child item is movedto a new parent in a single unit of workReferences: #478

• [orm] [bugs]

fixed relationship deletion error where parent/child with a singlecolumn as PK/FK on the child would raise a “blank out the primary key”error, if manually deleted or “delete” cascade without “delete-orphan”was used

• [orm] [bugs]

fix to deferred so that load operation doesn’t mistakenly occur when onlyPK col attributes are set

• [orm] [enhancements]

implemented foreign_keys argument to mapper. use inconjunction with primaryjoin/secondaryjoin arguments to specify/overrideforeign keys defined on the Table instance.References: #385

• [orm] [enhancements]

contains_eager(‘foo’) automatically implies eagerload(‘foo’)

• [orm] [enhancements]

added “alias” argument to contains_eager(). use it to specify the stringname or Alias instance of an alias used in the query for the eagerlyloaded child items. easier to use than “decorator”

• [orm] [enhancements]

added “contains_alias()” option for result set mapping to an alias ofthe mapped table

• [orm] [enhancements]

added support for py2.5 “with” statement with SessionTransactionReferences: #468

sql

• [sql]

the value of “case_sensitive” defaults to True now, regardless of thecasing of the identifier, unless specifically set to False. this isbecause the object might be label’ed as something else which doescontain mixed case, and propagating “case_sensitive=False” breaks that.Other fixes to quoting when using labels and “fake” column objects

• [sql]

added a “supports_execution()” method to ClauseElement, so thatindividual kinds of clauses can express if they are appropriate forexecuting…such as, you can execute a “select”, but not a “Table” or a“Join”.

• [sql]

fixed argument passing to straight textual execute() on engine,connection. can handle args or a list instance for positional, *kwargsor a dict instance for named args, or a list of list or dicts to invokeexecutemany()

• [sql]

small fix to BoundMetaData to accept unicode or string URLs

• [sql]

fixed named PrimaryKeyConstraint generation courtesyandrija at gmailReferences: #466

• [sql]

fixed generation of CHECK constraints on columnsReferences: #464

• [sql]

fixes to tometadata() operation to propagate Constraints at column andtable level

mysql

• [mysql]

fix to reflection on older DB’s that might return array() type for“show variables like” statements

mssql

• [mssql]

preliminary support for pyodbc (Yay!)References: #419

• [mssql]

better support for NVARCHAR types addedReferences: #298

• [mssql]

fix for commit logic on pymssql

• [mssql]

fix for query.get() with schemaReferences: #456

• [mssql]

fix for non-integer relationshipsReferences: #473

• [mssql]

DB-API module now selectable at run-timeReferences: #419

• [mssql] [415] [481] [tickets:422]

now passes many more unit tests

• [mssql]

better unittest compatibility with ANSI functionsReferences: #479

• [mssql]

improved support for implicit sequence PK columns with auto-insertReferences: #415

• [mssql]

fix for blank password in adodbapiReferences: #371

• [mssql]

fixes to get unit tests working with pyodbcReferences: #481

• [mssql]

fix to auto_identity_insert on db-url query

• [mssql]

added query_timeout to db-url query parms. currently works only forpymssql

• [mssql]

tested with pymssql 0.8.0 (which is now LGPL)

oracle

• [oracle]

when returning “rowid” as the ORDER BY column or in use with ROW_NUMBEROVER, oracle dialect checks the selectable its being applied to and willswitch to table PK if not applicable, i.e. for a UNION. checking forDISTINCT, GROUP BY (other places that rowid is invalid) still a TODO.allows polymorphic mappings to function.References: #436

• [oracle]

sequences on a non-pk column will properly fire off on INSERT

• [oracle]

added PrefetchingResultProxy support to pre-fetch LOB columns when theyare known to be present, fixesReferences: #435

• [oracle]

implemented reflection of tables based on synonyms, including acrossdblinksReferences: #379

• [oracle]

issues a log warning when a related table cant be reflected due tocertain permission errorsReferences: #363

misc

• [postgres]

better reflection of sequences for alternate-schema TablesReferences: #442

• [postgres]

sequences on a non-pk column will properly fire off on INSERT

• [postgres]

added PGInterval type, PGInet typeReferences: #444, #460

• [extensions]

added distinct() method to SelectResults. generally should only make adifference when using count().

• [extensions]

added options() method to SelectResults, equivalent to query.options()References: #472

• [extensions]

added optional table_opts dictionary to ActiveMapper, will send kwoptions to Table objectsReferences: #462

• [extensions]

added selectfirst(), selectfirst_by() to assign_mapperReferences: #467

0.3.4

Released: Tue Jan 23 2007

general

• [general]

global “insure”->”ensure” change. in US english “insure” is actuallylargely interchangeable with “ensure” (so says the dictionary), so I’m notcompletely illiterate, but its definitely sub-optimal to “ensure” which isnon-ambiguous.

orm

• [orm]

poked the first hole in the can of worms: sayingquery.select_by(somerelationname=someinstance) will create the join of theprimary key columns represented by “somerelationname“‘s mapper to theactual primary key in “someinstance”.

• [orm]

reworked how relations interact with “polymorphic” mappers, i.e. mappersthat have a select_table as well as polymorphic flags. better determinationof proper join conditions, interaction with user- defined join conditions,and support for self-referential polymorphic mappers.

• [orm]

related to polymorphic mapping relations, some deeper error checking whencompiling relations, to detect an ambiguous “primaryjoin” in the case thatboth sides of the relationship have foreign key references in the primaryjoin condition. also tightened down conditions used to locate “relationdirection”, associating the “foreignkey” of the relationship with the“primaryjoin”

• [orm]

a little bit of improvement to the concept of a “concrete” inheritancemapping, though that concept is not well fleshed out yet (added test caseto support concrete mappers on top of a polymorphic base).

• [orm]

fix to “proxy=True” behavior on synonym()

• [orm]

fixed bug where delete-orphan basically didn’t work with many-to-manyrelationships, backref presence generally hid the symptomReferences: #427

• [orm]

added a mutex to the mapper compilation step. ive been reluctant to add anykind of threading anything to SA but this is one spot that its reallyneeded since mappers are typically “global”, and while their state does notchange during normal operation, the initial compilation step does modifyinternal state significantly, and this step usually occurs not atmodule-level initialization time (unless you call compile()) but atfirst-request time

• [orm]

basic idea of “session.merge()” actually implemented. needs more testing.

• [orm]

added “compile_mappers()” function as a shortcut to compiling all mappers

• [orm]

fix to MapperExtension create_instance so that entity_name properlyassociated with new instance

• [orm]

speed enhancements to ORM object instantiation, eager loading of rows

• [orm]

invalid options sent to ‘cascade’ string will raise an exceptionReferences: #406

• [orm]

fixed bug in mapper refresh/expire whereby eager loaders didn’t properlyre-populate item listsReferences: #407

• [orm]

fix to post_update to ensure rows are updated even for non insert/deletescenariosReferences: #413

• [orm]

added an error message if you actually try to modify primary key values onan entity and then flush itReferences: #412

sql

• [sql]

added “fetchmany()” support to ResultProxy

• [sql]

added support for column “key” attribute to be useable inrow[]/row.

• [sql]

changed “BooleanExpression” to subclass from “BinaryExpression”, so thatboolean expressions can also follow column-clause behaviors (i.e. label(),etc).

• [sql]

trailing underscores are trimmed from func. calls, such as func.if_()

• [sql]

fix to correlation of subqueries when the column list of the selectstatement is constructed with individual calls to append_column(); thisfixes an ORM bug whereby nested select statements were not gettingcorrelated with the main select generated by the Query object.

• [sql]

another fix to subquery correlation so that a subquery which has only oneFROM element will not correlate that single element, since at least oneFROM element is required in a query.

• [sql]

default “timezone” setting is now False. this corresponds to Python’sdatetime behavior as well as Postgres’ timestamp/time types (which is theonly timezone-sensitive dialect at the moment)References: #414

• [sql]

the “op()” function is now treated as an “operation”, rather than a“comparison”. the difference is, an operation produces a BinaryExpressionfrom which further operations can occur whereas comparison produces themore restrictive BooleanExpression

• [sql]

trying to redefine a reflected primary key column as non-primary key raisesan error

• [sql]

type system slightly modified to support TypeDecorators that can beoverridden by the dialect (ok, that’s not very clear, it allows the mssqltweak below to be possible)

mysql

• [mysql]

mysql is inconsistent with what kinds of quotes it uses in foreign keysduring a SHOW CREATE TABLE, reflection updated to accommodate for all threestylesReferences: #420

• [mysql]

mysql table create options work on a generic passthru now, i.e. Table(…,mysqlengine=’InnoDB’, mysql_collate=”latin1_german2_ci”,mysql_auto_increment=”5”, mysql…), helpsReferences: #418

mssql

• [mssql]

added an NVarchar type (produces NVARCHAR), also MSUnicode which providesUnicode-translation for the NVarchar regardless of dialect convert_unicodesetting.

oracle

• [oracle]

slight support for binary, but still need to figure out how to insertreasonably large values (over 4K). requires auto_setinputsizes=True sent tocreate_engine(), rows must be fully fetched individually, etc.

firebird

• [firebird]

order of constraint creation puts primary key first before all otherconstraints; required for firebird, not a bad idea for othersReferences: #408

• [firebird]

Firebird fix to autoload multifield foreign keysReferences: #409

• [firebird]

Firebird NUMERIC type properly handles a type without precisionReferences: #409

misc

• [postgres]

fix to the initial checkfirst for tables to take current schema intoaccountReferences: #424

• [postgres]

postgres has an optional “server_side_cursors=True” flag which will utilizeserver side cursors. these are appropriate for fetching only partialresults and are necessary for working with very large unbounded resultsets. While we’d like this to be the default behavior, differentenvironments seem to have different results and the causes have not beenisolated so we are leaving the feature off by default for now. Uses anapparently undocumented psycopg2 behavior recently discovered on thepsycopg mailing list.

• [postgres]

added “BIGSERIAL” support for postgres table withPGBigInteger/autoincrement

• [postgres]

fixes to postgres reflection to better handle when schema names arepresent; thanks to jason (at) ncsmags.comReferences: #402

• [extensions]

added “validate=False” argument to assign_mapper, if True will ensure thatonly mapped attributes are namedReferences: #426

• [extensions]

assign_mapper gets “options”, “instances” functions added (i.e.MyClass.instances())

0.3.3

Released: Fri Dec 15 2006

string-based FROM clauses fixed, i.e. select(…, from_obj=[“sometext”])

fixes to passive_deletes flag, lazy=None (noload) flag

added example/docs for dealing with large collections

added object_session() method to sqlalchemy namespace

fixed QueuePool bug whereby its better able to reconnect to a databasethat was not reachable (thanks to Sébastien Lelong), also fixed dispose()method

patch that makes MySQL rowcount work correctly!References: #396

fix to MySQL catch of 2006/2014 errors to properly re-raise OperationalErrorexception

0.3.2

Released: Sun Dec 10 2006

major connection pool bug fixed. fixes MySQL out of syncerrors, will also prevent transactions getting rolled backaccidentally in all DBsReferences: #387

major speed enhancements vs. 0.3.1, to bring speedback to 0.2.8 levels

made conditional dozens of debug log calls that weretime-intensive to generate log messages

fixed bug in cascade rules whereby the entire object graphcould be unnecessarily cascaded on the save/update cascade

various speedups in attributes module

identity map in Session is by default no longer weak referencing.to have it be weak referencing, use create_session(weak_identity_map=True)fixesReferences: #388

MySQL detects errors 2006 (server has gone away) and 2014(commands out of sync) and invalidates the connection on which it occurred.

MySQL bool type fix:References: #307

postgres reflection fixes:References: #349, #382

added keywords for EXCEPT, INTERSECT, EXCEPT ALL, INTERSECT ALLReferences: #247

assign_mapper in assignmapper extension returns the created mapperReferences: #2110

added label() function to Select class, when scalar=True is usedto create a scalar subqueryi.e. “select x, y, (select max(foo) from table) AS foomax from table”

added onupdate and ondelete keyword arguments to ForeignKey; propagateto underlying ForeignKeyConstraint if present. (don’t propagate in theother direction, however)

fix to session.update() to preserve “dirty” status of incoming object

sending a selectable to an IN via the in() function no longer createsa “union” out of multiple selects; only one selectable to a the in() functionis allowed now (make a union yourself if union is needed)

improved support for disabling save-update cascade via cascade=”none” etc.

added “remote_side” argument to relation(), used only with self-referentialmappers to force the direction of the parent/child relationship. replacesthe usage of the “foreignkey” parameter for “switching” the direction.“foreignkey” argument is deprecated for all uses and will eventuallybe replaced by an argument dedicated to ForeignKey specification on mappers.

0.3.1

Released: Mon Nov 13 2006

orm

• [orm]

the “delete” cascade will load in all child objects, if they were notloaded already. this can be turned off (i.e. the old behavior) by settingpassive_deletes=True on a relation().

• [orm]

adjustments to reworked eager query generation to not fail on circulareager-loaded relationships (like backrefs)

• [orm]

fixed bug where eagerload() (nor lazyload()) option didn’t properlyinstruct the Query whether or not to use “nesting” when producing aLIMIT query.

• [orm]

fixed bug in circular dependency sorting at flush time; if object Acontained a cyclical many-to-one relationship to object B, and object Bwas just attached to object A, but object B itself wasn’t changed,the many-to-one synchronize of B’s primary key attribute to A’s foreign keyattribute wouldn’t occur.References: #360

• [orm]

implemented from_obj argument for query.count, improves count functionon selectresultsReferences: #325

• [orm]

added an assertion within the “cascade” step of ORM relationships to checkthat the class of object attached to a parent object is appropriate(i.e. if A.items stores B objects, raise an error if a C is appended to A.items)

• [orm]

new extension sqlalchemy.ext.associationproxy, provides transparent“association object” mappings. new exampleexamples/association/proxied_association.py illustrates.

• [orm]

improvement to single table inheritance to load full hierarchies beneaththe target class

• [orm]

fix to subtle condition in topological sort where a node could appear twice,forReferences: #362

• [orm]

additional rework to topological sort, refactoring, forReferences: #365

• [orm]

“delete-orphan” for a certain type can be set on more than one parent class;the instance is an “orphan” only if its not attached to any of those parents

misc

• [engine/pool]

some new Pool utility classes, updated docs

• [engine/pool]

“use_threadlocal” on Pool defaults to False (same as create_engine)

• [engine/pool]

fixed direct execution of Compiled objects

• [engine/pool]

create_engine() reworked to be strict about incoming **kwargs. all keywordarguments must be consumed by one of the dialect, connection pool, and engineconstructors, else a TypeError is thrown which describes the full set ofinvalid kwargs in relation to the selected dialect/pool/engine configuration.

• [databases/types]

MySQL catches exception on “describe” and reports as NoSuchTableError

• [databases/types]

further fixes to sqlite booleans, weren’t working as defaults

• [databases/types]

fix to postgres sequence quoting when using schemas

0.3.0

Released: Sun Oct 22 2006

general

• [general]

logging is now implemented via standard python “logging” module.“echo” keyword parameters are still functional but set/unsetlog levels for their respective classes/instances. all loggingcan be controlled directly through the Python API by settingINFO and DEBUG levels for loggers in the “sqlalchemy” namespace.class-level logging is under “sqlalchemy..”,instance-level logging under “sqlalchemy...0x..<00-FF>”.Test suite includes “–log-info” and “–log-debug” argumentswhich work independently of –verbose/–quiet. Logging addedto orm to allow tracking of mapper configurations, row iteration.

• [general]

the documentation-generation system has been overhauled to bemuch simpler in design and more integrated with Markdown

orm

• [orm]

attribute tracking modified to be more intelligent about detectingchanges, particularly with mutable types. TypeEngine objects nowtake a greater role in defining how to compare two scalar instances,including the addition of a MutableType mixin which is implemented byPickleType. unit-of-work now tracks the “dirty” list as an expressionof all persistent objects where the attribute manager detects changes.The basic issue that’s fixed is detecting changes on PickleTypeobjects, but also generalizes type handling and “modified” objectchecking to be more complete and extensible.

• [orm]

a wide refactoring to “attribute loader” and “options” architectures.ColumnProperty and PropertyLoader define their loading behavior via switchable“strategies”, and MapperOptions no longer use mapper/property copyingin order to function; they are instead propagated via QueryContextand SelectionContext objects at query/instances time.All of the internal copying of mappers and properties that was used to handleinheritance as well as options() has been removed; the structureof mappers and properties is much simpler than before and is clearly laid outin the new ‘interfaces’ module.

• [orm]

related to the mapper/property overhaul, internal refactoring tomapper instances() method to use a SelectionContext object to trackstate during the operation.SLIGHT API BREAKAGE: the append_result() and populate_instances()methods on MapperExtension have a slightly different method signaturenow as a result of the change; hoping that these methods are notin widespread use as of yet.

• [orm]

instances() method moved to Query now, backwards-compatibleversion remains on Mapper.

• [orm]

added contains_eager() MapperOption, used in conjunction withinstances() to specify properties that should be eagerly loadedfrom the result set, using their plain column names by default, or translatedgiven an custom row-translation function.

• [orm]

more rearrangements of unit-of-work commit scheme to better allowdependencies within circular flushes to work properly…updatedtask traversal/logging implementation

• [orm]

polymorphic mappers (i.e. using inheritance) now produces INSERTstatements in order of tables across all inherited classesReferences: #321

• [orm]

added an automatic “row switch” feature to mapping, which willdetect a pending instance/deleted instance pair with the sameidentity key and convert the INSERT/DELETE to a single UPDATE

• [orm]

“association” mappings simplified to take advantage ofautomatic “row switch” feature

• [orm]

“custom list classes” is now implemented via the “collection_class”keyword argument to relation(). the old way still works but isdeprecatedReferences: #212

• [orm]

added “viewonly” flag to relation(), allows construction ofrelations that have no effect on the flush() process.

• [orm]

added “lockmode” argument to base Query select/get functions,including “with_lockmode” function to get a Query copy that hasa default locking mode. Will translate “read”/”update”arguments into a for_update argument on the select side.References: #292

• [orm]

implemented “version check” logic in Query/Mapper, usedwhen version_id_col is in effect and query.with_lockmode()is used to get() an instance that’s already loaded

• [orm]

post_update behavior improved; does a better job at notupdating too many rows, updates only required columnsReferences: #208

• [orm]

adjustments to eager loading so that its “eager chain” iskept separate from the normal mapper setup, therebypreventing conflicts with lazy loader operation, fixesReferences: #308

• [orm]

fix to deferred group loading

• [orm]

session.flush() wont close a connection it openedReferences: #346

• [orm]

added “batch=True” flag to mapper; if False, save_objwill fully save one object at a time including callsto before_XXXX and after_XXXX

• [orm]

added “columnprefix=None” argument to mapper; prepends thegiven string (typically ‘’) to column-based attributes automaticallyset up from the mapper’s Table

• [orm]

specifying joins in the from_obj argument of query.select() willreplace the main table of the query, if the table is somewhere withinthe given from_obj. this makes it possible to produce custom joins andouterjoins in queries without the main table getting added twice.References: #315

• [orm]

eagerloading is adjusted to more thoughtfully attach its LEFT OUTER JOINsto the given query, looking for custom “FROM” clauses that may havealready been set up.

• [orm]

added join_to and outerjoin_to transformative methods to SelectResults,to build up join/outerjoin conditions based on property names. alsoadded select_from to explicitly set from_obj parameter.

• [orm]

removed “is_primary” flag from mapper.

sql

• [sql] [construction]

changed “for_update” parameter to accept False/True/”nowait”and “read”, the latter two of which are interpreted only byOracle and MySQLReferences: #292

• [sql] [construction]

added extract() function to sql dialect(SELECT extract(field FROM expr))

• [sql] [construction]

BooleanExpression includes new “negate” argument to specifythe appropriate negation operator if one is available.

• [sql] [construction]

calling a negation on an “IN” or “IS” clause will result in“NOT IN”, “IS NOT” (as opposed to NOT (x IN y)).

• [sql] [construction]

Function objects know what to do in a FROM clause now. theirbehavior should be the same, except now you can also do things likeselect([‘*’], from_obj=[func.my_function()]) to get multiplecolumns from the result, or even use sql.column() constructs to name thereturn columnsReferences: #172

schema

• [schema]

a fair amount of cleanup to the schema package, removal of ambiguousmethods, methods that are no longer needed. slightly more constrainedusage, greater emphasis on explicitness

• [schema]

the “primary_key” attribute of Table and other selectables becomesa setlike ColumnCollection object; is ordered but not numericallyindexed. a comparison clause between two pks that are derived from thesame underlying tables (i.e. such as two Alias objects) can be generatedvia table1.primary_key==table2.primary_key

• [schema]

ForeignKey(Constraint) supports “use_alter=True”, to create/drop a foreign keyvia ALTER. this allows circular foreign key relationships to be set up.

• [schema]

append_item() methods removed from Table and Column; preferablyconstruct Table/Column/related objects inline, but if needed useappend_column(), append_foreign_key(), append_constraint(), etc.

• [schema]

table.create() no longer returns the Table object, instead has noreturn value. the usual case is that tables are created via metadata,which is preferable since it will handle table dependencies.

• [schema]

added UniqueConstraint (goes at Table level), CheckConstraint(goes at Table or Column level).

• [schema]

index=False/unique=True on Column now creates a UniqueConstraint,index=True/unique=False creates a plain Index,index=True/unique=True on Column creates a unique Index. ‘index’and ‘unique’ keyword arguments to column are now boolean only; forexplicit names and groupings of indexes or unique constraints, use theUniqueConstraint/Index constructs explicitly.

• [schema]

added autoincrement=True to Column; will disable schema generationof SERIAL/AUTO_INCREMENT/identity seq for postgres/mysql/mssql ifexplicitly set to False

• [schema]

TypeEngine objects now have methods to deal with copying and comparingvalues of their specific type. Currently used by the ORM, see below.

• [schema]

fixed condition that occurred during reflection when a primary keycolumn was explicitly overridden, where the PrimaryKeyConstraint wouldget both the reflected and the programmatic column doubled up

• [schema]

the “foreign_key” attribute on Column and ColumnElement in generalis deprecated, in favor of the “foreign_keys” list/set-based attribute,which takes into account multiple foreign keys on one column.“foreign_key” will return the first element in the “foreign_keys” list/setor None if the list is empty.

sqlite

• [sqlite]

sqlite boolean datatype converts False/True to 0/1 by default

• [sqlite]

fixes to Date/Time (SLDate/SLTime) types; works as good as postgresnowReferences: #335

oracle

• [oracle]

Oracle has experimental support for cx_Oracle.TIMESTAMP, which requiresa setinputsizes() call on the cursor that is now enabled via the‘auto_setinputsizes’ flag to the oracle dialect.

firebird

• [firebird]

aliases do not use “AS”

• [firebird]

correctly raises NoSuchTableError when reflecting non-existent table

misc

• [ms-sql]

fixes bug 261 (table reflection broken for MS-SQL case-sensitivedatabases)

• [ms-sql]

can now specify port for pymssql

• [ms-sql]

introduces new “auto_identity_insert” option for auto-switchingbetween “SET IDENTITY_INSERT” mode when values specified for IDENTITY columns

• [ms-sql]

now supports multi-column foreign keys

• [ms-sql]

fix to reflecting date/datetime columns

• [ms-sql]

NCHAR and NVARCHAR type support added

• [connections/pooling/execution]

connection pool tracks open cursors and automatically closes themif connection is returned to pool with cursors still opened. Can beaffected by options which cause it to raise an error instead, or todo nothing. fixes issues with MySQL, others

• [connections/pooling/execution]

fixed bug where Connection wouldn’t lose its Transactionafter commit/rollback

• [connections/pooling/execution]

added scalar() method to ComposedSQLEngine, ResultProxy

• [connections/pooling/execution]

ResultProxy will close() the underlying cursor when the ResultProxyitself is closed. this will auto-close cursors for ResultProxy objectsthat have had all their rows fetched (or had scalar() called).

• [connections/pooling/execution]

ResultProxy.fetchall() internally uses DBAPI fetchall() for better efficiency,added to mapper iteration as well (courtesy Michael Twomey)