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

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 8 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) 8 years, 11 months ago  # | flag

OOPS. Sorry, I've just fixed it

Deepa 4 months, 4 weeks ago  # | flag

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

Add a comment

Sign in to comment