在python中多线程访问sqlite3数据库

2020年10月22日 / 16次阅读 / Last Modified 2020年10月22日
SQLite多线程

Python标准库中有sqlite3模块,可见对此数据库的认可。不过,此模块在使用时也有限制,同一个数据库连接,不能在不同线程中共享。

import threading
import sqlite3


sqlite_mutex = threading.Lock()
db = sqlite3.connect('tdb2')


def initdb():
    try:
        db.executescript("""
            create table stocks(id integer primary key, name text);
            """)
    except Exception as e:
        print(repr(e))


def insertdb(name):
    with sqlite_mutex:
        try:
            for i in range(100):
                db.execute('insert into stocks values (%d,%s)'%(i,name))
        except Exception as e:
            print(repr(e))


initdb()
ths = []

for i in range(10):
    th = threading.Thread(target=insertdb, args=(str(i),))
    ths.append(th)

for i in range(10):
    ths[i].start()

for i in range(10):
    ths[i].join()

print('done')

以上这段代码是错的!

  • 主线程的数据库连接db,在其它线程中直接使用;
  • 没有对数据库做 commit 和 close,默认是deferred模式,不是autocommit。
  • 重复运行这段代码,会有table已经存在,和插入重复数据违反constraint的错误。

这代码运行起来,会有如下错误提示:

$ python3 test_sqlite3.py
OperationalError('table stocks already exists')
ProgrammingError('SQLite objects created in a thread can only be used in that same thread. The object was created in thread id 140202847707776 and this is thread id 140202834728704.')
ProgrammingError('SQLite objects created in a thread can only be used in that same thread. The object was created in thread id 140202847707776 and this is thread id 140202826336000.')
ProgrammingError('SQLite objects created in a thread can only be used in that same thread. The object was created in thread id 140202847707776 and this is thread id 140202817832704.')
ProgrammingError('SQLite objects created in a thread can only be used in that same thread. The object was created in thread id 140202847707776 and this is thread id 140202809440000.')
ProgrammingError('SQLite objects created in a thread can only be used in that same thread. The object was created in thread id 140202847707776 and this is thread id 140202817832704.')
ProgrammingError('SQLite objects created in a thread can only be used in that same thread. The object was created in thread id 140202847707776 and this is thread id 140202809440000.')
ProgrammingError('SQLite objects created in a thread can only be used in that same thread. The object was created in thread id 140202847707776 and this is thread id 140202817832704.')
ProgrammingError('SQLite objects created in a thread can only be used in that same thread. The object was created in thread id 140202847707776 and this is thread id 140202809440000.')
ProgrammingError('SQLite objects created in a thread can only be used in that same thread. The object was created in thread id 140202847707776 and this is thread id 140202817832704.')
ProgrammingError('SQLite objects created in a thread can only be used in that same thread. The object was created in thread id 140202847707776 and this is thread id 140202809440000.')
done

官方文档:Multithreading Older SQLite versions had issues with sharing connections between threads. That’s why the Python module disallows sharing connections and cursors between threads. If you still try to do so, you will get an exception at runtime. The only exception is calling the interrupt() method, which only makes sense to call from a different thread.

正确的做法,是需要在每一个不同的线程中,创建自己的数据库连接并使用!示例代码如下:

import threading
import sqlite3


sqlite_mutex = threading.Lock()


def initdb():
    db = sqlite3.connect('tdb2')
    try:
        db.executescript("""
            create table if not exists stocks(id integer, name text);
            """)
    except Exception as e:
        print(repr(e))
    db.commit()
    db.close()


def insertdb(name):
    with sqlite_mutex:
        db = sqlite3.connect('tdb2')
        try:
            for i in range(100):
                db.execute('insert into stocks values (%d,%s)'%(i,name))
        except Exception as e:
            print(repr(e))
        db.commit()
        db.close()


initdb()
ths = []

for i in range(10):
    th = threading.Thread(target=insertdb, args=(str(i),))
    ths.append(th)

for i in range(10):
    ths[i].start()

for i in range(10):
    ths[i].join()

print('done')

这样,通过在各自的线程中创建自己的数据库连接对象,并且通过mutex实现互斥访问,即可多线程访问sqlite数据库。

-- EOF --

本文链接:https://www.pynote.net/archives/2606

留言区

《在python中多线程访问sqlite3数据库》有1条留言

电子邮件地址不会被公开。 必填项已用*标注

  • 麦新杰

    这有点像TCP的短连接,连接,干活,断开,一气呵成... [回复]


前一篇:
后一篇:

More


©Copyright 麦新杰 Since 2019 Python笔记

go to top