SQLAlchemy: Python SQL Toolkit and Object Relational Mapper

SQLAlchemy masthead from site

SQLAlchemy looks cool enough to make me go hunting for a Java equivalent for use on projects at work.

Take a look at the features page. I’m particularly interested in the Unit Of Work

The Unit Of Work system, a central part of SQLAlchemy’s Object Relational Mapper (ORM), organizes pending 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 inter-row dependencies, and groups redundant statements together where they can sometimes be batched even further. This produces the
maximum 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.

…and Raw SQL statement mapping

SQLA’s object relational query facilities can accommodate 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.

SQLAlchemy supports…

…dialects for SQLite, Postgresql, MySQL, Oracle, MS-SQL, Firebird, Sybase and others, most of which support multiple DBAPIs. Other dialects are
published as external projects. The corresponding DB-API 2.0 implementation (or sometimes one of several available) is required to use each
particular database. View Current DBAPI Support.

Bruce Eckel is a Java guy I respect. He wrote Thinking in Java and he said this about SQLAlchemy:

SQLAlchemy is a pretty amazing design…In SQLAlchemy, you need to explicitly start a session. What’s amazing is that all the changes you make
during that session are kept in some kind of parse tree, and then when the session ends SQL is created on-the-fly to produce a single, optimal
SQL statement for that particular sequence of changes. I found this idea pretty mind-blowing.

That sounds pretty sexy to me.