数据来源

url=https://user.guancha.cn/app/pneumonia

数据库

user=root, password=12345678, 
db=COVID19db

代码

import requests
from lxml import etree
import time
# import csv
import pymysql
def data_writer(country1,diagnosis1,died1,cure1,date1):
    conn = pymysql.connect(host=localhost, port=3306, user=root, password=12345678, db=COVID19db, charset=utf8,
                           connect_timeout=1000)
    cursor = conn.cursor()
    sql = """create table if not exists 14COVID2021 (id int(10) primary key auto_increment,国家 varchar(100),确诊 varchar(50),死亡 varchar(50),治愈 varchar(50),日期 varchar(50))"""
    cursor.execute(sql)  # 执行创建表的sql语句
    cursor.execute("show tables")  # 查看创建的表
    time.sleep(3)
    # conn = pymysql.connect(host=localhost, port=3306, user=root, password=12345678, db=COVID19db, charset=utf8,
    #                        connect_timeout=1000)

    cursor = conn.cursor()
    sql = insert into COVID2021(国家,确诊,死亡,治愈,日期) values(%s,%s,%s,%s,%s)
    cursor.execute(sql, (country1,diagnosis1,died1,cure1,date1))
    conn.commit()

def spider():
    headers={User-Agent:Mozilla/5.0 (Windows NT 6.3; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/63.0.3239.132 Safari/537.36}
    pre_url=https://user.guancha.cn/app/pneumonia
    # for x in range(1,11):
    html=requests.get(pre_url,headers=headers)
    # time.sleep(1)
    selector=etree.HTML(html.text)
   
    country2 = 中国
    diagnosis2 = selector.xpath("//div[@class=china]/div[2]/div[2]/div/div[1]/p[1]/text()")[0]
    died2 = selector.xpath("//div[@class=china]/div[2]/div[2]/div/div[4]/p[1]/text()")[0]
    cure2 = selector.xpath("//div[@class=china]/div[2]/div[2]/div/div[3]/p[1]/text()")[0]
    date2 = time.strftime("%Y-%m-%d", time.localtime())
    item = [country2, diagnosis2, died2, cure2]
    data_writer(country2, diagnosis2, died2, cure2, date2)
    print(正在抓取, item)

    country_list=selector.xpath("//div[@class=country]/div")

    for country1 in country_list:
        country=country1.xpath("div[1]/text()")[0]
        diagnosis=country1.xpath("div[2]/text()")[0]
        died=country1.xpath("div[3]/text()")[0]
        cure=country1.xpath("div[4]/text()")[0]
        date1=time.strftime("%Y-%m
        -%d",time.localtime())

        item=[country,diagnosis,died,cure]
        data_writer(country,diagnosis,died,cure,date1)
        print(正在抓取,item)

if __name__==__main__:
    spider()