这次要写一个脚本,做一个web汇报页面,利用到的PyH这个库,这个库用来生成html文件,wiki在这里,很简单的一个库,弄清关系以后很好使用,我只了解最基本的html,其中还有一部分CSS是朋友代劳,目前已经基本实现了,脚本应该可以优化,老大催的紧,先搞出结果再说
包含2部分,先贴下css的,很简单的小文件,common.css(实在懒得看这玩意)
- tr{ white-space:0;}
- .table0{border:none;width:100%;}
- .table0_td{padding:0 0px 0px 0;}
- .table1 {border-collapse:collapse;width:100%;}
- .table1 td{ height:30px; border:#333333 solid 1px;text-align:center;width:25%}
python脚本WeekReport.py
- #!/usr/bin/python
- #coding:utf-8
- from pyh import *
- import MySQLdb
- import datetime
- class Getdata:
- def __init__(self):
- self.conn=MySQLdb.connect(host='urhost',db='urdb',user='user',passwd='urpass')
- self.cur=self.conn.cursor()
- def getdata(self):
- D={} #main 字典
- d={} #临时字典
- now=datetime.date.today()
- delta=datetime.timedelta(days=7)
- lastnow=now-delta
- load='''
- 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_add(curdate(),interval -7 day) group by f_project_id,f_date order by f_date) c
- 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
- where c.f_project_id=a.f_pro_id and a.f_depart_id=b.f_depart_id
- '''
- idc='''
- 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(now()) 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=3 and f_date=date_add(curdate(),interval -7 day)
- 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;
- '''
- cdn='''
- 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_add(curdate(),interval -7 day)
- 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
- where c.f_depart_id=a.f_depart_id and c.f_project_id=b.f_pro_id
- '''
- dau='''
- select f_name,num,date from t_project a,
- (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
- where a.f_pro_id=b.id union all select f_name,num,date from t_project a,
- (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)
- group by f_project_id) b where a.f_pro_id=b.id
- '''
- srvsum='''
- 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_add(curdate(),interval -7 day) group by f_project_id) b where a.f_pro_id=b.id
- 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
- '''
- self.cur.execute(srvsum)
- rows=self.cur.fetchall()
- for row in rows:
- try:
- d[row[0]]=d[row[0]]+row[1::] # 数据合并汇总
- except KeyError,e:
- d[row[0]]=row[1::]
- for k,v in d.items():
- a=[]
- m=list(v)
- if now in v:
- a.append(m[m.index(now)-1])
- else:
- a.append(0)
- if lastnow in v:
- a.append(m[m.index(lastnow)-1])
- else:
- a.append(0)
- D[k]={'num':a}
- ###
- d={} ##置空字典
- self.cur.execute(load)
- rows=self.cur.fetchall()
- for row in rows:
- try:
- d[row[0]]=d[row[0]]+row[1::] # 数据合并汇总
- except KeyError,e:
- d[row[0]]=row[1::]
- for k,v in d.items():
- a=[]
- m=list(v)
- if now in v:
- a.append(m[m.index(now)-1])
- else:
- a.append(0)
- if lastnow in v:
- a.append(m[m.index(lastnow)-1])
- else:
- a.append(0)
- D[k]['load']=a
- ####
- d={}
- self.cur.execute(cdn)
- rows=self.cur.fetchall()
- for row in rows:
- try:
- d[row[0]]=d[row[0]]+row[1::] # 数据合并汇总
- except KeyError,e:
- d[row[0]]=row[1::]
- for k,v in d.items():
- a=[]
- m=list(v)
- if now in v:
- a.append(m[m.index(now)-1])
- else:
- a.append(0)
- if lastnow in v:
- a.append(m[m.index(lastnow)-1])
- else:
- a.append(0)
- D[k]['cdn']=a
- ####
- d={}
- self.cur.execute(idc)
- rows=self.cur.fetchall()
- for row in rows:
- try:
- d[row[0]]=d[row[0]]+row[1::] # 数据合并汇总
- except KeyError,e:
- d[row[0]]=row[1::]
- for k,v in d.items():
- a=[]
- m=list(v)
- if now in v:
- a.append(m[m.index(now)-1])
- else:
- a.append(0)
- if lastnow in v:
- a.append(m[m.index(lastnow)-1])
- else:
- a.append(0)
- D[k]['idc']=a
- ####
- d={}
- self.cur.execute(dau)
- rows=self.cur.fetchall()
- for row in rows:
- try:
- d[row[0]]=d[row[0]]+row[1::] # 数据合并汇总
- except KeyError,e:
- d[row[0]]=row[1::]
- for k,v in d.items():
- a=[]
- m=list(v)
- if now in v:
- a.append(m[m.index(now)-1])
- else:
- a.append(0)
- if lastnow in v:
- a.append(m[m.index(lastnow)-1])
- else:
- a.append(0)
- D[k]['dau']=a
- return D
- class ToHtml:
- '''
- 此类主要用来生成html文件
- '''
- def str3tocoma(self,num):#每三个数字加一个逗号
- e = list(str(num))
- if len(e)<=3:
- return num
- else:
- for i in range(len(e))[::-3][1:]:
- e.insert(i+1,",")
- return "".join(e)
- def Gentable(self,pro,data):
- def k(x,y):
- try:
- return float(x-y)/float(y)
- except ZeroDivisionError:
- return 0
- t=table(caption='%s' % pro,border="1",cl="table1",cellpadding="0",cellspacing="0")
- t<<tr(td('%s' % pro,bgColor='#0099ff')+td('当前数据')+td('上周同比数据')+td('上周同比百分比'))
- if data.has_key('dau'):
- N=k(data.get('dau')[0],data.get('dau')[1])
- if N>0.3:
- 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'))
- elif N<0:
- 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'))
- else:
- t<<tr(td('DAU')+td(self.str3tocoma(data.get('dau')[0]))+td(self.str3tocoma(data.get('dau')[1]))+td("%5.2f%%" % (N*100)))
-
- else:
- t<<tr(td('DAU')+td(0)+td(0)+td(0))
- if data.has_key('load'):
- N=k(data.get('load')[0],data.get('load')[1])
- if N>0.2:
- t<<tr(td('负载')+td(data.get('load')[0])+td(data.get('load')[1])+td("%5.2F%%" % (N*100),bgColor='#ff0000'))
- elif N<0:
- t<<tr(td('负载')+td(data.get('load')[0])+td(data.get('load')[1])+td("%5.2F%%" % (N*100),bgColor='#00ff00'))
- else:
- t<<tr(td('负载')+td(self.str3tocoma(data.get('load')[0]))+td(self.str3tocoma(data.get('load')[1]))+td("%5.2F%%" % (N*100)))
- else:
- t<<tr(td('负载')+td(0)+td(0)+td(0))
- if data.has_key('num'):
- N=k(data.get('num')[0],data.get('num')[1])
- if N>0.2:
- t<<tr(td('服务器数量')+td(data.get('num')[0])+td(data.get('num')[1])+td("%5.2F%%" % (N*100),bgColor='#ff0000'))
- elif N<0:
- t<<tr(td('服务器数量')+td(data.get('num')[0])+td(data.get('num')[1])+td("%5.2F%%" % (N*100),bgColor='#00ff00'))
- else:
- t<<tr(td('服务器数量')+td(data.get('num')[0])+td(data.get('num')[1])+td("%5.2F%%" % (N*100)))
- else:
- t<<tr(td('服务器数量')+td(0)+td(0)+td(0))
- if data.has_key('cdn'):
- N=k(data.get('num')[0],data.get('num')[1])
- if N>0.3:
- t<<tr(td('CDN流量')+td(data.get('cdn')[0])+td(data.get('cdn')[0])+td("%5.2F%%" % (N*100),bgColor='#ff0000'))
- elif N<0:
- t<<tr(td('CDN流量')+td(data.get('cdn')[0])+td(data.get('cdn')[0])+td("%5.2F%%" % (N*100),bgColor='#00ff00'))
- else:
- t<<tr(td('CDN流量')+td(data.get('cdn')[0])+td(data.get('cdn')[0])+td("%5.2F%%" % (N*100)))
- else:
- t<<tr(td('CDN流量')+td(0)+td(0)+td(0))
- if data.has_key('idc'):
- N=k(data.get('num')[0],data.get('num')[1])
- if N>0.3:
- t<<tr(td('IDC流量')+td(data.get('idc')[0])+td(data.get('idc')[0])+td("%5.2F%%" % (N*100),bgColor='#ff0000'))
- elif N<0:
- t<<tr(td('IDC流量')+td(data.get('idc')[0])+td(data.get('idc')[0])+td("%5.2F%%" % (N*100),bgColor='#00ff00'))
- else:
- t<<tr(td('IDC流量')+td(data.get('idc')[0])+td(data.get('idc')[0])+td("%5.2F%%" % (N*100)))
- else:
- t<<tr(td('IDC流量')+td(0)+td(0)+td(0))
- return t
- def Tohtml(self,tables):
- page=PyH('本周报告')
- page.addCSS('common.css')
- page<<h1('本周报告',align='center')
- tab=table(cellpadding="0",cellspacing="0",cl="table0")
- for t in range(0,len(tables),2):
- tab<<tr(td(tables[t-1],cl="table0_td")+td(tables[t],cl="table0_td"))
- page<<tab
- page.printOut()
- if __name__=="__main__":
- I=Getdata()
- d=I.getdata()
- tabrows=[]
- h=ToHtml()
- for k,v in d.items():
- t=h.Gentable(k,v)
- tabrows.append(t)
- h.Tohtml(tabrows)
最后插入图片一张:
阅读(17118) | 评论(0) | 转发(2) |