利用青龙面板备份搬瓦工VPS信息到数据库，并利用navicat生成图表

1. 在搬瓦工VPS中获取API信息

获取VEID和API KEY，下面的接口中会有用到

搬瓦工VPS面板中获取VEID和API KEY

2. 创建数据库

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for banwagon
-- ----------------------------
DROP TABLE IF EXISTS `banwagon`;
CREATE TABLE `banwagon`  (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `ve_status` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '状态',
  `ve_mac1` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT 'MAC地址',
  `ve_used_disk_space_b` bigint(20) NULL DEFAULT NULL COMMENT '硬盘已用空间',
  `ve_disk_quota_gb` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '硬盘配额GB',
  `ssh_port` int(11) NULL DEFAULT NULL COMMENT 'SSH端口',
  `load_average` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '平均负载',
  `mem_available_kb` int(32) NULL DEFAULT NULL COMMENT '可用内存KB',
  `swap_total_kb` int(32) NULL DEFAULT NULL COMMENT 'SWAP总额KB',
  `swap_available_kb` int(32) NULL DEFAULT NULL COMMENT 'SWAP可用KB',
  `node_location_id` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '节点位置ID',
  `node_location` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '节点位置',
  `node_datacenter` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '节点数据中心',
  `location_ipv6_ready` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '是否支持IPV6',
  `plan` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '计划名称',
  `plan_monthly_data` bigint(20) NULL DEFAULT NULL COMMENT '每月流量B',
  `monthly_data_multiplier` double NULL DEFAULT NULL COMMENT '某些位置提供更昂贵的带宽;此变量包含带宽计费系数。',
  `plan_disk` bigint(20) NULL DEFAULT NULL COMMENT '磁盘配额（字节）',
  `plan_ram` bigint(20) NULL DEFAULT NULL COMMENT 'RAM（字节）',
  `plan_swap` int(32) NULL DEFAULT NULL COMMENT 'SWAP（字节）',
  `os` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '系统',
  `email` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '邮箱',
  `data_counter` bigint(20) NULL DEFAULT NULL COMMENT '当前账单月使用的数据传输量。需要乘以 monthly_data_multiplier',
  `data_next_reset` bigint(20) NULL DEFAULT NULL COMMENT '传输计数器重置的日期和时间（UNIX 时间戳）',
  `ip_addresses` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT 'IP地址',
  `suspended` tinyint(1) NULL DEFAULT NULL COMMENT '暂停',
  `policy_violation` tinyint(1) NULL DEFAULT NULL COMMENT '违反政策',
  `total_abuse_points` int(32) NULL DEFAULT NULL COMMENT '暂停次数（当前日历年）',
  `max_abuse_points` int(32) NULL DEFAULT NULL COMMENT '累积的滥用点总数(计划在一个日历年)',
  `free_ip_replacement_interval` int(32) NULL DEFAULT NULL COMMENT '免费ip更换间隔',
  `error` int(32) NULL DEFAULT NULL COMMENT '已执行命令的退出状态码',
  `veid` bigint(20) NULL DEFAULT NULL COMMENT 'veid',
  `create_at` datetime NULL DEFAULT current_timestamp() COMMENT '创建时间',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 5 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;

3. 青龙面板新增脚本和任务

在青龙中新建脚本后添加任务，定时执行，就能定时将VPS状态信息存进数据库啦

import requests
import mysql.connector

# --- Configuration ---
API_ENDPOINT = "https://api.64clouds.com/v1/getLiveServiceInfo?veid=<YOUR_VEID_HERE>&api_key=<YOUR_API_KEY_HERE>" #改配置
DATABASE_HOST = "DATABASE_HOST" #改配置
DATABASE_USER = "DATABASE_USER" #改配置 
DATABASE_PASSWORD = "DATABASE_PASSWORD" #改配置
DATABASE_NAME = "YOUR_DATABASE_NAME" #改配置
DATABASE_TABLE = "DATABASE_TABLE" #改配置
DATABASE_PORT = 3306 #改配置


# --- API Call ---
try:
    response = requests.get(API_ENDPOINT)
    response.raise_for_status()
    item = response.json()

except requests.exceptions.RequestException as e:
    print(f"Error during API request: {e}")
    exit(1)


# --- Database interaction ---
try:
    mydb = mysql.connector.connect(
        host=DATABASE_HOST,
        user=DATABASE_USER,
        password=DATABASE_PASSWORD,
        database=DATABASE_NAME,
        port=DATABASE_PORT #Added Port setting

    )
    cursor = mydb.cursor(prepared=True)

    #SQL Injection Prevention: Use parameterized queries.
    sql = """
        INSERT INTO banwagon (
            ve_status, ve_mac1, ve_used_disk_space_b, ve_disk_quota_gb, ssh_port, load_average, 
            mem_available_kb, swap_total_kb, swap_available_kb, node_location_id, node_location, 
            node_datacenter, location_ipv6_ready, plan, plan_monthly_data, monthly_data_multiplier, 
            plan_disk, plan_ram, plan_swap, os, email, data_counter, data_next_reset, ip_addresses, 
            suspended, policy_violation, total_abuse_points, max_abuse_points, free_ip_replacement_interval, 
            error, veid
        ) VALUES (
            %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s
        )
    """
    #Map API response keys to database columns.  Handle missing keys gracefully.
    ip_addresses_str = ", ".join(item.get('ip_addresses', []))  # Handle missing or empty list
    values = (
        item.get('ve_status', None), item.get('ve_mac1', None), item.get('ve_used_disk_space_b', 0), item.get('ve_disk_quota_gb', None),
        item.get('ssh_port', 0), item.get('load_average', None), item.get('mem_available_kb', 0), item.get('swap_total_kb', 0),
        item.get('swap_available_kb', 0), item.get('node_location_id', None), item.get('node_location', None),
        item.get('node_datacenter', None), item.get('location_ipv6_ready', None), item.get('plan', None), item.get('plan_monthly_data', 0),
        item.get('monthly_data_multiplier', 0), item.get('plan_disk', 0), item.get('plan_ram', 0), item.get('plan_swap', 0),
        item.get('os', None), item.get('email', None), item.get('data_counter', 0), item.get('data_next_reset', 0), ip_addresses_str,
        item.get('suspended', None), item.get('policy_violation', None), item.get('total_abuse_points', 0), item.get('max_abuse_points', 0),
        item.get('free_ip_replacement_interval', 0), item.get('error', 0), item.get('veid', 0)
    )
    cursor.execute(sql, values)
    mydb.commit()
    print(cursor.rowcount, "records inserted.")

except mysql.connector.Error as err:
    print(f"Error during database interaction: {err}")
except Exception as e: #Catch any other unexpected errors
    print(f"An unexpected error occurred: {e}")
finally:
    if mydb and mydb.is_connected():  # Check if mydb is defined and connected
        cursor.close()
        mydb.close()

4. 在navicat中创建图表

数据入库之后就是将数据以图表方式展示了，这块可以直接用navicat的图表功能，无需编码就能快速创建图表了

  • 4.1 图表中新建工作区

    navicat中的图表功能

  • 4.2 工作区中新建数据源

    可以编辑自己的查询语句，或者自行处理数据，这里我想把已用流量转换成GB形式显示，就这样处理。
    自定义查询
    拖动可以关联表，待会就能选中自定义的字段了，记得点击应用并刷新数据
    关联表

  • 4.3 新建图表，数据源就选择我们刚刚创建的数据源，将需要的数据拖动到对应位置，即可生成对应的图表。

    新建图表并设置字段

  • 4.4 新建仪表板

    新建仪表板并将我们刚刚创建的图表拖动的仪表板中就大功告成啦
    将图表拖动到仪表板中