Python SQLite内存缓存

Question

Python SQLite内存缓存

4

我正在使用Python 2.7和SQLite。我正在构建一个包含数百万行的数据库。我希望只在必要时才将数据写入磁盘，以提高性能。我的想法是只定期调用commit()。我已经尝试了下面的代码。中间的查询显示我们获得了一致的读取结果。但是，当我查看硬盘时，我看到了一个名为example.db-journal的文件。这必须是数据被缓存的地方。在这种情况下，这对性能没有任何好处。有没有办法让插入操作先收集到内存中，然后再刷新到磁盘上？有更好的方法吗？

import sqlite3
conn = sqlite3.connect('example.db')
c = conn.cursor()
c.execute('CREATE TABLE if not exists stocks (date text, trans text, symbol text, qty real, price real)')

c.execute("INSERT INTO stocks VALUES ('2006-01-05','BUY','RHAT',100,35.14)")
c.execute("INSERT INTO stocks VALUES ('2006-01-05','BUY','RHAT',100,35.14)")
t = ('RHAT',)
c.execute('SELECT date, symbol, trans FROM stocks WHERE symbol=?', t)
# Here, we get 2 rows as expected.
print c.fetchall()
c.execute("INSERT INTO stocks VALUES ('2006-01-05','BUY','RHAT',100,35.14)")
c.execute("INSERT INTO stocks VALUES ('2006-01-05','BUY','RHAT',100,35.14)")
c.execute("INSERT INTO stocks VALUES ('2006-01-05','BUY','RHAT',100,35.14)")
c.execute("INSERT INTO stocks VALUES ('2006-01-05','BUY','RHAT',100,35.14)")
c.execute("INSERT INTO stocks VALUES ('2006-01-05','BUY','RHAT',100,35.14)")

conn.commit()

t = ('RHAT',)
c.execute('SELECT date, symbol, trans FROM stocks WHERE symbol=?', t)
# Here, we get all the rows as expected.
print c.fetchall()

conn.close()

更新：

如果有人遇到这个问题，我想给出一些代码的更新。我正在处理来自文本文件的5+百万行数据，并需要一个地方存储数据以供进一步处理。我最初将所有数据存储在内存中，但是内存不够用。因此，我切换到SQLite作为磁盘缓存。我的原始内存版本处理每50,000行的原始文本文件需要大约36秒。

经过测量，我第一次尝试使用SQLite进行批处理的版本需要大约660秒才能处理50,000行数据。根据评论（感谢发布者），我编写了以下代码：

self.conn = sqlite3.connect('myDB.db', isolation_level='Exclusive')
self.cursor.execute('PRAGMA synchronous = 0')
self.cursor.execute('PRAGMA journal_mode = OFF')

此外，我承诺在处理完1000行文本文件后提交。

if lineNum % 1000 == 0:
    self.conn.commit()

因此，这样一来，从文本文件中提取的5万行现在只需要约40秒。所以，虽然整体时间增加了11％，但是内存是恒定的，这更为重要。

- Doo Dah

你可以使用像Redis这样的内存存储来代替。 - Pramod

2

如果你真的需要这个（措施！），你可以尝试使用内存中的 SQLite 数据库（“:memory:”），并定期将其内容转储到磁盘上的数据库。 - ev-br

2个回答

1

在您的情况下，您正在自动提交模式下创建一个db连接，这意味着每次执行INSERT语句时，数据库都会启动一个事务，执行该语句并提交。因此，在这种情况下，您的commit是无意义的。请参见Python文档上的sqlite3。

但是，您正确地指出，应该在事务中理想地插入大量行。这会向连接发出信号，表明它应记录所有传入的INSERT语句在日志文件中，但延迟写入数据库文件直到事务提交。即使您的执行受到I/O操作的限制，写入日志文件也不会对性能造成严重影响。

- MrGumble

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Thomas Fenzl · Accepted Answer

首先，你确定需要这个吗？对于阅读，操作系统应该已经缓存了文件，如果你写了很多内容，并且不同步到磁盘，那么你可能会很容易地丢失数据。

如果你将其识别为瓶颈，你可以使用内存数据库，使用connect(':memory:')并获得一个迭代器，在需要时返回一个sql转储：http://docs.python.org/2/library/sqlite3.html#sqlite3.Connection.iterdump。

import sqlite3, os

in_memory = sqlite3.connect(':memory:')
# do stuff

con = sqlite3.connect('existing_db.db')
con.execute('drop table stocks')
for line in in_memory.iterdump():
    con.execute(line)

再次强调，如果需要，请进行度量。如果您有足够重要的数据，认真考虑使用其他数据存储方式，例如完整的数据库管理系统，比如Postgres。