1. # -*- coding = ut-8 -*-
    2. # @Time : 2021/4/14 10:15
    3. # @Author : PHC
    4. # @File lastGoodGoal.py
    5. # @Software : PyCharm
    7. from bs4 import  BeautifulSoup
    8. import xlwt
    9. import re
    10. import urllib.request,urllib.error
    11. import sqlite3
    14. def main():
    15.     link="https://v.qq.com/channel/nba?channel=nba&feature=4&iplayer=1&listpage=1"
    17.     askURl(link)
    18.     # savepath()
    19.     datalist=getDate(link)
    20.     savepath="科比个人锦集.xls"
    21.     # saveData(datalist,savepath)
    22.     dbpath="KoBeShow.db"
    23.     saveDB(datalist,dbpath)
    26. # 定制规则
    27. #1、获得影片的链接
    28. getVideoLink=re.compile(r'<a.*href="(.*?)".*>')                                      # .*
    29. #2、获得影片的封面
    30. getImgSrc=re.compile(r'<img.*src="(.*)".')
    31. #3、视频时长
    32. getTime=re.compile(r'<div class="figure_caption">(.*?)</div>')
    33. #4、获取标题名字
    34. getName=re.compile(r'<a.*target="_blank" title=.*>(.*?)</a>')
    37. #1、访问网页，得到网页内容
    38. def askURl(url):
    39.     head={
    40.         "user-agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/89.0.4389.114 Safari/537.36 Edg/89.0.774.76"
    41.     }
    42.     request=urllib.request.Request(url,headers=head)           #发送请求
    43.     html=""
    44.     try:
    45.         response=urllib.request.urlopen(request)                #响应请求
    46.         html=response.read().decode("utf-8")                    #根据请求获取网页内容
    47.         # print(html)
    48.     except urllib.error.URLError as e:
    49.         if hasattr(e,"code"):
    50.             print(e.code)
    51.         if hasattr(e,"reason"):
    52.             print(e.reason)
    54.     return html
    56. #2、定制规则、获取网页所需要的数据
    57. def getDate(link):
    58.     datalist=[]
    59.     html=askURl(link)
    61.     #逐一解析数据
    62.     soup=BeautifulSoup(html,"html.parser")
    63.     # for item in soup.find_all('div',class_="mod_figure mod_figure_h_default mod_figure_list_box"):
    64.     for item in soup.find_all('div', class_="list_item"):
    65.         data=[]                 #保存一部视频的信息
    66.         # # print(type(item))
    67.         item=str(item)
    68.         # print(item)
    70.         videoLink=re.findall(getVideoLink,item)[0]
    71.         # videoLink=videoLink.replace("[","")
    72.         # videoLink=re.sub("]","",videoLink)
    73.         data.append(videoLink)
    74.         # print(videoLink)
    76.         img=re.findall(getImgSrc,item)[0]
    77.         # img=img.replace("[","")
    78.         # img=re.sub("]","",img)
    79.         data.append(img)
    80.         # print(img)
    81.     #     #
    82.         time=re.findall(getTime,item)[0]
    83.         data.append(time)
    84.         # print(time)
    86.         name=re.findall(getName,item)[0]
    87.         data.append(name)
    88.         # print(name)
    90.         datalist.append(data)
    91.     # print(datalist)
    92.     return datalist
    94. #3、保存数据
    95. # def saveData(datalist,savepath):
    96. #     # print("数据已保存")
    97. #     table=xlwt.Workbook(encoding="utf-8",style_compression=0)
    98. #     sheet=table.add_sheet("科比绝杀锦集",cell_overwrite_ok=True)
    99. #     colum=("视频链接","视频封面","视频时长","视频名称")
    100. #     for i in range(0,4):
    101. #         sheet.write(0,i,colum[i])
    102. #     for i in range(len(datalist)):
    103. #         print("第%s条"%(i+1))
    104. #         data=datalist[i]
    105. #         for j in range(0,4):
    106. #             sheet.write(i+1,j,data[j])
    107. #     table.save(savepath)
    109. #保存数据到数据库
    111. def saveDB(datalist,dbpath):
    112.     create_db(dbpath)
    113.     conn=sqlite3.connect(dbpath)
    114.     cursor=conn.cursor()
    116.     for data in datalist:
    117.         for index in range(len(data)):
    118.             # if index==2:
    119.             #     continue
    120.             data[index]='"'+data[index]+'"'
    121.         sql='''
    122.             insert into kobeshow
    123.             (videolink,img,time,name)
    124.             values(%s)'''%",".join(data)
    126.         print(sql,";")
    127.         cursor.execute(sql)
    128.         conn.commit()
    130.     cursor.close()
    131.     conn.close()
    132.     print("已保存到数据库")
    135. #创建数据库
    136. def create_db(dbpath):
    137.     sql='''
    138.     create table if not exists kobeshow (
    139.     Id integer  primary key autoincrement,
    140.     videolink text,
    141.     img text,
    142.     time varchar (10),
    143.     name text
    144.     ) 
    146.     '''
    147.     conn=sqlite3.connect(dbpath)
    148.     cur=conn.cursor()
    149.     cur.execute(sql)
    150.     conn.commit()
    151.     conn.close()
    155.     print("数据库已创建")
    159. if __name__=="__main__":
    160.     main()
    161.     # create_db("test1.db")