Python字典转JSON插入MySQL JSON类型字段报无效JSON问题问询

• 数据库驱动二次转义：常用Python MySQL驱动（pymysql、mysql-connector等）执行参数化查询时会自动转义字符串参数。你提前用json.dumps()生成的JSON字符串自带双引号，二次转义后会变为{\"ath\": 69045...}格式，MySQL无法识别为合法JSON。
• 缺少显式JSON类型转换：部分MySQL版本或驱动不会自动将字符串识别为JSON类型，报错提示的may need CAST就是要求显式做类型转换。
• 空值格式异常：如果get("all_time")返回None，json.dumps(None)会得到字符串"null"，如果你的JSON字段要求必须存储对象而非空值，也会触发格式错误。

1. 采用参数化查询+显式CAST转换
   不要手动拼接SQL语句，使用驱动自带的参数占位符，同时在SQL中加入CAST声明转换为JSON类型，示例代码如下：

import json
import pymysql

# 空值兜底，避免get返回None
all_time_dict = ticker_market_data[ticker].get("all_time", {})
price_change_dict = ticker_market_data[ticker].get("price_change_percent", {})

# 生成JSON字符串，可加ensure_ascii=False避免非英文字符乱码
all_time_json = json.dumps(all_time_dict, ensure_ascii=False)
price_change_json = json.dumps(price_change_dict, ensure_ascii=False)

# 编写SQL，显式CAST字符串为JSON类型
insert_sql = """
INSERT INTO 你的表名 (ticker, all_time, price_change_percent)
VALUES (%s, CAST(%s AS JSON), CAST(%s AS JSON))
"""

# 执行参数化查询
cursor.execute(insert_sql, (ticker, all_time_json, price_change_json))
conn.commit()

2. 前置校验JSON合法性
   插入前可增加校验逻辑，确认生成的JSON格式正确：

def is_valid_json(json_str):
    try:
        json.loads(json_str)
        return True
    except json.JSONDecodeError:
        return False

# 校验示例
if not is_valid_json(all_time_json):
    # 自定义异常处理逻辑
    print("all_time字段JSON格式非法")

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