Chinaunix首页 | 论坛 | 博客
  • 博客访问: 351903
  • 博文数量: 26
  • 博客积分: 495
  • 博客等级: 下士
  • 技术积分: 562
  • 用 户 组: 普通用户
  • 注册时间: 2010-04-26 13:50
文章分类

全部博文(26)

文章存档

2015年(9)

2014年(6)

2013年(7)

2012年(2)

2011年(2)

分类: Python/Ruby

2012-06-25 18:38:55

这次要写一个脚本,做一个web汇报页面,利用到的PyH这个库,这个库用来生成html文件,wiki在这里,很简单的一个库,弄清关系以后很好使用,我只了解最基本的html,其中还有一部分CSS是朋友代劳,目前已经基本实现了,脚本应该可以优化,老大催的紧,先搞出结果再说包含2部分,先贴下css的,很简单的小文件,common.css(实在懒得看这玩意)

点击(此处)折叠或打开

  1. tr{ white-space:0;}
  2. .table0{border:none;width:100%;}
  3. .table0_td{padding:0 0px 0px 0;}
  4. .table1 {border-collapse:collapse;width:100%;}
  5. .table1 td{ height:30px; border:#333333 solid 1px;text-align:center;width:25%}
python脚本WeekReport.py 

点击(此处)折叠或打开

  1. #!/usr/bin/python
  2. #coding:utf-8
  3. from pyh import *
  4. import MySQLdb
  5. import datetime

  6. class Getdata:
  7.     def __init__(self):
  8.         self.conn=MySQLdb.connect(host='urhost',db='urdb',user='user',passwd='urpass')
  9.         self.cur=self.conn.cursor()

  10.     def getdata(self):
  11.         D={} #main 字典
  12.         d={} #临时字典
  13.         now=datetime.date.today()
  14.         delta=datetime.timedelta(days=7)
  15.         lastnow=now-delta

  16.         load='''
  17.         select a.f_name as project,c.avgload,c.f_date from t_project a,t_department b,
  18.         (select f_project_id,(sum(f_avg_load)/count(*)) as avgload,f_date from t_load_daily where f_date=date_add(curdate(),interval -7 day) group by f_project_id,f_date order by f_date) c
  19.         where c.f_project_id=a.f_pro_id and a.f_depart_id=b.f_depart_id union all select a.f_name as project,c.avgload,c.f_date from t_project a,t_department b,(select f_project_id,(sum(f_avg_load)/count(*)) as avgload,f_date from t_load_daily where f_date=date(now()) group by f_project_id,f_date order by f_date) c
  20.         where c.f_project_id=a.f_pro_id and a.f_depart_id=b.f_depart_id
  21.         '''
  22.         idc='''
  23.         select b.f_name as project,c.num,c.f_date from t_department a,t_project b,
  24.         (select f_depart_id,f_project_id,sum(f_flow_num) as num ,f_date from t_flows where f_type=3 and f_date=date(now()) group by f_project_id,f_date) c
  25.         where c.f_depart_id=a.f_depart_id and c.f_project_id=b.f_pro_id union all select b.f_name as project,c.num,c.f_date from t_department a,t_project b,(select f_depart_id,f_project_id,sum(f_flow_num) as num ,f_date from t_flows where f_type=3 and f_date=date_add(curdate(),interval -7 day)
  26.         group by f_project_id,f_date) c where c.f_depart_id=a.f_depart_id and c.f_project_id=b.f_pro_id;
  27.         '''

  28.         cdn='''
  29.         select b.f_name as project,c.num,c.f_date from t_department a,t_project b,
  30.         (select f_depart_id,f_project_id,sum(f_flow_num) as num ,f_date from t_flows where f_type=2 and f_date=date_add(curdate(),interval -7 day)
  31.         group by f_project_id,f_date) c where c.f_depart_id=a.f_depart_id and c.f_project_id=b.f_pro_id union all select b.f_name as project,c.num,c.f_date from t_department a,t_project b,(select f_depart_id,f_project_id,sum(f_flow_num) as num ,f_date from t_flows where f_type=2 and f_date=date(now()) group by f_project_id,f_date) c
  32.         where c.f_depart_id=a.f_depart_id and c.f_project_id=b.f_pro_id
  33.         '''
  34.         dau='''
  35.         select f_name,num,date from t_project a,
  36.         (select f_project_id as id ,sum(f_num) as num,f_date as date from t_dau where f_date=date(now()) group by f_project_id) b
  37.         where a.f_pro_id=b.id union all select f_name,num,date from t_project a,
  38.         (select f_project_id as id ,sum(f_num) as num,f_date as date from t_dau where f_date=date_add(curdate(),interval -7 day)
  39.         group by f_project_id) b where a.f_pro_id=b.id
  40.         '''
  41.         srvsum='''
  42.         select f_name,num,date from t_project a,
  43.         (select f_project_id as id,sum(f_costing_num) as num,f_date as date from t_costing_daily where f_costing_type=1 and f_date=date_add(curdate(),interval -7 day) group by f_project_id) b where a.f_pro_id=b.id
  44.         union all select f_name,num,date from t_project a,(select f_project_id as id,sum(f_costing_num) as num,f_date as date from t_costing_daily where f_costing_type=1 and f_date=date(now()) group by f_project_id) b where a.f_pro_id=b.id
  45.         '''
  46.         self.cur.execute(srvsum)
  47.         rows=self.cur.fetchall()

  48.         for row in rows:
  49.             try:
  50.                 d[row[0]]=d[row[0]]+row[1::] # 数据合并汇总
  51.             except KeyError,e:
  52.                 d[row[0]]=row[1::]

  53.         for k,v in d.items():
  54.             a=[]
  55.             m=list(v)
  56.             if now in v:
  57.                 a.append(m[m.index(now)-1])
  58.             else:
  59.                 a.append(0)
  60.             if lastnow in v:
  61.                 a.append(m[m.index(lastnow)-1])
  62.             else:
  63.                 a.append(0)
  64.             D[k]={'num':a}
  65.         ###
  66.         d={} ##置空字典
  67.         self.cur.execute(load)
  68.         rows=self.cur.fetchall()
  69.         for row in rows:
  70.             try:
  71.                 d[row[0]]=d[row[0]]+row[1::] # 数据合并汇总
  72.             except KeyError,e:
  73.                 d[row[0]]=row[1::]
  74.         for k,v in d.items():
  75.             a=[]
  76.             m=list(v)
  77.             if now in v:
  78.                 a.append(m[m.index(now)-1])
  79.             else:
  80.                 a.append(0)
  81.             if lastnow in v:
  82.                 a.append(m[m.index(lastnow)-1])
  83.             else:
  84.                 a.append(0)
  85.             D[k]['load']=a

  86.         ####
  87.         d={}
  88.         self.cur.execute(cdn)
  89.         rows=self.cur.fetchall()
  90.         for row in rows:
  91.             try:
  92.                 d[row[0]]=d[row[0]]+row[1::] # 数据合并汇总
  93.             except KeyError,e:
  94.                 d[row[0]]=row[1::]
  95.         for k,v in d.items():
  96.             a=[]
  97.             m=list(v)
  98.             if now in v:
  99.                 a.append(m[m.index(now)-1])
  100.             else:
  101.                 a.append(0)
  102.             if lastnow in v:
  103.                 a.append(m[m.index(lastnow)-1])
  104.             else:
  105.                 a.append(0)
  106.             D[k]['cdn']=a

  107.         ####
  108.         d={}
  109.         self.cur.execute(idc)
  110.         rows=self.cur.fetchall()
  111.         for row in rows:
  112.             try:
  113.                 d[row[0]]=d[row[0]]+row[1::] # 数据合并汇总
  114.             except KeyError,e:
  115.                 d[row[0]]=row[1::]
  116.         for k,v in d.items():
  117.             a=[]
  118.             m=list(v)
  119.             if now in v:
  120.                 a.append(m[m.index(now)-1])
  121.             else:
  122.                 a.append(0)
  123.             if lastnow in v:
  124.                 a.append(m[m.index(lastnow)-1])
  125.             else:
  126.                 a.append(0)
  127.             D[k]['idc']=a

  128.         ####
  129.         d={}
  130.         self.cur.execute(dau)
  131.         rows=self.cur.fetchall()
  132.         for row in rows:
  133.             try:
  134.                 d[row[0]]=d[row[0]]+row[1::] # 数据合并汇总
  135.             except KeyError,e:
  136.                 d[row[0]]=row[1::]
  137.         for k,v in d.items():
  138.             a=[]
  139.             m=list(v)
  140.             if now in v:
  141.                 a.append(m[m.index(now)-1])
  142.             else:
  143.                 a.append(0)
  144.             if lastnow in v:
  145.                 a.append(m[m.index(lastnow)-1])
  146.             else:
  147.                 a.append(0)
  148.             D[k]['dau']=a

  149.         return D







  150. class ToHtml:
  151.     '''
  152.        此类主要用来生成html文件

  153.     '''

  154.     def str3tocoma(self,num):#每三个数字加一个逗号
  155.         e = list(str(num))
  156.         if len(e)<=3:
  157.            return num
  158.         else:
  159.            for i in range(len(e))[::-3][1:]:
  160.                e.insert(i+1,",")
  161.            return "".join(e)

  162.     def Gentable(self,pro,data):
  163.         def k(x,y):
  164.             try:
  165.                return float(x-y)/float(y)
  166.             except ZeroDivisionError:
  167.                return 0
  168.         t=table(caption='%s' % pro,border="1",cl="table1",cellpadding="0",cellspacing="0")
  169.         t<<tr(td('%s' % pro,bgColor='#0099ff')+td('当前数据')+td('上周同比数据')+td('上周同比百分比'))
  170.         if data.has_key('dau'):
  171.             N=k(data.get('dau')[0],data.get('dau')[1])
  172.             if N>0.3:
  173.                 t<<tr(td('DAU')+td(self.str3tocoma(data.get('dau')[0]))+td(self.str3tocoma(data.get('dau')[1]))+td("%5.2F%%" % (N*100),bgColor='#ff0000'))
  174.             elif N<0:
  175.                 t<<tr(td('DAU')+td(self.str3tocoma(data.get('dau')[0]))+td(self.str3tocoma(data.get('dau')[1]))+td("%5.2f%%" % (N*100),bgColor='#00ff00'))
  176.             else:
  177.                 t<<tr(td('DAU')+td(self.str3tocoma(data.get('dau')[0]))+td(self.str3tocoma(data.get('dau')[1]))+td("%5.2f%%" % (N*100)))
  178.                 
  179.         else:
  180.             t<<tr(td('DAU')+td(0)+td(0)+td(0))
  181.         if data.has_key('load'):
  182.             N=k(data.get('load')[0],data.get('load')[1])
  183.             if N>0.2:
  184.                 t<<tr(td('负载')+td(data.get('load')[0])+td(data.get('load')[1])+td("%5.2F%%" % (N*100),bgColor='#ff0000'))
  185.             elif N<0:
  186.                 t<<tr(td('负载')+td(data.get('load')[0])+td(data.get('load')[1])+td("%5.2F%%" % (N*100),bgColor='#00ff00'))
  187.             else:
  188.                 t<<tr(td('负载')+td(self.str3tocoma(data.get('load')[0]))+td(self.str3tocoma(data.get('load')[1]))+td("%5.2F%%" % (N*100)))
  189.         else:
  190.             t<<tr(td('负载')+td(0)+td(0)+td(0))
  191.         if data.has_key('num'):
  192.             N=k(data.get('num')[0],data.get('num')[1])
  193.             if N>0.2:
  194.                 t<<tr(td('服务器数量')+td(data.get('num')[0])+td(data.get('num')[1])+td("%5.2F%%" % (N*100),bgColor='#ff0000'))
  195.             elif N<0:
  196.                 t<<tr(td('服务器数量')+td(data.get('num')[0])+td(data.get('num')[1])+td("%5.2F%%" % (N*100),bgColor='#00ff00'))
  197.             else:
  198.                 t<<tr(td('服务器数量')+td(data.get('num')[0])+td(data.get('num')[1])+td("%5.2F%%" % (N*100)))
  199.         else:
  200.             t<<tr(td('服务器数量')+td(0)+td(0)+td(0))
  201.         if data.has_key('cdn'):
  202.             N=k(data.get('num')[0],data.get('num')[1])
  203.             if N>0.3:
  204.                 t<<tr(td('CDN流量')+td(data.get('cdn')[0])+td(data.get('cdn')[0])+td("%5.2F%%" % (N*100),bgColor='#ff0000'))
  205.             elif N<0:
  206.                 t<<tr(td('CDN流量')+td(data.get('cdn')[0])+td(data.get('cdn')[0])+td("%5.2F%%" % (N*100),bgColor='#00ff00'))
  207.             else:
  208.                 t<<tr(td('CDN流量')+td(data.get('cdn')[0])+td(data.get('cdn')[0])+td("%5.2F%%" % (N*100)))
  209.         else:
  210.             t<<tr(td('CDN流量')+td(0)+td(0)+td(0))
  211.         if data.has_key('idc'):
  212.             N=k(data.get('num')[0],data.get('num')[1])
  213.             if N>0.3:
  214.                 t<<tr(td('IDC流量')+td(data.get('idc')[0])+td(data.get('idc')[0])+td("%5.2F%%" % (N*100),bgColor='#ff0000'))
  215.             elif N<0:
  216.                 t<<tr(td('IDC流量')+td(data.get('idc')[0])+td(data.get('idc')[0])+td("%5.2F%%" % (N*100),bgColor='#00ff00'))
  217.             else:
  218.                 t<<tr(td('IDC流量')+td(data.get('idc')[0])+td(data.get('idc')[0])+td("%5.2F%%" % (N*100)))
  219.         else:
  220.             t<<tr(td('IDC流量')+td(0)+td(0)+td(0))
  221.         return t


  222.     def Tohtml(self,tables):
  223.         page=PyH('本周报告')
  224.         page.addCSS('common.css')
  225.         page<<h1('本周报告',align='center')
  226.         tab=table(cellpadding="0",cellspacing="0",cl="table0")
  227.         for t in range(0,len(tables),2):
  228.             tab<<tr(td(tables[t-1],cl="table0_td")+td(tables[t],cl="table0_td"))
  229.         page<<tab
  230.         page.printOut()

  231. if __name__=="__main__":
  232.     I=Getdata()
  233.     d=I.getdata()
  234.     tabrows=[]
  235.     h=ToHtml()
  236.     for k,v in d.items():
  237.         t=h.Gentable(k,v)
  238.         tabrows.append(t)
  239.     h.Tohtml(tabrows)
最后插入图片一张:


阅读(17125) | 评论(0) | 转发(2) |
给主人留下些什么吧!~~