pandas read_sql_table使用

read_sql_table 从mysql数据库读出数据 并转json

案例

def pd_readsqltable():
    import pandas as pd
    import json
    a = pd.read_sql_table('mt', 'mysql://root:123456@172.17.0.2:3306/zy) # mt 为所查表的表名，zy为所要查询的表所在的数据库名。
    print(a, 'a------------------')
    print(type(a), 'type(a)------------------')
    b = a.to_json(orient='index', force_ascii=False)
    print(b, 'b=================')
    print(json.loads(b)['0']['id'], r"json.loads(b)['0']['id']=================")
    print(type(json.loads(b)), r"type(json.loads(b))=================")
    print(type(b), 'type(b)=================')

结果展示

   id      name            message               ctime               rtime
0   4   demo111   这是测试333109871wsx 2020-09-11 11:54:37 2020-10-28 15:51:44
1   6     demo3              这是测试3 2020-09-11 11:56:18 2020-09-11 11:56:18 a------------------
<class 'pandas.core.frame.DataFrame'> type(a)------------------
{"0":{"id":4,"name":"demo111","message":"这是测试333109871wsx","ctime":1599825277000,"rtime":1603900304000},"1":{"id":6,"name":"demo3","message":"这是测试3","ctime":1599825378000,"rtime":1599825378000}} b=================
4 json.loads(b)['0']['id']=================
<class 'dict'> type(json.loads(b))=================
<class 'str'> type(b)=================