Python + MySQL + Web 构建私有 Apple 设备监控系统

2. Node.js API 服务

安装依赖

npm install koa koa-router koa-bodyparser mysql2 dotenv

完整代码（app.js）

const Koa = require('koa');
const Router = require('koa-router');
const bodyParser = require('koa-bodyparser');
require('dotenv').config();

const app = new Koa();
const router = new Router();

// 数据库配置
const dbConfig = {
    host: process.env.DB_HOST || 'localhost',
    port: process.env.DB_PORT || 3306,
    user: process.env.DB_USER || 'root',
    password: process.env.DB_PASSWORD || '',
    database: process.env.DB_NAME || 'devices',
    charset: 'utf8mb4'
};
const mysql = require('mysql2/promise');

// 跨域中间件
app.use(async (ctx, next) => {
    ctx.set('Access-Control-Allow-Origin', '*');
    ctx.set('Access-Control-Allow-Methods', 'GET, POST, PUT, DELETE, OPTIONS');
    ctx.set('Access-Control-Allow-Headers', 'Content-Type, Authorization, X-Requested-With');
    ctx.set('Access-Control-Allow-Credentials', 'true');
    if (ctx.method === 'OPTIONS') {
        ctx.status = 204;
        return;
    }
    await next();
});

app.use(bodyParser());

// 数据库连接池
let pool = null;
async function getPool() {
    if (!pool) {
        pool = mysql.createPool({
            ...dbConfig,
            waitForConnections: true,
            connectionLimit: 10,
            queueLimit: 0
        });
    }
    return pool;
}

// 查询所有设备（完整信息）
router.get('/api/devices/all', async (ctx) => {
    try {
        const pool = await getPool();
        const [rows] = await pool.execute(`
            SELECT id, device_id, name, model, device_class, battery_level, battery_status, os_version, last_location, last_seen, is_online, raw_data, updated_at
            FROM devices ORDER BY updated_at DESC
        `);
        // 解析 JSON 字段
        const devices = rows.map(device => {
            let parsedData = {};
            try {
                if (device.raw_data) {
                    parsedData = typeof device.raw_data === 'string' ? JSON.parse(device.raw_data) : device.raw_data;
                }
            } catch (e) {
                console.error('解析 raw_data 失败:', e);
            }
            let parsedLocation = null;
            try {
                if (device.last_location) {
                    parsedLocation = typeof device.last_location === 'string' ? JSON.parse(device.last_location) : device.last_location;
                }
            } catch (e) {
                console.error('解析 last_location 失败:', e);
            }
            return { ...device, raw_data: parsedData, last_location: parsedLocation };
        });
        ctx.body = { code: 200, message: 'success', data: devices, total: devices.length, timestamp: new Date().toISOString() };
    } catch (error) {
        ctx.status = 500;
        ctx.body = { code: 500, message: '查询失败', error: error.message };
    }
});

// 查询所有设备（简化信息）
router.get('/api/devices', async (ctx) => {
    try {
        const pool = await getPool();
        const [rows] = await pool.execute(`
            SELECT id, device_id, name, model, device_class, battery_level, battery_status, os_version, last_location, last_seen, is_online, updated_at
            FROM devices ORDER BY updated_at DESC
        `);
        ctx.body = { code: 200, message: 'success', data: rows, total: rows.length };
    } catch (error) {
        ctx.status = 500;
        ctx.body = { code: 500, message: '查询失败', error: error.message };
    }
});

// 查询在线设备
router.get('/api/devices/online', async (ctx) => {
    try {
        const pool = await getPool();
        const [rows] = await pool.execute(`
            SELECT * FROM devices WHERE is_online = 1 ORDER BY updated_at DESC
        `);
        ctx.body = { code: 200, message: 'success', data: rows, total: rows.length };
    } catch (error) {
        ctx.status = 500;
        ctx.body = { code: 500, message: '查询失败', error: error.message };
    }
});

// 查询低电量设备（< 30%）
router.get('/api/devices/low-battery', async (ctx) => {
    try {
        const pool = await getPool();
        const [rows] = await pool.execute(`
            SELECT * FROM devices WHERE battery_level < 0.3 AND battery_level IS NOT NULL ORDER BY battery_level ASC
        `);
        ctx.body = { code: 200, message: 'success', data: rows, total: rows.length };
    } catch (error) {
        ctx.status = 500;
        ctx.body = { code: 500, message: '查询失败', error: error.message };
    }
});

// 设备统计信息
router.get('/api/devices/stats', async (ctx) => {
    try {
        const pool = await getPool();
        const [totalRows] = await pool.execute('SELECT COUNT(*) as total FROM devices');
        const [onlineRows] = await pool.execute('SELECT COUNT(*) as count FROM devices WHERE is_online = 1');
        const [lowBatteryRows] = await pool.execute('SELECT COUNT(*) as count FROM devices WHERE battery_level < 0.3 AND battery_level IS NOT NULL');
        const [avgBatteryRows] = await pool.execute('SELECT AVG(battery_level) as avg FROM devices WHERE battery_level IS NOT NULL');
        const [typeRows] = await pool.execute(`
            SELECT device_class, COUNT(*) as count FROM devices WHERE device_class IS NOT NULL GROUP BY device_class
        `);
        ctx.body = {
            code: 200,
            message: 'success',
            data: {
                total: totalRows[0].total,
                online: onlineRows[0].count,
                offline: totalRows[0].total - onlineRows[0].count,
                lowBattery: lowBatteryRows[0].count,
                avgBattery: avgBatteryRows[0].avg ? parseFloat(avgBatteryRows[0].avg).toFixed(2) : null,
                byType: typeRows
            }
        };
    } catch (error) {
        ctx.status = 500;
        ctx.body = { code: 500, message: '查询失败', error: error.message };
    }
});

// 健康检查
router.get('/api/health', async (ctx) => {
    try {
        const pool = await getPool();
        await pool.execute('SELECT 1');
        ctx.body = { code: 200, message: '服务正常', timestamp: new Date().toISOString() };
    } catch (error) {
        ctx.status = 500;
        ctx.body = { code: 500, message: '数据库连接失败', error: error.message };
    }
});

app.use(router.routes()).use(router.allowedMethods());

const PORT = process.env.PORT || 3000;
app.listen(PORT, () => {
    console.log(`🚀 服务器运行在 http://localhost:${PORT}`);
});

3. Web 前端页面

完整代码（index.html）

由于 HTML 代码较长，这里提供关键部分。完整代码请查看项目仓库或根据以下结构自行实现：

核心功能：

1. 设备卡片渲染：根据设备类型（手机/平板/电脑）渲染不同的卡片样式
2. 电量状态判断：自动识别电量格式（小数/百分比），正确显示状态
3. 自动刷新：每 30 秒自动更新设备数据
4. 响应式布局：使用 Tailwind CSS 实现美观的界面

关键 JavaScript 函数：

const API_BASE_URL = 'http://your-api-server:3000';

// 标准化电池电量值（统一转换为 0-1 之间的小数）
function normalizeBatteryLevel(batteryLevel) {
    if (batteryLevel === null || batteryLevel === undefined) return null;
    if (batteryLevel > 1) {
        return batteryLevel / 100; // 百分比格式转小数
    }
    return batteryLevel; // 已经是小数格式
}

// 获取电池颜色
function getBatteryColor(batteryLevel) {
    const normalized = normalizeBatteryLevel(batteryLevel);
    if (normalized === null) return 'bg-gray-400';
    if (normalized >= 0.8) return 'bg-green-500'; // ≥80%
    if (normalized >= 0.3) return 'bg-orange-500'; // 30%-79%
    return 'bg-red-500'; // <30%
}

// 获取状态标签
function getStatusBadge(device) {
    const batteryLevel = device.battery_level;
    const isOnline = device.is_online;
    if (!isOnline) {
        return '<span>离线</span>';
    }
    if (batteryLevel === null || batteryLevel === undefined) {
        return '<span>在线</span>';
    }
    const normalized = normalizeBatteryLevel(batteryLevel);
    if (normalized >= 0.8) {
        return '<span>电量充足</span>';
    } else if (normalized >= 0.3) {
        return '<span>电量中等</span>';
    } else {
        return '<span>电量不足</span>';
    }
}

// 加载设备数据
async function loadDevices() {
    try {
        const response = await fetch(`${API_BASE_URL}/api/devices/all`);
        const result = await response.json();
        if (result.code === 200 && result.data) {
            const devices = result.data;
            // 按设备类型分类
            const phoneDevices = devices.filter(d => d.device_class?.toLowerCase().includes('iphone') || d.name?.toLowerCase().includes('iphone'));
            const tabletDevices = devices.filter(d => d.device_class?.toLowerCase().includes('ipad') || d.name?.toLowerCase().includes('ipad'));
            const computerDevices = devices.filter(d => d.device_class?.toLowerCase().includes('mac') || d.name?.toLowerCase().includes('mac'));
            // 渲染到对应区域
            document.getElementById('phoneDevices').innerHTML = phoneDevices.map(device => renderDeviceCard(device)).join('');
            // ... 其他设备类型
            // 更新统计信息
            updateStats(devices);
        }
    } catch (error) {
        console.error('加载设备失败:', error);
    }
}

// 页面加载时自动加载，每 30 秒自动刷新
document.addEventListener('DOMContentLoaded', () => {
    loadDevices();
    setInterval(loadDevices, 30000);
});

完整 HTML 代码请参考项目中的 web/index.html 文件。

配置说明

环境变量配置

创建 .env 文件：

DB_HOST=localhost
DB_PORT=3306
DB_USER=your_username
DB_PASSWORD=your_password
DB_NAME=your_database
PORT=3000

Python 脚本配置

修改 update_devices.py 中的配置：

ICLOUD_EMAIL = "[email protected]"
ICLOUD_PASSWORD = "your_app_specific_password"  # 建议使用专用密码
CHINA_MAINLAND = True  # 中国大陆账户设为 True
MYSQL_CONFIG = {
    "host": "localhost",
    "port": 3306,
    "user": "your_username",
    "password": "your_password",
    "database": "your_database",
    "charset": "utf8mb4"
}

关键要点

1. batteryLevel 格式

根据 pyicloud 文档，batteryLevel 是 0-1 之间的小数（如 0.85 表示 85%）。前端代码已兼容两种格式（小数和百分比），会自动识别并转换。

2. 设备状态码

• deviceStatus = "200" - 设备在线
• deviceStatus = "201" - 设备离线

3. 离线设备限制

离线时 iCloud API 只返回基本信息（名称、电量），无法获取型号、系统版本等详细信息。这是 iCloud API 的限制，不是代码问题。

4. 会话管理

认证后的会话保存在 .pyicloud/ 目录，有效期约 2 个月。过期后需要重新手动运行脚本完成认证。

使用方法

1. 首次运行

# 运行 Python 脚本（会提示输入验证码）
python3 update_devices.py

# 启动 API 服务
cd api
npm install
npm start

# 在浏览器打开前端页面
open index.html

2. 设置定时任务

macOS - launchd：

# 创建 plist 文件（每 5 分钟执行一次）
launchctl load ~/Library/LaunchAgents/com.icloud.devices.update.plist

Linux - Crontab：

# 编辑 crontab
crontab -e
# 添加任务（每 5 分钟执行一次）
*/5 * * * * cd /path/to/project && /usr/bin/python3 update_devices.py >> logs/cron.log 2>&1

参考资源

• pyicloud 官方文档
• Koa2 官方文档
• Tailwind CSS 文档