CODEWITHSUNDEEP
databasepython-3.xmultithreadingsqlite
aditya rawat
aditya rawat

Reputation: 164

How to share database over different threads in python

I am currently working on a project that requires to share the database over different threads in python. I am currently using sqlite3 with python3.7.

The code below is a problem similar to what I am having

import threading, os, sqlite3

os.chdir("/home/aditya/Desktop")

connection = sqlite3.connect("Name.db") # I am not using  :memory: because I need something permanent
cursor = connection.cursor()
cursor.execute("create table if not exists Name (names varchar)")
cursor.execute("insert into Name values('Some_Name')")
print("result from original database", end = "\t")
result = cursor.execute("select * from Name")
print(result.fetchall())

def return_cursor():
    conn = sqlite3.connect("Name.db")
    cur = conn.cursor()
    return cur


def DB_thread_one(): 

    print("result from thread 1", end = "\t")
    cur = return_cursor()
    result = cur.execute("select * from Name")
    print(result.fetchall()) # THIS RETURNS  AN EMPTY LIST

def DB_thread_two(): 

    print("result from thread 1", end = "\t")
    cur = return_cursor()
    result = cur.execute("select * from Name")
    print(result.fetchall()) # THIS RETURNS  AN EMPTY LIST

if __name__ == "__main__": 
    # creating thread 

    t1 = threading.Thread(target=DB_thread_one) 

    t2 = threading.Thread(target=DB_thread_two) 

    # starting thread 1 
    t1.start() 
    # starting thread 2 
    t2.start() 
    # wait until thread 1 is completely executed 
    t1.join() 
    # wait until thread 2 is completely executed 
    t2.join() 

    # both threads completely executed 
    print("Done!")

I googled some solutions for it and came across something called shared-cache and tried to enable it using cache = shared but it didn't work as sqlite3.connect does not have any parameter cache.

I also read that python discourages database connection sharing over different threads but this link says that SQLite now supports sharing database connectivity over threads.

What did I miss? Is there any better alternative for sqlite3 that allows database sharing over threads (sqlalchemy?)

Upvotes: 1

Views: 1168

Answers (1)

CL.
CL.

Reputation: 180270

When two threads share a connection, they also share the transaction. This is likely to break your program's logic.

Just use two connections, one per thread.

Upvotes: 1

Related Questions