使用pandas将Excel文件导入SQLite时如何避免数据重复或覆盖

你现有代码的核心问题有两个：

1. if_exists='replace' 参数会在每次循环导入单个Excel文件时，直接覆盖整个table1表，导致前面导入的文件数据全部丢失
2. 没有配置去重规则，如果把参数改成append追加数据，又会出现重复插入的问题

解决方案1：给表加唯一约束（推荐） #

提前创建SQLite表时给判定重复的字段加唯一约束，插入冲突时自动跳过重复行，实现自动去重：

import pandas as pd
import os
import sqlite3

# 全局创建一次数据库连接，无需每次循环创建
conn = sqlite3.connect('data.db')
cursor = conn.cursor()

# 提前创建表，设置唯一约束，此处默认用Data+Hour判定重复，可根据实际需求调整唯一字段列表
cursor.execute('''
CREATE TABLE IF NOT EXISTS table1 (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    Data TEXT,
    Hour INTEGER,
    "Series 1" REAL,
    "Series 2" REAL,
    "Series 3" REAL,
    "Series 4" REAL,
    UNIQUE(Data, Hour) ON CONFLICT IGNORE
)
''')
conn.commit()

path = os.getcwd()
for filename in os.listdir(path):
    if filename.endswith('.xls'):
        df = pd.read_excel(filename)
        df.columns = ['Data', 'Hour', 'Series 1', 'Series 2', 'Series 3', 'Series 4']
        # 提前对单个Excel内的重复数据做去重，可选
        df = df.drop_duplicates(subset=['Data', 'Hour'], keep='first')
        # append模式追加数据，冲突行被自动忽略
        df.to_sql('table1', conn, if_exists='append', index=False)
        print(f"{filename} 导入完成")

conn.close()

解决方案2：数据比对后追加（无需修改表结构） #

如果不想修改原有表结构，可以在插入前先查询库内已有的唯一键，过滤掉重复数据后再插入：

import pandas as pd
import os
import sqlite3

conn = sqlite3.connect('data.db')
path = os.getcwd()

# 先判断表是否存在，不存在就首次插入
table_exist = pd.read_sql("SELECT name FROM sqlite_master WHERE type='table' AND name='table1'", conn).shape[0] > 0

for filename in os.listdir(path):
    if filename.endswith('.xls'):
        df = pd.read_excel(filename)
        df.columns = ['Data', 'Hour', 'Series 1', 'Series 2', 'Series 3', 'Series 4']
        df = df.drop_duplicates(subset=['Data', 'Hour'], keep='first')
        
        if table_exist:
            # 查询已有的唯一键
            exist_keys = pd.read_sql("SELECT DISTINCT Data, Hour FROM table1", conn)
            # 过滤出未存在的新数据
            df = df.merge(exist_keys, on=['Data', 'Hour'], how='left', indicator=True)
            df = df[df['_merge'] == 'left_only'].drop(columns=['_merge'])
        
        # 插入数据
        df.to_sql('table1', conn, if_exists='append', index=False)
        table_exist = True
        print(f"{filename} 导入完成")

conn.close()

两种方案都可以解决数据重复/被替换的问题，推荐优先用方案1，SQLite层面的约束性能更高，也能避免其他操作导致的重复数据问题。

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