logo标签列表

如何在Python sqlite3中将现有的数据库文件加载到内存中？

pythonperformancesqlite
78

我有一个现成的sqlite3数据库文件，需要进行一些复杂的计算。直接从文件中进行计算速度非常慢，而且由于文件不大（~10 MB），所以将其加载到内存中应该没有问题。

是否有一种Pythonic的方法可以将现有文件加载到内存中以加快计算速度？

10个回答
129

这里是我为我的Flask应用编写的片段：

import sqlite3
from io import StringIO
    
def init_sqlite_db(app):
    # Read database to tempfile
    con = sqlite3.connect(app.config['SQLITE_DATABASE'])
    tempfile = StringIO()
    for line in con.iterdump():
        tempfile.write('%s\n' % line)
    con.close()
    tempfile.seek(0)

    # Create a database in memory and import from tempfile
    app.sqlite = sqlite3.connect(":memory:")
    app.sqlite.cursor().executescript(tempfile.read())
    app.sqlite.commit()
    app.sqlite.row_factory = sqlite3.Row
1
对于Python 3：from io import StringIO - Emre
63

那么 sqlite3.Connection.backup(...) 呢？“该方法可在其他客户端或同一连接并发访问 SQLite 数据库时备份 SQLite 数据库。” 可用性：SQLite 3.6.11 或更高版本。自版本 3.7 起新增。

import sqlite3

source = sqlite3.connect('existing_db.db')
dest = sqlite3.connect(':memory:')
source.backup(dest)
5
如果您有Python 3.7+，这似乎是最佳方法。 - roskakori
2
@roskakori 我也这么认为 ;) - thinwybk
如果我这样做，我假设我在所有查询中使用dest。我的问题是，它是否会自动同步回源文件？当提交到dest时，它是否会保存回文件中？ - undefined
16

sqlite3.Connection.iterdump返回一个迭代器，可将数据库以SQL文本格式导出。在保存内存中的数据库以备将来恢复时非常有用。此函数提供了与sqlite3 shell中的.dump命令相同的功能。

获得此类迭代器并将基于磁盘的数据库导出到基于内存的数据库，然后您就可以开始进行计算了。当计算完成后，只需反向导出回磁盘即可。

3
太好了，但是你如何将那个转储文件恢复到内存数据库中呢？ - Adam Matan
连接到基于文件的数据库并进行倒转。这样做可能不太美观，但如果你的计算非常耗费资源，它将会得到回报。 - Fred Foo
9

首先，您应该尝试找出导致您观察到的缓慢的原因。您是否正在写入表格？您的写入是否足够大的事务，以便不会将不必要的临时结果保存到磁盘上？您是否可以更改写入以进入临时表（使用{{link2：pragma temp_store=memory}}）？您是否可以接受{{link3：pragma synchronous=off}}？

我不认为Python模块中公开了此功能，但sqlite有一个备份API，听起来正是您所要求的：一种从一个数据库复制到另一个数据库的方法（其中任何一个都可以是内存数据库），而且几乎自动地工作，无需用户可见枚举表格。（也许APSW公开了这个功能？）

另一个选择是创建一个ram磁盘（如果您对环境具有足够的控制权），然后将文件复制到那里。

感谢您深入的回答。我只是从中阅读，所以一个简单的复制到内存的技巧就可以了。 - Adam Matan
7
如果我们必须使用Python包装器，则没有比两次传递、读取和写入的解决方案更好的选择。但是从3.7.17版本开始，SQLite有了直接使用内存映射I/O访问磁盘内容的选项。sqlite mmap 如果您想使用mmap，则必须使用C接口，因为没有包装器提供它。
还有另一种硬件解决方案，即内存磁盘。然后您就可以拥有方便的文件IO和内存速度。
这是一个非常好的建议。我将我的性能分成了3倍，运行在Windows上。此外，它支持隐式VFS表（我使用R * Tree和FTS），无需任何额外的复制或备份操作。请注意，您不需要C来启用此功能，只需使用类似于PRAGMA mmap_size = 268435456的命令即可，因此它适用于任何支持SQLite的语言（我使用C＃）。 - Simon Mourier
4

这里有一种相对简单的方法，可以将SQLite数据库读入内存。根据您对数据操作的偏好，您可以使用Pandas数据框架或将表格写入内存中的sqlite3数据库。类似地，在操纵数据后，您可以使用相同的df.to_sqlite方法将结果存储回数据库表中。

import sqlite3 as lite
from pandas.io.sql import read_sql
from sqlalchemy import create_engine

engine = create_engine('sqlite://')
c = engine.connect()
conmem = c.connection
con = lite.connect('ait.sqlite', isolation_level=None) #Here is the connection to <ait.sqlite> residing on disk
cur = con.cursor()
sqlx = 'SELECT * FROM Table'
df = read_sql(sqlx, con, coerce_float=True, params=None) 

#Read SQLite table into a panda dataframe
df.to_sql(con=conmem, name='Table', if_exists='replace', flavor='sqlite')
4
0

使用Cenk Alti的解决方案，当进程达到500MB时，我总是在Python 3.7中遇到MemoryError问题。只有使用sqlite3的备份功能（由thinwybk提到），我才能够加载和保存更大的SQLite数据库。同样，您也可以只用三行代码实现相同的功能。

0

@thinwybk和Crooner的答案都非常出色。

如果您同时使用SQLAlchemy和source.backup(dest)函数连接到多个:memory: sqlite数据库，那么您可能会发现备份没有放入“正确”的内存数据库中。

这可以通过使用适当的连接字符串来解决：https://dev59.com/Kbvoa4cB1Zd3GeqP9r-b#65429612，而且不涉及任何黑客或未记录的功能的使用。

-1

SQLite支持内存数据库。

在Python中，您可以使用:memory:作为数据库名称。

也许您可以打开两个数据库（一个来自文件，一个空的内存数据库），将文件数据库中的所有内容迁移到内存中，然后进一步使用内存数据库进行计算。

4
有没有快速传输数据库的方法？逐个移动每个表格不够优雅。 - Adam Matan
1
这个答案基本上只是重复了问题。 - madoki
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接