SQLAlchemy 0.7.7

SQLAlchemy is a utility built to give Python developers the ability to integrate SQL into their development process.

SQLAlchemy integrates a powerful suite of well known persistence patterns, suited for efficient and high-performing database access.

Here are some key features of "SQLAlchemy":

Supported Databases:
· SQLAlchemy includes dialects for SQLite, Postgres, MySQL, Oracle, MS-SQL, Firebird, MaxDB, MS Access, Sybase and Informix; IBM has also released a DB2 driver. The corresponding DB-API 2.0 implementation (or sometimes one of several available) is required to use each particular database.

Unit Of Work:
· The Unit Of Work system, a central part of SQLAlchemy's Object Relational Mapper (ORM), organizes pending create/insert/update/delete operations into queues and flushes them all in one batch. To accomplish this it performs a topological "dependency sort" of all modified items in the queue so as to honor foreign key constraints, and groups redundant statements together where they can sometimes be batched even further. This produces the maxiumum efficiency and transaction safety, and minimizes chances of deadlocks. Modeled after Fowler's "Unit of Work" pattern as well as Hibernate, Java's leading object-relational mapper.

Function-based query construction:
· Function-based query construction allows SQL clauses to be built via Python functions and expressions. The full range of what's possible includes boolean expressions, operators, functions, table aliases, selectable subqueries, create/update/insert/delete statements, correlated updates, correlated EXISTS clauses, UNION clauses, inner and outer joins, bind parameters, and free mixing of literal text within expressions. Constructed expressions are compilable specific to any number of vendor database implementations (such as PostGres or Oracle), as determined by the combination of a Dialect and Compiler provided by the implementation.

Separate mapping and class design:
· Database mapping and class design are totally separate. Persisted objects have no subclassing requirement (other than 'object') and are POPO's : plain old Python objects. They retain serializability (pickling) for usage in various caching systems and session objects. SQLAlchemy "decorates" classes with non-intrusive property accessors to automatically log object creates and modifications with the UnitOfWork engine, to lazyload related data, as well as to track attribute change histories.

Eager-loading of objects:
· Whole graphs of related objects can often be loaded with a single query that is automatically generated to join the appropriate tables together, known as eager loading. The alternative to eager loading, lazy loading, loads related objects via distinct query executions. Each type of loading produces identical results and are interchangeable, allowing configuration at any level as well as query-time selection of the relationship-loading method to be used.

Composite (multiple-column) primary keys:
· In SQLAlchemy, primary and foreign keys are represented as sets of columns; truly composite behavior is implemented from the ground up. The ORM has industrial strength support for meaningful (non-surrogate) primary keys, including mutability and compatibility with ON UPDATE CASCADE, as well as explicit support for other common composite PK patterns such as "association" objects (many-to-many relationships with extra meaning attached to each association).

Self-referential tables and mappers:
· Self-referential tables and mappers are supported. Adjacency list structures can be created, saved, and deleted with proper cascading, with no code overhead beyond that of non-self-referential structures. Self-referential structures of any depth can be eagerly loaded in a single statement using joins; you set how deep you'd like to go.

Inheritance Mapping:
· Explicit support is available for single-table, concrete-table, and joined table inheritance. Polymorphic loading (that is, a query that returns objects of multple descendant types) is supported for all three styles. The loading of each may be optimized such that only one round trip is used to fully load a polymorphic result set.

Raw SQL statment mapping:
· SQLA's data mapper can accomodate raw SQL statements as well as plain result sets, and object instances can be generated from these results in the same manner as any other ORM operation. Any hyper-optimized query that you or your DBA can cook up, you can run in SQLAlchemy, and as long as it returns the expected columns within a rowset, you can get your objects from it. Statements which represent multiple kinds of objects can be used as well, with results received as named-tuples, or with dependent objects routed into collections on parent objects.

Pre- and post-processing of data:
· The type system allows pre- and post- processing of data, both at the bind parameter and the result set level. User-defined types can be freely mixed with built-in types. Generic types as well as SQL-specific types are available.

What's New in This Release: [ read full changelog ]

orm:
· [bug] Fixed issue in unit of work
· whereby setting a non-None self-referential
· many-to-one relationship to None
· would fail to persist the change if the
· former value was not already loaded.

· [feature] Added prefix_with() method
· to Query, calls upon select().prefix_with()
· to allow placement of MySQL SELECT
· directives in statements.Courtesy
· Diana Clarke

· [bug] Fixed bug in 0.7.6 introduced by
· [ticket:2409] whereby column_mapped_collection
· used against columns that were mapped as
· joins or other indirect selectables
· would fail to function.

· [feature] Added new flag to @validates
· include_removes.When True, collection
· remove and attribute del events
· will also be sent to the validation function,
· which accepts an additional argument
· "is_remove" when this flag is used.

· [bug] Fixed bug whereby polymorphic_on
· column that's not otherwise mapped on the
· class would be incorrectly included
· in a merge() operation, raising an error.

· [bug] Fixed bug in expression annotation
· mechanics which coul...