欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

python解析Json文件

程序员文章站 2024-02-03 10:13:10
...

项目背景:某网吧位置图信息保存为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])