Section A.2. SQLAlchemy in TurboGears

A.2. SQLAlchemy in TurboGears

TurboGears 1.0 uses SQLObject as its default database layer. This is partly due to SQLAlchemy's release status, because it remains a fairly recently released package with a low version number. More importantly, as of this writing, TurboGears tools such as CatWalk and Model Designer do not yet support SQLAlchemy.

However, everything you need to use SQLAlchemy in a TurboGears project is available, and quite a few significant projects are already using SQLAlchemy in production. Support for SQLAlchemy is readily available on the mailing list should you run into problems. If SQLAlchemy fits your project, you can feel confident that it is a good solution that delivers on its promises.

TurboGears support for SQLAlchemy includes the following:

• Quickstart can generate an SQLAlchemy-based project. Your project will have all the proper imports and configuration to use SQLAlchemy.

• The Identity package includes an identity provider for SQLAlchemy.

• TurboGears provides implicit database transactions around your requests.

• The tg-admin sql command supports SQLAlchemy.

When you quickstart a new project, you can specify that it is an SQLAlchemy project by adding the -s parameter. For example:

tg-admin quickstart -s ProjectName

Note

TurboGears does not let you mix SQLAlchemy and SQLObject in the same project.

When you do this, the generated configuration files will be correct for SQLAlchemy, and your model.py file will be all set up to use SQLAlchemy.

When working with your SQLAlchemy project, you will find two objects in turbogears.database that will prove useful to you: session and metadata.

When defining SQLAlchemy Table objects, be sure to define the tables in the turbogears.database.metadata if you want commands such as tg-admin sql create to work properly.

TurboGears provides implicit transactions around each request. This includes a session.flush() and also includes session.clear() at appropriate points in the request cycle. If you create a new object that needs to be stored in the database, you should use session.save(new_object) on the session located in turbogears.database.