使用Python+PYODBC向SQL Server插入数据报错：无法绑定SQL参数

问题描述 #

原本代码可正常向数据库插入记录，但添加number、promote_tag和routevdn参数后出现报错。此前将这些值硬编码到SQL中时运行正常，添加参数后触发以下错误：

Traceback (most recent call last):
  File "C:/Users/User/PycharmProjects/Sean Project.py", line 48, in <module>
    add_number()
  File "C:/Users/User/PycharmProjects/Sean Project.py", line 42, in add_number
    cursor.execute(update_query, pkey, number, numberTwo, promote_tag)
pyodbc.Error: ('HY004', '[HY004] [Microsoft][ODBC SQL Server Driver]Invalid SQL data type (0) (SQLBindParameter)')

相关代码：

def add_number():
    number = input("Number to add: ")
    producerNumber = input("Producer Number: ")
    routevdn = "+1234567891"
    now = datetime.now()
    today = now.strftime("%m/%d")
    promote_tag = producerNumber + "-Lead-" + today
    query = """select max (pkey+1) from table_test"""
    print(promote_tag)
    print(routevdn)
    print(number)
    cursor.execute(query)
    for row in cursor:
        row_to_list = [elem for elem in row]
        pkey = row_to_list
        print(pkey)
    query_calltype = """select max (pkey+1) from table2_test"""
    cursor.execute(query_calltype)
    for row in cursor:
        row_to_list2 = [elem for elem in row]
        pkey_calltype = row_to_list2
        print(pkey_table2_test)

    update_query = """INSERT INTO table_test
VALUES ((?), (?), (?), 'MY AGENCY NAME', '0', '0', '', (?), '0', '', '0', '', '',    '1', '', '0', '0', '', '', '', '1', '', '0', '', '', '', '',        '', '', '', '0', 'NULL');"""

    update_queryTwo = """INSERT INTO table2_test
    VALUES ((?), 'XX', '', (?), '', 'No', '', '', '', '', '0', (?), (?), '0', '', 'false', '', '0', '', '', '', '0', '0', '', '0', '', '', '0', 'NULL', 'NULL');"""
    cursor.execute(update_query, pkey, number, number, promote_tag)
    cursor.execute(update_queryTwo, pkey_calltype, routevdn, number, promote_tag)
    conn.commit()


add_number()

错误原因 #

1. 主键变量是列表而非单个值：查询max(pkey+1)后，将行数据转为列表赋值给pkey/pkey_calltype，导致传递给execute的是列表类型，与数据库字段的数值类型不匹配，触发数据类型错误。
2. SQL占位符多余括号：INSERT语句中(?)的额外括号会干扰参数绑定逻辑，导致ODBC驱动无法正确识别参数类型。
3. 变量名错误：代码中打印pkey_table2_test但实际变量是pkey_calltype，虽不直接引发报错，但会导致调试信息失效。
4. 参数绑定风险：列表类型的参数会被展开成多个值，导致占位符数量与传递参数数量不匹配。

修复方案 #

1. 提取单个主键值 #

将查询结果的单个值赋值给主键变量，同时处理表为空时max返回NULL的情况：

# 获取table_test的主键
cursor.execute(query)
row = cursor.fetchone()
pkey = row[0] if row[0] is not None else 1  # 表为空时默认从1开始

# 获取table2_test的主键
cursor.execute(query_calltype)
row = cursor.fetchone()
pkey_calltype = row[0] if row[0] is not None else 1

2. 修正SQL占位符 #

去掉INSERT语句中参数的多余括号，将(?)改为?：

update_query = """INSERT INTO table_test
VALUES (?, ?, ?, 'MY AGENCY NAME', '0', '0', '', ?, '0', '', '0', '', '', '1', '', '0', '0', '', '', '', '1', '', '0', '', '', '', '', '', '', '', '0', 'NULL');"""

update_queryTwo = """INSERT INTO table2_test
VALUES (?, 'XX', '', ?, '', 'No', '', '', '', '', '0', ?, ?, '0', '', 'false', '', '0', '', '', '', '0', '0', '', '0', '', '', '0', 'NULL', 'NULL');"""

3. 修正变量名错误 #

将print(pkey_table2_test)改为print(pkey_calltype)

4. 确保参数传递正确 #

传递参数时确保每个都是单个值，而非列表：

cursor.execute(update_query, pkey, number, number, promote_tag)
cursor.execute(update_queryTwo, pkey_calltype, routevdn, number, promote_tag)

完整修复代码 #

from datetime import datetime
import pyodbc

# 初始化数据库连接（替换为你的实际连接字符串）
conn = pyodbc.connect("DRIVER={SQL Server};SERVER=你的服务器名;DATABASE=你的数据库名;UID=用户名;PWD=密码")
cursor = conn.cursor()

def add_number():
    number = input("Number to add: ")
    producerNumber = input("Producer Number: ")
    routevdn = "+1234567891"
    now = datetime.now()
    today = now.strftime("%m/%d")
    promote_tag = producerNumber + "-Lead-" + today
    query = """select max(pkey+1) from table_test"""
    print(promote_tag)
    print(routevdn)
    print(number)
    
    # 获取table_test的主键
    cursor.execute(query)
    row = cursor.fetchone()
    pkey = row[0] if row[0] is not None else 1
    print(pkey)
    
    # 获取table2_test的主键
    query_calltype = """select max(pkey+1) from table2_test"""
    cursor.execute(query_calltype)
    row = cursor.fetchone()
    pkey_calltype = row[0] if row[0] is not None else 1
    print(pkey_calltype)

    # 修正后的插入语句
    update_query = """INSERT INTO table_test
VALUES (?, ?, ?, 'MY AGENCY NAME', '0', '0', '', ?, '0', '', '0', '', '', '1', '', '0', '0', '', '', '', '1', '', '0', '', '', '', '', '', '', '', '0', 'NULL');"""

    update_queryTwo = """INSERT INTO table2_test
VALUES (?, 'XX', '', ?, '', 'No', '', '', '', '', '0', ?, ?, '0', '', 'false', '', '0', '', '', '', '0', '0', '', '0', '', '', '0', 'NULL', 'NULL');"""
    
    # 执行插入
    cursor.execute(update_query, pkey, number, number, promote_tag)
    cursor.execute(update_queryTwo, pkey_calltype, routevdn, number, promote_tag)
    conn.commit()
    print("记录插入成功")

add_number()

# 关闭连接
cursor.close()
conn.close()

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