使用pyodbc操作SQL Server同时插入更新时遇参数标记错误

云原生阿岚

2026-6-28

用pyodbc将MongoDB数据写入SQL Server时的参数标记错误问题

我通过Python从MongoDB获取数据后，想用pyodbc写入SQL Server，但一直遇到参数相关错误。

第一种实现方式报错：

pyodbc.ProgrammingError: ('The SQL contains 0 parameter markers, but 5 parameters were supplied', 'HY000').

对应的代码：

def UpdateRecords(records):
    try:
        conn = pyodbc.connect("""Driver={SQL Server Native Client 11.0};
                                   Server=****;
                                   Database=****;
                                   Trusted_Connection=yes;""")
        dbCursor = conn.cursor()
        reqstring = """
        MERGE INTO mobile_cur as target
        USING(VALUES (?,?,?,?,?) ) as source (Sys,UserId,UserName,Lat,Lon)
        ON target.Sys = source.Sys
        WHEN MATCHED THEN
        UPDATE SET target.UserId = source.UserId,target.UserName = source.UserName,
        target.Lat = source.Lat,target.Lon = source.Lon
        WHEN NOT MATCHED BY target THEN
        INSERT (Sys,UserId,UserName,Lat,Lon)
        VALUES (source.Sys,source.UserId,source.UserName,source.Lat,source.Lon);"""
        dbCursor.executemany(reqstring,records)
        conn.commit()
        cursor.close()
        conn.close()

    finally:
        conn.close()
        cursor.close()

records = [(1503257, 1503257, 'HGQ', 77.54119, 66.625061), (1503253, 1503253, 'ZXC', 11.473105, 33.727711), (1503250, 1503250, 'FSD', 11.532186, 44.641628)]

我尝试直接把参数插入SQL语句而非用参数标记，结果还是报错：

pyodbc.ProgrammingError: ('The SQL contains 0 parameter markers, but 35 parameters were supplied', 'HY000')

第二种实现代码：

def UpdateRecords(records):
    try:
        conn = pyodbc.connect("""Driver={SQL Server Native Client 11.0};
                                   Server=___;
                                   Database=___;
                                   Trusted_Connection=yes;""")
        dbCursor = conn.cursor()        
        reqstring = """
        MERGE INTO mobile_cur as target
        USING(VALUES {} ) as source (Sys,UserId,UserName,Lat,Lon)
        ON target.Sys = source.Sys
        WHEN MATCHED THEN
        UPDATE SET target.UserId = source.UserId,target.UserName = source.UserName,
        target.Lat = source.Lat,target.Lon = source.Lon
        WHEN NOT MATCHED THEN
        INSERT (Sys,UserId,UserName,Lat,Lon)
        VALUES (source.Sys,source.UserId,source.UserName,source.Lat,source.Lon);""".format(','.join(['(?,?,?,?,?)' for _ in range(len(records))]))
        params = [item for sublist in records for item in sublist]
        dbCursor.execute(reqstring,params)
        conn.commit()
        cursor.close()
        conn.close()

    finally:
        conn.close()
        cursor.close()

问题原因及解决方案

核心问题分析

第一种实现的问题：executemany对MERGE语句的参数绑定支持存在缺陷，MERGE的USING子句结构导致pyodbc无法正确识别每条记录对应的参数标记。
第二种实现的问题：用format拼接的?会被当成普通SQL文本，而非参数占位符——pyodbc只会识别直接写在SQL语句中的?，格式化插入的?无法被解析为参数标记，因此报错"0个参数标记"。

可行解决方案

方案1：循环处理单条记录（小数据量适用）

如果数据量不大，直接循环每条记录执行单条MERGE语句，避免批量绑定的问题：

def UpdateRecords(records):
    conn = None
    cursor = None
    try:
        conn = pyodbc.connect("""Driver={SQL Server Native Client 11.0};
                                   Server=****;
                                   Database=****;
                                   Trusted_Connection=yes;""")
        cursor = conn.cursor()
        reqstring = """
        MERGE INTO mobile_cur as target
        USING(VALUES (?,?,?,?,?) ) as source (Sys,UserId,UserName,Lat,Lon)
        ON target.Sys = source.Sys
        WHEN MATCHED THEN
        UPDATE SET target.UserId = source.UserId,target.UserName = source.UserName,
        target.Lat = source.Lat,target.Lon = source.Lon
        WHEN NOT MATCHED BY target THEN
        INSERT (Sys,UserId,UserName,Lat,Lon)
        VALUES (source.Sys,source.UserId,source.UserName,source.Lat,source.Lon);"""
        for record in records:
            cursor.execute(reqstring, record)
        conn.commit()
    finally:
        if cursor:
            cursor.close()
        if conn:
            conn.close()

方案2：使用表值参数（TVP，大数据量适用）

SQL Server的表值参数支持一次性传递多条记录，效率更高，适合大数据量场景：

先在SQL Server中创建对应表类型：

CREATE TYPE MobileCurType AS TABLE (
    Sys INT,
    UserId INT,
    UserName VARCHAR(50),
    Lat FLOAT,
    Lon FLOAT
);

Python代码实现：

def UpdateRecords(records):
    conn = None
    cursor = None
    try:
        conn = pyodbc.connect("""Driver={SQL Server Native Client 11.0};
                                   Server=****;
                                   Database=****;
                                   Trusted_Connection=yes;""")
        cursor = conn.cursor()
        reqstring = """
        MERGE INTO mobile_cur as target
        USING @tvp as source
        ON target.Sys = source.Sys
        WHEN MATCHED THEN
        UPDATE SET target.UserId = source.UserId,target.UserName = source.UserName,
        target.Lat = source.Lat,target.Lon = source.Lon
        WHEN NOT MATCHED BY target THEN
        INSERT (Sys,UserId,UserName,Lat,Lon)
        VALUES (source.Sys,source.UserId,source.UserName,source.Lat,source.Lon);"""
        # 创建表值参数对象
        tvp = cursor.execute("SELECT * FROM MobileCurType WHERE 1=0").fetchall()
        for record in records:
            tvp.append(record)
        cursor.execute(reqstring, tvp=tvp)
        conn.commit()
    finally:
        if cursor:
            cursor.close()
        if conn:
            conn.close()

方案3：启用`fast_executemany`优化（pyodbc 4.0+适用）

pyodbc 4.0及以上版本支持fast_executemany，可以解决批量MERGE的参数绑定问题，同时提升执行效率：

def UpdateRecords(records):
    conn = None
    cursor = None
    try:
        conn = pyodbc.connect("""Driver={SQL Server Native Client 11.0};
                                   Server=****;
                                   Database=****;
                                   Trusted_Connection=yes;""")
        conn.fast_executemany = True  # 启用快速批量执行
        cursor = conn.cursor()
        reqstring = """
        MERGE INTO mobile_cur as target
        USING(VALUES (?,?,?,?,?) ) as source (Sys,UserId,UserName,Lat,Lon)
        ON target.Sys = source.Sys
        WHEN MATCHED THEN
        UPDATE SET target.UserId = source.UserId,target.UserName = source.UserName,
        target.Lat = source.Lat,target.Lon = source.Lon
        WHEN NOT MATCHED BY target THEN
        INSERT (Sys,UserId,UserName,Lat,Lon)
        VALUES (source.Sys,source.UserId,source.UserName,source.Lat,source.Lon);"""
        cursor.executemany(reqstring, records)
        conn.commit()
    finally:
        if cursor:
            cursor.close()
        if conn:
            conn.close()

额外代码修正提示

原代码中finally块直接调用cursor.close()和conn.close()会报错，需先判断对象是否存在；
第一种实现里存在变量名笔误：定义的是dbCursor，但关闭时用了cursor，会触发NameError。

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

使用pyodbc操作SQL Server同时插入更新时遇参数标记错误

问题原因及解决方案 #

核心问题分析 #

可行解决方案 #