poleaxe

20170529计划---统计业务量并生成EXCEL通过邮件发送

　　每个月都要统计这些业务量的东东，烦死了，赶紧通过python写一个来搞定吧，三天搞定吧，未完待续哈。

2017-5-29 19：50粗略地做了一个思维导图哈

 

 

终于第三天完成啦

  1 #encoding=utf-8
  2 import cx_Oracle as oracle
  3 import MYSQLdb as mysql
  4 from datetime import datetime
  5 import xlsxwriter
  6 import sys
  7 '''
  8 author:poleaxe
  9 function:实现统计应用业务量
 10 原理：读取按照格式的yewu.sql，再将SQL分别查询并返回结果到列表，
 11       最后创建xlsx文件并写入列表结果
 12 
 13 yewu.sql格式：
 14 --系统
 15 --业务指标
 16 --数据库
 17 select count(1)/#day from dual
 18 where createdate > #starttime
 19 and createdate < #endtime
 20 --end
 21 依此类推
 22 '''
 23 reload(sys)
 24 sys.setdefaultencoding('gbk')
 25 username = ''
 26 password = ''
 27 epcis = ''
 28 epods = ''
 29 cxcc = ''
 30 nba4ot = ''
 31 claim = ''
 32 sqlfile = 'yewu.sql'
 33 day='31'
 34 starttime = '2017-05-01'
 35 endtime = '2017-06-01'
 36 
 37 #格式化yewu.sql文件，按照系统、业务、数据库、SQL分割为一个二维列表
 38 def sql_file(sqlfile):
 39     a = open(sqlfile).read.split('--end')
 40     b = []
 41     for i in a:
 42         if i=='' or i =="\n\n":
 43             continue
 44         for j in  i.split('--'):
 45             b.append(j.strip()) #清理列表元素空格
 46     arr1 = []
 47     arr2 = []
 48     flag = 0
 49     for k in b:
 50         while flag >3: #创建有4个元素的arr2加入arr1列表
 51             arr1.append(arr2)
 52             #初始化arr2
 53             flag = 0
 54             arr2 = []
 55             print arr1
 56         else:
 57             if  k!='' and k!='\n\n' and k!='\n\n\n':
 58                 arr2.append(k)
 59                 flag += 1
 60     #替换列表里的#day、#starttime及#endtime
 61     for i in range(len(arr1)):
 62         arr1[i][3] = arr1[i][3].replace("#day",day)
 63         arr1[i][3] = arr1[i][3].replace("#starttime",starttime)
 64         arr1[i][3] = arr1[i][3].replace("#endtime", endtime)
 65     return arr1
 66 
 67 #查询二维列表的oracle SQL语句
 68 def oracle_select(db,sqlstr):
 69     listnew = ''
 70     sql = ''
 71     try:
 72         conn = oralce.connect(username,password,db)
 73         cur = conn.cusor()
 74         sql = cur.excecute(sqlstr)
 75     except Exception, e:
 76         print e.message
 77     else:
 78         listnew = sql.fetchone()
 79     cur.close()
 80     conn.close
 81     return listnew
 82 
 83 #查询没有mysql数据库的，果断pass哈
 84 def mysql_select(db,sql):
 85     pass
 86 
 87 
 88 #创建EXCEL表格
 89 def write_excel(info):
 90     date_now == datetime.now().strftime("%Y%m%d")
 91     xlsxfilename = '产险IT经营情况报表'.decode('utf-8').encode('gbk')
 92     workbook = xlsxwriter.Workbook(xlsxfilename,{'constant_memory':True})
 93     worksheet = workbook.add_worksheet()
 94     title = ["关键系统名称","业务类别","数据库","日均业务量"]
 95     for i in range(len(title):
 96         worksheet.write(0,i,title[i].decode('uft-8').encode('gbk'))
 97     for i in range(len(info)):
 98         for row, row_date in enumerate(info[i]):
 99             print row,row_date
100             worksheet.write(i+1,row,row_date)
101     workbook.close()
102     return xlsxfilename
103 
104 def main():
105     info = sql_file(sqlfile)
106     list_result = []
107     for sql in info:
108         list_result.append(oracle_select(sql[2],sql[3])
109     listnew = []
110     for i in list_result:
111         listnew.append(i[0])
112     for j in range(len(info)):
113         info[j][3] = listnew[j]
114     print info
115     write_excel(info)
116 
117 if __name__ == '__main__':
118     main()

 

posted on 2017-05-29 19:18  poleaxe  阅读(318)  评论(0)    收藏  举报

导航