python快速生成报表_python 自动生成报表

# !/usr/bin/env python

# -*- coding: utf-8 -*-

import cx_Oracle

import os

from pyecharts.faker import Faker

from pyecharts import options as opts

from pyecharts.charts import Pie

from pyecharts.faker import Faker

from pyecharts import options as opts

from pyecharts.charts import Bar

from pyecharts.globals import ThemeType

from pyecharts.charts import Bar, Page

from pyecharts.components import Table

from pyecharts.options import ComponentTitleOpts

os.environ['NLS_LANG'] = 'SIMPLIFIED CHINESE_CHINA.UTF8'

import datetime # 导入日期时间模块

import re

page = Page()

conn = cx_Oracle.connect('esbdata/xx@10.2.120.142/esbdb')

sql1="select /*+parallel(t 8)*/ b.description, count(*) \

from esb2_trans_log t ,servicesystem b \

where t.trans_date >= trunc(sysdate) \

and (t.respmsg like '%Read timed out%' or t.respmsg like '%异常%' or t.respmsg like '%超时%' or t.respmsg like '%通讯%失败%' or t.respmsg like '%主机%' or t.respmsg like '%网络%' or t.respmsg like '%数据库%' or t.respmsg like '%ORA%' or t.respmsg like '%系统繁忙%' ) \

and (t.logicsystem<>'AAAA' or t.respmsg <>'TGT已超时') \

AND (t.logicsystem<>'ELINK' or t.respcode<>'3040') \

AND (t.logicsystem<>'IBPS' or t.respcode<>'600011') \

AND (t.logicsystem<>'ELINK' or t.respcode<>'600011') \

AND (t.logicsystem<>'ELINK' or t.respcode<>'0201') \

AND (t.logicsystem<>'CNAPS' or t.respcode<>'AHVB001') \

AND (t.logicsystem<>'CNAPS' or t.respcode<>'UPC002') \

AND (t.logicsystem<>'CNAPS' or t.respcode<>'UPC002') \

AND (t.logicsystem<>'ELINK' or t.respcode<>'Y999') \

AND (t.logicsystem<>'ICCS' or t.respcode<>'0002') \

AND (t.logicsystem<>'ELINKHD' or t.respmsg<>'数据库查无符合条件的交易[0]') \

and (t.logicsystem<>'WECHAT' or t.respcode<>'500101092209') \

and (t.logicsystem<>'ELINK' or t.respmsg<>'数据库查无数据[0]') \

and (t.logicsystem<>'ELINK' or t.respmsg not like '%转账失败,具体请咨询收款方,确认是否存在开户行状态异常等情况%') \

and (t.logicsystem<>'ELINK' or t.respmsg not like '%主机账户记录不存在%') \

and (t.logicsystem<>'ELINK' or t.respmsg not like '%数据库操作失败[100]%') \

and (t.logicsystem<>'NCBS' or t.respmsg<>'[Inst.E0064]数据库中已存在新增的记录，请做修改操作！') \

and (t.logicsystem<>'CNAPS' or t.respmsg<>'系统异常') \

and (t.logicsystem<>'IBPS' or t.respmsg<>'账号状态异常') \

and (t.logicsystem<>'GAPS' or t.respmsg not like '%当前数据库查询无数据%') \

and (t.logicsystem<>'RIRS' or t.serviceid<>'0200200001116') \

and t.respmsg not like '%[Aplt.E0354]数据库记录已被修改，数据不一致%' \

and t.respcode<>'000000' \

and t.logicsystem<>'TLCHAT' \

and t.flowstepid in ('4','E') \

and t.respmsg not like '%帐户不存在或状态异常%' \

and t.logicsystem=b.name \

group by b.description \

order by 2 asc"

print(sql1)

cur = conn.cursor()

cur.execute(sql1)

result = cur.fetchall()

print (type(result))

print(result)

arrkey=[]

arrval=[]

for x in result:

print (x)

arrkey.append(x[0])

arrval.append(x[1])

print (arrkey)

print (arrval)

def bar_base1() -> Bar:

c = (

Bar(init_opts=opts.InitOpts(theme=ThemeType.ROMANTIC,width="1500px",height="600px",bg_color='#FFFFFF'))

#.add_xaxis(["身份核查系统", "外部数据平台", "新柜面", "新核心系统", "信贷系统", "网银系统","国结系统","回单系统"])

.add_xaxis(arrkey)

.add_yaxis("",arrval)

.set_series_opts(label_opts=opts.LabelOpts(position="right"))

.set_global_opts(title_opts=opts.TitleOpts(title="交易超时或异常按业务系统占比",title_textstyle_opts={'fontSize':20}))

#.set_global_opts(legend_opts=opts.LegendOpts(item_height=5, item_width=5))

#.set_global_opts(visualmap_opts=opts.VisualMapOpts(item_height=5))

.reversal_axis()

)

return c

mysql2="select description, cnt,respmsg,respcode from (select /*+parallel(t 8)*/ b.description,t.respcode,t.respmsg,count(*) as cnt \

from esb2_trans_log t ,servicesystem b \

where t.trans_date >= trunc(sysdate) \

and t.flowstepid in ('4', 'E') \

and t.logicsystem=b.name \

and (t.respmsg like '%Read timed out%' or t.respmsg like '%异常%' or t.respmsg like '%超时%' or t.respmsg like '%通讯%失败%' or t.respmsg like '%主机%' or t.respmsg like '%网络%' or t.respmsg like '%数据库%' or t.respmsg like '%ORA%' or t.respmsg like '%系统繁忙%' ) \

and (t.logicsystem<>'AAAA' or t.respmsg <>'TGT已超时') \

AND (t.logicsystem<>'ELINK' or t.respcode<>'3040') \

AND (t.logicsystem<>'IBPS' or t.respcode<>'600011') \

AND (t.logicsystem<>'ELINK' or t.respcode<>'600011') \

AND (t.logicsystem<>'ELINK' or t.respcode<>'0201') \

AND (t.logicsystem<>'CNAPS' or t.respcode<>'AHVB001') \

AND (t.logicsystem<>'CNAPS' or t.respcode<>'UPC002') \

AND (t.logicsystem<>'CNAPS' or t.respcode<>'UPC002') \

AND (t.logicsystem<>'ELINK' or t.respcode<>'Y999') \

AND (t.logicsystem<>'ICCS' or t.respcode<>'0002') \

AND (t.logicsystem<>'ELINKHD' or t.respmsg<>'数据库查无符合条件的交易[0]') \

and (t.logicsystem<>'WECHAT' or t.respcode<>'500101092209') \

and (t.logicsystem<>'ELINK' or t.respmsg<>'数据库查无数据[0]') \

and (t.logicsystem<>'ELINK' or t.respmsg not like '%转账失败,具体请咨询收款方,确认是否存在开户行状态异常等情况%') \

and (t.logicsystem<>'ELINK' or t.respmsg not like '%主机账户记录不存在%') \

and (t.logicsystem<>'ELINK' or t.respmsg not like '%数据库操作失败[100]%') \

and (t.logicsystem<>'NCBS' or t.respmsg<>'[Inst.E0064]数据库中已存在新增的记录，请做修改操作！') \

and (t.logicsystem<>'CNAPS' or t.respmsg<>'系统异常') \

and (t.logicsystem<>'IBPS' or t.respmsg<>'账号状态异常') \

and (t.logicsystem<>'GAPS' or t.respmsg not like '%当前数据库查询无数据%') \

and (t.logicsystem<>'RIRS' or t.serviceid<>'0200200001116') \

and t.respcode<>'000000' \

and t.logicsystem<>'TLCHAT' \

and t.respmsg not like '%帐户不存在或状态异常%' \

and t.respmsg not like '%[Aplt.E0354]数据库记录已被修改，数据不一致%' \

group by b.description,t.respcode,t.respmsg \

order by count(*) desc)"

print(mysql2)

cur = conn.cursor()

cur.execute(mysql2)

result = cur.fetchall()

print (type(result))

print(result)

def table_base() -> Table:

table = Table()

headers = ["系统名称","错误笔数","错误信息","错误码"]

rows = result

table.add(headers, rows).set_global_opts(

title_opts=ComponentTitleOpts(title="超时或异常按业务系统及错误类型汇总",title_style={"style": "font-size: 20px; font-weight:bold;"},subtitle_style= {"style": "font-size: 10px;"})

)

print (dir(table))

return table

#bar_base().render("/django/mysite/news/templates/report/reportesb.html.bak")

page.add(bar_base1())

page.add(table_base())

page.render("/django/mysite/news/templates/report/reportesb.html.bak")

#data2=(re.sub('https://assets.pyecharts.org/assets/echarts.min.js','/static/Js/Report/echarts.min.js',data1))

#data2=(re.sub('https://assets.pyecharts.org/assets/themes/romantic.js','/static/Js/Report/romantic.js',data2))

os.system("sed '7 a

泰隆银行当日交易系统运行报告

' -i /django/mysite/news/templates/report/reportesb.html.bak")

#os.system("sed -i '210,210s#font-size: 15px#font-size: 30px#g' /django/mysite/news/templates/report/reportesb.html.bak")

os.system("sed -i 's#https://assets.pyecharts.org/assets/echarts.min.js#/static/Js/Report/echarts.min.js#g' /django/mysite/news/templates/report/reportesb.html.bak")

os.system("sed -i 's#https://assets.pyecharts.org/assets/themes/romantic.js#/static/Js/Report/romantic.js#g' /django/mysite/news/templates/report/reportesb.html.bak")

os.system("mv /django/mysite/news/templates/report/reportesb.html.bak /django/mysite/news/templates/report/reportesb.html")