Python and sqlite3.ProgrammingError: Recursive use of cursors is prohibited
I wrote a python program like this that should work 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 am getting sqlite3.ProgrammingError: Recursive use of cursors is not allowed. How can I control the recursive cursor for sqlite3 in this case? thank you very much Paolo
source to share
Well the thing is the sqlite3 module doesn't like multithreaded applications, you can see that in the sqlite3 module documentation
... the Python module disallows sharing of connections and cursors between threads [1]
What I would do is use some kind of synchronization in the Func function like 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 synchronizes the execution of the .execute cursor, allowing only one thread to block, the rest of the threads will wait until it is released, when the blocking thread is executed, it will release the lock for the rest to take it.
This 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
source to share