Python使用mysql.connector传参报错：参数需为列表/元组/字典

问题场景 #

使用Python的mysql.connector连接MySQL数据库时，尝试通过变量传递参数执行包含LIKE关键字的SELECT查询，触发错误：

"Could not process parameters: str(Providing Quality Tyre Services in Ponteland), it must be of type list, tuple or dict"

错误代码示例：

import mysql.connector
from sentence_splitter import SentenceSplitter, split_text_into_sentences

mydb = mysql.connector.connect(
  host="00.00.00.00",
  user="user",
  password="password",
  database="database"
)

mycursor = mydb.cursor()

sql = ("""SELECT blog_paragraph FROM blog_paragraph WHERE blog_topic like '%"%s"%'""")
val = ("Providing Quality Tyre Services in Ponteland")
mycursor.execute(sql,val)

myresult = mycursor.fetchall()

for x in myresult:
      print(x)

而将参数直接硬编码进SQL语句时，程序可正常运行：

import mysql.connector
from sentence_splitter import SentenceSplitter, split_text_into_sentences

mydb = mysql.connector.connect(
  host="00.00.00.00",
  user="user",
  password="password",
  database="database"
)

mycursor = mydb.cursor()

sql = ("""SELECT blog_paragraph FROM blog_paragraph WHERE blog_topic like '%"Providing Quality Tyre Services in Ponteland"%'""")
mycursor.execute(sql)

myresult = mycursor.fetchall()

for x in myresult:
      print(x)

错误原因 #

1. 参数类型不符合要求：代码中val = ("Providing Quality Tyre Services in Ponteland")并没有创建元组，括号仅作为语法包裹，实际val还是单个字符串。mysql.connector要求execute()的第二个参数必须是列表、元组或字典类型，单个字符串不满足要求。若要定义单元素元组，需在末尾加逗号：val = ("xxx",)。
2. LIKE参数化写法错误：原SQL语句中like '%"%s"%'的写法不符合参数化规范，参数化查询会自动处理字符串的引号，无需手动在SQL中添加引号和百分号，正确做法是将百分号整合到参数值中。

正确的参数化查询方案（推荐） #

参数化查询能避免SQL注入风险，是生产环境的首选方案，正确写法如下：

import mysql.connector

mydb = mysql.connector.connect(
  host="00.00.00.00",
  user="user",
  password="password",
  database="database"
)

mycursor = mydb.cursor()

# SQL中仅用占位符%s，百分号放到参数值里
sql = "SELECT blog_paragraph FROM blog_paragraph WHERE blog_topic LIKE %s"
val = ("%Providing Quality Tyre Services in Ponteland%",)  # 末尾逗号创建单元素元组
mycursor.execute(sql, val)

myresult = mycursor.fetchall()

for x in myresult:
    print(x)

也可以用列表传递参数：

val = ["%Providing Quality Tyre Services in Ponteland%"]
mycursor.execute(sql, val)

用户临时解决方案 #

用户通过字符串格式化的方式解决了问题，但这种方式存在SQL注入风险，不推荐在生产环境使用：

mycursor = mydb.cursor()

val = "Providing Quality Tyre Services in Ponteland"
sql = ("SELECT blog_paragraph FROM blog_paragraph WHERE blog_topic like '%%%s%%'  " % val)

mycursor.execute(sql)

myresult = mycursor.fetchall()

这里的%%是字符串格式化中用来表示单个%的转义写法，最终生成的SQL会包含%xxx%的LIKE匹配规则。

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