将scrapy爬取到的数据保存到MySQL

cmd操作MySQL

• cmd到mysql的bin目录下
  
• 展示数据库
  • show databases;
• 进入数据库
  • use 数据库名;
• 展示表
  • show tables;
• 创建表
  • create table 表名(属性…);
• 删除表
  • drop 表名;
• 删除表格数据
  • delete from 表名 where …

常用对字符串的操作

• strip()
  • 默认是去掉字符串两端的空格
  • strip(“¥”) 去掉字符串两端的指定字符
  • 若想只对一端进行操作，可以使用lstrip(), rstrip()

案例

===========items.py===========
import scrapy
class Mysql01Item(scrapy.Item):
    # define the fields for your item here like:
    # name = scrapy.Field()

    # 对应数据库列，定义字段
    title = scrapy.Field() # 书名
    author = scrapy.Field()
    intro = scrapy.Field() # 简介
    price = scrapy.Field()
    press = scrapy.Field() # 出版社
    press_time = scrapy.Field()
    comment_num = scrapy.Field() # 评论数
=========settings.py==========
LOG_LEVEL = 'WARNING'
USER_AGENT = 'Mozilla/5.XXXXXXX'
ROBOTSTXT_OBEY = False
ITEM_PIPELINES = {
   'mysql01.pipelines.Mysql01Pipeline': 300,
}
==========dd.py===========
# -*- coding: utf-8 -*-
import scrapy
from mysql01.items import Mysql01Item
# from ..items import Mysql01Item 第二种写法 两个点代表上一级


class DdSpider(scrapy.Spider):
    name = 'dd'
    allowed_domains = ['dangdang.com']
    start_urls = ['http://search.dangXXXX.com/?key=xxxx']
    '''
        翻页的另一中方法
        pageNum = 199
        for page in range(2,pageNum):
            next_url = 'http://search.dangdang.com/?key=%D0%C4%C0%ED%D1%A7&act=input&page_index={}'.format(page)
            # next_url = f'http://search.dangdang.com/?key=%D0%C4%C0%ED%D1%A7&act=input&page_index=(page)'
            yield scrapy.Request(next_url, callback=..., meta={'item' : item})
    '''

    def parse(self, response):
        li_list = response.xpath("//ul[@class='bigimg']/li")
        print('*' * 100)
        # print(len(li_list))

        for li in li_list:
            item = Mysql01Item() # 生成一个item对象，用于存储数据
            item['title'] = li.xpath("./a/@title").extract_first()
            '''
                li.xpath("./p[@class='search_book_author']/span[1]/a[1]/@title") 获取到的是一个 [...]
                li.xpath("./p[@class='search_book_author']/span[1]/a[1]/@title").extract_first() 获取到的数 [...] 中的第一个字符串
            '''
            item['author'] = li.xpath("./p[@class='search_book_author']/span[1]/a[1]/@title").extract_first() if len(li.xpath("./p[@class='search_book_author']/span[1]/a[1]/@title")) > 0 else '无作者信息'
            item['intro'] = li.xpath("./p[@class='detail']/text()").extract_first()
            item['price'] = li.xpath("./p[@class='price']/span[@class='search_now_price']/text()").extract_first()
            item['press'] = li.xpath("./p[@class='search_book_author']/span[3]/a[1]/@title").extract_first()
            item['press_time'] = li.xpath("./p[@class='search_book_author']/span[2]/text()").extract_first()
            item['comment_num'] = li.xpath(".//a[@class='search_comment_num']/text()").extract_first()
            yield item
            # print(item)

        # 找到下一页的url地址
        next_url = response.xpath("//ul[@name='Fy']/li[@class='next']/a/@href").extract_first()
        # print(next_url) next_url字符串若没有，则会返回None

        if next_url != None:
            next_url = 'http://search.dangdang.com' + next_url
            # 翻页操作使用 scrapy.Request(...)
            # 可以yield的类型有：  None, dict, request对象, BaseItem
            yield scrapy.Request(
                next_url,
                callback=self.parse
            )

===========pipelines.py===========
# -*- coding: utf-8 -*-
# 导入第三方库pymysql，先在命令行pip install pymysql安装，用于对MySQL数据库进行操作
import pymysql

class Mysql01Pipeline(object):
    def process_item(self, item, spider):
        # 通过pymysql对本地数据库进行连接
        db = pymysql.connect(host='localhost', user='root', password='root', db='mybatis_plus', charset='utf8')

        # 获取db的游标对象，用于对事务进行处理
        cursor = db.cursor()

        # 对提取到的数据进行再处理，转换成最终存储到数据库中的类型
        title = item['title'].strip()
        author = item['author']
        intro = item['intro']
        price = item['price'].strip("¥") + '元'
        press = item['press']
        pressTime = item['press_time'].strip().strip('/')
        commentNum = item['comment_num'].strip('条评论')

        # 将数据插入到数据表中
        cursor.execute(
            'insert into dd values(%s,%s,%s,%s,%s,%s,%s)',
            (title,author, intro, price, press, pressTime,  commentNum)
        )

        # 使用数据库对象对事务操作进行提交
        db.commit()

        # 关闭游标对象和数据库对象
        cursor.close()
        db.close()

        print('=' * 100)
        # print(item)
        return item