如何在SQL中使用INSERT OR IGNORE实现机器学习预测数据去重入库

原代码存在的核心问题 #

• 重复创建表报错：循环内每次执行CREATE TABLE，第一次创建表后，后续循环会触发"table Table already exists"错误，必须添加IF NOT EXISTS。
• SQL注入风险：用字符串格式化%s拼接SQL语句，若输入内容含特殊字符（如单引号）会导致语法错误，甚至被注入攻击，必须用参数化查询。
• 数据库连接效率低下：循环内每次创建/关闭连接，频繁IO操作拖慢性能，应将连接放在循环外部。
• 重复数据拦截失效：INSERT OR IGNORE依赖唯一约束生效，但原表仅id为主键（自增），无法识别重复的输入数据。需给FolderNumber、Complaint1、Complaint2设置联合唯一约束，确保相同输入不会重复存储。
• 变量名与键名错误：
  • 模型预测结果赋值给Predictions，但插入时用Prediction，变量名不匹配会报错。
  • 数据读取时混用complaint2和Complaint2，键名大小写不一致会触发KeyError。
• 特征列名冲突：拼接两个向量转换后的DataFrame时，列名完全重复（如两个DataFrame都有"student"列），会导致模型输入特征混乱，需给特征列添加前缀区分来源。

优化后的完整代码 #

import sqlite3
import pandas as pd

# 初始化数据库连接（放在循环外）
conn = sqlite3.connect("mydata.db")
cur = conn.cursor()

# 创建表（添加IF NOT EXISTS和联合唯一约束）
cur.execute("""
CREATE TABLE IF NOT EXISTS complaint_predictions (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    FolderNumber TEXT,
    Complaint1 TEXT,
    Complaint2 TEXT,
    Prediction TEXT,
    UNIQUE(FolderNumber, Complaint1, Complaint2, Prediction)
)
""")
conn.commit()

for i in range(5):
    # 统一键名大小写（假设数据中实际键为Complaint2，若为complaint2则统一修改）
    complaint1 = data["Complaint1"][i]
    complaint2 = data["Complaint2"][i]
    folder_num = data["FolderNumber"][i]

    # 根据预测分支处理特征
    if models.predict([complaint1]) == "STUDENTS":
        # 给特征列添加前缀区分Complaint1和Complaint2
        c1_features = pd.DataFrame(
            vectoriserst.transform([complaint1]).toarray(),
            columns=[f"c1_{col}" for col in vectoriserst.get_feature_names()]
        )
        c2_features = pd.DataFrame(
            vectoriserst.transform([complaint2]).toarray(),
            columns=[f"c2_{col}" for col in vectoriserst.get_feature_names()]
        )
        new_data = pd.concat([c1_features, c2_features], axis=1)
        prediction = modelst.predict(new_data)[0]  # 取单个预测结果
    else:
        c1_features = pd.DataFrame(
            vectoriserfc.transform([complaint1]).toarray(),
            columns=[f"c1_{col}" for col in vectoriserfc.get_feature_names()]
        )
        c2_features = pd.DataFrame(
            vectoriserfc.transform([complaint2]).toarray(),
            columns=[f"c2_{col}" for col in vectoriserfc.get_feature_names()]
        )
        new_data = pd.concat([c1_features, c2_features], axis=1)
        prediction = modelfc.predict(new_data)[0]

    # 参数化插入（避免SQL注入，自动处理特殊字符）
    cur.execute("""
    INSERT OR IGNORE INTO complaint_predictions 
    (FolderNumber, Complaint1, Complaint2, Prediction)
    VALUES (?, ?, ?, ?)
    """, (folder_num, complaint1, complaint2, prediction))

# 批量提交后关闭连接
conn.commit()
conn.close()

关键优化点说明 #

1. 表结构优化：
   • 表名改为complaint_predictions（避免使用SQL关键字Table）。
   • 添加AUTOINCREMENT让id自动递增。
   • 新增UNIQUE(FolderNumber, Complaint1, Complaint2, Prediction)联合约束，确保相同输入+预测结果不会重复存储。
2. 数据库连接优化：循环外初始化连接，循环结束后统一关闭，减少IO开销。
3. 参数化查询：用?作为占位符，传入参数元组，彻底避免SQL注入和字符串拼接错误。
4. 特征列名处理：给Complaint1和Complaint2的特征列分别添加c1_、c2_前缀，解决列名冲突问题。
5. 变量名统一：修正Predictions到prediction的变量名错误，统一数据键名大小写。

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