python爬取百度体育东京2020奖牌榜数据并写入数据库中（一）

1.第三方库

import requests
import pymysql
import bs4

2.源代码

#1.寻找基础url
base_url = 'https://tiyu.baidu.com/tokffyolyhome/tab/奖牌榜'
#2.设置headers字典和params字典，再发请求
headers = {
    'user-agent': 'Mozilla/5.0 (Windows NT 6.1; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/77.0.3865.90 Safari/537.36',
}
#3.请求获取响应
response = requests.get(base_url, headers=headers)
#4.获取响应文档
texthtml=response.text
#5.使用lxml方式解析成bs4文档树
soup=bs4.BeautifulSoup(texthtml,'lxml')
#6.搜索文档树
#6.1获取类为ranlist类 下面的所有a标签，一个国家一个a标签
#6.2遍历解析国家获得奖牌数据
#准备一个列表容器用于存放所有国家的奖牌信息
countriesMedalInfo=[]
for item in soup.find(class_="rank-list").find_all("a"):
    cid=item.get("data-countryid")
    cname=item.find(class_="name").text
    allmedals=item.find(class_="item-all").text
    gold=item.find(class_="item-gold").text
    silver=item.find(class_="item-silver").text
    copper=item.find(class_="item-copper").text
    print("国家：{},国家id:{},获得金牌：{},获得银牌：{},获得铜牌：{},奖牌总数{}".format(cname,cid,gold,silver,copper,allmedals))
    countryMedalInfo={"cid":int(cid),"countryname":cname,"allmedal":int(allmedals),"gold":int(gold),"silver":int(silver),"cooper":int(copper)}
    countriesMedalInfo.append(countryMedalInfo)
#7.写入数据库
#7.1准备三条sql,查询，插入，更新
querySql="select * from medallist where cid = %d"
addSql="insert into medallist values(null,%d,'%s',%d,%d,%d,%d)"
updateSql="UPDATE `medallist` SET allmedal=%d,gold =%d,silver=%d,copper=%d WHERE cid =%d"
#7.3准备数据库连接
conn= pymysql.connect(host="192.168.xxx.xxx",user="xxx",
 	password="xxx",db="medal",port=3306)
#7.4获取游标
cursor = conn.cursor()
#7.5循环操作
for item in countriesMedalInfo:
    #7.3先查询有没有这个信息有则更新
    flag=cursor.execute(querySql%item['cid'])
    if flag>0:
        update=cursor.execute(updateSql%(item['allmedal'],item['gold'],item['silver'],item['cooper'],item['cid']))
        print('更新语句受影响的行数：',update)
    else:
        # 7.4没有则直接插入
        add=cursor.execute(addSql%((item['cid'],item['countryname'],item['allmedal'],item['gold'],item['silver'],item['cooper'])))
        print('添加语句受影响的行数：',add)
#8.1提交事务
conn.commit()
#8.2关闭资源
conn.close()

3.最终效果