python小工具-监控GreenPlum数据量超出阈值使用zabbix发送企业微信报警

# coding=utf-8
from __future__ import division
import sys
reload(sys)
sys.setdefaultencoding( "utf-8" )
import psycopg2
import datetime
import pymysql
import requests
import time
from prettytable import PrettyTable

# 今日日期
dt = datetime.date.today()
print(dt)
# 昨日日期
ydt = datetime.date.today() + datetime.timedelta(-1)
print(ydt)
# 开始时间
begtime = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
print(begtime)

# 发送到zabbix
# zabbix config
tokenUrl = 'your tokenUrl '
sendMsg = 'your sendMsg '
corpid = 'your corpid '
corpsecret = 'your corpsecret '
agentid = '1000010'


def get_token():
    values = {'corpid': corpid, 'corpsecret': corpsecret}
    req = requests.post(tokenUrl, params=values).json()
    return req["access_token"]

def send_msg(msg):
    url = sendMsg + get_token()
    # print url
    values = """{"touser" : "your work wechat name" ,
      "msgtype":"text",
      "agentid":"%s",
      "text":{
        "content": "%s"
      },
      "safe":"0"
      }""" % (agentid, msg)
    requests.post(url, values)

s=0
flag='true'
while(flag):
    #连接MySQL
    db1 = pymysql.connect(host="ip",port=port, user="user",password="password",database="database",charset="utf8")
    cursor = db1.cursor()
    #查询状态
    sql3="SELECT status FROM tb1 WHERE project='1'"
    cursor.execute(sql3)
    db1.close
    data=cursor.fetchone()
    if(data[0]!="SUCCEEDED"):
        time.sleep(180)
        print("查询中...")
        s=s+180
        if(s==18000):
            sys.exit()
    elif(data[0]=="SUCCEEDED"):
        print(data[0])
        break    
# 连接Greenplum
conn = psycopg2.connect(dbname="dbname", user="user", password="password", port="port", host="ip")
cur = conn.cursor()
sql = """
    select count(1) from order
"""
cur.execute(sql)
res = cur.fetchall()

# 更新时间
update_time = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')

# 连接MySQL
db = pymysql.connect(host="ip",port=port, user="user",password="password",database="database",charset="utf8")
cursor = db.cursor()
#统计数据插入到监控查询表中
sql = 'insert into monitor(dt,prov,tbl,t_rownum,y_rownum,inc_percent,status,update_time) values (%s,%s,%s,%s,%s,%s,%s,%s)'
#查询昨天的数据量
sql2='select count(1) from order where dt=date_sub(current_date,interval 1 day))'
cursor.execute(sql2)
data=cursor.fetchall()
#各省公司数据较昨日差值
inc_percent0=str(round((int(res[0][2])-int(data[0][1]))/int(res[0][2]),4)*100)
if (inc_percent0>'0.75' or inc_percent0<'0.11'):
    status0=0
else:
    status0=1
print(inc_percent0)
print(status0)

inc_percent1=str(round((int(res[1][2])-int(data[1][1]))/int(res[1][2]),4)*100)
if (inc_percent1>'1.74' or inc_percent1<'0.18'):
    status1=0
else:
    status1=1
print(status1)

inc_percent2=str(round((int(res[2][2])-int(data[2][1]))/int(res[2][2]),4)*100)
if (inc_percent2>'1.23' or inc_percent2<'0.20'):
    status2=0
else:
    status2=1

inc_percent3=str(round((int(res[3][2])-int(data[3][1]))/int(res[3][2]),4)*100)
if (inc_percent3>'0.71' or inc_percent3<'0.10'):
    status3=0
else:
    status3=1
inc_percent4=str(round((int(res[4][2])-int(data[4][1]))/int(res[4][2]),4)*100)
if (inc_percent4>'0.74' or inc_percent4<'0.13'):
    status4=0
else:
    status4=1
inc_percent5=str(round((int(res[5][2])-int(data[5][1]))/int(res[5][2]),4)*100)
if (inc_percent5>'0.56' or inc_percent5<'0.06'):
    status5=0
else:
    status5=1
inc_percent6=str(round((int(res[6][2])-int(data[6][1]))/int(res[6][2]),4)*100)
if (inc_percent6>'0.40' or inc_percent6<'0.12'):
    status6=0
else:
    status6=1
inc_percent7=str(round((int(res[7][2])-int(data[7][1]))/int(res[7][2]),4)*100)
if (inc_percent7>'0.18' or inc_percent7<'0.03'):
    status7=0
else:
    status7=1
inc_percent8=str(round((int(res[8][2])-int(data[8][1]))/int(res[8][2]),4)*100)
if (inc_percent8>'0.22' or inc_percent8<'0.09'):
    status8=0
else:
    status8=1
inc_percent9=str(round((int(res[9][2])-int(data[9][1]))/int(res[9][2]),4)*100)
if (inc_percent9>'0.27' or inc_percent9<'0.11'):
    status9=0
else:
    status9=1
inc_percent10=str(round((int(res[10][2])-int(data[10][1]))/int(res[10][2]),4)*100)
if (inc_percent10>'0.17' or inc_percent10<'0.05'):
    status10=0
else:
    status10=1
inc_percent11=str(round((int(res[11][2])-int(data[11][1]))/int(res[11][2]),4)*100)
if (inc_percent11>'0.22' or inc_percent11<'0.06'):
    status11=0
else:
    status11=1
inc_percent12=str(round((int(res[12][2])-int(data[12][1]))/int(res[12][2]),4)*100)
if (inc_percent12>'0.20' or inc_percent12<'0.06'):
    status12=0
else:
    status12=1
inc_percent13=str(round((int(res[13][2])-int(data[13][1]))/int(res[13][2]),4)*100)
if (inc_percent13>'0.31' or inc_percent13<'0.08'):
    status13=0
else:
    status13=1
inc_percent14=str(round((int(res[14][2])-int(data[14][1]))/int(res[14][2]),4)*100)
if (inc_percent14>'0.37' or inc_percent14<'0.13'):
    status14=0
else:
    status14=1
inc_percent15=str(round((int(res[15][2])-int(data[15][1]))/int(res[15][2]),4)*100)
if (inc_percent15>'0.89' or inc_percent15<'0.24'):
    status15=0
else:
    status15=1
inc_percent16=str(round((int(res[16][2])-int(data[16][1]))/int(res[16][2]),4)*100)
if (inc_percent16>'0.34' or inc_percent16<'0.15'):
    status16=0
else:
    status16=1
inc_percent17=str(round((int(res[17][2])-int(data[17][1]))/int(res[17][2]),4)*100)
if (inc_percent17>'0.28' or inc_percent17<'0.13'):
    status17=0
else:
    status17=1

val = [
    (dt, res[0][0], res[0][1], res[0][2],data[0][1],inc_percent0+"%", status0,update_time),
    (dt, res[1][0], res[1][1], res[1][2],data[1][1],inc_percent1+"%", status1,update_time),
    (dt, res[2][0], res[2][1], res[2][2],data[2][1],inc_percent2+"%", status2,update_time),
    (dt, res[3][0], res[3][1], res[3][2],data[3][1],inc_percent3+"%", status3,update_time),
    (dt, res[4][0], res[4][1], res[4][2],data[4][1],inc_percent4+"%", status4,update_time),
    (dt, res[5][0], res[5][1], res[5][2],data[5][1],inc_percent5+"%", status5,update_time),
    (dt, res[6][0], res[6][1], res[6][2],data[6][1],inc_percent6+"%", status6,update_time),
    (dt, res[7][0], res[7][1], res[7][2],data[7][1],inc_percent7+"%", status7,update_time),
    (dt, res[8][0], res[8][1], res[8][2],data[8][1],inc_percent8+"%", status8,update_time),
    (dt, res[9][0], res[9][1], res[9][2],data[9][1],inc_percent9+"%", status9,update_time),
    (dt, res[10][0], res[10][1], res[10][2],data[10][1],inc_percent10+"%", status10,update_time),
    (dt, res[11][0], res[11][1], res[11][2],data[11][1],inc_percent11+"%", status11,update_time),
    (dt, res[12][0], res[12][1], res[12][2],data[12][1],inc_percent12+"%", status12,update_time),
    (dt, res[13][0], res[13][1], res[13][2],data[13][1],inc_percent13+"%", status13,update_time),
    (dt, res[14][0], res[14][1], res[14][2],data[14][1],inc_percent14+"%", status14,update_time),
    (dt, res[15][0], res[15][1], res[15][2],data[15][1],inc_percent15+"%", status15,update_time),
    (dt, res[16][0], res[16][1], res[16][2],data[16][1],inc_percent16+"%", status16,update_time),
    (dt, res[17][0], res[17][1], res[17][2],data[17][1],inc_percent17+"%", status17,update_time)
]
try:
    cursor.executemany(sql, val)
    db.commit()
    print(cursor.rowcount, "inserted successfully")
except:
    # 回滚事务
    db.rollback()
#查询输出数据
sql3="select prov,inc_percent from gp_data_monitor where dt=current_date and update_time in (select max(update_time) as update_time from gp_data_monitor where dt=current_date) and status='0' "
cursor.execute(sql3)
results=cursor.fetchall()
db.close()
len1=len(results)
print(len1)


if len1==0:
    send_msg("Greenplum monitor done"+"\nTime : "+update_time+"\n订单量正常")
else:
    row = PrettyTable()
    row.field_names = ["prov","inc_percent"]
    for r in results:
        row.add_row(r)
    row_fmt=row.get_string(sortby="prov", reversesort=True)
    print(row_fmt)
    send_msg("Greenplum monitor done"+"\nTime : "+update_time+"\n以下订单异常，请验证\n\n"+str(row_fmt))