Scrapy(Python)爬虫框架案例实战教程，Mysql存储数据

scrapy startproject  tencent

tencent
├── tencent
│   ├── __init__.py
│   ├── __pycache__
│   ├── items.py        # Items的定义，定义抓取的数据结构
│   ├── middlewares.py  # 定义Spider和DownLoader的Middlewares中间件实现。
│   ├── pipelines.py    # 它定义Item Pipeline的实现，即定义数据管道
│   ├── settings.py     # 它定义项目的全局配置
│   └── spiders         # 其中包含一个个Spider的实现，每个Spider都有一个文件
│       ├── __init__.py
│       └── __pycache__
└── scrapy.cfg    #Scrapy部署时的配置文件，定义了配置文件路径、部署相关信息等内容

scrapy genspider hr hr.tencent.com

# -*- coding: utf-8 -*-
import scrapy

class HrSpider(scrapy.Spider):
    name = 'hr'
    allowed_domains = ['hr.tencent.com']
    start_urls = ['https://hr.tencent.com/position.php?keywords=python&lid=2156']

    def parse(self, response):
        #解析当前招聘列表信息的url地址：
        detail_urls = response.css('tr.even a::attr(href),tr.odd a::attr(href)').extract()
        #遍历url地址
        for url in detail_urls:
            #fullurl = 'http://hr.tencent.com/' + url
            #构建绝对的url地址，效果同上（域名加相对地址）
            fullurl = response.urljoin(url)
            print(fullurl)

import scrapy

class TencentItem(scrapy.Item):
    # define the fields for your item here like:
    # name = scrapy.Field()
    pass

class HrItem(scrapy.Item):
    '''
       人事招聘信息封装类
       （职位id号，名称、位置、类别、要求、人数、职责和要求）
    '''
    table = "hr"  #表名
    id = scrapy.Field()
    title = scrapy.Field()
    location = scrapy.Field()
    type = scrapy.Field()
    number = scrapy.Field()
    duty = scrapy.Field()
    requirement = scrapy.Field()

# -*- coding: utf-8 -*-
import scrapy
from tencent.items import HrItem

class HrSpider(scrapy.Spider):
    name = 'hr'
    allowed_domains = ['hr.tencent.com']
    start_urls = ['https://hr.tencent.com/position.php?keywords=python&lid=2156']

    def parse(self, response):
        #解析当前招聘列表信息的url地址：
        detail_urls = response.css('tr.even a::attr(href),tr.odd a::attr(href)').extract()
        #遍历url地址
        for url in detail_urls:
            #fullurl = 'http://hr.tencent.com/' + url
            #构建绝对的url地址，效果同上（域名加相对地址）
            fullurl = response.urljoin(url)
            #print(fullurl)
            # 构造请求准备爬取招聘详情信息，并指定由parse_page()方法解析回调函数
            yield scrapy.Request(url=fullurl,callback=self.parse_page)

        #获取下一页的url地址
        next_url = response.css("#next::attr(href)").extract_first()
        #判断若不是最后一页
        if next_url != "javascript:;":
            url = response.urljoin(next_url)
            #构造下一页招聘列表信息的爬取
            yield scrapy.Request(url=url,callback=self.parse)

    # 解析详情页
    def parse_page(self,response):
        #构造招聘信息的Item容器对象
        item = HrItem()
        # 解析id号信息，并封装到Item中
        item["id"] = response.selector.re_first('onclick="applyPosition\(([0-9]+)\);"')
        #标题
        item["title"] = response.css('#sharetitle::text').extract_first()
        #位置
        item["location"] = response.selector.re_first('<span class="lightblue l2">工作地点：</span>(.*?)</td>')
        #类别
        item["type"] = response.selector.re_first('<span class="lightblue">职位类别：</span>(.*?)</td>')
        #人数
        item["number"] = response.selector.re_first('<span class="lightblue">招聘人数：</span>([0-9]+)人</td>')
        #工作职责
        duty = response.xpath('//table//tr[3]//li/text()').extract()
        item["duty"] = ''.join(duty)
        #工作要求
        requirement = response.xpath('//table//tr[4]//li/text()').extract()
        item["requirement"] = ''.join(requirement)
        #print(item)
        #交给管道文件
        yield item

CREATE TABLE `hr` (
   `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
   `title` varchar(255) DEFAULT NULL,
   `location` varchar(32) DEFAULT NULL,
   `type` varchar(32) DEFAULT NULL,
   `number` varchar(32) DEFAULT NULL,
   `duty` text DEFAULT NULL,
   `requirement` text DEFAULT NULL,
   PRIMARY KEY (`id`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8

import pymysql

class TencentPipeline(object):
    def process_item(self, item, spider):
        return item

class MysqlPipeline(object):
    def __init__(self,host,user,password,database,port):
        self.host = host
        self.user = user
        self.password = password
        self.database = database
        self.port = port

    @classmethod
    def from_crawler(cls,crawler):
        return cls(
            host = crawler.settings.get("MYSQL_HOST"),
            user = crawler.settings.get("MYSQL_USER"),
            password = crawler.settings.get("MYSQL_PASS"),
            database = crawler.settings.get("MYSQL_DATABASE"),
            port = crawler.settings.get("MYSQL_PORT"),
        )

    def open_spider(self, spider):
        '''负责连接数据库'''
        self.db = pymysql.connect(self.host,self.user,self.password,self.database,charset="utf8",port=self.port)
        self.cursor = self.db.cursor()

    def process_item(self, item, spider):
        '''执行数据表的写入操作'''
        #组装sql语句
        data = dict(item)
        keys = ','.join(data.keys())
        values=','.join(['%s']*len(data))
        sql = "insert into %s(%s) values(%s)"%(item.table,keys,values)
        #指定参数，并执行sql添加
        self.cursor.execute(sql,tuple(data.values()))
        #事务提交
        self.db.commit()
        return item

    def close_spider(self, spider):
        '''关闭连接数据库'''
        self.db.close()

# 忽略爬虫协议
ROBOTSTXT_OBEY = False

# 并发量
CONCURRENT_REQUESTS = 1 

#下载延迟
DOWNLOAD_DELAY = 0

ITEM_PIPELINES = {
    #'educsdn.pipelines.EducsdnPipeline': 300,
    'educsdn.pipelines.MysqlPipeline': 301,
}
MYSQL_HOST = 'localhost'
MYSQL_DATABASE = 'mydb'
MYSQL_USER = 'root'
MYSQL_PASS = ''
MYSQL_PORT = 3306

scrapy crawl hr