F:
cd F:\mygithub\VDDataServer
scrapy startproject COVID19

cd COVID19
scrapy genspider Covid19Spider news.qq.com

class Covid19Item(scrapy.Item):
    # define the fields for your item here like:
    # name = scrapy.Field()
    collection = table = 'covid19'
    name = scrapy.Field()
    parent = scrapy.Field()
    new = scrapy.Field()
    now = scrapy.Field()
    total = scrapy.Field()
    cure = scrapy.Field()
    death = scrapy.Field()

    def start_requests(self):
        # 定义要爬取的页面列表
        urls = ["https://news.qq.com/zt2020/page/feiyan.htm#/?ct=United%20States&nojump=1",
                "https://news.qq.com/zt2020/page/feiyan.htm#/global?ct=United%20States&nojump=1"]
        # 循环发送请求
        for i in range(len(urls)):
            if i == 0:
                # 执行中国疫情页面的parer
                yield scrapy.Request(urls[i], callback=self.parse_China, meta={'page': i}, dont_filter=True)
            else:
                 # 执行海外疫情页面的parer
                yield scrapy.Request(urls[i], callback=self.parse_Outsee, meta={'page': i}, dont_filter=True)

# 疫情 中国
    def parse_China(self, response):

        provinces = response.xpath(
            '//*[@id="listWraper"]/table[2]/tbody').extract()

        for prn in provinces:
            item = Covid19Item()
            prnNode = Selector(text=prn)
            item['name'] = prnNode.xpath(
                '//tr[1]/th/p[1]/span//text()').extract_first().replace('区', '')
            item['parent'] = ''
            item['new'] = prnNode.xpath(
                '//tr[1]/td[2]/p[2]//text()').extract_first()
            item['now'] = prnNode.xpath(
                '//tr[1]/td[1]/p[1]//text()').extract_first()
            item['total'] = prnNode.xpath(
                '//tr[1]/td[2]/p[1]//text()').extract_first()
            item['cure'] = prnNode.xpath(
                '//tr[1]/td[3]/p[1]//text()').extract_first()
            item['death'] = prnNode.xpath(
                '//tr[1]/td[4]/p[1]//text()').extract_first()

            cityNodes = prnNode.xpath('//*[@class="city"]').extract()
            for city in cityNodes:
                cityItem = Covid19Item()
                cityNode = Selector(text=city)
                cityItem['name'] = cityNode.xpath(
                    '//th/span//text()').extract_first().replace('区', '')
                cityItem['parent'] = item['name']
                cityItem['new'] = ''
                cityItem['now'] = cityNode.xpath(
                    '//td[1]//text()').extract_first()
                cityItem['total'] = cityNode.xpath(
                    '//td[2]//text()').extract_first()
                cityItem['cure'] = cityNode.xpath(
                    '//td[3]//text()').extract_first()
                cityItem['death'] = cityNode.xpath(
                    '//td[4]//text()').extract_first()
                yield cityItem

            yield item

 # 海外
    def parse_Outsee(self, response):
        countries = response.xpath(
            '//*[@id="foreignWraper"]/table/tbody').extract()
        for country in countries:
            countryNode = Selector(text=country)
            item = Covid19Item()
            item['name'] = countryNode.xpath(
                '//tr/th/span//text()').extract_first()
            item['parent'] = ''
            item['new'] = countryNode.xpath(
                '//tr/td[1]//text()').extract_first()
            item['now'] = ''
            item['total'] = countryNode.xpath(
                '//tr/td[2]//text()').extract_first()
            item['cure'] = countryNode.xpath(
                '//tr/td[3]//text()').extract_first()
            item['death'] = countryNode.xpath(
                '//tr/td[4]//text()').extract_first()
            yield item

from scrapy import signals
from selenium import webdriver
from selenium.common.exceptions import TimeoutException
from selenium.webdriver.common.by import By
from selenium.webdriver.support.ui import WebDriverWait
from selenium.webdriver.support import expected_conditions as EC
from scrapy.http import HtmlResponse
from logging import getLogger
from time import sleep

class SeleniumMiddelware():
    def __init__(self,timeout=None,service_args=[]):
        self.logger  = getLogger(__name__)
        self.timeout = timeout
        self.browser = webdriver.PhantomJS(service_args=service_args)
        self.browser.set_window_size(1400,700)
        self.browser.set_page_load_timeout(self.timeout)
        self.wait = WebDriverWait(self.browser,self.timeout)
    
    def __del__(self):
        self.browser.close()

    def process_request(self,request,spider):
        self.logger.debug('PhantomJs is Starting')
        page = request.meta.get('page',1)
        try:
            # 访问URL
            self.browser.get(request.url)
            
            # 等待爬取的元素的加载
            if page == 0:
                self.wait.until(EC.presence_of_element_located((By.CSS_SELECTOR,'#listWraper')))
            else:
                self.wait.until(EC.presence_of_element_located((By.CSS_SELECTOR,'#foreignWraper')))
            # sleep(2)
            return HtmlResponse(url=request.url,body=self.browser.page_source,request=request,encoding='utf-8',status=200)
        except TimeoutException:
            return HtmlResponse(url=request.url,status=500,request=request)
    
    @classmethod
    def from_crawler(cls,crawler):
        return cls(timeout=crawler.settings.get('SELENIUM_TIMEOUT'),
                   service_args=crawler.settings.get('PHANTOMJS_SERVICE_ARGS'))
        

SELENIUM_TIMEOUT = 20

PHANTOMJS_SERVICE_ARGS = ['--load-images=false', '--disk-cache=true']

DOWNLOADER_MIDDLEWARES = {
   'COVID19.middlewares.SeleniumMiddelware': 543,
}

class MongoPipeline(object):
    def __init__(self, mongo_uri, mongo_db):
        self.mongo_uri = mongo_uri
        self.mongo_db = mongo_db

    @classmethod
    def from_crawler(cls, crawler):
        return cls(
            mongo_uri=crawler.settings.get('MONGO_URI'),
            mongo_db=crawler.settings.get('MONGO_DB')
        )

    def open_spider(self, spider):
        self.client = pymongo.MongoClient(self.mongo_uri)
        self.db = self.client[self.mongo_db]

    def process_item(self, item, spider):
        self.db[item.collection].insert(dict(item))
        return item

    def close_spider(self, spider):
        self.client.close()

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

    @classmethod
    def from_crawler(cls, crawler):
        return cls(
            host=crawler.settings.get('MYSQL_HOST'),
            database=crawler.settings.get('MYSQL_DB'),
            user=crawler.settings.get('MYSQL_USER'),
            password=crawler.settings.get('MYSQL_PASSWORD'),
            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 close_spider(self, spider):
        self.db.close()

    def process_item(self, item, spider):
        data = dict(item)
        keys = ', '.join(data.keys())
        values = ', '.join(['%s'] * len(data))
        sql = 'insert into {table}({keys}) values ({values}) on duplicate key update'.format(
            table=item.table, keys=keys, values=values)
        update = ','.join([" {key}=%s".format(key=key) for key in data])
        sql += update
        try:
            if self.cursor.execute(sql, tuple(data.values())*2):
                print('successful')
                self.db.commit()
        except pymysql.MySQLError as e:
            print(e)
            self.db.rollback()
        return item

MONGO_URI = 'localhost'
MONGO_DB = 'COVID19'

MYSQL_HOST = 'localhost'
MYSQL_DB = 'covid19'
MYSQL_USER = 'root'
MYSQL_PASSWORD = '123456'
MYSQL_PORT = 3306

ITEM_PIPELINES = {
    'COVID19.pipelines.MongoPipeline': 300,
    'COVID19.pipelines.MySqlPipeLine': 300
}

CREATE DATABASE covid19

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for covid19
-- ----------------------------
DROP TABLE IF EXISTS `covid19`;
CREATE TABLE `covid19`  (
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `parent` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `new` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `now` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `total` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `cure` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `death` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`name`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for dic_lnglat
-- ----------------------------
DROP TABLE IF EXISTS `dic_lnglat`;
CREATE TABLE `dic_lnglat`  (
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `lng` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `lat` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `type` int(0) NULL DEFAULT NULL,
  PRIMARY KEY (`name`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;

scrapy crawl covid19spider

# -*- coding: utf-8 -*-
from multiprocessing import Process
from scrapy import cmdline
import time
import logging
import os

# 配置参数便可, 爬虫名称，运行频率
confs = [
    {
        "spider_name": "covid19spider",
        "frequency": 10,
    },
]
 
 
def start_spider(spider_name, frequency):
    args = ["cd covid19","scrapy", "crawl", spider_name]
    while True:
        start = time.time()
        p = Process(target=cmdline.execute, args=(args,))
        p.start()
        p.join()
        logging.debug("### use time: %s" % (time.time() - start))
        time.sleep(frequency)
 
 
if __name__ == '__main__':
    for conf in confs:
        process = Process(target=start_spider,args=(conf["spider_name"], conf["frequency"]))
        process.start()
        time.sleep(86400)