🚀 项目标题：Python脚本实现多数据库查询及数据可视化
本项目旨在通过Python脚本实现一系列数据库查询操作，获取服务端设备信息及其区域分布数据，并进行数据处理和可视化展示，包括计算区域分布百分比、变异系数，以及使用pyecharts绘制区域分布百分比图和平均变异系数柱状图。
🚀 快速开始
数据库查询步骤
1. 获取服务端设备ID和所属组号
在db_alpha库中执行以下SQL语句，获取所有的服务端设备ID、所属组号（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
2. 获取单台server的区域分布
拿到上述查询得到的server，在db_beta库中执行以下SQL语句：
select loc.region,
count(1)
from connection_log cnt
left join device_location loc on loc.id = cnt.client_id
where server_id in (
# 这里填入单台server
)
group by loc.region
需要注意的是，查询结果中可能存在region为空的情况，这是因为connection_log表中的client_id并不在db_beta的device_location表中。
3. 处理区域为空的client_id
将上述结果中region为空的client_id，拿到db_alpha库的device_location表中查询：
select loc.region,
count(1)
from device_location loc
where loc.id in (
# 这里填入单台server的区域分布结果中为空的client_id
)
group by loc.region
4. 合并结果
将上述两部分结果合并，得到单台server的区域分布。
数据处理和可视化步骤
1. 计算区域分布百分比和变异系数
对单台server的区域分布结果，计算区域分布的百分比和变异系数。
2. 绘制单台server的区域分布百分比图
使用pyecharts库绘制单台server的区域分布百分比图。
3. 计算每个组的区域分布百分比和变异系数
对每个组的数据，计算区域分布百分比和变异系数。
4. 绘制每个组的区域分布百分比图
使用pyecharts库绘制每个组的区域分布百分比图。
5. 计算每个组的平均变异系数
计算每个组的平均变异系数。
6. 绘制每个组的平均变异系数柱状图
使用pyecharts库绘制每个组的平均变异系数柱状图。
💻 使用示例
基础用法
以下是一个简单的Python脚本示例，用于实现上述数据库查询和数据处理操作：
import pymysql
import pandas as pd
from scipy.stats import variation
from pyecharts.charts import Pie, Bar
from pyecharts import options as opts
# 连接数据库
db_alpha = pymysql.connect(host='localhost', user='root', password='password', database='db_alpha')
db_beta = pymysql.connect(host='localhost', user='root', password='password', database='db_beta')
# 获取服务端设备ID和所属组号
query1 = """
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 = pd.read_sql(query1, db_alpha)
# 遍历每个server，获取区域分布
for server in servers['id']:
# 在db_beta库中查询区域分布
query2 = 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}
group by loc.region
"""
result_beta = pd.read_sql(query2, db_beta)
# 处理region为空的client_id
empty_client_ids = result_beta[result_beta['region'].isnull()]['client_id'].tolist()
query3 = f"""
select loc.region,
count(1)
from device_location loc
where loc.id in ({','.join(map(str, empty_client_ids))})
group by loc.region
"""
result_alpha = pd.read_sql(query3, db_alpha)
# 合并结果
result = pd.concat([result_beta, result_alpha])
# 计算区域分布百分比
result['percentage'] = result['count(1)'] / result['count(1)'].sum()
# 计算变异系数
cv = variation(result['count(1)'])
# 绘制单台server的区域分布百分比图
pie = (
Pie()
.add(
"",
[list(z) for z in zip(result['region'], result['percentage'])],
radius=["30%", "75%"],
)
.set_global_opts(
title_opts=opts.TitleOpts(title=f"Server {server} Region Distribution"),
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}_region_distribution.html")
# 计算每个组的区域分布百分比和变异系数
grouped = servers.groupby('group_no')
for group_no, group in grouped:
# 合并该组所有server的区域分布结果
group_result = pd.DataFrame()
for server in group['id']:
# 读取单台server的区域分布结果
server_result = pd.read_html(f"server_{server}_region_distribution.html")[0]
group_result = pd.concat([group_result, server_result])
# 计算区域分布百分比
group_result['percentage'] = group_result['count(1)'] / group_result['count(1)'].sum()
# 计算变异系数
group_cv = variation(group_result['count(1)'])
# 绘制每个组的区域分布百分比图
pie = (
Pie()
.add(
"",
[list(z) for z in zip(group_result['region'], group_result['percentage'])],
radius=["30%", "75%"],
)
.set_global_opts(
title_opts=opts.TitleOpts(title=f"Group {group_no} Region Distribution"),
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")
# 计算每个组的平均变异系数
group_cvs = grouped['cv'].mean()
# 绘制每个组的平均变异系数柱状图
bar = (
Bar()
.add_xaxis(group_cvs.index.tolist())
.add_yaxis("Average Coefficient of Variation", group_cvs.values.tolist())
.set_global_opts(
title_opts=opts.TitleOpts(title="Average Coefficient of Variation by Group"),
xaxis_opts=opts.AxisOpts(name="Group No"),
yaxis_opts=opts.AxisOpts(name="Coefficient of Variation"),
toolbox_opts=opts.ToolboxOpts(is_show=True)
)
)
bar.render("group_average_cv.html")
# 关闭数据库连接
db_alpha.close()
db_beta.close()
高级用法
上述示例代码仅为基础实现，你可以根据实际需求进行扩展，例如：
- 优化数据库连接和查询性能，如使用连接池、批量查询等。
- 对数据进行更复杂的处理和分析，如添加更多的统计指标。
- 定制可视化图表的样式和布局，使其更符合项目需求。