Using SQLAlchemy

SQLAlchemy is a new ORM library developed by Michael Bayer. Michael claims
that SQLAlchemy is not a framework and does not need to dictate program
architecutre in order to work. The fundamental concept (borrowed from Java's
Hibernate) is the concept of a Unit of Work, which batches related operations
in order to produce the most efficient SQL queries.

Notable advantages of SQLAlchemy over SQLObject are the ability to map
arbitrary selects, the ability to map muliple tables/selects onto a single
object, and support for composite primary keys.

The disadvantage is that SQLAlchemy is more complex than SQLObject, requiring
separate table description, object declaration, and object mapping statements.
SQLAlchemy object properties retain their database name (for better or worse)
while SQLObject provides the styles package to allow name reformatting to suit
your taste. You can override the naming on a per-attribute basis but not
algorithmically.

If you use SQLAlchemy, you will lose access to fastdata. This could be fixed
rather simply by adding sqlalchemy-knowledgable conditions to the RuleDispatch
checks in formmaker.py. You will also lose the use of CatWalk/ModelDesigner
and will have to explicitly call objectstore.commit() in your controller
(i.e. transactions are not implicit).

This document covers the 0.1.0 release and was written on February 13, 2006.

Getting SQLAlchemy

SQLAlchemy made its first public release (0.1.0) on February 13, 2006. You can
get it from the cheeseshop:

The documentation is slightly confused in some places, so be sure you notice
the 'monkeypatch' version of mapper, assign_mapper. Also don't miss
the arguments to relation, which are hidden towards the top of the
advanced mapping page.

For the basics of how SQLAlchemy works and how to perform mapping, refer to
the main SQLAlchemy docs. This document only covers points relevant to TG.

Configuration

This section describes how to set up SQLAlchemy so that it will use a seperate
config in development and production mode.

The first thing you need to be aware of when coming from SQLObject is that
dburis in SQLAlchemy have schemas that vary based on the backend. Yes, this
entirely defeats the purpose of having them in uri format. Pick your format
from one of the uris shown below:

Tips and Gotchas

You may want to keep echo off by default and turn it on when you're debugging
in tg-admin shell by simply engine.echo = 1 in the interpreter.

Be aware that SQLAlchemy has transactions on all the time via the
Unit of Work abstraction and they are NOT implicit. This means that you
need to call objectstore.commit() to push your changes to the db, even in
the controller methods. If you're running an assign_mapper object, you can
call the object's commit() directly to commit the object. A UoW rollback is
objectstore.clear().

You can change a column's name using the alias parameter of the Column
constructor.

Custom Column Types

SQLObject uses formencode to perform python to database conversions. This
shows how to use the SQLAlchemy column datatypes. Below are two examples, one
which converts a number representing the system timestamp to a python datetime
while the other converts IPv4 addresses between integer and octet notation. Keep
in mind that convert_bind_param is to the database while
convert_result_value is from the database.