Accessing a database from a multithreaded application

[Alan Kemp]

Hi,

I have a problem that is half python, half design.  I have a
multithreaded network server working, each client request spawns a new
thread which deals with that client for as long as it is connected
(think ftp style rather than http style connections here).  Each thread
gets passed a reference to the main server to access things like the
list of connected clients, global data, etc.

Now I want to add a database to store usernames and other user data.  I
grabbed a copy of pysqlite2 which I have used successfully in the past
in single-threaded applications.  My initial plan was for the server to
create a single connection and cursor and then just use some sort of
mutex to control access to it from the various threads.  However,
apparently you can only access the cursor from the thread on which it
was created...

Can someone suggest a better (ie, valid) strategy for this?  Should I
be using a Queue to make a list of db requests/results to get accross
the thread boundary (erg, that sounds nasty)?  Should each client
thread create its own connection/cursor to the database?  Would that
even work, wont there be locking issues?

Any suggestions or pointers in the direction of more information would
be greatly appreciated.

Thanks for your time,

Alan