python取mysql数据写入excel

环境：MySQLdb openypyxl模块

python去zabbix的mysql数据库中取交换机不同时间段的进出口流量，然后写入excel中，每天cron执行，每周四邮件发送。（代码中第一行必须加上，不然crontab执行不了，或者在crontab时指明环境变量）

#!/usr/local/bin/python2.7
#_*_coding:utf-8_*_
import MySQLdb
import time
from datetime import datetime
from datetime import timedelta
from openpyxl import Workbook
from openpyxl import load_workbook

#根据系统时间选不同时间段
now = datetime.now()
now2 = now.strftime('%H:%M')
if now2 == '19:00':
  a = timedelta(hours=-10.5)
  Column = 8
elif now2 == '23:59':
  a = timedelta(hours=-5)
  Column = 14
else:
  a = timedelta(hours=-8.5)
  Column = 2
Column1 = Column
before = now + a
now = now.strftime('%Y-%m-%d %H:%M')
before = before.strftime('%Y-%m-%d %H:%M')


#去mysql中取数据
def get_data(id):
  conn = MySQLdb.connect(host='10.125.2.31',user='zabbix',passwd='zabbix',db='zabbix')
  cur = conn.cursor()

  sql = "select min(round(value/1000/1000,2)) as MIN,avg(round(value/1000/1000,2)) as AVG,max(round(value/1000/1000,2)) as MAX from history where itemid=%s and from_unixtime(clock)>%s and from_unixtime(clock)<%s  "
  params = (id,before,now) 
  reCount = cur.execute(sql,params)
  data = cur.fetchone()

  cur.close()
  conn.close()
  return data



#写入excel
wb = load_workbook("/home/hongpeng/network/network_flow.xlsx")#打开excel
ws = wb.get_sheet_by_name('IDC')#打开sheet‘IDC流量’
def write(begin,over,Column1 = Column):
  #取出第一列中每一行的数据，判断写入位置
  b = []
  for row_list in range(begin,over+1):
    a = ws.cell(row = row_list,column = Column).value
    row_list += 1
    b.append(a)
  #判断第一列每一行是否为空，为空就退出for循环,找到插入位置
  for i in range(len(b)):
    if not b[i]:
      Row = begin+i
      break
  for k,v in enumerate(tunple):
    pass
    if Column1 < Column+8:
      ws.cell(row=Row, column=Column1).value = tunple[k]
      Column1 += 1
  wb.save('/home/hongpeng/network/network_flow.xlsx')
    
if __name__ == '__main__':
  In = get_data('212535')
  Out = get_data('212583')
  tunple = In + Out
  write(6,12)
  In1 = get_data('231087')
  Out1 = get_data('231671')
  tunple = In1+Out1
  write(18,24)

 

#!/bin/bash
DATE=$(date +%Y-%m-%d)
cp -p /home/hongpeng/network/network_flow.xlsx /home/hongpeng/bak/network_flow_$DATE.xlsx
rm -f /home/hongpeng/network/network_flow.xlsx
cp -p /home/hongpeng/bak/network_flow.xlsx /home/hongpeng/network

 

发送邮件代码

#!/usr/local/bin/python2.7
#_*_coding:utf-8_*_
__author__ = 'hongpeng'
import smtplib
from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText
from email.mime.application import MIMEApplication
_user = "13522515079@163.com"
_pwd = "9063667243hp"
_to  = "1058552658@qq.com"



#如名字所示Multipart就是分多个部分
msg = MIMEMultipart()
msg["Subject"] = "network_flow"
msg["From"]  = _user
msg["To"]   = _to

#---这是文字部分---
part = MIMEText("网络流量见附件")
msg.attach(part)

#xlsx类型附件
part = MIMEApplication(open('/home/hongpeng/network/network_flow.xlsx','rb').read())
part.add_header('Content-Disposition', 'attachment', filename="network_flow.xlsx")
msg.attach(part)

s = smtplib.SMTP("smtp.163.com", timeout=30)#连接smtp邮件服务器,端口默认是25
s.login(_user, _pwd)#登陆服务器
s.sendmail(_user, _to, msg.as_string())#发送邮件
s.close()