调用阿里云数据库监控数据返回的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

Logo

华为开发者空间,是为全球开发者打造的专属开发空间,汇聚了华为优质开发资源及工具,致力于让每一位开发者拥有一台云主机,基于华为根生态开发、创新。

更多推荐