使用PYODBC实现SQL Server表Upsert时遇参数不匹配错误求助

你遇到的错误核心原因是：SQL语句中定义了26个参数占位符（?），但每个传入的记录仅提供了13个参数：

• UPDATE段需要13个参数：12个SET字段值 + 1个WHERE条件的ReporterCite值
• INSERT段需要另外13个参数：13个字段值

以下是两种无需SQLAlchemy的解决方案：

方法1：重复传递参数匹配占位符 #

将每个记录的参数列表复制一次，让总参数数量与26个占位符对应。同时注意不要在for循环中调用executemany——executemany本身就是批量处理所有记录的方法，外层循环属于冗余操作。

修改后的代码示例：

# 假设records是包含多个记录的列表，每个记录为13个值的元组/列表
# 扩展每个记录的参数，使总数量匹配26个占位符
expanded_records = [row + row for row in records]

sql_insert = """
    UPDATE qch_FullCaseList
    SET
    [DecisionDate]=?,
    [CaseName]=?,
    [FileNum]=?,
    [CourtLocation]=?,
    [CourtName]=?,
    [CourtAbbrv]=?,
    [Judge]=?,
    [CaseLength]=?,
    [CourtCite]=?,
    [ParallelCite]=?,
    [CitedCount]=?,
    [UCN]=?
    WHERE [ReporterCite]=?                                      

    IF @@ROWCOUNT = 0
        INSERT INTO qch_FullCaseList
            (
            [ReporterCite],
            [DecisionDate],
            [CaseName],
            [FileNum],
            [CourtLocation],
            [CourtName],
            [CourtAbbrv],
            [Judge],
            [CaseLength],
            [CourtCite],
            [ParallelCite],
            [CitedCount],
            [UCN]
            )
            VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
"""

# 直接批量执行，无需外层循环
cursor.executemany(sql_insert, expanded_records)
conn.commit()  # 务必提交事务

方法2：使用SQL Server原生MERGE语句（推荐） #

MERGE是SQL Server官方推荐的Upsert标准写法，仅需传递一次参数即可完成更新/插入逻辑，避免占位符重复的问题，代码逻辑更清晰。

修改后的代码示例：

sql_merge = """
    MERGE INTO qch_FullCaseList AS target
    USING (VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)) AS source
        (
        [ReporterCite],
        [DecisionDate],
        [CaseName],
        [FileNum],
        [CourtLocation],
        [CourtName],
        [CourtAbbrv],
        [Judge],
        [CaseLength],
        [CourtCite],
        [ParallelCite],
        [CitedCount],
        [UCN]
        )
    ON target.[ReporterCite] = source.[ReporterCite]
    WHEN MATCHED THEN
        UPDATE SET
            [DecisionDate] = source.[DecisionDate],
            [CaseName] = source.[CaseName],
            [FileNum] = source.[FileNum],
            [CourtLocation] = source.[CourtLocation],
            [CourtName] = source.[CourtName],
            [CourtAbbrv] = source.[CourtAbbrv],
            [Judge] = source.[Judge],
            [CaseLength] = source.[CaseLength],
            [CourtCite] = source.[CourtCite],
            [ParallelCite] = source.[ParallelCite],
            [CitedCount] = source.[CitedCount],
            [UCN] = source.[UCN]
    WHEN NOT MATCHED THEN
        INSERT
            (
            [ReporterCite],
            [DecisionDate],
            [CaseName],
            [FileNum],
            [CourtLocation],
            [CourtName],
            [CourtAbbrv],
            [Judge],
            [CaseLength],
            [CourtCite],
            [ParallelCite],
            [CitedCount],
            [UCN]
            )
        VALUES
            (
            source.[ReporterCite],
            source.[DecisionDate],
            source.[CaseName],
            source.[FileNum],
            source.[CourtLocation],
            source.[CourtName],
            source.[CourtAbbrv],
            source.[Judge],
            source.[CaseLength],
            source.[CourtCite],
            source.[ParallelCite],
            source.[CitedCount],
            source.[UCN]
            );
"""

# 直接使用原records，每个记录仅需13个参数
cursor.executemany(sql_merge, records)
conn.commit()

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