I’m sorry, I may have NIH

May 2, 2007

So, like I said in my previous post, SQLAlchemy rocks. However, it requires a bit of hoop-jumping to sync its transactions with Zope (2) transactions, and manage the creation of engines and connections. There are at least two SQLAlchemy/Zope 2 libraries which I know about:

Provides transaction integration, as well as some abstractions for creating SQLAlchemy mappers. Mapper classes can be generated, and rely on particular base classes to provide convenience functionality
Provides transactions integration as part of a larger suite of tools centered around briding SQLAlchemy mappers and Zope 3 interfaces, supporting among other things zope.formlib forms

Of these, ore.alchemist is quite intriguing, but way more than I need right now. I was using z3c.alchemist, but it seems Andreas wants it to go in a slightly different direction than I am going: The two nails in the coffin for me were the requirement to use special base classes (which of course will provide additional convenience APIs, but I have a need to let SQLAlchemy map exisiting classes which are used elsewhere in my application, and for which I do not want explicit additional dependencies) and the lack of support for intermixing SQLAlchemy ORM sessions with lower-level connections for executing raw SQL or Python-constructed SQL. This means that you can’t use a session to save some values and then expect those rows to be returned if you subsequently perform a “raw” query.

Thus is born Lead, aka collective.lead (as opposed to gold, geddit?). You can find it in the Cheese Shop or Collective subversion repository.

Lead aims to support two things, and two things only:

  • Linking SQL transactions to Zope transactions, so that there is one transaction and one connection (only opened if necessary, of course) per request and SQL commit/rollback is synced with Zope transaction commit/abort
  • Making databases available as named utilities (so you can configure several databases simultaneously, e.g. from different products), instantiated in such a way that it’s possible to read connection parameters from the ZODB or otherwise determine them at runtime (in my case, with a Plone control panel form to change them)

Here is how it may look:

 class TableOne(object):

 class TableTwo(object):

Those are two “domain” classes representing database entities. We will map these to the database in a moment. Of course, you would probably have a few properties explicitly defined, and/or an interface specified.

 from collective.lead import Database
 import sqlalchemy as sa 

 class MyDatabase(Database):
     def _url(self):
         return sa.engine.url.URL(drivername='mysql', username='user',
                    host='localhost',  database='testdb')

     def _setup_tables(self, metadata, tables):
         tables['table1'] = sa.Table('table1', metadata, autoload=True)
         tables['table2'] = sa.Table('table2', metadata, autoload=True)
     def _setup_mappers(self, tables, mappers):
         mappers['table1'] = sa.mapper(TableOne, tables['table1'])
         mappers['table2'] = sa.mapper(TableTwo, tables['table2'],
                                         properties = {
                                             'table1' : sa.relation(TableOne),

This is how you set up a new database connection. The _url property needs to return an SQLAlchemy URL, which specifies which database to connect to. I fetch this from a local utility which saves connection parameters in the ZODB.

The SQLAlchemy engine will be set up on-demand (basically, the first time someone fetches a session or a connection), using the ‘threadlocal’ strategy. When this happens, _setup_tables() and _setup_mappers() are called. This is where you encode your database schema and ORM mappings. They only get called once per database utility, but then again, your database schema is unlikely to change at runtime. 🙂

You would then register this class as a factory for a utility:


The IDatabase interface (which is implemented by the base class collective.lead.Database) has two important attributes – session and connection. These are just an SQLAlchemy Session and a Connection, but they are tied to the threadlocal connection and transaction managed by Lead.

Here’s how you might use them:

 from zope.component import getUtility
 from collective.lead import IDatabase

 db = getUtility(IDatabase, name='my.database')


 db.connection.execute("SELECT * FROM table1")

Hopefully that didn’t look so hard. I’ve released 1.0b1 to the cheesehop, which works for me… you should be able to depend on it – at least, I will be. 🙂


2 Responses to “I’m sorry, I may have NIH”

  1. […] Martin Aspeli: I’m sorry, I may have NIH […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: