cherrypy, sqlite & threading

[daniels]

I'm working on an app that will gather data through http from several
places, cache the data locally and then serve it through http.
So I was looking at the following. My app will first create several
threads that will gather data at a specified interval and cache that
data locally into a sqlite database.
Then in the main thread start a CherryPy app that will query that
sqlite db and serve the data.

My problem is how do i handle connections to sqlite db from my threads
and from the cherrypy app?
If I'd do a connection per thread to db will I also be able to create/
use in memory db?

[Robert Brewer]

"Multiple connections to ":memory:" within a single process create a
fresh database each time." [1] So you'll either have to 1) share a
single connection to a sqlite :memory: db among all threads (almost
certainly with a coarse mutex), 2) use a file-based sqlite db, or 3) use
something besides sqlite.

Robert Brewer
fuma...@aminus.org

[1] http://www.sqlite.org/cvstrac/wiki?p=InMemoryDatabase

[daniels]

Thank you for the quick answer.

> fuman...@aminus.org
>
> [1]http://www.sqlite.org/cvstrac/wiki?p=InMemoryDatabase

[sol]

I setup a separate thread in my cherrypy + sqlite app that only
handles the DB actions. I have a python Queue (thread safe) that
passes messages from cherrypy threads to the db thread and another for
the other direction.

For low activity sites I think this works fine (does for me) but the
serialization that the Queue use results in would likely / definitely
start falling apart for high activity sites.

[Eric Larson]

On Sun, Feb 8, 2009 at 6:51 AM, sol <s...@sr-research.com> wrote:
>
> I setup a separate thread in my cherrypy + sqlite app that only
> handles the DB actions. I have a python Queue (thread safe) that
> passes messages from cherrypy threads to the db thread and another for
> the other direction.
>
> For low activity sites I think this works fine (does for me) but the
> serialization that the Queue use results in would likely / definitely
> start falling apart for high activity sites.
>
> On Feb 7, 7:15 pm, daniels <daniels0...@gmail.com> wrote:
>> Thank you for the quick answer.
>>
>> On Feb 8, 1:31 am, "Robert Brewer" <fuman...@aminus.org> wrote:
>>
>> > daniels wrote:
>> > > I'm working on an app that will gather data through http from several
>> > > places, cache the data locally and then serve it through http.
>> > > So I was looking at the following. My app will first create several
>> > > threads that will gather data at a specified interval and cache that
>> > > data locally into a sqlite database.
>> > > Then in the main thread start a CherryPy app that will query that
>> > > sqlite db and serve the data.
>>
>> > > My problem is how do i handle connections to sqlite db from my threads
>> > > and from the cherrypy app?
>> > > If I'd do a connection per thread to db will I also be able to create/
>> > > use in memory db?
>>

I do something similar in that I have a sqlite db for reading data and
use an external process for writing. In my case I only have on
process/thread writing to the db at a time, so I don't really have any
locking issues as the only other process/thread is limited to reading.

The idea that that the writing threads write to a Queue and then only
allow one thread to write makes the most sense to me for how to make
sure your writes are going to occur correctly.

--

Eric Larson

[dasacc22]

Hi

I dont have a strong understanding of the subject, but from what I do
understand, I make use of sqlalchemy and it provides a scoped_session
object that handles creating and destroying threadlocal sessions. So
any thread can call the same Session object and it takes care of the
rest, you just have to make sure your transactions dont hang or
anything of the sort.

--
Daniel