Use Python to query MySQL database to generate Excel file to send monitoring weekly.

Business Party request Weekly send a weekly report out, the past week of the online project details sent out, our monitoring with Zabbix, the past has been manually filled, very time-consuming and very unprofessional, so I spent a few months to learn python, write the following script for your reference, Hope the great God don't laugh.

#!/usr/bin/env python#-*-Coding:utf8-*-import mysqldbimport stringimport xlsxwriterimport time,datetimeimport Sysreload (SYS) sys.setdefaultencoding (' utf-8 ') zdbhost = ' Zdbuser = ' Zdbpass = ' Zdbport = Zdbname = ' #时间戳 take the current time and 7 days ago Time stop_time = Int (Time.time ()) start_time = Stop_time-604800date = Time.strftime ("%y-%m-%d", Time.localtime (stop_ Time)) Xlsfilename = '%s Business weekly. xlsx '% date#keys format: itemname itemid type format groupidkeys = [[[' Uv/Day ', ' 167365 ', ' max        ', '%.2f ', 1, ' A ', ', ' [' pv/week ', ' 167359 ', ' Max ', '%.2f ', 1, ' 48 ',], [' Max concurrency ', ' 167364 ', ' Max ', '%.2f ', 1, ' 48 ',], [' max concurrency occurs ', ' 167364 ', ' Max ', '%.2f ', 1, ' 48 ',], [' Average QPS ', ' 167361 ', ' avg ', '%.2f ', 1, ' 48 '], [' Max QPs ', ' 167361 ', ' Max ', '%.2f ', 1, ' 48 '], [' Maximum QPS occurrence time ', ' 167361 ', ' Max ', '%.2f ', 1, ' 48 '], [' Maximum bandwidth (in) ', ' 81291 ', ' Max ', '%.2f ', ' 1048576 ', ' 48 '], [' Average bandwidth (in) ', ' 81291 ', ' avg ', '%.2f ', ' 1048576 ', ' 48 '], [' Maximum bandwidth (out) ', ' 81292 ', ' Max ', '%.2f ', ' 104 8576 ', ' 48 '], [' Average bandwidth (out) ', ' 81292 ', ' avg ', '%.2f ', ' 1048576 ', ' [']], [[' uv/Day ', ' 168141 ', ' Max ', '%.2f ', 1, ' + '], [' pv/Week ', ' 168135 ', ' ma X ', '%.2f ', 1, ' 16 ', [' max concurrency ', ' 168140 ', ' Max ', '%.2f ', 1, ' 16 '], [' Max concurrency time ', ' 168140 ', ' Max ', '%.2f ', 1, ' 16 ', '] , [' Average QPS ', ' 168137 ', ' avg ', '%.2f ', 1, ' 16 '], [' Max QPs ', ' 168137 ', ' Max ', '%.2f ', 1, ' 16 '], [' Maximum QPS occurrence time ', ' 1 68137 ', ' Max ', '%.2f ', 1, ' 16 '], [' Maximum bandwidth (in) ', ' 104522 ', ' Max ', '%.2f ', ' 1048576 ', ' 16 '], [' Average bandwidth (in) ', ' 104522 ', ' Avg ', '%.2f ', ' 1048576 ', ' 16 '], [' Maximum bandwidth (out) ', ' 104523 ', ' Max ', '%.2f ', ' 1048576 ', ' 16 '], [' Average bandwidth (out) ', ' 104523 ' , ' avg ', '%.2f ', ' 1048576 ', ' + '], [[' uv/Day ', ' 163672 ', ' Max ', '%.2f ', 1, ' ' + '], [' pv/Week ', ' 163666 ', ' Max ' , '%.2f ', 1, ' 71 '], [' Max concurrency ', ' 163671 ', ' Max ', '%.2f ', 1, ' 71 '], [' Max concurrency time ', ' 163671 ', ' Max ', '%.2f ', 1, ' 71 ', '] , [' Average QPS ', ' 163668 ', ' avg ', '%.2f ', 1, ' 71 '], [' Max QPs ', ' 163668 ', ' Max ', '%.2f ', 1, ' 71 '], [' Maximum QPS occurrence time ', ' 1 63668 ', ' Max ', '%.2f ', 1,' 71 '], [' Maximum bandwidth (in) ', ' 84942 ', ' Max ', '%.2f ', ' 1048576 ', ' 71 '], [' Average bandwidth (in) ', ' 84942 ', ' avg ', '%.2f ', ' 1048576 ', ' 7 1 '], [' Maximum bandwidth (out) ', ' 84943 ', ' Max ', '%.2f ', ' 1048576 ', ' 71 '], [' Average bandwidth (out) ', ' 84943 ', ' avg ', '%.2f ', ' 1048576 ', ' 7        1 ']], [[' uv/Day ', ' 154707 ', ' Max ', '%.2f ', 1, ' '], [' pv/Week ', ' 154722 ', ' Max ', '%.2f ', 1, ' 132 '], [' Max concurrency ', ' 154706 ', ' Max ', '%.2f ', 1, ' 132 '], [' Max concurrency time ', ' 154706 ', ' Max ', '%.2f ', 1, ' 132 ', '], [' Average QPS ', ' 15470 3 ', ' avg ', '%.2f ', 1, ' 132 '], [' Max QPs ', ' 154703 ', ' Max ', '%.2f ', 1, ' 132 '], [' Maximum QPS occurrence time ', ' 154703 ', ' Max ', '%.2f ', 1, ' 132 '], [' Maximum bandwidth (in) ', ' 152400 ', ' Max ', '%.2f ', ' 1048576 ', ' 132 '], [' Average bandwidth (in) ', ' 152400 ', ' avg ', '%.2f ', ' 10485 76 ', ' 132 '], [' Maximum bandwidth (out) ', ' 152402 ', ' Max ', '%.2f ', ' 1048576 ', ' 132 '], [' Average bandwidth (out) ', ' 152402 ', ' avg ', '%.2f ', ' 1 048576 ', ' uv/'], [['] ', ' 189559 ', ' Max ', '%.2f ', 1, ' + '], [' pv/Week ', ' 189553 ', ' Max ', '%.2f ', 1, ' 31 '] , [' Max concurrency ', ' 189558 ', ' Max ', '%.2f ', 1, ' 31 '], [' Max concurrent occurrence Time ', ' 189558 ', ' Max ', '%.2f ', 1, ' 31 ', '], [' Average QPS ', ' 189555 ', ' avg ', '%.2f ', 1, ' 31 '], [' Max QPs ', ' 189555 ', ' Max ', '%.2f ', 1, ' 31 '], [' Maximum QPS occurrence time ', ' 189555 ', ' Max ', '%.2f ', 1, ' 31 '], [' most Large bandwidth (in) ', ' 190757 ', ' Max ', '%.2f ', ' 1048576 ', ' 31 '], [' Average bandwidth ' (in) ', ' 190757 ', ' avg ', '%.2f ', ' 1048576 ', ' 31 '], [' most    Large bandwidth (out) ', ' 190758 ', ' Max ', '%.2f ', ' 1048576 ', ' 31 '], [' Average bandwidth (out) ', ' 190758 ', ' avg ', '%.2f ', ' 1048576 ', ' 31 '], [[' Uv/Day ', ' 188642 ', ' Max ', '%.2f ', 1, ' ['] ', ' pv/Week ', ' 188636 ', ' Max ', '%.2f ', 1, ' 50 '], [' Max concurrency ', ' 188641 ', ' Max ', '%.2f ', 1, ' 50 '], [' Max concurrency time ', ' 188641 ', ' Max ', '%.2f ', 1, ' 50 ', '], [' Average QPS ', ' 188638 ', ' avg ', '%.2f ', 1, ' 50 '], [' Max QPs ', ' 188638 ', ' Max ', '%.2f ', 1, ' 50 '], [' Maximum QPS occurrence time ', ' 188638 ', ' Max ', '%.2f ', 1, ' 50 '], [' Maximum bandwidth ( In) ', ' 33707 ', ' Max ', '%.2f ', ' 1048576 ', ' 50 '], [' Average bandwidth (in) ', ' 33707 ', ' avg ', '%.2f ', ' 1048576 ', ' 50 '], [' Maximum bandwidth (OU T) ', ' 33712 ', ' Max ', '%.2F ', ' 1048576 ', ' 50 '], [' Average bandwidth (out) ', ' 33712 ', ' avg ', '%.2f ', ' 1048576 ', ' ['] ']]def ' (): ' Open database Connection ' ' Co nn = 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": "Public Number", "71": "TV", "+": "Chiq3 Audio and Video", "$": "LA Uncher "," 50 ":" Personalized Recommendation "," 31 ":" Semantic Cloud "} value1 = {} value = {} #创建文件 workbook = Xlsxwriter.        Workbook (Xlsfilename) #定义Excel中的格式, refer to Http://xlsxwriter.readthedocs.io/merge_format = Workbook.add_format ({ ' Bold ': true, ' border ': true, ' align ': ' Center ', ' valign ': ' vcenter ', ' Fg_co        Lor ': ' #D7E4BC ',}) MERGE_FORMAT1 = Workbook.add_format ({' Border ': True, ' align ': ' Center ', ' valign ': ' vcenter ', ' text_wrap ': True,}) #创建工作薄 worksheet = Workbook.add_worksheet () worksheet . Set_column (' A:l ', one) #写入第一行 Worksheet.merGe_range (' a1:l1 ', ' Business Monitoring Weekly (%s) '. Decode (' utf-8 ')% date, Merge_format) #写入其他行 worksheet.write (1, 0, ' project name '. Deco De (' 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 = CU            Rsor.fetchone () [' result '] except MySQLdb.OperationalError:result2 = "not monitored" If target[0] in [' Maximum QPS occurrence time ', ' max concurrency time ']: #sql3 = ' ' Select clock from trends_uint where Itemid =%s and VA                lue_%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 () [' CL Ock '] result2 = Time.strftime ("%y-%m-%d%h:%m:%s", Time.localtime (RESULT3)) except MySQL Db.                operationalerror:result2 = ' not monitored ' #处理数据 if not result2:                result2 = "not monitored" if the 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:resul t = 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) W Orksheet.write (I,J,VALUE[GROUPID][ITEMID],MERGE_FORMAT1) J + = 1 i + = 1 workbook.close () Cursor.clo SE () conn.close () if __name__ = = "__main__": report ()

Final effect, send mail weekly by script

Use Python to query MySQL database to generate Excel file to send monitoring weekly.