pyqt4: multi-threaded database access

[Diez B. Roggisch]

Adrian Casey <caseyalice at adam.com.au> writes:

> I have a PyQt4 multi-threaded application which accesses many hosts
> concurrently via ssh.  I would like each thread to have access to a
> database so that it can look up details about the particular system it
> is connected to.
>
> The easy way is to have each thread create a connection to the database.
>  However, this is wasteful and likely to exhaust the maximum number of
> connections the database (postgresql) allows.  Instead, I would like to
> share a single database connection between all threads.  In PyQt4, this
> is not possible since the database connection can only be used by the
> thread which created it.
>
> So, I'm thinking I'll create a thread to just handle database queries.
> The worker threads will communicate with the database thread using
> signals and slots.
>
> My question is, if I have 20 worker threads who have emitted a signal
> which results in a database query, how do I ensure that the query
> results are sent back to the originating thread?
>
> Ideas, examples anyone?

First of all, do you *know* it will exhaust the connections, or is that
just some gut feeling? Because postgres can easily handle a few hundered
connections, and unless you really are exhausting these, it's the
easiest solution.

The next advice would be to create a pool of connections (pure python,
not Qt). Then you  lose the "can only be used by the thread which
created it" restriction. Of course you should only use a connection from
within one thread at a time, but that shouldn't be an issue.

So all you have to make sure is that you fetch & return the connections
properly. I would use a context-manager for that:


with connection_pooll as connection:
   do_some_queries(connection)


HTH,

Diez