利用python解析数据库中的json

如果存储到数据库中的某个字段是json格式的数据，利用sql来进行解析速度慢不说，很容易搞垮数据库，线上rds资源CPU瞬间100%是很可怕的。

下面分享下利用python解析json数据的代码，简单快捷：

这里会用到xlwt,pymysql,requests,xmltodict,json模块，可以自行搜索安装，一般用 pip3 install <模块名>  即可安装成功。

#!/usr/bin/env python
# -*- coding: utf-8 -*-
'''
 @Time : 2020/1/1 18:08
 @Author : Jason.Jia
 @contact: jiajunp@163.com
 @Version : 1.0
 @file :mysql_write_excel.py
 @desc :
 从mysql读取数据，写入excel中
'''

import xlwt,pymysql,requests,xmltodict,json

def export_excel(t_customer_base):
    #conn = MySQLdb.connect(host='10.72.230.231', user='crm_develop', passwd='LU0nxhbFjr7uoxoQb4Tp', db='crm_dev')
    # 线上连接
    conn = pymysql.connect(user='admin',
      host='127.0.0.1',
      port=3306,
      passwd='12345',
      db='workflow',
      charset='utf8')
    # 本地连接
    #conn = pymysql.connect(user='root',host='127.0.0.1',port=3306,passwd='123456',db='crm_dev',charset='utf8')
    cur = conn.cursor()
    print("SQL开始")
    sql = 'select shop.`name`,config.flow_model_name,models.process_key,models.flow_node_json,models.activit_editor_xml from flow_models models LEFT JOIN audit_prod.cnooc_audit_config config ON config.flow_process_key = models.process_key LEFT JOIN item_prod.parana_shop shop ON shop.id = config.shop_id where models.`status` = 1;'
    print(sql)
    #读取数据
    cur.execute(sql)
    fileds = [filed[0] for filed in cur.description]
    all_date = cur.fetchall() #所有数据
    # for result in all_date:
    #     print(1)

    #写excel

    book = xlwt.Workbook() #创建一个book

    sheet = book.add_sheet('result') #创建一个sheet表

    for col,filed in enumerate(fileds):
        sheet.write(0,col,filed)

    #从第一行开始写
    row = 1
    for data in all_date:
      # 定义元组
      listdata = []
      print(data[1] is None)
      #如果此数据为空则过滤
      if data[1] is None:
        print("1")
        continue
      # xml转json
      xmldata = data[4]
      # print(xmldata)
      xmldata = json.dumps(xmltodict.parse(xmldata))
      datajson = json.loads(data[3])
      datastr = ''
      for p in datajson:
        # 便利json数据
        taskId = p['taskDefId']
        if taskId not in xmldata:
          continue
        if 'taskCandidates' not in p:
          continue
        taskCan = p['taskCandidates']
        if taskCan is None:
          continue
        taskCan = taskCan[0]
        if taskCan is None:
          continue
        # print(taskCan)

        if 'candidateFunction' not in taskCan:
          continue
        taskCan = taskCan['candidateFunction']
        if taskCan is None:
          continue
        
        taskCan = taskCan['paramList']
        if taskCan is None:
          continue
        taskCan = taskCan[1]
        if taskCan is None:
          continue
        taskCan = taskCan['candidateValues']
        if taskCan is None:
          continue
        datastr = datastr + str(taskCan)
      print("====")
      print(datastr)
      if len(datastr.strip()) == 0:
        continue
      listdata.append(data[0])
      listdata.append(data[1])
      listdata.append(data[2])
      listdata.append(datastr)
      tup = tuple(listdata)
      for col,filed in enumerate(tup):
        sheet.write(row,col,filed)
      row += 1

    # str1 = ''
    # list1 = [{"taskDefId":"Task_1ysmzpf","resultKeys":["auditResult","remark"],"urlKeys":["processInstanceId"],"taskCandidates":[{"candidateType":5,"candidateFunction":{"type":1,"callPath":"io.terminus.flow.user.function.CnoocRoleFunction.candidateShopRole","paramList":[{"paramClass":"java.lang.String","type":2,"paramName":"部门","candidateParam":"${rootOrgId}","dataType":"3"},{"paramClass":"java.util.List","type":1,"paramName":"角色","valueSequence":["5"],"candidateValues":{"seller_Sales_Executive":"气电销售主管"},"dataType":"1"}]}}]},{"taskDefId":"Task_0agiddh","resultKeys":["auditResult","remark"],"urlKeys":["processInstanceId"],"taskCandidates":[{"candidateType":5,"candidateFunction":{"type":1,"callPath":"io.terminus.flow.user.function.CnoocRoleFunction.candidateShopRole","paramList":[{"paramClass":"java.lang.String","type":2,"paramName":"部门","candidateParam":"${rootOrgId}","dataType":"3"},{"paramClass":"java.util.List","type":1,"paramName":"角色","valueSequence":["5"],"candidateValues":{"seller_Campaign_manager":"气电营销活动管理员"},"dataType":"1"}]}}]}]
    
    book.save('%s.xls' %'审批流节点角色')


if __name__ == '__main__':
    export_excel('stocks')