1

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?)

1 Answer 1

1

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.

Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.