python - 迭代非常大的表并更新行的最有效方法是什么？

Question

问题:

在Python(3.6)中使用内置的SQLite3数据库时，迭代非常大的数据库表并逐行更新它有哪些注意事项？

要求:

我需要迭代每一行并使用列中的信息来执行操作，然后使用结果更新该行的第二列。我无法即时执行此操作，我需要完全更新表格。

此外，根据数据库的大小以及执行每个操作所需的时间，预计该操作将运行多天。考虑到这一点，它需要具有容错能力，并能够定期提交更改。

问题

我首先查看不同的获取方法，但我想知道不同方法的内存注意事项是什么。当您执行 fetchmany() 时，我在另一台服务器上看到了外部数据库数据库正在生成包含所有结果的 select 语句，但仅返回 arraysize一次结果——SQLite 也是这种情况吗？如果是这样，使用 fetchmany() 有什么好处吗？与 fetchall()与 fetchone() ？

我计划使用 executemany() 的生成器函数为了更新数据库并定期提交，假设我可以分块获取，以便间隔中断生成器函数以使其提交。

示例:

table:
  columns: id, value1, updated_value

def action_function():
    next_id = next_row()
    updated_value = compute_value(id)
    yield (updated_value, next_id)

def next_row():
    while True:
        results = cur.fetchmany(arraysize)
        if not results:
            break
        for result in results:
            yield result[0]

cur.executemany('''UPDATE table SET updated_value = ? WHERE id = ?''', action_function())

这应该是这个样子吗？我认为可能应该颠倒这些功能，以便使分块提交每个 arraysize ？

最后，数据库有数百万行，需要计算每行一个操作(使用同一行中的值)，然后更新一列。将数据库加载到内存中可能无法正常工作，因此 fetchall()似乎是这样，但考虑到标准 SQL 数据库在 SELECT 的情况下如何工作无论如何，语句都会将结果加载到内存中，Python 中的 SQLite3 是否会发生这种情况？如果没有，发生了什么？

有没有更好的方法可以做到这一点，例如简单地保存每个 block 的 start_rowid 和 stop_rowid 变量，然后计算下一个 block 大小并执行 SELECT只针对那些索引的声明？

感谢您的帮助!

编辑:

作为替代选项，有 create_function逐行进行。

类似这样的事情:

con.create_function("action", 1, compute_value)
cur.execute("UPDATE table SET updated_value = action(id)")

This answer建议使用这种方法，但考虑到它至少需要一定程度的容错性，我认为这种方法行不通，并且如果仅由于 compute_value 的时间，完成时间仍然会很长。做它该做的事。

如果我使用此方法并且过程被中断，到目前为止计算的值会提交到数据库还是会丢失？

Answer 1

假设 compute_value() 无法在数据库上完成，我会保持简单:

SELECT - 全部。然后 fetchone()、运行 compute_value() 并UPDATE 该行。你的问题听起来很像 compute_value() 是你大部分时间都失败的地方。一次读取和提交一行可能不会增加太多开销并最大限度地减少内存影响。它还具有额外的好处，即您始终在每次 compute_value() 之后提交。如果您认为如此，我会使用 fetchmany() 切换到窗口方法。

研究如何在行基础上并行执行 compute_value() 对于您的情况可能是有意义的。使用multiprocessing使用作业队列(其中的行是作业)可能会很有用。