python实现定时发送mysql数据库报表excel

1、准备好sql

select min(t.datetime2) tt,t.ip,t.hostname,group_concat(t.groupname),t.problem from 
(SELECT     a.name problem ,from_unixtime(a.clock) datetime2, 
a.objectid, b.itemid, c.hostid, d.ip, e.name hostname,h.name groupname FROM  
problem a,  functions b,  items c,  
interface d,  hosts e,  triggers f,  hosts_groups g, `hstgrp` h 
WHERE    a.objectid = b.triggerid        AND b.itemid = c.itemid       
  AND d.hostid = c.hostid         AND e.hostid = d.hostid        
   and d.hostid = g.hostid         and h.groupid=g.groupid    
        and a.objectid=f.triggerid         and a.r_eventid is  null )t 
         group by problem,objectid,itemid,hostid,ip,hostname         order by tt desc

2、准备python脚本
需注意环境xlwt模块 安装此模块的时候我是指定版本才安装上的 pip3 install xlwt==1.3.0
上脚本

import  smtplib
from email.mime.text import MIMEText

import email.mime.multipart
import email.mime.text
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart
from email.mime.application import MIMEApplication
import os,time
import pymysql
import xlwt

def sendmail(content):
    day= time.strftime('%Y-%m-%d',time.localtime())
    form_adder='xxx'
    password='xxx'
    to_adder=['xxx','XXX']
    smtp_server=('xxx')

    msg=email.mime.multipart.MIMEMultipart()
    msg['From']=form_adder
    msg['To']=','.join(to_adder)
    msg['subject']='zabbix warning'
    txt = email.mime.text.MIMEText(content, 'plain', 'utf-8')
    msg.attach(txt)
    file=r"/root/problem."+day+".xls"
    part = MIMEApplication(open(file, 'rb').read())
    part.add_header('Content-Disposition', 'attachment', filename="problem."+day+".xls")
    msg.attach(part)

    ret=True
    try:
        server=smtplib.SMTP(smtp_server,25)
        server.login(form_adder,password)
        server.sendmail(form_adder,to_adder,msg.as_string())
        server.quit()
    except Exception as e:
        print(e)
        ret=False
    return ret

def getFile():
    day= time.strftime('%Y-%m-%d',time.localtime())
    cmd= r"mysql -hxxx -uxxx-pxxx xxx < /root/problem.sql >/root/problem."+day+".csv"
    os.system(cmd)

def getExcel():
    conn1=pymysql.connect(host='x.x.x.x',user='xxx',password='xxx',database='xx', charset='utf8')
    sql1="select min(t.datetime2) tt,t.ip,t.hostname,group_concat(t.groupname),t.problem from \
(SELECT     a.name problem ,from_unixtime(a.clock) datetime2, a.objectid, b.itemid, c.hostid, d.ip, \
e.name hostname,h.name groupname FROM  problem a,  functions b,  items c,  interface d,  hosts e,  triggers f,  hosts_groups g, `hstgrp` h WHERE    a.objectid = b.triggerid \
        AND b.itemid = c.itemid \
        AND d.hostid = c.hostid \
        AND e.hostid = d.hostid \
        and d.hostid = g.hostid \
        and h.groupid=g.groupid \
        and a.objectid=f.triggerid \
        and a.r_eventid is  null )t \
        group by problem,objectid,itemid,hostid,ip,hostname \
        order by tt desc "

    conn2=pymysql.connect(host='x.x.x.x',user='xx',password='xx',database='xx', charset='utf8')
    sql2="select min(t.datetime2) tt,t.ip,t.hostname,group_concat(t.groupname),t.problem from \
(SELECT     a.name problem ,from_unixtime(a.clock) datetime2, a.objectid, b.itemid, c.hostid, d.ip, \
e.name hostname,h.name groupname FROM  problem a,  functions b,  items c,  interface d,  hosts e,  triggers f,  hosts_groups g, `groups` h WHERE    a.objectid = b.triggerid \
        AND b.itemid = c.itemid \
        AND d.hostid = c.hostid \
        AND e.hostid = d.hostid \
        and d.hostid = g.hostid \
        and h.groupid=g.groupid \
        and a.objectid=f.triggerid \
        and a.r_eventid is  null )t \
        group by problem,objectid,itemid,hostid,ip,hostname \
        order by tt desc "



    cursor=conn2.cursor()
    cursor.execute(sql2)
    result=cursor.fetchall()
   
    
    #cursor.description获取表格的字段信息
    fields=cursor.description
    
    cursor.close()
    conn2.close()
    
    #创建一个新的sheet
    workbook = xlwt.Workbook()
    sheet=workbook.add_sheet('sheet1',cell_overwrite_ok=True)
    style=xlwt.XFStyle()
    style.num_format_str='YYYY-MM-D h:mm:ss'  # Other options: D-MMM-YY, D-MMM, MMM-YY, h:mm, h:mm:ss, h:mm, h:mm:ss, M/D/YY h:mm, mm:ss, [h]:mm:ss, mm:ss.0

    #将表的字段名写入excel
    for field in range(len(fields)):
          sheet.write(0,field,fields[field][0])
    
    #结果写入excle
    for row in range(1,len(result)+1):
         for col in range(len(fields)):
              if col == 0:
                 sheet.write(row,col,result[row-1][col],style)  ###第一列为时间格式，设置为日期时间格式
              else :
                 sheet.write(row,col,result[row-1][col])
    day= time.strftime('%Y-%m-%d',time.localtime())
    file=r'/root/problem.'+day+'.xls'
    workbook.save(file)


getExcel()
sendmail('zabbix notice')

3、shell脚本
python3 /root/sendmail2.py

4、crontab -l
30 8 * * * sh /root/sendemail_zbx_problem.sh