Threading issue with SQLite

[Stephen Hansen]

On Fri, Jan 29, 2010 at 8:37 AM, Alan Harris-Reid <
aharrisreid at googlemail.com> wrote:

> Hi,
>
> I am creating a web application (using Python 3.1 and CherryPy 3.2) where a
> SQLite connection and cursor object are created using the following code
> (simplified from the original):
>
> class MainSite:
>   con = sqlite.connect('MyDatabase.db')
>   cursor = con.cursor()
>

This is bad. For one thing, every thread should have its own connection at
least-- you can store it in thread-local storage (see the threading module)
for re-use if you want, if cherrypy does thread pooling techniques or some
such (I'd be sorta surprised if it didn't).

For another thing, cursors are NOT meant to be long-running interfaces.  You
should not leave one open beyond one concrete action (e.g., one user
request, with however many SQL statements it takes to accomplish that work)
even if you are re-using the same connection across multiple
methods/invocations.

Questions...
> 1.  Is there a large overhead in opening a new SQLite connection for each
> thread (ie. within each method)?
>

You're making a web-app in Python, man. :) The overhead of a SQLite
connection is not exactly significant at that point :) Though you could use
thread pooling techniques with each thread having its own dedicated
connection to mitigate what overhead there is.


> 2.  Is there any way to use the same connection for the whole class (or
> should I forget that idea completely?)
>

You shouldn't think of "connection" and "class" as having any kind of
meaningful link, it just doesn't make sense. It won't work and what you're
-trying- to do doesn't actually even make sense. Besides the fact that
you're worrying prematurely about overhead and trying to optimize it away,
connections (and from them, cursors) to databases are things that are used
for binding together a bunch of statements into transactions and maintaining
'current state' of your interaction with the database: its a a logical
abstraction of "what I'm doing Now to the database", whereas the controller
class is, "what can be done."

3.  When a method returns to the calling method, is the connection
> automatically closed (assuming the object is local, of course) or does it
> have to be done explicitly using connection.close()?
>

If it goes out of scope, it will be closed if its a local variable (be sure
you commit if you need to first!). Assuming you aren't caching/saving the
connection in the thread for future calls it handles, if you have a thread
pool doing your work (again, I'd be -really- surprised if CherryPy with
threads didn't involve thread pools as opposed to starting up and stopping
threads continually as requests come in).

HTH,

--S


>
> TIA,
> Alan Harris-Reid
>
>
> --
> http://mail.python.org/mailman/listinfo/python-list
>


--S
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mail.python.org/pipermail/python-list/attachments/20100129/12be4f79/attachment-0001.html>