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

Why don't you give each thread its own cursor?

CL.
– CL.

2014-10-29 13:14:18 +00:00
Commented Oct 29, 2014 at 13:14 — CL.
– CL., Commented Oct 29, 2014 at 13:14

desertnaut · Accepted Answer · 2022-10-18 00:31:22Z

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

Demetrius7000 · Accepted Answer · 2023-02-02 19:44:18Z

3

One way is to fix it just block sqlite before writing

import sqlite3
con = sqlite3.connect(...)
...
with con:
    # Database is locked here
    cur = conn.cursor()
    res = cur.execute('''...''',(host,))

edited Feb 2, 2023 at 19:44

answered Feb 2, 2023 at 19:40

Demetrius7000

312 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related