如何在 Python 中附加内存中的 SQLite 数据库? [英] How can I attach an in-memory SQLite database in Python?

问题描述

我想合并 SQLite 数据库，有些可能在内存中.我通过将数据库路径指定为 :memory: 来创建内存数据库.在这篇文章之后，使用 SQLite 的 attach 功能似乎既简单又有效.但是如何将我的内存数据库指定为要附加的源?

例如，我想做这样的事情:

c1 = sqlite3.connect(":memory:")c1.execute(...创建表，插入一堆，提交...)c2 = sqlite3.connect(":memory:")c2.execute("""附加?AS ToMerge;开始；插入记录 SELECT * FROM ToMerge.Records;提交；""", (c1.get_attach_id(), ))

但是，当然，c1.get_attach_id() 是我为演示目的编写的方法，因为使用字符串 :memory: 会产生歧义.如何指定现有的 c1 数据库?

解决方案

连接到内存数据库的普通 :memory: 字符串不能从其他连接共享或附加.

您需要使用 file: URI 文件名连接字符串使用 ?cache=shared 参数能够在连接之间共享内存数据库；然后你也可以附加到它:

# 第一次连接c1 = sqlite3.connect("file::memory:?cache=shared", uri=True)# 第二个连接，到*同一个数据库*c2 = sqlite3.connect("file::memory:?cache=shared", uri=True)# 第三个连接，完全不同的数据库c3 = sqlite3.connect('/tmp/sqlite3.db', uri=True)# 可以附加到共享内存数据库，但前提是你使用了# uri=True 在原始连接上c3.execute("ATTACH DATABASE 'file::memory:?cache=shared' AS inmem")

请参阅内存数据库文档.

注意，这样的共享内存数据库只能一个；所有其他内存数据库必须对其连接保持私有.如果您需要更复杂的设置，请使用具有实际文件系统存储的数据库；如果您在 tempfile.mkdtemp() 临时目录 每个.

演示:

<预><代码>>>>导入 sqlite3>>>c1 = sqlite3.connect("file::memory:?cache=shared", uri=True)>>>c1.execute('CREATE TABLE foo (bar, baz)')<sqlite3.Cursor 对象在 0x106839490>>>>c1.execute("INSERT INTO foo VALUES ('spam', 'ham')")<sqlite3.Cursor 对象在 0x106839500>>>>c1.commit()>>>c2 = sqlite3.connect("file::memory:?cache=shared", uri=True)>>>list(c2.execute('SELECT * FROM foo'))[(你是垃圾邮件，你是垃圾邮件)]>>>c3 = sqlite3.connect('/tmp/sqlite3.db', uri=True)>>>c3.execute("ATTACH DATABASE 'file::memory:?cache=shared' AS inmem")<sqlite3.Cursor 对象在 0x1068395e0>>>>list(c3.execute('SELECT * FROM inmem.foo'))[(你是垃圾邮件，你是垃圾邮件)]

SQLite 版本 3.7.13 添加了对内存中共享缓存连接的支持；对于 Python，您可以使用 sqlite3.sqlite_version(字符串)或 sqlite3.sqlite_version_info(带整数的元组)检查底层库的版本:

<预><代码>>>>sqlite3.sqlite_version_info(3, 8, 10, 2)

I'd like to merge SQLite databases, and some may be in memory. I create the in-memory databases by specifying the database path as :memory:. Following this post, using the attach feature of SQLite seems like both a simple and efficient approach. But how can I specify my in-memory database as the source to attach?

For example, I'd want to do something like:

c1 = sqlite3.connect(":memory:")
c1.execute(...create table, insert a bunch, commit...)

c2 = sqlite3.connect(":memory:")
c2.execute("""
  ATTACH ? AS ToMerge;
  BEGIN; 
    INSERT INTO Records SELECT * FROM ToMerge.Records; 
  COMMIT;
""", (c1.get_attach_id(), ))

but, of course, c1.get_attach_id() is a method I made up for demonstration purposes, since using the string :memory: would be ambiguous. How can I specify the existing c1 database?

解决方案

A plain :memory: string connecting to an in-memory database cannot be shared or attached to from other connections.

You need to use a file: URI filename connection string with a ?cache=shared parameter to be able to share the in-memory database between connections; then you can also attach to it:

# first connection
c1 = sqlite3.connect("file::memory:?cache=shared", uri=True)

# second connection, to the *same database*
c2 = sqlite3.connect("file::memory:?cache=shared", uri=True)

# third connection, to a different database altogether
c3 = sqlite3.connect('/tmp/sqlite3.db', uri=True)
# can attach to the shared in-memory database, but only if you used
# uri=True on the original connection
c3.execute("ATTACH DATABASE 'file::memory:?cache=shared' AS inmem")

See the In-Memory Databases documentation.

Note that there can only be one such shared in-memory database; all other in-memory databases must remain private to their connection. Use databases with an actual filesystem storage if you need more complex setups; these are easy enough to clean up afterwards anyway if you create these in a tempfile.mkdtemp() temporary directory each.

Demo:

>>> import sqlite3
>>> c1 = sqlite3.connect("file::memory:?cache=shared", uri=True)
>>> c1.execute('CREATE TABLE foo (bar, baz)')
<sqlite3.Cursor object at 0x106839490>
>>> c1.execute("INSERT INTO foo VALUES ('spam', 'ham')")
<sqlite3.Cursor object at 0x106839500>
>>> c1.commit()
>>> c2 = sqlite3.connect("file::memory:?cache=shared", uri=True)
>>> list(c2.execute('SELECT * FROM foo'))
[(u'spam', u'ham')]
>>> c3 = sqlite3.connect('/tmp/sqlite3.db', uri=True)
>>> c3.execute("ATTACH DATABASE 'file::memory:?cache=shared' AS inmem")
<sqlite3.Cursor object at 0x1068395e0>
>>> list(c3.execute('SELECT * FROM inmem.foo'))
[(u'spam', u'ham')]

Support for in-memory shared-cache connections was added to SQLite version 3.7.13; for Python you can check the version of the underlying library with sqlite3.sqlite_version (string) or sqlite3.sqlite_version_info (tuple with integers):

>>> sqlite3.sqlite_version_info
(3, 8, 10, 2)

这篇关于如何在 Python 中附加内存中的 SQLite 数据库?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！