爬虫0601：scrapy实战scrapy实战：智联招聘数据采集

scrapy startproject zhilianspider

# -*- coding: utf-8 -*-

# Define here the models for your scraped items
#
# See documentation in:
# https://doc.scrapy.org/en/latest/topics/items.html
# 引入scrapy模块
import scrapy


class ZhilianItem(scrapy.Item):
    '''
    创建一个Item类型，用于定义爬虫采集的数据字段
    '''
    # 通过scrapy.Field()函数定义属性字段
    # 工作岗位名称
    job_name = scrapy.Field()
    # 发布公司名称
    company = scrapy.Field()
    # 岗位月薪
    salary = scrapy.Field()

# 创建数据库
CREATE DATABASE zhilian_spider DEFAULT CHARSET 'utf8';

USE zhilian_spider;

# 创建数据表
CREATE TABLE jobs(
    id INT AUTO_INCREMENT PRIMARY KEY,
    job_name VARCHAR(200),
    company VARCHAR(200),
    salary VARCHAR(50)
);

# 初始查询，是一张空表，无数据
SELECT * FROM jobs;

# coding:utf-8

import scrapy
from ..items import ZhilianItem
#http://sou.zhaopin.com/jobs/searchresult.ashx?jl=%E5%8C%97%E4%BA%AC&kw=%E7%88%AC%E8%99%AB&sm=0&sg=bb28054b0714445f85382f12e09c9228&p=1

class ZhilianSpider(scrapy.Spider):
    '''
    爬虫核心程序开发
    '''
    # 定义名称，用于命令行启动执行爬虫使用
    name = "zlspider"
    # 定义限定域名，防止跨域数据采集
    allowed_domains = ["zhaopin.com"]
    # 初始采集URL地址
    start_urls = (
        "http://sou.zhaopin.com/jobs/searchresult.ashx?jl=%E5%8C%97%E4%BA%AC&kw=%E7%88%AC%E8%99%AB&sm=0&sg=bb28054b0714445f85382f12e09c9228&p=1",
    )

    def parse(self, response):
        '''
        采集处理函数，scrapy下载器采集下载的数据存放在response中
        :param response:
        :return:
        '''
        # 通过xpath筛选得到当前工作列表
        job_list = response.xpath("//div[@id='newlist_list_content_table']/table[@class='newlist'][position()>1]")

        # 循环获取每个工作信息
        for job in job_list:
            # 筛选工作名称
            job_name = job.xpath("tr[1]/td[@class='zwmc']/div/a").xpath("string(.)").extract()[0]
            # 筛选发布公司
            company = job.xpath("tr[1]/td[@class='gsmc']/a").xpath("string(.)").extract()[0]
            # 筛选薪水待遇
            salary = job.xpath("tr[1]/td[@class='zwyx']").xpath("string(.)").extract()[0]

            # 封装生成item对象，交给pipelines模块进行后续数据验证和存储
            item = ZhilianItem()
            item['job_name'] = job_name
            item['company'] = company
            item['salary'] = salary

            yield item

# -*- coding: utf-8 -*-

# Define your item pipelines here
#
# Don't forget to add your pipeline to the ITEM_PIPELINES setting
# See: https://doc.scrapy.org/en/latest/topics/item-pipeline.html
# 引入sqlalchemy模块
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
# 进入pymysql模块，用于替代sqlalchemy底层的mysqldb
import pymysql
pymysql.install_as_MySQLdb()


class ZhilianPipeline(object):
    '''
    智联招聘爬虫管道模块，进行数据验证和存储
    '''
    def __init__(self):
        # 打开和数据库的连接引擎，获取连接会话对象
        engine = create_engine("mysql://root:@localhost/zhilian_spider?charset=utf8")
        Session = sessionmaker(bind=engine)
        self.session = Session()


    def process_item(self, item, spider):
        # 生成sql语句
        zl_sql = "insert into jobs(job_name, company, salary) values('%s', '%s', '%s')" % \
                 (item['job_name'], item['company'], item['salary'])
        # 执行sql语句
        self.session.execute(zl_sql)
        return item

    def close_spider(self, spider):
        # 提交数据并关闭数据库连接会话
        self.session.commit()
        self.session.close()

scrapy crawl zlspider

select * from jobs;