# openfalcon 通过python查询mysql数据上报
# 思路
主要是用python获取mysql数据，进行统计上报
# python脚本
python 包 需要自行 通过 pip 安装
#!-*- coding:utf8 -*-
## author:liuwenqi
## date: 2018-06-18
import requests
import time
import json
import pymysql
from decimal import *
db_config = {
'host': 'IP', ## 数据库地址
'port': 3306, ## 数据库端口
'user': 'user', ## 数据库名称
'password': 'password', ## 数据库地址
'db': 'db_name', ## 数据库名
'charset': 'utf8',
}
sql_get_order_10min_true='SQL'
sql_get_order_10min_false='SQL'
sql_get_order_yesterday_true='SQL'
sql_get_order_lastweek_true='SQL'
sql_get_order_yesterday_false='SQL'
sql_get_order_lastweek_false='SQL'
sql_get_order_one_min_true='SQL'
def sql_check(order_type):
conn = pymysql.connect(**db_config)
cur = conn.cursor()
if order_type == 0:
sql = sql_get_order_10min_true
elif order_type == 1:
sql = sql_get_order_10min_false
elif order_type == 2:
sql = sql_get_order_yesterday_true
elif order_type == 3:
sql = sql_get_order_yesterday_false
elif order_type == 4:
sql = sql_get_order_lastweek_true
elif order_type == 5:
sql = sql_get_order_one_min_true
else:
sql = sql_get_order_lastweek_false
rv = cur.execute(sql)
res = cur.fetchall()
cur.close()
return int(res[0][0])
def count_success_percent(success,fail):
success_percent_data = Decimal(success)/(Decimal(success) + Decimal(fail))*100
success_percent = round(success_percent_data,3)
return success_percent
ts = int(time.time())
order_today_success = sql_check(0)
order_today_fail = sql_check(1)
order_yesterday_success= sql_check(2)
order_yesterday_fail = sql_check(3)
order_lastweek_success = sql_check(4)
order_lastweek_fail = sql_check(6)
order_min_true = sql_check(5)
today_success_percent = count_success_percent(order_today_success,order_today_fail)
yesterday_success_percent = count_success_percent(order_yesterday_success,order_yesterday_fail)
payload = [
{
"endpoint": "order_data",
"metric": "今日订单成功数",
"timestamp": ts,
"step": 60,
"value": order_today_success,
"counterType": "GAUGE",
"tags": "",
},
{
"endpoint": "order_data",
"metric": "昨日订单成功数",
"timestamp": ts,
"step": 60,
"value": order_yesterday_success,
"counterType": "GAUGE",
"tags": "",
},
{
"endpoint": "order_data",
"metric": "上周订单成功数",
"timestamp": ts,
"step": 60,
"value": order_lastweek_success,
"counterType": "GAUGE",
"tags": "",
},
{
"endpoint": "order_data",
"metric": "today_order_count_fail",
"timestamp": ts,
"step": 60,
"value": order_today_fail,
"counterType": "GAUGE",
"tags": "",
},
{
"endpoint": "order_data",
"metric": "yesterday_order_count_fail",
"timestamp": ts,
"step": 60,
"value": order_yesterday_fail,
"counterType": "GAUGE",
"tags": "",
},
{
"endpoint": "order_data",
"metric": "lastweek_order_count_fail",
"timestamp": ts,
"step": 60,
"value": order_lastweek_fail,
"counterType": "GAUGE",
"tags": "",
},
{
"endpoint": "order_data",
"metric": "今日订单成功率",
"timestamp": ts,
"step": 60,
"value": today_success_percent,
"counterType": "GAUGE",
"tags": "",
},
{
"endpoint": "order_data",
"metric": "昨日订单成功率",
"timestamp": ts,
"step": 60,
"value": yesterday_success_percent,
"counterType": "GAUGE",
"tags": "",
},
{
"endpoint": "order_data",
"metric": "每分钟订单成功数",
"timestamp": ts,
"step": 60,
"value": order_min_true,
"counterType": "GAUGE",
"tags": "",
}
]
r = requests.post("http://127.0.0.1:1988/v1/push", data=json.dumps(payload))
print r.text
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
# 效果图
openfalcon screen
Grafana
