Warning: Can't synchronize with repository "(default)" (Unsupported version control system "svn": No module named svn). Look in the Trac log for more information.

Version 2 (modified by Karl Guertin, 13 years ago) (diff)

Minor update

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 objects by default retain their database_names rather than SQLObject's pythonicNames.

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 There are no other disadvantages to using SQLAlchemy with TurboGears.

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:

easy_install SQLAlchemy

Or grab it some other way via the SQLAlchemy Download Page. Michael provides thorough documentation and the pages on python sql construction, basic data mapping, and advanced data mapping are well worth the read.

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.


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:


And insert the following into your and files:

sqlalchemy.dburi = *<your connection uri>*
sqlalchemy.echo = 0                        #Set to 1 for echo

Now flip over to your <project>/ file and replace the contents with:

from sqlalchemy import *
import cherrypy

engine = create_engine(cherrypy.config.get('sqlalchemy.dburi'),

With those lines, you're ready to go. Keep everything SQLAlchemy related below the engine declaration.

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 implicit transactions on all the time via the Unit of Work abstraction. This means that you need to call objectstore.commit() to push your changes to the db. If you're running an assign_mapper object, you can call the object's commit() directly. A rollback is objectstore.clear().

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

Future Developments

Jonathan LaCour is working on a declarative way of describing forms through his ActiveMapper project.