2020年寒假学习进度第十三天
河北省疫情数据爬取
今天主要完成了河北省疫情数据的爬取,从2月8号开始爬取(附python详细代码)
from lxml import etree import re import requests #导入requests包 import SQL as SQL #url = 'http://www.hebwst.gov.cn/index.do?id=397505&templet=content&cid=45' #url ='http://www.hebwst.gov.cn/index.do?id=397291&templet=content&cid=45' #url='http://www.hebwst.gov.cn/index.do?id=395538&templet=content&cid=326' hrefs = [] def ULS(): urls = [] for i in range(6): url='http://www.hebwst.gov.cn/index.do?templet=search_list&searchType=1&searchText=河北省新型冠状病毒感染的肺炎疫情情况&type=search&cid=0&page='+str(i) print(url) strhtml = requests.get(url) tree = etree.HTML(strhtml.text) urls.append(tree.xpath('//td[@class=\'sy_new_list\']/a//@href')) print(urls) for href1 in urls: for href in href1: print(href) href = 'http://www.hebwst.gov.cn/'+ href a = re.match(r'.*?&cid=45', href) if (a): if (href !='http://www.hebwst.gov.cn/index.do?id=395795&templet=content&cid=45'): hrefs.append(href) print(hrefs) def info(url): print(url) strhtml = requests.get(url) # Get方式获取网页数据 tree = etree.HTML(strhtml.text) text=tree.xpath('//p//text()') text[0]=re.sub(r'\u3000','',text[0]) print(text) #新增 date=re.findall(r"(.+?日)", text[0]) print("时间",date) xin_que_num=re.findall(r"新增确诊病例(.+?例)", text[0]) mid = text[0].split("其中", 1)[1] num=len(mid.split("其中", 1)) if num>1: mid=mid.split("其中", 1)[0] xin_shi_num=re.findall(r"[,,、](.+?市)(.+?例)", mid) xin_chu_num = re.findall(r"新增治愈出院病例(.+?例)", text[0]) xin_yi_num = re.findall(r"新增疑似病例(.+?例)", text[0]) print("新增确诊病例",xin_que_num) print("详细新增确诊病例\n",xin_shi_num) print("新增治愈出院病例",xin_chu_num) print("新增疑似病例\n",xin_yi_num) #确诊 que_num=re.findall(r"累计报告确诊病例(.+?例)", text[1]) si_num=re.findall(r"例,其中死亡(.+?例)", text[1]) zhong_num=re.findall(r",现有重症病例(.+?例)", text[1]) yu_num=re.findall(r",累计治愈出院(.+?例)", text[1]) print("累计确诊病例",que_num) print("死亡病例",si_num) print("重症病例",zhong_num) print("出院病例",yu_num) #详细 que_xi_num=[] si_xi_num=[] zhong_xi_num=[] chu_xi_num=[] num=len(text[1].split("确诊病例中",1)) if num>1: mid = text[1].split("确诊病例中", 1)[1] num = len(mid.split("死亡病例中",1)) if num > 1: que=mid.split("死亡病例中",1)[0] que_xi_num = re.findall(r"[,、](.+?市)(.+?例)", que) si=mid.split("死亡病例中",1)[1] mid=si num = len(mid.split("重症病例中", 1)) if num > 1: si=mid.split("重症病例中",1)[0] si_xi_num = re.findall(r"[,、](.+?市)(.+?例)", si) zhong=mid.split("重症病例中",1)[1] mid=zhong num = len(mid.split("出院病例中", 1)) if num > 1: zhong=mid.split("出院病例中",1)[0] zhong_xi_num = re.findall(r"[,、](.+?市)(.+?例)", zhong) chu=mid.split("出院病例中",1)[1] chu_xi_num = re.findall(r"[,、](.+?市)(.+?例)", chu) else: zhong_xi_num = re.findall(r"[,、](.+?市)(.+?例)", zhong) else: si_xi_num = re.findall(r"[,、](.+?市)(.+?例)", si) print("详细确诊病例",que_xi_num) print("详细死亡病例",si_xi_num) print("详细重症病例",zhong_xi_num) print("详细出院病例",chu_xi_num) #疑似 yisi_num=re.findall(r"疑似病例(.+?例)", text[1]) print("疑似病例",yisi_num) #密切接触者 接触医学观察 正在隔离 miqie_num=re.findall(r"密切接触者(.+?人)", text[2]) jie_num=re.findall(r"解除隔离医学观察(.+?人)", text[2]) guan_num=re.findall(r"现有(.+?人)", text[2]) print("密切接触者",miqie_num) print("接触医学观察",jie_num) print("现有医学观察人数",guan_num) SQL.insert_province(date[0], "".join(xin_que_num),"".join(xin_chu_num), "".join(xin_yi_num), "".join(que_num), "".join(si_num), "".join(zhong_num), "".join(yu_num),"".join(yisi_num), "".join(miqie_num), "".join(jie_num), "".join(guan_num), url) for mid_value in que_xi_num: City=mid_value[0] Num=mid_value[1] print("CITY:",City) print("num:",Num) SQL.insert_city(date[0],City,Num,url) # ,,,,Ur for mid_value in xin_shi_num: City=mid_value[0] Num=mid_value[1] print("CITY:",City) print("num:",Num) SQL.update_db(City,"New_Confirmed_num",Num) for mid_value in si_xi_num: City=mid_value[0] Num=mid_value[1] print("CITY:",City) print("num:",Num) SQL.update_db(City,"Dead_num",Num) for mid_value in zhong_xi_num: City=mid_value[0] Num=mid_value[1] print("CITY:",City) print("num:",Num) SQL.update_db(City,"Zhong_num",Num) for mid_value in chu_xi_num: City=mid_value[0] Num=mid_value[1] print("CITY:",City) print("num:",Num) SQL.update_db(City,"Cured_num",Num) if __name__ =='__main__': #ULS() #SQL.delete_db("hebei_info") #SQL.delete_db("hebei_city_info") #for url in hrefs: #info(url) info('http://wsjkw.hebei.gov.cn/content/content_14/398316.jhtml')
import pymysql db = pymysql.connect(host='localhost', port=3306, user='root', passwd='', db='yiqing') def select_db(): '''查询数据库''' # 打开数据库连接 sql = "select * from blog_info" # 使用 cursor() 方法创建一个游标对象cur cur = db.cursor() # 使用 execute() 方法执行 SQL 查询 cur.execute(sql) # 使用 fetchall() 方法获取查询结果 data = cur.fetchall() # print(data) # 取出对应的psw值 # 关闭数据库连接 #db.close() return data def delete_db(table): '''删除操作''' # 使用cursor()方法获取操作游标 cur = db.cursor() sql_delete = "delete from "+table+" " try: cur.execute(sql_delete) # 执行 # 提交 db.commit() except Exception as e: print("操作异常:%s" % str(e)) # 错误回滚 db.rollback() #finally: #db.close() def update_db(city,valuename,value): '''3.更新操作''' # 使用cursor()方法获取操作游标 cur = db.cursor() sql_update = "update hebei_city_info set "+valuename+"='"+value+"' where city='"+city+"'" try: cur.execute(sql_update) # 执行sql # 提交 db.commit() except Exception as e: # 错误回滚 print("错误信息:%s" % str(e)) db.rollback() #finally: #db.close() def insert_city(Date, City, Confirmed_num,Url): '''插入操作''' # 使用cursor()方法获取操作游标 cur = db.cursor() print(Confirmed_num) print(Date) print(City) print(Url) sql_insert= "insert into hebei_city_info (Date, City,Confirmed_num,Url) values('"+Date+"','"+City+"','"+Confirmed_num+"','"+Url+"')" print(sql_insert) try: cur.execute(sql_insert) # 提交 db.commit() except Exception as e: print("错误信息:%s" % str(e)) # 错误回滚 db.rollback() #finally: #db.close() def insert_province(Date, New_Confirmed_num, New_Cured_num,New_Yisi_num,Confirmed_num,Dead_num,Zhong_num,Cured_num,Yisi_num,Miqie_num,None_Guan_num,Guan_num,Url): '''插入操作''' # 使用cursor()方法获取操作游标 cur = db.cursor() sql_insert= "insert into hebei_info (Date, New_Confirmed_num, New_Cured_num,New_Yisi_num,Confirmed_num,Dead_num,Zhong_num,Cured_num,Yisi_num,Miqie_num,None_Guan_num,Guan_num,Url) values('"+Date+"','"+New_Confirmed_num+"','"+New_Cured_num+"','"+New_Yisi_num+"','"+Confirmed_num+"','"+Dead_num+"','"+Zhong_num+"','"+Cured_num+"','"+Yisi_num+"','"+Miqie_num+"','"+None_Guan_num+"','"+Guan_num+"','"+Url+"')" print(sql_insert) print("AAAA") try: cur.execute(sql_insert) # 提交 db.commit() except Exception as e: print("错误信息:%s" % str(e)) # 错误回滚 db.rollback() #finally: #db.close() if __name__ == "__main__": values=select_db() i=0 for value in values: i=i+1 print(value[2]) a = select_db()[0][0] print("查询结果:%s" %str(a)) #删除 #delete_db() #修改 #update_db() #insert_db()
数据库爬取数据详情: