如何在python中实现跨线程共享数据库

Question

我目前正在做一个项目，需要在python中通过不同的线程共享数据库。我目前使用的是带有python3.7的sqlite3。

下面的代码是一个类似于我遇到的问题

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!") 

我在谷歌上搜索了一些解决方案，发现了一个名为shared-cache的东西，并试图使用cache = shared启用它，但它不起作用，因为sqlite3.connect没有任何参数cache。

我还读到SQLite不鼓励跨不同线程共享数据库连接，但是this link说python现在支持跨线程共享数据库连接。

我错过了什么？有没有更好的sqlite3替代方案，允许通过线程共享数据库(sqlalchemy?)

Answer 1

当两个线程共享一个连接时，它们也共享事务。这可能会破坏程序的逻辑。

只需使用两个连接，每个线程一个。