scrapy爬取的数据异步存储至MySQL

以scrapy爬虫爬取简书中全部的页面详情数据为例：

1.cmd执行scrapy genspider -t crawl jbooks jianshu.com 

创建完爬虫项目后最好为其创建一个脚本启动文件start.py 文件在项目根目录即可

from scrapy import cmdline #启动爬虫命令
cmdline.execute('scrapy crawl js'.split())

去配置文件更改默认的配置信息：

1.robot协议必须改为false

ROBOTSTXT_OBEY = False

2.添加准备好的请求头信息，防止被网站识别：

DEFAULT_REQUEST_HEADERS = { 'Accept': 'text/html,application/xhtml+xml,application/xml;q=0.9,*/*;q=0.8', 'Accept-Language': 'en', 'User-Agent': 'Mozilla/5.0 (Windows NT 6.3; Trident/7.0; rv 11.0) like Gecko', }

至此，新项目的配置完成，开始编写爬虫脚本。

先明确要获取的字段数据，在items完成：

import scrapy class JbookItem(scrapy.Item): title = scrapy.Field()          #标题
    content = scrapy.Field()        #内容
    article_id = scrapy.Field()     #文章ID
    source_url = scrapy.Field()     #源地址
    author =scrapy.Field()          #作者
    avatar = scrapy.Field()         #作者头像
    pub_time = scrapy.Field()       #发布日期

爬虫脚本代码：

# -*- coding: utf-8 -*-
import scrapy from scrapy.linkextractors import LinkExtractor from scrapy.spiders import CrawlSpider, Rule from jbook.items import JbookItem class JsSpider(CrawlSpider): name = 'js' allowed_domains = ['jianshu.com'] start_urls = ['https://www.jianshu.com/'] rules = ( #匹配当前页面中全部的url
        Rule(LinkExtractor(allow=r'.*/p/[0-9a-z]{12}.*'), callback='parse_detail', follow=True), ) def parse_detail(self, response): title = response.xpath('//div[@class="article"]/h1/text()').get().strip()           #标题
        # content = response.xpath('//div[@class="show-content-free"]//text()').getall()
        # cont = ''.join(content).strip() #内容
        content = response.xpath('//div[@class="show-content-free"]').get() url = response.url url1 = url.split("?")[0] article_id = url1.split('/')[-1]                                                    #文章id
 author = response.xpath('//div[@class="info"]/span[@class="name"]/a/text()').get().strip()  #作者
        avatar = response.xpath('//div[@class="author"]/a[@class="avatar"]/img/@src').get() avat = "https:"+avatar.split('?')[0]                                                #头像
 pub_time = response.xpath('//span[@class="publish-time"]/text()').get().strip()     #发布日期
 item = JbookItem(title=title, content=content, article_id=article_id, source_url=url, author=author, avatar=avat, pub_time=pub_time ) yield item

将item字典对象中的数据传递给管道文件去操作：piplines.py

import pymysql class JbookPipeline(object): def __init__(self): dbparams ={ 'host':'127.0.0.1', 'port':3306, 'user':'root', 'password':'root1234', 'database':'jbook', 'charset':'utf8' } self.conn = pymysql.connect(**dbparams)             #创建数据库连接对象
        self.cursor = self.conn.cursor()                    #创建一个游标对象
        self._sql =None                                     #初始化sql语句

    def process_item(self, item, spider): self.cursor.execute(self.sql, (item['title'], item['content'], item['author'], item['avatar'], item['pub_time'], item['article_id'], item['source_url'], )) self.conn.commit() #提交插入语句
        return item @property def sql(self): if not self._sql: self._sql=""" insert into article (id,title,content,author,avatar,pub_time,article_id,source_url) value (null,%s,%s,%s,%s,%s,%s,%s) """
            return self._sql return self._sql

import pymysql from twisted.enterprise import adbapi from pymysql import cursors class JbookTwistedPipeline(object): def __init__(self): dbparams = { 'host': '127.0.0.1', 'port': 3306, 'user': 'root', 'password': 'root1234', 'database': 'jbook', 'charset': 'utf8', 'cursorclass': cursors.DictCursor } self.dbpool = adbapi.ConnectionPool('pymysql',**dbparams) self._sql= None @property def sql(self): if not self._sql: self._sql = """ insert into article (id,title,content,author,avatar,pub_time,article_id,source_url) value (null,%s,%s,%s,%s,%s,%s,%s) """
            return self._sql return self._sql def process_item(self,item,spider): #对sql语句进行处理
        defer = self.dbpool.runInteraction(self.insert_item,item)  #执行函数insert_item 去插入数据
        defer.addErrback(self.handle_error,item,spider)            #遇到错误信息调用 handle_error方法

    def insert_item(self, cursor, item): cursor.execute(self.sql, ( item['title'], item['content'], item['author'], item['avatar'], item['pub_time'], item['article_id'], item['source_url'], )) def handle_error(self,error,item,spider): print('='*20+'error'+'='*20) print(error) print('='*20+'error'+'='*20)

在配置文件中开启piplines管道文件：

ITEM_PIPELINES = { # 'jbook.pipelines.JbookPipeline': 300,
   'jbook.pipelines.JbookTwistedPipeline': 300, }