如何用Python快速将CSV数据导入SQLite3表并匹配指定列

报错根因 #

你遇到的OperationalError: 5 values for 4 columns是INSERT语句指定的列数和传入参数数量不匹配导致：你要插入5个列，但CSV每行拆分后只有4个值，参数数量对不上。另外逐行插入+每次循环都commit是效率极低的核心原因，commit是磁盘IO操作，20万次IO自然需要数小时才能跑完。

1. 快速导入20万行数据的方案 #

首推Pandas批量导入方案，20万行数据通常几秒到几十秒即可完成：

import pandas as pd
import sqlite3

# 读取CSV文件：如果CSV有表头则header设为0，无表头设为None，names指定和表匹配的4个非主键列名
df = pd.read_csv('path_to_csv', header=None, names=['high', 'low', 'original', 'ship_date'])
# 若需要用行号作为主键index_of，新增对应列即可，start参数可调整起始值
df['index_of'] = df.index + 1

# 连接数据库批量写入
conn = sqlite3.connect('db_name.sqlite')
# if_exists设为append适配已创建的表，index=False不写入Pandas默认索引
df.to_sql('table_name', conn, if_exists='append', index=False)
conn.close()

如果不想依赖Pandas，用原生SQLite3的批量插入方案也能大幅提升效率：

import sqlite3
import csv

conn = sqlite3.connect("db_name.sqlite")
cursor = conn.cursor()
insert_batch = []

with open('path_to_csv', 'r', encoding='utf-8') as f:
    reader = csv.reader(f)
    # 如果CSV有表头，取消下一行注释跳过表头
    # next(reader)
    for idx, row in enumerate(reader, start=1):
        # 组装插入数据：首位为index_of主键值，后四位为CSV对应列
        insert_batch.append((idx, row[0], row[1], row[2], row[3]))

# 批量执行插入，仅提交一次
cursor.executemany("INSERT INTO table_name (index_of, high, low, original, ship_date) VALUES (?,?,?,?,?)", insert_batch)
conn.commit()
conn.close()

2. CSV列与表指定列匹配的方法 #

• 若你的index_of是自增主键（已设置AUTOINCREMENT属性），插入时不需要指定该列，数据库会自动生成主键值，INSERT语句调整为：

  INSERT INTO table_name (high, low, original, ship_date) VALUES (?,?,?,?)
  

  此时只需传入CSV的4个列值即可，不会出现参数不匹配问题。
• 若index_of需要自定义值（比如用CSV行号），只需在插入参数的对应位置补全主键值，保证参数数量和INSERT语句指定的列数、顺序完全匹配即可。
• 用Pandas方案时，只要DataFrame的列名和数据库表的列名完全一致，to_sql会自动匹配列写入，无需手动写INSERT语句，也不会出现列错配问题。

注意：尽量用csv.reader处理CSV文件，不要直接用split(',')拆分行，避免字段内包含逗号时拆分错误。

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