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

Source: Internet
Author: User

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.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.