MCP Server Get Database Config

🚀 Python腳本實現數據處理與可視化

本腳本的核心目標是通過一系列數據庫查詢操作，獲取服務端設備的相關信息，計算區域分佈百分比和變異係數，並使用pyecharts庫進行數據可視化展示。

🚀 快速開始

功能概述

腳本主要完成以下幾個步驟：

1. 從db_alpha庫中查詢服務端設備的ID和所屬組號。
2. 針對每臺服務端設備，在db_beta庫中查詢其區域分佈情況。
3. 處理區域為空的情況，從db_alpha庫中補充缺失的區域信息。
4. 合併兩部分結果，計算區域分佈百分比和變異係數。
5. 使用pyecharts庫繪製單臺服務端設備和每個組的區域分佈百分比圖。
6. 計算每個組的平均變異係數，並繪製柱狀圖。

代碼實現

import pymysql
import pandas as pd
from scipy.stats import variation
from pyecharts.charts import Pie, Bar
from pyecharts import options as opts

# 連接數據庫
def connect_db(host, user, password, database):
    return pymysql.connect(host=host, user=user, password=password, database=database)

# 執行SQL查詢
def execute_query(connection, query):
    with connection.cursor() as cursor:
        cursor.execute(query)
        return cursor.fetchall()

# 第一步：在db_alpha庫中查詢服務端設備ID和所屬組號
alpha_conn = connect_db('host_alpha', 'user_alpha', 'password_alpha', 'db_alpha')
server_query = """
select ex.device_id as id, ex.group_no
from service_config_alpha ex
where ex.is_active = 1
  and ex.status = 1
union all
select dv.id, dv.group_no
from device_config_beta dv
where dv.is_active = 1
  and dv.status = 1
"""
servers = execute_query(alpha_conn, server_query)
alpha_conn.close()

# 遍歷每臺服務端設備
for server in servers:
    server_id = server[0]

    # 第二步：在db_beta庫中查詢單臺server的區域分佈
    beta_conn = connect_db('host_beta', 'user_beta', 'password_beta', 'db_beta')
    beta_query = f"""
    select loc.region,
           count(1)
    from connection_log cnt
             left join device_location loc on loc.id = cnt.client_id
    where server_id = {server_id}
    group by loc.region
    """
    beta_result = execute_query(beta_conn, beta_query)

    # 找出區域為空的client_id
    null_client_ids = [row[0] for row in beta_result if row[0] is None]

    # 第三步：將為空的client_id在db_alpha庫的device_location表中查詢
    alpha_conn = connect_db('host_alpha', 'user_alpha', 'password_alpha', 'db_alpha')
    alpha_query = f"""
    select loc.region,
           count(1)
    from device_location loc
    where loc.id in ({','.join(map(str, null_client_ids))})
    group by loc.region
    """
    alpha_result = execute_query(alpha_conn, alpha_query)
    alpha_conn.close()

    # 第四步：合併兩部分結果
    combined_result = beta_result + alpha_result

    # 計算區域分佈百分比
    total_count = sum([row[1] for row in combined_result])
    percentages = [(row[0], row[1] / total_count) for row in combined_result]

    # 計算變異係數
    counts = [row[1] for row in combined_result]
    cv = variation(counts)

    # 第五步：使用pyecharts畫單臺server的區域分佈百分比圖
    pie = (
        Pie()
        .add(
            "區域分佈",
            [list(z) for z in zip([row[0] for row in percentages], [row[1] for row in percentages])],
            radius=["30%", "75%"],
        )
        .set_global_opts(
            title_opts=opts.TitleOpts(title=f"Server {server_id} 區域分佈百分比"),
            legend_opts=opts.LegendOpts(orient="vertical", pos_top="15%", pos_left="2%"),
            toolbox_opts=opts.ToolboxOpts(is_show=True)
        )
        .set_series_opts(label_opts=opts.LabelOpts(formatter="{b}: {d}%"))
    )
    pie.render(f"server_{server_id}_region_distribution.html")

    beta_conn.close()

# 第六步：計算每個組的區域分佈百分比和變異係數
group_data = {}
for server in servers:
    server_id = server[0]
    group_no = server[1]

    # 重複上述步驟獲取單臺server的區域分佈結果
    # ...

    if group_no not in group_data:
        group_data[group_no] = []
    group_data[group_no].extend(combined_result)

# 計算每個組的平均變異係數
group_cvs = {}
for group_no, data in group_data.items():
    counts = [row[1] for row in data]
    cv = variation(counts)
    group_cvs[group_no] = cv

    # 計算每個組的區域分佈百分比
    total_count = sum([row[1] for row in data])
    percentages = [(row[0], row[1] / total_count) for row in data]

    # 使用pyecharts畫每個組的區域分佈百分比圖
    pie = (
        Pie()
        .add(
            "區域分佈",
            [list(z) for z in zip([row[0] for row in percentages], [row[1] for row in percentages])],
            radius=["30%", "75%"],
        )
        .set_global_opts(
            title_opts=opts.TitleOpts(title=f"Group {group_no} 區域分佈百分比"),
            legend_opts=opts.LegendOpts(orient="vertical", pos_top="15%", pos_left="2%"),
            toolbox_opts=opts.ToolboxOpts(is_show=True)
        )
        .set_series_opts(label_opts=opts.LabelOpts(formatter="{b}: {d}%"))
    )
    pie.render(f"group_{group_no}_region_distribution.html")

# 第七步：使用pyecharts畫每個組的平均變異係數柱狀圖
bar = (
    Bar()
    .add_xaxis(list(group_cvs.keys()))
    .add_yaxis("平均變異係數", list(group_cvs.values()))
    .set_global_opts(
        title_opts=opts.TitleOpts(title="每個組的平均變異係數"),
        xaxis_opts=opts.AxisOpts(name="組號"),
        yaxis_opts=opts.AxisOpts(name="平均變異係數"),
        toolbox_opts=opts.ToolboxOpts(is_show=True)
    )
)
bar.render("group_avg_cv.html")

注意事項

• 請根據實際情況修改數據庫連接信息（主機名、用戶名、密碼等）。
• 確保pymysql和pyecharts庫已正確安裝。
• 代碼中的SQL語句可能需要根據實際數據庫表結構進行調整。