Python mysql.connector中cursor.execute()的.format()与%s在MySQL JSON字段的使用对比

嘿，我理解你想避开%s参数化占位符，改用字符串格式化（format或f-string）来处理JSON存入MySQL json字段的需求。先得给你提个关键提醒：直接用字符串格式化拼接SQL语句存在SQL注入风险，只有当你能100%确保传入的JSON内容是安全的（比如完全由你自己生成、无外部用户输入）时，才推荐这么做；如果涉及不可信数据，参数化查询（也就是你原来用的%s方式）依然是更安全的选择。

下面是满足你需求的具体实现方案：

方案1：转JSON字符串后直接格式化拼接 #

首先将Python的JSON对象（通常是字典）转换为标准JSON字符串，再用f-string或format拼入SQL语句，注意要用单引号包裹JSON字符串，让MySQL识别为json类型值：

import mysql.connector
import json

# 示例JSON对象（Python字典）
user_data = {"username": "Alice", "age": 30, "hobbies": ["reading", "hiking"]}
# 转换为JSON字符串
json_str = json.dumps(user_data)

# 用f-string拼接SQL语句
sql = f"INSERT INTO user_info (profile) VALUES ('{json_str}')"

# 执行SQL
conn = mysql.connector.connect(
    host="your_host",
    user="your_user",
    password="your_password",
    database="your_db"
)
cursor = conn.cursor()
cursor.execute(sql)
conn.commit()

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

如果是更新操作，逻辑类似：

user_id = 1
new_profile = {"status": "active", "last_login": "2024-05-20"}
json_str = json.dumps(new_profile)

sql = f"UPDATE user_info SET profile = '{json_str}' WHERE id = {user_id}"
# 后续执行逻辑同上...

方案2：处理JSON中的单引号避免语法错误 #

如果JSON内容包含单引号（比如{"name": "O'Neil"}），直接拼接会导致SQL语法错误。这时可以手动将JSON字符串中的单引号替换为两个单引号（MySQL中用双单引号表示转义）：

user_data = {"name": "O'Neil", "age": 28}
json_str = json.dumps(user_data).replace("'", "''")
sql = f"INSERT INTO user_info (profile) VALUES ('{json_str}')"

这样就能避免单引号破坏SQL语句结构，但依然无法完全防范SQL注入攻击，所以仅适用于安全可控的场景。

再次强调安全风险 #

如果你的JSON数据包含任何外部用户输入或不可信内容，绝对不要用字符串格式化拼接SQL！比如恶意输入{"name": "'); DROP TABLE user_info; --"}会直接删除你的数据表，后果严重。这种情况下，还是推荐你使用参数化查询：

sql = "INSERT INTO user_info (profile) VALUES (%s)"
cursor.execute(sql, (json_str,))

mysql.connector会自动处理参数转义和安全传递，既简单又安全。

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