python根据excel生成mysql表_使用Python查询MySQL数据库生成Excel文件发送监控周报。...

业务方要求每周发一封周报出来，将过去一周的线上项目的详细信息发送出来，我们的监控用的是zabbix，过去一直是手动填写，非常耗时耗力，而且显得非常不专业，所以我花了几个月时间学习Python，编写如下脚本供大家参考，望大神勿笑。

#!/usr/bin/env python

#-*- coding: utf8 -*-

import MySQLdb

import string

import xlsxwriter

import time,datetime

import sys

reload(sys)

sys.setdefaultencoding('utf-8')

zdbhost = ' '

zdbuser = ' '

zdbpass = ' '

zdbport =

zdbname = ' '

#时间戳 取当前时间和7天前的时间

stop_time = int(time.time())

start_time = stop_time - 604800

date = time.strftime("%Y-%m-%d",time.localtime(stop_time))

xlsfilename = '%s业务周报.xlsx' % date

#keys格式：itemname itemid type 格式化 groupid

keys = [

[

['UV/天','167365','max', '%.2f', 1,'48',],

['PV/周','167359','max', '%.2f', 1,'48',],

['最大并发','167364','max', '%.2f', 1,'48',],

['最大并发发生时间','167364','max', '%.2f', 1,'48',],

['平均QPS','167361','avg', '%.2f', 1,'48'],

['最大QPS','167361','max', '%.2f', 1,'48'],

['最大QPS发生时间','167361','max', '%.2f', 1,'48'],

['最大带宽(in)','81291','max', '%.2f', '1048576','48'],

['平均带宽(in)','81291','avg', '%.2f', '1048576','48'],

['最大带宽(out)','81292','max', '%.2f', '1048576','48'],

['平均带宽(out)','81292','avg', '%.2f', '1048576','48']

],

[

['UV/天','168141','max', '%.2f', 1,'16'],

['PV/周','168135','max', '%.2f', 1,'16'],

['最大并发','168140','max', '%.2f', 1,'16'],

['最大并发发生时间','168140','max', '%.2f', 1,'16',''],

['平均QPS','168137','avg', '%.2f', 1,'16'],

['最大QPS','168137','max', '%.2f', 1,'16'],

['最大QPS发生时间','168137','max', '%.2f', 1,'16'],

['最大带宽(in)', '104522','max', '%.2f', '1048576','16'],

['平均带宽(in)', '104522','avg', '%.2f', '1048576','16'],

['最大带宽(out)', '104523','max', '%.2f', '1048576','16'],

['平均带宽(out)', '104523','avg', '%.2f', '1048576','16']

],

[

['UV/天', '163672','max', '%.2f', 1,'71'],

['PV/周', '163666','max', '%.2f', 1,'71'],

['最大并发', '163671','max', '%.2f', 1,'71'],

['最大并发发生时间', '163671','max', '%.2f', 1,'71',''],

['平均QPS','163668','avg', '%.2f', 1,'71'],

['最大QPS','163668','max', '%.2f', 1,'71'],

['最大QPS发生时间','163668','max', '%.2f', 1,'71'],

['最大带宽(in)', '84942','max', '%.2f', '1048576','71'],

['平均带宽(in)', '84942','avg', '%.2f', '1048576','71'],

['最大带宽(out)', '84943','max', '%.2f', '1048576','71'],

['平均带宽(out)', '84943','avg', '%.2f', '1048576','71']

],

[

['UV/天', '154707','max', '%.2f', 1,'132'],

['PV/周', '154722','max', '%.2f', 1,'132'],

['最大并发', '154706','max', '%.2f', 1,'132'],

['最大并发发生时间', '154706','max', '%.2f', 1,'132',''],

['平均QPS','154703','avg', '%.2f', 1,'132'],

['最大QPS','154703','max', '%.2f', 1,'132'],

['最大QPS发生时间','154703','max', '%.2f', 1,'132'],

['最大带宽(in)', '152400','max', '%.2f', '1048576','132'],

['平均带宽(in)', '152400','avg', '%.2f', '1048576','132'],

['最大带宽(out)', '152402','max', '%.2f', '1048576','132'],

['平均带宽(out)', '152402','avg', '%.2f', '1048576','132']

],

[

['UV/天', '189559','max', '%.2f', 1,'31'],

['PV/周', '189553','max', '%.2f', 1,'31'],

['最大并发', '189558','max', '%.2f', 1,'31'],

['最大并发发生时间', '189558','max', '%.2f', 1,'31',''],

['平均QPS','189555','avg', '%.2f', 1,'31'],

['最大QPS','189555','max', '%.2f', 1,'31'],

['最大QPS发生时间','189555','max', '%.2f', 1,'31'],

['最大带宽(in)', '190757','max', '%.2f', '1048576','31'],

['平均带宽(in)', '190757','avg', '%.2f', '1048576','31'],

['最大带宽(out)', '190758','max', '%.2f', '1048576','31'],

['平均带宽(out)', '190758','avg', '%.2f', '1048576','31']

],

[

['UV/天','188642','max', '%.2f', 1,'50'],

['PV/周','188636','max', '%.2f', 1,'50'],

['最大并发','188641','max', '%.2f', 1,'50'],

['最大并发发生时间','188641','max', '%.2f', 1,'50',''],

['平均QPS','188638','avg', '%.2f', 1,'50'],

['最大QPS','188638','max', '%.2f', 1,'50'],

['最大QPS发生时间','188638','max', '%.2f', 1,'50'],

['最大带宽(in)', '33707','max', '%.2f', '1048576','50'],

['平均带宽(in)', '33707','avg', '%.2f', '1048576','50'],

['最大带宽(out)', '33712','max', '%.2f', '1048576','50'],

['平均带宽(out)', '33712','avg', '%.2f', '1048576','50']

]

]

def report():

'''打开数据库连接'''

conn = MySQLdb.connect(host=zdbhost,user=zdbuser,passwd=zdbpass,port=zdbport,db=zdbname,charset='utf8')

cursor = conn.cursor(cursorclass=MySQLdb.cursors.DictCursor)

i = 2

group = {"132":"微信公众号", "71":"微信电视", "16":"chiq3音视频", "48":"launcher", "50":"个性化推荐", "31":"语义云"}

value1 = {}

value = {}

#创建文件

workbook = xlsxwriter.Workbook(xlsfilename)

#定义Excel中的格式，参考http://xlsxwriter.readthedocs.io/

merge_format = workbook.add_format({

'bold': True,

'border': True,

'align': 'center',

'valign': 'vcenter',

'fg_color': '#D7E4BC',

})

merge_format1 = workbook.add_format({

'border': True,

'align': 'center',

'valign': 'vcenter',

'text_wrap': True,

})

#创建工作薄

worksheet = workbook.add_worksheet()

worksheet.set_column('A:L',11)

#写入第一行

worksheet.merge_range('A1:L1', '业务监控周报(%s)'.decode('utf-8') % date, merge_format)

#写入其他行

worksheet.write(1,0,'项目名称'.decode('utf-8'),merge_format1)

for targets in keys:

groupid = targets[0][5]

worksheet.write(i,0,group[groupid].decode('utf-8'),merge_format1)

j = 1

for target in targets:

item = target[0]

itemid = target[1]

sql2 = '''select %s(value_%s) as result from trends_uint where itemid = %s and clock >= %s''' % (target[2], target[2], itemid, start_time)

try:

cursor.execute(sql2)

result2 = cursor.fetchone()['result']

except MySQLdb.OperationalError:

result2 = "Not monitored"

if target[0] in ['最大QPS发生时间','最大并发发生时间']:

#sql3 = '''select clock from trends_uint where itemid = %s and value_%s = %s and clock >= %s and clock <= %s ''' % (target[1], target[2], result2, start_time, stop_time)

sql3 = '''select clock from trends_uint where itemid = %s and value_%s = %s and clock >= %s limit 1''' % (target[1], target[2], result2, start_time)

try:

cursor.execute(sql3)

result3 = cursor.fetchone()['clock']

result2 = time.strftime("%Y-%m-%d %H:%M:%S",time.localtime(result3))

except MySQLdb.OperationalError:

result2 = "Not monitored"

#处理数据

if not result2:

result2 = "Not monitored"

if type(result2) is str:

result = result2

else:

if result2 > 1000:

if result2 > 1000000:

if result2 > 1000000000:

result = str('%.2f' %(float(result2)/1000000000)) + 'G'

else:

result = str('%.2f' %(float(result2)/1000000)) + 'M'

else:

result = str('%.2f' %(float(result2)/1000)) + 'K'

else:

result = result2

value1.update({itemid:result})

value.update({groupid:value1})

worksheet.write(1,j,item.decode('utf-8'),merge_format1)

worksheet.write(i,j,value[groupid][itemid],merge_format1)

j += 1

i += 1

workbook.close()

cursor.close()

conn.close()

if __name__ == "__main__":

report()

最终效果，每周由脚本发送邮件