Python爬取最新省市区数据,并写入到数据库,可实现数据的三级联动

版权声明:本文为博主原创文章,遵循 CC 4.0 by-sa 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/u012977315/article/details/86151658

写在前面

本文仅做技术学习,勿用于其他途径。

创建项目

在PyCharm的Terminal中输入

scrapy startproject city_spider

定义爬取数据字段

import scrapy


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

爬虫核心代码

在spiders包底下创建city_spider.py文件,输入以下内容

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

import scrapy

from city_spider.items import CitySpiderItem




class city_spider(scrapy.Spider):
    name = 'city_spider'
    start_urls = ['http://www.stats.gov.cn/tjsj/tjbz/tjyqhdmhcxhfdm/2017/']

    """
    解析省数据
    """

    def parse(self, response):
        provinceSpiders = response.xpath("//tr[@class='provincetr']")

        for provinceSpider in provinceSpiders:
            cityItem = CitySpiderItem()
            citytrurl = provinceSpider.xpath(
                './td/a/@href').extract()
            for i in range(len(citytrurl)):
                cityItem['provincetr'] = provinceSpider.xpath('./td/a/text()').extract()[i]
                yield scrapy.Request(url="http://www.stats.gov.cn/tjsj/tjbz/tjyqhdmhcxhfdm/2017/" + citytrurl[i],
                                     meta={'cityItem': copy.deepcopy(cityItem)}, callback=self.parse_city)

    """
    解析市数据
    """

    def parse_city(self, response):
        # 接收上级已爬取的数据
        cityItem = response.meta['cityItem']
        citySpiders = response.xpath("//tr[@class='citytr']")

        for citySpider in citySpiders:
            countytrUrl = citySpider.xpath('./td[2]/a/@href').extract()
            for i in range(len(countytrUrl)):
                cityItem['citytr'] = citySpider.xpath('./td[2]/a/text()').extract()[i]
                yield scrapy.Request(url="http://www.stats.gov.cn/tjsj/tjbz/tjyqhdmhcxhfdm/2017/" + countytrUrl[i],
                                     meta={'cityItem': copy.deepcopy(cityItem)},
                                     callback=self.parse_countytr)

    """
        解析区县数据
        """

    def parse_countytr(self, response):
        # 接收上级已爬取的数据
        cityItem = response.meta['cityItem']
        citySpiders = response.xpath("//tr[@class='countytr']")

        for citySpider in citySpiders:
            cityItem['countytr'] = "".join(citySpider.xpath('./td[2]/a/text()').extract())
            if len(cityItem['countytr']) != 0:
                yield cityItem

因为这是爬取多个页面,组合item的数据,所以需要在解析市的时候传入省的数据,否则数据会混乱。如果仅仅只是写

meta={'cityItem': cityItem}

在解析市时,打印就会发现,数据总是传第一个省的数据。这是因为使用Request函数传递item时,使用的是浅复制(对象的字段值被复制时,字段引用的对象不会被复制),修改成:

 meta={'cityItem': copy.deepcopy(cityItem)}

实现深复制。

配置爬虫

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

# Scrapy settings for city_spider project
#
# For simplicity, this file contains only settings considered important or
# commonly used. You can find more settings consulting the documentation:
#
#     https://doc.scrapy.org/en/latest/topics/settings.html
#     https://doc.scrapy.org/en/latest/topics/downloader-middleware.html
#     https://doc.scrapy.org/en/latest/topics/spider-middleware.html

BOT_NAME = 'city_spider'

SPIDER_MODULES = ['city_spider.spiders']
NEWSPIDER_MODULE = 'city_spider.spiders'

# Crawl responsibly by identifying yourself (and your website) on the user-agent
USER_AGENTS = [
    "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; AcooBrowser; .NET CLR 1.1.4322; .NET CLR 2.0.50727)",
    "Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 6.0; Acoo Browser; SLCC1; .NET CLR 2.0.50727; Media Center PC 5.0; .NET CLR 3.0.04506)",
    "Mozilla/4.0 (compatible; MSIE 7.0; AOL 9.5; AOLBuild 4337.35; Windows NT 5.1; .NET CLR 1.1.4322; .NET CLR 2.0.50727)",
    "Mozilla/5.0 (Windows; U; MSIE 9.0; Windows NT 9.0; en-US)",
    "Mozilla/5.0 (compatible; MSIE 9.0; Windows NT 6.1; Win64; x64; Trident/5.0; .NET CLR 3.5.30729; .NET CLR 3.0.30729; .NET CLR 2.0.50727; Media Center PC 6.0)",
    "Mozilla/5.0 (compatible; MSIE 8.0; Windows NT 6.0; Trident/4.0; WOW64; Trident/4.0; SLCC2; .NET CLR 2.0.50727; .NET CLR 3.5.30729; .NET CLR 3.0.30729; .NET CLR 1.0.3705; .NET CLR 1.1.4322)",
    "Mozilla/4.0 (compatible; MSIE 7.0b; Windows NT 5.2; .NET CLR 1.1.4322; .NET CLR 2.0.50727; InfoPath.2; .NET CLR 3.0.04506.30)",
    "Mozilla/5.0 (Windows; U; Windows NT 5.1; zh-CN) AppleWebKit/523.15 (KHTML, like Gecko, Safari/419.3) Arora/0.3 (Change: 287 c9dfb30)",
    "Mozilla/5.0 (X11; U; Linux; en-US) AppleWebKit/527+ (KHTML, like Gecko, Safari/419.3) Arora/0.6",
    "Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.8.1.2pre) Gecko/20070215 K-Ninja/2.1.1",
    "Mozilla/5.0 (Windows; U; Windows NT 5.1; zh-CN; rv:1.9) Gecko/20080705 Firefox/3.0 Kapiko/3.0",
    "Mozilla/5.0 (X11; Linux i686; U;) Gecko/20070322 Kazehakase/0.4.5",
    "Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.9.0.8) Gecko Fedora/1.9.0.8-1.fc10 Kazehakase/0.5.6",
    "Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/535.11 (KHTML, like Gecko) Chrome/17.0.963.56 Safari/535.11",
    "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_7_3) AppleWebKit/535.20 (KHTML, like Gecko) Chrome/19.0.1036.7 Safari/535.20",
    "Opera/9.80 (Macintosh; Intel Mac OS X 10.6.8; U; fr) Presto/2.9.168 Version/11.52",
]

# Obey robots.txt rules
ROBOTSTXT_OBEY = True

# Configure maximum concurrent requests performed by Scrapy (default: 16)
# CONCURRENT_REQUESTS = 32

# Configure a delay for requests for the same website (default: 0)
# See https://doc.scrapy.org/en/latest/topics/settings.html#download-delay
# See also autothrottle settings and docs
# DOWNLOAD_DELAY = 3
# The download delay setting will honor only one of:
# CONCURRENT_REQUESTS_PER_DOMAIN = 16
# CONCURRENT_REQUESTS_PER_IP = 16

# Disable cookies (enabled by default)
COOKIES_ENABLED = False

# Disable Telnet Console (enabled by default)
# TELNETCONSOLE_ENABLED = False

# Override the default request headers:
DEFAULT_REQUEST_HEADERS = {
    'Accept': 'text/html,application/xhtml+xml,application/xml;q=0.9,*/*;q=0.8',
    "Accept-Encoding": "gzip,deflate"
    # 'Accept-Ranges': 'bytes',
    # 'Content-Encoding': 'gzip',
    # 'Content-Length': '1667',
    # 'Content-Type': 'text/html'
}

# Enable or disable spider middlewares
# See https://doc.scrapy.org/en/latest/topics/spider-middleware.html
# SPIDER_MIDDLEWARES = {
#    'city_spider.middlewares.CitySpiderSpiderMiddleware': 543,
# }

# Enable or disable downloader middlewares
# See https://doc.scrapy.org/en/latest/topics/downloader-middleware.html
# DOWNLOADER_MIDDLEWARES = {
#     #    'cnblogs.middlewares.MyCustomDownloaderMiddleware': 543,
#     'cnblogs.middlewares.RandomUserAgent': 1,
#     'scrapy.contrib.downloadermiddleware.httpproxy.HttpProxyMiddleware': 110,
#     # 'scrapy.downloadermiddlewares.httpproxy.HttpProxyMiddleware': 110,
#     'cnblogs.middlewares.ProxyMiddleware': 100,
# }

# Enable or disable extensions
# See https://doc.scrapy.org/en/latest/topics/extensions.html
# EXTENSIONS = {
#    'scrapy.extensions.telnet.TelnetConsole': None,
# }

# Configure item pipelines
# See https://doc.scrapy.org/en/latest/topics/item-pipeline.html
ITEM_PIPELINES = {
    'city_spider.pipelines.CitySpiderPipeline': 300,
}

# Enable and configure the AutoThrottle extension (disabled by default)
# See https://doc.scrapy.org/en/latest/topics/autothrottle.html
# AUTOTHROTTLE_ENABLED = True
# The initial download delay
# AUTOTHROTTLE_START_DELAY = 5
# The maximum download delay to be set in case of high latencies
# AUTOTHROTTLE_MAX_DELAY = 60
# The average number of requests Scrapy should be sending in parallel to
# each remote server
# AUTOTHROTTLE_TARGET_CONCURRENCY = 1.0
# Enable showing throttling stats for every response received:
# AUTOTHROTTLE_DEBUG = False

# Enable and configure HTTP caching (disabled by default)
# See https://doc.scrapy.org/en/latest/topics/downloader-middleware.html#httpcache-middleware-settings
# HTTPCACHE_ENABLED = True
# HTTPCACHE_EXPIRATION_SECS = 0
# HTTPCACHE_DIR = 'httpcache'
# HTTPCACHE_IGNORE_HTTP_CODES = []
# HTTPCACHE_STORAGE = 'scrapy.extensions.httpcache.FilesystemCacheStorage'

数据处理

# -*- 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
import codecs
import json
import os

from city_spider.spiders import data2db


class CitySpiderPipeline(object):
    """
        将数据保存到json文件,由于文件编码问题太多,这里用codecs打开,可以避免很多编码异常问题
        在类加载时候自动打开文件,制定名称、打开类型(只读),编码
        重载process_item,将item写入json文件,由于json.dumps处理的是dict,所以这里要把item转为dict
        为了避免编码问题,这里还要把ensure_ascii设置为false,最后将item返回回去,因为其他类可能要用到
        调用spider_closed信号量,当爬虫关闭时候,关闭文件 调用数据库操作类
        """

    def __init__(self):
        self.file = codecs.open('cityData.json', 'w', encoding="utf-8")
        self.file.write('[')

    def process_item(self, item, spider):
        lines = json.dumps(dict(item), ensure_ascii=False) + ",\n"
        self.file.write(lines)
        return item

    def close_spider(self, spider):
        self.file.seek(-2, os.SEEK_END)
        self.file.truncate()
        self.file.flush()

        self.file.write(']')
        self.file.close()

        data2db.json2DB()

在爬虫关闭方法内的 data2db.json2DB()方法不调用的话,那么完整的爬虫就写好了,并且把爬取的数据存储成json数据。

创建可实现三级联动的表

CREATE TABLE `tb_area` (
  `city_id` bigint(128) NOT NULL AUTO_INCREMENT COMMENT '城市ID',
  `city_name` varchar(256) NOT NULL COMMENT '城市名称',
  `parent_id` bigint(128) DEFAULT NULL COMMENT '父级ID',
  PRIMARY KEY (`city_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3346 DEFAULT CHARSET=utf8 COMMENT='城市表';

把json数据写入到数据库

import json
import os
from os import path

import pymysql


def json2DB():
    # 打开数据库连接
    # host username password dbname charset
    db = pymysql.connect("localhost", "root", "123456", "db_test", charset="utf8")

    # 使用 cursor() 方法创建多个游标对象,共用一个会导致保存到数据库的数据重复
    cursor = db.cursor()
    cursor1 = db.cursor()
    cursor2 = db.cursor()
    # 获取json文件(前两级目录下的文件)
    filename = os.path.dirname(os.path.dirname(path.dirname(__file__))) + '/cityData.json'

    """
    获取json数据中的省数据,如果数据库没有则插入,有则获取其id,
    查询市的记录,有则获取其id,无则插入并将省id赋值给该数据的父级ID,
    区县处理同市
    """
    with open(filename, 'r', encoding='UTF-8') as f:
        pop_data = json.load(f)

        for pop_dict in pop_data:
            ##省
            provincetr = pop_dict['provincetr']
            # 使用 execute()  方法执行 SQL 查询
            sql_select = "SELECT * from tb_area WHERE city_name = ('%s')" % str(provincetr)
            cursor.execute(sql_select)
            data = cursor.fetchone()
            province_id = None
            if data is None:
                # insert
                sql_insert = "INSERT IGNORE INTO  db_test.tb_area(city_name) values ('%s')" % (str(provincetr))
                cursor.execute(sql_insert)
                db.commit()
                province_id = cursor.lastrowid
            else:
                province_id = data[0]
            ##市
            citytr = pop_dict['citytr']
            sql_select1 = "SELECT * from tb_area WHERE city_name = ('%s')" % str(citytr);
            cursor.execute(sql_select1)
            dataCity = cursor.fetchone()
            city_id = None

            if dataCity is None:
                sql_insert1 = "INSERT IGNORE INTO  db_test.tb_area(city_name,parent_id) values ('%s','%s')" % (
                    str(citytr), str(province_id))
                cursor1.execute(sql_insert1)
                db.commit()
                city_id = cursor1.lastrowid
            else:
                city_id = dataCity[0]

            # 区县
            countytr = pop_dict['countytr']
            sql_select2 = "SELECT * from tb_area WHERE city_name = ('%s') " % str(countytr)
            cursor2.execute(sql_select2)
            dataCity = cursor.fetchone()
            if dataCity is None:
                sql_insert2 = "INSERT IGNORE INTO  db_test.tb_area(city_name,parent_id) values ('%s','%s')" % (
                    str(countytr), str(city_id))
                cursor2.execute(sql_insert2)
                db.commit()

    # 关闭数据库连接
    db.close()

至此,数据爬取、保存到数据库功能完成。

附上代码传送门

 

 

 

展开阅读全文

没有更多推荐了,返回首页