[mod_python] mod_python and SQLAlchemy don't mix?

On 09/06/07, Gambit <gambit at alpenjodel.de> wrote:
> Hi,
>
> 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

Which mailing list did this come from as the statement is wrong? This
myth is one I have been trying to stamp out as it just causes
misunderstandings of how mod_python works and lead people to think it
is crap.

When concurrent requests are handled by one Apache child process they
are done so in different threads, but all those threads execute within
the context of the same Python interpreter instance and use the same
modules and data.

Read:

  http://www.dscpl.com.au/wiki/ModPython/Articles/TheProcessInterpreterModel

for further details.

Beyond that, I am not familiar with SQLAlchemy and whether it is
itself actually thread safe, maybe others can comment.

One thing I would ask though is what version of mod_python are you
using? If you aren't using mod_python 3.3.1 I would suggest you
upgrade. If you are using mod_python 3.1.X or older, then be aware
there are multithreading bugs in it which conceivably could cause you
problems.

BTW, define what you mean by stability problems. One of the problems
with these database adapters is that the caching they do can cause
problems in a multiprocess web server like Apache as they cache data
in the processes and don't necessarily reflect quickly changes commit
by an alternate process.

Graham

> > 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):
>      pass
>
> sa.orm.clear_mappers()
> 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'])
>       try:
>           user = query.get_by(Uid=requested_uid)
>       except sa.exceptions.SQLError, details:
>           logging.debug("got this error: %s" % details)
>           dbsession.close()
>           return "Yet another crash"
>       else:
>           dbsession.close()
>           return show_message(req, "Looks good")
>
> _______________________________________________
> Mod_python mailing list
> Mod_python at modpython.org
> http://mailman.modpython.org/mailman/listinfo/mod_python
>