Python爬蟲爬取全球疫情數(shù)據(jù)并存儲到mysql數(shù)據(jù)庫的步驟

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 '當日現(xiàn)存確診',
 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;

"""
獲取全球疫情數(shù)據(jù)
"""
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'}
 # 創(chuàng)建會話對象
 # 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) # 改變時間格式，插入數(shù)據(jù)庫 日期
 # print(ds)
 c_name=i['name'] #國家
 continent=i['continent'] #所屬大洲
 nowConfirm=i['nowConfirm'] #現(xiàn)有確診
 confirm=i['confirm'] #累計確診
 confirmAdd=i['confirmAdd'] #新增確診
 suspect=i['suspect'] #現(xiàn)有疑似
 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()}開始插入世界數(shù)據(jù)")
 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()}插入世界數(shù)據(jù)完畢")
 else:
 print(f"{time.asctime()}世界數(shù)據(jù)已是最新數(shù)據(jù)！")
 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對象