2025.2.20

今天完成昨天没有完成的将数据用图形化的方式展现出来。

用python中的pyqt5可以画出各类图的大概形式。

 

 

 

 

 

from pyecharts.charts import Pie, Bar, Line, Page
from pyecharts import options as opts
from pyecharts.commons.utils import JsCode
import pymysql

# 数据库配置
DB_CONFIG = {
    "host": "localhost",
    "port": 3306,
    "user": "root",
    "password": "nidie",
    "db": "big_event",
    "charset": "utf8mb4"
}


def fetch_data_from_mysql(table_name, fields):
    """从MySQL获取数据"""
    connection = pymysql.connect(**DB_CONFIG)
    try:
        with connection.cursor() as cursor:
            sql = f"SELECT {','.join(fields)} FROM {table_name}"
            cursor.execute(sql)
            return cursor.fetchall()
    finally:
        connection.close()


def process_table1_data():
    """处理表1数据（type）"""
    raw_data = fetch_data_from_mysql("data1", ["type", "id", "access_count"])
    type_dict = {}
    for item in raw_data:
        type_name = item[0]
        id = item[1]
        num = item[2]
        type_dict[f"{type_name}/{id}"] = num
    return [(k, v) for k, v in type_dict.items()]


def process_table2_data():
    """处理表2数据（ip）"""
    raw_data = fetch_data_from_mysql("data2", ["ip", "id", "access_count"])
    ip_dict = {}
    for item in raw_data:
        ip = item[0]
        id = item[1]
        num = item[2]
        ip_dict[f"{ip}/{id}"] = num
    return [(k, v) for k, v in ip_dict.items()]


def process_table3_data():
    """处理表3数据（traffic）"""
    raw_data = fetch_data_from_mysql("data3", ["id", "total_traffic"])
    traffic_data = sorted(raw_data, key=lambda x: x[1], reverse=True)[:10]  # 取前10大流量
    return [(str(item[0]), int(item[1]) / 10000) for item in traffic_data]  # 转换为万单位


def create_pie_page():
    """创建饼图页面"""
    page = Page(layout=Page.SimplePageLayout)

    # 表1饼图
    pie1 = (
        Pie(
            init_opts=opts.InitOpts(width="1200px", height="800px")
        )
        .add("", process_table1_data(), radius=["30%", "70%"])
        .set_global_opts(
            title_opts=opts.TitleOpts(title="表1分类分布", pos_left="center"),
            legend_opts=opts.LegendOpts(
                orient="vertical",
                pos_top="15%",
                pos_right="2%",
                type_="scroll",
                is_show=True
            )
        )
        .set_series_opts(label_opts=opts.LabelOpts(formatter="{b}: {c}"))
        .set_colors(["#C1232B", "#B5C334", "#FCCE10", "#E87C25", "#27727B"])
    )

    # 表2饼图
    pie2 = (
        Pie(
            init_opts=opts.InitOpts(width="1200px", height="800px")
        )
        .add("", process_table2_data(), radius=["30%", "70%"])
        .set_global_opts(
            title_opts=opts.TitleOpts(title="表2IP分布", pos_left="center"),
            legend_opts=opts.LegendOpts(
                orient="vertical",
                pos_top="15%",
                pos_right="2%",
                type_="scroll",
                is_show=True
            )
        )
        .set_series_opts(label_opts=opts.LabelOpts(formatter="{b}: {c}"))
        .set_colors(["#C1232B", "#B5C334", "#FCCE10", "#E87C25", "#27727B", "#FE8463", "#9BCA63", "#FAD860", "#F3A43B",
                     "#60C0DD"])
    )

    # 表3饼图
    pie3 = (
        Pie(
            init_opts=opts.InitOpts(width="1200px", height="800px")
        )
        .add("", process_table3_data(), radius=["30%", "70%"])
        .set_global_opts(
            title_opts=opts.TitleOpts(title="表3流量分布", pos_left="center"),
            legend_opts=opts.LegendOpts(
                orient="vertical",
                pos_top="15%",
                pos_right="2%",
                type_="scroll",
                is_show=True
            )
        )
        .set_series_opts(label_opts=opts.LabelOpts(formatter="{b}: {c}"))
        .set_colors(["#C1232B", "#B5C334", "#FCCE10", "#E87C25", "#27727B", "#FE8463", "#9BCA63", "#FAD860", "#F3A43B",
                     "#60C0DD", "#D7504B", "#C6E579", "#F4E001", "#F0805A", "#26C0C0"])
    )

    page.add(pie1, pie2, pie3)
    page.render("pie_charts.html")


def create_bar_page():
    """创建柱状图页面"""
    page = Page(layout=Page.SimplePageLayout)

    # 表1柱状图
    table1_data = process_table1_data()
    bar1 = (
        Bar(
            init_opts=opts.InitOpts(width="900px", height="600px")
        )
        .add_xaxis([x[0] for x in table1_data])
        .add_yaxis("数量（次）", [x[1] for x in table1_data])
        .set_global_opts(
            title_opts=opts.TitleOpts(title="表1分类统计"),
            xaxis_opts=opts.AxisOpts(axislabel_opts=opts.LabelOpts(rotate=20)),

            # datazoom_opts=[opts.DataZoomOpts(type_="slider")],
        )
    )

    # 表2柱状图
    table2_data = process_table2_data()
    bar2 = (
        Bar(
            init_opts=opts.InitOpts(width="900px", height="600px")
        )
        .add_xaxis([x[0] for x in table2_data])
        .add_yaxis("数量（次）", [x[1] for x in table2_data])
        .set_global_opts(
            title_opts=opts.TitleOpts(title="表2IP统计"),
            xaxis_opts=opts.AxisOpts(axislabel_opts=opts.LabelOpts(rotate=20)),
            # datazoom_opts=[opts.DataZoomOpts(type_="slider")],
        )
    )

    # 表3柱状图
    table3_data = process_table3_data()
    bar3 = (
        Bar(
            init_opts=opts.InitOpts(width="900px", height="600px")
        )
        .add_xaxis([x[0] for x in table3_data])
        .add_yaxis("流量（万）", [x[1] for x in table3_data])
        .set_global_opts(
            title_opts=opts.TitleOpts(title="表3流量统计"),
            xaxis_opts=opts.AxisOpts(axislabel_opts=opts.LabelOpts(rotate=20)),
            # datazoom_opts=[opts.DataZoomOpts(type_="slider")],
        )
    )

    page.add(bar1, bar2, bar3)
    page.render("bar_charts.html")


def create_line_page():
    """创建折线图页面"""
    page = Page(layout=Page.SimplePageLayout)

    # 表1折线图
    table1_data = process_table1_data()
    line1 = (
        Line(
            init_opts=opts.InitOpts(width="900px", height="600px")
        )
        .add_xaxis([x[0] for x in table1_data])
        .add_yaxis("数量（次）", [x[1] for x in table1_data])
        .set_global_opts(
            title_opts=opts.TitleOpts(title="表1分类趋势"),
            xaxis_opts=opts.AxisOpts(axislabel_opts=opts.LabelOpts(rotate=20)),
            # datazoom_opts=[opts.DataZoomOpts(type_="slider")],
        )
    )

    # 表2折线图
    table2_data = process_table2_data()
    line2 = (
        Line(
            init_opts=opts.InitOpts(width="900px", height="600px")
        )
        .add_xaxis([x[0] for x in table2_data])
        .add_yaxis("数量（次）", [x[1] for x in table2_data])
        .set_global_opts(
            title_opts=opts.TitleOpts(title="表2IP趋势"),
            xaxis_opts=opts.AxisOpts(axislabel_opts=opts.LabelOpts(rotate=20)),
            # datazoom_opts=[opts.DataZoomOpts(type_="slider")],
        )
    )

    # 表3折线图
    table3_data = process_table3_data()
    line3 = (
        Line(
            init_opts=opts.InitOpts(width="900px", height="600px")
        )
        .add_xaxis([x[0] for x in table3_data])
        .add_yaxis("流量（万）", [x[1] for x in table3_data])
        .set_global_opts(
            title_opts=opts.TitleOpts(title="表3流量趋势"),
            xaxis_opts=opts.AxisOpts(axislabel_opts=opts.LabelOpts(rotate=20)),
            # datazoom_opts=[opts.DataZoomOpts(type_="slider")],
        )
    )

    page.add(line1, line2, line3)
    page.render("line_charts.html")


if __name__ == "__main__":
    # 生成三个页面
    create_pie_page()
    create_bar_page()
    create_line_page()
    print("图表已生成：pie_charts.html, bar_charts.html, line_charts.html")