python解析Json文件

项目背景：某网吧位置图信息保存为json字符串，放入门店表中。尝试将json解析获取门店座位信息。

解决思路： python连接数据库，读取报表中的json及门店编码字段

代码如下：

import pandas as pd
import demjson
import pymysql
import json 


## 定义连接数据库函数
def my_db(host,user,passwd,db,sql,port= 3306,charset = 'utf8'):
    coon = pymysql.connect(host=host,
                           port=port,
                           user=user,
                           passwd = passwd,
                           db = db,
                           charset = charset
                           )
    cur = coon.cursor()
    cur.execute(sql)
    if sql.strip()[:6].upper() == 'SELECT':
        res = cur.fetchall()
    else:
        coon.commit()
        res = 'ok'
    cur.close()
    coon.close()
    return res



#解析json代码
def json_read(text,com_code):
    comcodeList =[]
    AreaNameList =[]
    AreaPriceList =[] 
    AreaTagsList =[] 
    RoomsnumList =[]   
    object_len =len(text)
    for i in range(0,object_len):
        AreaName = text[i]['realAreaName']
        AreaPrice = text[i]['realAreaReferencePrice']
        try:            
            AreaTags = text[i]['realAreaTags']
        except BaseException:
            AreaTags = ''
        Roomsnum = len(text[i]['rooms'])
        RoomName =text[i]['rooms'][j]['roomName']
        comcodeList.append(com_code)
        AreaNameList.append(AreaName)
        AreaPriceList.append(AreaPrice)
        AreaTagsList.append(AreaTags)
        RoomsnumList.append(Roomsnum)

    guodu ={"comcode" :comcodeList,            
            "AreaName" :AreaNameList,
            "AreaPrice" :AreaPriceList,
            "AreaTags" :AreaTagsList,
            "Roomsnum" :RoomsnumList }
    data = pd.DataFrame(guodu)
    return data

##获取commcode 和 json 数据
sql = "select *  from shop"
res = my_db('xxx.xxx.xxx.xxx','root','xxxx','xxx',sql)



## 元组的第二个元素为json代码
dic ={}
final_frame = pd.DataFrame(columns = ["comcode", "AreaName", "AreaPrice", "AreaTags", "Roomsnum"])
for i in range(0,len(res)):
    dic[res[i][0]] = res[i][1]  #res返回的是sql查询的结果，第一列设置为键名，第二列是json文本
    json_test =json.loads(res[i][1])
    temp_frame = json_read(json_test,res[i][0])
    final_frame = pd.concat([final_frame,temp_frame],axis=0,ignore_index=True)
    print(i,res[i][0])