History of SQLAlchemy

  • 0.1 - 2006, very small API, threadlocal, ActiveMapper added to handle objects nicely
  • 0.2 - Lots of users, changed the API to be closer to today, inspired by Hibernate, Threadlocal turned off, wrote very fast
  • 0.3 - Rewrite a lot internals, ORMs reorganized, users needs evaluated, API stabilized
  • 0.4 - Here come developers to help out!, Lots of internal refactoring, speed profiling done, SQL expressions constructs done, lots of transactions and support for more databases.

Highlights of 0.4

  • Much faster than 0.3
  • Underlying code is simplified
  • lots less callcounts and redundant method calls
  • Smart operators in SQL calls to handle different database types

SQL Expression language

  • s - select([employees,c.id, employees,c.name])
  • select([func.now()]) will work with SQLlite!

New ORM Query ORM

  • Query object is fully generative. Mapped properties now have relational operators

Inline Aliasing

  • Alternate table ids to handle self-referencing tables in order to map out heirarchical data
  • All the work can be filtered against a single query expression
  • Easy dialogue making it very easy to implement
  • In 0.4, join() generates aliased joins and aliases criterion for you

High level operators

  • Has and Any are synonyms for SQL exists() method.

New ORM Configurations

  • In SQL 0.4.4 familiar table and mapper constructs can be moved into class declarations called ‘New Declarative Layer’.

Collections API

  • Sets, dictionaries, and any user-defined collection may be mapped using an open API
  • This means any python collection object can be used by SQLAlchemy
  • Use declarations to turn any object of your choice into SQLAlchemy objects. NICE!

Dynamic Relations

  • Very large collections can be managed by a ‘dynamic’ relation, which issues queries for every access
  • This way you don’t have to load a gigantic item into memory, just intelligent bits. A few restrictions, but it speeds things up

Polymorphic Inheritence

  • Whole inheritence hierachies can be loaded automatically
  • So mapping out standard fields in a table (Title, Description, Create, etc) is easy. This was a gotcha in that Django effort, remember?
  • Can use to specify criteria against a subclass.

New transactional features

  • Session can be configured to be ‘always transactional’, and optionally auto-flushing
  • Databases which supprt SAVEPOINT can nest transactions
  • Connections and ORM sessions both support two-phase commit semantics for supported databases

Other features

  • ORM supports mutable primary keys, ON UPDATE cascade.
  • Arbitrary SQL expressions can be asssigned to object attributes for ‘atomic’ update behavior
  • metadata.reflect() can load full schemas at once
  • New dialacts: MaxDB, Sybase, Access, Informix, DB2
  • Horizontal sharding: extension for ORM, transparently loads and saves rows across multiple databases

What is coming back

  • Migrate is back!
  • Dialects will soon decouple SQL compilers from DBAPI behavors, allowing reuse among JDBC, ODBC, multiple native DBAPI connectors
  • Jython support
  • Customize class instrumentation; PJE using it to integrate with Trellis
  • SQLAlchemy books