使用pyecharts做数据库的分析报告

原文：https://www.yuque.com/wei01/tl0iqs/gufd6w

环境：
python3
pyecharts 1.9.0
pandas 1.3.0
PyMySQL 1.0.2

安装
pip install -i Simple Index pyecharts pandas pymysql

将监控数据放在mysql中，使用pymysql读取数据，然后用pandas分析数据，最后用pyechars渲染出来

脚本地址：链接

import pandas as pd
from pyecharts.charts import Bar, Page, Pie, Line, Grid
from pyecharts import options as opts
import pymysql

config = {
    'host': '127.0.0.1',
    # 'host': '192.168.247.50',
    'port': 3306,
    'user': 'dba',
    'password': 'root1234',
    'database': 'test',
    'charset': 'utf8',
    'cursorclass': pymysql.cursors.Cursor,
}

# 采集每条线每个数据库大小
conn = pymysql.connect(**config)
cursor = conn.cursor()
sql1 = 'select concat(groupname,"_",dbname) groupdb,dbsize from groupsize order by dbsize desc limit 20'
df1 = pd.read_sql(sql1, conn)
attr = df1['groupdb'].tolist()
value = df1['dbsize'].tolist()

dattr = list(reversed(attr))    # ['group2_db61', 'group2_db40', 'group1_db63...]
dvalue = list(reversed(value))  # [747.0, 749.0, 752.0...]

# 采集每个条线的数据库总大小
sql2 = 'select groupname,sum(dbsize) sumsize from groupsize group by groupname'
cursor.execute(sql2)
res = cursor.fetchall()
# (('group1', 12441.0), ('group2', 11626.0), ('group3', 9993.0), ('group4', 12893.0))

# 采集近七日的总会话数和活动会话数
sql3 = 'select create_time,total_session, active_session from session'
df3 = pd.read_sql(sql3, conn)
ctime = df3['create_time'].tolist()
tsession = df3['total_session'].tolist()
asession = df3['active_session'].tolist()

bar = (
    # 初始化大小
    Bar()
        .add_xaxis(dattr)
        .add_yaxis("数据库名大小", dvalue)
        .reversal_axis()  # 反转后,需要数据也要反转
        .set_global_opts(
        # x轴坐标名称倾斜
        # xaxis_opts=opts.AxisOpts(axislabel_opts=opts.LabelOpts(rotate=-85)),
        # 设置y轴倾斜度
        yaxis_opts=opts.AxisOpts(axislabel_opts=opts.LabelOpts(rotate=-5)),
        title_opts=opts.TitleOpts(title="空间使用情况"),
        # 设置划动
        # datazoom_opts=opts.DataZoomOpts(type_='slider',range_start=0,range_end=500)
        # y轴滑动, 截取50%~100%,最上面到中间
        datazoom_opts=opts.DataZoomOpts(orient="vertical", range_end=100, range_start=50),
    )
    # 或者直接使用字典参数
    # .set_global_opts(title_opts={"text": "主标题", "subtext": "副标题"})

)

pie = (
    # 初始化大小
    Pie()
        .add("",res,)
        # .set_colors(["blue", "green", "gold", "red"])  # 设置每一块的颜色
        .set_global_opts(
            title_opts=opts.TitleOpts(pos_left="center",title="条线使用情况"),
            legend_opts=opts.LegendOpts(pos_bottom=0), #  图例组件在容器下侧，距离边界为0
    )
        .set_series_opts(
        tooltip_opts=opts.TooltipOpts(trigger="item", formatter="{b}: {c} ({d}%)"), # {a}（系列名称），{b}（数据项名称），{c}（数值）, {d}（百分比）
        label_opts=opts.LabelOpts(
            formatter="{a|{a}}{abg|}\n{hr|}\n {b|{b}: }{c}  {per|{d}%}  ",
            background_color="#eee",
            border_color="#aaa",
            border_width=1,
            border_radius=4,
            rich={
                "a": {"color": "#999", "lineHeight": 22, "align": "center"},
                "abg": {
                    "backgroundColor": "#e3e3e3",
                    "width": "100%",
                    "align": "right",
                    "height": 22,
                    "borderRadius": [4, 4, 0, 0],
                },
                "hr": {
                    "borderColor": "#aaa",
                    "width": "100%",
                    "borderWidth": 0.5,
                    "height": 0,
                },
                "b": {"fontSize": 16, "lineHeight": 33},
                "per": {
                    "color": "#eee",
                    "backgroundColor": "#334455",
                    "padding": [2, 4],
                    "borderRadius": 2,
                },
            },
        ),
        ) # 标签设置富文本
    )

l1 = (
    Line()
    .add_xaxis(xaxis_data=ctime)
    .add_yaxis(
        series_name="活动会话数",
        y_axis=asession,
        symbol_size=8,
        is_hover_animation=False,
        label_opts=opts.LabelOpts(is_show=False),
        linestyle_opts=opts.LineStyleOpts(width=1.5),
        is_smooth=True,
    )
    .set_global_opts(
        title_opts=opts.TitleOpts(
            title="过去七天每小时会话数", pos_left="center"
        ),
        tooltip_opts=opts.TooltipOpts(trigger="axis"),
        axispointer_opts=opts.AxisPointerOpts(
            is_show=True, link=[{"xAxisIndex": "all"}]
        ),
        datazoom_opts=[
            opts.DataZoomOpts(
                is_show=True,
                is_realtime=True,
                range_start=90,
                range_end=100,
                xaxis_index=[0, 1],
            )
        ],
        xaxis_opts=opts.AxisOpts(
            type_="category",
            boundary_gap=False,
            axisline_opts=opts.AxisLineOpts(is_on_zero=True),
        ),
        yaxis_opts=opts.AxisOpts(max_=500, name="会话数(个)"),
        legend_opts=opts.LegendOpts(pos_left="left"),
        toolbox_opts=opts.ToolboxOpts(
            is_show=True,
            feature={
                "dataZoom": {"yAxisIndex": "none"},
                "restore": {},
                "saveAsImage": {},
            },
        ),
    )
)

l2 = (
    Line()
    .add_xaxis(xaxis_data=ctime)
    .add_yaxis(
        series_name="总会话数",
        y_axis=tsession,
        xaxis_index=1,
        yaxis_index=1,
        symbol_size=8,
        is_hover_animation=False,
        label_opts=opts.LabelOpts(is_show=False),
        linestyle_opts=opts.LineStyleOpts(width=1.5),
        is_smooth=True,
    )
    .set_global_opts(
        axispointer_opts=opts.AxisPointerOpts(
            is_show=True, link=[{"xAxisIndex": "all"}]
        ),
        tooltip_opts=opts.TooltipOpts(trigger="axis"),
        xaxis_opts=opts.AxisOpts(
            grid_index=1,
            type_="category",
            boundary_gap=False,
            axisline_opts=opts.AxisLineOpts(is_on_zero=True),
            position="top",
        ),
        datazoom_opts=[
            opts.DataZoomOpts(
                is_realtime=True,
                type_="inside",
                range_start=90,
                range_end=100,
                xaxis_index=[0, 1],
            )
        ],
        yaxis_opts=opts.AxisOpts(is_inverse=True, name="活动会话数"),
        legend_opts=opts.LegendOpts(pos_left="7%"),
    )
)

grid=(
    Grid(init_opts=opts.InitOpts(width="1024px", height="768px"))
    .add(chart=l1, grid_opts=opts.GridOpts(pos_left=50, pos_right=50, height="35%"))
    .add(
        chart=l2,
        grid_opts=opts.GridOpts(pos_left=50, pos_right=50, pos_top="55%", height="35%"),
    )
)



page = Page(page_title='数据库报表',interval= 5,)    # 报表标题,每个图例之间的间隔
page.add(bar)
page.add(pie)
page.add(grid)



page.render('./html/page.html')