Python批量插入数据到数据库executemany详解

对比for循环单条插入与executemany批量插入的性能差异，给出SQLite/MySQL通用的批量插入写法，万级数据秒级入库。

场景痛点

要把几万条数据写进数据库，用for循环一条条execute，每条都要走一次网络/文件提交，跑下来几分钟甚至卡死。SQLite的 executemany 能把多条SQL打包一次执行，配合事务，插入十万行从几分钟压到几秒。

用到的库

sqlite3是标准库，无需pip安装：

pip install pandas

完整代码

import sqlite3
import time
from pathlib import Path

DB_PATH = Path("batch.db")

def make_rows(n=10000):
"""生成n条测试数据"""
return [(f"user_{i}", f"部门{i % 10}", 5000 + i * 10) for i in range(n)]

def create_table():
conn = sqlite3.connect(DB_PATH)
cur = conn.cursor()
cur.execute("DROP TABLE IF EXISTS users")
cur.execute("""
CREATE TABLE users (
id      INTEGER PRIMARY KEY AUTOINCREMENT,
name    TEXT,
dept    TEXT,
salary  INTEGER
)
""")
conn.commit()
conn.close()

def insert_one_by_one(rows):
"""错误示范：for循环单条插入"""
conn = sqlite3.connect(DB_PATH)
cur = conn.cursor()
t0 = time.time()
for r in rows:
cur.execute("INSERT INTO users (name, dept, salary) VALUES (?, ?, ?)", r)
conn.commit()
print(f"单条插入 {len(rows)} 行耗时: {time.time()-t0:.2f}s")
conn.close()

def insert_executemany(rows):
"""正确做法：executemany批量插入"""
conn = sqlite3.connect(DB_PATH)
cur = conn.cursor()
t0 = time.time()
cur.executemany(
"INSERT INTO users (name, dept, salary) VALUES (?, ?, ?)",
rows
)
conn.commit()
print(f"executemany {len(rows)} 行耗时: {time.time()-t0:.2f}s")
conn.close()

def insert_with_transaction(rows):
"""更稳的写法：显式事务 + 分批提交"""
conn = sqlite3.connect(DB_PATH)
cur = conn.cursor()
t0 = time.time()
batch_size = 1000
for i in range(0, len(rows), batch_size):
batch = rows[i:i+batch_size]
cur.executemany(
"INSERT INTO users (name, dept, salary) VALUES (?, ?, ?)",
batch
)
conn.commit()  # 每批提交一次，避免事务过大
print(f"分批executemany {len(rows)} 行耗时: {time.time()-t0:.2f}s")
conn.close()

if __name__ == "__main__":
create_table()
rows = make_rows(10000)
insert_one_by_one(rows)
create_table()
insert_executemany(rows)
create_table()
insert_with_transaction(rows)

代码讲解

cur.executemany(sql, rows) 接收一个SQL模板和一个参数列表（list/tuple of tuples），底层会把多条SQL合并执行。
单条execute在for循环里慢，主要慢在每次都有隐式事务开销；executemany 在一个事务里批量提交。
分批 commit() 是为了避免单次事务过大导致redo日志暴涨；万级数据一次commit也没问题，百万级建议分批。
参数列表里每个元素结构必须和SQL占位符一一对应，列顺序不能错。