使用Node.js爬虫存储MySQL数据库

第一次使用node爬虫，发帖记录下

 

1.在MySQL中新建book数据库，创建book表

 

 

2.创建index.js，使用npm安装导入模块：mysql、axios、cheerio、request、iconv-lite

let request = require('request')
let iconv = require('iconv-lite')
let mysql = require('mysql');
let axios = require('axios');
let cheerio = require('cheerio');

 

3.测试使用node.js连接本地MySQL数据库

let mysql = require("mysql");
let options = {
  host:'localhost',
  port:'3306',//可选，默认是3306
  user:'root', //数据库名
  password:'root', //数据库密码
  //database:'book' //选择连接指定的数据库
}
// 创建与数据库的连接的对象，传递参数
let con =  mysql.createConnection(options);
// 建立连接
con.connect((err) => {
  // 如果建立连接失败
  if (err) {
    console.log(err);
  }else {
    console.log('数据库连接成功');
  }
})
// 执行数据库语句
// 创建库
 let strSql4 = "create database mall";
 con.query(strSql4,(err,result) => {
   console.log(err);
   console.log(result);
 })
// 创建表:ps这里不同编译器可能会因为转义符产生报错
    let strSql5 = 'CREATE TABLE `mall`.`Untitled`  (\n' +
    '  `id` int NOT NULL AUTO_INCREMENT,\n' +
    '  `username` varchar(255) NULL,\n' +
     '  `password` varchar(255) NULL,\n' +
     '  `email` varchar(255) NULL,\n' +
    '  PRIMARY KEY (`id`)\n' +
     ');';
 con.query(strSql5,(err,result) => {
   console.log(err);
   console.log(result);
 })

4.找到网站所有书籍对应的css标签并解析URL

5.根据URL找到css标签解析小说的详细信息(书名、作者、简介等)

6.完整代码

let request = require('request')
let iconv = require('iconv-lite')
let mysql = require('mysql');
let axios = require('axios');
let cheerio = require('cheerio');

let page = 77013;
var count = 1;
let options = {
  host:'localhost',
  port:'3306',//可选，默认是3306
  user:'root',
  password:'root',
  database:'book'
}
let con = mysql.createConnection(options)
con.connect((err) => {
  // 如果建立连接失败
  if (err) {
    console.log(err);
  }else {
    console.log('数据库连接成功');
  }
})

//获取第n个页面所有书籍的链接
async function getPageUrl(callback) {
  // let httpUrl = "http://www.qishus.com/txt/" + 69011 + '.html'
  let httpUrl = "http://www.qishus.com/"
  let res = await axios.get(httpUrl)
  // console.log(res.data)
  let $ = cheerio.load(res.data)
  $("ul>li>a").each((i,ele) => {
    var url = "http://www.qishus.com" + $(ele).attr("href")
    // console.log(i);
    // console.log(url);
    callback(url)
  })
}


getPageUrl(function (url) {
    // console.log(url);
    request({url: url, encoding: null}, (error, res, body) => {
      let html = iconv.decode(body, 'gb2312');
      let $ = cheerio.load(html, {decodeEntities: false})
      // 书名
      let bookname = $(".pageMainArea #downInfoTitle").text()
      bookname = bookname.substring(0, bookname.length - 7)
      // 简介
      let bbrief = $(".mainstory #mainSoftIntro p").text()
      // 小说类型
      let type = $(".downInfoRowL b:nth-child(5) ").text()
      // 小说类型-value
      let type_value = $(".downInfoRowL").html()
      // console.log('打印'+type_value);
      if (type_value === null) {
        console.log('打印结束');
        return
      }
      type_value = type_value.split("<b>小说类型：</b>")[1]
      let type_index = type_value.lastIndexOf('
\n' +
          '<b>小说作者')
      type_value = type_value.substring(0, type_index)
      // 小说作者
      let author = $(".downInfoRowL b:nth-child(7) ").text()
      // 小说作者-value
      let author_value = $(".downInfoRowL").html()
      author_value = author_value.split("小说作者：</b>")[1]
      let author_index = author_value.lastIndexOf('
\n' +
          '<b>小')
      author_value = author_value.substring(0, author_index)
      // 小说大小
      let size = $(".downInfoRowL b:nth-child(9) ").text()
      // 小说大小-value
      let size_value = $(".downInfoRowL").html()
      size_value = size_value.split("小说大小：</b>")[1]
      let size_index = size_value.lastIndexOf('
\n' +
          '<b>推荐')
      size_value = size_value.substring(0, size_index)
      // 更新时间
      let time = $(".downInfoRowL b:nth-child(17) ").text()
      // 更新时间-value
      let time_value = $(".downInfoRowL").html()
      time_value = time_value.split("更新时间：</b>")[1]
      let time_index = time_value.lastIndexOf('
\n' +
          '<b>分 享 到')
      time_value = time_value.substring(0, time_index)
      // 下载
      let download = $("#downAddress").html()
      download = download.split('href=\"')[1]
      let index = download.lastIndexOf('\" target')
      download = download.substring(0, index)
      // url链接
      let bookUrl = url;
      // console.log(bookname,time,time_value,download,size,size_value,author,author_value,type,type_value);


      let arr = [bookname, author_value, type_value, size_value, time_value, bookUrl, download, bbrief];
      console.log(arr);
      // 插入数据库
      let strSql = "insert into book (bookname,author_value,type_value,size_value,time_value,bookUrl,download,bbrief) values (?,?,?,?,?,?,?,?)"
      con.query(strSql, arr, (err, result) => {
        if (err) {
          console.log(err);
        } else {
          console.log(result);
        }
      })
    })
  });

 

 代码到这里结束了，后面将数据进行处理开发图书馆web，如有错误还望多多指教