如何避免迭代SQLite游标时缓存查询结果？

我太懂这种头疼的感觉了——处理100GB级别的联合查询结果，结果游标直接把所有数据缓存到内存里，硬生生把内存榨干，试了PRAGMA cache_size=0还没效果，确实闹心。

先给你点透核心原因：Python的sqlite3模块默认会把整个查询结果集一次性加载到客户端内存中，哪怕你用for row in cursor:循环，底层还是先把所有数据拉过来了。而PRAGMA cache_size=0控制的是SQLite服务器端的页缓存，管不了客户端的结果集缓存，所以没用。

下面给你几个靠谱的解决方案：

1. 启用流式游标（逐行获取结果） #

修改连接和查询逻辑，强制让游标逐行从数据库取数据，而非一次性加载全部：

import sqlite3

# 连接时设置isolation_level=None，开启自动提交模式，触发流式游标
conn = sqlite3.connect('your_db.db', isolation_level=None)
cursor = conn.cursor()

# 可选：限制服务器端缓存大小（负数代表字节数，这里设为64KB）
cursor.execute("PRAGMA cache_size=-65536")

cursor.execute("SELECT * FROM table1 INNER JOIN table2 ON table1.id=table2.id")
# 用fetchone()循环逐行读取，避免全量缓存
while True:
    row = cursor.fetchone()
    if not row:
        break
    # do something with row
    pass

conn.close()

这种方式下，游标不会在内存中缓存全部结果，每次只从数据库拉取一行数据。

2. 分批查询（分块处理数据） #

如果流式游标还是有问题，可以把大查询拆成多个小批次，按某个有序字段（比如id）分块处理：

import sqlite3

conn = sqlite3.connect('your_db.db')
cursor = conn.cursor()

# 先获取待处理数据的id范围
cursor.execute("SELECT MIN(id), MAX(id) FROM table1")
min_id, max_id = cursor.fetchone()

batch_size = 10000  # 每次处理1万条，可根据内存情况调整
current_id = min_id

while current_id <= max_id:
    cursor.execute("""
        SELECT * FROM table1 
        INNER JOIN table2 ON table1.id=table2.id
        WHERE table1.id >= ? AND table1.id < ?
    """, (current_id, current_id + batch_size))
    
    for row in cursor:
        # do something with row
        pass
    
    current_id += batch_size

conn.close()

这种方法完全可控，每次只加载一小部分数据到内存。注意用来分批次的字段（比如id）最好有索引，否则每次查询都会全表扫描，效率会很低。

3. 调整临时存储设置（针对JOIN场景） #

JOIN操作可能会让SQLite生成临时表，默认临时表存在内存里会占空间，我们可以强制把临时表存到磁盘：

cursor.execute("PRAGMA temp_store = FILE")
# 可选：指定临时文件存储目录，避免占满系统盘
cursor.execute("PRAGMA temp_store_directory = '/path/to/your/temp/dir'")

这个设置配合前面的方法一起用，能进一步减少内存占用。

内容的提问来源于stack exchange，提问作者JuliettVictor