Should create_engine() be called per-process or per-request in threaded wsgi environment?

[Randy Syring]

I am developing a WSGI based web framework with sqlalchemy. I am
unclear about when create_engine() should be called. I initially
thought that engine creation and metadata would be initialized per
process and each thread/request would just get a new session.
However, I have recently run into error messages when using sqlite
with the framework in a threaded WSGI server:

"SQLite objects created in a thread can only be used in that same
thread..."

That lead me to this thread:

http://groups.google.com/group/pylons-discuss/browse_thread/thread/3d3009cd7421c45a/ed0f3dde401ff474?lnk=gst

Can someone weigh in on this issue? What are the performance
ramifications of needing to create an engine on each request as
opposed to each process? Do I also need to load my meta data on each
request or could I just re-bind the engine to the metadata on each
request? Should I not bind the engine to the metadata at all but just
bind it to the session?

Thanks.

[John Fries]

Yes, I would also like to know what is the appropriate way to use SQLAlchemy with respect to a WSGI server. I've been using Django with SQLAlchemy (not yet supported, but the recipe here http://lethain.com/entry/2008/jul/23/replacing-django-s-orm-with-sqlalchemy/ got me moving), and it's not clear how to do connection pools and all that other good stuff.

[Michael Bayer]

you should definitely create the engine and metadata on a per-process
basis. When using SQLite, the engine automatically chooses the
"SingletonThreadPool" connection pool, which will maintain a single
SQLite connection per application thread, which is never moved across
threads (unless you did so explicitly). The error you're getting
would only occur if you are sharing the connection returned by the
engine across threads, which can also occur if you're using a single
Session that's bound to a connection across threads. When using the
scoped_session() manager, this also should not occur - some
description of this lifecycle is at http://www.sqlalchemy.org/docs/05/session.html#unitofwork_contextual_lifespan
.

The scoped_session approach is widely used in conjunction with sqlite
in many web frameworks including Pylons, Turbogears and Zope so you'd
have to ensure that your specific approach is not sharing a single
Connection or Session between threads.

[Michael Bayer]

if you're using create_engine(), you're using a connection pool.  The diagram at http://www.sqlalchemy.org/docs/05/dbengine.html illustrates this.

[Randy Syring]

Thank you for taking the time to respond, I really appreciate it!

On Nov 6, 2:46 pm, Michael Bayer <mike...@zzzcomputing.com> wrote:

> you should definitely create the engine and metadata on a per-process  
> basis.   When using SQLite, the engine automatically chooses the  
> "SingletonThreadPool" connection pool, which will maintain a single  
> SQLite connection per application thread, which is never moved across  
> threads (unless you did so explicitly).  

Ah, well there is something I have overlooked. I have been forgetting
that there is a connection object since I never use it directly. I
was actually thinking that the engine was the connection, but I see
now that is not accurate. But would I need to close the connection
explicitly after each request?

> The error you're getting  
> would only occur if you are sharing the connection returned by the  
> engine across threads, which can also occur if you're using a single  
> Session that's bound to a connection across threads.  When using the  
> scoped_session() manager, this also should not occur - some  

> description of this lifecycle is athttp://www.sqlalchemy.org/docs/05/session.html#unitofwork_contextual_...
>   .

I do not believe that I am sharing the connection object across
threads, at least not deliberately. The only sqlalchemy objects I am
working with are an engine (which is stored at the process level), the
metadata (which is unbound and stored at the process level), and a
scoped session. At the end of each request, I call remove() on the
scoped session class, which I assumed was enough. Would there be
anything else I should do at the end of a request in order to "clean
up"?

Also, I am using Elixir. Is it possible that Elixir is holding on to
a connection object I don't know about? It uses scoped sessions by
default as well.

Thanks!

[Randy Syring]

Problem turns out to have been with my ISAPI WSGI interface, it looks
like it has a broken thread local model. More details here if anyone
is interested:

http://groups.google.com/group/sqlalchemy/browse_thread/thread/fbca1399020f6a2e