likes
comments
collection
share

我也来爬一爬12306 - Day3 数据库

作者站长头像
站长
· 阅读数 28

本文是《我也来爬一爬12306》系列文件中，第二天的内容，本系列文章的起始文章是：

概述

先来明确一下，在第三天里，我们的主要目的和任务是什么。

在第二天内，我们已经从12306网站上，获取了一个车站信息的列表。第三天的任务稍微简单一点，就是将这个列表写入到数据库中。

SQLite数据库

基于项目操作、移植等方面的考虑，笔者并没有使用原来比较熟悉的Postgres系统。因为这个系统定位在客户端工具集，而不是一个标准的Web服务，数据库的主要作用是在本地进行数据的存储和查询，特别是使用SQL语言，可以很方便的进行中期和后期的操作，而不需要其作为Web支撑系统所需要的网络特性和并发处理能力。

所以，这里的技术选择是SQLite3文件型数据库系统。这一类的数据库也被称为嵌入式数据库。这种数据库没有所谓的存储引擎和网络服务堆栈，数据就是存储在一个文件系统的文件当中，然后通过一个工具程序来连接使用，非常简单和轻便。虽然非常轻量化，但实际上SQLite3对于SQL的标准支持还是比较完善的，一般基本的SQL操作，都是没有问题的，和完整的关系数据库系统差异并不是很大。

关于SQLite3本身的特性和功能，以及和普通关系数据库系统的差异和区别，笔者有机会会另行深入研究探讨，这里就不再展开，主要专注在项目的应用方法。

但是，Nodejs没有内置的SQLite3支持(个人觉得完全应该实现一个，可以有很多的应用场景，如配置信息、字典等等)，所以需要使用一个外部的npm，笔者这里使用的是sqlite3。基本的使用方式如如下：

// 安装
// npm i sqlite3 --save

// 引用
const sqlite3 = require('sqlite3').verbose();

// 数据库文件和实例
const DBFILE =  __dirname+'/tldb.sqlite3';
const SQDB   = new sqlite3.Database(DBFILE);

// 数据库操作
... select, insert ...

如果在创建数据库实例时，指定文件名称，这个实例将会连接到文件映射的数据库系统上，如果文本不存在，还会进行创建，非常方便。

基础程序库

基础程序库中，数据库相关操作的代码和功能如下：


const dbQuery = async (sql, param)=> new Promise((r,j)=>{
    if (param) {
        SQDB.all(sql, param, (err,rows)=>{
            if (err) {
                r(null);
            } else {
                r(rows);
            }
        });
    } else {
        SQDB.all(sql,(err,rows)=>{
            if (err) {
                r(null);
            } else {
                r(rows);
            }
        });
    }
});

const dbExec = async (sql, param)=> new Promise((r,j)=> {
    if (param) {
        // prepared statment
        const stmt = SQDB.prepare(sql);
        if (param?.array) { // run arr
            for (let i = 0; param.data.length; i++) {
                stmt.run(param.data[i]);
            }
        } else {
            stmt.run(param);
        }
        stmt.finalize(err => r(err? false : true));
    } else {
        SQDB.run(sql, err => r(err ? false: true));
    };
});

可以看到，SQLite3 npm，提供了一套API可以进行数据操作，这里进行了一下简单的封装，更易于在当前项目中使用。对于数据库操作，主要有两种类型，一类是查询方式，需要返回一个结果集；一类是操作方式，只看操作结果。在代码中进行了封装和导出，并且都修改成了Promise模式，方便调用执行。

初始化

作为一个本地的文件化的数据库系统。可能会经常遇到移植的情况，所以，数据库文件是可能被实时创建和使用的。

前面已经提到,SQLite3的数据库文件，是在SQLite3数据库对象实例创建和连接时，按需创建的。如果这个文件不存在，则会实时创建一个。显然，这个时候，数据库文件中的内容是空的。按照业务的需求，我们需要在没有数据表的情况下，事先创建一套相关的数据库表，这是数据库初始化过程中的另一个重要的内容。

这个步骤在实现上是两个步骤。第一是准备一套相关的数据库表创建的SQL语句；第二就是在合适的时候(如程序启动时)，来调用这些创建语句。考虑到数据库表可能已经存在内容，我们可能并并不希望在程序启动时将其清除，所以这个语句需要先判断表的存在。最后，还可以准备一套数据库表清除或者删除的语句，已被项目升级和更新时，对数据库表中的内容进行清除，甚至重建。

// 所使用的数据库表
const DB = {
    TABLE_REGIONS   : "tl_regions",
    TABLE_STATIONS  : "tl_stations",
    TABLE_TRAINS    : "tl_trains",
    TABLE_TRAINUM   : "tl_trainums", // trains number add start end number date
    TABLE_STRAINS   : "tl_strains", // station trans 
    TABLE_SCHDULE   : "tl_schdule" // train schdule 
};

// 数据库表创建
const 
// 车站编码 名称 拼音 区域编码
SQL_CREATE = "create table if not exists",
SQL_DROP   = `
Drop table if exists ${DB.TABLE_STATIONS}; 
Drop table if exists ${DB.TABLE_REGIONS}; 
Drop table if exists ${DB.TABLE_TRAINS}; 
Drop table if exists ${DB.TABLE_STRAINS}; 
`,
SQL_TABLE_STATION = `${SQL_CREATE} ${DB.TABLE_STATIONS}  (scode text primary key, name text, pyname text, rcode text, tcount integer default 0) `,
SQL_TABLE_REGION  = `${SQL_CREATE} ${DB.TABLE_REGIONS}  (rcode text primary key, name text) `,
SQL_TABLE_TRAINS  = `${SQL_CREATE} ${DB.TABLE_TRAINS}  (tcode text primary key, name text, pyname text, rcode text) `,
SQL_TABLE_TRAINUM = `${SQL_CREATE} ${DB.TABLE_TRAINUM} (trainum text primary key, tcode text, tdate text, startcode text, endcode text , iflag interger default 0)`,
SQL_TABLE_STRAINS = `${SQL_CREATE} ${DB.TABLE_STRAINS} (scode text , tcode text, iorder integer default 0, iflag interger default 0, UNIQUE(scode,tcode))`,
SQL_TABLE_SCHDULE = `${SQL_CREATE} ${DB.TABLE_SCHDULE} (trainum text, iorder integer default 0, scode text, atime interger default 0, UNIQUE(trainum,scode) )`;

// console.log(SQL_TABLE_STRAINS);
// db init
const dbInit = async ()=>{
    // await dbExec(SQL_CLEAN);
    // await dbExec(SQL_TABLE_STATION);
    // await dbExec(SQL_TABLE_REGION);
    // await dbExec(SQL_TABLE_STRAINS);
    // await dbExec(SQL_TABLE_TRAINUM);
    await dbExec(SQL_TABLE_SCHDULE);
};

由于业务比较简单，数据量也不大，这里的数据库设计也比较简单，大部分使用文本形式的字段。这些SQL语句，都是由基于参数的字符串构造的方式。是考虑到这些操作基本上就只是在程序启动时一次性的执行，基本不会影响程序运行 的过程，但便于程序的调整和维护。

插入和更新车站信息

在项目中，建立完成这个数据库的操作，数据文件和数据库表的初始化之后，我们就可以结合实际的应用过程，来完成那个基本的业务需求了，就是将从12306上获取的车站信息，写入到这个数据库的表当中。


const 
// 车站编码 名称 拼音 区域编码
SQL_ADD_STATION = `insert into ${DB.TABLE_STATIONS}  (scode,name,pyname,rcode) values __VALUES__ ON CONFLICT(scode) DO NOTHING`,
SQL_ADD_REGION  = `insert into ${DB.TABLE_REGIONS}  (rcode,name) values __VALUES__ ON CONFLICT(rcode) DO NOTHING`;

// 插入或更新车站信息, svalues为在前一般准备好的语句片段
dbExec(SQL_ADD_STATION.replace("__VALUES__", svalue) );

// tb regions 
svalue = Array.from(rlist).join(",");
dbExec(SQL_ADD_REGION.replace("__VALUES__", svalue ));

这里有一些需要注意的地方：

  • 使用了insert on conflict模式，可以处理冲突数据，这里是忽略
  • 为了提高操作效率，使用了批量插入的模式，当然也可以轮询插入，但那就不能展现关系数据库批量操作的内涵和精髓了
  • 一般应用系统中，基于安全的考虑(防止SQL注入)，规范的数据插入，应该是执行语句+执行参数的调用方式，但本例中这个数据都是自己生成的，就简化成为简单的字符串构建的SQL语句（拼接构造）
  • 为了方便维护，SQL语句使用模板字符串，然后对数据部分整体构造和替换的模式，便于维护
  • 在数据构造节点，直接的进行数据SQL子句部分的生成，而不是对象数组转换的方式，简化处理，提高效率

数据检查和操作

在开发和调试过程中，我们不可避免的，需要对SQLite3数据文件中的数据进行检查和操作。当然，我们可以使用如navicat、DBeaver等通用的图形化的数据库管理工具，它们一般都具备对SQLite3的支持。但笔者觉得最为简单方便的，还是使用其官方提供的命令行工具，来进行实时的操作。

访问SQLite官方网站的下载页面 www.sqlite.org/download.ht… ，就可以现在对于操作系统的预编译二进制程序文件，一般的名称也是sqlite3(Window版本带有exe后缀)。将其放在系统环境变量指定的目录中，可以保证可以随时调用这个可执行文件。就可以打开命令行工具(如Windows的PowerShell)，并在命令行环境中，使用该工具进行相关的数据库操作了。

如下：

// 执行并连接数据库
PS C:\Work\Dev\timewheel> sqlite3 .\tldb.sqlite3
SQLite version 3.46.0 2024-05-23 13:25:27 (UTF-16 console I/O)
Enter ".help" for usage hints.
sqlite> .help
.archive ...             Manage SQL archives
.auth ON|OFF             Show authorizer callbacks
.backup ?DB? FILE        Backup DB (default "main") to FILE
.bail on|off             Stop after hitting an error.  Default OFF
...

// 数据表清单
sqlite> .table
tl_regions   tl_schdule   tl_stations  tl_strains   tl_trainums


// 普通 sql 查询
sqlite> select * from tl_stations limit 10;
VAP|北京北|beijingbei|0357|1533
BOP|北京东|beijingdong|0357|1617
BJP|北京|beijing|0357|1516
VNP|北京南|beijingnan|0357|1377
IPP|北京大兴|beijingdaxing|0357|17
BXP|北京西|beijingxi|0357|1426
IFP|北京朝阳|beijingchaoyang|0357|1548
CUW|重庆北|chongqingbei|1717|465
CQW|重庆|chongqing|1717|563
CRW|重庆南|chongqingnan|1717|563
Run Time: real 0.003 user 0.000000 sys 0.000000

至此，在本项目中，所有相关数据库操作的流程和要点，都已经完备。我们可以进入到下一个环节中了。

小结

在第三天的工作中，我们将在前一天中获取的数据，写入到了一个本地SQLite数据库文件当中。并且展开讨论了关于在nodejs中使用sqlite3程序库的相关内容，包括npm安装、引用过程、API调用和封装、数据操作和查询、数据表设计、初始化程序等，还讨论了使用sqlite3工具来对数据文件进行查询和管理。

转载自:https://juejin.cn/post/7399983106722218020
评论
请登录