[mod_python] mod_python and SQLAlchemy don't mix?

Gambit gambit at alpenjodel.de
Fri Jun 8 16:25:18 EDT 2007


I'm trying to write a fairly simple web app using mod_python and 
SQLAlchemy and I'm getting stability problems with the database which 
apparently are threading issues.

Basically what I do is to create a SQLAlchemy engine in a separate 
module that is imported once at the beggining of my app:

db = sa.create_engine('mysql://user:pass@localhost/mydb')

And then I use this db variable freely in my code. This leads to errors 
when multiple users are using the app.

The people at the SQLAlchemy mailing list didn't saw a problem with my 
approach and suggested this is a mod_python/mysql issue.

Browsing through the archives of this list I found some possible 
explanations in an earlier discussion:

> multiple threads in apache get multiple copies of mod_python, each with
> it's own copy of the imported database module in memory and each module
> maintaining a different database connection

> The process which apache chooses to to handle a request is random.

> Don't be fooled into thinking that module-level attributes are global
> across your application as they are not. In anything other than winnt-mpm
> (which is a single, threaded process), you will have multiple, independent
> copies of that variable. 

So... I conclude I have multiple, independent SQLAlchemy engines created 
and thus I cannot just use the db object I created freely in my app.

Does anyone knows how to solve this? Is someone here using mod_python 
and SQLAlchemy together successfully? Care to share some best practices?

Thanks in advance

Some sample code follows

In dbinit.py:

db = sa.create_engine('mysql://user:pass@localhost/mydb')
metadata = sa.BoundMetaData(db)
users_table = sa.Table('Users', metadata, autoload=True)

class User(object):

sa.mapper(User, users_table)

In myapp.py

import logging
import sqlalchemy as sa
from dbinit import *

def justatest(req):
      dbsession = sa.create_session(bind_to=db)
      query = dbsession.query(User)
      requested_uid = sanitize(req.form['uid'])
          user = query.get_by(Uid=requested_uid)
      except sa.exceptions.SQLError, details:
          logging.debug("got this error: %s" % details)
          return "Yet another crash"
          return show_message(req, "Looks good")

More information about the Mod_python mailing list