当迭代大量sqlite3记录时，临时存储已满

Question

我有一个大的SQLite数据库，我在那里加入一个3.5 m行表到自己。我使用SQLite，因为它是我的python3应用程序的序列化格式，而平面文件格式在我的工作流程中很重要。当使用

遍历此连接的行(大约55M行)时:

cursor.execute('SELECT DISTINCT p.pid, pp.pname, pp.pid FROM proteins'
               'AS p JOIN proteins AS pp USING(pname) ORDER BY p.pid')
for row in cursor:
    # do stuff with row.

EXPLAIN QUERY PLAN给出如下:

0|0|0|SCAN TABLE proteins AS p USING INDEX pid_index (~1000000 rows)
0|1|1|SEARCH TABLE proteins AS pp USING INDEX pname_index (pname=?) (~10 rows)
0|0|0|USE TEMP B-TREE FOR DISTINCT

sqlite3错误提示"数据库或磁盘已满"后说1,000.000行，这似乎表明一个完整的SQLite磁盘临时存储。由于我当前的机器上有足够的RAM，可以通过将tempstore设置为内存来解决这个问题，但这不是最优的，因为在这种情况下，所有的RAM似乎都用光了，我倾向于并行运行4个左右的这些进程。我(可能不正确)的假设是迭代器是一个生成器，不会在内存上施加很大的负载，不像fetchall会加载所有行。然而，我现在耗尽了磁盘空间(在一个小的SSD临时磁盘上)，并假设SQLite需要将结果存储在某个地方。

解决这个问题的一种方法可能是运行SELECT ... LIMIT x OFFSET y查询块，但是每次使用更大的OFFSET时，它们都会变慢。还有别的办法吗?这些临时文件中存储了什么?

Answer 1

0|0|0|USE TEMP B-TREE FOR DISTINCT

这是正在使用磁盘的内容。

为了支持DISTINCT, SQLite必须存储查询中已经出现的行。对于大量的结果，这个集合可能会变得很大。因此，为了节省内存，SQLite将临时存储在磁盘上。

删除DISTINCT子句是避免这个问题的一种简单方法，但它改变了查询的意义;现在可以得到重复的行。如果你不介意，或者你有唯一的索引或者其他方法来确保你不会得到重复，那就没关系了

Answer 2

你试图用SQLite3做的是一个非常糟糕的主意，让我试着解释为什么。

磁盘上有原始数据，它适合并且可读。

你在SQLite3内部生成一个结果，这个结果会大大扩展。

然后尝试通过sql连接器传输这个非常大的数据集。

关系数据库通常不是为这种操作而设计的。SQLite3也不例外。关系数据库是为小的快速查询而设计的，这些查询持续不到一秒，并且返回几行。

你最好用别的工具。

使用Pandas将整个数据集读入python是我推荐的解决方案。另外，使用itertools也是一个好主意。