Saturday, October 28, 2006

Workarounds for SQLAlchemy and Turbogears 1.0b

I've started work on a new TurboGears project to track merge requests for Bazaar. I was already familiar with earlier versions of TurboGears, but I decided to upgrade to 1.0b, and try out this new SQLAlchemy thing that everyone's raving about. It's supposed to be the official object-relational-mapper in TurboGears 1.1.

You have the option of using it in 1.0b, but the integration is not smooth.

Although you can use the identity framework with SQLAlchemy, the default that TurboGears provides is broken. TurboGears uses the ActiveMapper extension, but the default defines relationship between groups and users, and between groups and permissions in two places. Even though the definitions are equivalent, this is not allowed, and it fails silently.

So to use groups/permissions effectively, comment out the two many_to_many lines in Group. You'll still get users and properties attributes on Group, but they'll be created by the 'backref' parameters on the many_to_many statements in User and Property.

I've learned to like unit-testing. But the Turbogears TestCase object does not handle SQLAlchemy objects. So I wrote my own:

class TestMessages(TestCase):

def iter_active_mappers(self):
for key, item in model.__dict__.items():
if issubclass(item, ActiveMapper):
if item is not ActiveMapper:
yield item
except TypeError:

def setUp(self):
for active_mapper in self.iter_active_mappers():

def tearDown(self):
for active_mapper in self.iter_active_mappers():

Finally, I also had to force it to provide my own database configuration:
config.update({'sqlalchemy.dburi': 'sqlite:///:memory:'})

The tg-admin tool has quite limited support for SQLAlchemy: it only does sql create. I've done sql drop quite a lot in past projects. I could adapt the tearDown method above, but I'm currently using SQLite, so rm devdata is an adequate subsitute.

I hope these tips are helpful.

SQLAlchemy emits reams and reams of SQL kipple; To disable that, place sqlalchemy.echo = False in your config file.

It's great that TurboGears is so clearly committed to delivering best-of-breed components that they'll switch ORMs and templating libraries for their 1.1 release. But 1.1 isn't here yet, and while you can integrate SQLAlchemy and TurboGears today, there's a certain amount of pain involved.