SQLite3设置主键后仍出现重复CustomerID？如何解决？

你遇到的问题核心在于if_exists='replace'的工作机制：当使用这个参数时，pandas会先删除原有表，然后根据DataFrame的字段类型重新自动生成表结构，你之前手动定义的CustomerID为主键唯一的约束会被完全覆盖，新创建的表其实没有主键约束，所以重复的CustomerID能被存入。

下面是几种可行的实现CustomerID唯一性的方案：

方案一：先清洗DataFrame，去重后再导入 #

先对读取后的DataFrame按CustomerID去重，确保数据本身没有重复，再导入数据库：

import pandas as pd
import sqlite3

# 读取CSV到DataFrame
df = pd.read_csv('Purchase.csv')

# 按CustomerID去重，保留第一条记录
df = df.drop_duplicates(subset='CustomerID', keep='first')

# 连接数据库并导入数据
conn = sqlite3.connect('your_database.db')
df.to_sql('Client', conn, if_exists='replace', index=False)
conn.close()

方案二：手动创建带约束的表，再追加数据 #

先手动创建符合要求的表结构（带上主键唯一约束），再用append模式导入，让数据库层面的约束生效：

import pandas as pd
import sqlite3

# 读取CSV到DataFrame
df = pd.read_csv('Purchase.csv')

conn = sqlite3.connect('your_database.db')
cursor = conn.cursor()

# 手动创建带主键约束的Client表
cursor.execute('''
CREATE TABLE IF NOT EXISTS Client (
    PurchaseID INTEGER,
    CustomerID INTEGER PRIMARY KEY UNIQUE,
    CustomerName TEXT,
    -- 其他字段根据你的CSV补充
)
''')
conn.commit()

# 用append模式导入，遇到重复CustomerID会触发报错
try:
    df.to_sql('Client', conn, if_exists='append', index=False)
except sqlite3.IntegrityError:
    print("存在重复的CustomerID，导入失败，请先清洗数据")

conn.close()

如果想在导入时自动处理重复（比如忽略重复或更新已有记录），可以修改导入逻辑，用executemany配合ON CONFLICT子句：

# 替换to_sql的部分，改用executemany
cursor.executemany('''
INSERT INTO Client (PurchaseID, CustomerID, CustomerName)
VALUES (?, ?, ?)
ON CONFLICT(CustomerID) DO NOTHING  -- 忽略重复记录
-- 或者用 DO UPDATE SET CustomerName=excluded.CustomerName 来更新已有记录
''', df[['PurchaseID', 'CustomerID', 'CustomerName']].values.tolist())
conn.commit()

方案三：用SQLAlchemy指定字段约束，让to_sql创建正确表结构 #

如果想用replace模式但保留约束，可以借助SQLAlchemy来定义字段类型和约束，让pandas创建表时带上主键：

import pandas as pd
from sqlalchemy import create_engine, Integer, String

# 读取CSV到DataFrame
df = pd.read_csv('Purchase.csv')

# 用SQLAlchemy连接数据库
engine = create_engine('sqlite:///your_database.db')

# 指定每个字段的类型和约束
dtype = {
    'PurchaseID': Integer(),
    'CustomerID': Integer(),
    'CustomerName': String()
}

# 导入时设置primary_key参数，指定CustomerID为主键
df.to_sql(
    'Client',
    engine,
    if_exists='replace',
    index=False,
    dtype=dtype,
    primary_keys=['CustomerID']
)

这样即使使用replace，新创建的表也会带上CustomerID的主键唯一约束，数据库会自动阻止重复值存入。

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