数据爬取

编程爬取每日最新的疫情统计数据，并将爬取结果导入到数据库中，将可视化结果与统计数据结合，实时显示当前最新数据。

1、表结构（MySQL）

2、代码部分

import requests
import json
from pymysql import *


# 连接数据库的方法
def connectDB():
    try:
        db = connect(host='localhost', port=3306, user='root', password='123456', db='yiqing')
        print("数据库连接成功")
        return db
    except Exception as e:
        print(e)
    return NULL


db = connectDB()


# 向数据库中插入数据的方法
def insertInformation(db, table, Date, Province, City, Confirmed_num, Yisi_num, Cured_num, Dead_num, Code):
    cursor = db.cursor()
    try:
        cursor.execute(
            "insert into %s(Date,Province,City,Confirmed_num,Yisi_num,Cured_num,Dead_num,Code) values('%s','%s','%s','%s','%s','%s','%s','%s')" % (
            table, Date, Province, City, Confirmed_num, Yisi_num, Cured_num, Dead_num, Code))
        print("插入成功")
        db.commit()
        cursor.close()
        return True
    except Exception as e:
        print(e)
        db.rollback()
    return False


def queryInformation(city):
    cursor = db.cursor()
    sql = "SELECT * FROM info3 WHERE City =  '%s'" % (city)
    try:
        # print(sql)
        # 执行SQL语句
        cursor.execute(sql)
        # 获取所有记录列表
        results = cursor.fetchone()
        return results[-1]
    except Exception as e:
        print(e)
        db.rollback()


def clearTable(date):
    cursor = db.cursor()
    sql = "delete from info2 where Date = '%s'" % (date)
    try:
        print(sql)
        # 执行SQL语句
        cursor.execute(sql)
    except Exception as e:
        print(e)
        db.rollback()


def get_data():
    url = 'https://view.inews.qq.com/g2/getOnsInfo?name=disease_h5'
    json_text = requests.get(url).json()
    data = json.loads(json_text['data'])
    update_time = data['lastUpdateTime']
    all_counties = data['areaTree']
    all_list = []
    for country_data in all_counties:
        if country_data['name'] == '中国':
            all_provinces = country_data['children']
            for province_data in all_provinces:
                province_name = province_data['name']
                all_cities = province_data['children']
                for city_data in all_cities:
                    city_name = city_data['name']
                    city_total = city_data['total']
                    province_result = {'province': province_name, 'city': city_name, 'update_time': update_time}
                    province_result.update(city_total)
                    all_list.append(province_result)

    clearTable(all_list[0].get("update_time"))
    for info in all_list:
        print(info)
        insertInformation(db, "info1", info.get("update_time"), info.get("province"), info.get("city"),
                          info.get("confirm"), info.get("suspect"), info.get("heal"), info.get("dead"),
                          queryInformation(info.get("city")))


if __name__ == '__main__':
    get_data()

China