python框架Scrapy中crawlSpider的使用——爬取内容写进MySQL

一、先在MySQL中创建test数据库，和相应的site数据表

二、创建Scrapy工程

#scrapy startproject 工程名
scrapy startproject demo4

三、进入工程目录，根据爬虫模板生成爬虫文件

#scrapy genspider -l # 查看可用模板
#scrapy genspider -t 模板名 爬虫文件名 允许的域名
scrapy genspider -t crawl test sohu.com

四、设置IP池或用户代理（middlewares.py文件）

# -*- coding: utf-8 -*-
# 导入随机模块
import random
# 导入有关IP池有关的模块
from scrapy.downloadermiddlewares.httpproxy import HttpProxyMiddleware
# 导入有关用户代理有关的模块
from scrapy.downloadermiddlewares.useragent import UserAgentMiddleware

# IP池
class HTTPPROXY(HttpProxyMiddleware):
    # 初始化 注意一定是 ip=''
    def __init__(self, ip=''):
        self.ip = ip

    def process_request(self, request, spider):
        item = random.choice(IPPOOL)
        try:
            print("当前的IP是："+item["ipaddr"])
            request.meta["proxy"] = "http://"+item["ipaddr"]
        except Exception as e:
            print(e)
            pass


# 设置IP池
IPPOOL = [
    {"ipaddr": "182.117.102.10:8118"},
    {"ipaddr": "121.31.102.215:8123"},
    {"ipaddr": "1222.94.128.49:8118"}
]


# 用户代理
class USERAGENT(UserAgentMiddleware):
    #初始化 注意一定是 user_agent=''
    def __init__(self, user_agent=''):
        self.user_agent = user_agent

    def process_request(self, request, spider):
        item = random.choice(UPPOOL)
        try:
            print("当前的User-Agent是："+item)
            request.headers.setdefault('User-Agent', item)
        except Exception as e:
            print(e)
            pass


# 设置用户代理池
UPPOOL = [
    "Mozilla/5.0 (Windows NT 10.0; WOW64; rv:52.0) Gecko/20100101 Firefox/52.0", "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/59.0.3071.115 Safari/537.36", "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/51.0.2704.79 Safari/537.36 Edge/14.14393"
]

五、settngs.py配置

COOKIES_ENABLED = False

DOWNLOADER_MIDDLEWARES = {
    # 'scrapy.downloadermiddlewares.httpproxy.HttpProxyMiddleware':123,
    # 'demo4.middlewares.HTTPPROXY' : 125,
    'scrapy.downloadermiddlewares.useragent.UserAgentMiddleware': 2,
    'demo4.middlewares.USERAGENT': 1
}

ITEM_PIPELINES = {
    'demo4.pipelines.Demo4Pipeline': 300,
}

六、定义爬取关注的数据（items.py文件）

# -*- coding: utf-8 -*-
import scrapy
# Define here the models for your scraped items
#
# See documentation in:
# http://doc.scrapy.org/en/latest/topics/items.html

class Demo4Item(scrapy.Item):
    name = scrapy.Field()
    link = scrapy.Field()

七、爬虫文件编写（test.py）

# -*- coding: utf-8 -*-
import scrapy
from scrapy.linkextractors import LinkExtractor
from scrapy.spiders import CrawlSpider, Rule
from demo4.items import Demo4Item

class TestSpider(CrawlSpider):
    name = 'test'
    allowed_domains = ['sohu.com']
    start_urls = ['http://www.sohu.com/']

    rules = (
        Rule(LinkExtractor(allow=('http://news.sohu.com'), allow_domains=('sohu.com')), callback='parse_item',
             follow=False),
        # Rule(LinkExtractor(allow=('.*?/n.*?shtml'),allow_domains=('sohu.com')), callback='parse_item', follow=False),
    )

    def parse_item(self, response):
        i = Demo4Item()
        i['name'] = response.xpath('//div[@class="news"]/h1/a/text()').extract()
        i['link'] = response.xpath('//div[@class="news"]/h1/a/@href').extract()
        #i['description'] = response.xpath('//div[@id="description"]').extract()
        return i

八、管道文件编写（pipelines.py）

# -*- coding: utf-8 -*-
import pymysql
import json
# Define your item pipelines here
#
# Don't forget to add your pipeline to the ITEM_PIPELINES setting
# See: http://doc.scrapy.org/en/latest/topics/item-pipeline.html


class Demo4Pipeline(object):
    def __init__(self):
        # 数据库连接
        self.conn = pymysql.connect(host='localhost', user='root', password='123456', database='chapter17', charset='utf8')
        self.cur = self.conn.cursor()

    def process_item(self, item, spider):
        # 排除空值
        for j in range(0, len(item["name"])):
            nam = item["name"][j]
            lin = item["link"][j]
            print(type(nam))
            print(type(lin))
            # 注意参数化编写
            sql = "insert into site(name,link) values(%s,%s)"
            self.cur.execute(sql,(nam,lin))
            self.conn.commit()
        return item
    def close_spider(self, spider):
        self.cur.close()
        self.conn.close()

九、总结

1.注意在测试完数据库正常运行时，再开始写入数据，当然，在sql参数化处理的过程中，注意格式，千万不要弄错了