27.04.2007

And the winner is....

SQLAlchemy


SQLAlchemy would deserve the first price for the best Python package ever if there would be such a price.

SQLAlchemy is cool! Why is it cool? Because it solves all my problems!

Lets look at a real world example. I maintain a legacy system which is a hybrid solution storing binary content within the ZODB while keeping the metadata within a Postgres database. Internally we have to deal with hierarchies which are represented within the databases as self-referential table. A self-referential table allows you to store tree-like structure by having a back-references to the parent node. You can imagine that serializing and de-serializing a tree-like structure into a flat table structure within a RDBMS is big pain in the a**. The old legacy code for doing this consists of about 800 lines of Python code and SQL statements. Writing and maintaining such code is boring and really without fun. Well, such a self-referential is complicated enough but these tree structure also have internal references to other trees within the same table and reference to some other tables. So the complete tables is big forest of connected trees.

Lately I started looking at SQLAlchemy in the hope to get somehow out of the SQL hell. In SQLAlchemy every row of a particular table is represented as an instance of a mapper class. Fortunately SQLAlchemy provides support self-referential tables out-of-the-box. You configure your mapper class as a self-referential mapper, you perform a SELECT on the top node and then you're done. SQLAlchemy knows  how to deal with the parent-child relationship (by configuration) and make sthe children nodes of the tree available a Python property of the current node. Children node are loaded on request. That nodes are only loaded from the database if needed.

More advantages of SQLAlchemy: you can modify nodes within the subtree by traversing to them and changing their properties. To save a complete
tree you just have to save the root node only.  SQLAlchemy knows how update the rows for the modified nodes of the subtree. SQLAlchemy is doing that automatically for you. You don't have to keep track of changes,  you don't have to write a single SQL statement

What are the disadvantages of SQLAlchemy? I don't know any. SQLAlchemy has a very good documentation and a very responsive mailing list.

However:
  • SQLAlchemy is a big framework. The basics are easy however you must dig into SQLAlchemy when you face more complex scenarios like integrating SQLAlchemy with (unclean) legacy systems

See:

http://www.sqlalchemy.org
http://cheeseshop.python.org/pypi/z3c.sqlalchemy/