pandas数据dataframe转json

一、pandas读取数据库

import pandas as pd
from pymysql import connect

mysql_cn = connect(host='localhost', port=3306, user='mysql', passwd='mysql', db='mysql')
df = pd.read_sql('select * from t_mysql;', con=mysql_cn)
mysql_cn.close()

二、Json转为DataFrame

# 读取json
import pandas as pd
df = pd.read_json("test.json",encoding="utf-8", orient='records')

三、DataFrame转为Json

1.columns

df.to_json(orient="columns",force_ascii=False)
按照column进行转换，效果图如下：
{"宠物":{"李四":"汪星人","王五":"喵星人"},"年龄":{"李四":25,"王五":23}}

2.split

df.to_json(orient="split",force_ascii=False)

按照split进行转换，效果图如下：

{"columns":["宠物","年龄"],"index":["李四","王五"],"data":[["汪星人",25],["喵星人",23]]}

3.records

df.to_json(orient="records",force_ascii=False)

按照records进行转换，效果图如下：

[{"宠物":"汪星人","年龄":25},{"宠物":"喵星人","年龄":23}]

4.index

df.to_json(orient="index",force_ascii=False)

按照index进行转换，效果图如下：

{"李四":{"宠物":"汪星人","年龄":25},"王五":{"宠物":"喵星人","年龄":23}}

5.values

df.to_json(orient="values",force_ascii=False)

按照values进行数据转换，效果图如下：

[["汪星人",25],["喵星人",23]]