Re: Default way of using SQLAlchemy with Pylons - Kumar McMillan - com.googlegroups.pylons-discuss

11 messages in com.googlegroups.pylons-discussRe: Default way of using SQLAlchemy w...

From	Sent On	Attachments
Karlo Lozovina	20 May 2008 02:18
Karlo Lozovina	20 May 2008 02:22
Luis Bruno	20 May 2008 02:39
Kumar McMillan	20 May 2008 09:20
Luis Bruno	20 May 2008 09:32
Kumar McMillan	20 May 2008 09:45
Luis Bruno	20 May 2008 10:46
Mike Orr	20 May 2008 12:06
Luis Bruno	20 May 2008 12:41
Mike Orr	20 May 2008 14:28
Kumar McMillan	20 May 2008 15:43

Subject:	Re: Default way of using SQLAlchemy with Pylons
From:	Kumar McMillan (kuma...@gmail.com)
Date:	05/20/2008 03:43:36 PM
List:	com.googlegroups.pylons-discuss

On Tue, May 20, 2008 at 2:07 PM, Mike Orr <slug...@gmail.com> wrote:

engine = engine_from_config(config, 'sqlalchemy.') init_model(engine)

This is done in environment.py, as shown in "The Engine" section of the article. It is not normally done in the base controller.

otherwise I get errors like:

<class 'sqlalchemy.exceptions.ProgrammingError'>: (ProgrammingError) SQLite objects created in a thread can only be used in that same thread.The object was created in thread id -1224172656 and this is thread id -1249350768 None [{}]

SQLite does use one connection per thread, while other engines may give out multiple connections in one thread. You shouldn't have to use the "threadlocal" engine strategy; it's built into the SQLite engine.

But I don't know why it would use the wrong connnection in a thread. Nobody else has reported this problem. When exactly does this occur, and what kinds of queries does your application contain?

I see the same kind of error when using postgres. As I understand it, strategy='threadlocal' just allows you to share the connection per *thread*, like you mentioned. However, the problem is that in mod_wsgi when you are running a multi-threaded server your application object is actually passed to each new thread, which then does application(environ, start_response). As far as I can tell, load_environment() is not run in each thread, only once when the server starts up. To illustrate it, here is what my app.wsgi file looks like:

import os from paste.deploy import loadapp import paste.script.appinstall config = os.environ['PYLONS_CONFIG_PATH'] cmd = paste.script.appinstall.SetupCommand('setup-app') cmd.run([config])

application = loadapp('config:' + config)

The fix is to create and bind a new engine object per __call__ in BaseController and I suppose the thread of this email is whether or not that's the right thing to do. Seems ok to me. You wouldn't want the same engine object to be shared across each thread anyway since simultaneous things would be happening in a heavily loaded site. A secondary topic here might be whether or not the sqlalchemy docs should be updated for this scenario. I'm not sure that a multi-threaded mod_wsgi server setup is in the scope of that article or not. I suppose it should be documented somewhere ;)

hmm, I just found this: http://code.google.com/p/modwsgi/wiki/IntegrationWithPylons ... and noticed the suggested app.wsgi script does not explicitly call 'setup-app' like mine does. I have no idea where load_environment() would be called in that case then.