使用Python SQL.Connector向MySQL插入列表数据时SQL语法错误的解决方法

场景与代码 #

尝试使用Python向MySQL的userinfo表提交数据，表包含三列：eid(VARCHAR(6))、ename(VARCHAR(10))、questions(BLOB)，使用的代码如下：

data = [
    {"question": "How old was Harry?", "options": ["10", "20", "none of the above"], "answer": "10"},
    {"question": "example question", "options": ["1", "2", "3"], "answer": "1"},
]

mycursor.execute("INSERT INTO userinfo VALUES ('{}','{}','{}');".format("1234", "test", data))
my_database.commit()

报错信息 #

_mysql_connector.MySQLInterfaceError: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'question': 'How old was Harry?', 'options': ['10', '20', 'none of the above'], '' at line 1

错误原因 #

1. SQL语法被破坏：直接用str.format()拼接SQL时，Python列表data会被转为原始字符串（包含引号、冒号、方括号等），插入SQL后会打乱语句结构，导致语法错误。
2. 存在SQL注入风险：这种字符串拼接方式不安全，若数据包含特殊字符或恶意代码，会直接在SQL中执行。

解决方案 #

采用参数化查询+数据序列化的方式解决：

1. 序列化Python数据 #

questions是BLOB类型，需要把Python的列表/字典序列化为可存储的格式（比如JSON字符串），MySQL的BLOB支持存储这类字符串。

2. 使用参数化查询 #

用%s作为占位符（mysql-connector的标准占位符），将参数单独传入execute()方法，避免直接拼接SQL。

修正后的代码 #

import json

data = [
    {"question": "How old was Harry?", "options": ["10", "20", "none of the above"], "answer": "10"},
    {"question": "example question", "options": ["1", "2", "3"], "answer": "1"},
]

# 将Python列表序列化为JSON字符串
serialized_data = json.dumps(data)

# 参数化查询，自动处理转义与语法问题
mycursor.execute(
    "INSERT INTO userinfo (eid, ename, questions) VALUES (%s, %s, %s);",
    ("1234", "test", serialized_data)
)
my_database.commit()

读取数据时的还原操作 #

如果需要从表中读取questions数据，只需用json.loads()将存储的JSON字符串转回Python列表：

mycursor.execute("SELECT questions FROM userinfo WHERE eid = %s;", ("1234",))
result = mycursor.fetchone()
if result:
    loaded_data = json.loads(result[0])
    print(loaded_data)

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