import pymysql

import requests

import json

url_foreign = "https://api.inews.qq.com/newsqa/v1/automation/foreign/country/ranklist"

url_inside = "https://view.inews.qq.com/g2/getOnsInfo?name=disease_h5"

header = {"user-agent": "Mozilla/5.0 (Linux; Android 8.0.0; Pixel 2 XL Build/OPD1.170816.004) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/80.0.3987.116 Mobile Safari/537.36"}

response_foreign = requests.get(url_foreign, headers=header)

response_inside = requests.get(url_inside,headers=header)

data_foreign = json.loads(response_foreign.content.decode())

data_str_foreign = data_foreign['data']

data_inside = json.loads(response_inside.content.decode())

data_str_inside = data_inside['data']

data_json_inside = json.loads(data_str_inside)

db = pymysql.connect(host='39.97.109.245', port=3306, user='root', password='abc456', db='yiqing', charset='utf8')

cursor = db.cursor()

confirmed_total = 0

suspected_total = 0

dead_total = 0

healed_total = 0

lastUpdateTime = data_json_inside['lastUpdateTime']

for foreignData in data_str_foreign:

    countryName = foreignData['name']

    confirmed = foreignData['confirm']

    confirmed_total += confirmed

    suspected = foreignData['suspect']

    suspected_total += suspected

    dead = foreignData['dead']

    dead_total += dead

    healed = foreignData['heal']

    healed_total += healed

    # 将外国数据上传到数据库

    sql = "insert into worlddata(id,countryname,confirmed,suspected,dead,healed,lastupdateTime) values({},'{}','{}','{}','{}', '{}','{}')".format(0, countryName, confirmed, suspected, dead, healed, lastUpdateTime)

    cursor.execute(sql)

    db.commit()

ChinaName = data_json_inside['areaTree'][0]['name']

china_confirmed = data_json_inside['areaTree'][0]['total']['confirm']

confirmed_total += china_confirmed

china_suspected = data_json_inside['areaTree'][0]['total']['suspect']

suspected_total += china_suspected

china_healed = data_json_inside['areaTree'][0]['total']['heal']

healed_total += china_healed

china_dead = data_json_inside['areaTree'][0]['total']['dead']

dead_total += dead_total

sql_china = "insert into worlddata(id,countryname,confirmed,suspected,dead,healed,lastupdateTime) values({},'{}','{}','{}','{}', '{}','{}')".format(

    0, ChinaName, china_confirmed, china_suspected, china_dead, china_dead, lastUpdateTime)

cursor.execute(sql_china)

db.commit()

sql_total = "insert into worlddata(id,countryname,confirmed,suspected,dead,healed,lastupdateTime) values({},'{}','{}','{}','{}', '{}','{}')".format(0, 0, confirmed_total, suspected_total, dead_total, healed_total, lastUpdateTime)

cursor.execute(sql_total)

db.commit()