SQLAlchemy and Transaction Config Settings


Though the majority of folks will use TurboGears with SQLAlchemy, there are those who have interest in running the full stack of TG with a non-relational database like MongoDB or CouchDB. There are a few settings that allow this, the most pertinent is: use_sqlalchemy:

base_config.use_sqlalchemy – Set to False to turn off sqlalchemy support

TurboGears takes advantage of repoze’s transaction manager software. Basically, the transaction manager wraps each of your controller methods, and should a method fail, the transaction will roll back. if you utilize the transaction manager, then the result of a successful method call results in a commit to the database. If the contoller method does not utilize the database, there is no database interaction performed. What this means is that you never have to worry about committing, or rolling back when controller code fails, TG handles this for you automatically.

base_config.use_transaction_manager – Set to False to turn off the Transaction Manager and handle transactions yourself.

AppConfig Method Overrides


Setup SQLAlchemy database engine.

The most common reason for modifying this method is to add multiple database support. To do this you might modify your app_cfg.py file in the following manner:

from tg.configuration import AppConfig, config
from myapp.model import init_model

# add this before base_config =
class MultiDBAppConfig(AppConfig):
    def setup_sqlalchemy(self):
        '''Setup SQLAlchemy database engine(s)'''
        from sqlalchemy import engine_from_config
        engine1 = engine_from_config(config, 'sqlalchemy.first.')
        engine2 = engine_from_config(config, 'sqlalchemy.second.')
        # engine1 should be assigned to sa_engine as well as your first engine's name
        config['tg.app_globals'].sa_engine = engine1
        config['tg.app_globals'].sa_engine_first = engine1
        config['tg.app_globals'].sa_engine_second = engine2
        # Pass the engines to init_model, to be able to introspect tables
        init_model(engine1, engine2)

#base_config = AppConfig()
base_config = MultiDBAppConfig()

This will pull the config settings from your .ini files to create the necessary engines for use within your application. Make sure you have a look at Using Multiple Databases In TurboGears for more information.


Set up the transaction management middleware.

To abort a transaction inside a TG2 app:

import transaction

By default http error responses also roll back transactions, but this behavior can be overridden by overriding base_config[‘tm.commit_veto’].