Python爬虫爬取全球疫情数据并存储到mysql数据库的步骤

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对象