Welcome, guest | Sign In | My Account | Store | Cart

Workaround for the SQLite limitation that prevents multiple threads from sharing a Connection object.

Python, 64 lines
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
from threading import Thread
from Queue import Queue

import apsw

class SingleThreadOnly(object):
    def __init__(self, db):
        self.cnx = apsw.Connection(db) 
        self.cursor = self.cnx.cursor()
    def execute(self, req, arg=None):
        self.cursor.execute(req, arg or tuple())
    def select(self, req, arg=None):
        self.execute(req, arg)
        for raw in self.cursor:
            yield raw
    def close(self):
        self.cnx.close()

class MultiThreadOK(Thread):
    def __init__(self, db):
        super(MultiThreadOK, self).__init__()
        self.db=db
        self.reqs=Queue()
        self.start()
    def run(self):
        cnx = apsw.Connection(self.db) 
        cursor = cnx.cursor()
        while True:
            req, arg, res = self.reqs.get()
            if req=='--close--': break
            cursor.execute(req, arg)
            if res:
                for rec in cursor:
                    res.put(rec)
                res.put('--no more--')
        cnx.close()
    def execute(self, req, arg=None, res=None):
        self.reqs.put((req, arg or tuple(), res))
    def select(self, req, arg=None):
        res=Queue()
        self.execute(req, arg, res)
        while True:
            rec=res.get()
            if rec=='--no more--': break
            yield rec
    def close(self):
        self.execute('--close--')

if __name__=='__main__':

    db='people.db'
    multithread=True
    
    if multithread:
        sql=MultiThreadOK(db)
    else:
        sql=SingleThreadOnly(db)

    sql.execute("create table people(name,first)")
    sql.execute("insert into people values('VAN ROSSUM','Guido')")
    sql.execute("insert into people values(?,?)", ('TORVALDS','Linus'))
    for f, n in sql.select("select first, name from people"):
        print f, n
    sql.close()

This recipe is inspired by recipe 496799 from Wim SCHUT. I'm used to use a simple adapter, like SingleThreadOnly above, to access SQLite databases, but when I needed it in a multi-threaded application, a server, well, it crashed. The class MultiThreadOK above provides a multi-threaded enabled access to SQLite databases. It can be used as a drop in replacement for SingleThreadOnly. This recipe uses the ASPW driver but could easily use another one.

3 comments

dinesh vadhia 15 years, 11 months ago  # | flag

Traceback errors. Exception in thread Thread-1: Traceback (most recent call last): File "C:\Python25\lib\threading.py", line 486, in __bootstrap_inner self.run() File "acthread.py", line 56, in run cursor = self.cnx.cursor() AttributeError: 'MultiThreadOK' object has no attribute 'cnx'

Traceback (most recent call last): File "C:\Python25\lib\threading.py", line 462, in __bootstrap self.__bootstrap_inner() File "C:\Python25\lib\threading.py", line 537, in __bootstrap_inner _active_limbo_lock.release() apsw.ConnectionNotClosedError: apsw.Connection on "C:/.../test.db" at address 0x00BED7B0, allocated at acthread.py:55. The destructor has been called, but you haven't closed the connection. All connections must be explicitly closed. The SQLite database object is being leaked.

Louis RIVIERE (author) 15 years, 11 months ago  # | flag

OOPS. Sorry, I've just fixed it

Deepa 7 years, 5 months ago  # | flag

Could you please note how to perform executemany function so that bulk data can be inserted at a time using MultiThreadOK class