Python sqlite3中fetchmany循环仅执行一次即退出的问题

问题描述 #

编写了如下代码用于从SQLite数据库批量获取剧集描述，调用OpenAI接口清洗后更新回数据库。数据读写功能正常，但fetchmany的while循环仅执行完一个批次后就退出，尝试多种写法仍未解决。

原代码：

import openai
import sqlite3
import os
from dotenv import load_dotenv
load_dotenv()

def clean_and_save_descriptions_subset(days_old = 7, num_reivew = 1000, batch_size = 50):
    api_key = os.getenv("OPEN_AI_API_KEY")
    sql = f"""
    SELECT e.mp3_url, e.episode_description 
    From episodes e 
    JOIN shows s 
    ON e.itunesid = s.itunesid 
    WHERE published_date > STRFTIME('%s', 'now') - ({days_old} *24 *60 *60)
    AND s.num_reviews > {num_reivew}
    AND e.clean_episode_description is NULL
    """
    conn = sqlite3.connect("db/chatterpulse.db")
    cursor = conn.cursor()
    cursor.execute(sql)

    batch_no = 0
    while True:
        batch = cursor.fetchmany(batch_size)
        if not batch:
            break
        
        print(batch_no)
        mp3_urls = [row[0] for row in batch]
        episode_descriptions = [row[1] for row in batch]

        cleaned_descriptions = clean_descriptions(episode_descriptions, api_key)

        for mp3_url, cleaned_description in zip(mp3_urls, cleaned_descriptions):
            update_sql = f"""
            UPDATE episodes
            SET clean_episode_description = ?
            WHERE mp3_url = ?;
            """
            cursor.execute(update_sql, (cleaned_description, mp3_url))

        conn.commit()
        batch_no += 1

    conn.close()

clean_and_save_descriptions_subset()

问题原因 #

SQLite的游标（cursor）在执行**写入操作（如UPDATE）**后，会自动重置当前查询的结果游标位置。原代码中使用同一个cursor既处理SELECT的fetchmany读取，又执行UPDATE写入，导致第一次fetch后，执行UPDATE操作重置了SELECT查询的游标，后续fetchmany无法获取剩余数据，循环直接退出。

解决方案 #

使用两个独立的cursor：一个专门用于读取查询结果，另一个用于执行更新操作，避免读写操作互相干扰游标位置。

修改后的代码：

import openai
import sqlite3
import os
from dotenv import load_dotenv
load_dotenv()

def clean_and_save_descriptions_subset(days_old = 7, num_reivew = 1000, batch_size = 50):
    api_key = os.getenv("OPEN_AI_API_KEY")
    sql = f"""
    SELECT e.mp3_url, e.episode_description 
    From episodes e 
    JOIN shows s 
    ON e.itunesid = s.itunesid 
    WHERE published_date > STRFTIME('%s', 'now') - ({days_old} *24 *60 *60)
    AND s.num_reviews > {num_reivew}
    AND e.clean_episode_description is NULL
    """
    conn = sqlite3.connect("db/chatterpulse.db")
    # 创建两个独立的游标：一个读，一个写
    read_cursor = conn.cursor()
    write_cursor = conn.cursor()
    
    read_cursor.execute(sql)

    batch_no = 0
    while True:
        batch = read_cursor.fetchmany(batch_size)
        if not batch:
            break
        
        print(batch_no)
        mp3_urls = [row[0] for row in batch]
        episode_descriptions = [row[1] for row in batch]

        cleaned_descriptions = clean_descriptions(episode_descriptions, api_key)

        for mp3_url, cleaned_description in zip(mp3_urls, cleaned_descriptions):
            update_sql = """
            UPDATE episodes
            SET clean_episode_description = ?
            WHERE mp3_url = ?;
            """
            # 使用写游标执行更新
            write_cursor.execute(update_sql, (cleaned_description, mp3_url))

        conn.commit()
        batch_no += 1

    # 关闭两个游标和连接
    read_cursor.close()
    write_cursor.close()
    conn.close()

clean_and_save_descriptions_subset()

关键改动说明 #

• 拆分游标：创建read_cursor负责SELECT查询和fetchmany读取，write_cursor专门执行UPDATE操作，避免读写操作互相干扰游标状态。
• 保持查询游标独立：read_cursor在整个循环中仅处理初始SELECT的结果集，不会被写入操作重置，确保fetchmany能持续获取剩余批次的数据。

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