CREATE TABLE world(
 id INT(11) NOT NULL AUTO_INCREMENT,
 dt DATETIME NOT NULL COMMENT '日期',
 c_name VARCHAR(35) DEFAULT NULL COMMENT '國家',
 continent VARCHAR(35) DEFAULT NULL COMMENT '所屬大洲',
 nowConfirm INT(11) DEFAULT NULL COMMENT '累計確診',
 confirm INT(11) DEFAULT NULL COMMENT '當日現存確診',
 confirmAdd INT(11) DEFAULT NULL COMMENT '當日新增確診',
 suspect INT(11) DEFAULT NULL COMMENT '剩余疑似',
 heal INT(11) DEFAULT NULL COMMENT '累計治愈',
 dead INT(11) DEFAULT NULL COMMENT '累計死亡',
 confirmAddCut INT(11) DEFAULT NULL COMMENT 'confirmAddCut',
 confirmCompare INT(11) DEFAULT NULL COMMENT 'confirmCompare',
 nowConfirmCompare INT(11) DEFAULT NULL COMMENT 'nowConfirmCompare',
 healCompare INT(11) DEFAULT NULL COMMENT 'healCompare',
 deadCompare INT(11) DEFAULT NULL COMMENT 'deadCompare',
 PRIMARY KEY(id)
)ENGINE=INNODB DEFAULT CHARSET=utf8mb4;

"""
獲取全球疫情數據
"""
def get_world_data():
 url='https://api.inews.qq.com/newsqa/v1/automation/foreign/country/ranklist'
 headers={'user-agent': 'WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/86.0.4240.198 Safari/537.36'}
 # 創建會話對象
 # session = requests.session()
 # 請求接口
 # result = session.get('https://api.inews.qq.com/newsqa/v1/automation/foreign/country/ranklist')
 # 打印結果
 # print(result.text)
 res = requests.get(url, headers=headers)
 # print(res.text)
 response_data_0 = json.loads(res.text.replace('jQuery34102848205531413024_1584924641755(', '')[:-1]) #轉化json對象
 # print(response_data_0.keys())
 # print(response_data_0)
 response_data_1=response_data_0['data']
 # print(response_data_1)
 # print(response_data_1[0].keys())
 # data = jsonpath.jsonpath(resJson_1, '$.data.*')
 # print(resJson_1.keys())
 # for d in data:
 # res = '日期:' + d['date'] + '--' + d['continent'] + '--' + d['name'] + '--' + '新增確診:' + str(
 # d['confirmAdd']) + '累計確診:' + str(d['confirm']) + '治愈:' + str(d['heal']) + '死亡:' + str(d['dead'])
 # file = r'C:/Users/Administrator/Desktop/world_data.txt'
 # with open(file, 'w+', encoding='utf-8') as f:
 # f.write(res + '\n') # 加\n換行顯示
 # f.close()
 world={}
 for i in response_data_1:
 temp=i['y']+'.'+i['date']
 tup = time.strptime(temp, '%Y.%m.%d')
 dt = time.strftime('%Y-%m-%d', tup) # 改變時間格式，插入數據庫 日期
 # print(ds)
 c_name=i['name'] #國家
 continent=i['continent'] #所屬大洲
 nowConfirm=i['nowConfirm'] #現有確診
 confirm=i['confirm'] #累計確診
 confirmAdd=i['confirmAdd'] #新增確診
 suspect=i['suspect'] #現有疑似
 heal=i['heal'] #累計治愈
 dead=i['dead'] #累計死亡
 confirmAddCut=i['confirmAddCut']
 confirmCompare=i['confirmCompare']
 nowConfirmCompare=i['nowConfirmCompare']
 healCompare=i['healCompare']
 deadCompare=i['deadCompare']
 world[c_name] = {'dt':dt ,
  'continent': continent,
  'nowConfirm': nowConfirm,
  'confirm': confirm,
  'confirmAdd': confirmAdd,
  'suspect': suspect,
  'heal': heal,
  'dead': dead,
  'confirmAddCut': confirmAddCut,
  'confirmCompare': confirmCompare,
  'nowConfirmCompare': nowConfirmCompare,
  'healCompare': healCompare,
  'deadCompare': deadCompare,
  }
 return world

def insert_world():
 """
 更新 world 表
 :return:
 """
 cursor = None
 conn = None
 try:
 dic = get_world_data()
 print(dic)
 conn, cursor = get_conn()
 sql = "insert into world values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
 sql_query = 'select %s=(select dt from world order by id desc limit 1)' #對比當前最大時間戳
 cursor.execute(sql_query,dic['美國']['dt'])
 if not cursor.fetchone()[0]:
 print(f"{time.asctime()}開始插入世界數據")
 for k, v in dic.items(): # item 格式 {'2021-01-13': {'confirm': 41, 'suspect': 0, 'heal': 0, 'dead': 1}
 cursor.execute(sql, [0,v.get('dt'), k, v.get("continent"), v.get("nowConfirm"),
  v.get("confirm"), v.get("confirmAdd"),v.get("suspect"),v.get("heal"), v.get("dead")
  , v.get("confirmAddCut"), v.get("confirmCompare"), v.get("nowConfirmCompare"), v.get("healCompare"),
 v.get("deadCompare")])
 conn.commit() # 提交事務
 print(f"{time.asctime()}插入世界數據完畢")
 else:
 print(f"{time.asctime()}世界數據已是最新數據！")
 except:
 traceback.print_exc()
 finally:
 close_conn(conn, cursor)

import requests
import pymysql
import time
import json
import traceback

 url='https://api.inews.qq.com/newsqa/v1/automation/foreign/country/ranklist'
 headers={'user-agent': 'WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/86.0.4240.198 Safari/537.36'}
 
 res = requests.get(url, headers=headers)
 # print(res.text)
 response_data_0 = json.loads(res.text.replace('jQuery34102848205531413024_1584924641755(', '')[:-1]) #轉化json對象