如何将Node.js中Bluelinky行程数据存入Synology的MariaDB

需求背景 #

在GitHub上找到Bluelinky工具，希望将获取到的车辆行程数据存入Synology设备上的SQL数据库。已能查询到目标数据，但不清楚如何结构化并入库。

原始数据格式 #

控制台输出的行程数据格式如下：

[
{
timeRaw: '160543',
start: 2022-12-22T15:05:43.000Z,
end: 2022-12-22T15:10:43.000Z,
durations: { drive: 5, idle: 1 },
speed: { avg: 34, max: 57 },
distance: 2
},
{
timeRaw: '081630',
start: 2022-12-22T07:16:30.000Z,
end: 2022-12-22T07:20:30.000Z,
durations: { drive: 4, idle: 1 },
speed: { avg: 41, max: 82 },
distance: 2
}
]
数据条目数量随每日出行次数变化，可能为0或5条。

解决过程 #

1. 数据扁平化：将嵌套的durations、speed等字段拆解为数据库可直接存储的扁平字段。
2. 单条数据插入：实现单条数据插入MariaDB的逻辑。
3. 批量插入问题修复：最初遇到循环批量插入失败的问题，最终通过将插入语句放在循环内、pool.end()放在循环外解决了数据库连接过早关闭的问题。

完整实现代码 #

// 连接Bluelinky
const BlueLinky = require("bluelinky");

const client = new BlueLinky({
  username: '*****************',
  password: '*************',
  brand: 'hyundai', // 'hyundai', 'kia'
  region: '**', // 'US', 'EU', 'CA'
  pin: '********'
});

// 客户端登录成功时触发
client.on("ready", async () => {
  const vehicle = client.getVehicle("**************");
  // 获取指定日期行程数据，可替换为获取当日数据的逻辑
  // const trpInfo = await vehicle.tripInfo({year: new Date().getFullYear(), month: new Date().getMonth()+1, day: new Date().getDate()});
  const trpInfo = await vehicle.tripInfo({year: 2022, month: 12, day: 4});

  if (trpInfo != '') {
    // 构造日期相关字段
    const tripdaydateraw = trpInfo[0].dayRaw.slice(4,6) + '/' + trpInfo[0].dayRaw.slice(6,8) + '/' + trpInfo[0].dayRaw.slice(0,4);
    const tripdaydate = trpInfo[0].dayRaw.slice(0,4) + '-' + trpInfo[0].dayRaw.slice(4,6) + '-' + trpInfo[0].dayRaw.slice(6,8);
    const tripdayname = new Date(tripdaydateraw).toLocaleDateString('de-de', {weekday: 'long'});
    // 当日行程统计字段
    const tripdayCnt = trpInfo[0].tripsCount;
    const tripdayDistance = trpInfo[0].distance;
    const tripdayavgSpeed = trpInfo[0].speed.avg;
    const tripdaymaxSpeed = trpInfo[0].speed.max;
    
    // 循环处理每一条行程数据
    let i = 0, n = tripdayCnt - 1;
    while(i <= n) {
      // 提取单条行程的字段
      const tripavgSpeed = trpInfo[0].trips[i].speed.avg;
      const tripmaxSpeed = trpInfo[0].trips[i].speed.max;
      const strttimstamp = trpInfo[0].trips[i].start;
      const endtimstamp = trpInfo[0].trips[i].end;
      const strtime = strttimstamp.toLocaleTimeString();
      const endtime = endtimstamp.toLocaleTimeString();

      // 将分钟转换为hh:mm格式
      function toHoursAndMinutes(totalMinutes) {
        const minutes = totalMinutes % 60;
        const hours = Math.floor(totalMinutes / 60);
        return `${padTo2Digits(hours)}:${padTo2Digits(minutes)}`;
      }
      function padTo2Digits(num) {
        return num.toString().padStart(2, '0');
      }
      const drvtime = toHoursAndMinutes(trpInfo[0].trips[i].durations.drive);
      const drvdistance = trpInfo[0].trips[i].distance;

      // 构造SQL插入语句，循环执行批量插入
      pool.getConnection()
        .then(conn => {
          conn.query(
            "INSERT INTO nightfury_trips (tripdaydate, tripdayCnt, tripdayDistance, tripdayavgSpeed, tripdaymaxSpeed, tripavgSpeed, tripmaxSpeed, strtime, endtime, drvtime_minutes, drvdistance, tripdayname) VALUES(?,?,?,?,?,?,?,?,?,?,?,?)", 
            [tripdaydate, tripdayCnt, tripdayDistance, tripdayavgSpeed, tripdaymaxSpeed, tripavgSpeed, tripmaxSpeed, strtime, endtime, drvtime, drvdistance, tripdayname]
          )
          .then((rows) => {
            console.log(rows);
            conn.end();
          })
          .catch(err => {
            console.log(err);
            conn.end();
          });
        }).catch(() => {
          pool.end();
        });
      console.log(`${tripdaydate},"${tripdayCnt}","${tripdayDistance}","${tripdayavgSpeed}","${tripdaymaxSpeed}","${tripavgSpeed}","${tripmaxSpeed}","${strtime}","${endtime}","${drvtime}","${drvdistance}","${tripdayname}";"";`);
      i += 1;
    }
  }
});

（注：修正了原代码中插入参数与定义变量不一致的问题，将drvtime_minutes替换为实际定义的drvtime）

内容的提问来源于stack exchange，提问作者moses19850