共享：内存：使用sqlite3包在python中的不同线程之间的数据库

I would like to create a :memory: database in python and access it from different threads. Essentially something like:

我想在python中创建一个：memory：database，并从不同的线程访问它。基本上是这样的：

class T(threading.Thread):
    def run(self):
        self.conn = sqlite3.connect(':memory:')
        # do stuff with the database

for i in xrange(N):
    T().start()

and have all the connections referring to the same database.

并且所有连接都指向同一个数据库。

I am aware of passing check_same_thread=True to the connect function and sharing the connection between threads but would like to avoid doing that if possible. Thanks for any help.

我知道将check_same_thread = True传递给connect函数并共享线程之间的连接，但是如果可能的话，我们希望避免这样做。谢谢你的帮助。

EDIT: corrected a typo. I originally said "have all the connections referring to the same thread" substituting thread for database.

编辑：纠正错字。我最初说“将所有连接引用到同一个线程”将线程替换为数据库。

2 个解决方案

#1

Without hacking sqlite3 library itself you cannot reuse :memory: database, because it's guaranteed to be exclusive and private for every connection. To hack access to it, look closer at src/pager.c in sqlite3 distribution (not Python module distribution). Maybe, most convenient way to implement this would be make :memory:00, :memory:something, :memory:okay_hai etc. aliases to address different pPager->memDb pointers through some simple C-side mapping.

没有黑客攻击sqlite3库本身就无法重用：内存：数据库，因为它保证每个连接都是独占的和私有的。要破解对它的访问，请仔细查看sqlite3发行版中的src / pager.c（而不是Python模块分发）。也许，实现这个的最方便的方法是make：memory：00，：memory：something，：memory：okay_hai等别名通过一些简单的C端映射来处理不同的pPager-> memDb指针。

#2

SQLite had improved over last 4 years, so now shared in-memory databases are possible. Check the following code:

SQLite在过去4年中有所改进，因此现在可以共享内存数据库。检查以下代码：

import sqlite3

foobar_uri = 'file:foobar_database?mode=memory&cache=shared'
not_really_foobar_uri = 'file:not_really_foobar?mode=memory&cache=shared'

# connect to databases in no particular order
db2 = sqlite3.connect(foobar_uri, uri=True)
db_lol = sqlite3.connect(not_really_foobar_uri, uri=True)
db1 = sqlite3.connect(foobar_uri, uri=True)

# create cursor as db2
cur2 = db2.cursor()

# create table as db2
db2.execute('CREATE TABLE foo (NUMBER bar)')

# insert values as db1
db1.execute('INSERT INTO foo VALUES (42)')
db1.commit()

# and fetch them from db2 through cur2
cur2.execute('SELECT * FROM foo')
print(cur2.fetchone()[0])  # 42

# test that db_lol is not shared with db1 and db2
try:
    db_lol.cursor().execute('SELECT * FROM foo')
except sqlite3.OperationalError as exc:
    print(exc)  # just as expected

Database accesses are entangled intentionally, to show that two connections to the in-memory database with the same name are the same from SQLite's point of view.

故意纠缠数据库访问，以显示从SQLite的角度来看，具有相同名称的内存数据库的两个连接是相同的。

References:

参考文献：

SQLite URIs
SQLite URI
SQLite shared cache
SQLite共享缓存

Unfortunately, connection by URI is only available since Python 3.4. However, if you have Python 2.6 or later (but not Python 3), builtin sqlite3 module is still capable of importing APSW connections, which can be used to achieve same effect. Here goes the drop-in sqlite3 module replacement:

不幸的是，URI的连接仅在Python 3.4之后可用。但是，如果你有Python 2.6或更高版本（但不是Python 3），内置sqlite3模块仍然能够导入APSW连接，这可以用来实现相同的效果。这里是插件sqlite3模块替换：

from sqlite3 import *
from sqlite3 import connect as _connect
from apsw import Connection as _ApswConnection
from apsw import SQLITE_OPEN_READWRITE as _SQLITE_OPEN_READWRITE
from apsw import SQLITE_OPEN_CREATE as _SQLITE_OPEN_CREATE
from apsw import SQLITE_OPEN_URI as _SQLITE_OPEN_URI

# APSW and pysqlite use different instances of sqlite3 library, so initializing
# APSW won't help pysqlite. Because pysqlite does not expose any way to
# explicitly call sqlite3_initialize(), here goes an ugly hack. This only has
# to be done once per process.
_connect(':memory:').close()

def connect(database, timeout=5.0, detect_types=0, isolation_level=None,
            check_same_thread=True, factory=Connection, cached_statements=100,
            uri=False):
    flags = _SQLITE_OPEN_READWRITE | _SQLITE_OPEN_CREATE

    if uri:
        flags |= _SQLITE_OPEN_URI

    db = _ApswConnection(database, flags, None, cached_statements)
    conn = _connect(db, timeout, detect_types, isolation_level, 
                    check_same_thread, factory, cached_statements)

    return conn

#1

#2