使用Python更新MySQL指定行时遭遇参数错误求助

问题场景 #

需求为查询MySQL的JAX2表中ASSIGNEE字段为空的DID（电话号码），并将该号码的ASSIGNEE更新为用户输入的指定用户名。查询环节正常，但更新时出现报错。

原代码 #

import mysql.connector

mydb = mysql.connector.connect(
  host="127.0.0.1",
  user="root",
  password="password",
  database="DID_MASTER"
)

mycursor = mydb.cursor()
mycursor.execute("SELECT DID FROM JAX2 WHERE ASSIGNEE = ''")
myresult = mycursor.fetchall()

location = input("Enter users location: :")
name = input('Enter users name: ')

for x in myresult:
    print('Free number to be used: ', x)

    sql = "UPDATE JAX2 SET ASSIGNEE = %s WHERE DID = %s"
    val = (name, x)

    mycursor.executemany(sql, val)  
    mydb.commit()

    break

首次报错信息 #

Enter users name: Joe Smith
Free number to be used:  (1235136500,)
Traceback (most recent call last):
  File "/Users/user1/Desktop/old mac Desktop/DID Project/DB_Testing.py", line 27, in <module>
    mycursor.executemany(sql, val)  
  File "/Users/user1/Library/Python/3.9/lib/python/site-packages/mysql/connector/cursor_cext.py", line 406, in executemany
    self.execute(operation, params)
  File "/Users/user1/Library/Python/3.9/lib/python/site-packages/mysql/connector/cursor_cext.py", line 266, in execute
    prepared = self._cnx.prepare_for_mysql(params)
  File "/Users/user1/Library/Python/3.9/lib/python/site-packages/mysql/connector/connection_cext.py", line 738, in prepare_for_mysql
    raise ProgrammingError(
mysql.connector.errors.ProgrammingError: Could not process parameters: str(Joe Smith), it must be of type list, tuple or dict

调整后的报错信息 #

Traceback (most recent call last):
  File "/Users/nbecirovic/Desktop/old mac Desktop/DID Project/DB_Testing.py", line 27, in <module>
    mycursor.executemany(sql, val)  
  File "/Users/nbecirovic/Library/Python/3.9/lib/python/site-packages/mysql/connector/cursor_cext.py", line 406, in executemany
    self.execute(operation, params)
  File "/Users/nbecirovic/Library/Python/3.9/lib/python/site-packages/mysql/connector/cursor_cext.py", line 266, in execute
    prepared = self._cnx.prepare_for_mysql(params)
  File "/Users/nbecirovic/Library/Python/3.9/lib/python/site-packages/mysql/connector/connection_cext.py", line 726, in prepare_for_mysql
    result = self._cmysql.convert_to_mysql(*params)
_mysql_connector.MySQLInterfaceError: Python type tuple cannot be converted

错误原因 #

1. executemany用法错误：该方法用于批量执行SQL，要求第二个参数是包含多个参数组的列表/元组（如[(val1, val2), (val3, val4)]），但此处仅需更新单条记录，应使用execute方法。
2. 参数类型错误：fetchall()返回的每个结果x是一个元组（比如(1235136500,)），直接将元组作为DID参数传入，MySQL无法识别该类型，需提取元组中的具体值x[0]。

修复方案 #

• 将mycursor.executemany(sql, val)替换为mycursor.execute(sql, val)
• 修改参数元组为(name, x[0])，提取DID的具体数值

修正后的完整代码 #

import mysql.connector

mydb = mysql.connector.connect(
  host="127.0.0.1",
  user="root",
  password="password",
  database="DID_MASTER"
)

mycursor = mydb.cursor()
mycursor.execute("SELECT DID FROM JAX2 WHERE ASSIGNEE = ''")
myresult = mycursor.fetchall()

location = input("Enter users location: ")
name = input('Enter users name: ')

for x in myresult:
    did = x[0]
    print('Free number to be used: ', did)

    sql = "UPDATE JAX2 SET ASSIGNEE = %s WHERE DID = %s"
    val = (name, did)

    mycursor.execute(sql, val)  
    mydb.commit()
    print(f"Successfully assigned {did} to {name}")
    break

批量更新补充说明 #

如果需要将所有ASSIGNEE为空的DID批量更新给同一个用户，可保留executemany，但需构造正确的参数列表：

# 批量更新示例
sql = "UPDATE JAX2 SET ASSIGNEE = %s WHERE DID = %s"
val_list = [(name, x[0]) for x in myresult]
mycursor.executemany(sql, val_list)
mydb.commit()
print(f"Updated {len(val_list)} records")

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