python - 我是否需要多个游标对象来遍历记录集并同时更新？

Question

所以我有一个大型数据库，我无法立即将其保存在内存中。我必须遍历表中的每个项目，对其进行处理，然后将处理后的数据放入表中的另一列。

当我在游标上循环时，如果我尝试运行更新语句，它会截断记录集(我相信是因为它正在重新调整游标对象)。

问题:

创建第二个游标对象来运行更新语句是否允许我继续遍历原始选择语句？

我是否需要与数据库的第二个连接才能拥有第二个游标对象，这将允许我这样做？

sqlite 如何响应与数据库的两个连接，一个从表中读取，另一个写入表中？

我的代码(简化版):

import sqlite3

class DataManager():
    """ Manages database (used below). 
        I cut this class way down to avoid confusion in the question.
    """
    def __init__(self, db_path):
        self.connection = sqlite3.connect(db_path)
        self.connection.text_factory = str
        self.cursor = self.connection.cursor()

    def genRecordset(self, str_sql, subs=tuple()):
        """ Generate records as tuples, for str_sql.
        """
        self.cursor.execute(str_sql, subs)
        for row in self.cursor:
            yield row

select = """
            SELECT id, unprocessed_content 
            FROM data_table 
            WHERE processed_content IS NULL
         """

update = """
            UPDATE data_table
            SET processed_content = ?
            WHERE id = ?
         """
data_manager = DataManager(r'C:\myDatabase.db')
subs = []
for row in data_manager.genRecordset(str_sql):
    id, unprocessed_content = row
    processed_content = processContent(unprocessed_content)
    subs.append((processed_content, id))

    #every n records update the database (whenever I run out of memory)
    if len(subs) >= 1000:
        data_manager.cursor.executemany(update, subs)
        data_manager.connection.commit()
        subs = []
#update remaining records
if subs:
    data_manager.cursor.executemany(update, subs)
    data_manager.connection.commit()

我尝试的另一种方法是将我的选择语句修改为:

select = """
            SELECT id, unprocessed_content 
            FROM data_table 
            WHERE processed_content IS NULL
            LIMIT 1000
         """

然后我会做:

recordset = data_manager.cursor.execute(select)
while recordset:
    #do update stuff...
    recordset = data_manager.cursor.execute(select)

我遇到的问题是我的真实 select 语句中有一个 JOIN 并且需要一段时间，因此多次执行 JOIN 非常耗时。我试图通过只执行一次选择，然后使用生成器来加快这个过程，这样我就不必将它全部保存在内存中。

解决方案:

好的，我的前两个问题的答案是“否”。对于我的第三个问题，一旦与数据库建立连接，它就会锁定整个数据库，因此在第一个连接关闭之前，另一个连接将无法执行任何操作。

我找不到它的源代码，但根据经验证据，我相信一个连接一次只能使用一个游标对象，并且最后运行的查询优先。这意味着，当我遍历选定的记录集并一次生成一行时，一旦我运行第一个更新语句，我的生成器就会停止生成行。

我的解决方案是创建一个临时数据库，我将 processed_content 和 id 放在一起，这样我每个数据库都有一个连接/游标对象，并且可以继续循环选定的记录集，同时定期插入到临时数据库中。到达所选记录集的末尾后，我将临时数据库中的数据传输回原始数据库。

如果有人确切知道连接/游标对象，请在评论中告诉我。

Answer 1

我认为您的架构大致正确——用“游标”表示它会让“老 SQL 手”感到困惑，因为他们会考虑与 DECLARE foo CURSOR 相关的许多问题。 , FETCH FROM CURSOR , WHERE CURRENT OF CURSOR ，以及其他与 SQL 游标有关的漂亮东西。 Python DB API 的“游标”只是一种打包和执行 SQL 语句的便捷方式，不一定与 SQL 游标相关联——它不会遇到任何这些问题——尽管它可能会展示它自己的(完全原创的)；-)但是，通过你正在做的结果的“批处理”、你的适当提交等，你已经预防性地解决了我在介意。

在其他一些引擎上，我建议先选择一个临时表，然后在更新主表的同时从该临时表中读取，但我不确定在 sqlite 中性能会受到怎样的影响，具体取决于哪些索引你有(如果你的更新没有影响索引，那么我怀疑这样一个临时表在 sqlite 中根本不是优化——但我不能对你的数据运行基准测试，这是检查性能假设的唯一真实方法).

所以，我想说，去吧!-)