MCP Server Get Database Config

🚀 Python Script for Server Region Distribution Analysis

This Python script is designed to perform a series of database queries and data analysis tasks to obtain the regional distribution of servers, calculate percentages and coefficients of variation, and visualize the results using pyecharts.

🚀 Quick Start

Step 1: Retrieve Server Device IDs and Group Numbers

Execute the following SQL query in the db_alpha database to get all server device IDs and their corresponding group numbers (group_no).

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

Step 2: Obtain Regional Distribution for a Single Server

Use the obtained server IDs to execute the following SQL query in the db_beta database to get the regional distribution of a single server.

select loc.region,
       count(1)
from connection_log cnt
         left join device_location loc on loc.id = cnt.client_id
where server_id in (
    -- Here, fill in a single server ID
)
group by loc.region

Note that there may be cases where the region is empty. This is because the client_id in the connection_log table does not exist in the device_location table of the db_beta database.

Step 3: Query Missing Client IDs in db_alpha

Retrieve the client_id values with empty region from the previous result and query them in the device_location table of the db_alpha database.

select loc.region,
       count(1)
from device_location loc
where loc.id in (
    -- Here, fill in the client_id values with empty region from the previous result
)
group by loc.region

Step 4: Combine the Results

Combine the results from Step 2 and Step 3 to get the regional distribution of a single server.

Step 5: Calculate Percentages and Coefficients of Variation

Calculate the regional distribution percentages and coefficients of variation for a single server, each group, and the average coefficient of variation for each group.

Step 6: Visualize the Results

Use pyecharts to draw the regional distribution percentage chart for a single server, the regional distribution percentage chart for each group, and the bar chart of the average coefficient of variation for each group.

💻 Usage Examples

Basic Usage

# Here you can write the Python code to implement the above steps
# For example, use the `pymysql` library to connect to the database and execute SQL queries
import pymysql

# Connect to the database
conn_alpha = pymysql.connect(host='localhost', user='user', password='password', database='db_alpha')
conn_beta = pymysql.connect(host='localhost', user='user', password='password', database='db_beta')

# Execute SQL queries and process the results
# ...

# Use pyecharts to draw charts
from pyecharts import options as opts
from pyecharts.charts import Bar

bar = (
    Bar()
    .add_xaxis(["Region A", "Region B", "Region C"])
    .add_yaxis("Percentage", [20, 30, 50])
    .set_global_opts(title_opts=opts.TitleOpts(title="Regional Distribution Percentage"))
)
bar.render("regional_distribution.html")