python取mysql数据写入excel

环境：MySQLdb openpyxl模块

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('')

  Out = get_data('')

  tunple = In + Out

  write(6,12)

  In1 = get_data('')

  Out1 = get_data('')

  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 = "[email protected]"

_pwd = "9063667243hp"

_to  = "[email protected]"

#如名字所示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()