import requests
import json
import time
def get_details():
  url = 'https://view.inews.qq.com/g2/getOnsInfo?name=disease_h5&callback=jQuery34102848205531413024_1584924641755&_=1584924641756'
  headers ={
      'user-agent': 'Mozilla/5.0 (Windows NT 10.0; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/70.0.3538.25 Safari/537.36 Core/1.70.3741.400 QQBrowser/10.5.3863.400'
    }
  res = requests.get(url,headers=headers)
    #輸出全部信息
    # print(res.text)
  response_data = json.loads(res.text.replace('jQuery34102848205531413024_1584924641755(','')[:-1])
  #輸出這個字典的鍵值 dict_keys(['ret', 'data'])ret是響應值，0代表請求成功，data里是我們需要的數(shù)據(jù)
#   print(response_data.keys())
  """上面已經(jīng)轉化過一次字典，然后獲取里面的data，因為data是字符串，所以需要再次轉化字典
    print(json.loads(reponse_data['data']).keys())
    結果：
    dict_keys(['lastUpdateTime', 'chinaTotal', 'chinaAdd', 'isShowAdd', 'showAddSwitch',
    'areaTree', 'chinaDayList', 'chinaDayAddList', 'dailyNewAddHistory', 'dailyHistory',
    'wuhanDayList', 'articleList'])
    lastUpdateTime是最新更新時間，chinaTotal是全國疫情總數(shù)，chinaAdd是全國新增數(shù)據(jù)，
    isShowAdd代表是否展示新增數(shù)據(jù)，showAddSwitch是顯示哪些數(shù)據(jù)，areaTree中有全國疫情數(shù)據(jù)
  """
  areaTree_data = json.loads(response_data['data'])['areaTree']
  temp=json.loads(response_data['data'])
#   print(temp.keys())
#   print(areaTree_data[0].keys())
  """
  獲取上一級字典里的areaTree
  然后查看里面中國鍵值
  print(areaTree_data[0].keys())
  dict_keys(['name', 'today', 'total', 'children'])
  name代表國家名稱，today代表今日數(shù)據(jù)，total代表總數(shù),children里有全國各地數(shù)據(jù)，我們需要獲取全國各地數(shù)據(jù)，查看children數(shù)據(jù)
  print(areaTree_data[0]['children'])
  這里面是
  name是地區(qū)名稱，today是今日數(shù)據(jù)，total是總數(shù)，children是市級數(shù)據(jù)，
  我們通過這個接口可以獲取每個地區(qū)的總數(shù)據(jù)。我們遍歷這個列表，取出name，這個是省級的數(shù)據(jù)，還需要獲取市級數(shù)據(jù)，
  需要取出name，children（市級數(shù)據(jù)）下的name、total(歷史總數(shù))下的confirm、heal、dead，today(今日數(shù)據(jù))下的confirm（增加數(shù)），
  這些就是我們需要的數(shù)據(jù)
  """
    # print(areaTree_data[0]['children'])
  #   for province_data in areaTree_data[0]['children']:
    #   print(province_data)

  ds= temp['lastUpdateTime']
  details=[]
  for pro_infos in areaTree_data[0]['children']:
    province_name = pro_infos['name'] # 省名
    for city_infos in pro_infos['children']:
      city_name = city_infos['name'] # 市名
      confirm = city_infos['total']['confirm']#歷史總數(shù)
      confirm_add = city_infos['today']['confirm']#今日增加數(shù)
      heal = city_infos['total']['heal']#治愈
      dead = city_infos['total']['dead']#死亡
#       print(ds,province_name,city_name,confirm,confirm_add,heal,dead)
      details.append([ds,province_name,city_name,confirm,confirm_add,heal,dead])
  return details

# d=get_details()
 # print(d)

import requests
import json
import time
def get_history():
  url = 'https://view.inews.qq.com/g2/getOnsInfo?name=disease_other&callback=jQuery341026745307075030955_1584946267054&_=1584946267055'
  headers ={
    'user-agent': 'Mozilla/5.0 (Windows NT 10.0; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/70.0.3538.25 Safari/537.36 Core/1.70.3741.400 QQBrowser/10.5.3863.400'
  }
  res = requests.get(url,headers=headers)
#   print(res.text)
  response_data = json.loads(res.text.replace('jQuery341026745307075030955_1584946267054(','')[:-1])
#   print(response_data)
  data = json.loads(response_data['data'])
#   print(data.keys())
  chinaDayList = data['chinaDayList']#歷史記錄
  chinaDayAddList = data['chinaDayAddList']#歷史新增記錄
  history = {}
  for i in chinaDayList:
    ds = '2021.' + i['date']#時間
    tup = time.strptime(ds,'%Y.%m.%d')
    ds = time.strftime('%Y-%m-%d',tup)#改變時間格式，插入數(shù)據(jù)庫
    confirm = i['confirm']
    suspect = i['suspect']
    heal = i['heal']
    dead = i['dead']
    history[ds] = {'confirm':confirm,'suspect':suspect,'heal':heal,'dead':dead}
  for i in chinaDayAddList:
    ds = '2021.' + i['date']#時間
    tup = time.strptime(ds,'%Y.%m.%d')
    ds = time.strftime('%Y-%m-%d',tup)#改變時間格式，插入數(shù)據(jù)庫
    confirm_add = i['confirm']
    suspect_add = i['suspect']
    heal_add = i['heal']
    dead_add = i['dead']
    history[ds].update({'confirm_add':confirm_add,'suspect_add':suspect_add,'heal_add':heal_add,'dead_add':dead_add})
  return history

# h=get_history()
 # print(h)

import time
import pymysql
import traceback
def get_conn():
  """
  :return: 連接，游標
  """
  # 創(chuàng)建連接
  conn = pymysql.connect(host="127.0.0.1",
          user="root",
          password="000429",
          db="mydb",
          charset="utf8")
  # 創(chuàng)建游標
  cursor = conn.cursor() # 執(zhí)行完畢返回的結果集默認以元組顯示
  return conn, cursor
def close_conn(conn, cursor):
  if cursor:
    cursor.close()
  if conn:
    conn.close()

def update_details():
  """
  更新 details 表
  :return:
  """
  cursor = None
  conn = None
  try:
    li = get_details()
    conn, cursor = get_conn()
    sql = "insert into details(update_time,province,city,confirm,confirm_add,heal,dead) values(%s,%s,%s,%s,%s,%s,%s)"
    sql_query = 'select %s=(select update_time from details order by id desc limit 1)' #對比當前最大時間戳
    cursor.execute(sql_query,li[0][0])
    if not cursor.fetchone()[0]:
      print(f"{time.asctime()}開始更新最新數(shù)據(jù)")
      for item in li:
        cursor.execute(sql, item)
      conn.commit() # 提交事務 update delete insert操作
      print(f"{time.asctime()}更新最新數(shù)據(jù)完畢")
    else:
      print(f"{time.asctime()}已是最新數(shù)據(jù)！")
  except:
    traceback.print_exc()
  finally:
    close_conn(conn, cursor)

 update_details()

def insert_history():
  """
    插入歷史數(shù)據(jù)
  :return:
  """
  cursor = None
  conn = None
  try:
    dic = get_history()
    print(f"{time.asctime()}開始插入歷史數(shù)據(jù)")
    conn, cursor = get_conn()
    sql = "insert into history values(%s,%s,%s,%s,%s,%s,%s,%s,%s)"
    for k, v in dic.items():
      # item 格式 {'2021-01-13': {'confirm': 41, 'suspect': 0, 'heal': 0, 'dead': 1}
      cursor.execute(sql, [k, v.get("confirm"), v.get("confirm_add"), v.get("suspect"),
                 v.get("suspect_add"), v.get("heal"), v.get("heal_add"),
                 v.get("dead"), v.get("dead_add")])

    conn.commit() # 提交事務 update delete insert操作
    print(f"{time.asctime()}插入歷史數(shù)據(jù)完畢")
  except:
    traceback.print_exc()
  finally:
    close_conn(conn, cursor)

# insert_history()

def update_history():
  """
  更新歷史數(shù)據(jù)
  :return:
  """
  cursor = None
  conn = None
  try:
    dic = get_history()
    print(f"{time.asctime()}開始更新歷史數(shù)據(jù)")
    conn, cursor = get_conn()
    sql = "insert into history values(%s,%s,%s,%s,%s,%s,%s,%s,%s)"
    sql_query = "select confirm from history where ds=%s"
    for k, v in dic.items():
      # item 格式 {'2020-01-13': {'confirm': 41, 'suspect': 0, 'heal': 0, 'dead': 1}
      if not cursor.execute(sql_query, k):
        cursor.execute(sql, [k, v.get("confirm"), v.get("confirm_add"), v.get("suspect"),
                   v.get("suspect_add"), v.get("heal"), v.get("heal_add"),
                   v.get("dead"), v.get("dead_add")])
    conn.commit() # 提交事務 update delete insert操作
    print(f"{time.asctime()}歷史數(shù)據(jù)更新完畢")
  except:
    traceback.print_exc()
  finally:
    close_conn(conn, cursor)

# update_history()

create table history(
  ds datetime not null comment '日期',
  confirm int(11) default null comment '累計確診',
  confirm_add int(11) default null comment '當日新增確診',
  suspect int(11) default null comment '剩余疑似',
  suspect_add int(11) default null comment '當日新增疑似',
  heal int(11) default null comment '累計治愈',
  heal_add int(11) default null comment '當日新增治愈',
  dead int(11) default null comment '累計死亡',
  dead_add int(11) default null comment '當日新增死亡',
  primary key(ds) using btree
)engine=InnoDB DEFAULT charset=utf8mb4;
create table details(
  id int(11) not null auto_increment,
  update_time datetime default null comment '數(shù)據(jù)最后更新時間',
  province varchar(50) default null comment '省',
  city varchar(50) default null comment '市',
  confirm int(11) default null comment '累計確診',
  confirm_add int(11) default null comment '新增確診',
  heal int(11) default null comment '累計治愈',
  dead int(11) default null comment '累計死亡',
  primary key(id)
)engine=InnoDB default charset=utf8mb4;