连接数据库

node.js连接数据库需要先安装mysql包
npm install mysql
连接数据库操作

const mysql = require('mysql')
let options = {
   
  host: "localhost",
  user: "root",
  password: "123123",
  port: "3306",//可选
  database: "mall"
}
//创建与数据库连接的对象
let con = mysql.createConnection(options)
con.connect((err) => {
   
  if(err) {
   
    console.log(err)
  }else {
   
    console.log("连接成功")
  }
})

操作数据库

对数据库的操作

// 查询表
let strSql = "select * from students"
con.query(strSql,(err,results,fields) => {
   
  console.log(err)
  console.log(results)
})

//删除表的操作
let strSql2 = "drop table students"
con.query(strSql2,(err,results) => {
   
  console.log(err)
  console.log(results)
})

//删除库
let strSql3 = "drop database study01"
con.query(strSql3,(err,results) => {
   
  console.log(err)
  console.log(results)
})

// 创建库(要先把options里面的databases删除)
let strSql4 = "create database mall"
con.query(strSql4,(err,results) => {
   
  console.log(err)
  console.log(results)
})

//插入操作
let strSql5 = "insert into users (id,name,number) values (?,?,?)"
con.query(strSql5,["1","tyh","123"],(err,res) => {
   
  console.log(err)
  console.log(res)
})

将爬取的数据存入数据库

这边做个小练习，将爬取的数据存放在数据库中
spider.js

const mysql = require('mysql')
const axios = require('axios')
const cheerio = require('cheerio')

let page = 1
let count = 1
let con = mysql.createConnection({
   
  host: "localhost",
  user: "root",
  password: "123123",
  database: "mall"
})
//获取第N个页面所有书籍的连接
async function getPageUrl(num) {
   
  let httpUrl = "https://sobooks.cc/page/" + num
  let res = await axios.get(httpUrl)
  let $ = cheerio.load(res.data)
  $('#cardslist .card-item .thumb-img>a').each((i,ele) => {
   
    let href =  $(ele).attr("href")
    //根据地址访问详情页面
    getBookInfo(href)
  })
}
getPageUrl(page)

async function getBookInfo(href) {
   
  let res = await axios.get(href)
  let $ = cheerio.load(res.data)
  //书籍图片
  let bookimg = $('.article-content .bookpic img').attr('src')
  //书籍名称
  let bookname = $('.article-content .bookinfo li:nth-child(1)').text()
  bookname = bookname.substring(3,bookname.length)
  //书籍作者
  let author = $('.article-content .bookinfo li:nth-child(2)').text()
  author = author.substring(3,author.length)
  //书籍标签
  let tag = $('.article-content .bookinfo li:nth-child(4)').text()
  tag = tag.substring(3,tag.length)
  //时间
  let pubtime = $('.article-content .bookinfo li:nth-child(5)').text()
  pubtime = pubtime.substring(3,pubtime.length)
  //种类
  let cataory = $('#mute-category > a').text().trim()

  let arr = [bookname,author,tag,pubtime,bookimg,cataory]
  
  //插入数据库
  let strSql = "insert into books (bookname,author,tag,pubtime,bookimg,cataory) values (?,?,?,?,?,?)"
  con.query(strSql,arr,(err,res) => {
   
    console.log("插入完成")
  })
}

运行spider.js后数据库中数据则已经存放