Python and sqlite3.ProgrammingError: Recursive use of cursors not allowed

Question

i wrote a python program like this that should run in multithreading mode:

def Func(host,cursor,db):

    cursor.execute('''SELECT If_index, Username, Version, Community, Ip_traff FROM HOST WHERE
    Hostname = ?''',(host,))

    #do something

#--- Main ---

db = sqlite3.connect(os.getcwd()+'\HOST', check_same_thread = False) #opendatabase       
cursor = db.cursor()                                                 #generate a cursor

for ii in range(len(host)):  #host is a list of ipaddress

    #for each host i want generate a thread
    thr = threading.Thread(target = Func, args=(host[ii],cursor,db) 
    thr.start()

i receive the sqlite3.ProgrammingError: Recursive use of cursors not allowed. How can i manage the recursive cursor for sqlite3 in this case? thanks a lot Paolo

Answer 1

Well, the thing is the sqlite3 module doesn't likes multithread cases, you can see that in the sqlite3 module's documentation

...the Python module disallows sharing connections and cursors between threads[1]

What I would do is to use some sort of synchronization in the Func function, for example, a threading.Lock[2]. Your Func will look like this:

# Define the lock globally
lock = threading.Lock()

def Func(host,cursor,db):
    try:
        lock.acquire(True)
        res = cursor.execute('''...''',(host,))
        # do something
    finally:
        lock.release()

The previous code will synchronize the execution of the cursor.execute by letting just one thread take the lock, the other threads will wait until it's released, when the thread with the lock is done, it releases the lock for the others to take it.

That should fix the problem.

[1] https://docs.python.org/2/library/sqlite3.html#multithreading

[2] https://docs.python.org/2/library/threading.html?highlight=threading#rlock-objects