如何加速从Excel数据更新Sqlite3表的UPDATE操作？

我需要接收.xlsx文件并更新SQLite3表，现有代码可正常运行但速度极慢，怀疑操作方式存在问题，特此咨询如何加速UPDATE过程。

具体操作步骤 #

• 使用正则表达式将数据拆分至3个DataFrame；
• 清洗数据（最终保留loc和date两列）并创建字典；
• 通过嵌套循环遍历字典更新SQLite3表。

SQLite3表结构（以m表为例） #

CREATE TABLE "m" (
  "index" INTEGER,
  "loc"   TEXT,
  "1" REAL,
  "2" REAL,
  "3" REAL,
  "4" REAL,
  "5" REAL,
  "6" REAL,
  "7" REAL,
  "8" REAL,
  "9" REAL,
  "10"    REAL,
  "11"    REAL,
  "12"    REAL,
  "13"    REAL,
  "14"    REAL,
  "15"    REAL,
  "16"    REAL,
  "17"    REAL,
  "18"    REAL,
  "19"    REAL,
  "20"    REAL,
  "21"    REAL,
  "22"    REAL,
  "23"    REAL,
  "24"    REAL,
  "25"    REAL,
  "26"    REAL,
  "27"    REAL,
  "28"    REAL,
  "29"    REAL,
  "30"    REAL,
  "31"    REAL,
  "32"    REAL,
  "33"    REAL,
  "34"    REAL,
  "35"    REAL,
  "36"    REAL,
  "37"    REAL,
  "38"    REAL,
  "39"    REAL,
  "40"    REAL,
  "41"    REAL,
  "42"    REAL,
  "43"    REAL,
  "44"    REAL,
  "45"    REAL,
  "46"    REAL,
  "47"    REAL,
  "48"    REAL,
  "49"    REAL,
  "50"    REAL,
  "51"    REAL,
  "52"    REAL,
  "Type"  TEXT
)

当前使用的Python代码 #

import pandas as pd
import sqlite3

def clean(data):
    df = data[['loc', 'date']].reset_index(drop = True)#Filtering columns that i need
    df['date'] = df['date'].dt.isocalendar().week #Change column values to weeks
    return df
    
def update_cycle_counting(df):
    #Regex to filter data
    m = df[df['loc'].str.contains('A-[a-zA-Z]\d{2}-\d{3}-\d{2}.\d{2}|E[a-zA-Z]\d{3}-\d{4}|M[a-zA-Z]\d{3}-\d{4}|SAFE\d*')]
    j1 = df[df['loc'].str.contains('C-[a-zA-Z]\d{2}-\d{3}-\d{2}.\d{2}')]
    j2 = df[df['loc'].str.contains('B-[a-zA-Z]\d{2}-\d{3}-\d{2}.\d{2}')]
    #Assign cleaned data to new variables
    m = clean(m)
    j1 = clean(j1)
    j2 = clean(j2)
    #Creating dictionary to loop thru
    wh = {'m':m, 'j1':j1, 'j2':j2}
    #Create path and connect to database
    path ='count.db'
    conn = sqlite3.connect(path)
    #Loop table names == dict.keys
    for k,v in wh.items():
        #Updating rows
        for i, row in v.iterrows():
            cur = conn.cursor()
            cur.execute(f'UPDATE {k} SET "{row[1]}"= 1 WHERE "loc" = "{row[0]}";')
            conn.commit()
            cur.close()
    conn.close()

代码速度慢的核心原因是逐行执行UPDATE+每次更新都提交事务，这对SQLite是极大的性能损耗，结合其他可优化点，具体改进如下：

1. 给loc字段创建索引 #

如果loc字段无索引，每次UPDATE的WHERE条件都会触发全表扫描，速度会大幅下降。先给每个表的loc字段创建唯一索引：

CREATE UNIQUE INDEX idx_m_loc ON m(loc);
CREATE UNIQUE INDEX idx_j1_loc ON j1(loc);
CREATE UNIQUE INDEX idx_j2_loc ON j2(loc);

2. 批量执行更新，减少事务提交次数 #

SQLite的事务提交开销极高，不要每次UPDATE都调用conn.commit()，应将所有更新放在一个事务中，最后统一提交。

3. 使用参数化查询，避免字符串拼接 #

直接用f-string拼接SQL语句不仅效率低，还存在SQL注入风险，改用参数化查询可同时提升性能与安全性。

4. 优化DataFrame遍历方式 #

iterrows()是较慢的遍历方式，改用itertuples()可提升遍历效率。

import pandas as pd
import sqlite3

def clean(data):
    df = data[['loc', 'date']].reset_index(drop=True)
    df['date'] = df['date'].dt.isocalendar().week
    return df
    
def update_cycle_counting(df):
    # 正则过滤数据，转义点号避免匹配任意字符
    m = df[df['loc'].str.contains(r'A-[a-zA-Z]\d{2}-\d{3}-\d{2}\.\d{2}|E[a-zA-Z]\d{3}-\d{4}|M[a-zA-Z]\d{3}-\d{4}|SAFE\d*')]
    j1 = df[df['loc'].str.contains(r'C-[a-zA-Z]\d{2}-\d{3}-\d{2}\.\d{2}')]
    j2 = df[df['loc'].str.contains(r'B-[a-zA-Z]\d{2}-\d{3}-\d{2}\.\d{2}')]
    
    m = clean(m)
    j1 = clean(j1)
    j2 = clean(j2)
    
    wh = {'m': m, 'j1': j1, 'j2': j2}
    path = 'count.db'
    
    # 建立连接并创建游标
    conn = sqlite3.connect(path)
    cur = conn.cursor()
    
    try:
        for table_name, df_data in wh.items():
            # 使用itertuples()快速遍历
            for row in df_data.itertuples(index=False):
                loc_val = row.loc
                week_col = str(row.date)
                # 参数化查询
                cur.execute(f'UPDATE {table_name} SET "{week_col}" = 1 WHERE "loc" = ?;', (loc_val,))
        
        # 所有更新完成后统一提交
        conn.commit()
    except Exception as e:
        # 出错回滚事务
        conn.rollback()
        raise e
    finally:
        # 关闭游标与连接
        cur.close()
        conn.close()

极致优化：按周批量更新 #

如果数据量极大，可按周分组，将同一周的loc批量聚合，用IN语句一次更新多行，进一步减少SQL执行次数：

# 替换原循环内的遍历逻辑
for table_name, df_data in wh.items():
    # 按周分组，收集同一周的所有loc
    grouped = df_data.groupby('date')['loc'].apply(list).reset_index()
    for row in grouped.itertuples(index=False):
        week_col = str(row.date)
        loc_list = row.loc
        # 生成对应数量的占位符
        placeholders = ', '.join(['?'] * len(loc_list))
        cur.execute(f'UPDATE {table_name} SET "{week_col}" = 1 WHERE "loc" IN ({placeholders});', tuple(loc_list))

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