如何通过Pandas从MySQL读取JSON数据并转换为CSV？

我在MySQL数据库的某列中存储了如下格式的JSON数据，希望用Python读取该数据并转换成指定格式的CSV文件。

JSON数据示例： #

{"type":["11","27","26","6"],
 "comment":["","","","ADVANCE"],
 "remark":["","","","anything"],
 "unit":["1.00","1.00","1.00","5000"],
 "rate":["1300000","1409.37","100","1"],
 "extra":["1.00","850","850","1"],
 "amount":["1300000.00","1197964.50","85000.00","5000.00"]}

期望的CSV输出格式： #

"type","comment","remark","unit","rate","extra","amount"
"11","","","1.00","1300000","1.00","1300000.00"
"27","","","1.00","1409.37","850","1197964.50"
"26","","","1.00","100","850","85000.00"
"6","ADVANCE","anything","5000","1","1","5000.00"

解决方案 #

1. 安装依赖 #

需要用到MySQL连接库，执行以下命令安装：

pip install mysql-connector-python

2. Python实现代码 #

核心逻辑是读取MySQL中的JSON数据，解析后将"键对应列表"的结构转置为行数据，最后写入CSV。

import mysql.connector
import json
import csv

# 连接MySQL，替换为你的数据库配置
db = mysql.connector.connect(
    host="你的数据库地址",
    user="用户名",
    password="密码",
    database="数据库名"
)
cursor = db.cursor()

# 读取目标JSON列（假设表名为data_table，JSON列名为json_data）
cursor.execute("SELECT json_data FROM data_table")
# 如果有多行数据，改用fetchall()循环处理
json_result = cursor.fetchone()
json_str = json_result[0]

# 解析JSON字符串为字典
data_dict = json.loads(json_str)

# 提取表头和转置行数据
headers = list(data_dict.keys())
rows = zip(*data_dict.values())

# 写入CSV文件，确保所有字段被双引号包裹
with open('output.csv', 'w', newline='', encoding='utf-8') as csv_file:
    csv_writer = csv.writer(csv_file, quoting=csv.QUOTE_ALL)
    csv_writer.writerow(headers)
    csv_writer.writerows(rows)

# 关闭数据库连接
cursor.close()
db.close()

代码说明 #

• 数据库连接部分：替换配置参数为你的实际MySQL信息；若存在多条JSON数据，将fetchone()改为fetchall()并遍历处理。
• 数据转置：通过zip(*data_dict.values())将每个字段的列表按索引对齐，生成CSV需要的行结构。
• CSV格式：quoting=csv.QUOTE_ALL参数确保所有字段被双引号包裹，与期望输出格式一致。

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