调用阿里云数据库监控数据返回的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
所有评论(0)