Python将json转化为sql语句

调用阿里云数据库监控数据返回的json数据

基本思路

• 获取返回json数据
• 循环PerformanceKeys/PerformanceKey键内array
• 将key作为表名
• 将其它键作为属性名,并额外添加Date属性
• 循环Values/PerformanceValue，将Value和Date值存入数组
• 拼接字符串转化成插入sql语句

json格式

原代码

#案例数据
apidate={"DBInstanceId":"rm-test","RequestId":"test","PerformanceKeys":{"PerformanceKey":[{"Values":{"PerformanceValue":[{"Value":"2624&415&5&0&2204","Date":"2018-02-23T08:00:32Z"},{"Value":"2624&415&5&0&2204","Date":"2018-02-23T08:05:32Z"},{"Value":"2624&415&5&0&2204","Date":"2018-02-23T08:10:32Z"},{"Value":"2624&415&5&0&2204","Date":"2018-02-23T08:15:32Z"},{"Value":"2624&415&5&0&2204","Date":"2018-02-23T08:20:33Z"},{"Value":"2624&415&5&0&2204","Date":"2018-02-23T08:25:33Z"}]},"Key":"MySQL_DetailedSpaceUsage","Unit":"MB","ValueFormat":"ins_size&data_size&log_size&tmp_size&other_size"},{"Values":{"PerformanceValue":[{"Value":"0.28","Date":"2018-02-23T08:00:32Z"},{"Value":"0.26","Date":"2018-02-23T08:05:32Z"},{"Value":"0.26","Date":"2018-02-23T08:10:32Z"},{"Value":"0.38","Date":"2018-02-23T08:15:32Z"},{"Value":"0.28","Date":"2018-02-23T08:20:33Z"},{"Value":"0.36","Date":"2018-02-23T08:25:33Z"}]},"Key":"MySQL_IOPS","Unit":"int","ValueFormat":"io"}]},"EndTime":"2018-02-23T09:41Z","StartTime":"2018-02-23T08:00Z","Engine":"MySQL"}
apikey=apidate['PerformanceKeys']['PerformanceKey']
for i in apikey:
    #定义表名
    tablename=i["Key"]
    #定义列名
    tablecolumn=i['ValueFormat'].replace("&",",")+",date,unit"
    valueunit=i['Unit']
    sqlhalf="INSERT INTO "+tablename+" ("+tablecolumn+") VALUES"
    apivalues=i['Values']['PerformanceValue']
    valuelist=[]
    for i in  apivalues:
        tablevalue="("+i['Value'].replace("&",",")+",'"+i['Date'].replace("T"," ").replace("Z"," ")+"','"+valueunit+"')"
        valuelist.append(tablevalue)
    sql=sqlhalf+",".join(valuelist)+";"
    print(sql)

参考文献

实例性能数据返回参数

关于我

一个想到啥学啥的运维工程师
一个想靠手艺吃饭的人
一个能聊聊天的朋友
个人网站：http://sosad.cn