SQLite：选择结果受后续插入影响

3 投票

2 回答

1099 浏览

提问于 2025-04-17 08:01

我正在开发一个应用程序，需要处理一个表格中的很多行（超过1,000,000行）。在这个过程中，我会插入新行和更新已有的行。要求是，当我执行查询时，必须确保每一行都只返回一次，并且不能返回在查询执行后插入的行。我希望不把所有行都加载到内存中（这样会很耗时间和内存，我试过了）。

我写了一个小的Python示例，显示SQLite似乎并不会将插入（还有更新和删除）与一个长时间运行的查询隔离开来。我在SQLite的文档中找不到明确提到这种行为的地方，但我找到了一些链接，暗示插入可能会失败（可能是在SQLite的早期版本中？），但在我的示例中并没有出现这种情况。

import sqlite3

def select_affected_by_insert():
    # select from and simultaneously modify same table
    cn = sqlite3.connect(':memory:')
    cn.execute("CREATE TABLE demo (v INTEGER PRIMARY KEY)")

    n = 5
    values = [[v] for v in range(n)]
    cn.executemany('INSERT INTO demo VALUES (?)', values)

    for (v,) in cn.execute('SELECT v FROM demo'):

        with cn:
            # insert in transaction
            cn.execute('INSERT INTO demo VALUES (?)', [n + v])

        print v, n + v
        assert v < n, 'got more rows than expected!'

if __name__ == '__main__':
    select_affected_by_insert()

SQLite 3.6.12
Python 2.6.4

有没有比把数据复制到一个单独的（临时）表中然后从那里查询更好的解决办法？

补充说明：我忘了说我需要在循环中进行提交。这个过程可能会被中断，已经完成的部分工作必须提交，这样下次运行时就不需要重新做了。

内存管理数据插入 sqlite 临时表查询优化事务管理数据库隔离行级锁

2 个回答

如果你以延迟事务模式打开数据库，并在你的 SELECT-INSERT 逻辑结束时执行 COMMIT，就像这样：

cn = sqlite3.connect(':memory:', isolation_level='DEFERRED')
...
for (v,) in cn.execute('SELECT v FROM demo'):
    cn.execute('INSERT INTO demo VALUES (?)', [n + v])
cn.commit()

你的插入语句应该等到整个代码块结束后再执行。根据SQLite的事务控制文档：

如果同时对同一个SQLite数据库连接执行多个命令，自动提交会延迟到最后一个命令完成。例如，当执行一个 SELECT 语句时，命令的执行会在每返回一行结果时暂停。在这个暂停期间，可以对数据库中的其他表执行 INSERT、UPDATE 或 DELETE 命令。但是这些更改都不会被提交，直到原始的 SELECT 语句完成。

回答于 2025-04-17 由 Python大师

分享举报

1. 使用WAL模式（这样写数据和读数据的操作就不会互相干扰）

2. 为读数据和写数据分别使用不同的连接

回答于 2025-04-17 由 Python大师

分享举报

SQLite：选择结果受后续插入影响

2 个回答

撰写回答