python解析jason串，数据存入数据库

#######################

{
"xxxx":{ "errsum": 0, "result":{ "os": { "v_message": { "check": "OK", "info": "" }, "v_space": { "check": "OK", "info": "" }, "v_indoe": { "check": "OK", "info": "" }, "v_await": { "check": "OK", "info": "" }, "v_multipath": { "check": "OK", "info": "" }, "v_pagetable": { "check": "OK", "info": "" }, "v_process": { "check": "OK", "info": "" }, "v_osload": { "check": "OK", "info": "" } },"xxx1": { "v_connect": { "check":"OK", "info":"" }, "v_topgets": { "check":"v_topgets_c", "info":"v_topgets_i" }, "v_dbload": { "check":"OK", "info":"" }, "v_event": { "check":"OK", "info":"" }, "v_sesslong": { "check":"OK", "info":"" }, "v_archiveavg": { "check":"OK", "info":"" }, "v_archive":{ "check":"OK", "info":"" }, "v_alert": { "check":"OK", "info":"" } },"cluster": { "v_crsstatus": { "check": "OK", "info": "" }, "v_instancetatus": { "check": "OK", "info": "" }, "v_diskgroup": { "check": "OK", "info": "" }, "v_disk": { "check":"OK", "info":"" }}}}
}

#########################

import json
import jaydebeapi
import datetime
import sys
reload(sys)
sys.setdefaultencoding('UTF-8')


def jdbc_connect(url, user, password, jarFile):
    return jaydebeapi.connect("oracle.jdbc.driver.OracleDriver", url, [user, password], jarFile)


with open('D://result.json.2019121215') as fp:
    fp1 = fp.read().decode("utf-8","ignore")
    data = json.loads(fp1)

dt = datetime.datetime.now().strftime("%Y%m%d %H:%M:%S")

conn = jdbc_connect('jdbc:oracle:thin:@192.168.0.15:1521/orcl', 'system', 'oracle',
                    'D://pdi-ce-6.0.1.0-386//ojdbc6.jar')

for host in data.keys():
    for key in data[host]["result"].keys():
        if key == 'os':
            sql = "insert into scott.xuanjian_host (hostname,errsum,v_message,v_space,v_indoe,v_await,v_multipath," \
                  "v_pagetable,v_process,v_osload,creation_time) " \
                   "values (?,?, ?,?,?,?,?,?,?,?,?)"
            try:
                cur = conn.cursor()
                cur.execute(sql,(str(host), str(data[host]["errsum"]), str(data[host]["result"][key]["v_message"]["info"])[:3000],
                     str(data[host]["result"][key]["v_space"]["info"]), str(data[host]["result"][key]["v_indoe"]["info"]),
                     str(data[host]["result"][key]["v_await"]["info"]), str(data[host]["result"][key]["v_multipath"]["info"]),
                     str(data[host]["result"][key]["v_pagetable"]["info"]),str(data[host]["result"][key]["v_process"]["info"]),
                     str(data[host]["result"][key]["v_osload"]["info"]), dt
                     ))
            except Exception as e:
                # print host
                # print key
                raise e
                conn.rollback()
        elif key == 'cluster':
            sql = "insert into scott.xunjian_crs (hostname,v_crsstatus,v_instancetatus,v_diskgroup,v_disk,creation_time)" \
                  "values (?,?,?,?,?,?)"
            try:
                cur = conn.cursor()
                cur.execute(sql,
                            (str(host), str(data[host]["result"][key]["v_crsstatus"]["info"]),
                             str(data[host]["result"][key]["v_instancetatus"]["info"]),
                             str(data[host]["result"][key]["v_diskgroup"]["info"]),
                             str(data[host]["result"][key]["v_disk"]["info"])
                             , dt
                             ))
            except Exception as e:
                # print host
                # print key
                raise e
                conn.rollback()
        else:
            sql = "insert into scott.xunjian_database (hostname,instance_name,v_connect,v_topgets,v_dbload,v_event" \
                  ",v_sesslong,v_archiveavg,v_archive,v_alert,creation_time)" \
                  "values (?,?,?,?,?,?,?,?,?,?,?)"
            try:
                cur = conn.cursor()
                cur.execute(sql,
                            (str(host),key, str(data[host]["result"][key]["v_connect"]["info"]),
                             str(data[host]["result"][key]["v_topgets"]["info"]),
                             str(data[host]["result"][key]["v_dbload"]["info"]),
                             str(data[host]["result"][key]["v_event"]["info"]),
                             str(data[host]["result"][key]["v_sesslong"]["info"]),
                             str(data[host]["result"][key]["v_archiveavg"]["info"]),
                             str(data[host]["result"][key]["v_archive"]["info"]),
                             str(data[host]["result"][key]["v_alert"]["info"]),
                             dt
                             ))
            except Exception as e:
                # print host
                # print key
                raise e
                conn.rollback()

conn.close()