Using sql across multiple threads Python

Question

I have a server which allows (admins) to create,remove or edit other user's details, I have created the database alongside its cursors inside a function:

def DBSetup():
    global schoolDBConn, schoolDBCursor
    print("Setting up databases")
    schoolDBConn = sqlite3.connect("SCHOOL_DB.db")
    schoolDBCursor = schoolDBConn.cursor()
    schoolDBCursor.execute(
                            """
                               CREATE TABLE IF NOT EXISTS USER_DETAILS 
                               (
                               username text,
                               password text,
                               clearance int,
                               classes int
                               )
                            """
                            )
    schoolDBCursor.execute(   #line 300
                            """
                            CREATE TABLE IF NOT EXISTS CLASSES  
                            (
                            className text,
                            supervisor text,
                            assignmentName text
                            )
                            """
                            )
    schoolDBCursor.execute(
                            """
                            CREATE TABLE IF NOT EXISTS ASSIGNMENT 
                            (
                            setDate text,
                            dueDate text,
                            assignmentInfo text,
                            supervisor text
                            )  
                            """
                             )

I call this when the server starts up, and then I begin my thread:

if __name__ == "__main__":
    DBSetup()
    s = socket.socket(socket.AF_INET, socket.SOCK_STREAM)
    s.setsockopt(socket.SOL_SOCKET, socket.SO_REUSEADDR, 1)
    s.bind((host, port))
    s.listen(1)
    while True:
        conn, addr = s.accept()
        connThread = Thread(target=handler, args=(conn, addr))
        connThread.daemon = True
        connThread.start()

However inside my thread, whenever I use a function that edits the database, i get this error:

SQLITE3 ERROR:

SQLite objects created in a thread can only be used in that same thread.The object was created in thread id 9628 and this is thread id 12400

I am using global locks in my program "With global_lock:" whenever I edit a database.

Thank you in advance

Related question: https://stackoverflow.com/questions/10325683/can-i-read-and-write-to-a-sqlite-database-concurrently-from-multiple-connections — MaLiN2223, Oct 26 '18 at 21:58

CaptainPyPy · Accepted Answer · 2018-10-26T22:05:30.873

2

You could set the check_same_thread parameter to false. Like this in your case:

schoolDBConn = sqlite3.connect("SCHOOL_DB.db", check_same_thread=False)

From the docs:

By default, check_same_thread is True and only the creating thread may use the connection. If set False, the returned connection may be shared across multiple threads. When using multiple threads with the same connection writing operations should be serialized by the user to avoid data corruption.

edited Oct 26 '18 at 22:05

answered Oct 26 '18 at 21:58

CaptainPyPy

58
5

Thank you, seems to work for now, will test to see how it runs when I have multiple threads, hopefully the lock will do its job correctly – James Green Oct 26 '18 at 23:30

Using sql across multiple threads Python

1 Answers1