如何在Python中获取SQL循环插入后的最终记录总数

方案1：用计数器累加成功插入数 #

初始化一个计数器变量，每次插入成功后累加数值，循环结束后输出总数，同时优化提交逻辑（频繁提交会拖慢性能）：

# 初始化计数器
insert_count = 0

try:
    for row in data:
        LoadDt = row[0]
        FiscalWeek = row[1]
        InboundCaseVol = row[2]
        OutboundCaseVol6WCH = row[3]
        OutboundCaseVol6WWC = row[4]
        
        tgt_query = '''INSERT INTO"SAPTOM"."EL.ps.Tech::tbl.IBDbounrend_PY"("LoadDt","FiscalWeek","InboundCaseVol","OutboundCaseVol6WCH","OutboundCaseVol6WWC") VALUES (?,?,?,?,?) '''
        val = (LoadDt,FiscalWeek,InboundCaseVol,OutboundCaseVol6WCH,OutboundCaseVol6WWC)
        cur.execute(tgt_query, val)
        # 累加成功插入的行数
        insert_count += cur.rowcount
    # 循环结束后统一提交事务
    conn.commit()
    print(f'Number of Records inserted successfully: {insert_count}')
except Exception as e:
    print("Error inserting data:", e)
    # 出错时回滚事务，避免数据不一致
    conn.rollback()

方案2：改用批量插入（推荐，性能更优） #

把所有待插入的参数整理成列表，用executemany()一次性插入，此时cur.rowcount直接返回总插入行数，效率比逐条插入高很多：

try:
    # 整理所有插入参数
    insert_values = []
    for row in data:
        LoadDt = row[0]
        FiscalWeek = row[1]
        InboundCaseVol = row[2]
        OutboundCaseVol6WCH = row[3]
        OutboundCaseVol6WWC = row[4]
        insert_values.append((LoadDt,FiscalWeek,InboundCaseVol,OutboundCaseVol6WCH,OutboundCaseVol6WWC))
    
    tgt_query = '''INSERT INTO"SAPTOM"."EL.ps.Tech::tbl.IBDbounrend_PY"("LoadDt","FiscalWeek","InboundCaseVol","OutboundCaseVol6WCH","OutboundCaseVol6WWC") VALUES (?,?,?,?,?) '''
    # 执行批量插入
    cur.executemany(tgt_query, insert_values)
    conn.commit()
    print(f'Number of Records inserted successfully: {cur.rowcount}')
except Exception as e:
    print("Error inserting data:", e)
    conn.rollback()

额外优化提示 #

• 原脚本中每次循环都执行conn.commit()会大幅降低插入效率，建议统一在循环结束后提交
• 增加异常捕获后的回滚操作，避免部分数据提交后出错导致数据不一致

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